filter/subset a dataframe based on multiple time periods in R

Question

I need to filter or subset a large (100,000+ rows) dataframe using multiple time periods. I basically need to remove rows in the dataframe that have date/time between the start date/times and end date/times in the time period table. I found this post describing my question (Subsetting data by multiple date ranges - R), but I can't seem to get the code to work. Any advice is appreciated. See below for example data.

time period table:
start,end
7/26/2017 14:05,7/26/2017 16:05
8/24/2017 13:40,8/24/2017 15:40
6/29/2017 20:45,6/30/2017 0:41

dataframe:
time,temp.c,lux,serial.num
6/29/2017 20:40,33.63,0,20168779
6/29/2017 20:40,33.11,0,20168780
6/29/2017 20:50,20.42,602.8,20148333
6/29/2017 20:50,20.32,721.2,20148334
6/29/2017 20:50,19.75,3788.9,20148335
7/26/2017 16:00,22.9,183,20168779
7/26/2017 16:00,23.29,1237.9,20168780
7/26/2017 16:10,23.38,1173.3,20148333
7/26/2017 16:10,23.67,839.6,20148334
8/24/2017 15:40,24.06,387.5,20168780
8/24/2017 15:50,23.58,0,20148332

Cybernetic Cybernetic · Accepted Answer · 2018-01-14T05:39:15

Data:

time_period_table <- data.frame(start=c('7/26/2017 14:05', '8/24/2017 13:40', '6/29/2017 20:45'), end=c('7/26/2017 16:05', '8/24/2017 15:40', '6/30/2017 0:41'))
time_period_table $start <- as.POSIXct(time_period_table $start, format="%m/%d/%Y %H:%M")
time_period_table $end <- as.POSIXct(time_period_table $end, format="%m/%d/%Y %H:%M")

time_period_table

Keep rows in dataframe with date/time between start date/time and end date/time:

keep_by_date_time <- function(data_frame, start_date, start_time, end_date, end_time) {
    from <- paste(start_date, start_time)
    to <- paste(end_date, end_time)
    sub_frame <- subset(data_frame, start >= as.POSIXct(from) & end <= as.POSIXct(to))
    return(sub_frame)
}

Usage:

# return rows with date and time between July 26, 2017 at 1:05pm and July 26, 2017 at 5:05pm:

keep_by_date_time(time_period_table, '2017-07-26', '13:05', '2017-07-26', '17:05')

Remove rows in dataframe with date/time between start date/time and end date/time:

remove_by_date_time <- function(data_frame, start_date, start_time, end_date, end_time) {
    from <- paste(start_date, start_time)
    to <- paste(end_date, end_time)
    rem_row <- which(time_period_table$start >= as.POSIXct(from) & time_period_table$end <= as.POSIXct(to))
    rem_frame <- data_frame[-rem_row,]
    return(rem_frame)
}

Usage:

# remove rows with date and time between August 20, 2017 at 1:05pm and August 26, 2017 at 5:05pm:

remove_by_date_time(time_period_table, '2017-08-20', '13:05', '2017-08-26', '17:05')

filter/subset a dataframe based on multiple time periods in R

1 Answers