python pandas错误地读取excel日期 [英] python pandas incorrectly reading excel dates

问题描述

我有一个Excel文件，其日期格式如下:

I have an excel file with dates formatted as such:

22.10.07 16:00
22.10.07 17:00
22.10.07 18:00
22.10.07 19:00

使用熊猫的parse方法读取数据后，日期几乎正确读取:

After using the parse method of pandas to read the data, the dates are read almost correctly:

In [55]: nts.data['Tid'][10000:10005]
Out[55]:
10000    2007-10-22 15:59:59.997905
10001    2007-10-22 16:59:59.997904
10002    2007-10-22 17:59:59.997904
10003    2007-10-22 18:59:59.997904

我该怎么做?a)使它正常工作，或b)是否有技巧可以轻松解决此问题? (例如，日期时间的某种舍入"功能)

What do I need to do to either a) get it to work correctly, or b) is there a trick to fix this easily? (e.g. some kind of 'round' function for datetime)

推荐答案

我遇到了同样的问题，并没有通过使用Pandas解析日期，而是将自己的函数(如下所示)应用于相关列来解决了这个问题):

I encountered the same issue and got around it by not parsing the dates using Pandas, but rather applying my own function (shown below) to the relevant column(s) of the dataframe:

def ExcelDateToDateTime(xlDate):
    epoch = dt.datetime(1899, 12, 30)
    delta = dt.timedelta(hours = round(xlDate*24))
    return epoch + delta

df = pd.DataFrame.from_csv('path')

df['Date'] = df['Date'].apply(ExcelDateToDateTime)

注意:这将忽略小时级别以下的任何时间间隔，但这就是我所需要的，并且从您的示例中可以看出，您也可能会遇到这种情况.

Note: This will ignore any time granularity below the hour level, but that's all I need, and it looks from your example that this could be the case for you too.

这篇关于python pandas错误地读取excel日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！