CODEWITHSUNDEEP
pythonpython-3.xpandasdatetime
Chris90
Chris90

Reputation: 1998

Creating Columns for Hour of Day and date based on datetime column

How can I create a new column that has the day only, and hour of day only based of a column that has a datetime timestamp?

DF has column such as:

   Timestamp
2019-05-31 21:11:43 
2018-11-21 18:01:00 
2017-11-21 22:01:04 
2020-04-15 11:01:00 
2017-04-20 04:00:33

I want two new columns that look like below:

   Day      | Hour of Day
2019-05-31     21:00
2018-11-21     18:00
2017-11-21     22:00    
2020-04-15     11:00    
2017-04-20     04:00

I tried something like below but it only gives me a # for hour of day,

df['hour'] = pd.to_datetime(df['Timestamp'], format='%H:%M:%S').dt.hour

where output would be 9 for 9:32:00 which isnt what I want to calculate

Thanks!

Upvotes: 2

Views: 1718

Answers (2)

Rajesh Bhat
Rajesh Bhat

Reputation: 1000

You could convert time to string and then just select substrings by index.

df = pd.DataFrame({'Timestamp': ['2019-05-31 21:11:43', '2018-11-21 18:01:00', 
                             '2017-11-21 22:01:04', '2020-04-15 11:01:00', 
                             '2017-04-20 04:00:33']})
df['Day'], df['Hour of Day'] = zip(*df.Timestamp.apply(lambda x: [str(x)[:10], str(x)[11:13]+':00']))

Upvotes: 0

wwnde
wwnde

Reputation: 26676

Please try dt.strftime(format+string)

df['hour'] = pd.to_datetime(df['Timestamp']).dt.strftime("%H"+":00")

Following your comments below. Lets Try use df.assign and extract hour and date separately

df=df.assign(hour=pd.to_datetime(df['Timestamp']).dt.strftime("%H"+":00"), Day=pd.to_datetime(df['Timestamp']).dt.date)

Upvotes: 2

Related Questions