Dear R users: I have a data set that looks something like this: ID time y 1 2/01/2008 4 1 2/09/2008 12 1 9/01/2008 8 2 1/06/2008 3 2 3/01/2008 4 2 3/09/2008 9 2 6/03/2008 4 3 1/02/2008 3 3 1/10/2008 8 3 2/02/2008 7 3 2/10/2008 3 I'd like to sum up the weekly data to the monthly level, so that it looks something like this: ID time y 1 2/2008 16 1 9/2008 8 2 1/2008 3 2 3/2008 13 2 6/2008 4 3 1/2008 11 3 2/2008 10 What is the best way to do it? Time must be character. How do I truncate a character so that I can remove the date and only keep the month and year? Thank you very much in advance. Julia -- View this message in context: http://www.nabble.com/roll-weekly-data-to-monthly-level-tp21424700p21424700.html Sent from the R help mailing list archive at Nabble.com.
Augusto.Sanabria at ga.gov.au
2009-Jan-12 23:18 UTC
[R] roll weekly data to monthly level [SEC=UNCLASSIFIED]
Julia, I had a similar query a while ago which I solved using a suggestion from Gabor, have a look at: http://finzi.psych.upenn.edu/R/Rhelp02a/archive/69597.html Hope it helps, Augusto -------------------------------------------- Augusto Sanabria. MSc, PhD. Mathematical Modeller Risk & Impact Analysis Group Geospatial & Earth Monitoring Division Geoscience Australia (www.ga.gov.au) Cnr. Jerrabomberra Av. & Hindmarsh Dr. Symonston ACT 2601 Ph. (02) 6249-9155 -----Original Message----- From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org] On Behalf Of liujb Sent: Tuesday, 13 January 2009 8:58 To: r-help at r-project.org Subject: [R] roll weekly data to monthly level Dear R users: I have a data set that looks something like this: ID time y 1 2/01/2008 4 1 2/09/2008 12 1 9/01/2008 8 2 1/06/2008 3 2 3/01/2008 4 2 3/09/2008 9 2 6/03/2008 4 3 1/02/2008 3 3 1/10/2008 8 3 2/02/2008 7 3 2/10/2008 3 I'd like to sum up the weekly data to the monthly level, so that it looks something like this: ID time y 1 2/2008 16 1 9/2008 8 2 1/2008 3 2 3/2008 13 2 6/2008 4 3 1/2008 11 3 2/2008 10 What is the best way to do it? Time must be character. How do I truncate a character so that I can remove the date and only keep the month and year? Thank you very much in advance. Julia -- View this message in context: http://www.nabble.com/roll-weekly-data-to-monthly-level-tp21424700p21424700.html Sent from the R help mailing list archive at Nabble.com. ______________________________________________ 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.
I had trouble getting my output to look like yours until I realized that you did not want "to sum up the weekly data to the monthly level" but rather to sum up to the monthly *and* ID level. > dftag<-aggregate(dft$y, list(ID=dft$ID, Month=as.yearmon(dft$time, "%m/%d/%Y")), FUN=sum) # in Month order rather than ID, Month order > dftag[order(dftag$ID, dftag$Month), ] ID Month x 3 1 Feb 2008 16 7 1 Sep 2008 8 1 2 Jan 2008 3 5 2 Mar 2008 13 6 2 Jun 2008 4 2 3 Jan 2008 11 4 3 Feb 2008 10 -- David Winsemius On Jan 12, 2009, at 4:57 PM, liujb wrote:> > Dear R users: > > I have a data set that looks something like this: > ID time y > 1 2/01/2008 4 > 1 2/09/2008 12 > 1 9/01/2008 8 > 2 1/06/2008 3 > 2 3/01/2008 4 > 2 3/09/2008 9 > 2 6/03/2008 4 > 3 1/02/2008 3 > 3 1/10/2008 8 > 3 2/02/2008 7 > 3 2/10/2008 3 > > I'd like to sum up the weekly data to the monthly level, so that it > looks > something like this: > > ID time y > 1 2/2008 16 > 1 9/2008 8 > 2 1/2008 3 > 2 3/2008 13 > 2 6/2008 4 > 3 1/2008 11 > 3 2/2008 10 > > > What is the best way to do it? > > Time must be character. How do I truncate a character so that I can > remove > the date and only keep the month and year? > > Thank you very much in advance. > Julia > -- > View this message in context: http://www.nabble.com/roll-weekly-data-to-monthly-level-tp21424700p21424700.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > 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.
Try this. First we read in the data. In reality you would use the commented out read line. Then use chron to convert the times to chron dates and as.yearmon from zoo to convert them to year/months. Finally aggregate and sort.> Lines <- "ID time y+ 1 2/01/2008 4 + 1 2/09/2008 12 + 1 9/01/2008 8 + 2 1/06/2008 3 + 2 3/01/2008 4 + 2 3/09/2008 9 + 2 6/03/2008 4 + 3 1/02/2008 3 + 3 1/10/2008 8 + 3 2/02/2008 7 + 3 2/10/2008 3"> > library(zoo) # as.yearmon > library(chron) # chron > > # DF <- read.table("myfile.dat", header = TRUE, as.is = TRUE) > DF <- read.table(textConnection(Lines), header = TRUE, as.is = TRUE) > DF$yearmon <- as.yearmon(chron(DF$time)) > res <- aggregate(DF["y"], DF[c("ID", "yearmon")], sum) > res[order(res[, 1], res[, 2]), ]ID yearmon y 3 1 Feb 2008 16 7 1 Sep 2008 8 1 2 Jan 2008 3 5 2 Mar 2008 13 6 2 Jun 2008 4 2 3 Jan 2008 11 4 3 Feb 2008 10>On Mon, Jan 12, 2009 at 4:57 PM, liujb <liujulia7 at yahoo.com> wrote:> > Dear R users: > > I have a data set that looks something like this: > ID time y > 1 2/01/2008 4 > 1 2/09/2008 12 > 1 9/01/2008 8 > 2 1/06/2008 3 > 2 3/01/2008 4 > 2 3/09/2008 9 > 2 6/03/2008 4 > 3 1/02/2008 3 > 3 1/10/2008 8 > 3 2/02/2008 7 > 3 2/10/2008 3 > > I'd like to sum up the weekly data to the monthly level, so that it looks > something like this: > > ID time y > 1 2/2008 16 > 1 9/2008 8 > 2 1/2008 3 > 2 3/2008 13 > 2 6/2008 4 > 3 1/2008 11 > 3 2/2008 10 > > > What is the best way to do it? > > Time must be character. How do I truncate a character so that I can remove > the date and only keep the month and year? > > Thank you very much in advance. > Julia > -- > View this message in context: http://www.nabble.com/roll-weekly-data-to-monthly-level-tp21424700p21424700.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > 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. >