File formats and dataset comparison

Lesson 7 — Python

Lesson 7 of 20 Intermediate ~80 min

Learning objectives

  • Read and write text, CSV, Excel, JSON, Parquet, HDF5 and NumPy formats
  • Choose the right format for a given job
  • Handle nested JSON and flatten it into a DataFrame
  • Compare two datasets the way SAS PROC COMPARE does
  • Write a reusable comparison function that reports differences precisely

Choosing a format

Format Read Types preserved Compression Use for
CSV read_csv No No Interchange, human inspection
Excel read_excel Partly Yes Business handoff, reference tables
JSON read_json Partly No APIs, nested data, configuration
Parquet read_parquet Yes Yes Analytical storage, caching
HDF5 read_hdf Yes Yes Large arrays, partial reads
.npy / .npz np.load Yes Optional Raw NumPy arrays
Pickle read_pickle Yes Optional Never for untrusted data
SAS pyreadstat Yes + labels No Clinical source data

The default recommendation for anything that stays inside your pipeline is Parquet. It preserves dtypes exactly, compresses well, reads fast, and is readable from R, Python, Spark and DuckDB.

Text files

from pathlib import Path

# Read
text = Path("notes.txt").read_text(encoding="utf-8")
lines = Path("notes.txt").read_text().splitlines()

# Line by line, for a file too large to hold in memory
with open("large.log", encoding="utf-8") as f:
    for line in f:
        process(line.rstrip("\n"))

# Write
Path("out.txt").write_text("content\n", encoding="utf-8")

with open("out.txt", "w", encoding="utf-8") as f:
    f.write("first line\n")
    f.writelines(f"{x}\n" for x in items)
WarningAlways specify the encoding 
open("file.txt")                        # uses the platform default
open("file.txt", encoding="utf-8")      # explicit, portable

The default differs between Windows (often cp1252) and Linux/macOS (UTF-8). A script that works on your laptop and produces mojibake on the validation server is almost always this. Since Python 3.15 the default is UTF-8 everywhere, but being explicit costs nothing and works on every version.

CSV

import pandas as pd

df = pd.read_csv("data.csv")

df = pd.read_csv(
    "data.csv",
    dtype={"SITEID": "string", "SUBJID": "string"},   # preserve leading zeros
    parse_dates=["RFSTDTC"],
    na_values=["", "NA", "N/A", "."],
    keep_default_na=False,        # only YOUR sentinels count as missing
    encoding="utf-8",
    sep=",",
    thousands=",",
    decimal=".",
)

df.to_csv("out.csv", index=False, na_rep="")

The same rule as R’s readr: specify dtypes rather than letting the parser guess. SITEID read as an integer turns "007" into 7 and the join to the site table silently finds nothing.

For a file too large for memory:

for chunk in pd.read_csv("huge.csv", chunksize=100_000):
    process(chunk)

# Or push the work to DuckDB, which streams
import duckdb
duckdb.sql("SELECT USUBJID, AVG(AVAL) FROM 'huge.csv' GROUP BY USUBJID").df()

Excel

xl = pd.ExcelFile("workbook.xlsx")
xl.sheet_names

df = pd.read_excel("workbook.xlsx", sheet_name="Chemistry")
df = pd.read_excel("workbook.xlsx", sheet_name=0, skiprows=2, nrows=100)
df = pd.read_excel("workbook.xlsx", usecols="A:F")
df = pd.read_excel("workbook.xlsx", dtype=str)      # read everything as text

# Every sheet at once
sheets = pd.read_excel("workbook.xlsx", sheet_name=None)   # -> dict
combined = pd.concat(sheets, names=["sheet"]).reset_index(level=0)

Writing several sheets with formatting:

with pd.ExcelWriter("out.xlsx", engine="openpyxl") as writer:
    adsl.to_excel(writer, sheet_name="Subjects", index=False)
    adae.to_excel(writer, sheet_name="Events", index=False)

    metadata = pd.DataFrame({
        "Item":  ["Generated", "User", "Rows", "Python"],
        "Value": [pd.Timestamp.now().isoformat(), os.getlogin(),
                  len(adsl), sys.version.split()[0]],
    })
    metadata.to_excel(writer, sheet_name="Metadata", index=False)
TipExcel is a presentation format, not a data format

Merged cells, colour-coded meaning, footnotes in the last row, numbers stored as text, and dates as serial numbers are all normal. Read with dtype=str, inspect, then convert deliberately.

Excel serial dates, when a cell arrives as a number:

pd.to_datetime(45000, unit="D", origin="1899-12-30")
#> Timestamp('2023-03-15 00:00:00')

