Grouping instances and summarizing by probability of a condition being true

user_11301182 0
Viewed 57

I have data with the following structure, where each responder is assigned a task that could either have a status being TRUE or FALSE.

  month         Responder       Status  Department
  2020-02-01    A                 TRUE  1
  2020-02-01    B                FALSE  1
  2020-02-01    B                 TRUE  1
  2020-02-01    C                 TRUE  1
  2020-02-01    C                 TRUE  1
  2020-03-01    D                FALSE  2
  2020-03-01    E                FALSE  1
  2020-03-01    B                FALSE  1
  2020-03-01    F                FALSE  2
  2020-03-01    F                 TRUE  2
  2020-03-01    F                 TRUE  2

I want to output a data frame so that each responder is given a probability of having Status = FALSE. I would like to group these results by month and department as follows:

  month         Responder    Prob_False    N    n
  2020-02-01    A            0             1    0
  2020-02-01    B            0.5           2    1  
  2020-02-01    C            0             2    0  
  2020-03-01    B            1             1    1
  2020-03-01    D            1             1    1
  2020-03-01    E            1             1    1
  2020-03-01    F            0.333         3    1

Where N is the total number of tasks assigned to the responder for that month and n is the number of tasks that had a FALSE status, grouped by the month and the responder.

I am trying to use the group_by and summarize functions in dplyr, but I guess I am not grasping the correct application for this particular problem.

dplyr r
1 Answers

We can do a group by mean of the logical column, and get the frequency count with n() while getting the sum of logical vector for count of FALSE. When we negate (!, the TRUE/FALSE changes to FALSE/TRUE and TRUE/FALSE would be stored as 1/0 thus the mean or sum works

library(dplyr)
df1 %>% 
    group_by(month, Responder) %>% 
    summarise(Prob_False = mean(!Status), N = n(), n = sum(!Status))
# A tibble: 7 x 5
# Groups:   month [2]
#  month      Responder Prob_False     N     n
#  <chr>      <chr>          <dbl> <int> <int>
#1 2020-02-01 A              0         1     0
#2 2020-02-01 B              0.5       2     1
#3 2020-02-01 C              0         2     0
#4 2020-03-01 B              1         1     1
#5 2020-03-01 D              1         1     1
#6 2020-03-01 E              1         1     1
#7 2020-03-01 F              0.333     3     1

i.e. mean(!Status) is basicaly sum(Status == FALSE)/n()

data

df1 <- structure(list(month = c("2020-02-01", "2020-02-01", "2020-02-01", 
"2020-02-01", "2020-02-01", "2020-03-01", "2020-03-01", "2020-03-01", 
"2020-03-01", "2020-03-01", "2020-03-01"), Responder = c("A", 
"B", "B", "C", "C", "D", "E", "B", "F", "F", "F"), Status = c(TRUE, 
FALSE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE
), Department = c(1L, 1L, 1L, 1L, 1L, 2L, 1L, 1L, 2L, 2L, 2L)),
class = "data.frame", row.names = c(NA, 
-11L))
user_37322710
Related