r - How to sum and combine two data frames?

Question

I have two data frames:

DATA1:

ID    com_alc_cd   com_liv_cd   com_hyee_cd
A         1            0             0
B         0            0             1
D         0            0             0
C         0            1             0

DATA2:

ID     com_alc_dd   com_liv_dd   com_hyee_dd
B          0            2             0
A          1            0             2
C          0            1             0
D          0            1             0

I want to combine the two data frames, so as to obtain the sum of the two:

SUM(DATA1, DATA2):

    ID     com_alc   com_liv   com_hyee
    A         2          0         2
    B         0          2         1
    C         0          2         0
    D         0          1         0

Answer 1

Try this for example( assuming that your data.frames are matrix of the same size)

d1 <- DATA1[order(DATA1$ID),]  
d2 <- DATA2[order(DATA2$ID),]
data.frame(ID=d1$ID,as.matrix(subset(d1,select=-ID)) + 
                    as.matrix(subset(d2,select=-ID)))


 ID com_alc_cd com_liv_cd com_hyee_cd
1  A          2          0           2
2  B          0          2           1
4  C          0          2           0
3  D          0          1           0

EDIT general solution

library(reshape2)
## put the data in the long format
res <- do.call(rbind,lapply(list(DATA1,DATA2),melt,id.vars='ID'))
## polish names
res$variable <- gsub('(.*_.*)_.*','\1',res$variable)
## wide format and aggregate using sum
dcast(ID~variable,data=res,fun.aggregate=sum)

 ID com_alc com_hyee com_liv
1  A       2        2       0
2  B       0        1       2
3  C       0        0       2
4  D       0        0       1