10 Relational data

It’s rare that a data analysis involves only a single table of data. Typically you have many tables of data, and you must combine them to answer the questions that you’re interested in. Collectively, multiple tables of data are called relational data because it is the relations, not just the individual datasets, that are particularly important.

Relations are always defined between a pair of tables. All other relations are built up from this simple idea: the relations of three or more tables are always a property of the relations between each pair; sometimes both elements of a pair can be the same table.

To work with relational data you need verbs that work with pairs of tables. There are three families of verbs designed to work with relational data:

  • Mutating joins, which add new variables to one data frame from matching rows in another.

  • Filtering joins, which filter observations from one data frame based on whether or not they match an observation in the other table.

  • Set operations, which treat observations like they were set elements.

The most common place to find relational data is in a relational database management system, a term that encompasses almost all modern databases. If you’ve used a database before, you’ve almost certainly used SQL. If so, you should find the concepts in this chapter familiar, although their expression in dplyr is a little different. Generally, dplyr is a little easier to use than SQL because dplyr is specialised to data analysis: it makes common data analysis operations easier, at the expense of making it difficult to do other things.

10.1 nycflights13

You can use the nycflights13 package to learn about relational data. nycflights13 contains four data frames that are related to the flights table that you used in Data Transformation:

  • airlines lets you look up the full carrier name from its abbreviated code:

    airlines
    #> Source: local data frame [16 x 2]
    #> 
    #>    carrier                     name
    #>      (chr)                    (chr)
    #> 1       9E        Endeavor Air Inc.
    #> 2       AA   American Airlines Inc.
    #> 3       AS     Alaska Airlines Inc.
    #> 4       B6          JetBlue Airways
    #> 5       DL     Delta Air Lines Inc.
    #> 6       EV ExpressJet Airlines Inc.
    #> ..     ...                      ...
  • airports gives information about each airport, identified by the faa airport code:

    airports
    #> Source: local data frame [1,396 x 7]
    #> 
    #>      faa                           name   lat   lon   alt    tz   dst
    #>    (chr)                          (chr) (dbl) (dbl) (int) (dbl) (chr)
    #> 1    04G              Lansdowne Airport  41.1 -80.6  1044    -5     A
    #> 2    06A  Moton Field Municipal Airport  32.5 -85.7   264    -5     A
    #> 3    06C            Schaumburg Regional  42.0 -88.1   801    -6     A
    #> 4    06N                Randall Airport  41.4 -74.4   523    -5     A
    #> 5    09J          Jekyll Island Airport  31.1 -81.4    11    -4     A
    #> 6    0A9 Elizabethton Municipal Airport  36.4 -82.2  1593    -4     A
    #> ..   ...                            ...   ...   ...   ...   ...   ...
  • planes gives information about each plane, identified by its tailnum:

    planes
    #> Source: local data frame [3,322 x 9]
    #> 
    #>    tailnum  year                    type     manufacturer     model
    #>      (chr) (int)                   (chr)            (chr)     (chr)
    #> 1   N10156  2004 Fixed wing multi engine          EMBRAER EMB-145XR
    #> 2   N102UW  1998 Fixed wing multi engine AIRBUS INDUSTRIE  A320-214
    #> 3   N103US  1999 Fixed wing multi engine AIRBUS INDUSTRIE  A320-214
    #> 4   N104UW  1999 Fixed wing multi engine AIRBUS INDUSTRIE  A320-214
    #> 5   N10575  2002 Fixed wing multi engine          EMBRAER EMB-145LR
    #> 6   N105UW  1999 Fixed wing multi engine AIRBUS INDUSTRIE  A320-214
    #> ..     ...   ...                     ...              ...       ...
    #>    engines
    #>      (int)
    #> 1        2
    #> 2        2
    #> 3        2
    #> 4        2
    #> 5        2
    #> 6        2
    #> ..     ...
    #> Variables not shown: seats (int), speed (int), engine (chr)
  • weather gives the weather at each NYC airport for each hour:

    weather
    #> Source: local data frame [26,130 x 15]
    #> 
    #>    origin  year month   day  hour  temp  dewp humid wind_dir wind_speed
    #>     (chr) (dbl) (dbl) (int) (int) (dbl) (dbl) (dbl)    (dbl)      (dbl)
    #> 1     EWR  2013     1     1     0  37.0  21.9  54.0      230       10.4
    #> 2     EWR  2013     1     1     1  37.0  21.9  54.0      230       13.8
    #> 3     EWR  2013     1     1     2  37.9  21.9  52.1      230       12.7
    #> 4     EWR  2013     1     1     3  37.9  23.0  54.5      230       13.8
    #> 5     EWR  2013     1     1     4  37.9  24.1  57.0      240       15.0
    #> 6     EWR  2013     1     1     6  39.0  26.1  59.4      270       10.4
    #> ..    ...   ...   ...   ...   ...   ...   ...   ...      ...        ...
    #> Variables not shown: wind_gust (dbl), precip (dbl), pressure (dbl), visib
    #>   (dbl), date (time)

