Rounding Pandas Timestamp to Minutes

Question

I want to create a DateTimeIndex at 1 minute intervals based on the start and end timestamp (given in microseconds from the era) using pd_date_range() . To do this, I need to round off the initial timestamp and the final timestamp. Here is what I still have:

 import pandas as pd start = 1406507532491431 end = 1406535228420914 start_ts = pd.to_datetime(start, unit='us') # Timestamp('2014-07-28 00:32:12.491431') end_ts = pd.to_datetime(end, unit='us') # Timestamp('2014-07-28 08:13:48.420914') 

I want to round:

start_ts before Timestamp('2014-07-28 00:32') and

end_ts to Timestamp('2014-07-28 08:14') .

How can i do this?

Answer 1

Doing this in a simple method is currently an outstanding issue here

 In [22]: start = 1406507532491431 In [23]: end = 1406535228420914 [26]: dti = pd.to_datetime([start,end],unit='us') In [27]: dti Out[27]: <class 'pandas.tseries.index.DatetimeIndex'> [2014-07-28 00:32:12.491431, 2014-07-28 08:13:48.420914] Length: 2, Freq: None, Timezone: None In [29]: pd.DatetimeIndex(((dti.asi8/(1e9*60)).round()*1e9*60).astype(np.int64)) Out[29]: <class 'pandas.tseries.index.DatetimeIndex'> [2014-07-28 00:32:00, 2014-07-28 08:14:00] Length: 2, Freq: None, Timezone: None 

However, it is quite simple.

Pull invitations to implement are welcome.

Answer 2

Starting with version 0.18, Pandas has built- in datetime rounding functionality :

 start_ts.round('min') # Timestamp('2014-07-28 00:32:00') end_ts.round('min') # Timestamp('2014-07-28 08:14:00') 

You can also use .ceil or .floor if you need to force rounding up or down.

Answer 3

I had a similar problem and wanted to round up to a day. It turns out there is a simple way (it works for Y [ear] M [month] D [ay], h [ours], m [inute], s [econd]). Assuming df is a pandas DataFrame with a datecol column:

 df['datecol'] = df['datecol'].values.astype('<M8[m]') 

Will round it to m [inute]. Given that I found this question initially, I thought that I would contact the answer that I got, as it seems relevant,

A more efficient way to round to a timestamp with pandas

Answer 4

As pointed out by @ user3735204, you can round the columns with:

 df['datecol'] = df['datecol'].astype('datetime64[m]') 

where the unit in square brackets can be:

 Y[ear] M[month] D[ay], h[our], m[inute], s[econd] 

You can also round to the nearest ( help ) by specifying the column as an index and using the round method (available in pandas 0.19.0)

 df.index = pd.to_datetime(df['datecol']) df.index = df.index.round("S") 

Example:

 df = pd.DataFrame(data = tmpdata) df['datecol'] = df['datecol'].astype('datetime64[s]') print df['datecol'] 0 2016-10-05 05:37:42 1 2016-10-05 05:37:43 Name: datecol, dtype: datetime64[ns] df.index = pd.to_datetime(df['datecol']) df.index = df.index.round("S") print df.index DatetimeIndex(['2016-10-05 05:37:43', '2016-10-05 05:37:43'], dtype='datetime64[ns]', name=u'timestamp', freq=None) 

Answer 5

 import pandas as pd new_index = pd.date_range(start=start_ts.strftime('%Y-%m-%d %H:%M'), end=end_ts.strftime('%Y-%m-%d %H:%M'), freq='1min')