Geek Answers Handbook

How to convert string to datetime with zeros - python, pandas?

I have a series with some datetimes (like strings) and some zeros like "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 am trying to convert them to datetime:

df['Date'] = df['Date'].apply(lambda x: dt.datetime.strptime(x, '%Y-%m-%d %H:%M:%S'))

but I get the error:

time data 'nan' does not match format '%Y-%m-%d %H:%M:%S'

So, I'm trying to turn them into real zeros:

df.ix[df['Date'] == 'nan', 'Date'] = np.NaN

and repeat:

df['Date'] = df['Date'].apply(lambda x: dt.datetime.strptime(x, '%Y-%m-%d %H:%M:%S'))

but then I get the error:

should be a string, not a float

What is the fastest way to solve this problem?

+4
source share
2 answers

Just use to_datetimeand install errors='coerce'Duff for data processing:

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

the problem with the call strptimeis that it causes an error if the string or dtype is incorrect.

, :

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, .

< >

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 .

+10

, pandas . , , . ( ). , datetime, dict .

def str2time(val):
    try:
        return dt.datetime.strptime(val, '%H:%M:%S.%f')
    except:
        return pd.NaT

def TextTime2Time(s):
    times = {t : str2time(t) for t in s.unique()}
    return s.apply(lambda v: times[v])

df.date = TextTime2Time(df.date)
0

More articles:


All Articles