Aggregating 15 minute Interval Data into Hourly Intervals

This data is in 15 minute intervals of traffic flows. I want to be able to get it into hourly intervals. I have the data in 24 hour format but there are millions of records as the study covers 4 years so the only time shown is 0:00 or 12:00 am. Appreciate all the help.

q
#> function (save = "default", status = 0, runLast = TRUE) 
#> .Internal(quit(save, status, runLast))
#> <bytecode: 0x000000001554c7d8>
#> <environment: namespace:base>

 Detector_id       Detector_name Direction     Date Time Volume Speed Long_volume Device_id iti
1:    54211231 US54 at Dugan-EB-L1        EB 1/1/2016 0:00     20    68           0 505421123  NA
2:    54211232 US54 at Dugan-EB-L2        EB 1/1/2016 0:00    165    61           4 505421123  NA
3:    54211233 US54 at Dugan-EB-L3        EB 1/1/2016 0:00    260    59           0 505421123  NA
4:    54211241 US54 at Dugan-WB-L1        WB 1/1/2016 0:00     28    65           4 505421123  NA
5:    54211242 US54 at Dugan-WB-L2        WB 1/1/2016 0:00    190    59           4 505421123  NA
6:    54211243 US54 at Dugan-WB-L3        WB 1/1/2016 0:00    161    57           4 505421123  NA

x<-c(q$Time)
#> Error in q$Time: object of type 'closure' is not subsettable

x <- chron(Time=time)
#> Error in chron(Time = time): could not find function "chron"

x
#> Error in eval(expr, envir, enclos): object 'x' not found

q1<-cbind(q,x)
#> Error in cbind(q, x): object 'x' not found

names(q1)[13]<-"TimeNum"
#> Error in names(q1)[13] <- "TimeNum": object 'q1' not found

q$Hour = cut("TimeNum", breaks="hour")
#> Error in cut.default("TimeNum", breaks = "hour"): 'x' must be numeric

Created on 2019-10-17 by the reprex package (v0.3.0)

I continue to get that x must be numeric even through when I check the column type it returns numeric. I am new to the RStudio Community and I hope that I did the reprex correctly this time, any advice on that subject is greatly appreciated.

Could you provide sample data that we could use to actually reproduce your issue? There is not much aggregation we could do with all values being 0:00

1 Like

128 54219643 US54 at Woodlawn-WB-L3 0:00 184 60 8 505421963 NA 46 2
506 54219643 US54 at Woodlawn-WB-L3 0:15 152 59 4 505421963 NA 38 1
884 54219643 US54 at Woodlawn-WB-L3 0:30 132 61 4 505421963 NA 33 1
1268 54219643 US54 at Woodlawn-WB-L3 0:45 164 60 12 505421963 NA 41 3
1732 54219643 US54 at Woodlawn-WB-L3 1:00 92 59 4 505421963 NA 23 1
2036 54219643 US54 at Woodlawn-WB-L3 1:15 100 59 0 505421963 NA 25 0
2420 54219643 US54 at Woodlawn-WB-L3 1:30 88 59 0 505421963 NA 22 0
2798 54219643 US54 at Woodlawn-WB-L3 1:45 116 58 4 505421963 NA 29 1
3182 54219643 US54 at Woodlawn-WB-L3 2:00 80 62 8 505421963 NA 20 2
3566 54219643 US54 at Woodlawn-WB-L3 2:15 96 60 0 505421963 NA 24 0
3950 54219643 US54 at Woodlawn-WB-L3 2:30 48 58 0 505421963 NA 12 0
4469 54219643 US54 at Woodlawn-WB-L3 2:45 56 62 8 505421963 NA 14 2
4718 54219643 US54 at Woodlawn-WB-L3 3:00 64 61 4 505421963 NA 16 1
5102 54219643 US54 at Woodlawn-WB-L3 3:15 44 63 0 505421963 NA 11 0
5480 54219643 US54 at Woodlawn-WB-L3 3:30 68 61 12 505421963 NA 17 3
5858 54219643 US54 at Woodlawn-WB-L3 3:45 96 59 4 505421963 NA 24 1
6233 54219643 US54 at Woodlawn-WB-L3 4:00 96 62 0 505421963 NA 24 0
6611 54219643 US54 at Woodlawn-WB-L3 4:15 120 60 4 505421963 NA 30 1
7149 54219643 US54 at Woodlawn-WB-L3 4:30 248 61 8 505421963 NA 62 2
7364 54219643 US54 at Woodlawn-WB-L3 4:45 223 62 0 505421963 NA 55.75
7739 54219643 US54 at Woodlawn-WB-L3 5:00 260 60 0 505421963 NA 65 0
8114 54219643 US54 at Woodlawn-WB-L3 5:15 292 62 0 505421963 NA 73 0
8489 54219643 US54 at Woodlawn-WB-L3 5:30 372 61 4 505421963 NA 93 1
8864 54219643 US54 at Woodlawn-WB-L3 5:45 364 64 20 505421963 NA 91 5

