When the register and the report disagree — Joining and Reshaping Programme Data — Data Analysis

Two numbers for the same thing

Somewhere there is a line-level register — daily attendance marks, a screening book, a patient register. Somewhere else there is a monthly figure that was reported upward from it. The two disagree, and the gap is the most informative number in this course.

It is informative because it is bounded. Unlike most data quality questions, this one has a right answer sitting in a drawer: the register is the source, the report is a claim about the register, and the difference between them is a measurable quantity rather than an opinion.

Donor audits call the ratio between them the verification factor — recount the source documents, divide the recount by what was reported. A verification factor of 1.0 means the reporting is accurate. Anything else is a number with a cause.

Recompute from the register

Aggregate the register to the reporting grain, exactly as the previous lesson did:

attendance["marked"] = attendance["present"].isin(["true", "false"])
attendance["is_present"] = attendance["present"] == "true"

from_register = (
    attendance.merge(roster[["student_id", "school_id"]], on="student_id",
                     how="left", validate="many_to_one")
    .groupby("school_id")
    .agg(marks=("marked", "sum"), present=("is_present", "sum"))
)
from_register["rate"] = from_register["present"] / from_register["marks"]

from_register <- attendance |>
  left_join(select(roster, student_id, school_id), by = "student_id",
            relationship = "many-to-one") |>
  summarise(
    marks   = sum(present %in% c("true", "false")),
    present = sum(present == "true", na.rm = TRUE),
    .by = school_id
  ) |>
  mutate(rate = present / marks)

Note what marked counts. A mark exists where the value is true or false. Values of Y, N and blank are not marks under this definition — and that definition is a decision, which is the whole of the next section.

The coding decision that moves a denominator

One school recorded attendance with Y and N instead of true and false. Recompute both ways:

mapping = {"true": True, "false": False, "Y": True, "N": False}
attendance["is_present_mapped"] = attendance["present"].map(mapping)

both_ways = (
    attendance.merge(roster[["student_id", "school_id"]], on="student_id")
    .groupby("school_id")
    .agg(
        marks_strict=("is_present", lambda s: s.notna().sum()),
        rate_strict=("is_present", "mean"),
        marks_mapped=("is_present_mapped", lambda s: s.notna().sum()),
        rate_mapped=("is_present_mapped", "mean"),
    )
)
print(both_ways.loc["SCH09"])

both_ways <- attendance |>
  left_join(select(roster, student_id, school_id), by = "student_id") |>
  mutate(mapped = dplyr::recode(present, "Y" = "true", "N" = "false")) |>
  summarise(
    marks_strict = sum(present %in% c("true", "false")),
    rate_strict  = mean(present[present %in% c("true", "false")] == "true"),
    marks_mapped = sum(mapped %in% c("true", "false")),
    rate_mapped  = mean(mapped[mapped %in% c("true", "false")] == "true"),
    .by = school_id
  )

SCH09	Strict	Y/N mapped
Marks in denominator	2,015	2,865
Attendance rate	86.05%	85.72%

Read those two columns together, because the lesson is in the contrast. The denominator grows by 42%. The rate moves by a third of a point.

That is the most common shape of this problem and the reason it survives review so easily. Anyone checking the rate concludes the coding did not matter. Anyone who then reports “number of attendance days recorded” is out by 850. A defect that barely touches a ratio can be enormous in a count, and programme reporting is full of counts.

Across the whole file the same operation moves the district rate from 88.47% to 88.42% and the denominator from 69,101 to 69,974.

Put the two sources in one table

Never compare two numbers by looking at two printouts. Join them, keyed on the reporting unit and period, and compute the difference as a column.

comparison = (
    from_register.rename(columns={"present": "register_present"})
    .join(reported.rename(columns={"present": "reported_present"}), how="outer")
)
comparison["difference"] = comparison["register_present"] - comparison["reported_present"]
comparison["verification_factor"] = (
    comparison["register_present"] / comparison["reported_present"]
)
print(comparison.sort_values("difference").head(10))

comparison <- from_register |>
  full_join(reported, by = c("school_id", "month"),
            suffix = c("_register", "_reported")) |>
  mutate(
    difference = present_register - present_reported,
    verification_factor = present_register / present_reported
  ) |>
  arrange(difference)

A full join, deliberately. A unit that appears in the register and not in the report has stopped reporting; a unit in the report with nothing in the register is reporting figures nobody can trace. Both are findings and an inner join deletes both.

Decompose, do not average

The single most common mistake here is computing one district-wide verification factor. A district factor of 1.02 can be twenty schools at 1.00 and one at 1.40, and the average has hidden the only thing worth acting on.

print(comparison["verification_factor"].describe())
print(comparison[comparison["verification_factor"].sub(1).abs() > 0.05])

comparison |>
  summarise(across(verification_factor, list(min = min, median = median, max = max)))

comparison |> filter(abs(verification_factor - 1) > 0.05)

Report the distribution and the outliers, never the mean alone. A tolerance band — anything outside 0.95 to 1.05 gets looked at — turns this from a number into a work list, which is what a supervisor can actually use.

Why two sources disagree

Four causes, and they call for completely different responses. Work through them in this order.

Different definitions. The register counts marks; the report counts enrolled children. The most common cause by a distance, the least often suspected, and it is not a data quality problem at all — it is two indicators with one name.
Different periods. The report covers a calendar month, the register was extracted on the 28th. Check the boundaries before anything else.
Different coverage. The report includes a school the register extract excluded, or vice versa. The anti-join from lesson 1 answers this in one line.
Transcription. Somebody added the column up by hand. This is the cause everyone assumes and the least frequent of the four.

Only the last is an error in the ordinary sense. The first three are reconciliation problems, and reporting them as “data quality issues” gets a supervisor blamed for something that was decided in a form design meeting.

Report the gap; do not reconcile it away

The temptation is to adjust the register figure until it matches the report, or to publish only the one that looks better. Publish both, with the difference:

summary = pd.DataFrame({
    "source": ["Line-level register", "Reported monthly returns"],
    "attendance days recorded": [69101, 68240],
    "attendance rate": ["88.5%", "89.1%"],
})

summary <- tibble::tibble(
  source = c("Line-level register", "Reported monthly returns"),
  days   = c(69101, 68240),
  rate   = c("88.5%", "89.1%")
)

Two rows and a sentence naming the largest contributor to the gap. This is what a data quality assessment is built from, and the Data Quality Assessment course — the next and last in this module — turns it into a routine with a sampling strategy and a corrective action plan.

A gap you report is a finding. A gap you close quietly is a figure nobody can reproduce, including you, in six months.

What comes next

Every piece is now in place: joins you can prove, a stated grain, the right shape, a denominator, a complete calendar and a reconciled source. The last lesson assembles them into one analysis table — one row per unit of analysis, every column traceable to where it came from, built by a script that runs from raw files to final table in one command.