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)}

Answer 2

Or, as an alternative to @Chase's code, being a recent plyr fan with a background in databases:

require(plyr)
zz<-join(df1, df2, type="left")
zz[is.na(zz)] <- 0

Answer 3

Another alternative with data.table.

EXAMPLE DATA

dt1 <- data.table(df1)
dt2 <- data.table(df2)
setkey(dt1,x)
setkey(dt2,x)

CODE

dt2[dt1,list(y=ifelse(is.na(y),0,y))]

Answer 4

Assuming df1 has all the values of x of interest, you could use a dplyr::left_join() to merge and then either a base::replace() or tidyr::replace_na() to replace the NAs as 0s:

library(tidyverse)

# dplyr only:
df_new <- 
  left_join(df1, df2, by = 'x') %>% 
  mutate(y = replace(y, is.na(y), 0))

# dplyr and tidyr:
df_new <- 
  left_join(df1, df2, by = 'x') %>% 
  mutate(y = replace_na(y, 0))

# In the sample data column `x` is a factor, which will give a warning with the join. This can be prevented by converting to a character before the join:
df_new <- 
  left_join(df1 %>% mutate(x = as.character(x)), 
            df2 %>% mutate(x = as.character(x)), 
            by = 'x') %>% 
    mutate(y = replace(y, is.na(y), 0))

Answer 5

I used the answer given by Chase (answered May 11 '11 at 14:21), but I added a bit of code to apply that solution to my particular problem.

I had a frame of rates (user, download) and a frame of totals (user, download) to be merged by user, and I wanted to include every rate, even if there were no corresponding total. However, there could be no missing totals, in which case the selection of rows for replacement of NA by zero would fail.

The first line of code does the merge. The next two lines change the column names in the merged frame. The if statement replaces NA by zero, but only if there are rows with NA.

# merge rates and totals, replacing absent totals by zero
graphdata <- merge(rates, totals, by=c("user"),all.x=T)
colnames(graphdata)[colnames(graphdata)=="download.x"] = "download.rate"
colnames(graphdata)[colnames(graphdata)=="download.y"] = "download.total"
if(any(is.na(graphdata$download.total))) {
    graphdata[is.na(graphdata$download.total),]$download.total <- 0
}

Answer 6

Here, a data.table answer. This may be used in selected columns varying the cols_added_df2's definition

df1 = data.frame(x=c('a', 'b', 'c', 'd', 'e'))
df2 = data.frame(x=c('a', 'b', 'c'),y = c(0,1,0))
setDT(df1)
setDT(df2)
df3 <- merge(df1, df2, by = "x", all.x = TRUE)

cols_added_df2 <- setdiff(names(df2), names(df1)) 
df3[, 
  paste0(cols_added_df2) := lapply(.SD, function(col){
    fifelse(is.na(col), 1, col)
  }),
  .SDcols = cols_added_df2
]

Answer 7

With {powerjoin} we can do:

df1 = data.frame(x=c('a', 'b', 'c', 'd', 'e'))
df2 = data.frame(x=c('a', 'b', 'c'),y = c(0,1,0))
powerjoin::power_full_join(df1, df2, fill = 0)
#> Joining, by = "x"
#>   x y
#> 1 a 0
#> 2 b 1
#> 3 c 0
#> 4 d 0
#> 5 e 0

^{Created on 2022-04-28 by the reprex package (v2.0.1)}