How to group DataFrame by a period of time?
I have some data from log files and would like to group entries by a minute:
def gen(date, count=10):
while count > 0:
yield date, "event{}".format(randint(1,9)), "source{}".format(randint(1,3))
count -= 1
date += DateOffset(seconds=randint(40))
df = DataFrame.from_records(list(gen(datetime(2012,1,1,12, 30))), index='Time', columns=['Time', 'Event', 'Source'])
df:
Event Source
2012-01-01 12:30:00 event3 source1
2012-01-01 12:30:12 event2 source2
2012-01-01 12:30:12 event2 source2
2012-01-01 12:30:29 event6 source1
2012-01-01 12:30:38 event1 source1
2012-01-01 12:31:05 event4 source2
2012-01-01 12:31:38 event4 source1
2012-01-01 12:31:44 event5 source1
2012-01-01 12:31:48 event5 source2
2012-01-01 12:32:23 event6 source1
I tried these options:
-
df.resample('Min')
is too high level and wants to aggregate. -
df.groupby(date_range(datetime(2012,1,1,12, 30), freq='Min', periods=4))
fails with exception. -
df.groupby(TimeGrouper(freq='Min'))
works fine and returns aDataFrameGroupBy
object for further processing, e.g.:grouped = df.groupby(TimeGrouper(freq='Min')) grouped.Source.value_counts() 2012-01-01 12:30:00 source1 1 2012-01-01 12:31:00 source2 2 source1 2 2012-01-01 12:32:00 source2 2 source1 2 2012-01-01 12:33:00 source1 1
However, the TimeGrouper
class is not documented.
What is the correct way to group by a period of time? How can I group the data by a minute AND by the Source column, e.g. groupby([TimeGrouper(freq='Min'), df.Source])
?
You can group on any array/Series of the same length as your DataFrame --- even a computed factor that's not actually a column of the DataFrame. So to group by minute you can do:
df.groupby(df.index.map(lambda t: t.minute))
If you want to group by minute and something else, just mix the above with the column you want to use:
df.groupby([df.index.map(lambda t: t.minute), 'Source'])
Personally I find it useful to just add columns to the DataFrame to store some of these computed things (e.g., a "Minute" column) if I want to group by them often, since it makes the grouping code less verbose.
Or you could try something like this:
df.groupby([df['Source'],pd.TimeGrouper(freq='Min')])
Since the original answer is rather old and pandas introduced periods a different solution is nowadays:
df.groupby(df.index.to_period('T'))
Additionally, you can resample
df.resample('T')