How to group a pandas dataframe by a defined time interval?

by

Use base=30 in conjunction with label="right" parameters in pd.Grouper.

Specifying label="right" makes the time-period to start grouping from 6:30 (higher side) and not 5:30.
Also, base is set to 0 by default, hence the need to offset those by 30 to account for the forward propagation of dates.

Suppose, you want to aggregate the first element of every sub-group, then:

df.groupby(pd.Grouper(freq='60Min', base=30, label="right")).first()
# same thing using resample - df.resample('60Min', base=30, label="right").first()

yields:

                           data
index                          
2017-02-14 06:30:00  11198648.0
2017-02-14 07:30:00  11198650.0
2017-02-14 08:30:00         NaN
2017-02-14 09:30:00         NaN
2017-02-14 10:30:00         NaN
2017-02-14 11:30:00         NaN
2017-02-14 12:30:00         NaN
2017-02-14 13:30:00         NaN
2017-02-14 14:30:00         NaN
2017-02-14 15:30:00         NaN
2017-02-14 16:30:00         NaN
2017-02-14 17:30:00         NaN
2017-02-14 18:30:00         NaN
2017-02-14 19:30:00         NaN
2017-02-14 20:30:00         NaN
2017-02-14 21:30:00         NaN
2017-02-14 22:30:00         NaN
2017-02-14 23:30:00  11207728.0

More Related Contents:

  1. Pandas: groupby forward fill with datetime index
  2. How do I Pandas group-by to get sum?
  3. Apply multiple functions to multiple groupby columns
  4. Filtering Pandas DataFrames on dates
  5. Python Pandas : group by in group by and average?
  6. Keep only date part when using pandas.to_datetime
  7. How to print pandas DataFrame without index
  8. Python: get a frequency count based on two columns (variables) in pandas dataframe some row appers
  9. Pandas dataframe get first row of each group
  10. group by pandas dataframe and select latest in each group
  11. Pandas: Subtracting two date columns and the result being an integer
  12. Naming returned columns in Pandas aggregate function? [duplicate]
  13. How to change the datetime tick label frequency for matplotlib plots
  14. Splitting timestamp column into separate date and time columns
  15. How can I use cumsum within a group in Pandas?
  16. pandas group by and find first non null value for all columns
  17. Pandas converting row with unix timestamp (in milliseconds) to datetime
  18. Python pandas convert datetime to timestamp effectively through dt accessor
  19. Which is the fastest way to extract day, month and year from a given date?
  20. Pandas groupby for zero values
  21. Locate first and last non NaN values in a Pandas DataFrame
  22. Split pandas dataframe based on values in a column using groupby
  23. Number rows within group in increasing order in a pandas dataframe
  24. pandas divide row value by aggregated sum with a condition set by other cell
  25. Select row by max value in group in a pandas dataframe
  26. How to create Pandas groupby plot with subplots
  27. Pandas custom function to find whether it is the 1st, 2nd etc Monday, Tuesday, etc – all suggestions welcome
  28. Clean way to convert quarterly periods to datetime in pandas
  29. pandas extract year from datetime: df[‘year’] = df[‘date’].year is not working
  30. Python Pandas : pandas.to_datetime() is switching day & month when day is less than 13

Leave a Comment