Truncate `TimeStamp` column to hour precision in pandas `DataFrame`

by

In pandas 0.18.0 and later, there are datetime floor, ceil and round methods to round timestamps to a given fixed precision/frequency. To round down to hour precision, you can use:

>>> df['dt2'] = df['dt'].dt.floor('h')
>>> df
                      dt                     dt2
0    2014-10-01 10:02:45     2014-10-01 10:00:00
1    2014-10-01 13:08:17     2014-10-01 13:00:00
2    2014-10-01 17:39:24     2014-10-01 17:00:00

Here’s another alternative to truncate the timestamps. Unlike floor, it supports truncating to a precision such as year or month.

You can temporarily adjust the precision unit of the underlying NumPy datetime64 datatype, changing it from [ns] to [h]:

df['dt'].values.astype('<M8[h]')

This truncates everything to hour precision. For example:

>>> df
                       dt
0     2014-10-01 10:02:45
1     2014-10-01 13:08:17
2     2014-10-01 17:39:24

>>> df['dt2'] = df['dt'].values.astype('<M8[h]')
>>> df
                      dt                     dt2
0    2014-10-01 10:02:45     2014-10-01 10:00:00
1    2014-10-01 13:08:17     2014-10-01 13:00:00
2    2014-10-01 17:39:24     2014-10-01 17:00:00

>>> df.dtypes
dt     datetime64[ns]
dt2    datetime64[ns]

The same method should work for any other unit: months 'M', minutes 'm', and so on:

  • Keep up to year: '<M8[Y]'
  • Keep up to month: '<M8[M]'
  • Keep up to day: '<M8[D]'
  • Keep up to minute: '<M8[m]'
  • Keep up to second: '<M8[s]'

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 print pandas DataFrame without index
  4. datetime dtypes in pandas read_csv
  5. python pandas extract year from datetime: df[‘year’] = df[‘date’].year is not working
  6. Python Pandas Group by date using datetime data
  7. Removing the timestamp from a datetime in pandas dataframe
  8. Pandas filter dataframe rows with a specific year
  9. Python/Pandas convert string to time only
  10. Convert Pandas Series to DateTime in a DataFrame
  11. Python pandas integer YYYYMMDD to datetime
  12. Group DataFrame in 5-minute intervals
  13. Pandas custom function to find whether it is the 1st, 2nd etc Monday, Tuesday, etc – all suggestions welcome
  14. What values are valid in Pandas ‘Freq’ tags?
  15. Python: Adding hours to pandas timestamp
  16. pandas extract year from datetime: df[‘year’] = df[‘date’].year is not working
  17. Python Pandas : pandas.to_datetime() is switching day & month when day is less than 13
  18. Get weekday/day-of-week for Datetime column of DataFrame
  19. Plot pandas dates in matplotlib
  20. Using Pandas to pd.read_excel() for multiple worksheets of the same workbook
  21. How to add pandas data to an existing csv file?
  22. Pandas split column into multiple columns by comma
  23. Repeat rows in a pandas DataFrame based on column value
  24. Accessing every 1st element of Pandas DataFrame column containing lists
  25. Python – How to convert JSON File to Dataframe
  26. How to remove timezone from a Timestamp column in a pandas dataframe
  27. Pandas lookup from one of multiple columns, based on value
  28. How to add a suffix (or prefix) to each column name?
  29. How to delete multiple pandas (python) dataframes from memory to save RAM?
  30. How to read a Parquet file into Pandas DataFrame?

Leave a Comment