CODEWITHSUNDEEP
rdataframedata.table
Wilson Souza
Wilson Souza

Reputation: 860

Finding equal rows between dataframes in R

I have the following data set as example:

df1 <- data.frame(V1 = 1:10, V2 = 1:10, V3 = 1:10)
df2 <- data.frame(V1 = 5:1, V2 = 5:1, v3 = c(1, 4, 5, 2, 3))

If a row in df1 are present in df2, I would create a column in df1 that indicates the corresponding row to the df2 and for other rows showed FALSE or NULL or NA or 0 or ...

output expected:

   V1 V2 V3 rows_matched
1   1  1  1        FALSE
2   2  2  2            4
3   3  3  3        FALSE
4   4  4  4            2
5   5  5  5        FALSE
6   6  6  6        FALSE
7   7  7  7        FALSE
8   8  8  8        FALSE
9   9  9  9        FALSE
10 10 10 10        FALSE

Upvotes: 0

Views: 119

Answers (4)

PaulS
PaulS

Reputation: 25528

Another possible solution, based on dplyr::left_join (we have to previously capitalize V3 in df2):

library(dplyr)

df1 %>% 
  left_join(df2 %>% mutate(new = row_number()))

#> Joining, by = c("V1", "V2", "V3")
#>    V1 V2 V3 new
#> 1   1  1  1  NA
#> 2   2  2  2   4
#> 3   3  3  3  NA
#> 4   4  4  4   2
#> 5   5  5  5  NA
#> 6   6  6  6  NA
#> 7   7  7  7  NA
#> 8   8  8  8  NA
#> 9   9  9  9  NA
#> 10 10 10 10  NA

Upvotes: 1

B. Christian Kamgang
B. Christian Kamgang

Reputation: 6529

Here is another way of solving your problem using data.table

library(data.table)

setDT(df1)
setDT(df2)
 
df1[, rows_matched := df2[df1, on=.(V1,V2,V3), which=TRUE]]
# 
#        V1    V2    V3 rows_matched
#  1:     1     1     1           NA
#  2:     2     2     2            4
#  3:     3     3     3           NA
#  4:     4     4     4            2
#  5:     5     5     5           NA
#  6:     6     6     6           NA
#  7:     7     7     7           NA
#  8:     8     8     8           NA
#  9:     9     9     9           NA
# 10:    10    10    10           NA

Upvotes: 1

Onyambu
Onyambu

Reputation: 79338

in Base R:

cbind(df1, matched = match(interaction(df1), interaction(df2)))

   V1 V2 V3 matched
1   1  1  1      NA
2   2  2  2       4
3   3  3  3      NA
4   4  4  4       2
5   5  5  5      NA
6   6  6  6      NA
7   7  7  7      NA
8   8  8  8      NA
9   9  9  9      NA
10 10 10 10      NA

Upvotes: 2

langtang
langtang

Reputation: 24845

You can do a simple left join. Note: I fixed the column name in df2 from v3 to V3 to match the names of df1

left_join(
  df1, 
  df2 %>% mutate(rows_matched=row_number())
)

Output:

   V1 V2 V3 rows_matched
1   1  1  1           NA
2   2  2  2            4
3   3  3  3           NA
4   4  4  4            2
5   5  5  5           NA
6   6  6  6           NA
7   7  7  7           NA
8   8  8  8           NA
9   9  9  9           NA
10 10 10 10           NA

Upvotes: 1

Related Questions