Concatenate tables with variable data types in columns

16 views (last 30 days)
I'm trying to vertically concatenate the tables attached here.
tbl = [readtable('tsttbl1.xlsx'); readtable('tsttbl2.xslx')];
Then, I get the message:
>> Cannot concatenate the table variable 'Field61_FloatArray_1_53_' because it is a cell in one table and a non-cell in another.
For some reason when the excel file is loaded into MATLAB, the columns take on differing datatypes. In this case I've got cell vs. non-cells and the concatenation can't occur. How can I do this? Can I convert all the columns with cell values to non-cell values or visa-versa?
Thanks.

Answers (2)

Jon
Jon on 3 Nov 2021
I think part of the problem is the NAN's in your files. They don't automatically get imported as MATLAB NaN's as you hope. It just thinks they are text.
For this you can use
readtable('tsttbl1.xlsx','TreatAsMissing','NAN')
Unfortunately I think there are further complications where you have a whole column of NAN in your input file and it thinks the whole column is text.
Hopefully this gets you a little further
  3 Comments
Jon
Jon on 3 Nov 2021
Without using the treat as missing the NAN's in the excel just get treated as text. I think you still have a problem though when an entire column is NAN. Then I think it treats the whole column as non numeric and doesn't see them as NaN's. Sorry I don't have time to dig into it deeper, but maybe this helps, or someone else can pick it up from here.

Sign in to comment.


Seth Furman
Seth Furman on 4 Nov 2021
As Jon mentioned, we can set the variable type explicitly for those variables we know to be floating point numeric.
file1 = "tsttbl1.xlsx";
opts = detectImportOptions(file1, "VariableNamingRule", "preserve");
floatVars = contains(opts.VariableNames, "Float");
opts.VariableTypes(floatVars) = {'double'};
t1 = readtable(file1, opts);
file2 = "tsttbl2.xlsx";
opts = detectImportOptions(file2, "VariableNamingRule", "preserve");
floatVars = contains(opts.VariableNames, "Float");
opts.VariableTypes(floatVars) = {'double'};
t2 = readtable(file2, opts);
t = [t1; t2];
t(:, 1:3)
ans =
10×3 table
TIMESTAMP RECORD Field61_FloatArray(1,1)
____________________ ______ _______________________
06-Feb-2019 01:15:30 0 0.762
06-Feb-2019 01:16:00 1 1.455
06-Feb-2019 01:16:30 2 0.877
06-Feb-2019 01:17:00 3 0.747
06-Feb-2019 01:17:30 4 1.023
02-Jan-2020 00:23:00 4034 0.544
02-Jan-2020 00:31:00 4035 2.27
02-Jan-2020 00:32:00 4036 0.733
02-Jan-2020 00:33:00 4037 1.023
02-Jan-2020 00:34:00 4038 0.742

Products


Release

R2021a

Community Treasure Hunt

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

Start Hunting!