CODEWITHSUNDEEP
rdplyrtidyverse
ccwiris
ccwiris

Reputation: 29

mutate a new column based on some conditions in R

This is the table:

|WONUM | CHILD_CRAFT_LIST   | is_there_water_damage | repair_required_for_this_apartment |
|------|--------------------|-----------------------|------------------------------------|
| 1    | MAINT,MAINT        |Y                      |                                    |
| 2    | MAINT,SOHC,MAINT   |                       |Y                                   |
| 3    | MAINT,PAINTER,MAINT|Y                      |Y                                   |
| 4    | MAINT,SOHC,PLUMBER |Y                      |                                    |
| 5    | MAINT              |                       |                                    |

I want to create a new column "REPAIR_TYPE" based on the following condition:

  1. either is_there_water_damage == 'Y' or repair_required_for_this_apartment == 'Y' and all crafts in CHILD_CRAFT_LIST are in ("CARETAKE","MAINT","CHIEFCTK"), the REPAIR_TYPE will be 'Simple'
  2. either is_there_water_damage == 'Y' or repair_required_for_this_apartment == 'Y' and at least one craft in CHILD_CRAFT_LIST are not in ("CARETAKE","MAINT","CHIEFCTK"), the REPAIR_TYPE will be 'Complex'

so the final table I want is following:

|WONUM | CHILD_CRAFT_LIST   | is_there_water_damage | repair_required_for_this_apartment |REPAIR_TYPE|
|------|--------------------|-----------------------|------------------------------------|------|
| 1    | MAINT,MAINT        |Y                      |                                    |Simple|
| 2    | MAINT,SOHC,MAINT   |                       |Y                                   |Complex|
| 3    | MAINT,PAINTER,MAINT|Y                      |Y                                   |Complex|
| 4    | MAINT,SOHC,PLUMBER |Y                      |                                    |Complex|
| 5    | MAINT              |                       |                                    |NA    |

I created a list

craft <- c("CARETAKE","MAINT","CHIEFCTK")

I used case_when in mutate() function, but the result tuned out incorrectly. here is my code

table %>% 
  mutate(REPAIR_TYPE=case_when((is_there_water_damage == 'Y' | repair_required_for_this_apartment == 'Y') & all(CHILD_CRAFT_LIST %in% craft)) ~ 'Simple',
(is_there_water_damage == 'Y' | repair_required_for_this_apartment == 'Y') & any(CHILD_CRAFT_LIST %in% craft)) ~ 'Complex'))

can anyone help me fix the code? thanks!

Upvotes: 0

Views: 37

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 389325

You would need rowwise if you have list of values in CHILD_CRAFT_LIST.

library(dplyr)

df %>%
  #Use this step if CHILD_CRAFT_LIST is a comma separated string.
  #mutate(CHILD_CRAFT_LIST = strsplit(CHILD_CRAFT_LIST, ',')) %>%
  rowwise() %>%
  mutate(REPAIR_TYPE = case_when((is_there_water_damage == 'Y' | 
                                 repair_required_for_this_apartment == 'Y') & 
                                 all(CHILD_CRAFT_LIST %in% craft) ~ 'Simple', 
                                  (is_there_water_damage == 'Y' | 
                                  repair_required_for_this_apartment == 'Y') & 
                                   any(!CHILD_CRAFT_LIST %in% craft) ~ 'Complex'))

#  WONUM CHILD_CRAFT_LIST is_there_water_damage repair_required_for_this_apartment REPAIR_TYPE
#  <int> <list>           <chr>                 <chr>                              <chr>      
#1     1 <chr [2]>        "Y"                   ""                                 Simple     
#2     2 <chr [3]>        ""                    "Y"                                Complex    
#3     3 <chr [3]>        "Y"                   "Y"                                Complex    
#4     4 <chr [3]>        "Y"                   ""                                 Complex    
#5     5 <chr [1]>        ""                    ""                                 NA

data

df <- structure(list(WONUM = 1:5, CHILD_CRAFT_LIST = c("MAINT,MAINT", 
"MAINT,SOHC,MAINT", "MAINT,PAINTER,MAINT", "MAINT,SOHC,PLUMBER", 
"MAINT"), is_there_water_damage = c("Y", "", "Y", "Y", ""), repair_required_for_this_apartment = c("", 
"Y", "Y", "", "")), row.names = c(NA, -5L), class = "data.frame")

Upvotes: 1

Related Questions