Create Panda DataFrame using nested dictionaries and a list: dict:{dict:{dict:[list]}}

I have a series of nested dicts with a list as the deepest value.

data = {
   "etherA": {
      "vlanY": {
         "local": ['mac01', 'mac02'],
         "external": ['mac03', 'mac02']
      }
   },
   "etherB": {
      "vlanZ": {
         "local": ['mac06', 'mac09'],
         "external": ['mac01', 'mac02', 'mac03']
      }
   }
} 

To load the dict into a dataframe, I create the column headers and then loop through the dict and add a list to the end of the dataframe.

df = pd.DataFrame.from_dict({
   'interface': [],
   'vlan': [],
   'dyn': [],
   'mac-address': []
})

for a in data:
   for b in data[a]:
      for c in data[a][b]:
         for d in data[a][b][c]:
            df.loc[len(df)] = [a, b, c, d]

Final output:

print(df)

  interface   vlan       dyn mac-address
0    etherA  vlanY     local       mac01
1    etherA  vlanY     local       mac02
2    etherA  vlanY  external       mac03
3    etherA  vlanY  external       mac02
4    etherB  vlanZ     local       mac06
5    etherB  vlanZ     local       mac09
6    etherB  vlanZ  external       mac01
7    etherB  vlanZ  external       mac02
8    etherB  vlanZ  external       mac03

The "for loops" ultimately do what I need it to, but is there a panda method for getting the data from the dict into the dataframe?

I've read through numerous other posts and have tried their answers and suggestions. Most are dealing with a single nested dictionary and none have dealt with a nested, nested, nested list. A few of the suggested questions are what I was trying to achieve and the answer was to loop through to essentially flatten the data before appending it to the dataframe,so that may be the best course.

Another way to do this is:

import pandas as pd

data = {
   "etherA": {
      "vlanY": {
         "local": ['mac01', 'mac02'],
         "external": ['mac03', 'mac02']
      }
   },
   "etherB": {
      "vlanZ": {
         "local": ['mac06', 'mac09'],
         "external": ['mac01', 'mac02', 'mac03']
      }
   }
}

df = pd.DataFrame([
    {'interface': interface, 'vlan': vlan, 'dyn': dyn, 'mac-address': mac}
    for interface, vlan_dict in data.items()
    for vlan, dyn_dict in vlan_dict.items()
    for dyn, mac_list in dyn_dict.items()
    for mac in mac_list
])

which gives

 interface   vlan       dyn mac-address
0    etherA  vlanY     local       mac01
1    etherA  vlanY     local       mac02
2    etherA  vlanY  external       mac03
3    etherA  vlanY  external       mac02
4    etherB  vlanZ     local       mac06
5    etherB  vlanZ     local       mac09
6    etherB  vlanZ  external       mac01
7    etherB  vlanZ  external       mac02
8    etherB  vlanZ  external       mac03
​

I suggest create list of tuples first:

L = [(a,b,c,d) for a in data 
               for b in data[a] 
               for c in data[a][b] 
               for d in data[a][b][c]]
df = pd.DataFrame(L, columns=['interface','vlan','dyn','mac-address'])

Or:

L = [(a,b,c,d) for a, d in data.items()
               for b, d1 in d.items()
               for c, d2 in d1.items()
               for d in d2]
df = pd.DataFrame(L, columns=['interface','vlan','dyn','mac-address'])

print (df)

  interface   vlan       dyn mac-address
0    etherA  vlanY     local       mac01
1    etherA  vlanY     local       mac02
2    etherA  vlanY  external       mac03
3    etherA  vlanY  external       mac02
4    etherB  vlanZ     local       mac06
5    etherB  vlanZ     local       mac09
6    etherB  vlanZ  external       mac01
7    etherB  vlanZ  external       mac02
8    etherB  vlanZ  external       mac03

Firstly, you can flatten the nested dictionary using pd.json_normalize, then, you can build a list of lists and turn it into a DataFrame.

import pandas as pd

data = {
    "etherA": {
        "vlanY": {
            "local": ['mac01', 'mac02'],
            "external": ['mac03', 'mac02']
        }
    },
    "etherB": {
        "vlanZ": {
            "local": ['mac06', 'mac09'],
            "external": ['mac01', 'mac02', 'mac03']
        }
    }
}

df = pd.json_normalize(data, sep='_')
flatten_dict = df.to_dict(orient='records')[0]
res = []
for k, v in flatten_dict.items():
    for i in v:
        res.append(k.split("_")+[i])
res_df = pd.DataFrame(res, columns=["interface", "vlan", "dyn", "mac-address"])
print(res_df)