How to convert datetime to POSIXct or numeric/ aggregate timeseries intervals to hourly intervals?

Question

Date Runoff

 01/01/1989 00:05:00    0
 01/01/1989 00:10:00    0
 01/01/1989 00:15:00    0
 01/01/1989 00:20:00    0
 01/01/1989 00:25:00    0
 01/01/1989 00:30:00    0
 01/01/1989 00:35:00    0
 01/01/1989 00:40:00    0
 01/01/1989 00:45:00    0
 01/01/1989 00:50:00    0
 01/01/1989 00:55:00    0
 01/01/1989 01:00:00    0
 01/01/1989 01:05:00    0
 01/01/1989 01:10:00    0
 01/01/1989 01:15:00    0
 01/01/1989 01:20:00    0
 01/01/1989 01:25:00    0
 01/01/1989 01:30:00    0
 01/01/1989 01:35:00    0
 01/01/1989 01:40:00    0
 01/01/1989 01:45:00    0
 01/01/1989 01:50:00    0
 01/01/1989 01:55:00    0
 01/01/1989 02:00:00    0
 01/01/1989 02:05:00    0
 01/01/1989 02:10:00    0
 01/01/1989 02:15:00    0
 01/01/1989 02:20:00    0
 01/01/1989 02:25:00    0
 01/01/1989 02:30:00    0
 01/01/1989 02:35:00    0
 01/01/1989 02:40:00    0
 01/01/1989 02:45:00    0
 01/01/1989 02:50:00    0
 01/11/1989 14:00:00    0
 01/11/1989 14:05:00    0
 01/11/1989 14:10:00    0
 01/11/1989 14:15:00    0
 01/11/1989 14:20:00    0
 01/11/1989 14:25:00    0
 01/11/1989 14:30:00    0

I have been trying to aggregate the timeseries data to 1 hour timeseries by adding the "Runoff" for that hour.

 ##aggregate timeseries by the hour and sum the runoff
 aggregate(LD1['Surface Runoff'], list(cut(LD1$Date, "1 hour")), sum)

I got the error: 'x' must be numeric in cut(LD1$Date, "1 hour")

I tried converting the date time to POSIXct but it keeps saying that my data is not in an unambiguous format which does make sense because the dates are not in a sequence.

My end result is very similar to Aggregate 5-Minute data to hourly sums with present NA's but I don't know how to proceed futher.

Any help is much appreciated.

Thank you.

Edit: This is a sample of the date/time using dput function.

