# Python - Excel - Pivot - Part Four

Python Automation Article Series Test
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

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

#### 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.

Nothing change.

Nothing change.

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

``````  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: #### Formatting

I’m still using manual formatting, using paste special. Because I still change the design a lot. The output could be as below: 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 ].