The two schools at the bottom

The district dashboard ranks schools on attendance. SCH07 and SCH18 come out at 66.1% and 63.9% against a district average of 88.4%, and an emergency dropout response is being drafted for them.

The dashboard was built by constructing a grid of every student against every school day and filling in the marks.

The files

school-attendance-2024.v1.csv — 70,245 daily marks across sixty school days, February to April 2024.

school-roster-2024.v1.csv — 1,202 students across twenty-four schools.

Synthetic.

The task

Part one. Reproduce the dashboard’s figures for SCH07 and SCH18, then reproduce the correct ones. Show the two lines of code that differ and state in one sentence what each denominator is.

Part two. Establish what happened. For each school, list the dates it has no marks for. Say what the pattern is, why it is a closure rather than an attendance problem, and what evidence in the file supports that reading rather than the alternative.

Part three. Rank all twenty-four schools correctly. Report where SCH07 and SCH18 actually sit, and compute a second column the dashboard does not have: mark coverage, the share of expected student-days that carry a mark at all, using each school’s own open days.

Part four. Write the reply to whoever is drafting the response. Four sentences: what the dashboard’s number was, what the correct number is, what the fifteen missing days actually were, and what — if anything — should still be done about those two schools.

What you will need to handle

Three things, and only the first is obvious.

• present has five values. SCH09 recorded 873 marks as Y and N; 271 days were never marked. A boolean cast turns 1,144 observations into missing values and moves SCH09’s rate.
• Two students appear on the roster twice after a transfer that was never de-registered. Joining attendance to the roster fans their rows out.
• A missing day and an unmarked day are different things. One means the school was closed; the other means somebody forgot. Your denominator should treat them differently and your report should say how.

Three reference points

The district average is about 88.4% on marks made. SCH07 and SCH18 are each missing fifteen consecutive school days in March. If their corrected rates are not close to the district average, the denominator is still the full calendar.

The questions to answer in prose

Three sentences each.

1. The dashboard’s arithmetic was correct on the grid it built. Explain what was wrong with the grid, and give the general rule that would have prevented it.

2. Fifteen school days is a quarter of the term. Explain why excluding them from the attendance denominator is right and why saying nothing about them would be wrong, and give the separate indicator you would report instead.

3. This is the third time this platform has met an absence that is not a value — a facility that did not report, a monitoring round nobody drove, and now a day the school was closed. State the check that catches all three.

What to hand in

An R script producing the two versions of the SCH07 and SCH18 figures, the per-school missing-date listing, the corrected ranking with mark coverage, and the four-sentence reply as a comment at the foot of the file. Tables printed and written to outputs/.

How to know you are done

Every school’s attendance rate in your output carries the number of days that school was open. A reader can see from the table alone that two schools were open for forty-five days and the rest for sixty, without being told.