Remove Outliers in Pandas DataFrame using Percentiles

user_1472537 0
Viewed 91553

I have a DataFrame df with 40 columns and many records.

df:

User_id | Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7 |...| Col39

For each column except the user_id column I want to check for outliers and remove the whole record, if an outlier appears.

For outlier detection on each row I decided to simply use 5th and 95th percentile (I know it's not the best statistical way):

Code what I have so far:

P = np.percentile(df.Col1, [5, 95])
new_df = df[(df.Col1 > P[0]) & (df.Col1 < P[1])]

Question: How can I apply this approach to all columns (except User_id) without doing this by hand? My goal is to get a dataframe without records that had outliers.

Thank you!

outliers pandas python
5 Answers

Use this code and don't waste your time:

Q1 = df.quantile(0.25)
Q3 = df.quantile(0.75)
IQR = Q3 - Q1

df = df[~((df < (Q1 - 1.5 * IQR)) |(df > (Q3 + 1.5 * IQR))).any(axis=1)]

in case you want specific columns:

cols = ['col_1', 'col_2'] # one or more

Q1 = df[cols].quantile(0.25)
Q3 = df[cols].quantile(0.75)
IQR = Q3 - Q1

df = df[~((df[cols] < (Q1 - 1.5 * IQR)) |(df[cols] > (Q3 + 1.5 * IQR))).any(axis=1)]
user_118932720

To trim the entire DataFrame based on a single column, here is an easier way. Remove n rows from the top and bottom after sorting.

nb_to_trim = round(len(df.index) * 0.05)
df = df.sort_values(col1).iloc[nb_to_trim:-nb_to_trim, :]
user_25296190
Related