Concatenate tables with variable data types in columns

조회 수: 7 (최근 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
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 CenterFile Exchange에서 Spreadsheets에 대해 자세히 알아보기

제품


릴리스

R2021a

Community Treasure Hunt

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

Start Hunting!

Translated by