Joining two data tables and filter them by rows in R

Question

I have two data table, the first looks like this (Table1):

The second one looks like this (Table2):

I'm looking for a way to combine those two tables and make it look like this (Table3):

I'll elaborate:

• Table1 has more rows than Table2.

• Values in the X column of Table1 may repeat several times ("a", "a", "d", "d", "h", "h", etc.), but each repeated value has a different value under the Y column ("a" - 1, "a" - 2, "d" - 5, "d" - 6, etc.).

• Some values in the Y column of Table1 may be the same for different values under the "X" column (for example: "b" - 3 and "e" - 3).

• The "Y" column of Table 1 and the "Y" column of Table2 are the keys for joining the tables (remember that Table1 has more rows than Table2 - meaning that all the values of Table2$Y are found in Table1$Y but not vice versa).

• Eventually, Table3 should contain only the rows from columns "X" and "Y" from Table1 that have a unique value in column "Z" (forming a 3-column table).

I really hope my explanation is coherent, I find this joining operation very frustrating.

Thank you for your kind assistance. -A

Answer 1

Definitely easier to do this when you create a minimum reproducible example.

But what you're looking for is an inner_join

library(dplyr)

df3 <- inner_join(df1, df2, by = "Y")