I have a data frame with first two columns characters and the rest doubles. I want to add a row at the bottom that has the word 'Total' in the first two columns and calculates the sum of the column values in the rest.
For example let's use:
segment<- c('seg1', 'seg1', 'seg2', 'seg2', 'seg3', 'seg3')
subSegment<- c('subseg1', 'subseg2', 'subseg1', 'subseg2', 'subseg1', 'subseg2')
var.1<- c(100, 20, 30, 50, 40, 40)
var.2<- c(200, 30, 30, 70, 30, 140)
var.3<- c(50, 50, 40, 20, 30, 40)
var.4<- c(60, 50, 35, 53, 42, 20)
df<- data.frame(segment, subSegment, var.1, var.2, var.3, var.4)
This is how I did it:
df%>% # now need to add a row with totals
add_row( segment="Total",subSegment="Total", var.1 = sum(.$var.1), var.2= sum(.$var.2), var.3 = sum(.$var.3), var.4 = sum(.$var.4))
Obviously the names and values are just examples but in my real problem I have over 8 variables and doing things this way is a waste of time.
I am looking for a more general solution that just says to add_row with first 2 columns "Total" and then ....=sum(.$....) for all columns after the third.
Maybe using something like sapply(df[c(3:ncol(df))].
The janitor package has this ready to go:
library(janitor)
df %>%
adorn_totals("row", fill = "Total")
segment subSegment var.1 var.2 var.3 var.4
seg1 subseg1 100 200 50 60
seg1 subseg2 20 30 50 50
seg2 subseg1 30 30 40 35
seg2 subseg2 50 70 20 53
seg3 subseg1 40 30 30 42
seg3 subseg2 40 140 40 20
Total Total 280 500 230 260