Python for Excel

Cover
Copyright
Table of Contents
Preface
	Why I Wrote This Book
	Who This Book Is For
	How This Book Is Organized
	Python and Excel Versions
	Conventions Used in This Book
	Using Code Examples
	O’Reilly Online Learning
	How to Contact Us
	Acknowledgments
Part I. Introduction to Python
	Chapter 1. Why Python for Excel?
		Excel Is a Programming Language
			Excel in the News
			Programming Best Practices
			Modern Excel
		Python for Excel
			Readability and Maintainability
			Standard Library and Package Manager
			Scientific Computing
			Modern Language Features
			Cross-Platform Compatibility
		Conclusion
	Chapter 2. Development Environment
		The Anaconda Python Distribution
			Installation
			Anaconda Prompt
			Python REPL: An Interactive Python Session
			Package Managers: Conda and pip
			Conda Environments
		Jupyter Notebooks
			Running Jupyter Notebooks
			Notebook Cells
			Edit vs. Command Mode
			Run Order Matters
			Shutting Down Jupyter Notebooks
		Visual Studio Code
			Installation and Configuration
			Running a Python Script
		Conclusion
	Chapter 3. Getting Started with Python
		Data Types
			Objects
			Numeric Types
			Booleans
			Strings
		Indexing and Slicing
			Indexing
			Slicing
		Data Structures
			Lists
			Dictionaries
			Tuples
			Sets
		Control Flow
			Code Blocks and the pass Statement
			The if Statement and Conditional Expressions
			The for and while Loops
			List, Dictionary, and Set Comprehensions
		Code Organization
			Functions
			Modules and the import Statement
			The datetime Class
		PEP 8: Style Guide for Python Code
			PEP 8 and VS Code
			Type Hints
		Conclusion
Part II. Introduction to pandas
	Chapter 4. NumPy Foundations
		Getting Started with NumPy
			NumPy Array
			Vectorization and Broadcasting
			Universal Functions (ufunc)
		Creating and Manipulating Arrays
			Getting and Setting Array Elements
			Useful Array Constructors
			View vs. Copy
		Conclusion
	Chapter 5. Data Analysis with pandas
		DataFrame and Series
			Index
			Columns
		Data Manipulation
			Selecting Data
			Setting Data
			Missing Data
			Duplicate Data
			Arithmetic Operations
			Working with Text Columns
			Applying a Function
			View vs. Copy
		Combining DataFrames
			Concatenating
			Joining and Merging
		Descriptive Statistics and Data Aggregation
			Descriptive Statistics
			Grouping
			Pivoting and Melting
		Plotting
			Matplotlib
			Plotly
		Importing and Exporting DataFrames
			Exporting CSV Files
			Importing CSV Files
		Conclusion
	Chapter 6. Time Series Analysis with pandas
		DatetimeIndex
			Creating a DatetimeIndex
			Filtering a DatetimeIndex
			Working with Time Zones
		Common Time Series Manipulations
			Shifting and Percentage Changes
			Rebasing and Correlation
			Resampling
			Rolling Windows
		Limitations with pandas
		Conclusion
Part III. Reading and Writing Excel Files Without Excel
	Chapter 7. Excel File Manipulation with pandas
		Case Study: Excel Reporting
		Reading and Writing Excel Files with pandas
			The read_excel Function and ExcelFile Class
			The to_excel Method and ExcelWriter Class
		Limitations When Using pandas with Excel Files
		Conclusion
	Chapter 8. Excel File Manipulation with Reader and Writer Packages
		The Reader and Writer Packages
			When to Use Which Package
			The excel.py Module
			OpenPyXL
			XlsxWriter
			pyxlsb
			xlrd, xlwt, and xlutils
		Advanced Reader and Writer Topics
			Working with Big Excel Files
			Formatting DataFrames in Excel
			Case Study (Revisited): Excel Reporting
		Conclusion
Part IV. Programming the Excel Application with xlwings
	Chapter 9. Excel Automation
		Getting Started with xlwings
			Using Excel as Data Viewer
			The Excel Object Model
			Running VBA Code
		Converters, Options, and Collections
			Working with DataFrames
			Converters and Options
			Charts, Pictures, and Defined Names
			Case Study (Re-Revisited): Excel Reporting
		Advanced xlwings Topics
			xlwings Foundations
			Improving Performance
			How to Work Around Missing Functionality
		Conclusion
	Chapter 10. Python-Powered Excel Tools
		Using Excel as Frontend with xlwings
			Excel Add-in
			Quickstart Command
			Run Main
			RunPython Function
		Deployment
			Python Dependency
			Standalone Workbooks: Getting Rid of the xlwings Add-in
			Configuration Hierarchy
			Settings
		Conclusion
	Chapter 11. The Python Package Tracker
		What We Will Build
		Core Functionality
			Web APIs
			Databases
			Exceptions
		Application Structure
			Frontend
			Backend
			Debugging
		Conclusion
	Chapter 12. User-Defined Functions (UDFs)
		Getting Started with UDFs
			UDF Quickstart
		Case Study: Google Trends
			Introduction to Google Trends
			Working with DataFrames and Dynamic Arrays
			Fetching Data from Google Trends
			Plotting with UDFs
			Debugging UDFs
		Advanced UDF Topics
			Basic Performance Optimization
			Caching
			The Sub Decorator
		Conclusion
Appendix A. Conda Environments
	Create a New Conda Environment
	Disable Auto Activation
Appendix B. Advanced VS Code Functionality
	Debugger
	Jupyter Notebooks in VS Code
		Run Jupyter Notebooks
		Python Scripts with Code Cells
Appendix C. Advanced Python Concepts
	Classes and Objects
	Working with Time-Zone-Aware datetime Objects
	Mutable vs. Immutable Python Objects
		Calling Functions with Mutable Objects as Arguments
		Functions with Mutable Objects as Default Arguments
Index
About the Author
Colophon