I have 2 dataframes (x and y) with similar column names, and I would like to merge the 2 dataframes by the "ID" column. Also, I would like to merge them based on the following conditions:
For columns that are present in both dataframes, replace NA values with the non-NA values in either dataframe.
If the ID row is absent in the original dataframe (x), then create a new record below.
x <- data.frame( ID = c(1,2,3), S1 = c(1, NA, NA), S2 = c(2, 2, 2) ) y <- data.frame( ID = c(1, 2, 3, 4), S1 = c(1, 1, 1, 1), S3 = c(3, 3, 3, 3) )
and I would like to merge these 2 dataframes into a final dataframe that looks like this:
final <- data.frame( ID = c(1, 2, 3, 4), S1 = c(1, 1, 1, 1), S2 = c(2, 2, 2, NA), S3 = c(3, 3, 3, 3) )
I have tried using full_join and bind_rows but to no avail. It seems that doing a full_join would result in the creation of S1.x and S1.y as separate columns, but doesn't replace the missing values.
Is there a way that I can achieve this via tidyverse? If not, would appreciate any other solutions. Thanks!