03 Charting

This notebook cleans the charted data and checks it against the codebook. The charting form has two tables. data/raw/included_sources_raw.csv has one row per included source. data/raw/included_factors_raw.csv has one row per factor that a source reports. The notebook writes data/processed/included_sources_clean.csv, data/processed/included_factors_clean.csv, and data/processed/bibliography.csv, which holds the reference details of each included source, taken from the deduplicated records.

The variables, the closed value lists, and the required variables are read from codebook.yml, so this notebook holds no copy of them. Cleaning trims whitespace and lowercases the closed variables.

The checks come first. If any check finds an error, the notebook stops and writes nothing. A warning is printed and does not stop the run. Errors are an empty required cell, a value outside the codebook, a repeated source_id, a factor whose source_id is not in the sources table, a record_id that is not in the deduplicated records, and a source_id that screening did not include. Warnings are sources with no factors, included sources not charted yet, repeated factor rows, and factors not yet coded to a category or level.

from pathlib import Path

import pandas as pd
import yaml

ROOT = next(p for p in [Path.cwd(), *Path.cwd().parents] if (p / "_quarto.yml").exists())
PROC = ROOT / "data" / "processed"
DEDUP = PROC / "deduplicated_records.csv"
INCLUDED = PROC / "included_reports.csv"

TABLES = yaml.safe_load((ROOT / "codebook.yml").read_text(encoding="utf-8"))["tables"]
for t in TABLES.values():
    for v in t["variables"]:
        v["values"] = v.get("values", [])
        if not all(isinstance(x, str) for x in v["values"]):
            raise ValueError(f'codebook.yml: the values of {v["name"]} must be text. Put quotes around values such as "yes", "no", and "1".')


def load(name):
    t = TABLES[name]
    path = ROOT / t["file"]
    cols = [v["name"] for v in t["variables"]]
    if not path.exists():
        return pd.DataFrame(columns=cols)
    df = pd.read_csv(path, dtype=str).fillna("")
    df.columns = [c.strip().lower() for c in df.columns]
    absent = [c for c in cols if c not in df.columns]
    if absent:
        raise ValueError(f"{path.name} is missing column(s): {', '.join(absent)}")
    return df[cols].apply(lambda s: s.str.strip())


def sample(values, n=5):
    values = sorted(set(map(str, values)))
    return ", ".join(values[:n]) + (f" and {len(values) - n} more" if len(values) > n else "")


def lines(mask):
    # Line numbers in the CSV file: the header is line 1.
    numbers = sorted(i + 2 for i in mask[mask].index)
    return ", ".join(map(str, numbers[:5])) + (f" and {len(numbers) - 5} more" if len(numbers) > 5 else "")


sources, factors = load("sources"), load("factors")
have_data = len(sources) > 0 or len(factors) > 0
print(f"{len(sources)} charted source(s), {len(factors)} charted factor(s)." if have_data else "Nothing charted yet. Add rows to the two charting files in data/raw/.")

Nothing charted yet. Add rows to the two charting files in data/raw/.

if have_data:
    clean = {"sources": sources.copy(), "factors": factors.copy()}
    for name, df in clean.items():
        for v in TABLES[name]["variables"]:
            if v["values"]:
                df[v["name"]] = df[v["name"]].str.lower()

Checks

if have_data:
    errors, warnings = [], []
    src, fac = clean["sources"], clean["factors"]

    # Each table against the codebook
    for name, df in clean.items():
        file = Path(TABLES[name]["file"]).name
        for v in TABLES[name]["variables"]:
            col = df[v["name"]]
            if v.get("required"):
                empty = col == ""
                if empty.any():
                    errors.append(f"{file}: {int(empty.sum())} row(s) have an empty {v['name']} (line {lines(empty)}).")
            if v["values"]:
                bad = (col != "") & ~col.isin(v["values"])
                if bad.any():
                    found = sample(col[bad])
                    errors.append(f"{file}: {v['name']} has value(s) outside the codebook: {found} (line {lines(bad)}). Allowed: {', '.join(v['values'])}.")

    # Sources
    repeated = src["source_id"].duplicated(keep=False) & (src["source_id"] != "")
    if repeated.any():
        errors.append(f"included_sources_raw.csv: source_id values repeated: {sample(src.loc[repeated, 'source_id'])}.")
    if DEDUP.exists():
        dedup_ids = set(pd.read_csv(DEDUP, dtype=str)["record_id"])
        unknown = set(src["record_id"]) - dedup_ids - {""}
        if unknown:
            errors.append(f"included_sources_raw.csv: {len(unknown)} record_id value(s) are not in deduplicated_records.csv: {sample(unknown)}.")
    else:
        warnings.append("deduplicated_records.csv not found, so record_id values were not checked.")
    if INCLUDED.exists():
        included = set(pd.read_csv(INCLUDED, dtype=str)["source_id"])
        unknown = set(src["source_id"]) - included - {""}
        if unknown:
            errors.append(f"included_sources_raw.csv: {len(unknown)} source_id value(s) were not included in screening: {sample(unknown)}.")
        waiting = included - set(src["source_id"])
        if waiting:
            warnings.append(f"{len(waiting)} included source(s) are not charted yet: {sample(waiting)}.")
    else:
        warnings.append("included_reports.csv not found, so source_id values were not checked against screening.")

    # Factors
    unknown = set(fac["source_id"]) - set(src["source_id"]) - {""}
    if unknown:
        errors.append(f"included_factors_raw.csv: {len(unknown)} source_id value(s) are not in included_sources_raw.csv: {sample(unknown)}.")
    without = set(src["source_id"]) - set(fac["source_id"]) - {""}
    if without:
        warnings.append(f"{len(without)} charted source(s) have no factor rows: {sample(without)}.")
    same = fac.duplicated(["source_id", "factor", "factor_type", "level"], keep=False)
    if same.any():
        warnings.append(f"included_factors_raw.csv: {int(same.sum())} row(s) repeat another row (line {lines(same)}).")
    for col in ("factor_category", "level"):
        n = int((fac[col] == "").sum())
        if n:
            warnings.append(f"{n} factor row(s) have no {col} yet.")

    for w in warnings:
        print("Warning:", w)
    if errors:
        raise ValueError("Fix these problems in the charting files, then run this notebook again:\n- " + "\n- ".join(errors))
    print("No errors found.")

if have_data:
    src.to_csv(PROC / "included_sources_clean.csv", index=False)
    fac.to_csv(PROC / "included_factors_clean.csv", index=False)
    print("Wrote data/processed/included_sources_clean.csv and included_factors_clean.csv")
    if DEDUP.exists():
        dedup = pd.read_csv(DEDUP, dtype=str).fillna("")
        keep = ["record_id", "authors", "year", "title", "journal", "doi"]
        bib = src[["source_id", "record_id"]].merge(dedup[keep], on="record_id", how="left")
        bib.to_csv(PROC / "bibliography.csv", index=False)
        print("Wrote data/processed/bibliography.csv")
        display(bib.head(20))
    else:
        print("deduplicated_records.csv not found, so bibliography.csv was not written.")

Summary

if have_data:
    display(src.groupby(["tier", "field"]).size().rename("sources").reset_index())
    if len(fac):
        display(fac.groupby(["factor_type", "factor_category"], dropna=False).size().rename("factors").reset_index())