Joining Multiple Dataframes Together And Making Sure The Right Numbers Go To The Same Column

I have a few dataframes that all have a 'Dirty Price' and 'Factor' that I want to have in the same column. When I try to do this what happens when I join the second dataframe toget

Solution 1:

Since gs and nomura do not share the same combination of CUSIP and Counterpary, you can concat these two dataframes and then do the merge

df_concat = pd.concat([ gs[['CUSIP', 'temp_Counterparty','Dirty Price','Factor']] , 
                        nomura[['CUSIP', 'temp_Counterparty','Dirty Price','Factor']]
                      ], axis = 0)


df = df.merge(df_concat, how='left', on=['CUSIP', 'temp_Counterparty'])