将不定期的时间序列拆分为定期的月平均值-R

Question

为了确定对能源使用的季节性影响，我需要将计费数据库中的能源使用信息与每月温度进行匹配。

我正在使用一个计费数据集，该数据集具有不同长度的账单以及开始日期和结束日期，我想获取每个月内每个帐户的月平均值。例如，我有一个具有以下特征的计费数据库：

   acct amount      begin        end days
1  2242  11349 2009-10-06 2009-11-04   29
2  2242  12252 2009-11-04 2009-12-04   30
3  2242  21774 2009-12-04 2010-01-08   35
4  2242  18293 2010-01-08 2010-02-05   28
5  2243  27217 2009-10-06 2009-11-04   29
6  2243    117 2009-11-04 2009-12-04   30
7  2243  14543 2009-12-04 2010-01-08   35

我想弄清楚如何强制这些不规则的时间序列（对于每个帐户）以获取每个账单中每个月的每日平均金额，例如：

   acct amount      begin        end days avgamtpday
1  2242  11349 2009-10-01 2009-10-31   31          X
2  2242  12252 2009-11-01 2009-11-30   30          X
3  2242  21774 2009-12-01 2010-12-31   31          X
4  2242  18293 2010-01-01 2010-01-31   31          X
4  2242  18293 2010-02-01 2010-02-28   28          X
5  2243  27217 2009-10-01 2009-10-31   31          X
6  2243    117 2009-11-01 2009-11-30   30          X
7  2243  14543 2009-12-01 2009-12-31   30          X
7  2243  14543 2010-01-01 2010-01-31   31          X

我完全不知道哪个工具可以执行此操作，因为我只需要执行一次即可。

该表还有一个额外的折痕，即该表大约有150,000行，按照大多数标准来说，这并不是很大，但足以使R中的循环解决方案变得困难。我已经使用R中的zoo，xts和tempdisagg软件包进行了调查。我开始编写一个非常丑陋的循环，该循环将拆分每笔账单，然后在现有账单中为每个月创建一行，然后使用tapply（）通过accts进行汇总和几个月，但老实说，看不到如何有效地做到这一点。

在MySQL中，我已经尝试过了：

创建或替换视图v3，作为select 1 n union all select 1 union all select 1;
创建或替换视图v，作为v3a，v3b联合的全部选择1的选择1 n；
设置@n = 0;
如果存在日历则删除表；创建表日历（dt日期主键）； 从va，vb，vc，vd，ve，v中将dt
插入
dt并插入日历中，选择cast（‘2008-1-1’+间隔@n：= @ n + 1天作为日期）；

选择acct，金额，开始，结束，billAmtPerDay，sum（billAmtPerDay），MonthAmt，count（
）天，sum（billAmtPerDay）/ count（
）AverageAmtPerDay，year（dt），month（dt）FROM（选择*，金额/天来自帐单b内部联接日历c上的billAmtPerDay，位于开始和结束之间，以及开始<>
dt）x按acct，金额，开始，结束，billAmtPerDay，year（dt），month（dt）分组；

但是由于我不明白的原因，我的服务器不喜欢该表，并且即使在进行不同的计算时也会挂在内部联接上。我正在调查是否有任何临时内存限制。

谢谢！

Answer 1

这是开始使用data.table：

billdata <- read.table(text=" acct amount begin end days
1 2242 11349 2009-10-06 2009-11-04 29
2 2242 12252 2009-11-04 2009-12-04 30
3 2242 21774 2009-12-04 2010-01-08 35
4 2242 18293 2010-01-08 2010-02-05 28
5 2243 27217 2009-10-06 2009-11-04 29
6 2243 117 2009-11-04 2009-12-04 30
7 2243 14543 2009-12-04 2010-01-08 35", sep=" ", header=TRUE, row.names=1)

require(data.table)
DT = as.data.table(billdata)

首先，变型列begin和end至日期。与data.frame不同，这不会复制整个数据集。

DT[,begin:=as.Date(begin)]
DT[,end:=as.Date(end)]

然后找到时间跨度，找到每天的主要账单，并进行汇总。

alldays = DT[,seq(min(begin),max(end),by="day")]

setkey(DT, acct, begin)

DT[CJ(unique(acct),alldays),
   mean(amount/days,na.rm=TRUE),
   by=list(acct,month=format(begin,"%Y-%m")), roll=TRUE]

    acct   month        V1
 1: 2242 2009-10 391.34483
 2: 2242 2009-11 406.69448
 3: 2242 2009-12 601.43226
 4: 2242 2010-01 646.27465
 5: 2242 2010-02 653.32143
 6: 2243 2009-10 938.51724
 7: 2243 2009-11  97.36172
 8: 2243 2009-12 375.68065
 9: 2243 2010-01 415.51429
10: 2243 2010-02 415.51429

我认为您会发现流行的连接逻辑在SQL中非常麻烦，而且速度较慢。

我说这是一个提示，因为它不太正确。注意重复行10，因为帐户2243不会像帐户2242那样延伸到2010-02。要结束该rbind行，您可以在每个帐户的最后一行中使用rolltolast代替roll。或alldays按帐户而不是跨所有帐户创建。

看看上面的速度是否可以接受，我们可以从那里开始。

您可能会遇到1.8.2中的错误，该错误已在1.8.3中修复。我正在使用v1.8.3。

合并包含丢失的组和分组依据的联接时，“内部”错误消息已修复，编号＃2162。例如：X [Y，.N，by =
NonJoinColumn]其中Y包含一些与X不匹配的行。此错误也可能导致段错误。

让我知道，我们可以解决问题，也可以从R-Forge升级到1.8.3。

顺便说一句，很好的示例数据。这样可以更快地回答。

这是上面提到的完整答案。我不得不承认这有点棘手，因为它结合了的多个功能data.table。它应该在1.8.2中正常工作，但我仅在1.8.3中进行了测试。

DT[ setkey(DT[,seq(begin[1],last(end),by="day"),by=acct]),
    mean(amount/days,na.rm=TRUE),
    by=list(acct,month=format(begin,"%Y-%m")), roll=TRUE]

   acct   month        V1
1: 2242 2009-10 391.34483
2: 2242 2009-11 406.69448
3: 2242 2009-12 601.43226
4: 2242 2010-01 646.27465
5: 2242 2010-02 653.32143
6: 2243 2009-10 938.51724
7: 2243 2009-11  97.36172
8: 2243 2009-12 375.68065
9: 2243 2010-01 415.51429