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

41 views (last 30 days)
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");

Accepted Answer

Arif Hoq
Arif Hoq on 1 Feb 2022
Edited: Arif Hoq on 1 Feb 2022
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");
  3 Comments
Arif Hoq
Arif Hoq on 2 Feb 2022
Edited: Arif Hoq on 2 Feb 2022
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.

Sign in to comment.

More Answers (0)

Categories

Find more on Cell Arrays in Help Center and File Exchange

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by