I am looking to mimic Excel's SumIfs() function in R by creating a mean-if vector of conditional averages for each observation. I've seen a lot of examples that use aggregate() or setDT() to summarize a data frame based on fixed quantities. However, I'd like to create a vector of these summaries based on the variable inputs of each row in my data frame.
Here is an example of my data:
> a <- c('c', 'a', 'b', 'a', 'c', 'b', 'c', 'a', 'b', 'c', 'a', 'b', 'c', 'b', 'a')
> b <- c(6, 1, 1, 2, 1, 2, 2, 4, 3, 3, 5, 5, 4, 6, 6)
> c <- c(69.9, 21.2, 37, 25, 65.9, 33.1, 67, 28.4, 36, 67, 22, 37.9, 62.3, 30, 25)
> df <- data.frame(cbind(a, b, c))
> df$b <- as.numeric(as.character(df$b))
> df$c <- as.numeric(as.character(df$c))
> df
a b c
1 c 6 69.9
2 a 1 21.2
3 b 1 37.0
4 a 2 25.0
5 c 1 65.9
6 b 2 33.1
7 c 2 67.0
8 a 4 28.4
9 b 3 36.0
10 c 3 67.0
11 a 5 22.0
12 b 5 37.9
13 c 4 62.3
14 b 6 30.0
15 a 6 25.0
I would like to add a fourth column, df$d, that takes the average of df$c for those observations where df$a == x & y - 2 <= df$b < y where x and y are df$a and df$b, respectively, for the observation being calculated.
Doing this by hand, df$d looks like:
> df$d <- c(62.3, NA, NA, 21.2, NA, 37, 65.9, 25, 35.05, 66.45, 28.4, 36, 67, 37.9, 25.2)
> df
a b c d
1 c 6 69.9 62.30
2 a 1 21.2 NA
3 b 1 37.0 NA
4 a 2 25.0 21.20
5 c 1 65.9 NA
6 b 2 33.1 37.00
7 c 2 67.0 65.90
8 a 4 28.4 25.00
9 b 3 36.0 35.05
10 c 3 67.0 66.45
11 a 5 22.0 28.40
12 b 5 37.9 36.00
13 c 4 62.3 67.00
14 b 6 30.0 37.90
15 a 6 25.0 25.20
Is there a function I can use to do this automatically? Thanks for your help!
This can be done in a straightforward way using a left self-join in SQL. This joins to each row of the u instance of df those rows of the v instance of df that satisfy the on condition and then averages over their c values.
library(sqldf)
sqldf("select u.*, avg(v.c) as d
from df u left join df v
on u.a = v.a and v.b between u.b-2 and u.b-1
group by u.rowid")
giving:
a b c d
1 c 6 69.9 62.30
2 a 1 21.2 NA
3 b 1 37.0 NA
4 a 2 25.0 21.20
5 c 1 65.9 NA
6 b 2 33.1 37.00
7 c 2 67.0 65.90
8 a 4 28.4 25.00
9 b 3 36.0 35.05
10 c 3 67.0 66.45
11 a 5 22.0 28.40
12 b 5 37.9 36.00
13 c 4 62.3 67.00
14 b 6 30.0 37.90
15 a 6 25.0 25.20