Merge unequal dataframes and replace missing rows with 0

Question

I have two data.frames, one with only characters and the other one with characters and values.

df1 = data.frame(x=c('a', 'b', 'c', 'd', 'e')) df2 = data.frame(x=c('a', 'b', 'c'),y = c(0,1,0)) merge(df1, df2)   x y 1 a 0 2 b 1 3 c 0  

I want to merge df1 and df2. The characters a, b and c merged good and also have 0, 1, 0 but d and e has nothing. I want d and e also in the merge table, with the 0 0 condition. Thus for every missing row at the df2 data.frame, the 0 must be placed in the df1 table, like:

  x y 1 a 0 2 b 1 3 c 0 4 d 0 5 e 0 

Answer 1

Take a look at the help page for merge. The all parameter lets you specify different types of merges. Here we want to set all = TRUE. This will make merge return NA for the values that don't match, which we can update to 0 with is.na():

zz <- merge(df1, df2, all = TRUE) zz[is.na(zz)] <- 0  > zz   x y 1 a 0 2 b 1 3 c 0 4 d 0 5 e 0 

Updated many years later to address follow up question

You need to identify the variable names in the second data table that you aren't merging on - I use setdiff() for this. Check out the following:

df1 = data.frame(x=c('a', 'b', 'c', 'd', 'e', NA)) df2 = data.frame(x=c('a', 'b', 'c'),y1 = c(0,1,0), y2 = c(0,1,0))  #merge as before df3 <- merge(df1, df2, all = TRUE) #columns in df2 not in df1 unique_df2_names <- setdiff(names(df2), names(df1)) df3[unique_df2_names][is.na(df3[, unique_df2_names])] <- 0  

^{Created on 2019-01-03 by the reprex package (v0.2.1)}