One way to show the relationships between the different tables is with a drawing:

This diagram is a little overwhelming, and even so it’s simple compared to some you’ll see in the wild! The key to understanding diagrams like this is to remember each relation always concerns a pair of tables. You don’t need to understand the whole thing; you just need to understand the chain of relations between the tables that you are interested in.

For nycflights13:

  • flights connects to planes via a single variable, tailnum. flights connects to airlines with the carrier variable.

  • flights connects to airports in two ways: via the origin or the dest variables.

  • flights connects to weather via origin (the location), and year, month, day and hour (the time).

10.1.1 Exercises

  1. Imagine you want to draw (approximately) the route each plane flies from its origin to its destination. What variables would you need? What tables would you need to combine?

  2. I forgot to draw the a relationship between weather and airports. What is the relationship and what should it look like in the diagram?

  3. weather only contains information for the origin (NYC) airports. If it contained weather records for all airports in the USA, what additional relation would it define with flights?

  4. You might expect that there’s an implicit relationship between plane and airline, because each plane is flown by a single airline. Confirm or reject this hypothesis using data.

  5. We know that some days of the year are “special”, and fewer people than usual fly on them. How might you represent that data as a data frame? What would be the primary keys of that table? How would it connect to the existing tables?

10.2 Keys

The variables used to connect each pair of tables are called keys. A key is a variable (or set of variables) that uniquely identifies an observation. In simple cases, a single variable is sufficient to identify an observation. For example, each plane is uniquely identified by its tailnum. In other cases, multiple variables may be needed. For example, to identify an observation in weather you need five variables: year, month, day, hour, and origin.

There are two types of keys:

  • A primary key uniquely identifies an observation in its own table. For example, planes$tailnum is a primary key because it uniquely identifies each plane in the planes table.

  • A foreign key uniquely identifies an observation in another table. For example, the flights$tailnum is a foreign key because it appears in the flights table where it matches each flight to a unique plane.

A variable can be both part of primary key and a foreign key. For example, origin is part of the weather primary key, and is also a foreign key for the airport table.

Once you’ve identified the primary keys in your tables, it’s good practice to verify that they do indeed uniquely identify each observation. One way to do that is to count() the primary keys and look for entries where n is greater than one:

planes %>% count(tailnum) %>% filter(n > 1)
#> Source: local data frame [0 x 2]
#> 
#> Variables not shown: tailnum (chr), n (int)
weather %>% count(year, month, day, hour, origin) %>% filter(n > 1)
#> Source: local data frame [0 x 6]
#> Groups: year, month, day, hour [0]
#> 
#> Variables not shown: year (dbl), month (dbl), day (int), hour (int),
#>   origin (chr), n (int)

Sometimes a table doesn’t have an explicit primary key: each row is an observation, but no combination of variables reliably identifies it. For example, what’s the primary key in the flights table? You might think it would be the date plus the flight or tail number, but neither of those are unique:

