Assuming the following example:
set.seed(1234)
df=data.frame(grupo=rep(c("A1","A1.2","C","D"), 3),
ano=c(rep(2007,4),rep(2008,4),rep(2009,4)),
valor1=sample.int(100,12), valor2=sample(20,12),
stringsAsFactors = FALSE)
grupo ano valor1 valor2
1 A1 2007 12 6
2 A1.2 2007 62 18
3 C 2007 60 20
4 D 2007 61 15
5 A1 2008 83 5
6 A1.2 2008 97 16
7 C 2008 1 3
8 D 2008 22 4
9 A1 2009 99 13
10 A1.2 2009 47 12
11 C 2009 63 2
12 D 2009 49 1
I need to subtract from A1 the values of A1.2 for "value1" and "value2" (the original data frame has many more columns).
The solution I came up with is very laborious and at the end joins the lines of the two data frames created:
df2= df %>%
filter(grupo=="A1" | grupo=="A1.2") %>%
group_by(ano) %>%
arrange(ano,grupo) %>%
mutate(d1 = valor1 - lead(valor1, n=1)) %>%
mutate(d2 = valor2 - lead(valor2, n=1)) %>%
filter(grupo=="A1") %>%
mutate(grupo="Am") %>%
select(-3,-4) %>%
rename(valor1=d1,valor2=d2)
# A tibble: 3 x 4
# Groups: ano [3]
grupo ano valor1 valor2
<chr> <dbl> <int> <int>
1 Am 2007 -59 -15
2 Am 2008 75 -1
3 Am 2009 -18 -2
df= df %>%
filter(grupo!="A1" & grupo!="A1.2")
df=bind_rows(df,df2)
grupo ano valor1 valor2
1 C 2007 60 20
2 D 2007 61 15
3 C 2008 1 3
4 D 2008 22 4
5 C 2009 63 2
6 D 2009 49 1
7 Am 2007 -59 -15
8 Am 2008 75 -1
9 Am 2009 -18 -2
My questions are: 1. Would there be a simpler solution? 2. I can not figure out how to do the "mutate_all" for all columns at the same time except "group" and "year."