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

Question

假设我有以下 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

对于每个唯一索引 (a,b,c)，我想将数据框的每一行除以列 x1 中值为“y”的行。输出数据框应如下所示:

    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

我知道 pd.DataFrame.div，但不确定如何根据 x1 中的值执行此操作。有什么想法吗？

Answer 1

您可以使用 div 或 / 和 level，Pandas 会为您对齐索引:

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

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

输出:

    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