Mark Dalphin
2003-Oct-22 19:05 UTC
[R] How to reformat data from database into data.frame?
I'm trying to find a clever way to re-map data from a database query into a data.frame. Querying a database often returns a table (data.frame) like this: GeneID MethodID Value 6 1 123 6 2 456 6 3 987 7 1 234 7 3 432 8 2 190 8 3 34 8 1 864 Note that GeneID=7 doesn't have a value for MethodID=2. Note that GeneID=8 doesn't have the MethodID in any particular order and, in fact, there doesn't need to be an order to the GeneID, although I can force ordering as part of my SQL query. I want to reformat this into a data.frame with multiple columns: Value Value Value GeneID Method1 Method2 Method3 6 123 456 987 7 234 NA 432 8 864 190 34 Is there an elegant way to do this type of reformating in R? Thanks. Mark -- Mark Dalphin email: mdalphin at amgen.com Mail Stop: 29-2-A phone: +1-805-447-4951 (work) One Amgen Center Drive +1-805-375-0680 (home) Thousand Oaks, CA 91320 fax: +1-805-499-9955 (work)
Gabor Grothendieck
2003-Oct-22 19:59 UTC
[R] How to reformat data from database into data.frame?
Assuming your input data frame is genes: reshape(genes,idvar="GeneID",timevar="MethodID",direction="wide") --- On Wed 10/22, Mark Dalphin < mdalphin at amgen.com > wrote: From: Mark Dalphin [mailto: mdalphin at amgen.com] To: r-help at stat.math.ethz.ch Date: Wed, 22 Oct 2003 12:05:44 -0700 Subject: [R] How to reformat data from database into data.frame? I'm trying to find a clever way to re-map data from a database<br>query into a data.frame.<br><br>Querying a database often returns a table (data.frame) like this:<br><br>GeneID MethodID Value<br>6 1 123<br>6 2 456<br>6 3 987<br>7 1 234<br>7 3 432<br>8 2 190<br>8 3 34<br>8 1 864 <br><br>Note that GeneID=7 doesn't have a value for MethodID=2.<br><br>Note that GeneID=8 doesn't have the MethodID in any particular order<br>and, in fact, there doesn't need to be an order to the GeneID, although<br>I can force ordering as part of my SQL query.<br><br>I want to reformat this into a data.frame with multiple columns:<br><br> Value Value Value<br>GeneID Method1 Method2 Method3<br>6 123 456 987<br>7 234 NA 432<br>8 864 190 34<br><br>Is there an elegant way to do this type of reformating in R?<br><br>Thanks.<br>Mark<br><br>-- <br>Mark Dalphin email: mdalphin at amgen.com<br>Mail Stop: 29-2-A phone: +1-805-447-4951 (work)<br>One Amgen Center Drive +1-805-375-0680 (home)<br>Thousand Oaks, CA 91320 fax: +1-805-499-9955 (work)<br><br>______________________________________________<br>R-help at stat.math.ethz.ch mailing list<br>https://www.stat.math.ethz.ch/mailman/listinfo/r-help<br> _______________________________________________ No banners. No pop-ups. No kidding. Introducing My Way - http://www.myway.com
Apparently Analagous Threads
- Confidence intervals on Lowess (eg SiZer from J.S.Marron)
- Core dump using DBI/ROracle
- Fresh build from source of R-3.2.5 failing "make check" under 64-bit Ubuntu
- Regression test failed when building on an "older" Linux cluster
- Not able to write to PostgreSQL database using "dbWriteTable"