如何计算按日期分组的平均值作为财政季度

本文介绍了如何计算按日期分组的平均值作为财政季度的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有下表：

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.