it is possible to subtract certain rows and rename them?
year <- c(2005,2005,2005,2006,2006,2006,2007,2007,2007)
category <- c("a","b","c","a","b","c", "a", "b", "c")
value <- c(2,2,10,3,3,12,4,4,16)
df <- data.frame(year, category,value, stringsAsFactors = FALSE)
And this is how the result should look:
| year | category | value |
|---|---|---|
| 2005 | a | 2 |
| 2005 | b | 2 |
| 2005 | c | 4 |
| 2006 | a | 3 |
| 2006 | b | 3 |
| 2006 | c | 12 |
| 2007 | a | 4 |
| 2007 | b | 4 |
| 2007 | c | 16 |
| 2005 | c-b | 2 |
| 2006 | c-b | 9 |
| 2007 | c-b | 12 |
You can use group_modify:
library(tidyverse)
df %>%
group_by(year) %>%
group_modify(~ add_row(.x, category = "c-b", value = .x$value[.x$category == "c"] - .x$value[.x$category == "b"]))
# A tibble: 12 x 3
# Groups: year [3]
year category value
<dbl> <chr> <dbl>
1 2005 a 2
2 2005 b 2
3 2005 c 10
4 2005 c-b 8
5 2006 a 3
6 2006 b 3
7 2006 c 12
8 2006 c-b 9
9 2007 a 4
10 2007 b 4
11 2007 c 16
12 2007 c-b 12