使用json_normalize优化多层带列表结构化字典的DataFrame转换效率

Hey there! I feel your pain—those nested loops with repeated df.loc[len(df)] calls are brutal for large datasets. Every time you add a row that way, pandas has to resize the DataFrame's underlying memory, which piles up massive overhead as your file grows. pd.json_normalize is exactly the tool you need here—it’s built for structured JSON and handles nested arrays efficiently with vectorized operations.

First, Let’s Confirm Your JSON Structure #

Based on your loop code, I’m assuming your JSON looks something like this (adjust if your actual structure varies slightly):

{
  "content": [
    {
      "tag_id": "temperature",
      "data": {
        "score": [
          {"time": "2024-05-01T12:00:00", "value": 22.5},
          {"time": "2024-05-01T13:00:00", "value": 23.1}
        ]
      }
    },
    {
      "tag_id": "humidity",
      "data": {
        "score": [
          {"time": "2024-05-01T12:00:00", "value": 65},
          {"time": "2024-05-01T13:00:00", "value": 63}
        ]
      }
    }
  ]
}

The json_normalize Solution #

Here’s the concise, efficient implementation to get your desired ['tag', 'time', 'score'] DataFrame:

import pandas as pd

# Normalize the JSON, expanding the nested 'score' array while retaining 'tag_id'
df = pd.json_normalize(
    data=my_request['content'],
    record_path=['data', 'score'],  # Path to the array we want to expand into individual rows
    meta=['tag_id']  # Parent field to attach to every expanded row
)

# Rename columns to match your required schema
df = df.rename(columns={
    'tag_id': 'tag',
    'value': 'score'
})

# Reorder columns to your preferred sequence (optional but keeps things clean)
df = df[['tag', 'time', 'score']]

Why This Is Way Faster #

• No loop overhead: json_normalize processes the entire JSON structure in bulk, avoiding the repeated memory reallocations that tank performance with df.loc[len(df)].
• Vectorized operations: Pandas handles the array expansion and column mapping under the hood with optimized C-based operations—these are orders of magnitude faster than Python-level loops for large datasets.

Bonus: Extra Optimizations for Extremely Large Files #

If your JSON file is so big it’s straining memory, consider:

1. Loading the JSON in chunks (using libraries like ijson to stream the file) and processing each chunk with json_normalize.
2. Dropping any unused fields upfront in the json_normalize call to reduce memory footprint.

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