import pandas as pd
import numpy as np
data = {'Name':['Si','Ov','Sp','Sa','An'],
'Time1':['02:00:00', '03:02:00', '04:00:30','01:02:30','0'],
'Time2':['03:00:00', '0', '05:00:30','02:02:30','02:00:00']}
# Create DataFrame
df = pd.DataFrame(data)
# Print the output.
print (df)
Output
Name Time1 Time2
0 Siya 02:00:00 03:00:00
1 Ovi 03:02:00 0
2 Spruha 04:00:30 05:00:30
3 Saanvi 01:02:30 02:02:30
4 Ansh 0 02:00:00
want to add one more column to and apply the formula Time3=(Time1-Time2)/Time2
There is 0 or nan value also.
CodePudding user response:
Use to_timedelta
for convert times to timedeltas:
t1 = pd.to_timedelta(df['Time1'])
t2 = pd.to_timedelta(df['Time2'])
df['Time3'] = t1.sub(t2).div(t2)
print (df)
Name Time1 Time2 Time3
0 Si 02:00:00 03:00:00 -0.333333
1 Ov 03:02:00 0 inf
2 Sp 04:00:30 05:00:30 -0.199667
3 Sa 01:02:30 02:02:30 -0.489796
4 An 0 02:00:00 -1.000000
EDIT:
For add new row and column use:
def format_timedelta(x):
ts = x.total_seconds()
hours, remainder = divmod(ts, 3600)
minutes, seconds = divmod(remainder, 60)
return ('{}:{:02d}:{:02d}').format(int(hours), int(minutes), int(seconds))
t1 = pd.to_timedelta(df['Time1'])
t2 = pd.to_timedelta(df['Time2'])
df['Time3'] = t1.sub(t2).div(t2)
idx = len(df)
df.loc[idx] = (pd.concat([t1, t2], axis=1)
.sum()
.apply(format_timedelta))
df.loc[idx, ['Name','Time3']] = ['Total', df['Time3'].mask(np.isinf(df['Time3'])).sum()]
print (df)
Name Time1 Time2 Time3
0 Si 02:00:00 03:00:00 -0.333333
1 Ov 03:02:00 0 inf
2 Sp 04:00:30 05:00:30 -0.199667
3 Sa 01:02:30 02:02:30 -0.489796
4 An 0 02:00:00 -1.000000
5 Total 10:05:00 12:03:00 -2.022796