Nested Dictionary to excel with color formatting

user_13104276 0
Viewed 132

My dictionary looks like this :

dict1 = { '2020-10-11' : { 
                           'group1':{ 
                                     1 : 2356,
                                     21 : 10001,
                                     34 :  234 
                                   },
                            'group2':{
                                     11 : 999,
                                     2  : 101,
                                     13 : 1234 
                                     } 
                         },
         '2020-10-12' : { 
                           'group1':{ 
                                     11 : 236,
                                     21 : 100,
                                     34 :  34 
                                   },
                            'group2':{
                                     1 : 99,
                                     3 : 121,
                                     2 : 12 
                                     } 
                         }


}

I wanted my output to look something like this :

enter image description here

The requirement is : for every date, the color should be different.

I have tried this using this method:

reform = {(level1_key, level2_key, level3_key): values
          for level1_key, level2_dict in dict1.items()
          for level2_key, level3_dict in level2_dict.items()
          for level3_key, values      in level3_dict.items()}

out = pd.DataFrame(reform,index = ['amount']).T
names=['date', 'group', 'id']
out.index.set_names(names, inplace=True)

out in xls :

enter image description here

After this how am I supposed to proceed for the color formatting in excel using python?

excel colors python
1 Answers

The first step is the entirely flatten the structure so that a 2-dimensional representation of the nested values emmerges:

dict1 = {'2020-10-11': {'group1': {1: 2356, 21: 10001, 34: 234}, 'group2': {11: 999, 2: 101, 13: 1234}}, '2020-10-12': {'group1': {11: 236, 21: 100, 34: 34}, 'group2': {1: 99, 3: 121, 2: 12}}}
def flatten(d, c = []):
   flag = True
   for a, b in d.items():
     if isinstance(b, dict):
        yield from flatten(b, c=c+[a] if flag or not c else [*c[:-2],'',a])
     else:
        yield c+[a, b] if flag or not c else [*(['']*(len(c))),a, b]
     flag = False

data = list(flatten(dict1))
#[['2020-10-11', 'group1', 1, 2356], ['', '', 21, 10001], ['', '', 34, 234], ['', 'group2', 11, 999], ['', '', 2, 101], ['', '', 13, 1234], ['2020-10-12', 'group1', 11, 236], ['', '', 21, 100], ['', '', 34, 34], ['', 'group2', 1, 99], ['', '', 3, 121], ['', '', 2, 12]]

Next, create a pd.DataFrame from the results and apply the coloring:

import pandas as pd
df = pd.DataFrame(data, columns=['Date', 'Group', 'ID', 'Amount'])
writer = pd.ExcelWriter('test_rsults12.xls', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']
c_pool = iter([workbook.add_format({'bg_color': '#fff0c1'}), workbook.add_format({'bg_color': '#d5e6f5'})])
fmt = None
for i in range(len(data)):
   if data[i][0]:
      fmt = next(c_pool)
   worksheet.set_row(i+1, cell_format=fmt)

writer.save()

Result:

enter image description here

user_73267380
Related