How to find and correct date not in correct format from dataset in python using pandas

Question

my csv dataset has date column as datatype= object. I have to change that to date time. to do so i use the following code.

train=pd.read_csv(r"C:\train.csv")

train['Date'] = pd.to_datetime(train.Date,format='%Y-%d-%m')

This gives me error as below

ValueError: unconverted data remains: 2

It looks like 2 of these data might not be in actual order eg. may be 2020-02-14, is as 2020-14-02 or so. I have 17000 rows and manually it is impossible to catch 2 data.

Question is how do I correct it ? How to find which data is creating problem.

jezrael · Accepted Answer · 2020-03-22 08:33:28Z

3

You can check these rows by to_datetime with errors='coerce' for missing values for no matched vals, test it by Series.isna and filter by boolean indexing:

df = train[pd.to_datetime(train.Date,format='%Y-%d-%m', errors='coerce').isna()]
print (df)

answered Mar 22, 2020 at 8:33

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Uday mishra Over a year ago

Thanks, Doing testing, however after isna(), records are too large to figure out. Need to do lot of filtering. The problem is record are there but not sure why not getting converted. The reason need to fin. But thanks for your quick reply.

jezrael Over a year ago

@Udaymishra - Do you think many columns? Or many wrong datetimes?

Uday mishra Over a year ago

I didn't understand question, but I have one column "Date" where value error saying 2 data not converted.

jezrael Over a year ago

@Udaymishra - I only react for records are too large to figure out. So ask if resolve it.

Collectives™ on Stack Overflow

How to find and correct date not in correct format from dataset in python using pandas

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related