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:
average 2nd column when 1st column has same number

Subject: average 2nd column when 1st column has same number

From: andy

Date: 9 Mar, 2011 19:07:03

Message: 1 of 17

ok thanks, i have another question about matlab methods.

I have matrix and when i have a repeated number in the first column I want to take the average of the other column vector that have that same number in the first column. Then I want to make the matrix smaller by just keeping one row entry for the repeated number and the average that was previously calculated.

here is what i want to do.

i want to take : [ 1 14;1 16;2 30;2 31;2 32]

and make : [1 15;2 31]

I wonder if i have to use a loop or If i can use matlabs vector methods?
can you tell me how to do this or tell me how to get started

thanks

Subject: average 2nd column when 1st column has same number

From: Sean de

Date: 9 Mar, 2011 19:21:04

Message: 2 of 17

"andy " <andy.kata7878@gmail.com> wrote in message <il8j4n$nhg$1@fred.mathworks.com>...
> ok thanks, i have another question about matlab methods.
>
> I have matrix and when i have a repeated number in the first column I want to take the average of the other column vector that have that same number in the first column. Then I want to make the matrix smaller by just keeping one row entry for the repeated number and the average that was previously calculated.
>
> here is what i want to do.
>
> i want to take : [ 1 14;1 16;2 30;2 31;2 32]
>
> and make : [1 15;2 31]
>
> I wonder if i have to use a loop or If i can use matlabs vector methods?
> can you tell me how to do this or tell me how to get started
>
> thanks

doc accumarray

x = [1 14;1 16;2 30;2 31;2 32];
x2 = [unique(x(:,1)),accumarray(x(:,1),x(:,2),[],@mean)];

Subject: average 2nd column when 1st column has same number

From: Nasser M. Abbasi

Date: 9 Mar, 2011 19:44:07

Message: 3 of 17

On 3/9/2011 11:07 AM, andy wrote:
> ok thanks, i have another question about matlab methods.
>
> I have matrix and when i have a repeated number in the first column I want
> to take the average of the other column vector that have that same number in the first column.
> Then I want to make the matrix smaller by just keeping one row entry for the repeated number
> and the average that was previously calculated.
>
> here is what i want to do.
>
> i want to take : [ 1 14;1 16;2 30;2 31;2 32]
>
> and make : [1 15;2 31]
>
> I wonder if i have to use a loop or If i can use matlabs vector methods?
> can you tell me how to do this or tell me how to get started
>
> thanks


----------------------------
%data
A = [ 1 14;1 16;2 30;2 31;2 32; 5 6];
A = sortrows(A);
B = size(A);
i = 1;
j = 0;
done = false;

%engine

while not(done)

     [r,~] = ismember(A,A(i));
     I = find(r(:,1)==1);

     if length(I)>1
        i = i+length(I);
        j = j+1;
        B(j,:) = [A(I(1),1) mean(A(I,2))];
     else
        i = i+1;
     end

     if i>length(A)
        done = true;
     end
     
end
------------------------

EDU>> B
B =
      1 15
      2 31

--Nasser

Subject: average 2nd column when 1st column has same number

From: jonathan

Date: 9 Mar, 2011 20:16:05

Message: 4 of 17

"Nasser M. Abbasi" <nma@12000.org> wrote in message <il8laa$cgv$2@speranza.aioe.org>...
> On 3/9/2011 11:07 AM, andy wrote:
> > ok thanks, i have another question about matlab methods.
> >
> > I have matrix and when i have a repeated number in the first column I want
> > to take the average of the other column vector that have that same number in the first column.
> > Then I want to make the matrix smaller by just keeping one row entry for the repeated number
> > and the average that was previously calculated.
> >
> > here is what i want to do.
> >
> > i want to take : [ 1 14;1 16;2 30;2 31;2 32]
> >
> > and make : [1 15;2 31]
> >
> > I wonder if i have to use a loop or If i can use matlabs vector methods?
> > can you tell me how to do this or tell me how to get started
> >
> > thanks
>
>
> ----------------------------
> %data
> A = [ 1 14;1 16;2 30;2 31;2 32; 5 6];
> A = sortrows(A);
> B = size(A);
> i = 1;
> j = 0;
> done = false;
>
> %engine
>
> while not(done)
>
> [r,~] = ismember(A,A(i));
> I = find(r(:,1)==1);
>
> if length(I)>1
> i = i+length(I);
> j = j+1;
> B(j,:) = [A(I(1),1) mean(A(I,2))];
> else
> i = i+1;
> end
>
> if i>length(A)
> done = true;
> end
>
> end
> ------------------------
>
> EDU>> B
> B =
> 1 15
> 2 31
>
> --Nasser



Thanks , I can see this worked for Nassar, but I do not know why this wont work for me? the code and error is below, and

>> %data
clear
A = [ 1 14;1 16;2 30;2 31;2 32; 5 6];
A = sortrows(A);
B = size(A);
i = 1;
j = 0;
done = false;

%engine

while not(done)

     [r,~] = ismember(A,A(i));
     I = find(r(:,1)==1);

     if length(I)>1
        i = i+length(I);
        j = j+1;
        B(j,:) = [A(I(1),1) mean(A(I,2))];
     else
        i = i+1;
     end

     if i>length(A)
        done = true;
     end
     
end
??? [r,~] = ismember(A,A(i));
Error: Unbalanced or unexpected parenthesis or bracket

when i run it in an mfile it says points me to the column with the ~ as the problem
thanks

Subject: average 2nd column when 1st column has same number

From: Nasser M. Abbasi

Date: 9 Mar, 2011 20:38:46

Message: 5 of 17

On 3/9/2011 12:16 PM, jonathan wrote:

> end
> ??? [r,~] = ismember(A,A(i));
> Error: Unbalanced or unexpected parenthesis or bracket
>
> when i run it in an mfile it says points me to the column with the ~ as the problem
> thanks

You have an old version of Matlab.

Just replace "~" by "trash" it should work

--Nasser

Subject: average 2nd column when 1st column has same number

From: dpb

Date: 9 Mar, 2011 20:57:17

Message: 6 of 17

On 3/9/2011 2:38 PM, Nasser M. Abbasi wrote:
> On 3/9/2011 12:16 PM, jonathan wrote:
>
>> end
>> ??? [r,~] = ismember(A,A(i));
>> Error: Unbalanced or unexpected parenthesis or bracket
>>
>> when i run it in an mfile it says points me to the column with the ~
>> as the problem
>> thanks
>
> You have an old version of Matlab.
>
> Just replace "~" by "trash" it should work

Or, since is only trashing the trailing value, not skipping any desired,
the single return form works as if the tilde were present in recent
releases...

r = ismember(A,A(i));

--

Subject: average 2nd column when 1st column has same number

From: jonathan

Date: 9 Mar, 2011 21:40:21

Message: 7 of 17

dpb <none@non.net> wrote in message <il8pji$drk$1@news.eternal-september.org>...
> On 3/9/2011 2:38 PM, Nasser M. Abbasi wrote:
> > On 3/9/2011 12:16 PM, jonathan wrote:
> >
> >> end
> >> ??? [r,~] = ismember(A,A(i));
> >> Error: Unbalanced or unexpected parenthesis or bracket
> >>
> >> when i run it in an mfile it says points me to the column with the ~
> >> as the problem
> >> thanks
> >
> > You have an old version of Matlab.
> >
> > Just replace "~" by "trash" it should work
>
> Or, since is only trashing the trailing value, not skipping any desired,
> the single return form works as if the tilde were present in recent
> releases...
>
> r = ismember(A,A(i));
>
> --
Hey guys i had been looking to do something very similar to this also, but is there a way to have it also keep the rows if the number in column 1 does not repeat?
I see here that it will completely discard the whole row if the number in the first column is not repeating?

Subject: average 2nd column when 1st column has same number

From: Nasser M. Abbasi

Date: 9 Mar, 2011 21:54:59

Message: 8 of 17

On 3/9/2011 1:40 PM, jonathan wrote:
>
>>
>> --
> Hey guys i had been looking to do something very similar to this also, but is there
> a way to have it also keep the rows if the number in column 1 does not repeat?
> I see here that it will completely discard the whole row if the number
> in the first column is not repeating?

Those were the program specifications.

