R-tist
Reputation: 21
How to extract time intervals from a string column in R?
Here is the dput result of the data too:
x=c("Wed: 9:00 AM-1:00 PM;", "Sat: 9:00 AM-1:00 PM;", NA, "Wed: 3:00 PM-6:00 PM;Sat: 8:00 AM-1:00 PM;",
"Tue:8:00 am - 5:00 pm;Sat:8:00 am - 8:00 pm;", "Tue: 3:30 PM-6:30 PM;",
"Tue: 10:00 AM-7:00 PM;", "Fri: 8:00 AM-11:00 AM;", "Sat: 9:00 AM-1:00 PM;",
"Sat: 9:00 AM-1:00 PM;", "Wed: 2:30 PM-6:30 PM;", "Tue: 8:00 AM-6:00 PM;",
"Wed:3:00 pm - 7:00 pm;", "Thu: 3:00 PM-6:30 PM;", "Thu: 3:00 PM-7:00 PM;",
"Sat: 8:00 AM-11:00 AM;", "Sat: 8:00 AM-12:00 PM;", "Sat: 8:30 AM-1:00 PM;",
"Wed: 11:00 AM-6:00 PM;Thu: 11:00 AM-6:00 PM;Fri: 11:00 AM-6:00 PM;Sat: 11:00 AM-6:00 PM;",
"Fri: 7:30 AM-12:00 PM;", "Tue: 2:00 PM-6:00 PM;", "Sat: 8:30 AM-2:00 PM;",
"Sun: 9:00 AM-1:00 PM;", "Wed: 1:00 PM-5:00 PM;", "Tue: 2:00 PM-6:00 PM;",
"Thu: 3:30 PM-6:30 PM;", "Thu: 11:00 AM-3:00 PM;Fri: 11:00 AM-3:00 PM;Sat: 8:00 AM-3:00 PM;",
"Sat: 8:00 AM-2:00 PM;", "Sun: 9:00 AM-1:00 PM;", "Sun: 10:00 AM-2:00 PM;",
"Sat: 7:00 AM-12:00 PM;", "Thu: 2:00 PM-6:00 PM;", "Thu: 4:00 PM-6:30 PM;",
"Thu: 4:00 PM-9:00 PM;", "Sat:8:30 AM - 1:30 PM;", "Tue: 3:00 PM-7:00 PM;",
"Tue: 1:00 PM-5:00 PM;", NA, NA, "Wed: 1:00 PM-5:00 PM;", "Wed: 8:00 AM-5:00 PM;Sat: 8:00 AM-5:00 PM;")
I want to find out which companies close before 2 PM, and which companies open only for fewer than 4 hours a day. For companies with different open hours across a week, I want to use the average length of open hours for the days they actually open.
I am brand new to R and haven't been able to accomplish much to show for this problem. Appreciate any help provided. Thanks!
Upvotes: 1
Views: 226
Answers (1)
Onyambu
Reputation: 79288
data.frame(company=paste0("company",seq_along(x)),x,stringsAsFactors = F)%>%
unnest_tokens(word, x, token = strsplit, split= ";",)%>%
separate(word,c("day","open_time","close_time"),"(?<=[a-z]):|-")%>%
mutate(duration=strptime(close_time,"%I:%M %p")-strptime(open_time,"%I:%M %p"),
durinterval=duration<4,
closeearlierthan2=as.numeric(format(strptime(close_time,"%I:%M %p"),"%H"))<14)
company day open_time close_time duration durinterval closeearlierthan2
1 company1 wed 9:00 am 1:00 pm 4.0 hours FALSE TRUE
2 company2 sat 9:00 am 1:00 pm 4.0 hours FALSE TRUE
3 company3 <NA> <NA> <NA> NA hours NA NA
4 company4 wed 3:00 pm 6:00 pm 3.0 hours TRUE FALSE
5 company4 sat 8:00 am 1:00 pm 5.0 hours FALSE TRUE
6 company5 tue 8:00 am 5:00 pm 9.0 hours FALSE FALSE
7 company5 sat 8:00 am 8:00 pm 12.0 hours FALSE FALSE
8 company6 tue 3:30 pm 6:30 pm 3.0 hours TRUE FALSE
9 company7 tue 10:00 am 7:00 pm 9.0 hours FALSE FALSE
10 company8 fri 8:00 am 11:00 am 3.0 hours TRUE TRUE
11 company9 sat 9:00 am 1:00 pm 4.0 hours FALSE TRUE
12 company10 sat 9:00 am 1:00 pm 4.0 hours FALSE TRUE
13 company11 wed 2:30 pm 6:30 pm 4.0 hours FALSE FALSE
14 company12 tue 8:00 am 6:00 pm 10.0 hours FALSE FALSE
15 company13 wed 3:00 pm 7:00 pm 4.0 hours FALSE FALSE
16 company14 thu 3:00 pm 6:30 pm 3.5 hours TRUE FALSE
17 company15 thu 3:00 pm 7:00 pm 4.0 hours FALSE FALSE
18 company16 sat 8:00 am 11:00 am 3.0 hours TRUE TRUE
19 company17 sat 8:00 am 12:00 pm 4.0 hours FALSE TRUE
20 company18 sat 8:30 am 1:00 pm 4.5 hours FALSE TRUE
21 company19 wed 11:00 am 6:00 pm 7.0 hours FALSE FALSE
22 company19 thu 11:00 am 6:00 pm 7.0 hours FALSE FALSE
23 company19 fri 11:00 am 6:00 pm 7.0 hours FALSE FALSE
24 company19 sat 11:00 am 6:00 pm 7.0 hours FALSE FALSE
25 company20 fri 7:30 am 12:00 pm 4.5 hours FALSE TRUE
26 company21 tue 2:00 pm 6:00 pm 4.0 hours FALSE FALSE
27 company22 sat 8:30 am 2:00 pm 5.5 hours FALSE FALSE
28 company23 sun 9:00 am 1:00 pm 4.0 hours FALSE TRUE
29 company24 wed 1:00 pm 5:00 pm 4.0 hours FALSE FALSE
30 company25 tue 2:00 pm 6:00 pm 4.0 hours FALSE FALSE
31 company26 thu 3:30 pm 6:30 pm 3.0 hours TRUE FALSE
32 company27 thu 11:00 am 3:00 pm 4.0 hours FALSE FALSE
33 company27 fri 11:00 am 3:00 pm 4.0 hours FALSE FALSE
34 company27 sat 8:00 am 3:00 pm 7.0 hours FALSE FALSE
35 company28 sat 8:00 am 2:00 pm 6.0 hours FALSE FALSE
36 company29 sun 9:00 am 1:00 pm 4.0 hours FALSE TRUE
37 company30 sun 10:00 am 2:00 pm 4.0 hours FALSE FALSE
38 company31 sat 7:00 am 12:00 pm 5.0 hours FALSE TRUE
39 company32 thu 2:00 pm 6:00 pm 4.0 hours FALSE FALSE
40 company33 thu 4:00 pm 6:30 pm 2.5 hours TRUE FALSE
41 company34 thu 4:00 pm 9:00 pm 5.0 hours FALSE FALSE
42 company35 sat 8:30 am 1:30 pm 5.0 hours FALSE TRUE
43 company36 tue 3:00 pm 7:00 pm 4.0 hours FALSE FALSE
44 company37 tue 1:00 pm 5:00 pm 4.0 hours FALSE FALSE
45 company38 <NA> <NA> <NA> NA hours NA NA
46 company39 <NA> <NA> <NA> NA hours NA NA
47 company40 wed 1:00 pm 5:00 pm 4.0 hours FALSE FALSE
48 company41 wed 8:00 am 5:00 pm 9.0 hours FALSE FALSE
49 company41 sat 8:00 am 5:00 pm 9.0 hours FALSE FALSE
Upvotes: 1
Related Questions
- How to parse time-date (not date-time)
- R - Use Lubridate to create 1 second intervals in datetime column where only minutes are specified
- Extract time (HMS) from lubridate date time object?
- How can I separate this date time column into different columns?
- How to convert SQL interval as a string to a lubridate duration?
- How to convert a string into a date-time object
- How do I split my time data into intervals in R?
- How to extract time from lubridate?
- How to convert a string to a specific time format in R?
- Extract time stamps from string and convert to R POSIXct object