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.
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"
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?
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)
Boxplot of my transaction data before fixing the outliers. It's horrific: