CODEWITHSUNDEEP
rselectfilterdplyr
iHermes
iHermes

Reputation: 339

subseting dates and times in dplyr

I have a dataset, I want to subset weekday and weekends crashes on rush hours. (it is simulated data) 

data11 <-  structure(list(dow = structure(c(6L, 4L, 2L, 7L, 5L, 6L), .Label = c("1", "2", "3", "4", "5", "6", "1"), class = "factor"), 
           time = c(0L, 1655L, 53L, 447L, 2000L, 1224L), sun1 = c(0, 0, 0, 0, 0, 0), sun2 = c(0, 0, 0, 0, 0, 0), sun = c(0, 0, 0, 0, 0, 0)), row.names = c(NA, 6L), class = "data.frame")

I used this to make morning (1) and night (2) rush hour and others (0). But these also includes weekends which make no sense. 

data12=data11%>%mutate(crstime1=ifelse(630<time & time<830,1,0))%>%
          mutate(crstime2=ifelse(1650<time & time<1830,2,0))
data12$crstime3=(data12$crstime1+data12$crstime2)
table(data12$crstime3)
 0  1  2 
86  7  7

I want to use different times for weekend ( day of week (dow) = 6 and 7 ). I can do that with a code something like this but it will be torture. 

> data11=data11%>%mutate(sun1=ifelse(dow=="7" & 1100<time & time<1259,1,0))%>%
+   mutate(sun2=ifelse(dow=="7"& 1800<time & time<2059,2,0))
> data11$sun=(data11$sun1+data11$sun2)
> table(data11$sun)

 0  1  2 
96  2  2 
> dplyr::filter(data11,dow=="7")
   dow time sun1 sun2 sun
1    7  447    0    0   0
2    7 1751    0    0   0
3    7  330    0    0   0
4    7    0    0    0   0
5    7  216    0    0   0
6    7 1256    1    0   1
7    7    0    0    0   0
8    7 2032    0    2   2
9    7 1213    1    0   1
10   7 2030    0    2   2
11   7  323    0    0   0
12   7  300    0    0   0
13   7  843    0    0   0
14   7   53    0    0   0

My final question is that i cannot convert time to a time object. it is integer, works perfect but I like to learn how. I tried lubridate but failed.

> data11$dow
  [1] 6 4 2 7 5 3 1 5 1 3 7 4 1 6 3 3 5 3 7 3 3 6 2 1 2 3 6 2 6 7 6 7 6 7 6 7 4 2 1 7 6 1 2 5 1 1 5 7 6 3 4 1 6 7 5 6 6 6 6 4 6 3 6 3
 [65] 3 5 2 1 2 3 5 4 1 4 1 7 5 7 1 2 6 2 4 5 1 5 2 2 2 1 4 6 7 4 5 1 4 5 6 7
Levels: 1 2 3 4 5 6 7
> data11$time
  [1] 2203 1632 2115  447 2000 1424   30 1324  723 2105 1751  743 1854 1000 1327 1105  135 1815  330 1845 1546 1752  936 1643 1410
 [26] 1448 1630  942 2145    0 1500  216 1456 1256 1236    0 2037 1640    0 2032 1715  801 1219 2140   25  318 2234 1213 2230 2024
 [51] 2327 2048  315 2030 1830 2148  317 1338 2233 2348  220 1746 1334 2006  829 1345 2004 1907 2004 1200 1010 2030  954  809 1502
 [76]  323 1904  300 1310 1837  131 1520  820 1313 1529 1859 1600 1105 1821 1229 2052 1852  843  759 1609 1435 1722 1934 1225   53

Upvotes: 1

Views: 126

Answers (2)

A. Suliman
A. Suliman

Reputation: 13125

I'm not sure if I understand what you want correctly but here a try. Define time in the right format using stringr::str_pad and lubridate::hm then do a normal dplyr::case_when

library(dplyr)
library(lubridate)
data11 %>% mutate(time1 = stringr::str_pad(time, side="left", pad = "0", width = 4), 
                  time2 = hm(paste0(substr(time1,1,2),':',substr(time1,3,4))),
                  crstime = case_when(!dow %in% c(6,7) & time2 > hm("06:30") & time2 < hm("08:30") ~ 1,
                                      !dow %in% c(6,7) & time2 > hm("16:50") & time2 < hm("18:30") ~ 2,
                                      dow %in% c(6,7) & time2 > hm("11:00") & time2 < hm("12:59") ~ 3,
                                      dow %in% c(6,7) & time2 > hm("18:00") & time2 < hm("20:59") ~ 4,
                                      TRUE ~ 0))

  dow time sun1 sun2 sun time1      time2 crstime
1   6    0    0    0   0  0000         0S       0
2   4 1655    0    0   0  1655 16H 55M 0S       2
3   2   53    0    0   0  0053     53M 0S       0
4   1  447    0    0   0  0447  4H 47M 0S       0
5   5 2000    0    0   0  2000  20H 0M 0S       0
6   6 1224    0    0   0  1224 12H 24M 0S       3

PS: I updated data11 to include all posibilites/combinantions.

Upvotes: 0

dswdsyd
dswdsyd

Reputation: 616

R Doc: https://www.rdocumentation.org/packages/chron/versions/2.1-6/topics/chron

You could potentially looking at the library chron which I have used in the past.

chron has date and time classes in which you could perform operations on.

> library(chron)
> tms <- times(c("23:00:00", "22:29:00", "01:03:00","18:21:00", "16:56:00"))
> x <- chron(times = tms)
> x
[1] 23:00:00 22:29:00 01:03:00 18:21:00 16:56:00
> first_time_elem <- c(x[1], x[1] + "00:01:00")
> first_time_elem
[1] 23:00:00 23:01:00
> diff(first_time_elem)
[1] 00:01:00
> x[1]-x[2]
[1] 00:31:00

Hope this helps!

Upvotes: 1

Related Questions