Complex merge of two DataFrames

Question

I have two DataFrames:

df1:

                     top1 top2 top3
693541495124446625    US   GB   CN
912819499544441670    US   CN   TW

df2:

                         US   GB    CN    TW  \ ...
                                                                    
693541495124446625  939.00 932.00 806.00 789.00 ...
912819499544441670  992.00 646.00 981.00 796.00 ...

How can I merge or iterate over two Dataframes in order to get the folowing result:

                       top1          top2          top3
693541495124446625    'US 939.00'  'GB 932.00'   'CN 806.00'
912819499544441670    'US 992.00'  'CN 981.00'   'TW 796.00'

I know I can iterate taking df1 values and put that value in df2 as column [loc]ation via several for loops, but are there any optimized solution ?

Answer 1

You can try this with df.replace

u = df2.astype(str).radd(df2.columns+' ')
out = df1.T.replace(u.T).T

Or:

u = df2.astype(str).radd(df2.columns+' ')
df1.T.replace(u.to_dict('index')).T

---

print(out)
                        top1      top2      top3
693541495124446625  US 939.0  GB 932.0  CN 806.0
912819499544441670  US 992.0  CN 981.0  TW 796.0