Add total rows to data frame by group using two grouping variables in R

March 2, 2023

Suppose I have the data frame like the one below with two grouping variables "Group" and "Gender" and two additional variables with counts:

Group <- c("Group1","Group1","Group2","Group2")
Gender <- c("Male","Female","Male","Female")
Y <- c(7,5,6,10)
N <- c(45,8,2,11)
data <- cbind.data.frame(Group,Gender,Y,N)

> data
   Group Gender  Y  N
1 Group1   Male  7 45
2 Group1 Female  5  8
3 Group2   Male  6  2
4 Group2 Female 10 11

I need to add a total number by group and gender while keeping the group name. Here’s the output I am aiming for:

   Group Gender  Y  N
1 Group1   Male  7 45
2 Group1 Female  5  8
3 Group1  Total 12 53
4 Group2   Male  6  2
5 Group2 Female 10 11
6 Group2  Total 16 13

Could anyone suggest the best way to do this?

I appreciate your help!

>Solution :

We can use group_modify for this.

library(tidyverse)

data %>% 
  group_by(Group) %>% 
  group_modify(~bind_rows(.x, tibble(Gender = "Total", Y = sum(.x$Y), N = sum(.x$N))))

# A tibble: 6 × 4
# Groups:   Group [2]
  Group  Gender     Y     N
  <chr>  <chr>  <dbl> <dbl>
1 Group1 Male       7    45
2 Group1 Female     5     8
3 Group1 Total     12    53
4 Group2 Male       6     2
5 Group2 Female    10    11
6 Group2 Total     16    13