How can I subset a data frame with a second data frame

Question

I have two sets of data that were imported. The second set of data has only one column which corresponds with 44 out of 86 of the rows in the first data set. How can I form a new data set which only involves the data of the rows that appear in both data sets. The names of the columns are different.

I am not in a position to publish the data being use, there for code below is along the same lines but not exactly the same. However, as I am trying to select 44 rows choosing the lines isn't an option.

df1:

Fruit        Circumference      Weight 
Apple        10                 20
Orange       13                 30
Pear         10                 21
Kiwi         7                  15
Cherry       3                  10
Strawberry   4                  13
Blueberry    2                  5

df2:

 Fruit Name 
 Orange 
 Cherry 
 Blueberry

New data:

Fruit        Circumference      Weight 
Orange       13                 30
Cherry       3                  10
Blueberry    2                  5

Rui Barradas · Accepted Answer · 2020-12-13T16:46:11.957

Here are three base R ways.
Note that the second data.frame column name has an embedded space in it. The column name must, therefore, be between backticks in the first two ways and in the 3rd arguments by.x and by.y are used.

df1[df1$Fruit %in% df2$`Fruit Name`, ]
#      Fruit Circumference Weight
#2    Orange            13     30
#5    Cherry             3     10
#7 Blueberry             2      5


df1[match(df2$`Fruit Name`, df1$Fruit), ]
#      Fruit Circumference Weight
#2    Orange            13     30
#5    Cherry             3     10
#7 Blueberry             2      5


merge(df1, df2, by.x = "Fruit", by.y = "Fruit Name")
#      Fruit Circumference Weight
#1 Blueberry             2      5
#2    Cherry             3     10
#3    Orange            13     30

Data

df1 <- read.table(text = "
Fruit        Circumference      Weight 
Apple        10                 20
Orange       13                 30
Pear         10                 21
Kiwi         7                  15
Cherry       3                  10
Strawberry   4                  13
Blueberry    2                  5
", header = TRUE)

df2 <- read.table(text = "
 'Fruit Name'
 Orange 
 Cherry 
 Blueberry 
", header = TRUE, check.names = FALSE)

new <- read.table(text = "
Fruit        Circumference      Weight 
Orange       13                 30
Cherry       3                  10
Blueberry    2                  5
", header = TRUE)

@Sabrina Can you post sample data? Please edit **the question** with the output of `dput(df1)`. Or, if it is too big with the output of `dput(head(df1, 20))`. And the same for `df2`. — Rui Barradas, Dec 11 '20 at 19:43
@Sabrina [Possible duplicate](https://stackoverflow.com/questions/38850629/subset-a-column-in-data-frame-based-on-another-data-frame-list). — Rui Barradas, Dec 11 '20 at 19:45
Thank you for mentioning the similar question, however, none of the solutions for that question has worked — Sabrina, Dec 13 '20 at 10:26

score 1 · Answer 2 · edited Dec 13 '20 at 16:48

1

We can use inner_join. It also have an option to check with only selected columns with by argument

library(dplyr)
inner_join(df1, df2)

Or another option is intersect from dplyr

intersect(df1, df2)

With the posted data it wold be

inner_join(df1, df2, by = c("Fruit" = "Fruit Name"))

edited Dec 13 '20 at 16:48

Rui Barradas

70,273
8
34
66

answered Dec 11 '20 at 18:17

akrun

874,273
37
540
662

does this work if the names of the columns are different? – Sabrina Dec 11 '20 at 18:40
@Sabrina You may need to set the names of one of the dataset with that of another i.e. `inner_join(df1, setNames(df2, names(df1)))` (assuming the columns are in the same order) – akrun Dec 11 '20 at 18:41
@Sabrina Or you can specify the `by` option in `inner_join` i.e. `inner_join(df1, df2, by = setNames(names(df2), names(df1)))` – akrun Dec 11 '20 at 18:43

How can I subset a data frame with a second data frame

2 Answers2