Please use a copy/paste friendly format like explained here

d<-data.table(d)
#> Error in data.table(d): could not find function "data.table"

d$Vol4=d$Volume/4
#> Error in eval(expr, envir, enclos): object 'd' not found
d$LV4=d$Long_volume/4
#> Error in eval(expr, envir, enclos): object 'd' not found

d<-d[,-c("Time1","iti","x")]
#> Error in eval(expr, envir, enclos): object 'd' not found
d
#> Error in eval(expr, envir, enclos): object 'd' not found
d$Hour = cut(d$DateTime, breaks="hour")
#> Error in cut(d$DateTime, breaks = "hour"): object 'd' not found
q<-d%>%arrange(desc(Speed))
#> Error in d %>% arrange(desc(Speed)): could not find function "%>%"
q1<-datapasta::df_paste(head(q,20))
#> Could not format input_table as table. Unexpected class.
   data.frame(stringsAsFactors=FALSE,
                                                                                 Detector_id = c(54216431L, 54216431L, 54216431L, 54216431L, 54216431L,
                                                                                                 54216431L,
                                                                                                 54211832L,
                                                                                                 135209731L, 54212332L,
                                                                                                 135009621L,
                                                                                                 54211832L, 54215231L,
                                                                                                 54213231L,
                                                                                                 54216431L, 54216431L,
                                                                                                 54216431L,
                                                                                                 135008011L,
                                                                                                 135003211L, 54216431L,
                                                                                                 3321L),
                                                                               Detector_name = c("US54 at Washington-EB-L1", "US54 at Washington-EB-L1",
                                                                                                 "US54 at Washington-EB-L1",
                                                                                                 "US54 at Washington-EB-L1",
                                                                                                 "US54 at Washington-EB-L1",
                                                                                                 "US54 at Washington-EB-L1",
                                                                                                 "US54 at Hoover-EB-L2",
                                                                                                 "US-54 at Tyler - EB - L1",
                                                                                                 "US54 at I-235-EB-L2",
                                                                                                 "I-135 at Hydraulic-South-SB-L1",
                                                                                                 "US54 at Hoover-EB-L2",
                                                                                                 "US54 at Sycamore-EB-L1",
                                                                                                 "US54 at SW Blvd - East-EB-L1",
                                                                                                 "US54 at Washington-EB-L1",
                                                                                                 "US54 at Washington-EB-L1",
                                                                                                 "US54 at Washington-EB-L1",
                                                                                                 "I-135 at 13th-NB-L1",
                                                                                                 "I-135 at K-15 - NB - L1",
                                                                                                 "US54 at Washington-EB-L1",
                                                                                                 "Airport North - SB - L1"),
                                                                                   Direction = c("EB", "EB", "EB", "EB", "EB", "EB", "EB", "EB", "EB",
                                                                                                 "SB", "EB",
                                                                                                 "EB", "EB", "EB",
                                                                                                 "EB", "EB", "NB",
                                                                                                 "NB", "EB",
                                                                                                 "SB"),
                                                                                        Date = c("1/2/2016", "1/2/2016", "1/2/2016", "1/2/2016",
                                                                                                 "1/2/2016",
                                                                                                 "1/2/2016",
                                                                                                 "1/3/2016", "1/3/2016",
                                                                                                 "1/3/2016",
                                                                                                 "1/3/2016",
                                                                                                 "1/3/2016", "1/3/2016",
                                                                                                 "1/3/2016",
                                                                                                 "1/3/2016",
                                                                                                 "1/3/2016", "1/3/2016",
                                                                                                 "1/4/2016",
                                                                                                 "1/4/2016",
                                                                                                 "1/5/2016",
                                                                                                 "1/6/2016"),
                                                                                        Time = c("3:45", "4:15", "4:30", "4:45", "6:45", "7:00", "2:30",
                                                                                                 "2:30", "2:45",
                                                                                                 "3:00", "3:00",
                                                                                                 "3:00", "3:15",
                                                                                                 "10:45",
                                                                                                 "11:00", "16:45",
                                                                                                 "2:45", "3:15",
                                                                                                 "10:15", "1:30"),
                                                                                      Volume = c(4L, 4L, 4L, 4L, 12L, 12L, 16L, 4L, 4L, 4L, 4L, 4L, 4L,
                                                                                                 12L, 12L, 16L,
                                                                                                 4L, 4L, 20L,
                                                                                                 8L),
                                                                                       Speed = c(95L, 95L, 95L, 95L, 95L, 95L, 95L, 95L, 95L, 95L, 95L,
                                                                                                 95L, 95L, 95L,
                                                                                                 95L, 95L, 95L,
                                                                                                 95L, 95L, 95L),
                                                                                 Long_volume = c(4L, 4L, 4L, 4L, 4L, 12L, 4L, 0L, 0L, 4L, 4L, 0L, 0L, 8L,
                                                                                                 8L, 8L, 0L, 0L,
                                                                                                 4L, 8L),
                                                                                   Device_id = c(505421643L, 505421643L, 505421643L, 505421643L,
                                                                                                 505421643L,
                                                                                                 505421643L,
                                                                                                 505421183L, 505420973L,
                                                                                                 505421233L,
                                                                                                 513500963L,
                                                                                                 505421183L, 505421523L,
                                                                                                 505421323L,
                                                                                                 505421643L,
                                                                                                 505421643L, 505421643L,
                                                                                                 513500803L,
                                                                                                 513500323L,
                                                                                                 505421643L,
                                                                                                 545300033L),
                                                                                        Vol4 = c(1, 1, 1, 1, 3, 3, 4, 1, 1, 1, 1, 1, 1, 3, 3, 4, 1, 1, 5, 2),
                                                                                         LV4 = c(1, 1, 1, 1, 1, 3, 1, 0, 0, 1, 1, 0, 0, 2, 2, 2, 0, 0, 1, 2)
                                                                            )
