Clash Royale CLAN TAG

Subtle issue with joining/merging dataframes with the same column names

So I have a main set of data that looks like this:

value_num code value_letter
1 CDX A
2 DEF B
3 RPQ C
4 EEE D
5 FFX E
6 TRE F


And two other tables which we'll call map1 and map2

song album_code song_code
Song1 CDX GIB
Song2 DEF FRE
Song3 RPQ SSS

song album_code song_code
Song4 REA EEE
Song5 VEY FFX
Song6 LFM TRE


I want to join the main table with map1 where album_code is joined on code. Then I want to join map2 on this new table where song_code is joined on code.

Ideally the final result looks like this:

value_num code value_letter song album_code song_code
1 CDX A Song1 CDX GIB
2 DEF B Song2 DEF FRE
3 RPQ C Song3 RPQ SSS
4 EEE D Song4 REA EEE
5 FFX E Song5 VEY FFX
6 TRE F Song6 LFM TRE


Instead, the output has 9 total column names: value_num, code, value_letter, song_x, album_code_x, song_code_x, song_y, album_code_y, and song_code_y. Values from map1 are under the x columns and song2 values are under the y.

I've tried several different approaches with merges and joins. I don't care if the solution is a complex workaround, but it can't be a reformat of this data.

1 Answer
1

Doing two merge with combine_first

merge

r1=df.merge(df1,left_on='code',right_on='album_code',how='left')
r2=df.merge(df2,left_on='code',right_on='song_code',how='left')
r1.combine_first(r2)
Out[547]:
value_num code value_letter song album_code song_code
0 1 CDX A Song1 CDX GIB
1 2 DEF B Song2 DEF FRE
2 3 RPQ C Song3 RPQ SSS
3 4 EEE D Song4 REA EEE
4 5 FFX E Song5 VEY FFX
5 6 TRE F Song6 LFM TRE


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.