使用基于另一列的groupby的min-max规范化dataframe的列

Question

数据帧如图所示

Name     Job      Salary
john   painter    40000
peter  engineer   50000
sam     plumber   30000
john    doctor    500000
john    driver    20000
sam    carpenter  10000
peter  scientist  100000

如何按列名分组并对每个组的Salary列应用规范化？在

预期结果：

^{pr2}$

我试过以下方法

data = df.groupby('Name').transform(lambda x: (x - x.min()) / x.max()- x.min())

然而，这产生了

         Salary
0 -19999.960000
1 -50000.000000
2  -9999.333333
3 -19999.040000
4 -20000.000000
5 -10000.000000
6 -49999.500000

Answer 1

你快到了。在

>>> df                                                                                                                 
    Name        Job  Salary
0   john    painter   40000
1  peter   engineer   50000
2    sam    plumber   30000
3   john     doctor  500000
4   john     driver   20000
5    sam  carpenter   10000
6  peter  scientist  100000
>>>                                                                                                                    
>>> result = df.assign(Salary=df.groupby('Name').transform(lambda x: (x - x.min()) / (x.max()- x.min())))
>>> # alternatively, df['Salary'] = df.groupby(... if you don't need a new frame       
>>> result                                                                                                               
    Name        Job    Salary
0   john    painter  0.041667
1  peter   engineer  0.000000
2    sam    plumber  1.000000
3   john     doctor  1.000000
4   john     driver  0.000000
5    sam  carpenter  0.000000
6  peter  scientist  1.000000

所以基本上，你只是忘了把x.max() - x.min()括在括号里。在

---

请注意，通过一系列矢量化操作，这可以更快地完成。在

^{pr2}$

---

时间安排：

>>> # Setup
>>> df = pd.concat([df]*1000, ignore_index=True)                                                                       
>>> df.Name = np.arange(len(df)//4).repeat(4) # 4 names per group                                                      
>>> df                                                                                                                 
      Name        Job  Salary
0        0    painter   40000
1        0   engineer   50000
2        0    plumber   30000
3        0     doctor  500000
4        1     driver   20000
...    ...        ...     ...
6995  1748    plumber   30000
6996  1749     doctor  500000
6997  1749     driver   20000
6998  1749  carpenter   10000
6999  1749  scientist  100000

[7000 rows x 3 columns]
>>>
>>> # Tests @ i5-6200U CPU @ 2.30GHz
>>> %timeit df.groupby('Name').transform(lambda x: (x - x.min()) / (x.max()- x.min()))                                 
1.19 s ± 20.3 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
>>> %%timeit 
...: grouper = df.groupby('Name')['Salary'] 
...: maxes = grouper.transform('max') 
...: mins = grouper.transform('min') 
...: (df.Salary - mins)/(maxes - mins) 
...:  
...:                                                                                                                   
3.04 ms ± 94.5 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)