The profile you run before you touch it — Data Cleaning and Validation — Data Analysis

The file is evidence until you edit it

Six months from now someone will ask whether the August figure was always like that. There are two ways that conversation goes. Either you open a file saved on the day the export arrived and read the answer off it, or you reconstruct it from memory and a script that has been edited eleven times since.

So the first thing you do with a new export is not clean it. It is profile it and write the profile down, before a single value changes. The profile is cheap — twenty lines — and it is the only artefact that can ever prove what the file looked like on arrival.

This lesson builds that profile. The Foundations course taught the five checks that find defects; this one turns them into something you keep.

Read it as text first

Every reader guesses. read_csv looks at the first few thousand rows and decides each column is a number, a date or a string, and every one of those guesses can destroy information you will never get back — a leading zero on a facility code, a sentinel -99 averaged into a mean, a date read as month-first.

Read once with everything as a string. That read is for looking, not for computing.

import pandas as pd

PATH = "muac-screening-artibonite-2024.v1.csv"
raw = pd.read_csv(PATH, dtype="string", keep_default_na=False)

print(raw.shape)
print(raw.dtypes)

library(readr)
library(dplyr)

PATH <- "muac-screening-artibonite-2024.v1.csv"
raw <- read_csv(PATH, col_types = cols(.default = col_character()))

dim(raw)

Two arguments are doing real work in the Python call. dtype="string" stops the type inference; keep_default_na=False stops pandas turning the literal strings NA, N/A, null and nan into missing values before you have seen them. In this sector that matters more than it sounds — a column where an enumerator typed NA for “not applicable” is not the same column as one where the field was skipped, and pandas conflates them by default.

The R read is deliberately col_character() for everything, which is also what problems() needs to be useful: read strictly later and any value that would not convert is reported rather than silently made NA.

What a profile has to answer

Six questions, and a profile that does not answer all six is not one:

How much arrived? Rows and columns, against what you expected.
What is each column really? Its raw values, not the type a reader guessed.
Where are the holes? Per column, and per site — never one global figure.
Is the key a key? Not “does the column exist” but “is it unique”.
What is out of range? Against the sector’s limits, not against the data’s own.
What codes are in use? Every distinct value of every categorical column.

Shape, against an expectation you write down

EXPECTED_COLUMNS = [
    "child_id", "commune", "screening_date", "age_months",
    "sex", "muac_mm", "oedema", "outcome",
]

print(f"{len(raw)} rows, {raw.shape[1]} columns")
print("missing columns:", set(EXPECTED_COLUMNS) - set(raw.columns))
print("unexpected columns:", set(raw.columns) - set(EXPECTED_COLUMNS))

EXPECTED_COLUMNS <- c(
  "child_id", "commune", "screening_date", "age_months",
  "sex", "muac_mm", "oedema", "outcome"
)

cat(nrow(raw), "rows,", ncol(raw), "columns\n")
setdiff(EXPECTED_COLUMNS, names(raw))
setdiff(names(raw), EXPECTED_COLUMNS)

This register has 4,218 rows and eight columns. The useful part is not the count; it is that you wrote down what you expected. A column that quietly disappeared between one export and the next is the most common breaking change a form platform ships, and it is invisible unless something is comparing against a list.

The column inventory

For every column: how many rows are blank, how many distinct values, and the handful of most frequent ones. This one table replaces most of what people do by scrolling.

def inventory(df):
    rows = []
    for column in df.columns:
        values = df[column]
        counts = values.value_counts(dropna=False)
        rows.append({
            "column": column,
            "blank": int((values == "").sum()),
            "distinct": int(values.nunique(dropna=False)),
            "top": counts.index[0] if len(counts) else None,
            "top_n": int(counts.iloc[0]) if len(counts) else 0,
        })
    return pd.DataFrame(rows)


print(inventory(raw).to_string(index=False))

inventory <- function(df) {
  purrr::map_dfr(names(df), function(column) {
    values <- df[[column]]
    counts <- sort(table(values, useNA = "ifany"), decreasing = TRUE)
    tibble::tibble(
      column   = column,
      blank    = sum(values == "" | is.na(values)),
      distinct = dplyr::n_distinct(values),
      top      = names(counts)[1],
      top_n    = as.integer(counts[1])
    )
  })
}

print(inventory(raw), n = Inf)

Run it on this register and two rows are worth stopping at.

Column	Blank	Distinct	Note
age_months	226	55	5.4% blank — lesson 2 asks where
oedema	44	4	four distinct values in a boolean column

Four distinct values in a boolean column is the finding. The register holds true, false, an empty string, and N — twenty-five rows where an enumerator used Y/N conventions in a column the form expected true/false in. Cast that column to a boolean and the N rows become missing, silently, and you have lost twenty-five recorded negatives by treating them as unrecorded.

That is the whole argument for reading as text first. After the cast there is nothing to find.

Sentinels are not missing values, yet

muac_mm looks complete: no blanks at all. It is not.

print(raw["muac_mm"].value_counts().head())
print("rows coded -99:", int((raw["muac_mm"] == "-99").sum()))

raw |> count(muac_mm, sort = TRUE) |> head()
sum(raw$muac_mm == "-99")

Seventy-two rows carry -99, the register’s code for “not measured”. Read the column as a number without declaring that code and the mean drops by about two millimetres and the caseload is understated, because you have averaged seventy-two children in at minus ninety-nine.

