Python (pandas): How to divide each row by an 'absolute' row based on value in column

user_10546779 0
Viewed 50

suppose I have the following pandas DataFrame:

import numpy as np
import pandas as pd

np.random.seed(seed=9876)
df1 = pd.DataFrame(['a']*3+['b']*3+['c']*3)
df2 = pd.DataFrame(['x','y','z']*3)
df3 = pd.DataFrame(np.round(np.random.randn(9,2),2)*100)
df = pd.concat([df1, df2, df3], axis = 1)
df.columns = ['ind', 'x1', 'x2','x3']
df = df.set_index('ind')
print(df)
    x1     x2     x3
ind                 
a    x   39.0 -109.0
a    y   21.0   32.0
a    z  -93.0    3.0
b    x -111.0  -12.0
b    y   -1.0   66.0
b    z  -33.0  -30.0
c    x  -90.0 -103.0
c    y   22.0  -25.0
c    z   95.0  112.0

For each unique index (a,b,c), I would like to divide each row of the data frame by the row that has a value of 'y' in the column x1. The output data frame should look like this:

    x1     x2     x3
ind                 
a    x   1.857   -3.406
a    y   1.0     1.0
a    z  -4.429   0.094
b    x   111.0   -0.182
b    y   1.0     1.0
b    z   33.0    -0.455
c    x  -4.091   4.12
c    y   1.0     1.0
c    z   4.312   -4.48

I'm aware of pd.DataFrame.div, but unsure of how to do this based on the value in x1. Any ideas?

pandas python
2 Answers

You can use div or / with level, Pandas will align index for you:

cols = ['x2','x3']
df[cols] = df[cols].div(df.loc[df['x1']=='y',cols])

# or
# df[cols] /= df.loc[df['x1']=='y',cols]

Output:

    x1          x2        x3
ind                         
a    x    1.857143 -3.406250
a    y    1.000000  1.000000
a    z   -4.428571  0.093750
b    x  111.000000 -0.181818
b    y    1.000000  1.000000
b    z   33.000000 -0.454545
c    x   -4.090909  4.120000
c    y    1.000000  1.000000
c    z    4.318182 -4.480000
user_42384080

IIUC

df.iloc[:,1:]/=df.loc[df.x1=='y',['x2','x3']].reindex(df.index).values
df
    x1          x2        x3
ind                         
a    x    1.857143 -3.406250
a    y    1.000000  1.000000
a    z   -4.428571  0.093750
b    x  111.000000 -0.181818
b    y    1.000000  1.000000
b    z   33.000000 -0.454545
c    x   -4.090909  4.120000
c    y    1.000000  1.000000
c    z    4.318182 -4.480000
user_79645270
Related