custom function to extract and visualize outliers in python

Question

For the dataframe below,I want to write a function that

I. extract the outliers for each column and export the output as a csv file (I need help with this one)

II. visualize using boxplot and export as pdf file

Outlier definition: as boundaries ±3 standard deviations from the mean

OR

as being any point of data that lies over 1.5 IQRs below the first quartile (Q1) or above the third quartile (Q3)in a data set. High = (Q3) + 1.5 IQR Low = (Q1) – 1.5 IQR

See below for the dataset and my attempt :

# dataset 
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
  
# intialise data of lists.
data = {'region':['R1', 'R1', 'R2', 'R2', 'R2','R1','R1','R1','R2','R2'],
         'cost':[120.05, 181.90, 10.21, 133.01, 311.19,2003.4,112.4,763.2,414.8,812.5],
        'commission':[110.21, 191.12, 190.21,15.31, 245.09,63.41,811.3,10.34, 153.10, 311.17],
        'salary':[10022,19910, 19113,449999, 25519,140.29, 291.07, 390.22, 245.09, 4122.62],
        'revenue':[14029, 29100, 39022, 24509, 412271,110.21, 191.12, 190.21, 12.00, 245.09],
        'tax':[120.05, 181.90, 10.34, 153.10, 311.17,52119,32991,52883,69359,57835],
         'debt':[100.22,199.10, 191.13,199.99, 255.19,41218, 52991,1021,69152,79355],
         'income': [43211,7672991,56881,211,77342,100.22,199.10, 191.13,199.99, 255.19],
          'rebate': [31.21,429.01,538.18,621.58,6932.5,120.05, 181.90, 10.34, 153.10, 311.17],
        'scale':['small','small','small','mid','mid','large','large','mid','large','small']
        }
  
# Create DataFrame
df = pd.DataFrame(data)
  
# Print the output.
df

############## my attempt ####################


def outlier_extractor(data):
    # select numeric columns
    numeric_columns = data.select_dtypes(include=np.number).columns.tolist()
    #(I)Extract and export outliers as csv..... I need help with this one
    
    #(II) boxplot visualization 
    
    plt.figure(figsize=(10, 9))
    
    for i, variable in enumerate(numeric_columns):
        plt.subplot(4, 4, i + 1)
        plt.boxplot(data[variable],whis=1.5)
        plt.tight_layout()
        plt.title(variable)
    plt.savefig('graph_outliers.pdf')
    plt.show()

# driver code 
outlier_extractor(df)

Please comment and share your full code. Thanks in advance

@SalvatoreDanieleBianco: You can use outlier High = (Q3) + 1.5 IQR Low = (Q1) – 1.5 IQR or Outlier boundaries ±3 standard deviations from the mean — RayX500, Aug 08 '22 at 08:09
OK got it. So you want detect outlier in the measurement, not in the entire row. — Salvatore Daniele Bianco, Aug 08 '22 at 08:15
https://stackoverflow.com/questions/34782063/how-to-use-pandas-filter-with-iqr duplicated question from this one — lucky6qi, Aug 08 '22 at 08:16
@lucky6qi I want to export as csv file, it is not duplicated, I want to include in a function — RayX500, Aug 08 '22 at 08:18
@SalvatoreDanieleBianco, Yes outliers value in each column and export as a csv file — RayX500, Aug 08 '22 at 08:19
@lucky6qi, I want filter for all numeric columns, the link you shared is just for an individual column, hence why I am using a function — RayX500, Aug 08 '22 at 08:21
you can have a for loop to go over all columns, and using the function in the link for every column. — lucky6qi, Aug 08 '22 at 08:30

Salvatore Daniele Bianco · Accepted Answer · 2022-08-08T09:24:10.373

1

def outlier_extractor(data):
    numeric_data = data.select_dtypes(include=np.number)
    Q1, Q3 = numeric_data.quantile(.25), numeric_data.quantile(.75)
    IQR = Q3-Q1
    numeric_data[:] = np.where((numeric_data > Q3+1.5*IQR)|(numeric_data < Q1-1.5*IQR), np.nan, numeric_data)
    numeric_data.apply(lambda series:series.dropna().to_csv(series.name+".csv"))
    
    plt.figure(figsize=(10, 9))
    for i, variable in enumerate(numeric_columns):
        plt.subplot(4, 4, i + 1)
        plt.boxplot(data[variable],whis=1.5)
        plt.tight_layout()
        plt.title(variable)
    #plt.savefig('graph_outliers.pdf')
    plt.show()

outlier_extractor(df)

Note that the apply function saves each filtered column in one different csv file. From your description I though that this was your task.

Note also that you don't need the seaborn package

EDIT

To export all the filtered dataframe with missing values replacing the ouliers you have to replace the to_csv row with:

numeric_data.to_excel("filtered_numeric_data.xlsx")

edited Aug 08 '22 at 09:24

answered Aug 08 '22 at 08:44

Salvatore Daniele Bianco

2,496
1
8
22

works good, however, I want the output as one dataframe, then exported as a single csv file. Your code currently exports multiple csv files. Thanks – RayX500 Aug 08 '22 at 08:51
With corresponding column names – RayX500 Aug 08 '22 at 08:52
How can I export a dataframe with missing values? You want to replace the outliers with `NaN` for example? – Salvatore Daniele Bianco Aug 08 '22 at 09:06
Yes. You can repalce with `NaN` or alternatively highlight the outliers with a red color – RayX500 Aug 08 '22 at 09:10
`csv` format is a text only format, so you can't specify anything about the style, font, color etc... The only solution is to drop outlier with `NaN`. – Salvatore Daniele Bianco Aug 08 '22 at 09:13
True, I forgot that about `.csv`, you can highlight and then export as `.xlsx` – RayX500 Aug 08 '22 at 09:15
for this you will need the `openpyxl` package. Do you know haow to use it? – Salvatore Daniele Bianco Aug 08 '22 at 09:21
If its not too much, you can adjust the code to export as `xlsx`,otherwise, you can just export the dataframe with with outlier values and non outlier values as `NaN` in `csv` – RayX500 Aug 08 '22 at 09:22
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/247115/discussion-between-salvatore-daniele-bianco-and-rayx500). – Salvatore Daniele Bianco Aug 08 '22 at 09:27

custom function to extract and visualize outliers in python

1 Answers1