4.1 2つのテーブルを結合するverb

2つのテーブルを、共通するIDなどを用いて結合するためのverbがあります。テーブル内で、ある変数の値を用いることで行を一意に特定できる変数のことをキー（Key）と呼びます。その中で、特に設計者が最も好ましいものとして選択したものを主キー（Primary Key）と呼びます。例えば、以下のようなテーブル「users」であればIDはキーとなります。

usersテーブル

ID	gcode
A001	2
A002	1
A003	1
A004	2
A005	1

キーは単一の変数である必要はなく、複数変数の組み合わせによってキーとなることもあります。例えば、1日分の情報が1行となっているデータにおいて、年と月と日が異なる変数になっている場合には、3つの変数の組み合わせによってキーとなります。

上のテーブルで、gcodeは性別のコードを表していて、実際の性別の情報が以下のテーブル「gender」に格納されているとします。

genderテーブル

gcode	gender
1	女性
2	男性

このとき、usersテーブルにおけるgcodeは別のテーブルであるgenderテーブルのキーとなっており、usersテーブルの外部キーと呼ばれます。2つのテーブルの結合多くの場合、この外部キーを利用して行われます。

dplyrパッケージにおいて、2つのテーブルxとyを結合するためのverbは、

xxxxx_join(x, y)

の形で用います。xxxxxの部分は以下のようなものがあります。

verb	結合方法
`inner_join()`	`x`の外部キーの値と`y`のキーの値が一致する`x`と`y`の行を結合して出力
`left_join()`	`inner_join()`で出力される行に加えて、`x`の外部キーの値が`y`のキー値のいずれにも一致しない`x`の行も出力される。これらの行に対応する`y`の変数の値は`NA`となる。
`right_join()`	`inner_join()`で出力される行に加えて、`y`の外部キーの値が`x`のキー値のいずれにも一致しない`x`の行も出力される。これらの行に対応する`x`の変数の値は`NA`となる。
`semi_join()`	`x`の外部キーの値と`y`のキーの値が一致する`x`の行を出力
`anti_join()`	`x`の外部キーの値と`y`のキーの値が一致しない`x`の行を出力。`semi_join()`の`x`に関する補集合になる。

これらの動作を確認するために、以下のようにサンプルデータを作成します。

users <- data.frame(
    id = paste0("A00", 1:6),
    gcode = c(2, 1, 1, 2, 1, 3)
)
gender <- data.frame(
    gcode = 1:2,
    gender = c("女性", "男性")
)
resp <- data.frame(
  id = paste0("A00", c(1, 2, 4, 5)),
  Q1 = c(5, 3, 3, 2),
  Q2 = c(2, 3, 5, 5)
)
answer <- data.frame(
  acode = 1:5,
  answer = c("全く当てはまらない", "当てはまらない", "どちらでもない",
             "当てはまる", "とても当てはまる")
)

inner_join()関数でusersのidとrespのidが一致するusersの行とrespの行を結合したものを出力します。

inner_join(users, resp)

## Joining, by = "id"

##     id gcode Q1 Q2
## 1 A001     2  5  2
## 2 A002     1  3  3
## 3 A004     2  3  5
## 4 A005     1  2  5

left_join()関数でusersの全ての行に、respのidが一致するrespの行を結合したものを出力します。usersの行のうち、respのidと一致しない行については、respの列の値がNAとなります。

left_join(users, resp)

## Joining, by = "id"

##     id gcode Q1 Q2
## 1 A001     2  5  2
## 2 A002     1  3  3
## 3 A003     1 NA NA
## 4 A004     2  3  5
## 5 A005     1  2  5
## 6 A006     3 NA NA

semi_join()関数は、inner_join()関数と同様にusersのidとrespのidが一致するusersの行を出力しますが、対応するrespの結合は行われません。

semi_join(users, resp)

## Joining, by = "id"

##     id gcode
## 1 A001     2
## 2 A002     1
## 3 A004     2
## 4 A005     1

anti_join()関数は、usersのidとrespのidが一致しないusersの行を出力します。

anti_join(users, resp)

## Joining, by = "id"

##     id gcode
## 1 A003     1
## 2 A006     3

これらの関数をパイプで繋ぐことで、以下のような出力を得ることができます。left_join()関数の引数byは、外部キーと参照先のキーの変数名が異なる場合に指定します。指定しなければ、同じ変数名の列が外部キーおよび参照先のキー列として処理されます。rename()関数は、列名を変更するためのものです。