The origin is 1899-12-30, not 1900-01-01 — Excel treats 1900 as a leap year, which it was not.

JSON

import json

# Plain Python
data = json.loads('{"a": 1}')
data = json.load(open("config.json", encoding="utf-8"))

json.dumps(data, indent=2)
json.dump(data, open("out.json", "w"), indent=2, default=str)

# Straight to a DataFrame
df = pd.read_json("records.json")
df = pd.read_json("records.jsonl", lines=True)     # one object per line
df.to_json("out.json", orient="records", indent=2, date_format="iso")

Nested JSON

API responses are rarely flat. json_normalize flattens them:

response = {
    "study": "ABC-101",
    "subjects": [
        {"usubjid": "001", "demographics": {"age": 45, "sex": "F"},
         "events": [{"term": "Headache"}, {"term": "Nausea"}]},
        {"usubjid": "002", "demographics": {"age": 72, "sex": "M"},
         "events": [{"term": "Rash"}]},
    ],
}

# One row per subject, nested dicts flattened to dotted columns
subjects = pd.json_normalize(response["subjects"])
subjects.columns
#> ['usubjid', 'events', 'demographics.age', 'demographics.sex']

# One row per event, carrying subject-level fields down
events = pd.json_normalize(
    response["subjects"],
    record_path="events",
    meta=["usubjid", ["demographics", "age"]],
)
events
#>       term usubjid  demographics.age
#> 0  Headache     001                45
#> 1    Nausea     001                45
#> 2      Rash     002                72

record_path names the list to explode; meta names the parent fields to carry down, using a list for nested paths. This is the Python equivalent of tidyr::unnest().

Parquet

df.to_parquet("data.parquet", index=False, compression="snappy")
df = pd.read_parquet("data.parquet")

# Only the columns you need — Parquet is columnar, so this is genuinely cheaper
df = pd.read_parquet("data.parquet", columns=["USUBJID", "AVAL"])

# Partitioned, for large data
df.to_parquet("data/adlb", partition_cols=["PARAMCD"])

import pyarrow.dataset as ds
dataset = ds.dataset("data/adlb", format="parquet", partitioning="hive")
alt = dataset.to_table(filter=ds.field("PARAMCD") == "ALT").to_pandas()

Partitioning writes one subdirectory per value, so a filter on the partition column skips whole files rather than reading and discarding rows.

HDF5

For large numerical arrays with partial-read requirements:

df.to_hdf("store.h5", key="adsl", mode="w", format="table")
df = pd.read_hdf("store.h5", key="adsl")

# Query without loading everything
df = pd.read_hdf("store.h5", key="adlb", where="PARAMCD == 'ALT' and AVAL > 40")

with pd.HDFStore("store.h5") as store:
    store["adsl"] = adsl
    store["adae"] = adae
    print(store.keys())

format="table" is required for querying; the default "fixed" is faster to write but reads all or nothing. In practice Parquet has displaced HDF5 for most tabular work — HDF5 remains preferable for genuinely multidimensional arrays.

NumPy formats

import numpy as np

np.save("array.npy", arr)                 # single array
arr = np.load("array.npy")

np.savez("arrays.npz", x=x, y=y)          # several, uncompressed
np.savez_compressed("arrays.npz", x=x, y=y)
data = np.load("arrays.npz")
data["x"]

np.savetxt("array.csv", arr, delimiter=",", fmt="%.4f")
arr = np.loadtxt("array.csv", delimiter=",")
Importantallow_pickle and untrusted files 
np.load("array.npy", allow_pickle=True)     # can execute arbitrary code
pd.read_pickle("data.pkl")                  # same problem

Both deserialise arbitrary Python objects, which means loading a file from an untrusted source can run code. allow_pickle defaults to False in np.load for exactly this reason — do not turn it on to make an error go away.

For interchange with anyone outside your team, use Parquet or CSV.

Comparing two datasets

The Python equivalent of SAS PROC COMPARE and R’s diffdf. There is no single standard package, so it is worth understanding what a comparison must check.

The built-in tool

from pandas.testing import assert_frame_equal

assert_frame_equal(
    prod, qc,
    check_dtype=False,        # allow int64 vs Int64
    check_like=True,          # ignore column and row order
    rtol=1e-8,
)

assert_frame_equal raises on the first difference and is designed for tests, not for a QC report. For a report you need something that lists every difference.

A PROC COMPARE equivalent

from dataclasses import dataclass, field
import pandas as pd
import numpy as np