Sentinel codes are documented in the data dictionary and nowhere else. Check the dictionary before the first numeric read, every time — -99, -1, 999, 9999 and 88 are all in live use in this sector, and none of them look wrong in a summary.

Is the key a key?

The column called child_id is an identifier. Whether it is unique is a separate question, and the answer here is no.

duplicated_ids = raw["child_id"].duplicated(keep=False)
print("rows sharing a child_id:", int(duplicated_ids.sum()))
print("distinct ids involved:", raw.loc[duplicated_ids, "child_id"].nunique())

raw |>
  group_by(child_id) |>
  filter(n() > 1) |>
  ungroup() |>
  summarise(rows = n(), ids = n_distinct(child_id))

Twenty-four rows across twelve identifiers. Every join you write from here on assumes something about this column, and the assumption is currently false. Unit 2 is about that; the profile’s job is only to surface it on day one rather than in the middle of a join that quietly doubles a caseload.

Ranges, before you trust a summary

A five-number summary hides exactly the values you are looking for, because one implausible row barely moves a quartile. Look at the extremes directly.

muac = pd.to_numeric(raw["muac_mm"], errors="coerce")
muac = muac.where(muac != -99)

print(muac.describe())
print(muac.nsmallest(10).tolist())
print(muac.nlargest(10).tolist())

muac <- suppressWarnings(as.numeric(raw$muac_mm))
muac[muac == -99] <- NA

summary(muac)
head(sort(muac), 10)
head(sort(muac, decreasing = TRUE), 10)

The ten smallest values run 13, 14, 14, 15, 16, 17, 18 and then jump to 92. The first seven are centimetres that were never converted — a MUAC of 13.4 cm typed as 13. The mean barely notices them. The tail names them immediately.

Always look at the ten smallest and ten largest values of any measurement column. It costs one line and it is the single highest-yield check in this lesson.

Save the profile next to the data

The profile is only worth writing if it survives the session.

from pathlib import Path
import json

profile = {
    "file": PATH,
    "rows": len(raw),
    "columns": list(raw.columns),
    "blank_by_column": {c: int((raw[c] == "").sum()) for c in raw.columns},
    "distinct_by_column": {c: int(raw[c].nunique()) for c in raw.columns},
    "duplicate_key_rows": int(raw["child_id"].duplicated(keep=False).sum()),
    "categorical_values": {
        c: sorted(raw[c].unique().tolist())
        for c in ["commune", "sex", "oedema", "outcome"]
    },
}

Path("outputs/profiles").mkdir(parents=True, exist_ok=True)
Path("outputs/profiles/muac-2024-q4.json").write_text(json.dumps(profile, indent=2))

profile <- list(
  file    = PATH,
  rows    = nrow(raw),
  columns = names(raw),
  blank_by_column    = sapply(raw, function(x) sum(x == "" | is.na(x))),
  distinct_by_column = sapply(raw, dplyr::n_distinct),
  duplicate_key_rows = sum(duplicated(raw$child_id) | duplicated(raw$child_id, fromLast = TRUE)),
  categorical_values = lapply(
    raw[c("commune", "sex", "oedema", "outcome")],
    function(x) sort(unique(x))
  )
)

dir.create(here::here("outputs", "profiles"), recursive = TRUE, showWarnings = FALSE)
jsonlite::write_json(
  profile,
  here::here("outputs", "profiles", "muac-2024-q4.json"),
  pretty = TRUE, auto_unbox = TRUE
)

JSON rather than a printed table, because the point of the next section is to compare two of them.

Comparing this export against the last one

Most exports are the same export again, one quarter later. The interesting question is therefore never “what is in this file” but “what changed”.

previous = json.loads(Path("outputs/profiles/muac-2024-q3.json").read_text())

for column, values in profile["categorical_values"].items():
    was, now = set(previous["categorical_values"][column]), set(values)
    if was != now:
        print(f"{column}: new {sorted(now - was)}, gone {sorted(was - now)}")

growth = (profile["rows"] - previous["rows"]) / previous["rows"]
print(f"row count moved {growth:.1%}")

previous <- jsonlite::read_json(
  here::here("outputs", "profiles", "muac-2024-q3.json"),
  simplifyVector = TRUE
)

for (column in names(profile$categorical_values)) {
  was <- previous$categorical_values[[column]]
  now <- profile$categorical_values[[column]]
  if (!setequal(was, now)) {
    cat(column, ": new", setdiff(now, was), "| gone", setdiff(was, now), "\n")
  }
}

sprintf("row count moved %.1f%%", 100 * (profile$rows - previous$rows) / previous$rows)

A new value in a categorical column is the single most common way an analysis starts producing wrong answers without failing. A form gets a new response option, a thirteenth commune is added, an antigen is renamed — and every case_when written before that day now sends the new value to its else branch. Comparing profiles catches it on arrival, which is the only moment it is cheap.

A profile you did not save is a profile you did not run. The value is entirely in being able to open it later.

What comes next

You now know this register is missing 5.4% of its ages. That figure on its own is almost meaningless — it matters enormously whether those 226 rows are scattered across twelve communes or concentrated in one. The next lesson breaks missingness down until it either stops looking like an accident or proves it is one, and puts a number on what dropping those rows would do to the ranking you publish.