Need help for converting nested python dictionary into Pandas dataframe

Question

Hello,

I need help converting data consisting of a nested dictionary into a Pandas data frame that I would like to export to excel.

{"devices": {"Device1": {"interfaces": {"mgmt0": {"oper_status": "up", "ipv4": {"10.216.10.72/24": {"ip": "10.216.10.72", "prefix_length": "24"}}}, "Ethernet1/33": {"oper_status": "up", "port_channel": {"port_channel_int": "Port-channel53"}, "counters": {"in_crc_errors": 0, "in_errors": 0, "in_discard": 0, "out_errors": 0, "out_discard": 0}, "switchport_mode": "trunk", "access_vlan": 1, "trunk_vlans": "10,2101"}, "port-channel53": {"oper_status": "up", "counters": {"in_crc_errors": 0, "in_errors": 0, "in_discard": 0, "out_errors": 0, "out_discard": 0}, "switchport_mode": "trunk", "access_vlan": 1, "trunk_vlans": "10,2101"}, "Vlan2101": {"oper_status": "up", "ipv4": {"192.168.10.1/24": {"ip": "192.168.10.1", "prefix_length": "24"}}}}}, "device2": {"interfaces": {"mgmt0": {"oper_status": "up", "ipv4": {"10.216.10.73/24": {"ip": "10.216.10.73", "prefix_length": "24"}}}, "Ethernet1/47": {"oper_status": "up", "port_channel": {"port_channel_int": "Port-channel100"}, "counters": {"in_crc_errors": 0, "in_errors": 0, "in_discard": 0, "out_errors": 0, "out_discard": 0}, "switchport_mode": "trunk", "access_vlan": 1, "trunk_vlans": "1-4094"}, "Ethernet1/48": {"oper_status": "up", "port_channel": {"port_channel_int": "Port-channel100"}, "counters": {"in_crc_errors": 0, "in_errors": 0, "in_discard": 0, "out_errors": 0, "out_discard": 0}, "switchport_mode": "trunk", "access_vlan": 1, "trunk_vlans": "1-4094"}, "Ethernet1/53": {"oper_status": "up", "counters": {"in_crc_errors": 0, "in_errors": 0, "in_discard": 0, "out_errors": 0, "out_discard": 0}, "switchport_mode": "trunk", "access_vlan": 1, "trunk_vlans": "1-4094"}, "port-channel100": {"oper_status": "up", "counters": {"in_crc_errors": 0, "in_errors": 0, "in_discard": 0, "out_errors": 0, "out_discard": 0}, "switchport_mode": "trunk", "access_vlan": 1, "trunk_vlans": "1-4094"}}}, "Device3": {"interfaces": {"mgmt0": {"oper_status": "up", "ipv4": {"10.216.10.74/24": {"ip": "10.216.10.74", "prefix_length": "24"}}}, "Ethernet1/47": {"oper_status": "up", "port_channel": {"port_channel_int": "Port-channel100"}, "counters": {"in_crc_errors": 0, "in_errors": 0, "in_discard": 0, "out_errors": 0, "out_discard": 0}, "switchport_mode": "trunk", "access_vlan": 1, "trunk_vlans": "1-4094"}, "Ethernet1/48": {"oper_status": "up", "port_channel": {"port_channel_int": "Port-channel100"}, "counters": {"in_crc_errors": 0, "in_errors": 0, "in_discard": 0, "out_errors": 0, "out_discard": 0}, "switchport_mode": "trunk", "access_vlan": 1, "trunk_vlans": "1-4094"}, "Ethernet1/53": {"oper_status": "up", "counters": {"in_crc_errors": 0, "in_errors": 0, "in_discard": 0, "out_errors": 0, "out_discard": 0}, "switchport_mode": "trunk", "access_vlan": 1, "trunk_vlans": "10"}, "port-channel100": {"oper_status": "up", "counters": {"in_crc_errors": 0, "in_errors": 0, "in_discard": 0, "out_errors": 0, "out_discard": 0}, "switchport_mode": "trunk", "access_vlan": 1, "trunk_vlans": "1-4094"}}}, "Device4": {"interfaces": {"mgmt0": {"oper_status": "up", "ipv4": {"10.216.10.75/24": {"ip": "10.216.10.75", "prefix_length": "24"}}}, "Ethernet1/1": {"oper_status": "up", "counters": {"in_crc_errors": 0, "in_discard": 0, "out_discard": 0}, "switchport_mode": "trunk", "access_vlan": 1, "trunk_vlans": "1-4094"}, "Ethernet1/15": {"oper_status": "up", "counters": {"in_crc_errors": 0, "in_errors": 0, "in_discard": 0, "out_errors": 0, "out_discard": 0}, "switchport_mode": "trunk", "access_vlan": 20, "trunk_vlans": "1-4094"}, "Ethernet1/24": {"oper_status": "up", "counters": {"in_crc_errors": 0, "in_errors": 0, "in_discard": 0, "out_discard": 0}, "switchport_mode": "trunk", "access_vlan": 1, "trunk_vlans": "1-4094"}, "Ethernet1/47": {"oper_status": "up", "counters": {"in_crc_errors": 0, "in_discard": 0, "out_discard": 0}, "switchport_mode": "trunk", "access_vlan": 1, "trunk_vlans": "1-4094"}, "Ethernet1/53": {"oper_status": "up", "counters": {"in_crc_errors": 0, "in_errors": 0, "in_discard": 0, "out_discard": 0}, "switchport_mode": "trunk", "access_vlan": 1, "trunk_vlans": "1-4094"}, "Vlan5": {"oper_status": "up", "ipv4": {"10.216.43.11/24": {"ip": "10.216.43.11", "prefix_length": "24"}}}, "Vlan10": {"oper_status": "up", "ipv4": {"10.216.42.1/24": {"ip": "10.216.42.1", "prefix_length": "24"}}}, "Vlan20": {"oper_status": "up", "ipv4": {"10.216.20.1/24": {"ip": "10.216.20.1", "prefix_length": "24"}}}, "Vlan100": {"oper_status": "up", "ipv4": {"10.249.128.2/30": {"ip": "10.249.128.2", "prefix_length": "30"}}}, "Vlan101": {"oper_status": "up", "ipv4": {"10.249.128.6/30": {"ip": "10.249.128.6", "prefix_length": "30"}}}, "Vlan102": {"oper_status": "up", "ipv4": {"10.249.128.10/30": {"ip": "10.249.128.10", "prefix_length": "30"}}}, "Vlan103": {"oper_status": "up", "ipv4": {"10.249.128.14/30": {"ip": "10.249.128.14", "prefix_length": "30"}}}}}, "Device5": {"interfaces": {"mgmt0": {"oper_status": "up", "ipv4": {"10.216.10.76/24": {"ip": "10.216.10.76", "prefix_length": "24"}}}}}}}

