Search code examples
pythonjsonlistdictionaryexport-to-csv

How to access the elements in a list of dict keys and values for writing to csv rows in Python

I have my code working for writing a single json dictionary keys and values to a csv file iterating over a flat dict, like so.

Json Input:

{"id": 1702, "subnet": "10.111.0.0", "mask": "21", "sectionId": 3, "description": "POD", "linked_subnet": null, "firewallAddressObject": null, "vrfId": null, "masterSubnetId": 1158, "allowRequests": 0, "vlanId": null, "showName": 0, "device": null, "permissions": "{\"4\":\"3\"}", "pingSubnet": 0, "discoverSubnet": 0, "resolveDNS": 0, "DNSrecursive": 0, "DNSrecords": 0, "nameserverId": 0, "scanAgent": 0, "customer_id": null, "isFolder": 0, "isFull": 0, "isPool": 0, "tag": 2, "threshold": 0, "location": null, "editDate": null, "lastScan": null, "lastDiscovery": null, "calculation": {"Type": "IPv4", "IP address": "/", "Network": "10.111.0.0", "Broadcast": "10.111.7.255", "Subnet bitmask": "21", "Subnet netmask": "255.255.248.0", "Subnet wildcard": "0.0.7.255", "Min host IP": "10.111.0.1", "Max host IP": "10.111.7.254", "Number of hosts": "2046", "Subnet Class": "private A"}}

My code for this is:

rawdata = json.loads(res.content)
json_data_dict = json.dumps(rawdata)
print(f"\nPRINTING JSON DICT DATA\n {json_data_dict}")

json_parse = json.loads(json_data_dict, object_pairs_hook=OrderedDict)

with open('ipamsubnet.csv', 'w') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow(json_parse["data"].keys())  # header row
    writer.writerow(json_parse["data"].values())

And the csv file row looks correct for this single dictionary data output from the json data structure above.

What I am trying to do is now access nested dict elements that are in a list of dicts because there are subnets under the aforementioned master subnet dictionary that need to be accounted for in the csv but the json data structure returned from the API call is no longer a dictionary but rather a list of dicts.

Here is an excerpt of the nested data structure:

List of dicts, Input 2:

"data": [{"id": 1713, "subnet": "10.111.0.0", "mask": "27", "sectionId": 3, "description": "POD_Site", "linked_subnet": null, "firewallAddressObject": null, "vrfId": null, "masterSubnetId": 1702, "allowRequests": 0, "vlanId": null, "showName": 0, "device": null, "permissions": "{\"4\":\"3\"}", "pingSubnet": 0, "discoverSubnet": 0, "resolveDNS": 0, "DNSrecursive": 0, "DNSrecords": 0, "nameserverId": 0, "scanAgent": 0, "customer_id": null, "isFolder": 0, "isFull": 0, "isPool": 0, "tag": 2, "threshold": 0, "location": null, "editDate": null, "lastScan": null, "lastDiscovery": null}, {"id": 1714, "subnet": "10.111.0.32", "mask": "27", "sectionId": 3, "description": "POD_Site", "linked_subnet": null, "firewallAddressObject": null, "vrfId": null, "masterSubnetId": 1702, "allowRequests": 0, "vlanId": null, "showName": 0, "device": null, "permissions": "{\"4\":\"3\"}", "pingSubnet": 0, "discoverSubnet": 0, "resolveDNS": 0, "DNSrecursive": 0, "DNSrecords": 0, "nameserverId": 0, "scanAgent": 0, "customer_id": null, "isFolder": 0, "isFull": 0, "isPool": 0, "tag": 2, "threshold": 0, "location": null, "editDate": null, "lastScan": null, "lastDiscovery": null}, {"id": 1715, "subnet": "10.111.0.64", "mask": "27", "sectionId": 3, "description": "POD_Site", "linked_subnet": null, "firewallAddressObject": null, "vrfId": null, "masterSubnetId": 1702, "allowRequests": 0, "vlanId": null, "showName": 0, "device": null, "permissions": "{\"4\":\"3\"}", "pingSubnet": 0, "discoverSubnet": 0, "resolveDNS": 0, "DNSrecursive": 0, "DNSrecords": 0, "nameserverId": 0, "scanAgent": 0, "customer_id": null, "isFolder": 0, "isFull": 0, "isPool": 0, "tag": 2, "threshold": 0, "location": null, "editDate": null, "lastScan": null, "lastDiscovery": null}]

Is data now a dict of list of dicts where data is the key and the value is a list of dicts, or just a list of dicts?

Objective is to have the csv contain the number of rows according to each id key which signifies a the start of another dictionary, and each dictionary is a list index element, at least that is how I understand it.

Is something like this headed in the right direction:

'Key': list(data)[0]
'Value': list(data.values()[0]

Any guidance is much appreciated.

Solution

  • You can use pandas :

    import pandas as pd

json_parse = {"data" : [
    {'id': 1713, 'subnet': '10.111.0.0', 'mask': '27'},
    {'id': 1714, 'subnet': '10.111.0.32', 'mask': '27'},
    {'id': 1715, 'subnet': '10.111.0.64', 'mask': '27'}
]}

# create DataFrame
df = pd.DataFrame(json_parse["data"])

# save csv file
df.to_csv('ipamsubnet.csv', index=False)