Lookup in R with two criteria

Question

I have two dataframes:

df1 <- data.frame(row1 = c("A", "A", "A", "B", "B", "B", "C", "C", "C"),
                  row2 = c("aa", "bb", "cc", "aa", "bb", "cc", "aa", "bb", "cc"), 
                  row3 = c(1, 5, 7, 6, 8, 9, 3, 2, 4))

  row1 row2 row3
1    A   aa    1
2    A   bb    5
3    A   cc    7
4    B   aa    6
5    B   bb    8
6    B   cc    9
7    C   aa    3
8    C   bb    2
9    C   cc    4

and

df2 <- data.frame(row1 = c("A", "A", "B", "B", "B", "C", "C"),
                  row2 = c("aa", "bb", "aa", "bb", "cc", "aa", "cc"))

  row1 row2
1    A   aa
2    A   bb
3    B   aa
4    B   bb
5    B   cc
6    C   aa
7    C   cc

I would like to create row 3 for df2 based on rows 1 and 2. The desired output looks like this:

  row1 row2 row3
1    A   aa    1
2    A   bb    5
3    B   aa    6
4    B   bb    8
5    B   cc    9
6    C   aa    3
7    C   cc    4

In my real dataframe the df2 has many more columns (but row3 should be added based on two specific columns). I also tried it with left join (unfortunately didn't save my approach) but this also didn't work with my implementation.

I found many solutions for "lookups" but all of these compare only one column and i couldn't figure out how two compare multiple columns. There is probably an easy solution, but i'm stuck. Can anyone help me with this?

Dan Adams · Accepted Answer · 2023-02-06T21:51:18.963

Indeed, dplyr::left_join() is what you need here.

Also, you're using names like row1 for things that are actually columns. It might be leading to some confusion.

library(dplyr)

df1 <- data.frame(row1 = c("A", "A", "A", "B", "B", "B", "C", "C", "C"),
                  row2 = c("aa", "bb", "cc", "aa", "bb", "cc", "aa", "bb", "cc"), 
                  row3 = c(1, 5, 7, 6, 8, 9, 3, 2, 4))

df2 <- data.frame(row1 = c("A", "A", "B", "B", "B", "C", "C"),
                  row2 = c("aa", "bb", "aa", "bb", "cc", "aa", "cc"))


left_join(df2, df1)
#> Joining, by = c("row1", "row2")
#>   row1 row2 row3
#> 1    A   aa    1
#> 2    A   bb    5
#> 3    B   aa    6
#> 4    B   bb    8
#> 5    B   cc    9
#> 6    C   aa    3
#> 7    C   cc    4

^{Created on 2023-02-01 with reprex v2.0.2}

Lookup in R with two criteria

1 Answers1