How do I convert an excel file into a nested python dictionary?

Question

I have a very specific Excel sheet that looks like this:

I am trying to convert it to a Python dictionary like this:

item_map = {
    "1609755": {
        "name": "test 1",
        "price": 125,
        "check_type": "check 1"
    },
    "1609756": {
        "name": "test 2",
        "price": 2500,
        "check_type": "check 2"
    },
    "1609758": {
        "name": "test 3",
        "price": 2400,
        "check_type": "check 3"
    }
}

My question is: How do I convert a excel sheet to a Python dictionary?

So far I tried using the library sheet2dict and also pandas, however in the excel sheet, the id is a key in the dictionary. As a result, I am quite stumped on how to ensure that the first column is ALWAYS set as a key.

from sheet2dict import Worksheet
ws = Worksheet()
file_path = 'test.xlsx'

x = ws.xlsx_to_dict(path = file_path)

print(x)

Shivam Roy · Accepted Answer · 2021-04-21 10:42:51Z

1

Since I do not have the Excel file, I created the DataFrame using your dictionary mentioned in the question i.e, item_map likewise:

df = pd.DataFrame(item_map).T    #Had to take a Transpose
df.index.name = 'id'

You can import your table as a DataFrame using pandas.read_excel. Remember to set the index as 'id'.

df = pd.read_excel('file_path')
df.index.name = 'id'

Then, use this code:

item_map = {}

for index, row in list(df.iterrows()):
    item_map[index] = dict(row)

print(item_map)

Output:

{'1609755': {'name': 'test 1', 'price': 125, 'check_type': 'check 1'},
'1609756': {'name': 'test 2', 'price': 2500, 'check_type': 'check 2'},
'1609758': {'name': 'test 3', 'price': 2400, 'check_type': 'check 3'}}

edited Apr 21, 2021 at 10:42

answered Apr 21, 2021 at 8:51

Shivam Roy

2,0713 gold badges12 silver badges25 bronze badges

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

5 Comments

inspiring Over a year ago

Thank you, however it seems after importing my table as a dataframe, it automatically specifies the key as 0, 1, 2. How do I specify it as the first column in my Excel file?

inspiring Over a year ago

Actually I figured it out: df.set_index('id', inplace=True) will set it so the id is the key.

Shivam Roy Over a year ago

Yes, did it work ? I tested my code by recreating your DataFrame.I used the dictionary you have mentioned in your question likewise: df = pd.DataFrame(item_map) .T and then set the index as: df.index.name = 'id'

IODEV Over a year ago

@ShivamRoy: The initial example only give a flat structure, ie {0: {'Id': 751, 'Name': 'Test 1', 'Price': 125, 'check_type': 'Check 1'}, Please update the answer with a working solution with nested dicts as OP asked for.

Shivam Roy Over a year ago

@IODEV The output would look flat actually, but it is in exactly the same format OP asked for. I tried the code before posting it and it did return me the nested dictionary. I'm sorry if I didn't understand your issue, is it regarding the alignment of the lines ? If you run the code you'll find that the output is in the same nested format as OP asked for.

Collectives™ on Stack Overflow

How do I convert an excel file into a nested python dictionary?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related