Group DataFrame in 5-minute intervals

by

You can use df.resample to do aggregation based on a date/time variable. You’ll need a datetime index and you can specify that while reading the csv file:

df = pd.read_csv("filename.csv", parse_dates = [["DATE", "TIME"]], index_col=0)

This will result in a dataframe with an index where date and time are combined (source):

df.head()
Out[7]: 
                       OPEN    HIGH     LOW   CLOSE  VOLUME 
DATE_TIME                                                   
1997-02-03 09:04:00  3046.0  3048.5  3046.0  3047.5      505
1997-02-03 09:05:00  3047.0  3048.0  3046.0  3047.0      162
1997-02-03 09:06:00  3047.5  3048.0  3047.0  3047.5       98
1997-02-03 09:07:00  3047.5  3047.5  3047.0  3047.5      228
1997-02-03 09:08:00  3048.0  3048.0  3047.5  3048.0      136

After that you can use resample to get the sum, mean, etc. of those five minute intervals. 

df.resample("5T").mean()
Out[8]: 
                       OPEN    HIGH     LOW   CLOSE  VOLUME 
DATE_TIME                                                   
1997-02-03 09:00:00  3046.0  3048.5  3046.0  3047.5    505.0
1997-02-03 09:05:00  3047.6  3047.9  3046.8  3047.3    159.6
1997-02-03 09:10:00  3045.6  3045.9  3044.8  3045.0    110.2
1997-02-03 09:15:00  3043.6  3044.0  3042.8  3043.2     69.2
1997-02-03 09:20:00  3044.7  3045.2  3044.5  3045.0     65.8
1997-02-03 09:25:00  3043.8  3044.0  3043.5  3043.7     59.0
1997-02-03 09:30:00  3044.6  3045.0  3044.3  3044.6     56.0
1997-02-03 09:35:00  3044.5  3044.5  3043.5  3044.5     44.0

(T is used for minute frequency. Here is a list of other units.)

More Related Contents:

  1. How to join two dataframes for which column values are within a certain range?
  2. Filtering Pandas DataFrames on dates
  3. How to display pandas DataFrame of floats using a format string for columns?
  4. How to print pandas DataFrame without index
  5. Why isn’t my Pandas ‘apply’ function referencing multiple columns working? [closed]
  6. datetime dtypes in pandas read_csv
  7. How do I create test and train samples from one dataframe with pandas?
  8. How to map numeric data into categories / bins in Pandas dataframe
  9. python pandas extract year from datetime: df[‘year’] = df[‘date’].year is not working
  10. Python Pandas Group by date using datetime data
  11. Removing the timestamp from a datetime in pandas dataframe
  12. Pandas filter dataframe rows with a specific year
  13. Python/Pandas convert string to time only
  14. Convert Pandas Series to DateTime in a DataFrame
  15. Truncate `TimeStamp` column to hour precision in pandas `DataFrame`
  16. Python pandas integer YYYYMMDD to datetime
  17. Pandas custom function to find whether it is the 1st, 2nd etc Monday, Tuesday, etc – all suggestions welcome
  18. What values are valid in Pandas ‘Freq’ tags?
  19. Python: Adding hours to pandas timestamp
  20. pandas extract year from datetime: df[‘year’] = df[‘date’].year is not working
  21. Python Pandas : pandas.to_datetime() is switching day & month when day is less than 13
  22. Get weekday/day-of-week for Datetime column of DataFrame
  23. Plot pandas dates in matplotlib
  24. How to sort a dataFrame in python pandas by two or more columns?
  25. Converting between datetime, Timestamp and datetime64
  26. pandas concat generates nan values
  27. Pandas Groupby and Sum Only One Column
  28. Pandas Replace NaN with blank/empty string
  29. Pythonic/efficient way to strip whitespace from every Pandas Data frame cell that has a stringlike object in it
  30. Pandas: select all dates with specific month and day

Leave a Comment