How to prevent .xlsread to turn string data to NaN value

72 ビュー (過去 30 日間)
Andrea Del Rosario
Andrea Del Rosario 2019 年 1 月 8 日
Hi there,
I got this code at the moment. I converted my file to .xlsx so I can read my data in matlab. What the forloop does is it goes through the data and goes through cells that contains NaN value then deletes the entire row, resulting in no NaN values in the data set. However, in the end the columns that contained 'string' values turns to NaN value. How do I convert back those NaN values to string values again?
% Reads as xlsx file and contains a forloop that deletes rows that contains
% NaN value - However, when xlsxread is used, the columns that contains strings turns to NaN value.
T = xlsread('testfile4.xlsx');
for col = 6:size(T,2)
i = find(isnan(T(:,col))); %find all row with “NaN” in this col
T(i ,:) = [];
end
This is what it looks like after deleting the rows with NaN values. Now the second and third columns contains NaN values which was strings before.
sampleData7.PNG
  1 件のコメント
Andrea Del Rosario
Andrea Del Rosario 2019 年 1 月 8 日
Attached the file that I used for NaN rows deletion. PLEASE HELP :(

サインインしてコメントする。

採用された回答

Shubham Gupta
Shubham Gupta 2019 年 1 月 8 日
MATLAB doesn't give the option to store 'string' and 'double' in an double array. You can read the excel file and store it in a 'cell' array. You can try this :
[number,string,everything]=xlsread('testfile4.xlsx');
for col=5:2:size(everything,2)
rind=find(number(:,col)==0);
everything(rind+1,:)=[]; % '+1' because there are names in 1st row
end
I hope this helps
  5 件のコメント
Walter Roberson
Walter Roberson 2019 年 1 月 8 日
You cannot index a cell array by column name, only by column number. You can index a table by column name:
everything = readtable('testfile4.xlsx');
PCBRequiredCol = everything(:,{'PcbTestID','PcbID','Verify12VInput','VerifyCurrentDraw','RailNve5V','Rail3V3'});
The result would be a table object with those columns. The PcbID column is character so you cannot just convert this to numeric without excluding that column
num = PCBRequiredCol{:, {'PcbTestID','Verify12VInput','VerifyCurrentDraw','RailNve5V','Rail3V3'}};
Andrea Del Rosario
Andrea Del Rosario 2019 年 1 月 8 日
That make sense, but where do I plug this in with my code at the moment? Would you please be able to show me? This is what I have at the moment :(
[number,string,everything]=xlsread('testfile4.xlsx');
%New variable added to choose specific columns
PCBRequiredCol = everything(:,{'PcbTestID','PcbID','Verify12VInput','VerifyCurrentDraw','RailNve5V','Rail3V3'});
for col=5:2:size(PCBRequiredCol,2)
rind=find(number(:,col)==0);
PCBRequiredCol(rind+1,:)=[]; % '+1' because there are names in 1st row
end

サインインしてコメントする。

その他の回答 (2 件)

Cris LaPierre
Cris LaPierre 2019 年 1 月 8 日
xlsread with a single output only returns numeric data.
  • num = xlsread(filename) reads the first worksheet in the Microsoft® Excel® spreadsheet workbook named filename and returns the numeric data in a matrix.
If you want to capture other data types, you have to call it with more output variables.
[num,txt,raw] = xlsread(___)
The variable raw will contain all the data. Look at the documentation page for the details.

Walter Roberson
Walter Roberson 2019 年 1 月 8 日
編集済み: Walter Roberson 2019 年 1 月 8 日
Use readtable() . If you are using R2016b or later, you can use rmmissing() to remove the table rows with missing entries.

カテゴリ

Help Center および File ExchangeSpreadsheets についてさらに検索

タグ

Community Treasure Hunt

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

Start Hunting!

Translated by