我有下表:
Date Country Class Value 6/1/2010 USA A 45 6/1/2010 Canada A 23 6/1/2010 Brazil B 65 9/1/2010 USA B 47 9/1/2010 Canada A 98 9/1/2010 Brazil B 25 12/1/2010 USA B 14 12/1/2010 Canada A 79 12/1/2010 Brazil A 23 3/1/2011 USA A 84 3/1/2011 Canada B 77 3/1/2011 Brazil A 43 6/1/2011 USA A 45 6/1/2011 Canada A 23 6/1/2011 Brazil B 65 9/1/2011 USA B 47 9/1/2011 Canada A 98 9/1/2011 Brazil B 25 12/1/2011 USA B 14 12/1/2011 Canada A 79 12/1/2011 Brazil A 23 3/1/2012 USA A 84 3/1/2012 Canada B 77 3/1/2012 Brazil A 43在日期栏中,年份除以下列月份 - 三月,六月,九月和十二月。我需要将6月至3月的月份作为会计年度,并在每个会计年度计算国家和类别列平均值的平均值。
In column "Date" years are divided by the following months - March, June, September and December. I need to group months from June to March as a Fiscal Year and by each Fiscal Year calculate the mean of column "Value" by "Country" and "Class". Could anybody help me to do that?
我尝试使用data.table,但得到错误:
I am trying to do that using data.table but get the error:
d=data[,list(Val=mean(Value,na.rm=T)),by=list(Country,Class, Period.grp=cut(Period,list(6/1/2010,3/1/2011,6/1/2011,3/1/2012, 6/1/2012,3/1/2013,6/1/2013,3/1/2014)))]/ 1/2010,3/1/2011,6/1/2011,3/1/2012,:'x'必须是数字
Error in cut.default(Period, list(6/1/2010, 3/1/2011, 6/1/2011, 3/1/2012, : 'x' must be numeric
谢谢
推荐答案我发现我认为我写的答案,但实际上有点不同
I found the answer I was thinking I had written, but it's actually a bit different
# this should "shift" the year calculation 3 months and provide quarter c('Q1','Q2','Q3','Q4')[ 1+((as.POSIXlt(dates)$mon+3) %/% 3)%%4]然后将季度变化6个月的FY粘贴,但您可能需要调整,因为您的年份规格对于命名年份不明确:
This then pastes the FY with the quarter shifted 6 months, but you may need to adjust because your year specification was ambiguous about "naming the year":
dat$FY_Q <- paste( 1900+as.POSIXlt( dat$dates )$year+ 1*(as.POSIXlt( dat$dates )$mon %in% 7:12) , c('Q1','Q2','Q3','Q4')[ 1+((as.POSIXlt(dat$dates)$mon-6) %/% 3)%%4] , sep="-") dat Date Country Class Value dates FY_Q 1 6/1/2010 USA A 45 2010-06-01 2010-Q4 2 6/1/2010 Canada A 23 2010-06-01 2010-Q4 3 6/1/2010 Brazil B 65 2010-06-01 2010-Q4 4 9/1/2010 USA B 47 2010-09-01 2011-Q1 5 9/1/2010 Canada A 98 2010-09-01 2011-Q1 6 9/1/2010 Brazil B 25 2010-09-01 2011-Q1 snipped---------$ b b
现在,请按FY_Q和国家/地区进行点选或汇总:
So now do a tapply or aggregate by FY_Q and Country:
> with( dat, aggregate(Value, list(FY_Q, Country),FUN=mean) ) Group.1 Group.2 x 1 2010-Q4 Brazil 65 2 2011-Q1 Brazil 25 3 2011-Q2 Brazil 23 4 2011-Q3 Brazil 43 5 2011-Q4 Brazil 65 6 2012-Q1 Brazil 25 7 2012-Q2 Brazil 23 8 2012-Q3 Brazil 43 9 2010-Q4 Canada 23 10 2011-Q1 Canada 98 11 2011-Q2 Canada 79 12 2011-Q3 Canada 77 13 2011-Q4 Canada 23 14 2012-Q1 Canada 98 15 2012-Q2 Canada 79 16 2012-Q3 Canada 77 17 2010-Q4 USA 45 18 2011-Q1 USA 47 19 2011-Q2 USA 14 20 2011-Q3 USA 84 21 2011-Q4 USA 45 22 2012-Q1 USA 47 23 2012-Q2 USA 14 24 2012-Q3 USA 84请参阅:在R中将日期/时间格式设置为季节?类似的问题和解决方案。
See: Format date-time as seasons in R? for a somewhat similar problem and solutions.
更多推荐
如何计算按日期分组的平均值作为财政季度
发布评论