Merging two dataframes into one on a specific column-CodePudding

I have two separate dataframes and i would like to merge them based on a specific column. I have tried to concat the two but it just places the dataframes on top of each other. and i have tried pd.merge but it duplicates the other columns

Here is what i have...

I want something similar to this where the dates are merged and the symbols share the date.

Here is some dummy code if you'd like to implement an example

df_msft = [['2020-1-1', 10], ['2020-1-2', 15], ['2020-1-3', 14]]
df1 = pd.DataFrame(df_msft , columns = ['datetime', 'price'])

df_aapl = [['2020-1-1', 10], ['2020-1-2', 15], ['2020-1-3', 14]]
df2 = pd.DataFrame(df_aapl , columns = ['datetime', 'price'])

CodePudding user response：

You can use pd.concat:

out = pd.concat([df1, df2], keys=['MSFT', 'AAPL']).droplevel(1) \
        .rename_axis('symbol').set_index('datetime', append=True) \
        .swaplevel().sort_index()
print(out)

# Output
                 price
datetime symbol       
2020-1-1 AAPL       10
         MSFT       10
2020-1-2 AAPL       15
         MSFT       15
2020-1-3 AAPL       14
         MSFT       14

Export to excel:

out.to_excel('output.xlsx', merge_cells=True)