Academic Management EPTNM
Automating class monitoring for CEFET-MG technical courses with Python and Streamlit.
The Problem
SIGAA — the institutional academic system at CEFET-MG — does not generate consolidated management reports for secondary-level technical courses (EPTNM). To track student performance, a program coordinator had to download the class map — a single file containing all subjects — and manually cross-reference the data, a repetitive and error-prone process that consumed hours every quarter.
The scenario is even more complex for the Traffic Operations and Road Building Technical Programs. From the 2nd year onward, the technical subjects diverge between the two programs — but the secondary-education core subjects remain the same. This means that for 2nd and 3rd year students, separate XLS spreadsheets exist for each program's technical disciplines, while the shared secondary-education core stays in a single common spreadsheet.
The Solution — Project Evolution
First version developed in Google Colab. The script already read the MapaTurma.xls file exported from SIGAA and generated reports automatically, eliminating the manual data compilation work.
Upon returning to the course coordination role, the code was reactivated and expanded: it now automatically generates a PDF report with a professional layout (charts, boxplots, dynamic table of contents) and integrated the OpenAI API as an optional feature, generating analytical paragraphs from the processed data.
With AI-assisted development, the code was generalized to support any EPTNM course, not just Transportation. The scripts were then packaged into a Streamlit web application, allowing any coordinator to upload their spreadsheet and receive the PDF report directly in their institutional email — no installation required.
In July, the app began accepting up to four class reports at once, one per quarter, and gained a multi-quarter analysis. In August, the report was restructured: a dedicated section for Mathematics, Physics and Chemistry, a grades × absences quadrant chart and an auditable failure probability model replacing the old heuristic risk estimate. The current version is 2.0.1.
Key Features
Intelligent class map processing: The system automatically extracts the course name, class, and academic quarter from the header of the XLS file exported from SIGAA. Coordinators can upload up to four spreadsheets at once, one per quarter. Each file must cover a single quarter: a report mixing quarters is rejected with a warning, to keep the statistics accurate.
Dynamic approval threshold: CEFET-MG distributes points variably — 1st and 3rd quarters are worth 20 points; 2nd and 4th quarters are worth 30. The system identifies the current quarter and automatically adjusts the approval threshold (60%), making all calculated pass rates precise without any manual configuration.
Statistical attendance analysis: Attendance is analyzed using statistical metrics (mean, median, P90, standard deviation) rather than a fixed credit-hour calculation. This enables anomaly detection — students above P90 or beyond mean + 2σ — and produces a top-10 absentee ranking for the quarter.
STEM subjects: Mathematics, Physics and Chemistry, the subjects with the highest failure rates in EPTNM, get their own report section, with a summary table and four charts: a grade boxplot, achievement bands, a count of students below the threshold in one, two or all three of them at once, and a comparison with the class's overall average.
Grades × absences quadrants: A scatter plot places each student by average grade and total absences, split by the passing threshold and the class's average absences into four quadrants: on track, attendance risk, performance risk and double risk. Students at double risk are numbered by severity.
Multi-quarter analysis: With two or more quarters uploaded, the report adds a section that estimates each student's annual average, computes the IDA (Student Performance Index, which combines grades and absences across the quarters), flags the steepest performance drops and estimates the probability of failing. With a single quarter, the section does not appear.
Auditable failure probability: A deterministic step separates students who have already passed on grades from those who can no longer reach 60 points in regular-quarter grades. For the rest, a normal model with statistical shrinkage estimates, from the student's own performance, the chance of not making up the missing points, always between 1% and 99%. The report shows the calculation for each student. The 75% minimum attendance and make-up exams are outside the model.
Professional PDF report: Built with ReportLab, the document includes a dynamic table of contents, an absence distribution histogram, subject-level boxplots, performance charts, a highlighted list of at-risk students and subjects, and an optional AI-generated analytical paragraph.
Traffic Operations + Road Building special case (2nd & 3rd years): From the 2nd year onward, each program has its own technical disciplines in separate XLS spreadsheets, but the secondary-education core remains in a single shared spreadsheet. When this mode is enabled, the coordinator uploads both technical spreadsheets; the system extracts the secondary-education grades from the Road Building spreadsheet, incorporates them into the Traffic Operations data, and then generates two distinct PDFs — one per program — sent together in the same email.
Privacy by design: Report delivery is restricted to @cefetmg.br domain addresses. Spreadsheets and PDFs are processed exclusively in RAM (BytesIO) — no student data is ever written to disk or made available for direct download on the site. The app can optionally log usage metadata (date and time, course, quarter and the coordinator's email) to a spreadsheet, with no student grades or names.
Technology Stack
Python Streamlit Pandas ReportLab smtplib OpenAI API
Impact
The system replaced a fully manual process — which previously required hours of data compilation each quarter — with an accessible, centralized web interface. Today, any EPTNM coordinator at CEFET-MG can, in minutes, receive a complete management report in their institutional inbox, with data and charts ready to support faster, evidence-based pedagogical interventions.