Smoothing algorithm for real-world data

[darren0123]

Hi
I'd appreciate your help.
I have a bunch of livestock with an ear sensor on each, that sends me data about how much heat is going out of the ear.

I wanna take this data from each livestock and smooth it.

My question is, how to know which smoothing algorithm I should use to get an authentic reliable smoothed data?

Thank you.

 

[PeroK]

That's something a bit different for a first post!

 

[darren0123]

Thank you so much for the welcome
Yes, that is the problem I'm facing and I'd love the help of the forum

 

[berkeman]

I wanna take this data from each livestock and smooth it.

What do you mean by "smooth" the data? Do you just mean to take an average? Or do you want to filter the data from each cow to get more consistent data to then perform an analysis on?

What form is the data in? Is it already in an Excel spreadsheet, or do you get it in Comma Separated Variable (CSV) format and need to import it into Excel? What units are the data in (Fahrenheit or Celsius), and to what precision?

 

[PeroK]

Thank you so much for the welcome
Yes, that is the problem I'm facing and I'd love the help of the forum

I'm not sure where the best place would be for your question. It might get moved from Linear Algebra at some point.

 

[darren0123]

What do you mean by "smooth" the data? Do you just mean to take an average? Or do you want to filter the data from each cow to get more consistent data to then perform an analysis on?

What form is the data in? Is it already in an Excel spreadsheet, or do you get it in Comma Separated Variable (CSV) format and need to import it into Excel? What units are the data in (Fahrenheit or Celsius), and to what precision?

Hei Berkeman
thank you so much for the detailed reply.

I mean to treat the data from each cow individually, and smooth it to (like you said) get more consistent data to then perform an analysis on.

The data is on Excel sheet, in Celsius units.

 

[darren0123]

What do you mean by "smooth" the data? Do you just mean to take an average? Or do you want to filter the data from each cow to get more consistent data to then perform an analysis on?

What form is the data in? Is it already in an Excel spreadsheet, or do you get it in Comma Separated Variable (CSV) format and need to import it into Excel? What units are the data in (Fahrenheit or Celsius), and to what precision?

Hei,
The precision is 0,01

 

[berkeman]

I mean to treat the data from each cow individually, and smooth it to (like you said) get more consistent data to then perform an analysis on.

Does the data vary by time of day, etc., so that you just want to low-pass filter the data to get smoother curves? Or is it pretty constant throughout each day?

You can make a simple lowpass filter running calculation in Excel (by averaging several adjacent entries for each new "smoothed" entry. But you can also get a lot more sophisticated by doing a more complicated weighted average of nearby cells. This old PF thread talks a little bit about that, but depending on how much you want to learn about signal processing, it may be best to just use the simple averaging algorithm.

https://www.physicsforums.com/threads/low-pass-filter-in-excel.186980/

Can you post a screenshot of some of the data so we can see what it looks like? Use the "Attach files" link to upload a PDF or JPEG copy of the screenshot.

 

[darren0123]

Thank you so much Berkeman.
I'm going to dive into your suggestion of weighted average of nearby cells.
The data changes by the time of day.

I'll post up tomorrow a screenshot of the data.

I'd like to ask you one more thing please.
How would involve and take into account the standard deviation of the data when smoothing the data?

 

[berkeman]

How would involve and take into account the standard deviation of the data when smoothing the data?

I'm not sure what you are asking, sorry. You can use the standard Excel math functions to calculate the various averages of the data and the standard deviation of the data.

From a simple standpoint, for the smoothing function you would take the average of the current time cell and several previous time cells to get the new time cell value. How far back you extend that averaging depends on how quickly the data vary. And as I mentioned you can use a weighted average of the previous/older data, but the technique for calculating the coefficients for that weighted average involves some DSP math. There are standard tools to help calculate those coefficients for you however; I'll see if I can dig up a typical calculator tool/website for you.

So just to be sure you understand what I'm suggesting (for the simple non-weighed average case), you would take your column of time sampled data for a cow, say "Daisy", as your 2nd column in the Excel spreadsheet, say from cell B1 to cell B100. Put the numbers 1:100 in cells A1:A100 for reference for plotting later (or put in the evenly-spaced sample timestamps instead if you want to plot these curves versus time instead of versus sample number). BTW, in case you don't know the trick, make A1=1 and A2=A1+1 and then Fill-Down for cells A2:A100.

Then starting in cell C3, you make it the average of cells B1,B2,B3. Then cell C4 is the average of cells B2,B3,B4. And so on. Once you define the equation for cell C3, you can just copy cell C3 and paste it down from C4:C100. Does that make sense?

You can then Plot columns B and C versus column A to see if the smoothing is working well for you.

 

[berkeman]

BTW, here is a typical (simple) digital filter coefficient calculator page:

https://leventozturk.com/engineering/filter_design/

It may be more than you want to learn right now, but we can use it later if you decide you want to get more quantitative about setting up the polynomial for your lowpass filter.

 

[darren0123]

Berkeman, thank you so much!!
You dedicated me much time and very valuable information.

As I promised, here is the data.
The average is what I get from the sensor (heat that exits the ear of the cow, averaged over an hour).
The other graphs have smoothing algorithm applied on the original average graph.

By having the data, if you have more inputs for me to follow, I'd very appreciate it as well.

Thank you!

 

[berkeman]

The average is what I get from the sensor (heat that exits the ear of the cow, averaged over an hour).
The other graphs have smoothing algorithm applied on the original average graph.

Nice job smoothing! How did you do that smoothing?