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

Question

Giuseppe 2022-2-1

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1640250-how-can-i-create-a-rule-to-automatically-fill-blank-cells-during-the-table-reading-with-readtable-fu

编辑： Arif Hoq 2022-2-2

采纳的回答： Arif Hoq

NEOs_asteroids.csv

在 MATLAB Online 中打开

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

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Arif Hoq 2022-2-1

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1640250-how-can-i-create-a-rule-to-automatically-fill-blank-cells-during-the-table-reading-with-readtable-fu#answer_886240

编辑：Arif Hoq 2022-2-1

在 MATLAB Online 中打开

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 个评论
显示 1更早的评论隐藏 1更早的评论

Arif Hoq 2022-2-1

在 MATLAB Online 中打开

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 2022-2-2

编辑：Giuseppe 2022-2-2

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

Arif Hoq 2022-2-2

编辑：Arif Hoq 2022-2-2

在 MATLAB Online 中打开

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.

请先登录，再进行评论。

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

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

3 个评论
显示 1更早的评论隐藏 1更早的评论

更多回答（0 个）

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

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

0 个评论 显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

3 个评论 显示 1更早的评论隐藏 1更早的评论

更多回答（0 个）

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

3 个评论
显示 1更早的评论隐藏 1更早的评论