'''
@author:姜广明
@@data:2020年3月8日
'''
import pandas as pd
registration = pd.read_excel('d:/Python Temp/gj_registrationExport.xls', skiprows=1, usecols='A,C,F,G,H,I,R,T,U')
'''A="样品编号", B="委托编号", C="报告编号", D="数据状态", E="下一个状态", F="委托日期",
G="工程/产品名称", H="项目名称", I="委托单位", J="工程名称", K="生产厂家", L="样品数量",
M="抽样数量", N="规格型号", O="样品批号", P="商标", Q="检验类别", R="检测参数", S="样品资料",
T="指定主检人", U="标准价格费用", V="折扣价格费用", W="实际价格费用", X="收费情况", Y="报告交付方式",
Z="样品登记人", AA="委托人", AB="委托人电话", AC="委托单位地址"
'''
"""
registration文件数据整理
将'委托日期'数据改为为日期索引格式并且设置为表格的索引,
按报告编号排序
"""
registration['委托日期'] = pd.DatetimeIndex(registration['委托日期'])
registration = registration.set_index('委托日期')
registration.sort_values(by='报告编号', inplace=True)
pd.options.display.max_columns = 777
print(registration.head(3)) #输出表的前3行
registration.to_excel('d:/Python Temp/registration.xls')
registration_2020 = registration['2020'] #筛选出2020年的所有报告并写入registration_2020文件中
print(registration_2020.head(3)) #输出表的前3行
print(len(registration_2020['2020'])) #输出2020年的报告编号数量
registration_2020.to_excel('d:/Python Temp/registration_2020.xls')
'''
从registration_2020文件中,按月份筛选出2020年每个月的报告
并写入‘registration_2020按月统计’文件的不同Sheet中
'''
writer = pd.ExcelWriter('d:/Python Temp/registration_2020按月统计.xls')
registration_2020['2020-01'].to_excel(writer, '一月份')
registration_2020['2020-02'].to_excel(writer, '二月份')
registration_2020['2020-03'].to_excel(writer, '三月份')
registration_2020['2020-04'].to_excel(writer, '四月份')
registration_2020['2020-05'].to_excel(writer, '五月份')
registration_2020['2020-06'].to_excel(writer, '六月份')
registration_2020['2020-07'].to_excel(writer, '七月份')
registration_2020['2020-08'].to_excel(writer, '八月份')
registration_2020['2020-09'].to_excel(writer, '九月份')
registration_2020['2020-10'].to_excel(writer, '十月份')
registration_2020['2020-11'].to_excel(writer, '十一月份')
registration_2020['2020-12'].to_excel(writer, '十二月份')
writer.close()
print('输出2020年每个月接受委托的样品数量')
print('一月份', registration_2020['2020-01']['样品编号'].value_counts().head(5))
print('二月份', registration_2020['2020-02']['样品编号'].value_counts().head(5))
print('三月份', registration_2020['2020-03']['样品编号'].value_counts().head(5))
print('四月份', registration_2020['2020-04']['样品编号'].value_counts().head(5))
print('五月份', registration_2020['2020-05']['样品编号'].value_counts().head(5))
print('六月份', registration_2020['2020-06']['样品编号'].value_counts().head(5))
print('七月份', registration_2020['2020-07']['样品编号'].value_counts().head(5))
print('八月份', registration_2020['2020-08']['样品编号'].value_counts().head(5))
print('九月份', registration_2020['2020-09']['样品编号'].value_counts().head(5))
print('十月份', registration_2020['2020-10']['样品编号'].value_counts().head(5))
print('十一月份', registration_2020['2020-11']['样品编号'].value_counts().head(5))
print('十二月份', registration_2020['2020-12']['样品编号'].value_counts().head(5))
print('--------------------------------')