Merging columns from different data frames

Question

I have a problem....

I have two data frames

>anna1
     name   from       to        result
     11     66607     66841       0
     11     66846     67048       0
     11     67053     67404       0
     11     67409     68216       0
     11     68221     68786       0
     11     68791     69020       0
     11     69025     69289       0
     11     69294     70167       0
     11     70172     70560       0

and the second data frame is

>anna2
     name   from      to       result
     11     66607     66841       5
     11     66846     67048       6 
     11     67409     68216       7
     11     69025     69289       12
     11     70172     70560       45

What I want is to create a new data frame similar with the anna1 where all the 0 values will be replaced by the correct results in the correct row from the anna2

you are going to notice that in the anna2 data frame, in the from and to columns have only some same values with the respective in the anna1 data frame ....the intermediate are missing

So i need somehow to take the numbers from the result column in the anna2 and put them in the correct row in the anna1

thank you in advance

Best regards Anna

Solution 1

If the "from" column is guaranteed to be unique in both anna1 and anna2, AND every row in anna2 has a matching row in anna1 (though not vice versa), a simple solution is

row.index = function(d) which(anna1$from == d)[1]
indices = sapply(anna2$from, row.index)
anna1$result[indices] = anna2$result

Solution 2

A simpler merge:

anna3 <-merge(anna2,anna1[,1:3], all.y=TRUE)
anna3[is.na(anna3)] <- 0

Gives:

> anna3
  name  from    to result
1   11 66607 66841      5
2   11 66846 67048      6
3   11 67053 67404      0
4   11 67409 68216      7
5   11 68221 68786      0
6   11 68791 69020      0
7   11 69025 69289     12
8   11 69294 70167      0
9   11 70172 70560     45

Solution 3

Another approach

require(plyr)
anna <- rbind(anna1, anna2)
ddply(anna, .(name, from, to), summarize, result = sum(result))

EDIT. If the data frames are large, and speed is an issue, think of using data.table

require(data.table)
data.table(anna)[,list(result = sum(result)),'name, from, to']

Comments

[Anna]

In the anna2 data frame the values in the columns from and to are the same with the valuse in anna2. The problem is that in the anna2 the values are a subset of anna1 so.....I just need to match and replace the 0 with the values from the results of anna2 in the results in anna1 in the correct row

[David Robinson]

Do you mean "same with the values in anna1"? And I think you might misunderstand me. But "unique", I mean that you never have a case where the same value appears twice within anna1.

[David Robinson]

Did you try my solution? From what you're saying, I think it should work. If it doesn't work, be specific as to how it doesn't.

[user3253470]

@DavidRobinson Can you please help me with this question: [stackoverflow.com/questions/35484595/…

[user3253470]

Can you help me with this question: [stackoverflow.com/questions/35484595/…