Convert time data to string for calculation in Python Pandas DataFrames-CodePudding

I have a list of dictionaries:

mylist=
[{'Start Time': '02:13:40', 'End Time': '14:13:10'},
{'Start Time': '03:12:30', 'End Time': '13:07:45'},
{'Start Time': '05:03:20', 'End Time': '17:23:10'},
... 
{'Start Time': '08:23:40', 'End Time': '19:23:40'}]

I wanted to append a new column that records the time difference between Start Time and End Time. I tried the following:

dfmylist = pd.DataFrame(mylist)
dfmylist["new"] = (dfmylist['End Time'] - dfmylist['Start Time'])

It gave me the error: TypeError: unsupported operand type(s) for -: 'str' and 'str'

I think it is because the time data has not been converted to string, so I tried the following:

dfmylist['Start Time'] = pd.to_timedelta(dfmylist['Start Time'] ).dt.strftime('%hh:%mm:%ss')

But I received the error: ValueError: expected hh:mm:ss format

CodePudding user response：

You can do that in one line with the following.

dfmylist["new"] = pd.to_datetime(dfmylist["End Time"]) - pd.to_datetime(dfmylist["Start Time"])

CodePudding user response：

We can add the following format %H:%M:%S like so :

dfmylist['Start Time'] = pd.to_datetime(dfmylist['Start Time'], format="%H:%M:%S")
dfmylist['End Time'] = pd.to_datetime(dfmylist['End Time'], format="%H:%M:%S")

To get the expected result :

>>> dfmylist["new"] = (dfmylist['End Time'] - dfmylist['Start Time'])
>>> dfmylist
    Start Time           End Time               new
0   1900-01-01 02:13:40  1900-01-01 14:13:10    0 days 11:59:30
1   1900-01-01 03:12:30  1900-01-01 13:07:45    0 days 09:55:15
2   1900-01-01 05:03:20  1900-01-01 17:23:10    0 days 12:19:50
3   1900-01-01 08:23:40  1900-01-01 19:23:40    0 days 11:00:00