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

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

[Andrew Reibold]

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

[Maria]

Yes I am sorry. I will edit the question

Answer 1

[Azzi Abdelmalek]

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)))

[Maria]

But the '-' appears several times along the column, not only in the first row!

[Azzi Abdelmalek]

c3=A(cellfun(@(x)  ~isnan(x) & x~='-',A(:,3)),3)
out=mean(cell2mat(c3))

Answer 2

[Image Analyst]

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]

I'd check this answer out Maria ^

Answer 3

[Andrew Reibold]

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.