pandas实现按LOCATION分组匹配1970-Q1基准Value计算归一化值

核心计算规则 #

对齐原有SQL逻辑：按LOCATION字段对全表记录分组，组内每条记录的原始Value除以同组的基准值后乘以100，得到归一化字段normValue，最终输出按LOCATION、TIME字段升序排列。

方案1：以组内TIME='1970-Q1'的Value为固定基准 #

完全匹配原SQL的CTE关联计算逻辑，实现步骤：

• 提取全表中TIME='1970-Q1'的记录，构建LOCATION与对应基准Value的映射关系
• 给原表每一行匹配所属分组的基准值
• 按照公式计算normValue
• 按要求排序后整理输出结果

对应可直接运行的代码：

import pandas as pd

# 假设加载后的原始数据集为df，包含字段 LOCATION、TIME、Value
# 提取基准值映射
base_map = (
    df[df['TIME'] == '1970-Q1']
    .drop_duplicates(subset=['LOCATION'], keep='first')  # 规避同LOCATION重复基准值的异常
    .set_index('LOCATION')['Value']
    .to_dict()
)

# 匹配基准值并计算归一化值
df['base_value'] = df['LOCATION'].map(base_map)
df['normValue'] = df['Value'].div(df['base_value']) * 100

# 排序并清理临时列
result = (
    df.drop(columns=['base_value'])
    .sort_values(by=['LOCATION', 'TIME'], ascending=[True, True])
    .reset_index(drop=True)
)

注意：如果某类LOCATION不存在1970-Q1的记录，对应行的normValue会返回空值，可根据业务规则提前过滤或补充默认值。

方案2：取组内TIME升序排序后的首值为基准 #

无需硬编码指定基准时间，适配TIME字段为YYYY-QX字符串的格式特性（字典序与实际时间顺序完全一致），实现更轻量化：

import pandas as pd

# 假设加载后的原始数据集为df
# 先全表按LOCATION、TIME升序排列
df = df.sort_values(by=['LOCATION', 'TIME'], ascending=[True, True])
# 按分组取每组的第一个Value作为基准值
df['base_value'] = df.groupby('LOCATION')['Value'].transform('first')

# 计算归一化值并整理结果
df['normValue'] = df['Value'].div(df['base_value']) * 100
result = (
    df.drop(columns=['base_value'])
    .reset_index(drop=True)
)

优势：不需要单独筛选指定时间的记录，自动适配分组内时间最早的记录作为基准，和排序取首值的业务逻辑完全等价。

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