#>    Detector_id                  Detector_name Direction     Date  Time
#> 1     54216431       US54 at Washington-EB-L1        EB 1/2/2016  3:45
#> 2     54216431       US54 at Washington-EB-L1        EB 1/2/2016  4:15
#> 3     54216431       US54 at Washington-EB-L1        EB 1/2/2016  4:30
#> 4     54216431       US54 at Washington-EB-L1        EB 1/2/2016  4:45
#> 5     54216431       US54 at Washington-EB-L1        EB 1/2/2016  6:45
#> 6     54216431       US54 at Washington-EB-L1        EB 1/2/2016  7:00
#> 7     54211832           US54 at Hoover-EB-L2        EB 1/3/2016  2:30
#> 8    135209731       US-54 at Tyler - EB - L1        EB 1/3/2016  2:30
#> 9     54212332            US54 at I-235-EB-L2        EB 1/3/2016  2:45
#> 10   135009621 I-135 at Hydraulic-South-SB-L1        SB 1/3/2016  3:00
#> 11    54211832           US54 at Hoover-EB-L2        EB 1/3/2016  3:00
#> 12    54215231         US54 at Sycamore-EB-L1        EB 1/3/2016  3:00
#> 13    54213231   US54 at SW Blvd - East-EB-L1        EB 1/3/2016  3:15
#> 14    54216431       US54 at Washington-EB-L1        EB 1/3/2016 10:45
#> 15    54216431       US54 at Washington-EB-L1        EB 1/3/2016 11:00
#> 16    54216431       US54 at Washington-EB-L1        EB 1/3/2016 16:45
#> 17   135008011            I-135 at 13th-NB-L1        NB 1/4/2016  2:45
#> 18   135003211        I-135 at K-15 - NB - L1        NB 1/4/2016  3:15
#> 19    54216431       US54 at Washington-EB-L1        EB 1/5/2016 10:15
#> 20        3321        Airport North - SB - L1        SB 1/6/2016  1:30
#>    Volume Speed Long_volume Device_id Vol4 LV4
#> 1       4    95           4 505421643    1   1
#> 2       4    95           4 505421643    1   1
#> 3       4    95           4 505421643    1   1
#> 4       4    95           4 505421643    1   1
#> 5      12    95           4 505421643    3   1
#> 6      12    95          12 505421643    3   3
#> 7      16    95           4 505421183    4   1
#> 8       4    95           0 505420973    1   0
#> 9       4    95           0 505421233    1   0
#> 10      4    95           4 513500963    1   1
#> 11      4    95           4 505421183    1   1
#> 12      4    95           0 505421523    1   0
#> 13      4    95           0 505421323    1   0
#> 14     12    95           8 505421643    3   2
#> 15     12    95           8 505421643    3   2
#> 16     16    95           8 505421643    4   2
#> 17      4    95           0 513500803    1   0
#> 18      4    95           0 513500323    1   0
#> 19     20    95           4 505421643    5   1
#> 20      8    95           8 545300033    2   2

