How do I sum the values of sections of a table?

Hi there. I have this data, for which I created a new vector called schoolyear. How do I sum the total number of students for each schoolyear and save each value into a new vector or matrix?

So for example, the new vector totalstudents should read: 50, 66.

One suggestion I have been given is to use a for loop with possibly an if/then.

Note this is a sample of a much larger data set.

Thank you.

clc;
close all;
clear all;
table_a = readtable('Data1.xlsx');

%Create the new vector, schoolyear
for i = 1:height(table_a)
if table_a.month(i)>=8
    schoolyear(i) = table_a.year(i) + 1;
else
    schoolyear(i) = table_a.year(i);
end
end
table_a.schoolyear = schoolyear(:) 

table_a = 9×5 table
    month    day    year    students    schoolyear
    _____    ___    ____    ________    __________

      8       7     2000       12          2001   
      9       8     2000       14          2001   
      9       9     2000       13          2001   
      3      11     2001       11          2001   
      8       3     2001       17          2002   
     12      15     2001       14          2002   
      2       2     2002       10          2002   
      5       1     2002        9          2002   
      7       3     2002       16          2002   

Answer 1

[Tushar Behera]

Hi Macy,

I believe you want to group the students by the year and find the total.

This can be acheived by using the function "splitapply". This function apply a function to a group of data. For example:

clc;
close all;
clear all;
table_a = readtable('Data1.xlsx');
%Create the new vector, schoolyear
for i = 1:height(table_a)
if table_a.month(i)>=8
    schoolyear(i) = table_a.year(i) + 1;
else
    schoolyear(i) = table_a.year(i);
end
end
table_a.schoolyear = schoolyear(:) 
g=findgroups(table_a.schoolyear)%find the groups in the data
grouped_students_total= splitapply(@sum, table_a.students, g);%get the sum from the groups

Here in "grouped_students_total" you will find your desired result. To know more about "splitapply" you can follow the following documentation:

Split data into groups and apply function - MATLAB splitapply (mathworks.com)

I hope this resolves your query.

Regards,

Tushar

Answer 2

[Voss]

table_a = readtable('Data1.xlsx');

%Create the new vector, schoolyear
for i = 1:height(table_a)
    if table_a.month(i)>=8
        schoolyear(i) = table_a.year(i) + 1;
    else
        schoolyear(i) = table_a.year(i);
    end
end
table_a.schoolyear = schoolyear(:);

Here's one way:

table_summary = groupsummary(table_a,'schoolyear',@sum,'students')

table_summary = 2×3 table
    schoolyear    GroupCount    fun1_students
    __________    __________    _____________

       2001           4              50      
       2002           5              66