R adding a df with less rows to an existing data frame and show NA

Question

I have a data frame, say df1, that looks something like this:

    ID     
1   ABC
2   ABD
3   ABE
4   ABF
5   ABG
.
.

and I want to join a data frame to df1 that has less rows than df1, like this one:

    ID     Value1    Value 2
1   ABC      5         6
2   ABD      1         2
3   ABG      4         4
.
.

so that the final product looks like this:

    ID     Value1    Value 2
1   ABC      5         6
2   ABD      1         2
3   ABE      NA        NA
4   ABF      NA        NA 
5   ABG      4         4
.
.

How can I do about doing this? Thanks in advance.

Look into the `merge` function. Have you tried any code yet? — Tim Biegeleisen, Feb 15 '18 at 00:00
Possible duplicate of [Merge unequal dataframes and replace missing rows with 0](https://stackoverflow.com/questions/5965698/merge-unequal-dataframes-and-replace-missing-rows-with-0) — Kevin Arseneau, Feb 15 '18 at 00:58

score 1 · Accepted Answer · answered Feb 15 '18 at 00:03

1

Data

df1 = data.frame(ID =  c("ABC", "ABD", "ABE", "ABF", "ABG"))
df2 = data.frame(ID =  c("ABC", "ABD", "ABG"), Value1 = c(5, 4, 1), Value2 = c(6, 2, 4))

Solution

merge(df1, df2, all.x = TRUE)

#   ID Value1 Value2
#1 ABC      5      6
#2 ABD      4      2
#3 ABE     NA     NA
#4 ABF     NA     NA
#5 ABG      1      4

Explanation

Reading ?merge, we see that the logical argument all.x does exactly what you wanted:

all.x

logical; if TRUE, then extra rows will be added to the output, one for each row in x that has no matching row in y. These rows will have NAs in those columns that are usually filled with values from y. The default is FALSE, so that only rows with data from both x and y are included in the output.

answered Feb 15 '18 at 00:03

catastrophic-failure

3,759
1
24
43

Thanks! However, there's an error: cannot allocate vector of size 925.2 Mb, I'm assuming because my df is so large (10,000+ entries). Is there anything else I can do? – Alex Johanssen Feb 15 '18 at 00:17
@AlexJohanssen Your data is too big to be fit into memory, you have to sort that first. 10k entries isn't that big though. – catastrophic-failure Feb 15 '18 at 00:19
Any suggestions on how I can sort this out? It's still not completing its task after I run gc(). – Alex Johanssen Feb 15 '18 at 00:53
@AlexJohanssen I have no way to know why it's happening to you. I suggest you search this problem here on SO, and if no one asked it yet, ask it yourself. – catastrophic-failure Feb 15 '18 at 01:36
Good explanation – akrun Feb 15 '18 at 03:26

score 0 · Answer 2 · answered Feb 15 '18 at 00:04

0

Try merge(df1,df2,by = "ID", all = TRUE)

answered Feb 15 '18 at 00:04

Víctor Cortés

473
4
9

R adding a df with less rows to an existing data frame and show NA

2 Answers2

Data

Solution

Explanation

Linked