Created on 2019-10-22 by the reprex package (v0.3.0)

Use tibbletime package. First coerce your dataframe into time-series tibble with as_tbl_time function (index is your time variable). Next use function collapse_by("hourly). Group by time variable and summarize all desired variables.

When I try this I recieve the error:

tibbletime::as_tbl_time(d, index = Time)
Error: Specified index is not time based

This is an example of how you can perform aggregation and summaries.

df <- data.frame(stringsAsFactors=FALSE,
                 Detector_id = c(54216431L, 54216431L, 54216431L, 54216431L, 54216431L,
                                 54216431L,
                                 54211832L,
                                 135209731L, 54212332L,
                                 135009621L,
                                 54211832L, 54215231L,
                                 54213231L,
                                 54216431L, 54216431L,
                                 54216431L,
                                 135008011L,
                                 135003211L, 54216431L,
                                 3321L),
                 Detector_name = c("US54 at Washington-EB-L1", "US54 at Washington-EB-L1",
                                   "US54 at Washington-EB-L1",
                                   "US54 at Washington-EB-L1",
                                   "US54 at Washington-EB-L1",
                                   "US54 at Washington-EB-L1",
                                   "US54 at Hoover-EB-L2",
                                   "US-54 at Tyler - EB - L1",
                                   "US54 at I-235-EB-L2",
                                   "I-135 at Hydraulic-South-SB-L1",
                                   "US54 at Hoover-EB-L2",
                                   "US54 at Sycamore-EB-L1",
                                   "US54 at SW Blvd - East-EB-L1",
                                   "US54 at Washington-EB-L1",
                                   "US54 at Washington-EB-L1",
                                   "US54 at Washington-EB-L1",
                                   "I-135 at 13th-NB-L1",
                                   "I-135 at K-15 - NB - L1",
                                   "US54 at Washington-EB-L1",
                                   "Airport North - SB - L1"),
                 Direction = c("EB", "EB", "EB", "EB", "EB", "EB", "EB", "EB", "EB",
                               "SB", "EB",
                               "EB", "EB", "EB",
                               "EB", "EB", "NB",
                               "NB", "EB",
                               "SB"),
                 Date = c("1/2/2016", "1/2/2016", "1/2/2016", "1/2/2016",
                          "1/2/2016",
                          "1/2/2016",
                          "1/3/2016", "1/3/2016",
                          "1/3/2016",
                          "1/3/2016",
                          "1/3/2016", "1/3/2016",
                          "1/3/2016",
                          "1/3/2016",
                          "1/3/2016", "1/3/2016",
                          "1/4/2016",
                          "1/4/2016",
                          "1/5/2016",
                          "1/6/2016"),
                 Time = c("3:45", "4:15", "4:30", "4:45", "6:45", "7:00", "2:30",
                          "2:30", "2:45",
                          "3:00", "3:00",
                          "3:00", "3:15",
                          "10:45",
                          "11:00", "16:45",
                          "2:45", "3:15",
                          "10:15", "1:30"),
                 Volume = c(4L, 4L, 4L, 4L, 12L, 12L, 16L, 4L, 4L, 4L, 4L, 4L, 4L,
                            12L, 12L, 16L,
                            4L, 4L, 20L,
                            8L),
                 Speed = c(95L, 95L, 95L, 95L, 95L, 95L, 95L, 95L, 95L, 95L, 95L,
                           95L, 95L, 95L,
                           95L, 95L, 95L,
                           95L, 95L, 95L),
                 Long_volume = c(4L, 4L, 4L, 4L, 4L, 12L, 4L, 0L, 0L, 4L, 4L, 0L, 0L, 8L,
                                 8L, 8L, 0L, 0L,
                                 4L, 8L),
                 Device_id = c(505421643L, 505421643L, 505421643L, 505421643L,
                               505421643L,
                               505421643L,
                               505421183L, 505420973L,
                               505421233L,
                               513500963L,
                               505421183L, 505421523L,
                               505421323L,
                               505421643L,
                               505421643L, 505421643L,
                               513500803L,
                               513500323L,
                               505421643L,
                               545300033L),
                 Vol4 = c(1, 1, 1, 1, 3, 3, 4, 1, 1, 1, 1, 1, 1, 3, 3, 4, 1, 1, 5, 2),
                 LV4 = c(1, 1, 1, 1, 1, 3, 1, 0, 0, 1, 1, 0, 0, 2, 2, 2, 0, 0, 1, 2)
)

library(dplyr)
library(tsibble)
library(lubridate)

df %>%
    mutate(time_stamp = mdy_hm(paste(Date, Time))) %>% 
    as_tsibble(key = Detector_name, index = time_stamp) %>% 
    group_by_key() %>%
    index_by(day = ~ as.Date(.)) %>%
    summarise(
        avg_volume = mean(Volume, na.rm = TRUE),
        avg_speed = mean(Speed, na.rm = TRUE),
        avg_long_volume = mean(Long_volume, na.rm = TRUE),
    )
#> # A tsibble: 12 x 5 [1D]
#> # Key:       Detector_name [10]
#>    Detector_name            day        avg_volume avg_speed avg_long_volume
#>    <chr>                    <date>          <dbl>     <dbl>           <dbl>
#>  1 Airport North - SB - L1  2016-01-06       8           95            8   
#>  2 I-135 at 13th-NB-L1      2016-01-04       4           95            0   
#>  3 I-135 at Hydraulic-Sout… 2016-01-03       4           95            4   
#>  4 I-135 at K-15 - NB - L1  2016-01-04       4           95            0   
#>  5 US-54 at Tyler - EB - L1 2016-01-03       4           95            0   
#>  6 US54 at Hoover-EB-L2     2016-01-03      10           95            4   
#>  7 US54 at I-235-EB-L2      2016-01-03       4           95            0   
#>  8 US54 at SW Blvd - East-… 2016-01-03       4           95            0   
#>  9 US54 at Sycamore-EB-L1   2016-01-03       4           95            0   
#> 10 US54 at Washington-EB-L1 2016-01-02       6.67        95            5.33
#> 11 US54 at Washington-EB-L1 2016-01-03      13.3         95            8   
#> 12 US54 at Washington-EB-L1 2016-01-05      20           95            4

Created on 2019-10-22 by the reprex package (v0.3.0.9000)

1 Like