基于双变量的数值映射：如何匹配公司与日期关联评级数据

问题说明 #

现有两张数据表：

• 评级信息表：存储公司在指定日期的评级
• 债务工具价格表：存储各公司不同债务工具在指定日期的价格

需要通过公司名称和日期两个字段匹配，将评级信息关联到债务工具价格表中。

评级信息表 #

Company    Date         Rating
A          2016-02-01   AAA
A          2016-02-02   AA
B          2016-02-01   BBB
B          2016-02-02   A

债务工具价格表 #

Company    Debt-Instrument     Date         Price
A          X1                  2016-02-01   100
A          X1                  2016-02-02   101
A          X2                  2016-02-01   98
A          X2                  2016-02-02   99
B          Y1                  2016-02-01   101
B          Y1                  2016-02-02   100
B          Y2                  2016-02-01   90
B          Y2                  2016-02-02   89

预期结果 #

Company    Debt-Instrument     Date         Price  Rating
A          X1                  2016-02-01   100    AAA
A          X1                  2016-02-02   101    AA
A          X2                  2016-02-01   98     AAA
A          X2                  2016-02-02   99     AA
B          Y1                  2016-02-01   101    BBB
B          Y1                  2016-02-02   100    A
B          Y2                  2016-02-01   90     BBB
B          Y2                  2016-02-02   89     A

主流工具实现方法 #

1. Excel 实现 #

方法一：INDEX+MATCH 多条件匹配

在债务工具表的Rating列（如E2单元格）输入公式：

=INDEX(评级表!$C$2:$C$5, MATCH(1, (债务工具表!A2=评级表!$A$2:$A$5)*(债务工具表!C2=评级表!$B$2:$B$5), 0))

• 旧版Excel需按 Ctrl+Shift+Enter 触发数组计算，新版直接回车即可
• 逻辑：通过(公司匹配)*(日期匹配)生成布尔数组，MATCH定位匹配位置，INDEX返回对应评级

方法二：XLOOKUP 多条件匹配（Excel 365/2021+）

新版Excel可直接用简化写法：

=XLOOKUP(1, (评级表!$A$2:$A$5=A2)*(评级表!$B$2:$B$5=C2), 评级表!$C$2:$C$5)

2. Python Pandas 实现 #

使用merge方法，指定两个关联键：

import pandas as pd

# 构造示例数据
rating_df = pd.DataFrame({
    'Company': ['A', 'A', 'B', 'B'],
    'Date': ['2016-02-01', '2016-02-02', '2016-02-01', '2016-02-02'],
    'Rating': ['AAA', 'AA', 'BBB', 'A']
})

debt_df = pd.DataFrame({
    'Company': ['A', 'A', 'A', 'A', 'B', 'B', 'B', 'B'],
    'Debt-Instrument': ['X1', 'X1', 'X2', 'X2', 'Y1', 'Y1', 'Y2', 'Y2'],
    'Date': ['2016-02-01', '2016-02-02', '2016-02-01', '2016-02-02', '2016-02-01', '2016-02-02', '2016-02-01', '2016-02-02'],
    'Price': [100, 101, 98, 99, 101, 100, 90, 89]
})

# 左关联保留债务工具表所有数据
result_df = pd.merge(debt_df, rating_df, on=['Company', 'Date'], how='left')
print(result_df)

3. SQL 实现 #

通过LEFT JOIN关联两张表，ON条件同时匹配公司和日期：

SELECT 
    d.Company,
    d.`Debt-Instrument`,
    d.Date,
    d.Price,
    r.Rating
FROM debt_instruments d
LEFT JOIN ratings r 
    ON d.Company = r.Company 
    AND d.Date = r.Date;

内容的提问来源于stack exchange，提问作者Elias K.