this table is just some of the real data,so from that raw excel file i want to make a app to output the waging system

the lookup table this is the real data, probably this can help

and i come up with the code below

# Install required library
!pip install xlrd
import pandas as pd
from datetime import time, timedelta
import openpyxl
import math

# Mount google drive
from google.colab import drive
drive.mount('/content/drive')

# Read the Excel file
path = '/content/drive/MyDrive/Colab Notebooks/Book2.xls'
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)

# 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
            if end_time > overtime_threshold:
                overtime_hours  = (end_time - overtime_threshold).total_seconds() / 3600
        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
            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
            else :
                payment_each_date = "Name Not Listed"

    else:
        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
        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

# 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')

from that code i want to add if else in datetime so if date is any one of these

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-1-31']

it will make the payment each date 2 times higher

how do i implement it in my code? I try try several times like

if date in holidays_date:

if (df['Date'] == holidays_date):

but still gettin error

CodePudding user response：

There is indeed something strange going on, that I didn't have time to resolve. This works though. You need to import numpy.

def vectorized(t):
    if pd.Timestamp(pd.Timestamp(t).date()) in holidays_date:
        return True
    else:
        return False

df['flag'] = np.vectorize(vectorized)(df['Tgl/Waktu'])
df.loc[(df['flag']==True), 'Payment Each Date'] *= 2

CodePudding user response：

You can use:

holidays_date.append('2022-12-17')  # just for demo

df = pd.read_excel('your_file.xlsx', parse_dates=['Tgl/Waktu'])
m = df['Tgl/Waktu'].dt.normalize().isin(pd.DatetimeIndex(holidays_date))
print(m)

# Output
0      True
1      True
2     False
3     False
4     False
      ...  
91    False
92    False
93    False
94    False
95    False
Name: Tgl/Waktu, Length: 96, dtype: bool

Now, you can add pd.DateOffset:

df['Pay'] = (df['Tgl/Waktu'].mask(m, other=df['Tgl/Waktu'] pd.DateOffset(days=2))
                            .dt.normalize())
print(df)

# Output
          Nama   No.ID           Tgl/Waktu  No.PIN Kode Verifikasi        Pay
0         Alif  100061 2022-12-17 07:53:26     NaN      Sidik Jari 2022-12-19
1         Alif  100061 2022-12-17 13:00:25     NaN      Sidik Jari 2022-12-19
2         Alif  100061 2022-12-19 07:54:59     NaN      Sidik Jari 2022-12-19
3         Alif  100061 2022-12-19 16:18:14     NaN      Sidik Jari 2022-12-19
4         Alif  100061 2022-12-20 07:55:54     NaN      Sidik Jari 2022-12-20
..         ...     ...                 ...     ...             ...        ...
91  Bayu Aji N  100084 2022-12-22 07:31:05     NaN      Sidik Jari 2022-12-22
92  Bayu Aji N  100084 2022-12-22 07:31:27     NaN      Sidik Jari 2022-12-22
93  Bayu Aji N  100084 2022-12-22 16:16:57     NaN      Sidik Jari 2022-12-22
94  Bayu Aji N  100084 2022-12-23 07:37:30     NaN      Sidik Jari 2022-12-23
95  Bayu Aji N  100084 2022-12-23 16:16:40     NaN      Sidik Jari 2022-12-23

[96 rows x 6 columns]