sql - dplyr left_join por menor que, mayor que condición
postgresql left-join (4)
Esta pregunta está relacionada de alguna manera con problemas Combinando eficientemente dos marcos de datos en un criterio no trivial y Comprobando si la fecha está entre dos fechas en r . Y el que he publicado aquí solicitando si existe la función: problema de GitHub
Estoy buscando unir dos marcos de datos usando
dplyr::left_join()
.
La condición que uso para unirme es menor que, mayor que, es decir,
<=
y
>
.
¿
dplyr::left_join()
admite esta función?
o las teclas solo toman
=
operador entre ellas.
Esto es sencillo de ejecutar desde SQL (suponiendo que tenga el marco de datos en la base de datos)
Aquí hay un MWE: tengo dos conjuntos de datos, uno anual (
fdata
), mientras que el segundo es una especie de datos de encuestas que ocurren una vez cada cinco años.
Entonces, para todos los años en la
fdata
que se encuentran entre dos años de la encuesta, me uno a los datos correspondientes del año de la encuesta.
id <- c(1,1,1,1,
2,2,2,2,2,2,
3,3,3,3,3,3,
5,5,5,5,
8,8,8,8,
13,13,13)
fyear <- c(1998,1999,2000,2001,1998,1999,2000,2001,2002,2003,
1998,1999,2000,2001,2002,2003,1998,1999,2000,2001,
1998,1999,2000,2001,1998,1999,2000)
byear <- c(1990,1995,2000,2005)
eyear <- c(1995,2000,2005,2010)
val <- c(3,1,5,6)
sdata <- tbl_df(data.frame(byear, eyear, val))
fdata <- tbl_df(data.frame(id, fyear))
test1 <- left_join(fdata, sdata, by = c("fyear" >= "byear","fyear" < "eyear"))
yo obtengo
Error: cannot join on columns ''TRUE'' x ''TRUE'': index out of bounds
A menos que
left_join
pueda manejar la condición, pero a mi sintaxis le falta algo.
Parece que es el tipo de tarea que empaqueta direcciones fuzzyjoin . Las diversas funciones del paquete se ven y funcionan de manera similar a las funciones de unión de dplyr .
En este caso, una de las funciones de
fuzzy_*_join
funcionará para usted.
La principal diferencia entre
dplyr::left_join
y
fuzzyjoin::fuzzy_left_join
es que proporciona una lista de funciones para usar en el proceso de coincidencia con el argumento
match.fun
.
Tenga en cuenta que el argumento
by
todavía se escribe igual que en
left_join
.
A continuación se muestra un ejemplo.
Las funciones que solía hacer coincidir son
>=
y
<
para las
fyear
a
byear
y de
fyear
a
eyear
, respectivamente.
los
library(data.table) #v>=1.9.8
setDT(sdata); setDT(fdata) # converting to data.table in place
fdata[sdata, on = .(fyear >= byear, fyear < eyear), nomatch = 0,
.(id, x.fyear, byear, eyear, val)]
# id x.fyear byear eyear val
# 1: 1 1998 1995 2000 1
# 2: 2 1998 1995 2000 1
# 3: 3 1998 1995 2000 1
# 4: 5 1998 1995 2000 1
# 5: 8 1998 1995 2000 1
# 6: 13 1998 1995 2000 1
# 7: 1 1999 1995 2000 1
# 8: 2 1999 1995 2000 1
# 9: 3 1999 1995 2000 1
#10: 5 1999 1995 2000 1
#11: 8 1999 1995 2000 1
#12: 13 1999 1995 2000 1
#13: 1 2000 2000 2005 5
#14: 2 2000 2000 2005 5
#15: 3 2000 2000 2005 5
#16: 5 2000 2000 2005 5
#17: 8 2000 2000 2005 5
#18: 13 2000 2000 2005 5
#19: 1 2001 2000 2005 5
#20: 2 2001 2000 2005 5
#21: 3 2001 2000 2005 5
#22: 5 2001 2000 2005 5
#23: 8 2001 2000 2005 5
#24: 2 2002 2000 2005 5
#25: 3 2002 2000 2005 5
#26: 2 2003 2000 2005 5
#27: 3 2003 2000 2005 5
# id x.fyear byear eyear val
Una opción es unir las filas como una columna de lista y luego anular la columna:
# evaluate each row individually
fdata %>% rowwise() %>%
# insert list column of single row of sdata based on conditions
mutate(s = list(sdata %>% filter(fyear >= byear, fyear < eyear))) %>%
# unnest list column
tidyr::unnest()
# Source: local data frame [27 x 5]
#
# id fyear byear eyear val
# (dbl) (dbl) (dbl) (dbl) (dbl)
# 1 1 1998 1995 2000 1
# 2 1 1999 1995 2000 1
# 3 1 2000 2000 2005 5
# 4 1 2001 2000 2005 5
# 5 2 1998 1995 2000 1
# 6 2 1999 1995 2000 1
# 7 2 2000 2000 2005 5
# 8 2 2001 2000 2005 5
# 9 2 2002 2000 2005 5
# 10 2 2003 2000 2005 5
# .. ... ... ... ... ...
Usa un
filter
.
(Pero tenga en cuenta que esta respuesta
no
produce una UNIÓN
LEFT JOIN
correcta; pero el MWE da el resultado correcto con una
INNER JOIN
).
El paquete
dplyr
no es
dplyr
si se le pide que
dplyr
dos tablas sin algo en lo que fusionarse, por lo que a continuación, hago una variable ficticia en ambas tablas para este propósito, luego filtro, luego suelto
dummy
:
fdata %>%
mutate(dummy=TRUE) %>%
left_join(sdata %>% mutate(dummy=TRUE)) %>%
filter(fyear >= byear, fyear < eyear) %>%
select(-dummy)
Y tenga en cuenta que si hace esto en PostgreSQL (por ejemplo), el optimizador de consultas ve a través de la variable
dummy
como lo demuestran las siguientes dos explicaciones de consulta:
> fdata %>%
+ mutate(dummy=TRUE) %>%
+ left_join(sdata %>% mutate(dummy=TRUE)) %>%
+ filter(fyear >= byear, fyear < eyear) %>%
+ select(-dummy) %>%
+ explain()
Joining by: "dummy"
<SQL>
SELECT "id" AS "id", "fyear" AS "fyear", "byear" AS "byear", "eyear" AS "eyear", "val" AS "val"
FROM (SELECT * FROM (SELECT "id", "fyear", TRUE AS "dummy"
FROM "fdata") AS "zzz136"
LEFT JOIN
(SELECT "byear", "eyear", "val", TRUE AS "dummy"
FROM "sdata") AS "zzz137"
USING ("dummy")) AS "zzz138"
WHERE "fyear" >= "byear" AND "fyear" < "eyear"
<PLAN>
Nested Loop (cost=0.00..50886.88 rows=322722 width=40)
Join Filter: ((fdata.fyear >= sdata.byear) AND (fdata.fyear < sdata.eyear))
-> Seq Scan on fdata (cost=0.00..28.50 rows=1850 width=16)
-> Materialize (cost=0.00..33.55 rows=1570 width=24)
-> Seq Scan on sdata (cost=0.00..25.70 rows=1570 width=24)
y hacerlo más limpiamente con SQL da exactamente el mismo resultado:
> tbl(pg, sql("
+ SELECT *
+ FROM fdata
+ LEFT JOIN sdata
+ ON fyear >= byear AND fyear < eyear")) %>%
+ explain()
<SQL>
SELECT "id", "fyear", "byear", "eyear", "val"
FROM (
SELECT *
FROM fdata
LEFT JOIN sdata
ON fyear >= byear AND fyear < eyear) AS "zzz140"
<PLAN>
Nested Loop Left Join (cost=0.00..50886.88 rows=322722 width=40)
Join Filter: ((fdata.fyear >= sdata.byear) AND (fdata.fyear < sdata.eyear))
-> Seq Scan on fdata (cost=0.00..28.50 rows=1850 width=16)
-> Materialize (cost=0.00..33.55 rows=1570 width=24)
-> Seq Scan on sdata (cost=0.00..25.70 rows=1570 width=24)
data.table
agrega
data.table
no equi a partir de v 1.9.8
library(fuzzyjoin)
fuzzy_left_join(fdata, sdata,
by = c("fyear" = "byear", "fyear" = "eyear"),
match_fun = list(`>=`, `<`))
Source: local data frame [27 x 5]
id fyear byear eyear val
(dbl) (dbl) (dbl) (dbl) (dbl)
1 1 1998 1995 2000 1
2 1 1999 1995 2000 1
3 1 2000 2000 2005 5
4 1 2001 2000 2005 5
5 2 1998 1995 2000 1
6 2 1999 1995 2000 1
7 2 2000 2000 2005 5
8 2 2001 2000 2005 5
9 2 2002 2000 2005 5
10 2 2003 2000 2005 5
.. ... ... ... ... ...
También puede hacer que esto funcione con
foverlaps
en 1.9.6 con un poco más de esfuerzo.