CODEWITHSUNDEEP
rloopsdatetimefor-loopdplyr
Johnny Thomas
Johnny Thomas

Reputation: 623

Loop through all entries in one dataframe to find matches based on date criteria in another dataframe in R

So I have a dataframe as follows called df1:

df1 <- structure(list(startTime = structure(c(1519903920, 1519905060, 
1519913740, 1519919880), class = c("POSIXct", "POSIXt"), tzone = "America/New_York"), 
    endTime = structure(c(1519904880, 1519912200, 1519913940, 
    1522142880), class = c("POSIXct", "POSIXt"), tzone = "America/New_York"), 
    impact = c(92.17, 616.43, 63.69, 14.69), impactPercent = c(184.15, 
    1495.17, 138.69, 19.97), impactSpeedDiff = c(3587.72, 25726.22, 
    2616.01, 474.11), maxQueueLength = c(5.76053, 5.76053, 4.829511, 
    2.447619), tmcs = list(c("110N04623", "110-04623", "110N04624", 
    "110-04624", "110N04625", "110-04625", "110N04626", "110-04626", 
    "110N04627"), c("110N04623", "110-04623", "110N04624", "110-04624", 
    "110N04625", "110-04625", "110N04626", "110-04626", "110N04627"
    ), c("110N04623", "110-04623", "110N04624", "110-04624", 
    "110N04625", "110-04625", "110N04626", "110-04626"), c("110N04623", 
    "110-04623", "110N04624", "110-04624", "110N04625")), early_startTime = structure(c(1519903620, 
    1519904760, 1519913740, 1522133400), class = c("POSIXct", 
    "POSIXt"), tzone = "America/New_York")), row.names = c(NA, 
4L), class = "data.frame")

And given this dataframe I need to match with the following dataframe (df2).

df2 <- structure(list(created_tstamp = structure(c(1519926899, 1519913840, 
1519913840, 1519927924, 1522141200, 1522152619, 1522152708, 1522152728, 
1519928416, 1519928785, 1519929080, 1519929306, 1519929964, 1519930050, 
1522154148, 1519930311, 1519930139, 1519930470, 1519930660, 1519929579
), class = c("POSIXct", "POSIXt"), tzone = "America/New_York"), 
    closed_tstamp = structure(c(1519929764, 1519926987, 1519927686, 
    1519928360, 1522152738, 1522152779, 1522154882, 1522152819, 
    1519928464, 1519928914, 1519929266, 1519929741, 1519939420, 
    1519930622, 1522155300, 1519930334, 1519931054, 1519951230, 
    1519930766, 1519930830), class = c("POSIXct", "POSIXt"), tzone = "America/New_York"), 
    code = c("110-04508", "110N04623", "110N04623", "110P05583", 
    "", "", "110N04485", "110N04357", "110-05066", "110-04421", 
    "110N04421", "110P04577", "110-04204", "110-04269", "110+04673", 
    "110-04445", "", "110P05797", "110N04269", "110+04520")), row.names = c(NA, 
20L), class = "data.frame")

A match is indicated by two criteria together:

  1. created_tstamp in df2 is between early_startTime and endTime in df1
  2. code in df2 exists in the same tmcs cell in df1

Both conditions need to be met for it to be considered a match. Ultimately I would like to create an identifier to match each row of df2 to its corresponding match in df1. this is probably done with a loop of some sorts but I am unsure how to write it. Note: this is a subset of the data.

If a data point in df2 doesn't have a match in df1 it should be NA in the identifier column. and both df's should get an ID column in the end.

Upvotes: 0

Views: 367

Answers (1)

Daniel O
Daniel O

Reputation: 4358

I believe this should work. Its hard to tell as it returns no matches with the provided data. This is because none of the created_tstamp are earlier than your endTime

Edit: now that we have a match with the updated question we can juggle the output as follows

test <- apply(df2,1, function(x) which(
    x[1] > df1$early_startTime & 
    x[1] < df1$endTime &
    grepl(x[3], df1$tmcs) &
    x[3] != ""
    ))


IDlist <- lapply(test,paste0,collapse=";")
df2$ID <- unlist(ifelse(lengths(test) > 0,IDlist, NA))

output:

> df2
        created_tstamp       closed_tstamp      code   ID
1  2018-03-01 12:54:59 2018-03-01 13:42:44 110-04508 <NA>
2  2018-03-01 09:17:20 2018-03-01 12:56:27 110N04623    2
3  2018-03-01 09:17:20 2018-03-01 13:08:06 110N04623    2
4  2018-03-01 13:12:04 2018-03-01 13:19:20 110P05583 <NA>
5  2018-03-27 05:00:00 2018-03-27 08:12:18           <NA>
6  2018-03-27 08:10:19 2018-03-27 08:12:59           <NA>
7  2018-03-27 08:11:48 2018-03-27 08:48:02 110N04485 <NA>
8  2018-03-27 08:12:08 2018-03-27 08:13:39 110N04357 <NA>
9  2018-03-01 13:20:16 2018-03-01 13:21:04 110-05066 <NA>
10 2018-03-01 13:26:25 2018-03-01 13:28:34 110-04421 <NA>
11 2018-03-01 13:31:20 2018-03-01 13:34:26 110N04421 <NA>
12 2018-03-01 13:35:06 2018-03-01 13:42:21 110P04577 <NA>
13 2018-03-01 13:46:04 2018-03-01 16:23:40 110-04204 <NA>
14 2018-03-01 13:47:30 2018-03-01 13:57:02 110-04269 <NA>
15 2018-03-27 08:35:48 2018-03-27 08:55:00 110+04673 <NA>
16 2018-03-01 13:51:51 2018-03-01 13:52:14 110-04445 <NA>
17 2018-03-01 13:48:59 2018-03-01 14:04:14           <NA>
18 2018-03-01 13:54:30 2018-03-01 19:40:30 110P05797 <NA>
19 2018-03-01 13:57:40 2018-03-01 13:59:26 110N04269 <NA>
20 2018-03-01 13:39:39 2018-03-01 14:00:30 110+04520 <NA>

Upvotes: 1

Related Questions