Python: Arrange and order data in a dictionary from Excel Pandas

Question

I am able to read specific columns from an Excel file using Pandas.

Excel file:

Code:

import os

import pandas as pd

excel_file = "File.xlsx"
data = pd.read_excel(os.path.join("./", excel_file), usecols="A:B,H:I")
df = pd.DataFrame(data)
dict_data = df.to_dict(orient="dict")

The dict_data looks like this:

{
        "VLAN ID": {0: 100, 1: 200, 2: 300},
        "VLAN Name": {0: "MGMT", 1: "Users", 2: "Phones"},
        "Gateway": {0: "10.0.0.1", 1: "172.16.0.1", 2: "192.168.1.1"},
        "Subnet Mask": {0: "255.0.0.0", 1: "255.255.0.0", 2: "255.255.255.0"}
}

I want to convert dict_data to look like the following:

{
    "vlans": {
        {
            "id": 100,
            "name": "MGMT",
            "ipaddr": "10.0.0.1",
            "mask": "255.0.0.0",
        },
        {
            "id": 200,
            "name": "Users",
            "ipaddr": "172.16.0.1",
            "mask": "255.255.0.0",
        },
        {
            "id": 300,
            "name": "Phones",
            "ipaddr": "192.168.1.1",
            "mask": "255.255.255.0",
        },
    }
}

Then, the vlans will be passed to a Jinja2 template to be created? How can I achieve this output?

---

The Jinja2 template

{% for vlan in vlans.items() %}
vlan {{ vlan["id"] }}
 name {{ vlan["name"] }}
exit
!
interface vlan {{ vlan["id"] }}
 ip address {{ vlan["ipaddr"] }} {{ vlan["mask"] }}
 description {{ vlan["name"] }}
exit
!
{% endfor %}

Output from Jinja

vlan 100
 name MGMT
exit
!
interface vlan 100
 ip address 10.0.0.1 255.0.0.0
 description MGMT
exit
!
vlan 200
 name Users
exit
!
interface vlan 200
 ip address 172.16.0.1 255.255.0.0
 description Users
exit
!
vlan 300
 name Phones
exit
!
interface vlan 300
 ip address 192.168.1.1 255.255.255.0
 description Phones
exit
!

Answer 1

Seems like this would be easier to do from the DataFrame than the data_dict:

rename the columns to be the new values, then call to_dict and orient='records'.

out = {
    'vlans': df.rename(
        columns={'VLAN ID': 'id',
                 'VLAN Name': 'name',
                 'Gateway': 'ipaddr',
                 'Subnet Mask': 'mask'}
    ).to_dict(orient='records')
}

out:

{'vlans': [{'id': 100,
            'ipaddr': '10.0.0.1',
            'mask': '255.0.0.0',
            'name': 'MGMT'},
           {'id': 200,
            'ipaddr': '172.16.0.1',
            'mask': '255.255.0.0',
            'name': 'Users'},
           {'id': 300,
            'ipaddr': '192.168.1.1',
            'mask': '255.255.255.0',
            'name': 'Phones'}]}