Create Excel file with Python + similarity matrix

Introduction

There are several libraries for creating Excel files in Python, but XlsxWriter is recommended for the following reasons.

• Can output .xlsx files
• Simple and powerful because the functions are limited to output only

Installation

$ pip install xlsxwriter

Basic usage

Official sample translated into Japanese as appropriate

If you write like this

xlsxwriter_demo.py

#Create new files and worksheets
workbook = xlsxwriter.Workbook('demo.xlsx')
worksheet = workbook.add_worksheet()

#Change the width of column A
worksheet.set_column('A:A', 20)

#Added format to make it bold
bold = workbook.add_format({'bold': True})

#Writing text
worksheet.write('A1', 'Hello')

#Writing text and applying formatting
worksheet.write('A2', 'World', bold)

#Write a number (specify the cell address with a number)
worksheet.write(2, 0, 123)
worksheet.write(3, 0, 123.456)

#Insert image
worksheet.insert_image('B5', 'logo.png')

workbook.close()

Will be like this

Creating a similarity matrix

Just a personal note from here.

• I want to make a similarity matrix (this kind of guy) using the color scale.
• I want to automatically adjust the maximum and minimum values when specifying colors.
• Diagonal elements must be excluded from the calculation of maximum and minimum values

Maybe you can do it with numpy, scipy, matplotlib, but the one that came to my mind was xlsxwriter. The resulting sauce looks like this.

sim_matrix.py

# -*- coding: utf-8 -*-
import xlsxwriter
from xlsxwriter.utility import xl_rowcol_to_cell, xl_range_abs

#Create workbooks and worksheets
wb = xlsxwriter.Workbook("sim_matrix.xlsx")
ws = wb.add_worksheet("similarity matrix")

#Set data cell size
for i in range(5):
    ws.set_row(i, 40.5)
    ws.set_column(i, i, 6.0)

#Data entry
data = [[1.00, 0.15, 0.09, 0.01, 0.02],
        [0.15, 1.00, 0.12, 0.06, 0.03],
        [0.09, 0.12, 1.00, 0.08, 0.01],
        [0.01, 0.06, 0.08, 1.00, 0.05],
        [0.02, 0.03, 0.01, 0.05, 1.00]]
for i in range(5):
    for j in range(5):
        ws.write_number(i, j, data[i][j])

#Find the maximum and minimum values excluding diagonal elements using an array formula
data_field = xl_range_abs(0, 0, 4, 4)
ws.write_string(0, 6, "max sim")
max_formula = '{{=MAX(IF(ROW({0})=COLUMN({0}),"",{0}))}}'.format(data_field)
ws.write_formula(0, 7, max_formula)
ws.write_string(1, 6, "min sim")
min_formula = '{{=MIN(IF(ROW({0})=COLUMN({0}),"",{0}))}}'.format(data_field)
ws.write_formula(1, 7, min_formula)

#Apply color scale using conditional formatting
max_cell = xl_rowcol_to_cell(0, 7, row_abs=True, col_abs=True)
min_cell = xl_rowcol_to_cell(1, 7, row_abs=True, col_abs=True)
props = {
    "type": "2_color_scale",
    "max_color": "#FF6347", # tomato
    "min_color": "#FFFFE0", # lightyellow
    "max_type": "formula",
    "min_type": "formula",
    "max_value": max_cell,
    "min_value": min_cell}
ws.conditional_format(0, 0, 4, 4, props)

#writing
wb.close()

Such a file is output.