R(Python)のjoin条件で等号以外を使う
目的
下記のようなデータにおいて、Aのstart_dateからend_dateの期間でBをJOINしたい。
A = tibble(id = c(1,2,3), start_date = c('2018-01-01','2018-02-01','2018-03-01'), end_date = c('2018-01-02','2018-02-03','2018-03-01')) B = tibble(date = seq(as.Date('2018-01-01'), as.Date('2018-03-31'), by = 'day')) A # => # id start_date end_date # 1 1 2018-01-01 2018-01-02 # 2 2 2018-02-01 2018-02-03 # 3 3 2018-03-01 2018-03-01 head(B) # => # date # 1 2018-01-01 # 2 2018-01-02 # 3 2018-01-03 # 4 2018-01-04 # 5 2018-01-05 # 6 2018-01-06
SQLでは
INNER JOIN(A,B) ON A.date >= B.start_date AND A.date <= B.end_date
で可能だが、R/Pythonのinner_joinでは等号指定しか使えない
対応1
一度FULL JOINをおこない、filterで条件部分に当てはまるもののみ残す。
A %>% merge(B) %>% filter(start_date <= date & end_date >= date) %>% arrange(id) # => # id start_date end_date date # 1 1 2018-01-01 2018-01-02 2018-01-01 # 2 1 2018-01-01 2018-01-02 2018-01-02 # 3 2 2018-02-01 2018-02-03 2018-02-01 # 4 2 2018-02-01 2018-02-03 2018-02-02 # 5 2 2018-02-01 2018-02-03 2018-02-03 # 6 3 2018-03-01 2018-03-01 2018-03-01
対応2
こちらはRだけで可能だが、fuzzyjoinという、=
以外の、それこそ表記ゆれとかそういうレベルでも紐づけ対応ができるパッケージがあるらしい。
GitHub - dgrtwo/fuzzyjoin: Join tables together on inexact matching
fuzzyjoinパッケージでいい感じにjoin - Technically, technophobic.
library(fuzzyjoin) fuzzy_left_join(A, B, by = c("start_date" = "date", "end_date" = "date"), match_fun = list(`<=`, `>=`)) # => # id start_date end_date date # <dbl> <chr> <chr> <date> # 1 1 2018-01-01 2018-01-02 2018-01-01 # 2 1 2018-01-01 2018-01-02 2018-01-02 # 3 2 2018-02-01 2018-02-03 2018-02-01 # 4 2 2018-02-01 2018-02-03 2018-02-02 # 5 2 2018-02-01 2018-02-03 2018-02-03 # 6 3 2018-03-01 2018-03-01 2018-03-01
参考
sql - dplyr left_join by less than, greater than condition - Stack Overflow