I'm not sure what the title should be, maybe other users can edit it. Thanks.
I have a data:
date sum.A_1 sum.A_2 sum.A_3 sum.B_1 sum.B_2 sum.B_3
201101 245726 7951 81732 172 344 172
201102 1066407 34094 371868 793 1586 793
201103 1608750 51527 557677 1203 2406 1203
I want to create 1 new row and 2 new columns.
The result is like:
date sum.A_1 sum.A_2 sum.A_3 sum.B_1 sum.B_2 sum.B_3 SUM.A SUM.B
201101 245726 7951 81732 172 344 172 335409 688
201102 1066407 34094 371868 793 1586 793 1472369 3172
201103 1608750 51527 557677 1203 2406 1203 2217954 4812
TOTAL 2920883 93572 1011277 2168 4336 2168 4025732 8672
I want to create 2 new columns SUM.A and SUM.B and then create 1 new row TOTAL.
SUM.A is summed by sum.A_1 : sum.A_3; SUM.B is sum by sum.B_1 : sum.B_3.
TOTAL is summed by each column.
DATA
data <- "date sum.A_1 sum.A_2 sum.A_3 sum.B_1 sum.B_2 sum.B_3
1: 201101 245726 7951 81732 172 344 172
2: 201102 1066407 34094 371868 793 1586 793
3: 201103 1608750 51527 557677 1203 2406 1203
"
data <- read.table(text = data, header = T)
data <- as.data.table(data)
Any suggestion?
UPDATE
I figure out the sum of each rows but still cannot solve sum of each columns.
I browsed the StackOverFlow and saw 2 useful resources.
R: colSums when not all columns are numeric
How do I add a row to a data frame with totals?
So, I try it on my data:
rownames(data) <- data$date
addmargins(as.table(as.matrix(data[-1])), 1)
# Error in FUN(newX[, i], ...) : invalid 'type' (character) of argument
data["TOTAL", .SDcols = grep("sum.", names(data))] <- sapply(colSums(data), is.numeric)
# Error in colSums(data) : 'x' must be numeric
Is there a solution using data.table way?
SOLVED
janitor::adorn_totals(data, "row")
