MATLAB Central Newsreader - applying functions to grouped data

applying functions to grouped data

Anna Chen — Wed, 04 Nov 2009 21:12:01 -0500

hello,
i'm a fairly inexperienced matlab user, and i had a question about applying functions to data based on certain groupings. for example, let's say i have the following data set:

Date Height Weight
1/31 30 20
1/31 23 43
1/31 20 50
2/28 10 40
2/28 40 30
3/31 90 40
3/31 10 30
3/31 40 60

i want to compute 2-month rolling averages of height and weight. so on 2/28, i want the averages of heights on 1/31 and 2/28, and averages of weights on 1/31 and 2/28. For 3/31, i want to do the same for 3/31 and 2/28. I will end up with something like this:

Date Rolling2-monthHeight Rolling2-monthWeight
2/28 24.6 36.6
3/31 38 40

What would be the best way to do this? I've been trying to use while loops, but the date format kind of messes that up. Thanks so much for your help!

Re: applying functions to grouped data

ade77 — Wed, 04 Nov 2009 23:25:04 -0500

you can use a moving average filter. Type doc filter.

Also, if you can include the year with your date, then it will be more appropriate, and you can change it to a date number.

Let me know if you need more help with the code.

> Date Height Weight
> 1/31 30 20
> 1/31 23 43
> 1/31 20 50
> 2/28 10 40
> 2/28 40 30
> 3/31 90 40
> 3/31 10 30
> 3/31 40 60
>
> i want to compute 2-month rolling averages of height and weight. so on 2/28, i want the averages of heights on 1/31 and 2/28, and averages of weights on 1/31 and 2/28. For 3/31, i want to do the same for 3/31 and 2/28. I will end up with something like this:
>
> Date Rolling2-monthHeight Rolling2-monthWeight
> 2/28 24.6 36.6
> 3/31 38 40

you can use a moving average filter. Type doc filter.

Also, if you can include the year with your date, then it will be more appropriate, and you can change it to a date number.

Let me know if you need more help with the code.

Re: applying functions to grouped data

Anna Chen — Thu, 05 Nov 2009 03:03:02 -0500

thanks for your response! actually, i'm having a problem even getting the dates in correctly to matlab. i'm importing a large file that has dates in the first column, and matlab will always separate it from the rest of the columns. how would i reincorporate this first column and apply the moving average filter? also, what if i wanted to apply some other moving function, other than averages?

thanks so much!

"ade77 " <ade100a@gmail.com> wrote in message <hct2gg$mm8$1@fred.mathworks.com>...
> you can use a moving average filter. Type doc filter.
>
> Also, if you can include the year with your date, then it will be more appropriate, and you can change it to a date number.
>
> Let me know if you need more help with the code.
>
> > Date Height Weight
> > 1/31 30 20
> > 1/31 23 43
> > 1/31 20 50
> > 2/28 10 40
> > 2/28 40 30
> > 3/31 90 40
> > 3/31 10 30
> > 3/31 40 60
> >
> > i want to compute 2-month rolling averages of height and weight. so on 2/28, i want the averages of heights on 1/31 and 2/28, and averages of weights on 1/31 and 2/28. For 3/31, i want to do the same for 3/31 and 2/28. I will end up with something like this:
> >
> > Date Rolling2-monthHeight Rolling2-monthWeight
> > 2/28 24.6 36.6
> > 3/31 38 40
>
>
> you can use a moving average filter. Type doc filter.
>
> Also, if you can include the year with your date, then it will be more appropriate, and you can change it to a date number.
>
> Let me know if you need more help with the code.

Re: applying functions to grouped data

ade77 — Thu, 05 Nov 2009 03:48:02 -0500

> thanks for your response! actually, i'm having a problem even getting the dates in correctly to matlab. i'm importing a large file that has dates in the first column, and matlab will always separate it from the rest of the columns. how would i reincorporate this first column and apply the moving average filter? also, what if i wanted to apply some other moving function, other than averages?
>
> thanks so much!

First save your orignal file before manipulating it. then create another copy.

Is your file an excel file? One easy way is to create ( in excel) three additional columns for year, month and day. I am not sure how good your excel is.

The problem with excel is that when the date is in 'date format' in excel, MATLAB has problem recognizing that format.

So once you import it into MATLAB, let me know. Actually, the actual code of your moving average is very easy to write.

If you do not know how to create those columns in excel , let me know and I will give you the steps. Like new_col 1= YEAR(A1), new_col2 = MONTH(A1) etc

Re: applying functions to grouped data

Branko — Thu, 05 Nov 2009 09:34:01 -0500

