How to convert numbers in an hour column to actual hours

user_300978 0
Viewed 154

I have an 'hour' column in a pandas dataframe that is simply a list of numbers from 0 to 23 representing hours. How can I convert them to an hour format such as 01:00 when the numbers are single digit ( like 1 ) and double digit (like 18)? The single digit numbers need to have a leading zero, a colon and two trailing zeros. The double digit numbers need only a colon and two trailing zeros. How can this be accomplished in a dataframe? Also, I have a 'date' column that needs to merge with the hour column after the hour column is converted. 

 e.g.    date           hour  
           2018-07-01     0
           2018-07-01     1
           2018-07-01     3
           ...
           2018-07-01     21
           2018-07-01     22
           2018-07-01     23

Needs to look like:
          date
          2018-07-01 01:00
          ...
          2018-07-01 23:00

The source of the data is a .csv file. Thanks for your consideration. I'm new to pandas and I can't find in their documentation how to do this considering the single and double digit numbers.

pandas
1 Answers

Convert hours to timedeltas by to_timedelta and add to datetimes converted by to_datetime if necessary:

df['date'] = pd.to_datetime(df['date']) + pd.to_timedelta(df['hour'], unit='h')
print (df)
                 date  hour
0 2018-07-01 00:00:00     0
1 2018-07-01 01:00:00     1
2 2018-07-01 03:00:00     3
3 2018-07-01 21:00:00    21
4 2018-07-01 22:00:00    22
5 2018-07-01 23:00:00    23

If need also remove hour column use DataFrame.pop

df['date'] = pd.to_datetime(df['date']) + pd.to_timedelta(df.pop('hour'), unit='h')
print (df)
                 date
0 2018-07-01 00:00:00
1 2018-07-01 01:00:00
2 2018-07-01 03:00:00
3 2018-07-01 21:00:00
4 2018-07-01 22:00:00
5 2018-07-01 23:00:00
user_29010020
Related