2

I have a data set of two courses in 2 different semesters that takes the following form: 

set.seed(200)
sem <- sample(c("1", "2"), 200, replace = T)
course <- sample(c("1", "2"), 200, replace = T)
d.gender = sample(c(0, 1), 200, replace = T, prob = c(0.6, 0.4))
d.pass = sample(c(0, 1), 200, replace = T, prob = c(0.7, 0.3))
df <- data.frame(sem, course, d.gender, d.pass)

I'm trying to efficiently create a tbl of the 4 different sem,course combinations along with their total pass rate, the percentage of d.gender = 1, and finally the pass rates within those the 2 gender categories. I can make a table that provides all the values I need to calculate, but I know there's a more efficient way to calculate what I need without nesting a bunch of different group_by and summary functions, or making a whole bunch of different tbls and left_joining the columns I want. I can get what I need grinding away with indices and subset functions, but I'm hopeful that there is a better way to get a 4-row matrix with everything I need but it's ugly and takes forever, and it's easy to make mistakes in the code:

df1 <- df %>% group_by(sem, course, d.gender, d.pass) %>% summarize(total = n())
df1$total_pass <- rep(NA, dim(df1)[1])
df1$total_pass[1:4] <- sum(subset(df1, sem == "1" & course == "1" & d.pass == "1", 
    select = total))
df1$total_pass[5:8] <- sum(subset(df1, sem == "1" & course == "2" & d.pass == "1", 
    select = total))
df1$total_pass[9:12] <- sum(subset(df1, sem == "2" & course == "1" & d.pass == "1", 
    select = total))
df1$total_pass[13:16] <- sum(subset(df1, sem == "2" & course == "2" & d.pass == "1", 
    select = total))

df1$n_male <- rep(NA, dim(df1)[1])
df1$n_male[1:4] <- sum(subset(df1, sem == "1" & course == "1" & d.gender == "1", 
    select = total))
df1$n_male[5:8] <- sum(subset(df1, sem == "1" & course == "2" & d.gender == "1", 
    select = total))
df1$n_male[9:12] <- sum(subset(df1, sem == "2" & course == "1" & d.gender == "1", 
    select = total))
df1$n_male[13:16] <- sum(subset(df1, sem == "2" & course == "2" & d.gender == "1", 
    select = total))

df1$n_fem <- rep(NA, dim(df1)[1])
df1$n_fem[1:4] <- sum(subset(df1, sem == "1" & course == "1" & d.gender == "0", select = total))
df1$n_fem[5:8] <- sum(subset(df1, sem == "1" & course == "2" & d.gender == "0", select = total))
df1$n_fem[9:12] <- sum(subset(df1, sem == "2" & course == "1" & d.gender == "0", 
    select = total))
df1$n_fem[13:16] <- sum(subset(df1, sem == "2" & course == "2" & d.gender == "0", 
    select = total))

df1$pct_male <- rep(NA, dim(df1)[1])
df1$pct_male[1:4] <- df1$n_male[1:4]/sum(subset(df1, sem == "1" & course == "1", 
    select = total))
df1$pct_male[5:8] <- df1$n_male[5:8]/sum(subset(df1, sem == "1" & course == "2", 
    select = total))
df1$pct_male[9:12] <- df1$n_male[9:12]/sum(subset(df1, sem == "2" & course == "1", 
    select = total))
df1$pct_male[13:16] <- df1$n_male[13:16]/sum(subset(df1, sem == "2" & course == "2", 
    select = total))

df1$pct_fem <- rep(NA, dim(df1)[1])
df1$pct_fem <- 1 - df1$pct_male

df1$pct_pass <- rep(NA, dim(df1)[1])
df1$pct_pass[1:4] <- df1$total_pass[1:4]/sum(subset(df1, sem == "1" & course == "1", 
    select = total))
df1$pct_pass[5:8] <- df1$total_pass[5:8]/sum(subset(df1, sem == "1" & course == "2", 
    select = total))
df1$pct_pass[9:12] <- df1$total_pass[9:12]/sum(subset(df1, sem == "2" & course == 
    "1", select = total))
df1$pct_pass[13:16] <- df1$total_pass[13:16]/sum(subset(df1, sem == "2" & course == 
    "2", select = total))

