Pandas多层索引DataFrame按日期将原始分数转换为百分位值

问题描述 #

现有多层索引结构的Pandas DataFrame：

需将Raw_Score字段转换为按日期维度对比的百分位值，映射规则：

• 当日最高Raw_Score对应百分位值100
• 当日最低Raw_Score对应百分位值0
• 其余分数按区间占比做线性映射
  计算需支持三个统计层级：
• 全量数据维度
• Sector（板块）维度
• Region（区域）维度
  最终在原DataFrame中新增Adjusted_Score列存储计算结果。

注：附可直接运行的实现代码

实现步骤 #

1. 构造测试数据集 #

首先生成符合多层索引结构的测试数据：

import pandas as pd
import numpy as np
from datetime import datetime
from numpy import random

def create_df(num):
  df = pd.DataFrame()
  # 生成连续日期序列
  dates = pd.date_range(start="2022-04-01",end="2022-06-05").to_pydatetime().tolist()
  df['Dates'] = dates
  # 生成随机原始分数
  df['Raw_Score'] = np.random.randint(-999, 999, size=len(df))
  # 生成随机板块标识
  df['Sector'] = np.random.randint(10, 20, size=len(df))
  # 标记区域编号
  df['Region'] = num
  return df

# 拼接10个区域的数据生成完整数据集
big_df = []
for num in range(10):
  df = create_df(num)
  if num == 0:
    big_df = df.copy()
  else:
    big_df = pd.concat((big_df, df), axis=0)

df = big_df.copy()
df.reset_index(inplace=True)
df = df.drop_duplicates()
# 设置三层索引：Region -> Sector -> Dates
df.set_index(["Region", "Sector", "Dates"], inplace=True)

2. 核心计算逻辑 #

线性百分位映射的核心是先按统计维度+日期分组，再对组内分数做归一化，以全量数据维度为例，计算分为三步：

1. 按日期分组，计算组内Raw_Score的连续排名
2. 按日期分组，取组内最大排名值（即当日样本总量）
3. 归一化计算百分位，保证最小值映射为0、最大值映射为100

# 按日期分组计算原始分数排名（排名从1开始）
df['rank'] = df.groupby('Dates')[['Raw_Score']].apply(lambda g: g.rank(method='first'))

# 取每个日期分组内的最大排名
df['group_size'] = df.groupby('Dates')[['rank']].transform(lambda g: g.max())

# 线性映射得到全量维度的调整分
df['Adjusted_Score'] = (df['rank'] - 1) / (df['group_size'] - 1) * 100

# 查看计算结果
df

3. 多统计层级切换方法 #

只需修改groupby的分组键，即可快速切换统计层级：

• 板块（Sector）维度：分组键改为['Dates', 'Sector']，在同一日期、同一板块范围内做分数映射
• 区域（Region）维度：分组键改为['Dates', 'Region']，在同一日期、同一区域范围内做分数映射

计算完成后可删除中间辅助列rank、group_size。

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