编写通用函数计算DataFrame中嵌套字典差值并新增列

需求说明 #

针对现有DataFrame，按Exception、Hour维度分组，计算**2023-09-07（最新日期）与2023-09-06（前一日）**的unique_users和total_sessions差值，需分别对应jio和nokia两个运营商；要求实现通用可复用函数，适配cell这类包含多层嵌套字典的列。

原始数据集 #

import pandas as pd

data = [
    {"Date": "2023/09/06", "Hour": 2, "Exception": "S1AP", "cell": {'design': {'jio': {'total_sessions': 39273, 'unique_users': 30837}, 'nokia': {'total_sessions': 9523, 'unique_users': 7690}}}},
    {"Date": "2023/09/06", "Hour": 13, "Exception": "S1AP", "cell": {'design': {'jio': {'total_sessions': 46870, 'unique_users': 39330}, 'nokia': {'total_sessions': 11745, 'unique_users': 10059}}}},
    {"Date": "2023/09/07", "Hour": 13, "Exception": "S1AP", "cell": {'design': {'jio': {'total_sessions': 35688, 'unique_users': 29628}, 'nokia': {'total_sessions': 8759, 'unique_users': 7537}}}},
    {"Date": "2023/09/07", "Hour": 2, "Exception": "S1AP", "cell": {'design': {'jio': {'total_sessions': 37804, 'unique_users': 29654}, 'nokia': {'total_sessions': 8738, 'unique_users': 7272}}}}
]
df = pd.DataFrame(data)

已尝试的代码 #

import ast 
df2 = pd.json_normalize(df['cell'])
df3 = pd.concat([df, df2], axis=1)
df3 = df3.sort_values(['Hour', 'Exception', 'Date'], ascending=True)

# 分组计算差值
diff = df3.groupby(['Hour', 'Exception'])[df2.columns].diff()

# 整理结果
diff.columns = ["diff_"+ x for x in diff.columns]
df3 = pd.concat([df3, diff], axis=1)
df3.dropna().drop(columns=df2.columns)

期望输出 #

Date                Hour  Exception                IMSI_Operator
2023-09-07 00:00:00 2     S1AP: NAS: [2] Detach    {'design': {'jio': {'total_sessions': -1469, 'unique_users': -1183}, 'nokia': {'total_sessions': -785, 'unique_users': -418}}}
2023/09/07 0:00     13    S1AP: NAS: [2] Detach    {'design': {'jio': {'total_sessions': -11182, 'unique_users': -9702}, 'nokia': {'total_sessions': -2986, 'unique_users': -2522}}}

注：原期望输出中13点的差值符号有误，实际应为当日值减前日值：35688-46870=-11182，以此类推

通用解决方案 #

实现思路 #

1. 统一日期格式并转换为datetime类型，确保排序和差值计算的准确性
2. 按Hour、Exception分组，对每组内的嵌套字典数据按日期排序后计算差值
3. 将扁平化的差值结果重新组装为原始嵌套字典结构
4. 封装为通用函数，支持自定义日期列、分组列、嵌套字典列及目标键

通用函数代码 #

import pandas as pd

def calculate_nested_dict_diff(df, date_col, group_cols, nested_col, target_keys, date_order=['2023-09-06', '2023-09-07']):
    # 统一日期格式为datetime
    df[date_col] = pd.to_datetime(df[date_col])
    # 按分组列+日期排序
    df_sorted = df.sort_values(group_cols + [date_col]).reset_index(drop=True)
    
    # 定义差值计算函数
    def compute_diff(group):
        # 确保组内包含指定的两个日期
        if set(group[date_col].dt.strftime('%Y-%m-%d')) != set(date_order):
            return None
        # 获取两日的嵌套字典数据
        prev_data = group[group[date_col] == date_order[0]][nested_col].iloc[0]
        curr_data = group[group[date_col] == date_order[1]][nested_col].iloc[0]
        
        # 递归计算差值
        def recursive_diff(prev, curr):
            diff_dict = {}
            for k in curr.keys():
                if isinstance(curr[k], dict):
                    diff_dict[k] = recursive_diff(prev[k], curr[k])
                else:
                    # 当日值 - 前日值
                    diff_dict[k] = curr[k] - prev[k]
            return diff_dict
        
        diff_result = recursive_diff(prev_data, curr_data)
        # 构造结果行
        result_row = group[group[date_col] == date_order[1]][group_cols + [date_col]].iloc[0].to_dict()
        result_row['IMSI_Operator'] = diff_result
        return pd.Series(result_row)
    
    # 分组计算并整理结果
    result = df_sorted.groupby(group_cols).apply(compute_diff).dropna().reset_index(drop=True)
    # 格式化日期显示
    result[date_col] = result[date_col].dt.strftime('%Y-%m-%d %H:%M:%S')
    return result

# 调用函数
final_df = calculate_nested_dict_diff(
    df=df,
    date_col='Date',
    group_cols=['Hour', 'Exception'],
    nested_col='cell',
    target_keys=['jio', 'nokia'],
    date_order=['2023-09-06', '2023-09-07']
)

print(final_df)

输出结果 #

Date  Hour Exception                                      IMSI_Operator
0  2023-09-07 00:00:00     2      S1AP  {'design': {'jio': {'total_sessions': -1469, ...
1  2023-09-07 00:00:00    13      S1AP  {'design': {'jio': {'total_sessions': -11182,...

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