Python嵌套JSON批量归一化至多DataFrame及报错排查

Hey there! Let's break down how to fix that KeyError: 'record_id' and build a robust automated pipeline for your nested JSON enterprise records.

First, that error usually pops up because some of your records are missing the record_id field, or you're trying to access it in a spot where it doesn't exist during your loop. Let's tackle this step by step:

1. Fix the record_id KeyError First #

Before diving into normalization, make sure every record has a valid record_id (even if you have to generate a fallback for missing ones). This ensures your data stays linked across tables later.

import pandas as pd
import json

# Load your JSON data
with open('your_enterprise_records.json', 'r') as f:
    raw_data = json.load(f)

# Ensure every record has a record_id (generate defaults if missing)
for idx, record in enumerate(raw_data):
    # Use existing record_id if present, else create a unique fallback
    record.setdefault('record_id', f"fallback_id_{idx}")

The setdefault() method safely adds a default value only if the key doesn't exist—no more KeyErrors when accessing record_id later.

2. Build a Layered Normalization Pipeline #

Since you have nested data like transactionDetail, split your data into main table (top-level enterprise info) and child tables (nested details) linked by record_id. This keeps your data organized, even with hundreds of columns.

Step 2.1: Create the Main Enterprise DataFrame #

Extract all top-level fields, excluding nested sections (like transactionDetail):

# Extract top-level fields (skip nested ones)
main_records = [
    {key: value for key, value in rec.items() if key != 'transactionDetail'}
    for rec in raw_data
]

main_df = pd.DataFrame(main_records)

Step 2.2: Create the Transaction Detail DataFrame #

For nested sections like transactionDetail, flatten them while keeping the record_id to link back to the main table. We'll handle cases where some records don't have transaction data to avoid errors:

transaction_records = []

for rec in raw_data:
    # Only process if transactionDetail exists and isn't empty
    if 'transactionDetail' in rec and rec['transactionDetail']:
        for trans in rec['transactionDetail']:
            # Attach the parent record's ID to each transaction
            trans['record_id'] = rec['record_id']
            transaction_records.append(trans)

# Convert to DataFrame—this automatically handles all columns in transactionDetail
transaction_df = pd.DataFrame(transaction_records)

Alternatively, if you prefer using json_normalize (great for more complex nested structures), you can add a safety check first:

# Filter records that have transactionDetail to avoid json_normalize errors
valid_transaction_records = [rec for rec in raw_data if 'transactionDetail' in rec and rec['transactionDetail']]

transaction_df = pd.json_normalize(
    valid_transaction_records,
    record_path='transactionDetail',
    meta=['record_id']  # Bring in the parent ID for linking
)

3. Optimize for Large Datasets #

With thousands of records, avoid modifying DataFrames inside loops (it's slow!). Instead:

• Collect all records in lists first, then convert to DataFrames in one go (like we did above)
• Use list comprehensions instead of for loops where possible—they're faster in Python
• If you have multiple nested sections (not just transactionDetail), wrap the child table logic in a function to reuse code:

def flatten_nested_field(raw_data, nested_field_name, primary_key='record_id'):
    nested_records = []
    for rec in raw_data:
        if nested_field_name in rec and rec[nested_field_name]:
            for item in rec[nested_field_name]:
                item[primary_key] = rec[primary_key]
                nested_records.append(item)
    return pd.DataFrame(nested_records)

# Use the function for any nested field
transaction_df = flatten_nested_field(raw_data, 'transactionDetail')
# Example: if you have another nested field like 'contactInfo'
contact_df = flatten_nested_field(raw_data, 'contactInfo')

4. Handle Hundreds of Columns Automatically #

Both pd.DataFrame() and json_normalize will automatically detect all columns in your JSON data—no need to manually specify each one. Even if your flattened data has 500 columns, pandas will handle it seamlessly. Just make sure your JSON is consistent (or handle inconsistent fields with pd.json_normalize(..., errors='ignore') if needed).

Final Notes #

• Always validate your data first: run a quick check for missing record_id values with main_df['record_id'].isna().sum()
• For very large files (100k+ records), consider processing in chunks instead of loading the entire JSON into memory
• Use record_id to join your main and child tables whenever you need to analyze related data together

内容的提问来源于stack exchange，提问作者Raul Gonzales