"Anna Chen" <icedredtea@yahoo.com> wrote in message <hcsqn1$ju4$1@fred.mathworks.com>...
> hello,
> i'm a fairly inexperienced matlab user, and i had a question about applying functions to data based on certain groupings. for example, let's say i have the following data set:
>
> Date Height Weight
> 1/31 30 20
> 1/31 23 43
> 1/31 20 50
> 2/28 10 40
> 2/28 40 30
> 3/31 90 40
> 3/31 10 30
> 3/31 40 60
>
> i want to compute 2-month rolling averages of height and weight. so on 2/28, i want the averages of heights on 1/31 and 2/28, and averages of weights on 1/31 and 2/28. For 3/31, i want to do the same for 3/31 and 2/28. I will end up with something like this:
>
> Date Rolling2-monthHeight Rolling2-monthWeight
> 2/28 24.6 36.6
> 3/31 38 40
>
> What would be the best way to do this? I've been trying to use while loops, but the date format kind of messes that up. Thanks so much for your help!

Look at:
datenum
unique
accumarray

Branko

Re: applying functions to grouped data

Anna Chen — Thu, 05 Nov 2009 17:51:02 -0500

hello,
i changed my data to separate the dates. could you give me some tips on how to create a moving average filter? i think it would be very useful for a variety of things in the future.
thanks!

"ade77 " <ade100a@gmail.com> wrote in message <hcthti$43u$1@fred.mathworks.com>...
>
> > thanks for your response! actually, i'm having a problem even getting the dates in correctly to matlab. i'm importing a large file that has dates in the first column, and matlab will always separate it from the rest of the columns. how would i reincorporate this first column and apply the moving average filter? also, what if i wanted to apply some other moving function, other than averages?
> >
> > thanks so much!
>
> First save your orignal file before manipulating it. then create another copy.
>
> Is your file an excel file? One easy way is to create ( in excel) three additional columns for year, month and day. I am not sure how good your excel is.
>
> The problem with excel is that when the date is in 'date format' in excel, MATLAB has problem recognizing that format.
>
> So once you import it into MATLAB, let me know. Actually, the actual code of your moving average is very easy to write.
>
> If you do not know how to create those columns in excel , let me know and I will give you the steps. Like new_col 1= YEAR(A1), new_col2 = MONTH(A1) etc

Re: applying functions to grouped data

ade77 — Thu, 05 Nov 2009 19:09:03 -0500

> hello,
> i changed my data to separate the dates. could you give me some tips on how to create a moving average filter? i think it would be very useful for a variety of things in the future.
> thanks!

averages = filter(ones(1,2),2,data);

the equation you are solving indirectly is :
2y(n) = x(n) + x(n-1)
hence,
y(n) = (x(n) + x(n-1))/2

If you need to find the moving average of 3 previous, then it will be
averages = filter(ones(1,3),3,data);

for any case your data is the two columns of weights and Heigths.

    Note that for the first row, the moving average is (30 + 0)/2 = 15; etc
    anything before the first row is assumed zero, unless you want to specify your initial condition.

    If you want the output to be in a special formating,:
    and if your data if not confidential, you can click on my user name, and e mail the excel data to me or part of it to me, then I can properly reformat the excel file for you,and write the code for you.

    Note the reason I suggested you seperate the month,year and day is not only so that MATLAB can read the file easily, but also because, you can use the date functions in matlab to properly handle your situation.

If you need more help feel free to reply to this post.

Re: applying functions to grouped data

Anna Chen — Fri, 06 Nov 2009 04:22:01 -0500

thank you so much for your time! it was very helpful!

"ade77 " <ade100a@gmail.com> wrote in message <hcv7sf$h4o$1@fred.mathworks.com>...
>
> > hello,
> > i changed my data to separate the dates. could you give me some tips on how to create a moving average filter? i think it would be very useful for a variety of things in the future.
> > thanks!
>
> averages = filter(ones(1,2),2,data);
>
> the equation you are solving indirectly is :
> 2y(n) = x(n) + x(n-1)
> hence,
> y(n) = (x(n) + x(n-1))/2
>
> If you need to find the moving average of 3 previous, then it will be
> averages = filter(ones(1,3),3,data);
>
> for any case your data is the two columns of weights and Heigths.
>
> Note that for the first row, the moving average is (30 + 0)/2 = 15; etc
> anything before the first row is assumed zero, unless you want to specify your initial condition.
>
> If you want the output to be in a special formating,:
> and if your data if not confidential, you can click on my user name, and e mail the excel data to me or part of it to me, then I can properly reformat the excel file for you,and write the code for you.
>
> Note the reason I suggested you seperate the month,year and day is not only so that MATLAB can read the file easily, but also because, you can use the date functions in matlab to properly handle your situation.
>
> If you need more help feel free to reply to this post.
>