rsum

How to find equal lines in different tables?


I am working in R using two different tables with three columns, e.g.:

Initial            Final        Changes
    1              1              200
    1              3              500
    3              1              250
    3              3              175

Table 2
   Initial         Final        Changes
       1              3           180
       1              5           265
       3              3           147
       3              7           155

I need to sum the last column (Changes) of table 1 plus the last column (Changes) of table 2 when the lines of column “Initial” and “Final” of table 1 are equal to each line in table 2, generating a table like:

Initial            Final        Changes
       1                 1         200
       1                 3         680
       1                 5         265
       3                 1         250
       3                 3         322
       3                 7         155

I am trying this way, but I'm getting an error:

for (row in 1:nrow(t1) {
  t2[t2$Initial == row$Initial && t2$Final == row$Final,t2$Changes] <- row$Changes + t2[t2$Initial == row$Initial && t2$Final == row$Final,t2$Changes]
}

Can anyone help me, please?


Solution

  • You can join both tables and create a new column on the joined table to take the sums:

    joined = merge(t1,t2,by = c("Initial","Final"),all=T)
    joined["Changes.final"] <- apply(joined[,c("Changes.x","Changes.y")],
                                     FUN = function(x)sum(x,na.rm=T), MARGIN = 1)
    

    Data:

    t1 = data.frame(
      "Initial" = c(1,1,3,3),
      "Final" = c(1,3,1,3),
      "Changes" = c(200,500,250,175)
    )
    
    t2 = data.frame(
      "Initial" = c(1,1,3,3),
      "Final" = c(3,5,3,7),
      "Changes" = c(180,265,147,155)
    )