# Finding the averages for a unique text value

3 views (last 30 days)
Adam McGuinness on 2 Nov 2022
Commented: Dyuman Joshi on 2 Nov 2022
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:
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 Dyuman Joshi on 2 Nov 2022
Just a FYI - MATLAB recommendation is not to use xlsread.
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
##### 2 CommentsShowHide 1 older comment
Dyuman Joshi on 2 Nov 2022
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 = 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

Voss on 2 Nov 2022
Target_filepath = 'Eg data.xlsx';
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'} {} {'B2'} {[ 98446]} {'B3'} {} {'B3'} {[ 71043]} {'B4'} {} {'B5'} {}
mat = accumarray(findgroups(X(:,1)),vertcat(X{:,2}),[],@mean)
mat = 4×1
66824 111968 164185 136168
Adam McGuinness on 2 Nov 2022
Edited: Adam McGuinness on 2 Nov 2022
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?

### Categories

Find more on Data Import from MATLAB in Help Center and File Exchange

R2019a

### Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!