structure(list(Date = structure(1:217, .Label = c(" 06/04/2001 00:00:00", " 06/04/2001 00:05:00", " 06/04/2001 00:10:00", " 06/04/2001 00:15:00", " 06/04/2001 00:20:00", " 06/04/2001 00:25:00", " 06/04/2001 00:30:00", " 06/04/2001 00:35:00", " 06/04/2001 00:40:00", " 06/04/2001 00:45:00", " 06/04/2001 00:50:00", " 06/04/2001 00:55:00", " 06/04/2001 01:00:00", " 06/04/2001 01:05:00", " 06/04/2001 01:10:00", " 06/04/2001 01:15:00", " 06/04/2001 01:20:00", " 06/04/2001 01:25:00", " 06/04/2001 01:30:00", " 06/04/2001 01:35:00", " 06/04/2001 01:40:00", " 06/04/2001 01:45:00", " 06/04/2001 01:50:00", " 06/04/2001 01:55:00", " 06/04/2001 02:00:00", " 06/04/2001 02:05:00", " 06/04/2001 02:10:00", " 06/04/2001 02:15:00", " 06/04/2001 02:20:00", " 06/04/2001 02:25:00", " 06/04/2001 02:30:00", " 06/04/2001 02:35:00", " 06/04/2001 02:40:00", " 06/04/2001 02:45:00", " 06/04/2001 02:50:00", " 06/04/2001 02:55:00", " 06/04/2001 03:00:00", " 06/04/2001 03:05:00", " 06/04/2001 03:10:00", " 06/04/2001 03:15:00", " 06/04/2001 03:20:00", " 06/04/2001 03:25:00", " 06/04/2001 03:30:00", " 06/04/2001 03:35:00", " 06/04/2001 03:40:00", " 06/04/2001 03:45:00", " 06/04/2001 03:50:00", " 06/04/2001 03:55:00", " 06/04/2001 04:00:00", " 06/04/2001 04:05:00", " 06/04/2001 04:10:00", " 06/04/2001 04:15:00", " 06/04/2001 04:20:00", " 06/04/2001 04:25:00", " 06/04/2001 04:30:00", " 06/04/2001 04:35:00", " 06/04/2001 04:40:00", " 06/04/2001 04:45:00", " 06/04/2001 04:50:00", " 06/04/2001 04:55:00", " 06/04/2001 05:00:00", " 06/04/2001 05:05:00", " 06/04/2001 05:10:00", " 06/04/2001 05:15:00", " 06/04/2001 05:20:00", " 06/04/2001 05:25:00", " 06/04/2001 05:30:00", " 06/04/2001 07:00:00", " 06/04/2001 07:05:00", " 06/04/2001 07:10:00", " 06/04/2001 07:15:00", " 06/04/2001 07:20:00", " 06/04/2001 07:25:00", " 06/04/2001 07:30:00", " 06/04/2001 07:35:00", " 06/04/2001 07:40:00", " 06/04/2001 07:45:00", " 06/04/2001 07:50:00", " 06/04/2001 07:55:00", " 06/04/2001 08:00:00", " 06/04/2001 08:05:00", " 06/04/2001 08:10:00", " 06/04/2001 08:15:00", " 06/04/2001 08:20:00", " 06/04/2001 08:25:00", " 06/04/2001 08:30:00", " 06/04/2001 08:35:00", " 06/04/2001 08:40:00", " 06/04/2001 08:45:00", " 06/04/2001 08:50:00", " 06/04/2001 08:55:00", " 06/04/2001 09:00:00", " 06/04/2001 09:05:00", " 06/04/2001 09:10:00", " 06/04/2001 09:15:00", " 06/04/2001 09:20:00", " 06/04/2001 09:25:00", " 06/04/2001 09:30:00", " 06/04/2001 09:35:00", " 06/04/2001 09:40:00", " 06/04/2001 09:45:00", " 06/04/2001 09:50:00", " 06/04/2001 09:55:00", " 06/04/2001 10:00:00", " 06/04/2001 10:05:00", " 06/04/2001 10:10:00", " 06/04/2001 10:15:00", " 06/04/2001 10:20:00", " 06/04/2001 10:25:00", " 06/04/2001 10:30:00", " 06/04/2001 10:35:00", " 06/04/2001 10:40:00", " 06/04/2001 10:45:00", " 06/04/2001 10:50:00", " 06/04/2001 10:55:00", " 06/04/2001 11:00:00", " 06/04/2001 11:05:00", " 06/04/2001 11:10:00", " 06/04/2001 11:15:00", " 06/04/2001 11:20:00", " 06/04/2001 11:25:00", " 06/04/2001 11:30:00", " 06/09/2001 16:00:00", " 06/09/2001 16:05:00", " 06/09/2001 16:10:00", " 06/09/2001 16:15:00", " 06/09/2001 16:20:00", " 06/09/2001 16:25:00", " 06/09/2001 16:30:00", " 06/09/2001 16:35:00", " 06/09/2001 16:40:00", " 06/09/2001 16:45:00", " 06/09/2001 16:50:00", " 06/09/2001 16:55:00", " 06/09/2001 17:00:00", " 06/09/2001 17:05:00", " 06/13/2001 11:00:00", " 06/13/2001 11:05:00", " 06/13/2001 11:10:00", " 06/13/2001 11:15:00", " 06/13/2001 11:20:00", " 06/13/2001 11:25:00", " 06/13/2001 11:30:00", " 06/13/2001 11:35:00", " 06/13/2001 11:40:00", " 06/13/2001 11:45:00", " 06/13/2001 11:50:00", " 06/13/2001 11:55:00", " 06/13/2001 12:00:00", " 06/13/2001 12:05:00", " 06/13/2001 12:10:00", " 06/13/2001 12:15:00", " 06/13/2001 12:20:00", " 06/13/2001 12:25:00", " 06/13/2001 12:30:00", " 06/13/2001 12:35:00", " 06/13/2001 12:40:00", " 06/13/2001 12:45:00", " 06/13/2001 12:50:00", " 06/13/2001 12:55:00", " 06/13/2001 13:00:00", " 06/13/2001 13:05:00", " 06/13/2001 13:10:00", " 06/13/2001 13:15:00", " 06/13/2001 13:20:00", " 06/13/2001 13:25:00", " 06/13/2001 13:30:00", " 06/13/2001 13:35:00", " 06/13/2001 13:40:00", " 06/13/2001 13:45:00", " 06/13/2001 13:50:00", " 06/13/2001 13:55:00", " 06/13/2001 14:00:00", " 06/13/2001 14:05:00", " 06/13/2001 14:10:00", " 06/13/2001 14:15:00", " 06/13/2001 14:20:00", " 06/13/2001 14:25:00", " 06/13/2001 14:30:00", " 06/13/2001 14:35:00", " 06/13/2001 14:40:00", " 06/13/2001 14:45:00", " 06/13/2001 14:50:00", " 06/13/2001 14:55:00", " 06/13/2001 15:00:00", " 06/13/2001 15:05:00", " 06/13/2001 15:10:00", " 06/13/2001 15:15:00", " 06/13/2001 15:20:00", " 06/13/2001 15:25:00", " 06/13/2001 15:30:00", " 06/13/2001 15:35:00", " 06/13/2001 15:40:00", " 06/13/2001 15:45:00", " 06/13/2001 15:50:00", " 06/13/2001 15:55:00", " 06/13/2001 16:00:00", " 06/13/2001 16:05:00", " 06/13/2001 16:10:00", " 06/13/2001 16:15:00", " 06/13/2001 16:20:00", " 06/13/2001 16:25:00", " 06/20/2001 17:00:00", " 06/20/2001 17:05:00", " 06/20/2001 17:10:00", " 06/20/2001 17:15:00", " 06/20/2001 17:20:00", " 06/20/2001 17:25:00", " 06/20/2001 17:30:00", " 06/20/2001 17:35:00", " 06/20/2001 17:40:00", " 06/20/2001 17:45:00", " 06/20/2001 17:50:00", " 06/20/2001 17:55:00", " 06/20/2001 18:00:00", " 06/20/2001 18:05:00", " 08/06/2001 17:00:00" ), class = "factor"), Runoff = c(0, 0, 0, 0.009, 0.032, 0.04, 0.043, 0.044, 0.044, 0.044, 0.044, 0.044, 0.044, 0.026, 0.024, 0.023, 0.022, 0.022, 0.022, 0.022, 0.022, 0.022, 0.022, 0.022, 0.022, 0.059, 0.065, 0.066, 0.067, 0.067, 0.067, 0.067, 0.067, 0.067, 0.067, 0.067, 0.067, 0.03, 0.025, 0.023, 0.022, 0.022, 0.022, 0.022, 0.022, 0.022, 0.022, 0.022, 0.022, 0.098, 0.109, 0.111, 0.112, 0.112, 0.112, 0.112, 0.112, 0.112, 0.112, 0.112, 0.112, 0.017, 0.007, 0.003, 0.002, 0.001, 0.001, 0, 0.009, 0.017, 0.019, 0.021, 0.021, 0.022, 0.022, 0.022, 0.022, 0.022, 0.022, 0.022, 0.022, 0.022, 0.022, 0.022, 0.022, 0.022, 0.022, 0.022, 0.022, 0.022, 0.022, 0.022, 0.059, 0.065, 0.066, 0.067, 0.067, 0.067, 0.067, 0.067, 0.067, 0.067, 0.067, 0.067, 0.048, 0.045, 0.045, 0.045, 0.044, 0.044, 0.044, 0.044, 0.044, 0.044, 0.044, 0.044, 0.009, 0.004, 0.002, 0.001, 0.001, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.014, 0.068, 0.084, 0.088, 0.089, 0.089, 0.089, 0.089, 0.089, 0.089, 0.089, 0.089, 0.089, 0.089, 0.089, 0.089, 0.089, 0.089, 0.089, 0.089, 0.089, 0.089, 0.089, 0.089, 0.089, 0.051, 0.046, 0.045, 0.044, 0.044, 0.044, 0.044, 0.044, 0.044, 0.044, 0.044, 0.044, 0.009, 0.004, 0.002, 0.001, 0.001, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)), class = "data.frame", row.names = c(NA, -217L))

