全自动化处理每月缺卡数据，输出缺卡人员信息

# -*- coding: UTF-8 -*-
'''
代码用途 ：处理缺卡数据
作者     ：阿黎逸阳
博客     :  https://blog.csdn.net/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

def num_to_date(x):   
    '''
    日期处理函数
    把时间戳或带时分秒的时间转换成只含年月日的时间
    '''
    try:
        x1 = datetime(*xldate_as_tuple(x, 0)).strftime('%Y-%m-%d')
    except:
        x1 = datetime.date(x).strftime('%Y-%m-%d')
    return x1

#读取数据
os.chdir(r'D:\每月缺卡数据处理')
date = pd.read_excel('判断是否提交日志2.xlsx', sheet_name='Sheet1')

#调整日期格式
date['填报时间'] = date['填报时间'].apply(num_to_date)
date['日期'] = date['日期'].apply(num_to_date)

#取出想看缺卡信息的年月
y_m1 = date['日期'][0][0:7] 

def sele_ym(x, y_m=y_m1):
    '''
    判断数据框中的日期是否为某月
    '''
    if x.find(y_m)>=0:
        return True

#找出这一个月中的工作日，求出工作日的天数
days = calendar.Calendar().itermonthdates(int(y_m1.split('-')[0]), int(y_m1.split('-')[1]))    
mth_nwkdays = []  #非工作日
mth_wkdays = []   #工作日
mth_days = []     #全部日期
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)    #工作日天数

#定义获取缺卡信息的函数
def stat_dail_short(date, y_m1, work_days):
    '''
    date：存储日志的数据大表
    y_m1：月份
    work_days:该月的工作天数
    '''
    qk_file = []
    date_m = date[date['日期'].apply(sele_ym)==True]
    for i in set(date_m['填报人']):
        sub_date = date_m[date_m['填报人'] == i]
        if len(sub_date['日期'])<work_days:
            qk = str(set(sub_date['填报人'])) + str(set(sub_date['部门'])) + '缺了'+ str((work_days-len(sub_date['日期']))) + '次卡' + '；缺卡日期为：'+ str(set(mth_wkdays)^set(sub_date['日期']))
            qk_file.append(qk)
            print(set(sub_date['填报人']), set(sub_date['部门']), '缺了%d次卡'%(work_days-len(sub_date['日期'])), '；缺卡日期为：', set(mth_wkdays)^set(sub_date['日期']),sep='')
    qk_file_1 = pd.DataFrame(qk_file)
    qk_file_1.columns = ['缺卡信息']
    qk_file_1.to_csv(y_m1+' 缺卡名单'+'.csv', encoding='gbk') 
    
#调用函数获取缺卡名单
stat_dail_short(date, y_m1, work_days)

{'张继科'}{'体育部'}缺了5次卡；缺卡日期为：{'2022-04-11', '2022-04-29', '2022-04-22', '2022-04-18', '2022-04-21'}
{'杨紫'}{'历劫部'}缺了1次卡；缺卡日期为：{'2022-04-20'}
{'刘诗雯'}{'体育部'}缺了2次卡；缺卡日期为：{'2022-04-18', '2022-04-28'}