add row to a data frame that calculates sums of all numeric columns

user_18025385 0
Viewed 153

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

dplyr dataframe r sum
2 Answers

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
user_169192570

Let's add an additional row, with Total, Total in two columns, and let's calculate the sums along columns with colSums starting from column 3 till end ncol(df).

df<- rbind(df, c("Total", "Total", colSums(df[,3:ncol(df)], na.rm = TRUE)))
user_174868940
Related