How can I create a rule to automatically fill blank cells during the table reading with readtable function?

Hi guys!

I read a .csv file in matlab following this procedure: home -> importdata -> select my .csv file. Then I properly set the data type of first two columns by selecting text(string). Some cells of the second column are empty and when I recall the variable in the command window I get "".

Then I filter my data and once exported them in a .txt file I have empty cells: this can be a problem when I will read the data in my Fortran program. So, I would like to fill the blank space with a default string, such as "(Empty cell)" or something like that.

Can you help me?

Here the code I produced by following the above procedure:

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
Output_file_name = 'NEOs_asteroids_filtered.txt';
writetable(Ast_data_filtered,Output_file_name,...
    "WriteVariableNames",true,"Encoding",'UTF-8',"Delimiter","tab");

Answer 1

[Arif Hoq]

I think you are facing problem in column 2 and you want to replace the empty cell with any string. it can be 'Empty cell' or any other string.please check my part(Answer part)

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

%% Answer part
A=table2array(Ast_data);  % converting to array
B=A(:,2);  % extract only problematic column i.e column 2
% C=num2cell(A);  % converting to cell
idx=find(cellfun(@isempty, B)); % find the index of empty cell
B(idx)='Empty Cell';   % replace the empty cell with string 'Empty cell'
A(:,2)=[];  % delete column 2 from array A
insert_b_col=[A(:,1) B A(:,2:end)]; %insert column 2 into array A
pdes=insert_b_col(:,1);
name=insert_b_col(:,2);
epoch=insert_b_col(:,3);
a=insert_b_col(:,4);
e=insert_b_col(:,5);
i=insert_b_col(:,6);
om=insert_b_col(:,7);
w=insert_b_col(:,8);
ma=insert_b_col(:,9);
q=insert_b_col(:,10);
ad=insert_b_col(:,11);
T = table(pdes,name,epoch,a,e,i,om,w,ma,q,ad); % expected table

%%
%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
Output_file_name = 'NEOs_asteroids_filtered.txt';
writetable(Ast_data_filtered,Output_file_name,...
    "WriteVariableNames",true,"Encoding",'UTF-8',"Delimiter","tab");

[Arif Hoq]

Or you can add the code after this line:

Ast_data_filtered = Ast_data(Ast_cond,:);

like this;

%% 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,:);

%% Answer Part

A=table2array(Ast_data_filtered);  % converting to array
B=A(:,2);  % extract only problematic column i.e column 2
% C=num2cell(A);  % converting to cell
idx=find(cellfun(@isempty, B)); % find the index of empty cell
B(idx)='Empty Cell';   % replace the empty cell with string 'Empty cell'
A(:,2)=[];  % delete column 2 from array A
insert_b_col=[A(:,1) B A(:,2:end)];
pdes=insert_b_col(:,1);
name=insert_b_col(:,2);
epoch=insert_b_col(:,3);
a=insert_b_col(:,4);
e=insert_b_col(:,5);
i=insert_b_col(:,6);
om=insert_b_col(:,7);
w=insert_b_col(:,8);
ma=insert_b_col(:,9);
q=insert_b_col(:,10);
ad=insert_b_col(:,11);
T = table(pdes,name,epoch,a,e,i,om,w,ma,q,ad); % expected table

%%
%Data export for Fortran calculations
Output_file_name = 'NEOs_asteroids_filtered.txt';
writetable(Ast_data_filtered,Output_file_name,...
    "WriteVariableNames",true,"Encoding",'UTF-8',"Delimiter","tab");

[Giuseppe]

Hi @Arif Hoq, thanks for answer. Isnt'possbile to apply your solution directly as option of readtable function?

[Arif Hoq]

why not? just replace 'Ast_data_filtered' with 'T'. follow this code

writetable(T,Output_file_name,...
    "WriteVariableNames",true,"Encoding",'UTF-8',"Delimiter","tab");

you can check your txt file in the Current Folder.