Как суммировать по двум таблицам?

У меня есть две таблицы, в которых мне нужно сделать sumif. Таблица 1 содержит периоды времени, то есть год и квартал в конце года (т.е. 4, 8, 12 и т.д.). Таблица 2 содержит транзакции в течение года в кварталах 3, 6, 7 и т.д.

Мне нужна таблица 3, чтобы суммировать все транзакции в течение года, чтобы получить кумулятивную позицию в конце года.

Вот пример кода для объяснения того, как выглядят данные и как выглядит результат:

library(data.table)

x1 <- data.table("Name" = "LOB1", "Year" = 2000, 
                 "Quarter" = c(4, 8, 12, 16, 20, 24, 28, 32, 36))
x2 <- data.table("Name" = "LOB1", "Year" = 2000, 
                 "Quarter" = c(3, 6, 7, 9, 11, 14, 16, 20, 24), 
                 "Amount" = c(10000, 15000, -2500, 3500, -6500, 25000, 
                              11000, 9000, 7500))
x3 <- data.table("Name" = "LOB1", "Year" = 2000, 
                 "Quarter" = c(4, 8, 12, 16, 20, 24, 28, 32, 36), 
                 "Amount" = c(10000, 22500, 19500, 55500, 64500, 72000, 
                              72000, 72000, 72000))

Я пробовал merge, summarise, foverlaps, но не могу понять.

Ответы

Ответ 1

Хороший вопрос. В основном вы пытаетесь присоединиться к Name, Year и Quarter <= Quarter, суммируя все сопоставленные значения Amount. Это возможно и с использованием новых неравновесных объединений (которые были введены в последней стабильной версии data.table v-1.10.0) и foverlaps (в то время как последняя будет, вероятно, субоптимальной)

Non-Equi объединяет:

x2[x1, # for each value in `x1` find all the matching values in `x2`
   .(Amount = sum(Amount)), # Sum all the matching values in `Amount`
   on = .(Name, Year, Quarter <= Quarter), # join conditions
   by = .EACHI] # Do the summing per each match in `i`
#    Name Year Quarter Amount
# 1: LOB1 2000       4  10000
# 2: LOB1 2000       8  22500
# 3: LOB1 2000      12  19500
# 4: LOB1 2000      16  55500
# 5: LOB1 2000      20  64500
# 6: LOB1 2000      24  72000
# 7: LOB1 2000      28  72000
# 8: LOB1 2000      32  72000
# 9: LOB1 2000      36  72000

В качестве дополнительной заметки вы можете легко добавить Amount в место в x1 (предложенном @Frank):

x1[, Amount := 
  x2[x1, sum(x.Amount), on = .(Name, Year, Quarter <= Quarter), by = .EACHI]$V1
]

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

foverlaps:

Вы упомянули foverlaps, поэтому теоретически вы можете добиться того же, используя эту функцию. Хотя я боюсь, вы легко избавитесь от памяти. Используя foverlaps, вам нужно создать огромную таблицу, в которой каждое значение в x2 соединяется несколько раз с каждым значением в x1 и сохраняет все в памяти

x1[, Start := 0] # Make sure that we always join starting from Q0
x2[, Start := Quarter] # In x2 we want to join all possible rows each time 
setkey(x2, Name, Year, Start, Quarter) # set keys
## Make a huge cartesian join by overlaps and then aggregate
foverlaps(x1, x2)[, .(Amount = sum(Amount)), by = .(Name, Year, Quarter = i.Quarter)]
#    Name Year Quarter Amount
# 1: LOB1 2000       4  10000
# 2: LOB1 2000       8  22500
# 3: LOB1 2000      12  19500
# 4: LOB1 2000      16  55500
# 5: LOB1 2000      20  64500
# 6: LOB1 2000      24  72000
# 7: LOB1 2000      28  72000
# 8: LOB1 2000      32  72000
# 9: LOB1 2000      36  72000