按周划分熊猫数据框

Question

我有一个列"日期"one_answers"Num"的数据框。

dates = pd.date_range('1/1/2001','1/1/2003', freq = 'd')
nums = [np.random.randint(100) for x in range(len(dates))]
df = pd.DataFrame({'Dates': dates, 'DOW': dates.strftime('%a'), 'Nums': nums})
df = df[(df.DOW != 'Sat') & (df.DOW !='Sun')]
df = df.drop([7,18]).reset_index(drop = True)

我需要将数据框放入箱中，这样我就可以分别隔离每个星期。最终目标是查看每周的MAX"Nums"值，并将其与下一周的LAST值进行比较，以查看百分比变化是多少。例如:

week1 = df[0:5]
week2 = df[5:9]
week3 = df[9:12]
In [156]: w1max = week1.Nums.max()
Out[156]: 97
In [157]: w2Last = week2.iloc[-1].Nums
Out[157]: 76
pctChange = (w2Last-w1max)/float(w1max)
In [166]: pctChange
Out[166]: -0.21649484536082475

问题是有些周缺少天数(例如周2缺少周一，周3缺少周五)。那我怎么把它们分开呢?

最接近的似乎是使用df.resample()，但我不知道如何进行比较，我试图使用它

Answer 1

import numpy as np
import pandas as pd
np.random.seed(2016)
dates = pd.date_range('1/1/2001','1/1/2003', freq = 'd')
nums = [np.random.randint(100) for x in range(len(dates))]
df = pd.DataFrame({'Dates': dates, 'DOW': dates.strftime('%a'), 'Nums': nums})
df = df[(df.DOW != 'Sat') & (df.DOW !='Sun')]
df = df.drop([7,18]).reset_index(drop = True)
df2 = df.groupby(pd.Grouper(freq='W', key='Dates'))['Nums'].agg(['max','last'])
df2['previous_max'] = df2['max'].shift(1)
df2['change'] = (df2['last']-df2['previous_max'])/df2['previous_max']
print(df2.head())

收益率

            max  last  previous_max    change
Dates                                        
2001-01-07   83    39           NaN       NaN
2001-01-14   75    75          83.0 -0.096386
2001-01-21   97    18          75.0 -0.760000
2001-01-28   72    37          97.0 -0.618557
2001-02-04   84    24          72.0 -0.666667

---

df.groupby和pd.Grouper对象可用于按周分组行。您可以使用agg方法来查找每个组中Nums的max和last值:

In [163]: df2 = df.groupby(pd.Grouper(freq='W', key='Dates'))['Nums'].agg(['max','last'])
In [164]: df2.head()
Out[164]: 
            max  last
Dates                
2001-01-07   83    39
2001-01-14   75    75
2001-01-21   97    18
2001-01-28   72    37
2001-02-04   84    24

然后使用shift(1)将max的值下移一行:

In [165]: df2['previous_max'] = df2['max'].shift(1); df2.head()
Out[165]: 
            max  last  previous_max
Dates                              
2001-01-07   83    39           NaN
2001-01-14   75    75          83.0
2001-01-21   97    18          75.0
2001-01-28   72    37          97.0
2001-02-04   84    24          72.0

则可以通过简单的减法和除法计算变化百分比:

In [166]: df2['change'] = (df2['last']-df2['previous_max'])/df2['previous_max']; df2.head()
Out[166]: 
            max  last  previous_max    change
Dates                                        
2001-01-07   83    39           NaN       NaN
2001-01-14   75    75          83.0 -0.096386
2001-01-21   97    18          75.0 -0.760000
2001-01-28   72    37          97.0 -0.618557
2001-02-04   84    24          72.0 -0.666667