Making calculations with Multiindex columns

Question

import pandas as pd
import numpy as np

midx = pd.MultiIndex(levels = [['A', 'B'], ['x', 'y', 'z']],
                     codes = [[1, 1, 1, 0, 0, 0], [2, 1, 0, 2, 1, 0]])

df = pd.DataFrame([[0.8, 0.4, 0.3],
                   [0.6, 1.0, 0.1],
                   [0.1, 0.9, 0.5],
                   [0.4, 1.3, 0.6],
                   [0.3, 0.7, 0.4],
                   [2.3, 1.0, 0.2]], columns = ['K', 'L', 'M'], index = midx)

print(df)

       K    L    M
B z  0.8  0.4  0.3
  y  0.6  1.0  0.1
  x  0.1  0.9  0.5
A z  0.4  1.3  0.6
  y  0.3  0.7  0.4
  x  2.3  1.0  0.2

I have multiindex dataframe in this structure and this is what I want to calculate:

df.loc['B', 'M'] = (df.loc['B', 'K'] + df.loc['A', 'K']).div(df.loc['B', 'L'] + df.loc['A', 'L']) 

As a result of this process all values are NaN. How can I fix this?

Answer 1

There are missing values, because different index of a and df.loc['B', 'M'].index, solution is create MultiIndex, e.g. by MultiIndex.from_product:

a = (df.loc['B', 'K'] + df.loc['A', 'K']).div(df.loc['B', 'L'] + df.loc['A', 'L']) 
a.index = pd.MultiIndex.from_product([['B'], a.index])
df.loc['B', 'M'] = a
print (df)
       K    L         M
B z  0.8  0.4  0.705882
  y  0.6  1.0  0.529412
  x  0.1  0.9  1.263158
A z  0.4  1.3  0.600000
  y  0.3  0.7  0.400000
  x  2.3  1.0  0.200000

Another idea is converting to numpy array, but it should be risk if ordering of index in a is different like df.loc['B', 'M'].index, then should be assigned data in wrong order:

df.loc['B', 'M'] = a.to_numpy()
print (df)
       K    L         M
B z  0.8  0.4  0.705882
  y  0.6  1.0  0.529412
  x  0.1  0.9  1.263158
A z  0.4  1.3  0.600000
  y  0.3  0.7  0.400000
  x  2.3  1.0  0.200000