Match One Table And Map Value To Other In Pandas Python

I have two pandas dataframes: df1: LT route_1 c2 PM/2 120 44 PM/52 110 49 PM/522 103 51 PM/522 103 51 PM/24 105 48 PM/536 109 67 PM/536 109 67 P

Solution 1:

I think map should work:

df1['route_1'] = df1['LT'].map(df2.set_index('LT')['W_ID'])

Unfortunately not:

InvalidIndexError: Reindexing only valid with uniquely valued Index objects

EDIT:

Problem is with duplicates in LT column. Solution is add helper column by cumcount for unique left join by merge:

df1['g'] = df1.groupby('LT').cumcount()
df2['g'] = df2.groupby('LT').cumcount()
df = pd.merge(df1, df2, on=['LT','g'], how='left')
print (df)
        LT  route_1   c2  g   W_ID
0     PM/2      120   44  0  120.0
1    PM/52      110   49  0  110.0
2   PM/522      103   51  0  103.0
3   PM/522      103   51  1  103.0
4    PM/24      105   48  0  105.0
5   PM/536      109   67  0  109.0
6   PM/536      109   67  1  109.0
7  PM/5356      112  144  0  112.0

df1['route_1'] = df['W_ID']
df1.drop('g', axis=1, inplace=True)
print (df1)
        LT  route_1   c2
0     PM/2    120.0   44
1    PM/52    110.0   49
2   PM/522    103.0   51
3   PM/522    103.0   51
4    PM/24    105.0   48
5   PM/536    109.0   67
6   PM/536    109.0   67
7  PM/5356    112.0  144

Similar solution:

df1['g'] = df1.groupby('LT').cumcount()
df2['g'] = df2.groupby('LT').cumcount()
df = pd.merge(df1, df2, on=['LT','g'], how='left')
       .drop(['g', 'route_1'], axis=1)
       .rename(columns={'W_ID':'route_1'})
       .reindex_axis(['LT', 'route_1', 'c2'], axis=1)
print (df)
        LT  route_1   c2
0     PM/2    120.0   44
1    PM/52    110.0   49
2   PM/522    103.0   51
3   PM/522    103.0   51
4    PM/24    105.0   48
5   PM/536    109.0   67
6   PM/536    109.0   67
7  PM/5356    112.0  144