python: group data within time series

Question

Given the following database:

import pandas as pd
from time import strptime

data = {'num': ['B', 'A', 'B', 'A', 'B', 'A', 'B', 'C', 'B', 'A', 'C', 'B'],
        'dir': ['IN', 'IN', 'PASS', 'PASS', 'PASS', 'PASS', 'OUT', 'IN', 'IN', 'OUT', 'OUT', 'OUT'],
        'empty': [True, False, True, False, True, False, True, False, False, False, False, False],
        'station': [100, 1, 20, 20, 14, 14, 5, 65, 89, 8, 66, 11],
        'time': [strptime('20180115 00:25', '%Y%m%d %H:%M'),
                 strptime('20180115 00:30', '%Y%m%d %H:%M'),
                 strptime('20180115 01:10', '%Y%m%d %H:%M'),
                 strptime('20180115 01:10', '%Y%m%d %H:%M'),
                 strptime('20180115 02:10', '%Y%m%d %H:%M'),
                 strptime('20180115 02:10', '%Y%m%d %H:%M'),
                 strptime('20180115 03:33', '%Y%m%d %H:%M'),
                 strptime('20180115 03:33', '%Y%m%d %H:%M'),
                 strptime('20180115 03:34', '%Y%m%d %H:%M'),
                 strptime('20180115 04:10', '%Y%m%d %H:%M'),
                 strptime('20180115 04:11', '%Y%m%d %H:%M'),
                 strptime('20180115 10:10', '%Y%m%d %H:%M')]}

df = pd.DataFrame.from_dict(data)

I have to generate a list of the following expected result:

   num  empty  station_from  station_to       time_from         time_to
0    B   True           100           5  20180115 00:25  20180115 03:33
1    A  False             1           8  20180115 00:30  20180115 04:10
2    C  False            65          66  20180115 03:33  20180115 04:11
3    B  False            89          11  20180115 03:34  20180115 10:10

Explanation: Those are railway cars where each car is identified by num (i.e. A, B, C) Those can be either charged or empty (column empty) The column dir indicates if we have picked in or dropped out that specific car. There can be a lot of other statuses (like PASS in this example) but we are only interested in the IN and OUT statuses The station column is where the event was occurred And the time column is when the event was occurred.

I have to group the data between each IN and OUT pairs (or show the track of each of them) and show where and when the activities was happening The difficulty is that this is not a "normal" group by, but a kind of hierarchical, time series based one (in a same day a car can be loaded and emptied several times).

I don't know if pandas is the best structure for that exercise but there are hundreds of millions of records in the DB and performance is critical. (we are preselecting the data based on a specific timeframe)

How to achieve the expected result?

user_1379678 · Answer

1) Filter all rows with 'IN'

2) Find first next 'OUT' for each 'IN' with matching num and empty

result = df[df.dir=='IN'][['num','empty','station','time']].rename({'station':'station_from','time':'time_from'},axis='columns')
result[['station_to','time_to']] = result.apply(lambda r: df[(df.index>r.name) & (df.num==r.num) & (df.empty==r.empty) & (df.dir=='OUT')][['station','time']].iloc[0,:], axis=1)

print(result)

  num  empty  station_from                           time_from  station_to  \
0   B   True           100  (2018, 1, 15, 0, 25, 0, 0, 15, -1)           5   
1   A  False             1  (2018, 1, 15, 0, 30, 0, 0, 15, -1)           8   
7   C  False            65  (2018, 1, 15, 3, 33, 0, 0, 15, -1)          66   
8   B  False            89  (2018, 1, 15, 3, 34, 0, 0, 15, -1)          11   

                               time_to  
0   (2018, 1, 15, 3, 33, 0, 0, 15, -1)  
1   (2018, 1, 15, 4, 10, 0, 0, 15, -1)  
7   (2018, 1, 15, 4, 11, 0, 0, 15, -1)  
8  (2018, 1, 15, 10, 10, 0, 0, 15, -1)

python: group data within time series

1 Answers