Skip to content
Home
data-science

From Chaos to Clarity: Engineering a Resilient JSON‑to‑Tabular Pipeline for Messy API Data

Mausam

When I first tried to feed Random User data into a churn model, the pipeline crashed on the third request because the API suddenly added a new “login” field and omitted “registered.age” for a handful of users. If you’ve ever stared at a stack trace that blames “KeyError: 'registered.age'” while the data team is waiting for yesterday’s report, you know the pain. This guide is for data engineers and scientists who already know how to call an API but need a production‑grade layer that tolerates schema drift, coerces types, and fills gaps without breaking downstream jobs. By the end you’ll have a reusable script that fetches 100 random users, normalizes the nested JSON, applies robust type coercion, and writes a validated CSV plus a quick age distribution plot.

Key Takeaways

  • Use pandas.json_normalize as a first pass, then enforce a strict schema to catch hidden inconsistencies.
  • Centralize type coercion and imputation so that a single schema definition protects every downstream consumer.
  • Wrap network and parsing logic in explicit try/except blocks; surface a concise error report instead of a cryptic traceback.
  • Persist both the cleaned table and a lightweight diagnostic plot to make failures visible at a glance.
  • In production, version the schema alongside the pipeline code to make future API changes a controlled migration.

The Problem

External APIs rarely guarantee a stable contract. Random User returns a deeply nested JSON where fields can appear, disappear, or change type between calls. A naïve flatten‑and‑store approach works for a quick demo but shatters when the data model evolves. The challenge is to create a resilient ingestion layer that (1) normalizes arbitrary nesting, (2) validates and coerces each column to an expected type, (3) imputes sensible defaults for missing values, and (4) reports problems in a way that operations can act on without manual log digging.

Data and Sources

