Advanced Hands-On Python: Working with Excel and Spreadsheet Data
2h 46mIntermediate2024-06-21
Authors
Joe Marini
Senior Developer Advocate at Google, Developer
Course details
In this hands-on course, technology industry veteran Joe Marini guides you through a variety of practical ways that you can leverage Python to work more effectively with data from spreadsheets and Excel. Find out how to read and write CSV data, as well as how to convert data to and from CSV. Learn how Python can help you in creating, reading, and writing Excel workbooks. Plus, explore ways you can use Python when you’re working with Excel sheet data and formulas.
Learning objectives
Read and write CSV data.
Convert data from other formats like JSON to CSV and from CSV to other formats.
Create, read, and write Excel sheets.
Work with Excel sheet data.
Work with formulas.
Learning objectives
Read and write CSV data.
Convert data from other formats like JSON to CSV and from CSV to other formats.
Create, read, and write Excel sheets.
Work with Excel sheet data.
Work with formulas.
Skills covered
SpreadsheetsMicrosoft ExcelPythonData AnalysisProgramming LanguagesData ScienceBusiness Analysis and StrategyBusiness Software and ToolsOpen SourceMicrosoftSoftware DevelopmentOne-Off
Concepts
0. Introduction
- 01 - Python and spreadsheets - Made for each other
- 02 - Getting set up
1. Working with CSV Files
- 03 - The CSV format
- 04 - Reading CSV files into an array
- 05 - Reading CSV files into a dictionary
- 06 - Reading CSV files with a filter
- 07 - Writing a CSV file
- 08 - Writing a dictionary as CSV
- 09 - Challenge - Modify CSV content
- 10 - Solution - Modify CSV content
2. Manipulating Excel Files with openpyxl
- 11 - Overview of openpyxl
- 12 - Loading and exploring a workbook
- 13 - Creating a workbook
- 14 - Working with content
- 15 - Styling cells
- 16 - Applying conditional formatting
- 17 - Adding filters
- 18 - Challenge - Split a workbook
- 19 - Solution - Split a workbook
3. Creating Excel Files with XlsxWriter
- 20 - Introduction to XlsxWriter
- 21 - Creating a workbook
- 22 - Formatting worksheet content
- 23 - Creating an Excel table
- 24 - Applying conditional formatting
- 25 - Writing workbook properties
- 26 - Challenge - Split a workbook
- 27 - Solution - Split a workbook
4. Spreadsheets with pandas
- 28 - Introduction to pandas
- 29 - Reading CSV and Excel data
- 30 - Writing CSV and Excel data
- 31 - Exploring DataFrame content
- 32 - Manipulating DataFrame content
- 33 - Challenge - Excel insight
- 34 - Solution - Excel insight
Conclusion
- 35 - Next steps