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