Finding the averages for a unique text value

I have an excel document with data as below, I wish to obtain the (edit: mean and standard error) for column 6/F (RingSpotTotalIntenCh2) per unique identifier in Column 1/A. I have tried the following:

[~,~,dat]=xlsread(Target_filepath);
X=[dat(:,1) dat(:,6)];

mat = cell2mat(cellfun(@(V)accumarray(X(:,1),V,[],@mean),num2cell(X,1),'UniformOutput',false));

But AccumArray does not seem to like that Column 1/A is not a number:

Error using accumarray
Cells of first input SUBS must contain real, full, numeric vectors of equal length.

Error in ZaniaSpikeProteinIntensity>@(V)accumarray(X(:,1),V,[],@mean)

Error in ZaniaSpikeProteinIntensity (line 13)
mat = cell2mat(cellfun(@(V)accumarray(X(:,1),V,[],@mean),num2cell(X,1),'UniformOutput',false));

Is this possible? Edit: is it also possible to keep the identifiers (column 1) with the means?

Thanks

Answer 1

[Dyuman Joshi]

Just a FYI - MATLAB recommendation is not to use xlsread.

y=readtable("data.xlsx", "VariableNamingRule","preserve")

y = 8×6 table
     Well     Field    Cell Number    Top    Left    RingSpotTotalIntenCh2
    ______    _____    ___________    ___    ____    _____________________

    {'B2'}      1           1          2     514               25270      
    {'B2'}      1           2          2     541               34341      
    {'B2'}      1           3          3     237          1.0924e+05      
    {'B2'}      1           4          3     497               98446      
    {'B3'}      1           5          4     567          1.5289e+05      
    {'B3'}      1           6          5     114               71043      
    {'B4'}      1           7          6     368          1.6418e+05      
    {'B5'}      1           8          6     602          1.3617e+05      

[a,~,c]=unique(y.Well);
indmean=accumarray(c,y.RingSpotTotalIntenCh2,[],@mean)

indmean = 4×1

66824 111968 164185 136168

[Adam McGuinness]

Brilliant thank you, is it also possible to keep the First column to label the means, and also, is it possible to calculate the standard error?

[Dyuman Joshi]

Yes, you can see that output from unique() function call.

(The first output from unique can also be utilized in calling accumarray, as you can see it here in while calculating the mean)

y=readtable("data.xlsx", "VariableNamingRule","preserve")

y = 8×6 table
     Well     Field    Cell Number    Top    Left    RingSpotTotalIntenCh2
    ______    _____    ___________    ___    ____    _____________________

    {'B2'}      1           1          2     514               25270      
    {'B2'}      1           2          2     541               34341      
    {'B2'}      1           3          3     237          1.0924e+05      
    {'B2'}      1           4          3     497               98446      
    {'B3'}      1           5          4     567          1.5289e+05      
    {'B3'}      1           6          5     114               71043      
    {'B4'}      1           7          6     368          1.6418e+05      
    {'B5'}      1           8          6     602          1.3617e+05      

[a,~,c]=unique(y.Well)

a = 4×1 cell array
    {'B2'}
    {'B3'}
    {'B4'}
    {'B5'}

c = 8×1

1 1 1 1 2 2 3 4

indmean=accumarray(c,y.RingSpotTotalIntenCh2,[numel(a) 1],@mean)

indmean = 4×1

66824 111968 164185 136168

I guess you mean standard deviation, and yes, it can be calculated as well.

indstd=accumarray(c,y.RingSpotTotalIntenCh2,[],@std)

indstd = 4×1

1.0e+04 * 4.3131 5.7877 0 0

Answer 2

[Voss]

Target_filepath = 'Eg data.xlsx';

[~,~,dat]=xlsread(Target_filepath)

dat = 9×6 cell array
    {'Well'}    {'Field'}    {'Cell Number'}    {'Top'}    {'Left'}    {'RingSpotTotalIntenCh2'}
    {'B2'  }    {[    1]}    {[          1]}    {[  2]}    {[ 514]}    {[                25270]}
    {'B2'  }    {[    1]}    {[          2]}    {[  2]}    {[ 541]}    {[                34341]}
    {'B2'  }    {[    1]}    {[          3]}    {[  3]}    {[ 237]}    {[               109239]}
    {'B2'  }    {[    1]}    {[          4]}    {[  3]}    {[ 497]}    {[                98446]}
    {'B3'  }    {[    1]}    {[          5]}    {[  4]}    {[ 567]}    {[               152893]}
    {'B3'  }    {[    1]}    {[          6]}    {[  5]}    {[ 114]}    {[                71043]}
    {'B4'  }    {[    1]}    {[          7]}    {[  6]}    {[ 368]}    {[               164185]}
    {'B5'  }    {[    1]}    {[          8]}    {[  6]}    {[ 602]}    {[               136168]}

X = dat(2:end,[1 6])

X = 8×2 cell array
    {'B2'}    {[ 25270]}
    {'B2'}    {[ 34341]}
    {'B2'}    {[109239]}
    {'B2'}    {[ 98446]}
    {'B3'}    {[152893]}
    {'B3'}    {[ 71043]}
    {'B4'}    {[164185]}
    {'B5'}    {[136168]}

mat = accumarray(findgroups(X(:,1)),vertcat(X{:,2}),[],@mean)

mat = 4×1

66824 111968 164185 136168

[Adam McGuinness]

Brilliant thank you, is it also possible to keep the First column to label the means, and also, is it possible to calculate the standard error?