6. Series across documents

Question: Can tables from many documents be combined into one series?

Rationale

A single extracted table is a snapshot. The value of extraction comes when twenty years of yearbooks become one series that a user can chart and an API can serve. Combining tables across documents raises questions that a single extraction does not: the same statistic appears in several reports with different values after revision; units and labels change ("percent", "% of population"); regions are renamed or split; a methodology changes and the series breaks. Each of these has to be decided and recorded, or the combined series is wrong in a way that no single table was.

The combined series is also where extracted data join the catalog: a series record with its values file, its provenance per value, and its breaks is the same product as any other indicator the organization publishes.

Target state

• Every extracted table that belongs to a series is mapped to it, with the table's dimensions named (area, period) and its unit normalized.
• Overlapping values across documents are resolved by a stated rule (latest revision kept, earlier recorded), and every value carries its source document and page.
• Breaks in series are detected and recorded, and the series is published as a catalog record with a values file.

Maturity levels

Level	Steps
Foundational	Map the extracted tables of one statistic to a series and combine them with the script of this chapter; review the revisions.
AI-ready	Maintain the mapping for every series extracted; normalize units and areas through code lists; record breaks; publish the series with a values file and a record.
AI-native	Run the combination as a pipeline stage; let a model propose mappings for new tables and break explanations from the documents' notes, confirmed by a curator; serve the series through the API and agent interface.

Running example

The prevalence of undernourishment by region appears in the Food security report 2024 (2021 to 2023) and the Food security report 2025 (2022 to 2024). Combined, the series has twelve values for three areas and four years; four values are confirmed by both reports, and two 2023 values were revised in the later report. The series joins the catalog as FS_UNDERNOURISH_PCT.

Recipes

Recipe

6.1 Map extracted tables to a series and combine them

Level

Foundational

Skills

Data curator; Python

Indicative time

An hour per series to map; minutes to run

Result: each series has one values file with one row per area and period. Every value carries its source document and page, and a status that says whether a later document confirmed or revised it.

The same series appears in several documents: the 2024 report has values up to 2023, the 2025 report up to 2024, and the overlapping years may differ. The mapping file says which table in which document feeds which series, and how to read its rows and columns:

Column	Meaning
document_id, title_prefix	The document, and the start of the table title that identifies the table
series_id	The series the table feeds
row_role, column_role	Which of row and column is the area and which the period
unit_label, unit_aliases	The unit the series uses, and the other spellings that mean the same (percent, %)

The script reads the mapping and the verified tidy files, stacks the values, and resolves the overlaps:

"""Combine tidy extractions from several documents into one series.

Reads tidy CSV files (the output of snapshot_to_tidy.py: one row per value
with document_id, page, title, row, column, value, unit, status) and a
mapping file that says which table in which document feeds which series,
which of the table's dimensions is the area and which the period, and
which unit labels mean the same unit. Writes one series file with one row
per area and period, the value, the source document and page, and a
status:

    single      the value appears in one document
    confirmed   two documents report the same value for the area and period
    revised     a later document reports a different value; the later
                value is kept, the earlier one is recorded beside it

Documents are ordered by their year in the mapping's document identifier
order of appearance, earliest first. Prints the counts and the revisions.
Uses pandas.

Usage:
    python stack_series.py series_mapping.csv tidy_fs2024.csv tidy_fs2025.csv -o series_fs.csv

What this does not do: it matches areas by exact label and periods by
exact string; a renamed region or a fiscal-year period needs a mapping
row. It keeps the latest value of a revision, which is right for a
revised statistic and wrong for a definitional change; the documents'
notes say which, and the series record carries the break.
"""

from __future__ import annotations

import argparse
import sys
from pathlib import Path

import pandas as pd

OUT = [
    "series_id",
    "area",
    "period",
    "value",
    "unit",
    "source_document",
    "source_page",
    "status",
    "previous_value",
    "previous_document",
]


def map_rows(tidy: pd.DataFrame, mapping: pd.DataFrame) -> tuple[pd.DataFrame, int]:
    """Attach series, area, and period to each tidy row through the mapping; count rows no mapping covers."""
    mapped = []
    unmatched = 0
    for r in tidy.itertuples():
        m = mapping[
            (mapping["document_id"] == r.document_id)
            & mapping["title_prefix"].map(r.title.startswith)
        ]
        if m.empty:
            unmatched += 1
            continue
        m = m.iloc[0]
        aliases = {a.strip().lower() for a in m["unit_aliases"].split(";")}
        if r.unit.strip().lower() not in aliases:
            unmatched += 1
            continue
        area, period = (
            (r.row, r.column) if m["row_role"] == "area" else (r.column, r.row)
        )
        mapped.append(
            {
                "series_id": m["series_id"],
                "area": area,
                "period": period,
                "value": r.value,
                "unit": m["unit_label"],
                "source_document": r.document_id,
                "source_page": r.page,
            }
        )
    return pd.DataFrame(mapped), unmatched


