MATLAB Answers

0

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

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 Comment

Attached the file that I used for NaN rows deletion. PLEASE HELP :(

Sign in to comment.

Tags

3 Answers

Answer by Shubham Gupta on 8 Jan 2019
 Accepted Answer

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 Comments

Hi Walter,
in relation to what you said 'You can select columns out of the everything variable.; I tried creating another variable to specify the columns I wanted to show then do the deletion of rows, however I had error saying :
[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
%Here is the error when I made changes
>>Unable to use a value of type 'cell' as an index.
>>Error in attempt4 (line 12)
PCBRequiredCol = everything(:,{'PcbTestID','PcbID','Verify12VInput','VerifyCurrentDraw','RailNve5V','Rail3V3'});
Is there another way to specify columns using the everything variable?
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'}};
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

Sign in to comment.


Answer by Cris LaPierre
on 8 Jan 2019

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.

  0 Comments

Sign in to comment.


Answer by Walter Roberson
on 8 Jan 2019
Edited by Walter Roberson
on 8 Jan 2019

Use readtable() . If you are using R2016b or later, you can use rmmissing() to remove the table rows with missing entries.

  0 Comments

Sign in to comment.