Объединение таблицы данных в соответствии с диапазонами дат

У меня есть две таблицы, policies и 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 

Таблица политик действительно содержит термины политики, поскольку каждая строка уникально идентифицируется номером политики вместе с датой вступления в силу.

Я хочу объединить две таблицы таким образом, чтобы ассоциировать заявки с политическими терминами. Требование связано с термином политики, если он имеет тот же номер политики, а убыток – в соответствии с датой вступления в силу и датой истечения срока действия политики (эффективные даты – это включенные ограничения и даты истечения срока действия являются исключительными ограничениями.) Я объединять таблицы таким образом?

Это должно быть похоже на левое внешнее соединение. Результат должен выглядеть так:

  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 

Версия 1 (обновлена ​​для data.table v1.9.4 +)

Попробуй это:

 # 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 

Версия 2

@Arun предложил использовать новую функцию data.table из data.table . Моя попытка ниже кажется сложнее, а не проще, поэтому, пожалуйста, дайте мне знать, как ее улучшить.

 ## 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 

Версия 3

Используя foverlaps() , другая версия:

 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() требует как начального, так и конечного диапазонов / интервалов. Поэтому мы дублируем столбец lossDate на lossDate2 .

Поскольку EXDT должен быть открытым интервалом, мы вычитаем его из него и EXDTclosed его в новый столбец EXDTclosed .

Теперь мы установили ключ. foverlaps() требует, чтобы последние два ключевых столбца были интервалами. Поэтому они указаны последним. И мы также хотим, чтобы совпадение было соединено с первым policyNumber . Следовательно, это также указано в ключе.

Нам нужно установить ключ на claims (check ?foverlaps ). Мы не должны устанавливать ключ для policies . Но вы можете, если хотите (тогда вы можете пропустить аргумент by.x поскольку он по умолчанию принимает значение ключа). Поскольку мы не устанавливаем ключ для policies здесь, мы будем явно указывать соответствующие столбцы в аргументе by.x Тип перекрытия по умолчанию – это any , который нам не нужно изменять (и, следовательно, не указывать). Это приводит к:

 # 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 

Я думаю, что это в основном то, что вы хотите. Мне нужно бежать, так что не успевайте добавить политику без претензий и очистить столбцы, но я думаю, что сложные проблемы решаются:

 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 

Кроме того, обратите внимание, что тривиально удалить / выделить претензии за пределами дат политики из этого результата.

  • Почему суммировать или мутировать не работать с group_by при загрузке `plyr` после` dplyr`?
  • Как избежать предупреждения при введении НС путем принуждения
  • Ошибка в : цель присвоения расширяется до неязыкового объекта
  • Как просмотреть данные из файла .RData?
  • Добавление уравнения регрессионной линии и R2 на графике
  • R: ускорение операций «по группам»
  • Переопределить функцию, импортированную в пространстве имен
  • Создайте порядковый номер (счетчик) для строк внутри каждой группы данных (дубликат)
  • объединить data.frames в зависимости от года и заполнить отсутствующие значения
  • Причины использования функции set.seed
  • LDA с topicmodels, как я могу видеть, к каким темам относятся разные документы?
  • Interesting Posts

    Формат валюты Java Java

    Мой жесткий диск был заведомо перезаписан двумя плохими разделами

    Указатель мыши в моей виртуальной машине Ubuntu превратился в небольшую руку с документом, а клики игнорируются. Как выйти из этого режима?

    Использование scanf () в программах на C ++ быстрее, чем использование cin?

    Как вставить элементы в вектор?

    Выбор целого столбца, кроме первых X (заголовочных) ячеек в Excel

    Проверьте, содержит ли список любой другой список

    (Чешский) поддержка набора символов в gvim 7.3 на Windows 7

    Чтение строки из файла без знания длины строки

    UIScrollView не прокручивается после обновления до iOS7 / xcode 5

    Могу ли я безопасно подключить внутренний штыревой блок USB 2.0 / 1.1 к заголовку материнской платы USB3.0?

    Кто-нибудь знает набор привязок C # для FFMPEG?

    jackson меняет JsonIgnore динамически

    Macro изменяет функциональность при назначении кнопки в Word 2016

    Как распечатать текущую трассировку стека в .NET без каких-либо исключений?

    Давайте будем гением компьютера.