Using pandas, how to join two tables on variable index?

user_4512130 0
Viewed 90

There are two tables, the entries may have different id type. I need to join two tables based on id_type of df1 and the correct column of df2. For the background of the problem, the ids are security id in financial world, the id type may be CUSIP, ISIN, RIC etc..

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

The desired output is

  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
pandas merge
2 Answers

Here is an alternative approach, which generalizes to many security types (CUSIP, ISIN, RIC, SEDOL, etc.).

First, create df1 and df2 along the lines of the original example:

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]})

Second, create an intermediate data frame x1. We will use the first column for one join, and the second and third columns for a different join:

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'])

Join df1 and x1 to extract values from df1:

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

Finally, join df2 and x1 (from previous step) to get final result

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

The columns sec_id_type and sec_id show the joins work as expected.

user_136085990

NEW Solution 1: create a temporary column that determines the ID with np.where

df2['id'] = np.where(df2['type_A'] == df1['id'], df2['type_A'], df2['type_B'])
df = pd.merge(df2,df1[['id','value']],how='left',on='id').drop('id', axis=1)

NEW Solution 2: Can you simply merge on the index? If not go with solution #1.

df = pd.merge(df2, df1['value'], how ='left', left_index=True, right_index=True)

output:

    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

OLD Solution: Through a combination of pd.merge, pd.melt and pd.concat, I found a solution, although I wonder if there is a shorter way (probably):

df_A_B = pd.merge(df2[['type_A']], df2[['type_B']], how='left', left_index=True, right_index=True) \
           .melt(var_name = 'id_type', value_name='id')
df_C = pd.concat([df2[['type_C']]] * 2).reset_index(drop=True)
df_A_B_C = pd.merge(df_A_B, df_C, how='left', left_index=True, right_index=True)
df3 = pd.merge(df_A_B_C, df1, how='left', on=['id_type', 'id']).dropna().drop(['id_type', 'id'], axis=1)
df4 = pd.merge(df2, df3, how='left', on=['type_C'])
df4

output:

    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
user_63667700
Related