Convert milliseconds column to HH:mm:ss:ms datetime in Pandas

Question

I have a column containing the number of milliseconds passed (starting from 0, not epoch)

           time
0           NaN
1        4067.0
2     3444053.0
3    50477685.0
4    60476311.0
5    70475303.0
6    80475303.0

I'd like to convert this column to a HH:mm:ss:ms format. Is this possible? I've found that using pd.to_datetime(df['time'], unit='ms') always converts the column to epoch.

                    time
0                    NaT
1    1971-04-14 02:27:33

Whereas I would like something along the lines of:

            time
0            NaT
1    00:00:04:67

Answer 1

You might want to_timedelta here.

df.time = pd.to_timedelta(df.time, unit='ms')
df.time 

0               NaT
1   00:00:04.067000
2   00:57:24.053000
3   14:01:17.685000
4   16:47:56.311000
5   19:34:35.303000
6   22:21:15.303000
Name: time, dtype: timedelta64[ns]

---

To get back to square one, use df.total_seconds:

df.time.dt.total_seconds() * 1000

0           NaN
1        4067.0
2     3444053.0
3    50477685.0
4    60476311.0
5    70475303.0
6    80475303.0
Name: time, dtype: float64

Answer 2

You can separate the time portion using

df['time'] = pd.to_datetime(df['time'], unit='ms').dt.time 

the result will be

0                NaN
1    00:00:04.067000
2    00:57:24.053000
3    14:01:17.685000
4    16:47:56.311000
5    19:34:35.303000
6    22:21:15.303000
Name: time, dtype: object

to get the close to the desired output

df['time'] = pd.to_datetime(df['time'],
             unit='ms').dt.strftime('%H:%M:%S:%f').str[:-3] 

this will display the micro seconds using strftime and slicing will convert to milliseconds.

Here is the output of df['time']

0                
1    00:00:04:067
2    00:57:24:053
3    14:01:17:685
4    16:47:56:311
5    19:34:35:303
6    22:21:15:303
Name: time, dtype: object