One 、 Effect display
1. Realization effect
2. Raw data template
Two 、 Code details
1. Import library
2. Define time processing functions
3. Read data to adjust date format
4. Calculate the number of working days
5. Get the missing card list
Whether it is school or work, attendance statistics will be made , Some schools or companies will have too many missing cards every month ( For example, more than three times ) To punish the personnel .
Some companies also stipulate that basic level employees should submit logs on weekdays 、 Management personnel shall submit weekly or monthly reports , Those who submit less shall be punished .
If the company HR Handle the personnel log or card shortage data one by one , It will be a time-consuming and boring job .
This article provides a method to automatically handle attendance and missing logs .
No need to install Python, No need to learn Python grammar , As long as you can create new folders on your computer , Click the file to realize the statistical output of attendance and log missing list .
Next, let's take a look at the implementation steps .
One 、 Effect display 1. Realization effectFirst, let's take a look at the implementation effect .
The general implementation steps are as follows ：
step 1： stay D New disk “ Monthly card shortage data processing “ Folder ( Fixed in the code , This folder must be created ).
step 2： Deal with the missing attendance exe Files and raw data files are put into step1 New folder .
step 3： Click on exe file , Will automatically come out csv Results file , The specific format is as follows ：
The original data file needs to be ” Determine whether to submit the log 2.xlsx“, The raw data used in this article are as follows ( The header shall be named as follows )：
notes ： If you need the original data of this article 、 And running directly to get the results exe file , You can click the link to get Extraction code vb6x
The person filling in refers to the name of the student or employee , If the Department is a student, you can fill in a class .
Filling time refers to the filling time of the log , Date refers to the actual date of the log . In case of clock in , For both dates, you can fill in the actual punch in date .
In case of clock in , Today's completion column can be left blank .
If you want to store the annual data of employees' clocking in the original file , But I want to count the card shortage data of a certain month .
Just put the month you want to count on the first line of the date , The code has filtered the data sub box of the same month and year according to the first line of the date .
To set a scheduled task , Send the running results to relevant personnel by regular email , You can write to me .
Two 、 Code detailsFor partial understanding Python To a friend , If there is a personalized need , You can fine tune your code to meet your requirements . Next, the code to realize the above functions is described in detail .
1. Import libraryFirst, import the library to be loaded in this article , If you don't have some libraries installed , Cause the running code to report an error , Can be in Anaconda Prompt of use pip Method installation .
# -*- coding: UTF-8 -*-''' Code usage ： Processing missing card data author ： Ali Yiyang blog : https://blog.csdn.net/qq_32532663/article/details/106176609'''import osimport calendarimport numpy as npimport pandas as pdfrom datetime import datetimefrom xlrd import xldate_as_tuplefrom chinese_calendar import is_workdayfrom chinese_calendar import is_holidayfrom chinese_calendar import get_holiday_detailThis paper applies to os、calendar、numpy、pandas、datetime、xlrd、chinese_calendar library .
os The library can set the location where files are read .
calendar and chinese_calendar Library is a date processing library .
numpy and pandas The library handles data frames .
xlrd and datetime Library processing time .
2. Define time processing functionsThen apply xlrd and datetime The function in the library defines the time processing function , Convert the time stamp or the time with hour, minute and second into the time containing only the month, year and day .
def num_to_date(x): ''' Date processing function Convert the time stamp or the time with hour, minute and second into the time containing only the month, year and day ''' try: x1 = datetime(*xldate_as_tuple(x, 0)).strftime('%Y-%m-%d') except: x1 = datetime.date(x).strftime('%Y-%m-%d') return x1The purpose of defining the unified time of adult month and day is to facilitate the operation of subsequent code .
3. Read data to adjust date formatThen read the data , Use the time processing function defined in Section 2 to process the filling time and date .
# Reading data os.chdir(r'D:\ Monthly card shortage data processing ')date = pd.read_excel(' Determine whether to submit the log 2.xlsx', sheet_name='Sheet1')# Adjust date format date[' Filling time '] = date[' Filling time '].apply(num_to_date)date[' date '] = date[' date '].apply(num_to_date)The original partial data are as follows ：
Some data obtained by calling the time processing function are as follows ：
Then take the first value of the date column of the data frame , Get the year / month information to be counted . Obtain the number of working days of the month according to the information of the month .
# Take out the year and year you want to see the missing card information y_m1 = date[' date '][0][0:7] def sele_ym(x, y_m=y_m1): ''' Judge whether the date in the data frame is a month ''' if x.find(y_m)>=0: return True# Find the working days of the month , Find the number of working days days = calendar.Calendar().itermonthdates(int(y_m1.split('-')[0]), int(y_m1.split('-')[1])) mth_nwkdays = [] # Non working days mth_wkdays = [] # Working day 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) # Working days Compare the working days with the actual clock in or log in days of the current month , If the actual value is less than the theoretical value , It means that the employee is missing a card or asking for leave .
Because most of the employees clock in or write logs normally , At this time, the manual screening of the employees who lack cards has greatly reduced the screening area .
If there are special code requirements , Need help , You can trust me in the official account. .
5. Get the missing card listFinally, call the function to get the missing card list , It mainly compares each filling date with the actual working date .
# Define the function to obtain card shortage information def stat_dail_short(date, y_m1, work_days): ''' date： Large data table for storing logs y_m1： month work_days: Number of working days in the month ''' qk_file = [] date_m = date[date[' date '].apply(sele_ym)==True] for i in set(date_m[' Filled by ']): sub_date = date_m[date_m[' Filled by '] == i] if len(sub_date[' date '])<work_days: qk = str(set(sub_date[' Filled by '])) + str(set(sub_date[' department '])) + ' Missing '+ str((work_days-len(sub_date[' date ']))) + ' Second card ' + '; The card shortage date is ：'+ str(set(mth_wkdays)^set(sub_date[' date '])) qk_file.append(qk) print(set(sub_date[' Filled by ']), set(sub_date[' department ']), ' Missing %d Second card '%(work_days-len(sub_date[' date '])), '; The card shortage date is ：', set(mth_wkdays)^set(sub_date[' date ']),sep='') qk_file_1 = pd.DataFrame(qk_file) qk_file_1.columns = [' Missing card information '] qk_file_1.to_csv(y_m1+' Missing card list '+'.csv', encoding='gbk') # Call the function to get the missing card list stat_dail_short(date, y_m1, work_days)Get the results ：
{' Zhang Jike '}{' Ministry of sports '} Missing 5 Second card ; The card shortage date is ：{'2022-04-11', '2022-04-29', '2022-04-22', '2022-04-18', '2022-04-21'}
{' Andy '}{' Robbery Department '} Missing 1 Second card ; The card shortage date is ：{'2022-04-20'}
{' Liu shiwen '}{' Ministry of sports '} Missing 2 Second card ; The card shortage date is ：{'2022-04-18', '2022-04-28'}
The data in the results are reported by 、 Filling Department 、 Number of card missing 、 The specific card missing date is displayed by splicing . Will csv In the specified folder .
If you need to put your name 、 department 、 The number of missing cards and other information shall be separated , Can be in excel By specific conditions , Or adjust the code for implementation .
At the beginning of this article exe File generation method , You can refer to Below
Pinstaller(Python Packaging for exe file )
Before I put Python The files are packed into exe When , It's been a long time , This article will describe in detail how to quickly build without installing Python Files that can also be executed on your computer
1. stay prompt Run in pip install pyinstaller , install pyinstaller library
2. stay prompt Run in where pyinstaller
3. Find to be pack Path of file storage
Put the files to be packaged in the path found
C:\Users\Administrator\Anaconda3\Scripts in ( My path is this , You follow the path of step two )
4. call cmd window
Put the files to be packaged in
C:\Users\Administrator\Anaconda3 \Scripts Under the table of contents , Press... In this folder shift+ Right mouse button , Click on Open command window here call cmd
5. stay cmd Input in pyinstaller -F file name
Example ： pack Python Draw Picchu's video , stay cmd Input in pyinstaller -F pkq_1.py
To generate a common icon exe Executable file .
6. Generate exe file
Can be in the path
C:\Users\Administrator\Anaconda3\Scripts Under the dist Found the packed... In the folder exe file ( That is, there is no need to install Python You can also run the file ).
The file icon thus generated is in a standard fixed format , If you want to generate an icon with a specific shape, you need to use the 7 Click the statement in .
7. Generate custom shape icons , stay cmd Input in ：pyinstaller -i ico route -F xxxxx.py
Example ： pack Python Draw Picchu video py file , stay cmd Input in ( notes : I put ico The icon and the files to be packaged are placed in a folder , So I directly input ico Name )
pyinstaller -i pikaqiu2.ico -F pkq_1.pyThe generated icon is Picchu shaped exe file .
I'm generating exe There have been errors in the process of , Later, I saw the method on the Internet and said that it was in cmd Run in pip uninstall matplotlib, Run the build again exe The statement will not report an error .
It's really successful to follow the online method , Although I don't understand the principle , But thank you very much ！ If you don't report an error when packing , It is not recommended to delete matplotlib library .
This is about Python This is the end of the article on automatic processing of monthly attendance card shortage data , More about Python Please search the previous articles of the software development network or continue to browse the following related articles to deal with the missing attendance card data. I hope you will support the software development network in the future ！
