Transforming Excel Analysis into Python and pandas Data Models
Published by Pearson
Reading, writing and automated processing of Excel data with Python tools
Excel has a long and proud history of bringing data analysis and processing to the masses with its intuitive interface and powerful functionality. However, it has important limitations that are only becoming more apparent in our current age of Big Data and that can only be surpassed by upskilling to a more programming-oriented context.
This lecture will introduce you to the ways in which Python and Pandas can be used to build up on your Excel analyses to bring the power of sophisticated Data Science and Machine Learning tools into your pipeline. We’ll also cover how to both read data from and write results to Excel spreadsheets.
What you’ll learn and how you can apply it
- Review the basics of Pandas data frames
- Use Pandas to read and write basic Excel files
- Basic Excel spreadsheet manipulation using python packages
This live event is for you because...
- Users how are running up against Excel limitations
- Want to upskill into Python programming
- Need to read and write Excel spreadsheets
Prerequisites
- Basic understanding of Python 3
- Jupyter
Course Set-up
- Scientific Python distribution, like Anaconda
- xlutils
- openpyxl
- xlrd
- xlwt
- xlsxwriter
- pyexcel
Recommended Preparation
- (Video) Python Fundamentals - https://learning.oreilly.com/videos/python-fundamentals/9780135917411
- (Video) Pandas Data Cleaning and Modeling with Python https://learning.oreilly.com/videos/pandas-data-cleaning/9780135170199
- (Book) Fluent Python, 2nd Edition - https://learning.oreilly.com/library/view/fluent-python-2nd/9781492056348/
Schedule
The time frames are only estimates and may vary according to how the class is progressing.
Segment 1 – Excel Pitfalls Length (45 min)
- Default settings
- worksheet sizes and cross references
- Formatting and styling
- Functions and cell evaluation
Break, Q&A (length: 10 min)
Segment 2 – Pandas Data Frames Length (60 min)
- Importing csv files and Excel spreadsheets
- Data cleaning
- Subsetting
- DataFrame Manipulations
- Merge and Join
- Generating simple Excel spreadsheets
Break, Q&A (length: 10 min)
Segment 3 – Simple Data Modeling Length (60 min)
- Data smoothing
- Pivot tables
- Basic plotting
- Linear regression
- Curve fitting
- Assignment Title, Link, Description (optional)
Break, Q&A (length: 10 min)
Segment 4 – Manipulating Excel Spreadsheets Length (60 min)
- Adding sheets to a workbook
- Reading and formatting Excel files
- Inspecting arbitrary cells
- Modifying specific rows and columns
- Appending a dataframe to an excel sheet
- Assignment Title, Link, Description (optional)
Q&A (length: 10 min)
Your Instructor
Bruno Gonçalves
Bruno Gonçalves is currently a Head of Data Science working at the intersection of AI, Blockchain Technologies, and Finance. Previously, he was a Data Science Fellow at NYU's Center for Data Science while on leave from a tenured faculty position at Aix-Marseille Université. Since the completion of his PhD in the Physics of Complex Systems in 2008, he has pursued the use of Data Science and Machine Learning to the large-scale study of human behavior. In 2015, he was awarded the Complex Systems Society's Junior Scientific Award for "outstanding contributions in Complex Systems Science," and in 2018 he was named a Science Fellow of the Institute for Scientific Interchange in Turin, Italy.