Python list of nested dictionaries to CSV File

Question

I have a list of dictionaries that have other dictionaries on them.
Dictionary:

[[{'id': 1, 'networkId': 'L_1111', 'name': 'VLAN1', 'applianceIp': '1.1.1.1', 'subnet': '1.1.1.0/24', 'fixedIpAssignments': {}, 'reservedIpRanges': [], 'dnsNameservers': 'upstream_dns', 'dhcpHandling': 'Run a DHCP server', 'dhcpLeaseTime': '1 day', 'dhcpBootOptionsEnabled': False, 'dhcpOptions': [], 'interfaceId': '1', 'networkName': 'NETWORK1'}, {'id': 2, 'networkId': 'L_2222', 'name': 'VLAN2', 'applianceIp': '2.2.2.2', 'subnet': '2.2.2.0/24', 'fixedIpAssignments': {}, 'reservedIpRanges': [], 'dnsNameservers': 'upstream_dns', 'dhcpHandling': 'Do not respond to DHCP requests', 'interfaceId': '2', 'networkName': 'NETWORK2'}]]

JSON version:

[
   [
      {
         "id": 1,
         "networkId": "L_1111",
         "name": "VLAN1",
         "applianceIp": "1.1.1.1",
         "subnet": "1.1.1.0/24",
         "fixedIpAssignments": {},
         "reservedIpRanges": [],
         "dnsNameservers": "upstream_dns",
         "dhcpHandling": "Run a DHCP server",
         "dhcpLeaseTime": "1 day",
         "dhcpBootOptionsEnabled": false,
         "dhcpOptions": [],
         "interfaceId": "1",
         "networkName": "NETWORK1"
      },
      {
         "id": 2,
         "networkId": "L_2222",
         "name": "VLAN2",
         "applianceIp": "2.2.2.2",
         "subnet": "2.2.2.0/24",
         "fixedIpAssignments": {},
         "reservedIpRanges": [],
         "dnsNameservers": "upstream_dns",
         "dhcpHandling": "Do not respond to DHCP requests",
         "interfaceId": "2",
         "networkName": "NETWORK2"
      },
   ]
]

I am trying to move this to a CSV file. However, I haven't figured out how to do it. I tried with pandas library but it isn't giving me the output that I am looking for.
Something like this:

id,networkId,name,applianceIp,subnet,fixedIpAssignments,reservedIpRanges,dnsNameservers,dhcpHandling,interfaceId,networkName
1,L_1111,VLAN1,1.1.1.1,1.1.1.0/24,{},[],upstream_dns,Do not respond to DHCP requests,1,NETWORK1
2,L_2222,VLAN2,2.2.2.2,2.2.2.0/24,{},[],upstream_dns,Do not respond to DHCP requests,2,NETWORK2

Expected Output:

id networkId name     applianceIP subnet 
1  L_1111    VLAN1    1.1.1.1     1.1.1.0/24
2  L_2222    VLAN2    2.2.2.2     2.2.2.0/24

could you give us the code you have come up with to get the output you currently have ? — Tirterra, Jun 22 '22 at 10:43

score 0 · Accepted Answer · answered Jun 22 '22 at 10:47

0

I'd look at using pandas to convert the list to a dataframe and then you'll be able to export that to a csv file.

import pandas as pd

data = [[{'id': 1, 'networkId': 'L_1111', 'name': '1', 'applianceIp': '1.1.1.1', 'subnet': '1.1.1.0/24', 'fixedIpAssignments': {}, 'reservedIpRanges': [], 'dnsNameservers': 'upstream_dns', 'dhcpHandling': 'Run a DHCP server', 'dhcpLeaseTime': '1 day', 'dhcpBootOptionsEnabled': False, 'dhcpOptions': [], 'interfaceId': '1', 'networkName': '1'}, {'id': 2, 'networkId': 'L_2222', 'name': '2', 'applianceIp': '2.2.2.2', 'subnet': '2.2.2.0/24', 'fixedIpAssignments': {}, 'reservedIpRanges': [], 'dnsNameservers': 'upstream_dns', 'dhcpHandling': 'Do not respond to DHCP requests', 'interfaceId': '2', 'networkName': '2'}]]

df = pd.DataFrame(data[0])
df.to_csv("output.csv")

answered Jun 22 '22 at 10:47

Jack Sim

26
3

That works great to go over the first item in the list, however I have a list of N items that each item has N dicts on it – Walter Joel Solano Jun 22 '22 at 11:13
You could flatten the list first: https://stackoverflow.com/questions/952914/how-do-i-make-a-flat-list-out-of-a-list-of-lists – Jan Jaap Meijerink Jun 22 '22 at 12:02
Thank you @JanJaapMeijerink! once I got the list flatted pandas workds – Walter Joel Solano Jun 22 '22 at 14:43

score 0 · Answer 2 · answered Jun 22 '22 at 11:52

I used the csv module.

import json
import csv
import os

PATH = os.path.dirname(__file__)    # Get the path of the used directory

with open(PATH+r"\input.json", "r") as file:    # Access the data    
    json_data = json.load(file)
    json_data = [item for item in json_data[0]]

with open(PATH+r"\output.csv", "w+", newline='') as file:
    writer = csv.writer(file)
    headers = [list(data.keys()) for data in json_data]     # Divide the data in
    rows = [list(data.values()) for data in json_data]    # headers and rows
    for i in range(len(json_data)):
        writer.writerow(headers[i])    # Write everything
        writer.writerow(rows[i])

If you don't want to have headers just remove this line writer.writerow(headers[i])

Here is the data I get as output:

id,networkId,name,applianceIp,subnet,fixedIpAssignments,reservedIpRanges,dnsNameservers,dhcpHandling,dhcpLeaseTime,dhcpBootOptionsEnabled,dhcpOptions,interfaceId,networkName
1,L_1111,VLAN1,1.1.1.1,1.1.1.0/24,{},[],upstream_dns,Run a DHCP server,1 day,False,[],1,NETWORK1
id,networkId,name,applianceIp,subnet,fixedIpAssignments,reservedIpRanges,dnsNameservers,dhcpHandling,interfaceId,networkName
2,L_2222,VLAN2,2.2.2.2,2.2.2.0/24,{},[],upstream_dns,Do not respond to DHCP requests,2,NETWORK2

score 0 · Answer 3 · answered Jun 22 '22 at 12:09

0

If you use pandas dataframe, you can easily write csv file. Save each column of DataFrame to seperated column in csv file.

df.to_csv(r'myData.csv',sep=';',encoding="utf-8")

answered Jun 22 '22 at 12:09

Mr.F.K

21
1
3

Python list of nested dictionaries to CSV File

3 Answers3