Adding totals to all rows and columns in a df

Question

I have as simple question, at least I think it is/?!

I have a df, see below and attached example, that I would like to add totals to all rows and columns in. In reality the df I need to do it on has many more columns and rows so I need a solution that is not too time consuming please, that is does not sum each row column one at a time?

Data

df <- structure(list(Name = c("Joe", "Sanj", "Rob"), Date = c("12/08/2020", "13/08/2020", "14/08/2020"), Col1 = c(20, 60, 40), Col2 = c(40, 40, 40), Col3 = c(100, 233, 500)), row.names = c(NA, -3L), class = c("tbl_df", "tbl", "data.frame"))

# A tibble: 3 x 5
  Name  Date        Col1  Col2  Col3
  <chr> <chr>      <dbl> <dbl> <dbl>
1 Joe   12/08/2020    20    40   100
2 Sanj  13/08/2020    60    40   233
3 Rob   14/08/2020    40    40   500

ThomasIsCoding · Answer 1 · 2020-08-07T14:10:15.453

2

Is this what you are after?

df$total <- rowSums(df[grep("Col\\d+",names(df))],na.rm = TRUE)

or

df$total <- rowSums(df[-c(1:2)],na.rm = TRUE)

which gives

> df
# A tibble: 3 x 6
  Name  Date        Col1  Col2  Col3 total
  <chr> <chr>      <dbl> <dbl> <dbl> <dbl>
1 Joe   12/08/2020    20    40   100   160
2 Sanj  13/08/2020    60    40   233   333
3 Rob   14/08/2020    40    40   500   580

edited Aug 07 '20 at 14:10

answered Aug 07 '20 at 14:06

ThomasIsCoding

96,636
9
24
81

This is correct for the example, but surely the OP's columns aren't actually called `Col1` , `Col2`, `Col3` etc? – Allan Cameron Aug 07 '20 at 14:08
1

@AllanCameron Haha, that's right! Maybe `rowSums(df[-c(1:2)],na.rm = TRUE)`? – ThomasIsCoding Aug 07 '20 at 14:10

Duck · Accepted Answer · 2020-08-10T11:59:09.200

1

This can also help, similar to @ThomasIsCoding:

library(tidyverse)
#Code
df %>% bind_rows(df %>% select(-c(1:2)) %>% summarise_all(.funs = sum)) %>%
  mutate(Total = rowSums(.[3:5]))

Output:

# A tibble: 4 x 6
  Name  Date        Col1  Col2  Col3 Total
  <chr> <chr>      <dbl> <dbl> <dbl> <dbl>
1 Joe   12/08/2020    20    40   100   160
2 Sanj  13/08/2020    60    40   233   333
3 Rob   14/08/2020    40    40   500   580
4 <NA>  <NA>         120   120   833  1073

Update: You can also add a Total label:

df %>% bind_rows(df %>% select(-c(1:2)) %>% summarise_all(.funs = sum) %>% mutate(Name='Total')) %>%
  mutate(Total = rowSums(.[3:5]))

Output:

# A tibble: 4 x 6
  Name  Date        Col1  Col2  Col3 Total
  <chr> <chr>      <dbl> <dbl> <dbl> <dbl>
1 Joe   12/08/2020    20    40   100   160
2 Sanj  13/08/2020    60    40   233   333
3 Rob   14/08/2020    40    40   500   580
4 Total <NA>         120   120   833  1073

edited Aug 10 '20 at 11:59

answered Aug 07 '20 at 14:16

Duck

39,058
13
42
84

Great, thank you! Perfect, the only thing I need to add is a name for row 4? – Montana_innit96 Aug 10 '20 at 06:05
@Montana_innit96 I have added an update with code for what you want! Please check and let me know if that works for you! – Duck Aug 10 '20 at 11:59
that is perfect. Thank you very much! – Montana_innit96 Aug 10 '20 at 12:18
Actually, I still have a small issue: the actual df I want to apply this on actually has non-numeric data (my bad in original question!) in some columns and the the code is returning an error - Error in .Primitive("sum").... invalid 'type' (character) of argument. Adjusted code for df example below: df <- structure(list(Name = c("Joe", "Sanj", "Rob"), Date = c("12/08/2020", "13/08/2020", "14/08/2020"), Col1 = c(20, 60, 40), Col2 = c('green', 'red', 'blue'), Col3 = c(100, 233, 500)), row.names = c(NA, -3L), class = c("tbl_df", "tbl", "data.frame")) – Montana_innit96 Aug 10 '20 at 12:40
@Montana_innit96 Try using `as.numeric()` to transform such columns, and the you can try with the code. – Duck Aug 10 '20 at 12:41

Adding totals to all rows and columns in a df

2 Answers2