BUG: DateTime Index format bug when importing from .csv file

[filipporemonato]

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• (optional) I have confirmed this bug exists on the master branch of pandas.

---

Code Sample, a copy-pastable example

data = pd.read_csv(file_path, engine='python', sep=';')
data = data.set_index('timestamp_utc')
data.index = pd.to_datetime(data.index, utc=True)

Problem description

I am sorry but I cannot provide a mwe because time is against me, but I still want to report the problem.
I have loaded a .csv file which has a column called "timestamp_utc" containing the full timestamp, date+time.
I have then set the timestamp_utc column as my index, and converted it to datetime object.
My code was not working because some dates were apparently missing while, by hand inspectyion of the .csv file, they were present.

The .csv file has all dates in dd/mm/yyyy format, from 02/12/2019 to 03/06/2020, but after running the code above and checking the index in the dataframe, this was shown (see screenshot):

so the index jumps from 2019/12/02 (which is yyyy/dd/mm format) to 2019/02/13 (which is yyyy/mm/dd format)!

Expected Output

I do not mind much if Pandas choses to import something as yyyy/dd/mm or yyyy/mm/dd, I know that I can use the "dayfirst" option in case, but at least Pandas should be consistent. This simply breaks the logic of indexes, which should always be increasing values. I find it quite surprising that no warning was given during the set_index operation.

These kind of issues happen on a daily basis when working with Pandas and in my group several tens of hours are spent debugging what should be a straightforward thing. My suggestion to avoid these problems in the future is that Pandas should expect and treat dates as if they were dd/mm/yyyy or yyyy/mm/dd format, throwing (at least) a warning when a date in mm/dd/yyyy format is found, so that the user can correct the import.

Output of pd.show_versions()

INSTALLED VERSIONS

commit : None
python : 3.7.7.final.0
python-bits : 64
OS : Windows
OS-release : 10
machine : AMD64
processor : Intel64 Family 6 Model 142 Stepping 10, GenuineIntel
byteorder : little
LC_ALL : None
LANG : en_GB.UTF-8
LOCALE : None.None

pandas : 1.0.5
numpy : 1.18.5
pytz : 2020.1
dateutil : 2.8.1
pip : 20.1.1
setuptools : 49.2.0.post20200714
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : 7.16.1
pandas_datareader: None
bs4 : None
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : None
matplotlib : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
pytest : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
xlsxwriter : None
numba : None

[simonjayhawkins]

I am sorry but I cannot provide a mwe because time is against me

without a mwe, I suspect that this is a duplicate of #12585, so closing. Feel free to open a new issue with a mwe if the issue is not related to #12585

[filipporemonato]

Hei Simon, thanks for picking this up. I agree on the duplicate, sorry for opening it but as I said I was really strict on time. Glad you found out and closed. I totally understand the prioritization issue raised in the other thread by @jreback, but really dates should be the very foundations of a dataframe management system. I feel like my suggestion above, which has also been proposed by others, would work fine. Thanks a lot for your work. From: Simon Hawkins <notifications@github.com> Sent: fredag 24. juli 2020 16:17 To: pandas-dev/pandas <pandas@noreply.github.com> Cc: Filippo Remonato <Filippo.Remonato@sintef.no>; Author <author@noreply.github.com> Subject: Re: [pandas-dev/pandas] BUG: DateTime Index format bug when importing from .csv file (#35402) I am sorry but I cannot provide a mwe because time is against me without a mwe, I suspect that this is a duplicate of #12585<https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fpandas-dev%2Fpandas%2Fissues%2F12585&data=02%7C01%7Cfilippo.remonato%40sintef.no%7Ce8b1067ce7194a44e7ed08d82fdc4768%7Ce1f00f39604145b0b309e0210d8b32af%7C1%7C1%7C637311970440959754&sdata=VVItkdP8HxJPZdjG9%2FA4vMFryAKq3zF1191oAjU%2B%2FPw%3D&reserved=0>, so closing. Feel free to open a new issue with a mwe if the issue is not related to #12585<https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fpandas-dev%2Fpandas%2Fissues%2F12585&data=02%7C01%7Cfilippo.remonato%40sintef.no%7Ce8b1067ce7194a44e7ed08d82fdc4768%7Ce1f00f39604145b0b309e0210d8b32af%7C1%7C1%7C637311970440969714&sdata=UIaepY6IsvVVEbrmayG8vmaAtKAx1iKzepY0lc657Hs%3D&reserved=0> — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fpandas-dev%2Fpandas%2Fissues%2F35402%23issuecomment-663560771&data=02%7C01%7Cfilippo.remonato%40sintef.no%7Ce8b1067ce7194a44e7ed08d82fdc4768%7Ce1f00f39604145b0b309e0210d8b32af%7C1%7C1%7C637311970440979669&sdata=fDcmO8wu5wOJrbA1G9Af5HKsHK3z8VIrkBIPPuBuAWg%3D&reserved=0>, or unsubscribe<https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FABPSTZW5JI5464ZXXSS4FK3R5GJUNA%0D%0A+NCNFSM4PGVNBMQ&data=02%7C01%7Cfilippo.remonato%40sintef.no%7Ce8b1067ce7194a44e7ed08d82fdc4768%7Ce1f00f39604145b0b309e0210d8b32af%7C1%7C0%7C637311970440979669&sdata=8fzFQi975G%2B5m83mpsUlAmpGBaRKA0ggdIXDelssrnk%3D&reserved=0>.