Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

Thread Subject:
How to average column values to the same minute?

Subject: How to average column values to the same minute?

From: Li

Date: 2 Jun, 2010 19:44:04

Message: 1 of 10

I have a large matrix like below:

year month day hour min sec A_value
2010 5 20 9 30 05 396
2010 5 20 9 30 10 470
2010 5 20 9 30 15 490
...
...

I want to calculate the average A_value (the last column) withini a minute. Other than getting a loop and finding all rows with the same year month day hour minute and average them, is there a quicker way to do this? I know 'ismember' works for column data, do we have something like that?

Thanks.



Instead of

Subject: How to average column values to the same minute?

From: TideMan

Date: 2 Jun, 2010 20:16:20

Message: 2 of 10

On Jun 3, 7:44 am, "Li " <liqingji...@gmail.com> wrote:
> I have a large matrix like below:
>
> year month day hour min sec A_value
> 2010 5 20 9 30 05 396
> 2010 5 20 9 30 10 470
> 2010 5 20 9 30 15 490
> ...
> ...
>
> I want to calculate the average A_value (the last column) withini a minute. Other than getting a loop and finding all rows with the same year month day hour minute and average them, is there a quicker way to do this? I know 'ismember' works for column data, do we have something like that?
>
> Thanks.
>
> Instead of

What does this mean: "average A_value (the last column) withini a
minute"?
The data appear to be at 5 min intervals.
How can you average within a minute when the data are spaced at 5 min
intervals?
Does it mean you want to interpolate every minute?
Does it mean you want to interpolate every 5 minutes?

I have no idea why you think you could use ismember for this task.
Please explain.

Subject: How to average column values to the same minute?

From: Li

Date: 2 Jun, 2010 20:24:22

Message: 3 of 10

Thank you for the reply.

Sorry for the confusion. The data is at 5-second interval. So I want to average them into the same minute.

I did not mean ismember will work here at all, I was trying to find out if there a function similar to ismember that could solve my problem. Please ignore this.

TideMan <mulgor@gmail.com> wrote in message <f97cec95-f9ec-41a2-840b-76de084de454@y18g2000prn.googlegroups.com>...
> On Jun 3, 7:44 am, "Li " <liqingji...@gmail.com> wrote:
> > I have a large matrix like below:
> >
> > year month day hour min sec A_value
> > 2010 5 20 9 30 05 396
> > 2010 5 20 9 30 10 470
> > 2010 5 20 9 30 15 490
> > ...
> > ...
> >
> > I want to calculate the average A_value (the last column) withini a minute. Other than getting a loop and finding all rows with the same year month day hour minute and average them, is there a quicker way to do this? I know 'ismember' works for column data, do we have something like that?
> >
> > Thanks.
> >
> > Instead of
>
> What does this mean: "average A_value (the last column) withini a
> minute"?
> The data appear to be at 5 min intervals.
> How can you average within a minute when the data are spaced at 5 min
> intervals?
> Does it mean you want to interpolate every minute?
> Does it mean you want to interpolate every 5 minutes?
>
> I have no idea why you think you could use ismember for this task.
> Please explain.

Subject: How to average column values to the same minute?

From: TideMan

Date: 2 Jun, 2010 20:43:10

Message: 4 of 10

On Jun 3, 8:24 am, "Li " <liqingji...@gmail.com> wrote:
> Thank you for the reply.
>
> Sorry for the confusion. The data is at 5-second interval. So I want to average them into the same minute.
>
> I did not mean ismember will work here at all, I was trying to find out if there a function similar to ismember that could solve my problem. Please ignore this.
>
> TideMan <mul...@gmail.com> wrote in message <f97cec95-f9ec-41a2-840b-76de084de...@y18g2000prn.googlegroups.com>...
> > On Jun 3, 7:44 am, "Li " <liqingji...@gmail.com> wrote:
> > > I have a large matrix like below:
>
> > > year month day hour min sec A_value
> > > 2010 5 20 9 30 05 396
> > > 2010 5 20 9 30 10 470
> > > 2010 5 20 9 30 15 490
> > > ...
> > > ...
>
> > > I want to calculate the average A_value (the last column) withini a minute. Other than getting a loop and finding all rows with the same year month day hour minute and average them, is there a quicker way to do this? I know 'ismember' works for column data, do we have something like that?
>
> > > Thanks.
>
> > > Instead of
>
> > What does this mean: "average A_value (the last column) withini a
> > minute"?
> > The data appear to be at 5 min intervals.
> > How can you average within a minute when the data are spaced at 5 min
> > intervals?
> > Does it mean you want to interpolate every minute?
> > Does it mean you want to interpolate every 5 minutes?
>
> > I have no idea why you think you could use ismember for this task.
> > Please explain.

Oh, I see.........
I misread the headers.

A1=reshape(A(:,6),12,[]); % Form a 12xn matrix
Abar=mean(A1).'; % Take the mean of each column and transpose

Note: the no of rows in the matrix A must be a multiple of 12 or you
must reduce it to that.

Subject: How to average column values to the same minute?

From: Li

Date: 2 Jun, 2010 20:56:04

Message: 5 of 10

Thanks, but I am afraid that it does not solve my problem. I have millions of data from 1900 all the way to 2010. The number of measurements within a minute ranging from 1 to 20. I do not think it is simply a reshape and average. What I want are the averages within each minute, instead of the average of the whole time span.


