How do I create a new factor level that summarizes total values of other factor levels?

user_16604406 0
Viewed 164

I have a dataset where I have at least three columns

  year sex value
1 2019   M    10
2 2019   F    20
3 2020   M    50
4 2020   F    20

I would like to group by the first column, year, and then add another level to sex that corresponds the total value in column 3, that is, I would like something like this:

   year sex   value
  <int> <chr> <dbl>
1  2019 M        10
2  2019 F        20
3  2019 Total    30
4  2020 M        50
5  2020 F        20
6  2020 Total    70

Any help is appreciated, especially in dplyr.

dplyr r
2 Answers

You can summarise the data for each year and bind it to the original dataset.

library(dplyr)

df %>%
  group_by(year) %>%
  summarise(sex = 'Total', 
            value = sum(value)) %>%
  bind_rows(df) %>%
  arrange(year, sex)

#   year sex   value
#  <int> <chr> <dbl>
#1  2019 F        20
#2  2019 M        10
#3  2019 Total    30
#4  2020 F        20
#5  2020 M        50
#6  2020 Total    70

Or in base R -

aggregate(value~year, df, sum) |>
  transform(sex = 'Total') |>
  rbind(df)

data

df <- data.frame(year = rep(2019:2020, each = 2), 
                 sex = c('M', 'F'), value = c(10, 20, 50, 20))
user_39629140

Here is just another way of doing this:

library(dplyr)
library(purrr)

df %>%
  group_split(year) %>%
  map_dfr(~ add_row(.x, year = first(.x$year), sex = "Total", value = sum(.x$value)))

# A tibble: 6 x 3
   year sex   value
  <int> <chr> <dbl>
1  2019 M        10
2  2019 F        20
3  2019 Total    30
4  2020 M        50
5  2020 F        20
6  2020 Total    70
user_143145200
Related