automation-with-python

Introduction

To become a sought-after Backend, Cloud or DevOps Engineer, having a good knowledge of the Python Programming language is essential. Python is one of the most dynamic languages, and for cloud DevOps engineers, it helps make your scripting processes easy. In this piece, we will show you how to use Python to read and write on cells on an Excel sheet.

When working with files in Python, most people understand the basic io module used for creating, reading, and writing files. However, to read spreadsheet files, we use the openpyxl package.

Openpyxl is unique because it has better practical functions to work with spreadsheets,a dn it is easier to use.

Prerequisites.

Python 3.* installed
Any editor for your Python code.
Openpyxl installed

Step 1

Open your code editor and create a main.py file in a new Python project.

Open the integrated terminal and install the Python openpyxl package using the following commands.

pip3 install openpyxl

For this article, we will use an already existing spreadsheet to show to how to read, and write and we will call it inventory.xlsx. A copy of the spreadsheet can be found here.

Step 2

To use the openpyxl command, you import it into your project file using the import command as seen below.

import openpyxl

With the openpyxl command, you need to access the spreadsheet, and read the content. It is easy to do this by using the load_workbook() command as shown below.

open.load_workbook("inventory.xlsx")

For better readability, and usability, we need to save this command under a variable. Let's call it inv_file. So, the command will look like this below

inv_file = openpyxl.load_workbook("inventory.xlsx")

For Spreadsheets with multiple sheets, we use the command inv_file["Sheet1"] and save it to a variable called product_list as shown below.

product_list = inv_file["Sheet1"]

Reading value from the Spreadsheet

List each company and their respective product count

From the Excel file used in the project, there are 74 products, with different inventory units, prices, and suppliers. The first exercise here is to list each company with its respective product count. First, we will sort out the number of products from each supplier. This is as shown below.

product_per_supplier = {} 
for product_row in range(2, product_list.max_row + 1):
      supplier_name = product_list.cell(product_row, 4).value

      if supplier_name in product_per_supplier:
          current_num_products = products_per_supplier[supplier_name]
          product_per_supplier[supplier_name] = current_num_products + 1
      else:
          product_per_supplier[supplier_name] = 1

print(products_per_supplier)

The results on the terminal will be given as

{'AAA Company': 43, 'BBB Company': 17, 'CCC Company': 14}

Calculate total inventory value per supplier

Modifying the previous code by adding a variable to an empty array. This variable should be labelled total_value_per_supplier, the output will be given as thus;

product_per_supplier = {} 
total_value_per_supplier = {}

for product_row in range(2, product_list.max_row + 1):
      supplier_name = product_list.cell(product_row, 4).value
      inventory = product_list.cell(product_row, 2).value
      price = product_list.cell(product_row, 3).value

      if supplier_name in product_per_supplier:
          current_num_products = products_per_supplier[supplier_name]
          product_per_supplier[supplier_name] = current_num_products + 1
      else:
          product_per_supplier[supplier_name] = 1


      if supplier_name in total_value_per_supplier:
          current_total_value = total_value_per_supplier.get(supplier_name)
          total_value_per_supplier[supplier_name] = current_total_value + inventory * price

      else:
          total_value_per_supplier[supplier_name] = inventory * price

print(total_value_per_supplier)

This code will result in the following output printed out.

{'AAA Company': 10969859.95, 'BBB Company': 2375499.47, 'CCC Company': 8114363.62}

Print product with inventory less than 10.

To achieve this, we first modify the previous code and add a variable to an empty array. The variable will be labelled as product_under_10_inv It shows as follows.

product_per_supplier = {} 
total_value_per_supplier = {}
products_under_10_inv = {}

for product_row in range(2, product_list.max_row + 1):
      supplier_name = product_list.cell(product_row, 4).value
      inventory = product_list.cell(product_row, 2).value
      price = product_list.cell(product_row, 3).value
      product_num = product_list.cell(product_row, 1).value

      if supplier_name in product_per_supplier:
          current_num_products = products_per_supplier[supplier_name]
          product_per_supplier[supplier_name] = current_num_products + 1
      else:
          product_per_supplier[supplier_name] = 1


      if supplier_name in total_value_per_supplier:
          current_total_value = total_value_per_supplier.get(supplier_name)
          total_value_per_supplier[supplier_name] = current_total_value + inventory * price

      else:
          total_value_per_supplier[supplier_name] = inventory * price


      if inventory < 10:
          products_under_10_inv[int(product_num)] =       int(inventory)

print(products_under_10_inv)

The output will be printed as thus

{25: 7, 30:6, 74:2}

Conclusion

In this article, we have shown you how to use Python programming to automate several parts of your spreadsheet. This is a small tip of what the Python programming can do in a typical spreadheet.

Within the article, we listed each company and their respective product count, we also calculated the total inventory value per supplier, and also printed out all the Products with inventory less than 10.

Python is a powerful programming language, and the best when it comes to automation. In future article, we will discuss how the Python programming language works with AWS Cloud infrastructure.

Reference

Techworld with Nana - Python with Spreadsheets Tutorial

Automation with Python: Working with Spreadsheets