Joining Pandas dataframes in Python with the same index multiple times

Question

I have two dataframes:

>>>import import pandas as pd
>>>df1:

                 DD_PRICE
DATE                         
2013-09-19 20:55:00  3.516
2013-09-19 20:55:00  3.516
2013-09-19 20:55:00  3.516
2013-09-19 20:55:00 -4.476

>>>df2:
                 CAPI_PRICE
DATE                         
2013-09-19 20:55:00  3.516
2013-09-19 20:55:00  3.516
2013-09-19 20:55:00  3.516
2013-09-19 20:55:00 -4.476

Now when I run:

 joint_df = df1.join((df2), how='outer')

joint_df looks like this:

                  DD_PRICE  CAPI_PRICE
DATE                                   
2013-09-19 20:55:00  3.516  3.516
2013-09-19 20:55:00  3.516  3.516
2013-09-19 20:55:00  3.516  3.516
2013-09-19 20:55:00  3.516 -4.476
2013-09-19 20:55:00  3.516  3.516
2013-09-19 20:55:00  3.516  3.516
2013-09-19 20:55:00  3.516  3.516
2013-09-19 20:55:00  3.516 -4.476
2013-09-19 20:55:00  3.516  3.516
2013-09-19 20:55:00  3.516  3.516
2013-09-19 20:55:00  3.516  3.516
2013-09-19 20:55:00  3.516 -4.476
2013-09-19 20:55:00 -4.476  3.516
2013-09-19 20:55:00 -4.476  3.516
2013-09-19 20:55:00 -4.476  3.516
2013-09-19 20:55:00 -4.476 -4.476

This isnt what I want...

Ideally I want joint_df to look like this:

                  DD_PRICE  CAPI_PRICE
DATE                                   
2013-09-19 20:55:00  3.516  3.516
2013-09-19 20:55:00  3.516  3.516
2013-09-19 20:55:00  3.516  3.516
2013-09-19 20:55:00 -4.476 -4.476

How do I get this done? Tried different combos of merge and join parameters but I can't get it to work!

You have duplicate index. If the order of index in both dataframes always same, use df1['CAPI_PRICE'] = df2['CAPI_PRICE'].values? — Zero
– Zero, Commented Oct 4, 2017 at 12:23

jezrael · Accepted Answer · 2017-10-04 12:28:41Z

1

It seems you can use concat only - by default there is outer join:

df = pd.concat([df1, df2], axis=1)
print (df)
                     DD_PRICE  CAPI_PRICE
DATE                                     
2013-09-19 20:55:00     3.516       3.516
2013-09-19 20:55:00     3.516       3.516
2013-09-19 20:55:00     3.516       3.516
2013-09-19 20:55:00    -4.476      -4.476

answered Oct 4, 2017 at 12:28

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ioannis Nasios · Accepted Answer · 2017-10-04 12:29:21Z

0

You could try to just add a new column on first data frame by copying it from second data frame like this:

df1['CAPI_PRICE']=df2['CAPI_PRICE']

answered Oct 4, 2017 at 12:29

Ioannis Nasios

8,5474 gold badges41 silver badges59 bronze badges

Collectives™ on Stack Overflow

Joining Pandas dataframes in Python with the same index multiple times

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related