CODEWITHSUNDEEP
rdataframe
Neil
Neil

Reputation: 8247

Aggregating time on hourly basis and counting it

I have following dataframe in R.

 Date                    Car_NO
 2016-12-24 19:35:00       ABC
 2016-12-24 19:55:00       DEF
 2016-12-24 20:15:00       RTY
 2016-12-24 20:35:00       WER
 2016-12-24 21:34:00       DER
 2016-12-24 00:23:00       ABC
 2016-12-24 00:22:00       ERT
 2016-12-24 11:45:00       RTY
 2016-12-24 13:09:00       RTY

Date format is "POSIXct" "POSIXt"

I want to count hourly movement of car traffic. like 12-1,1-2,2-3,3-4 and so on

Currently my approach is following

df$time <- ymd_hms(df$Date)

df$hours <- hour(df$time) 
df$minutes <- minute(df$time)

df$time <- as.numeric(paste(df$hours,df$minutes,sep="."))

And after this I will apply ifelse loop to divide it in hourly time slots,but I think it will be long and tedious way to do it. Is there any easy approach in R.

My desired dataframe would be

 Time_Slots      Car_Traffic_count
   00-01               2
   01-02               0
   02-03               0
    .              
    .
    .
   19-20               2
   20-21               2
   21-22               1
    .
    .
    .

Upvotes: 1

Views: 97

Answers (1)

Florian
Florian

Reputation: 25425

Simplest would be to just use the starting hour to indicate a time interval:

# sample data
df = data.frame(time = Sys.time()+seq(1,10)*10000, runif(10) )

# summarize
library(dplyr)
df$hour = factor(as.numeric(format(df$time,"%H")), levels = seq(0,24))
df = df %>% 
      group_by(hour) %>% 
      summarize(count=n()) %>% 
      complete(hour, fill = list(count = 0))

Output:

# A tibble: 24 x 2
     hour count
   <fctr> <dbl>
 1      0     0
 2      1     1
 3      2     0
 4      3     0
 5      4     1
 6      5     0
 7      6     1
 8      7     0
 9      8     0
10      9     1
# ... with 14 more rows

You can optionally add:

df$formatted = paste0(as.character(df$hour),"-",as.numeric(as.character(df$hour))+1)

at then end to get your desired format. Hope this helps!

Upvotes: 2

Related Questions