Joining in relational data models

How it works

A join is the combination of two tables based on shared information. In technical terms, we merge the tables that need to be directly connected by a foreign key relation.

The existing links can be inspected in two ways:

1. Visually, by drawing the data model with dm_draw()

dm %>%
  dm_draw()

The directed arrows show explicitly the relation between different columns.

2. Printed to the console by calling dm_get_all_fks()

dm %>%
  dm_get_all_fks()

Joining Examples

Let’s look at some examples:

Add a column with airline names from the airlines table to the flights table.

dm_joined <-
  dm %>%
  dm_flatten_to_tbl(flights, airlines, .join = left_join)
dm_joined

As you can see below, the dm_joined data frame has one more column than the flights table. The difference is the name column from the airlines table.

dm$flights %>%
  names()

dm$airlines %>%
  names()

dm_joined %>%
  names()

The result is not a dm object anymore, but a (tibble) data frame:

dm_joined %>%
  class()

Another example:

Get all flights that can’t be matched with airlines names.

We expect the flights data from {nycflights13} package to be clean and well organized, so no flights should remain. You can check this with an anti_join:

dm %>%
  dm_flatten_to_tbl(flights, airlines, .join = anti_join)

An example with filtering on a dm and then merging:

Get all May 2013 flights from Delta Air Lines which didn’t depart from John F. Kennedy International Airport in - and join all the airports data into the flights table.

dm_nycflights13(subset = FALSE) %>%
  dm_filter(
    airlines = (name == "Delta Air Lines Inc."),
    airports = (name != "John F Kennedy Intl"),
    flights = (month == 5)
  ) %>% 
  dm_flatten_to_tbl(flights, airports, .join = left_join)

See vignette("tech-dm-filter") for more details on filtering.

A last example:

Merge all tables into one big table.

Sometimes you need everything in one place. In this case, you can use the dm_flatten_to_tbl() function. It joins all the tables in your dm object together into one wide table. All you have to do is to specify the starting table. The following joins are determined by the foreign key links.

dm_nycflights13() %>%
  dm_select_tbl(-weather) %>%
  dm_flatten_to_tbl(.start = flights)

To be more precise, dm_flatten_to_tbl() will join all tables from one level of hierarchy (i.e., direct neighbors to table .start). If you want to cover tables from all levels of hierarchy, use the argument recursive = TRUE for dm_flatten_to_tbl() instead.

Also, be aware that all column names need to be unique. The dm_flatten_to_tbl() takes care of this by automatically renaming the relevant columns and informs the user if any names were changed, e.g. dm_rename(airlines, airlines.name = name).

If you want to merge all tables, but get a nested table in return, use dm_wrap_tbl() with pull_tbl() instead:

dm_nycflights13() %>%
  dm_wrap_tbl(root = flights) %>%
  pull_tbl(flights)