sqldf запрос с разницей в датах

Я пытаюсь выполнить (упрощенный!) Запрос о различиях цвета и даты в следующих двух базах данных (выдержка):

A                           B   
    A.COL   A.TIME              B.COL   B.TIME
1   blue    2009-01-31      1   blue    2007-01-31
2   blue    2009-02-28      2   blue    2008-12-31
3   blue    2009-03-31      3   blue    2009-02-28
4   blue    2009-04-30      4   blue    2009-04-30
5   blue    2009-05-31      5   blue    2009-06-30
6   blue    2009-06-30      6   blue    2016-08-31
7   blue    2016-03-31
8   blue    2016-04-30
9   red ...
10  red ...

Что я хочу сделать: объединить таблицы на основе COL, а также разницы во времени, то есть разница между обоими временами не должна быть больше или меньше 2 месяцев (или, другими словами, находиться между -2 и +2, в зависимости от с какой даты начинается).

# For example starting with observation 1 from A, that would imply 2 matches:
2009-01-31 matched to 2008-12-31 (diff = 1)
2009-01-31 matched to 2009-02-28  (diff = -1)

# for obs 2 from A, that would imply 
2009-02-28 matched to 2008-12-31 (diff = 2)
2009-02-28 matched to 2009-02-28 (diff = 0)
2009-02-28 matched to 2009-04-30 (diff = -2)

и т. д. Я думал о какой-то функции разницы дат, либо от lubridate, что было проблематично в случаях месяцев с менее чем 30 днями и иногда делалось НП, либо с as.yearmon от zoo, которая работала, по крайней мере, для правильного вычисления различий. Однако мне не удалось правильно реализовать это в sqldf (Ошибка: ошибка в инструкции: рядом с "как": синтаксическая ошибка). Причина, по-видимому, в том, что нельзя использовать все функции R в sqldf. Есть идеи, как это можно сделать в R? Я также искал элегантный способ вычитания месяцев друг из друга. Проблема с lubridate: Добавить / вычесть 6 месяцев (время связывания) в R с использованием lubridate, но здесь был предложен один способ сделать это с помощью zoo: Узнайте разницу между датами в неделях, месяцах, кварталах и годах

Получите данные (спасибо @bouncyball ниже за код):

A <- read.table(
  text = "
  A.COL   A.TIME          
  blue    2009-01-31     
  blue    2009-02-28      
  blue    2009-03-31      
  blue    2009-04-30      
  blue    2009-05-31      
  blue    2009-06-30
  blue    2016-03-31
  blue    2016-04-30
  ", header = T, stringsAsFactors = FALSE)


B <- read.table(
  text = "
  B.COL   B.TIME
  blue    2007-01-31
  blue    2008-12-31
  blue    2009-02-28
  blue    2009-04-30
  blue    2009-06-30
  blue    2016-08-31
  ", stringsAsFactors = FALSE, header = T)

r merge date difference sqldf
person user3032689    schedule 06.09.2016    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Вот решение, которое использует функции из этого сообщения SO и пакета plyr:

library(plyr)

# turn a date into a 'monthnumber' relative to an origin
monnb <- function(d) { 
  lt <- as.POSIXlt(as.Date(d, origin="1900-01-01"))
  lt$year*12 + lt$mon 
  } 

# compute a month difference as a difference between two monnb's
mondf <- function(d1, d2) { monnb(d2) - monnb(d1) }

# iterate over rows of A looking for matches in B
adply(A, 1, function(x)
  B[x$A.COL == B$B.COL & 
      abs(mondf(as.Date(x$A.TIME), as.Date(B$B.TIME))) <= 2,]
)

#     A.COL    A.TIME  B.COL    B.TIME
# 1   blue 2009-01-31  blue 2008-12-31
# 2   blue 2009-01-31  blue 2009-02-28
# 3   blue 2009-02-28  blue 2008-12-31
# 4   blue 2009-02-28  blue 2009-02-28
# 5   blue 2009-02-28  blue 2009-04-30
#  ....

изменить: data.table реализация

library(data.table)
merge_AB <- data.table(merge(A,B, by.x = 'A.COL', by.y = 'B.COL'))

merge_AB[,DateDiff := abs(mondf(A.TIME, B.TIME))
       ][DateDiff <= 2]

 #     A.COL     A.TIME     B.TIME DateDiff
 # 1:  blue 2009-01-31 2008-12-31        1
 # 2:  blue 2009-01-31 2009-02-28        1
 # 3:  blue 2009-02-28 2008-12-31        2
 # 4:  blue 2009-02-28 2009-02-28        0
 # 5:  blue 2009-02-28 2009-04-30        2
 # ...

данные

A <- read.table(
text = "
A.COL   A.TIME          
blue    2009-01-31     
blue    2009-02-28      
blue    2009-03-31      
blue    2009-04-30      
blue    2009-05-31      
blue    2009-06-30
blue    2016-03-31
blue    2016-04-30
", header = T, stringsAsFactors = FALSE)


B <- read.table(
  text = "
B.COL   B.TIME
blue    2007-01-31
blue    2008-12-31
blue    2009-02-28
blue    2009-04-30
blue    2009-06-30
blue    2016-08-31
", stringsAsFactors = FALSE, header = T)
person bouncyball    schedule 06.09.2016
comment
Молодец! Только один вопрос: можно ли это сделать и с datatable? Я не знаком с plyr, и я только начал с datatable, поэтому мне интересно, можно ли его здесь использовать для достижения такой же сортировки? Кстати, вы не использовали sqldf для решения этой проблемы. Интересно: это невозможно в этом случае? - person user3032689; 07.09.2016
comment
@ user3032689 Да, мы можем, посмотрите мои изменения для реализации data.table - person bouncyball; 07.09.2016