CODEWITHSUNDEEP
pythonpandasdatetime
NotAName
NotAName

Reputation: 4347

Pandas to_datetime error 'unconverted data remains'

I'm trying to convert date column in my Pandas DataFrame to datetime format. If I don't specify date format, it works fine, but then further along in the code I get issues because of different time formats.

The original dates looks like this 10/10/2019 6:00 in european date format.

I tried specifying format like so:

df['PeriodStartDate'] = pd.to_datetime(df['PeriodStartDate'], 
          format="%d/%m/%Y")

which results in an error: unconverted data remains 6:00

I then tried to update format directive to format="%d/%m/%Y %-I/%H" which comes up with another error: '-' is a bad directive in format '%d/%m/%Y %-I/%H' even though I thought that to_datetime uses the same directives and strftime and in the latter %-I is allowed.

In frustration I then decided to chop off the end of the string since I don't really need hours and minutes:

    df['PeriodStartDate'] = df['PeriodStartDate'].str[:10]
    df['PeriodStartDate'] = pd.to_datetime(df['PeriodStartDate'], 
          format="%d/%m/%Y")

But this once again results in an error: ValueError: unconverted data remains: which of course comes from the fact that some dates have 9 digits like 3/10/2019 6:00

Not quite sure where to go from here.

Upvotes: 4

Views: 10757

Answers (2)

Andy L.
Andy L.

Reputation: 25249

format %H:%M would work(don't forget the : in between)

pd.to_datetime('10/10/2019 6:00', format="%m/%d/%Y %H:%M")

Out[1049]: Timestamp('2019-10-10 06:00:00')

pd.to_datetime('3/10/2019 18:00', format="%d/%m/%Y %H:%M")

Out[1064]: Timestamp('2019-10-03 18:00:00')

Upvotes: 5

NotAName
NotAName

Reputation: 4347

Oh, I feel so dumb. I figured out what the issue was. For some reason I thought that hours were in a 12-hour format, but they were in fact in a 24-hour format, so changing directive to "%d/%m/%Y %H:%M" solved it.

Upvotes: 2

Related Questions