combine two data frames or tibbles

Question

I have the following data frames:

a <- data.frame(Test=1:4,
            TestA=5:6)
> a
    Test TestA
1    1     5
2    2     6
3    3     5
4    4     6


b <- data.frame(TEST=1:10,
            TestB=11:20)

> b
    TEST TestB
1     1    11
2     2    12
3     3    13
4     4    14
5     5    15
6     6    16
7     7    17
8     8    18
9     9    19
10   10    20

And I want to combine them such that the result looks like:

    Test TestA TEST   TestB
1     1     5    1      11
2     2     6    2      12
3     3     5    3      13
4     4     6    4      14
5     0     0    5      15
6     0     0    6      16
7     0     0    7      17
8     0     0    8      18
9     0     0    9      19
10    0     0   10      20

That is, to combine with unmatched rows filled with zeros.

I am convinced that a simple solution exists and it would be fine if there is a way with dplyr.

Answer 1

You can combine two data frames using merge().

df<-merge(x=a,y=b,by.x="Test",by.y = "TEST",all= T)

The above produces:

   Test TestA TestB
1     1     5    11
2     2     6    12
3     3     5    13
4     4     6    14
5     5    NA    15
6     6    NA    16
7     7    NA    17
8     8    NA    18
9     9    NA    19
10   10    NA    20

If you want Test and TEST separate, you can create an ID column for both, and replace by.x and by.y with that ID variable.

To replace NAs with 0s, you can use df$TestA[is.na(TestA)]<-0. Same for Test if you want to keep both Test and TEST.

Answer 2

With data.table, you could

1. pre-fill 0s in b and then
2. update-join values in from a

like...

# input
a <- data.frame(Test=1:4, TestA=5:6)
b <- data.frame(TEST=1:10, TestB=11:20)
library(data.table)
setDT(a); setDT(b)

# prefill
b[, c("Test", "TestA") := 0L]

# update join
b[a, on=.(TEST = Test), c("Test", "TestA") := .(i.Test, i.TestA)]

    TEST TestB Test TestA
 1:    1    11    1     5
 2:    2    12    2     6
 3:    3    13    3     5
 4:    4    14    4     6
 5:    5    15    0     0
 6:    6    16    0     0
 7:    7    17    0     0
 8:    8    18    0     0
 9:    9    19    0     0
10:   10    20    0     0

This modifies b instead of creating a new table. This works for your example, but if you need a "full join" (where b doesn't have the full set of rows you want in the final table), another answer will fit better.

On the other hand, if your table has real NAs that you don't want filled with zeros, this is a better approach than the first few answers (which overwrite all NAs, not just those resulting from a row being unmatched in the tables' join/merge/combination).

---

To generalize to more columns, define a list of default values...

# input
a <- data.frame(Test=1:4, TestA=5:6)
b <- data.frame(TEST=1:10, TestB=11:20)

library(data.table)
setDT(a); setDT(b)    
defaults = list(Test = 0L, TestA = 0L)
new_cols = names(defaults)

# prefill defaults
b[, (new_cols) := defaults]

# update join
b[a, on=.(TEST = Test), (new_cols) := mget(sprintf("i.%s", new_cols))]