Search code examples
rdataframesqldf

Extracting row value based on matching columns in separate dataframes using SQL or R

I have two dataframes as shown below:

Df1

date a b
2023-01-31 10 john
2022-02-30 15 elle
2020-03-04 20 david

Df2

date a
2023-01-31 10
2022-02-30 30
2020-04-04 20

I want to extract the values in column b in Dataframe1 only when Df1$date = Df2$date AND Df1$a = Df2$a.

In the above example, the only row where both conditions match is row 1, so my output should be

b
john

How can I write this using sqldf in R or even using base R?

Solution

  • You can use inner_join from dplyr package

    library(dplyr)
df1 %>% 
  inner_join(df2, by = c("date", "a")) %>% 
  select(b) # as per @jpsmith comment
     b
1 john