Merging table rows, keep all columns
80 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
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 commentaires
Adam Danz
le 13 Nov 2019
Modifié(e) : Adam Danz
le 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
Réponse acceptée
Adam Danz
le 13 Nov 2019
Modifié(e) : Adam Danz
le 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 commentaires
Adam Danz
le 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?
Plus de réponses (0)
Voir également
Catégories
En savoir plus sur Logical dans Help Center et File Exchange
Produits
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!