title: Populate nested dictionary with spreadsheet data
body: <p>I have an idea of what I want my list of Python dictionaries to look like but am having issues pulling the spreadsheet data into the data structure.  The problem I have is that one row may have data to populate the parent dictionary values as well as 1 child.  For subsequent rows, if the values in the columns for the parent, are empty, then assume that the columns for the children belong to the previous parent.  If we come upon a new row where the parent data is not empty, consider it a new parent to add to the list.</p>
  
  <p>This is an example of what the spreadsheet would look like:</p>
  
  <pre><code>+--------------+-------------------+---------+----------+--------------------+--------------+------------------+-------------+---------------+----------------+
  | name         | descr             | adminSt | authSt   | server_hostname_ip | server_descr | server_preferred | server_EPG  | server_minPol | server_maxPoll |
  +--------------+-------------------+---------+----------+--------------------+--------------+------------------+-------------+---------------+----------------+
  | test1-NTPPOL | Test NTP Policy   | enabled | disabled | 10.10.10.10        | NTP1 server  | yes              | oob-default | 4             | 6              |
  +--------------+-------------------+---------+----------+--------------------+--------------+------------------+-------------+---------------+----------------+
  |              |                   |         |          | 10.10.10.11        | NTP2 server  | no               | oob-default | 4             | 6              |
  +--------------+-------------------+---------+----------+--------------------+--------------+------------------+-------------+---------------+----------------+
  |              |                   |         |          | 10.10.10.12        | NTP3 server  | no               | oob-default | 4             | 6              |
  +--------------+-------------------+---------+----------+--------------------+--------------+------------------+-------------+---------------+----------------+
  | test2-NTPPOL | Test 2 NTP policy | enabled | disabled | 20.10.10.10        | NTP1 server  | yes              | oob-default | 4             | 6              |
  +--------------+-------------------+---------+----------+--------------------+--------------+------------------+-------------+---------------+----------------+
  |              |                   |         |          | 20.10.10.11        | NTP2 server  | no               | oob-default | 4             | 6              |
  +--------------+-------------------+---------+----------+--------------------+--------------+------------------+-------------+---------------+----------------+
  |              |                   |         |          | 20.10.10.12        | NTP3 server  | no               | oob-default | 4             | 6              |
  +--------------+-------------------+---------+----------+--------------------+--------------+------------------+-------------+---------------+----------------+
  </code></pre>
  
  <p>I would like the data structure to look like this:</p>
  
  <pre><code>[
    {
      "name": "NTP_Policy1",
      "descr": "NTP Policy 1",
      "adminSt": "enabled",
      "authSt": "disabled",
      "servers": [
        {
          "hostname": "10.10.10.10",
          "descr": "NTP1 Server",
          "preferred": true,
          "server_EPG": "oob-default",
          "minPoll": 4,
          "maxPoll": 6
        },
        {
          "hostname": "20.10.10.10",
          "descr": "NTP2 Server",
          "preferred": false,
          "server_EPG": "oob-default",
          "minPoll": 4,
          "maxPoll": 6
        }
      ]
    },
    {
      "name": "NTP_Policy2",
      "descr": "NTP Policy 2",
      "adminSt": "enabled",
      "authSt": "disabled",
      "servers": [
        {
          "hostname": "30.10.10.10",
          "descr": "NTP3 Server",
          "preferred": true,
          "server_EPG": "oob-default",
          "minPoll": 4,
          "maxPoll": 6
        },
        {
          "hostname": "40.10.10.10",
          "descr": "NTP4 Server",
          "preferred": false,
          "server_EPG": "oob-default",
          "minPoll": 4,
          "maxPoll": 6
        }
      ]
    }
  ]
  </code></pre>
  
  <p>The closest code I came looked like this, however subsequent rows had the children appended to the parent level.</p>
  
  <pre><code>&gt;&gt;&gt; import pyexcel
  &gt;&gt;&gt; from pprint import pprint
  &gt;&gt;&gt; def excel_to_dict(sheet):
  ...     rows = sheet.iter_rows()
  ...     keys = next(rows)
  ...     dict_list = []
  ...     # For each row in the spreadsheet,
  ...     # Create an iterator pair so that the key is iterated over at the same time as its matching cell in the row
  ...     # Then save that pairing as descriptors of the switch
  ...     for row in rows:
  ...         dict = {}
  ...         dict['servers'] = []
  ...         server_atts = {}
  ...         for key,cell in zip(keys, row):
  ...             if str(cell.value) != 'None' and str(key.value) == 'name':
  ...                 dict[str(key.value)] = str(cell.value)
  ...                 parentKey = str(key.value)
  ...             elif (str(cell.value) != 'None' and str(key.value) == 'descr') or (str(cell.value) != 'None' and str(key.value) == 'adminSt') or (str(cell.value) != 'None' and str(key.value) == 'authSt'):
  ...                 dict[str(key.value)] = str(cell.value)
  ...             elif str(cell.value) == 'None':
  ...                 continue
  ...             else:
  ...                 server_atts[str(key.value)] = str(cell.value)
  ...         dict['servers'].append(server_atts.copy())
  ...         dict_list.append(dict.copy())
  ...     return dict_list
  &gt;&gt;&gt; wb = openpyxl.load_workbook('aci_config.xlsx')
  &gt;&gt;&gt; ntpPolsSheet = wb.get_sheet_by_name('ntp_pol')
  &gt;&gt;&gt; ntpPols = excel_to_dict(ntpPolsSheet)
  &gt;&gt;&gt;
  &gt;&gt;&gt; pprint(ntpPols)
  [{'adminSt': 'enabled',
    'authSt': 'disabled',
    'descr': 'Test NTP Policy',
    'name': 'test1-NTPPOL',
    'servers': [{'server_EPG': 'oob-default',
                 'server_descr': 'NTP1 server',
                 'server_hostname_ip': '10.10.10.10',
                 'server_maxPoll': '6',
                 'server_minPol': '4',
                 'server_preferred': 'yes'}]},
   {'servers': [{'server_EPG': 'oob-default',
                 'server_descr': 'NTP2 server',
                 'server_hostname_ip': '10.10.10.11',
                 'server_maxPoll': '6',
                 'server_minPol': '4',
                 'server_preferred': 'no'}]},
   {'servers': [{'server_EPG': 'oob-default',
                 'server_descr': 'NTP3 server',
                 'server_hostname_ip': '10.10.10.12',
                 'server_maxPoll': '6',
                 'server_minPol': '4',
                 'server_preferred': 'no'}]},
   {'adminSt': 'enabled',
    'authSt': 'disabled',
    'descr': 'Test 2 NTP policy',
    'name': 'test2-NTPPOL',
    'servers': [{'server_EPG': 'oob-default',
                 'server_descr': 'NTP1 server',
                 'server_hostname_ip': '20.10.10.10',
                 'server_maxPoll': '6',
                 'server_minPol': '4',
                 'server_preferred': 'yes'}]},
   {'servers': [{'server_EPG': 'oob-default',
                 'server_descr': 'NTP2 server',
                 'server_hostname_ip': '20.10.10.11',
                 'server_maxPoll': '6',
                 'server_minPol': '4',
                 'server_preferred': 'no'}]},
   {'servers': [{'server_EPG': 'oob-default',
                 'server_descr': 'NTP3 server',
                 'server_hostname_ip': '20.10.10.12',
                 'server_maxPoll': '6',
                 'server_minPol': '4',
                 'server_preferred': 'no'}]}]
  </code></pre>
  
  <p>What does the code need to look like to populate the dict list correctly?  Is there a better spreadsheet format that may make it easier to import the data in?  I am trying to do this all in a single sheet, not multiple sheets.</p>