python - 如何在 pct_change 计算中对 pandas DataFrame 中的多列进行分组

Question

我正在将 pct_change 计算应用于 pandas 数据帧。当月份列被排序时，一切正常。如果不是，则计算结果不正确。

现在这是我的代码:

data = [
('product_a','1/31/2014',53)
,('product_b','1/31/2014',44)
,('product_c','1/31/2014',36)
,('product_a','11/30/2013',52)
,('product_b','11/30/2013',43)
,('product_c','11/30/2013',35)
,('product_a','3/31/2014',50)
,('product_b','3/31/2014',41)
,('product_c','3/31/2014',34)
,('product_a','12/31/2013',50)
,('product_b','12/31/2013',41)
,('product_c','12/31/2013',34)
,('product_a','2/28/2014',52)
,('product_b','2/28/2014',43)
,('product_c','2/28/2014',35)
]

product_df = DataFrame( data, columns=['prod_desc','activity_month','prod_count'] )

for index, row in product_df.iterrows():
  row['activity_month']= datetime.strptime(row['activity_month'],'%m/%d/%Y')
  product_df.loc[index, 'activity_month'] = date.strftime(row['activity_month'],'%Y-%m-%d')

product_df['pct_ch'] = product_df.groupby('prod_desc')['prod_count'].pct_change()

product_df = product_df.sort(['prod_desc','activity_month'])

我得到的返回:

   prod_desc activity_month  prod_count    pct_ch
3      product_a     2013-11-30         52 -0.018868
9      product_a     2013-12-31         50  0.000000
0      product_a     2014-01-31         53       NaN
12     product_a     2014-02-28         52  0.040000
6      product_a     2014-03-31         50 -0.038462
4      product_b     2013-11-30         43 -0.022727
10     product_b     2013-12-31         41  0.000000
1      product_b     2014-01-31         44       NaN
13     product_b     2014-02-28         43  0.048780
7      product_b     2014-03-31         41 -0.046512
5      product_c     2013-11-30         35 -0.027778
11     product_c     2013-12-31         34  0.000000
2      product_c     2014-01-31         36       NaN
14     product_c     2014-02-28         35  0.029412
8      product_c     2014-03-31         34 -0.028571

这里的计算是无序的，因为每个产品第一个月的 pct_change 应该是 NaN。

我认为问题在于 pct_change 计算不包括 groupby 中的“activity_month”。当我尝试添加它时，我得到以下输出。

product_df['pct_ch'] = product_df.groupby(['prod_desc','activity_month'])['prod_count'].pct_change() 

   prod_desc activity_month  prod_count  pct_ch
3      product_a     2013-11-30         52     NaN
9      product_a     2013-12-31         50     NaN
0      product_a     2014-01-31         53     NaN
12     product_a     2014-02-28         52     NaN
6      product_a     2014-03-31         50     NaN
4      product_b     2013-11-30         43     NaN
10     product_b     2013-12-31         41     NaN
1      product_b     2014-01-31         44     NaN
13     product_b     2014-02-28         43     NaN
7      product_b     2014-03-31         41     NaN
5      product_c     2013-11-30         35     NaN
11     product_c     2013-12-31         34     NaN
2      product_c     2014-01-31         36     NaN
14     product_c     2014-02-28         35     NaN
8      product_c     2014-03-31         34     NaN

Answer 1

所以我认为您遇到的问题是 groupby 正在计算相同 prod_desc 的相邻行之间的百分比差异，并且当您执行操作时这不是按日期顺序排序的，因此移动排序上面的 groupby 将解决这个问题。您还可以删除 for 循环并使用 pandas 将其写为一行。

import pandas as pd 

data = [
('product_a','1/31/2014',53)
,('product_b','1/31/2014',44)
,('product_c','1/31/2014',36)
,('product_a','11/30/2013',52)
,('product_b','11/30/2013',43)
,('product_c','11/30/2013',35)
,('product_a','3/31/2014',50)
,('product_b','3/31/2014',41)
,('product_c','3/31/2014',34)
,('product_a','12/31/2013',50)
,('product_b','12/31/2013',41)
,('product_c','12/31/2013',34)
,('product_a','2/28/2014',52)
,('product_b','2/28/2014',43)
,('product_c','2/28/2014',35)
]

product_df = pd.DataFrame( data, columns=['prod_desc','activity_month','prod_count'])

product_df['activity_month'] = pd.to_datetime(product_df['activity_month'],
 format='%m/%d/%Y')

product_df = product_df.sort_values(['prod_desc','activity_month'])
product_df['pct_ch'] = product_df.groupby('prod_desc')['prod_count'].pct_change()

我认为这应该会产生您想要的答案。

    prod_desc activity_month  prod_count    pct_ch
3   product_a     2013-11-30          52       NaN
9   product_a     2013-12-31          50 -0.038462
0   product_a     2014-01-31          53  0.060000
12  product_a     2014-02-28          52 -0.018868
6   product_a     2014-03-31          50 -0.038462
4   product_b     2013-11-30          43       NaN
10  product_b     2013-12-31          41 -0.046512
1   product_b     2014-01-31          44  0.073171
13  product_b     2014-02-28          43 -0.022727
7   product_b     2014-03-31          41 -0.046512
5   product_c     2013-11-30          35       NaN
11  product_c     2013-12-31          34 -0.028571
2   product_c     2014-01-31          36  0.058824
14  product_c     2014-02-28          35 -0.027778
8   product_c     2014-03-31          34 -0.028571