Python Forum
Convert nested sample json api data into csv in python
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Convert nested sample json api data into csv in python
#1
Want to convert Sample JSON data into CSV file using python. I am retrieving JSON data from API. As my JSON has nested objects, so it normally cannot be directly converted to CSV.I don't want to do any hard coding and I want to make a python code fully dynamic.

So, I have written a function that flatten my JSON Data but I am not able to work out how to iterate all records, finding relevant column names and then output those data into CSV.

In the Sample JSON file I have mentioned only 2 records but in actual there are 100 records. Sample JSON Look like this:

[
   {
      "id":"Random_Company_57",
      "unid":"75",
      "fieldsToValues":{
         "Email":"None",
         "occupation":"SO1 Change",
         "manager":"None",
         "First Name":"Bells",
         "employeeID":"21011.0",
         "loginRequired":"true",
         "superUser":"false",
         "ldapSuperUser":"false",
         "archived":"true",
         "password":"None",
         "externalUser":"false",
         "Username":"Random_Company_57",
         "affiliation":"",
         "Phone":"+16 22 22 222",
         "unidDominoKey":"",
         "externalUserActive":"false",
         "secondaryOccupation":"SO1 Change",
         "retypePassword":"None",
         "Last Name":"Christmas"
      },
      "hierarchyFieldAccess":[
         
      ],
      "userHierarchies":[
         {
            "hierarchyField":"Company",
            "value":"ABC Company"
         },
         {
            "hierarchyField":"Department",
            "value":"gfds"
         },
         {
            "hierarchyField":"Project",
            "value":"JKL-SDFGHJW"
         },
         {
            "hierarchyField":"Division",
            "value":"Silver RC"
         },
         {
            "hierarchyField":"Site",
            "value":"SQ06"
         }
      ],
      "locale":{
         "id":1,
         "dateFormat":"dd/MM/yyyy",
         "languageTag":"en-UA"
      },
      "roles":[
         "User"
      ],
      "readAccessRoles":[
         
      ],
      "preferredLanguage":"en-AU",
      "prefName":"Christmas Bells",
      "startDate":"None",
      "firstName":"Bells",
      "lastName":"Christmas",
      "fullName":"Christmas Bells",
      "lastModified":"2022-02-22T03:47:41.632Z",
      "email":"None",
      "docNo":"None",
      "virtualSuperUser":false
   },
   {
      "id":"[email protected]",
      "unid":"98",
      "fieldsToValues":{
         "Email":"[email protected]",
         "occupation":"SO1 Change",
         "manager":"None",
         "First Name":"Bells",
         "employeeID":"21011.0",
         "loginRequired":"false",
         "superUser":"false",
         "ldapSuperUser":"false",
         "archived":"false",
         "password":"None",
         "externalUser":"false",
         "Username":"[email protected]",
         "affiliation":"",
         "Phone":"+16 2222 222 222",
         "unidDominoKey":"",
         "externalUserActive":"false",
         "secondaryOccupation":"SO1 Change",
         "retypePassword":"None",
         "Last Name":"Christmas"
      },
      "hierarchyFieldAccess":[
         
      ],
      "userHierarchies":[
         {
            "hierarchyField":"Company",
            "value":"ABC Company"
         },
         {
            "hierarchyField":"Department",
            "value":"PUHJ"
         },
         {
            "hierarchyField":"Project",
            "value":"RPOJ-SDFGHJW"
         },
         {
            "hierarchyField":"Division",
            "value":"Silver RC"
         },
         {
            "hierarchyField":"Site",
            "value":"SQ06"
         }
      ],
      "locale":{
         "id":1,
         "dateFormat":"dd/MM/yyyy",
         "languageTag":"en-UA"
      },
      "roles":[
         "User"
      ],
      "readAccessRoles":[
         
      ],
      "preferredLanguage":"en-AU",
      "prefName":"Christmas Bells",
      "startDate":"None",
      "firstName":"Bells",
      "lastName":"Christmas",
      "fullName":"Christmas Bells",
      "lastModified":"2022-03-16T05:04:13.085Z",
      "email":"[email protected]",
      "docNo":"None",
      "virtualSuperUser":false
   }
]
What I have tried.

def flattenjson(b, delim):
    val = {}
    for i in b.keys():
        if isinstance(b[i], dict):
            get = flattenjson(b[i], delim)
            for j in get.keys():
                val[i + delim + j] = get[j]
        else:
            val[i] = b[i]
    print(val)        
    return val
    
json=[{Sample JSON String that mentioned above}]
flattenjson(json,"__")
I don't know it is a right way to deal this problem or not? My final aim is that to convert all the above json data into a csv file.
Any help?
Reply
#2
I am having a difficult time seeing how the information would be organized in a csv. There are no rows or columns. What do you expect the CSV file to look like?
Reply
#3
try:
import pandas as pd
import os

