合并DataFrame并按指定条件分组计算期望占比及对应期望计数
合并DataFrame并计算期望数值的实现方案
实现逻辑
- 以
Sports为关联键合并df1和df2,匹配各运动对应的期望占比Expected_% - 按
Region分组统计每个区域的总参与人数(Count求和) - 用区域总人数 * 对应运动的期望占比 / 100,计算得到
Expected_count
代码实现
import pandas as pd # 示例数据构造,实际使用时替换为你自己的df1、df2读取逻辑即可 df1 = pd.DataFrame({ 'Sports': ['Cricket', 'Football', 'Tennis'], 'Expected_%': [70, 20, 10] }) df2 = pd.DataFrame({ 'Region': ['North', 'North', 'North', 'South', 'South', 'South'], 'Sports': ['Cricket', 'Football', 'Tennis', 'Cricket', 'Football', 'Tennis'], 'Count': [800, 50, 150, 1300, 550, 150], 'Percentage': [75, 5, 20, 65, 27.5, 7.5] }) # 合并匹配期望占比 merged_df = pd.merge(df2, df1, on='Sports', how='left') # 计算每个区域总人数 merged_df['region_total'] = merged_df.groupby('Region')['Count'].transform('sum') # 计算期望人数 merged_df['Expected_count'] = merged_df['region_total'] * merged_df['Expected_%'] / 100 # 剔除临时字段得到最终结果 final_df = merged_df.drop(columns='region_total') print(final_df)
输出结果
Region Sports Count Percentage Expected_% Expected_count 0 North Cricket 800 75.0 70 700.0 1 North Football 50 5.0 20 200.0 2 North Tennis 150 20.0 10 100.0 3 South Cricket 1300 65.0 70 1400.0 4 South Football 550 27.5 20 400.0 5 South Tennis 150 7.5 10 200.0
内容的提问来源于stack exchange,提问作者Danish
相关产品推荐
相关产品推荐

