How to filter and count in R based on variables with a type of name

user_10015881 0
Viewed 244

I have educational data in R that looks like this:

df <- data.frame(
   "StudentID" = c(101, 102, 103, 104, 105, 106, 111, 112, 113, 114, 115, 116, 121, 122, 123, 124, 125, 126),
   "FedEthn" = c(1, 1, 2, 2, 3, 3, 1, 1, 2, 2, 3, 3, 1, 1, 2, 2, 3, 3),
   "HIST.11.LEV" = c(1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 3, 3, 3, 5, 3, 3),
   "HIST.11.SCORE" = c(96, 95, 95, 97, 88, 99, 89, 96, 79, 83, 72, 95, 96, 93, 97, 98, 96, 87),
   "HIST.12.LEV" = c(2, 2, 1, 2, 1, 1, 2, 3, 2, 2, 2, 2, 4, 3, 3, 3, 3, 3),
   "SCI.9.LEV" = c(1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3),
   "SCI.9.SCORE" = c(91, 99, 82, 95, 65, 83, 96, 97, 99, 94, 95, 96, 89, 78, 96, 95, 97, 90),
   "SCI.10.LEV" = c(1, 2, 1, 2, 1, 1, 3, 3, 2, 2, 2, 3, 3, 3, 4, 3, 4, 3)
)

##    StudentID  FedEthn  HIST.11.LEV  HIST.11.SCORE  HIST.12.LEV  SCI.9.LEV  SCI.9.SCORE  SCI.10.LEV
## 1        101        1            1             96            2          1           91           1
## 2        102        1            1             95            2          1           99           2
## 3        103        2            1             95            1          1           82           1
## 4        104        2            1             97            2          1           95           2
## 5        105        3            1             88            1          1           65           1
## 6        106        3            1             99            1          1           83           1
## 7        111        1            2             89            2          2           96           3
## 8        112        1            2             96            3          2           97           3
## 9        113        2            2             79            2          2           99           2
## 10       114        2            2             83            2          2           94           2
## 11       115        3            2             72            2          2           95           2
## 12       116        3            2             95            2          2           96           3
## 13       121        1            3             96            4          3           89           3
## 14       122        1            3             93            3          3           78           3
## 15       123        2            3             97            3          3           96           4
## 16       124        2            3             98            3          3           95           3
## 17       125        3            3             96            3          3           97           4
## 18       126        3            3             87            3          3           90           3

HIST.11.LEV stands for the student's academic level in their 11th grade history course. (5 = highest academic level, 1 = lowest academic level. For example, 5 might be an AP or IB course.) HIST.11.SCORE indicates the student's score in the course.

When a student scores 95 or higher in a course, they're eligible to move up to a higher academic level in the following year (such that HIST.12.LEV = 1 + HIST.11.LEV). However, only some of these eligible students actually move up, and the teacher must agree to it. What I'm analyzing is whether these move-up rates for eligible students differ by reported federal ethnicity.

Here's how I'm achieving this so far:

var.level <- 1
var.ethn <- 1

actual.move.ups <- 
  (df %>% filter(FedEthn==var.ethn,
                 HIST.11.LEV==var.level,
                 HIST.11.SCORE>94,
                 HIST.12.LEV==var.level+1) %>% 
     count) +
  (df %>% filter(FedEthn==var.ethn,
                 SCI.9.LEV==var.level,
                 SCI.9.SCORE>94,
                 SCI.10.LEV==var.level+1) %>% 
     count)

eligible.move.ups <- 
  (df %>% filter(FedEthn==var.ethn,
                 HIST.11.LEV==var.level,
                 HIST.11.SCORE>94) %>% 
     count) +
  (df %>% filter(FedEthn==var.ethn,
                 SCI.9.LEV==var.level,
                 SCI.9.SCORE>94) %>% 
     count)

