I need your help!

If you find any typos, errors, or places where the text may be improved, please let me know. The best ways to provide feedback are by GitHub or hypothes.is annotations.

Opening an issue or submitting a pull request on GitHub

Hypothesis Adding an annotation using hypothes.is. To add an annotation, select some text and then click the on the pop-up menu. To see the annotations of others, click the in the upper right-hand corner of the page.

13 Relational data

13.1 Introduction

The datamodelr package is used to draw database schema.

13.2 nycflights13

Exercise 13.2.1

Imagine you wanted 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?

Drawing the routes requires the latitude and longitude of the origin and the destination airports of each flight. This requires the flights and airports tables. The flights table has the origin (origin) and destination (dest) airport of each flight. The airports table has the longitude (lon) and latitude (lat) of each airport. To get the latitude and longitude for the origin and destination of each flight, requires two joins for flights to airports, once for the latitude and longitude of the origin airport, and once for the latitude and longitude of the destination airport. I use an inner join in order to drop any flights with missing airports since they will not have a longitude or latitude.

This plots the approximate flight paths of the first 100 flights in the flights dataset.

Exercise 13.2.2

I forgot to draw the relationship between weather and airports. What is the relationship and how should it appear in the diagram?

The column airports$faa is a foreign key of weather$origin. The following drawing updates the one in Section 13.2 to include this relation. The line representing the new relation between weather and airports is colored black. The lines representing the old relations are gray and thinner.

Exercise 13.2.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?

If the weather was included for all airports in the US, then it would provide the weather for the destination of each flight. The weather data frame columns (year, month, day, hour, origin) are a foreign key for the flights data frame columns (year, month, day, hour, dest). This would provide information about the weather at the destination airport at the time of the flight take off, unless the arrival date-time were calculated.

So why was this not a relationship prior to adding additional rows to the weather table? In a foreign key relationship, the collection of columns in the child table must refer to a unique collection of columns in the parent table. When the weather table only contained New York airports, there were many values of (year, month, day, hour, dest) in flights that did not appear in the weather table. Therefore, it was not a foreign key. It was only after all combinations of year, month, day, hour, and airports that are defined in flights were added to the weather table that there existed this relation between these tables.

Exercise 13.2.4

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?

I would add a table of special dates, similar to the following table.

The primary key of the table would be the (year, month, day) columns. The (year, month, day) columns could be used to join special_days with other tables.

13.3 Keys

Exercise 13.3.1

Add a surrogate key to flights.

I add the column flight_id as a surrogate key. I sort the data prior to making the key, even though it is not strictly necessary, so the order of the rows has some meaning.

flights %>%
  arrange(year, month, day, sched_dep_time, carrier, flight) %>%
  mutate(flight_id = row_number()) %>%
  glimpse()
#> Rows: 336,776
#> Columns: 20
#> $ year           <int> 2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013, …
#> $ month          <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
#> $ day            <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
#> $ dep_time       <int> 517, 533, 542, 544, 554, 559, 558, 559, 558, 558, 557,…
#> $ sched_dep_time <int> 515, 529, 540, 545, 558, 559, 600, 600, 600, 600, 600,…
#> $ dep_delay      <dbl> 2, 4, 2, -1, -4, 0, -2, -1, -2, -2, -3, NA, 1, 0, -5, …
#> $ arr_time       <int> 830, 850, 923, 1004, 740, 702, 753, 941, 849, 853, 838…
#> $ sched_arr_time <int> 819, 830, 850, 1022, 728, 706, 745, 910, 851, 856, 846…
#> $ arr_delay      <dbl> 11, 20, 33, -18, 12, -4, 8, 31, -2, -3, -8, NA, -6, -7…
#> $ carrier        <chr> "UA", "UA", "AA", "B6", "UA", "B6", "AA", "AA", "B6", …
#> $ flight         <int> 1545, 1714, 1141, 725, 1696, 1806, 301, 707, 49, 71, 7…
#> $ tailnum        <chr> "N14228", "N24211", "N619AA", "N804JB", "N39463", "N70…
#> $ origin         <chr> "EWR", "LGA", "JFK", "JFK", "EWR", "JFK", "LGA", "LGA"…
#> $ dest           <chr> "IAH", "IAH", "MIA", "BQN", "ORD", "BOS", "ORD", "DFW"…
#> $ air_time       <dbl> 227, 227, 160, 183, 150, 44, 138, 257, 149, 158, 140, …
#> $ distance       <dbl> 1400, 1416, 1089, 1576, 719, 187, 733, 1389, 1028, 100…
#> $ hour           <dbl> 5, 5, 5, 5, 5, 5, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, …
#> $ minute         <dbl> 15, 29, 40, 45, 58, 59, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
#> $ time_hour      <dttm> 2013-01-01 05:00:00, 2013-01-01 05:00:00, 2013-01-01 …
#> $ flight_id      <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16,…

