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

Pierre Lapointe · Answer 1 · 2012-01-06T14:49:22.863

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

edited Jan 06 '12 at 14:49

answered Jan 06 '12 at 14:34

Pierre Lapointe

16,017
2
43
56

score 1 · Accepted Answer · answered Jan 06 '12 at 13:54

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

answered Jan 06 '12 at 13:54

David Robinson

77,383
16
167
187

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 – Anna Jan 06 '12 at 14:22
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 Jan 06 '12 at 14:24
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. – David Robinson Jan 06 '12 at 14:25
@DavidRobinson Can you please help me with this question: [http://stackoverflow.com/questions/35484595/data-frame-merge-and-selection-of-values-which-are-common-in-2-data-frames] – user3253470 Feb 18 '16 at 16:10

score 1 · Answer 3 · answered Jan 06 '12 at 18:32

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']

score 0 · Answer 4 · answered Jan 06 '12 at 14:06

0

You can use merge, but you have to explicitly specify what should be done with the two result columns.

d <- merge(anna1, anna2, by=c("name",  "from", "to"), all=TRUE)
d$result <- ifelse(d$result.x == 0 & !is.na( d$result.y ), d$result.y, d$result.x)
d <- d[,c("name", "from", "to", "result")]

answered Jan 06 '12 at 14:06

Vincent Zoonekynd

31,893
5
69
78

Can you help me with this question: [http://stackoverflow.com/questions/35484595/data-frame-merge-and-selection-of-values-which-are-common-in-2-data-frames] – user3253470 Feb 18 '16 at 16:11

Merging columns from different data frames

4 Answers4

Linked