The Attendance Dashboard With Holes In It
At Saraswati Secondary School a Class 10 AI team is building an attendance dashboard for the head teacher. The Term 1 register covers 620 students across 18 school days, which should give 11,160 entries. When the team loads the CSV, they find 412 blank attendance cells, 96 rows that appear twice because two class monitors submitted the same day, and 24 entries dated on Sundays, when school is closed. Two classes recorded attendance as 'P' and 'A' while the other sixteen used '1' and '0'. The head teacher wants a chart showing which class has the worst attendance so that she can call parents. One team member suggests deleting every row with a blank cell and drawing the chart from what is left. The parents' meeting is in one week.
1. List the data quality problems in this dataset and state the order in which you would fix them, justifying why the order matters.
Read the worked answer
The problems are of four kinds and they interact, so order matters.
- Duplicates (96 rows). Remove first. Duplicates inflate the denominator and can make one class look bigger than it is; if you handle missing values before deduplicating you may 'fill' a cell that already exists in the second copy.
- Coding (P/A vs 1/0). Standardise next: P becomes 1, A becomes 0. Until this is done, a blank and an 'A' are not the same as '0', and per-class totals are wrong.
- Sunday entries (24). Do not delete automatically. Check whether the date is a typo, for example 07/04 typed for 08/04. Quarantine them with a note; throwing away real attendance is worse than flagging it.
- Blanks (412). Handle last, at class-day level, because a blank may mean 'register not submitted' rather than 'student absent'.
Keep the raw file untouched and log every change, so the head teacher can audit the numbers she is about to act on.
2. Evaluate the suggestion to delete every row with a blank cell. Use the numbers in the setup to support your judgement.
Read the worked answer
Deleting 412 blank cells removes 412 of 11,160 expected entries, about 3.7% of the data. Overall that looks harmless, but the problem is where the loss falls. Blanks are rarely spread evenly; they cluster in a few classes whose monitors were absent, or on particular days. Two effects follow.
- Wrong denominator. Attendance is present divided by expected. Deleting blanks quietly shrinks 'expected', so a percentage rises without anyone attending more.
- Unfair ranking. A class with 30% missing entries is compared with a class with none. If the missing days were the bad days, that class now looks better than it is; either way the comparison is not like-for-like, which is exactly what the head teacher will use to call parents.
Better: keep the row, mark attendance as 'unknown', and report the percentage together with its coverage (entries available divided by expected). A class should be labelled 'insufficient data' rather than 'worst' if coverage is low.
3. Recommend how the team should present attendance to the head teacher, including any caveat, and explain why the caveat protects the school.
Read the worked answer
Report a percentage with its evidence base, not a bare league table.
- For each class give attended divided by expected, the number of expected entries, and the number of unknown entries.
- Add a coverage column and sort by coverage first. Any class below roughly 80% coverage is marked 'insufficient data', not ranked.
- State in one line what was cleaned: 96 duplicates removed, 24 Sunday rows excluded pending verification, P/A converted to 1/0.
- Show a bar chart with an uncertainty band, and highlight the two classes that used different codes so the head teacher knows their figures were recoded, not invented.
- Recommend the practical fix: one standard register page and submission by 3 pm.
The caveat protects the school. If a parent challenges a figure, the team can show exactly which entries were missing and why a class was or was not ranked, instead of defending a chart built on silently deleted rows.