add a total row in a dplyr code but just under a specifc colum

Question

I have this data set

structure(list(col2 = c(1, 1, 2, 3, 1, 2, 2, 3, 1, 2), col1 = c("R", 
"R", "R", "R", "R", "L", "R", "R", "R", "R")), class = c("grouped_df", 
"tbl_df", "tbl", "data.frame"), row.names = c(NA, -10L), groups = structure(list(
    col1 = c("L", "R", "R", "R"), col2 = c(2, 1, 2, 3), .rows = structure(list(
        6L, c(1L, 2L, 5L, 9L), c(3L, 7L, 10L), c(4L, 8L)), ptype = integer(0), class = c("vctrs_list_of", 
    "vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -4L), .drop = TRUE))

what I would like to do is just add only a final row under 'n' column (the new created with dplyr) wit column total. I have tried with this code, but I get total for every columns.

library(janitor)
    data %>% 
       group_by(col1, col2) %>% 
  mutate(col1 = recode(col1, 'R (change file name)' = 'R', 
                                             'L (change name e data EEG file)' = 'L')) %>% 
  summarise(n = n()) %>%
      adorn_totals("row")

I would be eager to learn how to fix it or other strategy for this purpose.

Thanks

Quinten · Answer 1 · 2023-03-09T09:10:45.357

3

You could use bind_rows to add a row with summarise which has sum for n and total for col1. Your first summarise has been changed to reframe to have an ungrouped dataframe like this:

library(dplyr)
data %>% 
  group_by(col1, col2) %>% 
  mutate(col1 = recode(col1, 'R (change file name)' = 'R', 
                       'L (change name e data EEG file)' = 'L')) %>% 
  reframe(n = n()) %>%
  bind_rows(summarise(., across(n, sum), across(col1, ~ "Total")))
#> # A tibble: 5 × 3
#>   col1   col2     n
#>   <chr> <dbl> <int>
#> 1 L         2     1
#> 2 R         1     4
#> 3 R         2     3
#> 4 R         3     2
#> 5 Total    NA    10

Old answer with different dataset from OP:

You could use the adorn_totals function from the janitor package like this:

library(dplyr)
library(janitor)

data %>% 
  group_by(col1, col2) %>% 
  mutate(col1 = recode(col2, 'R (change file name)' = 'R', 
                       'L (change name e data EEG file)' = 'L')) %>% 
  summarise(n = n()) %>%
  adorn_totals("row")
#> `summarise()` has grouped output by 'col1'. You can override using the
#> `.groups` argument.
#>   col1                            col2  n
#>      L L (change name e data EEG file)  1
#>      R                               R  8
#>      R            R (change file name)  1
#>  Total                               - 10

^{Created on 2023-03-09 with reprex v2.0.2}

edited Mar 09 '23 at 09:10

answered Mar 09 '23 at 08:59

Quinten

35,235
5
20
53

I had made a mistake in reporting te dataset. Now it describe better my case – 12666727b9 Mar 09 '23 at 09:03
Just curious, what is the differenc between your code and OP code? – Onyambu Mar 09 '23 at 09:09
No one actually. The first dataset I have reported here was wrong. Now there is the correct one – 12666727b9 Mar 09 '23 at 09:10
Hi @alias, I updated my answer according to your new dataset! – Quinten Mar 09 '23 at 09:11
Thanks @Quinten. In the adorn_totals() function istructons, I have read taht it is possible too to make this operation, by specifying the column you are interested to get the total from with a tidyselect method. Actually I cannot figure this out, though – 12666727b9 Mar 09 '23 at 09:13
the function reframe() does not exist on my end – 12666727b9 Mar 09 '23 at 09:22
`reframe` is from the latest `dplyr` version 1.1.0, please check [this](https://dplyr.tidyverse.org/reference/reframe.html). You could also use `summarise() %>% ungroup()`. – Quinten Mar 09 '23 at 09:23

TarJae · Accepted Answer · 2023-03-09T09:28:11.190

Here is how we could do it with adorn_totals:

adorn_totals has a ... argument: Using ... requires specifying values for the other arguments, even if they're empty, thus the ,,,, below to accept the default values for those arguments. See original answer by @Sam Firke Calculating and Appending Column Totals of Select Columns in a Data Frame in R

library(dplyr)
library(janitor)

df %>% 
  group_by(col1, col2) %>% 
  mutate(col1 = recode(col1, 'R (change file name)' = 'R', 
                       'L (change name e data EEG file)' = 'L')) %>% 
  summarise(n = n()) %>%
  adorn_totals("row",,,,n) 


  col1 col2  n
     L    2  1
     R    1  4
     R    2  3
     R    3  2
 Total    - 10

1

Nice use of `adorn_totals`! – Quinten Mar 09 '23 at 09:25

add a total row in a dplyr code but just under a specifc colum

2 Answers2