CODEWITHSUNDEEP
rtime-seriesposixct
stokeinfo
stokeinfo

Reputation: 133

Subset time series data into defined intervals

I am trying to subset, or filter, data into a defined time interval. Can you help me subset the following data into 2-minute time intervals? I have looked at Lubridate, split(), and cut() but cannot figure out how to properly do this.

I've looked at this post on SO, however it doesn't seem to be what I need.

Note that columns 1 and 2 are character classes, column 3 is a POSIXct class. If possible I'd like to have the solution use the datetime column (POSIXct).

         date  time            datetime use..kW.     gen..kW. Grid..kW.
120 12/31/2013 21:59 2013-12-31 21:59:00 1.495833 -0.003083333  1.495833
121 12/31/2013 21:58 2013-12-31 21:58:00 1.829583 -0.003400000  1.829583
122 12/31/2013 21:57 2013-12-31 21:57:00 1.977283 -0.003450000  1.977283
123 12/31/2013 21:56 2013-12-31 21:56:00 2.494750 -0.003350000  2.494750
124 12/31/2013 21:55 2013-12-31 21:55:00 2.218283 -0.003500000  2.218283
125 12/31/2013 21:54 2013-12-31 21:54:00 2.008283 -0.003566667  2.008283
126 12/31/2013 21:53 2013-12-31 21:53:00 2.010917 -0.003600000  2.010917
127 12/31/2013 21:52 2013-12-31 21:52:00 2.011867 -0.003583333  2.011867
128 12/31/2013 21:51 2013-12-31 21:51:00 2.015033 -0.003600000  2.015033
129 12/31/2013 21:50 2013-12-31 21:50:00 2.096550 -0.003850000  2.096550

The new subset would just take the data from every two minute interval and look like:

      date  time            datetime use..kW.     gen..kW. Grid..kW.
121 12/31/2013 21:58 2013-12-31 21:58:00 1.829583 -0.003400000  1.829583
123 12/31/2013 21:56 2013-12-31 21:56:00 2.494750 -0.003350000  2.494750
125 12/31/2013 21:54 2013-12-31 21:54:00 2.008283 -0.003566667  2.008283
127 12/31/2013 21:52 2013-12-31 21:52:00 2.011867 -0.003583333  2.011867
129 12/31/2013 21:50 2013-12-31 21:50:00 2.096550 -0.003850000  2.096550

For my data, I am actually going to be doing 5 and 15 minute intervals. But if I get a good solution for the data above and a 2 minute interval, I should be able to appropriately adjust the code to fit my needs.

Upvotes: 0

Views: 2779

Answers (1)

lukeA
lukeA

Reputation: 54237

Using cut and plyr::ddply:

groups <- cut(as.POSIXct(df$datetime), breaks="2 min")
library(plyr)
ddply(df, "groups", tail, 1)[, -1]
#         date  time            datetime use..kW.     gen..kW. Grid..kW.
# 1 12/31/2013 21:50 2013-12-31 21:50:00 2.096550 -0.003850000  2.096550
# 2 12/31/2013 21:52 2013-12-31 21:52:00 2.011867 -0.003583333  2.011867
# 3 12/31/2013 21:54 2013-12-31 21:54:00 2.008283 -0.003566667  2.008283
# 4 12/31/2013 21:56 2013-12-31 21:56:00 2.494750 -0.003350000  2.494750
# 5 12/31/2013 21:58 2013-12-31 21:58:00 1.829583 -0.003400000  1.829583

Or 

arrange(ddply(df, "groups", tail, 1)[, -1], datetime, decreasing=TRUE)

if you want to sort it the other way round.

Upvotes: 2

Related Questions