Join

Each of the data verbs described until now takes one one data table as an input. Join is different. It’s well named: it brings together two data tables. Once you can join two data tables, you can join any number of data tables by repeated joins. Joining is at the heart of combining data from multiple sources.

It’s conventional to refer to the two tables as the “left” and “right” tables.

To illustrate the differences and similarities between the different kinds of join, suppose you have these two tables:

clinicName postalCode
A 22120.00
B 35752.00
C 56718.00
D 35752.00
E 67756.00
F 69129.00
G 73455.00
H 73455.00
I 76292.00
postalCode over65
35752.00 0.46
22120.00 0.72
22120.00 0.93
92332.00 0.26
84739.00 0.46
67756.00 0.94

Variables that appear in both the left and right tables are called “overlap variables.” The only overlap variable here is postalCode.

The overlap variables determine which cases will go into the output table. In this example, there is just one overlap variable: postalCode.

The diagram below shows the cases in the left and right tables. The lines show the matches between left and right. The cases connected by a match are the overlap cases; there are five of them in the diagram. Cases without a match also appear in both the left and right tables.

plot of chunk exampleplot

Note that there are three different kinds of cases here:

  1. The matching cases that are in both the left and right. These come as pairs: the cases connected by a line in the diagram.
  2. Non-matching ones in the left.
  3. Non-matching ones in the right.

There are different types of join. The type of join specifies whether you want to include in the output the matching cases, the matching pairs, or the non-matching cases.

Inner Join

An inner join gives the matching pairs. Note that clinic A, which had two matches in the right table, appears twice, once for each matching pair in which clinic A is involved.

merge( LL, RR )
postalCode clinicName over65
22120.00 A 0.72
22120.00 A 0.93
35752.00 B 0.46
35752.00 D 0.46
67756.00 E 0.94

Outer Join

An outer join can include cases where there is no match. You might want to include the unmatched cases from the left table, from the right table, or from both tables.

Unmatched cases from the left table
merge( LL, RR, all.x=TRUE )
postalCode clinicName over65
22120.00 A 0.72
22120.00 A 0.93
35752.00 B 0.46
35752.00 D 0.46
56718.00 C
67756.00 E 0.94
69129.00 F
73455.00 H
73455.00 G
76292.00 I
Unmatched cases from the right table
merge( LL, RR, all.y=TRUE )
postalCode clinicName over65
22120.00 A 0.72
22120.00 A 0.93
35752.00 B 0.46
35752.00 D 0.46
67756.00 E 0.94
84739.00 0.46
92332.00 0.26
Unmatched cases from both tables
merge( LL, RR, all=TRUE )
postalCode clinicName over65
22120.00 A 0.72
22120.00 A 0.93
35752.00 B 0.46
35752.00 D 0.46
56718.00 C
67756.00 E 0.94
69129.00 F
73455.00 H
73455.00 G
76292.00 I
84739.00 0.46
92332.00 0.26

Using Join

Join is useful for many tasks.

Translate

Sometimes you want to rename or translate the levels of a categorical variable. If there is a large number of levels, it can make sense to store the translation in a data table, or to create it in a data table.

For example, in the Minneapolis mayoral election, voters made 38 different first choices.1 It’s ungainly to look at the relationships among all those choices. A sensible approach is to group the minor candidates together.

Defining a minor candidate to be one who got fewer than 1000 votes, it’s possible to create a table identifying the minor candidates:

Totals <- Minneapolis2013 %>%
  group_by( First ) %>%
  summarise( nVotes=n() )
CandidateGroups <- 
  Totals %>% 
  mutate( Candidate=ifelse( nVotes>1000, 
                            as.character(First), 
                            "Minor") )
First nVotes Candidate
ABDUL M RAHAMAN “THE ROCK” 338 Minor
ALICIA K. BENNETT 351 Minor
BETSY HODGES 28935 BETSY HODGES
BILL KAHN 97 Minor
BOB “AGAIN” CARNEY JR 56 Minor
BOB FINE 2094 BOB FINE
CAM WINTON 7511 CAM WINTON
CAPTAIN JACK SPARROW 264 Minor
CHRISTOPHER CLARK 188 Minor
CHRISTOPHER ROBIN ZIMMERMAN 170 Minor
CYD GORMAN 39 Minor
DAN COHEN 1798 DAN COHEN
DON SAMUELS 8335 DON SAMUELS
DOUG MANN 779 Minor
EDMUND BERNARD BRUYERE 70 Minor
GREGG A. IVERSON 144 Minor
JACKIE CHERRYHOMES 3524 JACKIE CHERRYHOMES
JAMES “JIMMY” L. STROUD, JR. 64 Minor
JAMES EVERETT 347 Minor
JAYMIE KELLY 196 Minor
JEFFREY ALAN WAGNER 164 Minor
JOHN CHARLES WILSON 37 Minor
JOHN LESLIE HARTWIG 97 Minor
JOSHUA REA 108 Minor
KURTIS W. HANNA 200 Minor
MARK ANDREW 19584 MARK ANDREW
MARK V ANDERSON 975 Minor
MERRILL ANDERSON 108 Minor
MIKE GOULD 204 Minor
NEAL BAXTER 145 Minor
OLE SAVIOR 693 Minor
overvote 93 Minor
RAHN V. WORKCUFF 65 Minor
STEPHANIE WOODRUFF 1010 STEPHANIE WOODRUFF
TONY LANE 219 Minor
TROY BENJEGERDES 148 Minor
undervote 834 Minor
UWI 117 Minor
nrow(CandidateGroups)
[1] 38

This table gives the translation between the listing in First and listing that lumps minor candidates together. There is one case for each candidate. Joining the translation table with the vote table produces a new column, with one case for each ballot and the minor candidates marked as such.