def resolve(mapped: pd.DataFrame) -> pd.DataFrame:
    """Keep the latest value per series, area, and period; mark it confirmed or revised when an earlier document reported it."""
    out = []
    for _, g in mapped.groupby(["series_id", "area", "period"], sort=True):
        first, last = g.iloc[0], g.iloc[-1]
        row = last.to_dict() | {
            "status": "single",
            "previous_value": "",
            "previous_document": "",
        }
        if len(g) > 1:
            if first["value"] == last["value"]:
                row["status"] = "confirmed"
            else:
                row |= {
                    "status": "revised",
                    "previous_value": first["value"],
                    "previous_document": first["source_document"],
                }
        out.append(row)
    return pd.DataFrame(out, columns=OUT)


def main(argv: list[str] | None = None) -> int:
    parser = argparse.ArgumentParser(
        description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter
    )
    parser.add_argument("mapping", type=Path)
    parser.add_argument("tidy", nargs="+", type=Path)
    parser.add_argument("-o", "--output", type=Path)
    args = parser.parse_args(argv)
    mapping = pd.read_csv(args.mapping, dtype=str).fillna("")
    order = {
        d: i for i, d in enumerate(mapping["document_id"].unique())
    }  # documents in the mapping's order, earliest first
    tidy = pd.concat(
        [pd.read_csv(p, dtype=str).fillna("") for p in args.tidy], ignore_index=True
    )
    tidy = tidy.sort_values("document_id", key=lambda s: s.map(order), kind="stable")

    mapped, unmatched = map_rows(tidy, mapping)
    series = resolve(mapped)
    series.to_csv(args.output or sys.stdout, index=False)

    counts = series["status"].value_counts()
    print(
        f"{len(tidy)} tidy rows from {len(args.tidy)} documents -> {len(series)} series values; {unmatched} rows unmatched",
        file=sys.stderr,
    )
    print(
        ", ".join(
            f"{k} {counts.get(k, 0)}" for k in ("single", "confirmed", "revised")
        ),
        file=sys.stderr,
    )
    for r in series[series["status"] == "revised"].itertuples():
        print(
            f"revised: {r.area} {r.period}: {r.previous_value} ({r.previous_document}) -> {r.value} ({r.source_document})",
            file=sys.stderr,
        )
    return 0


if __name__ == "__main__":
    sys.exit(main())

python stack_series.py series_mapping.csv tidy_fs2024.csv tidy_fs2025.csv -o series_fs.csv

Output for the running example:

18 tidy rows from 2 documents -> 12 series values; 0 rows unmatched
single 6, confirmed 4, revised 2
revised: Northern Region 2023: 9.7 (EX-REP-2024-03) -> 9.6 (EX-REP-2025-03)
revised: Total 2023: 7.9 (EX-REP-2024-03) -> 7.8 (EX-REP-2025-03)

The first line counts the tidy rows read and the series values written. The second counts the values by status: single when one document reports the value, confirmed when two documents agree, and revised when they differ. The lines that follow name each revision with both values and both documents.

1. Write the mapping (series_mapping.csv).
2. Run the script on the tidy files of chapter 4, verified in chapter 5, and read the result (series_fs.csv). Every value has a source document and page. A confirmed value agrees across documents; a revised value keeps the later one and records the earlier.
3. Review the revisions against the documents' notes. A revision after the release of final survey data is a revision; a change of definition is a break (recipe 6.2).
4. Rows reported as unmatched are tables whose title or unit the mapping does not cover. Extend the mapping, with a model's proposal where the catalog is large.

Worked example. The mapping has two rows, one per report, both feeding FS_UNDERNOURISH_PCT, with the row as the area and the column as the period, and percent and % listed as aliases of the unit. The 2024 report's table covers 2021 to 2023 and the 2025 report's covers 2022 to 2024, for the Northern Region, the Central Region, and the total. The script finds the four overlapping values for 2022 confirmed and two of the three for 2023 revised. The curator reads the 2025 report's note, which says that the 2023 figures were updated with final survey data, and records the two changes as revisions. The combined file's row for the Northern Region in 2023 reads 9.6, with the status revised, the earlier value 9.7, and the source EX-REP-2025-03, page 24.

What this does not do. The script matches area and period labels exactly and keeps the latest value. Renamed areas, split regions, and fiscal-year periods need rows in the mapping or a code list, and the choice between "revision" and "break" is the curator's.

Recipe

6.2 Detect and record breaks in the series

Level

AI-ready

Skills

Subject-matter statistician

Indicative time

Half a day per series

Result: every point at which the series stops being comparable with its past is recorded in the series record with its reason, and marked in the values file.

A break is a change in definition, coverage, classification, sample design, or base year, after which the values mean something slightly different. A revision is a correction of a few values. The two look alike in the numbers and differ in the documents.

