import pandas as pd
import openpyxl as ox
from itertools import groupby
import os
import tablib
class ExcelUtil:
# 当前项目路径
dir_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) + r'/elab_mvp/resources'
"""
解析excel文件
"""
def __init__(self, sheet_name=None, file_name=None):
if file_name:
self.path = os.path.join(self.dir_path, file_name)
else:
self.path = os.path.join(self.dir_path, 'mvp.xlsx')
if sheet_name:
self.sheet_name = sheet_name
else:
self.sheet_name = '测试数据'
def read_excel_by_pd(self):
df = pd.read_excel(self.path)
data = df.head()
print('获取到的数据{}'.format(data))
def read_excel_by_ox(self):
work_book = ox.load_workbook(self.path, data_only=True)
work_sheet = work_book.get_sheet_by_name(self.sheet_name)
# print('max_row:{}, max_col:{}'.format(work_sheet.max_row, work_sheet.max_column))
return work_sheet
def read_excel_by_ox_name(self, sheet_name):
work_book = ox.load_workbook(self.path, data_only=True)
work_sheet = work_book.get_sheet_by_name(sheet_name)
# print('max_row:{}, max_col:{}'.format(work_sheet.max_row, work_sheet.max_column))
return work_sheet
def init_crowd_info(self):
"""
整理不同人群包含的父选序号
:return:
"""
rows = [row for row in self.read_excel_by_ox().rows]
crowd_a = []
crowd_b = []
crowd_c = []
crowd_d = []
crowd_e = []
crowd_f = []
for row in rows[2:]:
option = row[4].value
a = row[6].value
if a is not None and a == 1 and option not in crowd_a:
crowd_a.append(option)
b = row[7].value
if b is not None and b == 1 and option not in crowd_b:
crowd_b.append(option)
c = row[8].value
if c is not None and c == 1 and option not in crowd_d:
crowd_c.append(option)
d = row[9].value
if d is not None and d == 1 and option not in crowd_d:
crowd_d.append(option)
e = row[10].value
if e is not None and e == 1 and option not in crowd_e:
crowd_e.append(option)
f = row[11].value
if f is not None and f == 1 and option not in crowd_f:
crowd_f.append(option)
return {'A': crowd_a, 'B': crowd_b, 'C': crowd_c, 'D': crowd_d, 'E': crowd_e, 'F': crowd_f}
def init_out_way(self):
result = {}
work_sheet = self.read_excel_by_ox_name('用户画像-出行方式')
rows = work_sheet.rows
for row in rows:
key = row[3].value + row[4].value + '市' + row[6].value + row[7].value
result[key] = float(row[9].value)
return result
def init_mvp_data(self):
"""
获取每个标签包括的父题父选项编号
:return:
"""
no_need_module = ['空间需求图谱-单品偏好', '空间需求图谱-精装关注点', '空间需求图谱-空间特性偏好', '空间需求-材质',
'空间需求-色调', '空间需求-色相']
rows = [row for row in self.read_excel_by_ox().rows][36:]
tag_name = None
tag_type = None
datas = []
for row in rows:
tag_type_1 = row[0].value
tag = row[1].value
values = row[3].value
corr = row[4].value
if tag_type_1:
tag_type = tag_type_1
if tag:
tag_name = tag
if values is not None and values != '找不到':
datas.append([tag_type, tag_name, values, corr])
result = {}
datas.sort(key=lambda obj: obj[0])
for tag_type, sub_datas in groupby(datas, key=lambda obj: obj[0]):
if tag_type not in no_need_module:
sub_list = [x for x in sub_datas]
sub_list.sort(key=lambda obj: obj[1])
sub_result = {}
for name, items in groupby(sub_list, key=lambda obj: obj[1]):
orders = []
for n in items:
orders.append([n[2], n[3]])
sub_result[name] = orders
result[tag_type] = sub_result
return result
def init_scores(self):
work_sheet = self.read_excel_by_ox()
rows = [row for row in work_sheet.rows]
datas = []
for row in rows[1:]:
if row[0].value is not None:
datas.append([row[0].value, row[1].value, row[2].value, row[3].value, row[4].value])
return datas
def init_module_info(self):
work_sheet = self.read_excel_by_ox()
max_column = work_sheet.max_column
rows = [row for row in work_sheet.rows][3:]
crowd_name = None
datas = []
for row in rows:
crowd = row[1].value
if crowd is not None:
crowd_name = crowd
behavior = row[2].value
score = row[4].value
for index in range(6, max_column - 1, 2):
module_name = row[index].value
if module_name is not None:
weight = row[index + 1].value
datas.append([crowd_name, behavior, score, module_name, weight])
results = {}
datas.sort(key=lambda obj: obj[0])
for name, items in groupby(datas, key=lambda obj: obj[0]):
sub_results = {}
sub_list = []
for it in items:
sub_list.append([x for x in it])
sub_list.sort(key=lambda obj: obj[3])
for name_1, itmes_1 in groupby(sub_list, key=lambda obj: obj[3]):
sub_data = []
for n in itmes_1:
# print(' {}'.format(n[1]))
sub_data.append([n[1], n[2], n[4]])
sub_results[name_1] = sub_data
results[name] = sub_results
return results
def module_behavior_info(self):
"""
构建模块和行为的关联信息
:return:
"""
work_sheet = self.read_excel_by_ox_name('行为-模块映射表')
max_column = work_sheet.max_column
rows = [row for row in work_sheet.rows][1:]
infos = []
for row in rows:
behavior_name = row[1].value
for i in range(2, max_column - 1):
module_name = row[i].value
if module_name:
if i == 2:
weight = 1
else:
weight = 0.5
infos.append([row[i].value, behavior_name, weight])
infos.sort(key=lambda obj: obj[0])
result = {}
for key, data in groupby(infos, key=lambda obj: obj[0]):
behavior_data = []
for dt in data:
dt_list = [x for x in dt]
if len(behavior_data) <= 14:
behavior_data.append([dt_list[1], dt_list[2]])
result[key] = behavior_data
return result
def read_options_info(self):
"""
获取选项的配置信息
:return:
"""
work_sheet = self.read_excel_by_ox()
rows = [row for row in work_sheet.rows][1:]
info = {}
for row in rows:
key = str(int(row[1].value)) + str(int(row[9].value))
# tag, title, name
info[key] = [row[15].value, row[13].value, row[14].value, row[6].value]
return info
def create_excle(self, file_name, header, data):
data_set = tablib.Dataset(data, header=header)
save_path = os.path.join(self.dir_path, file_name)
with open(save_path, 'wb', encoding='utf8') as f:
f.write(data_set.xlsx)
def wenjuanxin_84(self):
work_sheet = self.read_excel_by_ox()
question_work_sheet = self.read_excel_by_ox_name('Sheet2')
rows = [row for row in work_sheet.rows][1:]
question_rows = [row for row in question_work_sheet.rows][1:]
question_data = []
for qr in question_rows:
question_data.append([str(qr[0].value), str(qr[1].value), str(qr[2].value)])
question_dict = {}
question_data.sort(key=lambda obj: obj[0])
for key, data in groupby(question_data, key=lambda obj: obj[0]):
data_list = []
for dt in data:
data_list.append(dt[1:])
question_dict[str(key)] = data_list
# print(json.dumps(question_dict, indent=4, ensure_ascii=False))
def get_sub_option_id(sub_question_id, sub_option_content):
sub_option_contents = question_dict.get(str(sub_question_id))
if sub_option_contents:
for sc in sub_option_contents:
if sc[1] == sub_option_content:
return sc[0]
else:
# print(sub_question_id, sub_option_content)
pass
# uuid，score(sub_option_id)，created，sub_question_id
insert_data = []
for row in rows:
uuid = row[0].value + '1000'
date = row[1].value
question_1 = str(row[6].value).split('.')[1]
id_1 = get_sub_option_id(20, question_1)
if id_1:
insert_data.append([uuid, id_1, date, 20])
question_2 = str(row[7].value).split('.')[1]
id_2 = get_sub_option_id(29, question_2)
if id_2:
insert_data.append([uuid, id_2, date, 29])
question_3 = str(row[8].value).split('┋')
for q3 in question_3:
content = str(q3.split('.')[1])
id_3 = get_sub_option_id(370, content)
if id_3:
insert_data.append([uuid, id_3, date, 370])
question_4 = str(row[9].value).split('┋')
for q4 in question_4:
content = q4.split('.')[1]
id_4 = get_sub_option_id(371, content)
if id_4:
insert_data.append([uuid, id_4, date, 371])
question_5 = str(row[10].value).split('┋')
for q5 in question_5:
content = q5.split('、')[1]
id_5 = get_sub_option_id(372, content)
if id_5:
insert_data.append([uuid, id_5, date, 372])
question_6 = str(row[11].value).split('┋')
for q6 in question_6:
if q6.find('E') == 0:
content = q6.replace('E', '')
else:
content = q6.split('.')[1]
id_6 = get_sub_option_id(379, content)
if id_6:
insert_data.append([uuid, id_6, date, 379])
question_7 = str(row[12].value).split('┋')
for q7 in question_7:
content = q7.split('.')[1]
id_7 = get_sub_option_id(373, content)
if id_7:
insert_data.append([uuid, id_7, date, 373])
question_8 = str(row[13]).split('┋')
for q8 in question_8:
content = q8.split('.')[1]
id_8 = get_sub_option_id(374, content)
if id_8:
insert_data.append([uuid, id_8, date, 374])
question_9 = str(row[14].value).split('┋')
for q9 in question_9:
content = q9.split('.')[1]
id_9 = get_sub_option_id(375, content)
if id_9:
insert_data.append([uuid, id_9, date, 375])
question_10 = str(row[15].value).split('.')
id_10 = get_sub_option_id(376, question_10)
if id_10:
insert_data.append([uuid, id_10, date, 376])
question_11 = str(row[16].value).split('、')
id_11 = get_sub_option_id(379, question_11)
if id_11:
insert_data.append([uuid, id_11, date, 379])
question_12 = str(row[17].value).split('┋')
for q12 in question_12:
content = q12.split('.')
id_12 = get_sub_option_id(380, content)
if id_12:
insert_data.append([uuid, id_12, date, 380])
question_13 = str(row[18].value).split('┋')
for q13 in question_13:
content = q13.split('、')
id_13 = get_sub_option_id(381, content)
if id_13:
insert_data.append([uuid, id_13, date, 381])
question_14 = str(row[19].value).split('、')
id_14 = get_sub_option_id(395, question_14)
if id_14:
insert_data.append([uuid, id_14, date, 395])
city = str(row[20].value).split('-')[1]
id_city = get_sub_option_id(377, city)
if id_city:
insert_data.append([uuid, id_city, date, 377])
return insert_data
def get_table_type_info(self):
work_sheet = self.read_excel_by_ox()
rows = [row for row in work_sheet.rows][1:]
result = []
for row in rows:
question_name = row[3].value
info = row[8].value
infos = str(info).split('+')
if len(infos) == 2:
table_type = infos[1]
table_size = infos[0].split('p')[0]
result.append([table_type, table_size, question_name])
pass
else:
pass
return result
sql_jianye_1 = """insert into report_push_customer_info(name,house_or_region,customer_type,mail,status,creator,created) values(%s,%s,%s,%s,1,'binren', now())"""
sql_jianye_2 = """
insert into report_customer_authority_info (customer_id, house_or_brand_id, status, creator, created) values (%s, %s, 1, 'binren', now())
"""
sql_jianye_3 = """
insert into report_task_info(customer_id, task_key, status, creator, created) values (%s, %s, 1, 'binren', now())
"""
def jinaye_report(self):
mail_list_sheet = self.read_excel_by_ox_name('邮箱列表')
mail_rows =[row for row in mail_list_sheet.rows][1:]
customers = []
for row in mail_rows:
mail = row[0].value
name = row[1].value
customer_type = row[4].value
house_or_region = row[2].value
house_or_brand_id = row[6].value
# if house_or_brand_id:
customers.append([name, house_or_region, customer_type, mail, house_or_brand_id])
houses = {}
# region_list = self.read_excel_by_ox_name('区域映射关系')
# region_rows = [row for row in region_list.rows][1:]
# region_names = []
# for row in region_rows:
# name = row[2].value
# if name not in region_names:
# region_names.append(name)
# for name in region_names:
# ids = []
# for row in region_rows:
# if row[2].value == name:
# ids.append(row[0].value)
# houses[name] = ids
return customers
if __name__ == '__main__':
from mysql_db import MysqlDB
eu = ExcelUtil('邮箱列表', r'D:\elab\elab_mvp\resources\建业邮件推送数据收件信息汇总(1).xlsx')
mysql = MysqlDB('linshi', 1)
customers = eu.jinaye_report()
result1 = mysql.select("select * from report_push_customer_info where created like '2020-04-17%' and customer_type != 3")
# mysql.add_some(eu.sql_jianye_1, customers)
insert = []
for r in result1:
for c in customers:
if r[1] == c[0] and r[3] == c[2]:
insert.append([r[0], c[4]])
print(insert)