Python for Excel by Felix Zumstein -- Read -- Imperial Library of Trantor

Index

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

I. Introduction to Python 1. Why Python for Excel?

Excel Is a Programming Language

Excel in the News Programming Best Practices

Separation of concerns DRY principle Testing Version control

Modern Excel

Power Query and Power Pivot Power BI

Python for Excel

Readability and Maintainability Standard Library and Package Manager Scientific Computing Modern Language Features Cross-Platform Compatibility

Conclusion

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

3. Getting Started with Python

Data Types

Objects

Variables Functions Attributes and methods

Numeric Types

Mathematical operators

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

Defining functions Calling functions

Modules and the import Statement The datetime Class

PEP 8: Style Guide for Python Code

PEP 8 and VS Code Type Hints

Conclusion

II. Introduction to pandas 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

5. Data Analysis with pandas

DataFrame and Series

Index Columns

Data Manipulation

Selecting Data

Selecting by label Selecting by position Selecting by boolean indexing Selecting by using a MultiIndex

Setting Data

Setting data by label or position Setting data by boolean indexing Setting data by replacing values Setting data by adding a new column

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

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

III. Reading and Writing Excel Files Without Excel 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

8. Excel File Manipulation with Reader and Writer Packages

The Reader and Writer Packages

When to Use Which Package The excel.py Module OpenPyXL

Reading with OpenPyXL Writing with OpenPyXL Editing with OpenPyXL

XlsxWriter pyxlsb xlrd, xlwt, and xlutils

Reading with xlrd Writing with xlwt Editing with xlutils

Advanced Reader and Writer Topics

Working with Big Excel Files

Writing with OpenPyXL Writing with XlsxWriter Reading with xlrd Reading with OpenPyXL Reading sheets in parallel

Formatting DataFrames in Excel

Formatting a DataFrame’s index and headers Formatting a DataFrame’s data part

Case Study (Revisited): Excel Reporting

Conclusion

IV. Programming the Excel Application with xlwings 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

Excel charts Pictures: Matplotlib plots Defined names

Case Study (Re-Revisited): Excel Reporting

Advanced xlwings Topics

xlwings Foundations Improving Performance

Minimize cross-application calls Raw values App properties

How to Work Around Missing Functionality

Conclusion

10. Python-Powered Excel Tools

Using Excel as Frontend with xlwings

Excel Add-in Quickstart Command Run Main RunPython Function

RunPython without quickstart command

Deployment

Python Dependency Standalone Workbooks: Getting Rid of the xlwings Add-in Configuration Hierarchy Settings

Conclusion

11. The Python Package Tracker

What We Will Build Core Functionality

Web APIs Databases

The Package Tracker database Database connections SQL queries SQL injection

Exceptions

Application Structure

Frontend Backend Debugging

Conclusion

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

Minimize cross-application calls Using raw values

Caching The Sub Decorator

Conclusion

A. Conda Environments

Create a New Conda Environment Disable Auto Activation

B. Advanced VS Code Functionality

Debugger Jupyter Notebooks in VS Code

Run Jupyter Notebooks Python Scripts with Code Cells

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

← Prev
Back
Next →

← Prev
Back
Next →