'Formatting and add Header columns in xlsx dynamically - Python

I need to create an xlsx file for data from database.I am using the xlsx package for python

  `XlsxWriter==3.0.3`

My two data looks like this

keywords

[
   {
     "keyword": "mens wallet",
     "volume": 1234
     
   },
   {
       "keyword": "leather wallets",
       "volume": 23
    },
    .....
]

Results

[
{
    "id": 9596,
    "keyword_id": 8,
    "result_type": "sponsored_product",
    "keyword_name": "mens wallet",
    "asin": "B07FPVR858",
    "rank": 1
},
{
    "id": 9597,
    "keyword_id": 8,
    "result_type": "sponsored_product",
    "keyword_name": "mens wallet",
    "asin": "B07H2MVRV3",
    "rank": 2
},
{
    "id": 9598,
    "keyword_id": 8,
    "result_type": "sponsored_product",
    "keyword_name": "mens wallet",
    "asin": "B0796PTVLR",
    "rank": 3
},
{
    "id": 9599,
    "keyword_id": 8,
    "result_type": "sponsored_product",
    "keyword_name": "mens wallet",
    "asin": "B09P3QGR1D",
    "rank": 4
},
{
    "id": 16464,
    "keyword_id": 14,
    "result_type": "sponsored_product",
    "keyword_name": "wallets color brown",
    "asin": "B079DCW7GB",
    "rank": 17
},

What I tried so far is

  def result:
      import xlsxwriter
      
        workbook = xlsxwriter.Workbook("temp.xlsx")
        worksheet = workbook.add_worksheet()
        cell_format = workbook.add_format({"bold": True, "font_color": "red"})
        cell_format.set_font_size(16)
        cell_format.set_underline(34)
        cell_format.set_align("center")
        worksheet.write("A1", "Keyword", cell_format)
        worksheet.write("B1", "Search volume", cell_format)

        row = 1
        col = 0

        worksheet.set_column("B:B", 30)
        worksheet.set_column("A:A", 30)
        for key in keywords:
           worksheet.write(row, col, key["keyword"])
           worksheet.write(row, col + 1, key["volume"])
           row += 1
        workbook.close()

Output

What I want to do is to add header colums dynamically next to Search volume column (C1) based on asin keys in results. So that if the keyword_name matches keyword value from A1, it will populate the rank value for its asin. So a keyword_name has multiple asin and its asin has different rank value for every keyword, like the screenshot below

Any help is much appreciated.

Thanks

python-3.x

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source

'Formatting and add Header columns in xlsx dynamically - Python

Sources

Related Questions