The output into excel should be similar to this:

https://paste.pics/G68D2

Thank you for your assitance

Answer 1

So what you have here would more accurately be defined as a JSON object.

If you want to convert this to a Pandas dataframe, you have to decide how you would want the final dataframe to look.

An excel spreadsheet is a 2 dimensional grid, but this JSON object has many nested levels. Judging by your data, it looks like you have a dictionary where the top level keys are Device Names and the corresponding value for each device name key is a dictionary containing information about that device.

Inside each of these device dictionaries is a dictionary where the keys are Interface Names and the values are dictionaries with information about that interface.

The easiest way to convert this JSON object to a pandas dataframe would be to first convert it to a list of dictionaries, where each dictionary represents a row in the final pandas dataframe. Each dictionary will have a key for each column, and a value for that cell in that column.

The tricky part here is that each interface and each device doesn't always have the same keys inside. So you need to always check to see if the key you are looking for exists and if it doesn't, you fill in a default value.

I don't usually do this but here is the code I wrote to process the data you provided:

devices_list = []

EXPECTED_INTERFACE_KEYS = ['switchport_mode', 'access_vlan', 'trunk_vlans']

EXPECTED_COUNTERS_KEYS = ['in_crc_errors', 'in_errors', 'in_discard', 'out_errors', 'out_discard']

for device_name, device_data in DEVICE_DICT['devices'].items():

for interface_name, interface_data in device_data['interfaces'].items():

new_device_dict = {}

new_device_dict['Device'] = device_name

new_device_dict['Interface'] = interface_name

for interface_key in EXPECTED_INTERFACE_KEYS:

if interface_key in interface_data.keys():

new_device_dict[interface_key] = interface_data[interface_key]

else:

new_device_dict[interface_key] = ""

if 'ipv4' in interface_data:

new_device_dict['ip'] = list(interface_data['ipv4'].values())[0]['ip']

new_device_dict['prefix'] = list(interface_data['ipv4'].values())[0]['prefix_length']

else:

new_device_dict['ip'] = ""

new_device_dict['prefix'] = ""

if 'port_channel' in interface_data:

new_device_dict['port_channel'] = interface_data['port_channel']['port_channel_int']

else:

new_device_dict['port_channel'] = ""

for expected_key in EXPECTED_COUNTERS_KEYS:

if 'counters' in interface_data and expected_key in interface_data['counters']:

new_device_dict[expected_key] = interface_data['counters'][expected_key]

else:

new_device_dict[expected_key] = ""

devices_list.append(new_device_dict)

df = pd.DataFrame(devices_list)

df.to_csv("output.csv")