flights %>% count(year, month, day, flight) %>% filter(n > 1)
#> Source: local data frame [29,768 x 5]
#> Groups: year, month, day [365]
#> 
#>     year month   day flight     n
#>    (int) (int) (int)  (int) (int)
#> 1   2013     1     1      1     2
#> 2   2013     1     1      3     2
#> 3   2013     1     1      4     2
#> 4   2013     1     1     11     3
#> 5   2013     1     1     15     2
#> 6   2013     1     1     21     2
#> ..   ...   ...   ...    ...   ...
flights %>% count(year, month, day, tailnum) %>% filter(n > 1)
#> Source: local data frame [64,928 x 5]
#> Groups: year, month, day [365]
#> 
#>     year month   day tailnum     n
#>    (int) (int) (int)   (chr) (int)
#> 1   2013     1     1  N0EGMQ     2
#> 2   2013     1     1  N11189     2
#> 3   2013     1     1  N11536     2
#> 4   2013     1     1  N11544     3
#> 5   2013     1     1  N11551     2
#> 6   2013     1     1  N12540     2
#> ..   ...   ...   ...     ...   ...

When starting to work with this data, I had naively assumed that each flight number would be only used once per day: that would make it much easier to communicate problems with a specific flight. Unfortunately that is not the case! If a table lacks a primary key, it’s sometimes useful to add one with row_number(). That makes it easier to match observations if you’ve done some filtering and want to check back in with the original data. This is called a surrogate key.

A primary key and the corresponding foreign key in another table form a relation. Relations are typically one-to-many. For example, each flight has one plane, but each plane has many flights. In other data, you’ll occasionally see a 1-to-1 relationship. You can think of this as a special case of 1-to-many. It’s possible to model many-to-many relations with a many-to-1 relation plus a 1-to-many relation. For example, in this data there’s a many-to-many relationship between airlines and airports: each airport flies to many airlines; each airport hosts many airlines.

10.2.1 Exercises

  1. Identify the keys in the following datasets

    1. Lahman::Batting,
    2. babynames::babynames
    3. nasaweather::atmos
    4. fueleconomy::vehicles
  2. Draw a diagram illustrating the connections between the Batting, Master, and Salary tables in the Lahman package. Draw another diagram that shows the relationship between Master, Managers, AwardsManagers.

    How would you characterise the relationship between the Batting, Pitching, and Fielding tables?

10.3 Mutating joins

The first tool we’ll look at for combining a pair of tables is the mutating join. A mutating join allows you to combine variables from two tables. It first matches observations by their keys, then copies across variables from one table to the other.

Like mutate(), the join functions add variables to the right, so if you have a lot of variables already, the new variables won’t get printed out. For these examples, we’ll make it easier to see what’s going on in the examples by creating a narrower dataset:

(flights2 <- flights %>% select(year:day, hour, origin, dest, tailnum, carrier))
#> Source: local data frame [336,776 x 8]
#> 
#>     year month   day  hour origin  dest tailnum carrier
#>    (int) (int) (int) (dbl)  (chr) (chr)   (chr)   (chr)
#> 1   2013     1     1     5    EWR   IAH  N14228      UA
#> 2   2013     1     1     5    LGA   IAH  N24211      UA
#> 3   2013     1     1     5    JFK   MIA  N619AA      AA
#> 4   2013     1     1     5    JFK   BQN  N804JB      B6
#> 5   2013     1     1     5    LGA   ATL  N668DN      DL
#> 6   2013     1     1     5    EWR   ORD  N39463      UA
#> ..   ...   ...   ...   ...    ...   ...     ...     ...

(When you’re in RStudio, you can use View() to avoid this problem).

For example, imagine you want to add the full airline name to the flights data. You can combine the airlines and carrier data frames with left_join():

flights2 %>%
  left_join(airlines, by = "carrier")
#> Source: local data frame [336,776 x 9]
#> 
#>     year month   day  hour origin  dest tailnum carrier
#>    (int) (int) (int) (dbl)  (chr) (chr)   (chr)   (chr)
#> 1   2013     1     1     5    EWR   IAH  N14228      UA
#> 2   2013     1     1     5    LGA   IAH  N24211      UA
#> 3   2013     1     1     5    JFK   MIA  N619AA      AA
#> 4   2013     1     1     5    JFK   BQN  N804JB      B6
#> 5   2013     1     1     5    LGA   ATL  N668DN      DL
#> 6   2013     1     1     5    EWR   ORD  N39463      UA
#> ..   ...   ...   ...   ...    ...   ...     ...     ...
#> Variables not shown: name (chr)

