CODEWITHSUNDEEP
rmatching
JRP
JRP

Reputation: 135

How do I add column values based on matching IDs in R?

I have two data frames:

A:

ID    Var1    Var2    Var3
1       0       3       4
2       1       5       0
3       1       6       7

B:

ID    Var1    Var2    Var3    
1       2       4       2
2       2       1       1
3       0       2       1
4       1       0       3

I want to add the columns from A and B based on matching ID's to get data frame C, and keep row 4 from B (even though it does not have a matching ID from A):

ID    Var1    Var2    Var3
1       2       7       6
2       3       6       1
3       1       8       8
4       1       0       3

Upvotes: 0

Views: 1368

Answers (1)

thelatemail
thelatemail

Reputation: 93813

rbind and aggregate by ID:

aggregate(. ~ ID, data=rbind(A,B), sum)

#  ID Var1 Var2 Var3
#1  1    2    7    6
#2  2    3    6    1
#3  3    1    8    8
#4  4    1    0    3

In data.table you can similarly do:

library(data.table)
setDT(rbind(A,B))[, lapply(.SD, sum), by=ID]

And there would be analogous solutions in dplyr and sql or whatever else. Bind the rows, group by ID, sum.

Upvotes: 2

Related Questions