Dictionary of Dictionaries to Excel

Question

I have a dictionary of dictionaries (Di_1):

Di_1 = {1 : {101 : ['cat', 'mouse'],
             102 : ['dog'],
             103 : []},
        2 : {101 : ['cat', 'mouse', 'dog'],
             102 : ['dog', 'cat'],
             103 : ['mouse']},
        3 : {101 : ['cat', 'mouse', 'dog', 'lion'],
             102 : ['dog', 'cat'],
             103 : ['mouse', 'dragon']}}

I would like to export it to Excel (test_1.xlsx). I tried it in Pandas:

import pandas as pd
writer = pd.ExcelWriter('test_1.xlsx', engine='openpyxl') 
for A in [1,2,3]:
    df_A = pd.DataFrame(dict([ (k,pd.Series(v)) for k,v in Di_1[A].items() ])).T
    df_A.to_excel(writer, sheet_name=str(A))

This code ran but it didn't export anything to excel. How can I export it please?

Answer 1

Here missing writer.save():

writer = pd.ExcelWriter('test_1.xlsx', engine='openpyxl') 
for A in [1,2,3]:
    df_A = pd.DataFrame(dict([ (k,pd.Series(v)) for k,v in Di_1[A].items() ])).T
    df_A.to_excel(writer, sheet_name=str(A))
  
writer.save()

Solution should be simplify with DataFrame.from_dict:

writer = pd.ExcelWriter('test_1.xlsx', engine='openpyxl') 
for A in [1,2,3]:
    df_A = pd.DataFrame.from_dict(Di_1[A], orient='index')
    df_A.to_excel(writer, sheet_name=str(A))
  
writer.save()