将日期从Excel文件转换为 pandas

问题描述

我正在导入excel文件，其中的日期"列具有不同的编写方式:

I'm importing excel file, where the 'Date' column has different ways of writing:

      Date
13/03/2017
13/03/2017
13/03/2017
13/03/2017
   10/3/17
   10/3/17
    9/3/17
    9/3/17
    9/3/17
    9/3/17

导入熊猫:

df = pd.read_excel('data_excel.xls')
df.Date = pd.to_datetime(df.Date)

导致:

                     Date
               13/03/2017
64             13/03/2017
65             13/03/2017
66             13/03/2017
67    2017-10-03 00:00:00
68    2017-10-03 00:00:00
69    2017-09-03 00:00:00
70    2017-09-03 00:00:00
71    2017-09-03 00:00:00
72    2017-09-03 00:00:00

这意味着熊猫没有正确解析日期和时间:

Which means, pandas did not parse properly date and time:

10/3/17 -> 2017-10-03

当我尝试指定格式时:

df.Date = pd.to_datetime(df.Date, format='%d%m%Y')

得到错误:

ValueError: time data u'13/03/2017' does not match format '%d%m%Y' (match)

问题:

如何从Excel文件正确导入日期和时间到熊猫?

How to import properly date and times from the excel file to pandas?

推荐答案

新答案:

实际上pd.to_datetime有一个dayfirst关键字参数，在这里很有用:

Actually pd.to_datetime has a dayfirst keyword argument that is useful here:

df.Date = pd.to_datetime(df.Date,dayfirst=True)

结果:

>>> df.Date
0   2017-03-13
1   2017-03-13
2   2017-03-13
3   2017-03-13
4   2017-03-10
5   2017-03-10
6   2017-03-09
7   2017-03-09
8   2017-03-09
9   2017-03-09
Name: Date, dtype: datetime64[ns]

---

旧答案:

---

Old answer:

使用第三方模块 dateutil 即可处理变化.它有一个dayfirst关键字参数，在这里很有用:

Use the third-party module dateutil which can handle these kinds of variations. It has a dayfirst keyword argument that is useful here:

import dateutil

df = pd.read_excel('data_excel.xls')
df.Date = df.Date.apply(lambda x: dateutil.parser.parse(x,dayfirst=True))

结果:

>>> df.Date
0   2017-03-13
1   2017-03-13
2   2017-03-13
3   2017-03-13
4   2017-03-10
5   2017-03-10
6   2017-03-09
7   2017-03-09
8   2017-03-09
9   2017-03-09
Name: Date, dtype: datetime64[ns]

这篇关于将日期从Excel文件转换为 pandas 的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！