如何将excel数据转换为dict的嵌套列表？

Question

我有一个包含多个选项卡的excel文件，其中包含以下列标题和数据。我想将excel中的数据解析到嵌套dict列表中，以便构建配置

要创建以下格式的数据输出：

[ 
{ 
"SW-01": [{"swportA": "Et17/1", "swipA": "192.168.128.1", "toswname": "A20-01","toswport": "Et1/1", "toswip": "192.168.128.0"}, 
    {"swportA": "Et18/1", "swipA": "192.168.128.3", "toswname": "A20-","toswport": "Et2/1", "toswip": "192.168.128.2"}, 
    {"swportA": "Et19/1", "swipA": "192.168.128.5", "toswname": "A20-01", "toswport": "Et3/1", "toswip": "192.168.128.4"}]
  }, 
  { 
    "SW-02": [{"swportA": "Et17/1", "swipA": "192.168.128.129", "toswname": "A20-01", "toswport": "Et4/1", "toswip": "192.168.128.130"}, 
        {"swportA": "Et18/1", "swipA": "192.168.128.131", "toswname": "A20-01", "toswport": "Et5/1", "toswip": "192.168.128.132"}, 
        {"swportA": "Et19/1", "swipA": "192.168.128.133", "toswname": "A20-01", "toswport": "Et6/1", "toswip": "192.168.128.134"}]
    } 
] 

已尝试的代码：

book = xlrd.open_workbook(excelFile)
worksheet = book.sheet_by_index(0)
data = []
for sheet in book.sheets():
    listofiles = []
        for i in range(2, sheet.nrows):
            sw = {}
            row = sheet.row_values(i)
            swname = row[0]

            if not swname in data:
                swname
                data.append( { swname: {
                    'swport': row[1],
                    'swip': row[2],
                    'toswname': row[3],
                    'toswport': row[4],
                    'toswip': row[5]

                }})
        pprint(data)

但它不断重复开关列，每个条目

将两个Dict与组Switch A合并为新Dict

这是我试过的代码

d = {}
for key in (*dic1, *dic2):
    try:
        d.setdefault(key,[]).append(dic1[key])
    except KeyError:
        pass
    try:
        d.setdefault(key,[]).append(dic2[key])
    except KeyError:
        pass
pprint(d)

Answer 1

将^{}与lambda函数一起使用：

df = pd.read_excel(excelFile)

d = df.set_index('Switch A').groupby(level=0).apply(lambda x: x.to_dict('r')).to_dict()

---

print (d)

{
    'A18-SW-01': [{
        'swportA': 'Et17/1',
        'swipA': '192.168.128.1',
        'toswname': 'A20-FAB-01',
        'toswport': 'Et1/1',
        'toswip': '192.168.128.0'
    }, {
        'swportA': 'Et18/1',
        'swipA': '192.168.128.3',
        'toswname': 'A20-FAB-01',
        'toswport': 'Et2/1',
        'toswip': '192.168.128.2'
    }, {
        'swportA': 'Et19/1',
        'swipA': '192.168.128.5',
        'toswname': 'A20-FAB-01',
        'toswport': 'Et3/1',
        'toswip': '192.168.128.4'
    }],
    'A19-SW-01': [{
        'swportA': 'Et17/1',
        'swipA': '192.168.128.129',
        'toswname': 'A20-FAB-01',
        'toswport': 'Et4/1',
        'toswip': '192.168.128.130'
    }, {
        'swportA': 'Et18/1',
        'swipA': '192.168.128.131',
        'toswname': 'A20-FAB-01',
        'toswport': 'Et5/1',
        'toswip': '192.168.128.132'
    }, {
        'swportA': 'Et19/1',
        'swipA': '192.168.128.133',
        'toswname': 'A20-FAB-01',
        'toswport': 'Et6/1',
        'toswip': '192.168.128.134'
    }]
}