问题描述
我有一个包含一些日期时间(作为字符串)和一些空值作为nan"的系列:
I have a series with some datetimes (as strings) and some nulls as 'nan':
import pandas as pd, numpy as np, datetime as dt df = pd.DataFrame({'Date':['2014-10-20 10:44:31', '2014-10-23 09:33:46', 'nan', '2014-10-01 09:38:45']})
我正在尝试将这些转换为日期时间:
I'm trying to convert these to datetime:
df['Date'] = df['Date'].apply(lambda x: dt.datetime.strptime(x, '%Y-%m-%d %H:%M:%S'))
但我得到了错误:
time data 'nan' does not match format '%Y-%m-%d %H:%M:%S'
所以我试着把这些变成实际的空值:
So I try to turn these into actual nulls:
df.ix[df['Date'] == 'nan', 'Date'] = np.NaN
然后重复:
df['Date'] = df['Date'].apply(lambda x: dt.datetime.strptime(x, '%Y-%m-%d %H:%M:%S'))
然后我得到错误:
必须是字符串,不能是浮点数
must be string, not float
解决这个问题的最快方法是什么?
What is the quickest way to solve this problem?
推荐答案
只要使用to_datetime 并设置 errors='coerce' 来处理 duff 数据:
Just use to_datetime and set errors='coerce' to handle duff data:
In [321]: df['Date'] = pd.to_datetime(df['Date'], errors='coerce') df Out[321]: Date 0 2014-10-20 10:44:31 1 2014-10-23 09:33:46 2 NaT 3 2014-10-01 09:38:45 In [322]: df.info() <class 'pandas.core.frame.DataFrame'> Int64Index: 4 entries, 0 to 3 Data columns (total 1 columns): Date 3 non-null datetime64[ns] dtypes: datetime64[ns](1) memory usage: 64.0 bytes
调用 strptime 的问题是如果字符串或 dtype 不正确会引发错误.
the problem with calling strptime is that it will raise an error if the string, or dtype is incorrect.
如果你这样做了,那么它会起作用:
If you did this then it would work:
In [324]: def func(x): try: return dt.datetime.strptime(x, '%Y-%m-%d %H:%M:%S') except: return pd.NaT df['Date'].apply(func) Out[324]: 0 2014-10-20 10:44:31 1 2014-10-23 09:33:46 2 NaT 3 2014-10-01 09:38:45 Name: Date, dtype: datetime64[ns]
但是使用内置的 to_datetime 而不是调用 apply 会更快,这实际上只是循环您的系列.
but it will be faster to use the inbuilt to_datetime rather than call apply which essentially just loops over your series.
时间
In [326]: %timeit pd.to_datetime(df['Date'], errors='coerce') %timeit df['Date'].apply(func) 10000 loops, best of 3: 65.8 μs per loop 10000 loops, best of 3: 186 μs per loop
我们在这里看到使用 to_datetime 的速度提高了 3 倍.
We see here that using to_datetime is 3X faster.