0

I have a JSON body that I need to convert to CSV. I then need a perfect conversion back from the CSV to output the same JSON. So first JSON to CSV and then CSV to JSON.

Here is the JSON:

{
    "quoteId": "",
    "carrier": "",
    "productType": "",
    "effectiveDate": "",
    "referenceId": "",
    "agencyLocationCode": null,
    "locationId": null,
    "property": {
        "id": "",
        "address": {
            "propertyStreetNumber": "",
            "propertyStreetName": "",
            "propertyAddressLine2": null,
            "propertyCity": "",
            "propertyState": "",
            "propertyZipCode": ""
        },
        "details": null
    }
}

The ouput that I get running the following code is:

ouput csv: enter image description here

Code:

with open('resources/json/file.json') as data_file:    
data = json.load(data_file)
df = pd.json_normalize(data)
df.to_csv("file.csv", index=False, encoding="utf-8")

I now want that CSV to be translated back into the exact same JSON, currently I run this code:

df = pd.read_csv('file.csv')
df.to_json('new.json')

The output JSON I'm getting looks like this:

{
    "quoteId": {
        "0": null
    },
    "carrier": {
        "0": ""
    },
    "productType": {
        "0": ""
    },
    "effectiveDate": {
        "0": ""
    },
    "referenceId": {
        "0": 62646354
    },
    "agencyLocationCode": {
        "0": null
    },
    "locationId": {
        "0": null
    },
    "losses": {
        "0": null
    },
    "property.id": {
        "0": null
    },
    "property.address.propertyStreetNumber": {
        "0": ""
    },
    "property.address.propertyStreetName": {
        "0": ""
    },
    "property.address.propertyAddressLine2": {
        "0": null
    },
    "property.address.propertyCity": {
        "0": ""
    },
    "property.address.propertyState": {
        "0": ""
    },
    "property.address.propertyZipCode": {
        "0": ""
    },
    "property.details": ""
}

I need the ouput json to look like the first one. Please help!

Bruno Marques
  • 89
  • 1
  • 6
  • Does this answer your question? [Inverse of Pandas json\_normalize](https://stackoverflow.com/questions/54776916/inverse-of-pandas-json-normalize) – Michael Ruth Apr 23 '21 at 20:05
  • using `df.to_json('new.json', orient="records")` will get you closer, but I don't know a simple parameter to add to re-nest the results. I would likely rebuild the json from the csv without pandas. Is the use of pandas required? – JonSG Apr 23 '21 at 20:44

0 Answers0