0

I have a dataframe with a multiindex column, more or less like so

import pandas as pd

columns = pd.MultiIndex.from_product((("Length", "Weight"), ("Max", "Min"), ("asd",)), names=("Measure", "Info", "Unit"))
df = pd.DataFrame(0., columns=columns, index=range(2))

>>> df
Measure Length  Weight
Info    Max Min Max Min
Unit    asd asd asd asd
0       0   0   0   0
1       0   0   0   0

I would like to change the units based on the measurements. The connection between measure and unit is expressed as a dictionary:

measure_to_unit = {"Length": "m", "Weight": "kg"}

I have tried using .replace but it simply doesn't seem to be the right tool for the job.

The expected result is

Measure Length  Weight
Info    Max Min Max Min
Unit    m   m   kg  kg
0       0   0   0   0
1       0   0   0   0
edd313
  • 1,109
  • 7
  • 20
  • Try `df.columns = measure_to_unit.items()` –  Jun 27 '22 at 14:14
  • That will work in the example, not on my real data though. I have changed my example to clarify – edd313 Jun 27 '22 at 14:22
  • Maybe have a look at this: [link](https://stackoverflow.com/questions/41221079/rename-multiindex-columns-in-pandas) – Yehla Jun 27 '22 at 14:35

1 Answers1

2

Update

measure_to_unit = {"Length": "m", "Weight": "kg"}

l1, l2, l3 = zip(*df.columns)
newcol = pd.MultiIndex.from_arrays([l1, l2, [measure_to_unit[i] for i in l1]])
df.set_axis(newcol, axis=1)

Output:

     Length      Weight     
     Max  Min    Max  Min
       m    m     kg   kg
0    0.0  0.0    0.0  0.0
1    0.0  0.0    0.0  0.0

If the order of your dataframe columns match the order of your index, then you can do this:

df.set_axis(pd.MultiIndex.from_arrays([measure_to_unit.keys(), measure_to_unit.values()]), axis=1)

Output:

  Length Weight
       m     kg
0    0.0    0.0
1    0.0    0.0
Scott Boston
  • 147,308
  • 15
  • 139
  • 187
  • Thanks for the reply, this will work with my initial example but it's not the solution I'm looking for. I have changed the example input to clarify – edd313 Jun 27 '22 at 14:27
  • 1
    @edd313 No, don't think there is a native function to do this, but we can use list comprehension along with your dictionary to define the third level using from_arrays in pd.MultiIndex. – Scott Boston Jun 27 '22 at 15:02