Python中如何展开多列类JSON格式单元格，pd.json_normalize无效怎么办

不同列的JSON结构不一致（有单字典、列表嵌套字典、空值三种情况），直接调用pd.json_normalize()无法适配多种结构，按列分批次处理是通用性最高的方案：

第一步：修正测试数据构造代码 #

原给出的df构造代码包含多余引号，修正后可直接运行：

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'authors': pd.Series([
        [{'name': '/u/Ashamed_Boss2510', 'href': 'https://www.reddit.com/user/Ashamed_Boss2510'}], 
        [{'name': '/u/lazywizard99', 'href': 'https://www.reddit.com/user/lazywizard99'}], 
        [{'name': '/u/Guy_PCS', 'href': 'https://www.reddit.com/user/Guy_PCS'}]
    ], dtype='object', index=pd.RangeIndex(start=1, stop=4, step=1)), 
    'author_detail': pd.Series([
        {'name': '/u/Ashamed_Boss2510', 'href': 'https://www.reddit.com/user/Ashamed_Boss2510'}, 
        {'name': '/u/lazywizard99', 'href': 'https://www.reddit.com/user/lazywizard99'}, 
        {'name': '/u/Guy_PCS', 'href': 'https://www.reddit.com/user/Guy_PCS'}
    ], dtype='object', index=pd.RangeIndex(start=1, stop=4, step=1)), 
    'title_detail': pd.Series([
        {'type': 'text/plain', 'language': None, 'base': 'https://www.reddit.com/r/StockMarket/.rss', 'value': 'Always have an exit strategy!'}, 
        {'type': 'text/plain', 'language': None, 'base': 'https://www.reddit.com/r/StockMarket/.rss', 'value': 'Nikola reports yet another loss with $0 revenue'}, 
        {'type': 'text/plain', 'language': None, 'base': 'https://www.reddit.com/r/StockMarket/.rss', 'value': 'This crypto scam bilked investors out of $11 million — and paid action star Steven Seagal to promote it. What was left unsaid was that Seagal was being paid to be a spokesman, with the promise of $250,000 plus $750,000 in B2G coin for his time, the SEC said.'}
    ], dtype='object', index=pd.RangeIndex(start=1, stop=4, step=1)), 
    'media_thumbnail': pd.Series([
        np.nan, 
        [{'url': 'https://external-preview.redd.it/fcUXTQfhTGh8Pgf3r-NT8tejZU7c-e73eylUXQmsjGk.jpg?width=640&crop=smart&auto=webp&s=b23eeb929e75fb34da96509017784b9e29a0974f'}], 
        [{'url': 'https://external-preview.redd.it/L-WoVKyLp1qifGqMEXat-zP64V9qW-pfE_4brQwU_fg.jpg?width=640&crop=smart&auto=webp&s=bea54909841c2ca86571494226c47deb9c2a9d2e'}]
    ], dtype='object', index=pd.RangeIndex(start=1, stop=4, step=1))
}, index=pd.RangeIndex(start=1, stop=4, step=1))

第二步：按列类型分别展开 #

# 处理单字典结构列：author_detail、title_detail，直接展开后加前缀避免重名
author_detail_df = pd.json_normalize(df['author_detail']).add_prefix('author_detail_')
title_detail_df = pd.json_normalize(df['title_detail']).add_prefix('title_detail_')

# 处理列表嵌套字典列：authors
# 先炸开列表为单个字典，再展开，保留原索引对齐
authors_df = df['authors'].explode().pipe(pd.json_normalize).add_prefix('authors_').set_index(df.index)

# 处理带空值的列表嵌套字典列：media_thumbnail
# 先处理空值，把非空列表取出第一个字典（如果列表包含多个元素，保留explode逻辑即可）
media_df = df['media_thumbnail'].apply(
    lambda x: x[0] if isinstance(x, list) and len(x) > 0 else {}
).pipe(pd.json_normalize).add_prefix('media_thumbnail_').set_index(df.index)

# 合并所有展开后的列，不需要原JSON列的话可以提前删除
result_df = pd.concat([
    df.drop(columns=['authors', 'author_detail', 'title_detail', 'media_thumbnail']),
    author_detail_df,
    title_detail_df,
    authors_df,
    media_df
], axis=1)

方案适配性说明 #

• 如果某列的列表包含多个元素（比如一个条目对应多个作者），只要删掉取第一个元素的逻辑，保留explode后和原表按索引合并即可生成多行行数据，适配一对多场景
• 所有空值、空列表场景会自动填充NaN，不会触发解析报错
• 列前缀可按需修改，避免不同列的同名字段冲突

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