CODEWITHSUNDEEP
pythonpandasdata-processing
Pckpow
Pckpow

Reputation: 15

Seperate datetime in python and counting hours

Nama No.ID Tgl/Waktu No.PIN Kode Verifikasi
Alif 100061 17/12/2022 07:53:26 Sidik Jari
Alif 100061 17/12/2022 13:00:25 Sidik Jari
Alif 100061 19/12/2022 07:54:59 Sidik Jari
Alif 100061 19/12/2022 16:18:14 Sidik Jari
Alif 100061 20/12/2022 07:55:54 Sidik Jari
Alif 100061 20/12/2022 16:16:16 Sidik Jari
Alif 100061 21/12/2022 07:54:46 Sidik Jari
Alif 100061 21/12/2022 16:15:41 Sidik Jari
Alif 100061 22/12/2022 07:55:54 Sidik Jari
Alif 100061 22/12/2022 16:15:59 Sidik Jari
Alif 100061 23/12/2022 07:56:26 Sidik Jari
Alif 100061 23/12/2022 16:16:56 Sidik Jari

So I have a few questions in this thread. the first one is from the table above how to separate the Tgl/Waktu column from the excel file into 2 separate new column that is date and time.

then how do I count the total work hour from that code, I have an idea where you subtract the bigger hour to the lower one when the date is the same, but I cant execute it

Upvotes: 1

Views: 49

Answers (1)

Sergey Sakharovskiy
Sergey Sakharovskiy

Reputation: 599

If there were no lunch breaks, the hours worked can be calculated by simple max - min subtraction:

df['Tgl/Waktu'] = pd.to_datetime(df['Tgl/Waktu'])
df['date'] = df['Tgl/Waktu'].dt.date
df['time'] = df['Tgl/Waktu'].dt.time
hours_worked = (df.groupby(by='date').max()['Tgl/Waktu'] - df.groupby(by='date').min()['Tgl/Waktu'])\
.reset_index().rename(columns={'Tgl/Waktu': 'hours_worked'})
df = df.merge(hours_worked, on='date', how='left')
df['hours_worked'] = df.hours_worked.dt.seconds / 3600

Output:

    Nama    No.ID           Tgl/Wakt No.PIN Kode Verifikasi date    time     hours_worked
0   Alif    100061  2022-12-17 07:53:26 NaN Sidik Jari  2022-12-17  07:53:26    5.116389
1   Alif    100061  2022-12-17 13:00:25 NaN Sidik Jari  2022-12-17  13:00:25    5.116389
2   Alif    100061  2022-12-19 07:54:59 NaN Sidik Jari  2022-12-19  07:54:59    8.387500
3   Alif    100061  2022-12-19 16:18:14 NaN Sidik Jari  2022-12-19  16:18:14    8.387500
4   Alif    100061  2022-12-20 07:55:54 NaN Sidik Jari  2022-12-20  07:55:54    8.339444
5   Alif    100061  2022-12-20 16:16:16 NaN Sidik Jari  2022-12-20  16:16:16    8.339444
6   Alif    100061  2022-12-21 07:54:46 NaN Sidik Jari  2022-12-21  07:54:46    8.348611
7   Alif    100061  2022-12-21 16:15:41 NaN Sidik Jari  2022-12-21  16:15:41    8.348611
8   Alif    100061  2022-12-22 07:55:54 NaN Sidik Jari  2022-12-22  07:55:54    8.334722
9   Alif    100061  2022-12-22 16:15:59 NaN Sidik Jari  2022-12-22  16:15:59    8.334722
10  Alif    100061  2022-12-23 07:56:26 NaN Sidik Jari  2022-12-23  07:56:26    8.341667
11  Alif    100061  2022-12-23 16:16:56 NaN Sidik Jari  2022-12-23  16:16:56    8.341667

Upvotes: 1

Related Questions