|
|
马上注册，结交更多好友，享用更多功能^_^
您需要 登录 才可以下载或查看，没有账号？立即注册
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
