json-extract-pandas 0.1.13

A powerful Python utility designed to natively flatten, parse, and filter deeply nested JSON data into clean, normalized Pandas DataFrames. It acts as a powerful JSON-to-Pandas extraction engine for robust data pipelines.

JSON Extract (json_extract_pandas)

A powerful Python utility designed to natively flatten, parse, and filter deeply nested JSON data into clean, normalized Pandas DataFrames. It acts as a powerful JSON-to-Pandas extraction engine for robust data pipelines.

Features

• Deep Hierarchical Flattening: Uses recursive logic to safely flatten any nested JSON object, preserving exact path context via dot-notation (e.g. parent.child.property).
• Cartesian List Explosion: Intelligently handles embedded lists/arrays, exploding them into new rows to prevent data loss or complex column bloat.
• Dynamic Filtering: Robust API to slice your exact dataset out of massive payloads.
  • Column Filtering: Strict matching, 1-based numeric indexing (e.g., ["1-7", 10]), Prefix wildcards (parent.*), and Suffix wildcards (*.statusCode).
  • Row Filtering: Filter rows by exact values or lists of acceptable values.
• Intelligent Record Unpacking: Automatically detects and expands nested batches of objects (e.g., [[{obj1}, {obj2}]]) often found in paginated or sharded API responses.
• Column Sorting & Priority Ordering: Specify "pinned" columns to appear first, and keep the rest of the dataset sorted alphabetically.
• Smart Data Cleaning: Natively deduplicate rows, drop NaN/blanks, and cleanly simplify column headers without causing naming collisions.
• Self-Documenting Metadata: Instantly returns schema and dimension metadata (table_size, column_names mapped by 1-based index) alongside the DataFrame.

Requirements

• Python 3.9+
• pandas

---

Installation & Usage

Import extract_json or extract_file directly into your data pipeline script:

Method 1: Using extract_file (Recommended)

Automatically loads and parses .json, .json5, .yaml, or .yml files directly from disk:

from json_extract_pandas import extract_file

meta, df = extract_file('data.yaml')
print(df.head())

Method 2: Using extract_json

Pass a pre-parsed dictionary or list:

import json
from json_extract_pandas import extract_json

# 1. Load your raw payload
with open('data.json', 'r') as f:
    payload = json.load(f)

# 2. Extract!
meta, df = extract_json(payload)
print(df.head())

---

Function Reference

extract_file(filepath, **kwargs)

A convenience wrapper that detects the file extension (.json, .json5, .yaml, .yml), safely parses the file from disk, and pipes the python dictionary directly into extract_json.

extract_json(json_data, **kwargs)

The primary extraction engine.

Parameters

• json_data (dict | list): (Required) The parsed JSON payload to process. It handles normal dictionaries, lists of dictionaries, or even raw primitive 2D arrays ([[0, 1], [2, 3]]) mapping them securely to col1, col2.

• desired_columns (list): (Optional) A list of column names, indices, or wildcards to retain. It natively supports numeric ranges and Unix-style wildcards (*, ?).

  • Numeric Ranges/Indices (1-based): ["1-7", "10", 12] (Gets columns 1 through 7, 10, and 12). Duplicate overlaps are safely ignored.
  • Exact Match: ["accountId"]
  • Prefix Wildcard: ["shippingAddress.*"] (Gets all properties starting with shippingAddress.)
  • Suffix Wildcard: ["*.statusCode"] (Gets every statusCode across the entire document)

• explode_paths (list): (Optional) A list of specific array paths to explode. By default (None), NO nested lists are exploded; instead, they are serialized to strings to safely prevent Out-Of-Memory (OOM) combinatorial explosions on large payloads. Passing an explicit list explodes only those specific arrays into multiple rows via Cartesian product. If a list path is NOT included in explode_paths, the flattener will halt and serialize the entire unexploded list into a string.

  • Exact Match: ["line_items", "tags.code.category"]
  • Prefix Wildcard: ["shippingAddress.*"]

• row_filters (dict): (Optional) A dictionary mapping a specific column to a required value. You can supply a single exact match, or a list of matches.

  • Exact: {"accountId": "ACC-99823-XYZ"}
  • List: {"regionCode": ["US-EAST", "EU-WEST"]}

• remove_duplicates (bool): (Optional, Default: False) If True, drops any completely identical rows generated from Cartesian explosions after all filters are applied.

• simplify_columns (bool): (Optional, Default: False) If True, it trims away verbose parent hierarchies in the column names, leaving only the final child key (e.g., parent.child.shortName safely becomes shortName). Note: If simplifying causes a name collision (e.g., type.code and name.code both mapping to code), it intelligently retains the full dot-notation for those specific columns to prevent data overwrite.

• remove_empty (bool | str): (Optional, Default: False) Cleans the dataset by dropping rows polluted with missing values (NaN, None) or blank strings ("").

  • 'any' (or True): Strict. Drops the row if any of the filtered columns are missing.
  • 'all': Lenient. Drops the row only if all of the filtered columns are entirely missing.
  • False: Disabled. Retains everything.

• sort_columns (bool): (Optional, Default: False) If True, sorts the columns alphabetically. If used with keep_all_columns=True, it sorts only the "remaining" columns that were not explicitly pinned via desired_columns.

