Pandas: resample timeseries with groupby

by

In my original post, I suggested using pd.TimeGrouper.
Nowadays, use pd.Grouper instead of pd.TimeGrouper. The syntax is largely the same, but TimeGrouper is now deprecated in favor of pd.Grouper.

Moreover, while pd.TimeGrouper could only group by DatetimeIndex, pd.Grouper can group by datetime columns which you can specify through the key parameter.

You could use a pd.Grouper to group the DatetimeIndex’ed DataFrame by hour:

grouper = df.groupby([pd.Grouper(freq='1H'), 'Location'])

use count to count the number of events in each group:

grouper['Event'].count()
#                      Location
# 2014-08-25 21:00:00  HK          1
#                      LDN         1
# 2014-08-25 22:00:00  LDN         2
# Name: Event, dtype: int64

use unstack to move the Location index level to a column level:

grouper['Event'].count().unstack()
# Out[49]: 
# Location             HK  LDN
# 2014-08-25 21:00:00   1    1
# 2014-08-25 22:00:00 NaN    2

and then use fillna to change the NaNs into zeros.

Putting it all together,

grouper = df.groupby([pd.Grouper(freq='1H'), 'Location'])
result = grouper['Event'].count().unstack('Location').fillna(0)

yields

Location             HK  LDN
2014-08-25 21:00:00   1    1
2014-08-25 22:00:00   0    2

More Related Contents:

  1. How can I pivot a dataframe?
  2. GroupBy pandas DataFrame and select most common value
  3. How to groupby consecutive values in pandas DataFrame
  4. Pandas: rolling mean by time interval
  5. Combine Date and Time columns using python pandas
  6. pandas GroupBy columns with NaN (missing) values
  7. Count unique values per groups with Pandas [duplicate]
  8. pandas groupby sort within groups
  9. Pandas ‘count(distinct)’ equivalent
  10. Pandas Groupby Range of Values
  11. Count unique values using pandas groupby
  12. pandas groupby, then sort within groups
  13. Pandas GroupBy and select rows with the minimum value in a specific column
  14. group by in group by and average
  15. Pandas interpolate within a groupby
  16. How to plot stacked event duration (Gantt Charts)
  17. Python Pandas How to assign groupby operation results back to columns in parent dataframe?
  18. pandas – find first occurrence
  19. python pandas groupby() result
  20. Combine Date and Time columns using pandas
  21. Pivot Tables of Counts in Pandas DataFrame
  22. Pandas groupby with categories with redundant nan
  23. Pandas Correlation Groupby
  24. Add a sequence number to each element in a group using python
  25. Selecting Data between Specific hours in a pandas dataframe
  26. Pandas groupby and aggregation output should include all the original columns (including the ones not aggregated on)
  27. Split Python sequence (time series/array) into subsequences with overlap
  28. Get the row corresponding to the max in pandas GroupBy [duplicate]
  29. Get frequency of item occurrences in a column as percentage [duplicate]
  30. Why do I get a KeyError when using pandas apply?

Leave a Comment