We pull live data from the Random User API (https://randomuser.me/api/). The endpoint supports a results parameter to batch requests; we request 100 users in a single call. Data accessed on 2026-10-31.

Step 1 — The Wild West: Unpacking Inconsistent API Payloads

The first obstacle is that the JSON payload is not a flat table. Some records miss registered.age, others contain an extra login.sha256 field that we don’t need. We start by fetching the raw payload and inspecting a single record.

import requests, json

def fetch_raw(num=100):
    try:
        resp = requests.get(
            "https://randomuser.me/api/",
            params={"results": num, "inc": "gender,name,location,email,dob,registered,phone"},
            timeout=10,
        )
        resp.raise_for_status()
        return resp.json()
    except requests.exceptions.RequestException as e:
        raise RuntimeError(f"Network error while fetching Random User data: {e}")

This function isolates network errors (timeouts, DNS failures) and surfaces a clean exception that our pipeline can catch.

Step 2 — Flattening the Hierarchy: Initial Normalization with json_normalize

Once we have the raw dict, pandas.json_normalize expands nested keys into dotted column names. It also inserts NaN for missing paths, which is exactly what we need for later imputation.

import pandas as pd

def normalize(raw_json):
    # The user objects live under ['results']
    users = raw_json.get("results", [])
    df = pd.json_normalize(users, sep=".")
    return df

The resulting DataFrame contains columns like name.first, location.city, and dob.age. At this stage the schema is still fluid; we’ll lock it down in the next step.

Step 3 — Engineering Robust Type Coercion and Imputation

We define a canonical schema that maps each column to a target dtype and a default fill value. The schema lives in a single dictionary so that any future change is a one‑line edit.

SCHEMA = {
    "gender":          {"dtype": "category", "default": "unknown"},
    "name.title":      {"dtype": "string",   "default": ""},
    "name.first":      {"dtype": "string",   "default": ""},
    "name.last":       {"dtype": "string",   "default": ""},
    "location.city":   {"dtype": "string",   "default": ""},
    "location.state":  {"dtype": "string",   "default": ""},
    "location.country":{"dtype": "string",   "default": ""},
    "email":           {"dtype": "string",   "default": ""},
    "dob.age":         {"dtype": "Int64",    "default": -1},
    "registered.age": {"dtype": "Int64",    "default": -1},
    "phone":           {"dtype": "string",   "default": ""},
}

Now we coerce each column, filling missing values with the prescribed defaults. Pandas’ nullable integer type Int64 lets us keep NaN until we replace it.

def coerce_and_impute(df):
    for col, meta in SCHEMA.items():
        if col not in df.columns:
            # Add missing column with default values
            df[col] = meta["default"]
        # Coerce type
        try:
            df[col] = df[col].astype(meta["dtype"])
        except Exception as e:
            raise RuntimeError(f"Failed to cast column '{col}' to {meta['dtype']}: {e}")
        # Impute defaults for NaN
        if pd.api.types.is_numeric_dtype(df[col]):
            df[col] = df[col].fillna(meta["default"])
        else:
            df[col] = df[col].fillna(meta["default"])
    # Keep only the columns we declared
    return df[list(SCHEMA.keys())]

If a column cannot be cast (e.g., a stray string in dob.age), the pipeline raises a clear error instead of silently corrupting data.

Step 4 — Schema‑Aware Transformations for Production Resilience

With a clean DataFrame we can add downstream‑ready artifacts: a CSV for analytics and a quick histogram of ages to surface distribution shifts. Both outputs are written inside a try/except block that catches I/O errors.

import matplotlib.pyplot as plt
import os

def persist(df, out_dir="output"):
    os.makedirs(out_dir, exist_ok=True)
    csv_path = os.path.join(out_dir, "clean_users.csv")
    plot_path = os.path.join(out_dir, "age_distribution.png")
    try:
        df.to_csv(csv_path, index=False)
    except OSError as e:
        raise RuntimeError(f"Failed to write CSV to {csv_path}: {e}")

    # Simple diagnostic plot
    plt.figure(figsize=(6,4))
    df["dob.age"].hist(bins=range(0, 100, 5), edgecolor="black")
    plt.title("Age Distribution of Random Users")
    plt.xlabel("Age")
    plt.ylabel("Count")
    plt.tight_layout()
    try:
        plt.savefig(plot_path)
        plt.close()
    except Exception as e:
        raise RuntimeError(f"Failed to save plot to {plot_path}: {e}")

    return csv_path, plot_path

Putting It All Together

The orchestrator wires the pieces, handling any exception at the top level so that a CI job can surface a concise failure message.

def main():
    try:
        raw = fetch_raw(num=100)
        flat = normalize(raw)
        clean = coerce_and_impute(flat)
        csv_path, plot_path = persist(clean)
        print(f"✅ Clean data written to {csv_path}")
        print(f"✅ Age plot saved to {plot_path}")
    except RuntimeError as err:
        print(f"❌ Pipeline aborted: {err}")
        exit(1)

if __name__ == "__main__":
    main()

Complete Script

The full runnable script combining all steps:

#!/usr/bin/env python3
"""
Resilient JSON‑to‑Tabular pipeline for Random User API.
Fetches 100 users, normalizes nested JSON, enforces a strict schema,
imputes missing values, and persists a CSV + age histogram.
"""

import os
import requests
import pandas as pd
import matplotlib.pyplot as plt

# ----------------------------------------------------------------------
# Configuration: schema definition (single source of truth)
# ----------------------------------------------------------------------
SCHEMA = {
    "gender":           {"dtype": "category", "default": "unknown"},
    "name.title":       {"dtype": "string",   "default": ""},
    "name.first":       {"dtype": "string",   "default": ""},
    "name.last":        {"dtype": "string",   "default": ""},
    "location.city":    {"dtype": "string",   "default": ""},
    "location.state":   {"dtype": "string",   "default": ""},
    "location.country": {"dtype": "string",   "default": ""},
    "email":            {"dtype": "string",   "default": ""},
    "dob.age":          {"dtype": "Int64",    "default": -1},
    "registered.age":  {"dtype": "Int64",    "default": -1},
    "phone":            {"dtype": "string",   "default": ""},
}

# ----------------------------------------------------------------------
# Step 1: Fetch raw JSON, with network‑level error handling
# ----------------------------------------------------------------------
def fetch_raw(num: int = 100) -> dict:
    try:
        resp = requests.get(
            "https://randomuser.me/api/",
            params={"results": num, "inc": "gender,name,location,email,dob,registered,phone"},
            timeout=10,
        )
        resp.raise_for_status()
        return resp.json()
    except requests.exceptions.RequestException as e:
        raise RuntimeError(f"Network error while fetching Random User data: {e}")

# ----------------------------------------------------------------------
# Step 2: Normalize nested structures into a flat DataFrame
# ----------------------------------------------------------------------
def normalize(raw_json: dict) -> pd.DataFrame:
    users = raw_json.get("results", [])
    df = pd.json_normalize(users, sep=".")
    return df

# ----------------------------------------------------------------------
# Step 3: Coerce types and impute missing values according to SCHEMA
# ----------------------------------------------------------------------
def coerce_and_impute(df: pd.DataFrame) -> pd.DataFrame:
    for col, meta in SCHEMA.items():
        if col not in df.columns:
            df[col] = meta["default"]
        try:
            df[col] = df[col].astype(meta["dtype"])
        except Exception as e:
            raise RuntimeError(f"Failed to cast column '{col}' to {meta['dtype']}: {e}")

        # Impute defaults
        if pd.api.types.is_numeric_dtype(df[col]):
            df[col] = df[col].fillna(meta["default"])
        else:
            df[col] = df[col].fillna(meta["default"])
    return df[list(SCHEMA.keys())]

# ----------------------------------------------------------------------
# Step 4: Persist CSV and a diagnostic plot, with I/O error handling
# ----------------------------------------------------------------------
def persist(df: pd.DataFrame, out_dir: str = "output"):
    os.makedirs(out_dir, exist_ok=True)
    csv_path = os.path.join(out_dir, "clean_users.csv")
    plot_path = os.path.join(out_dir, "age_distribution.png")
    try:
        df.to_csv(csv_path, index=False)
    except OSError as e:
        raise RuntimeError(f"Failed to write CSV to {csv_path}: {e}")

    plt.figure(figsize=(6, 4))
    df["dob.age"].hist(bins=range(0, 100, 5), edgecolor="black")
    plt.title("Age Distribution of Random Users")
    plt.xlabel("Age")
    plt.ylabel("Count")
    plt.tight_layout()
    try:
        plt.savefig(plot_path)
        plt.close()
    except Exception as e:
        raise RuntimeError(f"Failed to save plot to {plot_path}: {e}")

    return csv_path, plot_path

# ----------------------------------------------------------------------
# Orchestrator
# ----------------------------------------------------------------------
def main():
    try:
        raw = fetch_raw(num=100)
        flat = normalize(raw)
        clean = coerce_and_impute(flat)
        csv_path, plot_path = persist(clean)
        print(f"✅ Clean data written to {csv_path}")
        print(f"✅ Age plot saved to {plot_path}")
    except RuntimeError as err:
        print(f"❌ Pipeline aborted: {err}")
        exit(1)

if __name__ == "__main__":
    main()

Expected Output

Running python3 json_to_tabular.py (or the script name you saved) should print two success lines and create an output/ folder containing:

  • clean_users.csv – 100 rows, 13 columns, all types validated.
  • age_distribution.png – a histogram showing the age spread of the sampled users.

Limitations and Tradeoffs

This approach assumes that the API returns a reasonably sized batch (≤ 500 records) that fits in memory. For high‑throughput streams you’d replace the in‑memory DataFrame with chunked processing (e.g., Dask or Polars). The schema is hard‑coded; a truly dynamic environment might benefit from a JSON‑Schema validator that can auto‑generate the SCHEMA dict. Finally, the imputation strategy (simple defaults) works for exploratory analysis but may be insufficient for production ML models where domain‑specific missing‑value handling is required.

Frequently Asked Questions

Why not use a library like pandera for schema validation?

Pandera offers expressive checks, but adding another dependency complicates deployment in constrained environments. Our hand‑rolled schema keeps the pipeline lightweight and fully transparent.

How can I extend this to handle pagination?

Wrap fetch_raw in a loop that respects the API’s page parameter, aggregating results into a list before calling normalize. Remember to respect rate limits.

What if the API adds a new nested object I care about?

Update SCHEMA with the dotted path (e.g., login.sha256) and add the desired dtype/default. The rest of the pipeline automatically picks it up.

Can I store the output in a data lake instead of a CSV?

Yes. Replace the df.to_csv call with a Parquet writer (df.to_parquet) or an S3 upload using boto3. The type‑coercion step remains unchanged.

Is the age histogram useful for monitoring data quality?

Absolutely. Sudden shifts in the age distribution often signal upstream API changes (e.g., a new demographic filter). Automate the plot generation in a CI job and alert on statistical anomalies.

What I'd Change

If I were to ship this at scale, I’d extract the schema into a version‑controlled JSON‑Schema file and generate the coercion logic automatically. Coupled with a lightweight schema‑registry service, any API change would become a controlled migration rather than a hard‑stop. Additionally, I’d replace the single‑threaded requests call with httpx.AsyncClient to parallelise large batches, cutting fetch time from seconds to sub‑second for thousands of records. Those upgrades turn a solid prototype into a truly production‑ready ingestion service.

Post a Comment

Hi! How can we help you? Send us a message and we'll get back to you.