@dataclass
class ComparisonResult:
    """Structured output of a dataset comparison."""
    match: bool
    n_base: int
    n_compare: int
    only_in_base: list = field(default_factory=list)
    only_in_compare: list = field(default_factory=list)
    columns_only_in_base: list = field(default_factory=list)
    columns_only_in_compare: list = field(default_factory=list)
    dtype_differences: pd.DataFrame = None
    value_differences: pd.DataFrame = None

    def __str__(self) -> str:
        if self.match:
            return f"MATCH — {self.n_base} rows, all values equal"

        parts = ["DIFFERENCES FOUND", ""]
        if self.n_base != self.n_compare:
            parts.append(f"Row count: base {self.n_base}, compare {self.n_compare}")
        if self.only_in_base:
            parts.append(f"Keys only in base ({len(self.only_in_base)}): "
                         f"{self.only_in_base[:5]}")
        if self.only_in_compare:
            parts.append(f"Keys only in compare ({len(self.only_in_compare)}): "
                         f"{self.only_in_compare[:5]}")
        if self.columns_only_in_base:
            parts.append(f"Columns only in base: {self.columns_only_in_base}")
        if self.columns_only_in_compare:
            parts.append(f"Columns only in compare: {self.columns_only_in_compare}")
        if self.dtype_differences is not None and len(self.dtype_differences):
            parts += ["", "Type differences:",
                      self.dtype_differences.to_string(index=False)]
        if self.value_differences is not None and len(self.value_differences):
            n = len(self.value_differences)
            parts += ["", f"Value differences ({n}):",
                      self.value_differences.head(20).to_string(index=False)]
            if n > 20:
                parts.append(f"... and {n - 20} more")
        return "\n".join(parts)


def compare_datasets(
    base: pd.DataFrame,
    compare: pd.DataFrame,
    keys: list[str],
    tolerance: float = 1e-8,
) -> ComparisonResult:
    """Compare two DataFrames row by row on a key, like SAS PROC COMPARE.

    Args:
        base: The reference dataset (production).
        compare: The dataset being checked (QC).
        keys: Columns that uniquely identify a record in both.
        tolerance: Relative tolerance for float comparison.

    Raises:
        ValueError: If a key column is absent, or keys are not unique.
    """
    for name, df in (("base", base), ("compare", compare)):
        missing = set(keys) - set(df.columns)
        if missing:
            raise ValueError(f"{name} is missing key column(s): {sorted(missing)}")
        dupes = df.duplicated(subset=keys).sum()
        if dupes:
            raise ValueError(
                f"{name} has {dupes} duplicate key(s) on {keys}. "
                "PROC COMPARE semantics require a unique key."
            )

    res = ComparisonResult(match=True, n_base=len(base), n_compare=len(compare))

    # --- Structure ----------------------------------------------------------
    res.columns_only_in_base    = sorted(set(base.columns) - set(compare.columns))
    res.columns_only_in_compare = sorted(set(compare.columns) - set(base.columns))

    b_keys = set(map(tuple, base[keys].astype(str).to_numpy()))
    c_keys = set(map(tuple, compare[keys].astype(str).to_numpy()))
    res.only_in_base    = sorted(b_keys - c_keys)[:100]
    res.only_in_compare = sorted(c_keys - b_keys)[:100]

    common_cols = [c for c in base.columns if c in compare.columns]

    # --- Types --------------------------------------------------------------
    dtypes = pd.DataFrame({
        "column":       common_cols,
        "base_dtype":   [str(base[c].dtype) for c in common_cols],
        "compare_dtype":[str(compare[c].dtype) for c in common_cols],
    })
    res.dtype_differences = dtypes[dtypes["base_dtype"] != dtypes["compare_dtype"]]

    # --- Values, on the intersection of keys --------------------------------
    merged = base[common_cols].merge(
        compare[common_cols], on=keys, suffixes=("_base", "_cmp"), how="inner"
    )

    rows = []
    for col in [c for c in common_cols if c not in keys]:
        b, c = merged[f"{col}_base"], merged[f"{col}_cmp"]

        if pd.api.types.is_numeric_dtype(b) and pd.api.types.is_numeric_dtype(c):
            differs = ~np.isclose(b.astype(float), c.astype(float),
                                  rtol=tolerance, equal_nan=True)
        else:
            differs = ~((b.astype(str) == c.astype(str)) | (b.isna() & c.isna()))

        if differs.any():
            d = merged.loc[differs, keys].copy()
            d["variable"]     = col
            d["base_value"]   = b[differs].values
            d["compare_value"]= c[differs].values
            rows.append(d)

    res.value_differences = (
        pd.concat(rows, ignore_index=True) if rows
        else pd.DataFrame(columns=[*keys, "variable", "base_value", "compare_value"])
    )

    res.match = not (
        res.only_in_base or res.only_in_compare
        or res.columns_only_in_base or res.columns_only_in_compare
        or len(res.dtype_differences) or len(res.value_differences)
    )
    return res

