如何防止Excel从时间戳中删除秒数? [英] How can I prevent Excel from removing seconds from timestamps?

问题描述

我有一个具有第二个分辨率的时间序列的DataFrame，并将其保存到CSV文件中:

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 = pd.DataFrame(index=dr, data=range(len(dr)))
df.to_csv('some_data.csv', header=False)

然后我可以在Excel中打开它，一切看起来都很好:

I can then open it in Excel and everything looks good:

如果我随后将文件保存在Excel中(不进行任何更改)，则在重新打开时，秒将舍入为0 :

If I then save the file in Excel (without changing anything), on reopening the seconds are rounded down to 0:

在记事本中查看Excel保存的文件显示秒已丢失:

Looking at the Excel-saved file in notepad shows that the seconds have been lost:

1/1/2020 9:00,0
1/1/2020 9:00,1
1/1/2020 9:00,2
...

当保存为副本或覆盖时会发生更改.奇怪的是，如果您在保存原始文档后将其保持打开状态，那么您仍然会看到保留的秒数，直到关闭并重新打开.

The change occurs when saving as a copy or overwriting. And oddly, if you keep the original document open after saving it, you will still see the seconds preserved, until you close and reopen.

在上下文中，我正在为其他(不使用Python的)用户编写文件，这些用户可能会使用Excel与他们的数据进行交互.甚至调整列的大小也会提示进行保存，因此我发现它们很可能会无意间丢失了数据.

In context, I am writing files for other (non-Python-using) users who will likely be using Excel to interact with their data. And even resizing a column prompts for a save, so I find it very likely that they could be inadvertently losing data.

虽然我使用Python创建此示例，但我也看到了用其他语言编写的时间戳的问题.

是否存在更好的记录时间序列数据的方法来防止这种情况发生?还是我可以与以这种格式获取数据的用户共享Excel中的修复程序(最好是永久性选项或设置)?

推荐答案

根据评论发布答案:

另存为XLSX而不是CSV

直接保存为Excel格式( df.to_excel()而不是 df.to_csv())，或从Excel将CSV保存为Excel格式.这将保留时间戳，并且不需要任何其他格式.贷方到 vmouffron 和

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.

在Excel中设置CSV数据的格式

另一种选择是使用设置单元格格式"选项(用于单元格或用于整个日期列)，并带有自定义"格式(在这种情况下，将模板"m/d/yyyy h:mm"更改为"m/d/yyyy h:mm:ss").此选项使您可以保留CSV格式和时间数据的完整范围.请为此信穆罕默德.

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.

这些选项中的每一个都有一个小的缺点.首先，您必须将用户锁定为使用Excel来查看数据.但是有许多免费的工具可以将数据从一种格式转换为另一种格式，因此用户很容易处理.对于第二个选项，格式化不是永久性的，每次打开文件时都必须进行格式化.对于用户来说，这似乎不太方便，但在某些情况下仍然有用.

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.

这篇关于如何防止Excel从时间戳中删除秒数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！