Computing the median of a group except one member

I have a dataset of a variable x for S seasons of the same J firms (not ordered). I'd like to compute for each firm in each season, the median of all other firms in that season. e.g. S=2, J=6, my dataset is

M=[1 2 3 6 5 4 2 3 4 1 5 6; 1 1 1 1 1 1 2 2 2 2 2 2; 2 4 6 5 7 8 3 7 5 7 5 3]'

First column is the firm index, second column the season, third column the x values for each firm in each season.

I am thinking something like

for i=1:size(M,1)/6
    for j=6*i-5:6*i
        M(j,4)=median(M(6*i-5:6*i,3));
    end
end

but with the index of M(:,3) inside median() somehow telling MATLAB to exclude the jth entry of M(:,3)?

Thank you!

[madhan ravi]

Could you give a short example of your desired output?

[wy6622]

Sure. M=[1 3 2 3 2 1; 1 1 1 2 2 2; 2 4 3 4 6 6; 3.5 2.5 3 6 5 5]'

My data is M(:, 1:3) and I'd like to obtain the full M above.

Thank you!

[madhan ravi]

You didn’t write your desired output yet?

[wy6622]

Sorry, I meant that M(:, 1:3) and I'd like to obtain the 4th column of M (desired output) whose entries are the median of all firms' x (column 3) except firm j's.

[Stephen23]

@weicheny: please show us the expected output, e.g.

3

or

[19,4,17.6]

or whatever you expect to get. It is much easier to test code when you provide an expected output we can check the code's output against.

[wy6622]

Sure. My input is

I=[1 3 2 3 2 1; 1 1 1 2 2 2; 2 4 3 4 6 6]'

Column 1 of I is the firm number, column 2 is the season number, and column 3 is the observed value x for each firm in each season.

And I'd like to obtain this matrix O:

O=[1 3 2 3 2 1; 1 1 1 2 2 2; 2 4 3 4 6 6; 3.5 2.5 3 6 5 5]'

That is, the matrix I with an extra column whose entries are the median of the other firms' x's in each season. I'd like to obtain the O matrix above.

Thank you!

[madhan ravi]

ok did you get the answer to your question since you accepted the answer ?

[wy6622]

It works for the most parts except the step where it tries to assign the computed median to the corresponding firm. The "row=..." row below returns 0's as opposed to the desired index.

M=[1 2 3 6 5 4 2 3 4 1 5 6; 1 1 1 1 1 1 2 2 2 2 2 2; 2 4 6 5 7 8 3 7 5 7 5 3]';
M(:,4)=0; %create column of zeros
num_seasons = unique(M(:,2)); %num of seasons
num_f = unique(M(:,1)); %num of firms
for i = 1:numel(num_seasons) % for each season...
    season = M(M(:,2) == num_seasons(i),:); %matrix of season being iterated
    for j = 1:numel(num_f) % for each firm...
        med = median(season(not(season(:,1)==num_f(j)),3)); %median of OTHER firms for the season
        disp(['Firm: ' num2str(num_f(j)) ' Season:' num2str(num_seasons(i)),...
            ' Median: ' num2str(med)]);
        row = all(M(:,[1 2])==[num2str(num_f(j)) num2str(num_seasons(i))],2); %locate row where firm and season matches
        M(row,4) = med; %append the median to the corresponding row, 4th col
    end
end

Answer 1

[Kevin Phung]

Hello!

Is this sort of what you're looking for?

M=[1 2 3 6 5 4 2 3 4 1 5 6; 1 1 1 1 1 1 2 2 2 2 2 2; 2 4 6 5 7 8 3 7 5 7 5 3]';
num_seasons = unique(M(:,2)); %num of seasons
num_f = unique(M(:,1)); %num of firms

for i = 1:numel(num_seasons) % for each season...
    season = M(M(:,2) == num_seasons(i),:); %matrix of season being iterated
    for j = 1:numel(num_f) % for each firm...
        med = median(season(not(season(:,1)==num_f(j)),3)); %median of OTHER firms for the season
        disp(['Firm: ' num2str(num_f(j)) ' Season:' num2str(num_seasons(i)),...
            ' Median: ' num2str(med)]);
    end
end

[wy6622]

Thank you - this works great! Is there a way, in the same loop, to place the calculated median with the corresponding firm j (whose x was omitted in the calculation) in a 4th column of M? (since the firm numbers in M are not necessarily in ascending order)

[Kevin Phung]

I don't have matlab with me right now, but try this:

M=[1 2 3 6 5 4 2 3 4 1 5 6; 1 1 1 1 1 1 2 2 2 2 2 2; 2 4 6 5 7 8 3 7 5 7 5 3]';
M(:,4)=0; %create column of zeros
num_seasons = unique(M(:,2)); %num of seasons
num_f = unique(M(:,1)); %num of firms
for i = 1:numel(num_seasons) % for each season...
    season = M(M(:,2) == num_seasons(i),:); %matrix of season being iterated
    for j = 1:numel(num_f) % for each firm...
        med = median(season(not(season(:,1)==num_f(j)),3)); %median of OTHER firms for the season
        disp(['Firm: ' num2str(num_f(j)) ' Season:' num2str(num_seasons(i)),...
            ' Median: ' num2str(med)]);
        row = all(M(:,[1 2])==[num2str(num_f(j)) num2str(num_seasons(i))],2); %locate row where firm and season matches
        M(row,4) = med; %append the median to the corresponding row, 4th col
    end
end

Let me know if this works.

[wy6622]

Thank you! I ran this and column 4 is all zeros. It appears that row returns all zeros...

[wy6622]

This seems to fix it

M=[1 2 3 6 5 4 2 3 4 1 5 6; 1 1 1 1 1 1 2 2 2 2 2 2; 2 4 6 5 7 8 1 2 10 7 5 3 ]'; 
M(:,4)=0; %create column of zeros 
num_seasons = unique(M(:,2)); %num of seasons 
%num_f = unique(M(:,1)); %num of firms - to be used inside loop 
%for cases where the number of firms may change from season to season
P =0;
for i = 1:numel(num_seasons) % for each season ... 
    season = M(:,2) == num_seasons(i); %index of season being iterated 
    num_f = (M(season,1));
    for j = 1:numel(num_f) % for each firm ... 
         P = P+1;
        notj = ~(M(:,1) == num_f(j));
        jointindex = and(season, notj);
        M(P,4) = median(M(jointindex, 3));  
    end 
end 

[Kevin Phung]

Just for the sake of following up with my mistake:

I made the mistake of converting the values into strings. row should have been:

row = all(M(:,[1 2])==[num_f(j) num_seasons(i)],2); %locate row where firm and season matches

so this should have worked:

M=[1 2 3 6 5 4 2 3 4 1 5 6; 1 1 1 1 1 1 2 2 2 2 2 2; 2 4 6 5 7 8 3 7 5 7 5 3]';
M(:,4)=0; %create column of zeros
num_seasons = unique(M(:,2)); %num of seasons
num_f = unique(M(:,1)); %num of firms
for i = 1:numel(num_seasons) % for each season...
    season = M(M(:,2) == num_seasons(i),:); %matrix of season being iterated
    for j = 1:numel(num_f) % for each firm...
        med = median(season(not(season(:,1)==num_f(j)),3)); %median of OTHER firms for the season
        row = all(M(:,[1 2])==[num_f(j) num_seasons(i)],2); %locate row where firm and season matches
        M(row,4) = med; %append the median to the corresponding row, 4th col
    end
end

use whichever method works best for you

[wy6622]

Thank you again for your help.