CODEWITHSUNDEEP
pythonpython-3.xpandastime
team
team

Reputation: 526

How to get the time difference in between same value in same column using python

Here I have a data with one input date and time. So here I want to get the only time difference in between same value in same column.

subset of my csv :

print (df)
         date      time  x3
0   10/3/2018   6:15:00   0
1   10/3/2018   6:45:00   5
2   10/3/2018   7:45:00   0
3   10/3/2018   9:00:00   0
4   10/3/2018   9:25:00   7
5   10/3/2018   9:30:00   0
6   10/3/2018  11:00:00   0
7   10/3/2018  11:30:00   0
8   10/3/2018  13:30:00   0
9   10/3/2018  13:50:00   5
10  10/3/2018  15:00:00   0
11  10/3/2018  15:25:00   0
12  10/3/2018  16:25:00   0
13  10/3/2018  18:00:00   7
14  10/3/2018  19:00:00   0
15  10/3/2018  19:30:00   0
16  10/3/2018  20:00:00   0
17  10/3/2018  22:05:00   0
18  10/3/2018  22:15:00   5
19  10/3/2018  23:40:00   0
20  10/4/2018   6:58:00   5
21  10/4/2018  13:00:00   0
22  10/4/2018  16:00:00   7
23  10/4/2018  17:00:00   5

expecting output is :

enter image description here

I wrote the code and it gave me with another column of my dataset. I just want to normally value of time difference without column data. 

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)

Upvotes: 1

Views: 187

Answers (1)

jezrael
jezrael

Reputation: 863801

I think you need:

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

mask = df['x3'].eq(5)
df['Duration'] = df[mask].groupby(['date','x3'])['time_diff'].diff()
print (df)
         date      time  x3           time_diff Duration
0   10/3/2018   6:15:00   0 2018-03-10 06:15:00      NaT
1   10/3/2018   6:45:00   5 2018-03-10 06:45:00      NaT
2   10/3/2018   7:45:00   0 2018-03-10 07:45:00      NaT
3   10/3/2018   9:00:00   0 2018-03-10 09:00:00      NaT
4   10/3/2018   9:25:00   7 2018-03-10 09:25:00      NaT
5   10/3/2018   9:30:00   0 2018-03-10 09:30:00      NaT
6   10/3/2018  11:00:00   0 2018-03-10 11:00:00      NaT
7   10/3/2018  11:30:00   0 2018-03-10 11:30:00      NaT
8   10/3/2018  13:30:00   0 2018-03-10 13:30:00      NaT
9   10/3/2018  13:50:00   5 2018-03-10 13:50:00 07:05:00
10  10/3/2018  15:00:00   0 2018-03-10 15:00:00      NaT
11  10/3/2018  15:25:00   0 2018-03-10 15:25:00      NaT
12  10/3/2018  16:25:00   0 2018-03-10 16:25:00      NaT
13  10/3/2018  18:00:00   7 2018-03-10 18:00:00      NaT
14  10/3/2018  19:00:00   0 2018-03-10 19:00:00      NaT
15  10/3/2018  19:30:00   0 2018-03-10 19:30:00      NaT
16  10/3/2018  20:00:00   0 2018-03-10 20:00:00      NaT
17  10/3/2018  22:05:00   0 2018-03-10 22:05:00      NaT
18  10/3/2018  22:15:00   5 2018-03-10 22:15:00 08:25:00
19  10/3/2018  23:40:00   0 2018-03-10 23:40:00      NaT
20  10/4/2018   6:58:00   5 2018-04-10 06:58:00      NaT
21  10/4/2018  13:00:00   0 2018-04-10 13:00:00      NaT
22  10/4/2018  16:00:00   7 2018-04-10 16:00:00      NaT
23  10/4/2018  17:00:00   5 2018-04-10 17:00:00 10:02:00

Upvotes: 1

Related Questions