Mapping columns from one dataframe to another to create a new column [duplicate]

df.merge

out = (df1.merge(df2, left_on='store', right_on='store_code')          .reindex(columns=['id', 'store', 'address', 'warehouse']))print(out)   id  store address warehouse0   1    100     xyz      Land1   2    200     qwe       Sea2   3    300     asd      Land3   4    400     zxc      Land4   5    500     bnm       Sea

pd.concat + df.sort_values

u = df1.sort_values('store')v = df2.sort_values('store_code')[['warehouse']].reset_index(drop=1)out = pd.concat([u, v], 1)print(out)   id  store address warehouse0   1    100     xyz      Land1   2    200     qwe       Sea2   3    300     asd      Land3   4    400     zxc      Land4   5    500     bnm       Sea    

The first sort call is redundant assuming your dataframe is already sorted on store, in which case you may remove it.

df.replace/df.map

s = df1.store.replace(df2.set_index('store_code')['warehouse'])print(s) 0    Land1     Sea2    Land3    Land4     Seadf1['warehouse'] = sprint(df1)   id  store address warehouse0   1    100     xyz      Land1   2    200     qwe       Sea2   3    300     asd      Land3   4    400     zxc      Land4   5    500     bnm       Sea

Alternatively, create a mapping explicitly. This works if you want to use it later.

mapping = dict(df2[['store_code', 'warehouse']].values)df1['warehouse'] = df1.store.map(mapping)print(df1)   id  store address warehouse0   1    100     xyz      Land1   2    200     qwe       Sea2   3    300     asd      Land3   4    400     zxc      Land4   5    500     bnm       Sea

Use map or join:

df1['warehouse'] = df1['store'].map(df2.set_index('store_code')['warehouse'])print (df1)   id  store address warehouse0   1    100     xyz      Land1   2    200     qwe       Sea2   3    300     asd      Land3   4    400     zxc      Land4   5    500     bnm       Sea

df1 = df1.join(df2.set_index('store_code'), on=['store']).drop('serialNo', 1)print (df1)   id  store address warehouse0   1    100     xyz      Land1   2    200     qwe       Sea2   3    300     asd      Land3   4    400     zxc      Land4   5    500     bnm       Sea