pythonexcelpandasdataframedata-processing
error when encoutering some data, but some are correct
so i make a waging program using python, with that waging program I have an excel data that I need to processed.
my program as such
# Install required library
!pip install xlrd
import pandas as pd
from datetime import time, timedelta
import openpyxl
import math
import numpy as np
# Mount google drive
from google.colab import drive
drive.mount('/content/drive')
# Read the Excel file
path = '/content/drive/MyDrive/Colab Notebooks/Book1.xlsx'
df = pd.read_excel(path)
# Convert the 'Tgl/Waktu' column to datetime format
df['Tgl/Waktu'] = pd.to_datetime(df['Tgl/Waktu'])
# Extract the date and time from the 'Tgl/Waktu' column
df['Date'] = df['Tgl/Waktu'].dt.date
df['Time'] = df['Tgl/Waktu'].dt.time
# Group the data by employee name and date
grouped_df = df.groupby(['Nama', 'Date'])
# Set the overtime threshold to 16:30:00
overtime_threshold = time(hour=16, minute=30)
# Set the late limit
late_limit = time(hour=8, minute=15)
# Set holidays date
holidays_date = ['2023-1-1', '2023-1-22', '2023-2-18', '2023-3-22', '2023-4-7', '2023-4-22', '2023-4-23', '2023-5-1', '2023-5-18', '2023-6-1', '2023-6-4','2023-6-29',
'2023-7-19', '2023-8-17', '2023-9-28', '2023-12-25', '2023-1-23', '2023-3-23', '2023-4-21', '2023-4-24', '2023-4-25', '2023-4-26', '2023-6-2', '2023-12-26',
'2023-1-8', '2023-1-15', '2023-1-29', '2023-2-5', '2023-2-12', '2023-2-19', '2023-2-26', '2023-3-5', '2023-3-12', '2023-3-19', '2023-3-26', '2023-4-2',
'2023-4-9', '2023-4-16', '2023-4-23', '2023-4-30', '2023-5-7', '2023-5-14', '2023-5-21', '2023-5-28', '2023-6-11', '2023-6-18', '2023-6-25',
'2023-7-2', '2023-7-9', '2023-7-16', '2023-7-23', '2023-7-30', '2023-8-6', '2023-8-13', '2023-8-20', '2023-8-27', '2023-9-3', '2023-9-10', '2023-9-17',
'2023-9-24', '2023-10-1', '2023-10-8', '2023-10-15', '2023-10-22', '2023-10-29', '2023-11-5', '2023-11-12', '2023-11-19', '2023-11-26', '2023-12-3', '2023-12-10',
'2023-12-17', '2023-12-24', '2023-12-31','2022-12-20']
# Iterate over the grouped data
for (name, date), group in grouped_df:
# Calculate the total work hours and overtime hours for each employee on each day
start_time = group['Time'].min()
end_time = group['Time'].max()
total_hours = (timedelta(hours=end_time.hour, minutes=end_time.minute, seconds=end_time.second) -
timedelta(hours=start_time.hour, minutes=start_time.minute, seconds=start_time.second)).total_seconds() / 3600
if total_hours > 8:
hours_worked = 8
if end_time > overtime_threshold:
overtime_hours += (end_time - overtime_threshold).total_seconds() / 3600
elif total_hours < 8:
if start_time > late_limit:
hours_worked = 5
else:
hours_worked = total_hours
hours_worked = math.floor(total_hours) # Round down the hours_worked value
overtime_hours = 0
if end_time > overtime_threshold:
overtime_hours += (end_time - overtime_threshold).total_seconds() / 3600
# Calculate the payment for each employee on each day
if hours_worked == 8:
if overtime_hours > 0:
if name == 'Alif':
payment_each_date = 60000 + overtime_hours * 10000
elif name == 'budi':
payment_each_date = 70000 + overtime_hours * 10000
elif name == 'adi':
payment_each_date = 60000 + overtime_hours * 10000
elif name == 'supriyanto':
payment_each_date = 70000 + overtime_hours * 10000
elif name == 'Edi':
payment_each_date = 60000 + overtime_hours * 10000
elif name == 'Tri Gunawan':
payment_each_date = 60000 + overtime_hours * 10000
elif name == 'Bayu Aji N':
payment_each_date = 60000 + overtime_hours * 10000
elif name == 'dani':
payment_each_date = 70000 + overtime_hours * 10000
else :
payment_each_date = "Name Not Listed"
else:
if name == 'Alif':
payment_each_date = 60000
elif name == 'budi':
payment_each_date = 70000
elif name == 'adi':
payment_each_date = 60000
elif name == 'supriyanto':
payment_each_date = 70000
elif name == 'Edi':
payment_each_date = 60000
elif name == 'Tri Gunawan':
payment_each_date = 60000
elif name == 'Bayu Aji N':
payment_each_date = 60000
elif name == 'dani':
payment_each_date = 70000
else :
payment_each_date = "Name Not Listed"
else:
if start_time > late_limit:
if name == 'Alif':
payment_each_date = 60000/2
elif name == 'budi':
payment_each_date = 70000/2
elif name == 'adi':
payment_each_date = 60000/2
elif name == 'supriyanto':
payment_each_date = 70000/2
elif name == 'Edi':
payment_each_date = 60000/2
elif name == 'Tri Gunawan':
payment_each_date = 60000/2
elif name == 'Bayu Aji N':
payment_each_date = 60000/2
elif name == 'dani':
payment_each_date = 70000/2
else :
payment_each_date = "Name Not Listed"
else:
if name == 'Alif':
payment_each_date = 60000
elif name == 'budi':
payment_each_date = 70000
elif name == 'adi':
payment_each_date = 60000
elif name == 'supriyanto':
payment_each_date = 70000
elif name == 'Edi':
payment_each_date = 60000
elif name == 'Tri Gunawan':
payment_each_date = 60000
elif name == 'Bayu Aji N':
payment_each_date = 60000
elif name == 'dani':
payment_each_date = 70000
else :
payment_each_date = "Name Not Listed"
# Add the total work hours, overtime hours, and payment as new columns to the dataframe
df.loc[(df['Nama'] == name) & (df['Date'] == date), 'Hours Worked'] = hours_worked
df.loc[(df['Nama'] == name) & (df['Date'] == date), 'Overtime Hours'] = overtime_hours
df.loc[(df['Nama'] == name) & (df['Date'] == date), 'Payment Each Date'] = payment_each_date
holiday_status = df['Tgl/Waktu'].dt.normalize().isin(pd.DatetimeIndex(holidays_date))
# make new column for holiday with boolean value then merge with the original dataframe and place it in between date and time using merge
df = pd.merge(df, holiday_status.to_frame('Holiday'), left_index=True, right_index=True)
# make the payment each date column to 1.5 times and add 5000 if the holiday column is true
df.loc[df['Holiday'] == True, 'Payment Each Date'] = df['Payment Each Date'] * 1.5 + 5000
# Calculate the total payment from payment each date and insert it into new column named Total Payment
df_total = df.groupby(['Nama', 'Date'])['Payment Each Date'].max().groupby('Nama').sum().rename('Total Payment')
df = df.merge(df_total, how='left', on='Nama')
# Print the resulting dataframe
print(df)
# write DataFrame to excel
df.to_excel(excel_writer=r'/content/drive/MyDrive/Colab Notebooks/test.xlsx')
with this data when I run the program it can be processed and got correct result
but when I use this data I get error unsupported operand type(s) for -: 'datetime.time' and 'datetime.time'
like below
so how do I fix it? is it the error on the data? or the program?
Solution
The Answer to make the program can run is to convert the end_time and overtime_treshold into timedelta format, such as
overtime_hours += (timedelta(hours=end_time.hour, minutes=end_time.minute, seconds=end_time.second) - timedelta(hours=overtime_threshold.hour, minutes=overtime_threshold.minute)).total_seconds() / 3600