Used:

result = compare_datasets(prod_adsl, qc_adsl, keys=["USUBJID"])
print(result)
DIFFERENCES FOUND

Row count: base 306, compare 305
Keys only in base (1): [('01-718-1427',)]
Columns only in base: ['RANDFL']

Type differences:
 column base_dtype compare_dtype
    AGE     int64       float64

Value differences (15):
      USUBJID variable base_value compare_value
 01-701-1015  TRTDURD        182           183
 01-701-1023  TRTDURD        170           171
 01-703-1096   AGEGR1      65-80           >80

Every difference type from the R validation lesson shows up here, and each means something different — a row-count difference is a population definition disagreement, a systematic TRTDURD off-by-one is a convention difference, and boundary differences in AGEGR1 are an ambiguous specification.

Warningequal_nan=True is deliberate 
np.isclose(np.nan, np.nan)                  # False
np.isclose(np.nan, np.nan, equal_nan=True)  # True

Two datasets that both have a missing value in the same cell agree. Without equal_nan=True, every missing value is reported as a difference and the report becomes useless.

Common mistakes

Mistake Consequence Fix
No encoding= Mojibake on another platform encoding="utf-8"
Letting read_csv guess dtypes IDs lose leading zeros Explicit dtype=
keep_default_na left on "NA" in a text field becomes missing Declare your own sentinels
Trusting Excel types Mixed columns, serial dates dtype=str, convert deliberately
allow_pickle=True on an untrusted file Arbitrary code execution Parquet or CSV
Comparing without equal_nan Every missing value reported equal_nan=True
Comparing on a non-unique key Cartesian blow-up, meaningless report Validate key uniqueness first
Raising the tolerance to hide a difference Real discrepancy suppressed Investigate first

Exercise 7.1 — Flatten a nested API response

An API returns one object per subject, each containing a nested demographics object and a list of visits, each visit containing a list of measurements. Produce one row per measurement, carrying the subject ID, age and visit date.

Show solution 
import pandas as pd

response = {
    "study": "ABC-101",
    "subjects": [
        {
            "usubjid": "001",
            "demographics": {"age": 45, "sex": "F"},
            "visits": [
                {"visit": "Baseline", "date": "2026-03-15",
                 "measurements": [{"test": "SYSBP", "value": 120},
                                  {"test": "DIABP", "value": 80}]},
                {"visit": "Week 4", "date": "2026-04-12",
                 "measurements": [{"test": "SYSBP", "value": 118}]},
            ],
        },
        {
            "usubjid": "002",
            "demographics": {"age": 72, "sex": "M"},
            "visits": [
                {"visit": "Baseline", "date": "2026-03-20",
                 "measurements": [{"test": "SYSBP", "value": 145}]},
            ],
        },
    ],
}

json_normalize flattens one level of list at a time, so a two-level nesting needs two passes:

# Pass 1: one row per visit, carrying subject fields down
visits = pd.json_normalize(
    response["subjects"],
    record_path="visits",
    meta=["usubjid", ["demographics", "age"], ["demographics", "sex"]],
)
visits.columns
#> ['visit', 'date', 'measurements', 'usubjid', 'demographics.age', 'demographics.sex']

# Pass 2: explode the measurements list, then flatten the dicts
long = (
    visits
    .explode("measurements", ignore_index=True)
    .pipe(lambda d: pd.concat(
        [d.drop(columns="measurements").reset_index(drop=True),
         pd.json_normalize(d["measurements"]).reset_index(drop=True)],
        axis=1,
    ))
    .rename(columns={"demographics.age": "age", "demographics.sex": "sex",
                     "date": "visit_date"})
    .assign(visit_date=lambda d: pd.to_datetime(d["visit_date"]))
    .loc[:, ["usubjid", "age", "sex", "visit", "visit_date", "test", "value"]]
)

long
#>   usubjid  age sex     visit visit_date   test  value
#> 0     001   45   F  Baseline 2026-03-15  SYSBP    120
#> 1     001   45   F  Baseline 2026-03-15  DIABP     80
#> 2     001   45   F    Week 4 2026-04-12  SYSBP    118
#> 3     002   72   M  Baseline 2026-03-20  SYSBP    145

