Merging table rows, keep all columns
80 visualizaciones (últimos 30 días)
Mostrar comentarios más antiguos
Marc Elpel
el 13 de Nov. de 2019
Comentada: Marc Elpel
el 14 de Nov. de 2019
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 comentarios
Adam Danz
el 13 de Nov. de 2019
Editada: Adam Danz
el 13 de Nov. de 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
Respuesta aceptada
Adam Danz
el 13 de Nov. de 2019
Editada: Adam Danz
el 13 de Nov. de 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 comentarios
Adam Danz
el 13 de Nov. de 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?
Más respuestas (0)
Ver también
Categorías
Más información sobre Logical en Help Center y File Exchange.
Productos
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!