pandas - 使用 Pandas ，如何在变量索引上连接两个表？

Question

有两个表，条目可能有不同的 id 类型。我需要根据 df1 的 id_type 和 df2 的正确列连接两个表。问题的背景，id是金融界的证券id，id类型可能是CUSIP、ISIN、RIC等。

print(df1)
   id id_type  value
0  11  type_A    0.1
1  22  type_B    0.2
2  13  type_A    0.3

print(df2)
  type_A type_B type_C
0     11     21     xx
1     12     22     yy
2     13     23     zz

所需的输出是

  type_A type_B type_C  value
0     11     21     xx    0.1
1     12     22     yy    0.2
2     13     23     zz    0.3

Answer 1

这是一种替代方法，可以推广到许多安全类型(CUSIP、ISIN、RIC、SEDOL 等)。
一、创建df1和 df2沿着原始示例的路线:

import numpy as np
import pandas as pd

df1 = pd.DataFrame({'sec_id': [11, 22, 33],
                    'sec_id_type': ['CUSIP', 'ISIN', 'RIC'], 
                    'value': [100, 200, 300]})

df2 = pd.DataFrame({'CUSIP': [11, 21, 31],
                    'ISIN': [21, 22, 23],
                    'RIC': [31, 32, 33],
                    'SEDOL': [41, 42, 43]})

二、创建中间数据框 x1 .我们将第一列用于一个连接，第二和第三列用于不同的连接:

index = [idx for idx in df2.index for _ in df2.columns]
sec_id_types = df2.columns.to_list() * df2.shape[0]
sec_ids = df2.values.ravel()

data = [
    (idx, sec_id_type, sec_id)
    for idx, sec_id_type, sec_id in zip(index, sec_id_types, sec_ids)
]

x1 = pd.DataFrame.from_records(data, columns=['index', 'sec_id_type', 'sec_id'])

加入 df1和 x1从 df1 中提取值:

x2 = (x1.merge(df1, on=['sec_id_type', 'sec_id'], how='left')
      .dropna()
      .set_index('index'))

最后加入 df2和 x1 (从上一步)得到最终结果

print(df2.merge(x2, left_index=True, right_index=True, how='left'))

   CUSIP  ISIN  RIC  SEDOL sec_id_type  sec_id  value
0     11    21   31     41       CUSIP      11  100.0
1     21    22   32     42        ISIN      22  200.0
2     31    23   33     43         RIC      33  300.0

栏目 sec_id_type和 sec_id显示连接按预期工作。