Finding the averages for a unique text value

3 views (last 30 days)
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

Answers (2)

Dyuman Joshi
Dyuman Joshi on 2 Nov 2022
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
  2 Comments
Dyuman Joshi
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=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

Sign in to comment.


Voss
Voss on 2 Nov 2022
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
  1 Comment
Adam McGuinness
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?

Sign in to comment.

Categories

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

Products


Release

R2019a

Community Treasure Hunt

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

Start Hunting!

Translated by