Convert column of integers to time in HH:MM:SS format efficiently

user_10964685 0
Viewed 3313

I am trying to develop a more efficient loop to complete a problem. At the moment, the code below applies a string if it aligns with a specific value. However, the values are in identical order so a loop could make this process more efficient.

Using the df below as an example, using integers to represent time periods, each integer increase equates to a 15 min period. So 1 == 8:00:00 and 2 == 8:15:00 etc. At the moment I would repeat this process until the last time period. If this gets up to 80 it could become very inefficient. Could a loop be incorporated here? 

import pandas as pd

d = ({
    'Time' :    [1,1,1,2,2,2,3,3,3,4,4,4,5,5,5,6,6,6],
})

df = pd.DataFrame(data = d)

def time_period(row) :
   if row['Time'] == 1 :
      return '8:00:00'
   if row['Time'] == 2 :
       return '8:15:00'
   if row['Time'] == 3 :
      return '8:30:00'
   if row['Time'] == 4 :
      return '8:45:00'
   if row['Time'] == 5 :
      return '9:00:00'
   if row['Time'] == 6 :
      return '9:15:00'       
   .....
   if row['Time'] == 80 :
      return '4:00:00'      

df['24Hr Time'] = df.apply(lambda row: time_period(row), axis=1)

print(df)

Out:

    Time 24Hr Time
0      1   8:00:00
1      1   8:00:00
2      1   8:00:00
3      2   8:15:00
4      2   8:15:00
5      2   8:15:00
6      3   8:30:00
7      3   8:30:00
8      3   8:30:00
9      4   8:45:00
10     4   8:45:00
11     4   8:45:00
12     5   9:00:00
13     5   9:00:00
14     5   9:00:00
15     6   9:15:00
16     6   9:15:00
17     6   9:15:00
pandas python loops
4 Answers

In general, you want to make a dictionary and apply

my_dict = {'old_val1': 'new_val1',...}
df['24Hr Time'] = df['Time'].map(my_dict)

But, in this case, you can do with time delta:

df['24Hr Time'] = pd.to_timedelta(df['Time']*15, unit='T') + pd.to_timedelta('7:45:00')

Output (note that the new column is of type timedelta, not string)

    Time    24Hr Time
0   1   08:00:00
1   1   08:00:00
2   1   08:00:00
3   2   08:15:00
4   2   08:15:00
5   2   08:15:00
6   3   08:30:00
7   3   08:30:00
8   3   08:30:00
9   4   08:45:00
10  4   08:45:00
11  4   08:45:00
12  5   09:00:00
13  5   09:00:00
14  5   09:00:00
15  6   09:15:00
16  6   09:15:00
17  6   09:15:00
user_42384080

This is possible with some simple timdelta arithmetic:

df['24Hr Time'] = (
    pd.to_timedelta((df['Time'] - 1) * 15, unit='m') + pd.Timedelta(hours=8))
df.head()

   Time 24Hr Time
0     1  08:00:00
1     1  08:00:00
2     1  08:00:00
3     2  08:15:00
4     2  08:15:00

df.dtypes

Time                   int64
24Hr Time    timedelta64[ns]
dtype: object

If you need a string, use pd.to_datetime with unit and origin:

df['24Hr Time'] = (
    pd.to_datetime((df['Time']-1) * 15, unit='m', origin='8:00:00')
      .dt.strftime('%H:%M:%S'))
df.head()

   Time 24Hr Time
0     1  08:00:00
1     1  08:00:00
2     1  08:00:00
3     2  08:15:00
4     2  08:15:00

df.dtypes

Time          int64
24Hr Time    object
dtype: object
user_49090870

I end up using this 

pd.to_datetime((df.Time-1)*15*60+8*60*60,unit='s').dt.time
0     08:00:00
1     08:00:00
2     08:00:00
3     08:15:00
4     08:15:00
5     08:15:00
6     08:30:00
7     08:30:00
8     08:30:00
9     08:45:00
10    08:45:00
11    08:45:00
12    09:00:00
13    09:00:00
14    09:00:00
15    09:15:00
16    09:15:00
17    09:15:00
Name: Time, dtype: object
user_79645270

A fun way is using pd.timedelta_range and index.repeat

n = df.Time.nunique()
c = df.groupby('Time').size()
df['24_hr'] = pd.timedelta_range(start='8 hours', periods=n, freq='15T').repeat(c)

Out[380]:
    Time    24_hr
0      1 08:00:00
1      1 08:00:00
2      1 08:00:00
3      2 08:15:00
4      2 08:15:00
5      2 08:15:00
6      3 08:30:00
7      3 08:30:00
8      3 08:30:00
9      4 08:45:00
10     4 08:45:00
11     4 08:45:00
12     5 09:00:00
13     5 09:00:00
14     5 09:00:00
15     6 09:15:00
16     6 09:15:00
17     6 09:15:00
user_101892140
Related