The result of joining airlines to flights is an additional variable: carrier. This is why I call this type of join a mutating join. In this case, you could have got to the same place using mutate() and basic subsetting:

flights2 %>%
  mutate(carrier = airlines$name[match(carrier, airlines$carrier)])
#> Source: local data frame [336,776 x 8]
#> 
#>     year month   day  hour origin  dest tailnum                carrier
#>    (int) (int) (int) (dbl)  (chr) (chr)   (chr)                  (chr)
#> 1   2013     1     1     5    EWR   IAH  N14228  United Air Lines Inc.
#> 2   2013     1     1     5    LGA   IAH  N24211  United Air Lines Inc.
#> 3   2013     1     1     5    JFK   MIA  N619AA American Airlines Inc.
#> 4   2013     1     1     5    JFK   BQN  N804JB        JetBlue Airways
#> 5   2013     1     1     5    LGA   ATL  N668DN   Delta Air Lines Inc.
#> 6   2013     1     1     5    EWR   ORD  N39463  United Air Lines Inc.
#> ..   ...   ...   ...   ...    ...   ...     ...                    ...

But this is hard to generalise when you need to match multiple variables, and takes close reading to figure out the overall intent.

The following sections explain, in detail, how mutating joins work. You’ll start by learning a useful visual representation of joins. We’ll then use that to explain the four mutating join functions: the inner join, and the three outer joins. When working with real data, keys don’t always uniquely identify observations, so next we’ll talk about what happens when there isn’t a unique match. Finally, you’ll learn how to tell dplyr which variables are the keys for a given join.

10.3.1 Understanding joins

To help you learn how joins work, I’m going to represent data frames visually:

(x <- data_frame(key = c(1, 2, 3), val_x = c("x1", "x2", "x3")))
#> Source: local data frame [3 x 2]
#> 
#>     key val_x
#>   (dbl) (chr)
#> 1     1    x1
#> 2     2    x2
#> 3     3    x3
(y <- data_frame(key = c(1, 2, 4), val_y = c("y1", "y2", "y3")))
#> Source: local data frame [3 x 2]
#> 
#>     key val_y
#>   (dbl) (chr)
#> 1     1    y1
#> 2     2    y2
#> 3     4    y3

The coloured column represents the “key” variable: these are used to match the rows between the tables. The grey column represents the “value” column that is carried along for the ride. In these examples I’ll show a single key variable and single value variable, but idea generalises in a straightforward way to multiple keys and multiple values.

A join is a way of connecting each row in x to zero, one, or more rows in y. The following diagram shows each potential match as an intersection of a pair of lines.

(If you look closely, you might notice that we’ve switched the order of the keys and values in x. This is to emphasise that joins match based on the key variable; value variable is just carried along for the ride.)

In an actual join, matches will be indicated with dots. The colour of the dots match the colour of the keys to remind that that’s what important. Then the number of dots = the number of matches = the number of rows in the output.

10.3.2 Inner join

The simplest type of join is the inner join. An inner join matches pairs of observations whenever their keys are equal:

(To be precise, this is an inner equijoin because the keys are matched using the equality operator. Since most joins are equijoins we usually drop that condition.)

The output of an inner join is a new data frame that contains the key, the x values, and the y values. We use by to tell dplyr which variable is the key:

x %>% inner_join(y, by = "key")
#> Source: local data frame [2 x 3]
#> 
#>     key val_x val_y
#>   (dbl) (chr) (chr)
#> 1     1    x1    y1
#> 2     2    x2    y2

The most important property of an inner join is that unmatched rows are dropped. This means that generally inner joins are not appropriate for use in analysis because it’s too easy to lose observations.

10.3.3 Outer joins

An inner join keeps observations that appear in both tables. An outer join keeps observations that appear in at least one of the tables. There are three types of outer joins:

  • A left join keeps all observations in x.
  • A right join keeps all observations in y.
  • A full join keeps all observations in x and y.

These joins work by adding an additional “virtual” observation to each table. This observation has a key that always matches (if no other key matches), and a value filled with NA.

Graphically, that looks like:

The most commonly used join is the left join: you use this whenever you lookup additional data out of another table, because it preserves the original observations even when there isn’t a match. The left join should be your default join: use it unless you have a strong reason to prefer one of the others.

