Most efficient way to determine overlapping timeseries in Python
<p>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:</p>

<p><strong>Time Series 1</strong></p>

<pre><code>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
</code></pre>

<p><strong>Time Series 2</strong></p>

<pre><code>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
</code></pre>

<p>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? </p>

<p><strong>Example</strong></p>

<p>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:</p>

<ul>
<li>11:50:10.000479 - 11:50:15.000234 (both have a value of 0.5) <strong>4.999755 seconds</strong></li>
<li>11:50:37.000199 - 11:50:49.000295 (both have a value of 0.5) <strong>12.000096 seconds</strong></li>
<li>11:50:53.000424 - 11:50:53.000982 (both have a value of 0.75) <strong>0.000558 seconds</strong></li>
<li>11:50:55.000792 - 11:50:55.000904 (both have a value of 0.25) <strong>0.000112 seconds</strong></li>
</ul>

<p>The result (4.999755+12.000096+0.000558+0.000112) / 49.999907 = <strong>34%</strong></p>

<p>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.</p>

<p><strong><em>setup</em></strong><br>
create 2 time series</p>

<pre><code>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)
</code></pre>

<hr>

<p><strong><em>TL;DR</em></strong></p>

<pre><code>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
</code></pre>

<hr>

<p><strong><em>explanation</em></strong></p>

<p><strong><em>build base <code>pd.DataFrame</code> <code>df</code></em></strong>  </p>

<ul>
<li>use <code>pd.concat</code> to align indexes</li>
<li>use <code>ffill</code> to let values propagate forward</li>
<li>use <code>dropna</code> to get rid of values of one series prior to the other starting</li>
</ul>

<hr>

<pre><code>df = pd.concat([s1, s2], axis=1).ffill().dropna()
df
</code></pre>

<p><a href="https://i.stack.imgur.com/Kkebu.png"><img src="https://i.stack.imgur.com/Kkebu.png" alt="enter image description here"></a></p>

<p><strong><em>calculate <code>'duration'</code></em></strong><br>
from current time stamp to next</p>

<pre><code>df['duration'] = df.index.to_series().diff().shift(-1).fillna(0)
df
</code></pre>

<p><a href="https://i.stack.imgur.com/1ZkA5.png"><img src="https://i.stack.imgur.com/1ZkA5.png" alt="enter image description here"></a></p>

<p><strong><em>calculate overlap</em></strong>  </p>

<ul>
<li><code>df.s1.eq(df.s2)</code> gives boolean series of when <code>s1</code> overlaps with <code>s2</code></li>
<li>use <code>groupby</code> above boolean series to aggregate total duration when <code>True</code> and <code>False</code></li>
</ul>

<hr>

<pre><code>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]
</code></pre>

<p><strong><em>percentage of time with same value</em></strong></p>

<pre><code>overlap.div(overlap.sum())

False    0.666657
True     0.333343
Name: duration, dtype: float64
</code></pre>

python performance pandas vector time-series