merge( Minneapolis2013, CandidateGroups, all.x=TRUE )
Candidate First Second
Minor ABDUL M RAHAMAN “THE ROCK” undervote
Minor ABDUL M RAHAMAN “THE ROCK” CAPTAIN JACK SPARROW
Minor ABDUL M RAHAMAN “THE ROCK” GREGG A. IVERSON
Minor ABDUL M RAHAMAN “THE ROCK” OLE SAVIOR
Minor ABDUL M RAHAMAN “THE ROCK” undervote
Minor ABDUL M RAHAMAN “THE ROCK” STEPHANIE WOODRUFF
Minor ABDUL M RAHAMAN “THE ROCK” undervote
Minor ABDUL M RAHAMAN “THE ROCK” OLE SAVIOR
Minor ABDUL M RAHAMAN “THE ROCK” undervote
Minor ABDUL M RAHAMAN “THE ROCK” BOB FINE
Minor ABDUL M RAHAMAN “THE ROCK” ABDUL M RAHAMAN “THE ROCK”
Minor ABDUL M RAHAMAN “THE ROCK” undervote
Minor ABDUL M RAHAMAN “THE ROCK” undervote
Minor ABDUL M RAHAMAN “THE ROCK” MIKE GOULD
Minor ABDUL M RAHAMAN “THE ROCK” undervote
Minor ABDUL M RAHAMAN “THE ROCK” undervote
Minor ABDUL M RAHAMAN “THE ROCK” undervote
Minor ABDUL M RAHAMAN “THE ROCK” undervote
Minor ABDUL M RAHAMAN “THE ROCK” CAM WINTON
Minor ABDUL M RAHAMAN “THE ROCK” undervote
Minor ABDUL M RAHAMAN “THE ROCK” BETSY HODGES
Minor ABDUL M RAHAMAN “THE ROCK” BILL KAHN
Minor ABDUL M RAHAMAN “THE ROCK” undervote
Minor ABDUL M RAHAMAN “THE ROCK” undervote
Minor ABDUL M RAHAMAN “THE ROCK” undervote
Minor ABDUL M RAHAMAN “THE ROCK” MARK ANDREW
Minor ABDUL M RAHAMAN “THE ROCK” MIKE GOULD
Minor ABDUL M RAHAMAN “THE ROCK” undervote
Minor ABDUL M RAHAMAN “THE ROCK” undervote
Minor ABDUL M RAHAMAN “THE ROCK” JAYMIE KELLY
Minor ABDUL M RAHAMAN “THE ROCK” BETSY HODGES
Minor ABDUL M RAHAMAN “THE ROCK” JOHN LESLIE HARTWIG
Minor ABDUL M RAHAMAN “THE ROCK” undervote
Minor ABDUL M RAHAMAN “THE ROCK” undervote
Minor ABDUL M RAHAMAN “THE ROCK” BILL KAHN
Minor ABDUL M RAHAMAN “THE ROCK” MARK ANDREW
Minor ABDUL M RAHAMAN “THE ROCK” ABDUL M RAHAMAN “THE ROCK”
Minor ABDUL M RAHAMAN “THE ROCK” undervote
Minor ABDUL M RAHAMAN “THE ROCK” MARK ANDREW
Minor ABDUL M RAHAMAN “THE ROCK” ABDUL M RAHAMAN “THE ROCK”
Minor ABDUL M RAHAMAN “THE ROCK” undervote
Minor ABDUL M RAHAMAN “THE ROCK” ABDUL M RAHAMAN “THE ROCK”
Minor ABDUL M RAHAMAN “THE ROCK” BETSY HODGES
Minor ABDUL M RAHAMAN “THE ROCK” ABDUL M RAHAMAN “THE ROCK”
Minor ABDUL M RAHAMAN “THE ROCK” CYD GORMAN
Minor ABDUL M RAHAMAN “THE ROCK” undervote
Minor ABDUL M RAHAMAN “THE ROCK” undervote
Minor ABDUL M RAHAMAN “THE ROCK” JAMES EVERETT
Minor ABDUL M RAHAMAN “THE ROCK” ABDUL M RAHAMAN “THE ROCK”
Minor ABDUL M RAHAMAN “THE ROCK” BETSY HODGES

Complete

Often, information is spread among multiple sources. Joining can bring this information together.

For instance, here are the population and area variables from CountryData.

PopArea <-
  select( CountryData, country, area, pop)
