How to keep milliseconds component of timestamp even if it has zero value?

user_13182801 0
Viewed 856

I am trying to write data to a csv file in Python. However I am having issues with the format of the timestamp when writing it.

My data is in UNIX millisecond format. I am using pandas inbuilt to_datetime(unit = 'ms') method for this. However, a lot of my early data has no intra-second records. The data is only stored at truncated second intervals. So, whilst I wish for my data to be stored liked this 2015-01-01 00:00:00.000, it instead stores it like this 2015-01-01 00:00:00 and drops the millisecond component completely. Once I get to this year, then I do have data that can be written as 2020-01-01 00:01:07.748, for example.

The part of my code which writes this can be seen here:

df = pd.DataFrame(data, columns = ['Trade ID', 'Date', 'Amount', 'Price'])
                
df['Date'] = pd.to_datetime(df['Date'], unit = 'ms')
df.to_csv(csv_file_path, mode = 'a', header = False, index = False)

I know for a fact that it has nothing to do with the line which actually writes it to the csv. As it keeps the millisecond data for records which do have intra-second records. I know that it is in the line where it converts it from milliseconds to datetime. I have tried using the format parameter as well in the method but that throws an error as you cannot specify both unit and format.

pandas datetime python
1 Answers

With microseconds (i do not know what exactly you have in Date column) and map and slice (the last line in the post)

df = pd.DataFrame({'datetime': {0: '2020/12/20 05:03:33.324',
                                1: '2020/12/20 05:03:55.33556'}})
print(df)
df['formatted'] = pd.to_datetime(df['datetime']).dt.strftime("%y-%m-%d %H:%M:%S.%f")
print(df)
df.to_csv("./dates.csv", mode = 'a', header = False, index = False)

Console output

                    datetime
0    2020/12/20 05:03:33.324
1  2020/12/20 05:03:55.33556
                    datetime       date_time_formatted
0    2020/12/20 05:03:33.324  20-12-20 05:03:33.324000
1  2020/12/20 05:03:55.33556  20-12-20 05:03:55.335560

in the csv file:

2020/12/20 05:03:33.324,20-12-20 05:03:33.324000
2020/12/20 05:03:55.33556,20-12-20 05:03:55.335560

From here i checked the format options: https://docs.python.org/3/library/datetime.html#strftime-and-strptime-behavior

In fact with map and slice it works:

df['with_map'] = pd.to_datetime(df['datetime']).dt.strftime("%y-%m-%d %H:%M:%S.%f").map(lambda date_time: date_time[:-3])

With str and slice (thanks to @MrFuppes)

df['dt_final'] = pd.to_datetime(df['datetime']).dt.strftime("%y-%m-%d %H:%M:%S.%f").str[:-3]
user_148611110
Related