Concatenate tables with variable data types in columns

3 次查看（过去 30 天）
Eric Escoto
Eric Escoto 2021-11-3
回答： Seth Furman 2021-11-4
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.

请先登录，再回答此问题。

回答（2 个）

Jon
Jon 2021-11-3
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 个评论
显示 1更早的评论
Eric Escoto
Eric Escoto 2021-11-3
This is the result. Doesn't seem benificial to do this.
It's odd that the readtable command reads some of the 'NAN' as NaN vs. the very next column as 'NAN'.
Jon
Jon 2021-11-3
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.

请先登录，再进行评论。

Seth Furman
Seth Furman 2021-11-4
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

类别

在 Help Center 和 File Exchange 中查找有关 Logical 的更多信息

标签

产品


版本

R2021a

Community Treasure Hunt

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

Start Hunting!

Translated by