Selecting values from one dataframe if dates matches other dataframe

Question

I have two dataframes df1 and df2, if df1$date1 matches df2$date2 then print only those values from df2 as shown in the output.

df1:

date1
01-09-2018
01-10-2018
01-11-2018
01-12-2018

df2:

date2       value
01-05-2018      0
01-06-2018      0
01-07-2018      0
01-08-2018      0
01-09-2018      5
01-10-2018      2
01-11-2018      7
01-12-2018      3

output:

value
5
2
7
3

Answer 1

We can try using merge here in inner join mode:

merge(df2, df1, by="date", all=FALSE)$value
[1] 5 2 7 3

# data
df2 <- data.frame(date=c("01-05-2018", "01-06-2018", "01-07-2018", "01-08-2018",
                         "01-09-2018","01-10-2018","01-11-2018","01-12-2018"),
                  value=c(0,0,0,0,5,2,7,3))
df1 <- data.frame(date=c("01-09-2018", "01-10-2018", "01-11-2018", "01-12-2018"))

Demo

Note that I renamed your date columns both to date, which is needed for merge to easily work.

Answer 2

Using sqldf: We can perform inner join between two data.frames using date column

library(sqldf)
sqldf("select a.value from df2 as a JOIN df1 USING(date)")

Output:

   value
1     5
2     2
3     7
4     3

Data (provided by Tim):

df2 <- data.frame(date=c("01-05-2018", "01-06-2018", "01-07-2018", "01-08-2018",
                 "01-09-2018","01-10-2018","01-11-2018","01-12-2018"),
                 value=c(0,0,0,0,5,2,7,3))
df1 <- data.frame(date=c("01-09-2018", "01-10-2018", "01-11-2018", "01-12-2018"))