This works, and I could iterate var.level from 1:5 and var.ethnicity from 1:7 and store the results in a data frame. But in my actual data, this approach would require 15 iterations of df %>% filter(...) %>% count (and I'd sum them all). The reason is that, in my actual data, there are 15 opportunities to move up across 5 subjects (HIST, SCI, MATH, ENG, WL) and 4 grade levels (9, 10, 11, 12).

My question is whether there's a more compact way to filter and count all instances where COURSE.GRADE.LEV==i, COURSE.GRADE+1.LEV==i+1, and COURSE.GRADE.SCORE>94 without typing/hard-coding each course name (HIST, SCI, MATH, ENG, WL) and each grade level (9, 10, 11, 12). And, what's the best way to store the results in a data frame?

For my sample data above, here's the ideal output. The data frame doesn't need to have this exact structure, though.

##    FedEthn  L1.Actual  L1.Eligible  L2.Actual  L2.Eligible  L3.Actual  L3.Eligible
## 1        1          3            3          3            3          1            1
## 2        2          2            3          0            1          1            3
## 3        3          0            1          1            3          1            2

*Note: I've read this helpful answer, but for my variable names, the grade level (9, 10, 11, 12) doesn't have a consistent string location (e.g., SCI.9 vs. HIST.11). Also, in some instances, I need to count a single row multiple times, since a single student could move up in multiple classes. Maybe the solution is to reshape the data from wide to long before performing the count?

subset filtering r
1 Answers

Using this great answer from @akrun, I was able to come up with a solution. I think I'm still making it unnecessarily complicated, though, and I hope to accept someone else's more compact answer.

course.names <- c("HIST.","SCI.")
grade.levels <- 9:11

tally.actual <- function(var.ethn, var.level){
  total.tally.actual <- NULL
  for(i in course.names){
    course.tally.actual <- NULL
    for(j in grade.levels){
      new.tally.actual <- df %>% filter(
        FedEthn == var.ethn,
        !!(rlang::sym(paste0(i,j,".LEV"))) == var.level,
        !!(rlang::sym(paste0(i,(j+1),".LEV"))) == (var.level+1),
        !!(rlang::sym(paste0(i,j,".SCORE"))) > 94
      ) %>% count
      course.tally.actual <- c(new.tally.actual, course.tally.actual)
    }
    total.tally.actual <- c(total.tally.actual, course.tally.actual)
  }
  return(sum(unlist(total.tally.actual)))
}

tally.eligible <- function(var.ethn, var.level){
  total.tally.eligible <- NULL
  for(i in course.names){
    course.tally.eligible <- NULL
    for(j in grade.levels){
      new.tally.eligible <- df %>% filter(
        FedEthn == var.ethn,
        !!(rlang::sym(paste0(i,j,".LEV"))) == var.level,
        !!(rlang::sym(paste0(i,j,".SCORE"))) > 94
      ) %>% count
      course.tally.eligible <- c(new.tally.eligible, course.tally.eligible)
    }
    total.tally.eligible <- c(total.tally.eligible, course.tally.eligible)
  }
  return(sum(unlist(total.tally.eligible)))
}

results <- data.frame("FedEthn" = 1:3, 
                      "L1.Actual" = NA, "L1.Eligible" = NA, 
                      "L2.Actual" = NA, "L2.Eligible" = NA, 
                      "L3.Actual" = NA, "L3.Eligible" = NA)

for(var.ethn in 1:3){
  for(var.level in 1:3){
    results[var.ethn,(var.level*2)] <- tally.actual(var.ethn,var.level)
    results[var.ethn,(var.level*2+1)] <- tally.eligible(var.ethn,var.level)
  }
}

This approach works, but it requires df to contain every combination of course (SCI, MATH, HIST, ENG, WL) and year (9, 10, 11, 12). See below for how I added to the original df. Including all possible combinations isn't a problem for my actual data, but I'm hoping there's a solution that doesn't require adding a bunch of columns filled with NA:

df$HIST.9.LEV = NA
df$HIST.9.SCORE = NA
df$HIST.10.LEV = NA
df$HIST.10.SCORE = NA
df$HIST.12.SCORE = NA
df$SCI.10.SCORE = NA
df$SCI.11.LEV = NA
df$SCI.11.SCORE = NA
df$SCI.12.LEV = NA
df$SCI.12.SCORE = NA
user_100158810
Related