eSIM Germany Data Plans JSON Structure Analysis

# eSimDB Germany Data-Plans — JSON Structure EDA **Goal:** determine the structure of the eSimDB `data-plans` API response — its fields, types, nesting, coverage, and value distributions — *without* ever dumping the full payload into view. **Endpoint:** `https://esimdb.com/api/client/countries/germany/data-plans` **Key probe finding:** despite appearing as a "~600 KB zip", the endpoint actually serves **plain JSON** (`content-type: application/json`). The ~600 KB is only the **gzipped transfer size**; the decompressed payload is **~11 MB** (`content-length: 11059843`). We therefore stream it to disk once and profile it programmatically. **Notebook flow:** fetch & cache → top-level map → recursive schema inference → entity deep-profile (plans & providers) → coverage/distribution charts → typed-schema summary with cleaning caveats.

import os, json, time
import requests

URL = "https://esimdb.com/api/client/countries/germany/data-plans"
RAW = "data-plans.raw"   # cached copy under /home/user (auto-persisted to outputs)

# --- 1. Stream to disk only if we don't already have a fresh cache ---
if os.path.exists(RAW) and os.path.getsize(RAW) > 1_000_000:
    print(f"Reusing cached payload: {RAW} ({os.path.getsize(RAW):,} bytes)")
else:
    t0 = time.time()
    with requests.get(URL, stream=True, timeout=180) as r:
        r.raise_for_status()
        print("content-type:", r.headers.get("content-type"))
        with open(RAW, "wb") as fh:
            for chunk in r.iter_content(chunk_size=1 << 20):
                fh.write(chunk)
    print(f"Downloaded {os.path.getsize(RAW):,} bytes in {time.time()-t0:.1f}s")
…

Reusing cached payload: data-plans.raw (11,059,843 bytes)
on-disk size: 11,059,843 bytes
top-level Python type: dict
top-level keys: ['totalPlans', 'totalProvider', 'numberOfPages', 'featured', 'plans', 'providers', 'reviews', 'banners', 'latestUpdate', 'topCountries']

import pandas as pd

def describe_top(v):
    """Return (type, length-or-value, preview) without dumping big containers."""
    t = type(v).__name__
    if isinstance(v, list):
        item_t = type(v[0]).__name__ if v else "—"
        return t, len(v), f"list[{item_t}]"
    if isinstance(v, dict):
        return t, len(v), "keys: " + ", ".join(list(v.keys())[:8])
    return t, "—", repr(v)

rows = []
for k, v in data.items():
    t, size, preview = describe_top(v)
    rows.append({"key": k, "type": t, "length": size, "preview": preview})

top_df = pd.DataFrame(rows)
…

Top-level object: 10 keys

import re, pandas as pd
from collections import Counter

# ---------- recursive JSON schema walker ----------
OID = re.compile(r"^[0-9a-fA-F]{24}$")          # MongoDB ObjectId (provider ids)

def jtype(v):
    if v is None:           return "null"
    if isinstance(v, bool): return "bool"        # bool BEFORE int
    if isinstance(v, int):  return "int"
    if isinstance(v, float):return "float"
    if isinstance(v, str):  return "str"
    if isinstance(v, list): return "array"
    if isinstance(v, dict): return "object"
    return type(v).__name__

acc           = {}                # path -> stats
obj_instances = Counter()         # path -> # object instances at this path
…

Inferred schema: 313 merged field paths [plans=13461 items; collapsed id-maps: providers{}(186 keys)]
Saved -> schema.csv

import re, numpy as np, pandas as pd
from collections import Counter

plans     = data["plans"]
providers = data["providers"]            # dict: provider_id -> provider object
N = len(plans)
print(f"PLANS  n={N:,}   |   PROVIDERS  n={len(providers)}\n")

# ---------- A. plan field inventory (universal vs optional) ----------
key_count = Counter()
for p in plans:
    key_count.update(p.keys())
inv = pd.DataFrame({"plan_field": list(key_count),
                    "present": list(key_count.values())})
inv["coverage_%"] = (100 * inv["present"] / N).round(1)
inv = inv.sort_values("coverage_%", ascending=False).reset_index(drop=True)
print("== Plan field inventory (first-level) ==")
display(inv)
…

PLANS  n=13,461   |   PROVIDERS  n=186

== Plan field inventory (first-level) ==

== Plan field schema (merged across all 13,461 plans) ==

== Numeric highlights ==
 capacity (MB?): n=13,461  min=0  median=3000  mean=13880  max=750000
   period (days?): n=13,461  min=0  median=15  mean=30  max=730
period value counts (top 8): {30: 4255, 7: 1640, 15: 1414, 1: 1092, 5: 908, 3: 891, 10: 864, 20: 532}

== prices (currency-keyed object) ==
currency coverage: {'USD': '11,075 (82%)', 'EUR': '2,765 (21%…

import numpy as np, pandas as pd
import matplotlib.pyplot as plt
import plotly.graph_objects as go
from plotly.subplots import make_subplots
from collections import Counter

plt.rcParams.update({"figure.facecolor":"white","axes.facecolor":"white",
                     "axes.edgecolor":"#444","font.size":11})
NAVY="#2b5c8a"; ORANGE="#e08a1e"; GREY="#9aa6b2"; RED="#c0392b"

# ===== Figure 1: plan field effective-missingness (cleaning priority) =====
ps = plan_schema.copy()
ps["eff_missing_%"] = (100 - ps["coverage_%"] * (1 - ps["null_%"]/100)).round(1)
ps = ps.sort_values("eff_missing_%")
colors = [RED if m>=50 else (ORANGE if m>=10 else NAVY) for m in ps["eff_missing_%"]]
fig, ax = plt.subplots(figsize=(9, 8))
ax.barh(ps["field"], ps["eff_missing_%"], color=colors)
ax.set_xlabel("Effective missing / absent rate (%)  —  field absent OR null")
…

Saved to outputs (downloadable from the file manager):
  • plan_field_nullrate.png (110808 bytes)

## Findings — inferred JSON structure The response is a **single JSON object** (≈11 MB) with a metadata envelope plus six collections. ### Top-level envelope | key | type | meaning | |---|---|---| | `totalPlans` | int | 13,461 | | `totalProvider` | int | 186 | | `numberOfPages` | int | 43 | | `latestUpdate` | str (ISO-8601) | e.g. `2026-07-26T19:43:45.460Z` | | `featured` | array[5] of plan objects | curated subset | | `plans` | **array[13,461]** of plan objects | the main entity | | `providers` | **object keyed by provider `_id`** (186) | id → provider object | | `reviews` | object `{count}` | review summary | | `banners` | object keyed by size (`5`,`25`) → array of banner objects | promo banners | | `topCountries` | array[8] `{name, slug, region, subregions[], plans, popularity, providerNumber, image}` | related destinations | ### `plans[]` — typed schema (33 first-level fields) ``` _id str MongoDB ObjectId (24-hex), unique name str provider str FK → providers._id (100% referential integrity) capacity int MB; 0–750,000; median 3,000; 0 = "unlimited" (182 plans) period int days; 0–730; modal 30; 0 = "lifetime" (114 plans) prices object{currency: number} 49 currencies; USD on 82%, EUR 21%, GBP 12% usdPrice float normalized USD price; 0–5,058.55; median 16.60 (7 free) usdPromoPrice float|null 48% null promoPrices object (always present) promo: object (only 4.2% of plans) # always-present booleans has5…