Pandas分组聚合统计后筛选并生成指定结构字典的实现问题

Part1 分组统计与筛选 #

# 新增辅助列简化聚合逻辑
df['is_green'] = df['color'] == 'Green'
df['is_purple'] = df['color'] == 'Purple'
df['green_temp'] = df['temp'].where(df['is_green'])
df['purple_temp'] = df['temp'].where(df['is_purple'])

# 按指定维度分组，计算所有要求字段
group_res = df.groupby(['city', 'gender', 'temp_range'], as_index=False).agg(
    Green_count = ('is_green', 'sum'),
    Purple_count = ('is_purple', 'sum'),
    Green_sum = ('green_temp', 'sum'),
    Purple_sum = ('purple_temp', 'sum'),
    Green_max = ('green_temp', 'max'),
    Purple_max = ('purple_temp', 'max')
)

# 计算Green占比，处理除零异常
group_res['total'] = group_res['Green_count'] + group_res['Purple_count']
group_res['Green_pct'] = group_res['Green_count'] / group_res['total'].replace(0, np.nan)
group_res['Green_pct'] = group_res['Green_pct'].fillna(0)

# 筛选符合Green_pct>0.5的记录
filtered_df = group_res[group_res['Green_pct'] > 0.5].reset_index(drop=True)

Part2 转换为指定格式字典 #

temp_dict = {}
for (city, gender), g_df in filtered_df.groupby(['city', 'gender']):
    # 提取所有区间的左右端点，去重排序后转为元组
    points = pd.concat([g_df['temp_range'].apply(lambda x: x.left), 
                        g_df['temp_range'].apply(lambda x: x.right)]).drop_duplicates().sort_values()
    temp_dict[(city, gender)] = tuple(points)

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