CODEWITHSUNDEEP
rdata.tabledata-manipulation
rw2
rw2

Reputation: 1783

Spread values from one column into multiple new columns using data.table

I have a table of Customer_ID, showing Payments by Year. The first (of many) customer appears like this:

 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

Note that some years are missing. I need to create 10 new columns, showing the Payments in the previous 10 years, for each row. The resulting table should look like this:

 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

(I know this is duplicating data - it is being prepared for a predictive model, in which previous payments (and other info) will be used to predict the current year's payment)

In SQL I would left join the table to itself, joining on ID and Year=(Year-1) etc... but I can't figure out how to do this in R.

I've also thought about using dplyr to group by ID, then mutate the new columns using lag, before ungrouping. But my tables are very large and I think this would be too slow. Ideally I would like to use data.table instead, but can't figure out how.

Any help much appreciated.

Upvotes: 1

Views: 220

Answers (2)

denis
denis

Reputation: 5673

You first do a merge with the combination of all dates and ID to have the missing years:

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

You then create the lagged columns, and reselect the lines with non missing 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

This should be quite efficient and should handle multiple IDs

The data

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))

Upvotes: 3

ThomasIsCoding
ThomasIsCoding

Reputation: 101034

Here is a base R option, similar idea to the solution by @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))

such that

    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

Data

> 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))

Upvotes: 0

Related Questions