R: Subset data frame based on multiple values for multiple variables

Question

I need to pull records from a first data set (called df1 here) based on a combination of specific dates, ID#s, event start time, and event end time that match with a second data set (df2). Everything works fine when there is just 1 date, ID, and event start and end time, but some of the matching records between the data sets contain multiple IDs, dates, or times, and I can't get the records from df1 to subset properly in those cases. I ultimately want to put this in a FOR loop or independent function since I have a rather large dataset. Here's what I've got so far:

I started just by matching the dates between the two data sets as follows:

match_dates <- as.character(intersect(df1$Date, df2$Date))

Then I selected the records in df2 based on the first matching date, also keeping the other columns so I have the other ID and time information I need:

records <- df2[which(df2$Date == match_dates[1]), ]

The date, ID, start, and end time from records are then:

[1] "01-04-2009" "599091"     "12:00"      "17:21"

Finally I subset df1 for before and after the event based on the date, ID, and times in records and combined them into a new data frame called final to get at the data contained in df1 that I ultimately need.

before <- subset(df1, NUM==records$ID & Date==records$Date & Time<records$Start)
after <- subset(df1, NUM==records$ID & Date==records$Date & Time>records$End)
final <- rbind(before, after)

Here's the real problem - some of the matching dates have more than 1 corresponding row in df2, and return multiple IDs or times. Here is what an example of multiple records looks like:

records <- df2[which(df2$Date == match_dates[25]), ]

> records$ID
[1] 507646 680845 680845
> records$Date
[1] "04-02-2009" "04-02-2009" "04-02-2009"
> records$Start
[1] "09:43" "05:37" "11:59"
> records$End
[1] "05:19" "11:29" "16:47"

When I try to subset df1 based on this I get an error:

before <- subset(df1, NUM==records$ID & Date==records$Date & Time<records$Start)
Warning messages:
1: In NUM == records$ID :
  longer object length is not a multiple of shorter object length
2: In Date == records$Date :
  longer object length is not a multiple of shorter object length
3: In Time < records$Start :
  longer object length is not a multiple of shorter object length

Trying to do it manually for each ID-date-time combination would be way to tedious. I have 9 years worth of data, all with multiple matching dates for a given year between the data sets, so ideally I would like to set this up as a FOR loop, or a function with a FOR loop in it, but I can't get past this. Thanks in advance for any tips!

Welcome to stackoverflow, @marcinus. If you would like an answer to this question, we need a minimum working example (MWE) that we can use to troubleshoot your problem. Use dput if you need to paste a subset of your examples here. — black_sheep07
It's a little unclear what your desired output is when you have multiple matches. For each matching event (everything in records), are you just after the one event immediately before and the one event immediately after ? The data.table package is definitely your friend for this sort of "complex" query on large datasets, though there is a significant learning curve. As blacksheep mentioned, a small reproducible example is important here. — mathematical.coffee
After some more tinkering I realized that there are multiple replicates of events for each date so instead of looking before and after the event, I changed the code to get at the times inclusive of the event, which in the end will get me to the same information I was looking for. I was able to do that with no problems, but now need to figure out how to run a loop on all the matching dates that will return all the date, time, and ID information. — marcinus

NGaffney NGaffney · Accepted Answer · 2015-12-23T05:54:11

If you're asking what I think you are the filter() function from the dplyr package combined with the match function does what you're looking for.

> df1 <- data.frame(A = c(rep(1,4),rep(2,4),rep(3,4)), B = c(rep(1:4,3)))
> df1
   A B
1  1 1
2  1 2
3  1 3
4  1 4
5  2 1
6  2 2
7  2 3
8  2 4
9  3 1
10 3 2
11 3 3
12 3 4
> df2 <- data.frame(A = c(1,2), B = c(3,4))
> df2
  A B
1 1 3
2 2 4
> filter(df1, A %in% df2$A, B %in% df2$B)
  A B
1 1 3
2 1 4
3 2 3
4 2 4

R: Subset data frame based on multiple values for multiple variables

1 Answers