• keep_all_columns (bool): (Optional, Default: False) Controls the behavior of the desired_columns list.

  • False (default): desired_columns acts as a strict filter. Only the matched columns are returned.
  • True: desired_columns acts as a Priority Order. Matched columns appear first in your specified order, followed by all other columns found in the dataset.

---

Full Example Pipeline

Here is an example demonstrating all parameters functioning in tandem to slice a massive, nested payload down to an exact, clean specification:

1. Sample Data (users_export.json)

[
  {
    "accountId": "ACC-123",
    "regionCode": "US-EAST",
    "shippingAddress": {
      "city": "New York",
      "zipCode": "10001"
    },
    "history": [
      {"orderId": "A1", "statusCode": "DELIVERED"},
      {"orderId": "A2", "statusCode": "PENDING"}
    ]
  },
  {
    "accountId": "ACC-999",
    "regionCode": "EU-WEST",
    "shippingAddress": {
      "city": "London",
      "zipCode": "E1 6AN"
    },
    "history": [
      {"orderId": "B1", "statusCode": "SHIPPED"}
    ]
  }
]

2. Python Script

import json
from json_extract_pandas import extract_json

with open('users_export.json', 'r') as f:
    data = json.load(f)

# Extract only the fields we care about, exactly for our target accounts
meta, df = extract_json(
    json_data=data,
    
    # 1. Grab Account ID, specific columns by index, and shipping address properties
    desired_columns=[
        "accountId", 
        "2-4",
        "shippingAddress.*",
        "*.statusCode"
    ],
    
    # 2. Filter to just these two specific regions
    row_filters={
        "regionCode": ["US-EAST", "EU-WEST"]
    },
    
    # 3. Clean up the resulting DataFrame
    remove_duplicates=True,
    simplify_columns=True,  # Turns "shippingAddress.zipCode" into "zipCode"
    remove_empty='all'      # Drop rows that are totally blank
)

print(df.to_csv(index=False))

---

Advanced Feature Highlights

1. Intelligent Record Unpacking

Often, enterprise APIs return data in nested lists:

[
  [{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}],
  [{"id": 3, "name": "Charlie"}]
]

json_extract_pandas detects this "Batch" structure automatically. Instead of creating a single row with col1, col2 prefixes, it "unpacks" the inner lists into 3 separate rows with standard id and name columns.

2. Priority Column Sorting

If you have a dataset with 200 columns but you always want the EmployeeID and Email at the very beginning (left-most side) and the rest of the columns sorted alphabetically:

meta, df = extract_json(
    data,
    desired_columns=["EmployeeID", "Email"],
    keep_all_columns=True, # Don't drop the other 198 columns
    sort_columns=True      # Sort the remaining 198 columns alphabetically
)

This ensures your resulting CSV or DataFrame is human-readable and consistently ordered.

3. Preventing Combinatorial Explosions (The Golden Rule)

When extracting highly nested JSON objects with multiple independent arrays, an unrestrained Cartesian product will cause an Out of Memory (OOM) crash.

Consider a deeply nested payload where multiple independent sibling lists exist:

{
  "orderId": "ORD-123",
  "addresses": [{"type": "shipping"}, {"type": "billing"}],
  "line_items": [{"sku": "L1"}, {"sku": "L2"}, {"sku": "L3"}],
  "tags": [
    {
      "code": {
        "category": ["A", "B"]
      },
      "value": ["v1", "v2"]
    }
  ]
}

If we exploded all of these arrays simultaneously, the engine performs a Cartesian product. If a single order contained 10 tags, 5 addresses, and 20 line items, exploding all of them generates 10 * 5 * 20 = 1,000 rows for just one record. If your API returns a page of 500 records, you suddenly have 500,000 rows held in memory.

To safely parse enterprise payloads, use explode_paths to tell the engine exactly which lists to explode into new rows, and which to serialize as simple strings.

meta, df = extract_json(
    json_data=payload,
    explode_paths=["line_items"] # <--- ONLY explode line items
)

In this scenario, the entire tags and addresses arrays will simply be stringified into single cells. The engine will result in exactly 3 rows (since line_items has 3 items) rather than multiplying out of control!

The Golden Rule of Ancestor Explosion: To explode a deeply nested array (like tags.code.category), the engine must also explode its parent arrays (tags). You do not need to manage this manually! json_extract_pandas features automatic Ancestor Explosion.

If you simply pass explode_paths=["tags.code.category"], the engine automatically recognizes that it must blow open "tags" to reach the requested child. It will explode the requested category list, while safely stringifying unrequested sibling lists like tags.value and line_items!

4. Safe Null and Empty Array Handling

When dealing with combinatorial explosions, missing data can be dangerous if not handled properly. The extraction engine handles None, NaN, and empty arrays ([]) gracefully to ensure your rows never accidentally disappear during a Cartesian Product:

• Explicit Nulls: If a key has a value of None or NaN, the engine treats it as a standard primitive. It safely carries the None value across all exploded rows during multiplication.
• Empty Arrays: If a nested list is entirely empty ({"line_items": []}), the engine intercepts it and converts it into a None primitive. Instead of multiplying by zero (which would erase the entire row), it multiplies by 1, safely propagating the None across the Cartesian Product.
• Missing Keys: If a key exists in one record but is completely missing in another, the engine naturally misaligns the schemas. When constructing the final DataFrame, it automatically backfills the missing keys with NaN (or None), ensuring perfect structural integrity.