how to convert a column of integer to standard hour time in python?

Question

I have a data frame like this:

BuyTime        ID      SellTime
94650          1       94651
94717          1       94817
120458         2       114119

the buy time and sell time type are integer but i want to convert them to standard time date. I already used

 quote['SellTime'] = pd.to_datetime(quote['SellTime'], unit = 's')

but it gives a start year like 1970-01-01 to time which is not needed. my data should be like this:

BuyTime     Id     SellTime
09:46:50    1      09:46:51
09:47:17    1      09:48:17
12:04:58    2      11:41:19

EDIT: also using this function regard to size of my data is not efficient:

 def format_time(value):
 t2 = str(value)
 if len(t2) == 5:
     return "0%s:%s:%s" % (t2[0], t2[1:3], t2[3:5])
 return "%s:%s:%s" % (t2[0:2], t2[2:4], t2[4:6])

You may need to supply a bit more information: what **exactly** does the integer value for buyTime and sellTime represent? Seconds since midnight? Something else? — payne, Dec 13 '17 at 14:31

jezrael · Accepted Answer · 2017-12-13T14:43:25.167

I think you need if need output as strings zfill and str[] for select by positions:

t1 = quote['BuyTime'].astype(str).str.zfill(6)
t2 = quote['SellTime'].astype(str).str.zfill(6)
quote['BuyTime'] = t1.str[0:2] + ':' + t1.str[2:4] + ':' + t1.str[4:6]
quote['SellTime'] = t2.str[0:2] + ':' + t2.str[2:4] + ':' + t2.str[4:6]
print (quote)
    BuyTime  ID  SellTime
0  09:46:50   1  09:46:51
1  09:47:17   1  09:48:17
2  12:04:58   2  11:41:19

Or if need python times add 0 by zfill, convert to datetimes and extract times:

t1 = quote['BuyTime'].astype(str).str.zfill(6)
t2 = quote['SellTime'].astype(str).str.zfill(6)
quote['BuyTime'] = pd.to_datetime(t1, format='%H%M%S').dt.time
quote['SellTime'] = pd.to_datetime(t2, format='%H%M%S').dt.time
print (quote)
    BuyTime  ID  SellTime
0  09:46:50   1  09:46:51
1  09:47:17   1  09:48:17
2  12:04:58   2  11:41:19

Alternative for strings outputs is strftime:

quote['BuyTime'] = pd.to_datetime(t1, format='%H%M%S').dt.strftime('%H:%M:%S')
quote['SellTime'] = pd.to_datetime(t2, format='%H%M%S').dt.strftime('%H:%M:%S')
print (quote)
    BuyTime  ID  SellTime
0  09:46:50   1  09:46:51
1  09:47:17   1  09:48:17
2  12:04:58   2  11:41:19

what if type of columns are dtype('O')? – ary Dec 17 '17 at 21:44 — ary, Dec 17 '17 at 21:44

score 0 · Answer 2 · answered Dec 13 '17 at 14:42

Assuming the date string only contains hour/minute/second (and minute/second are always 2 characters long, you could do something like this to parse the string using Python's modulo operator:

dateStr = '94522'
dateInt = int(dateStr)
sec = dateInt % 100
dateInt = dateInt - sec
min = dateInt % 10000
dateInt = dateInt - min
min /= 100
hour = dateInt / 10000

newDateStr = '{}:{}:{}'.format(hour, min, sec)
print 'number={} formatted={}'.format(dateStr, newDateStr)

This will work for both 5 and 6 character long time strings.

how to convert a column of integer to standard hour time in python?

2 Answers2

Linked