Automate Excel with Python From Manual Grind to One-Click Workflow (John Wengler)（Z-Library）

AUTOMATE EXCEL WITH PYTHON From Manual Grind to One-Click Workflow by John Wengler no starch press® San Francisco

AUTOMATE EXCEL WITH PYTHON. Copyright © 2026 by John Wengler. All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher. First printing 30 29 28 27 26 1 2 3 4 5 ISBN-13: 978-1-7185-0464-6 (print) ISBN-13: 978-1-7185-0465-3 (ebook) Published by No Starch Press®, Inc. 245 8th Street, San Francisco, CA 94103 phone: +1.415.863.9900 www.nostarch.com; info@nostarch.com Publisher: William Pollock Managing Editor: Jill Franklin Production Manager: Sabrina Plomitallo-González Production Editor: Jennifer Kepler Developmental Editor: Rachel Monaghan Cover Illustrator: Rob Fiore Interior Design: Octopod Studios Technical Reviewer: Daniel Zingaro Copyeditor: Ryan E. Holman Proofreader: Michael Fedison Indexer: BIM Creatives, LLC Library of Congress Control Number: 2026001077 For customer service inquiries, please contact info@nostarch.com. For information on distribution, bulk sales, corporate sales, or translations: sales@nostarch.com. For permission to translate this work: rights@nostarch.com. To report counterfeit copies or piracy: counterfeit@nostarch.com. The authorized representative in the EU for product safety and compliance is EU Compliance Partner, Pärnu mnt. 139b-14, 11317 Tallinn, Estonia, hello@eucompliancepartner.com, +3375690241. No Starch Press and the No Starch Press iron logo are registered trademarks of No Starch Press, Inc. Other product and company names mentioned herein may be the trademarks of their respective owners. Rather than use a trademark symbol with every occurrence of a trademarked

name, we are using the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The information in this book is distributed on an “As Is” basis, without warranty. While every precaution has been taken in the preparation of this work, neither the author nor No Starch Press, Inc. shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in it.

For Dragana, naturally.

About the Author John Wengler enjoys a Mark Twain–like career spanning journalism, community planning, risk management, and compliance. He taught himself Python to upgrade a spreadsheet process to help an employer save millions on a third-party system. John wrote the 2001 book Managing Energy Risk, as well as dozens of industry articles on corporate governance. A distinguished public speaker, he has taught courses at the Illinois Institute of Technology and Tulane University. John helped create and write the Energy Risk Professional certification program for the Global Association of Risk Professionals. He is also a member of Anaconda’s Customer Advocacy Program. Prior to learning Python, John previously attempted coding as an undergrad at the University of Wisconsin, using punch cards in the basement mainframe neon dungeon of the civil engineering building. He somehow managed to avoid any and all programming when achieving a master’s degree in urban planning at the University of Illinois Chicago and an MBA from Northwestern University’s Kellogg School. John’s hobbies include writing, studying history, and spending time in the outdoors. Unlike Mark Twain, however, he has yet to captain a riverboat.

