Conditional merging of two dataframes using pandas

Question

I have two dataframes- df1 having columns like ISIN, Name, Weight and df2 having columns like Short Name, ISIN.

df1 =

ISIN    Name            Weight
        Enbridge Inc    0.1
        UDR Inc         1.1
        Tyson Foods Inc 1.9

and df2=

Short Name            ISIN
Enbridge Inc.         bvefj154
UDR Group             iuhb38g7
Tyson Foods Pvt Ltd.  hruidf12

I have developed a fuzzy logic which will match Name and Short Name from df1 and df2. so it using the logic it will know that Enbridge Inc from both dataframes are one and the same. and for UDR Group and UDR Inc they are also same since names are matching, not all but almost all.

I am looking for a way to populate the ISIN column in df1 based on the logic that if the names match(Enbridge Inc matches in all) then select the ISIN for the respective Short Name from df2 and add it to ISIN column in df1 wherever relevant name is present.

So the output that I look forward to would look like this: df1=

ISIN            Name            Weight
bvefj154        Enbridge Inc    0.1
iuhb38g7        UDR Inc         1.1
hruidf12        Tyson Foods Inc 1.9

using pandas's merge function I tried achieving the task but got a an error like:

KeywordError:'Name'

here's the code for the same.

import pandas as pd
df1 = pd.merge(df1, df2, on=['Name', '% Weight'], how='right')

How can I do this? Please help.

EDIT: Here is the code for fuzzy logic matching using fuzzywuzzy module

def fuzzy_merge(df_1, df_2, key1, key2, threshold=90, limit=1):
    """
    :param df_1: the left table to join
    :param df_2: the right table to join
    :param key1: key column of the left table
    :param key2: key column of the right table
    :param threshold: how close the matches should be to return a match, based on Levenshtein distance
    :param limit: the amount of matches that will get returned, these are sorted high to low
    :return: dataframe with boths keys and matches
    """
    s = df_2[key2].tolist()

    m = df_1[key1].apply(lambda x: process.extract(x, s, limit=limit))
    df_1['matches'] = m

    m2 = df_1['matches'].apply(lambda x: ', '.join([i[0] for i in x if i[1] >= threshold]))
    df_1['matches'] = m2

    print(df_1)
    df_1.to_csv('fuzzy-1390-match.csv')
    #return df_1


fuzzy_merge(df1, df2, 'Name', 'Short Name', threshold=90)

Output:

5,Enbridge Inc Flt 07/15/80 Sr:20-A,0.0127,ENBRIDGE INC
6,Enbridge Inc. 6.25% 03/01/78,0.0122,ENBRIDGE INC
7,Emera 6.75% 6/15/76-26,0.0113,MERA
8,Scentre Group Trust 2 Flt 09/24/80 Sr:144A,0.011,SCENTRE GROUP
9,Credit Suisse Group AG 7.5 Perp,0.0106,
10,Aegon Funding Corp Ii 5.100% 12/15/49,0.0101,
11,Dte Energy Co 5.250% 12/01/77 Sr:E,0.01,DTE ENERGY CO
12,Dai-Ichi Life Insurance 4%,0.0099,
13,Southern Co Flt 09/15/51 Sr:21-A,0.0098,SOUTHERN CO

EDIT2:

This is the dataframe(df1 and df2): df1=

0             Transcanada Trust 5.875 08/15/76    0.0176
1              Bp Capital Markets Plc Flt Perp    0.0169
2               Transcanada Trust Flt 09/15/79    0.0169
3              Bp Capital Markets Plc Flt Perp    0.0155
4          Prudential Financial 5.375% 5/15/45    0.0150
5            Enbridge Inc Flt 07/15/80 Sr:20-A    0.0127
6                 Enbridge Inc. 6.25% 03/01/78    0.0122
7                       Emera 6.75% 6/15/76-26    0.0113
8   Scentre Group Trust 2 Flt 09/24/80 Sr:144A    0.0110
9              Credit Suisse Group AG 7.5 Perp    0.0106
10       Aegon Funding Corp Ii 5.100% 12/15/49    0.0101
11          Dte Energy Co 5.250% 12/01/77 Sr:E    0.0100
12                  Dai-Ichi Life Insurance 4%    0.0099
13            Southern Co Flt 09/15/51 Sr:21-A    0.0098
14         Prudential Financial 5.625% 6/15/43    0.0097
15         Southern Co 4.950% 01/30/80 Sr:2020    0.0093
16  Scentre Group Trust 2 Flt 09/24/80 Sr:144A    0.0093
17             Metlife Inc 9.25% 4/8/2038 144A    0.0089
18          American Intl Group 8.175% 5/15/58    0.0086
19               Southern Co Flt 01/15/51 Sr:B    0.0079

df2=

          Short Name          ISIN
