马上注册，结交更多好友，享用更多功能^_^
您需要 登录 才可以下载或查看，没有账号？立即注册
x
代码如下:import pandas as pd
import numpy as np
from datetime import datetime
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
# 打开文件位置
fpath = r'E:\test\打卡整理\至联0731-0806.csv'
# header是第0行作为“列名”，读取数据存在名为df的DataFrame中
df = pd.read_csv(fpath,header = 0,index_col = None,encoding = 'gbk',low_memory=False)
# 修改时间未datatime
df['呼叫时间'] = pd.to_datetime(df['呼叫时间'])
# 提取目标数据
date = df[['接听座席','呼叫时间']]
# 设置名字选择
namedate1 = df['接听座席']
print(namedate1)
name = []
namedate2 = namedate1.dropna()
namedate3 = namedate2.drop_duplicates()
for sel in namedate3:
print(sel)
name.append(sel)
print(name)
print(len(name))
'''
# 分析数据
# 解析单个坐席
df1 = date.loc[date['接听座席'] == 'yan(1025)']
# 分切日期和时间便于查找
df1['日期'] = df1['呼叫时间'].apply(lambda x: str(x).split()[0]).astype('datetime64')
df1['时间'] = df1['呼叫时间'].apply(lambda x: str(x).split()[1])
# 解析具体日期
df2 = df1[df1['日期'] == datetime(2020,7,24)]
# 筛选第一个
df3 = df2.drop_duplicates(subset = '接听座席')
# 提取目的数据
dname = df3.iat[0,0]
dtimetemp = df3.iat[0,3]
ditme = dtimetemp[:5]
'''
# 设置总人员数据的列表
namedf = []
# 设置星期一到星期五的列表
days1 = []
days2 = []
days3 = []
days4 = []
days5 = []
# 设置每人每日的列表
day1 = []
day2 = []
day3 = []
day4 = []
day5 = []
# 设置星期一到星期五的目标数据的列表
data1 = dict()
data2 = dict()
data3 = dict()
data4 = dict()
data5 = dict()
namea = []
nameb = []
namec = []
named = []
namee = []
for each_name in name:
namedf.append(date.loc[date['接听座席'] == each_name])
for each_time in namedf:
each_time['日期'] = each_time['呼叫时间'].apply(lambda x: str(x).split()[0]).astype('datetime64')
each_time['时间'] = each_time['呼叫时间'].apply(lambda x: str(x).split()[1])
each_time['星期'] = each_time['呼叫时间'].dt.dayofweek
for each_days in namedf:
days1.append(each_days[each_days['星期'] == 0])
days2.append(each_days[each_days['星期'] == 1])
days3.append(each_days[each_days['星期'] == 2])
days4.append(each_days[each_days['星期'] == 3])
days5.append(each_days[each_days['星期'] == 4])
for each_day1 in days1:
day1.append(each_day1.drop_duplicates(subset = '接听座席'))
for each_day2 in days2:
day2.append(each_day2.drop_duplicates(subset = '接听座席'))
for each_day3 in days3:
day3.append(each_day3.drop_duplicates(subset = '接听座席'))
for each_day4 in days4:
day4.append(each_day4.drop_duplicates(subset = '接听座席'))
for each_day5 in days5:
day5.append(each_day5.drop_duplicates(subset = '接听座席'))
for dayone in day1:
if len(dayone.index) == 0:
continue
name1 = dayone.iat[0,0]
name2 = name1[:-6]
namea.append(name2)
time1 = dayone.iat[0,3]
time2 = time1[:5]
data1[name2] = time2
for daytwo in day2:
if len(daytwo.index) == 0:
continue
name1 = daytwo.iat[0,0]
name2 = name1[:-6]
nameb.append(name2)
time1 = daytwo.iat[0,3]
time2 = time1[:5]
data2[name2] = time2
for daythree in day3:
if len(daythree.index) == 0:
continue
name1 = daythree.iat[0,0]
name2 = name1[:-6]
namec.append(name2)
time1 = daythree.iat[0,3]
time2 = time1[:5]
data3[name2] = time2
for dayfour in day4:
if len(dayfour.index) == 0:
continue
name1 = dayfour.iat[0,0]
name2 = name1[:-6]
named.append(name2)
time1 = dayfour.iat[0,3]
time2 = time1[:5]
data4[name2] = time2
for dayfive in day5:
if len(dayfive.index) == 0:
continue
name1 = dayfive.iat[0,0]
name2 = name1[:-6]
namee.append(name2)
time1 = dayfive.iat[0,3]
time2 = time1[:5]
data5[name2] = time2
print('星期一数据：')
print(data1)
print(len(data1))
print('星期二数据：')
print(data2)
print(len(data2))
print('星期三数据：')
print(data3)
print(len(data3))
print('星期四数据：')
print(data4)
print(len(data4))
print('星期五数据：')
print(data5)
print(len(data5))
# 设置路径
addr = r'E:\test\打卡整理\0724-0731RCB LastCall.xlsx'
# 打开文件
wb = load_workbook(addr)
# 选择表
ws = wb['Sheet2']
# 第一行输入标题
ws.append(['星期五数据：',None,'星期一数据：',None,'星期二数据：',None,
'星期三数据：',None,'星期四数据：',None])
for d5 in range(len(data5)):
ws.cell(d5+2,1).value = namee[d5]
ws.cell(d5+2,2).value = data5[namee[d5]]
for d1 in range(len(data1)):
ws.cell(d1+2,3).value = namea[d1]
ws.cell(d1+2,4).value = data1[namea[d1]]
for d2 in range(len(data2)):
ws.cell(d2+2,5).value = nameb[d2]
ws.cell(d2+2,6).value = data2[nameb[d2]]
for d3 in range(len(data3)):
ws.cell(d3+2,7).value = namec[d3]
ws.cell(d3+2,8).value = data3[namec[d3]]
for d4 in range(len(data4)):
ws.cell(d4+2,9).value = named[d4]
ws.cell(d4+2,10).value = data4[named[d4]]
wb.save(addr)
感觉很多都重复，但是想不到怎么优化，有大神给个思路吗？最好是优化后的 | 
( 粤ICP备18085999号-1 | 粤公网安备 44051102000585号)
狗仔卡
发表于 2020-8-10 18:13:52
置顶卡
千斤顶
显身卡
楼主
发表于 2020-8-13 11:27:31
