How to create a new column, by variable?

Question

My data includes data on different people (ID) over each Day of the week and the time they spend in different areas of the hospital or Ward. I am given this time, in minutes:seconds or Duration. An example of my data is:

ShiftData <- data.frame(ID = c("Nelson", "Nelson", "Nelson", "Nelson", "Nelson", 
                      "Justin", "Justin", "Justin", "Justin", "Justin", 
                      "Nelson", "Nelson", "Nelson", "Nelson", "Nelson", 
                      "Justin", "Justin", "Justin", "Justin", "Justin"), 
               Day = c("Monday", "Monday", "Monday", "Monday", "Monday", 
                       "Monday", "Monday", "Monday", "Monday", "Monday",
                      "Tuesday", "Tuesday", "Tuesday", "Tuesday", "Tuesday", 
                      "Tuesday", "Tuesday", "Tuesday", "Tuesday", "Tuesday"), 
               Ward = c("Gen", "Anaesth", "Front Desk", "PreOp", "Front Desk", 
                       "PreOp", "Front Desk", "Anaesth", "Front Desk", "Gen",
                       "Gen", "Anaesth", "PreOp", "Front Desk", "Gen", 
                       "Front Desk", "PreOp", "PostOp", "Front Desk", "Anaesth"),
               Duration = c("5:35", "4:08", "4:30", "6:33", "4:17", 
                            "15:35", "4:28", "9:37", "18:33", "4:20",
                            "9:45", "8:28", "6:37", "2:34", "4:27", 
                            "19:35", "4:20", "9:47", "11:33", "4:26"))

I first wish to include a column that indicates when each ID was on a rotation or shift. A "Front Desk" in the Ward column indicates when a person alters their shift. A person may start on "Front Desk", regulated by how many hours they work the day before (this calculation not required for the current analysis). My anticipated output would be:

ShiftData$Shift <- c(1,1,0,2,0,
                     1,0,2,0,3,
                     1,1,1,0,2,
                     0,1,1,0,2)

My question is similar to this question except when there is a "Front Desk" I want a 0 and any activity afterward, to count sequentially up.

How do I please create this?

I know that I can include a 0 for "Front Desk" using:

ShiftData$Shift <- ifelse(ShiftData$Ward=='Front Desk', 0, NA)

But I am unsure how to include a sequential count for every other part of the column?

How in row 2, 12, 13 etc. the numbers are carry forwarded as it is without any increment? — Ronak Shah, Mar 30 '17 at 13:32

score 2 · Accepted Answer · answered Mar 30 '17 at 22:31

This problem can be solved with dplyr:

ShiftData$Shift <- (ShiftData %>%
                    group_by(ID,Day) %>%
                    mutate(tmp = ifelse(Ward=="Front Desk",1,0), #tag to sum front desk shifts
                           tmp2 = cumsum(tmp),                   #cumsum shows shifts in a day
                           Ward1 = Ward[1],                      #this and the below count your first shift if you didn't start on desk duty
                           shift = ifelse(Ward1=="Front Desk",tmp2,tmp2+1))
                    )$shift
ShiftData$Shift[ShiftData$Ward=="Front Desk"] <- 0

score 2 · Answer 2 · edited May 23 '17 at 12:26

Note that your question is quite similar to this one.

So this is a way to solve it:

library(dplyr)

ShiftData %>%
  group_by(ID, Day) %>% 
  mutate(Shift = cumsum(Ward != "Front Desk" & lag(Ward) %in% c("Front Desk", NA))) %>% 
  mutate(Shift = ifelse(Ward == "Front Desk", 0, Shift))

# Source: local data frame [20 x 5]
# Groups: ID, Day [4]
# 
#        ID     Day       Ward Duration Shift
#    <fctr>  <fctr>     <fctr>   <fctr> <dbl>
# 1  Nelson  Monday        Gen     5:35     1
# 2  Nelson  Monday    Anaesth     4:08     1
# 3  Nelson  Monday Front Desk     4:30     0
# 4  Nelson  Monday      PreOp     6:33     2
# 5  Nelson  Monday Front Desk     4:17     0
# 6  Justin  Monday      PreOp    15:35     1
# 7  Justin  Monday Front Desk     4:28     0
# 8  Justin  Monday    Anaesth     9:37     2
# 9  Justin  Monday Front Desk    18:33     0
# 10 Justin  Monday        Gen     4:20     3
# 11 Nelson Tuesday        Gen     9:45     1
# 12 Nelson Tuesday    Anaesth     8:28     1
# 13 Nelson Tuesday      PreOp     6:37     1
# 14 Nelson Tuesday Front Desk     2:34     0
# 15 Nelson Tuesday        Gen     4:27     2
# 16 Justin Tuesday Front Desk    19:35     0
# 17 Justin Tuesday      PreOp     4:20     1
# 18 Justin Tuesday     PostOp     9:47     1
# 19 Justin Tuesday Front Desk    11:33     0
# 20 Justin Tuesday    Anaesth     4:26     2

How it works: After grouping, we create the Shift column by adding 1 each time a non-Front Desk is preceded by a Front Desk. Then we replace Shift by 0 on all Front Desk rows.

How to create a new column, by variable?

2 Answers2