Tidy your Data: Join Data Sets

Session 10: Join Data Sets

10.1 Tidying data

Often observations are spread across multiple data sets, and you will have to join data sets into one single table.

Go through the RStudio Primer Join Data Sets and learn about the different functions to join data sets.

 

Assignment

For this assignment, we will be joining the dataset taxation we used in Session 2 and 4 and a dataset from Open Data Basel City. You can download this new dataset from this webpage and call it kennzahlen_basel.

  1. Look at the two datasets. Join the kennzahlen_basel to the taxation dataset by year and quarter, retaining only the years that are present in both datasets (2015 to 2017). Your output should look like this:
year quarter quarter_no N income_mean income_median income_gini wealth_mean wealth_median wealth_gini Wohnviertel ID Anteil Sozialhilfeempfänger Anteil Ausländer Anteil Personen ohne Religionszugehörigkeit Anteil Personen in Einpersonenhaushalten Gymnasialquote Altersquotient Anteil Sesshafte Jugendquotient Baujahr der Wohngebäude Anteil Einfamilienhäuser Anteil Grünflächen Fläche pro Wohnung Wohnfläche pro Person Vermögenssteuer pro Veranlagung Einkommenssteuer pro Veranlagung Erwerbstätigenquote Arbeitsplätze pro Einwohner Arbeitslosenquote
2015 Altstadt Grossbasel 1 1675 98117 51000 0.637 1015165 34167 0.930 1 0.0171149144 0.2514262429 0.5179299104 0.3244626407 0.7777777778 0.2979214781 0.3288508557 0.1189376443 18254004065 0.1016260163 0.0666933545 90808058608 55739958 42974797255 15795731129 0.8242629221 4.3544473047 0.019072193
2015 Vorstädte 2 2999 99422 58511 0.580 1392666 44249 0.943 2 0.0316351684 0.3533278554 0.532251438 0.3458125947 0.6363636364 0.3747263059 0.2791700904 0.1479512043 18990979667 0.0757855823 0.1941983862 81803421369 51200218 49023171399 14894945946 0.779869044 4.5307181938 0.0234714672
2015 Am Ring 3 6239 75841 52788 0.521 435416 35245 0.865 3 0.0384108454 0.3403313877 0.4882319714 0.3000205634 0.5740740741 0.3601476015 0.3055921672 0.2076752768 19183832335 0.1984602224 0.2390179539 82343801381 47311517 2378931266 10820800512 0.8077279565 0.9906581653 0.0252819637
2015 Breite 4 5497 56292 47242 0.415 140160 13462 0.842 4 0.0566399639 0.3193049757 0.4715107751 0.2883006761 0.2924528302 0.3394025102 0.3543946745 0.227328973 19398939394 0.1363636364 0.3585967524 69810768933 39026379 48302681775 71238581681 0.7324650618 0.1403727831 0.0466659488
2015 St. Alban 5 6439 100470 61125 0.558 1033797 56238 0.911 5 0.0324150597 0.3085399449 0.4723599633 0.2511932616 0.4786324786 0.4901769768 0.3474747475 0.2779672025 19386675042 0.3400335008 0.3227323161 93782306324 49809694 38395558889 15620768077 0.7289325653 2.1964539042 0.0297646061
2015 Gundeldingen 6 11541 55575 47248 0.433 145346 11338 0.860 6 0.0616651708 0.3919011668 0.4412121852 0.2792870645 0.3366336634 0.2155230596 0.3012512539 0.2048743907 19318792135 0.029494382 0.1742961539 70418289354 38692324 4605646986 70755415415 0.7397509203 0.6350379648 0.0400550591
2015 Bruderholz 7 4986 115298 65114 0.607 2519406 62152 0.959 7 0.0176619936 0.2298266917 0.4586598962 0.1668169522 0.6081081081 0.4424654793 0.4273098576 0.3704222534 19484105769 0.7346153846 0.7137686422 10938321839 50067809 85856866706 18103101406 0.7528344063 0.1301440149 0.023127373
2015 Bachletten 8 8112 77778 57698 0.486 413563 35928 0.857 8 0.022092075 0.2103195196 0.4554822448 0.2249736565 0.6091370558 0.3746287129 0.4218251909 0.2948019802 19340255898 0.5617752899 0.4086191053 9317958042 47396467 16326508096 11363577884 0.7900055569 0.1900611389 0.0236359502
2015 Gotthelf 9 4191 63532 50790 0.441 229963 29072 0.821 9 0.0374269006 0.2742690058 0.4869883041 0.3019398786 0.5679012346 0.3534927332 0.3606725146 0.2498827942 19244623984 0.368902439 0.3251091924 7518842002 42361168 46323497124 95513314957 0.7500905263 0.3191841201 0.0259751729
2015 Iselin 10 9739 51788 44777 0.428 130113 8713 0.858 10 0.0894615291 0.3602771363 0.4455451562 0.2841523342 0.2623574144 0.2604693971 0.331530327 0.2539346526 19371656315 0.1677018634 0.3751254803 68638626339 37648552 42586793444 64053080479 0.7011769552 0.1550200906 0.0426790524
2015 St. Johann 11 10894 52273 41997 0.479 131943 7124 0.868 11 0.0887751873 0.4378626437 0.447884798 0.235626819 0.3183279743 0.2253960821 0.3009811162 0.2541949582 19329380356 0.2478576137 0.2731269759 71828334333 36619708 45047423073 61812197771 0.7038177893 1.0037327588 0.0492902629
2015 Altstadt Kleinbasel 12 1588 61193 42122 0.537 249077 8871 0.906 12 0.0605316973 0.3574642127 0.5390593047 0.3524734982 0.3181818182 0.2713711991 0.2748466258 0.1313826736 1880975 0.1035714286 0.0861577252 72511597084 43750334 87018052869 75096550613 0.822944757 1.7495900292 0.045515299
2015 Clara 13 2438 49702 40433 0.458 130164 8303 0.865 13 0.0890528905 0.464698647 0.4742927429 0.2700939457 0.1842105263 0.3136246787 0.3099630996 0.1792141021 19283307692 0.0192307692 0.1576626742 67319444444 37293003 40434198783 62212223124 0.6640630012 0.6648474784 0.0491365353
2015 Wettstein 14 3455 73498 54256 0.487 296022 28136 0.855 14 0.0484707566 0.2952960114 0.5106421034 0.2716890772 0.5972222222 0.3012472885 0.3634412449 0.2147505423 19332017045 0.3394886364 0.2750203793 83081255771 45870096 16889717411 99341610453 0.7730590712 1.8122657655 0.0307747873
2015 Hirzbrunnen 15 5289 59376 51368 0.410 186072 16113 0.849 15 0.049250056 0.2244235505 0.4723528095 0.1943587987 0.3105590062 0.4592964824 0.4173942243 0.3364824121 19402349903 0.6914138154 0.5877485987 80509463371 3923069 67001187285 72444279969 0.716425039 0.3392196911 0.0335215832
2015 Rosental 16 3158 51788 42854 0.466 76964 3000 0.880 16 0.1069889451 0.5441259134 0.3846730373 0.2144889057 0.0921052632 0.129697286 0.231216039 0.2630480167 19459581882 0.0522648084 0.0838723923 69305816789 33392761 22505179584 58161758034 0.7003501874 0.9755240767 0.0561192764
2015 Matthäus 17 9052 50044 38545 0.503 128623 4561 0.888 17 0.1276765937 0.5161210928 0.4467142506 0.241082177 0.2163461538 0.1651172708 0.2823037165 0.2209808102 19248153034 0.0712401055 0.1672942887 67381836945 34554332 41903382451 62024897299 0.6752448448 0.1882448695 0.0512705919
2015 Klybeck 18 4093 42281 37400 0.435 59248 1500 0.882 18 0.1295590637 0.5145617855 0.41385411 0.2189267485 0.184 0.2063812601 0.330430049 0.2774636511 19369953917 0.0737327189 0.1134904954 65612052731 31787298 1752650514 46334769946 0.6123583837 0.9509754565 0.0704337269
2015 Kleinhüningen 19 1609 45955 41403 0.418 68636 2678 0.864 19 0.1331710709 0.4568845619 0.3831710709 0.1889650198 0.170212766 0.2797356828 0.3358831711 0.3039647577 19462710843 0.156626506 0.0958097627 7050295858 32888446 19243363995 47714277267 0.6601747734 1.652421818 0.0575156161
2015 Riehen 20 11758 90865 63689 0.520 642705 46952 0.875 20 0.032510622 0.2406549864 0.4588723922 0.1653416573 0.5225 0.4891934634 0.4338091373 0.3511685117 19569330085 0.6613885505 0.7971970588 1029512657 47776757 29589716796 13108315789 0.732929437 0.1599758339 0.018997877
2015 Bettingen 30 628 122319 68594 0.620 2359934 78649 0.948 30 0.0153473344 0.2770597738 0.4216478191 0.1113105925 0.6666666667 0.4057771664 0.4095315024 0.2971114168 19619710611 0.7909967846 0.9034484133 12140650407 51261065 26926188198 13426116427 0.732929437 0.2943346012 0.018997877
2016 Altstadt Grossbasel 1 1676 99020 51264 0.643 1042452 31236 0.928 1 0.0186234818 0.2526315789 0.5275303644 0.3238866397 0.6875 0.2966150316 0.3275303644 0.1204819277 18254615385 0.1012145749 0.0669919477 90383055757 55190427699 51881848894 17343393735 0.821070228 4.4224977046 0.0152577544
2016 Vorstädte 2 3024 103696 58786 0.588 1434679 48881 0.940 2 0.0324412003 0.3684103812 0.5419708029 0.3306934932 0.6666666667 0.379525593 0.2761557178 0.1598002497 18986623616 0.0793357934 0.1959563245 82064324002 50417294042 78289125886 16127909889 0.8100294788 4.5909770873 0.0242808282
2016 Am Ring 3 6178 77051 54114 0.519 448259 38772 0.859 3 0.0349558354 0.33959782 0.4949257658 0.2939909762 0.595959596 0.357468727 0.3062394287 0.208682855 19184872014 0.1988054608 0.2396088874 82223343643 46895766814 32120137397 11520438408 0.8025100513 0.9869864551 0.0273742642
2016 Breite 4 5619 56742 47243 0.426 149641 14148 0.843 4 0.0561708861 0.3244801085 0.4772830018 0.2854202401 0.311827957 0.3311619718 0.3549954792 0.226584507 19397367625 0.1376701967 0.3569380827 69808843406 38815268399 48215108977 72009137421 0.7127164035 0.1178869912 0.0462277709
2016 St. Alban 5 6403 103531 61595 0.568 1018634 59000 0.904 5 0.0299752498 0.3103859199 0.4807956733 0.2455745996 0.4727272727 0.4914786561 0.3495279127 0.2791754585 19386368997 0.3428812131 0.3277974454 93616527846 49285013675 42061965759 15633891206 0.7473000328 2.1554188667 0.0295674895
2016 Gundeldingen 6 11572 56107 47690 0.436 147613 12709 0.852 6 0.0681336161 0.3957582185 0.4475609756 0.2757723577 0.2478632479 0.2169818428 0.3036585366 0.2039478641 1932197193 0.0294736842 0.1771602738 70454019874 38661596132 46721327303 70155679743 0.7435328659 0.6310109477 0.0392068343
2016 Bruderholz 7 5122 125412 65674 0.635 2549938 74863 0.953 7 0.0179812466 0.2311086597 0.4611141754 0.1616093504 0.6241610738 0.4418511066 0.4243794815 0.3820925553 19484359962 0.7348411935 0.7148602913 10942018349 49539984254 19314398374 19364924846 0.7368534062 0.1345701144 0.0217801474
2016 Bachletten 8 8075 79537 58412 0.493 464497 45676 0.850 8 0.0238920434 0.2138485362 0.4655261411 0.2191074232 0.6507936508 0.3713899472 0.419733058 0.2951947892 19341433134 0.5636726547 0.4158910593 93209538376 46922235541 1762144018 11286095512 0.7867315425 0.1941237061 0.0208221466
2016 Gotthelf 9 4217 63460 50693 0.445 704932 27817 0.941 9 0.0410758661 0.2762753983 0.4850168104 0.2916604947 0.4512195122 0.3506920009 0.3533109195 0.2540464462 19245126904 0.3675126904 0.3234019862 75194288504 41694555704 34833688952 85917451399 0.7708965529 0.3163428181 0.0273422872
2016 Iselin 10 9709 51446 44369 0.434 127693 9631 0.849 10 0.0888379942 0.3673457088 0.4484691418 0.2752863758 0.2824427481 0.2552763819 0.3269648023 0.2606669712 19371028295 0.1670117322 0.3683115309 6861622807 37090903537 42508366863 63584781801 0.6941878054 0.1599629595 0.0459342343
2016 St. Johann 11 10868 52412 41580 0.486 146195 8176 0.869 11 0.0852206273 0.4343434343 0.4583732057 0.2336505477 0.3169014085 0.2272119544 0.3071770335 0.252143138 19329156785 0.2496706192 0.2714846156 71766733068 36569330525 48603965292 6338095167 0.7058676515 1.0017170903 0.0485466598
2016 Altstadt Kleinbasel 12 1614 61429 42007 0.533 282293 10020 0.904 12 0.0673511294 0.3585215606 0.549486653 0.3411400795 0.3333333333 0.2751599767 0.2755646817 0.1413612565 18808932384 0.1067615658 0.0809756044 72425378041 43858865565 11232948052 81315571429 0.7344872505 1.6829506213 0.0375301588
2016 Clara 13 2415 50801 40142 0.474 135518 8111 0.867 13 0.0796723753 0.4638868206 0.4740630429 0.2709424084 0.2285714286 0.3268438787 0.3137254902 0.1815799326 19283307692 0.0192307692 0.1596210692 67242647059 37087506589 43689323843 63939031238 0.6584951805 0.682934165 0.0502164591
2016 Wettstein 14 3421 76177 55101 0.496 350734 31499 0.860 14 0.0518994114 0.3090779383 0.520242554 0.265625 0.5846153846 0.3052230686 0.3624041377 0.2200761697 19330711238 0.3371266003 0.266193975 83063306699 45613367799 12295143029 99022080699 0.7717170245 1.7915593592 0.0401573444
2016 Hirzbrunnen 15 5378 60768 52620 0.412 195472 18501 0.839 15 0.0469791204 0.2202354509 0.4796756997 0.1888775856 0.2848101266 0.4476663357 0.415926255 0.3406156902 19416778481 0.6974683544 0.5876235025 81240291262 39170436553 68139708738 71508539806 0.7290199848 0.3431629351 0.0369978956
2016 Rosental 16 3363 54494 44851 0.466 92875 3833 0.884 16 0.0970354771 0.5493277175 0.4132512555 0.2204309919 0.1728395062 0.126899017 0.209460554 0.2522341376 19486174497 0.0704697987 0.1002189053 69534114202 34063791109 24050197771 57832405609 0.6825391622 1.0550658217 0.0665948747
2016 Matthäus 17 9009 51111 38102 0.511 143892 5302 0.887 17 0.1231140242 0.5114395251 0.4520158298 0.2417771545 0.2081218274 0.1688927187 0.2877195152 0.2246445498 19249359087 0.0711150132 0.1653125582 67272074741 34462788763 42609984706 62866448547 0.6746880259 0.1895854429 0.0506528739
2016 Klybeck 18 3982 41703 36803 0.437 60372 1965 0.879 18 0.126218926 0.5177860184 0.4117566268 0.2165480675 0.1652173913 0.2014227642 0.3414366158 0.2784552846 19369701149 0.0735632184 0.1176141824 65596241611 31850266629 1621977333 44860079576 0.6086956209 0.8460756002 0.068902559
2016 Kleinhüningen 19 1577 46046 41368 0.414 71011 3763 0.857 19 0.135862069 0.4637931034 0.3806896552 0.1855559528 0.125 0.2821782178 0.3482758621 0.3129812981 19462710843 0.156626506 0.1555940923 70403690037 32466906475 20774610106 51625021834 0.6372415353 1.8708456991 0.0623085841
2016 Riehen 20 11891 92220 63592 0.526 810996 60135 0.883 20 0.0339738368 0.2454837319 0.4653313527 0.1652957305 0.5130890052 0.4886212698 0.4333700704 0.3593376428 19570231425 0.6618757613 0.7966213488 1028827326 47703072174 32961133034 13082421204 0.7386451888 0.1633855008 0.0193855888
2016 Bettingen 30 629 135547 67711 0.661 2216243 99999 0.934 30 0.0132890365 0.2516611296 0.4401993355 0.123853211 0.7647058824 0.4207492795 0.4119601329 0.3141210375 19619710611 0.7942122186 0.9025974193 12124096386 52376958525 28447711599 13690040752 0.7386451888 0.295300345 0.0193855888
2017 Altstadt Grossbasel 1 1694 97111 53596 0.631 1060205 31391 0.928 1 0.0192458759 0.2706205813 0.5161036921 0.3013100437 0.8235294118 0.2827663134 0.3153967007 0.1372002231 18225777778 0.103030303 0.0672594599 90238128987 53608292683 53431955206 16397193099 0.8330483229 4.6034157729 0.0257474605
2017 Vorstädte 2 3005 103714 59216 0.589 1523290 48690 0.941 2 0.0292712067 0.3823178017 0.5459976105 0.3250573753 0.7096774194 0.3679706601 0.2751891677 0.1668704156 18988284133 0.0774907749 0.1979066977 81919421488 49782854747 90282837567 17822478275 0.7795234 4.5962916034 0.0242808282
2017 Am Ring 3 6177 78761 54487 0.525 476747 41941 0.861 3 0.0358673374 0.3487982113 0.5071734675 0.2812912395 0.6 0.3513671589 0.3066890255 0.2181605498 19186879795 0.199488491 0.2387369114 82188064516 46070146819 34892898736 11942650296 0.799931595 1.0124582407 0.0313845067
2017 Breite 4 5605 56888 47393 0.431 153404 14799 0.843 4 0.0574082363 0.3239704566 0.4905998209 0.2817591432 0.3176470588 0.3458501491 0.3446732319 0.2221442358 19409206587 0.1347305389 0.3609948412 70018878611 38894604768 4752415108 71914427093 0.7366764015 0.1186841507 0.0492950163
2017 St. Alban 5 6437 102457 62163 0.560 1056515 65590 0.902 5 0.0297356828 0.3163546256 0.4913729809 0.2442383361 0.4960629921 0.4835733938 0.345356094 0.2798187409 19385498311 0.3403716216 0.3217029926 93656417765 4927614194 48195869632 15825977301 0.745714483 2.1779987176 0.0293703729
2017 Gundeldingen 6 11457 56544 47463 0.441 161585 13491 0.855 6 0.0721360097 0.3906043112 0.4513532122 0.2741177106 0.2478632479 0.2185565454 0.3108945501 0.2068548996 19322265077 0.0294530154 0.1748682421 70457689544 38581193803 52614626172 71513573948 0.7515026656 0.6213669972 0.0418457558
2017 Bruderholz 7 5126 105973 65812 0.561 1273808 78357 0.900 7 0.0155299739 0.2415291051 0.4768679409 0.1561637788 0.6666666667 0.4294129197 0.4206125109 0.3785588062 19485173244 0.7338787295 0.714625621 10931231437 49012147985 20903358745 2053130534 0.741483763 0.1338107448 0.0251482114
2017 Bachletten 8 8068 81580 59358 0.497 491478 50438 0.848 8 0.0260689757 0.2177830293 0.4749279965 0.2165757417 0.7150537634 0.3730717018 0.4221253969 0.2980377638 19341489617 0.5607028754 0.4165584579 93102741623 46661074077 18933535516 11232568499 0.7890182081 0.1855298991 0.0241987109
2017 Gotthelf 9 4211 65391 50910 0.462 698434 30156 0.936 9 0.0440251572 0.2776071376 0.4942226123 0.2907906426 0.4457831325 0.3477246208 0.3571741992 0.2478413069 19246744659 0.3682604273 0.3236973914 75034021871 41631672598 37425216358 87348026629 0.7561847968 0.2952870375 0.0344512819
2017 Iselin 10 9790 51600 44173 0.437 137154 10000 0.851 10 0.0941134157 0.3751348759 0.4521040643 0.2700090607 0.3027888446 0.2541942505 0.3278383887 0.2586378888 19371906401 0.1665519615 0.3644713358 68560730215 36696560494 43963134635 6306206372 0.7022997678 0.1585375962 0.0501539147
2017 St. Johann 11 10819 52890 42027 0.490 151139 8508 0.864 11 0.0874826741 0.4340014927 0.4664676405 0.2319218603 0.3695652174 0.2264596958 0.3177311014 0.2515956189 193293083 0.2476943347 0.2711033057 71778727634 3640733945 55031399192 64947676276 0.7007318463 0.9993926236 0.050352553
2017 Altstadt Kleinbasel 12 1586 62138 41962 0.546 286780 9591 0.905 12 0.0721153846 0.3766025641 0.5612980769 0.3301846286 0.6111111111 0.2695800227 0.2752403846 0.1469920545 18804929078 0.1028368794 0.0809475704 72333116037 42812662619 11113745342 82525018634 0.7428789485 1.6119596398 0.0542989532
2017 Clara 13 2432 50680 40137 0.471 139220 8430 0.862 13 0.0797912006 0.463335819 0.4789957743 0.2705667276 0.2307692308 0.3241017964 0.3233905046 0.181511976 19283461538 0.0230769231 0.1596210148 67138123924 36921764243 46532129817 61755711968 0.6597514819 0.677452829 0.0539961926
2017 Wettstein 14 3413 79537 55521 0.517 373707 33876 0.862 14 0.0478045326 0.3179886686 0.529213881 0.2606643674 0.6029411765 0.2857904559 0.3549929178 0.219941349 193315625 0.3352272727 0.2812192436 83239791219 45582391542 19445770912 10408881084 0.7898662651 1.8146923057 0.038280833
2017 Hirzbrunnen 15 5419 60699 52717 0.411 184461 18864 0.825 15 0.0474539545 0.2399783315 0.4895991333 0.1926121372 0.3771428571 0.4339984651 0.4053087757 0.336914812 19417718078 0.6965865992 0.5900990697 80952634948 39319586495 7257829222 74472770398 0.7221371046 0.339768581 0.0402258999
2017 Rosental 16 3347 54543 44634 0.468 87336 3866 0.863 16 0.1051587302 0.5573870574 0.4252136752 0.2099798543 0.25 0.1183767872 0.208028083 0.2592514718 19486174497 0.0704697987 0.1378866555 69282678311 33677971375 23941690647 58310964029 0.7069895936 1.0521806526 0.0654724892
2017 Matthäus 17 8997 50786 38565 0.502 148639 5867 0.881 17 0.1200878845 0.5062146893 0.4620213434 0.2448914227 0.2569832402 0.1725466165 0.2908976773 0.222008229 19251622807 0.0719298246 0.1657748101 67282379603 34715461266 4749281424 6265424484 0.6766771209 0.1766922707 0.0554710741
2017 Klybeck 18 3974 41569 36178 0.444 66050 2242 0.878 18 0.1362121633 0.5166620383 0.4180783116 0.2207061471 0.1379310345 0.2058579144 0.3419883366 0.2901952638 19370229358 0.0733944954 0.1181822602 655081529 3177527931 17609207038 44101190648 0.6085440815 0.8199685746 0.0716586613
2017 Kleinhüningen 19 1557 45664 40315 0.423 76802 3415 0.857 19 0.1431040442 0.4690632561 0.391289319 0.1869393613 0.2222222222 0.2806629834 0.3515381956 0.317679558 19464011976 0.1556886228 0.1550874766 70636764706 32593659942 17110575139 48391428571 0.6320040497 2.0457450292 0.0631074121
2017 Riehen 20 11965 92348 63726 0.525 841152 60515 0.883 20 0.0354745012 0.2554164087 0.4744059807 0.1656778837 0.5178082192 0.4871772275 0.4324555974 0.3636203402 19572413207 0.6608400097 0.7949819327 10291804734 4759281612 33228178059 13431462954 0.742600131 0.164020109 0.0193855888
2017 Bettingen 30 626 129265 67289 0.652 2379097 108794 0.933 30 0.012605042 0.2478991597 0.4571428571 0.126394052 0.6 0.423580786 0.4260504202 0.308588064 19619710611 0.7942122186 0.9024492559 12134406439 52380952381 32142287066 14099867508 0.742600131 0.2840578741 0.0193855888

Created by Gayoung Son for the Center for Cognitive and Decision Sciences