Довольно редко при анализе данных приходится иметь дело только с одно таблицей данных, на практике, обычно, у вас есть несколько таблиц, каждая из которых влияет на анализ, и вам необходимы инструменты, чтобы гибко комбинировать их:
(Дальнейшее обсуждение предполагает что вы располагаете аккуратными данными, то есть в строках располагаются наблюдения, а в колонках – переменные. Если вы не слишком знакомы с этим подходом, я рекомендую вам сперва почитать про него.)
Все действия над парами таблиц работают похожим образом. Они принимают аргументы x и y, представляющие комбинируемые таблицы. Возвращается всегда новая таблица того же типа что и x.
Модифицирующие соединения позволяют комбинировать переменные из различных таблиц. Например, возьмём данные из nycflights13. В одной таблице содержатся данные о рейсе и аббревиатура номера борта, а в другой – соответствие аббревиатур полным наименованиям. Вы можете использовать соединение чтобы добавить наименования бортов к данным рейса:
library("nycflights13")
# Удаляем незначащие переменные,
# так будет легче разобраться в результате соединения.
flights2 <- flights %>% select(year:day, hour, origin, dest, tailnum, carrier)
flights2 %>%
left_join(airlines)
#> Joining by: "carrier"| year | month | day | hour | origin | dest | tailnum | carrier | name |
|---|---|---|---|---|---|---|---|---|
| 2013 | 1 | 1 | 5 | EWR | IAH | N14228 | UA | United Air Lines Inc. |
| 2013 | 1 | 1 | 5 | LGA | IAH | N24211 | UA | United Air Lines Inc. |
| 2013 | 1 | 1 | 5 | JFK | MIA | N619AA | AA | American Airlines Inc. |
| 2013 | 1 | 1 | 5 | JFK | BQN | N804JB | B6 | JetBlue Airways |
| 2013 | 1 | 1 | 5 | LGA | ATL | N668DN | DL | Delta Air Lines Inc. |
| … | … | … | … | … | … | … | … | … |
Наравне с x и y любое объединение принимает аргумент by, который управляет тем, какие именно переменные будут использованы для сопоставления наблюдений между двумя таблицами. Существует несколько способов для их указания, как будет проиллюстрировано далее на примере различных таблиц из nycflights13:
NULL, используемый по умолчанию. dplyr будет пытаться использовать все совпадающие (по именам) переменные из обоих таблиц, т. н. естественное соединение. Например, таблицы рейсов и сводок погоды совпадают по их общим переменным: year (год), month (месяц), day (день), hour (час) и origin (место отправления).
flights2 %>% left_join(weather)
#> Joining by: c("year", "month", "day", "hour", "origin")| year | month | day | hour | origin | dest | tailnum | carrier | temp | dewp | humid | wind_dir |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 2013 | 1 | 1 | 5 | EWR | IAH | N14228 | UA | NA | NA | NA | NA |
| 2013 | 1 | 1 | 5 | LGA | IAH | N24211 | UA | NA | NA | NA | NA |
| 2013 | 1 | 1 | 5 | JFK | MIA | N619AA | AA | NA | NA | NA | NA |
| 2013 | 1 | 1 | 5 | JFK | BQN | N804JB | B6 | NA | NA | NA | NA |
| 2013 | 1 | 1 | 5 | LGA | ATL | N668DN | DL | NA | NA | NA | NA |
| … | … | … | … | … | … | … | … | … | … | … | … |
(Variables not shown: wind_speed (dbl), wind_gust (dbl), precip (dbl), pressure (dbl), visib (dbl))
Вектор строк, by = "x". Работает подобно естественному соединению, за тем исключением, что использует только заранее перечисленные имена переменных. Например, flights и planes содержат переменную с именем year, но она имеет различный смысл, поэтому мы будем выполнять соединение только по tailnum.
flights2 %>% left_join(planes, by = "tailnum")| year.x | month | day | hour | origin | dest | tailnum | carrier | year.y |
|---|---|---|---|---|---|---|---|---|
| 2013 | 1 | 1 | 5 | EWR | IAH | N14228 | UA | 1999 |
| 2013 | 1 | 1 | 5 | LGA | IAH | N24211 | UA | 1998 |
| 2013 | 1 | 1 | 5 | JFK | MIA | N619AA | AA | 1990 |
| 2013 | 1 | 1 | 5 | JFK | BQN | N804JB | B6 | 2012 |
| 2013 | 1 | 1 | 5 | LGA | ATL | N668DN | DL | 1991 |
| … | … | … | … | … | … | … | … | … |
(Variables not shown: type (chr), manufacturer (chr), model (chr), engines (int), seats (int), speed (int), engine (chr))
Note that the year columns in the output are disambiguated with a suffix.
Именованные вектор строк: by = c("x" = "a"), позволяет искать совпадение по переменной x в таблице x и переменной a таблице y. Обе переменные будут представлены в результирующей таблице.
У каждого рейса есть аэропорты вылета и прилёта (airport), так что нам понадобится определить по которому из них делать объединение:
flights2 %>% left_join(airports, c("dest" = "faa"))| year | month | day | hour | origin | dest | tailnum | carrier |
|---|---|---|---|---|---|---|---|
| 2013 | 1 | 1 | 5 | EWR | IAH | N14228 | UA |
| 2013 | 1 | 1 | 5 | LGA | IAH | N24211 | UA |
| 2013 | 1 | 1 | 5 | JFK | MIA | N619AA | AA |
| 2013 | 1 | 1 | 5 | JFK | BQN | N804JB | B6 |
| 2013 | 1 | 1 | 5 | LGA | ATL | N668DN | DL |
| … | … | … | … | … | … | … | … |
(Variables not shown: name (chr), lat (dbl), lon (dbl), alt (int), tz (dbl), dst (chr))
flights2 %>% left_join(airports, c("origin" = "faa"))| year | month | day | hour | origin | dest | tailnum | carrier | name |
|---|---|---|---|---|---|---|---|---|
| 2013 | 1 | 1 | 5 | EWR | IAH | N14228 | UA | Newark Liberty Intl |
| 2013 | 1 | 1 | 5 | LGA | IAH | N24211 | UA | La Guardia |
| 2013 | 1 | 1 | 5 | JFK | MIA | N619AA | AA | John F Kennedy Intl |
| 2013 | 1 | 1 | 5 | JFK | BQN | N804JB | B6 | John F Kennedy Intl |
| 2013 | 1 | 1 | 5 | LGA | ATL | N668DN | DL | La Guardia |
| … | … | … | … | … | … | … | … | … |
(Variables not shown: lat (dbl), lon (dbl), alt (int), tz (dbl), dst (chr))
Существует четыре вида модифицирующих объединений, отличающихся поведением в случае отсутствия совпадающих строк. Мы рассмотрим каждое на нескольких простых примерах:
(df1 <- data_frame(x = c(1, 2), y = 2:1))| x | y |
|---|---|
| 1 | 2 |
| 2 | 1 |
(df2 <- data_frame(x = c(1, 3), a = 10, b = "a"))| x | a | b |
|---|---|---|
| 1 | 10 | a |
| 3 | 10 | a |
inner_join(x, y) включает только наблюдения имеющие совпадения в обеих таблицах x и y.
df1 %>% inner_join(df2) %>% knitr::kable()
#> Joining by: "x"| x | y | a | b |
|---|---|---|---|
| 1 | 2 | 10 | a |
left_join(x, y) (левое соединение) включает все наблюдения из x, вне зависимости от того, есть ли для них совпадения. Это наиболее востребованный вид объединения, потому что он гарантирует, что вы не потеряете наблюдения из первой таблицы.
df1 %>% left_join(df2)
#> Joining by: "x"| x | y | a | b |
|---|---|---|---|
| 1 | 2 | 10 | a |
| 2 | 1 | NA | NA |
right_join(x, y) (правое соединение) включает все наблюдения из y. Это какleft_join(y, x), только наоборот.
df1 %>% right_join(df2)
#> Joining by: "x"| x | y | a | b |
|---|---|---|---|
| 1 | 2 | 10 | a |
| 3 | NA | 10 | a |
df2 %>% left_join(df1)
#> Joining by: "x"| x | a | b | y |
|---|---|---|---|
| 1 | 10 | a | 2 |
| 3 | 10 | a | NA |
full_join() (полное) включает все наблюдения из x и y.
df1 %>% full_join(df2)
#> Joining by: "x"| x | y | a | b |
|---|---|---|---|
| 1 | 2 | 10 | a |
| 2 | 1 | NA | NA |
| 3 | NA | 10 | a |
левое, правое и полное соединения также известны под общим именем внешние соединения. Если при вычислении внешнего соединения для строки не находится совпадений – новые переменные заполняются пустыми значениями.
Хотя, в основном, объединения используются для добавления новых переменных, они также могут породить и новые наблюдения. В тех случаях, когда находится более одного совпадающего наблюдения, происходит добавление всех возможных комбинаций (Декартово произведение) совпадающих наблюдений:
df1 <- data_frame(x = c(1, 1, 2), y = 1:3)
df2 <- data_frame(x = c(1, 1, 2), z = c("a", "b", "a"))
df1 %>% left_join(df2)
#> Joining by: "x"| x | y | z |
|---|---|---|
| 1 | 1 | a |
| 1 | 1 | b |
| 1 | 2 | a |
| 1 | 2 | b |
| 2 | 3 | a |
Фильтрующие объединения ищут совпадающие наблюдения также, как и модифицирующие, но воздействуют на наблюдения, а не на переменные:
semi_join(x, y) сохраняет все наблюдения из x для которых есть совпадения в y.anti_join(x, y) отбрасывает все наблюдения из x для которых есть совпадения в y.Они особенно полезны для поиска несовпадений при объединениях. Например, множество рейсов в наборе данных nycflights13 не имеют совпадений по номеру борта в таблице самолётов:
library("nycflights13")
flights %>%
anti_join(planes, by = "tailnum") %>%
count(tailnum, sort = TRUE)| tailnum | n |
|---|---|
| 2512 | |
| N725MQ | 575 |
| N722MQ | 513 |
| N723MQ | 507 |
| N713MQ | 483 |
| … | … |
Если вы сомневаетесь по поводу того какие наблюдения войдут в соединение, начните с semi_join() или anti_join(). semi_join() или anti_join() никогда не размножают, а только удаляют записи.
df1 <- data_frame(x = c(1, 1, 3, 4), y = 1:4)
df2 <- data_frame(x = c(1, 1, 2), z = c("a", "b", "a"))
# Вначале четыре строки:
df1 %>% nrow()
#> [1] 4
# И четыре строки после соединения
df1 %>% inner_join(df2, by = "x") %>% nrow()
#> [1] 4
# Но только два совпадения
df1 %>% semi_join(df2, by = "x") %>% nrow()
#> [1] 2Последний тип действий с двумя таблицами – операции со множествами. Они предполагают, что аргументы x и y имеют одинаковый набор переменных, и обращаются с наблюдениями как с элементами множеств:
intersect(x, y): возвращает только наблюдения, имеющиеся и в x, и в yunion(x, y): возвращает уникальные наблюдения из x или ysetdiff(x, y): возвращает наблюдения из x не содержащиеся в y.Для данных:
(df1 <- data_frame(x = 1:2, y = c(1L, 1L)))| x | y |
|---|---|
| 1 | 1 |
| 2 | 1 |
(df2 <- data_frame(x = 1:2, y = 1:2))| x | y |
|---|---|
| 1 | 1 |
| 2 | 2 |
Существуют следующие варианты:
intersect(df1, df2)| x | y |
|---|---|
| 1 | 1 |
# Обратите внимание, что возвращается 3 строки, а не 4
union(df1, df2)| x | y |
|---|---|
| 2 | 2 |
| 2 | 1 |
| 1 | 1 |
setdiff(df1, df2)| x | y |
|---|---|
| 2 | 1 |
setdiff(df2, df1)| x | y |
|---|---|
| 2 | 2 |
Каждое действие с двумя таблицами имеет свой SQL-эквивалент:
| R | SQL |
|---|---|
inner_join() |
SELECT * FROM x JOIN y ON x.a = y.a |
left_join() |
SELECT * FROM x LEFT JOIN y ON x.a = y.a |
right_join() |
SELECT * FROM x RIGHT JOIN y ON x.a = y.a |
full_join() |
SELECT * FROM x FULL JOIN y ON x.a = y.a |
semi_join() |
SELECT * FROM x WHERE EXISTS (SELECT 1 FROM y WHERE x.a = y.a) |
anti_join() |
SELECT * FROM x WHERE NOT EXISTS (SELECT 1 FROM y WHERE x.a = y.a) |
intersect(x, y) |
SELECT * FROM x INTERSECT SELECT * FROM y |
union(x, y) |
SELECT * FROM x UNION SELECT * FROM y |
setdiff(x, y) |
SELECT * FROM x EXCEPT SELECT * FROM y |
x и y не обязательно должны быть таблицами из одной базы данных. Если вы укажете copy = TRUE, dplyr скопирует данные из y в то же место, что и x. Это удобно, если вы загружаете итоговый набор данных и определяете область, для которой вы хотите сохранить данные в полном объёме. Вы можете вызвать semi_join(x, y, copy = TRUE) для загрузки индексов интересующих наблюдений в ту же базу данных, где находится x, и в дальнейшем выполнять эту операцию более эффективно.
Если вы работаете с большим объёмом данных, возможно, полезно будет установить auto_index = TRUE. Это автоматически приведёт к созданию индекса по полям, использующимся для соединения во временной таблице.
При объединении таблиц dplyr ведёт себя более консервативно, чем R в отношении типов сравниваемых переменных. Это может удивить при работе с факторами:
Факторы с различными уровнями будут преобразованы к строке с выдачей предупреждения:
df1 <- data_frame(x = 1, y = factor("a"))
df2 <- data_frame(x = 2, y = factor("b"))
full_join(df1, df2) %>% str()
#> Joining by: c("x", "y")
#> Warning: joining factors with different levels, coercing to character
#> vector
#> Classes 'tbl_df', 'tbl' and 'data.frame': 2 obs. of 2 variables:
#> $ x: num 1 2
#> $ y: chr "a" "b"Факторы с одинаковыми уровнями будут преобразованы к строке с выдачей предупреждения:
df1 <- data_frame(x = 1, y = factor("a", levels = c("a", "b")))
df2 <- data_frame(x = 2, y = factor("b", levels = c("b", "a")))
full_join(df1, df2) %>% str()
#> Joining by: c("x", "y")
#> Warning: joining factors with different levels, coercing to character
#> vector
#> Classes 'tbl_df', 'tbl' and 'data.frame': 2 obs. of 2 variables:
#> $ x: num 1 2
#> $ y: chr "a" "b"Факторы не будут преобразованы только в том случае, если они совпадают и по уровням и по порядку в точности:
df1 <- data_frame(x = 1, y = factor("a", levels = c("a", "b")))
df2 <- data_frame(x = 2, y = factor("b", levels = c("a", "b")))
full_join(df1, df2) %>% str()
#> Joining by: c("x", "y")
#> Classes 'tbl_df', 'tbl' and 'data.frame': 2 obs. of 2 variables:
#> $ x: num 1 2
#> $ y: Factor w/ 2 levels "a","b": 1 2При соединении по фактору и строке, фактор будет преобразован к строке с выдачей предупреждения:
df1 <- data_frame(x = 1, y = "a")
df2 <- data_frame(x = 2, y = factor("a"))
full_join(df1, df2) %>% str()
#> Joining by: c("x", "y")
#> Warning: joining factor and character vector, coercing into character
#> vector
#> Classes 'tbl_df', 'tbl' and 'data.frame': 2 obs. of 2 variables:
#> $ x: num 1 2
#> $ y: chr "a" "a"В остальных случаях, логические типы будут преобразованы в целочисленные без выдачи предупреждения, целочисленные – в числа с плавающей точкой, а попытка приведения к строке будет вызывать ошибку:
df1 <- data_frame(x = 1, y = 1L)
df2 <- data_frame(x = 2, y = 1.5)
full_join(df1, df2) %>% str()
#> Joining by: c("x", "y")
#> Classes 'tbl_df', 'tbl' and 'data.frame': 2 obs. of 2 variables:
#> $ x: num 1 2
#> $ y: num 1 1.5
df1 <- data_frame(x = 1, y = 1L)
df2 <- data_frame(x = 2, y = "a")
full_join(df1, df2) %>% str()
#> Joining by: c("x", "y")
#> Error in eval(expr, envir, enclos): cannot join on columns 'y' x 'y': Can't join on 'y' x 'y' because of incompatible types (character / integer)dplyr не предлагает функций для работы с тремя и более таблицами. Вместо этого предполагается использовать Reduce(), как описано в Advanced R, для интерактивного расширения двухтабличных действий на любое количество таблиц.