cassionData Analysis

Back to the lessonLesson 7 of 8The table you analyse

When the register and the report disagree

The same deck as the downloads, rendered as a page. Start the slideshow to present it full screen — arrow keys or a click advance one slide, Escape leaves.

Slides · PDFSlides · PowerPoint

  1. Slide 1 / 18

    What this lesson covers

    • Two numbers for the same thing
    • Recompute from the register
    • The coding decision that moves a denominator
    • Put the two sources in one table
    • Decompose, do not average
    • Why two sources disagree
    • Report the gap; do not reconcile it away
    • What comes next
    Speaker notes
    Recompute the indicator from the line-level register, put it beside what was reported upward, and decompose the difference by reporting unit instead of averaging it away.

  2. Slide 2 / 18

    Two numbers for the same thing

    • Somewhere there is a line-level register — daily attendance marks, a screening book, a patient register.
    Speaker notes
    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.

  3. Slide 3 / 18

    Recompute from the register — In Python

    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"]
    Speaker notes
    Aggregate the register to the reporting grain, exactly as the previous lesson did:

  4. Slide 4 / 18

    Recompute from the register — In R

    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)
    Speaker notes
    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.

  5. Slide 5 / 18

    The coding decision that moves a denominator — In Python

    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"])
    Speaker notes
    One school recorded attendance with Y and N instead of true and false. Recompute both ways:

  6. Slide 6 / 18

    The coding decision that moves a denominator — In R

    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
  )

  7. Slide 7 / 18

    The coding decision that moves a denominator

    SCH09StrictY/N mapped
    Marks in denominator2,0152,865
    Attendance rate86.05%85.72%
    Speaker notes
    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.

  8. Slide 8 / 18

    Put the two sources in one table — In Python

    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))
    Speaker notes
    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.

  9. Slide 9 / 18

    Put the two sources in one table — In R

    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)
    Speaker notes
    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.

  10. Slide 10 / 18

    Decompose, do not average — In Python

    print(comparison["verification_factor"].describe())
print(comparison[comparison["verification_factor"].sub(1).abs() > 0.05])
    Speaker notes
    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.

  11. Slide 11 / 18

    Decompose, do not average — In R

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

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

  12. Slide 12 / 18

    Decompose, do not average

    • Report the distribution and the outliers, never the mean alone — A tolerance band — anything outside 0.95 to 1.05 gets…
    Speaker notes
    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.

  13. Slide 13 / 18

    Why two sources disagree

    • Different definitions. The register counts marks; the report counts enrolled children. The most common cause by a…
    • Different periods. The report covers a calendar month, the register was extracted on the 28th. Check the boundaries…
    • Different coverage. The report includes a school the register extract excluded, or vice versa. The anti-join from…
    • Transcription. Somebody added the column up by hand. This is the cause everyone assumes and the least frequent of…
    Speaker notes
    Four causes, and they call for completely different responses. Work through them in this order. 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.

  14. Slide 14 / 18

    Report the gap; do not reconcile it away — In Python

    summary = pd.DataFrame({
    "source": ["Line-level register", "Reported monthly returns"],
    "attendance days recorded": [69101, 68240],
    "attendance rate": ["88.5%", "89.1%"],
})
    Speaker notes
    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:

  15. Slide 15 / 18

    Report the gap; do not reconcile it away — In R

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

  16. Slide 16 / 18

    Report the gap; do not reconcile it away

    A gap you report is a finding. A gap you close quietly is a figure nobody can reproduce, including you, in six months.
    Speaker notes
    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.

  17. Slide 17 / 18

    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.
    Speaker notes
    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.

  18. Slide 18 / 18

    Where this goes next

    Read the full lesson, with runnable code Back to the lesson