Time calculations, mean , median, mode

Question

(	Name	Gun_time	Net_time
John	28:48:00	28:47:00	4:38:00
George	29:11:00	29:10:00	4:42:00
Mike	29:38:00	29:37:00	4:46:00
Sarah	29:46:00	29:46:00	4:48:00
Roy	30:31:00	30:30:00	4:55:00

Q1. How can I add another column stating difference between Gun_time and Net_time? Q2. How will I calculate the mean for Gun_time and Net_time. Please help!

I have tried doing the following but it doesn't work

df['Difference'] = df['Gun_time'] - df['Net_time']

for mean value I tried df['Gun_time'].mean

but it doesn't work either, please help!

Q.3 What if we have times in 28:48 (minutes and seconds) format and not 28:48:00 the function gives out a value error.

ValueError: expected hh:mm:ss format

please add code examples as text / formatted code block, not image. — FObersteiner, Feb 09 '21 at 05:10
actually, I meant your input data (which is still a screenshot at the moment). We can't easily copy data from an image for testing, that's the reason. Anyways, from the looks of it, I'd say you need to cast to [timedelta](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Timedelta.html), e.g. `pd.to_timedelta(df['Gun_time']) - pd.to_timedelta(df['Net_time'])`. — FObersteiner, Feb 09 '21 at 08:33
MrFuppes, please take a look now may be you can help. Thank you — Usman Shaikh, Feb 09 '21 at 15:21
MrFuppes I get a value error when converting columns to timedelta using 28:48 format (minutes and seconds) . It states value error: expected hh:mm:ss format. How can we update the function so it can also accomodate mm:ss format as well or update it to hh:mm:ss Thank you — Usman Shaikh, Feb 09 '21 at 17:48
ok I think this only happens if the whole column has that format. a simple solution might be to prepend a string `'00:'`, see e.g. https://stackoverflow.com/questions/20025882/add-a-string-prefix-to-each-value-in-a-string-column-using-pandas — FObersteiner, Feb 09 '21 at 18:13

score 1 · Accepted Answer · answered Feb 09 '21 at 15:48

Convert your columns to dtype timedelta, e.g. like

for col in ("Gun_time", "Net_time", "Pace"):
    df[col] = pd.to_timedelta(df[col])

Now you can do calculations like

df['Gun_time'].mean()
# Timedelta('1 days 05:34:48')

or

df['Difference'] = df['Gun_time'] - df['Net_time']

#df['Difference']
# 0   0 days 00:01:00
# 1   0 days 00:01:00
# 2   0 days 00:01:00
# 3   0 days 00:00:00
# 4   0 days 00:01:00
# Name: Difference, dtype: timedelta64[ns]

If you need nicer output to string, you can use

def timedeltaToString(td):
    hours, remainder = divmod(td.total_seconds(), 3600)
    minutes, seconds = divmod(remainder, 60)
    return f"{int(hours):02d}:{int(minutes):02d}:{int(seconds):02d}"


df['diffString'] = df['Difference'].apply(timedeltaToString)

# df['diffString']
# 0    00:01:00
# 1    00:01:00
# 2    00:01:00
# 3    00:00:00
# 4    00:01:00
#Name: diffString, dtype: object

Time calculations, mean , median, mode

1 Answers1