I have a df akin to df1 where I want to break out the rows so that the HOURS column is in intervals of 4, shown in df2. How would I approach this problem and what packages are recommended?
IDs can have more than one sequence on a given day. For example, an ID can be listed 2-3 times on a given day, being assigned more than one unit and and more than one CODE.
The following are required:
df1 (current)
EMPLID TIME_RPTG_CD START_DATE_TIME END_DATE_TIME Hrs_Time_Worked
<chr> <chr> <dttm> <dttm> <dbl>
1 X00007 REG 2014-07-03 16:00:00 2014-07-03 02:00:00 10.0
df2 (desired)
EMPLID TIME_RPTG_CD START_DATE_TIME END_DATE_TIME Hrs_Time_Worked
<chr> <chr> <dttm> <dttm> <dbl>
1 X00007 REG 2014-07-03 16:00:00 2014-07-03 20:00:00 4.0
1 X00007 REG 2014-07-03 20:00:00 2014-07-04 24:00:00 4.0
1 X00007 REG 2014-07-04 24:00:00 2014-07-04 02:00:00 2.0
library(tidyverse)
library(lubridate)
df1%>%
group_by(Row)%>%
mutate(S=paste(START_DATE,START_TIME),
HOURS=list((n<-c(rep(4,HOURS%/%4),HOURS%%4))[n!=0]))%>%
unnest()%>%
mutate(E=dmy_hm(S)+hours(cumsum(HOURS)),
S=E-hours(unlist(HOURS)),
START_DATE=format(S,"%d-%b-%y"),
END_DATE=format(E,"%d-%b-%y"),
START_TIME=format(S,"%H:%M"),
END_TIME=format(E,"%H:%M"),S=NULL,E=NULL)
# A tibble: 6 x 9
# Groups: Row [3]
Row ID UNIT CODE START_DATE END_DATE START_TIME END_TIME HOURS
<chr> <int> <chr> <chr> <chr> <chr> <chr> <chr> <dbl>
1 A 1 3ESD REG 06-Aug-14 06-Aug-14 01:00 05:00 4.
2 A 1 3ESD REG 06-Aug-14 06-Aug-14 05:00 07:00 2.
3 B 2 3E14E OE2 12-Aug-14 13-Aug-14 21:00 01:00 4.
4 C 3 3E5E REG 19-Aug-14 20-Aug-14 21:00 01:00 4.
5 C 3 3E5E REG 20-Aug-14 20-Aug-14 01:00 05:00 4.
6 C 3 3E5E REG 20-Aug-14 20-Aug-14 05:00 07:00 2.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With