Python - Excel - Pivot - Part Four

   
   
 python  
 automation  
Python Automation Article Series Test

LibreOffice

Python - Excel

Table of Content
Where to Discuss?

Local Group

Preface

Goal: Writing the xslx sheet.

We are doing it using our previous Dog Class.

6: Pivot in Sheet Preparation

The difference is just the output. The output is a sheet instead of CLI.

Code Skeleton

The big picture of the Dog Class.

import openpyxl
from openpyxl import Workbook
from openpyxl import load_workbook

class DogSample:
  # ...

  def __init__(self, sheet_src, rows, sheet_dst):
    # ...

  def reset(self):
    # ...

  def get_total(self):
    # ...

  def display_to_console(self):
    # ...

  def update(self, pred, prob):
    # ...

  def walk_each_row(self):
    for row in self.rows:
      # ...

  def prepare_sheet(self):
    # ...

  def write_to_sheet(self):
    # ...

  def pivot(
    self, title, name_pred, name_prob,
    name_key_dest, name_val_dest):
    # ...

def main():
  # ...

main()

Global Initialization

We need to import Workbook.

import openpyxl
from openpyxl import Workbook
from openpyxl import load_workbook

Dog Class Property

We can add more probability group, to serve more category.

class DogSample:
  str_ranges  = {  0: '0.0-0.2',
                  20: '0.2-0.4', 40: '0.4-0.6',
                  60: '0.6-0.8', 80: '0.8-1.0' }

In this case, we have more probability for male dogs, so we have to add the ranges.

Dog Class Initialization

We only initialize two variables

  • sheet_src,
  • rows,
  • sheet_dst,
  def __init__(self, sheet_src, rows, sheet_dst):
    self.sheet_src = sheet_src
    self.rows      = rows
    self.sheet_dst = sheet_dst

Pivot Method

After this three variables

  • title,
  • name_pred,
  • name_prob

This also initialize two other variables

  • name_key_dest,
  • name_val_dest
  def pivot(
    self, title,
    name_pred, name_prob,
    name_key_dest, name_val_dest
  ):
    self.title     = title
    self.name_pred = name_pred
    self.name_prob = name_prob
    self.name_key_dest = name_key_dest
    self.name_val_dest = name_val_dest

    self.reset()
    self.walk_each_row()
    self.display_to_console()
    self.write_to_sheet()

Now it call four functions.

Defining Property

We should reset the counter for each calls.

  def reset(self):
    self.sample = {
      'puppy'    : 0,
      'juvenile' : 0,
      'junior'   : { 40: 0, 60: 0, 80: 0 },
      'female'   : { 40: 0, 60: 0, 80: 0 },
      'male'     : { 0:  0, 20: 0, 40: 0, 60: 0, 80:0 }
    }

Walk The Loop

Nothing change.

This require update method.

Updating Sample Count

Nothing change.

Counting Total

Nothing change.

Printing The Output

Nothing change.

7: Fill the Worksheet

This is still the same Class as before, but we focus on the the worksheet.

Main function

Consider see the bigger picture first. This is how we call the pivot picture.

def main():
  book_src  = load_workbook('recap.xlsx')
  sheet_src = book_src["Combined"]

  book_dst = Workbook()
  sheet_dst = book_dst.active

  r_start =  3
  r_stop  = 43
  rows = range(r_start, r_stop)

  sample = DogSample(sheet_src, rows, sheet_dst)
  sample.prepare_sheet()
  sample.pivot('First Pool',  'B', 'C', 'C', 'D')
  sample.pivot('Second Pool', 'D', 'E', 'E', 'F')
  sample.pivot('Third Pool',  'F', 'G', 'G', 'H')

  # Save the file
  book_dst.save("sample.xlsx")

And call it later as:

main()

I will explain the sample.pivot(...) later.

Dog Class Initialization

Let me remind, the initialization, that consist sheet_dst.

  def __init__(self, sheet_src, rows, sheet_dst):
    self.sheet_src = sheet_src
    self.rows      = rows
    self.sheet_dst = sheet_dst

Preparing Sheet

Now we can write directly the row header into the worksheet.

  def prepare_sheet(self):
    row_name = 'B'
    self.sheet_dst[row_name + '3']  = 'Puppy'
    self.sheet_dst[row_name + '4']  = 'Junior'
    self.sheet_dst[row_name + '7']  = 'Female'
    self.sheet_dst[row_name + '10'] = 'Male'
    self.sheet_dst[row_name + '15'] = 'Total Result'

Worksheet: Preparation

We need to add column header.

  def write_to_sheet(self):
    # shorter form of property
    s = self.sample
    sr = self.str_ranges

    row_p = self.name_key_dest # row name: probability
    row_c = self.name_val_dest # row name: count value
    self.sheet_dst[row_p + '2']  = 'Prob'
    self.sheet_dst[row_c + '2']  = 'Count'

Worksheet: Puppy

Then we can continue with Puppy.

    self.sheet_dst[row_p + '3']  = '1.0'
    self.sheet_dst[row_c + '3']  = s['puppy']

Worksheet: Junior or Juvenile

Since we have two mode for different cameras, the displayed result is different for both:

    if s['juvenile'] > 0:
      self.sheet_dst[row_p + '6']  = 'Juvenile'
      self.sheet_dst[row_c + '6']  = s['juvenile']

    if sum(s['junior'].values()) > 0:
      for key in [40, 60, 80]:
        col = str(4+int(key/20)-2)
        self.sheet_dst[row_p + col]  = sr[key]
        self.sheet_dst[row_c + col]  = s['junior'][key]

I remind that the second camera use juvenile term, without probability.

Worksheet: Male or Female

And the rest is the same

    for key in [40, 60, 80]:
      if s['female'][key]:
        col = str(7+int(key/20)-2)
        self.sheet_dst[row_p + col]  = sr[key]
        self.sheet_dst[row_c + col]  = s['female'][key]

    for key in [0, 20, 40, 60, 80]:
      if s['male'][key]:
        col = str(10+int(key/20))
        self.sheet_dst[row_p + col]  = sr[key]
        self.sheet_dst[row_c + col]  = s['male'][key]

Total Count

Do not forget to also write the count.

    self.sheet_dst[row_c + '15']  = self.get_total()

We are done.

The Result in Sheet

The plain result without formatting comes as below figure:

openpyxl: Output: Plain Pivot

Formatting

I’m still using manual formatting, using paste special. Because I still change the design a lot.

openpyxl: Output: Paste Special

The output could be as below:

openpyxl: Output: Colored Pivot

Like I said, in my real life busy office hours, I use many multicolumn pivot table.

This script really save my precious time.

What is Next 🤔?

Consider continue reading [ Python - Excel - Diff ].