1

I use xlsread to read a xlsx file. I expect the rawdata will return a 34x3 cell array.

[raw_num raw_txt rawdata]=xlsread('file.xlsx');

The raw_num and raw_txt return correct value, 34x1 cell array and 34x2 cell array.

However, when I check rawdata, it returns 139x6 cell array. It contains not only data in excel file but also NaN element. For the NaN element, there is no data in excel file. I can only assume that I might type something before and then I delete it. But why matlab read it?

The rawdata looks like this:

'a' 'b' 'c' NaN NaN

'd' 'e' 'f' NaN NaN

NaN NaN NaN NaN NaN

How can I avoid this when I use xlsread?

Or how can I remove NaN after I got this matrix?

Thanks for help

Vahe Tshitoyan
  • 1,439
  • 1
  • 11
  • 21
chshiu
  • 25
  • 5

2 Answers2

0

You can remove NaN's like below:

k = {'a' 'b' 'c' NaN NaN}
k(cellfun(@isnan, k)) = [] ;
Siva Srinivas Kolukula
  • 1,251
  • 1
  • 7
  • 14
0

If NaN elements are only at the edges of the matrix like shown in your example, you could first remove rows with all NaN, then remove the columns with all NaN.

% test data
A = {NaN, NaN, NaN, NaN; NaN, NaN, NaN, NaN; NaN, NaN, NaN, NaN};
A{1, 1} = 'a';
A{1, 2} = 'b';
A{2, 1} = 'c';
A{2, 2} = 'd';

fh = @(x) all(isnan(x));
% remove rows with all NaN
A(all(cellfun(fh, A),2),:) = [];
% remove columns with all NaN
A(:,all(cellfun(fh, A),1)) = [];

The reason you have to do it this way is because cellfun does not preserve dimensions, so you have to run it on each dimension individually. Alternatively, you could write a for loop.

Vahe Tshitoyan
  • 1,439
  • 1
  • 11
  • 21
  • all(cellfun(fh, A)) only returns the logic of NaN at "first row". So when NaN in matrix A is not the same in row and column, this code cannot execute appropriately. For example, I change A to a 3x4 cell: A = {NaN, NaN, NaN, NaN; NaN, NaN, NaN,NaN; NaN, NaN, NaN,NaN}; A{1, 1} = 'a'; A{1, 2} = 'b'; A{2, 1} = 'c'; A{2, 2} = 'd'; then A(all(cellfun(fh, A)),:) = [] will show error – chshiu Jun 07 '17 at 11:15
  • I think it should be: fh = @(x) all(isnan(x)); a1=cellfun(fh,A); A(a1(:,1),:)=[]; A(:,a1(1,:))=[]; – chshiu Jun 07 '17 at 11:21
  • @chshiu the code you wrote will work only if the non-NaN elements are at the top left of your matrix, but it will fail if there are in the middle, or if there are some rows with NaNs in the middle, etc. Although it is an interesting approach. I'll see now if I can modify it to solve your problem – Vahe Tshitoyan Jun 07 '17 at 11:28
  • I tested your code and my code under the condition that NaN is in the middle. Both work successfully. – chshiu Jun 07 '17 at 11:38
  • Good, as long as it solves your problem. Try this `A = {NaN, NaN, NaN, NaN; NaN, NaN, NaN, NaN; NaN, NaN, NaN, NaN; NaN, NaN, NaN, NaN; NaN, NaN, NaN, NaN}; A{2, 2} = 'a'; A{2, 3} = 'b'; A{4, 2} = 'c'; A{4, 3} = 'd';`, you will see the problem. – Vahe Tshitoyan Jun 07 '17 at 11:41
  • I got it! Your code only found out whole row or column are NaN. Thanks for help – chshiu Jun 07 '17 at 11:44