TideMan <mulgor@gmail.com> wrote in message <83ae6706-488d-4f66-ba0a-56dff8392379@11g2000prv.googlegroups.com>...
> Oh, I see.........
> I misread the headers.
>
> A1=reshape(A(:,6),12,[]); % Form a 12xn matrix
> Abar=mean(A1).'; % Take the mean of each column and transpose
>
> Note: the no of rows in the matrix A must be a multiple of 12 or you
> must reduce it to that.

Subject: How to average column values to the same minute?

From: Bruno Luong

Date: 2 Jun, 2010 21:19:07

Message: 6 of 10

A=[2010 5 20 9 30 05 396;
   2010 5 20 9 30 10 470;
   2010 5 20 9 30 15 490;
   2010 5 20 10 15 02 200;
   2010 5 20 10 15 06 100];

minutes = A(:,1:5);
[trash trash J] = unique(minutes,'rows');
avg = accumarray(J(:),A(:,7))./accumarray(J(:),1)

% Bruno

Subject: How to average column values to the same minute?

From: Li

Date: 2 Jun, 2010 21:19:07

Message: 7 of 10

I found the solution: B = grpstats(A, A(:,1:5));

Thanks.



"Li " <liqingjiang@gmail.com> wrote in message <hu6gh4$e3t$1@fred.mathworks.com>...
> Thanks, but I am afraid that it does not solve my problem. I have millions of data from 1900 all the way to 2010. The number of measurements within a minute ranging from 1 to 20. I do not think it is simply a reshape and average. What I want are the averages within each minute, instead of the average of the whole time span.
>
>
> TideMan <mulgor@gmail.com> wrote in message <83ae6706-488d-4f66-ba0a-56dff8392379@11g2000prv.googlegroups.com>...
> > Oh, I see.........
> > I misread the headers.
> >
> > A1=reshape(A(:,6),12,[]); % Form a 12xn matrix
> > Abar=mean(A1).'; % Take the mean of each column and transpose
> >
> > Note: the no of rows in the matrix A must be a multiple of 12 or you
> > must reduce it to that.

Subject: How to average column values to the same minute?

From: TideMan

Date: 2 Jun, 2010 21:46:12

Message: 8 of 10

On Jun 3, 8:56 am, "Li " <liqingji...@gmail.com> wrote:
> Thanks, but I am afraid that it does not solve my problem. I have millions of data from 1900 all the way to 2010. The number of measurements within a minute ranging from 1 to 20. I do not think it is simply a reshape and average. What I want are the averages within each minute, instead of the average of the whole time span.
>
> TideMan <mul...@gmail.com> wrote in message <83ae6706-488d-4f66-ba0a-56dff8392...@11g2000prv.googlegroups.com>...
> > Oh, I see.........
> > I misread the headers.
>
> > A1=reshape(A(:,6),12,[]);  % Form a 12xn matrix
> > Abar=mean(A1).';   % Take the mean of each column and transpose
>
> > Note: the no of rows in the matrix A must be a multiple of 12 or you
> > must reduce it to that.

You said the data were at 5 sec interval and I assumed you were
telling the truth.
Now you tell us they are at intervals between 5 and 60 seconds.

A different strategy is needed when the data are not equispaced.
Here's one method:
1. Convert the times to Matlab days:
t=datenum(A(:,1:6));
2. Use histc to split the time into equispaced bins of 5 min.
3. Use the second argument of histc to determine which bin each A(:,
7) belongs to and loop to calculate the mean in each bin.

Subject: How to average column values to the same minute?

From: Roger Stafford

Date: 2 Jun, 2010 22:46:04

Message: 9 of 10

"Li " <liqingjiang@gmail.com> wrote in message <hu6ca4$6p$1@fred.mathworks.com>...
> I have a large matrix like below:
>
> year month day hour min sec A_value
> 2010 5 20 9 30 05 396
> 2010 5 20 9 30 10 470
> 2010 5 20 9 30 15 490
> ...
> ...
>
> I want to calculate the average A_value (the last column) withini a minute. Other than getting a loop and finding all rows with the same year month day hour minute and average them, is there a quicker way to do this? I know 'ismember' works for column data, do we have something like that?
>
> Thanks.
- - - - - - - -
  Call your matrix A.

 [b,m,n] = unique(A(1:5,1),'rows');
 [q,p] = sort(n);
 c = cumsum([0;A(p,7)]);
 f = find([true;diff(q)~=0;true]);
 B = [b,diff(c(f))./diff(f)];

The result matrix B has discarded the seconds column - I don't know what you want to do with it. It contains only the unique year, month, day, hour, and minute combinations (in sorted order,) along with the corresponding average of A_value.

  Note: Bruno's solution looks simpler but I thought I would contribute this solution anyway for the sake of variety.

Roger Stafford

Subject: How to average column values to the same minute?

From: Li

Date: 3 Jun, 2010 01:42:04

Message: 10 of 10

Thank you all for the responses. I am glad to see the many methods.


> - - - - - - - -
> Call your matrix A.
>
> [b,m,n] = unique(A(1:5,1),'rows');
> [q,p] = sort(n);
> c = cumsum([0;A(p,7)]);
> f = find([true;diff(q)~=0;true]);
> B = [b,diff(c(f))./diff(f)];
>
> The result matrix B has discarded the seconds column - I don't know what you want to do with it. It contains only the unique year, month, day, hour, and minute combinations (in sorted order,) along with the corresponding average of A_value.
>
> Note: Bruno's solution looks simpler but I thought I would contribute this solution anyway for the sake of variety.
>
> Roger Stafford

Tags for this Thread

What are tags?

A tag is like a keyword or category label associated with each thread. Tags make it easier for you to find threads of interest.

Anyone can tag a thread. Tags are public and visible to everyone.

Contact us