Drop rows based on one column values

user_10374129 0
Viewed 963

I've a dataframe which looks like this:

        wave       mean    median        mad
0    4050.32  -0.016182 -0.011940   0.008885
1    4208.98   0.023707  0.007189   0.032585
2    4508.28   3.662293  0.001414   7.193139
3    4531.62 -15.459313 -0.001523  30.408377
4    4551.65   0.009028  0.007581   0.005247
5    4554.46   0.001861  0.010692   0.027969
6    6828.60 -10.604568 -0.000590  21.084799
7    6839.84  -0.003466 -0.001870   0.010169
8    6842.04 -32.751551 -0.002514  65.118329
9    6842.69  18.293519 -0.002158  36.385884
10   6843.66   0.006386 -0.002468   0.034995
11   6855.72   0.020803  0.000886   0.040529

As it's clearly evident in the above table that some of the values in the column mad and median are very big(outliers). So i want to remove the rows which have these very big values.

For example in row3 the value of mad is 30.408377 which very big so i want to drop this row. I know that i can use one line to remove these values from the columns but it doesn't removes the complete row

df[np.abs(df.mad-df.mad.mean()) <= (3*df.mad.std())]

But i want to remove the complete row.

How can i do that?

outliers pandas python-2.7 dataframe
2 Answers

Predicates like what you've given will remove entire rows. But none of your data is outside of 3 standard deviations. If you tone it down to just one standard deviation, rows are removed with your example data.

Here's an example using your data:

import pandas as pd
import numpy as np

columns = ["wave", "mean", "median", "mad"]
data = [
    [4050.32, -0.016182, -0.011940, 0.008885],
    [4208.98, 0.023707, 0.007189, 0.032585],
    [4508.28, 3.662293, 0.001414, 7.193139],
    [4531.62, -15.459313, -0.001523, 30.408377],
    [4551.65, 0.009028, 0.007581, 0.005247],
    [4554.46, 0.001861, 0.010692, 0.027969],
    [6828.60, -10.604568, -0.000590, 21.084799],
    [6839.84, -0.003466, -0.001870, 0.010169],
    [6842.04, -32.751551, -0.002514, 65.118329],
    [6842.69, 18.293519, -0.002158, 36.385884],
    [6843.66, 0.006386, -0.002468, 0.034995],
    [6855.72, 0.020803, 0.000886, 0.040529],
]

df = pd.DataFrame(np.array(data), columns=columns)
print("ORIGINAL: ")
print(df)

print()

res = df[np.abs(df['mad']-df['mad'].mean()) <= (df['mad'].std())]
print("REMOVED: ")
print(res)

this outputs:

ORIGINAL:
       wave       mean    median        mad
0   4050.32  -0.016182 -0.011940   0.008885
1   4208.98   0.023707  0.007189   0.032585
2   4508.28   3.662293  0.001414   7.193139
3   4531.62 -15.459313 -0.001523  30.408377
4   4551.65   0.009028  0.007581   0.005247
5   4554.46   0.001861  0.010692   0.027969
6   6828.60 -10.604568 -0.000590  21.084799
7   6839.84  -0.003466 -0.001870   0.010169
8   6842.04 -32.751551 -0.002514  65.118329
9   6842.69  18.293519 -0.002158  36.385884
10  6843.66   0.006386 -0.002468   0.034995
11  6855.72   0.020803  0.000886   0.040529

REMOVED:
       wave       mean    median        mad
0   4050.32  -0.016182 -0.011940   0.008885
1   4208.98   0.023707  0.007189   0.032585
2   4508.28   3.662293  0.001414   7.193139
3   4531.62 -15.459313 -0.001523  30.408377
4   4551.65   0.009028  0.007581   0.005247
5   4554.46   0.001861  0.010692   0.027969
6   6828.60 -10.604568 -0.000590  21.084799
7   6839.84  -0.003466 -0.001870   0.010169
10  6843.66   0.006386 -0.002468   0.034995
11  6855.72   0.020803  0.000886   0.040529

Observe that rows indexed 8 and 9 are now gone.

Be sure you're reassigning the output of df[np.abs(df['mad']-df['mad'].mean()) <= (df['mad'].std())] as shown above. The operation is not done in place.

user_36917830

Doing df[np.abs(df.mad-df.mad.mean()) <= (3*df.mad.std())] will not change the dataframe. But assign it back to df, so that:

df = df[np.abs(df.mad-df.mad.mean()) <= (3*df.mad.std())]
user_102921700
Related