The three schools at the bottom

The education adviser has one support visit to give and asks for a ranking. The script below produces one. Three schools sit at the bottom, well clear of the rest, and a team is about to be sent to all three.

None of the three belongs there.

The files

school-roster-2024.v1.csv — 1,202 rows covering 1,200 students in 24 schools. school-attendance-2024.v1.csv — 70,245 daily attendance marks over 60 school days. Both synthetic.

The script

library(dplyr)
library(readr)
library(tidyr)

roster     <- read_csv("school-roster-2024.v1.csv")
attendance <- read_csv("school-attendance-2024.v1.csv")

ranking <- roster |>
  left_join(attendance, by = "student_id") |>                      # 1
  complete(student_id, attendance_date) |>                         # 2
  mutate(present = if_else(present == "true", TRUE, FALSE)) |>     # 3
  mutate(present = replace_na(present, FALSE)) |>                  # 4
  left_join(select(roster, student_id, school_id),
            by = "student_id") |>                                  # 5
  summarise(attendance_rate = mean(present), .by = school_id) |>
  arrange(attendance_rate)

head(ranking, 5)

The task

Part one — the ledger. For each of the five numbered steps, record the number of rows going in, the number coming out, and one sentence on why it changed. Some steps change nothing; those rows still go in the ledger.

Part two — the ranking. Produce the ranking a second time, correctly, and put the two side by side. State which schools move and by how many places.

Part three — the recommendation. Name the school the support visit should go to, and say in one sentence each what should happen to the three the script picked.

What the data actually holds

You may use these facts, but you must show the code that finds each one:

• Two schools recorded no attendance for the fifteen school days from 11 to 29 March.
• One school recorded attendance with Y and N for a substantial share of its rows.
• The roster has 1,202 rows and 1,200 students.

Three defects, three schools, and the script converts all three into low attendance.

Two reference points

Computed on marks that actually exist, the worst-attending school in this district is at about 78.5%, and the school ranked second worst is at about 79.0%.

The script’s ranking puts three schools below 66%. If your corrected ranking still has anything under 75%, one of the five steps is still doing what it was doing.

The questions to answer in prose

Three sentences each.

1. Step 3 converts present to a logical with if_else. Say exactly what it does to a value of Y, what step 4 then does to that row, and why the two steps together are worse than either alone.

2. Step 2 completes the grid across all students and all dates. Explain why that is the right instinct and the wrong implementation here, and what the grid should have been built from instead.

3. Steps 1 and 5 both join the roster. Say what the second one does that the first did not, what happens to the columns, and how a reader of the output would know — or not know — that it happened.

What to hand in

An R script producing the five-row ledger and both rankings as data frames, printed, plus a three-sentence recommendation as a comment at the foot of the file.

How to know you are done

Your ledger’s final row count equals the number of school-days the schools were actually open, and you can say what that number is and how you derived it. Every join in your corrected script carries a relationship = argument.