Dear all, Question: How to merge two data frames such that new column are added in a particular way? I'm not actually sure how to best articulate my question to be honest, so i hope showing you what I want to achieve will communicate my question better. Lets say I have two data frames:> DF1 <- data.frame(cbind(Show=c('Firefly', 'Red Dwarf'), Measure=1:2, Datetime=c('08/26/2009 9:30 AM', '08/26/2009 9:30 AM')))> DF2 <- data.frame(cbind(Show=c('Firefly', 'Red Dwarf'), Measure=3:4, Datetime=c('08/26/2009 11:30 AM', '08/26/2009 11:30 AM')))And then let us merge these:> DF3 <- merge(DF1, DF2, all=TRUE)Show Measure Datetime 1 Firefly 1 08/26/2009 9:30 AM 2 Firefly 3 08/26/2009 11:30 AM 3 Red Dwarf 2 08/26/2009 9:30 AM 4 Red Dwarf 4 08/26/2009 11:30 AM What i would like to do is merge the data frames such that i end up with the following: Show 08/26/2009 9:30 AM 08/26/2009 11:30 AM Firefly 1 3 Red Dwarf 2 4 my reason for doing this is so that i can plot a time series somehow. I hope the formating stays when i post this message and that what i'm trying to do is easy to understand. Thank you kindly for any help in advance. Tony
Try this: xtabs(as.numeric(Measure) ~ Show + Datetime, data = DF3) On Thu, Aug 27, 2009 at 8:04 AM, Tony Breyal <tony.breyal@googlemail.com>wrote:> Dear all, > > Question: How to merge two data frames such that new column are added > in a particular way? > > I'm not actually sure how to best articulate my question to be honest, > so i hope showing you what I want to achieve will communicate my > question better. > > Lets say I have two data frames: > > > DF1 <- data.frame(cbind(Show=c('Firefly', 'Red Dwarf'), Measure=1:2, > Datetime=c('08/26/2009 9:30 AM', '08/26/2009 9:30 AM'))) > > > DF2 <- data.frame(cbind(Show=c('Firefly', 'Red Dwarf'), Measure=3:4, > Datetime=c('08/26/2009 11:30 AM', '08/26/2009 11:30 AM'))) > > And then let us merge these: > > > DF3 <- merge(DF1, DF2, all=TRUE) > > Show Measure Datetime > 1 Firefly 1 08/26/2009 9:30 AM > 2 Firefly 3 08/26/2009 11:30 AM > 3 Red Dwarf 2 08/26/2009 9:30 AM > 4 Red Dwarf 4 08/26/2009 11:30 AM > > > What i would like to do is merge the data frames such that i end up > with the following: > > Show 08/26/2009 9:30 AM 08/26/2009 11:30 AM > Firefly 1 3 > Red Dwarf 2 4 > > my reason for doing this is so that i can plot a time series somehow. > > I hope the formating stays when i post this message and that what i'm > trying to do is easy to understand. Thank you kindly for any help in > advance. > > Tony > > ______________________________________________ > R-help@r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide > http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. >-- Henrique Dallazuanna Curitiba-Paraná-Brasil 25° 25' 40" S 49° 16' 22" O [[alternative HTML version deleted]]
You may want to use the reshape package for this task:> library(reshape) > recast(DF3,Show ~ Datetime, id.var=names(DF3),value="Measure")Show 08/26/2009 11:30 AM 08/26/2009 9:30 AM 1 Firefly 3 1 2 Red Dwarf 4 2 If you want to plot time series, you can do something like the following> mydf <- .Last.value ## save the output from above to mydf > library(zoo) > zobj <- zoo(`mode<-`(t(mydf),"numeric"), > as.chron(strptime(names(mydf)[-1],"%m/%d/%Y %I:%M %p"))) > plot(zobj)(zobj is a time series object of the zoo class) ----- Original Message ---- From: Tony Breyal <tony.breyal at googlemail.com> To: r-help at r-project.org Sent: Thursday, August 27, 2009 4:04:30 AM Subject: [R] Merge data frames but with a twist. Dear all, Question: How to merge two data frames such that new column are added in a particular way? I'm not actually sure how to best articulate my question to be honest, so i hope showing you what I want to achieve will communicate my question better. Lets say I have two data frames:> DF1 <- data.frame(cbind(Show=c('Firefly', 'Red Dwarf'), Measure=1:2, Datetime=c('08/26/2009 9:30 AM', '08/26/2009 9:30 AM')))> DF2 <- data.frame(cbind(Show=c('Firefly', 'Red Dwarf'), Measure=3:4, Datetime=c('08/26/2009 11:30 AM', '08/26/2009 11:30 AM')))And then let us merge these:> DF3 <- merge(DF1, DF2, all=TRUE)Show Measure Datetime 1 Firefly 1 08/26/2009 9:30 AM 2 Firefly 3 08/26/2009 11:30 AM 3 Red Dwarf 2 08/26/2009 9:30 AM 4 Red Dwarf 4 08/26/2009 11:30 AM What i would like to do is merge the data frames such that i end up with the following: Show 08/26/2009 9:30 AM 08/26/2009 11:30 AM Firefly 1 3 Red Dwarf 2 4 my reason for doing this is so that i can plot a time series somehow. I hope the formating stays when i post this message and that what i'm trying to do is easy to understand. Thank you kindly for any help in advance. Tony ______________________________________________ R-help at r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
On Thu, Aug 27, 2009 at 9:55 AM, Stephen Tucker<brown_emu at yahoo.com> wrote:> You may want to use the reshape package for this task: > >> library(reshape) >> recast(DF3,Show ~ Datetime, id.var=names(DF3),value="Measure") > ? ? ? Show 08/26/2009 11:30 AM 08/26/2009 9:30 AM > 1 ? Firefly ? ? ? ? ? ? ? ? ? 3 ? ? ? ? ? ? ? ? ?1 > 2 Red Dwarf ? ? ? ? ? ? ? ? ? 4 ? ? ? ? ? ? ? ? ?2 > > If you want to plot time series, you can do something like the following > >> mydf <- .Last.value ## save the output from above to mydf >> library(zoo) >> zobj <- zoo(`mode<-`(t(mydf),"numeric"), >> ? ? ? ? ? ? as.chron(strptime(names(mydf)[-1],"%m/%d/%Y %I:%M %p"))) >> plot(zobj) > > (zobj is a time series object of the zoo class)Note that as.chron can take % codes directly so the as.chron portion can be shortened to: as.chron(names(mydf)[-1],"%m/%d/%Y %I:%M %p")