Objective: To map a date from one dataframe to another given that it falls within a certain date interval. For example, let's say we need to deliver a gift within the time interval from either 20/12/2017 to 25/12/2017 or 26/12/2017 to 30/12/2017, and receive a response from the gift sender on the 23/12/2017. I want to create a function that can identify where to place the response date based on the date interval it falls within. In the example above, the response date would fall in the interval 20/12/2017 to 25/12/2017. Note: The term "Match" below means a certain condition is met from one data frame with another.
Here is some sample code to demonstrate what I mean.
# Creating the Data Frame with a start and end date interval
StartDate <- seq(as.Date("2000/1/1"), by = "month", length.out = 10)
EndDate <- StartDate +7
Dates_Interval <- data.frame(StartDate,EndDate)
# Creating a second data frame with the response dates only
ResponseDate <- seq(as.Date("2000/1/6"), by = "month", length.out = 10)
Response_Substitute <- data.frame(ResponseDate)
# Substituting random NA values
Response_Substitute[c(1,5,8),] <- NA
# > Response_Substitute
# ResponseDate
# 1 <NA>
# 2 2000-02-09
# 3 2000-03-06
# 4 2000-04-06
# 5 <NA>
# 6 2000-06-06
# 7 2000-07-06
# 8 <NA>
# 9 2000-09-06
# 10 2000-10-06
# Creating a function which evaluates a value in data frame two
# (Response_Substitute) and checks
# whether it meets
# a condition in Dates_Interval.
dateresponses <- function(x,y,z) {
sub_date <- ifelse ( y <=x && x <= z, x, NA)
converteddate <- as.Date(sub_date, origin = "1899-12-30")
return(converteddate)
}
# Example of the function in use to show how it matches a certain condition.
x <- Response_Substitute[2,1]
b <- dateresponses(x,Dates_Interval[2,1],Dates_Interval[2,2])
# > b
# [1] "1930-02-04"
# Example of the function in use to show when a response date does not
# match a certain condition
x <- Response_Substitute[2,1] <- as.Date("2000/2/9")
b <- dateresponses(x,Dates_Interval[2,1],Dates_Interval[2,2])
# > b
# [1] NA
# Example of the function in use to show when there is no response date in
# the Response_Substitute variable
x <- Response_Substitute[1,1]
b <- dateresponses(x,Dates_Interval[2,1],Dates_Interval[2,2])
# > b
# [1] NA
I need a function that will be able to create a new column in the Dates_Interval data frame which matches the response date with the date interval it falls within from StartDate and EndDate columns. If there is no match, then the response will be NA if there is no response. If there is a response but the response date does not fall into any interval, then I want a dataframe to be created that captures unmatched responses.
This is what the final dataframe could look like:
Dates_Interval$ResponseDate <- Response_Substitute
# > Dates_Interval
# StartDate EndDate ResponseDate
# 1 2000-01-01 2000-01-08 <NA>
# 2 2000-02-01 2000-02-08 2000-02-06
# 3 2000-03-01 2000-03-08 2000-03-06
# 4 2000-04-01 2000-04-08 2000-04-06
# 5 2000-05-01 2000-05-08 <NA>
# 6 2000-06-01 2000-06-08 2000-06-06
# 7 2000-07-01 2000-07-08 2000-07-06
# 8 2000-08-01 2000-08-08 <NA>
# 9 2000-09-01 2000-09-08 2000-09-06
# 10 2000-10-01 2000-10-08 2000-10-06
And for response dates that are not NA but do not match any interval another dataframe could be created like this:
Unmatched_Response_Date <- data.frame(seq(as.Date("2000/1/9"), by = "month",
length.out = 2))
colnames(Unmatched_Response_Date) <- "Unmatched Responses"
Unmatched_Response_Date
# > Unmatched_Response_Date
# Unmatched Responses
# 1 2000-01-09
# 2 2000-02-09
EDIT:
There is bug I have noticed when using the dateresponses function. When I use a date from the Response_substitute data frame. The output of the date is not the same as the data frame. e.g. for Response_substitute[2,1] the value should be 2000-02-09 but instead I get 1930-02-04. Any ideas also for solving this issue?