But if you want to keep a row, even if there is no hit, simply
add it.

Here is a modified version to do that

----------------------------------------------
%data
A = [ 1 14;1 16;2 30;2 31;2 32; 5 6];
A = sortrows(A);
B = size(A);
i = 1; j = 0;
done = false;

%engine
while not(done)

      [r,~] = ismember(A,A(i));
      I = find(r(:,1)==1);
      
      j = j + 1;
      if length(I)>1
         i = i+length(I);
         B(j,:) = [A(I(1),1) mean(A(I,2))];
      else
         B(j,:) = A(i,:);
         i = i+1;
      end

      if i>length(A)
         done = true;
      end
      
end
-------------------------------

A =
      1 14
      1 16
      2 30
      2 31
      2 32
      5 6
EDU>> B
B =
      1 15
      2 31
      5 6


Also see a post by Sean De, he seems to have a shorter solution
using accumarray you might be able also to modify to do what you
want.

--Nasser

Subject: average 2nd column when 1st column has same number

From: Bruno Luong

Date: 9 Mar, 2011 22:10:21

Message: 9 of 17

"Sean de " <sean.dewolski@nospamplease.umit.maine.edu> wrote in message <il8jv0$i08$1@fred.mathworks.com>...

>
> x = [1 14;1 16;2 30;2 31;2 32];
> x2 = [unique(x(:,1)),accumarray(x(:,1),x(:,2),[],@mean)];

This is fine assuming the first column is successive integers (1, 2...). Otherwise a further step is needed:

A = [ 10 14;10 16;22 30;22 31;22 32]

[Val I J] = unique(A(:,1));
B = [Val accumarray(J(:), A(:,2)) ./ accumarray(J(:), 1)]

Note that the ratio of two accumarray using sums is usually faster than @mean.

Bruno

Subject: average 2nd column when 1st column has same number

From: jonathan

Date: 10 Mar, 2011 02:14:05

Message: 10 of 17

"Nasser M. Abbasi" <nma@12000.org> wrote in message <il8svm$1h2$1@speranza.aioe.org>...
> On 3/9/2011 1:40 PM, jonathan wrote:
> >
> >>
> >> --
> > Hey guys i had been looking to do something very similar to this also, but is there
> > a way to have it also keep the rows if the number in column 1 does not repeat?
> > I see here that it will completely discard the whole row if the number
> > in the first column is not repeating?
>
> Those were the program specifications.
>
> But if you want to keep a row, even if there is no hit, simply
> add it.
>
> Here is a modified version to do that
>
> ----------------------------------------------
> %data
> A = [ 1 14;1 16;2 30;2 31;2 32; 5 6];
> A = sortrows(A);
> B = size(A);
> i = 1; j = 0;
> done = false;
>
> %engine
> while not(done)
>
> [r,~] = ismember(A,A(i));
> I = find(r(:,1)==1);
>
> j = j + 1;
> if length(I)>1
> i = i+length(I);
> B(j,:) = [A(I(1),1) mean(A(I,2))];
> else
> B(j,:) = A(i,:);
> i = i+1;
> end
>
> if i>length(A)
> done = true;
> end
>
> end
> -------------------------------
>
> A =
> 1 14
> 1 16
> 2 30
> 2 31
> 2 32
> 5 6
> EDU>> B
> B =
> 1 15
> 2 31
> 5 6
>
>
> Also see a post by Sean De, he seems to have a shorter solution
> using accumarray you might be able also to modify to do what you
> want.
>
> --Nasser

hello,
Nasser or anyone else, I am sorry for still nut fully understanding this code you have put out here, but can you tell me what would need to be done to have this work on a matrix with more colums so that the other columns would also be averaged?

Subject: average 2nd column when 1st column has same number

From: Nasser M. Abbasi

Date: 10 Mar, 2011 04:14:20

Message: 11 of 17

On 3/9/2011 6:14 PM, jonathan wrote:

>
> hello,
> Nasser or anyone else, I am sorry for still nut fully understanding this code you have put out
> here, but can you tell me what would need to be done to have this work on a matrix with more colums
> so that the other columns would also be averaged?

Is this a new specifications now? :)

