0

I am trying to extract rows at 5 minute intervals from 1 minute data. My data looks like this:

structure(list(Date = structure(c(1509408000, 1509408000, 1509408000, 
1509408000, 1509408000, 1509408000), class = c("POSIXct", "POSIXt"
), tzone = "UTC"), Time = structure(c(-2209021500, -2209021560, 
-2209021620, -2209021680, -2209021740, -2209021800), class = c("POSIXct", 
"POSIXt"), tzone = "UTC"), O = c(3674, 3675, 3674, 3675, 3675, 
3675), H = c(3674, 3675, 3675, 3676, 3676, 3675), L = c(3673, 
3674, 3674, 3674, 3675, 3675), C = c(3673, 3674, 3674, 3675, 
3675, 3675)), row.names = c(NA, -6L), class = c("tbl_df", "tbl", 
"data.frame"))

structure(list(Date = structure(c(1506902400, 1506902400, 1506902400, 
1506902400, 1506902400, 1506902400), class = c("POSIXct", "POSIXt"
), tzone = "UTC"), Time = structure(c(-2209071300, -2209071360, 
-2209071420, -2209071480, -2209071540, -2209071600), class = c("POSIXct", 
"POSIXt"), tzone = "UTC"), O = c(3450, 3451, 3451, 3452, 3450, 
3449), H = c(3451, 3451, 3451, 3452, 3452, 3451), L = c(3448, 
3449, 3449, 3450, 3450, 3449), C = c(3448, 3451, 3450, 3451, 
3452, 3450)), row.names = c(NA, -6L), class = c("tbl_df", "tbl", 
"data.frame"))

I have looked at:

Create a time interval of 15 minutes from minutely data in R?

How to subset and extract time series by time interval in row

but none do exactly what I want. Maybe I could use this: substr(t,15,16)=="00".

but I'm not sure how to combine it with filter.

Desired Output: find rows at 30 minute intervals:

Desired Output: find rows at 30 minute intervals:

s__
  • 9,270
  • 3
  • 27
  • 45
EJG_27
  • 111
  • 10

1 Answers1

1

You can extract rows with a minute-mark ending in 0 or 5 with

df[substr(format(df$Time, '%M'), 2, 2) %in% c(0, 5),]
# or 
df[as.numeric(format(df$Time, '%M')) %% 5 == 0,]
# or 
df[grep('[0|5]$', format(df$Time, '%M')),]

With filter:

library(dplyr)
df %>% 
  filter(substr(format(df$Time, '%M'), 2, 2) %in% c(0, 5))

# or 

df %>% 
  filter(as.numeric(format(df$Time, '%M')) %% 5 == 0)
IceCreamToucan
  • 28,083
  • 2
  • 22
  • 38
  • will the filter example pick up minute-mark ending in 0 or 5? – EJG_27 Dec 18 '18 at 13:44
  • Yes, it just checks if the minute is an integer multiple of `5`, i.e. the minute-mark divided by 5 has a remainder of 0. I added the substr option to filter too, but they give the same result. – IceCreamToucan Dec 18 '18 at 13:51
  • Thanks. I have tried the code and it works. I was also looking for help to create the intervals if there are missing rows at the beginning i.e. if 14:30 is not there starting from 14:29 – EJG_27 Dec 18 '18 at 13:55
  • You'll have to add more to your question descibing what you want to be done in those cases, because it's not 100% clear. Select the next one? The previous one? What if there are >1 missing? What if a single row is the closest non-missing Time-value to two different 5-minute makrs, should it be used twice? et cetera. – IceCreamToucan Dec 18 '18 at 13:56