Pandas multiindex dataframe: calculation applied to all columns in an index level
Let's do some dataframe reshaping like this:
import pandas as pd
from io import StringIO
# Create your input dataframe
csvdata = StringIO("""100 110 120 50 52 52
200 205 210 80 80 80
300 300 300 100 100 100
50 60 70 20 22 24
100 100 100 40 40 40
150 140 130 50 47 44""")
df = pd.read_csv(csvdata, sep='\s\s+', header=None, engine='python')
df.index = pd.MultiIndex.from_product([[*'AB'],[1,2,3]])
df.columns = pd.MultiIndex.from_product(['Population Homes'.split(' '),
[2018, 2019, 2020]])
df_out=df.stack().eval('Ratio = Population / Homes').unstack().round(1)
print(df_out)
Output:
Homes Population Ratio
2018 2019 2020 2018 2019 2020 2018 2019 2020
A 1 50 52 52 100 110 120 2.0 2.1 2.3
2 80 80 80 200 205 210 2.5 2.6 2.6
3 100 100 100 300 300 300 3.0 3.0 3.0
B 1 20 22 24 50 60 70 2.5 2.7 2.9
2 40 40 40 100 100 100 2.5 2.5 2.5
3 50 47 44 150 140 130 3.0 3.0 3.0
Using stack
, eval
and unstack
.