从类系列中提取元素

Question

给出以下样本数据（10条记录）：

test_df = pd.DataFrame({"PN_id": ["745d626b", "745d626b", "fce503fb", "df3d727e", "df3d727e", "56c00531", "72ebb2b3", "5d1bc5d3", "72ebb2b3", "5c32fc8a", "5c32fc8a"], 
                        "PN_raw": ['{"audience":{"and":[{"segment":"67537044-27db-4a0b-b5b7-362c9c5b2ba7"},{"tag":"BR","group":"ua_locale_country"},{"tag":"90_P******_BR","group":"******_CRM"}]}}', 
                                   '{"audience":{"and":[{"segment":"67537044-27db-4a0b-b5b7-362c9c5b2ba7"},{"tag":"BR","group":"ua_locale_country"},{"tag":"90_P******_BR","group":"******_CRM"}]}}', 
                                   '{"audience":{"and":[{"and":[{"segment":"850c8d94-1236-45a1-93fc-08b0337b4059"}]},{"and":[{"tag":"All_S****_ES","group":"******_CRM"}]}]}}', 
                                   '{"audience":{"and":[{"segment":"67537044-27db-4a0b-b5b7-362c9c5b2ba7"},{"tag":"BR","group":"ua_locale_country"},{"tag":"All_S*****_BR","group":"******_CRM"}]}}', 
                                   '{"audience":{"and":[{"segment":"67537044-27db-4a0b-b5b7-362c9c5b2ba7"},{"tag":"BR","group":"ua_locale_country"},{"tag":"All_S*****_BR","group":"******_CRM"}]}}', 
                                   '{"audience":{"and":[{"and":[{"segment":"850c8d94-1236-45a1-93fc-08b0337b4059"}]},{"and":[{"tag":"All_S****_ES","group":"******_CRM"}]}]}}', 
                                   '{"audience":{"and":[{"segment":"67537044-27db-4a0b-b5b7-362c9c5b2ba7"},{"tag":"BR","group":"ua_locale_country"},{"tag":"P_90_or_S_90_BR","group":"******_CRM"}]}}', 
                                   '{"audience":{"and":[{"segment":"67537044-27db-4a0b-b5b7-362c9c5b2ba7"},{"tag":"P_90_or_S_90_ESLA","group":"******_CRM"}]}}', 
                                   '{"audience":{"and":[{"segment":"67537044-27db-4a0b-b5b7-362c9c5b2ba7"},{"tag":"BR","group":"ua_locale_country"},{"tag":"P_90_or_S_90_BR","group":"******_CRM"}]}}', 
                                   '{"audience":{"and":[{"and":[{"segment":"850c8d94-1236-45a1-93fc-08b0337b4059"}]},{"and":[{"tag":"P_90_or_S_90_ES","group":"******_CRM"}]}]}}', 
                                   '{"audience":{"and":[{"and":[{"segment":"850c8d94-1236-45a1-93fc-08b0337b4059"}]},{"and":[{"tag":"P_90_or_S_90_ES","group":"******_CRM"}]}]}}']})

我怎样才能达到以下期望的输出？（在同一DF内或在单独的DF上，我认为这是一种可能）：

test_df_desired = pd.DataFrame({"PN_id":["745d626b", "745d626b", "fce503fb", "df3d727e", "df3d727e", "56c00531", "72ebb2b3", "5d1bc5d3", "72ebb2b3", "5c32fc8a", "5c32fc8a"], 
                                "segment":["67537044-27db-4a0b-b5b7-362c9c5b2ba7", "67537044-27db-4a0b-b5b7-362c9c5b2ba7", "850c8d94-1236-45a1-93fc-08b0337b4059", "67537044-27db-4a0b-b5b7-362c9c5b2ba7", "67537044-27db-4a0b-b5b7-362c9c5b2ba7", "850c8d94-1236-45a1-93fc-08b0337b4059", "67537044-27db-4a0b-b5b7-362c9c5b2ba7", "67537044-27db-4a0b-b5b7-362c9c5b2ba7", "67537044-27db-4a0b-b5b7-362c9c5b2ba7", "850c8d94-1236-45a1-93fc-08b0337b4059", "850c8d94-1236-45a1-93fc-08b0337b4059"], 
                                "tag_1":["BR", "BR", "All_S****_ES", "BR", "BR", "All_S****_ES", "BR", "P_90_or_S_90_ESLA", "BR", "P_90_or_S_90_ES", "P_90_or_S_90_ES"], 
                                "group_1":["ua_locale_country", "ua_locale_country", "******_CRM", "ua_locale_country", "ua_locale_country", "******_CRM", "ua_locale_country", "******_CRM", "ua_locale_country", "******_CRM", "******_CRM"], 
                                "tag_2":["90_P******_BR", "90_P******_BR", np.nan, "All_S*****_BR", "All_S*****_BR", np.nan, "P_90_or_S_90_BR", np.nan, "P_90_or_S_90_BR", np.nan, np.nan], 
                                "group_2":["******_CRM", "******_CRM", np.nan, "******_CRM", "******_CRM", np.nan, "******_CRM", np.nan, "******_CRM", np.nan, np.nan]})

