My xlsx / csv dataset is getting filled with NaNs

19 views (last 30 days)
Samuel
Samuel on 22 May 2014
Answered: dpb on 22 May 2014
I am in the process of importing a set of xlsx data values using matlab's "import data" function, and extracting out the code. However, for the first time, the data I import gets filled with NaNs. These are data values that I exported from a measurement device, and therefore should all be numeric and have no reason to be not classified as so.
The dataset file I am trying to use is attached, and the specific routine imported from matlab is below. Thanks for any help in advance.
%%Import data from spreadsheet
% Script for importing data from the following spreadsheet:
%
% Workbook:
%
% Worksheet: Sheet1
%
% To extend the code for use with different selected data or a different
% spreadsheet, generate a function instead of a script.
% Auto-generated by MATLAB on 2014/05/22 00:21:19
close all;clc;clear all;
%%Import the data
[~, ~, raw] = xlsread('C:\matlab\settotal.xlsx','Sheet1','A2:AV1713');
raw(cellfun(@(x) ~isempty(x) && isnumeric(x) && isnan(x),raw)) = {''};
%%Replace non-numeric cells with NaN
R = cellfun(@(x) ~isnumeric(x) && ~islogical(x),raw); % Find non-numeric cells
raw(R) = {NaN}; % Replace non-numeric cells
%%Create output variable
data = reshape([raw{:}],size(raw));
%%Allocate imported array to column variable names
des1e = data(:,1);
des1t = data(:,2);
des1c = data(:,3);
des2e = data(:,4);
des2t = data(:,5);
des2c = data(:,6);
des3e = data(:,7);
des3t = data(:,8);
des3c = data(:,9);
des4e = data(:,10);
des4t = data(:,11);
des4c = data(:,12);
deb1e = data(:,13);
deb1t = data(:,14);
deb1c = data(:,15);
deb2e = data(:,16);
deb2t = data(:,17);
deb2c = data(:,18);
deb3e = data(:,19);
deb3t = data(:,20);
deb3c = data(:,21);
deb4e = data(:,22);
deb4t = data(:,23);
deb4c = data(:,24);
ds1se = data(:,25);
ds1st = data(:,26);
ds1sc = data(:,27);
ds2se = data(:,28);
ds2st = data(:,29);
ds2sc = data(:,30);
ds3se = data(:,31);
ds3st = data(:,32);
ds3sc = data(:,33);
ds4se = data(:,34);
ds4st = data(:,35);
ds4sc = data(:,36);
ds5be = data(:,37);
ds5bt = data(:,38);
ds5bc = data(:,39);
ds6be = data(:,40);
ds6bt = data(:,41);
ds6bc = data(:,42);
ds7be = data(:,43);
ds7bt = data(:,44);
ds7bc = data(:,45);
ds8be = data(:,46);
ds8bt = data(:,47);
ds8bc = data(:,48);
%%Clear temporary variables
clearvars data raw R;
end
  1 Comment
Geoff Hayes
Geoff Hayes on 22 May 2014
Do you mean to say that all of the data in your data set appears as NaNs when you import it, or just a subset of the data? I imported your data and did observe some NaNs, but these seemed to correspond with empty or blank cells within the first sheet of the Excel document, so the appearance of NaNs makes sense. Are you observing something different?

Sign in to comment.

Answers (1)

dpb
dpb on 22 May 2014
Starting at row 1389 you've got holes in your data -- xlsread fills those in as NaN by default. There are a lot more holes later on...
Why don't you just use xlsread directly?
[d,t]=xlsread('C:\matlab\settotal.xlsx');
will give you the numeric values in array d and the header text in t w/o any other hoopla needed. Still have the NaN for the missing locations, but that's owing to your data, not xlsread
Lines in the spreadsheet are as follows at line 1389ff (truncated after column 5--
1389 0.036120115 47.97346083 0.01559 0.035450712 75.52568539 ...
1390 0.036146386 47.9745615 0.01559 ...
...

Products

Community Treasure Hunt

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

Start Hunting!