jsondata = '''
[
   {
      "id":"Random_Company_57",
      "unid":"75",
      "fieldsToValues":{
         "Email":"None",
         "occupation":"SO1 Change",
         "manager":"None",
         "First Name":"Bells",
         "employeeID":"21011.0",
         "loginRequired":"true",
         "superUser":"false",
         "ldapSuperUser":"false",
         "archived":"true",
         "password":"None",
         "externalUser":"false",
         "Username":"Random_Company_57",
         "affiliation":"",
         "Phone":"+16 22 22 222",
         "unidDominoKey":"",
         "externalUserActive":"false",
         "secondaryOccupation":"SO1 Change",
         "retypePassword":"None",
         "Last Name":"Christmas"
      },
      "hierarchyFieldAccess":[
          
      ],
      "userHierarchies":[
         {
            "hierarchyField":"Company",
            "value":"ABC Company"
         },
         {
            "hierarchyField":"Department",
            "value":"gfds"
         },
         {
            "hierarchyField":"Project",
            "value":"JKL-SDFGHJW"
         },
         {
            "hierarchyField":"Division",
            "value":"Silver RC"
         },
         {
            "hierarchyField":"Site",
            "value":"SQ06"
         }
      ],
      "locale":{
         "id":1,
         "dateFormat":"dd/MM/yyyy",
         "languageTag":"en-UA"
      },
      "roles":[
         "User"
      ],
      "readAccessRoles":[
          
      ],
      "preferredLanguage":"en-AU",
      "prefName":"Christmas Bells",
      "startDate":"None",
      "firstName":"Bells",
      "lastName":"Christmas",
      "fullName":"Christmas Bells",
      "lastModified":"2022-02-22T03:47:41.632Z",
      "email":"None",
      "docNo":"None",
      "virtualSuperUser":false
   },
   {
      "id":"[email protected]",
      "unid":"98",
      "fieldsToValues":{
         "Email":"[email protected]",
         "occupation":"SO1 Change",
         "manager":"None",
         "First Name":"Bells",
         "employeeID":"21011.0",
         "loginRequired":"false",
         "superUser":"false",
         "ldapSuperUser":"false",
         "archived":"false",
         "password":"None",
         "externalUser":"false",
         "Username":"[email protected]",
         "affiliation":"",
         "Phone":"+16 2222 222 222",
         "unidDominoKey":"",
         "externalUserActive":"false",
         "secondaryOccupation":"SO1 Change",
         "retypePassword":"None",
         "Last Name":"Christmas"
      },
      "hierarchyFieldAccess":[
          
      ],
      "userHierarchies":[
         {
            "hierarchyField":"Company",
            "value":"ABC Company"
         },
         {
            "hierarchyField":"Department",
            "value":"PUHJ"
         },
         {
            "hierarchyField":"Project",
            "value":"RPOJ-SDFGHJW"
         },
         {
            "hierarchyField":"Division",
            "value":"Silver RC"
         },
         {
            "hierarchyField":"Site",
            "value":"SQ06"
         }
      ],
      "locale":{
         "id":1,
         "dateFormat":"dd/MM/yyyy",
         "languageTag":"en-UA"
      },
      "roles":[
         "User"
      ],
      "readAccessRoles":[
          
      ],
      "preferredLanguage":"en-AU",
      "prefName":"Christmas Bells",
      "startDate":"None",
      "firstName":"Bells",
      "lastName":"Christmas",
      "fullName":"Christmas Bells",
      "lastModified":"2022-03-16T05:04:13.085Z",
      "email":"[email protected]",
      "docNo":"None",
      "virtualSuperUser":false
   }
]
'''

# set path to script directory --- change as necessary
os.chdir(os.path.abspath(os.path.dirname(__file__)))

df = pd.read_json(jsondata)
df.to_csv('MyData.csv')
Reply
#4
I tried doing that. While it does produce a csv file, it is not a nicely organized file.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  encrypt data in json file help jacksfrustration 1 54 6 hours ago
Last Post: deanhystad
  Convert File to Data URL michaelnicol 3 1,080 Jul-08-2023, 11:35 AM
Last Post: DeaD_EyE
  Python Script to convert Json to CSV file chvsnarayana 8 2,344 Apr-26-2023, 10:31 PM
Last Post: DeaD_EyE
  [split] Parse Nested JSON String in Python mmm07 4 1,414 Mar-28-2023, 06:07 PM
Last Post: snippsat
  Read nested data from JSON - Getting an error marlonbown 5 1,309 Nov-23-2022, 03:51 PM
Last Post: snippsat
  openpyxl convert data to float jacklee26 13 5,714 Nov-19-2022, 11:59 AM
Last Post: deanhystad
  Convert Json to table format python_student 2 5,067 Sep-28-2022, 12:48 PM
Last Post: python_student
  Reading Data from JSON tpolim008 2 1,031 Sep-27-2022, 06:34 PM
Last Post: Larz60+
  Python Split json into separate json based on node value CzarR 1 5,475 Jul-08-2022, 07:55 PM
Last Post: Larz60+
  Struggling with Juggling JSON Data SamWatt 7 1,820 May-09-2022, 02:49 AM
Last Post: snippsat

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020