The {dm} package offers functions to work with relational data models in R. A common task for multiple, separated tables that have a shared attribute is merging the data.
This document introduces you to the joining functions of {dm} and shows how to apply them using data from the {nycflights13} package.
Relational data models consist of multiple tables that are linked with foreign keys. They are the building blocks for joining tables. Read more about relational data models in the vignette “Introduction to Relational Data Models”.
First, we load the packages that we need:
To explore filtering with {dm}, we’ll use the {nycflights13} data
with its tables flights
, planes
,
airlines
and airports
.
This dataset contains information about the 336,776 flights that
departed from New York City in 2013, with 3,322 different planes and
1,458 airports involved. The data comes from the US Bureau of
Transportation Statistics, and is documented in
?nycflights13
.
First, we have to create a dm
object from the
{nycflights13} data. This is implemented with
dm_nycflights13()
.
A data model object contains the data as well as metadata.
If you would like to create a dm
from other tables,
please look at ?dm
and the function
new_dm()
.
dm
object{dm} allows you to join two tables of a dm
object based
on a shared column. You can use all join functions that you know from
the {dplyr} package. Currently {dplyr} supports four types of mutating
joins, two types of filtering joins, and a nesting join. See
?dplyr::join
for details.
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:
dm_draw()
The directed arrows show explicitly the relation between different columns.
dm_get_all_fks()
Let’s look at some examples:
Add a column with airline names from the
airlines
table to the flights
table.
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.
The result is not a dm
object anymore, but a (tibble)
data frame:
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
:
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.
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: