10 Combining datasets with keys

Summary

  • If \(V\) is the set of variables for a dataset, a subset \(K \subseteq V\) is a key if for two observations \(x\) and \(y\), \(x[K] = y[K]\) implies that \(x[V] = y[V]\). That is, the measurements of the columns in the key uniquely determine the observation.

  • Given a first table and a second table of data, left_join creates a table that has every observation from the first table, and draws measurements from both tables.

  • Given a first table and second table of data, a full_join creates a table where all observations and measurements from either one or two tables appear.

  • Given a first table and second table of data, an inner_join is used to create a table where each observation in the output appears in two tables, with measurements from both tables.

  • Given a first table and second table of data, a semi_join is used to create a table where each observation in the output is in the first and second tables, with measurements from the first table.

  • Given a first table and second table of data, an anti_join is used to create a table where each observation in the output is in the first table but not the second table, with measurements from the first table.

  • The row_number function can be used with mutate to create an artificial key for any dataset.

10.1 Keys

Recall that the observations in a dataset are unique. Because of this, if all of the variable values are known for every observation, it is possible to tell which observation is which. Consider the following data set.
latitude longitude temperature weather
34.1 117.7 26.3 sunny
41.9 -87.6 22.1 sunny
34.1 112.2 16.7 rainy

Consider the set of variables \(V = \{\text{latitude}, \text{longitude}, \text{temperature}, \text{weather} \}\). Note that the set is surrounded by curly braces \(\{\) and \(\}\) to indicate that it is a set, and the order of the objects in the set is not important.

If the value of the observation for all elements of \(V\) is known, then it is possible to determine exactly which observation is being discussed. For instance, if the observation for \(V\) is known to be \(34.1, 117.7, 26.3, \text{sunny}\), then it must be the first observation.

On the other hand, if only \(\{ \text{latitude} \}\) is known, that is not enough to determine the observation. For instance, knowing the latitude is 34.1 means that either the first or third observation is being discussed. Therefore, say that \(\{\text{latitude}, \text{longitude}, \text{temperature}, \text{weather} \}\) is a key, while \(\{ \text{latitude} \}\) is not.

To formally define a key, it helps recall set notation. For a set \(S\), write \(s \in S\) to mean that \(s\) is in \(S\) is a true statement. For instance, \[ \text{temperature} \in \{\text{latitude}, \text{longitude}, \text{temperature}, \text{weather} \} \] is a true statement.

A key consists of some of the variables being measured. When a set consists of some (or all) of the elements of another set, it is called a subset of the other set. So a key is a subset of the variables. But more is needed!

For a subset of variables \(K \subset V\), let \(x[K]\) denote the values of an observation for those \(K\) variables. For instance, \[ x[\{ \text{latitude}, \text{longitude} \}] = (34.1, 117.7) \] for the first observation. Then a set of variables is a key if knowing the values of these variables is enough to pinpoint the exact observation.

Given a relation with variables \(V = \{v_1, \ldots, v_n\}\), a subset \(K \subseteq V\) is a key if for all observations \(x\) and \(y\), \(x[K] = y[K] \rightarrow x = y\).

In the example above, there are many keys.

\(K\) Is it a key?
\(\{ \text{latitude} \}\) No
\(\{ \text{latitude}, \text{longitude} \}\) Yes
\(\{ \text{weather} \}\) No
\(\{ \text{weather}, \text{latitude} \}\) Yes

Often, a key is intentionally designed into the dataset. For instance, a hospital might have a variable called patient_id that is different for each individual admitted to the hospital. The United States uses the Social Security Number to uniquely identify individuals for purposes of running the Social Security system. Such a designed key is called a primary key.

A key that is designed for a dataset is called a primary key.

10.2 Joining relations using keys

Keys are important because they allow data from more than one data set to be combined. The simplest type of combination of data sets is called a left join. In this combination, two different relations have spread out the data over more than one table.

For instance, the following code creates a tibble temp_data that holds the temperature data that has been considered so far, but not the weather data.

temp_data <- tibble(
  latitude    = c(34.1, 41.9, 34.1),
  longitude   = c(117.7, -87.6, 112.2),
  temperature = c(26.3, 22.1, 16.7)
)
temp_data |> kable() |> kable_styling()
latitude longitude temperature
34.1 117.7 26.3
41.9 -87.6 22.1
34.1 112.2 16.7

The next tibble weather_data holds a measurement called weather, but not the temperature measurement for some locations.

weather_data <- tibble(
  latitude    = c(34.1, 41.9, 32.5, 35.6),
  longitude   = c(117.7, -87.6, 90.4, 110),
  weather     = c("sunny", "sunny", "rainy", "rainy")
)
weather_data |> kable() |> kable_styling()
latitude longitude weather
34.1 117.7 sunny
41.9 -87.6 sunny
32.5 90.4 rainy
35.6 110.0 rainy

