r – pivot lapply data.table
given the following dt:
Category <- c('A','A','A','B','B','B','B','A','B','B')
Amount <- c(10,20,30,15,20,40, 50, 80,20,10)
ID <- c('x01','x01','x02','x03','x03','x04','x05','x06','x07','x08')
dt_1 <- data.table(Category, Amount, ID)
dt_1
I would like to get the following output:
Category <- c('A','B')
NumRecords <- c(4,6)
TotalAmount <- c(140,155)
CountUniqueID <- c(3,5)
dt_2 <- data.table(Category, NumRecords, TotalAmount, CountUniqueID)
dt_2
possibly extending adjusting the following code that uses lapply:
ColsBy <- c("Category")
ColsSummary <- c("Amount")
dt_2 <- dt_1[, lapply(.SD, sum, na.rm=TRUE), by = ColsBy, .SDcols = ColsSummary ]
Read more here: Source link