Turn pandas datetime to hour:min rounded to 15 min

Question

I read this excell sheet (only column of 'DATEHEUREMAX') with pandas using this command:

xdata = read_excel('Data.xlsx', 'Data', usecols=['DATEHEUREMAX'])

now I want to turn this df into a simplify df with only hour:min rounded to 15min up. The main idea is to plot an histogram base on hour:min

Maybe you don't need to round the data. You may just need to use the proposed solution under https://stackoverflow.com/questions/27365467/can-pandas-plot-a-histogram-of-dates — Cedric Zoppolo, Nov 27 '19 at 20:55
Or even better try out this solution https://stackoverflow.com/questions/34814606/a-per-hour-histogram-of-datetime-using-pandas/34820891 — Cedric Zoppolo, Nov 27 '19 at 20:57

Joran Beasley · Answer 1 · 2019-11-27T21:00:10.467

0

I think this is what you are asking for

rounded_column = df['time_column'].dt.round('15min').strftime("%H:%M")

although i agree with the commenters you might not really need to do this and just use a timegrouper

edited Nov 27 '19 at 21:00

answered Nov 27 '19 at 20:54

Joran Beasley

110,522
12
160
179

realy close, how to get rid of date part, keep only "10:45" ? – Jonathan Roy Nov 27 '19 at 20:57
my goal is to plot an histogram to know the frequency of each date – Jonathan Roy Nov 27 '19 at 20:59

Valdi_Bo · Accepted Answer · 2019-11-27T21:09:33.630

Consider the following DataFrame, with a single column, read as datetime (not string):

                  Dat
0 2019-06-03 12:07:00
1 2019-06-04 10:04:00
2 2019-06-05 11:42:00
3 2019-06-06 10:17:00

To round these dates to 15 mins run:

df['Dat2'] = df.Dat.dt.round('15T').dt.time.map(lambda s: str(s)[:-3])

The result is:

                  Dat  Dat2
0 2019-06-03 12:07:00 12:00
1 2019-06-04 10:04:00 10:00
2 2019-06-05 11:42:00 11:45
3 2019-06-06 10:17:00 10:15

For demonstration purpose, I saved the result in a new column, but you can save it in the original column.

Cedric Zoppolo · Answer 3 · 2019-11-27T21:14:05.993

There is no need to round your column in order to get a histogram of dates with your DATEHEUREMAX column. For this purpose you can just make use of pd.Grouper as detailed below.

Toy sample code

You can work out this example to get a solution with your date column:

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
# Generating a sample of 10000 timestamps and selecting 500 to randomize them
df = pd.DataFrame(np.random.choice(pd.date_range(start=pd.to_datetime('2015-01-14'),periods = 10000, freq='S'), 500),  columns=['date'])
# Setting the date as the index since the TimeGrouper works on Index, the date column is not dropped to be able to count
df.set_index('date', drop=False, inplace=True)
# Getting the histogram
df.groupby(pd.Grouper(freq='15Min')).count().plot(kind='bar')
plt.show()

This code resolves to a graph like below:

Solution with your data

For your data you should be able to do something like:

import pandas as pd
import matplotlib.pyplot as plt
xdata = read_excel('Data.xlsx', 'Data', usecols=['DATEHEUREMAX'])
xdata.set_index('DATEHEUREMAX', drop=False, inplace=True)
xdata.groupby(pd.Grouper(freq='15Min')).count().plot(kind='bar')
plt.show()

Turn pandas datetime to hour:min rounded to 15 min

3 Answers3

Toy sample code

Solution with your data