Mutating multiple columns to get 1 or 0 for passfail conditions

user_2359320 0
Viewed 89

I have a dataframe like this 

ID <- c("A","A","A","A","A","A","A","A")
Step <- c("Step_1","Step_1","Step_2","Step_2","Step_3","Step_3","Step_3","Step_4")
Passfail <- c("Pass","Pass","Fail","Pass","Fail","Fail","Pass","Fail")
Measurement <- c("Length","Length","Breadth","Breadth",
                 "Height","Height","Height","Width")

df <- data.frame(ID,Step,Passfail,Measurement)

I am trying to create several columns that when true should return 1 else 0 GROUPED by (Measurement,ID,ToolID)

For each group,

  1. AP = 1 if only passes in the passfail
  2. AF = 1 if only fails in the passfail
  3. SFP = 1 if there is only 1 fail and atleast 1 pass in passfail
  4. MFP = 1 if there are more than 1 fails and atleast 1 pass in passfail.

Desired Output is

  Measurement ID   Step AP AF SFP MFP
       Length  A Step_1  1  0   0   0
      Breadth  A Step_2  0  0   1   0
       Height  A Step_3  0  0   0   1
        Width  A Step_4  0  1   0   0

I am trying it this way to get the AP & AF columns but not quite getting it right

library(dplyr)
df1 <- df %>%
  group_by(Measurement,ID,Step) %>% 
  mutate(AP = case_when((Passfail == "Pass" & Passfail != "Fail") ~ 1, TRUE ~ 0),
         AF = case_when((Passfail == "Fail" & Passfail != "Pass") ~ 1, TRUE ~ 0)
         ) %>%
  distinct()
data.table dplyr tidyverse dataframe r
1 Answers

Here's a fixed version of your approach:

df %>%
  group_by(Measurement,ID,Step) %>% 
  summarize(AP = case_when(all(Passfail == "Pass") ~ 1, TRUE ~ 0),
            AF = case_when(all(Passfail == "Fail") ~ 1, TRUE ~ 0),
            SFP = case_when(sum(Passfail == "Fail") == 1 & sum(Passfail == "Pass") > 0 ~ 1, TRUE ~ 0),
            MFP = case_when(sum(Passfail == "Fail") > 1 & sum(Passfail == "Pass") > 0 ~ 1, TRUE ~ 0))
# A tibble: 4 x 7
# Groups:   Measurement, ID [?]
#   Measurement ID    Step      AP    AF   SFP   MFP
#   <fct>       <fct> <fct>  <dbl> <dbl> <dbl> <dbl>
# 1 Breadth     A     Step_2     0     0     1     0
# 2 Height      A     Step_3     0     0     0     1
# 3 Length      A     Step_1     1     0     0     0
# 4 Width       A     Step_4     0     1     0     0

With all(...) we require that the condition holds for all the cases of Passfail, while with sum(Passfail == "Fail") we count the number of failures. With those two techniques we cover all four cases.

Notice, though, that since for each variable you have only two cases, you may also simplify the code a little to

df %>% 
  group_by(Measurement,ID,Step) %>% 
  summarize(AP = 1 * all(Passfail == "Pass"),
            AF = 1 * all(Passfail == "Fail"),
            SFP = 1 * (sum(Passfail == "Fail") == 1 & sum(Passfail == "Pass") > 0),
            MFP = 1 * (sum(Passfail == "Fail") > 1 & sum(Passfail == "Pass") > 0))

The logical expressions give TRUE or FALSE and when multiplying by 1 we coerce those logical vectors into a binary ones, as needed.

user_13205350
Related