Combinación de tabla de datos basada en intervalos de fechas

Tengo dos tablas, policies y claims

 policies policies policyNumber EFDT EXDT 1: 123 2012-01-01 2013-01-01 2: 123 2013-01-01 2014-01-01 3: 124 2013-01-01 2014-01-01 4: 125 2013-02-01 2014-02-01 claims claims claimNumber policyNumber lossDate claimAmount 1: 1 123 2012-02-01 10 2: 2 123 2012-08-15 20 3: 3 123 2013-01-01 20 4: 4 124 2013-10-31 15 

La tabla de políticas realmente contiene términos de política, ya que cada fila se identifica de manera única por un número de política junto con una fecha de vigencia.

Quiero fusionar las dos tablas de una manera que asocie reclamos con términos de política. Un reclamo está asociado con un término de póliza si tiene el mismo número de póliza y la fecha de pérdida del reclamo cae dentro de la fecha de vigencia y expiración del término de póliza (las fechas de vigencia son límites inclusivos y las fechas de vencimiento son límites exclusivos). Combino las tablas de esta manera?

Esto debería ser similar a una combinación externa izquierda. El resultado debería verse como

  policyNumber EFDT EXDT claimNumber lossDate claimAmount 1: 123 2012-01-01 2013-01-01 1 2012-02-01 10 2: 123 2012-01-01 2013-01-01 2 2012-08-15 20 3: 123 2013-01-01 2014-01-01 3 2013-01-01 20 4: 124 2013-01-01 2014-01-01 4 2013-10-31 15 5: 125 2013-02-01 2014-02-01 NA  NA 

Versión 1 (actualizada para data.table v1.9.4 +)

Prueba esto:

 # Policies table; I've added policyNumber 126: policies<-data.table(policyNumber=c(123,123,124,125,126), EFDT=as.Date(c("2012-01-01","2013-01-01","2013-01-01","2013-02-01","2013-02-01")), EXDT=as.Date(c("2013-01-01","2014-01-01","2014-01-01","2014-02-01","2014-02-01"))) # Claims table; I've added two claims for 126 that are before and after the policy dates: claims<-data.table(claimNumber=c(1,2,3,4,5,6), policyNumber=c(123,123,123,124,126,126), lossDate=as.Date(c("2012-2-1","2012-8-15","2013-1-1","2013-10-31","2012-06-01","2014-03-01")), claimAmount=c(10,20,20,15,5,25)) # Set the keys for policies and claims so we can join them: setkey(policies,policyNumber,EFDT) setkey(claims,policyNumber,lossDate) # Join the tables using roll # ans<-policies[claims,list(EFDT,EXDT,claimNumber,lossDate,claimAmount,inPolicy=F),roll=T][,EFDT:=NULL] ## This worked with earlier versions of data.table, but broke when they updated the by-without-by behavior... ans<-policies[claims,list(.EFDT=EFDT,EXDT,claimNumber,lossDate,claimAmount,inPolicy=F),by=.EACHI,roll=T][,`:=`(EFDT=.EFDT, .EFDT=NULL)] # The claim should have inPolicy==T where lossDate is between EFDT and EXDT: ans[lossDate>=EFDT & lossDate<=EXDT, inPolicy:=T] # Set the keys again, but this time we'll join on both dates: setkey(ans,policyNumber,EFDT,EXDT) setkey(policies,policyNumber,EFDT,EXDT) # Union the ans table with policies that don't have any claims: ans<-rbindlist(list(ans, ans[policies][is.na(claimNumber)])) ans # policyNumber EFDT EXDT claimNumber lossDate claimAmount inPolicy #1: 123 2012-01-01 2013-01-01 1 2012-02-01 10 TRUE #2: 123 2012-01-01 2013-01-01 2 2012-08-15 20 TRUE #3: 123 2013-01-01 2014-01-01 3 2013-01-01 20 TRUE #4: 124 2013-01-01 2014-01-01 4 2013-10-31 15 TRUE #5: 126   5 2012-06-01 5 FALSE #6: 126 2013-02-01 2014-02-01 6 2014-03-01 25 FALSE #7: 125 2013-02-01 2014-02-01 NA  NA NA 

Versión 2