Exercise 13.3.2

Identify the keys in the following datasets

  1. Lahman::Batting
  2. babynames::babynames
  3. nasaweather::atmos
  4. fueleconomy::vehicles
  5. ggplot2::diamonds

(You might need to install some packages and read some documentation.)

The answer to each part follows.

  1. The primary key for Lahman::Batting is (playerID, yearID, stint). The columns (playerID, yearID) are not a primary key because players can play on different teams within the same year.

  2. The primary key for babynames::babynames is (year, sex, name). The columns (year, name) are not a primary key since there are separate counts for each name for each sex, and the same names can be used by more than one sex.

  3. The primary key for nasaweather::atmos is (lat, long, year, month). The primary key represents the location and time that the measurement was taken.

  4. The column id, the unique EPA identifier of the vehicle, is the primary key for fueleconomy::vehicles.

  5. There is no primary key for ggplot2::diamonds since there is no combination of variables that uniquely identifies each observation. This is implied by the fact that the number of distinct rows in the dataset is less than the total number of rows, meaning that there are some duplicate rows.

    If we need a unique identifier for our analysis, we could add a surrogate key.

Exercise 13.3.3

Draw a diagram illustrating the connections between the Batting, Master, and Salaries tables in the Lahman package. Draw another diagram that shows the relationship between Master, Managers, AwardsManagers.

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

For the Batting, Master, and Salaries tables:

  • Master

    • Primary key: playerID

  • Batting

    • Primary key: playerID, yearID, stint

    • Foreign keys:

      • playerID = Master$playerID (many-to-1)

  • Salaries

    • Primary key: yearID, teamID, playerID

    • Foreign keys:

      • playerID = Master$playerID (many-to-1)

The columns teamID and lgID are not foreign keys even though they appear in multiple tables (with the same meaning) because they are not primary keys for any of the tables considered in this exercise. The teamID variable references Teams$teamID, and lgID does not have its own table.

R for Data Science uses database schema diagrams to illustrate relations between the tables. Most flowchart or diagramming software can be used used to create database schema diagrams, as well as some specialized database software. The diagrams in R for Data Science were created with OmniGraffle, and their sources can be found in its GitHub repository.

The following diagram was created with OmniGraffle in the same style as those in R for Data Science . It shows the relations between the Master, Batting and Salaries tables.

Another option to draw database schema diagrams is the R package datamodelr, which can programmatically create database schema diagrams. The following code uses datamodelr to draw a diagram of the relations between the Batting, Master, and Salaries tables.

For the Master, Manager, and AwardsManagers tables:

  • Master

    • Primary key: playerID

  • Managers

    • Primary key: yearID, teamID, inseason

    • Foreign keys:

      • playerID references Master$playerID (many-to-1)

  • AwardsManagers:

    • Primary key: playerID, awardID, yearID

    • Foreign keys:

      • playerID references Master$playerID (many-to-1)

