I have two dataframes, each with a different index, and I'd like to merge the 'hop' column from df2 into df1 based on the 'src' column in df1 and the index in df2.
df1
src dest val
0 1 2 X
1 1 2 Y
2 1 3 Z
3 1 4 X
4 2 2 X
5 3 2 Y
6 4 3 Z
7 4 1 X
df1
id hop
1 3
2 5
3 2
4 3
i.e. the resulting dataframe needs to be merged as such:
df3
src dest val hop
0 1 2 X 3
1 1 2 Y 3
2 1 3 Z 3
3 1 4 X 3
4 2 2 X 5
5 3 2 Y 2
6 4 3 Z 3
7 4 1 X 3
I think it's probably a pd.merge() or pd.join() operation but I'm having a spot of bother with it :(