How do I intersect two data.frames in R?

Question

I have two tables that are in the data.frame structure. Table 1 contains a column of 200 gene IDs (letters and numbers) and Table 2 contains a list of 4,000 gene IDs (in rows) as well as 20 additional columns. I want to intersect these two tables and generate a new Table 3 that contains the 200 gene IDs as well as the associated information in the 20 columns.

table3 <- table1%n%table2

Answer 1

You want something like

table3 <- merge(table1, table2, by.x="id", by.y="id", all.x=T, all.y=F)

You might also be able to do subsetting with something like this:

table3 <- table2[table2$id %in% table1$id,]

A reprex would have made this post more likely to get a good response, but you should have been able to find something to help you with a little searching. If these don't work because you have a unique problem no one has asked before, give is a reprex and we can try to give you alternative solutions.

edit: for a little more context, here's a similar question I replied to last week and here's a great post on understanding merges.

Answer 2

I recommend the dplyr package. It works more intuitively than merge in my opinion.

you can just type:

table3 <- left_join(table1, table2, by = "unique_id")