Error with dayfirst option and extra spaces in dates

[Scheibs]

I found a bad behaviour for the dayfirst option in to_datetime. It's seems that dayfirst= True is only applied on the first row.

import pandas as pd
df = pd.DataFrame(['2018-04-11 10:30:00','2018-04-11  10:30:00','2018-04-11  10:15:00'],columns=['date'])
df["date dayFristTrue"] = pd.to_datetime(df["date"], dayfirst=True)
df["date dayFristFalse"] = pd.to_datetime(df["date"], dayfirst=False)
print df
                   date   date dayFristTrue  date dayFristFalse
0   2018-04-11 10:30:00 2018-04-11 10:30:00 2018-04-11 10:30:00
1  2018-04-11  10:30:00 2018-11-04 10:30:00 2018-04-11 10:30:00
2  2018-04-11  10:15:00 2018-11-04 10:15:00 2018-04-11 10:15:00

Output of pd.show_versions()

INSTALLED VERSIONS ------------------ commit: None python: 2.7.14.final.0 python-bits: 64 OS: Windows OS-release: 7 machine: AMD64 processor: Intel64 Family 6 Model 94 Stepping 3, GenuineIntel byteorder: little LC_ALL: None LANG: fr LOCALE: None.None

pandas: 0.23.3
pytest: 3.3.2
pip: 9.0.1
setuptools: 38.4.0
Cython: 0.27.3
numpy: 1.14.0
scipy: 1.0.0
pyarrow: None
xarray: 0.10.7
IPython: 5.4.1
sphinx: 1.6.6
patsy: 0.5.0
dateutil: 2.6.1
pytz: 2017.3
blosc: None
bottleneck: 1.2.1
tables: 3.4.2
numexpr: 2.6.4
feather: None
matplotlib: 2.1.2
openpyxl: 2.4.10
xlrd: 1.1.0
xlwt: 1.3.0
xlsxwriter: 1.0.2
lxml: 4.2.2
bs4: 4.6.0
html5lib: 1.0.1
sqlalchemy: 1.2.1
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

[TomAugspurger]

I think it's the extra spaces in your later dates:

In [12]: pd.to_datetime('2018-04-11 10:30:00', dayfirst=True)
Out[12]: Timestamp('2018-04-11 10:30:00')

In [13]: pd.to_datetime('2018-04-11  10:30:00', dayfirst=True)
Out[13]: Timestamp('2018-11-04 10:30:00')

I think there's a warning in the docs about this kind of behavior:

    Warning: dayfirst=True is not strict, but will prefer to parse
    with day first (this is a known bug, based on dateutil behavior).

Though, strangely, python-datetuil does handle the spaces. Perhaps an issue in pandas then.

In [26]: dateutil.parser.parse('2018-04-11 10:30:00', dayfirst=True)
Out[26]: datetime.datetime(2018, 11, 4, 10, 30)

In [27]: dateutil.parser.parse('2018-04-11  10:30:00', dayfirst=True)
Out[27]: datetime.datetime(2018, 11, 4, 10, 30)

[chris-b1]

The issue here is that your date 'looks' like it is ISO-8601 formatted - YYYY-MM-DD, which we have a special fastpath for.

The dayfirst keyword was really targeted to disambiguate MM/DD/YYYY vs DD/MM/YYYY. If it is at all within your control I'd strongly recommend against storing dates in YYYY-DD-MM.

That said, would consider this a bug, as @TomAugspurger showed dateutil can handle this, you are welcome to have a look.

[MarcoGorelli]

Thanks for the report

As of PDEP4, this is fixed:

In [45]: df = pd.DataFrame(['2018-04-11  10:30:00','2018-04-11  10:30:00','2018-04-11  10:15:00'],columns=['date'])

In [46]: df["date dayFristTrue"] = pd.to_datetime(df["date"], dayfirst=True)

In [47]: df["date dayFristFalse"] = pd.to_datetime(df["date"], dayfirst=False)

In [48]: df
Out[48]: 
                   date   date dayFristTrue  date dayFristFalse
0  2018-04-11  10:30:00 2018-11-04 10:30:00 2018-04-11 10:30:00
1  2018-04-11  10:30:00 2018-11-04 10:30:00 2018-04-11 10:30:00
2  2018-04-11  10:15:00 2018-11-04 10:15:00 2018-04-11 10:15:00

Closing then, but thanks for the report!