Store identical rows in specified column

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

[Scott MacKenzie]

No data file was attached.

[neamah al-naffakh]

Dear @Scott MacKenzie

Dataset is attached and the code is modified

thanks for your response

[Scott MacKenzie]

OK, thanks. I just posted an answer.

Answer 1

[Scott MacKenzie]

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

[neamah al-naffakh]

Dear @Scott MacKenzie thanks for your answer.

For each IDs, I need to create a cell array or table (that includes all relative data for this particular ID - So I can pre-process it later).

I have tried your code but it doesnt give exactly what I was looking for. this is because your code store the data of the last ID only.

For example, the ID 2090260, and all relative data ( all columns of this particular ID) stores in cell array or table (rather than write them into files).

I do beleive we are nearly there but we just want to modify the code a little bit.

[Scott MacKenzie]

@neamah al-naffakh you're welcome.

I'm not sure what you mean by "your code store the data of the last ID only". My code produces a table for each ID and the table includes all the rows and columns for that ID. I see that there are 4 or 5 rows for each ID. This makes sense since there are 199 rows in the the table T and there are 41 unique IDs. So, all the data are accounted for.

But, my code doesn't "store" anything. It just creates a table for each unique ID. If you want to store the table you'll need to use writematrix. Have you added code to do this? If so, make sure you create a unique filename for each ID. If you use the same filename, files will get overwritten.

If you want the data in a cell, then you can use

C = table2cell(Tnew);

[neamah al-naffakh]

Dear @Scott MacKenzie, Again many thanks for your quick response.

Yes I mean that I want to store the table data for each IDs.

How can I create a unique filename for each iID inside the for loop?

I have added one line to your code as showing bellow

for i=1:length(Unique_Sh_ID)
    Tnew = Selected_Data2(Selected_Data2.MMSI==Unique_Sh_ID(i),:); 
    writetable(Tnew,'ID.txt','Delimiter',' ');  
    %type 'myData.txt';
    
end

Thanks

[Scott MacKenzie]

The trick is to change the filename in each iteration of the for-loop. This way you won't overwrite files previously written. Instead of

writetable(Tnew,'ID.txt','Delimiter',' '); 

add the iteration number after "ID". Something like this:

writetable(Tnew, compose('ID%d.txt',i),'Delimiter',' '); 

With this, the files will be ID1.txt, ID2.txt, ID3.txt, etc.

[neamah al-naffakh]

Many thanks for your kindness, I would try this and back with output.

Your help and support are highly appreciated @Scott MacKenzie

[Scott MacKenzie]

You're welcome. BTW, you might want to have the actual ID in the filename, rather than just the loop index. For this, try...

f = compose('ID_%d.txt', Tnew.MMSI(1));
writetable(Tnew, f{:});

[neamah al-naffakh]

Dear @Scott MacKenzie,

When I run the above code , it shows this error

Undefined function 'compose' for input arguments of type 'char'.

Could you help me to fix this please (Note I use Matlab 2015a)

[Scott MacKenzie]

@neamah al-naffakh, hmmm, yes, compose was introduced in R2016b. Just change compose to sprintf and change f{:} to just f in the writetable function:

f = sprintf('ID_%d.txt', Tnew.MMSI(1))
writetable(Tnew, f);

sprintf is a much older function, but it works in much the same way as compose. This should work.

[neamah al-naffakh]

Thanks again for being very patient with me

I have this error

writetable(Tnew, f{:});

Cell contents reference from a non-cell array object.

[Scott MacKenzie]

Oops, I think our comments crossed. I submitted, and then did an edit and resubmitted. Change f{:} to f in writeable.

[neamah al-naffakh]

@Scott MacKenzie You're very kind.

Thanks for your kindness