In both tables, \(\{\text{latitude}, \text{longitude} \}\) form a key that uniquely identifies the observation. And so using this key, it is possible to join the weather data to the temperature data. The command to do so is left_join, which is part of the package dplyr.

left_join(temp_data, weather_data, by = join_by(latitude, longitude))
## # A tibble: 3 × 4
##   latitude longitude temperature weather
##      <dbl>     <dbl>       <dbl> <chr>  
## 1     34.1     118.         26.3 sunny  
## 2     41.9     -87.6        22.1 sunny  
## 3     34.1     112.         16.7 <NA>

Note that the observations in the left join match those of the first (left) table. Because the third observation in temp_data does not appear in weather_data, the weather measurement for that observation was set to NA.

What should happen when the user does not know what the key is? In that case, the left_join command will try to use the column names that appear in both tables as the joining variables. Note that they might not actually be a key, so be careful with the default choice!

temp_data |>
  left_join(weather_data)
## Joining with `by = join_by(latitude, longitude)`
## # A tibble: 3 × 4
##   latitude longitude temperature weather
##      <dbl>     <dbl>       <dbl> <chr>  
## 1     34.1     118.         26.3 sunny  
## 2     41.9     -87.6        22.1 sunny  
## 3     34.1     112.         16.7 <NA>

The above code results in the same new table as before. It gives an informational message Joining with by = join_by(latitude, longitude).

10.3 The full/outer join

When a join can add columns to a table, it is called a mutating join. (Recall that the function mutate() also can add columns to a table.) The left join is an example of a mutating join.

In a mutating join the output columns can come from either input table, and always include the key columns.

A full join is another mutating join that is similar to a left join, except that observations from both tables are always included. This can lead to having NA values when an observation is in one table but not the other.

In the following outer join, there are two observations in common, one belonging only to temp_data, and two belonging only to weather_data.

full_join(temp_data, weather_data)
## Joining with `by = join_by(latitude, longitude)`
## # A tibble: 5 × 4
##   latitude longitude temperature weather
##      <dbl>     <dbl>       <dbl> <chr>  
## 1     34.1     118.         26.3 sunny  
## 2     41.9     -87.6        22.1 sunny  
## 3     34.1     112.         16.7 <NA>   
## 4     32.5      90.4        NA   rainy  
## 5     35.6     110          NA   rainy

The result is two observations with both a temperature measurement and a weather measurement, one observation with only a temperature measurement, and two observations with only a weather measurement.

Note that full_join is similar to union in that observations appear if they show up in either of the two tables.

10.4 Inner joins

In an inner join, only observations that appear in both tables are included. This is similar to the intersect function from earlier.

inner_join(temp_data, weather_data)
## Joining with `by = join_by(latitude, longitude)`
## # A tibble: 2 × 4
##   latitude longitude temperature weather
##      <dbl>     <dbl>       <dbl> <chr>  
## 1     34.1     118.         26.3 sunny  
## 2     41.9     -87.6        22.1 sunny

10.5 Filtering joins

In a filtering join the columns from the first table are kept as is, and the output rows form a subset of the first table rows.

The two filtering joins are semi_join and anti_join. These roughly correspond to intersect and setdiff.

In a semi join the columns from the first table are kept as is, and the output rows are those observations in the first table and in the second.

semi_join(temp_data, weather_data)
## Joining with `by = join_by(latitude, longitude)`
## # A tibble: 2 × 3
##   latitude longitude temperature
##      <dbl>     <dbl>       <dbl>
## 1     34.1     118.         26.3
## 2     41.9     -87.6        22.1

An anti join keeps the columns from the first table, and removes those observations that are in both the first and second table.

anti_join(temp_data, weather_data)
## Joining with `by = join_by(latitude, longitude)`
## # A tibble: 1 × 3
##   latitude longitude temperature
##      <dbl>     <dbl>       <dbl>
## 1     34.1      112.        16.7

Since an observation from the first table is either in the second table or not, the union of these two tibbles returns the original table.

union(
  semi_join(temp_data, weather_data),
  anti_join(temp_data, weather_data)
)
## Joining with `by = join_by(latitude, longitude)`
## Joining with `by = join_by(latitude, longitude)`
## # A tibble: 3 × 3
##   latitude longitude temperature
##      <dbl>     <dbl>       <dbl>
## 1     34.1     118.         26.3
## 2     41.9     -87.6        22.1
## 3     34.1     112.         16.7

10.6 Rows and columns

It helps to remember the number of rows and columns output by the various join commands to understand what they do.

  • left_join. Number of rows equals the number of rows in the first table when the key used to join is a key in both tables. If the joining variables are not a key in the second table, then the number of rows could be larger than the number of rows in the first table. The number of columns in the join is the number of columns in the first table plus any columns added from the second table.

  • full_join. When the joining variables form a key in both tables, the number of rows equals the number of observations that appear in either one or both of the tables. Number of columns is the number of columns in the first table plus any columns added from the second table.

  • inner_join. When the joining variables form a key in both tables, the number of rows equals the number of observations that appear in both tables. Number of columns is the number of columns in the first table plus any columns added from the second table.

  • semi_join. Number of rows equals the number of observations that appear in both the first and second tables. Number of columns is the number of columns in the first table.

  • anti_join. Number of rows equals the number of observations that appear in the first table but not in the second table. Number of columns is the number of columns in the first table.

