R - Creating new columns based on multiple conditions and time of event

user_13721511 0
Viewed 73

I need to create new columns based on multiple conditions and time points from previous columns. I have the following data frame:

table <- data.frame(RowID=c("A1", "A2", "A3", "A4", "A5", "A6", "A7", "A8", "A9", "A10", "A11", "A12", "A13", "A14", "A15"), Machine=c("Ace", "Ace", "Ace", "Ame", "Ame", "Cay", "Cay", "Cay", "Cay", "Cay", "Gap", "Gap", "Dex", "Dex", "Dex"), Time=c(1,2,3,1,2,1,2,3,4,5,1,2,1,2,3), Status=c("Good", "Good", "Bad", "Bad", "Good", "Good", "Bad", "Good", "Good", "Bad", "Good", "Good", "Bad", "Bad", "Good"))

print(table)
 RowID Machine Time Status
1     A1     Ace    1   Good
2     A2     Ace    2   Good
3     A3     Ace    3    Bad
4     A4     Ame    1    Bad
5     A5     Ame    2   Good
6     A6     Cay    1   Good
7     A7     Cay    2    Bad
8     A8     Cay    3   Good
9     A9     Cay    4   Good
10   A10     Cay    5    Bad
11   A11     Gap    1   Good
12   A12     Gap    2   Good
13   A13     Dex    1    Bad
14   A14     Dex    2    Bad
15   A15     Dex    3   Good

For every Machine, the Time shows when the reading was taken. I would like to create two new columns Verdict and Outcome. For Verdict column, I would like to label "YES" for any Machine with a "Good" status before a "Bad" (e.g. Ace and Cay), otherwise label "NO". For Outcome column, I would like to label "Event" at the first time "Bad" status appears for a Machine, "BeforeEvent" for the "Good" status right before the "Bad" status appears. For any other "Good" status that was not directly before a "Bad", to label "Before" and for any status after the first "Bad" status to be labeled "After".

The final data frame I am hoping to get is as follows:

table_new <- data.frame(RowID=c("A1", "A2", "A3", "A4", "A5", "A6", "A7", "A8", "A9", "A10", "A11", "A12", "A13", "A14", "A15"), Machine=c("Ace", "Ace", "Ace", "Ame", "Ame", "Cay", "Cay", "Cay", "Cay", "Cay", "Gap", "Gap", "Dex", "Dex", "Dex"), Time=c(1,2,3,1,2,1,2,3,4,5,1,2,1,2,3), Status=c("Good", "Good", "Bad", "Bad", "Good", "Good", "Bad", "Good", "Good", "Bad", "Good", "Good", "Bad", "Bad", "Good"), Verdict=c("YES", "YES", "YES", "NO", "NO", "YES", "YES", "YES", "YES", "YES", "NO", "NO", "NO", "NO", "NO"), Outcome=c("Before", "BeforeEvent", "Event", "None", "None", "BeforeEvent", "Event", "After", "After", "After", "None", "None", "None", "None", "None"))

print(table_new)
   RowID Machine Time Status Verdict     Outcome
1     A1     Ace    1   Good     YES      Before
2     A2     Ace    2   Good     YES BeforeEvent
3     A3     Ace    3    Bad     YES       Event
4     A4     Ame    1    Bad      NO        None
5     A5     Ame    2   Good      NO        None
6     A6     Cay    1   Good     YES BeforeEvent
7     A7     Cay    2    Bad     YES       Event
8     A8     Cay    3   Good     YES       After
9     A9     Cay    4   Good     YES       After
10   A10     Cay    5    Bad     YES       After
11   A11     Gap    1   Good      NO        None
12   A12     Gap    2   Good      NO        None
13   A13     Dex    1    Bad      NO        None
14   A14     Dex    2    Bad      NO        None
15   A15     Dex    3   Good      NO        None

Would really appreciate any help with this as I will need to repeat this multiple times so would be great if it could be automated - thank you!

dplyr if-statement r
1 Answers

Here is an example to try (with my_table). It remains unclear if you may have multiple transitions from Good to Bad, or how you might want to handle that.

First, group_by Machine. I would consider an Event where the previous row was "Good", the current row is "Bad". A Boolean can be used to flag when this occurs.

The Verdict will be labelled "YES" if any value within the group is TRUE for Event, otherwise it will be "NO".

Using case_when you can indicate the Outcome comparing the row number within the Machine group with when the Event first occurred (using min in case there was multiple transitions within a group).

I hope this is helpful for you.

library(dplyr)

my_table %>%
  group_by(Machine) %>%
  mutate(Event = ifelse(lag(Status, default = "Bad") == "Good" & Status == "Bad", TRUE, FALSE),
         Verdict = ifelse(any(Event), "YES", "NO"),
         Outcome = ifelse(Verdict == "NO", "None",
           case_when(
             row_number() + 1 < min(which(Event)) ~ "Before",
             row_number() < min(which(Event)) ~ "BeforeEvent",
             row_number() == min(which(Event)) ~ "Event",
             row_number() > min(which(Event)) ~ "After"
           )))

Output

   RowID Machine  Time Status Event Verdict Outcome    
   <chr> <chr>   <dbl> <chr>  <lgl> <chr>   <chr>      
 1 A1    Ace         1 Good   FALSE YES     Before     
 2 A2    Ace         2 Good   FALSE YES     BeforeEvent
 3 A3    Ace         3 Bad    TRUE  YES     Event      
 4 A4    Ame         1 Bad    FALSE NO      None       
 5 A5    Ame         2 Good   FALSE NO      None       
 6 A6    Cay         1 Good   FALSE YES     BeforeEvent
 7 A7    Cay         2 Bad    TRUE  YES     Event      
 8 A8    Cay         3 Good   FALSE YES     After      
 9 A9    Cay         4 Good   FALSE YES     After      
10 A10   Cay         5 Bad    TRUE  YES     After      
11 A11   Gap         1 Good   FALSE NO      None       
12 A12   Gap         2 Good   FALSE NO      None       
13 A13   Dex         1 Bad    FALSE NO      None       
14 A14   Dex         2 Bad    FALSE NO      None       
15 A15   Dex         3 Good   FALSE NO      None

Data

my_table <- structure(list(RowID = c("A1", "A2", "A3", "A4", "A5", "A6", 
"A7", "A8", "A9", "A10", "A11", "A12", "A13", "A14", "A15"), 
    Machine = c("Ace", "Ace", "Ace", "Ame", "Ame", "Cay", "Cay", 
    "Cay", "Cay", "Cay", "Gap", "Gap", "Dex", "Dex", "Dex"), 
    Time = c(1, 2, 3, 1, 2, 1, 2, 3, 4, 5, 1, 2, 1, 2, 3), Status = c("Good", 
    "Good", "Bad", "Bad", "Good", "Good", "Bad", "Good", "Good", 
    "Bad", "Good", "Good", "Bad", "Bad", "Good")), class = "data.frame", row.names = c(NA, 
-15L))
user_34606700
Related