CODEWITHSUNDEEP
rdplyr
Chris S.
Chris S.

Reputation: 2225

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.

Upvotes: 0

Views: 849

Answers (2)

Frank
Frank

Reputation: 66819

Here's a way using data.table, binding rows as in Gregor's answer:

library(data.table)
setDT(x)

dcast(x, id ~ key)[, .(id, key = "n", value = total - a - b)][, rbind(.SD, x)][order(id)]

   id   key value
1:  1     n     7
2:  1     a     1
3:  1     b     2
4:  1 total    10
5:  2     n     5
6:  2     a     4
7:  2     x     1
8:  2     b     3
9:  2 total    12

Upvotes: 2

Gregor Thomas
Gregor Thomas

Reputation: 145755

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

Upvotes: 3

Related Questions