# Smooth Non Normally Distributed Data

I have ~16,000 probability sets of goals scored [maximum of 12] like below [some % are rounded hence do not add up to 100%]:

Goals  0    1    2    3    4    5    6    7    8    9   10   11   12
-------------------------------------------------------------------------
1:     9   15   24   23   14    8    4    2  0.5  0.2...    AVERAGE TEAM
2:    29   25   26   11    -    -    -    9    -    -    -
3:     4    9   32   33   10   10    2    1    -    -    -
4:    22   36   17   16    9    -    -    -    -    -    -  BAD TEAM
5:     3   10   11   22   23   19    9    1    1    -    -  GOOD TEAM

Tacky illustrative examples of objective [on the gentle side]:
2:   27   29   24   10    1    -    1    7    1    -    -
3:    4   11   30   31   13    8    3    1    -    -    -
4:   No problem other than 5 & 6 goals should perhaps greater than 0%

• 2: Is a perfect example: 9% for 7 goals but 0% for 4, 5, 6 & 8 goals is a serious problem. 29%, 25%, 26% is a smaller problem too that would greatly benefit from a light touch of smoothing.
• 3: It is hard to tell, should 2 & 3 goals peak that high? 4 & 5 goals probably should not be the same expected frequency.

The data is in excel. I am not looking for best practise, just some simple methods that would scale well based on the number of sets. It would be nice if it was a little less hacky / tacky than "three median/mean methods."

Obviously a lot of interference might make pretty graphs [not the aim] but is not statistically sound and would not hold up in the future. Meddling with expected frequencies feels intuitively bad practise, and could be frowned upon. I respect that if that is community consensus, as it is now without smoothing data, the expected frequencies work BUT are worse than actually using the mean. This should not be case for soccer but maybe the 7% tail above 5.5 is not that bad. However I would like to try gently smoothing the data to see if I can improve things so that at least using frequencies is equals predictive power of using mean.

Edit: The objective would be to smooth each set [row] individually. The first row is merely there to illustrate the average distribution and that any smoothing should really maintain a positive skew. Each set is expected frequencies of a given teams performance for their next match against an average team. Whatever the smoothing, the aim would be that it is actually a better representation of the raw expected frequencies. The smoothing desired would be anything that could be achieved using excel functions / formulas [even if it requires 100+ helper columnns, not an issue, performance is the real inhibitor].

• The question is unclear. It looks like you have 16,000 observations of a 12-dimensional measure, or 16K 12-bar graphs.. What kind of smoothing are you looking for? The representative sample would be like an average of each column, yes?
– Karl
Jan 11, 2016 at 16:20

I don't think Smoothing is the right term for what you want. I think you want each row to look a little more like the first row; so just make a new row N equal to the average of row N and row 1. You can use a weighted average if you want to tune how much similarity is required. Then, a third sheet to re-normalize each row into a total of 100%.

This procedure will yield a new dataset that is partially the original, and partly 'smoothed' towards your expected value (row 1).

• I do "want each row to look a little more like the first row" but each set did not happen by chance. I want a similar skewed shape but not to dilute team's expected performance [eg revert back to mean]. By "representation" in the edit I do not mean appearances, I mean a better prediction. Set 2 is ridiculous to predict 0% chance of 6 goals, yet 7 goals as 9%. A hammer [method] needs to hit that down & realistically smoosh some of expected frequency to 5, 6, 8 & 9 goals but respect that most of the percentage that oozes from 7 goals should go towards 6 goals as 9 goals is very unlikely.