It will help, since it is starting to get confusing, to explain from the top
what is it you are looking for.

if there are more columns, then what mean are you now looking for?

Initially, it was 2 columns, and if there was a hit, then it was the mean
of the second column for all matched hits.

But if there are more than 2 columns, what mean is it you want? May be an example
will help. Make up some data, and show what you expect to see. I am sure
someone will help you better then.


--Nasser

Subject: average 2nd column when 1st column has same number

From: Bruno Luong

Date: 10 Mar, 2011 07:32:04

Message: 12 of 17

Average multiple column:

A = [ 10 14 8;
      10 16 6;
      22 30 10;
      22 31 20;
      22 32 17]

[m n] = size(A);
[Val I J] = unique(A(:,1));
C = repmat(2:n, [m, 1]);
R = repmat(J, [1 n-1]);
B = A(:,2:end);
B = accumarray([R(:) C(:)], B(:));
B = B ./ repmat(accumarray(J(:), 1), [1 n]);
B(:,1) = Val

% Bruno

Subject: average 2nd column when 1st column has same number

From: jonathan

Date: 10 Mar, 2011 13:13:05

Message: 13 of 17

"Nasser M. Abbasi" <nma@12000.org> wrote in message <il9j71$g7i$1@speranza.aioe.org>...
> On 3/9/2011 6:14 PM, jonathan wrote:
>
> >
> > hello,
> > Nasser or anyone else, I am sorry for still nut fully understanding this code you have put out
> > here, but can you tell me what would need to be done to have this work on a matrix with more colums
> > so that the other columns would also be averaged?
>
> Is this a new specifications now? :)
>
> It will help, since it is starting to get confusing, to explain from the top
> what is it you are looking for.
>
> if there are more columns, then what mean are you now looking for?
>
> Initially, it was 2 columns, and if there was a hit, then it was the mean
> of the second column for all matched hits.
>
> But if there are more than 2 columns, what mean is it you want? May be an example
> will help. Make up some data, and show what you expect to see. I am sure
> someone will help you better then.
>
>
> --Nasser

I am trying to find the average of the seperate columns when the the first column has the same value down multiple rows. Here is my example.

 A =[ 0 2 8; 0 4 10;1 4 2;1 6 10;2 4 7]
 result=[ 0 3 9;1 5 6;2 4 7]

I want to do the same average method to column 3 that Nasser previously did for column 2. I would like to know the way Nasser did it only with multiple rows. I like his method becuase i know i will sometimes have a 0 in the first column. If someone shows me how to do it with multiple columns i think i will be able to make it work for more columns if need be.
Thank you for your patience

Subject: average 2nd column when 1st column has same number

From: Bruno Luong

Date: 10 Mar, 2011 13:30:08

Message: 14 of 17

"jonathan " <senator314159@hotmail.com> wrote in message <il9c5c$q7u$1@fred.mathworks.com>...
> "Nasser M. Abbasi" <nma@12000.org> wrote in message <il8svm$1h2$1@speranza.aioe.org>...
> > On 3/9/2011 1:40 PM, jonathan wrote:
> > >
> > >>
> > >> --
> > > Hey guys i had been looking to do something very similar to this also, but is there
> > > a way to have it also keep the rows if the number in column 1 does not repeat?
> > > I see here that it will completely discard the whole row if the number
> > > in the first column is not repeating?
> >
> > Those were the program specifications.
> >
> > But if you want to keep a row, even if there is no hit, simply
> > add it.
> >
> > Here is a modified version to do that
> >
> > ----------------------------------------------
> > %data
> > A = [ 1 14;1 16;2 30;2 31;2 32; 5 6];
> > A = sortrows(A);
> > B = size(A);
> > i = 1; j = 0;
> > done = false;
> >
> > %engine
> > while not(done)
> >
> > [r,~] = ismember(A,A(i));
> > I = find(r(:,1)==1);
> >
> > j = j + 1;
> > if length(I)>1
> > i = i+length(I);
> > B(j,:) = [A(I(1),1) mean(A(I,2))];
> > else
> > B(j,:) = A(i,:);
> > i = i+1;
> > end
> >
> > if i>length(A)
> > done = true;
> > end
> >
> > end
> > -------------------------------
> >
> > A =
> > 1 14
> > 1 16
> > 2 30
> > 2 31
> > 2 32
> > 5 6
> > EDU>> B
> > B =
> > 1 15
> > 2 31
> > 5 6
> >
> >
> > Also see a post by Sean De, he seems to have a shorter solution
> > using accumarray you might be able also to modify to do what you
> > want.
> >
> > --Nasser
>
> hello,
> Nasser or anyone else, I am sorry for still nut fully understanding this code you have put out here, but can you tell me what would need to be done to have this work on a matrix with more colums so that the other columns would also be averaged?

