Finding the corresponding date of one data.frame within another data.frame (data.table)

Question

As this is my first question on stackoverflow, I hope that it meets all requirements. Down below, You've got the reproducible example. Two data.tables with monthly data and one data.table which provides corresponding dates.

First, I wanted to check, if the Dates of dt_list1 and dt_list2 are in the timeframe (variable: Within.period) of the corresponding date values of dt_dates_melted.

Problem is here, that there are several conditions, which the comparison has to fulfill:

If it is >Store< 1 or 2, it should check if >Date< is within the timeframe of >dates< for >Store< 1 or 2.
If it is >Store< 3, it should check if >Date< is within the timeframe of >dates< for >Region< C and >City< 1.
If it is >Region< C AND >Interviewer< 1, it should check if >Date< is within the timeframe of >dates< for >Region< B.
If it is >Region< C AND >Interviewer< 2 or 3, it should check if >Date< is within the timeframe of >dates< for >Region< C and >City< 0.
For the Rest of >Region< C, it should check if >Date< is within the timeframe of >dates< for >Region< C and >City< 1.
For the remaining >Regions< A and B, it should check, if >Date< is within the timeframe of >dates< for >Regions< A and B.

library(data.table)

list1 <- data.table(
  Month = rep(1, 500), 
  Region = sample(c("A", "B", "C"), 500, replace = T, prob = c(0.25, 0.25, 0.5)),
  Store = sample(seq(1:7), 500, replace = T), 
  Interviewer = sample(seq(1:9), 500, replace = T),
  Sale = sample(c(TRUE, FALSE), 500, replace = T, prob = c(0.25, 0.75)),
  Date = sample(seq(as.Date('2020/01/01'), as.Date('2020/01/13'), by="day"), 500, replace = T),
  Day = NA,
  Within.Period = NA,
)

list2 <- data.table(
  Month = rep(2, 600), 
  Region = sample(c("A", "B", "C"), 600, replace = T, prob = c(0.25, 0.25, 0.5)),
  Store = sample(seq(1:8), 600, replace = T), 
  Interviewer = sample(seq(1:10), 600, replace = T),
  Sale = sample(c(TRUE, FALSE), 600, replace = T, prob = c(0.25, 0.75)),
  Date = sample(seq(as.Date('2020/02/02'), as.Date('2020/02/14'), by="day"), 600, replace = T),
  Day = NA,
  Within.Period = NA
)

list1 <- list1[with(list1,
                    order(Region, Store, Interviewer, Sale, Date))]

list2 <- list1[with(list2,
                    order(Region, Store, Interviewer, Sale, Date))]

dates <- data.table(
  Region = c(rep(NA, 4), "A", "A", "B", "B", "C", "C", "C", "C"),
  City = c(rep(NA, 8), 0, 0, 1, 1),
  Store = c(1, 1, 2, 2, rep(NA, 8)),
  Month = rep(c(1,2), 6),
  Sale.Date = as.Date(c("02.01", "03.02", "03.01", "04.02", "03.01", "04.02", "06.01", "07.02", "09.01", "10.02", "09.01", "10.02"), format = "%d.%m"),
  Day.1 = as.Date(c("01.01", "02.02", "02.01", "03.02", "02.01", "03.02", "05.01", "06.02", "08.01", "09.02", "08.01", "09.02"), format = "%d.%m"),
  Day.2 = as.Date(c(rep(NA, 2),"03.01", "04.02", "03.01", "04.02", "06.01", "07.02", "09.01", "10.02", "09.01", "10.02"), format = "%d.%m"),
  Day.3 = as.Date(c(rep(NA, 4), "04.01", "05.02", "07.01", "08.02", "10.01", "11.02", "10.01", "11.02"), format = "%d.%m"),
  Day.4 = as.Date(c(rep(NA, 6), "08.01", "09.02", "11.01", "12.02", "11.01", "12.02"), format = "%d.%m"),
  Day.5 = as.Date(c(rep(NA, 10), "12.01","13.02"), format = "%d.%m")
)

dates_melted <- melt(dates, id.vars = c("Region", "City", "Store", "Month", "Sale.Date"), variable.name = "Day", value.name = "Date", na.rm = TRUE)
´´´

At first, I tried to solve the problem by subsetting the data.tables and merge or left_join. Problem here is that I want to compare dates of different Regions and/or Stores. Furthermore, they are not disjoint and can overlap.

Secondly, i tried to provide the correct logical value for each condition via (here the 2nd condition):

dt_list1$Within.Period <- with(dt_list1, Store == "3" & ifelse(Sale == FALSE, Date %in% dt_dates_melted$Dates[dt_dates_melted$Region == "C" & dt_dates_melted$City == 1], Date %in% dt_dates_melted$Sale.Dates[dt_dates_melted$Region == "C" & dt_dates_melted$City == 1]))
´´´

As this would work for the specific cases, it does not work for the 1st (and also last) condition:

dt_list1$Within.Period <- with(dt_list1, Store %in% dt_dates_melted$Store & ifelse(Sale == FALSE, Date %in% dt_dates_melted$Dates[!is.na(dt_dates_melted$Store)], Date %in% dt_dates_melted$Sale.Dates[!is.na(dt_dates_melted$Store)]))

´´´

This would compare the dates of specific Stores with ALL remaining Store Dates of dt_dates_melted.

The second problem which would emerge by this kind of comparison is, that it would NOT provide the corresponding >Day< of dt_dates_melted, which is needed for the second question:

I would like to fill the >Day< column of dt_list1 and dt_list2 with their respective days of dt_listed_melted.

As this is my first question and I am relatively new to programming in R, I am looking forward to get some kind of help.

Finally, I should also mention, that the original data sets have got 50 columns and 70'000 rows with 20 Regions, 300 Stores and 200 Interviewers.

With best regards, Julian

Finding the corresponding date of one data.frame within another data.frame (data.table)

0 Answers