CODEWITHSUNDEEP
pythonpandastime
team
team

Reputation: 526

How to calculate time difference in between same rows step by step continously in pandas python

Here I have a dataset with three inputs x1,x2,x3 with date and time. Here in my X3 column I have similar values in rows.

What I want to do is I want to find the time difference in similar values step wise in rows when the start time will be 0.

Here I used the code 

df['time_diff']= pd.to_datetime(df['date'] + " " + df['time'],
                            format='%d/%m/%Y %H:%M:%S', dayfirst=True)

mask = df['x3'].ne(0)
df['Duration'] = df[mask].groupby(['date','x3'])['time_diff'].transform('first')
df['Duration'] = df['time_diff'].sub(df['Duration']).dt.total_seconds().div(3600)

This code is giving this value.

date	time	                x3         Expected output of time difference
10/3/2018	6:00:00	        0          NaN
10/3/2018	7:00:00	        5          0 =start time for 5
10/3/2018	8:00:00	        0          NaN
10/3/2018	9:00:00	        7          0=start time for 7
10/3/2018	10:00:00	0          NaN
10/3/2018	11:00:00	0          NaN
10/3/2018	12:00:00	0          NaN
10/3/2018	13:45:00	0          NaN
10/3/2018	15:00:00	0          NaN
10/3/2018	16:00:00	0          NaN
10/3/2018	17:00:00	0          NaN
10/3/2018	18:00:00	0          NaN
10/3/2018	19:00:00	5          12 hr =from starting time of 5
10/3/2018	20:00:00	0          NaN
10/3/2018	21:30:00	7          12.30hr = from starting time of 7
10/4/2018	6:00:00	        0         NaN
10/4/2018	7:00:00	        0         NaN
10/4/2018	8:00:00	        5         0 = starting time of 5 because new day
10/4/2018	9:00:00	        7         0 = starting time of 5 because new day
10/4/2018	11:00:00	5         3hr
10/4/2018	12:00:00	5         4hr
10/4/2018	13:00:00	5         5hr
10/4/2018	16:00:00	0         NaN
10/4/2018	17:00:00	0         NaN
10/4/2018	18:00:00	7         11hr

But what I expected output is I want to find the time difference step wise with time mean:

date	time	      x3   Expected for 5 (time_diff) Expected for 7(time_diff) 
10/3/2018 6:00:00     0      NaN                      NaN
10/3/2018 7:00:00     5      0 =start time for 5      NaN
10/3/2018 8:00:00     0      1hr                      NaN
10/3/2018 9:00:00     7      1hr                      0=start time for 7                          
10/3/2018 11:00:00    0      1hr                      1hr
10/3/2018 12:00:00    0      1hr                      1hr
10/3/2018 13:45:00    0      1.45hr                   1.45hr
10/3/2018 15:00:00    0      1.15hr                   1.15hr
10/3/2018 16:00:00    0      1hr                      1hr
10/3/2018 17:00:00    0      1hr                      1hr
10/3/2018 18:00:00    0      1hr                      1hr
10/3/2018 19:00:00    5     0 hr =nextstartingtime5   1hr
10/3/2018 20:00:00    0      1hr                       1hr
10/3/2018 21:30:00    7      1.5hr                    0 =starting 7
10/4/2018 6:00:00     0      1hr                       1hr
10/4/2018 7:00:00     0      1hr                       1hr
10/4/2018 8:00:00     5      0 = startingbecausenewda  1hr
10/4/2018 9:00:00     7      1hr                       0 =starting time for 7
10/4/2018 11:00:00    5      2hr                      2hr
10/4/2018 12:00:00    5      1hr                      1hr

Upvotes: 1

Views: 77

Answers (1)

jezrael
jezrael

Reputation: 863481

You can processing all unique values without 0 in x3 column:

df['time_diff']= pd.to_datetime(df['date'] + " " + df['time'],
                            format='%d/%m/%Y %H:%M:%S', dayfirst=True)

for x in df.loc[df['x3'].ne(0), 'x3'].unique():
    s = df['x3'].eq(x).cumsum()
    df['Expected {}'.format(x)] = (df[s != 0].groupby(['date',s])['time_diff']
                                                .diff()
                                                .dt.total_seconds()
                                                .div(3600)
                                                .fillna(0))

print (df)
         date      time  x3 Expected           time_diff  Expected 5  \
0   10/3/2018   6:00:00   0      NaN 2018-03-10 06:00:00         NaN   
1   10/3/2018   7:00:00   5        0 2018-03-10 07:00:00        0.00   
2   10/3/2018   8:00:00   0      NaN 2018-03-10 08:00:00        1.00   
3   10/3/2018   9:00:00   7        0 2018-03-10 09:00:00        1.00   
4   10/3/2018  10:00:00   0      NaN 2018-03-10 10:00:00        1.00   
5   10/3/2018  11:00:00   0      NaN 2018-03-10 11:00:00        1.00   
6   10/3/2018  12:00:00   0      NaN 2018-03-10 12:00:00        1.00   
7   10/3/2018  13:45:00   0      NaN 2018-03-10 13:45:00        1.75   
8   10/3/2018  15:00:00   0      NaN 2018-03-10 15:00:00        1.25   
9   10/3/2018  16:00:00   0      NaN 2018-03-10 16:00:00        1.00   
10  10/3/2018  17:00:00   0      NaN 2018-03-10 17:00:00        1.00   
11  10/3/2018  18:00:00   0      NaN 2018-03-10 18:00:00        1.00   
12  10/3/2018  19:00:00   5     12hr 2018-03-10 19:00:00        0.00   
13  10/3/2018  20:00:00   0      NaN 2018-03-10 20:00:00        1.00   
14  10/3/2018  21:30:00   7  12.30hr 2018-03-10 21:30:00        1.50   
15  10/4/2018   6:00:00   0      NaN 2018-04-10 06:00:00        0.00   
16  10/4/2018   7:00:00   0      NaN 2018-04-10 07:00:00        1.00   
17  10/4/2018   8:00:00   5        0 2018-04-10 08:00:00        0.00   
18  10/4/2018   9:00:00   7        0 2018-04-10 09:00:00        1.00   
19  10/4/2018  11:00:00   5      3hr 2018-04-10 11:00:00        0.00   
20  10/4/2018  12:00:00   5      4hr 2018-04-10 12:00:00        0.00   
21  10/4/2018  13:00:00   5      5hr 2018-04-10 13:00:00        0.00   
22  10/4/2018  16:00:00   0      NaN 2018-04-10 16:00:00        3.00   
23  10/4/2018  17:00:00   0      NaN 2018-04-10 17:00:00        1.00   
24  10/4/2018  18:00:00   7     11hr 2018-04-10 18:00:00        1.00   

    Expected 7  
0          NaN  
1          NaN  
2          NaN  
3         0.00  
4         1.00  
5         1.00  
6         1.00  
7         1.75  
8         1.25  
9         1.00  
10        1.00  
11        1.00  
12        1.00  
13        1.00  
14        0.00  
15        0.00  
16        1.00  
17        1.00  
18        0.00  
19        2.00  
20        1.00  
21        1.00  
22        3.00  
23        1.00  
24        0.00

Upvotes: 1

Related Questions