I have the following 2 dataframes
Data = data.frame(Name = c("Steve", "Sephiroth", "Hero"),
Date = c("03/01/2019", "01/09/2017", "06/07/2018"))
Map = data.frame(Name = c("Steve", "Steve", "Steve" ,"Sephiroth", "Sephiroth" ,"Sephiroth", "Hero", "Hero", "Hero"),
Class = c("1A", "2B" ,"3A", "1E", "2C", "3E", "1A", "2D" ,"3A"),
StartDate = c("01/03/2017" ,"01/06/2018" ,"27/02/2019" ,"01/05/2017" ,"01/01/2018" ,"01/01/2019" ,"01/05/2017", "01/01/2018", "09/09/2019"),
EndDate = c("31/12/2017", "31/10/2018", "31/09/2019", "08/06/2017", "31/12/2018", "31/12/2019", "29/11/2017", "29/08/2018", "20/11/2019"))
I want the create a new dataframe by merging them according to their dates. If the "Date" falls in between "StartDate" and "EndDate", it should take the Class accordingly. For Example, Hero's date is 06/07/2018, so it should return 2D.
When merging, I do not want the StartDate and EndDate to be included in the dataframe. And if the Date does not fall in between any of the date range, it should return NA.
*I am not able to use library(sqldf) as my workspace does not have the package and no internet connection to download it
edit *