[BioC] Merge dataframes
D. Schruth
dschruth at u.washington.edu
Sat Oct 8 18:29:14 CEST 2011
Hi Joao,
A simple one line solution using two functions from the 'caroline' CRAN
package could suffice as well:
nerge(list(data=data1, col=nv(color, rownames(data2))), all.x=T)
nv : named vector
nerge : named merge
Dave
On Sat, 8 Oct 2011, "João Daniel N. Duarte" wrote:
> Hi everybody,
>
> Paniagua explanation and solution worked very good! Thank you a lot!
> I got the code to what I need.
>
> As Paniagua said, I was losing row.names when I merged a data.frame with a
> factor object. Instead of merging data1 to data2$color, I merged it to
> data2["color"]. It preserves row.names.
>
> When I do:
>
> d1 <- merge(data1, data2["color"], by=0, all.x=T)
>
> It creates a new variable on column 1, with the original row.names, so I have
> to transform this data.frame in a new one, using this variable as the
> row.names:
>
> d2 <- data.frame(d1, row.names=1)
>
> It does the job, but row.names are not in the same order as in data1, so I
> reorder it:
>
> d2[row.names(data1),]
>
> And it gives me the dataframe data1, binded to the new variable color, with
> the NAs in the proper position.
>
> Thank you all!
>
> Cheers,
>
> Joao D.
>
> Em 07/10/2011, às 20:36, Paniagua, Eric escreveu:
>
>> Hi Joao,
>>
>> To save space, I'm not going to paste the data frames in. Just start a
>> session, run your original code, and then try out the commands below.
>>
>> The problem is a simple type confusion. If you check the class of
>> data2$color, you'll see it's not a data frame:
>>
>>> class(data2$color)
>> [1] "factor"
>>
>> The problem is that a factor is not a data frame, but merge expects data
>> frames as arguments. If either one is not a data frame, merge will coerce
>> the type if possible. In this case, that means that passing data2$color to
>> merge is equivalent to this:
>>
>>> merge(data1, as.data.frame(data2$color), by=0, all.x=T)
>>
>> If you inspect the coerced version of the argument, you'll see that new row
>> names have been assigned during coercion. Well, technically not "new" ones
>> because a factor does not have row names. In other words, the row names
>> were lost when you used the '$'. Just run the following to see:
>>
>>> data2$color
>>> as.data.frame(data2$color)
>>
>> The solution is to just subset the data frame instead of pulling out the
>> factor. If you replace data2$color in your code with data2["color"], your
>> code behaves as intended.
>>
>> A good first clue in discovering this issue is the fact that in your
>> original code, you end up in the <NA> elements all at the bottom, rows
>> 16-20, every time. The odds of this specific arrangement happening any one
>> particular time are 1 in 15504, or about 0.00645%. Coincidentally, the 15
>> non <NA> values are occupying rows 1-15, making the hypothesis that your
>> rows are being relabeled somewhere along the way much more probable. And,
>> in this case, correct.
>>
>> Underlying that insight or intuition is the much more general debugging
>> approach of actually checking and looking at every intermediate value in
>> your computation that you can. And, of course, reading the documentation
>> :)
>>
>> For instance, check out the class and value of data2["color"]:
>>
>>> class(data2["color"])
>> [1] "data.frame"
>>
>> You can also see that data2["color"] preserves the row names:
>>
>>> data2["color"]
>>
>> You can try this to verify that with my solution the values work out
>> correctly (though I'm sure there's a better way to do it directly in R):
>>
>>> data3 <- merge(data1, data2["color"], by=0, all.x=T)
>>> data3
>>> data4 <- data.frame(data3, row.names=1)
>>> data4
>>> data4[row.names(data1),]
>>> data5 <- merge(data1, data4, by=0)
>>> data5
>>> data6 <- data.frame(data5, row.names=1)
>>> data6
>>> data6[row.names(data1),]
>>
>> (Of course, I'm using way more variables there than are required for the
>> computation. Consider their addition as purely didactic.)
>>
>> Anyway, I hope that helps! Happy scripting.
>>
>> Best,
>> Eric
>>
>> ________________________________________
>> From: bioconductor-bounces at r-project.org
>> [bioconductor-bounces at r-project.org] on behalf of João Daniel Nunes Duarte
>> [jdanielnd at gmail.com]
>> Sent: Friday, October 07, 2011 6:32 PM
>> Subject: [BioC] Merge dataframes
>>
>> Hello,
>>
>> I am having some problems to use the 'merge' function. I'm not sure if I
>> got
>> its working right.
>>
>> What I want to do is:
>>
>> 1) Suppose I have a dataframe like:
>>
>> height width
>> 1 1.1 2.3
>> 2 2.1 2.5
>> 3 1.8 1.9
>> 4 1.6 2.1
>> 5 1.8 2.4
>>
>> 2) And I generate a second dataframe sampled from this one, like:
>>
>> height width
>> 1 1.1 2.3
>> 3 1.8 1.9
>> 5 1.8 2.4
>>
>> 3) Next, I add a new variable from this dataframe:
>>
>> height width color
>> 1 1.1 2.3 red
>> 3 1.8 1.9 red
>> 5 1.8 2.4 blue
>>
>> 4) So, I want to merge those dataframes, so that the new variable, color,
>> is
>> binded to the first dataframe. Of course some cases won't have value for
>> it,
>> since I generated this variable in a smaller dataframe. In those cases I
>> want the value to be NA. The result dataframe should be:
>>
>> height width color
>> 1 1.1 2.3 red
>> 2 2.1 2.5 NA
>> 3 1.8 1.9 red
>> 4 1.6 2.1 NA
>> 5 1.8 2.4 blue
>>
>> I have written some codes, but they're not working properly. The new
>> variable has its values mixed up, and they do not correspond to its
>> row.names.
>>
>> # Generate the first dataframe
>> data1 <- data.frame(height=rnorm(20,3,0.2),width=rnorm(20,2,0.5))
>> # Sample a smaller dataframe from data1
>> data2 <- data1[sample(1:20,15,replace=F),]
>> # Generate the new variable
>> color <- sample(c("red","blue"),15,replace=T)
>> # Bind the new variable to data2
>> data2 <- cbind(data2, color)
>> # Merge the data1 and data2$color by row.names, and force it to has the
>> same
>> values that data1. Next it generates a new dataframe where column 1 is the
>> row.name, and then sort it by the row.name from data1.
>> data.frame(merge(data1,data2$color, by=0,
>> all.x=T),row.names=1)[row.names(data1),]
>>
>> I'm not sure what am I doing wrong. Can anyone see where the mistake is?
>>
>> Thank you!
>>
>> Cheers,
>>
>> Joao D.
>>
>> [[alternative HTML version deleted]]
>>
>> _______________________________________________
>> Bioconductor mailing list
>> Bioconductor at r-project.org
>> https://stat.ethz.ch/mailman/listinfo/bioconductor
>> Search the archives:
>> http://news.gmane.org/gmane.science.biology.informatics.conductor
>
> _______________________________________________
> Bioconductor mailing list
> Bioconductor at r-project.org
> https://stat.ethz.ch/mailman/listinfo/bioconductor
> Search the archives:
> http://news.gmane.org/gmane.science.biology.informatics.conductor
More information about the Bioconductor
mailing list