Search code examples
pythonpandasmulti-index

Combine two dataframes on one level of multiindex

Consider two dataframes df_a and df_b. df_a has a multiindex. df_b has a regular index. The first level of the multiindex of df_a matches the index of df_b. I want to divide (or add, subtract or multiply) dataframe df_a with df_b matching the first level of the index of df_a with the index of df_b.

import pandas
df_a = pd.DataFrame(data=[1,2,8,4],
                    index=pd.MultiIndex.from_product([['A', 'B'], [1, 2]]))
df_b = pd.DataFrame(data=[2, 4], index=['A', 'B'])

The operation df_a / df_b (after whatever coding is necessary) should give something like this

A 1  .5  
  2  1
B 1  2
  2  1
Solution

  • IIUC div with level

    df_a.div(df_b,axis=0,level=0)
Out[30]: 
       0
A 1  0.5
  2  1.0
B 1  2.0
  2  1.0