Most efficient way to determine overlapping timeseries in Python
Question: I am trying to determine what percentage of the time that two time series
overlap using python's pandas library. The data is nonsynchronous so the times
for each data point do not line up. Here is an example:

**Time Series 1**

    
    
    2016-10-05 11:50:02.000734    0.50
    2016-10-05 11:50:03.000033    0.25
    2016-10-05 11:50:10.000479    0.50
    2016-10-05 11:50:15.000234    0.25
    2016-10-05 11:50:37.000199    0.50
    2016-10-05 11:50:49.000401    0.50
    2016-10-05 11:50:51.000362    0.25
    2016-10-05 11:50:53.000424    0.75
    2016-10-05 11:50:53.000982    0.25
    2016-10-05 11:50:58.000606    0.75
    

**Time Series 2**

    
    
    2016-10-05 11:50:07.000537    0.50
    2016-10-05 11:50:11.000994    0.50
    2016-10-05 11:50:19.000181    0.50
    2016-10-05 11:50:35.000578    0.50
    2016-10-05 11:50:46.000761    0.50
    2016-10-05 11:50:49.000295    0.75
    2016-10-05 11:50:51.000835    0.75
    2016-10-05 11:50:55.000792    0.25
    2016-10-05 11:50:55.000904    0.75
    2016-10-05 11:50:57.000444    0.75
    

Assuming the series holds its value until the next change what is the most
efficient way to determine the percentage of time that they have the same
value?

**Example**

Lets calculate the time that these series overlap starting at 11:50:07.000537
and ending at 2016-10-05 11:50:57.000444 0.75 since we have data for both
series for that period. Time that there is overlap:

  * 11:50:10.000479 - 11:50:15.000234 (both have a value of 0.5) **4.999755 seconds**
  * 11:50:37.000199 - 11:50:49.000295 (both have a value of 0.5) **12.000096 seconds**
  * 11:50:53.000424 - 11:50:53.000982 (both have a value of 0.75) **0.000558 seconds**
  * 11:50:55.000792 - 11:50:55.000904 (both have a value of 0.25) **0.000112 seconds**

The result (4.999755+12.000096+0.000558+0.000112) / 49.999907 = **34%**

One of the issues is my actual timeseries has much more data such as 1000 -
10000 observations and I need to run many more pairs. I thought about forward
filling a series and then simply comparing the rows and dividing the total
number of matches over the total number of rows but I do not think this would
be very efficient.


Answer: **_setup_**  
create 2 time series

    
    
    from StringIO import StringIO
    import pandas as pd
    
    
    txt1 = """2016-10-05 11:50:02.000734    0.50
    2016-10-05 11:50:03.000033    0.25
    2016-10-05 11:50:10.000479    0.50
    2016-10-05 11:50:15.000234    0.25
    2016-10-05 11:50:37.000199    0.50
    2016-10-05 11:50:49.000401    0.50
    2016-10-05 11:50:51.000362    0.25
    2016-10-05 11:50:53.000424    0.75
    2016-10-05 11:50:53.000982    0.25
    2016-10-05 11:50:58.000606    0.75"""
    
    s1 = pd.read_csv(StringIO(txt1), sep='\s{2,}', engine='python',
                     parse_dates=[0], index_col=0, header=None,
                     squeeze=True).rename('s1').rename_axis(None)
    
    txt2 = """2016-10-05 11:50:07.000537    0.50
    2016-10-05 11:50:11.000994    0.50
    2016-10-05 11:50:19.000181    0.50
    2016-10-05 11:50:35.000578    0.50
    2016-10-05 11:50:46.000761    0.50
    2016-10-05 11:50:49.000295    0.75
    2016-10-05 11:50:51.000835    0.75
    2016-10-05 11:50:55.000792    0.25
    2016-10-05 11:50:55.000904    0.75
    2016-10-05 11:50:57.000444    0.75"""
    
    s2 = pd.read_csv(StringIO(txt2), sep='\s{2,}', engine='python',
                     parse_dates=[0], index_col=0, header=None,
                     squeeze=True).rename('s2').rename_axis(None)
    

* * *

**_TL;DR_**

    
    
    df = pd.concat([s1, s2], axis=1).ffill().dropna()
    overlap = df.index.to_series().diff().shift(-1) \
                .fillna(0).groupby(df.s1.eq(df.s2)).sum()
    overlap.div(overlap.sum())
    
    False    0.666657
    True     0.333343
    Name: duration, dtype: float64
    

* * *

**_explanation_**

**_build base`pd.DataFrame` `df`_**

  * use `pd.concat` to align indexes
  * use `ffill` to let values propagate forward
  * use `dropna` to get rid of values of one series prior to the other starting

* * *
    
    
    df = pd.concat([s1, s2], axis=1).ffill().dropna()
    df
    

[![enter image description
here](https://i.stack.imgur.com/Kkebu.png)](https://i.stack.imgur.com/Kkebu.png)

**_calculate`'duration'`_**  
from current time stamp to next

    
    
    df['duration'] = df.index.to_series().diff().shift(-1).fillna(0)
    df
    

[![enter image description
here](https://i.stack.imgur.com/1ZkA5.png)](https://i.stack.imgur.com/1ZkA5.png)

**_calculate overlap_**

  * `df.s1.eq(df.s2)` gives boolean series of when `s1` overlaps with `s2`
  * use `groupby` above boolean series to aggregate total duration when `True` and `False`

* * *
    
    
    overlap = df.groupby(df.s1.eq(df.s2)).duration.sum()
    overlap
    
    False   00:00:33.999548
    True    00:00:17.000521
    Name: duration, dtype: timedelta64[ns]
    

**_percentage of time with same value_**

    
    
    overlap.div(overlap.sum())
    
    False    0.666657
    True     0.333343
    Name: duration, dtype: float64
    

