Find the outliers in data and replace them with mean of two consecutive values before and after that-CodePudding

I have a data frame with two rows. I want to replace the outlier in each rows with the mean of value before and after it. Honestly, I dont know how to exactly find the outliers. For example, for the first row, the values 20, 100, -10 are outlier. Since they are far from the mean values of most samples.

df = pd.DataFrame()

df['id'] = [1, 2]
df['val1']= [5, 12]
df['val2']= [6, 12]
df['val3']= [7, 12]
df['val4']= [20, -20]
df['val5']= [5, 12]
df['val6']= [4, 13]
df['val7']= [8, 10]
df['val8']= [9, 12]
df['val9']= [100, 100]
df['val10']= [-10, 12]
df['val11']= [5, 13]
df['val12']= [8, 12]


       id val1 val2 val3 val4 val5 val6 val7 val8   val9 val10 val11 val12
   0    1   5    6      7   20  5     4   8     9    100    -10  5      8
   1    2   12   12     12  -20 12    13  10    12   100    12.  13     12

The desired output is:

id  val1 val2 val3 val4 val5 val6 val7 val8 val9 val10 val11 val12
0   1    5     6    7    6      5   4   8   9   7   7   5    8
1   2    12  12     12   12     12  13  10  12  12  12  13  12

Honestly, I dont have any idea how to solve this. Could you please help me with that?

CodePudding user response：

You might want to look at SciPy's Stats and ZScore to help find outliers. I was able to whip something up with the assumption that all negative values are outliers and that any value with a zscore of 2 or greater is also an outlier. I'm not sure how all of your data will look, so playing with the max zscore value might be necessary. Anyway, with the data you provided, I was able to get the same output you wanted:

import pandas as pd
from scipy import stats
import numpy as np

df = pd.DataFrame({'id'  : [1, 2],'val1':[5, 12],'val2':[6, 12],'val3':[7, 12],
                   'val4':[20, -20],'val5':[5, 12],'val6':[4, 13],'val7':[8, 10],
                   'val8':[9, 12],'val9':[100, 100],'val10':[-10, 12],'val11':[5, 13],'val12':[8, 12]})

finalDF = pd.DataFrame()
for dfIndex in range(len(df)):
    # melt the row to work with it like a list
    meltList = df[dfIndex:dfIndex 1].melt().value  
    
    # Loop through the melted list removing all elements that have a zscore of more than 2
    badList = []    
    while True:
        zscores = stats.zscore(meltList)
        bad = np.unique([zscores[zscores==x].index for x in zscores if x > 2])
        if len(bad) < 1: # if there are no more zscores greater than two, stop the loop
            break
        for x in bad:
            badList.append(x)
            del meltList[x]
    # Find all values that are negative, as those are outliers too
    bad = [meltList[meltList==x].index for x in meltList if x < 0]
    for x in bad:
        badList.append(x[0])
    
    # Get the original melted list again (without the removed indexes)    
    meltList = df[dfIndex:dfIndex 1].melt().value
    
    # Sort the bad values list so that it can be used for the next loop
    badList.sort()
    # Change the outlier to the mean of the values it is sandwiched between
    # If the previous or next value are also outliers, find the next value that isn't an outlier
    for x in badList:
        prevVal = meltList[x-1]
        nextVal = meltList[x 1]

        n=1
        while x-n in badList:
            n  = 1
            prevVal = meltList[x-n]
        n=1
        while x n in badList:
            n  = 1
            nextVal = meltList[x n]
        meltList[x] = (prevVal   nextVal)/2

    # Get the original melted dataframe as a new variable
    newDF = df[dfIndex:dfIndex 1].melt()
    # Change the values in the new dataframe to the ones without outliers
    newDF.value = meltList
    # Set the index to the same value to be used to pivot the table
    newDF.index=[dfIndex]*len(newDF)
    # Pivot the now outlier free dataframe back to its original format
    newDF = newDF.pivot(index=None,columns='variable', values="value")
    # Combine all the rows
    finalDF = pd.concat([finalDF, newDF])

# Put the columns back in the correct order
finalDF[['id','val1','val2','val3','val4','val5','val6','val7','val8','val9','val10','val11','val12']]

Output:

id  val1 val2 val3 val4 val5 val6 val7 val8 val9 val10 val11 val12
0   1    5     6    7    6      5   4   8   9   7   7   5    8
1   2    12  12     12   12     12  13  10  12  12  12  13  1