Merging table rows, keep all columns
61 views (last 30 days)
Show older comments
I'm trying to combine data from multiple tables into one. (data files attached). Seems like a simple join(), or outerjoin(), but every path has run into issues.
Specifically what I want to do:
- Add rows from table 2 to table 1.
- Keep all rows in both tables (append rows)
- Where column names match, use that column
- Where columns are new, add column to table width
- Keep column names (outer join is renaming based on source table)
- Some table values are empty and should combine as empty values in existing and/or new columns as needed.
Tried so far:
- Join - Fails do to some empty values
- Join w/Replaced nan - fails do to some other key value error
- outerjoin() w/multiple configuration options - all failed.
- innerjoin90 - does not seem like what I want (throwing out data).
When done combining the attached tables there should be slight more columns than the first table, and rows should be the sum of rows in both tables.
This should be a common issue so assuming I am missing some simple solution...?
Using Matlab 2016b
Marc
6 Comments
Adam Danz
on 13 Nov 2019
Edited: Adam Danz
on 13 Nov 2019
I've read-in your tables and the column names match between both tables. Points 3 and 4 in your question (thanks for the numbering - that makes this easy to discuss) mention column names that do not match. Are there supposed to be column names that do not match?
I should add that upon reading in your table, Matlab had to modify some of the column names to conform to Matlab syntax.
Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table.
The original column headers are saved in the VariableDescriptions property.
Set 'PreserveVariableNames' to true to use the original column headers as table variable names.
files = {'RESULTS_SAMP1.CSV', 'RESULTS_SAMP2.CSV'}; %Full paths are always better
T1 = readtable(files{1},'Delimiter',',');
T2 = readtable(files{2},'Delimiter',',');
% Do column names match?
all(ismember(T1.Properties.VariableNames, T2.Properties.VariableNames)) % Yes
all(ismember(T2.Properties.VariableNames, T1.Properties.VariableNames)) % Yes
Accepted Answer
Adam Danz
on 13 Nov 2019
Edited: Adam Danz
on 13 Nov 2019
% Read in the data
files = {'RESULTS_SAMP1.CSV', 'RESULTS_SAMP2.CSV'}; %Full paths are always better
T1 = readtable(files{1},'Delimiter',',');
T2 = readtable(files{2},'Delimiter',',');
% Simulate column-mismatch
T1 = removevars(T1,'SpecimenType'); % remove col 3
T2 = removevars(T2,'Test'); % remove col 5
% Vertically concatenate tables
T3 = outerjoin(T1,T2,'MergeKeys', true)
4 Comments
Adam Danz
on 13 Nov 2019
Glad I could help out.
Just so I understand, the problem you're describing isn't with the merging of tables, it's with importing the tables. Is that correct?
Have you tried importing the tables without using the PreserveVariableNames flag?
Could you attach one of the files causing problems?
More Answers (0)
See Also
Categories
Find more on Matrix Indexing in Help Center and File Exchange
Products
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!