3

I have a column in my dataframe that lists time in HH:MM:SS. When I run dtype on the column, it comes up with dtype('o') and I want to be able to use it as the x-axis for plotting some of my other signals. I saw previous documentation on using to_datetime and tried to use that to convert it to a usable time format for matplotlib.

Used pandas version is 0.18.1

I used:

time=pd.to_datetime(df.Time,format='%H:%M:%S')

where the output then becomes:

time
0       1900-01-01 00:00:01 

and is carried out for the rest of the data points in the column.

Even though I specified just hour,minutes,and seconds I am still getting date. Why is that? I also tried

time.hour()

just to extract the hour portion but then I get an error that it doesn't have an 'hour' attribute.

Any help is much appreciated! Thanks! Sample data in image

2

2 Answers 2

6

Now in 2019, using pandas 0.25.0 and Python 3.7.3.

(Note : Edited answer to take plotting in account)

Even though I specified just hour,minutes,and seconds I am still getting date. Why is that?

According to pandas documentation I think it's because in a pandas Timestamp (equivalent of Datetime) object, the arguments year, month and day are mandatory, while hour, minutes and seconds are optional. Therefore if you convert your object-type object in a Datetime, it must have a year-month-day part - if you don't indicate one, it will be the default 1900-01-01.

Since you also have a Date column in your sample, you can use it to have a datetime column with the right dates that you can use to plot :

import pandas as pd

df['Time'] = df.Date + " " + df.Time
df['Time'] = pd.to_datetime(df['Time'], format='%m/%d/%Y %H:%M:%S')

df.plot('Time', subplots=True)

With this your 'Time' column will display values like : 2016-07-25 01:12:07 and its dtype is datetime64[ns].

That being said, IF you plot day by day and you only want to compare times within a day (and not dates+times), having a default date does not seem bothering as long as it's the same date for all times - the times will be correctly compared on a same day, be it a wrong one.

And in the least likely case you would still want a time-only column, this is the reverse operation :

import pandas as pd

df['Time-only'] = pd.to_datetime(df['Time'], format='%H:%M:%S').dt.time

As explained before, it doesn't have a date (year-month-day) so it cannot be a datetime object, therefore this column will be in Object format.

Sign up to request clarification or add additional context in comments.

1 Comment

Yes, it's totally ok to edit your own answer as soon as you have more/new/correct information to provide. So conclusion is, it's not a duplicate due to plotting involved. I suggest we will delete these comments which are no longer needed.
2

You can extract a time object like:

import pandas as pd

df = pd.DataFrame([['12:10:20']], columns={"time": "item"})
time = pd.to_datetime(df.time, format='%H:%M:%S').dt.time[0]

After which you can extract desired properties as:

hour = time.hour

(Source)

5 Comments

Gives me more errors TypeError Traceback (most recent call last) C:\Users\a421835\AppData\Local\Continuum\Anaconda3\lib\site-packages\pandas\tseries\tools.py in _convert_listlike(arg, box, format, name)
408 try: --> 409 values, tz = tslib.datetime_to_datetime64(arg) 410 return DatetimeIndex._simple_new(values, name=name, tz=tz) pandas\tslib.pyx in pandas.tslib.datetime_to_datetime64 (pandas\tslib.c:29768)() TypeError: Unrecognized value type: <class 'str'>
During handling of the above exception, another exception occurred: ValueError Traceback (most recent call last) <ipython-input-14-9f23978b3fbe> in <module>() ----> 1 time=pd.to_datetime(df.Time,format='%H:%M').time() C:\Users\a421835\AppData\Local\Continuum\Anaconda3\lib\site-packages\pandas\util\decorators.py in wrapper(*args, **kwargs)
I added sample data to allow running example directly
Okay that worked now. I just needed to replace your [0] with [:] to get it to apply to the entire column. Thanks for that

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.