How to sum columns and rows in a wide R dataframe?

I'd like to mutate by dataframe by summing both columns and rows.

mydata <-structure(list(description.y = c("X1", "X2"), `2011` = c(13185.66, 
82444.01), `2012` = c(14987.61, 103399.4), `2013` = c(26288.98, 
86098.22), `2014` = c(15238.21, 88540.04), `2015` = c(15987.11, 
113145.1), `2016` = c(16324.57, 113196.2), `2017` = c(16594.87, 
122167.57), `2018` = c(20236.02, 120058.21), `2019` = c(20626.69, 
130699.68), `2020` = c(19553.83, 136464.31), `2021` = c(10426.32, 
56392.28)), class = c("grouped_df", "tbl_df", "tbl", "data.frame"
), row.names = c(NA, -2L), groups = structure(list(description.y = c("X1", 
"X2"), .rows = structure(list(1L, 2L), ptype = integer(0), class = c("vctrs_list_of", 
"vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -2L), .drop = TRUE))

I can sum rows like this

mydata1 <- mydata %>% 
      mutate(Total = rowSums(across(where(is.numeric))))

Which provides an extra column with totals for the rows enter image description here

But I'm not sure how to add Columns to the dataframe while also retaining all existing values

I've tried this but it doesn't work. Any thoughts?

 mydata1 <- mydata %>% 
      mutate(Total = rowSums(across(where(is.numeric)))) %>% 
      mutate(Total = colSums(across(where(is.numeric))))

Update: See comment @Mwavu -> many thanks! direct solution with adorn_total():

mydata %>% adorn_totals(where = c("row", "col"))

First answer: We could use adorn_totals()

library(dplyr)
library(janitor)
mydata %>% 
  mutate(Total = rowSums(across(where(is.numeric)))) %>% 
  adorn_totals()

 description.y     2011      2012      2013      2014      2015      2016      2017      2018      2019      2020     2021     Total
            X1 13185.66  14987.61  26288.98  15238.21  15987.11  16324.57  16594.87  20236.02  20626.69  19553.83 10426.32  189449.9
            X2 82444.01 103399.40  86098.22  88540.04 113145.10 113196.20 122167.57 120058.21 130699.68 136464.31 56392.28 1152605.0
         Total 95629.67 118387.01 112387.20 103778.25 129132.21 129520.77 138762.44 140294.23 151326.37 156018.14 66818.60 1342054.9

Another way is to first summarize and then bind_rows:

library(dplyr)

mydata %>% 
  ungroup() %>% 
  mutate(Total = rowSums(across(where(is.numeric)))) %>%
  bind_rows(summarize(., description.y = "Total", across(where(is.numeric), sum)))

Output

# A tibble: 3 x 13
  description.y `2011`  `2012`  `2013`  `2014`  `2015`  `2016`  `2017`  `2018`  `2019`  `2020` `2021`    Total
  <chr>          <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>  <dbl>    <dbl>
1 X1            13186.  14988.  26289.  15238.  15987.  16325.  16595.  20236.  20627.  19554. 10426.  189450.
2 X2            82444. 103399.  86098.  88540. 113145. 113196. 122168. 120058. 130700. 136464. 56392. 1152605.
3 Total         95630. 118387. 112387. 103778. 129132. 129521. 138762. 140294. 151326. 156018. 66819. 1342055.

How to sum columns and rows in a wide R dataframe?

Related

Recent Posts