I'm looking at some financial time series data, and am having trouble adding a new column to a pivot table that I created using a dataFrame. The original data frame is called df and has the following format.
Factor DlyReturn
DataDate
1996-12-31 BETA -1.447831e-03
1996-12-31 MOMENTUM -0.000090
1996-12-31 SIZE -2.030551e-03
1997-01-01 BETA -3.546312e-07
1997-01-01 MOMENTUM 0.000012
1997-01-01 SIZE -6.264164e-08
1997-01-02 BETA -1.789611e-03
1997-01-02 MOMENTUM -0.003170
1997-01-02 SIZE -2.353855e-04
1997-01-03 BETA 1.729851e-03
1997-01-03 MOMENTUM -0.000198
1997-01-03 SIZE 1.179356e-03
1997-01-06 BETA 6.946758e-04
1997-01-06 MOMENTUM 0.000812
1997-01-06 SIZE 1.956925e-04
df is indexed by the column "DataDate". I went ahead and created a pivot table that would give me the values in "DlyReturn" first by indexed by "Factor" and then indexed by "DataDate"using the following
pivot = pd.pivot_table(df,index=['Factor',df.index],values='DlyReturn')
the new object pivot now has the following format :
Factor DataDate
BETA 1996-12-31 -0.000383
1997-01-01 -0.000010
1997-01-02 -0.003319
1997-01-03 -0.000467
1997-01-06 0.001267
I'm now trying to add a column to this pivot table that gives me the rolling standard deviation for BETA, MOMENTUM, and SIZE across the entire date range contained in "DataDate". I made the following attempt but kept getting an error
pivot['rolling_std']=pd.rolling_std(pivot.xs(('Factor','DataDate'),axis=0),window=252)
The error message i kept getting KeyError: ('Factor', 'DataDate'). Can't figure out if there's a mistake in my code, or if stylistically I'm approaching this all wrong. Hoping someone will have some suggestion.
