Attendance is counted whether you go to school or work, and some schools or companies penalize those who miss too many cards per month (e.g., three or more).
Some companies also require the submission of logs on working days for junior staff and weekly or monthly reports for managers, and penalize those who fail to submit.
It would be a time-consuming and boring task for company HR to work on personnel logs or missing card data one by one.
This paper provides methods to automate the handling of missing attendance and logs.
You don't need to install Python, you don't need to learn Python syntax, as long as you can create a new folder on your computer and click on the file to realize the statistical output of attendance and log missing list.
Let's take a look at the steps to realize it.
I. Effectiveness demonstration
1. Realize the effect
Let's first look at the implementation.
The steps for the general realization are as follows:
Step 1: Create a new "Monthly Missing Card Data Processing" folder in the D-disk (already fixed in the code, you must create this folder).
Step 2: Put the processing attendance missing exe file and the raw data file into the new folder created by STEP1.
Step 3: Click on the exe file and it will automatically come out with the csv result file in the following format:
2. Raw data templates
Raw data files need to be "to determine whether to submit the log", the raw data used in this paper are as follows (the table header needs to be named as follows):
Note: If you need the raw data of this article, and the exe file to run the results directly, you can click thelink (on a website)Get Extract Code vb6x
Where the filler means the name of the student or employee, the department can fill in so and so class if it is a student.
The fill time refers to the time the log was filled in, and the date refers to the actual date of the log. In the case of time and attendance clocking, both dates can be filled in with the actual date of clocking in.
If you are clocking in for attendance, the Work Completed Today column can be left empty.
If the original file wants to hold a full year's worth of data on employee punching, but you want to count the missing cards for one of the months.
Simply put the month you want to count on the first line of the date, the code has been filtered by the first line of the date with the same year and month data sub-box.
If you need to set up a timed task to send the run result timed email to the relevant people, you can email me privately.
Second, the code details
For some of you who know Python, if you have personalized needs, you can fine-tune the code to achieve your needs. The next section describes in detail the code that implements the above functionality.
1. Importing
First import the libraries that need to be loaded for this article, if you have some libraries that are not yet installed, causing errors when running the code, you can install them in Anaconda Prompt using the pip method.
# -*- coding: UTF-8 -*- ''' Code Purpose : Handle Missing Card Data Author : Aly Yiyang Blog : /qq_32532663/article/details/106176609 ''' import os import calendar import numpy as np import pandas as pd from datetime import datetime from xlrd import xldate_as_tuple from chinese_calendar import is_workday from chinese_calendar import is_holiday from chinese_calendar import get_holiday_detail
This article applies to os, calendar, numpy, pandas, datetime, xlrd, chinese_calendar libraries.
The os library can set the location where the file is read.
The calendar and chinese_calendar libraries are date handling libraries.
The numpy and pandas libraries handle dataframes.
The xlrd and datetime libraries handle time.
2. Define the time processing function
Then apply functions from the xlrd and datetime libraries to define time handlers that convert timestamps or times with hours, minutes, and seconds to years, months, and days only.
def num_to_date(x):
'''
Date Handling Functions
Converts a timestamp or time with hours, minutes, and seconds to a year, month, and day only.
'''
try:
x1 = datetime(*xldate_as_tuple(x, 0)).strftime('%Y-%m-%d')
except:
x1 = (x).strftime('%Y-%m-%d')
return x1The purpose of defining it as a year, month, day, and uniform time is to make it easier for subsequent code to run.
3. Read data to adjust the date format
Then read the data and apply the time processing function defined in the second subsection to put the fill time and date into process.
#Read the data
(r'D:\ Monthly missing card data processing')
date = pd.read_excel('Determine whether to commit logs', sheet_name='Sheet1')
# Adjustment of date format
date['Filling time'] = date['Filling time'].apply(num_to_date)
date['Date'] = date['Date'].apply(num_to_date)The raw part of the data is as follows:
Some of the data obtained by calling the time processing function is as follows:
4. Calculation of the number of working days
Then take out the first value of the date column of the data frame and get the year and month information to be counted. Get the number of working days in the month based on the month and year information.
# Take out the year and month that you want to see the missing card information
y_m1 = date['Date'][0][0:7]
def sele_ym(x, y_m=y_m1):
'''
Determine if the date in the data box is a certain month
'''
if (y_m)>=0:
return True
# Find the number of working days in the month and find the number of working days
days = ().itermonthdates(int(y_m1.split('-')[0]), int(y_m1.split('-')[1]))
mth_nwkdays = [] # Non-working days
mth_wkdays = [] # Working days
mth_days = [] # All dates
for day in days:
if str(day).find(y_m1)>=0:
#print(str(day))
mth_days.append(str(day))
if is_workday(day)>0:
mth_wkdays.append(str(day))
else:
mth_nwkdays.append(str(day))
work_days = len(mth_wkdays) #Number of working daysCompare the number of days worked with the actual number of days the employee clocked in or logged out for the month; if the actual value is less than the theoretical value, it means the employee is missing a card or taking a vacation.
Since most of the employees are normally clocking in or writing in their logs, manually checking for missing employees has drastically narrowed down the checking surface.
If you have any special code needs and need to ask for help, you can private message me in public.
5. Obtaining a list of missing cards
Finally the function is called to get the list of missing cards, mainly to compare each filled date with the actual working date.
# Define functions to get information about missing cards
def stat_dail_short(date, y_m1, work_days):
'''
date: large table of data for storing logs
y_m1: month
work_days: the number of days worked in the month.
'''
qk_file = []
date_m = date[date['Date'].apply(sele_ym)==True]
for i in set(date_m['Filler']):
sub_date = date_m[date_m['Filler'] == i]
if len(sub_date['Date'])<work_days:
qk = str(set(sub_date['Filler'])) + str(set(sub_date['Sector'])) + 'Missing'+ str((work_days-len(sub_date['Date']))) + 'Secondary card' + '; the date of the missing card is: '+ str(set(mth_wkdays)^set(sub_date['Date']))
qk_file.append(qk)
print(set(sub_date['Filler']), set(sub_date['Sector']), 'Missing %d times card'%(work_days-len(sub_date['Date'])), '; the date of the missing card is: ', set(mth_wkdays)^set(sub_date['Date']),sep='')
qk_file_1 = (qk_file)
qk_file_1.columns = ['Missing card information']
qk_file_1.to_csv(y_m1+' List of missing cards '+'.csv', encoding='gbk')
# Call the function to get the list of missing cards
stat_dail_short(date, y_m1, work_days)Getting results:
{'Zhang Jike'}{'Athletic Department'} has five missing cards; the missing card dates are: {'2022-04-11', '2022-04-29', '2022-04-22', '2022-04-18', '2022-04-21'}
{'Yang Zi'}{'Ministry of Calendars'} is missing 1 card; the date of the missing card is: {'2022-04-20'}
{'Liu Shiwen'} {'Athletic Department'} 2 missing cards; missing card dates are: {'2022-04-18', '2022-04-28'}
The data in the result is displayed in a splice with the person who filled in the report, the department who filled in the report, the number of missing cards, and the specific date of the missing card. It will be stored as a csv in the specified folder.
If you need to separate information such as name, department, number of missing cards, etc., you can do it in excel by specific conditions or tweak the code a bit to make it happen.
The exe file generation method at the beginning of this article can be found atthe following text
Pinstaller(Python packaged as exe file)
I've had a hard time packaging Python files into exe's on my own, so this article explains in detail how to quickly create a file that can be executed on computers that don't have Python installed.
1. Run pip install pyinstaller in prompt to install thepyinstaller storehouse
2. Run where in promptpyinstaller
3. Finding a place to beask for a doggy bag (at a restaurant)Path where the file is stored
Place the files to be packaged in the path found
C:\Users\Administrator\Anaconda3\Scripts (my path is this one, you just follow the path in step 2)
4. Calling the cmd window
Place the files to be packaged in the
C:\Users\Administrator\Anaconda3 \Scripts directory, in this folder press shift + right mouse button , click here to open the command window call cmd
5. Type pyinstaller -F file name in cmd
Example: To package a video of Python drawing Pikachu, type pyinstaller -F in cmd pkq_1.py
This generates an exe executable file with a common icon.
6. Generate exe file
This can be done in the path
C:\Users\Administrator\Anaconda3\Scripts under the dist folder to find the packaged exe file (i.e. the one that can be run without installing Python).
The file icon generated in this way is a standard fixed format, if you want to generate a specific specific shape of the icon need to use the statement in point 7.
7. To generate custom shaped icons, type in cmd: pyinstaller -i ico path -F
Example: To package a Python Pikachu drawing video, type it into cmd (note: I put the ico icon in the same folder as the file to be packaged, so I typed the name of the ico directly).
pyinstaller -i -F pkq_1.py
Generate an exe file whose icon is in the shape of Pikachu.
I've been getting errors during the process of generating the exe, and then I saw a method online that said to run pip uninstall matplotlib in cmd first, and then run the statement that generates the exe and it won't report errors.
The online method really worked, I didn't understand the principle, but thanks a lot! It's still not recommended to remove the matplotlib library if you don't report errors when you package it.
This article on Python automated processing of monthly attendance card missing data is introduced to this article, more related Python processing of attendance card missing data content, please search for my previous posts or continue to browse the following related articles I hope that you will support me in the future more!