pandas add higher level column multiindex

Question

I am looking for a way to add a higher level column index in pandas.

I have a multiindexed pandas dataframe like this:

>>>df

   product       A         B         C
   sell_buy      sell buy  sell buy  sell buy 
group company
  1    Bank X     100  50   200  10    20  20
  2    Bank Y      20  40   150  40    30  20
  3    Bank Z     100  20    20  10    40  10

I want to add a higher level index in columns from company, and set a row index with the date of today (using something like datetime.today())

   company       Bank X                        Bank Y                        Bank Z
   product       A         B         C         A         B         C         A         B         C
   sell_buy      sell buy  sell buy  sell buy  sell buy  sell buy  sell buy  sell buy  sell buy  sell buy
 2020-08-15       100  50   200  10    20  20    20  40   150  40    30  20   100  20    20  10    40  10

I tried several attempts, but I cannot figure out how to make it work...

pd.MultiIndex.from_product([df.columns.levels, ['Bank X']])
pd.MultiIndex.from_product([df.columns.levels, ['product']])

Any help would be appreciated. Thank you!

user_2901002 · Answer

I believe you need in first step convert first level to date values and then reshape by DataFrame.unstack, MultiIndex.reorder_levels and DataFrame.sort_index:

date = pd.Timestamp('today').floor('d')
df.index = pd.MultiIndex.from_product([[date], df.index.get_level_values(1)])

df = df.unstack().reorder_levels([2,0,1], axis=1).sort_index(axis=1)
print (df)
company    Bank X                        Bank Y                        Bank Z  \
product         A        B        C           A        B        C           A   
sell_buy      buy sell buy sell buy sell    buy sell buy sell buy sell    buy   
2020-08-15     50  100  10  200  20   20     40   20  40  150  20   30     20   

company                            
product           B        C       
sell_buy   sell buy sell buy sell  
2020-08-15  100  10   20  10   40

pandas add higher level column multiindex

1 Answers