summarizing data.table - creating multiple columns subset by date in R

user_6119375 0
Viewed 67

I have data about ID and the corresponding amount over multiple years. Something like this: 

 ID <- c(rep("A", 5), rep("B", 7), rep("C", 3))
  amount <- c(sample(1:10000, 15))
  Date <- c("2016-01-22","2016-07-25", "2016-09-22", "2017-10-22", "2017-01-02",
              "2016-08-22", "2016-09-22", "2016-10-22", "2017-08-22", "2017-09-22", "2017-10-22", "2018-08-22", 
              "2016-10-22","2017-10-25", "2018-10-22")

Now, I want to analyse every year of every ID. Specifically, I am interested in the amount. For one, I want to know the overall amount for every year. Then, i also want to know the overall amount for first 11 months of every year, first 10 months of every year, first 9 months of every year and first 8 months of every year. For this purpose I have calculated the cumSum for every ID per year as follows: 

  myData <- cbind(ID, amount, Date)
  myData <- as.data.table(myData)

  # createe cumsum per ID per Year
  myData$Date <- as.Date(myData$Date, format = "%Y-%m-%d")
  myData[order(clientID, clDate)]
  myData[, CumSum := cumsum(amount), by =.(ID, year(Date))]

How can summarise the data.table such that i get columns amount9month, amount10month, amount11month for every ID in every year?

data.table r
2 Answers

In which format do you want it? There are two simple options. You can get the requested result easily in two different formats:

# Prepare the data
ID <- c(rep("A", 5), rep("B", 7), rep("C", 3))
amount <- c(sample(1:1, 15, replace = TRUE))
Date <- c("2016-01-22","2016-07-25", "2016-09-22", "2017-10-22", "2017-01-02", "2016-08-22", "2016-09-22", "2016-10-22", "2017-08-22", "2017-09-22", "2017-10-22", "2018-08-22", "2016-10-22","2017-10-25", "2018-10-22")
myData <- data.frame(ID, amount, Date)
# Add year column
myData$Date <- as.Date(myData$Date, format = "%Y-%m-%d")
myData$year <- format(myData$Date,"%Y")

Please note that I changed the amounts for testing purposes. Now two solutions.

# Format 1
by(myData$amount, list(myData$ID, myData$year), cumsum, simplify = TRUE)
# Format 2
aggregate(myData$amount, list(ID = myData$ID, Date = myData$year), cumsum)

However, you might want to have the result to be a new column in the data frame? You can solve it:

# Format: New column
myData <- myData[order(myData$year, myData$ID),] # sort by year and ID
myData$cumsum <- rep(0, nrow(myData))
for (r in 1:nrow(myData)) {
  if (r > 1 && myData$year[r-1] == myData$year[r] && myData$ID[r-1] == myData$ID[r])
    myData$cumsum[r] <- myData$cumsum[r-1] + myData$amount[r]
  else
    myData$cumsum[r] <- myData$amount[r]
}

I do not know a smooth solution with basic R. Maybe someone from the "dplr faction" has a neat trick up their sleeve?

user_132415450

Between cumsum, by and dcast this is almost quite straightforward. The most difficult bit is dealing with those months without any data in. Hence this solution isn't as brief as it almost was, but it does do things the "data.table way" and avoids slow operations like looping through rows.

# Just sort the formatting out first
myData[, Date:=as.Date(Date)]
myData[, `:=`(amount = as.numeric(amount),
              year = year(Date),
              month = month(Date))]
bycols <- c('ID', 'year', 'month')

# Summarise all transactions for the same ID in the same month
summary <- myData[, .(amt = sum(amount)), by=bycols]

# Create a skeleton table with all possible combinations of ID, year and month, to fill in any gaps.
skeleton <- myData[, CJ(ID, year, month = 1:12, unique = TRUE)]

# Join the skeleton to the actual data, to recreate the data but with no gaps in
result.long <- summary[skeleton, on=bycols, allow.cartesian=TRUE]
result.long[, amt.cum:=cumsum(fcoalesce(amt, 0)), by=c('ID', 'year')]

# Cast the data into wide format to have one column per month
result.wide <- dcast(result.long, ID + year ~ paste0('amount',month,'month'), value.var='amt.cum')

NB. If you don't have fcoalesce, update your data.table package.

user_135665930
Related