I've added a sample of the dataframe using dput. Is that what you asked for? — Alfy Joseph, Oct 10 '19 at 20:50
Alfy, that is incomplete. `dput()` gives you something you can re-evaluate to get the same object. What you show above is incomple: beginning is missing, and it is only one of the two columns. It *does* however show your problem: dates are still `factor`, you want to `as.character()` that and feed it to one of the time parsers to get `POSIXct`. I'd use `anytime::anytime()` which even takes the factor variable. — Dirk Eddelbuettel, Oct 10 '19 at 20:57
I've added the sample dataframe with dput function Dirk. Initially because I had a very large dataset I could not copy the entire dataframe, so I took a section of my original dataframe and ran the function. I used anytime::anytime() but I still keep getting the same error. — Alfy Joseph, Oct 10 '19 at 21:16

score 0 · Answer 1 · answered Oct 10 '19 at 21:24

Here is an example with base functions:

library(data.table)
dt <- read.table(text = "datetime,value
 01/01/1989 00:05:00,0
 01/01/1989 00:10:00,0
 01/01/1989 00:15:00,0
 01/01/1989 00:20:00,0
 01/01/1989 00:25:00,0
 01/01/1989 00:30:00,0
 01/01/1989 00:35:00,0
 01/01/1989 00:40:00,0
 01/01/1989 00:45:00,0
 01/01/1989 00:50:00,0
 01/01/1989 00:55:00,0
 01/01/1989 01:00:00,0
 01/01/1989 01:05:00,0
 01/01/1989 01:10:00,0
 01/01/1989 01:15:00,0
 01/01/1989 01:20:00,0
 01/01/1989 01:25:00,0
 01/01/1989 01:30:00,0
 01/01/1989 01:35:00,0
 01/01/1989 01:40:00,0
 01/01/1989 01:45:00,0
 01/01/1989 01:50:00,0
 01/01/1989 01:55:00,0
 01/01/1989 02:00:00,0
 01/01/1989 02:05:00,0
 01/01/1989 02:10:00,0
 01/01/1989 02:15:00,0
 01/01/1989 02:20:00,0
 01/01/1989 02:25:00,0
 01/01/1989 02:30:00,0
 01/01/1989 02:35:00,0
 01/01/1989 02:40:00,0
 01/01/1989 02:45:00,0
 01/01/1989 02:50:00,0
 01/11/1989 14:00:00,0
 01/11/1989 14:05:00,0
 01/11/1989 14:10:00,0
 01/11/1989 14:15:00,0
 01/11/1989 14:20:00,0
 01/11/1989 14:25:00,0
 01/11/1989 14:30:00,0", header = TRUE, row.names = NULL, sep = ",")
dt <- setDT(dt)
dt[, datetime := as.POSIXct(datetime, format = "%d/%m/%Y %H:%M:%S")]
dt[, datehour := as.POSIXct(round(datetime, "hours"))]

Other rounding options can be found here:

Round a POSIX date (POSIXct) with base R functionality

Sting to date time conversion is here:

converting datetime string to POSIXct date/time format in R

How to convert datetime to POSIXct or numeric/ aggregate timeseries intervals to hourly intervals?

1 Answers1