Python Forum
splitting time (h,m,s) from dataframe column
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
splitting time (h,m,s) from dataframe column
#1
Hi, I'm kind of new to python and I have been trying to split a time column in order to convert it all into a single unit but my splitting function does not work.

mydata= pd.read_excel("myclock.xlsx",sheet_name="mytime",skiprows=0, index_col = [1])

Name ID Date Check In Check Out
Worker_one 00001 Friday, September 28, 2018 9:50:35 18:50:23
Worker_two 00002 Friday, September 28, 2018 10:43:03 19:54:34
Worker_tree 00003 Friday, September 28, 2018 9:37:45 18:38:01
Worker_four 00004 Friday, September 28, 2018 8:44:07 17:30:04
Worker_five 00005 Friday, September 28, 2018 8:30:54 17:30:30
Worker_six 00006 Friday, September 28, 2018 9:01:43 18:04:50
Worker_seven 00007 Friday, September 28, 2018 8:34:16 17:34:50
Worker_eight 00008 Friday, September 28, 2018 11:57:43 20:58:54
Worker_nine 00009 Friday, September 28, 2018 9:03:53 18:40:43
Worker_ten 00010 Friday, September 28, 2018 8:23:04 18:45:32

from datetime import time

:
try:
hours, minutes, seconds = time_str.split(":")
except ValueError:
return
return int(hours)*60+ int(minutes) + int(seconds)/60.0


Check_Out_Minutes = mydata['Check Out'].apply(time_to_minutes)

And it returns an attribute error

---------------------------------------------------------------------------
AttributeError Traceback (most recent call last)
<ipython-input-13-d84e37515c72> in <module>()
----> 1 Check_Out_Minutes = mydata['Check Out'].apply(time_to_minutes)

C:\Program Files\Anaconda3\lib\site-packages\pandas\core\series.py in apply(self, func, convert_dtype, args, **kwds)
2549 else:
2550 values = self.asobject
-> 2551 mapped = lib.map_infer(values, f, convert=convert_dtype)
2552
2553 if len(mapped) and isinstance(mapped[0], Series):

pandas/_libs/src/inference.pyx in pandas._libs.lib.map_infer()

<ipython-input-5-c033186c9b10> in time_to_minutes(time_str)
1 def time_to_minutes(time_str):
2 try:
----> 3 hours, minutes, seconds = time_str.split(':')
4 except ValueError:
5 return

AttributeError: 'datetime.time' object has no attribute 'split'
Reply
#2
One way to get datetime.time object from string and access hours, minutes and seconds:

>>> import datetime
>>> check_in = '9:50:35'
>>> check_in_time = datetime.datetime.strptime(check_in, '%H:%M:%S').time()
>>> check_in_time.hour
9
>>> check_in_time.minute
50
>>> check_in_time.second
35
>>> check_in_time
datetime.time(9, 50, 35)
I'm not 'in'-sane. Indeed, I am so far 'out' of sane that you appear a tiny blip on the distant coast of sanity. Bucky Katt, Get Fuzzy

Da Bishop: There's a dead bishop on the landing. I don't know who keeps bringing them in here. ....but society is to blame.
Reply
#3
(Oct-09-2018, 07:05 AM)dedaelfl Wrote:
Check_Out_Minutes = mydata['Check Out'].apply(time_to_minutes)
Output:
And it returns an attribute error --------------------------------------------------------------------------- AttributeError Traceback (most recent call last) <ipython-input-13-d84e37515c72> in <module>() ----> 1 Check_Out_Minutes = mydata['Check Out'].apply(time_to_minutes) C:\Program Files\Anaconda3\lib\site-packages\pandas\core\series.py in apply(self, func, convert_dtype, args, **kwds) 2549 else: 2550 values = self.asobject -> 2551 mapped = lib.map_infer(values, f, convert=convert_dtype) 2552 2553 if len(mapped) and isinstance(mapped[0], Series): pandas/_libs/src/inference.pyx in pandas._libs.lib.map_infer() <ipython-input-5-c033186c9b10> in time_to_minutes(time_str) 1 def time_to_minutes(time_str): 2 try: ----> 3 hours, minutes, seconds = time_str.split(':') 4 except ValueError: 5 return AttributeError: 'datetime.time' object has no attribute 'split'

As the exception message states, mydata['Check Out'] Series contain objects of type datetime.time - and you try to treat them as strings.

Change your function to calculate time from datetime.time object - and you are set
Output:
In [22]: def time_2_mins(t): ...: return t.hour * 60 + t.minute + t.second / 60 ...: times = pd.Series([time(12, 15, 40), time(8, 25, 6)]) ...: print(times) ...: times.apply(time_2_mins) ...: ...: 0 12:15:40 1 08:25:06 dtype: object Out[22]: 0 735.666667 1 505.100000 dtype: float64
Test everything in a Python shell (iPython, Azure Notebook, etc.)
  • Someone gave you an advice you liked? Test it - maybe the advice was actually bad.
  • Someone gave you an advice you think is bad? Test it before arguing - maybe it was good.
  • You posted a claim that something you did not test works? Be prepared to eat your hat.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  concat 3 columns of dataframe to one column flash77 2 778 Oct-03-2023, 09:29 PM
Last Post: flash77
  HTML Decoder pandas dataframe column mbrown009 3 962 Sep-29-2023, 05:56 PM
Last Post: deanhystad
  attempt to split values from within a dataframe column mbrown009 8 2,224 Apr-10-2023, 02:06 AM
Last Post: mbrown009
  New Dataframe Column Based on Several Conditions nb1214 1 1,783 Nov-16-2021, 10:52 PM
Last Post: jefsummers
  Putting column name to dataframe, can't work. jonah88888 1 1,804 Sep-28-2021, 07:45 PM
Last Post: deanhystad
  Setting the x-axis to a specific column in a dataframe devansing 0 1,994 May-23-2021, 12:11 AM
Last Post: devansing
Question [Solved] How to refer to dataframe column name based on a list lorensa74 1 2,239 May-17-2021, 07:02 AM
Last Post: lorensa74
Question Pandas - Creating additional column in dataframe from another column Azureaus 2 2,919 Jan-11-2021, 09:53 PM
Last Post: Azureaus
  Iterate through dataframe to extract delta of a particular time period lynnette1983 1 1,628 Oct-22-2020, 12:19 AM
Last Post: scidam
  Filter data based on a value from another dataframe column and create a file using lo pawanmtm 1 4,245 Jul-15-2020, 06:20 PM
Last Post: pawanmtm

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020