Trying to concentrate a cell containing tables

Hi guys

I have quite a big cell array here with each cell contains a 1x6 table.

I'm trying to take the data out of each cell out to present all of it on one table...

It doesn't let me use the vertcat though because the first column of each one of these tables is of the type "datetime" and I get this message:

% Error using concentrate (line 6)
% An error occurred when concatenating the table variable 'Date' using VERTCAT.
% 
% Caused by:
%     Error using datetime/vertcat (line 1348)
%     All inputs must be datetimes or date/time character vectors or date/time strings.

By the way, I don't mind removing the whole date column... I tried that, it might be why I'm getting this error message...

I want to go from state 1 here to state 2:

THANKS TO ANYBODY WHO HELPS!

[Image Analyst]

How big? Millions of rows? Can you attach a small portion of it in a .mat file so people can try things?

[Adam Danz]

You can identify which tables are not in datetime format by using the following line. The output, isNotDT is a logical vector the same size as your cell array c where true values mark tables that do not have datetime format in the chosen column.

% Which tables are not datetime?
isNotDT = cellfun(@(T)~isdatetime(T.Var1), c);  % T.Var1 is your datetime column
% or 
isNotDT = cellfun(@(T)~isdatetime(T(:,1), c);   % Date time columns is column 1

To remove the 1st column from all tables,

% Remove 1st col
T_noDT = cellfun(@(T){T(:,2:end)}, c)

[Or Shem Tov]

Not that big, 34091 rows

every one of them is the same

[Or Shem Tov]

@Adam Danz

Thank you very much for the help, can you please explain what you did here?

T_noDT = cellfun(@(T){T(:,2:end)}, c)

Would love to learn from what you did rather than just copy hehe

[Adam Danz]

Great!

cellfun(function, cellarray) applies a function to each element of a cell array.

The function is @(T){T(:, 2:end)} which is an anonymous function. The input is a table T. T(:, 2:end) extracts all of the columns of T except the first one. The result is stored in a cell. So, the output to cellfun() in this case is another cell array the same size as the input cell array and each element is a table that's missing the first column from the input tables.

[Adam Danz]

Or Shem Tov' answer moved here as a comment.

Thank you!

I also forgot to mention, seems like some of the rows have {0×0 double} in them, they turn out to be missing data, what if I wanna join all the rows without losing their index number?

seems when I'm using verdcat it shrinks the row number from 34k to 31k because of the missing data

[Adam Danz]

If I understand correctly, some of your cells are empty; they don't contain a table.

Based on your screen shots, I assume your tables all contain 1 row and 6 columns (reduced to 5 columns).

If you want to preserve the index number of each table after concatenating them, you'll need to fill the empty cell arrays with tables that contain missing values. That way, when you concatenate the 1-row tables, the rows of NaN values will maintain the index order.

Since this seems to be addressing your main question, I'll continue in the answers section.

[Or Shem Tov]

This is what I basically want to do, I'm trying to expand each row to show its contents but the missing data must be ignored and left blank without deleting the rows because I have to connect it to a different table later on and the sequence is important

[Adam Danz]

Yes, I understand.

But, you cannot have a blank row within a table of numeric values. It's not possible.

You can, however, use NaN values to fill the empty rows which is what I'm suggesting you do.

Example:

  7×4 table
    Var1    Var2    Var3    Var4
    ____    ____    ____    ____
      8       1       4       2 
      8       2       9       2 
      3       8       4       4 
      2       3       2       8 
    NaN     NaN     NaN     NaN 
      4       1       2       4 
      8       7       6       7 

Answer 1

[Adam Danz]

Summary of comments under the question:

1) Remove the datetime column from each table.

T_noDT = cellfun(@(T){T(:,2:end)}, c)

2) To maintain the index number of each table after concatenating, fill empty cells with tables of missing data.

% C is your cell array
% If this is done before you remove the datetime column, 
C(cellfun(@isempty, C)) = {table(NaT, nan, nan, nan, nan, nan)}; 

% If this is done after you remove the datetime column,
C(cellfun(@isempty, C)) = {table(nan, nan, nan, nan, nan)}; 

Updated to show that this works with OP's data

% Clear out the workspace and load the data
clear 
load('historyfile.mat', 'history3')

% remove 1st col
C = cellfun(@(T){T(:,2:end)}, history3);