PopArea
country area pop
Afghanistan 652230.00 31822848.00
Akrotiri 123.00 15700.00
Albania 28748.00 3020209.00
Algeria 2381741.00 38813722.00
American Samoa 199.00 54517.00
Andorra 468.00 85458.00
Angola 1246700.00 19088106.00
Anguilla 91.00 16086.00
Antarctica
Antigua and Barbuda 443.00 91295.00
Argentina 2780400.00 43024374.00
Armenia 29743.00 3060631.00
Aruba 180.00 110663.00
Ashmore and Cartier Islands 5.00
Australia 7741220.00 22507617.00
Austria 83871.00 8223062.00
Azerbaijan 86600.00 9686210.00
Bahamas, The 13880.00 321834.00
Bahrain 760.00 1314089.00
Bangladesh 143998.00 166280712.00
Barbados 430.00 289680.00
Belarus 207600.00 9608058.00
Belgium 30528.00 10449361.00
Belize 22966.00 340844.00
Benin 112622.00 10160556.00
Bermuda 54.00 69839.00
Bhutan 38394.00 733643.00
Bolivia 1098581.00 10631486.00
Bosnia and Herzegovina 51197.00 3871643.00
Botswana 581730.00 2155784.00
Bouvet Island 49.00
Brazil 8514877.00 202656788.00
British Indian Ocean Territory 54400.00
British Virgin Islands 151.00 32680.00
Brunei 5765.00 422675.00
Bulgaria 110879.00 6924716.00
Burkina Faso 274200.00 18365123.00
Burma 676578.00 55746253.00
Burundi 27830.00 10395931.00
Cabo Verde 4033.00 538535.00
Cambodia 181035.00 15458332.00
Cameroon 475440.00 23130708.00
Canada 9984670.00 34834841.00
Cayman Islands 264.00 54914.00
Central African Republic 622984.00 5277959.00
Chad 1284000.00 11412107.00
Chile 756102.00 17363894.00
China 9596960.00 1355692576.00
Christmas Island 135.00 1530.00
Clipperton Island 6.00
Cocos (Keeling) Islands 14.00 596.00
Colombia 1138910.00 46245297.00
Comoros 2235.00 766865.00
Congo, Democratic Republic of the 2344858.00 77433744.00
Congo, Republic of the 342000.00 4662446.00
Cook Islands 236.00 10134.00
Coral Sea Islands 3.00
Costa Rica 51100.00 4755234.00
Cote d’Ivoire 322463.00 22848945.00
Croatia 56594.00 4470534.00
Cuba 110860.00 11047251.00
Curacao 444.00 146836.00
Cyprus 9251.00 1172458.00
Czech Republic 78867.00 10627448.00
Denmark 43094.00 5569077.00
Dhekelia 131.00 15700.00
Djibouti 23200.00 810179.00
Dominica 751.00 73449.00
Dominican Republic 48670.00 10349741.00
Ecuador 283561.00 15654411.00
Egypt 1001450.00 86895099.00
El Salvador 21041.00 6125512.00
Equatorial Guinea 28051.00 722254.00
Eritrea 117600.00 6380803.00
Estonia 45228.00 1257921.00
Ethiopia 1104300.00 96633458.00
European Union 511434812.00
Falkland Islands (Islas Malvinas) 12173.00 2840.00
Faroe Islands 1393.00 49947.00
Fiji 18274.00 903207.00
Finland 338145.00 5268799.00
France 643801.00 66259012.00
French Polynesia 4167.00 280026.00
French Southern and Antarctic Lands 55.00
Gabon 267667.00 1672597.00
Gambia, The 11295.00 1925527.00
Gaza Strip 360.00 1816379.00
Georgia 69700.00 4935880.00
Germany 357022.00 80996685.00
Ghana 238533.00 25758108.00
Gibraltar 7.00 29185.00
Greece 131957.00 10775557.00
Greenland 2166086.00 57728.00
Grenada 344.00 110152.00
Guam 544.00 161001.00
Guatemala 108889.00 14647083.00
Guernsey 78.00 65849.00
Guinea 245857.00 11474383.00
Guinea-Bissau 36125.00 1693398.00
Guyana 214969.00 735554.00
Haiti 27750.00 9996731.00
Heard Island and McDonald Islands 412.00
Holy See (Vatican City) 0.00 842.00
Honduras 112090.00 8598561.00
Hong Kong 1104.00 7112688.00
Howland Island 2.00
Hungary 93028.00 9919128.00
Iceland 103000.00 317351.00
India 3287263.00 1236344631.00
Indonesia 1904569.00 253609643.00
Iran 1648195.00 80840713.00
Iraq 438317.00 32585692.00
Ireland 70273.00 4832765.00
Isle of Man 572.00 86866.00
Israel 20770.00 7821850.00
Italy 301340.00 61680122.00
Jamaica 10991.00 2930050.00
Jan Mayen 377.00
Japan 377915.00 127103388.00
Jersey 116.00 96513.00
Jordan 89342.00 7930491.00
Kazakhstan 2724900.00 17948816.00
Kenya 580367.00 45010056.00
Kiribati 811.00 104488.00
Korea, North 120538.00 24851627.00
Korea, South 99720.00 49039986.00
Kosovo 10887.00 1859203.00
Kuwait 17818.00 2742711.00
Kyrgyzstan 199951.00 5604212.00
Laos 236800.00 6803699.00
Latvia 64589.00 2165165.00
Lebanon 10400.00 5882562.00
Lesotho 30355.00 1942008.00
Liberia 111369.00 4092310.00
Libya 1759540.00 6244174.00
Liechtenstein 160.00 37313.00
Lithuania 65300.00 3505738.00
Luxembourg 2586.00 520672.00
Macau 28.00 587914.00
Macedonia 25713.00 2091719.00
Madagascar 587041.00 23201926.00
Malawi 118484.00 17377468.00
Malaysia 329847.00 30073353.00
Maldives 298.00 393595.00
Mali 1240192.00 16455903.00
Malta 316.00 412655.00
Marshall Islands 181.00 70983.00
Mauritania 1030700.00 3516806.00
Mauritius 2040.00 1331155.00
Mexico 1964375.00 120286655.00
Micronesia, Federated States of 702.00 105681.00
Moldova 33851.00 3583288.00
Monaco 2.00 30508.00
Mongolia 1564116.00 2953190.00
Montenegro 13812.00 650036.00
Montserrat 102.00 5215.00
Morocco 446550.00 32987206.00
Mozambique 799380.00 24692144.00
Namibia 824292.00 2198406.00
Nauru 21.00 9488.00
Navassa Island 5.00
Nepal 147181.00 30986975.00
Netherlands 41543.00 16877351.00
New Caledonia 18575.00 267840.00
New Zealand 267710.00 4401916.00
Nicaragua 130370.00 5848641.00
Niger 1267000.00 17466172.00
Nigeria 923768.00 177155754.00
Niue 260.00 1190.00
Norfolk Island 36.00 2210.00
Northern Mariana Islands 464.00 51483.00
Norway 323802.00 5147792.00
Oman 309500.00 3219775.00
Pakistan 796095.00 196174380.00
Palau 459.00 21186.00
Panama 75420.00 3608431.00
Papua New Guinea 462840.00 6552730.00
Paracel Islands
Paraguay 406752.00 6703860.00
Peru 1285216.00 30147935.00
Philippines 300000.00 107668231.00
Pitcairn Islands 47.00 48.00
Poland 312685.00 38346279.00
Portugal 92090.00 10813834.00
Puerto Rico 13790.00 3620897.00
Qatar 11586.00 2123160.00
Romania 238391.00 21729871.00
Russia 17098242.00 142470272.00
Rwanda 26338.00 12337138.00
Saint Barthelemy 7267.00
Saint Helena, Ascension, and Tristan da Cunha 308.00 7776.00
Saint Kitts and Nevis 261.00 51538.00
Saint Lucia 616.00 163362.00
Saint Martin 54.00 31530.00
Saint Pierre and Miquelon 242.00 5716.00
Saint Vincent and the Grenadines 389.00 102918.00
Samoa 2831.00 196628.00
San Marino 61.00 32742.00
Sao Tome and Principe 964.00 190428.00
Saudi Arabia 2149690.00 27345986.00
Senegal 196722.00 13635927.00
Serbia 77474.00 7209764.00
Seychelles 455.00 91650.00
Sierra Leone 71740.00 5743725.00
Singapore 697.00 5567301.00
Sint Maarten 34.00 39689.00
Slovakia 49035.00 5443583.00
Slovenia 20273.00 1988292.00
Solomon Islands 28896.00 609883.00
Somalia 637657.00 10428043.00
South Africa 1219090.00 48375645.00
South Georgia and South Sandwich Islands 3903.00
South Sudan 644329.00 11562695.00
Spain 505370.00 47737941.00
Spratly Islands 5.00
Sri Lanka 65610.00 21866445.00
Sudan 1861484.00 35482233.00
Suriname 163820.00 573311.00
Svalbard 62045.00 1872.00
Swaziland 17364.00 1419623.00
Sweden 450295.00 9723809.00
Switzerland 41277.00 8061516.00
Syria 185180.00 17951639.00
Taiwan 35980.00 23359928.00
Tajikistan 143100.00 8051512.00
Tanzania 947300.00 49639138.00
Thailand 513120.00 67741401.00
Timor-Leste 14874.00 1201542.00
Togo 56785.00 7351374.00
Tokelau 12.00 1337.00
Tonga 747.00 106440.00
Trinidad and Tobago 5128.00 1223916.00
Tunisia 163610.00 10937521.00
Turkey 783562.00 81619392.00
Turkmenistan 488100.00 5171943.00
Turks and Caicos Islands 948.00 49070.00
Tuvalu 26.00 10782.00
Uganda 241038.00 35918915.00
Ukraine 603550.00 44291413.00
United Arab Emirates 83600.00 5628805.00
United Kingdom 243610.00 63742977.00
United States 9826675.00 318892103.00
United States Pacific Island Wildlife Refuges 22.00
Uruguay 176215.00 3332972.00
Uzbekistan 447400.00 28929716.00
Vanuatu 12189.00 266937.00
Venezuela 912050.00 28868486.00
Vietnam 331210.00 93421835.00
Virgin Islands 1910.00 104170.00
Wake Island 7.00
Wallis and Futuna 142.00 15561.00
West Bank 5860.00 2731052.00
Western Sahara 266000.00 554795.00
Yemen 527968.00 26052966.00
Zambia 752618.00 14638505.00
Zimbabwe 390757.00 13771721.00

