Looping through a Dataframe to remove outliers (calling function) by city

user_10610631 0
Viewed 453

I have a data frame with Date, city, sales -

Date            City  Sales
2008-01-01      C1.   10000
2008-01-01      C2    2000
2008-01-02      C1.   13000
2008-01-02      C2    5000

and so on...

I have a function for outliers -

def detect_discrete_outliers(data):
    outliers=[]
    threshold=3
    mean = np.mean(data)
    std =np.std(data)
    for i in data:
        z_score= (i - mean)/std 
        if np.abs(z_score) > threshold:
            outliers.append(i)
    return outliers

Now, I want to use this outlier function to remove outliers from df

detect_discrete_outliers(df['sales'])
df = df[~ df['sales'].isin(detect_discrete_outliers(df['sales']))]

This removes the outliers in sales

However, I think its not accurate to do that, I need to remove outliers for each city and not overall outliers.

Can anyone suggest what's the best way to do this?

dataframe for-loop function python
1 Answers

You will want to use a groupby and custom transform function that takes your data series and returns a boolean Series of whether or not each value within the group is an outlier.

Your outliers function can be simplified quite a bit since pandas makes doing math across the whole Series quite easy

def detect_discrete_outliers(data):
    threshold = 3
    mean = data.mean()
    std = data.std()
    z_score = (data - mean)/std 
    outliers = abs(z_score) > threshold

    return outliers

We can now do a groupby on the 'City' column and the outlier function to return a boolean Series showing which rows in 'Sales' are outliers within their own groups

That boolean Series (really it's inverse, since we want to discard outliers) can then be used to index the original dataframe

df = df[~df.groupby('City')['Sales'].transform(detect_discrete_outliers)]

This should also run stupidly faster than your original code snippet as well since it's now using all of speed boost pandas can give over OG python for loops

user_157872350
Related