Create mean y values by multiple groups in one graph

Dear all, I have a snippet of my panel

id 	Year 	y 	Cat1 	Cat2 	Cat3 	Cat4
1 	2000 	45 	1 	0 	0 	0
1 	2001 	56 	1 	0 	0 	0
1 	2002 	23 	1 	0 	0 	0
1 	2003 	56 	1 	0 	0 	0
1 	2004 	45 	1 	0 	0 	0
2 	2000 	45 	0 	1 	0 	0
2 	2001 	90 	0 	1 	0 	0
2 	2002 	35 	0 	1 	0 	0
2 	2003 	79 	0 	1 	0 	0
2 	2004 	32 	0 	1 	0 	0

Ofcourse my full panel has id=30 and Year=5 .

I want to find the mean values of y by Cat1, up to Cat4, when each Cat takes the value of 1. And then I want to plot a bar chart of these mean values of y against the X axis that will contain the labels Cat1,..., Cat4 in a vertical position.

Is that possible to do that in Matlab?

If it is not clear, please let me know!

Many thanks in advance!

[Mann Baidi]

Hi,

what is the significane of "N" and "T" variables?

[ektor]

Thank you. I mean id=30 and Year=5. I made the correction above

[Mann Baidi]

Okay, thanks for the clarification.

As per my understanding of the question, you would like to plot the data from Cat1 Cat2.... and y column, right?

[ektor]

yes exactly

Answer 1

[Star Strider]

Try this —

T1 = array2table([1 	2000 	45 	1 	0 	0 	0
1 	2001 	56 	1 	0 	0 	0
1 	2002 	23 	1 	0 	0 	0
1 	2003 	56 	1 	0 	0 	0
1 	2004 	45 	1 	0 	0 	0
2 	2000 	45 	0 	1 	0 	0
2 	2001 	90 	0 	1 	0 	0
2 	2002 	35 	0 	1 	0 	0
2 	2003 	79 	0 	1 	0 	0
2 	2004 	32 	0 	1 	0 	0], 'VariableNames',{'id','Year','y','Cat1','Cat2','Cat3','Cat4'})

T1 = 10x7 table
    id    Year    y     Cat1    Cat2    Cat3    Cat4
    __    ____    __    ____    ____    ____    ____

    1     2000    45     1       0       0       0  
    1     2001    56     1       0       0       0  
    1     2002    23     1       0       0       0  
    1     2003    56     1       0       0       0  
    1     2004    45     1       0       0       0  
    2     2000    45     0       1       0       0  
    2     2001    90     0       1       0       0  
    2     2002    35     0       1       0       0  
    2     2003    79     0       1       0       0  
    2     2004    32     0       1       0       0  

Cat_mean = NaN(2, 4);
for k = 1:4
Cat_mean(:,k) = accumarray(1+T1{:,k+3}, 1, [], @(x)mean(T1.y(x)));
end
Cat_mean

Cat_mean = 2x4

45 45 45 45 45 45 45 45

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

There are several ways to do this in MATLAB. This is the approach that I usually use.

for k = 1:4
    Catu(:,k) = unique(T1{:,k+3},'stable');
end

Catu

Catu = 2x4

1 0 0 0 0 1 0 0

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

figure
hold on
for k = 1:4
    bar(k,Cat_mean(:,k), 'stacked')
end
hold off
xlabel('Cat')
ylabel('Counts')

EDIT — Added bar chart.

.

[ektor]

Thank you, I want only for the case where each Cat=1. I want to exclude the case Cat=0

[Star Strider]

My pleasure!

I am not certain what you want.

Try this —

T1 = array2table([1 	2000 	45 	1 	0 	0 	0
1 	2001 	56 	1 	0 	0 	0
1 	2002 	23 	1 	0 	0 	0
1 	2003 	56 	1 	0 	0 	0
1 	2004 	45 	1 	0 	0 	0
2 	2000 	45 	0 	1 	0 	0
2 	2001 	90 	0 	1 	0 	0
2 	2002 	35 	0 	1 	0 	0
2 	2003 	79 	0 	1 	0 	0
2 	2004 	32 	0 	1 	0 	0], 'VariableNames',{'id','Year','y','Cat1','Cat2','Cat3','Cat4'})

T1 = 10x7 table
    id    Year    y     Cat1    Cat2    Cat3    Cat4
    __    ____    __    ____    ____    ____    ____

    1     2000    45     1       0       0       0  
    1     2001    56     1       0       0       0  
    1     2002    23     1       0       0       0  
    1     2003    56     1       0       0       0  
    1     2004    45     1       0       0       0  
    2     2000    45     0       1       0       0  
    2     2001    90     0       1       0       0  
    2     2002    35     0       1       0       0  
    2     2003    79     0       1       0       0  
    2     2004    32     0       1       0       0  

