#coding=utf-8
import pandas as pd
# 显示所有列
pd.set_option('display.max_columns', None)
# 显示所有行
pd.set_option('display.max_rows', None)
# 设置value的显示长度为100,默认为50
pd.set_option('max_colwidth',100)
content_list = []
user_labels_list = []
# 读取excel表格
user_labels = pd.DataFrame(pd.read_excel("2013.xlsx"))
# 选择客户机构名称这个字段
for label in user_labels["客户机构名称(*)"]:
user_labels_list.append(label)
user_labels_lists = list(set(list2))
user_labels_lists.sort(key=list2.index)
for i in user_labels_lists:
data = user_labels[user_labels["客户机构名称(*)"] == "{}".format(i)]
datas = data["产品名称"].tolist()
datas_list = list(set(datas))
# print(datas_list) # ['DTG', '实时行情QTS'] 产品名称种类
for ii in datas_list:
data2 = data[data["产品名称"] == "{}".format(ii)]
try:
item = {}
item["kehunanme"] = i
def shiyong():
try:
shiyong = data2[data2["账号类型(*)\n(正式\试用)"] == "试用"]
return shiyong
except:
pass
shiyong = shiyong()
# print(shiyong)
def chishu():
try:
num = shiyong["操作日期"].shape
chishu = str(num)[1:-1].split(",")[0]
if chishu is None:
chishu = ""
return chishu
else:
return chishu
except:
pass
item["chishu"] = chishu()
def yiriqi():
try:
yiriqi = (shiyong["操作日期"].tolist())[0]
if yiriqi is None:
yiriqi = ""
return yiriqi
else:
return yiriqi
except:
pass
item["yiriqi"] = yiriqi()
def erriqi():
try:
erriqi = (shiyong["操作日期"].tolist())[-1]
if erriqi is None:
erriqi= ""
return erriqi
else:
return erriqi
except:
pass
item["erriqi"] = erriqi()
def cpname():
try:
cpname = (shiyong["产品名称"].tolist())[0]
if cpname is None:
cpname = ""
return cpname
else:
return cpname
except:
pass
item["cpname"] = cpname()
def ren():
try:
ren = (shiyong["商务联系人(*)"].tolist())[0]
if ren is None:
ren = ""
return ren
else:
return ren
except:
pass
item["ren"] = ren()
def zhengshi():
try:
zhengshi = data2[data2["账号类型(*)\n(正式\试用)"]=="正式"]
return zhengshi
except:
pass
zhengshi = zhengshi()
# print(zhengshi)
def zschishu():
try:
nums = zhengshi["操作日期"].shape
zschishu = str(nums)[1:-1].split(",")[0]
if zschishu is None:
zschishu= ""
return zschishu
else:
return zschishu
except:
pass
item["zschishu"] = zschishu()
def sanriqi():
try:
sanriqi = (zhengshi["操作日期"].tolist())[0]
if sanriqi is None:
sanriqi = ""
return sanriqi
else:
return sanriqi
except:
pass
item["sanriqi"] = sanriqi()
print(item)
content_list.append(item)
except Exception as e:
print(e)
kehunanme = [z1["kehunanme"] for z1 in content_list]
chishu = [z2["chishu"] for z2 in content_list]
yiriqi = [z3["yiriqi"] for z3 in content_list]
erriqi = [z4["erriqi"] for z4 in content_list]
cpname = [z5["cpname"] for z5 in content_list]
zschishu = [z6["zschishu"] for z6 in content_list]
sanriqi = [z7["sanriqi"] for z7 in content_list]
ren = [z8["ren"] for z8 in content_list]
data = pd.DataFrame({'客户名称': kehunanme, '试用次数': chishu, '第一次试用日期': yiriqi,'最后一次试用日期':erriqi,'产品名称':cpname,'购买次数':zschishu,'正式购买试用日期':sanriqi,'销售人员':ren})
data.to_excel("ProductManager.xlsx",index=0)