From a different source, here are the locations of the various countries:

CountryCentroids
name iso_a3 long lat
Afghanistan AFG 66.17 33.78
Aland ALA 19.97 60.20
Albania ALB 20.26 41.14
Algeria DZA 2.83 28.14
American Samoa ASM -170.72 -14.30
Andorra AND 1.53 42.53
Angola AGO 17.74 -12.36
Anguilla AIA -63.01 18.22
Antarctica ATA -61.24 -72.62
Antigua and Barb. ATG -61.76 17.15
Argentina ARG -64.92 -35.39
Armenia ARM 45.22 40.31
Aruba ABW -69.96 12.49
Ashmore and Cartier Is. -99 123.59 -12.43
Australia AUS 134.61 -25.85
Austria AUT 14.32 47.60
Azerbaijan AZE 47.82 40.27
Bahamas BHS -76.23 24.26
Bahrain BHR 50.56 26.06
Bangladesh BGD 90.42 23.81
Barbados BRB -59.52 13.15
Belarus BLR 28.23 53.55
Belgium BEL 4.83 50.66
Belize BLZ -88.53 17.22
Benin BEN 2.55 9.62
Bermuda BMU -64.75 32.29
Bhutan BTN 90.58 27.40
Bolivia BOL -64.44 -16.73
Bosnia and Herz. BIH 17.99 44.18
Botswana BWA 23.96 -22.26
Br. Indian Ocean Ter. IOT 72.46 -7.31
Brazil BRA -52.87 -10.83
British Virgin Is. VGB -64.41 18.47
Brunei BRN 114.86 4.48
Bulgaria BGR 25.35 42.79
Burkina Faso BFA -1.54 12.27
Burundi BDI 30.07 -3.51
Côte d’Ivoire CIV -5.33 7.61
Cambodia KHM 105.04 12.69
Cameroon CMR 12.92 5.68
Canada CAN -98.38 61.06
Cape Verde CPV -23.78 16.23
Cayman Is. CYM -80.08 19.70
Central African Rep. CAF 20.60 6.50
Chad TCD 18.81 15.31
Chile CHL -70.89 -35.82
China CHN 103.94 36.59
Colombia COL -72.87 3.88
Comoros COM 43.70 -12.09
Congo COG 15.34 -0.88
Cook Is. COK -159.77 -21.24
Costa Rica CRI -83.95 10.01
Croatia HRV 16.65 45.11
Cuba CUB -78.79 21.70
Curaçao CUW -69.00 12.19
Cyprus CYP 33.29 35.03
Czech Rep. CZE 15.54 49.78
Dem. Rep. Congo COD 25.28 -5.40
Dem. Rep. Korea PRK 127.25 40.13
Denmark DNK 9.97 56.11
Djibouti DJI 42.78 11.70
Dominica DMA -61.35 15.46
Dominican Rep. DOM -70.32 18.95
Ecuador ECU -78.19 -1.46
Egypt EGY 30.03 26.41
El Salvador SLV -88.68 13.78
Eq. Guinea GNQ 10.48 1.64
Eritrea ERI 39.17 15.26
Estonia EST 25.99 58.63
Ethiopia ETH 39.81 8.56
Faeroe Is. FRO -6.77 62.05
Falkland Is. FLK -59.65 -51.71
Fiji FJI 177.77 -17.44
Finland FIN 26.45 64.43
Fr. Polynesia PYF -140.82 -16.67
Fr. S. Antarctic Lands ATF 69.35 -49.18
France FRA 2.72 46.53
Gabon GAB 11.99 -0.65
Gambia GMB -15.30 13.46
Georgia GEO 43.77 42.18
Germany DEU 10.56 51.08
Ghana GHA -1.01 7.94
Greece GRC 22.88 39.16
Greenland GRL -41.20 74.70
Grenada GRD -61.72 12.05
Guam GUM 144.76 13.42
Guatemala GTM -90.18 15.74
Guernsey GGY -2.54 49.49
Guinea GIN -10.66 10.40
Guinea-Bissau GNB -14.64 12.08
Guyana GUY -58.70 4.74
Haiti HTI -72.49 18.98
Heard I. and McDonald Is. HMD 73.47 -53.09
Honduras HND -86.46 14.84
Hong Kong HKG 114.14 22.36
Hungary HUN 19.61 47.21
Iceland ISL -18.30 64.92
India IND 79.81 22.93
Indian Ocean Ter. -99 96.90 -12.17
Indonesia IDN 116.28 -1.66
Iran IRN 54.49 32.52
Iraq IRQ 43.99 32.95
Ireland IRL -7.96 53.16
Isle of Man IMN -4.51 54.22
Israel ISR 35.21 31.41
Italy ITA 12.27 42.88
Jamaica JAM -77.15 18.19
Japan JPN 138.85 36.96
Jersey JEY -2.08 49.19
Jordan JOR 37.03 31.16
Kazakhstan KAZ 67.56 48.19
Kenya KEN 38.01 0.42
Kiribati KIR -155.86 -1.14
Korea KOR 127.94 36.45
Kosovo -99 20.76 42.64
Kuwait KWT 47.76 29.16
Kyrgyzstan KGZ 74.82 41.49
Lao PDR LAO 103.87 18.45
Latvia LVA 25.17 56.85
Lebanon LBN 35.98 33.79
Lesotho LSO 28.43 -29.69
Liberia LBR -9.06 6.43
Libya LBY 18.18 26.99
Liechtenstein LIE 9.55 47.13
Lithuania LTU 24.06 55.35
Luxembourg LUX 6.30 49.78
Macao MAC 113.49 22.22
Macedonia MKD 21.91 41.57
Madagascar MDG 46.92 -19.43
Malawi MWI 34.49 -13.53
Malaysia MYS 112.12 2.16
Maldives MDV 73.48 4.16
Mali MLI -3.31 17.33
Malta MLT 14.34 36.00
Marshall Is. MHL 169.73 7.07
Mauritania MRT -10.07 20.27
Mauritius MUS 57.52 -20.34
Mexico MEX -102.30 23.92
Micronesia FSM 151.86 7.36
Moldova MDA 28.66 47.20
Monaco MCO 7.41 43.75
Mongolia MNG 103.27 46.85
Montenegro MNE 19.20 42.76
Montserrat MSR -62.18 16.74
Morocco MAR -6.10 31.83
Mozambique MOZ 35.73 -17.29
Myanmar MMR 96.67 21.17
N. Cyprus -99 33.50 35.15
N. Mariana Is. MNP 145.69 15.24
Namibia NAM 17.41 -22.10
Nauru NRU 166.94 -0.52
Nepal NPL 84.17 28.30
Netherlands NLD 5.82 52.21
New Caledonia NCL 166.55 -21.16
New Zealand NZL 172.90 -41.81
Nicaragua NIC -84.90 12.86
Niger NER 9.58 17.42
Nigeria NGA 8.28 9.58
Niue NIU -169.85 -19.06
Norfolk Island NFK 167.94 -29.06
Norway NOR 15.77 67.47
Oman OMN 56.14 20.43
Pakistan PAK 69.56 29.92
Palau PLW 134.52 7.38
Palestine PSE 35.02 31.77
Panama PAN -79.91 8.57
Papua New Guinea PNG 144.83 -6.62
Paraguay PRY -58.17 -23.20
Peru PER -74.14 -9.18
Philippines PHL 122.99 11.07
Pitcairn Is. PCN -128.30 -24.37
Poland POL 19.64 52.14
Portugal PRT -7.76 39.69
Puerto Rico PRI -66.32 18.28
Qatar QAT 51.43 25.23
Romania ROU 23.47 45.52
Russia RUS 96.86 61.70
Rwanda RWA 30.07 -2.08
S. Geo. and S. Sandw. Is. SGS -36.51 -54.34
S. Sudan SSD 31.10 8.60
São Tomé and Principe STP 6.75 0.40
Saint Helena SHN -14.32 -7.97
Saint Lucia LCA -60.95 13.89
Samoa WSM -172.05 -13.81
San Marino SMR 12.49 43.93
Saudi Arabia SAU 44.68 23.93
Senegal SEN -14.23 14.45
Serbia SRB 20.99 44.05
Seychelles SYC 55.49 -4.69
Siachen Glacier -99 77.29 35.47
Sierra Leone SLE -11.51 8.54
Singapore SGP 103.91 1.37
Sint Maarten SXM -63.09 18.06
Slovakia SVK 19.75 48.79
Slovenia SVN 15.01 46.13
Solomon Is. SLB 159.54 -8.71
Somalia SOM 46.06 6.04
Somaliland -99 45.34 10.43
South Africa ZAF 25.22 -29.05
Spain ESP -3.39 40.40
Sri Lanka LKA 80.88 7.57
St-Barthélemy BLM -62.85 17.90
St-Martin MAF -63.02 18.09
St. Kitts and Nevis KNA -62.65 17.25
St. Pierre and Miquelon SPM -56.27 46.84
St. Vin. and Gren. VCT -61.25 12.99
Sudan SDN 30.22 13.77
Suriname SUR -55.63 4.10
Swaziland SWZ 31.70 -26.65
Sweden SWE 16.86 62.75
Switzerland CHE 8.35 46.84
Syria SYR 38.68 34.93
Taiwan TWN 121.00 23.64
Tajikistan TJK 71.28 38.51
Tanzania TZA 35.00 -6.40
Thailand THA 101.14 15.08
Timor-Leste TLS 125.39 -9.00
Togo TGO 1.16 8.55
Tonga TON -175.03 -21.13
Trinidad and Tobago TTO -61.29 10.42
Tunisia TUN 9.74 34.09
Turkey TUR 35.36 39.02
Turkmenistan TKM 59.70 39.13
Turks and Caicos Is. TCA -71.96 21.83
U.S. Virgin Is. VIR -64.77 18.33
Uganda UGA 32.52 1.22
Ukraine UKR 31.56 49.06
United Arab Emirates ARE 54.30 23.54
United Kingdom GBR -2.59 53.89
United States USA -98.84 39.47
Uruguay URY -55.79 -32.87
Uzbekistan UZB 63.49 41.77
Vanuatu VUT 167.99 -16.15
Vatican VAT 12.43 41.90
Venezuela VEN -65.91 7.08
Vietnam VNM 106.41 16.69
W. Sahara ESH -12.17 24.65
Wallis and Futuna Is. WLF -178.05 -14.24
Yemen YEM 47.82 15.79
Zambia ZMB 27.95 -13.50
Zimbabwe ZWE 30.06 -19.06