left_join(users, gender) %>% 
  left_join(resp) %>% 
  left_join(answer, by = c("Q1" = "acode")) %>% 
  rename(q1a = answer) %>% 
  left_join(answer, by = c("Q2" = "acode")) %>% 
  rename(q2a = answer) %>% 
  select(id, gender, ends_with("a"))

## Joining, by = "gcode"

## Joining, by = "id"

##     id gender              q1a              q2a
## 1 A001   男性 とても当てはまる   当てはまらない
## 2 A002   女性   どちらでもない   どちらでもない
## 3 A003   女性             <NA>             <NA>
## 4 A004   男性   どちらでもない とても当てはまる
## 5 A005   女性   当てはまらない とても当てはまる
## 6 A006   <NA>             <NA>             <NA>

nycflights13パッケージには、flightsデータの他に、空港の情報が集約されたairportsデータが含まれています。例えば、空港ごとの遅延に関する要約を地図上に可視化したいような場合、空港の緯度経度の情報はairportsの方に含まれているため、これら両方のテーブルを結合したうえで分析する必要があります。

flights %>% 
    group_by(dest) %>% 
    summarise(
        arr_delay = mean(arr_delay, na.rm = TRUE),
        n = n(), .groups = "drop"
    ) %>%
    left_join(airports, by = c("dest" = "faa"))

## # A tibble: 105 x 10
##    dest  arr_delay     n name              lat    lon   alt    tz dst   tzone   
##    <chr>     <dbl> <int> <chr>           <dbl>  <dbl> <dbl> <dbl> <chr> <chr>   
##  1 ABQ        4.38   254 Albuquerque In~  35.0 -107.   5355    -7 A     America~
##  2 ACK        4.85   265 Nantucket Mem    41.3  -70.1    48    -5 A     America~
##  3 ALB       14.4    439 Albany Intl      42.7  -73.8   285    -5 A     America~
##  4 ANC       -2.5      8 Ted Stevens An~  61.2 -150.    152    -9 A     America~
##  5 ATL       11.3  17215 Hartsfield Jac~  33.6  -84.4  1026    -5 A     America~
##  6 AUS        6.02  2439 Austin Bergstr~  30.2  -97.7   542    -6 A     America~
##  7 AVL        8.00   275 Asheville Regi~  35.4  -82.5  2165    -5 A     America~
##  8 BDL        7.05   443 Bradley Intl     41.9  -72.7   173    -5 A     America~
##  9 BGR        8.03   375 Bangor Intl      44.8  -68.8   192    -5 A     America~
## 10 BHM       16.9    297 Birmingham Intl  33.6  -86.8   644    -6 A     America~
## # ... with 95 more rows

また、weatherデータには、出発地（EWR：ニューアークリバティー国際空港）における時間ごとの温度、湿度、風速、降雨量などの情報が含まれています。これらの気象条件と飛行機の遅延に関する分析を行いたいような場合でも、テーブルの結合処理が必要となります。

flights %>% 
  group_by(year, month, day, hour) %>% 
  summarise(
    arr_delay = mean(arr_delay, na.rm = TRUE),
    n = n(), .groups = "drop"
  ) %>% 
  left_join(weather)

## Joining, by = c("year", "month", "day", "hour")

## # A tibble: 20,720 x 17
##     year month   day  hour arr_delay     n origin  temp  dewp humid wind_dir
##    <int> <int> <int> <dbl>     <dbl> <int> <chr>  <dbl> <dbl> <dbl>    <dbl>
##  1  2013     1     1     5      9        6 EWR     39.0  28.0  64.4      260
##  2  2013     1     1     5      9        6 JFK     39.0  27.0  61.6      260
##  3  2013     1     1     5      9        6 LGA     39.9  25.0  54.8      250
##  4  2013     1     1     6      3.78    52 EWR     37.9  28.0  67.2      240
##  5  2013     1     1     6      3.78    52 JFK     37.9  27.0  64.3      260
##  6  2013     1     1     6      3.78    52 LGA     39.9  25.0  54.8      260
##  7  2013     1     1     7      3.76    49 EWR     39.0  28.0  64.4      240
##  8  2013     1     1     7      3.76    49 JFK     39.0  28.0  64.4      260
##  9  2013     1     1     7      3.76    49 LGA     39.9  26.1  57.3      250
## 10  2013     1     1     8     -2.40    58 EWR     39.9  28.0  62.2      250
## # ... with 20,710 more rows, and 6 more variables: wind_speed <dbl>,
## #   wind_gust <dbl>, precip <dbl>, pressure <dbl>, visib <dbl>,
## #   time_hour <dttm>