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?
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)
)