Scalanie tabeli danych na podstawie zakresów dat
Mam dwie tabele, policies i claims
policies<-data.table(policyNumber=c(123,123,124,125),
EFDT=as.Date(c("2012-1-1","2013-1-1","2013-1-1","2013-2-1")),
EXDT=as.Date(c("2013-1-1","2014-1-1","2014-1-1","2014-2-1")))
> 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<-data.table(claimNumber=c(1,2,3,4),
policyNumber=c(123,123,123,124),
lossDate=as.Date(c("2012-2-1","2012-8-15","2013-1-1","2013-10-31")),
claimAmount=c(10,20,20,15))
> 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
Tabela zasad naprawdę zawiera zasady-warunki, ponieważ każdy wiersz jest jednoznacznie identyfikowany przez numer polityki wraz z datą wejścia w życie.
Chcę połączyć te dwie tabele w sposób, który kojarzy roszczenia z polisy-terms. Roszczenie jest powiązane z okresem obowiązywania polisy, jeśli ma ten sam numer polisy, a data utraty roszczenia mieści się w dacie wejścia w życie i wygaśnięcia okresu obowiązywania polisy (w tym daty wejścia w życie ograniczenia i daty ważności są wyłącznymi ograniczeniami.) Jak połączyć tabele w ten sposób?
Powinno to być podobne do lewego połączenia zewnętrznego. Wynik powinien wyglądać tak:
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> NA
2 answers
Wersja 1 (Aktualizacja danych.tabela v1.9.4+)
Spróbuj tego:
# 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 <NA> <NA> 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 NA
Wersja 2
@Arun zasugerował użycie nowej funkcji foverlaps z data.table. Moja próba poniżej wydaje się trudniejsza, a nie łatwiejsza, więc proszę dać mi znać, jak ją poprawić.
## 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 <NA> <NA> 5 2012-06-01 5 FALSE
## 6: 126 <NA> <NA> 6 2014-03-01 25 FALSE
## 7: 125 2013-02-01 2014-02-01 NA <NA> NA NA
Wersja 3
Używając foverlaps(), inna wersja:
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() wymaga zarówno początku i końca zakresów/interwałów. Dlatego duplikujemy lossDate kolumna na lossDate2.
Ponieważ EXDT musi być przedział otwarty, odejmujemy jeden z nich i umieszczamy go w nowej kolumnie EXDTclosed.
foverlaps() wymaga, aby ostatnie dwie kluczowe kolumny były przedziałami. Więc są określone jako ostatnie. Chcemy również, aby nakładające się join do pierwszego dopasowania przez policyNumber. Stąd też jest on również określony w kluczu.
Musimy ustawić klucz na claims (sprawdź ?foverlaps). Nie musimy ustawiać klucza policies. Ale możesz, jeśli chcesz (wtedy możesz pominąć by.x argument, ponieważ domyślnie przyjmuje wartość klucza). Ponieważ nie ustawiamy tutaj klucza dla policies, określimy jawnie odpowiednie kolumny w argumencie by.x. Typ zakładki Domyślnie to any, którego nie musimy zmieniać(i dlatego nie jest określony). To powoduje:
# 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> NA <NA> 2013-02-01 2014-02-01 2014-01-31
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-10-28 22:06:15
Myślę, że to robi to, co chcesz. Muszę uruchomić, więc nie mam czasu, aby dodać polisę bez roszczeń i wyczyścić kolumny, ale myślę, że trudne kwestie są rozwiązane: {]}
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
Należy również pamiętać, że usuwanie/podkreślanie roszczeń poza datami zasad jest trywialne z tego wyniku.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-10-23 00:24:12