Home > Mobile >  I want percentage change in two time column (column format is hh:mm:ss) in pandas
I want percentage change in two time column (column format is hh:mm:ss) in pandas

Time:09-24

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
  • Related