Created Nested Dictionary from a excel document

user_7242533 0
Viewed 26

I'm really struggling to do it. I have an excel file like that:

mgmt_ip         interface   mtu     enabled     instance         anycast_ip
192.168.0.217                   
0               vlan2       9050    TRUE        Vrf-Customer-1-Vlan 
0               vlan3       9050    TRUE        Vrf-Customer-2-Vlan 
0               vlan10      9000    TRUE        Vrf-Customer-1-Vlan     192.168.10.1/24
0               vlan20      9000    TRUE        Vrf-Customer-1-Vlan     192.168.20.1/24
0               vlan100     9000    TRUE        Vrf-Customer-2-Vlan     192.168.100.1/24
0               vlan200     9000    TRUE        Vrf-Customer-2-Vlan     192.168.200.1/24
192.168.0.218                       
0               vlan2       9050    TRUE        Vrf-Customer-1-Vlan 
0               vlan3       9050    TRUE        Vrf-Customer-2-Vlan 
0               vlan10      9000    TRUE        Vrf-Customer-1-Vlan     192.168.10.1/24
0               vlan20      9000    TRUE        Vrf-Customer-1-Vlan     192.168.20.1/24
0               vlan100     9000    TRUE        Vrf-Customer-2-Vlan     192.168.100.1/24
0               vlan200     9000    TRUE        Vrf-Customer-2-Vlan     192.168.200.1/24

And I need to generate a nested dictionary like this:

