Este es un seguimiento deesta pregunta, donde la respuesta aceptada mostró un ejemplo de un ejercicio de emparejamiento usandodata.table, incluidas las condiciones no equi.

Antecedentes

La configuración básica es que tenemosDT1 con una muestra de detalles de las personas, yDT2, que es una especie de base de datos maestra. Y el objetivo es averiguar si cada persona enDT1 coincide con al menos una entrada enDT2.

Primero, inicializamos una columna que indicaría una coincidencia conFALSE, para que sus valores puedan actualizarse aTRUE cada vez que se encuentra una coincidencia.

DT1[, MATCHED := FALSE]

La siguiente solución general se utiliza para actualizar la columna:

DT1[, MATCHED := DT2[.SD, on=.(Criteria), .N, by=.EACHI ]$N > 0L ]

En teoría, se ve (y debería funcionar) bien. La subexpresiónDT2[.SD, on=.(Criteria), .N, by=.EACHI] produce una subtabla con cada fila deDT1y calcula elN columna, que es el número de coincidencias para esa fila encontrada enDT2. Entonces, cuando seaN es mayor que cero, el valor deMATCHED enDT1 se actualiza aTRUE.

Funciona según lo previsto en un trivialejemplo reproducible. Pero encontré un comportamiento inesperado al usarlo con los datos reales, y no puedo llegar al fondo. Me puede faltar algo o puede ser un error. Desafortunadamente, no puedo proporcionar un ejemplo reproducible mínimo, porque los datos son grandes y solo se muestran en los datos grandes. Pero intentaré documentarlo lo mejor que pueda.

Comportamiento inesperado o un error

Lo que ayudó a notar esto es que, por una razón histórica, las coincidencias debían buscarse en dos bases de datos separadas y, por lo tanto, el filtro!(MATCHED) se agregó a la expresión para actualizar solo aquellos valores que aún no se han encontrado:

DT1[!(MATCHED), MATCHED := DT2[.SD, on=.(Criteria), .N, by=.EACHI ]$N > 0L ]

Luego noté que si la línea se vuelve a ejecutar varias veces, con cada ejecución posterior, habrá más y más coincidencias, que no se igualaron en las ejecuciones anteriores. (Nada que ver con bases de datos separadas, cada ejecución coincide con DT2).

Primer intento:

   MATCHED       N
1:   FALSE 3248007
2:    TRUE 2379514

Segunda carrera:

   MATCHED       N
1:   FALSE 2149648
2:    TRUE 3477873

Para investigar, luego filtré los casos que no coincidían en la primera ejecución, pero sí en la segunda. Parece que la mayoría de los casos fueron falsos negativos, es decir, aquellos que deberían haberse emparejado en la primera ejecución, pero no lo fueron. (Pero con muchas ejecuciones, eventualmente también aparecen muchos falsos positivos).

Por ejemplo, aquí hay una entrada deDT1:

         DATE FORENAME SURNAME
1: 2016-01-01     JOHN   SMITH

Y una entrada coincidente de DT2:

   START_DATE EXPIRY_DATE FORENAME SURNAME
1: 2015-09-09  2017-05-01     JOHN   SMITH

Ejecutando la subexpresión (descrita anteriormente) sola, fuera de la expresión principal, para verN números, vemos que no da como resultado una coincidencia, cuando debería (N=0) (También puede notar queSTART_DATE yEND_DATE asumir el valor deDATE en la salida, pero ese es un problema completamente diferente).

SUB <- DF2[DF1, on=.(FORENAME, SURNAME, START_DATE <= DATE, EXPIRY_DATE >= DATE), .N, by=.EACHI]
SUB[FORENAME=="JOHN" & "SURNAME=="SMITH"]

   FORENAME SURNAME START_DATE EXPIRY_DATE N
1:     JOHN   SMITH 2016-01-01  2016-01-01 0

Sin embargo, el comportamiento defectuoso es que el resultado se ve afectado por lo que otras filas están presentes enDF1. Por ejemplo, supongamos que sé queJOHN SMITHnúmero de fila enDF1 es 149 y filtroDF1 solo a esa fila:

DF2[DF1[149], on=.(FORENAME, SURNAME, START_DATE <= DATE, EXPIRY_DATE >= DATE), .N, by=.EACHI]

   FORENAME SURNAME START_DATE EXPIRY_DATE N
1:     JOHN   SMITH 2016-01-01  2016-01-01 1

En segundo lugar, también noté que el comportamiento con errores ocurre solo con más de un criterio no equi en las condiciones. Si las condiciones sonon=.(FORENAME, SURNAME, START_DATE <= DATE), ya no hay diferencias entre las ejecuciones y todas las filas parecen coincidir correctamente la primera vez.

Desafortunadamente, para resolver el problema del mundo real, yodebe tener varias condiciones de coincidencia no equi. No solo para asegurar queDT1'sDATE está entreDT2'sSTART_DATE yEND_DATEs, pero también queDT1'sCHECKING_DATE es antesDT2'sEFFECTIVE_DATEetc.

Para resumir

No equi se une endata.table comportarse de manera defectuosa cuando:

Algunas filas están presentes / ausentes en una de las tablas

Y

Más de una condición no equi

Actualización: ejemplo reproducible

set.seed(123)
library(data.table)
library(stringi)

n <- 100000

DT1 <- data.table(RANDOM_STRING = stri_rand_strings(n, 5, pattern = "[a-k]"),
                  DATE = sample(seq(as.Date('2016-01-01'), as.Date('2016-12-31'), by="day"), n, replace=T))

DT2 <- data.table(RANDOM_STRING = stri_rand_strings(n, 5, pattern = "[a-k]"),
                  START_DATE = sample(seq(as.Date('2015-01-01'), as.Date('2017-12-31'), by="day"), n, replace=T))

DT2[, EXPIRY_DATE := START_DATE + floor(runif(1000, 200,300))]

#Initialization
DT1[, MATCHED := FALSE]

#First run
DT1[!(MATCHED), MATCHED := DT2[.SD, on=.(RANDOM_STRING, START_DATE <= DATE, EXPIRY_DATE >= DATE), .N, by=.EACHI ]$N > 0L ]
DT1[, .N, by=MATCHED]

   MATCHED     N
1:   FALSE 85833
2:    TRUE 14167

#Second run
DT1[!(MATCHED), MATCHED := DT2[.SD, on=.(RANDOM_STRING, START_DATE <= DATE, EXPIRY_DATE >= DATE), .N, by=.EACHI ]$N > 0L ]
DT1[, .N, by=MATCHED]

   MATCHED     N
1:   FALSE 73733
2:    TRUE 26267

#And so on with subsequent runs...