Three techniques worth extracting:

  • meta=[["demographics", "age"]] — a list inside meta addresses a nested path. The resulting column is dot-joined, hence the rename afterwards.
  • explode() then json_normalize() — explode turns a list column into one row per element but leaves each element as a dict; json_normalize on that column turns the dicts into columns. Both steps are needed.
  • reset_index(drop=True) before concat(axis=1) — concatenating on the column axis aligns on the index. Without the reset, explode’s duplicated index values misalign the two frames and you silently get NaNs. This is the index-alignment behaviour from lesson 5, biting.

A defensive check, since the whole point is reshaping without losing records:

expected = sum(len(m["measurements"])
               for s in response["subjects"] for m in s["visits"])
assert len(long) == expected, f"expected {expected} rows, got {len(long)}"

Exercise 7.2 — Add a summary report to the comparison

Extend compare_datasets() with a per-variable summary — one row per column giving the number of differences, the percentage, and the largest absolute difference for numeric columns. Explain why that last statistic matters.

Show solution 
def summarise_differences(
    result: ComparisonResult,
    n_compared: int,
) -> pd.DataFrame:
    """One row per variable that differs, with magnitude for numeric columns."""
    vd = result.value_differences
    if vd is None or vd.empty:
        return pd.DataFrame(
            columns=["variable", "n_diff", "pct_diff",
                     "max_abs_diff", "max_rel_diff", "example_base",
                     "example_compare"]
        )

    rows = []
    for var, grp in vd.groupby("variable", sort=False):
        b = pd.to_numeric(grp["base_value"], errors="coerce")
        c = pd.to_numeric(grp["compare_value"], errors="coerce")
        numeric = b.notna().all() and c.notna().all()

        abs_diff = (b - c).abs() if numeric else pd.Series(dtype=float)
        rel_diff = (abs_diff / b.abs().replace(0, np.nan)) if numeric else pd.Series(dtype=float)

        rows.append({
            "variable":        var,
            "n_diff":          len(grp),
            "pct_diff":        round(100 * len(grp) / n_compared, 2),
            "max_abs_diff":    round(abs_diff.max(), 6) if numeric else None,
            "max_rel_diff":    round(rel_diff.max(), 6) if numeric else None,
            "example_base":    grp["base_value"].iloc[0],
            "example_compare": grp["compare_value"].iloc[0],
        })

    return (pd.DataFrame(rows)
            .sort_values("n_diff", ascending=False)
            .reset_index(drop=True))
result = compare_datasets(prod, qc, keys=["USUBJID"])
summary = summarise_differences(result, n_compared=len(prod))
print(summary.to_string(index=False))
 variable  n_diff  pct_diff  max_abs_diff  max_rel_diff example_base example_compare
   AGEGR1      12      3.92          None          None        65-80             >80
  TRTDURD       3      0.98           1.0      0.005495          182             183
     BMIBL       2      0.65      0.000001      0.000000     24.31447        24.31447

Why the magnitude matters

The count alone cannot distinguish three very different situations, and all three appear in the output above:

  1. BMIBL — 2 differences, max absolute 1e-6. Floating-point noise from a different operation order. Not a finding. Had the tolerance been set to 1e-6 rather than 1e-8 these would not have been reported at all, and seeing the magnitude tells you that immediately.

  2. TRTDURD — 3 differences, max absolute exactly 1.0. Every difference is exactly one day. A maximum absolute difference that is a clean round number is the signature of a convention difference, not a calculation error — here, whether treatment duration is inclusive of both endpoints. The magnitude points straight at the cause.

  3. AGEGR1 — 12 differences, no magnitude (categorical). Needs investigation of the values themselves, which is why example_base and example_compare are in the report.

Without magnitude, all three read as “some differences” and a reviewer has to open the detail table for each. With it, you can triage in seconds: dismiss the first, explain the second, investigate the third.

The relative difference column matters when scales vary. An absolute difference of 0.5 is noise on a value of 50,000 and a serious error on a value of 0.6. Reporting both lets the reviewer judge without knowing the units.

One caution. pd.to_numeric(errors="coerce") treats a column as numeric only if every differing value converts. A numeric column where one QC value is the string "NOT DONE" falls back to the categorical branch and reports no magnitude — which is correct behaviour, but worth knowing so an empty max_abs_diff is not read as “no numeric difference”.

Recap

  • Parquet is the default for anything staying inside your pipeline
  • Always specify encoding= and explicit dtype= for IDs
  • json_normalize with record_path and meta flattens one nesting level per pass
  • reset_index(drop=True) before concat(axis=1) or the index misaligns the frames
  • Never allow_pickle=True or read_pickle on a file you did not create
  • A PROC COMPARE equivalent must check structure, keys, dtypes and values separately
  • equal_nan=True — two datasets missing the same cell agree
  • Report the magnitude of differences, not just the count

Next: Reading SAS and clinical datasets.

Back to top