python - 嵌套字典以使用颜色格式表现出色

Question

我的字典看起来像这样:

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 
                                     } 
                         }


}

我希望我的输出看起来像这样:

要求是:对于每个日期，颜色应该不同。
我已经尝试过使用这种方法:

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)

在 xls 中:

在此之后，我应该如何使用 python 在 excel 中进行颜色格式化？

Answer 1

第一步是完全展平结构，以便出现嵌套值的二维表示:

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]]

接下来，创建一个 pd.DataFrame从结果中并应用着色:

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()

结果: