How do I isolate rows from a table, containing the same column value?

I received a very large spreadsheet document with data of students, per student the columns exist of name, school, date of birth etc. I imported this spreadsheat as a table in matlab, and set all columns as strings.

The goal is to extract rows of students who went to the same highschool. Let's say this value is in collumn 3 and is alphabetically ordered.

I tried this in matlab by iterating and trying to save the students in a new table when the schools match. I found a solution where I have to copy one school in my code and then it gives a clean table with all students who went to that school.

But since there are 3000 different schools in the document it would be nice let a loop iterate over all schools and do all the work in one time.

This is what I thought would have worked but it didnt't:

MASSADOC = sortrows(MASSADOC,'school','ascend');
T=table; 
Names=strings;
for i =1:15310%there are 15310 rows in my document
    Names(i)=MASSADOC.Omschrijvingin(i); %to get a string array with all schools
end
U=unique(Names); %a string array with all schools 1 time in it

for i = 1:size(U)
    x=U(i);
    for j = 1:15310
        
        if MASSADOC.school(j)==U(i)
            
            T(j,:)=MASSADOC(j,:);
            
        else
        end
        writetable(T, x+'.xlsx','Sheet',1);
       
    end
    
    
end

[Mikel Spillemaekers]

Here's a sample of the excel.

[JESUS DAVID ARIZA ROYETH]

how you read your file to obtain MASSADOC?

[Mikel Spillemaekers]

Massadoc2 is just a part of a copy of MASSADOC. So in the code MASSADOC should be Massadoc2 in this case.

Answer 1

[Guillaume]

There is no need to sort the table beforehand:

[group, schoolname] = findgroups(MASSADOC.school);  %get unique schools and assign unique group to each one
for g = 1:numel(schoolname)  %iterate over each school/group
    writetable(MASSADOC(group == g, :), sprintf('%s.xlsx', schoolname{g})); %and save the rows that match the group
end

[Mikel Spillemaekers]

This doesn't give me any saved tables when I run the code..

[Guillaume]

There were a number of typos which I've now fixed. The code will create an excel file for each school. Most likely, you didn't fix the code correctly if you didn't get any file written.

Note that the code will output the files in the current folder. It would be better to specify the destination folder. I assumed you know how to do that, but if not:

destinationfolder = 'C:\somewhere\somefolder';
[group, schoolname] = findgroups(MASSADOC.school);  %get unique schools and assign unique group to each one
for g = 1:numel(schoolname)  %iterate over each school/group
    writetable(MASSADOC(group == g, :), fullfile(destionationfolder, sprintf('%s.xlsx', schoolname{g}))); %and save the rows that match the group
end

[Mikel Spillemaekers]

Thank you very much! This worked very good!

One last question, if I now want to filter out some students before creating the excels, like I only want the 'Belgium' school to be saved (which is given in another column in the massa file), do I just put an If statement in the forloop?

[Guillaume]

It's very rare that you need if in matlab, and typically the way beginners use if makes the code more complicated.

Here, the simplest way to do this is first to create a new table with only the 'Belgium' schools then use the same code as above with that table:

destinationfolder = 'C:\somewhere\somefolder';
Belgium_massadoc = MASSADOC(strcmp(MASSODOC.SomeVariable, 'Belgium', :);  %keep only the rows for which SomeVariable is 'Belgium'
[group, schoolname] = findgroups(Belgium_massadoc.school);  %get unique schools and assign unique group to each one
for g = 1:numel(schoolname)  %iterate over each school/group
    writetable(Belgium_massadoc(group == g, :), fullfile(destionationfolder, sprintf('%s.xlsx', schoolname{g}))); %and save the rows that match the group
end

However, if you want to do that for each country a slightly different approach would be better (basically same code as the original but with a different findgroups).

[Mikel Spillemaekers]

This works like a charm! Thank you very much. I think I'm too much thinking in a Python or Java way with the usage of If and For.

Do you have any idea how big excel files can go that matlab can handle? or does this just depend on you computer power?

[Guillaume]

Ultimately, it all depends on your computer but most likely, Excel will struggle on a big excel sheet before matlab does since excel needs to keep in memory not only the cell values, but their formatting, formulae and other properties which matlab doesn't store.

In addition, in matlab you can always resort to datastore and tall arrays to deal with data that would never fit in memory.