Temporary association in Pandas

Question

I would like to do something like the SQL groupby or R aggregate operation in Pandas. I have a series of lines with irregular timestamps, I would like to create temporary bins and count the number of lines falling into each bit. I cannot figure out how to use resample for this.

String Examples

Time, Val

05.33, XYZ  
05.45, ABC  
07.13, DEF  

Result

05.00-06.00, 2  
06.00-07.00, 0  
07.00-08.00, 1

Answer 1

If you are indexing a different value, you can use the groupby operator at the timestamp.

In [1]:  dft = pd.DataFrame({'A' : ['spam', 'eggs', 'spam', 'eggs'] * 6,
                    'B' : np.random.randn(24),
                    'C' : [np.random.choice(pd.date_range(datetime.datetime(2013,1,1,0,0,0),datetime.datetime(2013,1,2,0,0,0),freq='T')) for i in range(24)]})
In [2]: dft['B'].groupby([dft['C'].apply(lambda x:x.hour)]).agg(pd.Series.nunique)
Out[2]:
C
2     1
4     1
6     1
7     1
9     1
10    2
11    1
12    4
14    1
15    2
16    1
18    3
19    1
20    1
21    1
22    1
23    1
dtype: float64

If you index timestamps, you can use re-fetching.

In [3]: dft2 = pd.DataFrame({'A' : ['spam', 'eggs', 'spam', 'eggs'] * 6,
               'B' : np.random.randn(24)},
               index = [np.random.choice(pd.date_range(datetime.datetime(2013,1,1,0,0,0),datetime.datetime(2013,1,2,0,0,0),freq='T')) for i in range(24)])
In [4]: dft2.resample('H',how=pd.Series.nunique)
Out[4]: 

                     A   B
2013-01-01 01:00:00  1   1
2013-01-01 02:00:00  0   0
2013-01-01 03:00:00  0   0
2013-01-01 04:00:00  0   0
2013-01-01 05:00:00  2   2
2013-01-01 06:00:00  2   3
2013-01-01 07:00:00  1   2
2013-01-01 08:00:00  2   2
2013-01-01 09:00:00  1   1
2013-01-01 10:00:00  2   3
2013-01-01 11:00:00  1   1
2013-01-01 12:00:00  1   2
2013-01-01 13:00:00  0   0
2013-01-01 14:00:00  1   1
2013-01-01 15:00:00  0   0
2013-01-01 16:00:00  1   1
2013-01-01 17:00:00  1   2
2013-01-01 18:00:00  0   0
2013-01-01 19:00:00  0   0
2013-01-01 20:00:00  2   2
2013-01-01 21:00:00  1   1