基于Pandas DataFrame按小时/Exception计算运营商unique_users差值

需求概述 #

处理含嵌套字典列IMSI_Operator的Pandas DataFrame，需完成以下操作：

• 仅针对Exception为s2ap、Hour为2和13的数据
• 计算2023-09-07（最新日）与2023-09-06（前一日）的bsnl、Other的unique_users差值
• 将差值字段添加到原DataFrame，最终仅保留2023-09-07的行

示例输入数据 #

import pandas as pd

data = [
    {"Date": "2023-09-06", "Hour": 2, "Exception": "s2ap", "IMSI_Operator": {"bsnl": {"unique_users": 100}, "Other": {"unique_users": 200}}},
    {"Date": "2023-09-06", "Hour": 13, "Exception": "s2ap", "IMSI_Operator": {"bsnl": {"unique_users": 150}, "Other": {"unique_users": 250}}},
    {"Date": "2023-09-07", "Hour": 2, "Exception": "s2ap", "IMSI_Operator": {"bsnl": {"unique_users": 120}, "Other": {"unique_users": 210}}},
    {"Date": "2023-09-07", "Hour": 13, "Exception": "s2ap", "IMSI_Operator": {"bsnl": {"unique_users": 160}, "Other": {"unique_users": 270}}},
    {"Date": "2023-09-07", "Hour": 2, "Exception": "other", "IMSI_Operator": {"bsnl": {"unique_users": 90}, "Other": {"unique_users": 180}}}
]
df = pd.DataFrame(data)

完整解决方案代码 #

import pandas as pd

# 加载数据（替换为你的实际数据源）
data = [
    {"Date": "2023-09-06", "Hour": 2, "Exception": "s2ap", "IMSI_Operator": {"bsnl": {"unique_users": 100}, "Other": {"unique_users": 200}}},
    {"Date": "2023-09-06", "Hour": 13, "Exception": "s2ap", "IMSI_Operator": {"bsnl": {"unique_users": 150}, "Other": {"unique_users": 250}}},
    {"Date": "2023-09-07", "Hour": 2, "Exception": "s2ap", "IMSI_Operator": {"bsnl": {"unique_users": 120}, "Other": {"unique_users": 210}}},
    {"Date": "2023-09-07", "Hour": 13, "Exception": "s2ap", "IMSI_Operator": {"bsnl": {"unique_users": 160}, "Other": {"unique_users": 270}}},
    {"Date": "2023-09-07", "Hour": 2, "Exception": "other", "IMSI_Operator": {"bsnl": {"unique_users": 90}, "Other": {"unique_users": 180}}}
]
df = pd.DataFrame(data)

# 1. 筛选目标维度的数据：指定Exception、Hour和日期范围
target_subset = df[
    (df["Exception"] == "s2ap") &
    (df["Hour"].isin([2, 13])) &
    (df["Date"].isin(["2023-09-06", "2023-09-07"]))
].copy()

# 2. 从嵌套字典中提取unique_users值，生成可计算的列
target_subset["bsnl_users"] = target_subset["IMSI_Operator"].apply(lambda x: x["bsnl"]["unique_users"])
target_subset["other_users"] = target_subset["IMSI_Operator"].apply(lambda x: x["Other"]["unique_users"])

# 3. 按Hour和Exception分组，计算两日差值
# 给日期打标记：0代表前一日，1代表最新日
target_subset["date_tag"] = target_subset["Date"].map({"2023-09-06": 0, "2023-09-07": 1})
# 透视表对齐两日数据
pivot_diff = target_subset.pivot_table(
    index=["Hour", "Exception"],
    columns="date_tag",
    values=["bsnl_users", "other_users"]
).reset_index()

# 计算差值：最新日数值 - 前一日数值
pivot_diff["bsnl_diff"] = pivot_diff[("bsnl_users", 1)] - pivot_diff[("bsnl_users", 0)]
pivot_diff["other_diff"] = pivot_diff[("other_users", 1)] - pivot_diff[("other_users", 0)]

# 清理列，保留分组字段和差值结果
diff_result = pivot_diff[["Hour", "Exception", "bsnl_diff", "other_diff"]]

# 4. 合并差值到原DataFrame，仅保留最新日期的行
final_df = pd.merge(
    df[df["Date"] == "2023-09-07"],
    diff_result,
    on=["Hour", "Exception"],
    how="left"
)

# 非目标维度的行填充NaN（可根据需求改为0）
final_df[["bsnl_diff", "other_diff"]] = final_df[["bsnl_diff", "other_diff"]].fillna(pd.NA)

# 查看结果
print(final_df)

预期输出 #

Date  Hour Exception                                      IMSI_Operator  bsnl_diff  other_diff
0  2023-09-07     2      s2ap  {'bsnl': {'unique_users': 120}, 'Other': {'uni...       20.0        10.0
1  2023-09-07    13      s2ap  {'bsnl': {'unique_users': 160}, 'Other': {'uni...       10.0        20.0
2  2023-09-07     2     other  {'bsnl': {'unique_users': 90}, 'Other': {'uniqu...        NaN         NaN

关键步骤说明 #

1. 精准筛选：通过布尔索引锁定需要计算的Exception、Hour和日期范围，排除无关数据
2. 提取嵌套值：用apply方法从字典列中提取目标字段，将嵌套结构转为扁平列，方便后续计算
3. 透视表计算差值：利用pivot_table将两日的数据按分组维度对齐，直接做减法得到差值，确保维度匹配准确
4. 合并与过滤：只保留最新日期的行，将差值字段匹配到对应行，非目标行的差值设为NaN（可根据业务需求调整为0或其他值）

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