Pandas Replace certain values in each column

Question

I have a dataframe which looks as below

+---+-------------+---------+---------------+---------------+---------+------+--------------------------+-----+----------+
|   | 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 |
+---+-------------+---------+---------------+---------------+---------+------+--------------------------+-----+----------+

After watching the box plot for each variable.I found that they have outliers in them.

So in each column except Outcome I want to replace the values which are greater than 95 percentile with value at 75 percentile and values which are less than 5 percentile with 25 percentile of that particular column

For example in column Glucose values which are above 95 percentile I want to replace them with value at 75 percentile of Glucose column

How can I do this with pandas filter and percentile function

Any help for this will be appreciated

sacuL · Accepted Answer · 2018-11-14 16:31:40Z

3

You can use apply on all columns except outcome, with the functions np.clip and 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

[EDIT] I misread the question at first, here is a way to change the 5th and 95th percentile to 25th and 75th respectively, using np.select:

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

edited Nov 14, 2018 at 16:31

answered Nov 13, 2018 at 17:42

sacuL

51.6k9 gold badges88 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

sacuL Over a year ago

I've never thought about it... why not? the documentation for pd.np.clip should just be the docs for np.clip. In any case, then I'll just delete the pd.

jpp Over a year ago

Never tried it myself, that's all. Also you can do np.percentile(x, [25, 75]). That version would get my vote :).

sacuL Over a year ago

Thanks! Yeah, that's better!

Rookie_123 Over a year ago

What does *np.percentile does.I read about np.percentile but why * is used

sacuL Over a year ago

np.percentile(x, [25, 75]) returns values for the 25th and the 75th percentile, so you need to unpack that using * so that the 25th percentile gets passed to a_min and the 75th percentile gets passed to a_max of np.clip

|

Collectives™ on Stack Overflow

Pandas Replace certain values in each column

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related