Chapter 11 Joining two data frames

11.1 Variables for Matching

What does it mean for a case in the Left table to match a case in the Right table? A match is defined in terms of one or more variables that appear in both the Left and the Right tables. For each case in the Left table has values for the matching variable(s) that are identical to the values for the corresponding matching variable(s) in the Right table, a match between the Left case and the Right case is established.

Table 11.3 displays data on human migration between countries from dcData::MigrationFlows. Each case in MigrationFlows registers migration from an origin country to a destination country.

Table 11.3: The first several cases in the MigrationFlows data frame. (Not all variables are shown.) Each case is about migration from one country to another: the “origin” and “destination” countries. Country names are given using the three-letter ISO-A3 code. For instance, the migration to France (FRA) from Algeria (DZA) is conspicuously large.

Suppose your interest in these data is to understand the forces that shape migration. Variables from dcData::CountryData, could be useful here. Table 11.4 selects a few health measures for each country: life expectancy, infant mortality, and fraction of the population considered underweight.

Table 11.4: CountryData: Health-related data from the CIA World Factbook data. Note that the country is identified by its common name.

Consolidating the health-related data to the migration data is done with a join. For instance, suppose you want to bring side-by-side the health data for the “origin” country and the migration data. To do this, you’ll have to match the origincode variable in MigrationFlows to the country variable in CountryData. It’s easy enough to do this for those countries whose code you can figure out, e.g. FRA for France, AFG for Afghanistan, etc. Common sense tells you to look up those codes you don’t already know, e.g. AUT for Austria and AUS for Australia.

Data-wrangling software will construct a match only when the information is in exactly the same form. So, while AUT is equivalent to Austria, you need to demonstrate this equivalence. In other words, you’ll need to find or construct a data frame that contains the translation from country code to country name. This sometimes requires creativity; it certainly requires a knowledge of what data are available. For instance, the dcData::CountryCentroids data frame (Table 11.5) lists both the country and the country code:

CountryCentroids %>% 
  select(name, iso_a3)

Table 11.5: Two variables selected from CountryCentroids.

To translate the country variable in CountryData to the iso_a3 code, join CountryCentroids to CountryData, producing 11.6.

InfantMortality <- 
  CountryCentroids %>%
  select(name, iso_a3) %>% 
  left_join(CountryData %>% select(country, infant),
            by = c("name" = "country")) 

Table 11.6: InfantMortality table produced by joining CountryCentroids to CountryData.

Note that the by argument to left_join() specifies that the case matching is to be accomplished by comparing the name variable in the Left table to the country variable in the Right table. Now you are in a position to join the infant mortality data to the migration data. The variables for matching will be iso_a3 in InfantMortality and destcode in MigrationFlows:

MigrationFlows %>%
  left_join(InfantMortality, by = c("destcode" = "iso_a3")) 

Often, the tables you are joining have the same name for corresponding variables. A natural join [Click to see note.]Natural Join: The selection of variables that match the cases in one table to another based simply on variables with the same name that appear in both tables. defines the matching cases by all the same-named pairs of variables. In other situations, the corresponding variables may have different names, or you may not want to join based on all the same-named pairs. In these situations, you can explicity identify the corresponding variables with the by argument.

Or, you could bring in infant mortality for the origin country:

MigrationFlows %>%
  left_join(InfantMortality, by = c("origincode" = "iso_a3")) 

11.2 Different Types of Join

You’ve seen the role of matching variables in a join operation. When there is a match between a case in the Left table and a case in the Right table, the remaining variables from the Right table are concatenated onto the case from the Left table.

Some questions remain:

• What happens when a case in the Right table has no matches in the Left?
• What happens when a case in the Left table has no matches in the Right?
• What happens when there are multiple cases in the Left that match a case in the Right?

There are different answers that are appropriate in different situations. For that reason, there are a variety of join operators that work in different ways. A few of the more commonly encountered join operators include:

• left_join() — the output has all the cases from the Left, even if there is no match in the right.
• inner_join() — the output has only the cases from the Left with a match in the Right.
• full_join() — the output will have all the cases from both the Left and the Right.

All of these join operators behave the same when there are multiple matches in the Right table for a case in the Left table: Each of these multiple matches will produce a case in the output.

11.3 Common Uses for Joins

Mutating

Mutating means to add new variables to a data frame. The mutate() data verb lets you add new variables constructed by transformation operations on existing variables. The join verbs can do something similar: constructing new variables by pulling them out of a different data frame.

Translating

