import pandas as pd import openpyxl as ox from itertools import groupby import os 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 if __name__ == '__main__': import json eu = ExcelUtil(file_name=r'D:\elab\elab_mvp\resources\module.xlsx') data = eu.module_behavior_info() print(json.dumps(data, ensure_ascii=False))