Trying to concentrate a cell containing tables

29 views (last 30 days)
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!
  9 Comments
Or Shem Tov
Or Shem Tov on 2 Apr 2020
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
Adam Danz on 2 Apr 2020
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

Sign in to comment.

Accepted Answer

Adam Danz
Adam Danz on 2 Apr 2020
Edited: Adam Danz on 2 Apr 2020
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
  3 Comments
Or Shem Tov
Or Shem Tov on 2 Apr 2020
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
Adam Danz on 2 Apr 2020
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

Sign in to comment.

More Answers (1)

Or Shem Tov
Or Shem Tov on 2 Apr 2020
I uploaded the file to google drive since it was more than 5MB after zipping
  5 Comments
Or Shem Tov
Or Shem Tov on 2 Apr 2020
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
Adam Danz on 2 Apr 2020
Edited: Adam Danz on 2 Apr 2020
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.

Sign in to comment.

Categories

Find more on Numeric Types in Help Center and File Exchange

Products


Release

R2020a

Community Treasure Hunt

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

Start Hunting!