Save nested dictionary with array of numbers into CSV file

Question

I have a dictionary (cell_summary) with 46 items(b1c0,b1c1,b1c2..) in it. Each item in "cell_summary" is a dictionary with 8 items in it. Each of the 8 items is an array of numbers. I would like to convert the 46 items as 46 sheets in a single CSV or excel workbook. 8 key names must be the column names in each sheet and the corresponding data should come in the same column. See the images attached to get a clear Idea of the data. Each sheet in the CSV file should have all the 8 field names and the values in the array must appear in the column of the corresponding field.

I tried the below code. But, all the values in the array are being stored in only one cell of the CSV file. Please see the attached image for reference.

Please help me with this.

import csv
import itertools

with open("Cell Summary.csv", 'w', newline='' )as f:

    fields = ['IR', 'QC', 'QD', 'Tavg', 'Tmin', 'Tmax', 'chargetime', 'cycle']
    w = csv.DictWriter( f, fields )
    for key,val in sorted(cell_summary.items()):
        row = {'IR': key}
        row.update(val)
        w.writerow(row)

Answer 1

Instead of writing a dictionary, it is easier to write a Dataframe as shown in the following implementation. You will need openpyxl installed, see the documentation for how to install.

import numpy as np
import pandas as pd
writer = pd.ExcelWriter('test.xlsx', engine='openpyxl')
diction={'b1c0':{'IR':np.arange(1,100,1),'QC':np.arange(1,100,1),'QD':np.arange(1,100,1)},
        'b1c1':{'IR':np.arange(1,100,1),'QC':np.arange(1,100,1),'QD':np.arange(1,100,1)},
        'b1c2':{'IR':np.arange(1,100,1),'QC':np.arange(1,100,1),'QD':np.arange(1,100,1)}}

for df_name, df in diction.items():
    pd.DataFrame.from_dict(df).to_excel(writer, sheet_name=df_name)
writer.save()