How to prevent pandas from rounding LDAP timestamps

Question

I am importing a CSV into a pandas dataframe. One column contains an 18 digit LDAP timestamp. I am trying to convert this timestamp however it appears that it is being rounded causing incorrect calculation.

data.csv:

Event ID,Clock-Time,ProcessID
10,133081599160584000,2824,44
10,133081599160584000,2824,84
10,133081599160667000,2824,44
10,133081599160667000,2824,92
10,133081599160667000,2824,116
10,133081599160667000,2824,132

script.py

#!/usr/bin/python

from datetime import datetime, timedelta
import pandas
pandas.set_option('display.max_colwidth', None)
pandas.set_option('display.float_format','{:.0f}'.format)
pandas.set_option('display.precision', 20)

in_csv = "data.csv"

df = pandas.read_csv(in_csv,sep=',',header=0, float_precision='high')

print(df.dtypes)
print(df)

# convert win32 timestamp to unix timestamp
df['Clock-Time'] = df['Clock-Time'].apply(lambda timestamp: datetime(1601, 1, 1) + timedelta(seconds=(timestamp/10000000)))

print(df)

output:

Event ID        int64
Clock-Time    float64
ProcessID       int64
Size            int64
dtype: object
   Event ID         Clock-Time  ProcessID  Size
0        10 133082000000000000       2824    44
1        10 133082000000000000       2824    84
2        10 133082000000000000       2824    44
3        10 133082000000000000       2824    92
4        10 133082000000000000       2824   116
5        10 133082000000000000       2824   132
    Event ID          Clock-Time  ProcessID  Size
0        10 2022-09-21 02:13:20       2824    44
1        10 2022-09-21 02:13:20       2824    84
2        10 2022-09-21 02:13:20       2824    44
3        10 2022-09-21 02:13:20       2824    92
4        10 2022-09-21 02:13:20       2824   116
5        10 2022-09-21 02:13:20       2824   132

How can I get pandas to respect the full value so I can get the accurate timestamp?

abokey · Answer

I can't reproduce the issue by creating your .csv from Windows Notepad.

   Event ID                 Clock-Time  ProcessID  Size
0        10 2022-09-20 15:05:16.058399       2824    44
1        10 2022-09-20 15:05:16.058399       2824    84
2        10 2022-09-20 15:05:16.066700       2824    44
3        10 2022-09-20 15:05:16.066700       2824    92
4        10 2022-09-20 15:05:16.066700       2824   116
5        10 2022-09-20 15:05:16.066700       2824   132

I bet you createad your .csv file from an Excel spreadsheet. By default, when you enter a number over 12 digits (e.g, a 18 digit LDAP timestamp) in a spreadsheet, the number is auto-corrected to scientific notation. For example, "133081599160584000" is converted to "1.33082E+17" (hence the single value "133082000000000000" in your initial dataframe after calling pandas.read_csv). So when you export the spreadsheet to a text file (e.g, .csv), it is the scientific notation (what Excel sees) that will be exported and not the actual (x>12)-digit value.

You can fix that upstream by changing the type of the concerned column in Excel before creating the .csv :

How to prevent pandas from rounding LDAP timestamps

1 Answers