% Replace empties with nan tables; use headers from 1st table
% This assumes the first element of history3 is not empty.
C(cellfun(@isempty, C)) = {table(nan, nan, nan, nan, nan, ...
    'VariableNames', C{1}.Properties.VariableNames)}; 

% Vertically concatenate
T = vertcat(C{:});

% Show the first few rows 
T(1:10, :)
% ans =
%   10×5 table
%      High      Low       Open     Close       Volume  
%     ______    ______    ______    ______    __________
%      159.7     124.5    131.79    157.99    7.0769e+06
%      159.7     124.5    131.79    157.99    7.0769e+06
%        416    389.01     404.3    396.29     6.023e+05
%        416    389.01     404.3    396.29     6.023e+05
%      25.93     25.29     25.44     25.67    5.3935e+06
%      170.2    167.08    168.87    169.39     7.612e+05
%     102.13    98.028     99.55    101.17     5.573e+05
%     125.57    122.37     124.4    124.55    3.0624e+06
%      81.61     80.52     80.72     80.84    2.8356e+06
%     103.95     98.41     103.5     99.81    1.3078e+06

[Or Shem Tov]

Trying to do this

history3(cellfun(@isempty, history3)) = {table(NaT, nan, nan, nan, nan, nan)};

And im getting an error:

Unable to use a value of type datetime as an index.

[Or Shem Tov]

And when I try after then I get this:

T_noDT = cellfun(@(T){T(:,2:end)}, history3)
T_noDT(cellfun(@isempty, T_noDT)) = {table(nan, nan, nan, nan, nan)};

% Index in position 1 is invalid. Array indices must be positive integers or logical values.

[Adam Danz]

Could you share the entire copy-pasted error message? It may be easier if you attach a mat file with the history3 variable.

Here's evidence that this should work

C{1,1} = table(datetime('now'), 1,2,3,4,5);
C{2,1} = table(datetime('now'), 1,2,3,4,5);
C{3,1} = [];
C{4,1} = [];
C{5,1} = table(datetime('now'), 1,2,3,4,5);

C(cellfun(@isempty, C)) = {table(NaT, nan, nan, nan, nan, nan)};

T = vertcat(C{:})
% T =
%   5×6 table
%             Var1            Var2    Var3    Var4    Var5    Var6
%     ____________________    ____    ____    ____    ____    ____
%     01-Apr-2020 22:32:12      1       2       3       4       5 
%     01-Apr-2020 22:32:12      1       2       3       4       5 
%                      NaT    NaN     NaN     NaN     NaN     NaN 
%                      NaT    NaN     NaN     NaN     NaN     NaN 
%     01-Apr-2020 22:32:12      1       2       3       4       5 

Answer 2

[Or Shem Tov]

I uploaded the file to google drive since it was more than 5MB after zipping

https://drive.google.com/file/d/1nG4T8Tel-kKBN77cEjwyNhM8S8Xfg5Bq/view

[Or Shem Tov]

[Or Shem Tov]

Maybe because the empty cells are 0x0 doubles

[Adam Danz]

I just updated my answer to show that the answer works with your data. I don't know why you are getting those errors. My guess is that a variable was overwritten in your workspace. Please see the updated section of the answer.

[Or Shem Tov]

Found a way around it, I created a variable k that takes the first cell as an example of a row and then change all the variables to 1, then I replace the empty cells with k, so any empty cell will be a table full of 1s.

I don't know why it didn't work with your solution though, it says something about the indexing.

Maybe it takes cellfun(@isempty, C) as 0 and then C(0) isn't a valid index?

C = cellfun(@(T){T(:,2:end)}, history);

%%

k = C{1};
k.Open = 1; k.Close = 1; k.High = 1; k.Low = 1; k.Volume = 1;

for i = 1:size(C,1)
    
   if (isempty(C{i}) == 1)
   
       C{i} = k;
 
   end
   
end

T = vertcat(C{:});

[Adam Danz]

If you look at my updated answer, it does work. The problem was that this line

C(cellfun(@isempty, C)) = {table(nan, nan, nan, nan, nan)}; 

correctly filled the cell array with a NaN table but the variable names did not match the other tables' variable names. Since it didn't have the same variable names as the other tables, they couldnt' be vertically concatenated. Note that this produces a different error than the ones you shared.

If you look at my updated answer, it works with the data you provided. If you'd rather use 1s than NaNs, you can replace the NaNs in that line of code.