如何基于CUSIP匹配两个Pandas DataFrame指定列做减法运算

问题需求 #

我需要用Pandas DataFrame，以CUSIP作为唯一标识，计算两个数据帧中(x$1000)和PRN AMT列的差值，且解决方案需支持行顺序不同的场景。我查阅了dataframe.subtract()的文档，但不知如何应用到该场景。

数据帧1 #

CUSIP       (x$1000)     PRN AMT   TICKER                            
594918104  2765345852  2114080582   MSFT
037833100  1891440177  3058252946   AAPL
02079K305  1721936077   132543866  GOOGL
023135106  1341784239  2573051329   AMZN

数据帧2 #

CUSIP       (x$1000)     PRN AMT   TICKER  
594918104  3034828140  1612323669   MSFT
037833100  2463247977  2628732382   AAPL
02079K305  2096049986    93429916  GOOGL
023135106  1581124222   118724459   AMZN

期望输出 #

CUSIP       (x$1000)     PRN AMT   TICKER  
594918104  -269482288  501756913   MSFT
037833100  -571807800  429520564   AAPL
02079K305  -374113909   39113950   GOOGL
023135106  -239339983  2454326870   AMZN

数据重建代码 #

import pandas as pd
dataset_1 = {'CUSIP': ['594918104', '037833100', '02079K305', '023135106'], '(x$1000)': [
    2765345852, 1891440177, 1721936077, 1341784239], 'PRN AMT': [2114080582, 3058252946, 132543866, 2573051329], 'TICKER': ['MSFT', 'AAPL', 'GOOGL', 'AMZN']}
dataset_2 = {'CUSIP': ['594918104', '037833100', '02079K305', '023135106'], '(x$1000)': [
    3034828140, 2463247977, 2096049986, 1581124222], 'PRN AMT': [1612323669, 2628732382, 93429916, 118724459], 'TICKER': ['MSFT', 'AAPL', 'GOOGL', 'AMZN']}
df_1 = pd.DataFrame(data=dataset_1)
df_2 = pd.DataFrame(data=dataset_2)

解决方案 #

核心思路 #

利用索引匹配实现行顺序无关的计算：

• 将CUSIP设为两个DataFrame的索引，让subtract方法自动按唯一标识对齐行
• 仅对目标列执行减法运算，保留其他列的原始数据
• 最后重置索引恢复原始结构

完整实现代码 #

import pandas as pd

# 数据重建（可直接复用上面的代码）
dataset_1 = {'CUSIP': ['594918104', '037833100', '02079K305', '023135106'], 
             '(x$1000)': [2765345852, 1891440177, 1721936077, 1341784239], 
             'PRN AMT': [2114080582, 3058252946, 132543866, 2573051329], 
             'TICKER': ['MSFT', 'AAPL', 'GOOGL', 'AMZN']}
dataset_2 = {'CUSIP': ['594918104', '037833100', '02079K305', '023135106'], 
             '(x$1000)': [3034828140, 2463247977, 2096049986, 1581124222], 
             'PRN AMT': [1612323669, 2628732382, 93429916, 118724459], 
             'TICKER': ['MSFT', 'AAPL', 'GOOGL', 'AMZN']}
df_1 = pd.DataFrame(data=dataset_1)
df_2 = pd.DataFrame(data=dataset_2)

# 1. 将CUSIP设置为索引，确保行匹配基于唯一标识
df1_indexed = df_1.set_index('CUSIP')
df2_indexed = df_2.set_index('CUSIP')

# 2. 指定需要计算差值的列，执行df1 - df2的减法
target_cols = ['(x$1000)', 'PRN AMT']
diff_result = df1_indexed[target_cols].subtract(df2_indexed[target_cols])

# 3. 合并不需要计算的列（如TICKER），重置索引恢复原始格式
final_result = pd.concat([diff_result, df1_indexed['TICKER']], axis=1).reset_index()

# 输出结果
print(final_result)

代码说明 #

1. 设置索引：set_index('CUSIP')让DataFrame以唯一标识作为行标签，不管原始行顺序如何，后续运算都会自动按CUSIP对齐
2. 列选择计算：仅对(x$1000)和PRN AMT列执行减法，避免干扰其他列
3. 合并与重置索引：concat将差值结果和TICKER列合并，reset_index()把CUSIP从索引变回普通列，完全匹配期望输出结构

验证行顺序无关性 #

如果打乱df_2的行顺序，结果依然会正确对齐CUSIP：

# 打乱df_2的行顺序
df_2_shuffled = df_2.sample(frac=1).reset_index(drop=True)
# 重复上述步骤，结果不受行顺序影响

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