Merging data frame and filling missing values

Question

I want to merge the following 3 data frames and fill the missing values with -1. I think I should use the fct merge() but not exactly know how to do it.

> df1
  Letter Values1
1      A      1
2      B      2
3      C      3
> df2
  Letter Values2
1      A      0
2      C      5
3      D      9
> df3
  Letter Values3
1      A     -1
2      D      5
3      B     -1

desire output would be:

  Letter Values1  Values2   Values3
1      A      1      0        -1
2      B      2     -1        -1  # fill missing values with -1
3      C      3      5        -1
4      D     -1      9         5

code:

> dput(df1)
structure(list(Letter = structure(1:3, .Label = c("A", "B", "C"
), class = "factor"), Values1 = c(1, 2, 3)), class = "data.frame", row.names = c(NA, 
-3L))
> dput(df2)
structure(list(Letter = structure(1:3, .Label = c("A", "C", "D"
), class = "factor"), Values2 = c(0, 5, 9)), class = "data.frame", row.names = c(NA, 
-3L))
> dput(df3)
structure(list(Letter = structure(c(1L, 3L, 2L), .Label = c("A", 
"B", "D"), class = "factor"), Values3 = c(-1, 5, -1)), class = "data.frame", row.names = c(NA, 
-3L))

score 3 · Accepted Answer · answered Jul 08 '20 at 07:00

You can get data frames in a list and use merge with Reduce. Missing values in the new dataframe can be replaced with -1.

new_df <- Reduce(function(x, y) merge(x, y, all = TRUE), list(df1, df2, df3))
new_df[is.na(new_df)] <- -1

new_df
#  Letter Values1 Values2 Values3
#1      A       1       0      -1
#2      B       2      -1      -1
#3      C       3       5      -1
#4      D      -1       9       5

A tidyverse way with the same logic :

library(dplyr)
library(purrr)

list(df1, df2, df3) %>%
   reduce(full_join) %>%
   mutate(across(everything(), replace_na, -1))

score 0 · Answer 2 · answered Jul 08 '20 at 07:02

Here's a dplyr solution

df1 %>% 
    full_join(df2, by = "Letter") %>%
    full_join(df3, by = "Letter") %>%
    mutate_if(is.numeric, function(x) replace_na(x, -1))

output:

  Letter Values1 Values2 Values3
  <chr>    <dbl>   <dbl>   <dbl>
1 A            1       0      -1
2 B            2      -1      -1
3 C            3       5      -1
4 D           -1       9       5

Merging data frame and filling missing values

2 Answers2