table with NaNs into separate cells

I have a table (M) with 4 columns (columns named X, Y, Z,K) , there is a break between the data that loads as NaN.

I want place each data chunk into cells with all 4 columns (X, Y, Z,K).

Thank you!

Answer 1

[Voss]

% making a table with some all-NaN rows:
Var = randi(100,12,4);
Var([3 9],:) = NaN;
M = array2table(Var)

M = 12x4 table
    Var1    Var2    Var3    Var4
    ____    ____    ____    ____

      7      12       6       2 
     80      39      44      44 
    NaN     NaN     NaN     NaN 
     48      78      45      83 
      3      97      37      62 
     35      55      21       4 
     37      44      22      79 
     85      95      92      85 
    NaN     NaN     NaN     NaN 
     98      21      58      64 
     13      57       8      58 
     96      89      36      89 

% split the table on the all-NaN rows into a cell array of tables:
idx = find(all(isnan(M{:,:}),2));
s_idx = [1; idx+1];
e_idx = [idx-1; size(M,1)];
result = arrayfun(@(s,e)M(s:e,:),s_idx,e_idx,'UniformOutput',false)

result = 3x1 cell array
    {2x4 table}
    {5x4 table}
    {3x4 table}

celldisp(result)

 
result{1} =
 
    Var1    Var2    Var3    Var4
    ____    ____    ____    ____

      7      12       6       2 
     80      39      44      44 

 
 
result{2} =
 
    Var1    Var2    Var3    Var4
    ____    ____    ____    ____

     48      78      45      83 
      3      97      37      62 
     35      55      21       4 
     37      44      22      79 
     85      95      92      85 

 
 
result{3} =
 
    Var1    Var2    Var3    Var4
    ____    ____    ____    ____

     98      21      58      64 
     13      57       8      58 
     96      89      36      89 

 

[puccapearl]

Thank you Voss, I now have a cell with tables. How can I pull each table that contains a specific column value. For example in the picture above, I want to pull all the tables where the 3rd column == 1 into a separate cell.

I tried but the tables combine as a double which I don't want, I want to keep the separate tables.

Can also repost as a different question.

[Voss]

You're welcome!

One way to have all the tables in separate cells, but only the rows where column 3 is 1, is to split them first, as in my answer, and then:

result = cellfun(@(t)t(t{:,3} == 1,:),result,'UniformOutput',false);

[puccapearl]

amazing, thank you!

[Voss]

You're welcome!

[puccapearl]

Voss, one more thing, I want to subtract the first and last values of each table in column 1,

I tried this,

F = @(x)x(end)-x(1);

b = cellfun(F, result , 'un',0);

but I need to index into the first column of each table and I'm not sure how to do this.

[Voss]

F = @(x)x{end,1}-x{1,1};
b = cellfun(F, result);

[puccapearl]

ah yes I thought so! My issue is, when I do this:

result = cellfun(@(t)t(t{:,3} == 1,:),result,'UniformOutput',false);

I get a cell with some empty tables,

and that gives me an indexing error error when I try to do:

F = @(x)x{end,1}-x{1,1};

b = cellfun(F, result);

Do you know how I can remove the empty tables prior to avoid the error? I tried:

result = table2cell(rmmissing(cell2table(result)))

and

result(cellfun(@isempty,result))=[];

and

result(:,all(ismissing(result)))=[];

and

new_result = rmmissing(result);

but they do not remove the empty tables :/

[Voss]

This is how to do it. Try it again.

result(cellfun(@isempty,result)) = [];

[puccapearl]

It works! Thank you Voss! :D

[Voss]

You're welcome!