如何计算按财政季度分组的日期平均值 [英] How to calculate Mean by Date Grouped as Fiscal Quarters

问题描述

我有下表:

 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

在日期"列中，年份除以以下月份 - 三月、六月、九月和十二月.我需要将六月到三月的月份分组为财政年度，并按每个财政年度计算国家"和类别"列值"的平均值.有人可以帮我做吗?

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)))]  

cut.default 中的错误(Period, list(6/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]

然后将 FY 粘贴到季度移动 6 个月，但您可能需要调整，因为您的年份规范对命名年份"不明确:

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---------

现在按 FY_Q 和 Country 做一个 Tapply 或聚合:

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.

这篇关于如何计算按财政季度分组的日期平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！