One common sort of mutation deserves special attention. Often, you will need to translate levels in one variable into a different set of levels. You’ve already seen this in the MigrationFlows and CountryData task; Migration data had countries listed in ISO-code form, whereas CountryData has countries listed by name. The translation from one form to another happened to be described by the CountriesCentroid data.

Filtering

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. inner_join() lets you choose the cases you want in that list. For example, Table 11.7 lists the G8 countries. To extract the G8 cases from another table, use an inner join between that table and G8 country table as illustrated in Table 11.8.

G8Countries <- 
  CountryGroups %>%
  filter( G8 ) %>%
  select( country )
G8Countries

Table 11.7: The G8 countries.

G8CountryData <-
  CountryData %>%
  inner_join(G8Countries)
G8CountryData

Table 11.8: An inner join of CountryData with G8 extracts just the G8 cases.

11.4 Exercises

Problem 11.1: Most data verbs, when used with the chaining syntax %>%, have arguments that consist only of reduction and transformation functions, constants, and variables. For instance:

BabyNames %>%
  group_by(year) %>%
  summarise(total = sum(count))

In contrast, the join family of data verbs — inner_join(), left_join(), etc. — always have a data table as one of the arguments inside the parentheses. Explain why.

Problem 11.2: Consider these two tables containing demographic and geographic information about countries.

Table 11.9: Demographics

Table 11.10: CountryCentroids

Explain why the information in the two tables cannot be successfully combined by laying the two tables side by side into a single table, that is, by simply copying the long and lat variables from one table and pasting them alongside the country, pop and area variables in the other table.

Problem 11.3: Use the ZipGeography and ZipDemography data tables (from dcData) to create these graphics.

1. Make a bar chart showing, for each state, the count of the population that is under 5 years old, between 5 and 18 years old, between 18 and 65 years old, and over 65.
   
2. Make another bar chart showing the proportion of the state population that is in each of the age categories.
   
3. Call a ZIP code “crowded” when the population is over 50,000. Make a scatter plot showing the geographic location of the crowded ZIPs.
   
4. Make a choropleth map of the US showing, for each state, one of the following (your choice). Hint: you’ll have to sum over all the ZIP codes in a state before you can meaningfully find the fraction.
   1. the fraction of housing units (Totalhousingunits) that are vacant (Vacanthousingunits).
      
   2. the fraction of the population that is over 65 years old.
      
   3. the fraction of the population with a bachelor’s degree (Bachelorsdegreeorhigher).
      
5. Pick out the 10,000 zip codes with the highest population. Make a scatter plot of the latitude versus longitude. (Hints: arrange() and head().) Use color to represent the fraction of the population that is over 65.
   
6. How many zip codes have a WaterArea that is more than 50% of the LandArea? Make a scatter plot showing the geographical location of these, with color indicating the population.

Problem 11.4: Make a scatterplot map of the locations of the Medicare providers. (Hint: the ZIP code provides this information. You can translate it into latitude and longitude with ZipGeography.) Overlay this on a choropleth map giving the population of each state.

Problem 11.5: Using the ZipGeography data table (from dcData), answer the following questions. In addition to the answer itself, show the statement that you used and the data table created by your statement that contains the answer.

1. How many different counties are there?
2. Which city names are used in the most states?
3. Identify cities that include more than 5% of their state population; which of those city names are used in the most states?
4. Does any state have more than one time zone?
5. Does any city have more than one time zone?
6. Does any county have more than one time zone?

Problem 11.6: Migration and Mortality

One possible driver of migration is that it tends to be from countries with high infant mortality to those with low mortality.

Here are two data tables that might be used answer a research question: does migration tend to be from high infant-mortality countries to low infant-mortality countries? To answer this question, a glyph-ready data table like this would be useful.

This glyph-ready table could be constructed from the MigrationFlows data table and the HealthIndicators data table.

Table 11.11: MigrationFlows

Table 11.12: HealthIndicators

Your task is to figure out the data wrangling steps to get from the two input tables to the glyph-ready table.

To start, fill in this blank table by hand with the right entries from the input tables. Assume that countryB in the glyph-ready data corresponds to origincode in MigrationFlows. This will help you be better able to appreciate the individual data wrangling steps.

Now, answer these questions.

1. How will you create a data table from MigrationFlows that renames origincode, destcode, and Y2000 to countryB, countryA and toAfromB?
2. While toAfromB comes directly from MigrationFlows, the reverse flow, toBfromA, needs to be brought in via a join. What pairs of variables are being matched for the join?
   
3. Both infantA and infantB come from HealthIndicators. What variables are being matched for infantA?
4. What variables are being matched for InfantB?