lucia_fernandez0 views
I read a CSV exported from our shop. The order_date column looks like 03/11/2025 (day first), but pandas says the type is object and sorting puts 25/12/2025 before 03/11/2025. A few cells contain junk such as not a date.
How do I convert the column and keep track of the bad rows?
Be kind and specific. Read the community guidelines.
Accepted answer
The CSV reader does not guess dates, so the column arrives as text, and text sorts character by character, which is why 25/12/2025 comes before 03/11/2025. Convert it yourself and say which order the parts are in:
import pandas as pd
df["order_date"] = pd.to_datetime(df["order_date"], format="%d/%m/%Y", errors="coerce")
bad = df[df["order_date"].isna()]
format="%d/%m/%Y" states that the day comes first, so 03/11/2025 is 3 November rather than 11 March. errors="coerce" turns anything that is not a date, such as not a date, into a missing value instead of stopping with an error, and bad then holds exactly the rows to look at. Sorting by the column now orders by real dates, with the missing ones last.
Worked. Eleven rows were bad and they were all a different export format from one shop. Better to know than to have them silently dropped.