Subsetting data based on both date ranges and variable matching

Question

I have two different data sets, and I'd like to take a subset of the first based on a combination of variables from the second. Specifically, I'd like to look at a combination of date ranges and straight variable matching.

Looking at the below data frames created here:

options(stringsAsFactors = FALSE)


Loc<-rep("A",10)
InEvent<-rep("IN",10)
InDate<-c("2016-05-10","2016-05-20","2016-05-25","2016-06-10","2016-06-20","2016-07-05","2016-07-17","2016-07-27","2016-08-10","2016-08-20")
InSN<-c("H1","H1","H1","H1","H1","H2","H2","H2","H2","H2")
OutEvent<-rep("OUT",10)
OutDate<-c("2016-05-15","2016-05-23","2016-06-02","2016-06-14","2016-06-26","2016-07-09","2016-07-26","2016-08-09","2016-08-19","2016-08-26")
OutSN<-c("H1","H1","H1","H1","H1","H2","H2","H2","H2","H2")

Cali<-data.frame(Loc,InEvent,InDate,InSN,OutEvent,OutDate,OutSN)

Cali$InDate<-as.POSIXct(strptime(Cali$InDate,format="%Y-%m-%d", tz="UTC"))

Cali$OutDate<-as.POSIXct(strptime(Cali$OutDate,format="%Y-%m-%d", tz="UTC"))
Cali



Sen<-rep("CL",20)
Date<-c("2016-04-10","2016-05-11","2016-05-12","2016-05-13","2016-05-17","2016-05-26","2016-06-17","2016-06-27","2016-07-08","2016-07-20","2016-07-27","2016-08-01","2016-08-05","2016-08-07","2016-08-12","2016-08-15","2016-08-19","2016-08-20","2016-08-23","2016-09-20")
SN<-c("H1","H1","H2","H5","H1","H1","H7","H2","H2","H2","H1","H2","H1","H5","H2","H5","H3","H1","5","H2")


Data<-data.frame(Sen,Date,SN)


Data$Date<-as.POSIXct(strptime(Data$Date,format="%Y-%m-%d", tz="UTC"))
Data

In the final result, I only want rows from the "Data" data frame that lay within the date ranges in "Cali", but also match the H values in "InSN" and "OutSN".

For example, the first row of Cali has a range from 2016-5-10 : 2016-5-15 and a SN value of H1. So I would only want rows in "Data" that are within this date range and have "H1" in the "SN" column.

The resulting data column should be a subset of "Data" only including rows that meet the matching criteria (rows 2,6,9,10,12,15)

Sergio Alyoshkin · Accepted Answer · 2017-04-15 18:46:16Z

1

library(dplyr)
library(magrittr)
Data=left_join(Cali, Data, by = c("InSN"="SN")) %>%
  filter(Date>=InDate, Date<=OutDate)

answered Apr 15, 2017 at 18:46

Sergio Alyoshkin

2121 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Subsetting data based on both date ranges and variable matching

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related