6

I am new to Pandas and Python. I want to do some date time operations in my script. I am getting date time information from a csv file in following format: 01APR2017 6:59

How to convert it into pandas datetime format? Something like: 2017-04-01 06:59:00

jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
Sourabh Saxena
  • 127
  • 1
  • 1
  • 7
  • There is a duplicate question [here](http://stackoverflow.com/questions/21269399/datetime-dtypes-in-pandas-read-csv) which is helpful. – gincard Jan 06 '17 at 08:31

1 Answers1

10

You can use to_datetime with parameter format:

s = pd.Series(['01APR2017 6:59','01APR2017 6:59'])

print (s)
0    01APR2017 6:59
1    01APR2017 6:59
dtype: object

print (pd.to_datetime(s, format='%d%b%Y %H:%M'))
0   2017-04-01 06:59:00
1   2017-04-01 06:59:00
dtype: datetime64[ns]

Another possible solution is use date_parser in read_csv:

import pandas as pd
from pandas.compat import StringIO

temp=u"""date
01APR2017 6:59
01APR2017 6:59"""
#after testing replace 'StringIO(temp)' to 'filename.csv'
parser = lambda x: pd.datetime.strptime(x, '%d%b%Y %H:%M')
df = pd.read_csv(StringIO(temp), parse_dates=[0], date_parser=parser)

print (df)
                 date
0 2017-04-01 06:59:00
1 2017-04-01 06:59:00

print (df.date.dtype)
datetime64[ns]

EDIT by comment:

If values cannot be parsed to datetime, add parameter errors='coerce' for convert to NaT:

s = pd.Series(['01APR2017 6:59','01APR2017 6:59', 'a'])
print (s)
0    01APR2017 6:59
1    01APR2017 6:59
2                 a
dtype: object

print (pd.to_datetime(s, format='%d%b%Y %H:%M', errors='coerce'))
0   2017-04-01 06:59:00
1   2017-04-01 06:59:00
2                   NaT
dtype: datetime64[ns]
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • Thanks for the quick reply. I tried solution #1 but i am getting following error - ValueError: time data ' 01APR2017 6:59' does not match format '%d%b%y %H:%M' some of the fields in that colum of csv contains no date and are blank/empty – Sourabh Saxena Jan 06 '17 at 08:57
  • Hi Jezrael, It seems the problem is that the dtype of my column is float. How to change it to object type? – Sourabh Saxena Jan 06 '17 at 09:14
  • use `df['col'] = df['col'].astype(str)`, but if data are like `01APR2017 6:59` it is string, not float. – jezrael Jan 06 '17 at 09:16