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

37 visualizaciones (últimos 30 días)
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");

Respuesta aceptada

Arif Hoq
Arif Hoq el 1 de Feb. de 2022
Editada: Arif Hoq el 1 de Feb. de 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 comentarios
Giuseppe
Giuseppe el 2 de Feb. de 2022
Editada: Giuseppe el 2 de Feb. de 2022
Hi @Arif Hoq, thanks for answer. Isnt'possbile to apply your solution directly as option of readtable function?
Arif Hoq
Arif Hoq el 2 de Feb. de 2022
Editada: Arif Hoq el 2 de Feb. de 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.

Iniciar sesión para comentar.

Más respuestas (0)

Categorías

Más información sobre Tables en Help Center y File Exchange.

Productos


Versión

R2021a

Community Treasure Hunt

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

Start Hunting!

Translated by