Join/Append tables and keep particular table variable if duplicate rows

12 views (last 30 days)
I have a an ecxel table that is updated inside excel after being generated in MATLAB with a column of some user text comments- these comments are not always present. I would like to add new rows and often duplicate rows to this table, but the new/duplicate rows will never contain comments. In the event there is a duplicate row added for the key variables, I would like to preserve the comment from the excel file if it is present, otherwise it will remain or be created as blank.
Not sure the best approach here- my various attempts at joins are not working well.
Here is an example of the excel file with comments and some new data containing duplicate rows:
excel=readtable('commented.xlsx')
excel = 6×4 table
Date Time Value Comment ____________ __________ _____ _____________ {'20220912'} {'015304'} 0.1 {0×0 char } {'20220912'} {'172427'} 0 {0×0 char } {'20220914'} {'002824'} -0.7 {0×0 char } {'20220915'} {'042702'} -0.7 {'Windy Day'} {'20220915'} {'170959'} 0.2 {0×0 char } {'20220916'} {'231015'} -0.9 {0×0 char }
load newdata.mat
newdata
newdata = 6×3 table
Date Time Value ____________ __________ _____ {'20220913'} {'087626'} -0.4 {'20220914'} {'002824'} -0.7 {'20220915'} {'042702'} -0.7 {'20220915'} {'170959'} 0.2 {'20220916'} {'231015'} -0.9 {'20220917'} {'115816'} -0.8
The answer would look like this- all new rows from newdata are addded, but when there are duplicate Date/Time that have a comment, that comment is preserved, ('20220915' '042702' would keep the 'Windy Day' comment) Otherwise, the Comment variable is an empty char cell.
load answer
answer
answer = 8×4 table
Date Time Value Comment ____________ __________ _____ _____________ {'20220912'} {'015304'} 0.1 {0×0 char } {'20220912'} {'172427'} 0 {0×0 char } {'20220913'} {'087626'} -0.4 {0×0 char } {'20220914'} {'002824'} -0.7 {0×0 char } {'20220915'} {'042702'} -0.7 {'Windy Day'} {'20220915'} {'170959'} 0.2 {0×0 char } {'20220916'} {'231015'} -0.9 {0×0 char } {'20220917'} {'115816'} -0.8 {0×0 char }
  4 Comments
Jan
Jan on 20 Feb 2023
@Marcus Glover: The example from your last comment is the result of a standard concatenation. But in the original question joining two tables with 6 elements does not create a table with 12 elements. So what should be removed?
Marcus Glover
Marcus Glover on 20 Feb 2023
Edited: Marcus Glover on 20 Feb 2023
Thanks. Your questions have given me the answer. I do just want to remove the four rows in the newdata table that have duplicate rows in the excel table. The I do not need a join at all and can just concatenate as you suggest after creating a dummy variable 'Comment' for those that are not duplicate rows.

Sign in to comment.

Accepted Answer

Marcus Glover
Marcus Glover on 20 Feb 2023
Jan's question led me to the answer.
commented=readtable('commented.xlsx');
load newdata.mat;
load answer;
% eliminate duplicate rows
idx = ismember(newdata(:,1:3), commented(:,1:3));
newdata(idx,:)=[];
% Create a new variable with class cell and contents 0x0 char
newVar = cell(height(newdata), 1);
newVar(:) = {''};
newdata.Comment = newVar
newdata = 2×4 table
Date Time Value Comment ____________ __________ _____ __________ {'20220913'} {'087626'} -0.4 {0×0 char} {'20220917'} {'115816'} -0.8 {0×0 char}
answer2=sortrows([newdata; commented],[1 2])
answer2 = 8×4 table
Date Time Value Comment ____________ __________ _____ _____________ {'20220912'} {'015304'} 0.1 {0×0 char } {'20220912'} {'172427'} 0 {0×0 char } {'20220913'} {'087626'} -0.4 {0×0 char } {'20220914'} {'002824'} -0.7 {0×0 char } {'20220915'} {'042702'} -0.7 {'Windy Day'} {'20220915'} {'170959'} 0.2 {0×0 char } {'20220916'} {'231015'} -0.9 {0×0 char } {'20220917'} {'115816'} -0.8 {0×0 char }

More Answers (0)

Categories

Find more on Tables in Help Center and File Exchange

Tags

Community Treasure Hunt

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

Start Hunting!