How to Create a nested dictionary, from excel data using pandas in python?

Question

using Excel table data, Want to create a nested dictionary as follows:

    dict_mainmenu_items = {"id"     : {"lbl101":"file"   ,"lbl102":"accounts"  ,"lbl103":"inventory","lbl104":"manufacture"},
                           "english" : {"lbl101":"File"  ,"lbl102":"Accounts"  ,"lbl103":"Inventory","lbl104":"Manufacture"},
                           "tamil"   : {"lbl101":"tamil_file","lbl102":"tamil_accounts","lbl103":"tamil_inventory","lbl104":"tamil_manu"},
                           "hindi"   : {"lbl101":"hindi_file","lbl102":"hindi_accounts","lbl103":"hindi_inventory","lbl104":"hindi_manuf"}}

try to solve the problem by using pandas, and code as follows:

import pandas as pd

file_path = r'C:/Users/Asus/Desktop/Documents/pyhton_dict_example.xlsx'
df = pd.read_excel(file_path)
df.set_index('lbl_name',inplace=True)

print(df.to_dict(orient='index'))

its produce the following result :

{'lbl101': {'id': 'file', 'english': 'File', 'tamil': 'tamil_file', 'hindi': 'Hindi_File'}, 'lbl102': {'id': 'accounts', 'english': 'Accounts', 'tamil': 'tamil_accounts', 'hindi': 'Hindi_Accounts'}, 'lbl103': {'id': 'inventory', 'english': 'Inventory', 'tamil': 'tamil_inventory', 'hindi': 'Hindi_Inventory'}, 'lbl104': {'id': 'manufacture', 'english': 'Manufacture', 'tamil': 'tamil_manuf', 'hindi': 'Hindi_Manufacture'}}

Excel Table

lbl_name	id	english	tamil	hindi
lbl101	file	File	tamil_file	hindi_file
lbl102	accounts	Accounts	tamil_accounts	hindi_accounts
lbl103	inventory	Inventory	tamil_inventory	hindi_inventory
lbl104	manufacture	Manufacture	tamil_manufact	hindi_manu

| lbl_name | id         |english     |tamil            |hindi          |
|------------------------------------------------------|---------------|
| lbl101   | file       | File       |tamil_File       |hindi_file     |
| lbl102   | accounts   | Accounts   |tamil_accounts   |hindi+accounts |          |
| lbl103   | inventory  | Inventory  |tamil_inventory  |hindi_inventory|
| lbl104   | manufacture| Manufacture|tamil_manufact|  |hindi_manu     |

whats wrong with your current code? what does it currently give you? — Christian Trujillo, Aug 17 '22 at 16:29
Does the [`df.to_dict` documentation](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_dict.html) answer your question? Also, please take time to read [How to create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) — Vladimir Fokow, Aug 17 '22 at 16:31
Don't use `orient='index'`, leave it as it is by default: `orient='dict'` — Ignatius Reilly, Aug 17 '22 at 16:31
Does this answer your question? [Convert a Pandas DataFrame to a dictionary](https://stackoverflow.com/questions/26716616/convert-a-pandas-dataframe-to-a-dictionary) — Vladimir Fokow, Aug 17 '22 at 16:39
@Vladimir Fokow, goat a solution from ignatius Reilly comment,as above — Bala, Aug 17 '22 at 16:48

score 0 · Answer 1 · answered Aug 17 '22 at 17:05

0

you should transpose the dataframe before converting it into dictionary.

df.T.to_dict()

answered Aug 17 '22 at 17:05

Dan911

1

How to Create a nested dictionary, from excel data using pandas in python?

1 Answers1