About the Technical Reviewer Daniel Zingaro is a teaching professor at the University of Toronto. He co-directs the GenAI in CS Education Consortium (https://www.teachcswithai.org), aimed at helping faculty and institutions integrate generative AI into the computer science curriculum. He is the author of Algorithmic Thinking, 2nd edition (No Starch Press, 2023), and Learn to Code by Solving Problems (No Starch Press, 2021) and co-author of Learn AI-Assisted Python Programming (Manning, 2023). Visit https://www.danielzingaro.com for more information on these and his other programming books.

BRIEF CONTENTS Acknowledgments Introduction PART I: FROM SPREADSHEETS TO DATAFRAMES Chapter 1: Getting Started with Python Chapter 2: Displaying Data and Understanding Data Types Chapter 3: Creating and Manipulating Dataframes and Lists Chapter 4: Adding, Modifying, and Calculating Column Data Chapter 5: Accessing and Transforming Individual Cell Values Chapter 6: Filtering and Displaying Dataframes PART II: TOOLS TO REPLICATE EXCEL FUNCTIONALITY Chapter 7: Counting and Summing Values Chapter 8: Merging Dataframes Chapter 9: Formatting and Calculating Dates and Times PART III: WORKFLOW TECHNIQUES Chapter 10: Reading Excel Files into Dataframes Chapter 11: Saving Dataframes to Excel Chapter 12: There and Back Again: An Excel–Python–Excel Workflow Appendix A: Working with Folders, Files, and Pathnames Appendix B: Cleaning Up a Messy Spreadsheet Appendix C: The Ducks Module Python Quick Reference Index

CONTENTS IN DETAIL ACKNOWLEDGMENTS INTRODUCTION Why This Book? Why Python? How This Book Is Organized Dataframes, Your New Best Friends PART I: FROM SPREADSHEETS TO DATAFRAMES 1 GETTING STARTED WITH PYTHON Technical Considerations for Working with Python Python Version Distribution Platform Integrated Development Environment The IDLE Shell: A Simple Interface Spyder: A More Robust Working Environment Summary 2 DISPLAYING DATA AND UNDERSTANDING DATA TYPES Printing String Variables and Strings of Strings Data Types Printing Numbers and Numeric Variables Concatenating Different Data Types Learning from Your Mistakes Customizing Functionality with Parameters

Printing an Empty Line Escape Characters and Escape Sequences Potential Pitfalls for Python Novices Using Quotation Marks Inconsistently Ignoring Case Sensitivity Not Knowing Your Data Summary 3 CREATING AND MANIPULATING DATAFRAMES AND LISTS What Exactly Is a Dataframe? How to Create a Dataframe Importing the pandas Module and Manually Creating a Dataframe Copying a Dataframe Subsetting a Dataframe by Column Common Dataframe Operations Counting Rows Using the len() Function Counting Rows and Columns with the shape Attribute Deleting Rows with a Specific Value Identifying and Dropping Duplicated Rows Concatenating Dataframes Lists: The DNA of Dataframes Creating a List Isolating Unique List Values Appending a Single Item to a List Adding a List to a List Sorting a List Identifying Minimum, Maximum, and Mean List Values Removing a List Element Comparing Lists Creating an Empty Dataframe with a List

Converting a Column into a List Another Fragment of Dataframe DNA: The Series Object Summary 4 ADDING, MODIFYING, AND CALCULATING COLUMN DATA Defining a Column Changing How Columns Appear in a Dataframe Reordering Columns in a Dataframe Dropping Columns Renaming Columns Sorting Dataframes by Column Values Printing Select Columns Changing Values in Columns Overwriting All Column Values Replacing Particular Values in Dataframes and Target Columns Replacing Substrings Creating New Columns Adding a New Column with a Single Value Duplicating a Column Concatenating Two or More Columns into a New One Math Methods and Operators Applying the sum() Method to a Dataframe Column Returning Average, Maximum, or Minimum Values Calculating Median Values Rounding a Single Column or Full Dataframe Converting to Absolute Values Stringing Together Multiple Methods Storing Calculated Values in a New Column Comparing Values Conditional Logic

Controlling Execution Flow with if Logic Iterating Through a List Repeating a Process with the while Loop Iterating Through a Dataframe Replicating SUMIF with the where() Conditional Statement Storing Values Returned from iterrows() in a New Column Storing Conditional Results in a New Column with List Comprehensions Handling Exceptions with a try-except Block Summary 5 ACCESSING AND TRANSFORMING INDIVIDUAL CELL VALUES An Overview of Values and Variables Converting Integers, Floats, and Strings Converting Individual Variables Converting an Entire Dataframe Column Converting Objects to Strings Manually Inserting Values with the input() Function Answering a Question and Saving the Answer Selecting a Menu Option Pausing Execution to Review Output Working with NaN Objects Manually Creating NaN Objects Manually Entering NaN Objects into a Dataframe Replacing NaN Objects Slicing Techniques for Strings and Lists Slicing a Single String Slicing Within an Entire Column Slicing a List from a List Pulling a Select Element from a List Indexing Techniques for Dataframes and Series Objects

Pulling Values by Index Position Pulling Values by Unique Index Label Targeting Single Values Pulling a Value from a Series Modifying Existing Cells Splitting Techniques Splitting a Single String Handling Inconsistent Delimiters Splitting Columns in a Dataframe Summary 6 FILTERING AND DISPLAYING DATAFRAMES A Closer Look at the DataFrame() Method Using Optional Arguments Creating an Empty Dataframe with a Column List Working with Dataframe Indexes Naming and Renaming an Index Renumbering or Resetting an Index Sorting by the Index Moving Values Between the Index and a Column Subsetting Dataframes By Column List By Relative Index Location By Index Label By Matching Values in Columns By Excluding Values in Columns By a Substring Value By Column Labels in a List By Inclusion By Exclusion By Mathematical Condition By NaN Objects

Controlling the Appearance of Dataframe Output Printing the First or Last Few Rows Printing Specific Rows Printing the Rightmost Columns Customizing Global Display Settings Working with Dictionary Objects Declaring a Single Dictionary Declaring a List of Dictionaries Accessing and Modifying Dictionary Contents Creating New Dictionary Key-Value Pairs on the Fly Storing Dictionaries in Dataframes Summary PART II: TOOLS TO REPLICATE EXCEL FUNCTIONALITY 7 COUNTING AND SUMMING VALUES The value_counts() Method Counting Every Value in a Column Counting Specific Values in a Column Counting a List of Specific Values in a Column Normalizing value_counts() Results The crosstab() Method Creating Basic Cross-Tabulations Performing Math Operations Adding Row and Column Totals Handling Missing Values Normalizing crosstab() Data The pivot_table() Method Breaking Down the Basic Form of pivot_table() Grouping Unique Values Calculating Math Values Handling NaN Objects

Organizing Dataframe Values Summary 8 MERGING DATAFRAMES The Basics of VLOOKUP and merge() How merge() Handles Orphaned Keys The Full merge() Method Syntax Specifying Join Type Analyzing Merge Results Defining Keys Handling Different Key Column Labels Checking Your Match Expectations Quality Control with the shape Attribute Summary 9 FORMATTING AND CALCULATING DATES AND TIMES Introducing the Datetime Module and datetime.now() Creating Datetime Objects Isolating Units of Time as Integers Converting Datetime Objects to Strings Transforming Timestamps Converting a Single Datetime Object to a Custom-Formatted String Isolating Units of Time as Strings Removing Leading Zeros from Single-Digit Time Elements Working with Time Durations: The Timedelta Object Comparing and Calculating Dates and Times Datetime Objects in Dataframes Dataframe Datetime Operations Using Directives to Customize to_datetime() Results Calculating Timedeltas in a New Column

Subsetting a Dataframe Using Datetime Objects Summary PART III: WORKFLOW TECHNIQUES 10 READING EXCEL FILES INTO DATAFRAMES Creating or Downloading Your Excel Spreadsheet Introducing the read_excel() Method Importing a Specific Tab from a Workbook Importing All Tabs at Once Filtering Source Data Parsing Input Spreadsheets Dealing with More Complex Spreadsheets Setting the Column Labels for Your Dataframe Setting an Excel Column as the Dataframe Index Handling Hard Returns in Excel Data Reading in a CSV File Summary 11 SAVING DATAFRAMES TO EXCEL Simple Single-Tab Export Exporting a More Complex Dataframe Designating the Tab Name Excluding the Dataframe Index Freezing the View The Six Steps to Exporting and Formatting Excel Files Step 1: Creating a Writer Object and Excel File Step 2: Adding Multiple Dataframes to One Excel Workbook Step 3: Closing the Writer Object and Excel File Step 4: Creating and Populating the Workbook Object and Excel File

Step 5: Formatting the Excel File Step 6: Closing the Workbook Object and Excel File Emailing from Python Sending a Basic Email Converting Dataframes to HTML Code Sending an Email Containing HTML Summary 12 THERE AND BACK AGAIN: AN EXCEL–PYTHON–EXCEL WORKFLOW The Scenario Analyzing the Vet’s Workbook Flowcharting the Manual Process Coding with Modularity Writing and Calling UDFs Defining Required and Optional Parameters Returning One or More Values Saving UDFs in a Separate Script Writing Your First Script: Rolling Over a File Importing Your Favorite Modules Importing the Ducks UDFs Printing a Header Setting Your Dataframe Display Preferences Setting the Date Creating a New File for Today’s Date Automating Exception Reports and Data Management Tasks Importing the Vet’s Workbook Tabs Generating and Sending an Email Automating the Overdue Invoice Process Filtering Today’s Appointments Pausing the Program’s Execution Automating Daily Appointment Email

Recording New Appointments Exporting Dataframes to Excel Tabs Updating and Formatting the Excel File in Spyder Automating Updates in Excel Files Analyzing Trends with Dynamic Pivot Tables Using Spyder as Your Daily Workflow GUI Summary A WORKING WITH FOLDERS, FILES, AND PATHNAMES Pathnames in File Explorer vs. Python Viewing and Changing Your Working Directory Listing the Contents of a Folder Creating a New Directory Checking Whether a Pathname Exists Copying a File Renaming or Moving a File or Folder Deleting a File Deleting Folders File Management Quick Reference B CLEANING UP A MESSY SPREADSHEET Reading a Messy Spreadsheet into a Dataframe Customizing Column Labels Specifying a Column as the Index Dropping a Record by Index Label Sorting by Index Label Splitting Columns: Converting Full Names to First and Last Renaming Columns Reordering Columns Changing a Specific Cell Value Converting Strings to Datetime Objects

Exporting the Cleaned-Up Dataframe Back to Excel Formatting the Clean Excel File The Final Product C THE DUCKS MODULE PYTHON QUICK REFERENCE INDEX