CODEWITHSUNDEEP
rmergedataframe
Lisann
Lisann

Reputation: 5855

Merge unequal dataframes and replace missing rows with 0

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

Upvotes: 77

Views: 162887

Answers (7)

moodymudskipper
moodymudskipper

Reputation: 47330

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)

Upvotes: 0

Captain Tyler
Captain Tyler

Reputation: 594

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
]

Upvotes: 0

sbha
sbha

Reputation: 10422

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

Upvotes: 3

Chase
Chase

Reputation: 69201

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)

Upvotes: 119

Ian E. Gorman
Ian E. Gorman

Reputation: 31

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
}

Upvotes: 2

Wojciech Sobala
Wojciech Sobala

Reputation: 7561

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

Upvotes: 4

Nick Sabbe
Nick Sabbe

Reputation: 11946

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

Upvotes: 7

Related Questions