Why do I get NaN values?

2019 4 22
0 个回答
更新时间：2019 4 24
2 次查看（30 天）

0 个投票

I have used exactly the same code for Sheet 5 and different column names and I get numerical values but when I use it at Sheet 6 and slightly adjacent columns but the same number of rows I get NaN.
Any idea why this happens?
x1 = readtable('ValidationTest.xls', 'Sheet',6, 'Range','N2:N385 ');
y1 = readtable('ValidationTest.xls', 'Sheet',6, 'Range','A2:A385 ');
x2 = readtable('ValidationTest.xls', 'Sheet',6, 'Range','O2:O868');
y2 = readtable('ValidationTest.xls', 'Sheet',6, 'Range','B2:B868');
P1 = polyfit(x1{:,:}, y1{:,:}, 6 )
P2 = polyfit(x2{:,:}, y2{:,:}, 6)
allX = unique([x1{:,:}; x2{:,:}]);
Pd = polyval(P1, allX) - polyval(P2, allX)
P11 = polyval(P1, allX)
P22 = polyval(P2, allX)
hold on
plot(allX, Pd, 'b')
plot(allX, P11, 'g')
plot(allX, P22, 'r')

13 个评论
显示 11更早的评论 隐藏 11更早的评论

madhan ravi
madhan ravi 2019-4-22
I don’t see how a help can be offered without you providing any data file.
Stelios Fanourakis
Stelios Fanourakis 2019-4-23
@madhan
All cell values are numerical values with decimals such as 0,043, 0,67 etc.
In the 'Range' there is none empty cell.
Walter Roberson
Walter Roberson 2019-4-23
A copy of the data file would make it much easier for us to investigate.
Stelios Fanourakis
Stelios Fanourakis 2019-4-23
In that case
%% Setup the Import Options
opts = spreadsheetImportOptions("NumVariables", 15);
% Specify sheet and range
opts.Sheet = "Sheet6";
opts.DataRange = "A2:O873";
% Specify column names and types
opts.VariableNames = ["m2readypng", "A6algpng", "Var3", "Var4", "Var5", "Var6", "Var7", "Var8", "Var9", "Var10", "Var11", "Var12", "Var13", "XX", "XXX"];
opts.SelectedVariableNames = ["m2readypng", "A6algpng", "XX", "XXX"];
opts.VariableTypes = ["double", "double", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "double", "double"];
opts = setvaropts(opts, [3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], "WhitespaceRule", "preserve");
opts = setvaropts(opts, [3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], "EmptyFieldRule", "auto");
% Import the data
ValidationTestS6 = readtable("/Users/steliosphanourakes/Desktop/Matlab/DesMoines/karadokei - Copy/ValidationTest.xls", opts, "UseExcel", false);
%% Convert to output type
e = table2array(ValidationTestS6);
%% Clear temporary variables
clear opts
P1 = polyfit(e(:,4), e(:,2), 6 )
P2 = polyfit(e(:,1), e(:,3), 6)
allX = unique([e(:,3); e(:,4)]);
Pd = polyval(P1, allX) - polyval(P2, allX)
P11 = polyval(P1, allX)
P22 = polyval(P2, allX)
hold on
plot(allX, Pd, 'b')
plot(allX, P11, 'g')
plot(allX, P22, 'r')
I used the automated Matlab method to import from excel.
Until the P1 = polyfit command the data are imported fine as numbers. Can you spot now the difference and why my import method didn't work?? (my method is described at the begining of this thread).
Now using the polyfit command I start getting NaN from that point and onwards. Why is this happening? Does it matter that the two columns I am trying to polyfit are not the same size? Why size should matter?
Stelios Fanourakis
Stelios Fanourakis 2019-4-24
Now since I turned all values to cell arrays, data are coming exactly how I want them to.
But by the time they get to polyfit I get the error
Error using polyfit
Too many input arguments.
Error in see (line 33)
P1 = polyfit(e{:,4}, e{:,2}, 6 )
I want to apply a curve fitting between the 4th column of the e sheet and the second column of the same sheet.
Stelios Fanourakis
Stelios Fanourakis 2019-4-24
I finally made it run with numbers but I don't think the results are correct since I am not getting plots in the end
{[ NaN]} {[5.6760]} {[ NaN]} {[37.0230]}
{[ NaN]} {[5.6760]} {[ NaN]} {[37.0660]}
{[ NaN]} {[5.6760]} {[ NaN]} {[37.1090]}
{[ NaN]} {[5.6760]} {[ NaN]} {[37.1520]}
{[ NaN]} {[5.6760]} {[ NaN]} {[37.1950]}
{[ NaN]} {[5.6760]} {[ NaN]} {[37.2380]}
{[ NaN]} {[ 0]} {[ NaN]} {[ NaN]}
{[ NaN]} {[ 0]} {[ NaN]} {[ NaN]}
{[ NaN]} {[ 0]} {[ NaN]} {[ NaN]}
{[ NaN]} {[ 0]} {[ NaN]} {[ NaN]}
{[ NaN]} {[ 0]} {[ NaN]} {[ NaN]}
{[ NaN]} {[ 0]} {[ NaN]} {[ NaN]}
Warning: Polynomial is not unique; degree >= number of data points.
> In polyfit (line 74)
In see (line 33)
P1 =
1.0e-03 *
0.3916 0 0 0 0 0 0
Warning: Polynomial is not unique; degree >= number of data points.
> In polyfit (line 74)
In see (line 34)
P2 =
1.0e-03 *
0.3916 0 0 0 0 0 0
Pd =
0
P11 =
4.8020
P22 =
4.8020
Is it correct if I write the commands with the brackets like this?
I want to say the 4th whole column of the cell matrix with the 2nd whole column of the cell matrix as X,Y and apply the 6th degree polynomial. The same underneath for 3rd and st column as X,Y. Is it correct written?
P1 = polyfit(e{4}, e{2}, 6 )
P2 = polyfit(e{3}, e{1}, 6)
allX = unique([e{3}; e{4}]);
Rik
Rik 2019-4-24
You still haven't attached the file (or a smaller file that reproduces the issue).
Stelios Fanourakis
Stelios Fanourakis 2019-4-24
Here are my data. Can you spot the problem Rik?
Stelios Fanourakis
Stelios Fanourakis 2019-4-24
In the case of that code
clc;
clear all;
%% Setup the Import Options
opts = spreadsheetImportOptions("NumVariables", 15);
% Specify sheet and range
opts.Sheet = "Sheet6";
opts.DataRange = "A2:O873";
% Specify column names and types
opts.VariableNames = ["m2readypng", "A6algpng", "Var3", "Var4", "Var5", "Var6", "Var7", "Var8", "Var9", "Var10", "Var11", "Var12", "Var13", "XX", "XXX"];
opts.SelectedVariableNames = ["m2readypng", "A6algpng", "XX", "XXX"];
opts.VariableTypes = ["double", "double", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "double", "double"];
opts = setvaropts(opts, [3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], "WhitespaceRule", "preserve");
opts = setvaropts(opts, [3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], "EmptyFieldRule", "auto");
% Import the data
ValidationTestS6 = readtable("/Users/steliosphanourakes/Desktop/Matlab/DesMoines/karadokei - Copy/ValidationTest.xls", opts, "UseExcel", false);
%% Convert to output type
e = table2array(ValidationTestS6);
x1 = e(:,4)
y1 = e(:,2)
x2 = e(:,3)
y2 = e(:,1)
%% Clear temporary variables
clear opts
P1 = polyfit(x1, y1, 6)
P2 = polyfit(x2, y2, 6)
allX = unique([x1; x2]);
Pd = polyval(P1, allX) - polyval(P2, allX)
P11 = polyval(P1, allX)
P22 = polyval(P2, allX)
hold on
plot(allX, Pd, 'b')
plot(allX, P11, 'g')
plot(allX, P22, 'r')
I get numbers until allX. From Pd and onwards I get NaN.
Why is that?
Stelios Fanourakis
Stelios Fanourakis 2019-4-24
Finally, after using that code I get results and plots. The point is that not for all excel sheets and datasets I get the exactly same graph, as in the excel. The graph I get in Matlab differs the graph I get from excel for some sheets.
Can you take a look at the script please, and tell me if anything is wrong?
%% Import data from spreadsheet
% Script for importing data from the following spreadsheet:
%
% Workbook: /Users/steliosphanourakes/Desktop/Matlab/DesMoines/karadokei - Copy/ValidationTest.xls
% Worksheet: Sheet12
%
% Auto-generated by MATLAB on 24-Apr-2019 17:55:58
clc;
clear all;
%% Setup the Import Options
opts = spreadsheetImportOptions("NumVariables", 16);
% Specify sheet and range
opts.Sheet = "Sheet12";
opts.DataRange = "A2:P868";
% Specify column names and types
opts.VariableNames = ["m8readypng", "A36algpng", "Var3", "Var4", "Var5", "Var6", "Var7", "Var8", "Var9", "Var10", "Var11", "Var12", "Var13", "XX", "Var15", "XXX"];
opts.SelectedVariableNames = ["m8readypng", "A36algpng", "XX", "XXX"];
opts.VariableTypes = ["double", "double", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "double", "string", "double"];
opts = setvaropts(opts, [3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 15], "WhitespaceRule", "preserve");
opts = setvaropts(opts, [3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 15], "EmptyFieldRule", "auto");
% Setup rules for import
opts.ImportErrorRule = "omitrow";
opts.MissingRule = "omitrow";
opts = setvaropts(opts, [1, 2, 3, 4], "TreatAsMissing", '');
% Import the data
ValidationTestS12 = readtable("/Users/steliosphanourakes/Desktop/Matlab/DesMoines/karadokei - Copy/ValidationTest.xls", opts, "UseExcel", false);
%% Convert to output type
ValidationTestS12 = table2array(ValidationTestS12);
%% Clear temporary variables
clear opts
e = ValidationTestS12;
x1 = e(:,4)
y1 = e(:,2)
x2 = e(:,3)
y2 = e(:,1)
%after the readtable
mask = isnan(x1) | isnan(y1);
x1(mask,:) = [];
y1(mask,:) = [];
mask = isnan(x2) | isnan(y2);
x2(mask,:) = [];
y2(mask,:) = [];
P1 = polyfit(x1, y1, 6)
P2 = polyfit(x2, y2, 6)
allX = unique([x1; x2]);
Pd = polyval(P1, allX) - polyval(P2, allX)
P11 = polyval(P1, allX)
P22 = polyval(P2, allX)
hold on
plot(allX, Pd, 'b')
plot(allX, P11, 'g')
plot(allX, P22, 'r')
John D'Errico
John D'Errico 2019-4-24
编辑：John D'Errico 2019-4-24
The file you attached has only one page in it. If I extract those 4 variables, it appears that y2 is constant at 4.8. ABSOLUTELY constant.
If I plot x1 and y1, I see this discreet mess:
That is what this is all about? An absolutely copnstant function, and a that mess?
Sorry, but how do you justify fitting a 6th degree poltynomial to that? You have asked how many questions on this so far?
Stelios Fanourakis
Stelios Fanourakis 2019-4-24
编辑：Stelios Fanourakis 2019-4-24
@John
The graph is correct. It should be like this more or less. By luck, I sent you one dataset with a constant value. Not all sheets are the same. It’s just happened for the particular one. Most of the datasets are curves.
Hopefully, I need to ask as many questions as I need in order to get the right answer. Hope it won’t bother.
Walter Roberson
Walter Roberson 2019-4-24
That code is not set up to read the VTest.xlsx file you provided.
The earlier code was set up to read it (once the file name was changed), but it did not take into account that two of the columns were shorter than the others.

请先登录，再进行评论。

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

回答（0 个）

类别

在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by