% Cat_mean = NaN(2, 4);
for k = 1:2%4
Cat_mean(:,k) = accumarray(T1{:,k+3}(T1{:,k+3} ~= 0), 1, [], @(x)mean(T1.y(x)));
end
Cat_mean

Cat_mean = 1x2

45 45

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

for k = 1:4
    Catu(:,k) = unique(T1{:,k+3},'stable');
end

Catu

Catu = 2x4

1 0 0 0 0 1 0 0

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

figure
hold on
for k = 1:2%4
    bar(k,Cat_mean(:,k), 'stacked')
end
hold off
set(gca,'XTick',(1:4))
xlabel('Cat')
ylabel('Counts')

This is not going to work correctly (without some restrictions) with the example data, however it probably will with the full data set.

.

[ektor]

can this grapn be inverted and put the names instead of 1 2?

Many thanks

[Star Strider]

The easiest way —

T1 = array2table([1 	2000 	45 	1 	0 	0 	0
1 	2001 	56 	1 	0 	0 	0
1 	2002 	23 	1 	0 	0 	0
1 	2003 	56 	1 	0 	0 	0
1 	2004 	45 	1 	0 	0 	0
2 	2000 	45 	0 	1 	0 	0
2 	2001 	90 	0 	1 	0 	0
2 	2002 	35 	0 	1 	0 	0
2 	2003 	79 	0 	1 	0 	0
2 	2004 	32 	0 	1 	0 	0], 'VariableNames',{'id','Year','y','Cat1','Cat2','Cat3','Cat4'})

T1 = 10x7 table
    id    Year    y     Cat1    Cat2    Cat3    Cat4
    __    ____    __    ____    ____    ____    ____

    1     2000    45     1       0       0       0  
    1     2001    56     1       0       0       0  
    1     2002    23     1       0       0       0  
    1     2003    56     1       0       0       0  
    1     2004    45     1       0       0       0  
    2     2000    45     0       1       0       0  
    2     2001    90     0       1       0       0  
    2     2002    35     0       1       0       0  
    2     2003    79     0       1       0       0  
    2     2004    32     0       1       0       0  

% Cat_mean = NaN(2, 4);
for k = 1:2%4
Cat_mean(:,k) = accumarray(T1{:,k+3}(T1{:,k+3} ~= 0), 1, [], @(x)mean(T1.y(x)));
end
Cat_mean

Cat_mean = 1x2

45 45

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

for k = 1:4
    Catu(:,k) = unique(T1{:,k+3},'stable');
end

Catu

Catu = 2x4

1 0 0 0 0 1 0 0

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

figure
hold on
for k = 1:2%4
    bar(k,Cat_mean(:,k), 'stacked')
end
hold off
set(gca,'XTick',(1:4), 'XTickLabel',compose('Cat%d',1:4))
% xlabel('Cat')
ylabel('Counts')

Another option is to use categorical tick labels, however that causes significant problems if you want to add anything numeric later, so I would not recommend it.

.

Answer 2

[Adam Danz]

Use groupsummary to compute the means of each group. Then plot the results using bar() with categorical x values.

This assumes "1" does not appear in more than 1 group.

% Input table
T = array2table([1 	2000 	45 	1 	0 	0 	0
1 	2001 	56 	1 	0 	0 	0
1 	2002 	23 	1 	0 	0 	0
1 	2003 	56 	0 	1 	0 	0
1 	2004 	45 	0 	1 	0 	0
2 	2000 	45 	0 	0 	1 	0
2 	2001 	90 	0 	0 	1 	0
2 	2002 	35 	0 	0 	0 	1
2 	2003 	79 	0 	0 	0 	1], ...
'VariableNames',{'id','Year','y','Cat1','Cat2','Cat3','Cat4'});

% Compute group means
groups = {'Cat1','Cat2','Cat3','Cat4'};
mu = groupsummary(T,groups,'mean','y')

mu = 4x6 table
    Cat1    Cat2    Cat3    Cat4    GroupCount    mean_y
    ____    ____    ____    ____    __________    ______

     0       0       0       1          2             57
     0       0       1       0          2           67.5
     0       1       0       0          2           50.5
     1       0       0       0          3         41.333

% Get the group order so ensure the groups are in the same order as the
% means.
groupOrder = varfun(@find,mu(:,1:numel(groups)),'OutputFormat','uniform');
means = mu.mean_y(groupOrder); 

% Plot results
bar(categorical(groups),means)
ylabel('mean')