If you have large data set, you might think about the runtime, Nasseré method using while-loop and it is slow relatively to accumarray as showed in this example

data = rand(1e6,1);
A1 = floor(100*rand(1e6,1));
A = [A1 data];
clear A1 data

tic
[m n] = size(A);
[Val I J] = unique(A(:,1));
C = repmat(2:n, [m, 1]);
R = repmat(J, [1 n-1]);
B = A(:,2:end);
B = accumarray([R(:) C(:)], B(:));
B = B ./ repmat(accumarray(J(:), 1), [1 n]);
B(:,1) = Val;
t1 = toc; % 0.283590 seconds.
%B

%data
tic
A = sortrows(A);
B = size(A);
i = 1;
j = 0;
done = false;
%engine
while not(done)

     [r,~] = ismember(A,A(i));
     I = find(r(:,1)==1);

     if length(I)>1
        i = i+length(I);
        j = j+1;
        B(j,:) = [A(I(1),1) mean(A(I,2))];
     else
        i = i+1;
     end

     if i>length(A)
        done = true;
     end
     
end
t2 = toc; % 2.396467 seconds.

The difference is even more pronounced if the first column of A has more non-repeat elements (e.g., 1000).

% Bruno

Subject: average 2nd column when 1st column has same number

From: Bruno Luong

Date: 10 Mar, 2011 14:15:08

Message: 15 of 17

If you don't like ACCUMARRAY function (I adore it), here is another method using CUMSUM. This method will be more prone to numerical errors (difference of large numbers):
 
data = rand(1e6,1);
A1 = floor(100*rand(1e6,2));
A = [A1 data];
clear A1 data

[m n] = size(A);
As = sortrows(A,1);
j = [0; find(diff([As(:,1); Inf]))]+1;
B = cumsum([zeros(1,n-1); As(:,2:end)]);
B = B(j,:);
B = diff(B,1,1);
n = diff(j);
B = bsxfun(@rdivide, B, n);
A1 = As(j(1:end-1));
B = [A1 B];

% Bruno

Subject: average 2nd column when 1st column has same number

From: jonathan

Date: 10 Mar, 2011 14:18:05

Message: 16 of 17