@Arun sugirió usar la nueva función data.table de data.table . Mi bash a continuación parece más difícil, no más fácil, así que háganmelo saber cómo mejorarlo.

 ## The foverlaps function requires both tables to have a start and end range, and the "y" table to be keyed claims[, lossDate2:=lossDate] ## Add a redundant lossDate column to use as the end range for claims setkey(policies, policyNumber, EFDT, EXDT) ## Set the key for policies ("y" table) ## Find the overlaps, remove the redundant lossDate2 column, and add the inPolicy column: ans2 <- foverlaps(claims, policies, by.x=c("policyNumber", "lossDate", "lossDate2"))[, `:=`(inPolicy=T, lossDate2=NULL)] ## Update rows where the claim was out of policy: ans2[is.na(EFDT), inPolicy:=F] ## Remove duplicates (such as policyNumber==123 & claimNumber==3), ## and add policies with no claims (policyNumber==125): setkey(ans2, policyNumber, claimNumber, lossDate, EFDT) ## order the results setkey(ans2, policyNumber, claimNumber) ## set the key to identify unique values ans2 <- rbindlist(list( unique(ans2), ## select only the unique values policies[!.(ans2[, unique(policyNumber)])] ## policies with no claims ), fill=T) ans2 ## policyNumber EFDT EXDT claimNumber lossDate claimAmount inPolicy ## 1: 123 2012-01-01 2013-01-01 1 2012-02-01 10 TRUE ## 2: 123 2012-01-01 2013-01-01 2 2012-08-15 20 TRUE ## 3: 123 2012-01-01 2013-01-01 3 2013-01-01 20 TRUE ## 4: 124 2013-01-01 2014-01-01 4 2013-10-31 15 TRUE ## 5: 126   5 2012-06-01 5 FALSE ## 6: 126   6 2014-03-01 25 FALSE ## 7: 125 2013-02-01 2014-02-01 NA  NA NA 

Versión 3

Usando foverlaps() , otra versión:

 require(data.table) ## 1.9.4+ setDT(claims)[, lossDate2 := lossDate] setDT(policies)[, EXDTclosed := EXDT-1L] setkey(claims, policyNumber, lossDate, lossDate2) foverlaps(policies, claims, by.x=c("policyNumber", "EFDT", "EXDTclosed")) 

foverlaps() requiere intervalos / intervalos de inicio y fin . Por lo tanto, lossDate columna de lossDate en lossDate2 .

Como EXDT necesita ser un intervalo abierto, restamos uno de él y lo colocamos en una nueva columna EXDTclosed .

Ahora, establecemos la clave. foverlaps() requiere que las últimas dos columnas clave sean intervalos. Entonces están especificados al final. Y también queremos superponer join para la primera coincidencia por policyNumber . Por lo tanto, también está especificado en la clave.

Necesitamos establecer la clave en los claims (verifique ?foverlaps ). No tenemos que establecer claves en las policies . Pero puede by.x si lo desea (luego puede omitir el argumento by.x ya que de forma predeterminada toma el valor de la clave). Como no establecemos la clave para las policies aquí, especificaremos explícitamente las columnas correspondientes en el argumento by.x El tipo de superposición por defecto es any , que no tenemos que cambiar (y, por lo tanto, no se especifica). Esto resulta en:

 # policyNumber claimNumber lossDate claimAmount lossDate2 EFDT EXDT EXDTclosed # 1: 123 1 2012-02-01 10 2012-02-01 2012-01-01 2013-01-01 2012-12-31 # 2: 123 2 2012-08-15 20 2012-08-15 2012-01-01 2013-01-01 2012-12-31 # 3: 123 3 2013-01-01 20 2013-01-01 2013-01-01 2014-01-01 2013-12-31 # 4: 124 4 2013-10-31 15 2013-10-31 2013-01-01 2014-01-01 2013-12-31 # 5: 125 NA  NA  2013-02-01 2014-02-01 2014-01-31 

Creo que esto hace más que nada lo que quieres. Necesito correr, así que no tengo tiempo para agregar la política sin reclamos y limpiar las columnas, pero creo que se abordan los problemas difíciles:

 setkey(policies, policyNumber, EXDT) policies[, EXDT2:=EXDT] policies[claims[, list( policyNumber, lossDate, lossDate, claimNumber, claimAmount)], roll=-Inf] # policyNumber EXDT EFDT EXDT2 lossDate claimNumber claimAmount # 1: 123 2012-02-01 2012-01-01 2013-01-01 2012-02-01 1 10 # 2: 123 2012-08-15 2012-01-01 2013-01-01 2012-08-15 2 20 # 3: 123 2013-01-01 2012-01-01 2013-01-01 2013-01-01 3 20 # 4: 124 2013-10-31 2013-01-01 2014-01-01 2013-10-31 4 15 

Además, tenga en cuenta que es trivial eliminar / resaltar las reclamaciones fuera de las fechas de política de este resultado.