What to replace outliers with? (supermarket transaction data) 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 = &quot;Gold Customer&quot;.
Jane Doe's average spend is 25% of the overall average spend = &quot;Bronze&quot;
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 &quot;Gold&quot; 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']&gt;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:
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 &gt; $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