Durations: Finish Times with Timedelta

Finish times typed as text such as '0:27:15' sort wrongly and can't be averaged, so pd.to_timedelta turns them into durations, the ones it can't read, such as '27:15', are found with errors='coerce' and fixed, dt.total_seconds() turns a duration into minutes and a pace per mile, and whole minutes and seconds turn it back into text people can read.

Journeyman · Python · Updated

By

After this lesson you can

  • Turn times such as '0:27:15' into durations with pd.to_timedelta, and find and fix the ones it can't read
  • Work out minutes and pace per mile with dt.total_seconds(), and format a duration for people as minutes and seconds

Key takeaways

  • A duration such as a finish time is a length of time, not a moment, and pandas stores it as a Timedelta.
  • pd.to_timedelta() reads text written as hours:minutes:seconds, such as '0:27:15', and stops on '27:15' unless you add the missing hours first.
  • .dt.total_seconds() turns a duration into a number of seconds, so dividing by 60 gives minutes you can average.
  • Dividing a finish time by the distance gives the pace, the time each mile took.
  • Whole minutes (// 60) and the seconds left over (% 60) turn a duration back into text such as 8:47 for people to read.

How long a delivery took, how long a call lasted, how long a runner was on the course: a duration is a length of time, not a moment on the calendar, and files store it as text such as 0:27:15. To average it, compare it or divide it, it has to become a real duration. You'll do this for anything timed, from support calls to video lengths to race results.

The example is the Hillcrest 5K Fun Run's results, results.csv: one row for each of the 432 runners who finished, in finishing order, with a bib (the runner's race number) and the finish_time the timing volunteers typed. A 5K is 3.1 miles. The runners and the race are our own made-up data.

Times stored as text

The race director wants the fastest and slowest times for the newsletter:

import pandas as pd

res = pd.read_csv("results.csv")
print("rows:", len(res))
print("fastest:", res["finish_time"].min())
print("slowest:", res["finish_time"].max())
rows: 432
fastest: 0:17:30
slowest: 59:17

The slowest can't be right: the file is in finishing order, and the last three rows say otherwise:

res["finish_time"].tail(3).tolist()
['1:11:22', '1:11:27', '1:12:00']

finish_time is text, compared character by character, and 59:17 starts with a 5, which comes after the 1 of 1:12:00. Some volunteers also left off the hours for runners under an hour, typing 59:17 for 0:59:17. Two problems, one cause: these are durations stored as text.

pd.to_timedelta, and the times it can't read

pd.to_timedelta() turns text into a Timedelta, pandas' type for a length of time (Parts of a Date: .dt made one by subtracting two dates). It reads hours:minutes:seconds:

pd.to_timedelta(res["finish_time"])
ValueError: expected hh:mm:ss format

It wants hh:mm:ss, hours:minutes:seconds, and stops on the first time typed without the hours. As with dates, errors="coerce" lets it finish, with NaT for each time it can't read, so you can count and look:

times = pd.to_timedelta(res["finish_time"], errors="coerce")
print("missing after:", times.isna().sum())
res["finish_time"][times.isna()].head(3).tolist()
missing after: 142
['18:15', '19:23', '19:44']

142 times, a third of the race, are minutes:seconds. Dropping them isn't an option: these are real runners with real times. .str.len() from Tidying Text with .str tells the two layouts apart:

res["finish_time"].str.len().value_counts()
finish_time
7    290
5    142
Name: count, dtype: int64

Every short time has 5 characters (18:15) and every full one 7 (0:18:17, 1:12:00). So put "0:" in front of the short ones. mask() does that: where the condition is True, it uses the second value instead, and elsewhere it keeps the value as it was:

short = res["finish_time"].str.len() == 5
typed = res["finish_time"].mask(short, "0:" + res["finish_time"])
times = pd.to_timedelta(typed)
print("missing after:", times.isna().sum())
print("slowest:", times.max())
missing after: 0
slowest: 0 days 01:12:00

No time lost, and the slowest is right: 1 hour 12 minutes.

Minutes and pace: total_seconds

A Timedelta prints as 0 days 01:12:00, which is hard to average in your head. .dt.total_seconds() turns each duration into a number of seconds, and dividing by 60 gives minutes:

res["finish_minutes"] = (times.dt.total_seconds() / 60).round(2)
res.head(3)
   bib finish_time  finish_minutes
0  180     0:17:30           17.50
1  524     0:17:45           17.75
2  110     0:17:54           17.90
print("average finish:", round(res["finish_minutes"].mean(), 1), "minutes")
average finish: 34.6 minutes

Runners care most about pace, the time each mile took: the finish time divided by 3.1 miles. A Timedelta divides as a number does:

pace = times / 3.1
pace.head(3)
0   0 days 00:05:38.709677
1   0 days 00:05:43.548387
2   0 days 00:05:46.451612
Name: finish_time, dtype: timedelta64[us]

Correct, and unreadable. Nobody says their pace is "0 days, 5 minutes, 38.709677 seconds".

A duration people can read

Runners write a pace as minutes:seconds, 5:39. Round the pace to whole seconds, then split it with two pieces of Python arithmetic: //, read "floor divide", gives the whole minutes in it, and %, read "remainder", the seconds left over. .astype(str) makes each number text, and .str.zfill(2) fills with zeros on the left up to two characters, so 6 seconds shows as 06:

seconds = pace.dt.total_seconds().round().astype(int)
res["pace"] = (seconds // 60).astype(str) + ":" + (seconds % 60).astype(str).str.zfill(2)
res[res["bib"] == 421]
   bib finish_time  finish_minutes  pace
5  421     0:18:17           18.28  5:54

Maria Lopez, bib 421, finished sixth in 18 minutes 17 seconds: 5 minutes 54 seconds a mile. The last two finishers walked the course:

res.tail(2)
     bib finish_time  finish_minutes   pace
430  406     1:11:27           71.45  23:03
431  462     1:12:00           72.00  23:14

Keep the Timedelta and the minutes for working out; the formatted text is for the results board, the newsletter and anyone who reads them.

Check yourself

2 questions to try on the lesson page.

So what

Turning typed times into Timedeltas, fixing the ones that can't be read instead of dropping them, and formatting the answer back as minutes and seconds gives every runner a finish time and a pace they can trust, and read.

Key terms

Related