Merge unequal dataframes by matching two rows replace with 0 the missing values in R

Question

I would like to create a new data frame by merging two unequal data frames by matching two columns and replace with 0 the missing values. These are two examples of the data frames I have:

df1
ID YEAR INTERVIEW  ID_HOUSEHOLD
1    2017           300
1    2018           300
1    2019           300
2    2017           150
2    2018           150
2    2019           150
3    2017           420
3    2018           420

df2
ID YEAR INTERVIEW  YEARS_EDU
1    2017           10
1    2018           10
1    2019           10
3    2017           3
3    2018           3

*note that in the second data frame I don´t have information for individual 2 I would like to get the following data frame:

df3
df1
ID YEAR INTERVIEW  ID_HOUSEHOLD  YEARS_EDU
1    2017           300           10
1    2018           300           10
1    2019           300           10
2    2017           150           0
2    2018           150           0
2    2019           150           0
3    2017           420           3
3    2018           420           3

I am trying:

df3<-merge(df1,df2, by="ID", all=TRUE)
df3<-merge(df1,df2, by="ID","YEAR_INTERVIEW", all=TRUE)

The first option replicates hundreds of ID observations with years of interviews while the second gives me 0 values.

Any help would be much appreciated :) THANK YOU

user_3732271 · Answer

The by needs to be a vector i.e. we can create a vector with c(). Also, all = TRUE, is a full join, but here, it should be a left join, so it is all.x = TRUE. If there is no match, then the element will be NA by default

out <- merge(df1,df2, by=c("ID","YEAR_INTERVIEW"), all.x=TRUE)

The NAs can be converted to 0

out$YEARS_EDU[is.na(out$YEARS_EDU)] <- 0

-output

out
#  ID YEAR_INTERVIEW ID_HOUSEHOLD YEARS_EDU
#1  1           2017          300        10
#2  1           2018          300        10
#3  1           2019          300        10
#4  2           2017          150         0
#5  2           2018          150         0
#6  2           2019          150         0
#7  3           2017          420         3
#8  3           2018          420         3

data

df1 <- structure(list(ID = c(1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L), 
 YEAR_INTERVIEW = c(2017L, 
2018L, 2019L, 2017L, 2018L, 2019L, 2017L, 2018L), ID_HOUSEHOLD = c(300L, 
300L, 300L, 150L, 150L, 150L, 420L, 420L)), class = "data.frame",
row.names = c(NA, 
-8L))


df2 <- structure(list(ID = c(1L, 1L, 1L, 3L, 3L), 
YEAR_INTERVIEW = c(2017L, 
2018L, 2019L, 2017L, 2018L), YEARS_EDU = c(10L, 10L, 10L, 3L, 
3L)), class = "data.frame", row.names = c(NA, -5L))

Merge unequal dataframes by matching two rows replace with 0 the missing values in R

1 Answers

data