Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to join two dataframes on datetime index autofill non matched rows with nan

Tags:

python

pandas

x_index=pd.date_range(dt.date(2010,1,1),dt.date(2010,1,5))
y_index=pd.date_range(dt.date(2010,1,2),dt.date(2010,1,6))
x = pd.DataFrame({"AAPL":[1,2,3,4,5]}, index=x_index)
y = pd.DataFrame({"GE": [1,2,3,4,5]}, index=y_index)

The result should be:

            AAPL GE
2010-01-01   1   nan
2010-01-02   2   1
2010-01-03   3   2
2010-01-04   4   3
2010-01-05   5   4
2010-01-06   nan     5
like image 897
zsljulius Avatar asked Mar 28 '14 14:03

zsljulius


1 Answers

As you don't have a common column you need to specify to use the indices of both dataframes and that you want to perform an 'outer' merge:

In [226]:

x.merge(y, how='outer', left_index=True, right_index=True)
Out[226]:
            AAPL  GE
2010-01-01     1 NaN
2010-01-02     2   1
2010-01-03     3   2
2010-01-04     4   3
2010-01-05     5   4
2010-01-06   NaN   5

[6 rows x 2 columns]
like image 159
EdChum Avatar answered Oct 30 '22 12:10

EdChum