到目前为止，使用pd.json_normalize(test_df["PN_raw"].apply(ast.literal_eval), record_path = ["audience", "and"])，我已经设法找到了dict路径结构为audience -> and的记录，但是对于路径为audience -> and -> and的记录，这是行不通的，我也无法绕过它添加record_path = ["audience", "and", "and"]，我认为这是行不通的。我认为这需要通过循环序列来解决，如果它包含一个或两个+和，则根据它是否包含一个不同的函数来应用不同的函数

当前输出不仅在上面提到的行上“失败”，而且还存在将数据“转换”到正确行的问题（如果运行上面的行，您将看到我的意思）

Answer 1

import json


def promote(d):
    if list(d.keys()) == ['and']:
        for i in d['and']:
            yield from promote(i)
    else:
        yield d

parsed = []
data = {"PN_id": ["745d626b", "745d626b", "fce503fb", "df3d727e", "df3d727e", "56c00531", "72ebb2b3", "5d1bc5d3", "72ebb2b3", "5c32fc8a", "5c32fc8a"],
 "PN_raw": ['{"audience":{"and":[{"segment":"67537044-27db-4a0b-b5b7-362c9c5b2ba7"},{"tag":"BR","group":"ua_locale_country"},{"tag":"90_P******_BR","group":"******_CRM"}]}}',
            '{"audience":{"and":[{"segment":"67537044-27db-4a0b-b5b7-362c9c5b2ba7"},{"tag":"BR","group":"ua_locale_country"},{"tag":"90_P******_BR","group":"******_CRM"}]}}',
            '{"audience":{"and":[{"and":[{"segment":"850c8d94-1236-45a1-93fc-08b0337b4059"}]},{"and":[{"tag":"All_S****_ES","group":"******_CRM"}]}]}}',
            '{"audience":{"and":[{"segment":"67537044-27db-4a0b-b5b7-362c9c5b2ba7"},{"tag":"BR","group":"ua_locale_country"},{"tag":"All_S*****_BR","group":"******_CRM"}]}}',
            '{"audience":{"and":[{"segment":"67537044-27db-4a0b-b5b7-362c9c5b2ba7"},{"tag":"BR","group":"ua_locale_country"},{"tag":"All_S*****_BR","group":"******_CRM"}]}}',
            '{"audience":{"and":[{"and":[{"segment":"850c8d94-1236-45a1-93fc-08b0337b4059"}]},{"and":[{"tag":"All_S****_ES","group":"******_CRM"}]}]}}',
            '{"audience":{"and":[{"segment":"67537044-27db-4a0b-b5b7-362c9c5b2ba7"},{"tag":"BR","group":"ua_locale_country"},{"tag":"P_90_or_S_90_BR","group":"******_CRM"}]}}',
            '{"audience":{"and":[{"segment":"67537044-27db-4a0b-b5b7-362c9c5b2ba7"},{"tag":"P_90_or_S_90_ESLA","group":"******_CRM"}]}}',
            '{"audience":{"and":[{"segment":"67537044-27db-4a0b-b5b7-362c9c5b2ba7"},{"tag":"BR","group":"ua_locale_country"},{"tag":"P_90_or_S_90_BR","group":"******_CRM"}]}}',
            '{"audience":{"and":[{"and":[{"segment":"850c8d94-1236-45a1-93fc-08b0337b4059"}]},{"and":[{"tag":"P_90_or_S_90_ES","group":"******_CRM"}]}]}}',
            '{"audience":{"and":[{"and":[{"segment":"850c8d94-1236-45a1-93fc-08b0337b4059"}]},{"and":[{"tag":"P_90_or_S_90_ES","group":"******_CRM"}]}]}}']}

data['PN'] = list(map(json.loads, data['PN_raw']))
for ind, pn_id in enumerate(data['PN_id']):
    parsed_sub = {'PN_id': pn_id}
    count = 1
    for chunk in promote(data['PN'][ind]['audience']):
        if 'segment' in chunk:
            parsed_sub.update(chunk)
        else:
            parsed_sub.update({f'{k}{count}': v for k, v in chunk.items()})
            count +=1
    parsed.append(parsed_sub)

df = pd.DataFrame(parsed)

我发现pandas混淆了JSON，我更喜欢用基本的python来处理它。我要说的是，JSON可以有如此不同的形状，以至于没有很好的方法来实现一个通用的“make_the_JSON_flat（）”函数，尽管如果有这样的东西，我很想了解它