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

phpoffice/phpspreadsheet

phpoffice/phpspreadsheet is a successor of phpExcel. We recently developed an automated solution to pull all the backup success rate reports from Dell EMC Data Protection Advisor (DPA). The portal which displays the report was good but not enough to use that information further like sharing the reports, pivoting etc..

After trying several methods, found this library to be very useful. There aren’t many examples available on the internet to retrieve data from MySQL and write to XLSX file. Hope this blog might be helpful who would like to use this library for creating XLSX files. Fear not, it doesn’t throw errors while opening this file in MS Office Excel

Compare entire excel files online!

Vlookup, Countif etc.. are things of the past to do comparison of tables, columns etc in excel..

Say hello to ‘Cloudy Excel‘ an online tool to compare entire excel files by few clicks. Neither we need an expertise in Vlookup nor knowledge in writing formula.

Upload both source file and altered file to the cloudy excel to see all the difference values between the spreadsheets,

Convert XLSX to HTML / MHT

During certain scenarios we want to automate file conversion for sending reports via email. If we need to send emails to external recipients; Macro enabled excel sheets might get blocked on the go.

To overcome such situations, I found a site to convert excel “XLSX” to “XLS”. I would like to thank to owner of the blog and courtesy to use the script.

http://kuzmin.ca/blog/?p=578

But this may not help us since even XLS file extensions with Macros will be blocked by exchange. So I modified script to get HTM/MHT format which can easily be sent via emails without any hiccups.

VB Script is as below

Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(Wscript.Arguments(0))
objExcel.Application.Visible = False
objExcel.Application.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs Wscript.Arguments(1), 45
objExcel.ActiveWorkbook.Close
objExcel.Application.DisplayAlerts = True
objExcel.Application.Quit
WScript.Quit
‘ XLSX->XLS conversion script by Michael Kuzmin
http://kuzmin.ca/blog/?p=578
‘ This is how you can you it:
‘ c:\xlsx2htm.vbs “C:\inputdocument.xlsx” “C:\outputdocument”
‘ file format numnber 57 is for pdf format
’44 is for html
’45 is for mht

‘end of script

Copy above script to notepad and save it as “cc:\xlsx2htm.vbs”

Open command prompt and type

c:\xlsx2htm.vbs “C:\inputdocument.xlsx” “C:\outputdocument

output file will be in “MHT” format.