如何从DataFrame的JSON格式diff列提取键并合并到单列？

问题描述 #

现有如下Pandas DataFrame：

import pandas as pd

df = pd.DataFrame({'diff': [
    {'info': {'version': {'from': '2.0.0_1', 'to': '2.3.4_1'}}, 
     'paths': {'modified': {'/dummy': {'operations': {'added': ['PUT_1']}}}}, 
     'endpoints': {'added': [{'method': 'PUT_1', 'path': '/dummy_1'}]}, 
     'components': {'schemas': {'added': ['ObjectOfObjects_1', 'inline_object_1', 'ObjectOfObjects_inner_1']}, 
                    'requestBodies': {'added': ['inline_object_1', 'nested_response_1']}}},
    {'info': {'version': {'from': '2.0.0_2', 'to': '2.3.4_2'}}, 
     'paths': {'modified': {'/dummy': {'operations': {'added': ['PUT_2']}}}}, 
     'endpoints': {'added': [{'method': 'PUT_2', 'path': '/dummy_2'}]}, 
     'components': {'schemas': {'added': ['ObjectOfObjects_2', 'inline_object_2', 'ObjectOfObjects_inner_2']}, 
                    'requestBodies': {'added': ['inline_object_2', 'nested_response_2']}}}
] })

需要提取diff列中每个JSON对象的指定嵌套键路径，将这些路径合并到一个名为components的新列中。原代码是生成多列存储每个键对应的值，现在需要改为将所有键路径（如info-version）用逗号连接后存入单列，期望输出如下：

diff                                                            components
0   {'info': {'version': {'from': '2.0.0_1', 'to': '2.3.4_1'}}...   info-version,paths-modified,endpoints-added,components-schemas-added,components-requestBodies-added
1   {'info': {'version': {'from': '2.0.0_2', 'to': '2.3.4_2'}}...   info-version,paths-modified,endpoints-added,components-schemas-added,components-requestBodies-added

原代码如下：

def try_get(obj, *keys, defaultVal=None):
    try:
        for k in keys: obj = obj[k]
        return obj
    except: return defaultVal

kSep = '.' 
extractKeys = [ ('info', 'version'), 
                ('paths', 'modified'), 
                ('endpoints', 'added'),
                ('components', 'schemas', 'added'), 
                ('components', 'requestBodies', 'added') ]
for kl in extractKeys:
    df[kSep.join(kl)] = df['diff'].map(lambda d: try_get(d, *kl))

解决方案 #

方法1：保留所有指定的键路径（无论是否存在） #

如果需要将所有预定义的键路径都加入新列，无需检查每个JSON对象是否包含该路径，可以直接将键路径转换为字符串后合并：

def try_get(obj, *keys, defaultVal=None):
    try:
        for k in keys: obj = obj[k]
        return obj
    except: return defaultVal

# 修改分隔符为-，匹配期望输出的格式
kSep = '-' 
extractKeys = [ ('info', 'version'), 
                ('paths', 'modified'), 
                ('endpoints', 'added'),
                ('components', 'schemas', 'added'), 
                ('components', 'requestBodies', 'added') ]

# 将每个键元组转换为带分隔符的字符串
key_paths = [kSep.join(keys) for keys in extractKeys]
# 合并所有路径为逗号分隔的字符串
combined_keys = ','.join(key_paths)

# 给所有行赋值相同的合并结果
df['components'] = combined_keys

方法2：仅保留JSON对象中实际存在的键路径 #

如果需要过滤掉那些在当前JSON对象中不存在的键路径，可通过try_get检查后再收集：

def try_get(obj, *keys, defaultVal=None):
    try:
        for k in keys: obj = obj[k]
        return obj
    except: return defaultVal

kSep = '-' 
extractKeys = [ ('info', 'version'), 
                ('paths', 'modified'), 
                ('endpoints', 'added'),
                ('components', 'schemas', 'added'), 
                ('components', 'requestBodies', 'added') ]

def collect_existing_keys(diff_obj):
    existing_paths = []
    for keys in extractKeys:
        # 检查当前键路径是否存在
        if try_get(diff_obj, *keys) is not None:
            existing_paths.append(kSep.join(keys))
    return ','.join(existing_paths)

# 对每行的diff应用函数，生成components列
df['components'] = df['diff'].apply(collect_existing_keys)

结果验证 #

运行上述代码后，df的components列将符合期望输出格式，包含所有指定的键路径（或仅存在的路径）。

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