1. Read the notes and methodology sections of the source documents for the series. The text layer of chapter 1 makes them searchable.
2. Compare overlapping values. A revision changes a few recent values by small amounts; a break changes all overlapping values or shifts the level. Flag both for the statistician.
3. Record each break in the series record (methodology, notes, or the schema's limitations field) with the period and the reason, and mark the values from the break onward with a status in the values file. The SDMX OBS_STATUS code list has "break" as a value.
4. Where the earlier and later definitions can be reconciled, publish the correspondence or the overlap period so that users can link the segments. Otherwise publish two series.

Worked example. When the curator extends the series back to the 2016 food security report, every overlapping value with the 2017 report differs by between 0.6 and 0.9 points, and all in the same direction. That pattern is a level shift, and the methodology section of the 2017 report confirms it: the 2016 household survey changed the food item list, so the prevalence from 2016 onwards is measured on a different basis. The statistician records a break at 2016 in the series record with that reason, marks the 2016 and later values with OBS_STATUS B, and publishes the two overlapping years so that a user can see the size of the shift. The 2023 changes, by contrast, are small and limited to one year, and stay recorded as revisions.

What this does not do. A break detected from numbers alone is a guess. The documents' notes are the evidence, and a statistician decides. A language model can draft the break explanation from the notes for review, which the AI-native level does.

Recipe

6.3 Publish the series as a catalog record

Level

AI-ready

Skills

Data curator

Indicative time

An hour per series

Result: the combined series is an indicator record in the catalog with a values file, the source of every value, and its breaks recorded, and it is served like any other series.

1. Create the indicator record in the World Bank indicator schema (dissemination cookbook, chapter 1), with the source documents listed as sources and the extraction method described.
2. Publish the values file in the layout of the dissemination cookbook's values files (SDMX-style columns, OBS_STATUS for confirmed, revised, and break), with the source document and page as attributes per value.
3. Link the record to the table records of chapter 7 and to the document records, in both directions.
4. Run the completeness check with the indicator profile, and the verification of printed numbers against the catalog (chapter 5, recipe 5.3), which now has the series to check against.

Worked example. The indicator record for the regional prevalence of undernourishment names the three food security reports as its sources. Its methodology field says that the values were extracted from the reports' district tables, verified against the printed totals, and combined with the latest revision kept. The values file follows the dissemination cookbook's layout, with OBS_STATUS B from 2016 and the source document and page as attributes on each row. The record links to the three table records and the three document records, and each of them links back. The completeness check passes at the AI-ready level, and the number verification of chapter 5 now checks the printed figures of future reports against this series.

What this does not do. A series built from documents has the quality of the documents. The record says so in its methodology, and the source per value lets a user go back to the page.

Implementation options

• Mapping: a CSV per series as in this chapter; a code list for areas and periods when they vary; a model's proposal for new tables at the AI-native level.
• Overlap rule: latest revision kept and earlier recorded, as here; or an explicit vintage dimension when users need every published value (a real-time database).
• Breaks: OBS_STATUS codes and the series record's notes; a correspondence or an overlap period where segments can be linked.
• Publication: the indicator schema and values files of the dissemination cookbook; SDMX for exchange.

World Bank examples

• The dissemination cookbook's values files and indicator record are the form a combined series takes.
• Data360 serves series with their metadata and sources, the destination of a series extracted from documents.
• The World Development Indicators document revisions and breaks in their series notes, the practice recipe 6.2 follows.

Resources

• SDMX OBS_STATUS code list: the published codes for provisional, revised, break, and estimated values.
• IMF Dissemination Standards Bulletin Board: revision policy and practice for official statistics, for the overlap rule.
• Eurostat guidelines on revision policy: the European Statistics Code of Practice principle on revisions and the related guidelines.
• ALFRED: a vintage database of economic series, the reference example for keeping every published value.
• Retrieval chapter of the dissemination cookbook: the values file layout and the API that serves the series.

Common mistakes

• Averaging conflicting values. An overlap is a revision or a break, and a curator decides which; an average is neither.
• Units normalized by hand. The mapping normalizes them once, or the next report breaks the series again.
• Provenance at the table level only. Each value keeps its document and page, because values from one series come from many documents.
• Series without a record. A values file alone cannot be found, cited, or checked.

Verification

• Mapping. Every extracted table that belongs to a series has a mapping row, and no rows are unmatched.
• Overlaps. Every revised value has its earlier value recorded, and the revisions have been reviewed against the documents' notes.
• Breaks. The breaks are recorded in the record and marked in the values file.
• Catalog. The series record passes the completeness check, and the printed-number verification finds the series.

Checklist

0 of 6 done

• Mapping from extracted tables to series with dimension roles and unit aliases
• Combined values file with source document and page per value
• Revisions reviewed; latest kept and earlier recorded
• Breaks recorded in the record and marked in the values file
• Series record in the indicator schema linked to tables and documents
• Completeness check and printed-number verification pass

Ticks are saved in this browser only.