For AwardsManagers, the columns (awardID, yearID, lgID) are not a primary key because there can be, and have been ties, as indicated by the tie variable.

The relations between the Master, Managers, and AwardsManagers tables are shown in the following two diagrams: the first created manually with OmniGraffle, and the second programmatically in R with the datamodelr package.

The primary keys of Batting, Pitching, and Fielding are the following:

  • Batting: (playerID, yearID, stint)
  • Pitching: (playerID, yearID, stint)
  • Fielding: (playerID, yearID, stint, POS).

While Batting and Pitching has one row per player, year, stint, the Fielding table has additional rows for each position (POS) a player played within a stint.

Since Batting, Pitching, and Fielding all share the playerID, yearID, and stint we would expect some foreign key relations between these tables. The columns (playerID, yearID, stint) in Pitching are a foreign key which references the same columns in Batting. We can check this by checking that all observed combinations of values of these columns appearing in Pitching also appear in Batting. To do this I use an anti-join, which is discussed in the section Filtering Joins.

Similarly, the columns (playerID, yearID, stint) in Fielding are a foreign key which references the same columns in Batting.

The following diagram shows the relations between the Batting, Pitching, and Fielding tables.

13.4 Mutating joins

Exercise 13.4.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:

(Don’t worry if you don’t understand what semi_join() does — you’ll learn about it next.)

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

Exercise 13.4.2

Add the location of the origin and destination (i.e. the lat and lon) to flights.

You can perform one join after another. If duplicate variables are found, by default, dplyr will distinguish the two by adding .x, and .y to the ends of the variable names to solve naming conflicts.

The suffix argument overrides this default behavior. Since is always good practice to have clear variable names, I will use the suffixes "_dest" and "_origin" to specify whether the column refers to the destination or origin airport.

Exercise 13.4.3

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

The question does not specify whether the relationship is with departure delay or arrival delay. I will look at both.

To compare the age of the plane to flights delay, I merge flights with the planes, which contains a variable plane_year, with the year in which the plane was built. To look at the relationship between plane age and departure delay, I will calculate the average arrival and departure delay for each age of a flight. Since there are few planes older than 25 years, so I truncate age at 25 years.

I will look for a relationship between departure delay and age by plotting age against the average departure delay. The average departure delay is increasing for planes with ages up until 10 years. After that the departure delay decreases or levels off. The decrease in departure delay could be because older planes with many mechanical issues are removed from service or because air lines schedule these planes with enough time so that mechanical issues do not delay them.

There is a similar relationship in arrival delays. Delays increase with the age of the plane until ten years, then it declines and flattens out.

Exercise 13.4.5

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

There was a large series of storms (derechos) in the southeastern US (see June 12-13, 2013 derecho series). The following plot show that the largest delays were in Tennessee (Nashville), the Southeast, and the Midwest, which were the locations of the derechos.

13.5 Filtering joins

Exercise 13.5.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 problems.)

Flights that have a missing tailnum all have missing values of arr_time, meaning that the flight was canceled.

Many of the tail numbers that don’t have a matching value in planes are registered to American Airlines (AA) or Envoy Airlines (MQ). The documentation for planes states

American Airways (AA) and Envoy Air (MQ) report fleet numbers rather than tail numbers so can’t be matched.

However, not all tail numbers appearing inflights from these carriers are missing from the planes table. I don’t know how to reconcile this discrepancy.

Exercise 13.5.2

Filter flights to only show flights with planes that have flown at least 100 flights.

First, I find all planes that have flown at least 100 flights. I need to filter flights that are missing a tail number otherwise all flights missing a tail number will be treated as a single plane.

Now, I will semi join the data frame of planes that have flown at least 100 flights to the data frame of flights to select the flights by those planes.

This can also be answered with a grouped mutate.

Exercise 13.5.3

Combine fueleconomy::vehicles and fueleconomy::common to find only the records for the most common models.