0   ABU DHABI COMMER  AEA000201011
1   ABU DHABI NATION  AEA002401015
2   ABU DHABI NATION  AEA006101017
3   ADNOC DRILLING C  AEA007301012
4   ALPHA DHABI HOLD  AEA007601015
5      DUBAI ISLAMIC  AED000201015
6    EMAAR PROP PJSC  AEE000301011
7           ETISALAT  AEE000401019
8   EMIRATES NBD PJS  AEE000801010
9    INTL HOLDING CO  AEI000201014
10   FIRST ABU DHABI  AEN000101016
11  SCHLUMBERGER LTD  AN8068571086
12  ERSTE GROUP BANK  AT0000652011
13            OMV AG  AT0000743059
14        VERBUND AG  AT0000746409
15  ARISTOCRAT LEISU  AU000000ALL7
16  AUST AND NZ BANK  AU000000ANZ3
17      AFTERPAY LTD  AU000000APT1
18           ASX LTD  AU000000ASX7
19     BHP GROUP LTD  AU000000BHP4

your problem is that `Name` isn't present in both dfs. You should give merge `left_on` and `right_on`, in your case `Name` and `short_name`. But since you want to use a fuzzy merge you should do that first — Josh Friedlander, Dec 13 '21 at 13:26
@JoshFriedlander Yes I am doing the fuzzy match first and then I am doing the merge, however I am not sure what column names do I add on left_on and right_on. could you please provide an example? — technophile_3, Dec 13 '21 at 13:33
what does the fuzzy match look like? is it a function? what does it output? [here](https://stackoverflow.com/questions/13636848/is-it-possible-to-do-fuzzy-match-merge-with-python-pandas) is an example with fuzzy merge which might be applicable to you — Josh Friedlander, Dec 13 '21 at 13:39
@JoshFriedlander I have used the same code, adding the output and code in the edit — technophile_3, Dec 13 '21 at 13:46

score 1 · Accepted Answer · 2021-12-13T15:03:20.020

You can use split to get the result. I search if the first word in df1.Name is in df2.Short Name

import pandas as pd

df1 = pd.DataFrame({'ISIN': ['', '', ''], 'Name': ['Enbridge Inc', 'UDR Inc', 'Tyson Foods Inc'], 'Weight': ['0.1', '1.1', '1.9']})
df2 = pd.DataFrame({'Short Name': ['Enbridge Inc.', 'UDR Group', 'Tyson Foods Pvt Ltd.'], 'ISIN': ['bvefj154', 'iuhb38g7', 'hruidf12']})

def strMergeData(strColumnDf1):
    strColumnDf1 = strColumnDf1.split()[0]
    for strColumnDf2 in df2['Short Name']:
        if strColumnDf1 in strColumnDf2:
            return df2[df2['Short Name'] == strColumnDf2]['ISIN'].values[0]
            break
        else:
            pass
        
df1['ISIN'] = df1.apply(lambda x: strMergeData(x['Name']),axis=1)
print(df1)

Output :

       ISIN             Name Weight
0  bvefj154     Enbridge Inc    0.1
1  iuhb38g7          UDR Inc    1.1
2  hruidf12  Tyson Foods Inc    1.9

Demo

You find below the test of your example in the provided code. I just add TRANSCANADA in ALPHA DHABI HOLD to match some example.

import pandas as pd

df1 = pd.DataFrame({'ISIN': ['', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''],
                    'Name': ['Transcanada Trust 5.875 08/15/76',
                             'Bp Capital Markets Plc Flt Perp',
                             'Transcanada Trust Flt 09/15/79',
                             'Bp Capital Markets Plc Flt Perp',
                             'Prudential Financial 5.375% 5/15/45',
                             'Enbridge Inc Flt 07/15/80 Sr:20-A',
                             'Enbridge Inc. 6.25% 03/01/78',
                             'Emera 6.75% 6/15/76-26',
                             'Scentre Group Trust 2 Flt 09/24/80 Sr:144A',
                             'Credit Suisse Group AG 7.5 Perp',
                             'Aegon Funding Corp Ii 5.100% 12/15/49',
                             'Dte Energy Co 5.250% 12/01/77 Sr:E',
                             'Dai-Ichi Life Insurance 4%',
                             'Southern Co Flt 09/15/51 Sr:21-A',
                             'Prudential Financial 5.625% 6/15/43',
                             'Southern Co 4.950% 01/30/80 Sr:2020',
                             'Scentre Group Trust 2 Flt 09/24/80 Sr:144A',
                             'Metlife Inc 9.25% 4/8/2038 144A',
                             'American Intl Group 8.175% 5/15/58',
                             'Southern Co Flt 01/15/51 Sr:B',
                             19.5],
                    'Weight': [0.0176, 0.0169, 0.0169, 0.0155,0.0150,0.0127,0.0122,0.0113,0.0110,0.0106,0.0101,0.0100
                               ,0.0099,0.0098,0.0097,0.0093,0.0093,0.0089,0.0086,0.0079,0.0091]})

df2 = pd.DataFrame({'Short Name': ['ABU DHABI COMMER', 'ABU DHABI NATION', 'ABU DHABI NATION',
                                   'ADNOC DRILLING C','TRANSCANADA ALPHA DHABI HOLD','DUBAI ISLAMIC' ,
                                   'EMAAR PROP PJSC','ETISALAT','EMIRATES NBD PJS','INTL HOLDING CO' ,
                                   'FIRST ABU DHABI'  ,'SCHLUMBERGER LTD'  ,'ERSTE GROUP BANK'  ,'OMV AG',
                                   'VERBUND AG',  'ARISTOCRAT LEISU',  'AUST AND NZ BANK',  'AFTERPAY LTD',
                                   'ASX LTD',  'BHP GROUP LTD',19.5],
                    'ISIN': [ 'AEA000201011','AEA002401015','AEA006101017','AEA007301012','AEA007601015',
                              'AED000201015','AEE000301011','AEE000401019','AEE000801010','AEI000201014',
                              'AEN000101016','AN8068571086','AT0000652011','AT0000743059','AT0000746409',
                              'AU000000ALL7','AU000000ANZ3','AU000000APT1','AU000000ASX7','AU000000BHP4','FLOAT_TEST'] })

def strMergeData(strColumnDf1):
    strColumnDf1 = str(strColumnDf1).split()[0]
    for strColumnDf2 in df2['Short Name']:
        if str(strColumnDf1).upper() in str(strColumnDf2).upper():
            return df2[df2['Short Name'] == strColumnDf2]['ISIN'].values[0]
            break
        else:
            pass
        
df1['ISIN'] = df1.apply(lambda x: strMergeData(x['Name']),axis=1)
print(df1)

Output :

            ISIN                                        Name  Weight
0   AEA007601015            Transcanada Trust 5.875 08/15/76  0.0176
1           None             Bp Capital Markets Plc Flt Perp  0.0169
2   AEA007601015              Transcanada Trust Flt 09/15/79  0.0169
3           None             Bp Capital Markets Plc Flt Perp  0.0155
4           None         Prudential Financial 5.375% 5/15/45  0.0150
5           None           Enbridge Inc Flt 07/15/80 Sr:20-A  0.0127
6           None                Enbridge Inc. 6.25% 03/01/78  0.0122
7           None                      Emera 6.75% 6/15/76-26  0.0113
8           None  Scentre Group Trust 2 Flt 09/24/80 Sr:144A  0.0110
9           None             Credit Suisse Group AG 7.5 Perp  0.0106
10          None       Aegon Funding Corp Ii 5.100% 12/15/49  0.0101
11          None          Dte Energy Co 5.250% 12/01/77 Sr:E  0.0100
12          None                  Dai-Ichi Life Insurance 4%  0.0099
13          None            Southern Co Flt 09/15/51 Sr:21-A  0.0098
14          None         Prudential Financial 5.625% 6/15/43  0.0097
15          None         Southern Co 4.950% 01/30/80 Sr:2020  0.0093
16          None  Scentre Group Trust 2 Flt 09/24/80 Sr:144A  0.0093
17          None             Metlife Inc 9.25% 4/8/2038 144A  0.0089
18          None          American Intl Group 8.175% 5/15/58  0.0086
19          None               Southern Co Flt 01/15/51 Sr:B  0.0079
20    FLOAT_TEST                                        19.5  0.0091

Demo

I get this error TypeError: argument of type 'float' is not iterable on df1['ISIN'] = df1.apply(lambda x: strmerge(x['Name']), axis=1). df1 and df2 shown in the question are a minimal representation but in reality they are quite big — technophile_3, Dec 13 '21 at 14:00
checked. it works fine there. The dataframes shown above are minimally representated while in reality they are quite big and your code is throwing the error. — technophile_3, Dec 13 '21 at 14:07
If you don't give all the examples in your question, I can't know if there are floats that exist. Can you share an example of your full dataframe that i can check it. — , Dec 13 '21 at 14:14
I saw the two dataframe you added. I don't see where it can match between the two — , Dec 13 '21 at 14:39
Actually the df2 is quite big(in 1000s) and there's no way I can share the complete dataframe. maybe using the given dataframe you can try something like if strcolumn not in i: if it works at your end I can try at my end. — technophile_3, Dec 13 '21 at 14:42
AttributeError: 'float' object has no attribute 'upper' got this now. — technophile_3, Dec 13 '21 at 14:56
Hi There Achraf, by searching the first word I am able to get the output however for some of the names I am getting in correct ISINs — technophile_3, Dec 16 '21 at 10:18

Conditional merging of two dataframes using pandas

1 Answers1