Convert integer in format HHMMSS to datetime in Pandas

Question

I have a long dataframe (35 million rows) of data that includes a Time column. The time in the original CSV is in the format HHMMSS, which translates into pandas as an integer. Being an integer, if the Hours are less than 2 digits the first digit drops off (ex: 090000 (9am) becomes 90000 in pandas).

I am trying to convert these integers into an actual datetime value that I can extract .time() from, so as to be able to count in custom intervals of minutes, seconds, hours etc.

How do I convert an integer (such as 90000 or 100000) into their respective times (9am, 10am)?

Are you using `read_csv` ? You can use `parse_dates` to specify the column should be parsed as a date — Panagiotis Kanavos, Sep 19 '22 at 18:10
@PanagiotisKanavos Yes I am, could you share the syntax for that ? — philbeet, Sep 19 '22 at 18:13
https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html — BigBen, Sep 19 '22 at 18:18

score 2 · Accepted Answer · answered Sep 19 '22 at 18:24

You can use to_datetime after converting to string and zfilling the zeros:

df = pd.DataFrame({'time': [90000, 0, 123456]})

df['time2'] = pd.to_datetime(df['time'].astype(str).str.zfill(6), format='%H%M%S').dt.time

Or, as string:

df['time2'] = pd.to_datetime(df['time'].astype(str).str.zfill(6), format='%H%M%S').dt.strftime('%H:%M:%S')

Output:

     time     time2
0   90000  09:00:00
1       0  00:00:00
2  123456  12:34:56

score 0 · Answer 2 · answered Sep 20 '22 at 08:20

You can convert the values to time (Timedelta) directly when loading through the converters parameter. This parameter tells read_csv how to convert specific columns. Unfortunately, there's no equivalent to strptime for timedelta.

One quick & dirty way to parse this format into a timedelta is to use a regular expression or just split the string into parts, eg :

def parse_timedelta(time_str):
    s=time_str.zfill(6)
    return pd.Timedelta(
        hours=int(s[0:2]),
        minutes=int(s[2:4]),
        seconds=int(s[4:6]))

This can be used to convert fields in HHMMSS format to timedelta:

csv="Time\n90000\n000000\n123456"
df = pd.read_csv(StringIO(csv),converters={'Time':parse_time})
>>> df
             Time
0 3 days 18:00:00
1 0 days 00:00:00
2 0 days 12:34:56

Convert integer in format HHMMSS to datetime in Pandas

2 Answers2