How to find rows with same values in two columns?

Question

It's a little hard to explain, but I'm trying to compare the column "cpf" from two different data frames. I want to identify when the value in the two "cpf" columns from (df1) and (df2) is equal (these values can be in different rows). After that, I want to update the NA values if these are available from the other data frame

df1 
    cpf x  y
1   21  NA NA
2   32  NA NA
3   43  NA NA
4   54  NA NA
5   65  NA NA

df2 
    cpf x  y
1   54  5  10
2   0   NA NA
3   65  3   2
4   0   NA NA
5   0  NA NA

I want the following result

df3 
    cpf x  y
1   21  NA NA
2   32  NA NA
3   43  NA NA
4   54  5  10
5   65  3   2

Answer 1

We could do a join on 'cpf' and use fcoalecse

library(data.table)
setDT(df1)[df2, c('x', 'y') := .(fcoalesce(x, i.x), 
        fcoalesce(y, i.y)), on = .(cpf)]

-output

df1
#   cpf  x  y
#1:  21 NA NA
#2:  32 NA NA
#3:  43 NA NA
#4:  54  5 10
#5:  65  3  2

---

Or using coalecse from dplyr after a left_join

library(dplyr)
left_join(df1, df2, by = 'cpf') %>%
     transmute(cpf, x = coalesce(x.x, x.y), y = coalesce(y.x, y.y))
#  cpf  x  y
#1  21 NA NA
#2  32 NA NA
#3  43 NA NA
#4  54  5 10
#5  65  3  2

---

In base R, can use match

i1 <- match(df1$cpf, df2$cpf, nomatch = 0)
i2 <- match(df2$cpf, df1$cpf, nomatch = 0)
df1[i2, -1] <- df2[i1, -1]

data

df1 <- structure(list(cpf = c(21L, 32L, 43L, 54L, 65L), x = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_), y = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_)), row.names = c("1", 
"2", "3", "4", "5"), class = "data.frame")

df2 <- structure(list(cpf = c(54L, 0L, 65L, 0L, 0L), x = c(5L, NA, 3L, 
NA, NA), y = c(10L, NA, 2L, NA, NA)), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5"))