Create total and sub-total with group

I have a dataset where I compare national sales vs total sales (national international) by year. My database looks like this:

structure(list(year = c(2012, 2012, 2012, 2012, 2013, 2013, 2013, 
2013, 2013, 2014, 2014, 2014, 2014, 2014, 2014, 2015, 2015, 2015, 
2015, 2015, 2015, 2015, 2015), market = c("national", "international", 
"national", "international", "national", "national", "national", 
"international", "national", "national", "international", "national", 
"national", "national", "international", "international", "national", 
"international", "national", "international", "national", "national", 
"national"), amount = c(253, 123, 165, 265, 216, 65, 214, 416, 
67, 156, 563, 327, 200, 392, 690, 135, 160, 89, 50, 206, 199, 
50, 57)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-23L))

In order to create a line plot, I create the mutate the dataset as follows:

df2<- df |>
    group_by(year, market) |>
    summarise(amount=sum(amount))

However, now I have total data for national and for international markets, but I would like to substitute the data for international markets for total = national international. Is there a way to do this? I tried with a few different ways to group / ungroup but have not managed to figure it out.

CodePudding user response：

I'm not exactly sure what you're looking for here, but would something like this work:

library(dplyr)
sum_dat <- dat %>%
  group_by(market, year) %>% 
  summarise(amount = sum(amount))
#> `summarise()` has grouped output by 'market'. You can override using the
#> `.groups` argument.

alldat <- dat %>% group_by(year) %>% 
  summarise(amount = sum(amount)) %>% 
  mutate(market = "total") %>% 
  dplyr::select(all_of(names(sum_dat))) %>% 
  bind_rows(sum_dat, .)
alldat
#> # A tibble: 12 × 3
#> # Groups:   market [3]
#>    market         year amount
#>    <chr>         <dbl>  <dbl>
#>  1 international  2012    388
#>  2 international  2013    416
#>  3 international  2014   1253
#>  4 international  2015    430
#>  5 national       2012    418
#>  6 national       2013    562
#>  7 national       2014   1075
#>  8 national       2015    516
#>  9 total          2012    806
#> 10 total          2013    978
#> 11 total          2014   2328
#> 12 total          2015    946

^{Created on 2022-12-30 by the reprex package (v2.0.1)}