Pandas: align two time series given an "overlapping" region?

user_11198081 0
Viewed 172

I have two time-series, A and B. The time series have different lengths and different dates and different frequencies.

The core issue is that I want to find the dates between the two time series that overlap.

This is easy enough to do, but there's a twist.

Imagine time series A has a value on 01/01/2020 and series B has a value on 02/01/2020. These are only one day apart, and UNLESS there's a better candidate (e.g. if B ALSO has a value on 01/01/02020), I want to just include this one.

So, really the twist consists of a "lag" of, say, 5 days. Then what I want to do is the following:

  1. Consider all date in time series A.
  2. For each such date, find the corresponding date in time series B.
  3. For each date for which a match in B was NOT found, set a lag = 1 day, and search in B again.
  4. Repeat step 3 with lag = 2, 3, ..., 5 days.
  5. Return a new times series C, which consists of the dates of A and the values of B, found by matching using the above procedure.
pandas datetime python
1 Answers

You can achieve this by, at step 2, finding the nearest date in B using a simple min

from random import random as r
import random
random.seed(123456)

ts_a = pd.Series([pd.Timestamp(f"{d+1}-jan-2021") for d in range(5)])

base = pd.Timestamp(f"01-jan-2021")
one_day = pd.Timedelta("1D")
ts_b = pd.Series([base + int(10*r())*one_day for _ in range(5)])

print(ts_a)
print(ts_b)

for t in ts_a:
    deltas = abs(ts_b - t)
    index_closest = deltas.idxmin()
    print(f"The closest day to {t} in ts_b is {ts_b.iloc[index_closest]}")

0   2021-01-01
1   2021-01-02
2   2021-01-03
3   2021-01-04
4   2021-01-05
dtype: datetime64[ns]
0   2021-01-09
1   2021-01-08
2   2021-01-01
3   2021-01-02
4   2021-01-01
dtype: datetime64[ns]
The closest day to 2021-01-01 00:00:00 in ts_b is 2021-01-01 00:00:00
The closest day to 2021-01-02 00:00:00 in ts_b is 2021-01-02 00:00:00
The closest day to 2021-01-03 00:00:00 in ts_b is 2021-01-02 00:00:00
The closest day to 2021-01-04 00:00:00 in ts_b is 2021-01-02 00:00:00
The closest day to 2021-01-05 00:00:00 in ts_b is 2021-01-08 00:00:00
user_88995650
Related