Pandas Python - Finding Time Series Not Covered
Question: Hoping someone can help me out with this one because I don't even know where
to start.

Given a data frame that contains a series of start and end times, such as:

    
    
    Order   Start Time              End Time
    1       2016-08-18 09:30:00.000 2016-08-18 09:30:05.000
    1       2016-08-18 09:30:00.005 2016-08-18 09:30:25.001
    1       2016-08-18 09:30:30.001 2016-08-18 09:30:56.002
    1       2016-08-18 09:30:40.003 2016-08-18 09:31:05.003
    1       2016-08-18 11:30:45.000 2016-08-18 13:31:05.000
    

For each order id, I am looking to find a list of time periods that are not
covered by any of the ranges between the earliest start time and latest end
time

So in the example above, I would be looking for

    
    
    2016-08-18 09:30:05.000 to 2016-08-18 09:30:00.005 (the time lag between the first and second rows)
    2016-08-18 09:30:25.001 to 2016-08-18 09:30:30.001 (the time lag between the second and third rows)
    

and

    
    
    2016-08-18 09:31:05.003 to 2016-08-18 11:30:45.000 (the time period between 4 and 5)
    

There is overlap between the 3 and 4 rows, so they wouldn't count

**A few things to consider (additional color):**

Each record indicates an outstanding order placed at (for example) one of the
stock exchanges. Therefore, I can have orders open at Nasdaq and NYSE at the
same time. I also can have a short duration order at Nasdaq and a long one at
NYSE starting at the same time.

That would look as following:

    
    
    Order   Start Time              End Time
    1       2016-08-18 09:30:00.000 2016-08-18 09:30:05.000  (NYSE)
    1       2016-08-18 09:30:00.001 2016-08-18 09:30:00.002  (NASDAQ)
    

I am trying to figure out when we are doing nothing at all, and I have no live
orders on any exchanges.

I have zero idea where to even start on this..any ideas would be appreciated


Answer: ### Setup

    
    
    from StringIO import StringIO
    import pandas as pd
    
    text = """Order   Start Time               End Time
    1       2016-08-18 09:30:00.000  2016-08-18 09:30:05.000
    1       2016-08-18 09:30:00.005  2016-08-18 09:30:25.001
    1       2016-08-18 09:30:30.001  2016-08-18 09:30:56.002
    1       2016-08-18 09:30:40.003  2016-08-18 09:31:05.003
    1       2016-08-18 11:30:45.000  2016-08-18 13:31:05.000
    2       2016-08-18 09:30:00.000  2016-08-18 09:30:05.000
    2       2016-08-18 09:30:00.005  2016-08-18 09:30:25.001
    2       2016-08-18 09:30:30.001  2016-08-18 09:30:56.002
    2       2016-08-18 09:30:40.003  2016-08-18 09:31:05.003
    2       2016-08-18 11:30:45.000  2016-08-18 13:31:05.000"""
    
    df = pd.read_csv(StringIO(text), sep='\s{2,}', engine='python', parse_dates=[1, 2])
    

### Solution

    
    
    def find_gaps(df, start_text='Start Time', end_text='End Time'):
        # rearrange stuff to get all times and a tracker
        # in single columns.
        cols = [start_text, end_text]
        df = df.reset_index()
        df1 = df[cols].stack().reset_index(-1)
        df1.columns = ['edge', 'time']
        df1['edge'] = df1['edge'].eq(start_text).mul(2).sub(1)
    
        # sort by ascending time, then descending edge
        # (starts before ends if equal time)
        # this will ensure we avoid zero length gaps.
        df1 = df1.sort_values(['time', 'edge'], ascending=[True, False])
    
        # we identify gaps when we've reached a number
        # of ends equal to number of starts.
        # we'll track that with cumsum, when cumsum is
        # zero, we've found a gap
        # last position should always be zero and is not a gap.
        # So I remove it.
        track = df1['edge'].cumsum().iloc[:-1]
    
        gap_starts = track.index[track == 0]
        gaps = df.ix[gap_starts]
        gaps[start_text] = gaps[end_text]
        gaps[end_text] = df.shift(-1).ix[gap_starts, start_text]
    
        return gaps
    
    df.set_index('Order').groupby(level=0).apply(find_gaps)
    

[![enter image description
here](http://i.stack.imgur.com/9Gyyl.png)](http://i.stack.imgur.com/9Gyyl.png)

