Compute average of a column in a cell without considering NaN values

1 view (last 30 days)
I have a cell array with 3 columns and 130000 rows:
A={56276 2009 '-'
56278 2009 NaN
56281 2009 NaN
56285 2009 33.9
56301 2009 '-'
56313 2009 42.5}
I want to have the mean of the values of the third column. If I use the following code,
mean(cell2mat(A(:,3)))
I get an error because I am inlcuding 'NaN' and '-' elements to do the average and I only want to do the average of the numbers.
Can someone help me? Thanks
  2 Comments
Andrew Reibold
Andrew Reibold on 28 Jul 2014
Edited: Andrew Reibold on 28 Jul 2014
Do you mean 3 columns and 130,000 rows as is begun in your example?
And I am assuming 42,5 should be 42.5

Sign in to comment.

Accepted Answer

Azzi Abdelmalek
Azzi Abdelmalek on 28 Jul 2014
Edited: Azzi Abdelmalek on 28 Jul 2014
A={56276 2009 '-'
56278 2009 NaN
56281 2009 NaN
56285 2009 33.9
56313 2009 42.5}
c3=cell2mat(A(2:end,3))
out=nanmean(c3)
%or
out=mean(c3(~isnan(c3)))
  2 Comments
Maria
Maria on 28 Jul 2014
But the '-' appears several times along the column, not only in the first row!

Sign in to comment.

More Answers (2)

Image Analyst
Image Analyst on 28 Jul 2014
It may not be the most compact way, but it's simple and easy to understand:
clc;
A={56276 2009 '-';...
56278 2009 NaN;...
56281 2009 NaN;...
56285 2009 33.9;...
56301 2009 '-';...
56313 2009 42.5}
col3 = A(:,3)
theSum = 0;
counter = 0;
for row = 1 : size(col3, 1)
if isnumeric(col3{row}) & ~isnan(col3{row})
theSum = theSum + col3{row};
counter = counter + 1;
end
end
theMean = theSum / counter

Andrew Reibold
Andrew Reibold on 28 Jul 2014
Edited: Andrew Reibold on 28 Jul 2014
Hi Maria,
Here is a rough example of how to get the mean value for EVERY column gathered in your matrix. Its definitely not the best practice, but I think it will work for you.
for clmn = 1:130000
rowsum= 0;
count = 0;
for row = 1:3
if isnumeric(A{rw,clmn}) & ~isnan(A{rw,clmn})
rowsum = rowsum+ A{rw,clmn}
count= count+1
end
end
MeanOfEachColumn(clmn) = rowsum/count;
end
Now if you want the mean of column three, you can just ask for MeanOfEachColumn(3). You can do this with any column you want now.
Variable names are annoyingly long for clarity. I would play with this and let me know if you get errors
I wrote this very quickly and with minimal testing. Let me know if you find walls you can't overcome.

Categories

Find more on Characters and Strings in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!