如何将DataFrame与透视表关联以生成对应比率列？

嗨，这个需求其实很常见，核心就是把原数据里的数值映射到对应的区间，再通过区间匹配拿到对应的比率。我给你两种实用的方法，你可以根据自己的场景选择：

方法一：用DataFrame合并（直观易维护） #

这种方法适合映射表比较大、后续可能需要调整的场景，步骤清晰，容易排查问题：

1. 先构造原数据和映射表的DataFrame #

首先把你的原始数据和透视表都转换成结构化的DataFrame：

import pandas as pd

# 原始数据
df = pd.DataFrame({
    'ID': ['A1', 'A2', 'A3'],
    'Sum total': [40, 70, 100],
    'Sum partial': [25, 50, 40]
})

# 把透视表转成长格式的映射DataFrame
mapping_data = [
    {'Sum total interval': '0-50', 'Sum partial interval': '0-30', 'Ratio': 0.10},
    {'Sum total interval': '0-50', 'Sum partial interval': '30-55', 'Ratio': 0.17},
    {'Sum total interval': '0-50', 'Sum partial interval': '55-70', 'Ratio': 0.22},
    {'Sum total interval': '50-75', 'Sum partial interval': '0-30', 'Ratio': 0.14},
    {'Sum total interval': '50-75', 'Sum partial interval': '30-55', 'Ratio': 0.18},
    {'Sum total interval': '50-75', 'Sum partial interval': '55-70', 'Ratio': 0.25},
    {'Sum total interval': '75-100', 'Sum partial interval': '0-30', 'Ratio': 0.20},
    {'Sum total interval': '75-100', 'Sum partial interval': '30-55', 'Ratio': 0.27},
    {'Sum total interval': '75-100', 'Sum partial interval': '55-70', 'Ratio': 0.38}
]
mapping_df = pd.DataFrame(mapping_data)

2. 给原始数据的数值列划分区间 #

用pd.cut把Sum total和Sum partial的值映射到对应的区间标签：

# 定义Sum total的区间边界与标签
total_bins = [0, 50, 75, 100]
total_labels = ['0-50', '50-75', '75-100']
df['Sum total interval'] = pd.cut(df['Sum total'], bins=total_bins, labels=total_labels, include_lowest=True)

# 定义Sum partial的区间边界与标签
partial_bins = [0, 30, 55, 70]
partial_labels = ['0-30', '30-55', '55-70']
df['Sum partial interval'] = pd.cut(df['Sum partial'], bins=partial_bins, labels=partial_labels, include_lowest=True)

这里的include_lowest=True是为了确保0这类最小值能被正确划分到第一个区间里。

3. 合并两个DataFrame得到比率列 #

用两个区间列作为匹配键，把映射表的比率合并到原始数据中：

# 合并数据
result_df = df.merge(mapping_df, on=['Sum total interval', 'Sum partial interval'], how='left')

# 整理成你需要的列顺序和名称
result_df = result_df[['ID', 'Sum total', 'Sum partial', 'Ratio']].rename(columns={'Ratio': 'Ratio given by grid'})

print(result_df)

方法二：用嵌套字典映射（简洁高效） #

如果你的映射表比较小，用嵌套字典会更简洁，代码量更少：

import pandas as pd

df = pd.DataFrame({
    'ID': ['A1', 'A2', 'A3'],
    'Sum total': [40, 70, 100],
    'Sum partial': [25, 50, 40]
})

# 构造嵌套字典，对应透视表的映射关系
ratio_map = {
    '0-50': {'0-30': 0.10, '30-55': 0.17, '55-70': 0.22},
    '50-75': {'0-30': 0.14, '30-55': 0.18, '55-70': 0.25},
    '75-100': {'0-30': 0.20, '30-55': 0.27, '55-70': 0.38}
}

# 先划分区间（和方法一的步骤一样）
total_bins = [0, 50, 75, 100]
total_labels = ['0-50', '50-75', '75-100']
df['Sum total interval'] = pd.cut(df['Sum total'], bins=total_bins, labels=total_labels, include_lowest=True)

partial_bins = [0, 30, 55, 70]
partial_labels = ['0-30', '30-55', '55-70']
df['Sum partial interval'] = pd.cut(df['Sum partial'], bins=partial_bins, labels=partial_labels, include_lowest=True)

# 用apply函数匹配对应比率
df['Ratio given by grid'] = df.apply(lambda row: ratio_map[row['Sum total interval']][row['Sum partial interval']], axis=1)

# 整理列
result_df = df[['ID', 'Sum total', 'Sum partial', 'Ratio given by grid']]
print(result_df)

两种方法最终都会输出你想要的结果：

ID  Sum total  Sum partial  Ratio given by grid
0  A1         40           25                 0.10
1  A2         70           50                 0.18
2  A3        100           40                 0.27

备注：内容来源于stack exchange，提问作者Théo M