Smoothing of Data

[munleyj]

I have 35 years of temperature data. I have the average of each day in a column. However, my data is noisy. How can I smooth the data? Do I need a filter and how would I do this?b I would like to send you the file but I can not attach files. PLEASE HELP!

 

[Gerald Higgins]

No thanks.

Try posting a small sample of your data directly into a post, and alongside it, show what you want the data to be converted to.

 

[munleyj]

[TABLE="width: 207"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]Like this. But the numbers still junp around a little. It should go consistantly up.

AVG.HIGH
[/TD]
[TD][/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]------[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]42[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]43[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]42[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]39[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]37[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]39[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]39[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]41[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]41[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD="align: right"]39[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD="align: right"]42[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD="align: right"]42[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD="align: right"]42[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD="align: right"]43[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD="align: right"]44[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD="align: right"]46[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD="align: right"]47[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]43[/TD]
[/TR]
[TR]
[TD="align: right"]47[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]43[/TD]
[/TR]
[TR]
[TD="align: right"]47[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]43[/TD]
[/TR]
[TR]
[TD="align: right"]46[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD="align: right"]42[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD="align: right"]46[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD="align: right"]46[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD="align: right"]43[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]44[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

Sorry, I just don't understand what you want.

In post #12, I see three columns of numbers.
Does each column represent something different ?
If so, what ?
I could understand it if you had two columns, one of original data and one showing what you want to convert it to.
But three ?

Please, you need to be really really clear about what you want.

 

[munleyj]

From left to right, the first column is the average high for each day. The second column is where I was trying to smooth in the first column using the median formula. The third column is supposed to be the smooth data of the first column. All I want to do is smooth the first column.

 

[Gerald Higgins]

OK.

So what is the logic behind the manipulation you have done to the source data ?
Is there some maths behind it, or is it your own subjective view of what the numbers "should" be ?

If it's just your own subjective view, then I don't see how we can help you.

Are you familiar with concepts like regression analysis ?
They might help you, for example
Regression analysis - Wikipedia, the free encyclopedia

I'm not saying this type of regression analysis is right for you, I don't know enough about what you're trying to do.

My point is, there are several (indeed many) statistical methods of "smoothing" data like yours.

You need to decide for yourself which of these is best for you.

Then, if you're having trouble doing the actual calculations, we might be able to help you do them in Excel.

ALSO, bear in mind that standard Excel charts include trend options.

 

[munleyj]

The Data in the first column is a simple average of actual high temperatures from 1977-2012. I am trying to smooth the day to to look like what is in the second column. One that is done,, the third column can be deleted.

 

[Gerald Higgins]

So how did you get the numbers in the second column ?

 

[munleyj]

By doing a MEDIAN of numbers within the range of a number in the first column. I was told by the Climate Precition Center and from Golden Gate Weather that that was not good enough.

 

[Gerald Higgins]

I'm really sorry, please don't take this personally, but I feel like I'm bashing my head against a brick wall in this thread.

In post #16, you said you want to produce the numbers in the second column.

Then in post #18, you're saying that the numbers in the second column are "not good enough" which implies you don't want the numbers in the second column.

I've got to give up on this one I'm afraid, unless you can be REALLY REALLY CLEAR about what EXACTLY you want to do.

If you want any more help from me, please read post #15 thoroughly.
Also, if you're trying to satisfy the requirements of the "Climate Precition (?) Center" and "Golden Gate Weather", how about finding out what they consider to be good enough, and then sharing that with us ?

Maybe other people on the board have a better idea of what is required here.

Good luck !

 

[munleyj]

Pretend the 2nd and the third column are not there. I TRIED smoothing the data in the first column in the second and third column but I did not do it right. I just want to smooth the data in the first column to look SOMETHING LIKE what is in the 2nd column.