如何在Python中展开Twitter Ads API的复杂嵌套列（字典嵌套字典的列表）

Hey there! Let's work through this nested DataFrame column problem—those layered JSON structures can feel overwhelming when you're starting out with Python and pandas, but we'll break it down into simple, actionable steps.

First, let's ground this with a concrete example. I’ll assume your DataFrame has a column (let’s call it nested_metrics) filled with JSON that matches your list→dict→dict structure, plus optional nested lists:

# Sample DataFrame
import pandas as pd
data = {
    "user_id": [1, 2],
    "nested_metrics": [
        '[{"app_clicks": {"value": 20}, "card_engagement": {"value": 15}, "carousel_swipes": [{"count": 5}, {"count": 3}]}]',
        '[{"app_clicks": {"value": 10}, "card_engagement": {"value": 8}, "carousel_swipes": [{"count": 2}]}]'
    ]
}
df = pd.DataFrame(data)

Step 1: Parse JSON strings into Python objects #

If your nested column is stored as a string (super common!), first convert it into native Python lists/dicts using json.loads:

import json

df['nested_metrics'] = df['nested_metrics'].apply(json.loads)

Step 2: Extract the inner dictionary from the outer list #

Since your structure starts with a list wrapping a dictionary, we’ll pull out that inner dict (assuming each list has one main metrics object—adjust if your lists have multiple entries):

# Grab the first (and only) dict from the list; handle edge cases where it might not be a list
df['metrics_dict'] = df['nested_metrics'].apply(lambda x: x[0] if isinstance(x, list) else x)

Step 3: Expand nested dictionaries into separate columns #

Use pandas’ json_normalize to flatten the dict→dict structure into individual columns. This will automatically handle keys like app_clicks and their nested value fields:

# Flatten the nested metrics dict
normalized_metrics = pd.json_normalize(df['metrics_dict'])

# Merge the flattened columns back with your original DataFrame (drop intermediate columns)
final_df = pd.concat([df.drop(['nested_metrics', 'metrics_dict'], axis=1), normalized_metrics], axis=1)

Step 4: Clean up column names (optional) #

You’ll notice columns like app_clicks.value—let’s rename them to something cleaner:

final_df.rename(columns=lambda col: col.replace('.value', ''), inplace=True)

Step 5: Handle nested lists (like carousel_swipes) #

If some metrics are lists (e.g., carousel_swipes), you have two practical options:

1. Convert lists to strings if you just want to keep the data intact without expanding:

   final_df['carousel_swipes'] = final_df['carousel_swipes'].apply(str)
   

2. Explode lists into multiple rows if you want to analyze each list entry separately:

   # Explode the list into individual rows
   exploded_df = final_df.explode('carousel_swipes')
   # Flatten the dict inside each carousel swipe entry
   exploded_df = pd.concat([exploded_df.drop('carousel_swipes', axis=1), pd.json_normalize(exploded_df['carousel_swipes'])], axis=1)
   

After these steps, you’ll have separate columns for app_clicks, card_engagement, and either consolidated or exploded carousel_swipes data—exactly what you wanted!

If your actual JSON structure has slight variations (like multiple dicts in the outer list), just tweak the lambda function in Step 2 (e.g., use pd.Series(x).explode() to expand the list first before normalizing).

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