CODEWITHSUNDEEP
rdataframe
tanay
tanay

Reputation: 458

Need to compare two dataframes of different length and update a column of one of the dataframe based on matching criteria in r

I have two dataframes df1 and df2. df1 > df2 in size.

df1 -> A      B        C       D     E  F
       1   4-23-2015   abc    dcv    0  0
       2   5-23-2015   abc    dcvm    1  0
       3   4-24-2015   ab    dcv      0  0
       4   4-23-2014   abc    dcv     0  0

df2 -> A      B         C      D     E F
       1   4-23-2015   abc    dcv    0  0
       3   4-24-2015   abc    dcv    0  0
       9   4-23-2015   abc    dcv    0  0

Now I want to compare

df1$A with df2$A and df1$B with df2$B and when it is true then put df1$F =1. So my output for the above example will be

  df1 -> A      B        C       D     E  F
       1   4-23-2015   abc    dcv    0    1
       2   5-23-2015   abc    dcvm    1   0
       3   4-24-2015   ab    dcv      0   1
       4   4-23-2014   abc    dcv     0   0

The column B is a Date type column and also the comparison is done row vs row.

Upvotes: 1

Views: 836

Answers (2)

Sotos
Sotos

Reputation: 51582

Based on your example, 

df1$F[apply(df1[,c('A', 'B')], 1, paste, collapse = '') %in% apply(df2[,c('A', 'B')], 1, paste, collapse = '')] <- 1
df1
#  A         B   C    D E F
#1 1 4-23-2015 abc  dcv 0 1
#2 2 5-23-2015 abc dcvm 1 0
#3 3 4-24-2015  ab  dcv 0 1
#4 4 4-23-2014 abc  dcv 0 0

Upvotes: 1

David Arenburg
David Arenburg

Reputation: 92282

This is almost a usual merge dupe, but for both cleaner output and faster performance, I would suggest data.table binary left join with simultanious updating by reference. Something like

library(data.table)
setDT(df1)[df2, F := 1L, on = c("A", "B")]
df1
#    A         B   C    D E F
# 1: 1 4-23-2015 abc  dcv 0 1
# 2: 2 5-23-2015 abc dcvm 1 0
# 3: 3 4-24-2015  ab  dcv 0 1
# 4: 4 4-23-2014 abc  dcv 0 0

Otherwise, a simple merge(df1, df2, by = c("A", "B"), all.x = TRUE) will show you which rows were matched, but you will need to remove unnecessary columns and update F accordingly.

Upvotes: 3

Related Questions