Pandas DataFrame Groupby Two Columns And Get First And Last

I have a DataFrame Like following. df = pd.DataFrame({'id' : [1,1,2,3,2], 'value' : ['a','b','a','a','c'], 'Time' : ['6/Nov/2012 23:59:59 -0600','6/Nov/2012 00:00:

Solution 1:

First ensure you're column is a proper datetime column:

In [11]: df['Time'] = pd.to_datetime(df['Time'])

Now, you can do the groupby and use agg with the first and last groupby methods:

In [12]: g = df.groupby(['id', 'value'])

In [13]: res = g['Time'].agg({'first': 'first', 'last': 'last'})

In [14]: res = g['Time'].agg({'enter': 'first', 'exit': 'last'})

In [15]: res['time_diff'] = res['exit'] - res['enter']

In [16]: res
Out[16]:
                        exit               enter  time_diff
id value
1  a     2012-11-06 23:59:59 2012-11-06 23:59:59     0 days
   b     2012-11-06 00:00:05 2012-11-06 00:00:05     0 days
2  a     2012-11-07 00:00:09 2012-11-07 00:00:09     0 days
   c     2012-11-27 00:00:17 2012-11-27 00:00:17     0 days
3  a     2012-11-27 00:00:13 2012-11-27 00:00:13     0 days

Note: this is a bit of a boring example since there is only one item in each group...