Chris S. Chris S. - 10 days ago 5
R Question

Add missing subtotals to each group using dplyr

I need to add a new row to each id group where the key= "n" and value is the total - a + b

x <- data_frame( id = c(1,1,1,2,2,2,2),
key = c("a","b","total","a","x","b","total"),
value = c(1,2,10,4,1,3,12) )

# A tibble: 7 × 3
id key value
<dbl> <chr> <dbl>
1 1 a 1
2 1 b 2
3 1 total 10
4 2 a 4
5 2 x 1
6 2 b 3
7 2 total 12


In this example, the new rows should be

1 n 7
2 n 5


I tried getting the a+b subtotal and joining that to the total count to get the difference, but after using nine dplyr verbs I seem to be going in the wrong direction. Thanks.

Answer

This isn't a join, it's just binding new rows on:

x %>% group_by(id) %>%
    summarize(
      value = sum(value[key == 'total']) - sum(value[key %in% c('a', 'b')]),
      key = 'n'
    ) %>%
    bind_rows(x) %>%
    select(id, key, value) %>%  # back to original column order
    arrange(id, key)            # and a start a row order
# # A tibble: 9 × 3
#      id   key value
#   <dbl> <chr> <dbl>
# 1     1     a     1
# 2     1     b     2
# 3     1     n     7
# 4     1 total    10
# 5     2     a     4
# 6     2     b     3
# 7     2     n     5
# 8     2 total    12
# 9     2     x     1
Comments