Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to define format when using pandas to_datetime?

Tags:

I want to plot RESULT vs TIME based on a testresult.csv file that has following format, and I have trouble to get the TIME column's datatype defined properly.

TIME,RESULT   03/24/2016 12:27:11 AM,2   03/24/2016 12:28:41 AM,76   03/24/2016 12:37:23 AM,19   03/24/2016 12:38:44 AM,68   03/24/2016 12:42:02 AM,44   ... 

To read the csv file, this is the code I wrote: raw_df = pd.read_csv('testresult.csv', index_col=None, parse_dates=['TIME'], infer_datetime_format=True)
This code works, but it is extremely slow, and I assume that the infer_datetime_format takes time. So I tried to read in the csv by default first, and then convert the object dtype 'TIME' to datetime dtype by using to_datetime(), and I hope by defining the format, it might expedite the speed.

raw_df =  pd.read_csv('testresult.csv') raw_df.loc['NEWTIME'] = pd.to_datetime(raw_df['TIME'], format='%m/%d%Y %-I%M%S %p') 

This code complained error:

"ValueError: '-' is a bad directive in format '%m/%d%Y %-I%M%S %p'"

like image 882
ju. Avatar asked Apr 25 '16 18:04

ju.


People also ask

What is pandas datetime format?

By default pandas datetime format is YYYY-MM-DD ( %Y-%m-%d ).


1 Answers

The format you are passing is invalid. The dash between the % and the I is not supposed to be there.

df['TIME'] = pd.to_datetime(df['TIME'], format="%m/%d/%Y %I:%M:%S %p") 

This will convert your TIME column to a datetime.


Alternatively, you can adjust your read_csv call to do this:

pd.read_csv('testresult.csv', parse_dates=['TIME'],      date_parser=lambda x: pd.to_datetime(x, format='%m/%d/%Y %I:%M:%S %p')) 

Again, this uses the appropriate format with out the extra -, but it also passes in the format to the date_parser parameter instead of having pandas attempt to guess it with the infer_datetime_format parameter.

like image 76
Andy Avatar answered Oct 07 '22 15:10

Andy