Subsetting one df based on two columns of df1 and df2

Question

I am trying to subset one DF based on matching two columns of another DF.

DF1:

ColA    ColB     ColD
d       4        3
e       3        23
w       9        93 
q       2        4

DF2:

ColA    ColB     ColD
d       4        9
e       343      545
w       9        76 
x       34       94
y       01       0

I want to subset DF2 to only include the rows where ColA and ColB are found in DF 1. In this case the desired output would be:

ColA    ColB     ColD
d       4        9
w       9        76

How can I subset based on two columns? If possible, I'd like to do this with dplyr, subset() function, but the simpler implementation the better!

FYI `subset` is a base function, not dplyr. What you're looking for is a semi- or inner-join — camille, Feb 01 '22 at 23:12

PaulS · Accepted Answer · 2022-02-01T23:21:35.653

A possible solution, based on inner_join:

library(tidyverse)

df1 <- data.frame(
  stringsAsFactors = FALSE,
  ColA = c("d", "e", "w", "q"),
  ColB = c(4L, 3L, 9L, 2L),
  ColD = c(3L, 23L, 93L, 4L)
)

df2 <- data.frame(
  stringsAsFactors = FALSE,
  ColA = c("d", "e", "w", "x", "y"),
  ColB = c(4L, 343L, 9L, 34L, 1L),
  ColD = c(9L, 545L, 76L, 94L, 0L)
)

df2 %>% 
  inner_join(df1, by = c("ColA", "ColB")) %>% 
  select(-ColD.y, ColD = ColD.x)

#>   ColA ColB ColD
#> 1    d    4    9
#> 2    w    9   76

Subsetting one df based on two columns of df1 and df2

1 Answers1