Convert excel to nested dictionary

chun xu 0
Viewed 32

I try to convert excel file to a python dictionary. The excel looks like below excel

The dictionary I would like to have is as below

[
  {
    "ipam.prefixes": [
      {
        "10.0.1.0/28": {
          "prefix": "10.0.1.0/28",
          "status": "container",
          "vlan": {"vid": 200}
        }
      }
  ]
]

If the excel data is flat, like "prefix" and "status" I can convert them, but with extra level of data, I could not figure out how to make it. Most of the data in the excel are basic key/value pair, but some random item will be key:{key1:value} or key:{key1:value1, key2:value2}, I try to find a generic way to convert them to proper dictionary ragardless what does user enter. It is ok to change the format of the excel if it makes the python code easy to manipulate the data.

dictionary pandas python
1 Answers

You could probably try something like this :

import pprint
import pandas as pd
dfs = pd.read_excel('test.xlsx', sheet_name=None)
res = {}
for sheet_name, df in dfs.items():
    df_dict = df.to_dict()  # TODO : check if there is at least 4 rows, otherwise it will crash
    subname = df_dict['prefix'][0]
    res[sheet_name] = [
        {subname:{
            key: df_dict[key][0] if pd.isna(df_dict[key][1])
                 else {df_dict[key][0]: df_dict[key][1]} if pd.isna(df_dict[key][3])
                 else {df_dict[key][0]: df_dict[key][1], df_dict[key][2]: df_dict[key][3]}
                 for key in df_dict.keys()
        }}
    ]
pprint.pprint(res)

This is far from perfect and has many caveats (e.g. you have to add rows to your dataframes if there are not 4 rows), and I took some assumptions (like that you won't have more than two levels of key:value pairs, or that you'll always have a 'prefix' key).

That still should be a good start.

Note that the output in your question misses a bracket, hence this code output might be slightly be different than the one you asked for

Alexandre GAREL0
Related