CODEWITHSUNDEEP
pythonpandas
LemonSqueezy
LemonSqueezy

Reputation: 51

Merging two dataframes into one on a specific column

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...

Current Dataframe

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

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'])

Upvotes: 0

Views: 42

Answers (1)

Corralien
Corralien

Reputation: 120391

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)

enter image description here

Upvotes: 1

Related Questions