时间戳

首页 > 解决方案 > dataframe to_datetime not reading dates correctly

dataframe - dataframe to_datetime not reading dates correctly

问题描述

Part of an excel file is as below. 

Action Date1               Action Date2
15.06.2018 - 06:06:30   17.06.2018 - 15:52:35
09.07.2018 - 10:12:13   09.07.2018 - 11:39:42
09.08.2018 - 15:21:45   
10.07.2018 - 10:00:13   00.00.0000 - 00:00:00

......

I want to extract the latest action dates and I have the following codes

dates = df.fillna(axis=1, method='ffill')
df['Latest date'] = dates[dates.columns[-1]]

But this codes returns the correct dates as below.

2018-06-17 15:52:35
2018-09-07 11:39:42
2018-09-08 15:21:45
2018-10-07 10:00:13

.....

I tried 

df['Latest date']=pd.to_datetime(df['Latest date'],format="%d%m%Y")

but it still gives me the same outcome.

标签： dataframepython-datetime

解决方案

使用参数format，检查http://strftime.org/：

df['Latest date']=pd.to_datetime(df['Latest date'],format="%d.%m.%Y - %H:%M:%S")

或参数dayfirst=True：

df['Latest date']=pd.to_datetime(df['Latest date'], dayfirst=True)

print (df)
          Latest date
0 2018-06-15 06:06:30
1 2018-07-16 08:53:49
2 2018-07-09 10:12:13
3 2018-08-09 15:21:45

编辑：添加参数errors='coerce'以将不可解析的值转换为NaT：

df = df.apply(lambda x: pd.to_datetime(x,format="%d.%m.%Y - %H:%M:%S", errors='coerce'))
dates = df.ffill(axis=1)
df['Latest date'] = dates.iloc[:, -1]
print (df)
        Action Date1        Action Date2         Latest date
0 2018-06-15 06:06:30 2018-06-17 15:52:35 2018-06-17 15:52:35
1 2018-07-09 10:12:13 2018-07-09 11:39:42 2018-07-09 11:39:42
2 2018-08-09 15:21:45                 NaT 2018-08-09 15:21:45
3 2018-07-10 10:00:13                 NaT 2018-07-10 10:00:13

推荐阅读