A join will bring together these two sources. Before this can be done, the overlap variables must be explicitly stated. As things stand, there are no variables in common between CountryCentroids and PopArea.

names( CountryCentroids )
[1] "name"   "iso_a3" "long"   "lat"   
names( PopArea)
[1] "country" "area"    "pop"    

It’s name and country that connects the two tables. To make this explicit, let’s change the variable name from “name” to “country”:

CountryCentroids <-
  CountryCentroids %>% 
  mutate( country=name ) %>%
  select( -name )
Together <- merge( PopArea, CountryCentroids )
Together
country area pop iso_a3 long lat
Afghanistan 652230.00 31822848.00 AFG 66.17 33.78
Albania 28748.00 3020209.00 ALB 20.26 41.14
Algeria 2381741.00 38813722.00 DZA 2.83 28.14
American Samoa 199.00 54517.00 ASM -170.72 -14.30
Andorra 468.00 85458.00 AND 1.53 42.53
Angola 1246700.00 19088106.00 AGO 17.74 -12.36
Anguilla 91.00 16086.00 AIA -63.01 18.22
Antarctica ATA -61.24 -72.62
Argentina 2780400.00 43024374.00 ARG -64.92 -35.39
Armenia 29743.00 3060631.00 ARM 45.22 40.31
Aruba 180.00 110663.00 ABW -69.96 12.49
Australia 7741220.00 22507617.00 AUS 134.61 -25.85
Austria 83871.00 8223062.00 AUT 14.32 47.60
Azerbaijan 86600.00 9686210.00 AZE 47.82 40.27
Bahrain 760.00 1314089.00 BHR 50.56 26.06
Bangladesh 143998.00 166280712.00 BGD 90.42 23.81
Barbados 430.00 289680.00 BRB -59.52 13.15
Belarus 207600.00 9608058.00 BLR 28.23 53.55
Belgium 30528.00 10449361.00 BEL 4.83 50.66
Belize 22966.00 340844.00 BLZ -88.53 17.22
Benin 112622.00 10160556.00 BEN 2.55 9.62
Bermuda 54.00 69839.00 BMU -64.75 32.29
Bhutan 38394.00 733643.00 BTN 90.58 27.40
Bolivia 1098581.00 10631486.00 BOL -64.44 -16.73
Botswana 581730.00 2155784.00 BWA 23.96 -22.26
Brazil 8514877.00 202656788.00 BRA -52.87 -10.83
Brunei 5765.00 422675.00 BRN 114.86 4.48
Bulgaria 110879.00 6924716.00 BGR 25.35 42.79
Burkina Faso 274200.00 18365123.00 BFA -1.54 12.27
Burundi 27830.00 10395931.00 BDI 30.07 -3.51
Cambodia 181035.00 15458332.00 KHM 105.04 12.69
Cameroon 475440.00 23130708.00 CMR 12.92 5.68
Canada 9984670.00 34834841.00 CAN -98.38 61.06
Chad 1284000.00 11412107.00 TCD 18.81 15.31
Chile 756102.00 17363894.00 CHL -70.89 -35.82
China 9596960.00 1355692576.00 CHN 103.94 36.59
Colombia 1138910.00 46245297.00 COL -72.87 3.88
Comoros 2235.00 766865.00 COM 43.70 -12.09
Costa Rica 51100.00 4755234.00 CRI -83.95 10.01
Croatia 56594.00 4470534.00 HRV 16.65 45.11
Cuba 110860.00 11047251.00 CUB -78.79 21.70
Cyprus 9251.00 1172458.00 CYP 33.29 35.03
Denmark 43094.00 5569077.00 DNK 9.97 56.11
Djibouti 23200.00 810179.00 DJI 42.78 11.70
Dominica 751.00 73449.00 DMA -61.35 15.46
Ecuador 283561.00 15654411.00 ECU -78.19 -1.46
Egypt 1001450.00 86895099.00 EGY 30.03 26.41
El Salvador 21041.00 6125512.00 SLV -88.68 13.78
Eritrea 117600.00 6380803.00 ERI 39.17 15.26
Estonia 45228.00 1257921.00 EST 25.99 58.63
Ethiopia 1104300.00 96633458.00 ETH 39.81 8.56
Fiji 18274.00 903207.00 FJI 177.77 -17.44
Finland 338145.00 5268799.00 FIN 26.45 64.43
France 643801.00 66259012.00 FRA 2.72 46.53
Gabon 267667.00 1672597.00 GAB 11.99 -0.65
Georgia 69700.00 4935880.00 GEO 43.77 42.18
Germany 357022.00 80996685.00 DEU 10.56 51.08
Ghana 238533.00 25758108.00 GHA -1.01 7.94
Greece 131957.00 10775557.00 GRC 22.88 39.16
Greenland 2166086.00 57728.00 GRL -41.20 74.70
Grenada 344.00 110152.00 GRD -61.72 12.05
Guam 544.00 161001.00 GUM 144.76 13.42
Guatemala 108889.00 14647083.00 GTM -90.18 15.74
Guernsey 78.00 65849.00 GGY -2.54 49.49
Guinea 245857.00 11474383.00 GIN -10.66 10.40
Guinea-Bissau 36125.00 1693398.00 GNB -14.64 12.08
Guyana 214969.00 735554.00 GUY -58.70 4.74
Haiti 27750.00 9996731.00 HTI -72.49 18.98
Honduras 112090.00 8598561.00 HND -86.46 14.84
Hong Kong 1104.00 7112688.00 HKG 114.14 22.36
Hungary 93028.00 9919128.00 HUN 19.61 47.21
Iceland 103000.00 317351.00 ISL -18.30 64.92
India 3287263.00 1236344631.00 IND 79.81 22.93
Indonesia 1904569.00 253609643.00 IDN 116.28 -1.66
Iran 1648195.00 80840713.00 IRN 54.49 32.52
Iraq 438317.00 32585692.00 IRQ 43.99 32.95
Ireland 70273.00 4832765.00 IRL -7.96 53.16
Isle of Man 572.00 86866.00 IMN -4.51 54.22
Israel 20770.00 7821850.00 ISR 35.21 31.41
Italy 301340.00 61680122.00 ITA 12.27 42.88
Jamaica 10991.00 2930050.00 JAM -77.15 18.19
Japan 377915.00 127103388.00 JPN 138.85 36.96
Jersey 116.00 96513.00 JEY -2.08 49.19
Jordan 89342.00 7930491.00 JOR 37.03 31.16
Kazakhstan 2724900.00 17948816.00 KAZ 67.56 48.19
Kenya 580367.00 45010056.00 KEN 38.01 0.42
Kiribati 811.00 104488.00 KIR -155.86 -1.14
Kosovo 10887.00 1859203.00 -99 20.76 42.64
Kuwait 17818.00 2742711.00 KWT 47.76 29.16
Kyrgyzstan 199951.00 5604212.00 KGZ 74.82 41.49
Latvia 64589.00 2165165.00 LVA 25.17 56.85
Lebanon 10400.00 5882562.00 LBN 35.98 33.79
Lesotho 30355.00 1942008.00 LSO 28.43 -29.69
Liberia 111369.00 4092310.00 LBR -9.06 6.43
Libya 1759540.00 6244174.00 LBY 18.18 26.99
Liechtenstein 160.00 37313.00 LIE 9.55 47.13
Lithuania 65300.00 3505738.00 LTU 24.06 55.35
Luxembourg 2586.00 520672.00 LUX 6.30 49.78
Macedonia 25713.00 2091719.00 MKD 21.91 41.57
Madagascar 587041.00 23201926.00 MDG 46.92 -19.43
Malawi 118484.00 17377468.00 MWI 34.49 -13.53
Malaysia 329847.00 30073353.00 MYS 112.12 2.16
Maldives 298.00 393595.00 MDV 73.48 4.16
Mali 1240192.00 16455903.00 MLI -3.31 17.33
Malta 316.00 412655.00 MLT 14.34 36.00
Mauritania 1030700.00 3516806.00 MRT -10.07 20.27
Mauritius 2040.00 1331155.00 MUS 57.52 -20.34
Mexico 1964375.00 120286655.00 MEX -102.30 23.92
Moldova 33851.00 3583288.00 MDA 28.66 47.20
Monaco 2.00 30508.00 MCO 7.41 43.75
Mongolia 1564116.00 2953190.00 MNG 103.27 46.85
Montenegro 13812.00 650036.00 MNE 19.20 42.76
Montserrat 102.00 5215.00 MSR -62.18 16.74
Morocco 446550.00 32987206.00 MAR -6.10 31.83
Mozambique 799380.00 24692144.00 MOZ 35.73 -17.29
Namibia 824292.00 2198406.00 NAM 17.41 -22.10
Nauru 21.00 9488.00 NRU 166.94 -0.52
Nepal 147181.00 30986975.00 NPL 84.17 28.30
Netherlands 41543.00 16877351.00 NLD 5.82 52.21
New Caledonia 18575.00 267840.00 NCL 166.55 -21.16
New Zealand 267710.00 4401916.00 NZL 172.90 -41.81
Nicaragua 130370.00 5848641.00 NIC -84.90 12.86
Niger 1267000.00 17466172.00 NER 9.58 17.42
Nigeria 923768.00 177155754.00 NGA 8.28 9.58
Niue 260.00 1190.00 NIU -169.85 -19.06
Norfolk Island 36.00 2210.00 NFK 167.94 -29.06
Norway 323802.00 5147792.00 NOR 15.77 67.47
Oman 309500.00 3219775.00 OMN 56.14 20.43
Pakistan 796095.00 196174380.00 PAK 69.56 29.92
Palau 459.00 21186.00 PLW 134.52 7.38
Panama 75420.00 3608431.00 PAN -79.91 8.57
Papua New Guinea 462840.00 6552730.00 PNG 144.83 -6.62
Paraguay 406752.00 6703860.00 PRY -58.17 -23.20
Peru 1285216.00 30147935.00 PER -74.14 -9.18
Philippines 300000.00 107668231.00 PHL 122.99 11.07
Poland 312685.00 38346279.00 POL 19.64 52.14
Portugal 92090.00 10813834.00 PRT -7.76 39.69
Puerto Rico 13790.00 3620897.00 PRI -66.32 18.28
Qatar 11586.00 2123160.00 QAT 51.43 25.23
Romania 238391.00 21729871.00 ROU 23.47 45.52
Russia 17098242.00 142470272.00 RUS 96.86 61.70
Rwanda 26338.00 12337138.00 RWA 30.07 -2.08
Saint Lucia 616.00 163362.00 LCA -60.95 13.89
Samoa 2831.00 196628.00 WSM -172.05 -13.81
San Marino 61.00 32742.00 SMR 12.49 43.93
Saudi Arabia 2149690.00 27345986.00 SAU 44.68 23.93
Senegal 196722.00 13635927.00 SEN -14.23 14.45
Serbia 77474.00 7209764.00 SRB 20.99 44.05
Seychelles 455.00 91650.00 SYC 55.49 -4.69
Sierra Leone 71740.00 5743725.00 SLE -11.51 8.54
Singapore 697.00 5567301.00 SGP 103.91 1.37
Sint Maarten 34.00 39689.00 SXM -63.09 18.06
Slovakia 49035.00 5443583.00 SVK 19.75 48.79
Slovenia 20273.00 1988292.00 SVN 15.01 46.13
Somalia 637657.00 10428043.00 SOM 46.06 6.04
South Africa 1219090.00 48375645.00 ZAF 25.22 -29.05
Spain 505370.00 47737941.00 ESP -3.39 40.40
Sri Lanka 65610.00 21866445.00 LKA 80.88 7.57
Sudan 1861484.00 35482233.00 SDN 30.22 13.77
Suriname 163820.00 573311.00 SUR -55.63 4.10
Swaziland 17364.00 1419623.00 SWZ 31.70 -26.65
Sweden 450295.00 9723809.00 SWE 16.86 62.75
Switzerland 41277.00 8061516.00 CHE 8.35 46.84
Syria 185180.00 17951639.00 SYR 38.68 34.93
Taiwan 35980.00 23359928.00 TWN 121.00 23.64
Tajikistan 143100.00 8051512.00 TJK 71.28 38.51
Tanzania 947300.00 49639138.00 TZA 35.00 -6.40
Thailand 513120.00 67741401.00 THA 101.14 15.08
Timor-Leste 14874.00 1201542.00 TLS 125.39 -9.00
Togo 56785.00 7351374.00 TGO 1.16 8.55
Tonga 747.00 106440.00 TON -175.03 -21.13
Trinidad and Tobago 5128.00 1223916.00 TTO -61.29 10.42
Tunisia 163610.00 10937521.00 TUN 9.74 34.09
Turkey 783562.00 81619392.00 TUR 35.36 39.02
Turkmenistan 488100.00 5171943.00 TKM 59.70 39.13
Uganda 241038.00 35918915.00 UGA 32.52 1.22
Ukraine 603550.00 44291413.00 UKR 31.56 49.06
United Arab Emirates 83600.00 5628805.00 ARE 54.30 23.54
United Kingdom 243610.00 63742977.00 GBR -2.59 53.89
United States 9826675.00 318892103.00 USA -98.84 39.47
Uruguay 176215.00 3332972.00 URY -55.79 -32.87
Uzbekistan 447400.00 28929716.00 UZB 63.49 41.77
Vanuatu 12189.00 266937.00 VUT 167.99 -16.15
Venezuela 912050.00 28868486.00 VEN -65.91 7.08
Vietnam 331210.00 93421835.00 VNM 106.41 16.69
Yemen 527968.00 26052966.00 YEM 47.82 15.79
Zambia 752618.00 14638505.00 ZMB 27.95 -13.50
Zimbabwe 390757.00 13771721.00 ZWE 30.06 -19.06

Filter

You can use the filter() function to extract those cases that meet a given criterion. Sometimes you may have a list of the cases you want. merge() lets you choose the cases you want in that list.

For example, here is a list of the G8 countries.

G8 <- 
  CountryGroups %>%
  filter( G8 ) %>%
  select( country )
G8
country
Canada
France
Germany
Italy
Japan
Russia
United Kingdom
United States

The G8 table can be used to extract the G8 countries from another table:

G8Data <- merge( Together, G8 )
G8Data
country area pop iso_a3 long lat
Canada 9984670.00 34834841.00 CAN -98.38 61.06
France 643801.00 66259012.00 FRA 2.72 46.53
Germany 357022.00 80996685.00 DEU 10.56 51.08
Italy 301340.00 61680122.00 ITA 12.27 42.88
Japan 377915.00 127103388.00 JPN 138.85 36.96
Russia 17098242.00 142470272.00 RUS 96.86 61.70
United Kingdom 243610.00 63742977.00 GBR -2.59 53.89
United States 9826675.00 318892103.00 USA -98.84 39.47

Please use the comment system to make suggestions, point out errors, or to discuss the topic.

comments powered by Disqus


  1. There are the 36 candidates as well as no choice (“undervote”) or disallowed multiple choices (“overvote”).