0

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)
miu
  • 189
  • 2
  • 13
  • What research have you done? Do you know how to read the Excel file? – dspencer Apr 09 '20 at 06: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 Apr 09 '20 at 07:02

1 Answers1

2

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'
    }]
}
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • thanks for providing quick answer, how to add the dict key, with the given name? – miu Apr 09 '20 at 07:01
  • @miu - Not understand, can you explain more? – jezrael Apr 09 '20 at 07:03
  • thanks, I just update the code, I was trying. Trying to understand the panda DataFrame. – miu Apr 09 '20 at 07:06
  • 1
    @miu - Isee. My solution working with `DataFrame`, so is necessary first step `df = pd.read_excel(excelFile)` – jezrael Apr 09 '20 at 07:07
  • For multiple data sheet tab, Can I use the following: book = xlrd.open_workbook(excelFile) for sheet in book.sheets(): < your solutions > df = pd.read_excel(sheet) d... – miu Apr 09 '20 at 07:10
  • 1
    @miu - it shoud be one way. In pandas is used another way - reading all sheetnames to dictionary of DataFrame – jezrael Apr 09 '20 at 07:15
  • @miu like [this](https://stackoverflow.com/questions/52538718/pandas-create-separate-dataframe-for-each-excel-sheet/52538754#52538754) – jezrael Apr 09 '20 at 07:16
  • i got following errors: ```Traceback (most recent call last): File "confgenerate.py", line 119, in d = df.set_index('Switch A').groupby(level=1).apply(lambda x: x.to_dict('r')).to_dict() File ".../....frame.py", line 4303, in set_index raise KeyError(f"None of {missing} are in the columns") KeyError: "None of ['Switch A'] are in the columns"``` – miu Apr 09 '20 at 08:06
  • here is the code I tried: ```xls = pd.ExcelFile(excelFile) for sheet in xls.sheet_names: df = pd.read_excel(excelFile, sheet_name = sheet) d = df.set_index('Switch A').groupby(level=1).apply(lambda x: x.to_dict('r')).to_dict() ``` – miu Apr 09 '20 at 08:07
  • @miu - Maybe `Switch A` is not column, but index, so only remove `.set_index('Switch A')` – jezrael Apr 09 '20 at 08:07
  • I get index value in ``` {0: [{'Switch A ': 'A18-SW-01', 'Switch A IP': '192.168.128.1', 'Switch A Port': 'Et17/1', 'Switch B': 'A20-FAB-01', 'Switch B IP': '192.168.128.0', 'Switch B port': 'Et1/1'}], 1: [{'Switch A ': 'A18-SW-01', 'Switch A IP': '192.168.128.3', 'Switch A Port': 'Et18/1', 'Switch B': 'A20-FAB-01', 'Switch B IP': '192.168.128.2', 'Switch B port': 'Et2/1'}], ``` – miu Apr 09 '20 at 08:10
  • 1
    @miu - I see it. There is space `'Switch A '` – jezrael Apr 09 '20 at 08:11
  • 1
    @miu - So change `.set_index('Switch A')` to `.set_index('Switch A ')` - space after `A` – jezrael Apr 09 '20 at 08:11
  • Hi ~@jezrael, I have `Switch A` exits in the different spread sheet, How to merge into new dict. I have update the code, that I have tried, but getting values twices. – miu Apr 12 '20 at 20:14
  • @miu - do you think [this](https://stackoverflow.com/questions/38987/how-do-i-merge-two-dictionaries-in-a-single-expression-in-python) ? If not working I think best is create new question. – jezrael Apr 13 '20 at 06:49