Clear Filters
Clear Filters

Combining excel files with unequal rows

3 views (last 30 days)
I have two excel files which I would like to combine into a single file. The data is as follows
:
I would like to combine these two tables into a single table with the columns "Name" "Date" "Value 1" "Value2" where the occassion of John 04-05-2011 shows a value for Value 1 but remains empty for Value 2.

Accepted Answer

Dyuman Joshi
Dyuman Joshi on 9 Apr 2024
Edited: Dyuman Joshi on 9 Apr 2024
Try outerjoin -
%Data from the images attached for example
%Use readtable() to read the data as tables directly
Name = ["Adam" "Adam" "John" "John" "John" "Karen"].';
Date = datetime(2010, [11 12 11 12 5 5], [5 6 5 6 4 4]).';
Value1 = [12 16 8 3 14 12].';
t1 = table(Name, Date, Value1)
t1 = 6x3 table
Name Date Value1 _______ ___________ ______ "Adam" 05-Nov-2010 12 "Adam" 06-Dec-2010 16 "John" 05-Nov-2010 8 "John" 06-Dec-2010 3 "John" 04-May-2010 14 "Karen" 04-May-2010 12
Name = ["Adam" "Adam" "John" "John" "Karen"].';
Date = datetime(2010, [11 12 11 12 5], [5 6 5 6 4]).';
Value2 = [8 6 1 3 8].';
t2 = table(Name, Date, Value2)
t2 = 5x3 table
Name Date Value2 _______ ___________ ______ "Adam" 05-Nov-2010 8 "Adam" 06-Dec-2010 6 "John" 05-Nov-2010 1 "John" 06-Dec-2010 3 "Karen" 04-May-2010 8
%join tables
out = outerjoin(t1, t2, 'Keys', [1 2])
out = 6x6 table
Name_t1 Date_t1 Value1 Name_t2 Date_t2 Value2 _______ ___________ ______ _________ ___________ ______ "Adam" 05-Nov-2010 12 "Adam" 05-Nov-2010 8 "Adam" 06-Dec-2010 16 "Adam" 06-Dec-2010 6 "John" 04-May-2010 14 <missing> NaT NaN "John" 05-Nov-2010 8 "John" 05-Nov-2010 1 "John" 06-Dec-2010 3 "John" 06-Dec-2010 3 "Karen" 04-May-2010 12 "Karen" 04-May-2010 8
  4 Comments
Katelyn
Katelyn on 9 Apr 2024
Thank you so much! I tried this and it worked perfectly.

Sign in to comment.

More Answers (0)

Products


Release

R2023b

Community Treasure Hunt

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

Start Hunting!