How read the exact number of decimal digits with readtable from a .csv file?

Hi guys,

I want to read a .csv file containing numbers with more than 17 decimal digits (I'm not sure of the exact number of these decimal digits) by using the function readtable.

I manage to perform this task but the collected data has a reduced number of decimal digits with respect to data stored into orginal .csv file.

How can I keep the same number of decimal digits as in the original file in matlab?

Here an example code that tries to read the .csv file "NEOs_asteroids.csv" attached to this question.

clear all; close all; clc
file_name_asteroids = 'NEOs_asteroids.csv';

% Options settings 
opts = detectImportOptions(file_name_asteroids);
opts.DataLines = 2;
opts.VariableNamesLine = 1;

% Read data into a table array
Asteroids_orbit_elem = readtable(file_name_asteroids,opts);

[Stephen23]

"I want to read a .csv file containing numbers with more than 17 decimal digits..."

In fact your file data contains maximum 16 significant digits:

str = fileread('NEOs_asteroids.csv');
raw = regexp(str,'\d*\.?\d+','match');
len = cellfun(@numel,regexprep(raw,'(^0?\.0+|\.)',''));
[mxl,idx] = max(len)

mxl = 16

idx = 3

raw{idx}

ans = '1.458273100364966'

"How can I keep the same number of decimal digits as in the original file in matlab?"

Whilst importing the file data as text is certainly possible and will retain all of the digits as you request, it will make processing your data afterwards slow and complex. What is much more likely is that you are misunderstand the significance (in the mathematical sense) of those digits and what they represent in DOUBLE values.

Lets take a look at that number:

format long G 
num = str2double(raw{idx})

num = 
          1.45827310036497

fprintf('%.42f\n',num)

1.458273100364966046171844027412589639425278

fprintf('%.16g\n',num)

1.458273100364966

Those values were (most likely) originally saved from DOUBLE values. MATLAB imports those values as the closest DOUBLE values, which perfectly match the DOUBLE values used to create the file. Lets try converting those strings to double and then back again, to see if we lose any information:

vec = str2double(raw);
isequal(vec,str2double(compose("%.16g",vec))) % round-trip conversion

ans = logical
   1

Yep, exactly the same (just as expected). So what you are trying to do is based on a misunderstanding of the significance of DOUBLE values, and will be a lot of effort for absolutely zero benefit.

[Giuseppe]

Hi @Stephen, thanks for your answer. So I can safely use the readtable function for reading?

[Stephen23]

@Giuseppe: yes.

[Giuseppe]

Hi @Stephen, when I export my file in a text file, I lose some digits (I've checked some numbers and I lose 1 digit) as you can see by comparing the attached files. How can I fix?

P.S. I know the speech about significant digits but I need to mantain within the output file the same numbers of the original file.

Input file: NEOs_asteroids.csv (it is attached in my first question);

Output file: NEOs_asteroids_filtered.txt

Here the code:

clear all; close all; clc;

%Data import from .CSV files
file_name_asteroids = 'NEOs_asteroids.csv';

%Asteroid data reading 
opts = delimitedTextImportOptions("NumVariables", 11);

% Specify range and delimiter
opts.DataLines = [2, Inf];
opts.Delimiter = ",";

% Specify column names and types
opts.VariableNames = ["pdes", "name", "epoch", "a", "e", "i", "om", "w", "ma", "q", "ad"];
opts.VariableTypes = ["string", "string", "double", "double", "double", "double", "double", "double", "double", "double", "double"];

% Specify file level properties
opts.ExtraColumnsRule = "ignore";
opts.EmptyLineRule = "read";

% Specify variable properties
opts = setvaropts(opts, ["pdes", "name"], "WhitespaceRule", "preserve");
opts = setvaropts(opts, ["pdes", "name"], "EmptyFieldRule", "auto");

% Import the data
Ast_data = readtable(file_name_asteroids,opts);

%%Data filtering

i_max = 5; % (deg) 
e_max = 0.1;
q_min = 0.9;  %(AU)
ad_max = 1.1; % (AU)


Ast_cond = Ast_data.i <= i_max & Ast_data.e <= e_max &...
                Ast_data.q >= q_min & Ast_data.ad <= ad_max;

Ast_data_filtered = Ast_data(Ast_cond,:);

%Data export for Fortran calculations

% find empty cells
% emptyCells = cellfun(@isempty,Ast_data.name);

% Fill empty cells with a customized string
% Ast_data.name(emptyCells) = '(Empty cell)'

Output_path = 'D:\OneDrive\MSc_Thesis\Projects\NEOs_orbits\InputFiles\';
Output_file_name = 'NEOs_asteroids_filtered.txt';
writetable(Ast_data_filtered,[Output_path,Output_file_name],...
    "WriteVariableNames",true,"Encoding",'UTF-8',"Delimiter","comma");

[Stephen23]

WRITETABLE writes text files using FORMAT LONG G, so it has 15 significant digits:

https://www.mathworks.com/help/matlab/ref/writetable.html#btydgw3-4

If you must maintain exactly the same information as the original file then you can import the file data as text.

[Giuseppe]

I tried but I have issues with filtering. So I need to reconvert to number keeping the same digits. Some ideas?

[Stephen23]

"So I need to reconvert to number keeping the same digits. Some ideas?"

Convert to numeric for the filtering, but write the original text to the new file (not the numbers).

[Giuseppe]

How can I link the filtered numerical data to the original data?

[Stephen23]

"How can I link the filtered numerical data to the original data?"

That is exactly what indexing is for. See my answer.

Answer 1

[Stephen23]

This imports the data as text, converts to numeric for the filter calculation, and then saves the text. Caveat: it does not preserve the double quotes, which are removed automatically by READTABLE.

i_max = 5; % (deg) 
e_max = 0.1;
q_min = 0.9;  %(AU)
ad_max = 1.1; % (AU)

fnm = 'NEOs_asteroids.csv';
opt = detectImportOptions(fnm);
opt = setvaropts(opt,'Type','string');
tbs = readtable(fnm, opt);                         % <- table of text data!
tbn = array2table(str2double(tbs{:,:}),...
    'VariableNames',tbs.Properties.VariableNames); % <- table of numeric data!
idx = tbn.i<=i_max & tbn.e<=e_max & tbn.q>=q_min & tbn.ad<=ad_max;
writetable(tbs(idx,:),'NEOs_asteroids_filtered.csv')

And if you want to filter those tables (e.g. for further processing in MATLAB) then of course you can trivially use exactly the same index:

tbs_filtered = tbs(idx,:) % <- filtered text table!
tbn_filtered = tbn(idx,:) % <- filtered numeric table!

[Stephen23]

If the double quotes are significant you can add them back in yourself, e.g.:

fnm = 'NEOs_asteroids.csv';
opt = detectImportOptions(fnm);
opt = setvaropts(opt,'Type','string');
tbs = readtable(fnm, opt);
mat = str2double(tbs{:,:});
idn = isnan(mat);
tmp = tbs{:,:};
tmp(idn) = strcat('"',tmp(idn),'"');
tbs{:,:} = tmp;
tbn = array2table(mat, 'VariableNames',tbs.Properties.VariableNames);
idx = tbn.i<=i_max & tbn.e<=e_max & tbn.q>=q_min & tbn.ad<=ad_max;
writetable(tbs(idx,:),'NEOs_asteroids_filtered.csv')

[Giuseppe]

Oh, thanks very much! Can you also show me a short procedure to fill the empty the fields of column "name" with the string"(NO NAME)" within the strings variable?

[Stephen23]

"...fill the empty the fields of column "name" with the string"(NO NAME)" within the strings variable?"

ide = ismissing(tbs.name);
tbs.name(ide) = "(NO NAME)";

Answer 2

[David Hill]

The digits should still be there (just not displayed) as long as they do not exceed floating point accurracy. Look at this:

file_name_asteroids = 'NEOs_asteroids.csv';
opts = detectImportOptions(file_name_asteroids);
opts.DataLines = 2;
opts.VariableNamesLine = 1;
Asteroids_orbit_elem = readtable(file_name_asteroids,opts);
format long
Asteroids_orbit_elem(1:10,:)

[Giuseppe]

@David Hill thank for your answer. As I wrote in the above answer, I lose some digits when exporting to a new file. Can you check and give me a feedback?