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

Question

Marcus Glover 2023-2-20

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1915550-join-append-tables-and-keep-particular-table-variable-if-duplicate-rows

编辑： Marcus Glover 2023-2-20

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 个评论
显示 2更早的评论隐藏 2更早的评论

Jan 2023-2-20

@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 2023-2-20

编辑：Marcus Glover 2023-2-20

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.

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Marcus Glover 2023-2-20

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1915550-join-append-tables-and-keep-particular-table-variable-if-duplicate-rows#answer_1175490

在 MATLAB Online 中打开

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   }