Another way to depict the different types of joins is with a Venn diagram:

However, this is not a great representation. It might jog your memory about which join preserves the observations in which table, but it suffers from a major limitation: a Venn diagram can’t show what happens when keys don’t uniquely identify an observation.

10.3.4 Duplicate keys

So far all the diagrams have assumed that the keys are unique. But that’s not always the case. This section explains what happens when the keys are not unique. There are two possibilities:

  1. One table has duplicate keys. This is useful when you want to add in additional information as there is typically a one-to-many relationship.

    Note that I’ve put the key column in a slightly different position in the output. This reflects that the key is a primary key in y and a foreign key in x.

    x <- data_frame(key = c(1, 2, 2, 1), val_x = paste0("x", 1:4))
    y <- data_frame(key = 1:2, val_y = paste0("y", 1:2))
    left_join(x, y, by = "key")
    #> Source: local data frame [4 x 3]
    #> 
    #>     key val_x val_y
    #>   (dbl) (chr) (chr)
    #> 1     1    x1    y1
    #> 2     2    x2    y2
    #> 3     2    x3    y2
    #> 4     1    x4    y1
  2. Both tables have duplicate keys. This is usually an error because in neither table do the keys uniquely identify an observation. When you join duplicated keys, you get all possible combinations, the Cartesian product:

    x <- data_frame(key = c(1, 2, 2, 3), val_x = paste0("x", 1:4))
    y <- data_frame(key = c(1, 2, 2, 3), val_y = paste0("y", 1:4))
    left_join(x, y, by = "key")
    #> Source: local data frame [6 x 3]
    #> 
    #>     key val_x val_y
    #>   (dbl) (chr) (chr)
    #> 1     1    x1    y1
    #> 2     2    x2    y2
    #> 3     2    x2    y3
    #> 4     2    x3    y2
    #> 5     2    x3    y3
    #> 6     3    x4    y4

10.3.5 Defining the key columns

