Store identical rows in specified column

1 view (last 30 days)
Hi ,
I have a table that has thousands of input, an example is attached
Date ID Name Des
01/01/2021 2090260 'MIA' 'USA-MIAME'
01/01/2021 2090260 'MIA' 'USA-MIAME'
01/01/2021 2094230 'ALOC' 'USA-NEW'
01/01/2021 2094230 'ALOC' 'USA-NEW'
01/01/2021 2094700 'KATERINA' 'USA-FLORIDA'
01/01/2021 2094700 'KATERINA' 'USA-FLORIDA'
01/01/2021 2094700 'KATERINA' 'USA-FLORIDA'
01/01/2021 2094700 'KATERINA' 'USA-FLORIDA'
01/01/2021 2094700 'KATERINA' 'USA-FLORIDA'
I want to check if each input in the second column (i.e., ID) IS EQUAL. If so, store it in a seperate array or table.
so the expected output would be
Date ID Name Des
01/01/2021 2090260 'MIA' 'USA-MIAME'
01/01/2021 2090260 'MIA' 'USA-MIAME'
Date ID Name Des
01/01/2021 2094230 'ALOC' 'USA-NEW'
01/01/2021 2094230 'ALOC' 'USA-NEW'
Date ID Name Des
01/01/2021 2094700 'KATERINA' 'USA-FLORIDA'
01/01/2021 2094700 'KATERINA' 'USA-FLORIDA'
01/01/2021 2094700 'KATERINA' 'USA-FLORIDA'
01/01/2021 2094700 'KATERINA' 'USA-FLORIDA'
01/01/2021 2094700 'KATERINA' 'USA-FLORIDA'
I have written the following code but it doesnt work
clear;
% % data = Tbl.c;
[~,~,data] = xlsread('Test.csv');
Selected_Data=(data(2:end,[1 2 3 4 6 9 10 18 ]));
% Remove NAN rows
Selected_Data(any(cellfun(@(x) any(isnan(x)),Selected_Data),2),:) = [];
Selected_Data1 = cell2table(Selected_Data);
Selected_Data2=sortrows(Selected_Data1,{'Selected_Data2'},{'ascend'});
Selected_Data2.Properties.VariableNames = {'TimeStamp' 'MMSI' 'LATITUDE' 'LONGITUDE' 'SPEED' 'IMO' 'NAME' 'DES'};
% this part doesnt work
for i= 1:size(Selected_Data2,1)
for iter=1:size(Selected_Data2,1) %Selected_Data2 is the table name
if Selected_Data2(iter,2)=Selected_Data2(iter+1,2) % check condition
Segments=Selected_Data2(iter,:); % Store identical ID with all relevant data in a table or cell array
end
end
Group_Segment(i)=Segments;
end
  3 Comments
neamah al-naffakh
neamah al-naffakh on 12 Mar 2022
Dataset is attached and the code is modified
thanks for your response
Scott MacKenzie
Scott MacKenzie on 12 Mar 2022
OK, thanks. I just posted an answer.

Sign in to comment.

Accepted Answer

Scott MacKenzie
Scott MacKenzie on 12 Mar 2022
This seems to answer your question. There are 41 uniques IDs in the second column of your data set. The code below extracts the data/rows according the unique IDs and creates a new table. You can't create an array of tables in MATLAB, so it's not entirely clear what you want to do with each of the new tables. You could always use writetable within the loop to save each new table in a file.
T = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/924899/Example.csv');
u = unique(T.MMSI); % 2nd column, as per question
for i=1:length(u)
Tnew = T(T.MMSI==u(i),:)
% do something with Tnew (perhaps write to file)
end
  11 Comments
Scott MacKenzie
Scott MacKenzie on 16 Mar 2022
Edited: Scott MacKenzie on 16 Mar 2022
Oops, I think our comments crossed. I submitted, and then did an edit and resubmitted. Change f{:} to f in writeable.

Sign in to comment.

More Answers (0)

Categories

Find more on Structures in Help Center and File Exchange

Products


Release

R2015a

Community Treasure Hunt

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

Start Hunting!