jjdblast jjdblast - 2 months ago 18
R Question

R aggregate data in round 10 minutes

I have a R dataframe,

df
, like this:

WIFIAPTag passengerCount timeStamp MAC
1 E1-1A-1<E1-1-01> 15 2016-09-10 18:55:04 5869.6c54.d040
750 E1-1A-1<E1-1-01> 14 2016-09-10 18:56:01 5869.6c54.d040
1499 E1-1A-1<E1-1-01> 18 2016-09-10 18:57:01 5869.6c54.d040
2248 E1-1A-1<E1-1-01> 17 2016-09-10 18:58:02 5869.6c54.d040
2997 E1-1A-1<E1-1-01> 17 2016-09-10 18:59:01 5869.6c54.d040
3746 E1-1A-1<E1-1-01> 14 2016-09-10 19:00:01 5869.6c54.d040
3746 E1-1A-1<E1-1-01> 1 2016-09-10 19:05:01 5869.6c54.d040


Now I want to aggregate this dataframe every 10 minutes, like this:

WIFIAPTag passengerCount timeStamp MAC
1 E1-1A-1<E1-1-01> 81 2016-09-10 18:50:00 5869.6c54.d040
2 E1-1A-1<E1-1-01> 15 2016-09-10 19:00:00 5869.6c54.d040


I using
aggregate
and
cut
in R like this:

output <- aggregate(passengerCount ~ cut(timeStamp, breaks = "10 mins"), df, sum)


But I can only get the data start from
2016-09-10 18:55:00
:

output
WIFIAPTag timeStamp passengerCount
1 E1-1A-1<E1-1-01> 2016-09-10 18:55:00 95
2 E1-1A-1<E1-1-01> 2016-09-10 19:05:00 1


How can I make the output start from
2016-09-10 18:50:00
?

Answer

Giving a value for breaks like "10 mins" will partition the interval using the first and the last date with 10 min segments.

Instead, choose your breaks explicitly:

(Using lubridate, since I prefer not to hardcode the lowest and highest values)

library(lubridate)

lowtime <- min(df$timeStamp)
hightime <- max(df$timeStamp)

# Set the minute and second to the nearest 10 minute value
minute(lowtime) <- floor(minute(lowtime)/10) * 10
minute(hightime) <- ceiling(minute(hightime)/10) * 10
second(lowtime) <- 0
second(hightime) <- 0

# Set the breakpoints at 10 minute intervals
breakpoints <- seq.POSIXt(lowtime, hightime, by = 600)
output <- aggregate(passengerCount ~ cut(timeStamp, breaks = breakpoints), df, sum)
Comments