使用Pivot Table处理带自定义问题的JSON数据时信息错位求助

问题场景 #

有一个包含多列自定义问题的JSON数据集，部分问题标题重复、部分不同，数据如下：

apend = [
    {
        "first_name": "Raúl Pedro",
        "last_name": "Moreno Zavaleta",
        "email": "raulmoreno147@gmail.com",
        "custom_questions": [
            {
                "title": "a",
                "value": "si"
            },
            {
                "title": "b",
                "value": "no"
            },
            {
                "title": "c",
                "value": "001"
            } 
        ],
        "status": "approved",
        "create_time": "2023-02-18T17:25:30Z"
    },
    {
        "first_name": "Milagritos",
        "last_name": "Canales Lora",
        "email": "milacanaleslora@gmail.com",
        "custom_questions": [
            {
                "title": "a",
                "value": "no"
            },
            {
                "title": "b",
                "value": "si"
            }
        ],
        "status": "approved",
        "create_time": "2023-02-21T23:07:24Z"
    },
    {
        "first_name": "Eliza",
        "last_name": "Carbajal Leon",
        "email": "milacanaleslora@gmail.com",
        "custom_questions": [
            {
                "title": "a",
                "value": "no"
            },
            {
                "title": "e",
                "value": "identiti"
            }
        ],
        "status": "approved",
        "create_time": "2023-02-21T23:07:24Z"
    }
]

原处理代码如下：

import pandas as pd
import numpy as np

pp1 = pd.json_normalize(apend)
pp = pd.DataFrame.from_dict(np.concatenate(pp1['custom_questions']).tolist())
crear = pd.pivot_table(pp, values='value',columns='title', aggfunc= list).reset_index()
crear = (crear.apply(lambda x: x.apply(pd.Series).stack()).reset_index().drop('index', 1))
ee = crear.drop(["level_0", "level_1"], axis=1).reset_index(drop=True)
unir = pd.merge(pp1,ee, how = "outer", left_index = True, right_index = True)
unir = unir.drop(['custom_questions'], axis = 1)

执行后出现问题：第三个用户的信息丢失，e列的数据错误映射到第一个用户的行中。

问题原因 #

原代码在合并所有custom_questions时，丢失了每个问题对应的用户归属信息，导致透视后无法正确关联回原用户。

正确解决方案 #

核心思路是保留每个自定义问题对应的用户原始索引，确保透视后的数据能准确匹配到对应用户：

import pandas as pd

# 1. 初始规范化，保留原始数据结构
pp1 = pd.json_normalize(apend)

# 2. 展开custom_questions，同时保留用户的原始索引
expanded = pp1.explode('custom_questions').reset_index(drop=False)

# 3. 解析每个custom_question的title和value
questions_df = pd.json_normalize(expanded['custom_questions'])

# 4. 合并用户基础信息与解析后的问题数据
combined = pd.concat([expanded.drop('custom_questions', axis=1), questions_df], axis=1)

# 5. 按用户原始索引透视，将问题标题转为列
pivoted = combined.pivot(index='index', columns='title', values='value').reset_index(drop=True)

# 6. 合并基础信息与透视后的问题数据，得到最终结果
result = pd.concat([pp1.drop('custom_questions', axis=1), pivoted], axis=1)

print(result)

最终输出效果 #

first_name          last_name                    email    status          create_time    a    b     c        e
0  Raúl Pedro  Moreno Zavaleta  raulmoreno147@gmail.com  approved  2023-02-18T17:25:30Z   si   no   001       NaN
1  Milagritos    Canales Lora  milacanaleslora@gmail.com  approved  2023-02-21T23:07:24Z   no   si   NaN       NaN
2       Eliza   Carbajal Leon  milacanaleslora@gmail.com  approved  2023-02-21T23:07:24Z   no  NaN   NaN  identiti

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