"Bruno Luong" <b.luong@fogale.findmycountry> wrote in message <ilajp0$g7m$1@fred.mathworks.com>...
> "jonathan " <senator314159@hotmail.com> wrote in message <il9c5c$q7u$1@fred.mathworks.com>...
> > "Nasser M. Abbasi" <nma@12000.org> wrote in message <il8svm$1h2$1@speranza.aioe.org>...
> > > On 3/9/2011 1:40 PM, jonathan wrote:
> > > >
> > > >>
> > > >> --
> > > > Hey guys i had been looking to do something very similar to this also, but is there
> > > > a way to have it also keep the rows if the number in column 1 does not repeat?
> > > > I see here that it will completely discard the whole row if the number
> > > > in the first column is not repeating?
> > >
> > > Those were the program specifications.
> > >
> > > But if you want to keep a row, even if there is no hit, simply
> > > add it.
> > >
> > > Here is a modified version to do that
> > >
> > > ----------------------------------------------
> > > %data
> > > A = [ 1 14;1 16;2 30;2 31;2 32; 5 6];
> > > A = sortrows(A);
> > > B = size(A);
> > > i = 1; j = 0;
> > > done = false;
> > >
> > > %engine
> > > while not(done)
> > >
> > > [r,~] = ismember(A,A(i));
> > > I = find(r(:,1)==1);
> > >
> > > j = j + 1;
> > > if length(I)>1
> > > i = i+length(I);
> > > B(j,:) = [A(I(1),1) mean(A(I,2))];
> > > else
> > > B(j,:) = A(i,:);
> > > i = i+1;
> > > end
> > >
> > > if i>length(A)
> > > done = true;
> > > end
> > >
> > > end
> > > -------------------------------
> > >
> > > A =
> > > 1 14
> > > 1 16
> > > 2 30
> > > 2 31
> > > 2 32
> > > 5 6
> > > EDU>> B
> > > B =
> > > 1 15
> > > 2 31
> > > 5 6
> > >
> > >
> > > Also see a post by Sean De, he seems to have a shorter solution
> > > using accumarray you might be able also to modify to do what you
> > > want.
> > >
> > > --Nasser
> >
> > hello,
> > Nasser or anyone else, I am sorry for still nut fully understanding this code you have put out here, but can you tell me what would need to be done to have this work on a matrix with more colums so that the other columns would also be averaged?
>
> If you have large data set, you might think about the runtime, Nasseré method using while-loop and it is slow relatively to accumarray as showed in this example
>
> data = rand(1e6,1);
> A1 = floor(100*rand(1e6,1));
> A = [A1 data];
> clear A1 data
>
> tic
> [m n] = size(A);
> [Val I J] = unique(A(:,1));
> C = repmat(2:n, [m, 1]);
> R = repmat(J, [1 n-1]);
> B = A(:,2:end);
> B = accumarray([R(:) C(:)], B(:));
> B = B ./ repmat(accumarray(J(:), 1), [1 n]);
> B(:,1) = Val;
> t1 = toc; % 0.283590 seconds.
> %B
>
> %data
> tic
> A = sortrows(A);
> B = size(A);
> i = 1;
> j = 0;
> done = false;
> %engine
> while not(done)
>
> [r,~] = ismember(A,A(i));
> I = find(r(:,1)==1);
>
> if length(I)>1
> i = i+length(I);
> j = j+1;
> B(j,:) = [A(I(1),1) mean(A(I,2))];
> else
> i = i+1;
> end
>
> if i>length(A)
> done = true;
> end
>
> end
> t2 = toc; % 2.396467 seconds.
>
> The difference is even more pronounced if the first column of A has more non-repeat elements (e.g., 1000).
>
> % Bruno


Bruno, I am trying to do it on more colmns then just one and sometimes I have o in the first column. I do appreciate that your way is faster (my data is like 100 rows.)
I have an example of what I am trying to do at the very bottom. here i have the code I have been trying to modify without much success.

 A =[ 0 2 8; 0 4 10;1 4 2;1 6 10;2 4 7]
A = sortrows(A);
B = size(A);
i = 1; j = 0;
done = false;
 
%engine
while not(done)
 
      [r,trash] = ismember(A,A(i));
      I = find(r(:,1)==1);
      
      j = j + 1;
      if length(I)>1
         i = i+length(I);
         B(j,:) = [A(I(1),1) mean(A(I,2))]; %line I try to change
      else
         B(j,:) = A(i,:);
         i = i+1;
      end
 
      if i>length(A)
         done = true;
      end
      
end
B

I have been trying modifications like this?
B(j,:) = [A(I(1),1) mean(A(I,2)) mean(A(I,3))];
But I get:
Error in ==> NasserCodeMathWorks at 20
         B(j,:) = [A(I(1),1) mean(A(I,2)) mean(A(I,3))];

What I want to do is take: A =[ 0 2 8; 0 4 10;1 4 2;1 6 10;2 4 7]
 and make : result= [ 0 3 9;1 5 6;2 4 7]

Thanks

Subject: average 2nd column when 1st column has same number

From: Bruno Luong

Date: 10 Mar, 2011 14:27:05

Message: 17 of 17

"jonathan " <senator314159@hotmail.com> wrote in message
> Bruno, I am trying to do it on more colmns then just one and sometimes I have o in the first column. I do appreciate that your way is faster (my data is like 100 rows.)
> I have an example of what I am trying to do at the very bottom. here i have the code I have been trying to modify without much success.

General principle: I usually don't modify a code from another person (Nasser's).

Bruno

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