Convert JSON data from pandas to a specific JSON schema/format in python

Question

I have some JSON data in python that looks like this:

>>> print name_frame
... 
               name    name1    name2    name3     name4
Micro inc.      NaN    Jim D  Susan A      NaN       NaN
Vitacore    Billy B      NaN  Sally Q   Mark G       NaN
>>> payload = name_frame.apply(lambda x: [x.dropna()], axis=1).to_json(force_ascii=False)
... 
>>> print payload
... 




   {
"Micro inc.":[{"name1":"Jim D","name2":"Susan A"}],
    "Vitacore":[{"name":"Billy B","name2":"Sally Q","name3":"Mark G"}],

}

And I need it to look like this:

finalJSON = { 
    "company":{
        "name": "Micro inc.",
        "founders": {
            "name": "Jim D",
            "name": "Susan A",
            }
    }
    "company":{
        "name": "Vitacore",
        "founders": {
            "name": "Billy B",
            "name": "Sall Q", 
            "name":"Mark G",
        }

Does anyone know of any tools, libraries, or general advice on how I can get this done? I need to send each company object as a POST request to an API and it requires this format. From there I need to append the results to a pandas DataFrame. Which I believe should involve looping through the JSON data, submitting each company the API, taking the result and adding it to a dict or if possible directly to a Pandas DataFrame

payload= '''a single company from finalJSON'''

#p is a POST Request
p = requests.post((url + '/r'), json=payload, headers=headers)
p.text #<---- gotta go to a Pandas DataFrame

Thank you in advance for any help or advice

Are you sure that's a valid json structure with duplicate keys? — Zero, Jul 13 '17 at 19:04
Strange, but valid. https://stackoverflow.com/questions/21832701/does-json-syntax-allow-duplicate-keys-in-an-object — Alexander, Jul 13 '17 at 19:23

Alexander · Accepted Answer · 2017-07-13T19:44:49.287

1

finalJSON = []
for company, names in df.iterrows():
    names = ['"{0}"'.format(name) for name in names.dropna().tolist()]
    names_json_str = ('"name": ' if names else '') + ', "name": '.join(names)
    finalJSON.append('"company": {"name": "' + company + '", "founders": {' + names_json_str + '}')
finalJSON = ', '.join(finalJSON)

>>> finalJSON
'"company": {"name": "Micro inc.", "founders": {"name": "Jim D", "name": "Susan A"}, 
 "company": {"name": "Vitacore", "founders": {"name": "Billy B", "name": "Sally Q", "name": "Mark G"}'

edited Jul 13 '17 at 19:44

answered Jul 13 '17 at 19:16

Alexander

105,104
32
201
196

How would I store the Output as a POST Request or as individual objects? – cgclip Jul 13 '17 at 19:27
nevermind, I see that I should probably just make the POST requests in the same loop – cgclip Jul 13 '17 at 19:36

Convert JSON data from pandas to a specific JSON schema/format in python

I have some JSON data in python that looks like this:

And I need it to look like this:

1 Answers1

Linked