What's the best way to aggregate date time by hourly interval?

user_18876308 0
Viewed 46

I'm having trouble parsing my date time column that are currently in 'chr' type. I want the date time to be grouped by hour interval and sum corresponding values, then merge two date frames.

a <- c("2016-04-12 12:00:00", "2016-04-12 12:01:00")
   b <- c(10, 20)
   df_1 <- data.frame(a,b)
   names(df_1) <- c('Date', 'Steps') 

c1 <- c("4/12/2016 12:00:00 AM", "4/12/2016 05:00:00 PM")
   d <- c(20,8)
   df_2 <- data.frame(c1,d) 
   names(df_2) <- c('Date', 'Intensity')

df_1 (with minutes interval) to join df_2 (with hourly interval but the whole day is separated by AM PM)

I have tried converting it using as.POSIXct and ymd to datetime type but it's returning NA values. I tried below code from a post I saw before, it worked but it didn't record the PM time of the day. code below

df_1 <- aggregate(df_1["Steps"], 
                 list(Date=cut(as.POSIXct(df_1$Date), "hour")),
                 sum)

Also, I wanna remove that AM PM on the second date frame.

r
1 Answers

While the aggregate for df_1 appears to work fine, for df_2 you need to define the time format, using strptime which converts character objects to "POSIX*t".

aggregate(df_2["Intensity"], 
          list(Date=cut(strptime(df_2$Date, '%m/%d/%Y %I:%M:%S %p'), "hour")),
          sum)
#                  Date Intensity
# 1 2016-04-12 05:00:00         8
# 2 2016-04-12 12:00:00        20

Explanation:

  • %m/%d/%Y month, day, year, separated by a slash
  • the space between date and time
  • %I:%M:%S hour (12h format), minute, second, separated by a colon
  • another space
  • %p the AM/PM indicator

Read ?strptime for different options, since this may also depend on your locale.

user_65740380
Related