Merge unequal dataframes and replace missing rows with 0

by

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)

More Related Contents:

  1. Simultaneously merge multiple data.frames in a list
  2. Compare two data.frames to find the rows in data.frame 1 that are not present in data.frame 2
  3. Merging a lot of data.frames [duplicate]
  4. Merge two data frames while keeping the original row order
  5. How do I combine two data-frames based on two columns? [duplicate]
  6. How to append rows to an R data frame
  7. Read and rbind multiple csv files
  8. Select rows from one data.frame that are not present in a second data.frame
  9. Merge data frames based on rownames in R
  10. Merge or combine by rownames
  11. Identify records in data frame A not contained in data frame B [closed]
  12. How can I merge more than 2 dataframes in R by rownames?
  13. Data.frame Merge and Selection of values which are common in 2 Data.frames
  14. Deleting specific rows with a pattern[start and end indicators] from a dataframe [closed]
  15. Replace values in a dataframe based on lookup table
  16. Create a sequential number (counter) for rows within each group of a dataframe [duplicate]
  17. Extracting specific columns from a data frame
  18. How to delete rows from a dataframe that contain n*NA
  19. Cumulatively paste (concatenate) values grouped by another variable
  20. How to move cells with a value row-wise to the left in a dataframe [duplicate]
  21. combining two data frames of different lengths [closed]
  22. Why am I getting X. in my column names when reading a data frame?
  23. How to subset data in R without losing NA rows?
  24. Using grep to help subset a data frame
  25. Add an index (numeric ID) column to large data frame [duplicate]
  26. Replace logical values (TRUE / FALSE) with numeric (1 / 0)
  27. Split dataframe using two columns of data and apply common transformation on list of resulting dataframes
  28. Replace value with the name of its respective column
  29. Efficiently merging two data frames on a non-trivial criteria
  30. What you can do with a data.frame that you can’t with a data.table?

Leave a Comment