Remove Outliers from Dataframe using pandas in Python

Question

I would like to remove outliers from my dataset. It looks like this:

                  time       Gbps
0  2018-11-20 00:00:00  29.821748
1  2018-11-20 01:00:00  38.620987
2  2018-11-20 02:00:00   0.010000
3  2018-11-20 00:00:00  29.821748
4  2018-11-20 01:00:00  38.620987
5  2018-11-20 02:00:00   0.010000

As you take a look at this table, you can see that number 5 and 2 are the outliers. I wrote a interquartile range (IQR) method to remove them. However, it does not work. I don't know if I do something wrong in Pandas/Python, or it's the fact I do something wrong in statistics. Any ideas? The result from this function is the same frame as I had at the beginning.

def IQR(data):
    q1 = data['Gbps'].quantile(0.25)
    q3 = data['Gbps'].quantile(0.75)
    iqr = q3 - q1
    fence_low = q1 - 1.5 * iqr
    fence_high = q3 + 1.5 * iqr
    cleaned_data = data.loc[(data['Gbps'] > fence_low) & (data['Gbps'] < fence_high)]
    return cleaned_data

data = {
    'time': ['2018-11-20 00:00:00', '2018-11-20 01:00:00', '2018-11-20 02:00:00', '2018-11-20 00:00:00', '2018-11-20 01:00:00', '2018-11-20 02:00:00'],
  'Gbps': [29.8217476333333333, 38.6209872666666667, 0.01, 29.8217476333333333, 38.6209872666666667, 0.01]
}

df1 = pd.DataFrame(data, columns = ['time', 'Gbps'] 
cleaned1 = IQR(df1)
print(cleaned1)

Answer 1

You just don't have enough data in your dataset.

More accurately - your outliers are not affected by your filter function.

fence_low is equal to -35.974423375

fence_high is equal to 79.858537625

So the values of 0.01 are lying within this range.

I created an example notebook for you to show the difference you can check it out here: https://github.com/OzmundSedler/learning/blob/master/IQR%20example.ipynb

Long story short - here is your data:

And here is the data, where I have added 15 more values in the range of [20:40]. In that case, 0.01 points are classified as outliers and filtered correctly.

Answer 2

def remove_outliers(df, out_cols, T=1.5, verbose=True):
    # Copy of df
    new_df = df.copy()
    init_shape = new_df.shape
    # For each column
    for c in out_cols:
        q1 = new_df[c].quantile(.25)
        q3 = new_df[c].quantile(.75)
        col_iqr = q3 - q1
        col_max = q3 + T * col_iqr
        col_min = q1 - T * col_iqr
        # Filter data without outliers and ignoring nan
        filtered_df = new_df[(new_df[c] <= col_max) & (new_df[c] >= col_min)]
        if verbose:
            n_out = new_df.shape[0] - filtered_df.shape[0] 
            print(f" Columns {c} had {n_out} outliers removed")
        new_df = filtered_df
            
    if verbose:
        # Print shrink percentage
        lines_red = df.shape[0] - new_df.shape[0]
        print(f"Data reduced by {lines_red} lines, or {lines_red/df.shape[0]*100:.2f} %")
    return new_df