pandas如何先按Brand分组取Number最大值对应行，空值再按ID分组补全

完整实现代码 #

import pandas as pd

# 初始化示例数据集
df = pd.DataFrame({
    'ID': ['M2','M7','M9','M4','M3','M6','M99','M8','M10','M10'],
    'Color' : ['pink','blue','blue','yellow','blue','pink','red','pink','red','blue'],
    'Number': [23255,1755,7897,6666,1311,78946,44893,148791,18783,487913],
    'Brand':["Brand9","Brand9","Brand9","Brand7","Brand7","","Brand1","Brand1","",""]
})

# ----------------------第一步：按Brand分组计算最大值及对应字段----------------------
# 过滤非空Brand的分组，取每组Number最大值对应的行
brand_non_empty = df[df['Brand'].ne('')]
max_brand_mapping = brand_non_empty.loc[
    brand_non_empty.groupby('Brand')['Number'].idxmax(), 
    ['Brand', 'Number', 'Color', 'ID']
].rename(columns={
    'Number': 'Max',
    'Color': 'Max Color',
    'ID': 'Max ID'
})
# 合并映射表到原数据集，空Brand行自动留空
df = df.merge(max_brand_mapping, on='Brand', how='left')

# ----------------------第二步：计算Final字段和备注----------------------
# 非空Brand行直接复用Max ID、Max Color作为Final值
df['Final ID'] = df['Max ID']
df['Final Color'] = df['Max Color']

# 处理空Brand的行
empty_brand_df = df[df['Brand'].eq('')].copy()
# 按ID分组取每组Number最大值对应的ID和Color
max_id_mapping = empty_brand_df.loc[
    empty_brand_df.groupby('ID')['Number'].idxmax(),
    ['ID', 'Color']
].rename(columns={
    'ID': 'final_id_tmp',
    'Color': 'final_color_tmp'
})
# 合并映射到空Brand数据
empty_brand_df = empty_brand_df.merge(max_id_mapping, on='ID', how='left')
# 回填Final字段
df.loc[df['Brand'].eq(''), ['Final ID', 'Final Color']] = empty_brand_df[['final_id_tmp', 'final_color_tmp']].values

# 生成备注列
df['备注'] = ''
# 统计空Brand下各ID出现次数
id_cnt = empty_brand_df['ID'].value_counts()
single_ids = id_cnt[id_cnt == 1].index
multi_ids = id_cnt[id_cnt > 1].index
df.loc[(df['Brand'].eq('')) & df['ID'].isin(single_ids), '备注'] = '返回本身数据'
df.loc[(df['Brand'].eq('')) & df['ID'].isin(multi_ids), '备注'] = '按ID分组取最大值对应值'

# 输出结果（将NaN转为空字符串匹配展示要求）
print(df.fillna(''))

补充说明 #

如果同一Brand下存在多条Number相同的最大值记录，代码默认取排序后的第一条，若需自定义取值规则，可修改idxmax()部分的逻辑，额外增加排序条件即可。

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