Click here to Skip to main content
15,877,103 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I wrote a program where a dataframe is traversed & when any column with the name 'Date' is encountered, all the rows under that column are supposed to be converted to a 'datetime' object using 'pd.to_datetime' in the format mentioned. But this method is not working for me.

The 'Date' column in my dataset consists of dates in various formats & with different separators. When I do debugging I get an error message for those dates that are not in the format specified.

Isn't the whole point of using the method a way to convert dates in any format to a datetime object & in the format specified?

What I have tried:

from dateutil.parser import parse
import re
from datetime import datetime
import calendar

import pandas as pd

def date_fun(filepath):
   date_list=['Date', 'date', 'Dates', 'dates']
   for i in filepath.columns:
      for j in date_list:     
          if i==j:
            filepath[i]=pd.to_datetime(filepath[i], format='%d-%m-%Y')

main_path = pd.read_csv('C:/Data_Cleansing/lockdown_us.csv')

Error: time data '26-2016-09' does not match format '%d-%m-%Y' (match)
Where is the mistake in my code?
Updated 7-Jan-23 5:23am

1 solution

Seriously? How do you not see this? The error message told you exactly what is wrong.

Your date format specifier is '%d-%m-%y'. It's looking for a string with the values IN ORDER: day, month, year. The date string you're passing in is day, YEAR, MONTH. The to_dateTime method cannot guess at which value goes where. You have to tell it which value is in which position. This is why you have to specify the date format string. In your case, the format string you specified is wrong, '%d-%m-%y' when it should be '%d-%y-%m'.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900