Json to excel using python

Question

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

Muhammad Hassan · Accepted Answer · 2021-10-09 13:18:14Z

2

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

answered Oct 9, 2021 at 13:18

Muhammad Hassan

4,2492 gold badges16 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Olvin Roght · Accepted Answer · 2021-10-09 14:55:19Z

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]),))

balderman · Accepted Answer · 2021-10-09 14:24:49Z

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

SeaBean · Accepted Answer · 2021-10-09 13:49:42Z

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

Atul Raj · Accepted Answer · 2021-10-09 14:24:19Z

0

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)

edited Oct 9, 2021 at 14:24

answered Oct 9, 2021 at 13:44

Atul Raj

513 bronze badges

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Json to excel using python

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related