{
'192.168.0.217': {'interface': 
                     {'vlan': 'vlan2', 'mtu': '9000', 'enabled': 'TRUE', 'instance': 'Vrf-Customer-1-Vlan, 'anycast_ip': None},
                     {'vlan': 'vlan3', 'mtu': '9000', 'enabled': 'TRUE', 'instance': 'Vrf-Customer-1-Vlan, 'anycast_ip': None},
                     {'vlan': 'vlan10', 'mtu': '9000', 'enabled': 'TRUE', 'instance': 'Vrf-Customer-1-Vlan, 'anycast_ip': 192.168.10.1/24},
                     {'vlan': 'vlan20', 'mtu': '9000', 'enabled': 'TRUE', 'instance': 'Vrf-Customer-1-Vlan, 'anycast_ip': 192.168.20.1/24},
                     {'vlan': 'vlan100', 'mtu': '9000', 'enabled': 'TRUE', 'instance': 'Vrf-Customer-1-Vlan, 'anycast_ip': 192.168.100.1/24},
                     {'vlan': 'vlan200', 'mtu': '9000', 'enabled': 'TRUE', 'instance': 'Vrf-Customer-1-Vlan, 'anycast_ip': 192.168.200.1/24}

                 }, 
'192.168.0.217': {'interface': 
                     {'vlan': 'vlan2', 'mtu': '9000', 'enabled': 'TRUE', 'instance': 'Vrf-Customer-1-Vlan, 'anycast_ip': None},
                     {'vlan': 'vlan3', 'mtu': '9000', 'enabled': 'TRUE', 'instance': 'Vrf-Customer-1-Vlan, 'anycast_ip': None},
                     {'vlan': 'vlan10', 'mtu': '9000', 'enabled': 'TRUE', 'instance': 'Vrf-Customer-1-Vlan, 'anycast_ip': 192.168.10.1/24},
                     {'vlan': 'vlan20', 'mtu': '9000', 'enabled': 'TRUE', 'instance': 'Vrf-Customer-1-Vlan, 'anycast_ip': 192.168.20.1/24},
                     {'vlan': 'vlan100', 'mtu': '9000', 'enabled': 'TRUE', 'instance': 'Vrf-Customer-1-Vlan, 'anycast_ip': 192.168.100.1/24},
                     {'vlan': 'vlan200', 'mtu': '9000', 'enabled': 'TRUE', 'instance': 'Vrf-Customer-1-Vlan, 'anycast_ip': 192.168.200.1/24}

                 }
}

I tried it but it's not appending and I'm sure I'm not doing it in the most effective way.

def getVlanTab(file, sheet_name):

db = read_excel_data(file, sheet_name)

dc_dict = {}
for i in range(len(db["mgmt_ip"])):
    if db["mgmt_ip"][i] != 0:
        mgmt_ip = db["mgmt_ip"][i]
        dc_dict[mgmt_ip] = {}
    else:
        dc_dict[mgmt_ip]['interface'] = {}
        vlan = db["vlan"][i]

        dc_dict[mgmt_ip]['interface']['vlan'] = vlan

Thanks for any help here.

python-3.x excel python
1 Answers

Can you try the following:

import json
import numpy as np
import pandas as pd

df = pd.read_excel('path/to/your/excelfile')
df['mgmt_ip'] = df['mgmt_ip'].replace({'0': np.nan}).fillna(method='ffill')
df = df.replace({None: np.nan})
df = df.dropna(thresh=2)
df = df.fillna('None')

results = {}
for name, g in df.groupby(by=['mgmt_ip']):
    g = g.drop('mgmt_ip', axis=1)
    data = json.loads(g.to_json(orient='table'))['data']
    for d in data:
        __ = d.pop('index')
    results[name] = {
        'interface': data
    }
print(results)

Output:

{
    "192.168.0.217":{
        "interface":[
            {
                "vlan":"vlan2",
                "mtu":9050.0,
                "enabled":"TRUE",
                "instance":"Vrf-Customer-1-Vlan",
                "anycast_ip":"None"
            },
            {
                "vlan":"vlan3",
                "mtu":9050.0,
                "enabled":"TRUE",
                "instance":"Vrf-Customer-2-Vlan",
                "anycast_ip":"None"
            },
            {
                "vlan":"vlan10",
                "mtu":9000.0,
                "enabled":"TRUE",
                "instance":"Vrf-Customer-1-Vlan",
                "anycast_ip":"192.168.10.1/24"
            },
            {
                "vlan":"vlan20",
                "mtu":9000.0,
                "enabled":"TRUE",
                "instance":"Vrf-Customer-1-Vlan",
                "anycast_ip":"192.168.20.1/24"
            },
            {
                "vlan":"vlan100",
                "mtu":9000.0,
                "enabled":"TRUE",
                "instance":"Vrf-Customer-2-Vlan",
                "anycast_ip":"192.168.100.1/24"
            },
            {
                "vlan":"vlan200",
                "mtu":9000.0,
                "enabled":"TRUE",
                "instance":"Vrf-Customer-2-Vlan",
                "anycast_ip":"192.168.200.1/24"
            }
        ]
    },
    "192.168.0.218":{
        "interface":[
            {
                "vlan":"vlan2",
                "mtu":9050.0,
                "enabled":"TRUE",
                "instance":"Vrf-Customer-1-Vlan",
                "anycast_ip":"None"
            },
            {
                "vlan":"vlan3",
                "mtu":9050.0,
                "enabled":"TRUE",
                "instance":"Vrf-Customer-2-Vlan",
                "anycast_ip":"None"
            },
            {
                "vlan":"vlan10",
                "mtu":9000.0,
                "enabled":"TRUE",
                "instance":"Vrf-Customer-1-Vlan",
                "anycast_ip":"192.168.10.1/24"
            },
            {
                "vlan":"vlan20",
                "mtu":9000.0,
                "enabled":"TRUE",
                "instance":"Vrf-Customer-1-Vlan",
                "anycast_ip":"192.168.20.1/24"
            },
            {
                "vlan":"vlan100",
                "mtu":9000.0,
                "enabled":"TRUE",
                "instance":"Vrf-Customer-2-Vlan",
                "anycast_ip":"192.168.100.1/24"
            },
            {
                "vlan":"vlan200",
                "mtu":9000.0,
                "enabled":"TRUE",
                "instance":"Vrf-Customer-2-Vlan",
                "anycast_ip":"192.168.200.1/24"
            }
        ]
    }
}
user_28255700
Related