使用data.table将值从一列扩展到多个新列

Question

我有一张Customer_ID表，显示了按年计算的付款情况。第一个(许多)客户出现如下：

 ID    Payment    Year
112          0    2004
112          0    2005
112          0    2006
112       9592    2007
112      12332    2008
112       9234    2011
112       5400    2012
112       7392    2014
112       8321    2015

请注意，有些年不见了。我需要为每一行创建10个新列，显示前10年的付款情况。生成的表应该如下所示：

 ID    Payment    Year   T-1  T-2  T-3  T-4  T-5  T-6  T-7  T-8  T-9 T-10   
112          0    2004  NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
112          0    2005     0 NULL NULL NULL NULL NULL NULL NULL NULL NULL
112          0    2006     0    0 NULL NULL NULL NULL NULL NULL NULL NULL
112        952    2007     0    0    0 NULL NULL NULL NULL NULL NULL NULL
112       1232    2008   952    0    0    0 NULL NULL NULL NULL NULL NULL
112        924    2011  NULL NULL 1232  952    0    0    0 NULL NULL NULL 
112        500    2012   924 NULL NULL 1232  952    0    0    0 NULL NULL 
112        392    2014  NULL  500  924 NULL NULL 1232  952    0    0    0
112        821    2015   392 NULL  500  924 NULL NULL 1232  952    0    0

(我知道这是重复的数据-它正在为一个预测模型做准备，在预测模型中，以前的付款(和其他信息)将用于预测本年度的付款)

在SQL中，我将把联接表留给自己，加入ID和Year=(年份-1)等.但我不知道怎么用R。

我还考虑过使用dplyr按ID对组进行分组，然后在取消分组之前使用延迟对新列进行变异。但是我的桌子很大，我想这个太慢了。理想情况下，我希望使用data.table，但不知道如何使用。

任何帮助都很感激。

Answer 1

首先，使用所有日期和ID的组合进行合并，以获得缺少的年份：

dftot <- merge(df,CJ(Year =seq(min(df$Year),max(df$Year),1),ID = unique(df$ID)),all = T,by = "Year")
dftot[,ID := ID.y]
dftot[,c("ID.x","ID.y") := NULL]
dftot[,Year := as.numeric(Year)]
dftot <- dftot[order(Year)]

    Year Payment  ID
 1: 2004       0 112
 2: 2005       0 112
 3: 2006       0 112
 4: 2007    9592 112
 5: 2008   12332 112
 6: 2009      NA 112
 7: 2010      NA 112
 8: 2011    9234 112
 9: 2012    5400 112
10: 2013      NA 112
11: 2014    7392 112
12: 2015    8321 112

然后创建滞后列，并重新选择不缺少Payement的行。

dftot[,c(paste0("T-",1:10)) := lapply(1:10,function(i){
    if(.N>1){
      c(rep(NA,i),Payment[1:(.N-i)])
    }else{NA}
  }),by = ID ][!is.na(Payment)]




   Year Payment  ID  T-1  T-2   T-3   T-4  T-5   T-6   T-7  T-8 T-9 T-10
1: 2004       0 112   NA   NA    NA    NA   NA    NA    NA   NA  NA   NA
2: 2005       0 112    0   NA    NA    NA   NA    NA    NA   NA  NA   NA
3: 2006       0 112    0    0    NA    NA   NA    NA    NA   NA  NA   NA
4: 2007    9592 112    0    0     0    NA   NA    NA    NA   NA  NA   NA
5: 2008   12332 112 9592    0     0     0   NA    NA    NA   NA  NA   NA
6: 2011    9234 112   NA   NA 12332  9592    0     0     0   NA  NA   NA
7: 2012    5400 112 9234   NA    NA 12332 9592     0     0    0  NA   NA
8: 2014    7392 112   NA 5400  9234    NA   NA 12332  9592    0   0    0
9: 2015    8321 112 7392   NA  5400  9234   NA    NA 12332 9592   0    0

这应该非常有效，并且应该处理多个IDs。

数据

library(data.table)
df <- setDT(read.table(text = "ID    Payment    Year
112          0    2004
                       112          0    2005
                       112          0    2006
                       112       9592    2007
                       112      12332    2008
                       112       9234    2011
                       112       5400    2012
                       112       7392    2014
                       112       8321    2015",header = T))

Answer 2

下面是一个基本的R选项，类似于@denis的解决方案

u <- merge(df1,
  data.frame(ID = unique(df1$ID), Year = min(df1$Year):max(df1$Year)),
  by = c("ID", "Year"), all = TRUE
)

subset(cbind(u, `colnames<-`(do.call(
  rbind,
  lapply(
    Reduce(c, c(NA, u$Payment), accumulate = TRUE)[1:nrow(u)],
    function(x) `length<-`(head(rev(x), 10), 10)
  )
), paste0("T-", 1:10))), !is.na(Payment))

这样的话

    ID Year Payment  T-1  T-2   T-3   T-4  T-5   T-6   T-7  T-8 T-9 T-10
1  112 2004       0   NA   NA    NA    NA   NA    NA    NA   NA  NA   NA
2  112 2005       0    0   NA    NA    NA   NA    NA    NA   NA  NA   NA
3  112 2006       0    0    0    NA    NA   NA    NA    NA   NA  NA   NA
4  112 2007    9592    0    0     0    NA   NA    NA    NA   NA  NA   NA
5  112 2008   12332 9592    0     0     0   NA    NA    NA   NA  NA   NA
8  112 2011    9234   NA   NA 12332  9592    0     0     0   NA  NA   NA
9  112 2012    5400 9234   NA    NA 12332 9592     0     0    0  NA   NA
11 112 2014    7392   NA 5400  9234    NA   NA 12332  9592    0   0    0
12 112 2015    8321 7392   NA  5400  9234   NA    NA 12332 9592   0    0

数据

> dput(df1)
structure(list(ID = c(112L, 112L, 112L, 112L, 112L, 112L, 112L, 
112L, 112L), Payment = c(0L, 0L, 0L, 9592L, 12332L, 9234L, 5400L,
7392L, 8321L), Year = c(2004L, 2005L, 2006L, 2007L, 2008L, 2011L,
2012L, 2014L, 2015L)), class = "data.frame", row.names = c(NA,
-9L))