CODEWITHSUNDEEP
rdplyrlubridate
longlivebrew
longlivebrew

Reputation: 301

dplyr & lubridate - filter by hour and minute (i.e. 10:15)

Using dplyr and lubridate:

I have seen many posts on how to filter for hours i.e. filter (hour(Timestamp)>7) but I'm looking to to filter daily between 9 am - 8:15 pm (regardless of the day, although here is just 1/1/2015).

Take this sample data (df), also: https://drive.google.com/file/d/10x_VY2stTRuVwz7QFWXMbaRqNqMRBeIk/view?usp=sharing): 

ID  timestamp   value
a   1/1/2015 8:45   1
a   1/1/2015 9:00   2
a   1/1/2015 9:15   3
a   1/1/2015 9:30   4
a   1/1/2015 9:45   5
a   1/1/2015 10:00  6
a   1/1/2015 10:15  7
a   1/1/2015 19:45  11
a   1/1/2015 20:00  12
a   1/1/2015 20:15  13
a   1/1/2015 20:30  14
a   1/1/2015 20:45  14
b   1/1/2015 8:45   1
b   1/1/2015 9:00   2
b   1/1/2015 9:15   4
b   1/1/2015 9:30   5
b   1/1/2015 9:45   5
b   1/1/2015 10:00  5
b   1/1/2015 10:15  5
b   1/1/2015 19:45  5
b   1/1/2015 20:00  5
b   1/1/2015 20:15  5
b   1/1/2015 20:30  2
b   1/1/2015 20:45  2

Using the following code: 

df %>% group_by(ID) %>% 
+     filter(hour(timestamp)>=9 & hour(timestamp)<21 & minute(timestamp)<16) %>% 
+     summarise(mean = mean(value)) %>% as.data.frame()

Yields: 

  ID     mean
1  a 7.166667
2  b 4.333333

Which doesn't include any timestamps with minute(Timestamp) greater than 0:15 (which mistakenly leaves out 9:45am)

The correct means should be: 

  ID     mean
1  a 7
2  b 4.555

Any thoughts? 

> head(dput(df))
structure(list(ID = c("a", "a", "a", "a", "a", "a", "a", "a", 
"a", "a", "a", "a", "b", "b", "b", "b", "b", "b", "b", "b", "b", 
"b", "b", "b"), timestamp = structure(c(1420119900, 1420120800, 
1420121700, 1420122600, 1420123500, 1420124400, 1420125300, 1420159500, 
1420160400, 1420161300, 1420162200, 1420163100, 1420119900, 1420120800, 
1420121700, 1420122600, 1420123500, 1420124400, 1420125300, 1420159500, 
1420160400, 1420161300, 1420162200, 1420163100), class = c("POSIXct", 
"POSIXt"), tzone = ""), value = c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 
11L, 12L, 13L, 14L, 14L, 1L, 2L, 4L, 5L, 5L, 5L, 5L, 5L, 5L, 
5L, 2L, 2L)), .Names = c("ID", "timestamp", "value"), class = "data.frame", row.names = c(NA, 
-24L))
  ID           timestamp value
1  a 2015-01-01 08:45:00     1
2  a 2015-01-01 09:00:00     2
3  a 2015-01-01 09:15:00     3
4  a 2015-01-01 09:30:00     4
5  a 2015-01-01 09:45:00     5
6  a 2015-01-01 10:00:00     6

Upvotes: 1

Views: 2604

Answers (2)

Benjamin
Benjamin

Reputation: 17359

Another option is to calculate the number of minutes since midnight. To get between 09:00 and 20:15, the number of minutes since midnight needs to be between 9*60 and 20*60 + 15

library(dplyr)
library(lubridate)

df %>% 
  mutate(since_midnight = hour(timestamp) * 60 + minute(timestamp)) %>% 
  filter(since_midnight >= 9*60 & since_midnight < (20 * 60 + 15)) %>% 
  summarise(mean = mean(value))

Upvotes: 3

YOLO
YOLO

Reputation: 21709

I think you don't need hour conversion. Here I'm calculating between 11am to 10pm:

df %>% 
    group_by(ID) %>% 
    filter(timestamp > '2015-01-01 11:00:00' & timestamp < '2015-01-01 22:00:00') %>% 
    summarise(mean = mean(value)) %>% as.data.frame()

Upvotes: 0

Related Questions