Python Pandas Pivot Table

This blog is going to be mostly helpful to folks who work with excel and databases. But, if you work with Excel and find yourself needing to deal with automation of pivot tables, you might also appreciate the technique.

When working with MySQL database, it was not an easy way for me to fetch the data and reshape it to a Pivot Table. I did duckduckgo, found ample of examples but most of them were hard to understand or not yielding the required output.

Python’s Pandas Pivot Table was the savior!! I followed below steps to fetch data from MySQL DB and generate a simple Pivot Table. Later this pivot table exported as an excel file and shared with stakeholders using slack.

  • Create connection string to MySQL DB using PyMySQL
  • Create a SQLAlchemy Engine (ORM)
  • Write a Query statement to fetch data from DB
  • Create a Pandas Dataframe directly from the query using SQLAlchemy
  • Create a Pandas Pivot Table with required Index, Columns and Values
  • Export data to excel or csv file using to_excel and to_csv respectively
  • Share it to stakeholders via slack

Please find below Python code screenshots

I hope this blog triggered a thought to automate the mundane task of generating pivot tables from spreadsheets or databases. Please reach out to me if you want to check out the full code.

Thank you for stopping by this blog and please share your suggestions below under the Comment section

Up Next – Manage Alerts of VMAX3 array using REST API

Image Courtesy:

Matt Whitt
https://ttorial.com/Images/python-automation-automate-mandane-task-python.jpg

Advertisements

Automate Cisco switches config using NETMIKO

Netmiko is a simplified module to manage Network devices via SSH. This module is developed by Kirk Byers. I would recommend all network engineers to learn and equip this module to automate day to day configuration tasks.

netmiko_show_arp

I’ve written a demo script to disable SNMP Server’s “globalenforcepriv” on both Cisco IOS and Nexus switches. Using this script we could make the necessary changes on 100’s of switches in few minutes saving time and energies of network engineers.

Al Sweigart wrote a book called ‘Automate boring stuff using python’. This is one of the scripts which could help network engineers to automate monotonous tasks using Python and Netmiko module.

EMC VMAX Storage Automated Performance Report

This blog is being written as a companion to my previous blog on Automated EMC VMAX Capacity Reporting

platform-vmax

In recent times, we’re asked to develop scripts to capture performance metrics from EMC VMAX storage. There is a ‘symstat’ command with many attributes to capture performance metrics information from the array. But this command was not fulfilling all our requirements. While exploring various options and consultation with EMC support / community we decided to try Unisphere / RESTAPI.

So far I was using Perl as THE LANGUAGE to talk to my storage arrays. But I was forced to switch over to Python which works best with REST API / JSON. Additionally, there are lots of code out there on RESTAPI written in Python. So it is easy to ‘get inspired’ by those codes and write customized code for our requirements. So this would make me yet another ‘Pythonistas’ 🙂

This is my first ever Python (version 2.7 on GNU/Debian Linux) script to capture EMC VMAX Performance Metrics retrieved from Unisphere for VMAX (version 8.2) via RESTAPI. I’ve referred this Python script to develop custom script to suit our requirements. Many thanks to Matt Cowger (mcowger) for sharing the script in Github.

There are plenty of metrics that can be captured using this script but I’ve written a simple code for demo purpose to print few metrics in CSV format which can be either imbibed by excel for further reporting / charting or injected to MySQL DB to do many stuffs…

Here is the sample + cropped output for reference. In the below table timestamp (column B) is in epoch format which is converted to MYSQL datetime format via INSERT query

2016-11-04-22_25_58-book1-excel

 

P.S: I’ve changed VMAX serial number for various factors🙂

If interested, please reach out to me to get these Python scripts.

Image Courtesy: https://www.emc.com

References: https://github.com/mcowger/randompython/blob/master/symmREST.py

Thanks for stopping by… Please leave your comments / suggestions.

EMC VMAX Storage Automated Capacity Report

EMC VMAX3

Storage capacity reporting has become a tedious task these days unless we don’t have tools in place. These tools are not as cheap we can think of. There are additional layers added to it to hype and increase its value in market. But if customer ask a storage admin to run the report manually then its a nightmare for the admins.

We always want to go by the easy way which means GUI / EMC Unisphere but for reporting this doesn’t helps to customize and fulfill the customer requirements . EMC SYMCLI / Solutions Enabler can be used effectively here to address this issue. We can use SYMCLI to automate these manual reports. By means of automation, we can ensure quality, timely and error-free reports are generated which can be scheduled via CRON or Tasks Scheduler to send it directly to the stakeholders. SYMCLI built-in supports XML formatted output of the commands which would become easier to parse the information using XML supported programming language.

I’ve developed PERL scripts to generate Storage Pool capacity and Disk capacity report for multiple VMAX storage arrays which are managed from the SYMCLI / Solutions Enabler server. Following are the benefits of using this script.
1. Generates Pool capacity report for all storage arrays which has columns – SYM ID,MODEL,TOTAL USABLE POOL CAPACITY TB,TOTAL USED POOL CAPACITY TB,TOTAL FREE POOL CAPACITY TB, TOTAL POOL UTILIZATION %, TOTAL POOL SUBSCRIBED %
2. Generates Disk capacity report for all storage arrays which has columns – SYM ID,MODEL,TOTAL EFD,TOTAL FC_SAS,TOTAL SATA_NLSAS,FORMATTED EFD CAPACITY GB,FORMATTED FC_SAS CAPACITY GB,FORMATTED SATA_NLSAS CAPACITY GB, TOTAL FORMATTED CAPACITY GB, UNFORMATTED EFD CAPACITY GB,UNFORMATTED FC_SAS CAPACITY GB,UNFORMATTED SATA_NLSAS CAPACITY GB,TOTAL UNCONFIGURED CAPACITY GB,TOTAL UNFORMATTED CAPACITY GB

Output generated from the scripts will be in the form of CSV format. This is information can be plugged into desired format and generate reports with Pivots, charts etc…

Please find below  sample output for reference.

Pool Capacity Report:

2016-02-17 21_21_57-Book1 - Excel

Disk Capacity Report:

2016-02-17 21_22_41-Book1 - Excel

P.S: I’ve removed VMAX serial number for various factors 🙂

If interested, please reach out to me to get these PERL scripts.

Image Courtesy: http://www.storagereview.com and http://www.emc.com