Complex merge of two DataFrames

user_15097160 0
Viewed 66

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 ?

pandas dataframe merge python
3 Answers

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
user_98406370

Something like

out = df1.T.replace(df2.T).astype('str').radd(df1.T+' ').T
Out[317]: 
                        top1      top2      top3
693541495124446625  US 939.0  GB 932.0  CN 806.0
912819499544441670  US 992.0  CN 981.0  TW 796.0
user_79645270

First, turn the 2nd dataframe into a dictionary of dictionaries:

df2_dict = {i:None for i in df2.index}

for key in df2_dict:
    df2_dict[key] = {col: df2.loc[key, col] for col in df2.columns}

then you can just create a new df with the same rows and cols as ds1, and iterate over its them:

df3 = pd.DataFrame(index=df1.index,
                   columns=df1.columns)

for i in df3.index:
    for col in df3.columns:
        df3.loc[i, col] = df1.loc[i, col] + ' ' + str(df2_dict[i][df1.loc[i, col]])
user_62155840
Related