MATLAB Answers

export csv file without NaN values

39 views (last 30 days)
joseph Frank
joseph Frank on 28 Sep 2013
Commented: Elena Bertseva on 2 Jun 2017
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 ?

  0 Comments

Sign in to comment.

Answers (2)

Azzi Abdelmalek
Azzi Abdelmalek on 28 Sep 2013
Edited: Azzi Abdelmalek on 28 Sep 2013
DATA(isnan(DATA))=0

  3 Comments

joseph Frank
joseph Frank on 28 Sep 2013
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
Azzi Abdelmalek on 28 Sep 2013
% convert DATA to a cell array
DATA1=num2cell(DATA)
DATA1(isnan(DATA))={[]}
Then export DATA1
Elena Bertseva
Elena Bertseva on 2 Jun 2017
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."

Sign in to comment.


Image Analyst
Image Analyst on 28 Sep 2013
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.

  0 Comments

Sign in to comment.

Tags