How to cap values in pandas within IQR respective to a column

user_7273886 0
Viewed 649

I wanted to clip values of a DataFrame into their inter qantile range, so that:

  1. If the actual value is higher than its 75th percentile it will default to 75th percentile value
  2. If the actual value is lower than 25th percentile it will default to 25th percentile.
  3. If the value is in between 25th and 75th percentile it will be the same value.

Is there a way to do it for all columns in one go (i.e. A max becomes 3, B and C max become 2.25)

test = pd.DataFrame({'A':[1,2,3,1,2,3,1,1,2,3,1,12],
                     'B':[0,1,2,1,0,1,3,1,2,1,3,9],
                     'C':[0,1,2,2,1,2,3,1,2,1,4,17]})

test.describe()


       A         B         C
25%    1.000000  1.000000  1.000000
75%    3.000000  2.250000  2.250000
max    12.000000 9.000000  17.000000
pandas python-3.x
1 Answers

I would do a for loop on column:

for col in test:
    low,high = test[col].quantile([.25,.75])
    test[col] = test[col].clip(low,high)

print(test)

Output:

    A     B     C
0   1  1.00  1.00
1   2  1.00  1.00
2   3  2.00  2.00
3   1  1.00  2.00
4   2  1.00  1.00
5   3  1.00  2.00
6   1  2.25  2.25
7   1  1.00  1.00
8   2  2.00  2.00
9   3  1.00  1.00
10  1  2.25  2.25
11  3  2.25  2.25

Update clip works with dataframe as well:

thresh = test.quantile([.25,.75])
test.clip(lower=thresh.loc[.25], 
          upper=thresh.loc[.75],
          axis=1)
user_42384080
Related