So far, the pairs of tables have always been joined by a single variable, and that variable has the same name in both tables. That constraint was encoded by by = "key". You can use other values for by to connect the tables in other ways:

  • The default, by = NULL, uses all variables that appear in both tables, the so called natural join. For example, the flights and weather tables match on their common variables: year, month, day, hour and origin.

    flights2 %>% left_join(weather)
    #> Joining by: c("year", "month", "day", "hour", "origin")
    #> Source: local data frame [336,776 x 18]
    #> 
    #>     year month   day  hour origin  dest tailnum carrier  temp  dewp humid
    #>    (dbl) (dbl) (int) (dbl)  (chr) (chr)   (chr)   (chr) (dbl) (dbl) (dbl)
    #> 1   2013     1     1     5    EWR   IAH  N14228      UA    NA    NA    NA
    #> 2   2013     1     1     5    LGA   IAH  N24211      UA    NA    NA    NA
    #> 3   2013     1     1     5    JFK   MIA  N619AA      AA    NA    NA    NA
    #> 4   2013     1     1     5    JFK   BQN  N804JB      B6    NA    NA    NA
    #> 5   2013     1     1     5    LGA   ATL  N668DN      DL    NA    NA    NA
    #> 6   2013     1     1     5    EWR   ORD  N39463      UA    NA    NA    NA
    #> ..   ...   ...   ...   ...    ...   ...     ...     ...   ...   ...   ...
    #> Variables not shown: wind_dir (dbl), wind_speed (dbl), wind_gust (dbl),
    #>   precip (dbl), pressure (dbl), visib (dbl), date (time)
  • A character vector, by = "x". This is like a natural join, but uses only some of the common variables. For example, flights and planes have year variables, but they mean different things so we only want to join by tailnum.

    flights2 %>% left_join(planes, by = "tailnum")
    #> Source: local data frame [336,776 x 16]
    #> 
    #>    year.x month   day  hour origin  dest tailnum carrier year.y
    #>     (int) (int) (int) (dbl)  (chr) (chr)   (chr)   (chr)  (int)
    #> 1    2013     1     1     5    EWR   IAH  N14228      UA   1999
    #> 2    2013     1     1     5    LGA   IAH  N24211      UA   1998
    #> 3    2013     1     1     5    JFK   MIA  N619AA      AA   1990
    #> 4    2013     1     1     5    JFK   BQN  N804JB      B6   2012
    #> 5    2013     1     1     5    LGA   ATL  N668DN      DL   1991
    #> 6    2013     1     1     5    EWR   ORD  N39463      UA   2012
    #> ..    ...   ...   ...   ...    ...   ...     ...     ...    ...
    #> Variables not shown: type (chr), manufacturer (chr), model (chr), engines
    #>   (int), seats (int), speed (int), engine (chr)

    Note that the year variables (which appear in both input data frames, but are not constrained to be equal) are disambiguated in the output with a suffix.

  • A named character vector: by = c("a" = "b"). This will match variable a in table x to variable b in table y. The variables from x will be used in the output.

    For example, if we want to draw a map we need to combine the flights data with the airports data which contains the location (lat and long) of each airport. Each flight has an origin and destination airport, so we need to specify which one we want to join to:

    flights2 %>% left_join(airports, c("dest" = "faa"))
    #> Source: local data frame [336,776 x 14]
    #> 
    #>     year month   day  hour origin  dest tailnum carrier
    #>    (int) (int) (int) (dbl)  (chr) (chr)   (chr)   (chr)
    #> 1   2013     1     1     5    EWR   IAH  N14228      UA
    #> 2   2013     1     1     5    LGA   IAH  N24211      UA
    #> 3   2013     1     1     5    JFK   MIA  N619AA      AA
    #> 4   2013     1     1     5    JFK   BQN  N804JB      B6
    #> 5   2013     1     1     5    LGA   ATL  N668DN      DL
    #> 6   2013     1     1     5    EWR   ORD  N39463      UA
    #> ..   ...   ...   ...   ...    ...   ...     ...     ...
    #> Variables not shown: name (chr), lat (dbl), lon (dbl), alt (int), tz
    #>   (dbl), dst (chr)
    flights2 %>% left_join(airports, c("origin" = "faa"))
    #> Source: local data frame [336,776 x 14]
    #> 
    #>     year month   day  hour origin  dest tailnum carrier
    #>    (int) (int) (int) (dbl)  (chr) (chr)   (chr)   (chr)
    #> 1   2013     1     1     5    EWR   IAH  N14228      UA
    #> 2   2013     1     1     5    LGA   IAH  N24211      UA
    #> 3   2013     1     1     5    JFK   MIA  N619AA      AA
    #> 4   2013     1     1     5    JFK   BQN  N804JB      B6
    #> 5   2013     1     1     5    LGA   ATL  N668DN      DL
    #> 6   2013     1     1     5    EWR   ORD  N39463      UA
    #> ..   ...   ...   ...   ...    ...   ...     ...     ...
    #>                   name
    #>                  (chr)
    #> 1  Newark Liberty Intl
    #> 2           La Guardia
    #> 3  John F Kennedy Intl
    #> 4  John F Kennedy Intl
    #> 5           La Guardia
    #> 6  Newark Liberty Intl
    #> ..                 ...
    #> Variables not shown: lat (dbl), lon (dbl), alt (int), tz (dbl), dst (chr)

10.3.6 Exercises

  1. Compute the average delay by destination, then join on the airports data frame so you can show the spatial distribution of delays. Here’s an easy way to draw a map of the United States:

    You might want to use the size or colour of the points to display the average delay for each airport.

  2. Is there a relationship between the age of a plane and its delays?

  3. What weather conditions make it more likely to see a delay?

  4. What happened on June 13 2013? Display the spatial pattern of delays, and then use Google to cross-reference with the weather.

10.3.7 Other implementations

base::merge() can perform all four types of mutating join:

dplyr merge
inner_join(x, y) merge(x, y)
left_join(x, y) merge(x, y, all.x = TRUE)
right_join(x, y) merge(x, y, all.y = TRUE),
full_join(x, y) merge(x, y, all.x = TRUE, all.y = TRUE)

The advantages of the specific dplyr verbs is that they more clearly convey the intent of your code: the difference between the joins is really important but concealed in the arguments of merge(). dplyr’s joins are considerably faster and don’t mess with the order of the rows.

