How to find common rows between two dataframe in R?

Question

I would like to make a new data frame which only includes common rows of two separate data.frame. example:

data.frame 1

1 id300
2 id2345
3 id5456
4 id33
5 id45
6 id54

data.frame2

1 id832
2 id300
3 id1000
4 id45
5 id984
6 id5456
7 id888

So I want my output be:

1 id300
2 id45
3 id5456

any suggestion please?

Answer 1

The appropriate dplyr function here is inner_join (returns all rows from df x that have a match in df y.)

library(dplyr)
inner_join(df1, df2)

      V1
1  id300
2 id5456
3   id45

Note: the rows are returned in the order in which they are in df1. If you did inner_join(df2, df1), id45 would come before id5456.

Answer 2

common <- intersect(data.frame1$col, data.frame2$col)  
data.frame1[common,] # give you common rows in data frame 1  
data.frame2[common,] # give you common rows in data frame 2

Answer 3

Use merge

new_data_frame <- merge(data.frame1, data.frame2)

I'm assuming you have only one column in each data frame and they have the same name in both frames. If not use the column you want to intersect by with by.x = "nameCol1" and by.y = "nameCol2", where nameCol are the real column names.

Added after first comment
If you have more columns in any data frame the command is the same. Do it this way:

>a  #Data frame 1
      c1 c2
1  id300  6
2 id2345  5
3 id5456  4
4   id33  3
5   id45  2
6   id54  1

> b #Data frame 2
     a  f
1  asd 12
2 id33 10
3 id45  8
4 id54  6

As you may see, they don't share column names and have 2 columns each. So:

> merge(a,b, by.x = "c1", by.y = "a")

    c1 c2  f
1 id33  3 10
2 id45  2  8
3 id54  1  6

The only rows that are left are those that have the same entries in common in the selected columns.

Answer 4

We can also do this with fintersect from data.table after converting the data.frame to data.table

library(data.table)
fintersect(setDT(df1), setDT(df2))
#       v1
#1:  id300
#2:   id45
#3: id5456

data

df1 <- structure(list(v1 = c("id300", "id2345", "id5456", "id33", "id45", 
"id54")), .Names = "v1", class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6"))

df2 <- structure(list(v1 = c("id832", "id300", "id1000", "id45", "id984", 
"id5456", "id888")), .Names = "v1", class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6", "7"))

Answer 5

To achieve this, you should assign the row names in both data frame and then process with intersect in R. This can be achieved with the following command:

intersect(dataframe.1$V1,dataframe.2$V2)

Answer 6

Use intersect function of dplyr package

1. install this package

2. use this function like

   var_name <- intersect(Name of 1st dataset, Name of 2nd dataset)

The best thing about this is it can work with character also while merge and inner_join cannot able to give results also you can use this function to see where is their location on your dataset

for (i in (no. of duplicates ) ) {
 for (j in ( no. of rows in other dataset  ) ) {
  if (new[i] == ac$variable_name[j]) {
  print(j)
  }
  } 
}