77

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
Improve this question
1
  • We usually call characters also values, so your y column would be called numeric. Commented Sep 4, 2018 at 15:22

7 Answers 7

Reset to default
119

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)

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Hi Chase, can I used command "all=true' for df1 only. Sometimes this command includes data that are not available in df1 but are available in df2
@jbest - there are arguments all.x and all.y where x == the first data.frame object and y == the second, precisely for this situation. See the help page for ?merge for details.
7

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
Improve this answer

Comments

4

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))]
Improve this answer

1 Comment

In version 1.10.4, you don't need to setkey and can use df2[df1, on="x"][is.na(y), y := 0] immediately after creating the data.tables to produce the desired result.
3

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))
Improve this answer

Comments

2

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
}
Improve this answer

Comments

1

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
]
Improve this answer

2 Comments

fifelse(is.na(col), 1, col) should be ifelse(is.na(col), 0, col) to replace missing with 0, correct?
you can, but I tried to keep into data.table pkg. See fifelse
0

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)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.