时间戳

首页 > 解决方案 > 使用 python 实现 Json 的 Excel

python - 使用 python 实现 Json 的 Excel

问题描述

Json 代码：下面我们有我使用 API 从站点中提取的 json 数据格式

response={
      "result": [
        {
          "id": "1000",
          "title": "Fishing Team View",
          "sharedWithOrganization": True,
          "ownerId": "324425",
          "sharedWithUsers": ["1223","w2qee3"],
          "filters": [
            {
              "field": "tag5",
              "comparator": "==",
              "value": "fishing"
            }
          ]
        },
        {
          "id": "2000",
          "title": "Farming Team View",
          "sharedWithOrganization": False,
          "ownerId": "00000",
          "sharedWithUsers": [
            "00000",
            "11111"
          ],
          "filters": [
            {
              "field": "tag5",
              "comparator": "!@",
              "value": "farming"
            }
          ]
        }
      ]
    }

Python代码：我正在使用下面的代码来解析json数据，但我无法过滤到不同的列，特别是过滤部分到单独的列中，比如内部过滤器我想要制作字段，比较器单独的列

    records=[]
    for data in response['result']:
        id = data['id']
        title = data['title']
        sharedWithOrganization = data['sharedWithOrganization']
        ownerId = data['ownerId'] 
        sharedWithUsers = '|'.join(data['sharedWithUsers'])
        filters = data['filters']
        print(filters)
        records.append([id,title,sharedWithOrganization,ownerId,sharedWithUsers])
        
        #print(records)
    
        
        
    ExcelApp = win32.Dispatch('Excel.Application')
    ExcelApp.Visible= True
    
    #creating excel and renaming sheet
    
    wb = ExcelApp.Workbooks.Add()
    ws= wb.Worksheets(1)
    ws.Name="Get_Views"
        
        
        
    #assigning header value
    header_labels=('Id','Title','SharedWithOrganization','OwnerId','sharedWithUsers')
    for index,val in enumerate(header_labels):
        ws.Cells(1, index+1).Value=val
        
        
        
    row_tracker = 2
    column_size = len(header_labels)
    
    for row in records:
        ws.Range(ws.cells(row_tracker,1),ws.cells(row_tracker,column_size)).value = row
        row_tracker +=1

在此处输入图像描述

我正在做 API pull 我得到这种格式，我将 .json 格式传递给 python 以实现数据到 excel 但我无法将列表数据过滤到单独的列中，你能帮我吗

标签： pythonpython-3.xexcelpandasdataframe

解决方案

尝试：

df = pd.Series(response).explode().apply(pd.Series).reset_index(drop=True)
df = df.join(df['filters'].explode().apply(pd.Series)).drop(columns=['filters'])
df['sharedWithUsers'] = df['sharedWithUsers'].str.join('|')

输出：

     id              title  sharedWithOrganization ownerId sharedWithUsers field comparator    value
0  1000  Fishing Team View                    True  324425     1223|w2qee3  tag5         ==  fishing
1  2000  Farming Team View                   False   00000     00000|11111  tag5         !@  farming

推荐阅读