export csv file without NaN values

Hi,

I am using the following code:

filename='AllFirms.csv';

%write string to csv

fileID = fopen(filename,'wt');
[rows, columns] = size(DATAtxt);
for index = 1:rows    
    fprintf(fileID, '%s,', DATAtxt{index,1:end-1});
    fprintf(fileID, '%s\n', DATAtxt{index,end});
end 

fclose(fileID);

%write numerical data to csv
dlmwrite(filename, DATA,'-append', 'delimiter', ',');

where DATA is a numeric matrix and DATAtxt contains the header names of the columns. This code ,however, writes NaN values which causes me problems later on . How can I export these NaN values as empty ?

Answer 1

[Azzi Abdelmalek]

 DATA(isnan(DATA))=0

[joseph Frank]

this will replace nans with zeros. I don't want to do that because a missing value is not a zero. It will affect my regression results later on if I do that. since I am exporting to csv,can't I have empty cells in the csv file without any word or number in them?

[Azzi Abdelmalek]

% convert DATA to a cell array

DATA1=num2cell(DATA)
DATA1(isnan(DATA))={[]}

Then export DATA1

[Elena Bertseva]

Nice idea, but in my case, when the NaNs are on the ends of the lines and rows, dlmwrite gives: "The input cell array cannot be converted to a matrix."

Answer 2

[Image Analyst]

Give an example of a line where you're writing data that is nan. All I see is the first part where you write the string data but I don't know how you want the numerical data. For example, let's say DATA = [1,2,nan, 4,5]. Do you want

1,2,,4,5

or do you want

1,2,4,5

in the csv file? And why can't you just write it out a number at a time using isnan() and fprintf()? You don't have to use dlmwrite() you know.