Search code examples
python-3.xpandasstrftimestring-to-datetime

pandas to_datetime couldn't parse string into dates and return strings


I have a Series s as

10241715000
  201709060
   11202017
     112017
     111617
     102417
     110217
    1122018

I tried the following code to convert s into datetime;

pd.to_datetime(s.str[:7], format='%-m%d%Y', errors='coerce')

but it returned s as it is without any conversions been done, I was expecting something like,

NaT
NaT
2017-01-20
NaT
NaT
NaT
NaT
2018-01-12

The format is defined according to strftime directives that %-m indicates Month as a decimal number, e.g. 1; %Y indicates Year as a decimal number, e.g. 2018. I am wondering what is the issue here. I am using Pandas 0.22.0 and Python 3.5.

UPDATE

data = np.array(['10241715000','201709060','11202017','112017','111617','102417',
 '110217','1122018'])

s = pd.Series(data)

pd.to_datetime(s.str[-7:], format='%-m%d%Y', errors='coerce')

0    1715000
1    1709060
2    1202017
3     112017
4     111617
5     102417
6     110217
7    1122018
dtype: object

Solution

  • It should be -7 not 7 for str slice

    pd.to_datetime(s.astype(str).str[-7:], format='%m%d%Y', errors='coerce')
    Out[189]: 
    0          NaT
    1          NaT
    2   2017-01-20
    3   2017-01-01
    4          NaT
    5          NaT
    6          NaT
    7   2018-11-02
    Name: a, dtype: datetime64[ns]
    

    Update

    pd.to_datetime(s.str[-7:].str.pad(8,'left','0'), format='%m%d%Y', errors='coerce')
    Out[208]: 
    0          NaT
    1          NaT
    2   2017-01-20
    3          NaT
    4          NaT
    5          NaT
    6          NaT
    7   2018-01-12
    dtype: datetime64[ns]