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

Question

Macy 2023-4-26

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1953034-how-can-i-organize-data-into-monthly-means-averages-across-all-years-and-then-plot-it-in-a-bar-grap

回答： Sugandhi 2023-4-26

Student_data.xlsx

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

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

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Antoni Garcia-Herreros 2023-4-26

在 MATLAB Online 中打开

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 2023-4-26

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1953034-how-can-i-organize-data-into-monthly-means-averages-across-all-years-and-then-plot-it-in-a-bar-grap#answer_1223559

在 MATLAB Online 中打开

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);