2023-02 Dataset Presensi

import pandas as pd
import numpy as np
days_size = 28
days = range(1,days_size + 1)
year_month = '2023-02'

sampul = pd.read_excel('2023-02-presensi-sampul.xls', header= 1)
sampul['Waktu'] = sampul['Tanggal'].str[6:10] + "-" + sampul['Tanggal'].str[3:5] + "-" + sampul['Tanggal'].str[0:2] + " " + sampul['Jam']
sampul['Waktu'] = pd.to_datetime(sampul['Waktu'])
sampul.drop(['Tanggal scan','Tanggal', 'Jam','NIP','Jabatan','Departemen','Kantor','Verifikasi','I/O','Workcode','SN','Mesin'], axis= 1, inplace= True)
sampul.columns = ['ID','Nama','Waktu']
print(sampul.shape)
sampul.head()

singkarak = pd.read_excel(
    '2023-02-presensi-singkarak.xls',
    'Catatan',
    header= 3
)


column_names = list(range(1,days_size + 1))
column_names.insert(0, 'Dept.')
column_names.insert(0, 'Nama')
column_names.insert(0, 'ID')
column_names.insert(len(column_names), 'error')
singkarak.columns = column_names

ids = []
names = []
times = []

for index, row in singkarak.iterrows():
    for i in days:
        if not pd.isna(row[i]):
            check = row[i].split('\n')
            ids.append(row['ID'])
            names.append(row['Nama'])
            checkin = year_month + '-' + str(i).rjust(2,'0') + ' ' + check[0]
            times.append(checkin)

            if check[1]:                
                ids.append(row['ID'])
                names.append(row['Nama'])
                checkout = year_month + '-' + str(i).rjust(2,'0') + ' ' + check[1]
                times.append(checkout)
            
singkarak_converted = pd.DataFrame(
    {
        'ID': ids,
        'Nama': names,
        'Waktu': times
    }
)
singkarak_converted['Waktu'] = pd.to_datetime(singkarak_converted['Waktu'])
print(singkarak_converted.shape)
singkarak_converted.head()

import datetime as dt
import pytz as tz
ct = dt.datetime.now(tz.timezone('Asia/Jakarta'))
timestamp = ct.strftime("%Y-%m-%d-%H-%M-%S")

presensi = pd.concat([sampul, singkarak_converted])
#presensi.sort_values(by=['ID', 'Waktu'], inplace= True)

print(presensi.shape)
presensi.to_csv(timestamp + '-presensi-bersihx.csv', index= False)

presensi[presensi['ID'] == 108]