how to convert excel data to nested list of dict?

Question

I have excel file with multiple tabs with this following column header and data. I would like to parse the data from the excel into the list of nested dict, so that I can build the configuration.

Like to create data output following format:

[ 
{ 
"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"}]
    } 
]

CODE Tried:

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)

but it keeps repeating the Switch column, with each entry.

Merging two DICTs into new Dict with group Switch A

Here is the code I tried

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)

What research have you done? Do you know how to read the Excel file? — dspencer
– dspencer, Commented Apr 9, 2020 at 6:49
I have started using xlrd to open the excel file and looping thru tabs. Just not getting data correctly stored, let me update the code I have tried. — miu
– miu, Commented Apr 9, 2020 at 7:02

jezrael · Accepted Answer · 2020-04-09 07:06:40Z

1

Use DataFrame.groupby with lambda function:

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'
    }]
}

edited Apr 9, 2020 at 7:06

answered Apr 9, 2020 at 6:56

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

miu Over a year ago

thanks for providing quick answer, how to add the dict key, with the given name?

jezrael Over a year ago

@miu - Isee. My solution working with DataFrame, so is necessary first step df = pd.read_excel(excelFile)

jezrael Over a year ago

@miu - it shoud be one way. In pandas is used another way - reading all sheetnames to dictionary of DataFrame

jezrael Over a year ago

@miu - I see it. There is space 'Switch A '

jezrael Over a year ago

@miu - So change .set_index('Switch A') to .set_index('Switch A ') - space after A

|

Collectives™ on Stack Overflow

how to convert excel data to nested list of dict?

1 Answer 1

15 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related