CODEWITHSUNDEEP
rdplyr
HNSKD
HNSKD

Reputation: 1644

How to combine selected rows and slice a row for the others?

I have a data below:

enter image description here

df <- structure(list(Remarks = c("deployment (ct) - on site", "deployment (ct) - on site", 
"am - perimeter screening, pm -wfh", "am - perimeter screening, pm -wfh", 
"on site for perimeter screening @blk4 main", "on site for perimeter screening @blk4 main", 
"onsite: am deployment, bowyer block c level 1 (dmc)"), am_pm = c(NA, 
NA, "am|pm", "am|pm", NA, NA, "am"), `Work Status` = c("On-Duty (Deployment)", 
"On-Duty (On-Site)", "On-Duty (WFH)", "On-Duty (Deployment)", 
"On-Duty (Deployment)", "On-Duty (On-Site)", "On-Duty (On-Site)"
)), row.names = c(NA, 7L), class = "data.frame")

This is what I would like to do:

  1. If for the same Remarks, am_pm == "am|pm" - then combine Work Status
  2. If for the same Remarks, am_pm == "am" - then append "On-Duty (WFH)"
  3. If for the same Remarks, is.na(am_pm) - then take the first line item

Desired Output:

enter image description here

I can't think of how to do it efficiently - Hope to get some help. Thanks!

Upvotes: 0

Views: 43

Answers (2)

Ronak Shah
Ronak Shah

Reputation: 388982

Similar answer as @Jrm_FRL but using different conditions. 

library(dplyr)

df %>%
  group_by(Remarks) %>%
  summarise(Work_status = case_when(
       all(am_pm == 'am|pm') ~ toString(`Work Status`),
       all(is.na(am_pm)) ~ first(`Work Status`), 
       any(am_pm == 'am') ~ toString(c('On-Duty (WFH)', `Work Status`))))


# A tibble: 4 x 2
#  Remarks                                             Work_status                               
#  <chr>                                               <chr>                                     
#1 am - perimeter screening, pm -wfh                   On-Duty (WFH), On-Duty (Deployment)       
#2 deployment (ct) - on site                           deployment (ct) - on site                 
#3 on site for perimeter screening @blk4 main          On-Duty (Deployment) 
#4 onsite: am deployment, bowyer block c level 1 (dmc) On-Duty (WFH), On-Duty (On-Site)

Upvotes: 1

Jrm_FRL
Jrm_FRL

Reputation: 1413

Here is a proposal using case_when: 

df %>% 
  group_by(Remarks) %>%
  summarise("Work Status" = case_when(
    unique(am_pm) == "am|pm" ~ paste(`Work Status`, collapse = "; "),
    unique(am_pm) == "am" ~ paste("On-Duty (WFH);", paste(`Work Status`, collapse = "; ")),
    all(is.na(am_pm)) ~ `Work Status`[1],
    TRUE ~ "error"
  )
 )

# # A tibble: 4 x 2
# Remarks                                             `Work Status`                      
# <chr>                                               <chr>                              
#   1 am - perimeter screening, pm -wfh                   On-Duty (WFH); On-Duty (Deployment)
# 2 deployment (ct) - on site                           On-Duty (Deployment)               
# 3 on site for perimeter screening @blk4 main          On-Duty (Deployment)               
# 4 onsite: am deployment, bowyer block c level 1 (dmc) On-Duty (WFH); On-Duty (On-Site)

Upvotes: 2

Related Questions