10.7 What if something other than a key is used to join?

So far our examples have used the key \(\{ \text{latitude}, \text{longitude} \}\) in order to identify observations. What if it was forced to use something that was not a key to join the tables?

For instance, \(\{ \text{latitude} \}\) by itself is not a key for temp_data, since the first and third observations in this dataset have the same latitude value.

temp_data
## # A tibble: 3 × 3
##   latitude longitude temperature
##      <dbl>     <dbl>       <dbl>
## 1     34.1     118.         26.3
## 2     41.9     -87.6        22.1
## 3     34.1     112.         16.7

So given that this is not a key, what happens when it is used as the joining variable? Let’s try it out!

left_join(temp_data, weather_data,
          by = "latitude")
## # A tibble: 3 × 5
##   latitude longitude.x temperature longitude.y weather
##      <dbl>       <dbl>       <dbl>       <dbl> <chr>  
## 1     34.1       118.         26.3       118.  sunny  
## 2     41.9       -87.6        22.1       -87.6 sunny  
## 3     34.1       112.         16.7       118.  sunny

Note that there are now two columns, longitude.x and longitude.y. Note that both the temp_data and the weather_data had a longitude column. So in order to distinguish which was which, it gets broken into two columns to distinguish data on longitude that came from the first dataset versus the second dataset. If you wish to make sure that this does not happen, make longitude part of the key.

left_join(temp_data, weather_data,
          by = c("latitude", "longitude"))
## # A tibble: 3 × 4
##   latitude longitude temperature weather
##      <dbl>     <dbl>       <dbl> <chr>  
## 1     34.1     118.         26.3 sunny  
## 2     41.9     -87.6        22.1 sunny  
## 3     34.1     112.         16.7 <NA>

10.8 Artificial keys

So far the keys used have come from the data itself. However, sometimes it is useful to create an artificial key, which is a new variable that can serve as a key by assigning each observation a different value. The row_number function from dplyr can be used to accomplish this task.

temp_data |>
  mutate(id = row_number(), .before = 1)
## # A tibble: 3 × 4
##      id latitude longitude temperature
##   <int>    <dbl>     <dbl>       <dbl>
## 1     1     34.1     118.         26.3
## 2     2     41.9     -87.6        22.1
## 3     3     34.1     112.         16.7

An artificial or surrogate key is a variable deliberately added to a dataset whose entries are all unique, thereby making it a key.

Questions

Consider the following tibbles.

t1 <- tibble(
  emp_id = c(1001, 1116, 1239),
  class = c("temp", "full", "full")
)
t2 <- tibble(
  emp_id = c(1001, 1211, 1239, 1543),
  comp = c(16232, 42003, 51522, 44023)
)

  1. What is the primary key in t1?

  2. How can we add the information from the comp variable of t2 to the three observations in t1?

  3. How do we remove the observations from t1 that also appear as comp values in t2, without adding the comp variable to the result?

  1. The class variable contains repeats, so is not a key. The emp_id is clearly designed to be a key in the data set, and so it makes sense that
emp_id

is the primary key.

  1. We can use left_join to add this information. Of course, we do not have the comp value for employee number 1116, so that will result in a NA value.
t1 |> left_join(t2, by = "emp_id")
## # A tibble: 3 × 3
##   emp_id class  comp
##    <dbl> <chr> <dbl>
## 1   1001 temp  16232
## 2   1116 full     NA
## 3   1239 full  51522
  1. This is done via a semi_join, which is a filtering join that only keeps those observations where the key appears in both tables.
t1 |> semi_join(t2, by = "emp_id")
## # A tibble: 2 × 2
##   emp_id class
##    <dbl> <chr>
## 1   1001 temp 
## 2   1239 full

(Note the difference between this and an inner_join: the semi_join only filters observations, it does not add the comp variable to t1.)

Write code to add a surrogate key to the iris dataset built into R using the function row_number(). Name the new key number. Print out the first five rows of the new table.

This can be done with

iris |>
  mutate(number = row_number()) |>
  select(number, everything()) |>
  head(n = 5)
##   number Sepal.Length Sepal.Width Petal.Length Petal.Width Species
## 1      1          5.1         3.5          1.4         0.2  setosa
## 2      2          4.9         3.0          1.4         0.2  setosa
## 3      3          4.7         3.2          1.3         0.2  setosa
## 4      4          4.6         3.1          1.5         0.2  setosa
## 5      5          5.0         3.6          1.4         0.2  setosa