merging two dataframes with same variable names and (mostly) different na's

I have a situation where I have three dataframe. Using the dummy data below, the dataframes are set up as follows:

• df has an ID variable and a number of additional variables

• df1 has an ID variable to match df and information for varX_J where X
  is 00:19 (as characters), and J is a description for the variable
  name. The first three letters stay the same (var) for all variables

• df2 is the same as df1, with different information.

I need to merge df1 and df2 with df, while merging the data in the columns. df1 and df2 have the same observations. They should have different information (e.g, if there is a value for ID 1 in var09_married in df1, then there shouldn't be information in that same cell in df2. However, the data is messy and there are probably places where this isn't true.

To create this dummy data, I have the following script:

library('dplyr')

df <- data.frame(id = c(1:20),
                 og_var1 = sample(c(1:50), 20, replace=TRUE),
                 state = sample(c(1:52), 20, replace=TRUE),
                 race = sample(c(1:5), 20, replace=TRUE)
                 )

df1 <- left_join(data.frame(id = (1:20)), data.frame(
                  id = c(3,6,9,12),
                  var09_married = c(1,NA,2,1),
                  var09_happiness = c(1,NA,3,2),
                  var10_married = c(NA,1,2,2),
                  var10_happiness = c(NA,5,2,5)), by=c("id"))

df2 <- left_join(data.frame(id = (1:20)), data.frame(
                  id = c(3,6,11,15),
                  var09_married = c(NA,1,1,1),
                  var09_happiness = c(NA,3,3,2),
                  var10_married = c(1,NA,2,1),
                  var10_happiness = c(2,NA,4,4)), by=c("id"))


df <- left_join(df, df1, by=c("id"))
df <- left_join(df, df2, by=c("id"))

What I want is to merge this information together without duplicating the columns. If there is information in df1 and df2 in the same place (e.g., id3 has information for var10 in both df1 and df2), then I want to have the information from df1 in the final dataframe. But I'd also like to create a flag if this information is dropped. So the final dataframe should look like:

dput(df)
structure(list(id = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 
13, 14, 15, 16, 17, 18, 19, 20), og_var1 = c(6L, 4L, 33L, 7L, 
37L, 16L, 34L, 42L, 37L, 37L, 39L, 41L, 24L, 33L, 30L, 2L, 20L, 
29L, 33L, 47L), state = c(2L, 35L, 11L, 14L, 16L, 16L, 40L, 39L, 
28L, 13L, 5L, 26L, 28L, 15L, 13L, 31L, 43L, 25L, 16L, 28L), race = c(5L, 
4L, 2L, 1L, 1L, 2L, 3L, 2L, 2L, 4L, 2L, 3L, 5L, 2L, 3L, 2L, 5L, 
1L, 5L, 5L), var09_married = c(NA, NA, 1, NA, NA, 1, NA, NA, 
2, NA, 1, 1, NA, NA, 1, NA, NA, NA, NA, NA), var09_happiness = c(NA, 
NA, 1, NA, NA, 3, NA, NA, 3, NA, 3, 2, NA, NA, 2, NA, NA, NA, 
NA, NA), var10_married = c(NA, NA, 1, NA, NA, 1, NA, NA, 2, NA, 
2, 2, NA, NA, 1, NA, NA, NA, NA, NA), var10_happiness = c(NA, 
NA, 2, NA, NA, 5, NA, NA, 2, NA, 4, 5, NA, NA, 4, NA, NA, NA, 
NA, NA), flag = c(0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0)), row.names = c(NA, -20L), class = "data.frame")

Try rows_patch instead of joining:

library(dplyr)

df1 |>
  rows_patch(df2, by = "id") |>
  right_join(df, by = "id")

From the documentation ?rows_patch:

> works like rows_update() but only overwrites NA values

This means that if there are values in df1 they will remain. Where there are NA in df1 and values in df2 then those values will be "patched" (i.e. updated with values from df2).

However, depending on your needs you might consider using rows_update which will update the entire row in df1 with a row from df2 if matched.