Split array into separate arrays at row with NaNs

Hello,

Probably a simple problem? I have been sent some data in an Excel file that has several sets of data on one sheet. The data are separated into the individual sets with a blank line, that becomes a row of "NaN"s. How can I make separate arrays from this big one? I didn't see the answer to this in documentation.

Thanks!

Doug Anderson

Answer 1

[Stephen23]

First lets define some trial data:

>> M = [0,1;2,3;NaN,NaN;4,5;6,7;NaN,NaN;8,9]
M =
   0     1
   2     3
 NaN   NaN
   4     5
   6     7
 NaN   NaN
   8     9
>> idx = all(isnan(M),2);

Method One: accumarray:

>> idy = 1+cumsum(idx);
>> idz = 1:size(M,1);
>> C = accumarray(idy(~idx),idz(~idx),[],@(r){M(r,:)});
>> C{:}
ans =
   0     1
   2     3
ans =
   4     5
   6     7
ans =
   8     9

Method Two: mat2cell:

>> idr = diff(find([1;diff(idx);1]));
>> D = mat2cell(M,idr(:),size(M,2));
>> D{1:2:end}
ans =
     0     1
     2     3
ans =
     4     5
     6     7
ans =
     8     9

You can access these data easily and efficiently using cell indexing, e.g.:

>> C{2}
ans =
     4     5
     6     7

Warning: Whatever you do, do NOT try to create lots of arrays in the workspace. This will be slow, buggy, obfuscated, and makes working with the data difficult:

https://www.mathworks.com/matlabcentral/answers/304528-tutorial-why-variables-should-not-be-named-dynamically-eval

[Douglas Anderson]

Can you elaborate as to how to do this with mat2cell? A cell that is split up at the NaN locations?

[Stephen23]

See my answer. I prefer the accumarray method, which correctly deals with contiguous rows of NaNs.

[Douglas Anderson]

Thank you very much!!! Nicely done.

Good advice about all the arrays, too. I was thinking of reshape(), but am not sure that all of the segments are the same size. Understand about the cell array approach.

[Stephen23]

@Douglas Anderson: my pleasure. Remember to accept the answer that best resolves your original question. Accepting is an easy way to show us that you appreciate our (volunteer) effort, and shows other that your question has been resolved.

[Peng Li]

@Stephen Cobeldick

Just a comment that the accumarray solution doesn't correctly deal with contiguous rows of NaNs, as as long as there are more than 1 contiguous rows of NaNs, cumsum will pick additional NaN rows, which results in a jump in idy(~idx). Therefore, the final C will contains cell elements that are empty. So additional steps are required to filter them. See an example modified based on yours:

M = [0,1;2,3;NaN,NaN;nan nan; nan nan; 4,5;6,7;NaN,NaN;8,9];
idx = all(isnan(M),2);
idy = 1+cumsum(idx);
idz = 1:size(M,1);
C = accumarray(idy(~idx),idz(~idx),[],@(r){M(r,:)})
C =

  5×1 cell array

    {2×2 double}
    {0×0 double}
    {0×0 double}
    {2×2 double}
    {1×2 double}

[Andrew Sol]

Thank you very much! It's very helpful!