What’s on your mind?

AI-generated answers may be inaccurate

New-student mentor matching

  • Python
  • Data Analysis
  • Excel
  • Developer tools

I wrote this grouping script for an overseas university's new-student mentoring program. It takes student and mentor lists and produces an Excel workbook that staff can review, use for attendance, and amend. The rules cover nationality, communication platform, visa status, and academic program; the workbook layout was adjusted for the people using it.

Preparing the lists

The student inputs include CSV and Excel files. pandas normalizes column names and prepares names, IDs, programs, and email fields. Source and residency information determine Local, Granted, or Pending status.

The original nationality value is retained for the mainland-China rule, while a cleaned version is used in the report. An existing enrollment list can exclude previously recorded students. Missing nationality, missing personal email, and IDs repeated across granted and pending inputs are collected as warnings.

Mentor loading similarly normalizes columns, preferred names, and group labels, mapping WX and WA to the WeChat and WhatsApp pools.

Assignment order

Pending students are removed from the assignment pool and listed separately. The remaining students are split into WeChat and WhatsApp pools according to the nationality rule and assigned to the corresponding mentors.

Within each pool, students are grouped by academic program. Larger programs are processed first, with group counts estimated from their size. A balancing pass then moves students from the largest group to the smallest until their size difference at that stage is no more than one.

A final ID check looks for unassigned students. It prints a warning and adds any missing entries to the pool's first mentor group. Loading, assignment, and export live in separate modules, with field-conversion rules in another module.

Hold pending-visa students aside before assigning the remaining students within their communication poolsHold pending-visa students aside before assigning the remaining students within their communication pools

The workbook

The output contains Report, Mentor, Pending, and two assignment sheets. The WeChat and WhatsApp sheet names follow the group-number prefix, such as AEP-WX and AEP-WA.

I formatted mentor rows separately from student rows, added per-group numbering and Attended checkboxes, and left two blank rows after each group for later additions. Missing nationality and email cells are highlighted in red, so those warnings travel with the workbook.

Report summarizes students, mentors, groups, and platform counts and identifies cases such as a mentor assigned to multiple groups. Mentor retains the prepared mentor list, while Pending lists students awaiting assignment.

openpyxl reads the Excel inputs; XlsxWriter produces the formatted output and checkboxes.

Mentor rows, attendance boxes, and blank lines let staff continue working in the exported workbookMentor rows, attendance boxes, and blank lines let staff continue working in the exported workbook