CODEWITHSUNDEEP
pythonpandasdatedatetimedataframe
Satyajit Pattnaik
Satyajit Pattnaik

Reputation: 135

Rounding down datetime column by the hour

11/22/2017 10:00  14.442473
11/22/2017 10:05  19.446146
11/22/2017 10:10  49.382300
11/22/2017 10:15  51.216980
11/22/2017 10:20  50.674092
11/22/2017 10:25  14.893244
11/22/2017 10:30  27.191617
11/22/2017 10:35  19.826802
11/22/2017 10:40   9.996578
11/22/2017 10:45   7.929272
11/22/2017 10:50  22.770500
11/22/2017 10:55  32.611105

Suppose i have data like above, my output for all the column A should be: 11/22/2017 10:00, roundup function won't work, as time after 10:30 are considered as 11:00, hence need help to ignore the minutes & seconds to get my exact data ready for further analysis.

Upvotes: 1

Views: 72

Answers (3)

Satyajit Pattnaik
Satyajit Pattnaik

Reputation: 135

Well, thanks everyone for your answers, here's what i tried and it worked as well, hopefully it might be helpful for someone, hence posting..

I tried this:

df['date'] = pd.to_datetime(df['date'])
df['just_date'] = df['date'].dt.date
df['just_hour'] = df['date'].dt.hour

df['Period'] = df.just_date.astype(str).str.cat(df.just_hour.astype(str), sep=' ') + ':00:00'

Upvotes: 0

piRSquared
piRSquared

Reputation: 294586

As mentioned by @cᴏʟᴅsᴘᴇᴇᴅ, you need to address converting to datetime.

To keep it 💯 pandas. I'd stick with @cᴏʟᴅsᴘᴇᴇᴅ's answer but present it like this:

df.assign(Date=pd.to_datetime(df.Date).dt.floor('H'))

                  Date          A
0  2017-11-22 10:00:00  14.442473
1  2017-11-22 10:00:00  19.446146
2  2017-11-22 10:00:00  49.382300
3  2017-11-22 10:00:00  51.216980
4  2017-11-22 10:00:00  50.674092
5  2017-11-22 10:00:00  14.893244
6  2017-11-22 10:00:00  27.191617
7  2017-11-22 10:00:00  19.826802
8  2017-11-22 10:00:00   9.996578
9  2017-11-22 10:00:00   7.929272
10 2017-11-22 10:00:00  22.770500
11 2017-11-22 10:00:00  32.611105

But an alternative using Numpy's types

df.assign(Date=pd.to_datetime(df.Date).values.astype('datetime64[h]'))

                  Date          A
0  2017-11-22 10:00:00  14.442473
1  2017-11-22 10:00:00  19.446146
2  2017-11-22 10:00:00  49.382300
3  2017-11-22 10:00:00  51.216980
4  2017-11-22 10:00:00  50.674092
5  2017-11-22 10:00:00  14.893244
6  2017-11-22 10:00:00  27.191617
7  2017-11-22 10:00:00  19.826802
8  2017-11-22 10:00:00   9.996578
9  2017-11-22 10:00:00   7.929272
10 2017-11-22 10:00:00  22.770500
11 2017-11-22 10:00:00  32.611105

Upvotes: 2

cs95
cs95

Reputation: 403278

Starting with - 

print(s)

11/22/2017 10:00    14.442473
11/22/2017 10:05    19.446146
11/22/2017 10:10    49.382300
11/22/2017 10:15    51.216980
11/22/2017 10:20    50.674092
11/22/2017 10:25    14.893244
11/22/2017 10:30    27.191617
11/22/2017 10:35    19.826802
11/22/2017 10:40     9.996578
11/22/2017 10:45     7.929272
11/22/2017 10:50    22.770500
11/22/2017 10:55    32.611105
Name: Data, dtype: float64

First, convert the index to a datetime index using pd.to_datetime - 

df.index = pd.to_datetime(df.index, errors='coerce')

Assuming the dates are part of the index for this Series, use the floor function with an hourly frequency for datetimes - 

s.index = s.index.floor('H')
print(s)

2017-11-22 10:00:00    14.442473
2017-11-22 10:00:00    19.446146
2017-11-22 10:00:00    49.382300
2017-11-22 10:00:00    51.216980
2017-11-22 10:00:00    50.674092
2017-11-22 10:00:00    14.893244
2017-11-22 10:00:00    27.191617
2017-11-22 10:00:00    19.826802
2017-11-22 10:00:00     9.996578
2017-11-22 10:00:00     7.929272
2017-11-22 10:00:00    22.770500
2017-11-22 10:00:00    32.611105
Name: Data, dtype: float64

If you want to apply the floor function on a dataframe column (say, Date), use the .dt accessor - 

df['Date'] = pd.to_datetime(df['Date']).dt.floor('H')

Upvotes: 3

Related Questions