How do I get rid of NaNs in MATLAB?

Question

I have files which have many empty cells which appear as NaNs when I use cell2mat, but the problem is when I need to get the average values I cannot work with this as it shows error with NaN. In excel it overlooks NaN values, so how do I do the same in MATLAB?

In addition, I am writing a file using xlswrite:

xlswrite('test.xls',M);

I have data in all rows except 1. How do I write:

M(1,:) = ('time', 'count', 'length', 'width')

In other words, I want M(1,1)='time', M(1,2)='count', and so on. I have data from M(2,1) to M(10,20). How can I do this?

Answer 1

As AP correctly points out, you can use the function isfinite to find and keep only finite values in your matrix. You can also use the function isnan. However, removing values from your matrix can have the unintended consequence of reshaping your matrix into a row or column vector:

>> mat = [1 2 3; 4 NaN 6; 7 8 9]  % A sample 3-by-3 matrix

mat =

     1     2     3
     4   NaN     6
     7     8     9

>> mat = mat(~isnan(mat))  % Removing the NaN gives you an 8-by-1 vector

mat =

     1
     4
     7
     2
     8
     3
     6
     9

Another alternative is to use some functions from the Statistics Toolbox (if you have access to it) that are designed to deal with matrices containing NaN values. Since you mention taking averages, you may want to check out nanmean:

>> mat = [1 2 3; 4 NaN 6; 7 8 9];
>> nanmean(mat)

ans =

     4     5     6     % The column means computed by ignoring NaN values

---

EDIT: To answer your additional question on the use of xlswrite, this sample code should illustrate one way you can write your data:

C = {'time','count','length','width'};  % A cell array of strings
M = rand(10,20);                        % A 10-by-20 array of random values
xlswrite('test.xls',C);           % Writes C to cells A1 through D1
xlswrite('test.xls',M,'A2:T11');  % Writes M to cells A2 through T11

Answer 2

Use ' isfinite ' function to get rid of all NaN and infinities

A=A(isfinite(A))

%create the cell array containing the column headers columnHeader = {'Column 1', 'Column 2', 'Column 3', 'Column 4', 'Column 5',' '};

%write the column headers first xlswrite('myFile1.xls', columnHeader );

% write the data directly underneath the column headers xlswrite('newFile.xls',M,'Sheet1','A2');