SQL is the inspiration for dplyr’s conventions, so the translation is straightforward:

dplyr SQL
inner_join(x, y, by = "z") SELECT * FROM x INNER JOIN y USING (z)
left_join(x, y, by = "z") SELECT * FROM x LEFT OUTER JOIN y USING (z)
right_join(x, y, by = "z") SELECT * FROM x RIGHT OUTER JOIN y USING (z)
full_join(x, y, by = "z") SELECT * FROM x FULL OUTER JOIN y USING (z)

Note that “INNER” and “OUTER” are optional, and often omitted.

Joining different variables between the tables, e.g. inner_join(x, y, by = c("a" = "b")) uses a slightly different syntax in SQL: SELECT * FROM x INNER JOIN y ON x.a = y.b. As this syntax suggests SQL supports a wide range of join types than dplyr because you can connect the tables using constraints other than equality (sometimes called non-equijoins).

10.4 Filtering joins

Filtering joins match observations in the same way as mutating joins, but affect the observations, not the variables. There are two types:

  • semi_join(x, y) keeps all observations in x that have a match in y.
  • anti_join(x, y) drops all observations in x that have a match in y.

Semi-joins are useful for matching filtered summary tables back to the original rows. For example, imagine you’ve found the top ten most popular destinations:

top_dest <- flights %>%
  count(dest, sort = TRUE) %>%
  head(10)
top_dest
#> Source: local data frame [10 x 2]
#> 
#>     dest     n
#>    (chr) (int)
#> 1    ORD 17283
#> 2    ATL 17215
#> 3    LAX 16174
#> 4    BOS 15508
#> 5    MCO 14082
#> 6    CLT 14064
#> ..   ...   ...

Now you want to find each flight that went to one of those destinations. You could construct a filter yourself:

flights %>% filter(dest %in% top_dest$dest)
#> Source: local data frame [141,145 x 16]
#> 
#>     year month   day dep_time dep_delay arr_time arr_delay carrier tailnum
#>    (int) (int) (int)    (int)     (dbl)    (int)     (dbl)   (chr)   (chr)
#> 1   2013     1     1      542         2      923        33      AA  N619AA
#> 2   2013     1     1      554        -6      812       -25      DL  N668DN
#> 3   2013     1     1      554        -4      740        12      UA  N39463
#> 4   2013     1     1      555        -5      913        19      B6  N516JB
#> 5   2013     1     1      557        -3      838        -8      B6  N593JB
#> 6   2013     1     1      558        -2      753         8      AA  N3ALAA
#> ..   ...   ...   ...      ...       ...      ...       ...     ...     ...
#> Variables not shown: flight (int), origin (chr), dest (chr), air_time
#>   (dbl), distance (dbl), hour (dbl), minute (dbl)

But it’s difficult to extend that approach to multiple variables. For example, imagine that you’d found the 10 days with highest average delays. How would you construct the filter statement that used year, month, and day to match it back to flights?

Instead you can use a semi-join, which connects the two tables like a mutating join, but instead of adding new columns, only keeps the rows in x that have a match in y:

flights %>% semi_join(top_dest)
#> Joining by: "dest"
#> Source: local data frame [141,145 x 16]
#> 
#>     year month   day dep_time dep_delay arr_time arr_delay carrier tailnum
#>    (int) (int) (int)    (int)     (dbl)    (int)     (dbl)   (chr)   (chr)
#> 1   2013     1     1      554        -4      740        12      UA  N39463
#> 2   2013     1     1      558        -2      753         8      AA  N3ALAA
#> 3   2013     1     1      608         8      807        32      MQ  N9EAMQ
#> 4   2013     1     1      629        -1      824        14      AA  N3CYAA
#> 5   2013     1     1      656        -4      854         4      AA  N4WNAA
#> 6   2013     1     1      709         9      852        20      UA  N26226
#> ..   ...   ...   ...      ...       ...      ...       ...     ...     ...
#> Variables not shown: flight (int), origin (chr), dest (chr), air_time
#>   (dbl), distance (dbl), hour (dbl), minute (dbl)

Graphically, a semi-join looks like this:

Only the existence of a match is important; it doesn’t matter which observation is matched. This means that filtering joins never duplicate rows like mutating joins do:

