从大量json文件中提取数据并比较

 有这样一个需求，在一个现有的json文件中抽取出指定型号的数据，放入excel中，之后再从本地的各个型号的文件夹中的json文件中提取出json放入excel中，最后对比两部分数据是否一致。

本地的各个型号的文件有57个，每个文件存放的json文件目录固定。

Android有9项support项、iOS有10项support项，所以需要在远端和本地的各个型号support json文件中查找这9-10项的配置

实现过程中，读写excel用到了pandas，可以循环写入，也可以一次性写入，具体实现方法如下：

def getLocalData(filePath, oSystem):
    global localSupportKeys

    # Android和iOS相同，都是从remote support中获取
    devsModel = getDevicesModel()

    # 读取已有的excel，按列插入数据
    df = pandas.read_excel(filePath, sheet_name='Sheet1')

    # 查找本次的改动项，Android一共9项（不包含video），iOS一共10项（包含video）
    if oSystem == 'Android':
        localSupportKeys = localSupportKeyOfAndroid
    elif oSystem == 'iOS':
        localSupportKeys = localSupportKeyOfiOS

    for devModel in devsModel:
        print(devModel)

        # 打开对应型号的local support文件，此处区分Android和iOS
        supportOflocal = openLocalSupportFile(devModel, oSystem)

        for localSupportItem in localSupportKeys:
            supportOflocal_copy = copy.deepcopy(supportOflocal)

            print(localSupportItem)

            # 在下沉文件中取出设备型号，这样更准确一些，也可以用当前的devModel这个是从远程文件中获取到的
            model = supportOflocal_copy["model"]
            for ii in supportOflocal_copy["config"]:
                if ii["funcId"] == localSupportItem:
                    funcId = ii["funcId"]
                    del ii["funcId"]
                    print("删除funcId后的项：", ii)
                    # 删除项目后需要排序，不然remote和local对比不一致
                    iiAfterSorted = OrderedDict(sorted(ii.items()))
                    iiJson = json.dumps(iiAfterSorted)
                    df["Local model"] = [model]
                    df["Local funcId"] = [funcId]
                    df["Local support"] = [iiJson]
                    with pandas.ExcelWriter(filePath, engine='openpyxl', mode='a', if_sheet_exists='overlay') as writer:
                        # 获取最大行数
                        max_row = writer.sheets['Sheet1'].max_row
                        df.to_excel(writer, sheet_name='Sheet1', index=False, header=False, startrow=max_row)

def getRemoteData(filePath, oSystem):
    global remoteSupportKeys

    # 此处不区分Android和iOS，远程文件是一个
    remoteSupports = openRemoteSupportFile()

    # 创建新的dataframe，用来一次性写入数据
    dfNewData = pandas.DataFrame({
        "Remote model": [],
        "Remote funcId": [],
        "Remote support": []
    })

    # 每行内容列表
    remoteModel = []
    remoteFuncId = []
    remoteSupport = []

    # 查找本次的改动项，Android一共9项（不包含video），iOS一共10项（包含video），参考wiki：https://wiki.glazero.com/pages/viewpage.action?pageId=26268592
    if oSystem == 'Android':
        remoteSupportKeys = remoteSupportKeyOfAndroid
    elif oSystem == 'iOS':
        remoteSupportKeys = remoteSupportKeyOfiOS

    for item in remoteSupports:
        # 每个型号对应一个item，获取对应的设备型号
        # df["Remote model"] = item["model"]
        # 按照support项遍历item["config"]
        for remoteSupportItem in remoteSupportKeys:
            supprtOfRemote_copy = copy.deepcopy(item)
            print(remoteSupportItem)

            # 在远程support文件中取出设备型号
            model = supprtOfRemote_copy["model"]
            for ii in supprtOfRemote_copy["config"]:
                if ii["funcId"] == remoteSupportItem:
                    funcId = ii["funcId"]
                    print(ii["funcId"])
                    del ii["funcId"]
                    # 删除项目后需要排序，不然remote和local对比不一致
                    iiAfterSorted = OrderedDict(sorted(ii.items()))
                    iiJson = json.dumps(iiAfterSorted)
                    remoteModel.append(model)
                    remoteFuncId.append(funcId)
                    remoteSupport.append(iiJson)

    dfNewData["Remote model"] = remoteModel
    dfNewData["Remote funcId"] = remoteFuncId
    dfNewData["Remote support"] = remoteSupport

    with pandas.ExcelWriter(filePath, engine='openpyxl', mode='a', if_sheet_exists='overlay') as writer:
        dfNewData.to_excel(writer, sheet_name='Sheet1', index=False, header=True, startrow=0)

def compareOfRemoteAndLocal(filePath):
    df = pandas.read_excel(filePath, sheet_name='Sheet1')

    df["Compare result"] = df.apply(lambda row: "一致" if row["Remote support"] == row["Local support"] and row["Remote model"] == row["Local model"] else "不一致", axis=1)

    df.to_excel(filePath, sheet_name='Sheet1', index=False, header=True, engine='openpyxl')

数据报告如下：