CODEWITHSUNDEEP
pythonpandastime-series
jpdus
jpdus

Reputation: 10199

Pandas: How to merge Time Series with complementary Dates?

I want to create a new Series wich contains the lowest of (low on this day)/(close on previous day). So first i filter the days for each condition:

a=low['SPY'][low['SPY']<close['SPY'].shift(1)]
b=close['SPY'].shift(1)[low['SPY']>=close['SPY'].shift(1)]

Now a and b both have "holes":

a:
2013-06-21 16:00:00    1577.70
2013-06-24 16:00:00    1560.33
2013-06-28 16:00:00    1601.06
2013-07-02 16:00:00    1606.77

b:
2013-06-25 16:00:00    1573.09
2013-06-26 16:00:00    1588.03
2013-06-27 16:00:00    1603.26
2013-07-01 16:00:00    1606.28

How to concatenate a and b so that i get back one time series with a correct index?

I tried pd.concat in all variations (or creating a series C with the orginial index and merging on it) but it always just appends and does not merge as expected.

Many thanks!

Upvotes: 3

Views: 1371

Answers (1)

Andy Hayden
Andy Hayden

Reputation: 375395

Why not just sort_index after doing the append/concat?

In [11]: a.append(b) #  equivalent to pd.concat([a, b])
Out[11]:
2013-06-21 16:00:00    1577.70
2013-06-24 16:00:00    1560.33
2013-06-28 16:00:00    1601.06
2013-07-02 16:00:00    1606.77
2013-06-25 16:00:00    1573.09
2013-06-26 16:00:00    1588.03
2013-06-27 16:00:00    1603.26
2013-07-01 16:00:00    1606.28
dtype: float64

In [12]: a.append(b).sort_index()
Out[12]:
2013-06-21 16:00:00    1577.70
2013-06-24 16:00:00    1560.33
2013-06-25 16:00:00    1573.09
2013-06-26 16:00:00    1588.03
2013-06-27 16:00:00    1603.26
2013-06-28 16:00:00    1601.06
2013-07-01 16:00:00    1606.28
2013-07-02 16:00:00    1606.77
dtype: float64

For efficiency pandas doesn't sort by default, and needs to be asked explicitly.

Upvotes: 2

Related Questions