Subset rows according to a range of time

Question

I have a data frame that looks like that

            date_time loc_id node  energy   kgco2 
1 2009-02-27 00:11:08     87  103 0.00000 0.00000 
2 2009-02-27 01:05:05     87  103 7.00000 3.75900 
3 2009-02-27 02:05:05     87  103 6.40039 3.43701 
4 2009-02-27 03:05:05     87  103 4.79883 2.57697 
5 2009-02-27 04:05:05     87  103 4.10156 2.20254 
6 2009-02-27 05:05:05     87  103 2.59961 1.39599

Is there anyway I can subset it according to range of time, for example, 2am to 5am. I should then get a result that looks like this:

            date_time loc_id node  energy   kgco2  
3 2009-02-27 02:05:05     87  103 6.40039 3.43701 
4 2009-02-27 03:05:05     87  103 4.79883 2.57697 
5 2009-02-27 04:05:05     87  103 4.10156 2.20254

`str( df )`, i.e. is `date_time` actually a `date-time` class or a character vector? — Simon O'Hanlon, Oct 17 '13 at 07:23

score 17 · Answer 1 · answered Oct 17 '13 at 07:32

17

One way to do it is to use lubridate and define an interval :

library(lubridate)

date1 <- as.POSIXct("2009-02-27 02:00:00")
date2 <- as.POSIXct("2009-02-27 05:00:00")
int <- new_interval(date1, date2)

df[df$datetime %within% int,]

answered Oct 17 '13 at 07:32

juba

47,631
14
113
118

3

just to update this great answer, now `new_interval()` is deprecated. Use `interval()` instead. – BSP Apr 06 '17 at 13:19

score 16 · Accepted Answer · answered Oct 17 '13 at 07:31

16

I'd use the lubridate package and the hour() function to make your life easier...

require( lubridate )

with( df , df[ hour( date_time ) >= 2 & hour( date_time ) < 5 , ] )

#            date_time loc_id node  energy   kgco2
#3 2009-02-27 02:05:05     87  103 6.40039 3.43701
#4 2009-02-27 03:05:05     87  103 4.79883 2.57697
#5 2009-02-27 04:05:05     87  103 4.10156 2.20254

answered Oct 17 '13 at 07:31

Simon O'Hanlon

58,647
14
142
184

Thank you, this is the most simple method so far! The lubridate package seems very simple and easier than xts, I am going to read up on it. – Wet Feet Oct 17 '13 at 09:06
@user2809288 glad to be of service. I also quite like juba's suggestion below (also from `lubridate`). Cheers! – Simon O'Hanlon Oct 17 '13 at 09:08
1

@user2809288 while I have nothing against `lubridate` package, I think it's not apples to apples comparison when you compare `lubridate` with `xts`. `lubridate` is utility package while `xts` is timeseries class – CHP Oct 17 '13 at 10:05
What if I wanted to select every 3rd element from that subset? How will the syntax change? – Apurv Jun 28 '17 at 06:38
Great example! I am a huge fan of lubridate now. – philiporlando Aug 15 '17 at 23:43
How do you treat midnight? Both 24 and 00 seem to fail. – InterruptedException Mar 25 '18 at 14:44

score 6 · Answer 3 · answered Oct 17 '13 at 07:42

I would suggest using xts package for time series analysis. It has very convenient subsetting functions.

DF
##             date_time loc_id node  energy   kgco2
## 1 2009-02-27 00:11:08     87  103 0.00000 0.00000
## 2 2009-02-27 01:05:05     87  103 7.00000 3.75900
## 3 2009-02-27 02:05:05     87  103 6.40039 3.43701
## 4 2009-02-27 03:05:05     87  103 4.79883 2.57697
## 5 2009-02-27 04:05:05     87  103 4.10156 2.20254
## 6 2009-02-27 05:05:05     87  103 2.59961 1.39599

require(xts)
XTSDATA <- xts(DF[, -1], DF[, 1])
XTSDATA["T02:00:00/T05:00:00"]
##                     loc_id node  energy   kgco2
## 2009-02-27 02:05:05     87  103 6.40039 3.43701
## 2009-02-27 03:05:05     87  103 4.79883 2.57697
## 2009-02-27 04:05:05     87  103 4.10156 2.20254

score 1 · Answer 4 · answered Jun 08 '18 at 02:30

1

Use the lubridate::hours function to extract the hour number.
Then use dplyr::filter function to get the result.

answered Jun 08 '18 at 02:30

Jiaxiang

865
12
23

Subset rows according to a range of time

4 Answers4

Linked