在给定未排序的其他约束的情况下规范化数据帧中的值

Question

我有这样一个数据框：

            counter leg_rate pose_rate component    approach      rmse
0   proc/stat-stime        d         d      test    Baseline  1.583097
1   proc/stat-stime        d         r      test  AEW - MTEN  0.516108
2   proc/stat-stime        d         d      test        ASDF  0.705861
3   proc/stat-stime        r         r      test        ASDF  0.345816
4   proc/stat-utime        d         r      test    Baseline  1.128632
5   proc/stat-stime        d         r      test    Baseline  1.579803
6   proc/stat-stime        r         r      test    Baseline  1.345895
7   proc/stat-utime        r         r      test  AEW - MTEN  0.187236
8   proc/stat-utime        d         d      test    Baseline  1.193776
9   proc/stat-stime        r         d      test        ASDF  0.014975
10  proc/stat-utime        r         r      test        ASDF  0.985493
11  proc/stat-utime        r         d      test  AEW - MTEN  0.897336
12  proc/stat-stime        r         d      test    Baseline  1.415103
13  proc/stat-utime        r         d      test    Baseline  1.724266
14  proc/stat-utime        r         r      test    Baseline  1.294654
15  proc/stat-utime        d         d      test  AEW - MTEN  0.263845
16  proc/stat-utime        r         d      test        ASDF  0.497368
17  proc/stat-stime        d         d      test  AEW - MTEN  0.143402
18  proc/stat-utime        d         r      test  AEW - MTEN  0.233437
19  proc/stat-stime        r         d      test  AEW - MTEN  0.431739
20  proc/stat-utime        d         r      test        ASDF  0.002475
21  proc/stat-stime        d         r      test        ASDF  0.331700
22  proc/stat-stime        r         r      test  AEW - MTEN  0.985123
23  proc/stat-utime        d         d      test        ASDF  0.464989

我想通过将rmse与approach中名为Baseline的值相除来规范化它。最后应该有一个新的列rmse-norm，其中包含各自的标准化值。所有其他列基本上都提供了一个上下文，在划分rmse时需要进行匹配。这意味着争吵

1   proc/stat-stime        d         r      test  AEW - MTEN  0.516108

需要除以与其他列匹配的行

5   proc/stat-stime        d         r      test    Baseline  1.579803

在Baseline方法中总会有一个匹配的行

我尝试过使用groupby和为其他列使用索引的各种方法，但是由于列的顺序未知，我无法想出一些简洁的方法来为正确的值分配正确的顺序

Answer 1

我想你可以用：

#filter all rows with Baseline to `MultiIndex` `Series`
cols = ['counter','leg_rate','pose_rate','component']
s = df[df.approach == 'Baseline'].set_index(cols)['rmse']
print (s)
counter          leg_rate  pose_rate  component
proc/stat-stime  d         d          test         1.583097
proc/stat-utime  d         r          test         1.128632
proc/stat-stime  d         r          test         1.579803
                 r         r          test         1.345895
proc/stat-utime  d         d          test         1.193776
proc/stat-stime  r         d          test         1.415103
proc/stat-utime  r         d          test         1.724266
                           r          test         1.294654
Name: rmse, dtype: float64

#sorting for matching, because set_index sort index
df = df.sort_values(cols)
#divide by s, output to numpy array for assign to rmse column
df['rmse'] = df.set_index(cols)['rmse'].div(s).values
#sort index to original unsorted df
print (df.sort_index())
            counter leg_rate pose_rate component    approach      rmse
0   proc/stat-stime        d         d      test    Baseline  1.000000
1   proc/stat-stime        d         r      test  AEW - MTEN  0.326691
2   proc/stat-stime        d         d      test        ASDF  0.445873
3   proc/stat-stime        r         r      test        ASDF  0.256941
4   proc/stat-utime        d         r      test    Baseline  1.000000
5   proc/stat-stime        d         r      test    Baseline  1.000000
6   proc/stat-stime        r         r      test    Baseline  1.000000
7   proc/stat-utime        r         r      test  AEW - MTEN  0.144622
8   proc/stat-utime        d         d      test    Baseline  1.000000
9   proc/stat-stime        r         d      test        ASDF  0.010582
10  proc/stat-utime        r         r      test        ASDF  0.761202
11  proc/stat-utime        r         d      test  AEW - MTEN  0.520416
12  proc/stat-stime        r         d      test    Baseline  1.000000
13  proc/stat-utime        r         d      test    Baseline  1.000000
14  proc/stat-utime        r         r      test    Baseline  1.000000
15  proc/stat-utime        d         d      test  AEW - MTEN  0.221017
16  proc/stat-utime        r         d      test        ASDF  0.288452
17  proc/stat-stime        d         d      test  AEW - MTEN  0.090583
18  proc/stat-utime        d         r      test  AEW - MTEN  0.206832
19  proc/stat-stime        r         d      test  AEW - MTEN  0.305094
20  proc/stat-utime        d         r      test        ASDF  0.002193
21  proc/stat-stime        d         r      test        ASDF  0.209963
22  proc/stat-stime        r         r      test  AEW - MTEN  0.731946
23  proc/stat-utime        d         d      test        ASDF  0.389511

另一个带有groupby和自定义函数f的解决方案：

def f(x):
    x.rmse = x['rmse'] / x.loc[x['approach'] == 'Baseline', 'rmse'].item()
    return x

df = df.groupby(['counter','leg_rate','pose_rate','component']).apply(f)
print (df)
            counter leg_rate pose_rate component    approach      rmse
0   proc/stat-stime        d         d      test    Baseline  1.000000
1   proc/stat-stime        d         r      test  AEW - MTEN  0.326691
2   proc/stat-stime        d         d      test        ASDF  0.445873
3   proc/stat-stime        r         r      test        ASDF  0.256941
4   proc/stat-utime        d         r      test    Baseline  1.000000
5   proc/stat-stime        d         r      test    Baseline  1.000000
6   proc/stat-stime        r         r      test    Baseline  1.000000
7   proc/stat-utime        r         r      test  AEW - MTEN  0.144622
8   proc/stat-utime        d         d      test    Baseline  1.000000
9   proc/stat-stime        r         d      test        ASDF  0.010582
10  proc/stat-utime        r         r      test        ASDF  0.761202
11  proc/stat-utime        r         d      test  AEW - MTEN  0.520416
12  proc/stat-stime        r         d      test    Baseline  1.000000
13  proc/stat-utime        r         d      test    Baseline  1.000000
14  proc/stat-utime        r         r      test    Baseline  1.000000
15  proc/stat-utime        d         d      test  AEW - MTEN  0.221017
16  proc/stat-utime        r         d      test        ASDF  0.288452
17  proc/stat-stime        d         d      test  AEW - MTEN  0.090583
18  proc/stat-utime        d         r      test  AEW - MTEN  0.206832
19  proc/stat-stime        r         d      test  AEW - MTEN  0.305094
20  proc/stat-utime        d         r      test        ASDF  0.002193
21  proc/stat-stime        d         r      test        ASDF  0.209963
22  proc/stat-stime        r         r      test  AEW - MTEN  0.731946
23  proc/stat-utime        d         d      test        ASDF  0.389511