Normalize pandas panel (divide all values by first slice)

Question

I'm having a hard time normalizing a pandas "data" panel like this one:

In[23]:data Out[23]: <class 'pandas.core.panel.Panel'> Dimensions: 6 (items) x 252 (major_axis) x 4 (minor_axis) Items axis: Open to Adj Close Major_axis axis: 2011-01-03 00:00:00 to 2011-12-30 00:00:00 Minor_axis axis: AAPL to XOM

Basically, I'd like to divide all values by another panel which only contains the first "slice" of the original panel (first values for all 'items' and 'minor_axis'):

In[25]:data[:,:1,:] Out[25]: <class 'pandas.core.panel.Panel'> Dimensions: 6 (items) x 1 (major_axis) x 4 (minor_axis) Items axis: Open to Adj Close Major_axis axis: 2011-01-03 00:00:00 to 2011-01-03 00:00:00 Minor_axis axis: AAPL to XOM

Result should be a panel of equal size to the original with all values divided by the corresponding first ones (first date).

Thanks and regards.

PD: I've tried .apply and .divide as per other posts, but cannot find the way to make it work with this configuration (dividing by a slice of the panel); using simple scalar division both methods work fine.

Answer 1

This is my first answer on StackOverflow, so hopefully this is helpful. I've run into the same problem, but instead of a pandas panel object, I'm using a Dataframe object with a multiindex. You should be able to get the desired affect in the following way: merge your dataframe with a smaller dataframe with only the first date for each stock selected.

Here's some dummy code.

Let's say your full dataframe is 'df' with a multiindex made up of two columns ['ticker','period']. In that dataframe is one column ['price'].

Here's how you could do what you want.

df_slice = df[df.reset_index()['period'] == '2017-01-01']
df_merged = pd.merge(df.reset_index(), df_slice, how='inner', on='ticker')
df_merged['price_normalized'] = df_merged.price_x/df_merged.price_y

I hope that helps!