pandas数据处理 之excel

# coding:utf-8
# author :yuyang2@sensetime.com
# function ：根据excel和搜索desktop的文件名称和路径，自动生成json配置文件

import re
import os
import json
import pandas as pd

# 需要预先设置的路径
pathReadExcel = 'C:\\yuyang2\\桌面\\桌面测试文件夹\\Desktop\\黄婷\\10个对标字节效果\\参数调整.xlsx'  # 产品给的excel路径
pathReadJson = 'C:\\Users\yuyang2\\Downloads\\releaseFor鱼耳20210329\\json\\zhigan.json'  # 原始json路径，美妆type为中文
pathDesktopRoot = 'C:\\Users\\yuyang2\\Downloads\\releaseFor鱼耳20210329'  # desktop文件夹跟路径
jsonGenerateNum = 8  # 生成的json个数，不超过excel中的行数


### read json data ###
def readJson(pathJson):
    with open(pathJson, 'r') as f:
        write_data = json.load(f)
    return write_data


### write json data ###
def writeJson(dataDict, pathSaveJson):
    with open(pathSaveJson, 'w') as f:
        json.dump(dataDict, f, ensure_ascii=False, )  # ensure_ascii=False解决中文乱码


### read excel data ###
def readExcel(pathReadExcel):
    dataFrame = pd.DataFrame(pd.read_excel(pathReadExcel))
    return dataFrame


# 根据索引，返回数组的子集,或删除子集
def subListSet(alist, idex=None, rmIdex=None):
    tempList = alist.copy()
    if bool(idex):
        return [tempList[x] for x in idex]
    elif bool(rmIdex):
        for i in sorted(rmIdex, reverse=True):
            del tempList[i]
        return tempList


# 检查excel标题和json中文件中的异同
def checkListDiff(list_1, list_2):
    dif = set(list_1) ^ set(list_2)
    for element in dif:
        source = 'json' if element in list_1 else 'excel'
        if '磨皮' not in element and '美白' not in element:
            print("\033[1;33mdifferent element: %s is from  %s, not exist in the other file!\n" % (element, source))


# 从批量文件中，找有特定后缀文件的files
def serchFile(path, suffix):
    searchList = [element.replace(suffix, '') for element in os.listdir(path) if element.endswith(suffix)]
    return searchList


# 判断文件夹是否存在，输入可以是字符串或列表
def fileExists(pathList):
    if type(pathList) != list:
        os.makedirs(path)
        return
    for path in pathList:
        if not os.path.exists(path):
            os.makedirs(path)
            print("%s 目标文件夹不存在！已经自动创建！！", path)


pathFilters = os.path.join(pathDesktopRoot, 'filters')  # filters文件夹路径
pathMakeups = os.path.join(pathDesktopRoot, 'makeups')  # makeups文件夹路径
pathJson = os.path.join(pathDesktopRoot, 'jsonNew')  # 生成的json路径

# 判断文件夹是否存在
needFileList = [pathFilters, pathFilters, pathJson]
fileExists(needFileList)

pathSaveJson = os.path.join(pathJson, '{}.json')  # 生成的json保文件保存存路径

df = readExcel(pathReadExcel)  # 读取excel文件
titleList = df.columns.to_list()

# 打印excel中title名字
beautyName = subListSet(titleList, rmIdex=[0, 1, 2])[:-7]  # 从excel中选取BeauParams的名字
print("\033[1;30mbeautyName:\033[1;36m{}, 数量为:{}".format(beautyName, len(beautyName)))

makeupsName = titleList[-7:]  # 从excel中选取BeauParams的名字
print("\033[1;30mmakeups Name:\033[1;36m{}, 数量为:{}".format(makeupsName, len(makeupsName)))



# 读取json文件，得到字典
write_data = readJson(pathReadJson)

# check differents in excel and json
beautyNameJson = [item["name"] for item in write_data["BeauParams"]]  # 从json中获取BeauParams的名字列表，以便check
checkListDiff(beautyNameJson, beautyName)  # 此处只对Params进行了check。其它没有check

# 美妆和滤镜文件列表
makeupsList = serchFile(pathMakeups, '.zip')
filtersList = serchFile(pathFilters, '.model')

if __name__ == '__main__':

    # 判断设置的json生成个数和excel中实际行数之间的关系
    jsonGenerateNum = jsonGenerateNum if jsonGenerateNum < int(df.shape[0]) else int(df.shape[0])

    # 循环遍历json文件
    for i in range(jsonGenerateNum):
        saveNameJson = re.findall('[a-zA-Z]+', df.loc[[i], ['现命名']].values.tolist()[0][0])[0]

        # 处理美颜参数
        beautyValue = df.loc[[i], beautyName].values.tolist()[0]  # 从excel中选取BeauParams的名字对应的值
        beautyValue = [float(x / 100) for x in beautyValue]
        beautyDict = dict((key, value) for key, value in zip(beautyName, beautyValue))  # 字典推导式

        for element in write_data["BeauParams"]:  # 遍历json文件的BeauParams参数
            numInJson, numInExcel = re.findall('\d', element['name']), re.findall('\d', beautyName[0])
            if element['name'] in beautyDict:
                element['value'] = beautyDict[element['name']]
            elif '美白' in element['name'] and numInJson == numInExcel:
                element['value'] = beautyDict[beautyName[0]]
            elif '磨皮' in element['name']:
                element['磨皮模式'], element['磨皮强度'] = float(numInExcel[0]), beautyDict[beautyName[2]]
            else:
                element['value'] = 0.0

        # 处理滤镜参数
        filterName = df.loc[[i], ['滤镜']].values.tolist()[0][0]
        filterNameExcel = ';'.join(re.findall('\D+', filterName))
        filterValueExcel = ';'.join(re.findall('\d+', filterName))
        filterValueExcel = float(filterValueExcel) if filterValueExcel else 0.0
        write_data["AllFilterName"][0]['name'] = ''
        write_data["AllFilterName"][0]['value'] = 0.0
        count = 0
        for name in filtersList:
            if filterNameExcel in name:
                write_data["AllFilterName"][0]['name'] = filterNameExcel
                write_data["AllFilterName"][0]['value'] = filterValueExcel
                count += 1
        if count > 1:
            print("Filter文件夹中有两个相近的名字，请修改excel中'滤镜'参数的命名！！")
            assert False

        # 处理美妆参数
        for element in write_data["makeup"]:
            # 处理excel中美妆参数
            makeupsValue = df.loc[[i], makeupsName].values.tolist()[0]  # 从excel中选取BeauParams的名字对应的值
            makeupsDict = dict((key, value) for key, value in zip(makeupsName, makeupsValue))  # 字典推导式
            element['name'], element['value'] = '', 0  # 先清空json中数据

            if element['type'] == '整装':
                element['name'], element['value'] = saveNameJson, 1
            if element['type'] in makeupsDict:
                makeupValue = makeupsDict[element['type']]
                if makeupValue != 0:
                    element['name'], element['value'] = re.findall('\d*\D+', makeupValue)[0], float(
                        re.findall('\D+(\d+)', makeupValue)[0]) / 100

        ###将处理后的数据写入json，并保存生成##
        write_data["AllMaterialName"][0] = saveNameJson
        writeJson(write_data, pathSaveJson.format(saveNameJson))