11、pandas-record
<pre><code>
import pandas as pd
import time
import hashlib
import xlsxwriter
def get_timeStamp(time_str):
#将其转换为时间数组
timeStruct = time.strptime(time_str, "%Y-%m-%d %H:%M:%S")
#转换为时间戳:
timeStamp = int(time.mktime(timeStruct))
return timeStamp
def it_write_excel():
# '''
# 读取变量
book = xlsxwriter.Workbook('all_record.xlsx')
sheet = book.add_worksheet('sheet1')
# 设置sheet表单元格列宽
sheet.set_column("A:A", 10) # 欧洲
sheet.set_column("B:B", 14) # 商品名称
sheet.set_column("C:C", 14) # 正面图
sheet.set_column("D:D", 15) # 货号
sheet.set_column("E:E", 20) # 欧洲零售价
sheet.set_column("F:F", 25) # 商品链接
sheet.set_column("G:G", 25) # 商品链接
sheet.set_column("H:H", 25) # 商品链接
sheet.set_column("I:L", 14) # 商品链接
# 设定整个sheet表的单元格的格式
property = {
'font_size': 11, # 字体大小
'bold': False, # 是否加粗
'align': 'center', # 水平对齐方式 left
'valign': 'vcenter', # 垂直对齐方式
'font_name': u'微软雅黑',
'text_wrap': False, # 是否自动换行
}
cell_format = book.add_format(property)
# 设置sheet表单元格行高
sheet.set_row(0, 22) # 设置第一行的高度为22
# 在向单元格中写入内容时，加上单元格样式
# 插入第一行
sheet.write(0, 0, '名字', cell_format)
sheet.write(0, 1, '休息', cell_format)
sheet.write(0, 2, '加班', cell_format)
sheet.write(0, 3, '工作', cell_format)
sheet.write(0, 4, '周末加班分钟', cell_format)
sheet.write(0, 5, '平时加班分钟', cell_format)
sheet.write(0, 6, '全部加班分钟', cell_format)
sheet.write(0, 7, '全部加班小时', cell_format)
sheet.write(0, 8, '正常请假', cell_format)
sheet.write(0, 9, '正常出差', cell_format)
sheet.write(0, 10, '异常请假出差', cell_format)
sheet.write(0, 11, '异常加班期间时间', cell_format)
img_format = {'x_offset': 4, # 左右移动
'y_offset': 0,
'x_scale': 0.2, # 缩放比例
'y_scale': 0.19}
# 插入爬取it_goods_list信息
row_number = 1
for product in all_add_time_list:
sheet.set_row(row_number, 52) # 设置第row_number行的高度为52
sheet.write(row_number, 0, product['名字'], cell_format)
# try:
# sheet.write(row_number, 0, product['名字'], cell_format)
# except Exception as e:
# print(product)
# print(e)
sheet.write(row_number, 1, product['休息'], cell_format)
sheet.write(row_number, 2, product['加班'], cell_format)
sheet.write(row_number, 3, product['工作'], cell_format) # product['productCode']
sheet.write(row_number, 4, product['周末加班分钟'], cell_format)
sheet.write(row_number, 5, product['平时加班分钟'], cell_format)
sheet.write(row_number, 6, product['全部加班分钟'], cell_format)
sheet.write(row_number, 7, product['全部加班小时'], cell_format)
sheet.write(row_number, 8, product['正常请假'], cell_format)
sheet.write(row_number, 9, product['正常出差'], cell_format)
sheet.write(row_number, 10, product['异常请假出差'], cell_format)
sheet.write(row_number, 11, product['异常加班期间时间'], cell_format)
# zxg_dict['异常加班期间时间']
row_number = row_number + 1
book.close()
# print(download_type + ': 写入EXCEL成功')
return None
def read_infor():
sheet = pd.read_excel('record0.xlsx')
sheet.drop(sheet.index[[0,2]],inplace=True)
sheet.drop(sheet.index[[0]],inplace=True)
sheet.drop(sheet.index[[0]],inplace=True)
sheet.columns = ['name','id','depart','job','work_data','raw_time','time_1','time_2']
# sheet.head(5)
time_8_30_str = "2017-11-24 08:30:00"
time_9_00_str = "2017-11-24 09:00:00"
time_18_00_str = "2017-11-24 18:00:00"
time_23_59_str = "2017-11-24 23:59:00"
time_8_30 = get_timeStamp(time_8_30_str)
time_9_00 = get_timeStamp(time_9_00_str)
time_18_00 = get_timeStamp(time_18_00_str)
time_23_59 = get_timeStamp(time_23_59_str)
names_set = set()
name_list = []
if 1:
for name in sheet.name.values:
hash_title = hashlib.md5(name.encode(encoding='UTF-8')).hexdigest()
if hash_title in names_set:
continue
name_list.append(name)
names_set.add(hash_title)
all_add_time_list = []
for zxg in name_list:
zxg_dict = {}
zxg_dict['名字'] = zxg
zxg_df = sheet.loc[sheet['name'] == zxg]
zxg_df = zxg_df.fillna(0)
# zxg_size = int(zxg.size/8)
no_work_list_1 = []
no_work_list_2 = []
rest_list = []
add_job_list =[]
raw_job_list = []
no_work_list = []
if 2:
for index,row in zxg_df.iterrows():
if row['time_1']=='休息' and row['raw_time'] == 0:
rest_list.append(row)
elif row['time_1']=='休息' and row['raw_time'] != 0:
add_job_list.append(row)
elif '请假 08:30' in str(row) and '请假 18:00' in str(row):
no_work_list_1.append(row)
elif '出差 08:30' in str(row) and '出差 18:00' in str(row):
no_work_list_2.append(row)
elif '请假'in str(row):
no_work_list_list.append(row)
no_work_list.append(row)
elif '出差'in str(row):
no_work_list_list.append(row)
no_work_list.append(row)
else:
raw_job_list.append(row)
zxg_dict['休息'] = len(rest_list)
zxg_dict['正常请假'] = len(no_work_list_1)
zxg_dict['正常出差'] = len(no_work_list_2)
zxg_dict['异常请假出差'] = len(no_work_list)
zxg_dict['加班'] = len(add_job_list)
zxg_dict['工作'] = len(raw_job_list)
# print('休息：', len(rest_list))
# print('加班：', len(add_job_list))
# print('工作：', len(raw_job_list))
yi_chang_add_time = 0
time_i = '2017-11-24 '
time_o = ':00'
for item in no_work_list:
if '请假' in str(item['time_1']) and '请假' in str(item['time_1']) == 0:
continue
raw_time_list = item['raw_time'].split(',')
raw_size = len(raw_time_list)
t1 = time_i + raw_time_list[0] + time_o
t2 = time_i + raw_time_list[raw_size - 1] + time_o
t2 = get_timeStamp(t2)
t1 = get_timeStamp(t1)
if t2 <= time_18_00:
continue
if t2 == time_23_59:
continue
yi_chang_add_time = yi_chang_add_time + ((t2 - time_18_00)/60)
zxg_dict['异常加班期间时间'] = yi_chang_add_time
# 周末
if 3:
weekend_true_add_job_min = 0
for item in add_job_list:
# print(item)
# print(type(item))
time_i = '2017-11-24 '
time_o = ':00'
raw_time_list = item['raw_time'].split(',')
raw_size = len(raw_time_list)
t1 = time_i + raw_time_list[0] + time_o
t2 = time_i + raw_time_list[raw_size-1] + time_o
# if t1 < time_8_30:
# # 8点半以前来 多算90分钟
# true_add_job_min = int((get_timeStamp(t2) - get_timeStamp(time_8_30))/60) - 90
# else:
# # 9点以后是多少就是多少
# true_add_job_min = int((get_timeStamp(t2) - get_timeStamp(t1)) / 60)
if get_timeStamp(t1) < time_8_30:
true_add_job_min = int((get_timeStamp(t2) - get_timeStamp(time_8_30_str)) / 60) - 90
else:
true_add_job_min = int((get_timeStamp(t2) - get_timeStamp(t1))/60) - 90
weekend_true_add_job_min = weekend_true_add_job_min + true_add_job_min
zxg_dict['周末加班分钟'] = weekend_true_add_job_min
# print(weekend_true_add_job_min,'min')
# print(weekend_true_add_job_min/60,'hour')
# 平时
if 4:
raw_job_add_job_time_list = []
for item in raw_job_list:
time_i = '2017-11-24 '
time_o = ':00'
if item['raw_time'] == 0:
continue
try:
raw_time_list = item['raw_time'].split(',')
except Exception as e:
print(item)
print(item['raw_time'])
print(e)
continue
raw_size = len(raw_time_list)
t1 = time_i + raw_time_list[0] + time_o
t2 = time_i + raw_time_list[raw_size-1] + time_o
t2 = get_timeStamp(t2)
t1 = get_timeStamp(t1)
if t1 < time_8_30 and t2 > time_18_00:
raw_job_add_job_time_list.append(t2 - time_18_00)
if t1 > time_8_30 and t1 < time_9_00 and t2 > time_18_00:
need_add_time = t1 - time_8_30
raw_job_add_job_time_list.append(t2 - need_add_time - time_18_00)
if t1 > time_9_00 and t2 > time_18_00:
need_add_time = t1 - time_9_00
raw_job_add_job_time_list.append(t2 - 3*need_add_time - time_18_00)
if 5:
raw_job_true_add_job_min = 0
for item in raw_job_add_job_time_list:
raw_job_true_add_job_min = raw_job_true_add_job_min + item
zxg_dict['平时加班分钟'] = int(raw_job_true_add_job_min/60)
# print(int(raw_job_true_add_job_min/60),'min')
# print((raw_job_true_add_job_min/3600),'hour')
all_add_job_time = int(raw_job_true_add_job_min/60) + weekend_true_add_job_min + yi_chang_add_time
zxg_dict['全部加班分钟'] = all_add_job_time
zxg_dict['全部加班小时'] = all_add_job_time/60
# print(all_add_job_time)
# print(all_add_job_time/60)
# print(zxg_dict)
# no_work_list_list.append(no_work_list)
all_add_time_list.append(zxg_dict)
return all_add_time_list
no_work_list_list = []
all_add_time_list = read_infor()
# for item in no_work_list_list:
# if '出差 08:30' in str(item)
# from pandas.core.frame import DataFrame
# data = DataFrame(no_work_list_list)
# names_set = set()
# name_list = []
# if 1:
# for name in data.name.values:
# hash_title = hashlib.md5(name.encode(encoding='UTF-8')).hexdigest()
# if hash_title in names_set:
# continue
# name_list.append(name)
# names_set.add(hash_title)
# no_work_list_1 = []
# no_work_list_2 = []
# need_check_list = []
# for zxg in name_list:
# zxg_dict = {}
# zxg_dict['名字'] = zxg
# zxg_df = data.loc[data['name'] == zxg]
# zxg_df = zxg_df.fillna(0)
#
# for index, row in zxg_df.iterrows():
# if '请假 08:30' in str(row) and '请假 18:00' in str(row):
# no_work_list_1.append(row)
# elif '出差 08:30' in str(row) and '出差 18:00' in str(row):
# no_work_list_2.append(row)
# else:
# need_check_list.append(row)
#
# need_check_df = DataFrame(need_check_list)
# need_check_df.to_excel('need_check.xls',index=0)
# data.to_excel('sheet_1.xls',index=0)
# data.to_excel('sheet_2.xls')
# print(data.head(5))
# print(no_work_list_list)
# print(len(no_work_list_list))
it_write_excel()
## ```</code></pre>