What to replace outliers with? (supermarket transaction data)

Question

I have a transaction dataset from a supermarket. Let's say the average spend is $50.

I want to get each customer's average spend and rank them based on where they fall based on this $50 average spend.

For example:

John Doe's average spend is 150% of the overall average spend = "Gold Customer".

Jane Doe's average spend is 25% of the overall average spend = "Bronze"

etc etc

Now to calculate the overall average spend of the supermarket, I want to get rid of outliers. This is a grocery store, but they may have a TV on sale every now and then. So there are some single transactions that may be $600+. I want to get rid of these.

The question is, what do I replace them with?

I look for transactions 3 standard deviations above the mean. These are my outliers.

I don't want to replace them with the mean / median. I could be losing some of my "Gold" customers if I erase their bigger purchases and replace them with $50.

Could I replace the outliers with mean + 3*std_dev ?

I'm using python, so the current code is:

# set threshold above which transaction will be labeled an outlier
# this is the average spend plus 3 times standard dev

value_threshold = (df['amount'].mean()+(df['amount'].std()*3))


# now replace any outlier with the value threshold. 
# this will ensure any big spenders stay big spenders so I can rank them accordingly

df['amount'] = np.where(df['amount']>value_threshold ,value_threshold ,df['amount'])

Does my approach make sense or am I breaking any rules?

Should I be using the median instead with interquartile ranges to find outliers?

Distribution:

After removing the outliers using my method above. Notice we still have big spenders in the data to the right of the chart. (my gold customers)

Bonus

Boxplot of my transaction data before fixing the outliers. It's horrific:

Answer 1

Simplest approach

First a very fast and perhaps practical approach: just remove them without replacing them!

From your bar chart, it seems you have a lot of transactions - several hundred thousand. Removing a few hundred (I can't even see a bar for the > $600 transactions) and not replacing them wouldn't mean the remaining data is unusable. Replacing those transaction e.g. using the mean of the distribution, is basically trying to make sure they don't have any significant effect on the model; so what is the point in replacing them?

Looking at your frequency chart, I would be interested to know what appears in the final large peak. Could you just remove those transaction?

You could give this a go and move onto the modelling, see if you get sensible results - if not, come back to investigate further.

---

Back to statistical methods

Using 3 standard deviations isn't a bad approach - assuming your data is normally distributed, it means you only remove 0.3% of the data. The issue is perhaps that your distribution of transaction amounts does not look normally distributed - it looks more like a beta distribution(the orange line):

You could therefore try your approach of removing data outside the distribution, by computing the parameters of the beta distribution, and using those instead of the normal mean/std.

To do this, check out scipy.stats.beta, and be aware that you should probably normalise your transactions to the range [0, 1.0] (see Scikit-Learn's StandardScaler). Here is a thread on understanding the output of your beta distribution.

---

Impute

If you simple treat your outliers as missing data, there are some nice ideas and explanations about filling missing gaps in your data (a.k.a. data imputation) in this well-known book: Elements of Statistical Learning (pdf)$^1$ - see section 9.6.

$^1$Authors: Trevor Hastie, Robert Tibshirani, Jerome Friedman

Answer 2

You can use any of these below for replacing the outliers

Quantile-based Flooring and Capping In this technique, we will do the flooring (e.g., the 10th percentile) for the lower values and capping (e.g., the 90th percentile) for the higher values. The lines of code below print the 10th and 90th percentiles of the variable 'amount', respectively. These values will be used for quantile-based flooring and capping.

print(df['amount'].quantile(0.10))
print(df['amount'].quantile(0.90))

or

Top Coding means capping the maximum of the distribution at an arbitrary set value. A top coded variable is one for which data points above an upper bound are censored. By implementing top coding, the outlier is capped at a certain maximum value and looks like many other observations.