Aggregating 15 minute Interval Data into Hourly Intervals

[CatsKSU]

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.

[andresrcs]

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

[CatsKSU]

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

[andresrcs]

Please use a copy/paste friendly format like explained here

FAQ: How to do a minimal reproducible example ( reprex ) for beginners

Please feel free to improve this FAQ, just keep in mind the general goal of making it friendly for r beginners (and if possible for non native English speakers as well) EDIT: Translations to other languages are also welcome (please try to keep translation as close as posible to the original English text)

[CatsKSU]

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)}

[Enuma]

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.

[CatsKSU]

When I try this I recieve the error:

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

[andresrcs]

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)}