Why does the above code join on make and model and not just model? It is possible for two car brands (make) to produce a car with the same name (model). In both the vehicles and common data we can find some examples. For example, “Truck 4WD” is produced by many different brands.

If we were to merge these data on the model column alone, there would be incorrect matches.

Exercise 13.5.4

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?

I will start by clarifying how I will be measuring the concepts in the question. There are three concepts that need to be defined more precisely.

  1. What is meant by “delay”? I will use departure delay. Since the weather data only contains data for the New York City airports, and departure delays will be more sensitive to New York City weather conditions than arrival delays.

  2. What is meant by “worst”? I define worst delay as the average departure delay per flight for flights scheduled to depart in that hour. For hour, I will use the scheduled departure time rather than the actual departure time. If planes are delayed due to weather conditions, the weather conditions during the scheduled time are more important than the actual departure time, at which point, the weather could have improved.

  3. What is meant by “48 hours over the course of the year”? This could mean two days, a span of 48 contiguous hours, or 48 hours that are not necessarily contiguous hours. I will find 48 not-necessarily contiguous hours. That definition makes better use of the methods introduced in this section and chapter.

  4. What is the unit of analysis? Although the question mentions only hours, I will use airport hours. The weather dataset has an observation for each airport for each hour. Since all the departure airports are in the vicinity of New York City, their weather should be similar, it will not be the same.

First, I need to find the 48 hours with the worst delays. I group flights by hour of scheduled departure time and calculate the average delay. Then I select the 48 observations (hours) with the highest average delay.

Then I can use semi_join() to get the weather for these hours.

For weather, I’ll focus on precipitation, wind speed, and temperature. I will display these in both a table and a plot.
Many of these observations have a higher than average wind speed (10 mph) or some precipitation. However, I would have expected the weather for the hours with the worst delays to be much worse.

It’s hard to say much more than that without using the tools from Exploratory Data Analysis section to look for covariation between weather and flight delays using all flights. Implicitly in my informal analysis of trends in weather using only the 48 hours with the worst delays, I was comparing the weather in these hours to some belief I had about what constitutes “normal” or “good” weather. It would be better to actually use data to make that comparison.

Exercise 13.5.5

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

The expression anti_join(flights, airports, by = c("dest" = "faa")) returns the flights that went to an airport that is not in the FAA list of destinations. Since the FAA list only contains domestic airports, these are likely foreign flights. However, running that expression that there are only four airports in this list.

In this set of four airports three are in Puerto Rico (BQN, SJU, and PSE) and one is in the US Virgin Islands ( STT).

The reason for this discrepancy is that the flights and airports tables are derived from different sources. The flights data comes from the US Department of Transportation Bureau of Transportation Statistics, while the airport metadata comes from openflights.org. The BTS includes Puerto Rico and U.S. Virgin Islands as “domestic” (part of the US), while the openflights.org give use different values of country for airports in the US states ("United States") Puerto Rico ("Puerto Rico") and US Virgin Islands ("Virgin Islands").

The expression anti_join(airports, flights, by = c("faa" = "dest")) returns the US airports that were not the destination of any flight in the data. Since the data contains all flights from New York City airports, this is also the list of US airports that did not have a nonstop flight from New York City in 2013.

Exercise 13.5.6

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 the tools you’ve learned above.

At each point in time, each plane is flown by a single airline. However, a plane can be sold and fly for multiple airlines. Logically, it is possible that a plane can fly for multiple airlines over the course of its lifetime. But, it is not necessarily the case that a plane will fly for more than one airline in this data, especially since it comprises only a year of data. So let’s check to see if there are any planes in the data flew for multiple airlines.

First, find all distinct airline, plane combinations.

The number of planes that have flown for more than one airline are those tailnum that appear more than once in the planes_carriers data.

The names of airlines are easier to understand than the two-letter carrier codes. The airlines data frame contains the names of the airlines.

13.6 Join problems

No exercises

13.7 Set operations

No exercises