Mapping data from 2 dataframes in pandas

Question

I have two dataframes which I need to map and final output should look like this.

Input 1

LPAR    DC
A       LA
B       NY
C       CT
D       VA
E       WO

Input 2

LPAR    PROD
A       Microsoft
A       Symantec
A       Vmware
B       Compuware
C       BMC
B       CA

Final Output

LPAR    DC  PROD
A       LA  Microsoft
A       LA  Symantec
A       LA  Vmware
B       NY  Compuware
B       NY  CA
C       CT  BMC
D       VA  
E       WO

really similar to this: http://stackoverflow.com/questions/25493625/vlookup-in-pandas-using-join basically you can `merge` or `map` to do this. e.g. `input1.merge(input2, on='LPAR', how='left')` or `input1['PROD'] = input1['LPAR'].map(input2.set_index('LPAR')['PROD'], na_action='ignore').fillna('')` — EdChum, Dec 20 '16 at 11:45

score 2 · Accepted Answer · answered Dec 20 '16 at 11:47

You can merge two DataFrames and fillna with empty string

dict1 = {'LPAR': ['A', 'B', 'C', 'D', 'E'], 
            'DC': ['LA', 'NY', 'CT', 'VA', 'WO']}
df1 = pd.DataFrame(dict1)

dict2 = {'LPAR': ['A', 'A', 'A', 'B', 'B', 'C'], 
             'PROD': ['Microsoft', 'Symantec', 'Vmware', 'Compuware', 'CA', 'BMC']}
df2 = pd.DataFrame(dict2)

df3 = df1.merge(df2, on='LPAR', how='outer').fillna('')

Mapping data from 2 dataframes in pandas

1 Answers1