CODEWITHSUNDEEP
pythonpandasdataframe
Peter Lucas
Peter Lucas

Reputation: 1991

Pandas: use map function to LOOKUP a value in another df

I'd like to use the map function to update values in df1 based on the looked up value in df2The lookup column is ISIN_CUSIP_CODE

df1 = [('ISIN_CUSIP_CODE', ['US68323ABL70', '9128284D9', '912828W89', 'CA135087J470','CA135087J470','912796QP7','US20030NCM11','US912810SD19','XS1851277969',]),
                ('Product', ['GOVT', 'GOVT', 'GOVT', 'GOVT', 'GOVT', 'GOVT', '', '', '',]),        
                 ]    
        df1 = pd.DataFrame.from_items(df1)
        print(df1)
df2 = [('ISIN_CUSIP_CODE', ['US20030NCM11', 'US912810SD19', 'XS1851277969', 'XS1391086987', 'CA064151BL66', 'CA13595ZZ661', ]),
                ('Product_MRD', ['CORP', 'GOVT', 'CORP', 'CORP','CORP','CORP',]),
                 ]    
        df2 = pd.DataFrame.from_items(df2)
        print(df2)

df1
  ISIN_CUSIP_CODE Product
0    US68323ABL70    GOVT
1       9128284D9    GOVT
2       912828W89    GOVT
3    CA135087J470    GOVT
4    CA135087J470    GOVT
5       912796QP7    GOVT
6    US20030NCM11        
7    US912810SD19        
8    XS1851277969        
 df2
  ISIN_CUSIP_CODE Product_MRD
0    US20030NCM11        CORP
1    US912810SD19        GOVT
2    XS1851277969        CORP
3    XS1391086987        CORP
4    CA064151BL66        CORP
5    CA13595ZZ661        CORP

My map function is not returning the looked up values in df2

df1['Product'].map(df2.set_index('ISIN_CUSIP_CODE')['Product_MRD'])
print(df1)

  ISIN_CUSIP_CODE Product
0    US68323ABL70    GOVT
1       9128284D9    GOVT
2       912828W89    GOVT
3    CA135087J470    GOVT
4    CA135087J470    GOVT
5       912796QP7    GOVT
6    US20030NCM11        
7    US912810SD19        
8    XS1851277969

Upvotes: 0

Views: 1272

Answers (2)

Mark Warburton
Mark Warburton

Reputation: 517

A purely pandas solution:

pd.concat([df1,df2.rename(columns = {'Product_MRD':'Product'})]).drop_duplicates(['ISIN_CUSIP_CODE'],keep='last').sort_values('ISIN_CUSIP_CODE')

No extra libraries required

Upvotes: 2

Federico Pucci
Federico Pucci

Reputation: 51

This is a simple solution using partial.

from functools import partial
def lookup(row, lookup_df):
    try:
        return lookup_df[lookup_df.ISIN_CUSIP_CODE == row['ISIN_CUSIP_CODE']].Product_MRD.values[0]
    except:
        return row['Product']
df1['ProductLooked'] = df1.apply(partial(lookup, lookup_df=df2), axis=1)

Upvotes: 1

Related Questions