The inverse of a semi-join is an anti-join. An anti-join keeps the rows that don’t have a match:

Anti-joins are are useful for diagnosing join mismatches. For example, when connecting flights and planes, you might be interested to know that there are many flights that don’t have a match in planes:

flights %>%
  anti_join(planes, by = "tailnum") %>%
  count(tailnum, sort = TRUE)
#> Source: local data frame [722 x 2]
#> 
#>    tailnum     n
#>      (chr) (int)
#> 1       NA  2512
#> 2   N725MQ   575
#> 3   N722MQ   513
#> 4   N723MQ   507
#> 5   N713MQ   483
#> 6   N735MQ   396
#> ..     ...   ...

10.4.1 Exercises

  1. What does it mean for a flight to have a missing tailnum? What do the tail numbers that don’t have a matching record in planes have in common? (Hint: one variable explains ~90% of the problem.)

  2. Find the 48 hours (over the course of the whole year) that have the worst delays. Cross-reference it with the weather data. Can you see any patterns?

  3. What does anti_join(flights, airports, by = c("dest" = "faa")) tell you? What does anti_join(airports, flights, by = c("faa" = "dest")) tell you?

10.5 Join problems

The data you’ve been working with in this chapter has been cleaned up so that you’ll have as few problems as possible. Your own data is unlikely to be so nice, so there are a few things that you should do with your own data to make your joins go smoothly.

  1. Start by identifying the variables that form the primary key in each table. You should usually do this based on your understanding of the data, not empirically by looking for a combination of variables that give a unique identifier. If you just look for variables without thinking about what they mean, you might get (un)lucky and find a combination that’s unique in your current data but the relationship might not be true in general.

    airports %>% count(alt, lat) %>% filter(n > 1)
    #> Source: local data frame [1 x 3]
    #> Groups: alt [1]
    #> 
    #>     alt   lat     n
    #>   (int) (dbl) (int)
    #> 1    13  40.6     2
  2. Check that none of the variables in the primary key are missing. If a value is missing then it can’t identify an observation!

  3. Check that your foreign keys match primary keys in another table. The best way to do this is with an anti_join(). It’s common for keys not to match because of data entry errors. Fixing these is often a lot of work.

    If you do have missing keys, you’ll need to be thoughtful about your use of inner vs. outer joins, carefully considering whether or not you want to drop rows that don’t have a match.

Be aware that simply checking the number of rows before and after the join is not sufficient to ensure that your join has gone smoothly. If you have an inner join with duplicate keys in both tables, you might get unlucky as the number of dropped rows might exactly equal the number of duplicated rows!

10.6 Set operations

The final type of two-table verb is set operations. Generally, I use these the least frequently, but they are occasionally useful when you want to break a single complex filter into simpler pieces that you then combine.

All these operations work with a complete row, comparing the values of every variable. These expect the x and y inputs to have the same variables, and treat the observations like sets:

  • intersect(x, y): return only observations in both x and y.
  • union(x, y): return unique observations in x and y.
  • setdiff(x, y): return observations in x, but not in y.

Given this simple data:

(df1 <- data_frame(x = 1:2, y = c(1L, 1L)))
#> Source: local data frame [2 x 2]
#> 
#>       x     y
#>   (int) (int)
#> 1     1     1
#> 2     2     1
(df2 <- data_frame(x = 1:2, y = 1:2))
#> Source: local data frame [2 x 2]
#> 
#>       x     y
#>   (int) (int)
#> 1     1     1
#> 2     2     2

The four possibilities are:

intersect(df1, df2)
#> Source: local data frame [1 x 2]
#> 
#>       x     y
#>   (int) (int)
#> 1     1     1
# Note that we get 3 rows, not 4
union(df1, df2)
#> Source: local data frame [3 x 2]
#> 
#>       x     y
#>   (int) (int)
#> 1     2     2
#> 2     2     1
#> 3     1     1
setdiff(df1, df2)
#> Source: local data frame [1 x 2]
#> 
#>       x     y
#>   (int) (int)
#> 1     2     1
setdiff(df2, df1)
#> Source: local data frame [1 x 2]
#> 
#>       x     y
#>   (int) (int)
#> 1     2     2