Json to excel using python-CodePudding

Json code:Below we have json data format which I am pulling from site using API

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 Code : I am using below code to parse the json data but I am unable to filters into different column specially filters part into separate column like Inside filter i a want to make field , comparator separate column

    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

I am doing API pull i am getting this kind of format and I am passing that .json format to python to achieve the data into excel but I am unable to filters list data into separate column can you please help me with

CodePudding user response：

Try:

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('|')

Output:

     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

CodePudding user response：

You can use pd.json_normalize to normalize the Json: Specify different parameters record_path= to read up to different depths for fields under tag result and tag filters respectively.

Then, join the 2 resultant dataframes together, as follows:

# read fields under tag `result`
df_result = pd.json_normalize(response, record_path=['result'])

# read fields under tag `filters` within `result`
df_filters = pd.json_normalize(response, record_path=['result', 'filters'])

# Join 2 resultant dataframes together
df = df_result.join(df_filters).drop('filters', axis=1)

# Join fields in `sharedWithUsers`
df['sharedWithUsers'] = df['sharedWithUsers'].str.join('|')

Result:

print(df)

     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

CodePudding user response：

You can see how I did it below because that returns a list you can select the first item of the list with [0], like datafield["filters"][0]["field"], also you can create a csv file and import that in excel instead.

import json
import csv

rows = []
headers = ["Id", "Title", "SharedWithOrganization", "OwnerId","SharedWithUsers","field", "comparator", "value"]

for datafield in response["result"] :
    susers = ""
    for u in datafield["sharedWithUsers"] :
        susers = susers   u   "|"
    susers = susers[:-1]
    if datafield["sharedWithOrganization"] :
        boolval = "TRUE"
    else :
        boolval = "FALSE"
    rows.append([datafield["id"], datafield["title"], boolval,     datafield["ownerId"], susers, datafield["filters"][0]["field"], datafield["filters"][0]["comparator"], datafield["filters"][0]["value"]])


with open('responseOutput.csv', 'w', encoding='UTF8', newline='') as f:
    writer = csv.writer(f)

    # write the header
    writer.writerow(headers)

    # write multiple rows
    writer.writerows(rows)

CodePudding user response：

The code below generates out.csv which can be opened by Excel.
Note that the code does not require any external library.

import csv

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"
            }
          ]
        }
      ]
    }
fields = ['id','title','sharedWithOrganization','ownerId','sharedWithUsers']
data = []
for entry in response['result']:
  data.append(['|'.join(entry[f]) if isinstance(entry[f],list) else entry[f] for f in fields])
with open('out.csv','w') as f:
  writer = csv.writer(f)
  writer.writerow(fields)
  for line in data:
    writer.writerow(line)

out.csv

id,title,sharedWithOrganization,ownerId,sharedWithUsers
1000,Fishing Team View,True,324425,1223|w2qee3
2000,Farming Team View,False,00000,00000|11111

CodePudding user response：

Using DictWriter you can write required columns into .csv file which you can open in Excel.

Code:

from csv import DictWriter

response = { ... }
with open("result.csv", "w", newline="") as f:
    writer = DictWriter(
        f,
        ("id", "title", "sharedWithOrganization", "ownerId", "sharedWithUsers"),
        extrasaction="ignore"
    )
    writer.writeheader()
    for obj in response["result"]:
        writer.writerow({**obj, "sharedWithUsers": "|".join(obj["sharedWithUsers"])})

Or you can use csv.writer (will consume a bit less memory as doesn't copy all fields):

import csv
from operator import itemgetter

response = { ... }

keys = "id", "title", "sharedWithOrganization", "ownerId", "sharedWithUsers"
getter = itemgetter(*keys)
with open("result.csv", "w", newline="") as f:
    writer = csv.writer(f)
    writer.writerow(keys)
    for obj in response["result"]:
        row = getter(obj)
        writer.writerow(row[:-1]   ("|".join(row[-1]),))