替换每列中的某些值

Question

我有一个如下所示的数据帧

+---+-------------+---------+---------------+---------------+---------+------+--------------------------+-----+----------+
|   | Pregnancies | Glucose | BloodPressure | SkinThickness | Insulin | BMI  | DiabetesPedigreeFunction | Age | Outcome  |
+---+-------------+---------+---------------+---------------+---------+------+--------------------------+-----+----------+
| 0 |           6 |   148.0 |          72.0 |          35.0 |   125.0 | 33.6 |                    0.627 |  50 |        1 |
| 1 |           1 |    85.0 |          66.0 |          29.0 |   125.0 | 26.6 |                    0.351 |  31 |        0 |
| 2 |           8 |   183.0 |          64.0 |          29.0 |   125.0 | 23.3 |                    0.672 |  32 |        1 |
| 3 |           1 |    89.0 |          66.0 |          23.0 |    94.0 | 28.1 |                    0.167 |  21 |        0 |
| 4 |           0 |   137.0 |          40.0 |          35.0 |   168.0 | 43.1 |                    2.288 |  33 |        1 |
+---+-------------+---------+---------------+---------------+---------+------+--------------------------+-----+----------+

在看了每个变量的方框图之后，我发现它们有异常值。你知道吗

所以在除Outcome之外的每一列中，我想替换特定列的greater than 95 percentile with value at 75 percentile值和less than 5 percentile with 25 percentile值

例如，在Glucose列中，大于95%的值，我想用Glucose列中75%的值替换它们

如何使用过滤器和百分位函数实现这一点

任何帮助都将不胜感激

Answer 1

可以对除outcome之外的所有列使用apply，函数为np.clip和np.percentile：

import numpy as np

percentile_df = df.set_index('Outcome').apply(lambda x: np.clip(x, *np.percentile(x, [25,75]))).reset_index()

>>> percentile_df
   Outcome  Pregnancies  Glucose  BloodPressure  SkinThickness  Insulin   BMI  \
0        1          6.0    148.0           66.0           35.0    125.0  33.6   
1        0          1.0     89.0           66.0           29.0    125.0  26.6   
2        1          6.0    148.0           64.0           29.0    125.0  26.6   
3        0          1.0     89.0           66.0           29.0    125.0  28.1   
4        1          1.0    137.0           64.0           35.0    125.0  33.6   

   DiabetesPedigreeFunction   Age  
0                     0.627  33.0  
1                     0.351  31.0  
2                     0.672  32.0  
3                     0.351  31.0  
4                     0.672  33.0  

[编辑]我首先误读了这个问题，这里有一种方法可以使用np.select将第5个和第95个百分位分别更改为第25和第75个百分位：

def cut(column):
    conds = [column > np.percentile(column, 95),
             column < np.percentile(column, 5)]
    choices = [np.percentile(column, 75),
               np.percentile(column, 25)]
    return np.select(conds,choices,column)

df.set_index('Outcome',inplace=True)

df = df.apply(lambda x: cut(x)).reset_index()

>>> df
   Outcome  Pregnancies  Glucose  BloodPressure  SkinThickness  Insulin   BMI  \
0        1          6.0    148.0           66.0           35.0    125.0  33.6   
1        0          1.0     89.0           66.0           29.0    125.0  26.6   
2        1          6.0    148.0           64.0           29.0    125.0  26.6   
3        0          1.0     89.0           66.0           29.0    125.0  28.1   
4        1          1.0    137.0           64.0           35.0    125.0  33.6   

   DiabetesPedigreeFunction   Age  
0                     0.627  33.0  
1                     0.351  31.0  
2                     0.672  32.0  
3                     0.351  31.0  
4                     0.672  33.0