Calculating AVERAGEIF in R by factor level

Question

In Calculating percentiles by factor using ave() in r, I asked how to calculate percentiles within the ave() function. With that task finished, I'm faced with a more difficult task.

Take the following data:

DistrictName            Building Name   X2.Yr.AVG       Thirty          Seventy
Ionia Public Schools    Emerson         -0.337464323    -0.196387489    -0.046524185
Ionia Public Schools    Jefferson       -0.318673587    -0.196387489    -0.046524185
Ionia Public Schools    Ionia Middle    -0.290854669    -0.196387489    -0.046524185
Ionia Public Schools    Ionia Middle    -0.288202752    -0.196387489    -0.046524185
Ionia Public Schools    Twin Rivers El  -0.23426755     -0.196387489    -0.046524185
Ionia Public Schools    R.B. Boyce El   -0.202319963    -0.196387489    -0.046524185
Ionia Public Schools    Twin Rivers El  -0.142995221    -0.196387489    -0.046524185
Ionia Public Schools    Emerson         -0.141620372    -0.196387489    -0.046524185
Ionia Public Schools    Jefferson       -0.141407078    -0.196387489    -0.046524185
Ionia Public Schools    R.B. Boyce El   -0.115530249    -0.196387489    -0.046524185
Ionia Public Schools    Ionia Middle    -0.111449269    -0.196387489    -0.046524185
Ionia Public Schools    Twin Rivers El  -0.054918339    -0.196387489    -0.046524185
Ionia Public Schools    Jefferson       -0.045591501    -0.196387489    -0.046524185
Ionia Public Schools    A.A. Rather     0.002251298     -0.196387489    -0.046524185
Ionia Public Schools    R.B. Boyce El   0.020669633     -0.196387489    -0.046524185
Ionia Public Schools    Emerson         0.065064968     -0.196387489    -0.046524185
Ionia Public Schools    A.A. Rather     0.182776319     -0.196387489    -0.046524185

What I'm trying to do is something akin to what the AVERAGEIF function in Excel. In Excel, I can say =AVERAGEIF(C2:C18, "<-.196387489"), which spits out the average value -0.278630474. I need something in R that allows me to do the following: I want to create new variables for the average value of: 1) any values of X2.Yr.AVG that are smaller than the value of Thirty 2) any values that are larger than the value of Seventy

The catch is that I need to be able to perform this operation in a large data frame with 722 levels for the factor DistrictName. In the step for calculating the percentiles, I used the ave() function to create percentiles according to the desired factor as follows:

    MATHgap$Thirty<-ave(MATHgap$X2.Yr.AVG, MATHgap$DistrictName, 
       FUN= function(x) quantile(x, 0.3))

and

    MATHgap$Seventy<-ave(MATHgap$X2.Yr.AVG, MATHgap$DistrictName, 
       FUN= function(x) quantile(x, 0.7))

Is there any way to do something akin to AVERAGEIF within ave() so that the operation is repeated for each value of DistrictName independently of the others? I.e, Ionia Public Schools should have an average value for X2.Yr.AVG less than -0.196387489 and for X2.Yr.AVG greater than -0.046524185, and I want to be able to perform the same function for all districts using their respective values for X2.Yr.AVG, Thirty, and Seventy.

If this is confusing, apologies.

by(X2.Yr.AVG, MATHgap$DistrictName, function(x) {p = quantile(x, c(0.3, 0.7)); mean(x[(x>p[1] & x<p[2])])}) by, aggregate, ave, tapply basically do the same thing with different syntax. — Vlo

Nick Kennedy Nick Kennedy · Accepted Answer · 2015-06-29T18:29:41

Here's a solution using dplyr:

MATHgap %>%
  group_by(DistrictName) %>%
  mutate(MeanLT30 = mean(X2.Yr.AVG[X2.Yr.AVG < Thirty]),
    MeantGT70 = mean(X2.Yr.AVG[X2.Yr.AVG > Seventy]))

Calculating AVERAGEIF in R by factor level

1 Answers