Search code examples
pythonpython-3.xpandasdataframemulti-index

Assign a new column in a multi-indexed dataframe

I have the following multi-indexed dataframe:

Multi-indexed dataframe

I want to add a new column labeled returns and assign the percentage change applied to the close column i.e

bars.loc[symbol, 'returns'] = bars.loc[symbol]['close'].pct_change()

The challenge is that the returns column does not get populated as expected. So with the snippet below, here is what I got

for symbol in symbols:
   bars.loc[symbol, 'returns'] = bars.loc[symbol]['close'].pct_change()
print(df.loc[symbols[0]].tail())

enter image description here

The returns column has NaN as their values, what am I doing wrong here?

Solution

  • You need to assign the values to the new column. Assuming the DF is bars and symbol index is level_0 then for the loop, you can use the code below. This assumes you want 10.0 as the return % and not 0.1.

    idx_list = bars.index.levels[0]

for symbol in idx_list:
        x = bars.loc[symbol, 'close'].pct_change()*100
        bars.loc[symbol, 'returns'] = x.values