Pandas问题：按规则为每个dates_marker保留最优holiday行

需求说明 #

针对每个dates_marker，需从对应holiday列表（或单个值）中筛选出唯一最优holiday，优先级规则：

• 优先选择**平均售票量（avg(tickets_sold)）**最高的holiday
• 若售票量平局，则以**平均营收（avg(revenue)）**为依据
• 若仍平局，取原始holiday列表中的第一个项

修正后的实现代码 #

import pandas as pd
import numpy as np

data = {
    'holiday': [["King's Birthday", "Father's Day", "Charles Day"], ["King's Birthday", "Father's Day"], 
                ["New Year's Day", "Father's Day"], ["New Year's Day", "Father's Day"], 
                ['Christmas', 'Boxing Day'], ['Christmas', 'Boxing Day'], 
                ['Christmas', 'Boxing Day'], ['Christmas', 'Boxing Day'], 
                "New Year's Day", "New Year's Day", "New Year's Day", "New Year's Day", 
                'Easter', 'Easter', 'Easter', 'Easter'],
    'tickets_sold': [167, 168, 1, 2, 356, 357, 358, 359, 92, 93, 105, 106, 97, 98, 89, 90],
    'revenue': [12, 12, 44, 33, 21, 212, 12, 12, 2021, 2021, 2022, 2022, 2023, 2023, 2024, 2024],
    'dates_marker': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]
}

# 1. 构造DataFrame，保留原始holiday列表用于平局时取第一个
df = pd.DataFrame(data)
df['original_holiday'] = df['holiday'].copy()

# 2. 将holiday列表拆分为单行记录
df_exploded = df.explode('holiday')

# 3. 生成各holiday的全局平均售票量和营收
reference_data = df_exploded.groupby('holiday').agg(
    tickets_sold_avg=('tickets_sold', np.mean),
    revenue_avg=('revenue', np.mean)
).reset_index()

# 4. 关联参考数据，给每个holiday标记全局平均值
df_merged = df_exploded.merge(reference_data, on='holiday', how='left')

# 5. 给每个holiday在原始列表中的位置编号，用于平局时取第一个
df_merged['holiday_order'] = df_merged.apply(
    lambda row: row['original_holiday'].index(row['holiday']) if isinstance(row['original_holiday'], list) else 0,
    axis=1
)

# 6. 按dates_marker分组，按优先级排序后取每组第一行
result = df_merged.sort_values(
    by=['dates_marker', 'tickets_sold_avg', 'revenue_avg', 'holiday_order'],
    ascending=[True, False, False, True]
).groupby('dates_marker').first().reset_index()

# 可选：移除中间辅助列
result = result.drop(columns=['original_holiday', 'holiday_order', 'tickets_sold_avg', 'revenue_avg'])

print(result)

关键步骤解释 #

• 保留original_holiday列：用于后续判断holiday在原始列表中的顺序，处理最后一步平局的情况
• 生成reference_data：计算每个holiday的全局平均售票量和营收，作为筛选的核心依据
• 新增holiday_order列：标记每个holiday在原始列表中的位置，确保平局时取第一个项
• 多维度排序+分组取首：按dates_marker分组后，先按平均售票量降序、平均营收降序，再按原始位置升序排序，每组第一行即为最优结果

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