I have a multi-index dataframe where I'd like to perform an operation on each group based on the minimum value of one of the columns. For example:
import pandas as pd
d={'name':['foo','foo', 'foo', 'bar', 'bar', 'bar','baz', 'baz'],
'grp':[1, 2, 4, 1, 4, 8, 2, 4],
'val':[50, 100, 200, 25, 100, 200, 75, 150]}
df = pd.DataFrame(data=d)
df.set_index(['name', 'grp'], inplace=True)
df
Gives me a dataframe like this:
val
name grp
foo 1 50
2 100
4 200
bar 1 25
4 100
8 200
baz 2 75
4 150
What I'd like to do is perform an operation on each value for each 'grp' grouping based on the lowest value in 'grp'. For example, normalize each 'val' by dividing each one in the group by the lowest value to get something like this:
val
name grp
foo 1 50 1
2 100 2
4 200 4
bar 1 25 1
4 100 4
8 200 8
baz 2 75 1
4 150 2
Note that the calculation is performed on the 'val' column, but based on the minimum 'grp' number's 'val' value. I'm struggling with finding a good way to loop through this, so thanks for any pointers. I'm using Python v3.6 if it makes a difference.
Using div with level
df.div(df.min(level=[0]),level='name')
Out[157]:
val
name grp
foo 1 1.0
2 2.0
4 4.0
bar 1 1.0
4 4.0
8 8.0
baz 2 1.0
4 2.0