Converting Unix 13-digits to datetime/timestamp format with pandas

user_12132968 0
Viewed 2833

I have a pandas data frame containing 13-digit/milliseconds data like below, which I am trying to convert to legible date and timestamp format using .datetime() or 'pd.Timestamp(), both of which seem to require 10-digit/seconds data.

  Date
0 1569968699999
1 1569968700000
2 1569968759999
3 1569968760000
4 1569968819999

I have tried converting the data to type 'int' or even 'float' using .astype(int) or .astype(float) with pandas, respectively, and then dividing by 1000 to get to 10-digit/seconds data, which obviously yields a 'cannot convert the series to type 'float'' error.

I understand why the 'divide by 1000' option would only work with defining a variable (as mentioned on multiple other stack overflow posts/questions).

I've also tried using:

df['Date'] = pd.to_datetime(df['Date'], unit='s')

or 

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

but the first seems to add more digits to the data frame, and the second returns a 'signed integer is greater than maximum' error.

Any help appreciated. Thanks.

pandas unix-timestamp python-3.x datetime python
1 Answers

Maybe this one can help:

import pandas as pd
import numpy as np
import datetime
df=pd.DataFrame()
i=[1569968699999,1569968700000,1569968759999,1569968760000,1569968819999]
df['date']=pd.Series(i)
df['date']=df['date'].apply(lambda d: datetime.datetime.fromtimestamp(int(d)/1000).strftime('%Y-%m-%d %H:%M:%S'))

Output:

                  date
0  2019-10-02 01:24:59
1  2019-10-02 01:25:00
2  2019-10-02 01:25:59
3  2019-10-02 01:26:00
4  2019-10-02 01:26:59
user_120804790
Related