How can I organize data into monthly means/averages across all years? And then plot it in a bar graph?

Hello, I would like to make a barplot of average monthly number of students, in other words the mean of the total students across all school years. So the x-axis would read August, September, October, etc. and the y axis would be the average number of students in that month across all years.

I am not sure where to start on this, below is just some code for students per year, not related to what I am trying to find.

Table = readtable("Student_data.xlsx"); % Read in data

%Part 3: Assign school year (SY) to each daily observation
SY = zeros(size(Table.month));
for i = 1:height(Table)
    if Table.month(i)>=8  % months "greater" than or equal to August (month 8) are in the following schoolyear, so add 1 yr
        SY(i) = Table.year(i) + 1;
    else
        SY(i) = Table.year(i);     % months "lesser" than August are in the regular year
    end
end
Table.schoolyear = SY(:); % add "schoolyear" as a column in Table

%For each school year, sum up the total students just for that year.  
g = findgroups(Table.schoolyear); % "group" the unique school years
students = splitapply(@sum, Table.students, g);% sum the total students of each school year "group" 
year = splitapply(@mean, Table.schoolyear, g);

%Make a Barplot for annual total students
bar(year,students);
ylabel("Students");
xlabel("School Year");
title("Annual Student Totals");

% Now make a barplot of monthly mean students
% I am stuck here

[Antoni Garcia-Herreros]

Hello Macy,

You could try something like this:

Table = readtable("Student_data.xlsx");
Mat=table2array(Table);
Months=zeros(12,1);
for i=1:12
    imonth=Mat(Mat(:,1)==i,4);
    Months(i)=sum(imonth);
    
end
MonthLabel={'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'};

bar(Months);
ylabel("Students");
xlabel("Month");
title("Monthly Student Totals");
set(gca,'xticklabel',MonthLabel);

Error using readtable
Unable to find or open 'Student_data.xlsx'. Check the path and filename or file permissions.

Answer 1

[Sugandhi]

Hi,

I understand that you would like to make a barplot of average monthly number of students.

You can solve your problem something like this:

Table = readtable("Student_data.xlsx"); % Read in data
monthNames= {'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'};
SY = zeros(size(Table.month));
for i = 1:height(Table)
    if Table.month(i)>=8  % months "greater" than or equal to August (month 8) are in the following schoolyear, so add 1 yr
        SY(i) = Table.year(i) + 1;
    else
        SY(i) = Table.year(i);     % months "lesser" than August are in the regular year
    end
end
Table.schoolyear = SY(:); % add "schoolyear" as a column in Table
%For each month, sum up the total students for all year and divide by total number of years.  
g = findgroups(Table.schoolyear);% "group" the unique school years
year = splitapply(@mean, Table.schoolyear, g); % get unique years 
TotalYears= size(year); 
TotalYears=TotalYears(1,1); %get total number of years
g1 = findgroups(Table.month); % "group" the unique months
totalStudents = splitapply(@sum, Table.students, g1); % sum the total students of each school month "group" 
months=splitapply(@mean, Table.month, g1); % get unique month
avgStudents= totalStudents/TotalYears(1); % get average monthly number of students.
%Make a Barplot for average monthly number of students
bar(months,avgStudents);
ylabel("Students");
xlabel("Month");
title("Average monthly number of students");
set(gca,'xticklabel',monthNames);