How to normalize subset of rolling pandas dataframe?

user_10281060 0
Viewed 380

So I have a dataframe full of an entire universe of stock tickers. They are MultiIndexed by ticker and then date. What I am trying to do is normalize one of the indicator columns only for that subset of the data. For example I would want a to normalize the data on a rolling Zscore for ind1 only based on the ticker A, then the same for ticker B and so on and so forth. I am able to apply this normalization to a slice of the dataframe iteratively but I am looking for a way to easily apply this rolling normalizationn without slicing by ticker.

This is the Zscore normalization I am using but I want to apply it to each ticker subset in the dataframe without slicing and then reappending to a new dataframe.

df['normalizedInd1'] = (df['ind1'] - df['ind1'].rolling(30).mean()) / df['ind1'].rolling(30).std()

And this is a sample of the dataframe structure

                  secid       ind1       ind2
ticker  date            
A   2011-01-03  101149.0    100792.5533  177960.0
    2011-01-04  101149.0    127658.3339  304624.0
    2011-01-05  101149.0    122648.9491  77050.0
B   2011-01-06  101150.0    110161.8415  151825.0
    2011-01-10  101150.0    112800.4117  378804.0
    2011-01-11  101150.0    79074.8645   525628.0
pandas stock dataframe python-3.x
1 Answers

Can you try to .groupby the first column of you index with level=0 OR ticker? Then, use the .groupby object g to do your calculation per group. I have changed from rolling(30) to rolling(2), so that there is output:

g = df.groupby(level=0)['ind1'].rolling(2)
df['normalizedInd1'] = (df['ind1'] - g.mean().droplevel(0)) / g.std().droplevel(0)
df
Out[1]: 
                      secid         ind1      ind2  normalizedInd1
ticker date                                                       
A      2011-01-03  101149.0  100792.5533  177960.0             NaN
       2011-01-04  101149.0  127658.3339  304624.0        0.707107
       2011-01-05  101149.0  122648.9491   77050.0       -0.707107
B      2011-01-06  101150.0  110161.8415  151825.0             NaN
       2011-01-10  101150.0  112800.4117  378804.0        0.707107
       2011-01-11  101150.0   79074.8645  525628.0       -0.707107
user_63667700
Related