9

I have an object column in a pandas dataframe in the format dd/mm/yyyy, that I want to convert with to_datetime.

I tried to convert it to datetime using the below:

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

I get the following errors:

TypeError: Unrecognized value type: <class 'str'>
ValueError: unconverted data remains:

Does this mean that there is a blank row somewhere, I have checked the original csv and I cannot see one.

Matt Houston
  • 193
  • 1
  • 1
  • 4
  • 2
    It might be because of the formatting of your data. Check https://stackoverflow.com/questions/32583256/convert-pandas-column-to-datetime-ii – ysearka Aug 17 '18 at 15:46

1 Answers1

14

It means you have an extra space. Though pd.to_datetime is very good at parsing dates normally without any format specified, when you actually specify a format, it has to match EXACTLY.

You can likely solve your issue by adding .str.strip() to remove the extra whitespace before converting.

import pandas as pd
df['Time stamp'] = pd.to_datetime(df['Time stamp'].str.strip(), format='%d/%m/%Y')

Alternatively, you can take advantage of its ability to parse various formats of dates by using the dayfirst=True argument

df['Time stamp'] = pd.to_datetime(df['Time stamp'], dayfirst=True)

Example:

import pandas as pd
df = pd.DataFrame({'Time stamp': ['01/02/1988', '01/02/1988 ']})

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

ValueError: unconverted data remains:

pd.to_datetime(df['Time stamp'].str.strip(), format='%d/%m/%Y')
#0   1988-02-01
#1   1988-02-01
#Name: Time stamp, dtype: datetime64[ns]

pd.to_datetime(df['Time stamp'], dayfirst=True)
#0   1988-02-01
#1   1988-02-01
#Name: Time stamp, dtype: datetime64[ns]
Community
  • 1
  • 1
ALollz
  • 57,915
  • 7
  • 66
  • 89