Why do I get NaN values?

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')

[madhan ravi]

I don’t see how a help can be offered without you providing any data file.

[Stelios Fanourakis]

@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]

A copy of the data file would make it much easier for us to investigate.

[Stelios Fanourakis]

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]

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]

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]

You still haven't attached the file (or a smaller file that reproduces the issue).

[Stelios Fanourakis]

Here are my data. Can you spot the problem Rik?

[Stelios Fanourakis]

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]

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]

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]

@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]

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.