Exponential Filter/Smoothing
#1
The problem...
The problem is you've got noisy time series data. Ideally you would like to plot it on graph to show the average trend or compare it with another variable. BUT its too noisy and clutters the graph if you plot it with markers and lines. With just markers it appears as a scatter cloud?
Aha, then you see that Excel has the option to add a "trendline". But you can't seem to find one that does the data justice -or- its just plain wrong in places. The "moving average" seems the better one of the options, but to get good smoothing you have to average many periods. That has some bad effects: it delays when changes occurs, especially when comparing to another variable (plotted in another series).
The problem...
The problem is you've got noisy time series data. Ideally you would like to plot it on graph to show the average trend or compare it with another variable. BUT its too noisy and clutters the graph if you plot it with markers and lines. With just markers it appears as a scatter cloud?
Aha, then you see that Excel has the option to add a "trendline". But you can't seem to find one that does the data justice -or- its just plain wrong in places. The "moving average" seems the better one of the options, but to get good smoothing you have to average many periods. That has some bad effects: it delays when changes occurs, especially when comparing to another variable (plotted in another series).
Resist the temptation of moving average!!!
A solution ...
A nice easy way to sort out this data is to apply a simple exponential filter or smoothing. There are better, but this is simple and for 80% of the time will work great.
How does it work?
I will illustrate this in the spreadsheet, but the maths is simply like this:
New Smoothed value = (1-factor) * actual data + (factor)*(previous smoothed value)
or:
y_n=(1-k)*x_n + k*y_n-1
y = smoothed value
x = actual (noisy) data
k = smoothing factor, a real number between 0 and 1. 0= no smoothing
How to set this up in a spreadsheet:
1. Estimate a value for the starting smoothed average. I usually use the first raw actual data point or take an average of the first few points.
2. Setup the filter to the right of the actual data points.
3. Reference the smoothing constant "k" as cell using absolute $ reference eg. $b$1. This will allow you to adjust it easily.
4. Copy the filter down so that you have two columns- to the left your original data and to your right the smoothed values.
5. Play and adjust "k" to suit your needs. As a first guess use 0.5. I commonly use anywhere from 0.5 to 0.85. Be wary of values above this as you could be over smoothing, but it does depend on your application.
6. For graphing purposes I suggest keeping your original raw data series (but use markers only, i.e. scatter plot) and then plotting the smoothed trend as a second series (lines only).
http://www.4shared.com/file/11752632/8565b041/exponential_filter.html
A solution ...
A nice easy way to sort out this data is to apply a simple exponential filter or smoothing. There are better, but this is simple and for 80% of the time will work great.
How does it work?
I will illustrate this in the spreadsheet, but the maths is simply like this:
New Smoothed value = (1-factor) * actual data + (factor)*(previous smoothed value)
or:
y_n=(1-k)*x_n + k*y_n-1
y = smoothed value
x = actual (noisy) data
k = smoothing factor, a real number between 0 and 1. 0= no smoothing
How to set this up in a spreadsheet:
1. Estimate a value for the starting smoothed average. I usually use the first raw actual data point or take an average of the first few points.
2. Setup the filter to the right of the actual data points.
3. Reference the smoothing constant "k" as cell using absolute $ reference eg. $b$1. This will allow you to adjust it easily.
4. Copy the filter down so that you have two columns- to the left your original data and to your right the smoothed values.
5. Play and adjust "k" to suit your needs. As a first guess use 0.5. I commonly use anywhere from 0.5 to 0.85. Be wary of values above this as you could be over smoothing, but it does depend on your application.
6. For graphing purposes I suggest keeping your original raw data series (but use markers only, i.e. scatter plot) and then plotting the smoothed trend as a second series (lines only).
http://www.4shared.com/file/11752632/8565b041/exponential_filter.html
1 comment:
For some data applications a moving average could be good enough, so it really depends on your data.
Post a Comment