如何在Pandas DataFrame中展平JSON列？

Ah, I see the issue here! The problem is that your 'e' column contains JSON strings, not actual Python dictionaries—so json_normalize can't parse them directly. Let's fix this step by step.

Step 1: Convert JSON Strings to Dictionaries #

First, we need to turn each JSON string in the 'e' column into a Python dict. We can use json.loads() with apply() for this:

import pandas as pd
import json

# Your original DataFrame
df = pd.DataFrame({
    'id': [1, 2, 3],
    'e': ['{"k1":"v1","k2":"v2"}', '{"k1":"v3","k2":"v4"}', '{"k1":"v5","k2":"v6"}']
})

# Convert JSON strings to dicts
df['e'] = df['e'].apply(json.loads)

Step 2: Flatten the Dictionary Column #

Now that 'e' contains dicts, we can use json_normalize to expand it. There are two simple ways to do this:

Method 1: Concat with Original ID Column

Normalize the 'e' column, rename the columns to include the 'e.' prefix, then combine with the original 'id' column:

# Normalize the 'e' column
normalized_e = pd.json_normalize(df['e'])

# Rename columns to match your desired output
normalized_e.columns = [f'e.{col}' for col in normalized_e.columns]

# Combine with the 'id' column
result = pd.concat([df[['id']], normalized_e], axis=1)

Method 2: Use json_normalize with meta Parameter

A more concise approach is to pass the entire DataFrame's records to json_normalize, specifying the path to the nested data and the metadata (the 'id' column):

result = pd.json_normalize(df.to_dict('records'), record_path='e', meta='id')

# Reorder columns and rename to add 'e.' prefix
result = result[['id', 'k1', 'k2']]
result.columns = ['id', 'e.k1', 'e.k2']

Final Result #

Either method will give you the desired DataFrame:

The key mistake was forgetting to convert the JSON strings to dictionaries first—once you fix that, json_normalize works exactly as expected!

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