CODEWITHSUNDEEP
pythonpandasdataframemulti-index
dsbbsd9
dsbbsd9

Reputation: 107

How to iterate and calculate over a pandas multi-index dataframe

I have a pandas multi-index dataframe:

>>> df
                     0         1
first second
A     one     0.991026  0.734800
      two     0.582370  0.720825
B     one     0.795826 -1.155040
      two     0.013736 -0.591926
C     one    -0.538078  0.291372
      two     1.605806  1.103283
D     one    -0.617655 -1.438617
      two     1.495949 -0.936198

I'm trying to find an efficient way to divide each number in column 0 by the maximum number in column I that shares the same group under index "first", and make this into a third column. Is there a simply efficient method for doing something like this that doesn't require multiple for loops?

Upvotes: 1

Views: 112

Answers (1)

jezrael
jezrael

Reputation: 863146

Use Series.div with max for maximal values per first level:

print (df[1].max(level=0))
first
A    0.734800
B   -0.591926
C    1.103283
D   -0.936198
Name: 1, dtype: float64


df['new'] = df[0].div(df[1].max(level=0))
print (df)
                     0         1       new
first second                              
A     one     0.991026  0.734800  1.348702
      two     0.582370  0.720825  0.792556
B     one     0.795826 -1.155040 -1.344469
      two     0.013736 -0.591926 -0.023206
C     one    -0.538078  0.291372 -0.487706
      two     1.605806  1.103283  1.455480
D     one    -0.617655 -1.438617  0.659748
      two     1.495949 -0.936198 -1.597898

Upvotes: 2

Related Questions