Hi R users, I have a dataframe, with year, month, day, and other variables. I wanted to calculated monthly values of the variables. For example, there is one variable called 'count'. I use the code below to convert daily data to monthly data. df.count.mon = aggregate(count ~ year+month, data= df, sum) The new dataframe has three columns: year, month, and count. Now I want to add one more column as 'time', which has the format 'yyyy-mm'. I use the code below but the new column has all NA values. What is the problem and how to solve it? df.count.mon$time = as.Date(paste(df.count.mon$year, df.count.mon$month), '%Y %m') I had experience to add one more column with the format 'yyyy-mm-dd', which works, but not with monthly format. Thanks for your help. [[alternative HTML version deleted]]
Hi Lily. Two problems. You have named the month field "mon" and then refer to it as "month". Second, as the resolution of as.Date is days, it can't produce a valid date without specifying the day. Thus: df.count.mon<-data.frame(count=sample(1:24,24), year=rep(2014:2015,each=2),mon=rep(1:12,2)) # make each day the first day of the month df.count.mon$time<- as.Date(paste(df.count.mon$year, df.count.mon$mon,1), '%Y %m %d') df.count.mon count year mon time 1 22 2014 1 2014-01-01 2 12 2014 2 2014-02-01 ... You will get values, but I don't think they are the ones you want. Jim On Thu, Feb 23, 2017 at 6:33 AM, lily li <chocold12 at gmail.com> wrote:> Hi R users, > > I have a dataframe, with year, month, day, and other variables. I wanted to > calculated monthly values of the variables. For example, there is one > variable called 'count'. I use the code below to convert daily data to > monthly data. > > df.count.mon = aggregate(count ~ year+month, data= df, sum) > > The new dataframe has three columns: year, month, and count. Now I want to > add one more column as 'time', which has the format 'yyyy-mm'. I use the > code below but the new column has all NA values. What is the problem and > how to solve it? > > df.count.mon$time = as.Date(paste(df.count.mon$year, df.count.mon$month), > '%Y %m') > > I had experience to add one more column with the format 'yyyy-mm-dd', which > works, but not with monthly format. Thanks for your help. > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > 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.
Yes, it is a little different. Is there a way to get 'yyyy-mm' format? Thanks. On Wed, Feb 22, 2017 at 2:10 PM, Jim Lemon <drjimlemon at gmail.com> wrote:> Hi Lily. > Two problems. You have named the month field "mon" and then refer to > it as "month". Second, as the resolution of as.Date is days, it can't > produce a valid date without specifying the day. Thus: > > df.count.mon<-data.frame(count=sample(1:24,24), > year=rep(2014:2015,each=2),mon=rep(1:12,2)) > # make each day the first day of the month > df.count.mon$time<- > as.Date(paste(df.count.mon$year, df.count.mon$mon,1), > '%Y %m %d') > df.count.mon > count year mon time > 1 22 2014 1 2014-01-01 > 2 12 2014 2 2014-02-01 > ... > You will get values, but I don't think they are the ones you want. > > Jim > > On Thu, Feb 23, 2017 at 6:33 AM, lily li <chocold12 at gmail.com> wrote: > > Hi R users, > > > > I have a dataframe, with year, month, day, and other variables. I wanted > to > > calculated monthly values of the variables. For example, there is one > > variable called 'count'. I use the code below to convert daily data to > > monthly data. > > > > df.count.mon = aggregate(count ~ year+month, data= df, sum) > > > > The new dataframe has three columns: year, month, and count. Now I want > to > > add one more column as 'time', which has the format 'yyyy-mm'. I use the > > code below but the new column has all NA values. What is the problem and > > how to solve it? > > > > df.count.mon$time = as.Date(paste(df.count.mon$year, > df.count.mon$month), > > '%Y %m') > > > > I had experience to add one more column with the format 'yyyy-mm-dd', > which > > works, but not with monthly format. Thanks for your help. > > > > [[alternative HTML version deleted]] > > > > ______________________________________________ > > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > > 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. >[[alternative HTML version deleted]]