I have a nested dictionary and I want to do the following:

1. Create excel worksheet based on name of dictionary (Excel sheets called dict1, dict2 etc based on example below)
2. Create headers in the Excel sheet that match the dictionary - so cell A1 = key1, cell B1 = key2
3. Populate the data that I have in the dictionary into the relevant columns (e.g col A2,A3,A4 etc to have data1, data2, data3 (and the same for the rest of the columns)

Input dictionary example:

nested_dict = {'dict_1': {'key1': ['data1', 'data2', 'data3'], 'key2': ['1', '2', '3'], 'key3': ['value1', 'value2', 'value3']},
'dict_2': {'key1': ['data1', 'data2', 'data3'], 'key2': ['1', '2', '3'], 'key3': ['value1', 'value2', 'value3']}}

Desired output in Excel

key1 | key2|key3|
data1| 1   |value1
data2| 2   |value2
data3| 3   |value3

I am currently using openpyxl to try to achieve this but not exactly sure how to iterate through the nested dictionary correctly and access create worksheets for each dictionary then repeat for the rest of the data.

for k,v in nested_dict.items:
    for sheetName in wb.sheetnames:
        wb.create_sheet(k)
        ws.cell(row=1, column=1).value 
        
wb.save('test.xlsx')  

Could someone point me in the right direction?

CodePudding user response：

U can use 'pandas' package

import pandas as pd
nested_dict = {'dict_1': {'key1': ['data1', 'data2', 'data3'], 'key2': ['1', '2', '3'], 'key3': ['value1', 'value2', 'value3']},
               'dict_2': {'key1': ['data1', 'data2', 'data3'], 'key2': ['1', '2', '3'], 'key3': ['value1', 'value2', 'value3']}
               }
writer = pd.ExcelWriter('test.xlsx')
for sheet,data in nested_dict.items():
    df = pd.DataFrame(data)
    df.to_excel(writer, sheet_name=sheet,index=False)
writer.save()