Retrieve a specific value from a JSON data and generate a CSV file

Question

I am retrieving this data from api and it comes in JSON format. I only need the certain portion of the data and ignore all other data. Please check my Output csv how the final csv look like this. I need the result key, in that result value I need id and unid and userHierarchies field.

{
    "apiVersion": "3.0",
    "loggedInUser": {
        "id": "[email protected]",
        "unid": "192",
        "userHierarchies": [
            {
                "hierarchyField": "Project",
                "value": "Eli-f"
            },
            {
                "hierarchyField": "Division",
                "value": "DDD"
            },
            {
                "hierarchyField": "Site",
                "value": "RD02"
            },
            {
                "hierarchyField": "Company",
                "value": "Core"
            },
            {
                "hierarchyField": "Department",
                "value": "Operations"
            }
        ]
     
    },
        "results":[
       {
          "id":"Random_Company_57",
          "unid":"75",
          "userHierarchies":[
             {
                "hierarchyField":"Company",
                "value":"ABC Company"
             },
             {
                "hierarchyField":"Department",
                "value":"gfds"
             },
             {
                "hierarchyField":"Project",
                "value":"JKL-SDFGHJW"
             },
             {
                "hierarchyField":"Division",
                "value":"Silver RC"
             },
             {
                "hierarchyField":"Site",
                "value":"SQ06"
             }
          ],
          "preferredLanguage":"en-AU",
          "prefName":"Christmas Bells",
          
       },
       {
          "id":"[email protected]",
          "unid":"98",
          "userHierarchies":[
             {
                "hierarchyField":"Company",
                "value":"ABC Company"
             },
             {
                "hierarchyField":"Department",
                "value":"PUHJ"
             },
             {
                "hierarchyField":"Project",
                "value":"RPOJ-SDFGHJW"
             },
             {
                "hierarchyField":"Division",
                "value":"Silver RC"
             },
             {
                "hierarchyField":"Site",
                "value":"SQ06"
             }
          ],
          "preferredLanguage":"en-AU",
          "prefName":"Christmas Bells",
          
       }
    ]
}

My Output CSV look like this:

id,unid,hierarchyField,value
Random_Company_57,75,Company,ABC Company
Random_Company_57,75,Department,gfds
Random_Company_57,75,Project,JKL-SDFGHJW
Random_Company_57,75,Division,Silver RC
Random_Company_57,75,Site,SQ06
[email protected],98,Company,ABC Company
[email protected],98,Department,PUHJ
[email protected],98,Project,RPOJ-SDFGHJW
[email protected],98,Division,Silver RC

My python Code look like this:

import requests
from pathlib import Path
from pprint import pprint
import pandas as pd
import time
import os
import argparse

parser = argparse.ArgumentParser(description="Process some integers.")
parser.add_argument("-path_save", help="define where to save the file")
parser.add_argument("--verbose", help="display processing information")

start = time.time()

def GetData(URL, endPoint, path_save, verbose):
    response = requests.get(URL, auth=('[email protected]', 'uojk00'),
                            headers={
        'Content-Type': 'application/json',
        'x-api-key': 'ydVtsni1blwJHb65OJBrrtV',
    })
    print(endPoint,response)
    df = pd.DataFrame(response.json()["results"])
    print(df)
    df.to_csv(os.path.join(path_save,f"{endPoint}.csv"), index=False)


if __name__ == '__main__':
    start = time.time()
    args = parser.parse_args()
    path_save = Path(args.path_save)
    verbose = args.verbose
    endPoint=['users']   
    for endPt in endPoint:
        URL = "https://api.com/v10/chor/" + endPt
        GetData(URL, endPt, path_save, verbose)
    print("Processed time:", time.time() - start)  # Total Time

Any help how I generate that CSV???

Pandas can trivially write a CSV file, and you are already doing that; what exactly do you need help with? — tripleee
– tripleee, Commented Jun 15, 2022 at 5:07
i just need to generate a csv file that I mentined in the question. The csv file contains only four columns id,unid,hierarchyField,value. If you check json data closely you will all those four fields in results(Key). I want to ignore all other data. Please check result(key) closely. — ghj
– ghj, Commented Jun 15, 2022 at 5:17
Please edit your question to clarify which part of that you are not able to do without help. See also the guidance for providing a minimal reproducible example. — tripleee
– tripleee, Commented Jun 15, 2022 at 5:20
I have changed the json structure. I have put only that json data that is relevant to us. — ghj
– ghj, Commented Jun 15, 2022 at 5:41

Andrej Kesely · Accepted Answer · 2022-06-15 09:20:07Z

1

If data is your data from api you have in your question, you can use next example how to save it to CSV in required format:

df = pd.DataFrame(data["results"]).explode("userHierarchies")
df = pd.concat([df, df.pop("userHierarchies").apply(pd.Series)], axis=1)
df = df[["id", "unid", "hierarchyField", "value"]]

df.to_csv("data.csv", index=False)

Saves data.csv:

id,unid,hierarchyField,value
Random_Company_57,75,Company,ABC Company
Random_Company_57,75,Department,gfds
Random_Company_57,75,Project,JKL-SDFGHJW
Random_Company_57,75,Division,Silver RC
Random_Company_57,75,Site,SQ06
[email protected],98,Company,ABC Company
[email protected],98,Department,PUHJ
[email protected],98,Project,RPOJ-SDFGHJW
[email protected],98,Division,Silver RC
[email protected],98,Site,SQ06

answered Jun 15, 2022 at 9:20

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

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

Collectives™ on Stack Overflow

Retrieve a specific value from a JSON data and generate a CSV file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related