CODEWITHSUNDEEP
r
user4918087
user4918087

Reputation: 421

R: Adding values in 2 data frames with different dimensions

I have 2 dataframes:

df1: (3 rows, 3 columns)

Type     CA     AR     OR   Total
alpha    2      3      1      6
beta     1      5      2      8
delta    8      1      1      10

df2: (4 rows, 2 columns)

Type     CA     AR     Total
alpha    3      4        7
beta     2      6        8
gamma    9      1        10
delta    4      1        5

I want to add the values in the two dataframes. The result should be as below: I tried df1 + df2, but the problem I face is that the two dataframes are different dimensions / sizes. Is there any sort of function that will let me add the rows/columns that have the same row/column name (some sort of match function to match the names.) In the event that there are no occurences of the value (in this case --> gamma, OR, I want it to print 0/NA --> I don't care what actually.) And I need to be some sort of match and not hard-coded because I will be doing this for several tables. 

Type     CA     AR       OR   Total
alpha    5      7        1     13
beta     3      11       2     16
gamma    9      1        0     10
delta    12     2        1     15

Thanks!!

Upvotes: 2

Views: 1367

Answers (3)

Nick Fisch
Nick Fisch

Reputation: 24

Another way to do it is to install package plyr, this will allow you to use the rbind.fill function which does not require the same columns. The the aggregate function will sum your values. 

install.packages("plyr")
data<-rbind.fill(df1,df2)
data[is.na(data)]<-0
aggregate(data[c("CA","AR","OR","Total")],by=data["Type"], FUN = sum) 


   Type CA AR OR Total
1 alpha  5  7  1    13
2  beta  3 11  2    16
3 delta 12  2  1    15
4 gamma  9  1  0    10

Upvotes: 0

mpalanco
mpalanco

Reputation: 13570

Using sqldf:

library(sqldf)
sqldf('SELECT Type, sum(CA) CA, sum(AR) AR, sum ([OR]) [OR], sum(Total) Total 
      FROM (SELECT Type, CA, AR, [OR], Total FROM df1
      UNION ALL
      SELECT Type, CA, AR, Null as [OR], Total FROM df2) 
      GROUP by Type')

Output:

   Type CA AR OR Total
1 alpha  5  7  1    13
2  beta  3 11  2    16
3 delta 12  2  1    15
4 gamma  9  1 NA    10

Upvotes: 1

MrFlick
MrFlick

Reputation: 206232

I personally would use a melt/cast reshaping strategy

library(reshape2)
melted <- rbind(melt(df1), melt(df2))
dcast(melted, Type~variable, sum)

#    Type CA AR OR Total
# 1 alpha  5  7  1    13
# 2  beta  3 11  2    16
# 3 delta 12  2  1    15
# 4 gamma  9  1  0    10

Upvotes: 3

Related Questions