My df1:
df1 = pd.DataFrame({'Col1' : ['A_B_C', 'A_B_C', 'A_B_C', 'D_E_F', 'D_E_F', 'G_H', 'A_B_C'],
'Col2' : ['red', 'red', 'red', 'ash', 'ash', 'green', 'red']})
df1
My df2:
df2 = pd.DataFrame({'ID_Number' : [100, 200, 300, 400, 500],
'Col1' : ['A_B_C', 'D_E_F','G_H', 'DD', 'WW'],
'Col2' : ['red', 'ash', 'green', 'black', 'sky']})
df2
I would like to match the df1 col1 value with the df2 col1 value. If the df1 col1 value exactly matches the df2 col1 value, then bring the corresponding ID_Number from df2.
My Desired Result would be :
ID_Number Col1 Col2
100 A_B_C red
100 A_B_C red
100 A_B_C red
200 D_E_F ash
200 D_E_F ash
300 G_H green
100 A_B_C red
I tried this way : My_desire_df = df2['Col1'].map(df1.set_index('Col1')['ID_Number']) My_desire_df
but no getting the desired result as below
My Desired Result would be :
ID_Number Col1 Col2
100 A_B_C red
100 A_B_C red
100 A_B_C red
200 D_E_F ash
200 D_E_F ash
300 G_H green
100 A_B_C red