Перемещение суммы на основе дат

Question

У меня большой набор данных, который я хотел бы рассчитать для скользящей годовой суммы столбца. Это должен быть точный год, поэтому я не могу использовать rollapply, поскольку он основан на определенном количестве дней, а не на фактических датах.

В качестве примера у меня есть следующий код:

dates = seq.Date(as.Date('2006-01-01'),as.Date('2007-12-31'),by='days')
num = 1:length(dates)
y = cbind(ld,num)

        ld num
[1,] 13149   1
[2,] 13150   2
[3,] 13151   3
[4,] 13152   4
[5,] 13153   5
[6,] 13154   6

Я хотел бы получить скользящую историческую сумму за один год.

Единственный способ, который мне удалось решить, - это использовать цикл и подмножество фрейма данных. Это не очень эффективно, и я надеялся, что кто-нибудь посоветует мне, как учесть високосные годы, используя вместо этого функцию встраивания, поскольку это происходит намного быстрее.

Используя функцию встраивания, у меня есть следующий код, который работает, если это не високосный год.

b = embed(y[,2],366)
sums = colSums(b)

a = ld[length(dates)-365:length(dates)]
final = cbind(dates = a, rollsum = rev(sums))


head(final)
     dates rollsum
[1,] 13513   66795
[2,] 13514   67160
[3,] 13515   67525
[4,] 13516   67890
[5,] 13517   68255
[6,] 13518   68620

Есть ли у кого-нибудь более эффективный способ расчета скользящей суммы на основе конкретных дат, а не количества дней?

Answer 1

Вы можете добавить столбец к вашим данным с датой год назад (с учетом високосных годов) и использовать sqldf рассчитать скользящую сумму.

# Sample data
dates <- seq.Date(as.Date('2006-01-01'),as.Date('2007-12-31'),by='days')
d <- data.frame( date = dates, value = rnorm(length(dates)) )
#d <- d[ sample(length(dates), length(dates)/2), ]  # For more irregular data
d <- d[ order(d$date), ]

# Compute the date one year ago (you can also use lubridate, for date arithmetic)
d$previous_year <- sapply( 
  d$date, 
  function(u) as.character(seq(u, length=2, by="-1 years")[2]) 
)
d$date <- as.character(d$date)

# Compute the rolling sum
library(sqldf)
sqldf( "
  SELECT A.date         AS date, 
         SUM( B.value ) AS sum, 
         MIN( B.date )  AS start, 
         MAX( B.date )  AS end, 
         COUNT(*)       AS observations
  FROM d A, d B
  WHERE A.previous_year < B.date AND B.date <= A.date
  GROUP BY A.date
" )

Answer 2

Этот ответ использует embed, но это может не дать желаемых результатов для первых 366 строк:

library(data.table)
library(mondate)

# Create table with sample dates:
dt2<-data.table(dates = seq.Date(as.Date('2006-01-01'),as.Date('2012-12-31'),by='days'),key="dates")

# Generate some sample values to be summed, initialize the rolling sum values, add the row number, and determine the number of days between each date at the prior year (365 or 366):
set.seed(6540)
dt2[,c("val","valroll","rowid","lag"):=list(sample((1L:1e6L)-1L,.N),0L,1:.N,as.integer(dates-as.Date(mondate(dates)-12)))]

# Create a table with column values made up of each of the preceding 366 rows:
dt2b<-data.table(embed(dt2[,val],366))

# Set the 366th column to 0 if the prior year was 365 days ago:
dt2b[dt2[(dt2[lag-rowid==0L,rowid]+1L):nrow(dt2),lag]==365L,V366:=0L]

# Sum the rows of the second table, and add the result to the first table:
dt2[(dt2[lag-rowid==0L,rowid]+1L):nrow(dt2),valroll:=as.integer(rowSums(dt2b))]
rm(dt2b)

Кроме того, столбец "valroll" из моего другого ответа (используя for loop) включает одну дополнительную строку "val" по сравнению с этим ответом. Я думаю, что этот ответ должен быть изменен, но я не уверен.

Answer 3

Это должно работать быстро, хотя все еще использует цикл:

library(data.table)
library(mondate)

# Create table with sample dates:
dt<-data.table(dates = seq.Date(as.Date('2006-01-01'),as.Date('2012-12-31'),by='days'),key="dates")

# Generate some sample values to be summed, initialize the rolling sum values, and add the row number:
set.seed(6540)
dt[,c("val","valroll","rowid"):=list(sample((1L:1e6L)-1L,.N),0L,1:.N)]

# Subtract one year (12 months) from each date, then subtract that from the original date to get the number of days
# Create a column to indicate the starting row number to sum from:
dt[,rowid_lag:=pmax.int(1,rowid-as.integer(dates-as.Date(mondate(dates) - 12)))]

# For each row, sum from row rowid_lag to rowid:
for(i in 1:nrow(dt)) {
  #dt[i,valroll:=dt[dt[i,rowid_lag:rowid],sum(val)]]
  set(dt, i, "valroll", dt[dt[i,rowid_lag:rowid],sum(val)])
}
rm(i)

Выше предполагается, что в датах нет пробелов. Если это неверное предположение, можно изменить ответ.

Использование embed интересно - я не слышал об этом раньше. Я пошел по этому пути, но решил вернуться к циклу, когда не мог понять, как обрабатывать первые 365 строк. Я постараюсь закончить это решение и опубликовать его тоже, если это поможет.

Я также рассмотрел маршрут, который выбрал @VincentZoonekynd, хотя и использовал data.table скорее, чем sqldf (так как я более знаком с этим). Но по моему опыту "перекрестное соединение" в этом типе решения довольно быстро разрушается, поэтому, если у вас очень много рядов, это будет невозможно.