How Can I Prevent Excel From Removing Seconds From Timestamps?

I have a DataFrame of time series with second resolution, which I save into a CSV file: import pandas as pd dr = pd.date_range('01/01/2020 9:00', '01/01/2020 9:10', freq='1s') df

Solution 1:

Posting an answer based on the comments:

Save as XLSX rather than CSV

Save directly into Excel format (df.to_excel() instead of df.to_csv()) or save your CSV into Excel format from Excel. This will preserve the timestamps and not require any additional formatting. Credit to vmouffron and Ron for this approach.

Format the data of the CSV in Excel

The other option is to use the "Format Cell" option (for the cells or for the whole date column) with a "Custom" format (in this case, changing the template "m/d/yyyy h:mm" to "m/d/yyyy h:mm:ss"). This option allows you to retain the CSV format and the full extent of the time data. Credit to Mohammad for this answer.

Each of these options has a small downside. In the first, you have to lock users into using Excel to view the data. But there are many free tools for converting the data from one format to the other, so this is easy for users to handle. For the second option, the formatting is not permanent and has to be done every time the file is opened. This seems less convenient for users, but still may be useful in some situations.