How to stop pandas converting some dates automatically when reading a csv

Question

I have a csv, and one column is date, format dd/mm/yyyy.

I read it using z=pd.read_csv('property_scrape.csv')

My raw data is:

After I read it in, some of the values are kept in the format I downloaded (dd/mm/yyyy), while somewhere in the middle, the dates are converted to yyyy-mm-dd:

27 01/10/2019 28 01/10/2019 29 01/10/2019 ... 21092 2020-08-22 21093 2020-08-22 21094 2020-08-22

Name: Date, Length: 21122, dtype: object

Does anyone know why this happens?

Also, is there a way to ensure that this date column is always read the correct/constant way?

is it possible that the date format in the csv is not consistent? maybe pandas checks and converts the chunk that seem to match some form, converts that and leaves the rest. you could probably write a function to fix that after reading in the data. — sammywemmy
– sammywemmy, Commented Aug 22, 2020 at 22:13
that was my first thought, but going through the csv, all dates are of that shown in the input style, dd/mm/yyyy. Going further, I found the exact entry the dates are different, and in the csv they are the same format — frank
– frank, Commented Aug 22, 2020 at 22:18
sounds like this could be relevant: stackoverflow.com/questions/55309199/… — dm2
– dm2, Commented Aug 22, 2020 at 22:48

Doug F · Accepted Answer · 2020-08-23 00:10:53Z

The problem is Pandas samples the first row and thinks it is MM/DD/YYYY instead of DD/MM -- there isn't a real way to know this. Then later when it finds 22, which not a valid MM it defaults to object/string.

You can add flag infer_datetime_format=False and it should read as strings then you can parse it -- you can pass a lambda to read_csv as well, not sure if an easier way to just pass a format string -- see article article dsexchange 34357

I like the theory, but the value 30/05/2020 is read in and parsed correctly, as is 13/10/2019

Collectives™ on Stack Overflow

How to stop pandas converting some dates automatically when reading a csv

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related