df1$male_pass_pct <- rep(NA, dim(df1)[1])
df1$male_pass_pct[1:4] <- subset(df1, sem == "1" & course == "1" & d.gender == "1" & 
    d.pass == "1", select = total)/df1$n_male[1:4]
df1$male_pass_pct[5:8] <- subset(df1, sem == "1" & course == "2" & d.gender == "1" & 
    d.pass == "1", select = total)/df1$n_male[5:8]
df1$male_pass_pct[9:12] <- subset(df1, sem == "2" & course == "1" & d.gender == "1" & 
    d.pass == "1", select = total)/df1$n_male[9:12]
df1$male_pass_pct[13:16] <- subset(df1, sem == "2" & course == "2" & d.gender == 
    "1" & d.pass == "1", select = total)/df1$n_male[13:16]

df1$fem_pass_pct <- rep(NA, dim(df1)[1])
df1$fem_pass_pct[1:4] <- subset(df1, sem == "1" & course == "1" & d.gender == "0" & 
    d.pass == "1", select = total)/df1$n_fem[1:4]
df1$fem_pass_pct[5:8] <- subset(df1, sem == "1" & course == "2" & d.gender == "0" & 
    d.pass == "1", select = total)/df1$n_fem[5:8]
df1$fem_pass_pct[9:12] <- subset(df1, sem == "2" & course == "1" & d.gender == "0" & 
    d.pass == "1", select = total)/df1$n_fem[9:12]
df1$fem_pass_pct[13:16] <- subset(df1, sem == "2" & course == "2" & d.gender == "0" & 
    d.pass == "1", select = total)/df1$n_fem[13:16]


df2 <- df1 %>% 
    group_by(sem, course) %>% 
    summarize(total_pass = first(total_pass), 
              pct_pass = first(pct_pass), 
              n_male = first(n_male), 
              n_fem = first(n_fem), 
              pct_male = first(pct_male), 
              pct_fem = first(pct_fem), 
              male_pass_pct = first(male_pass_pct), 
              fem_pass_pct = first(fem_pass_pct))

df2 <- unique(df1[, c(1, 2, 6, 11, 7:10, 12, 13)])
df2[, c(9, 10)] <- lapply(df2[, c(9, 10)], as.numeric)

that's really laborious for only needing measures for 4 rows, but I can't get it to work otherwise for this aggregation... Any help would be awesome

zx8754
  • 52,746
  • 12
  • 114
  • 209
slap-a-da-bias
  • 376
  • 1
  • 6
  • 25

1 Answers1

3

Just group and then summarise the original. You can use n() to reference the number of rows in a group, and can reference variables that have been previously created in summarise, which lets you do

df %>% group_by(sem, course) %>% 
    summarise(total_pass = sum(d.pass), 
              n_male = sum(d.gender), 
              n_fem = sum(d.gender == 0), 
              pct_male = n_male / n(), 
              pct_fem = n_fem / n(), 
              pct_pass = total_pass / n(), 
              male_pass_pct = sum(d.gender & d.pass) / n_male, 
              fem_pass_pct = sum(d.gender == 0 & d.pass) / n_fem)

## Source: local data frame [4 x 10]
## Groups: sem [?]
## 
##      sem course total_pass n_male n_fem  pct_male   pct_fem  pct_pass male_pass_pct fem_pass_pct
##   <fctr> <fctr>      <dbl>  <dbl> <int>     <dbl>     <dbl>     <dbl>         <dbl>        <dbl>
## 1      1      1         14     20    30 0.4000000 0.6000000 0.2800000    0.25000000    0.3000000
## 2      1      2          7     19    26 0.4222222 0.5777778 0.1555556    0.05263158    0.2307692
## 3      2      1         12     23    23 0.5000000 0.5000000 0.2608696    0.30434783    0.2173913
## 4      2      2         16     25    34 0.4237288 0.5762712 0.2711864    0.20000000    0.3235294

Reshaping your data to move gender from column headers to an actual variable will make your data tidier and require fewer operations, if you like.

alistaire
  • 42,459
  • 4
  • 77
  • 117
  • thank you, thank you. I knew there was an easier way. I didn't know you could use logical expressions inside summarise function. That makes things much easier – slap-a-da-bias Feb 08 '17 at 21:14