Python for Excel Users: Know Excel? You Can Learn Python

About the Author....9 Brief Contents....11 Contents in Detail....13 Introduction....21 Who This Book Is For....22 How Spreadsheets Can Hold You Back....23 Detecting Errors....24 Documenting Your Work....25 Collaborating with Others....26 Integrating Modern AI Tools....27 The Benefits of Using Python vs. Excel....27 Speed....28 Modularity....30 Automation....30 Interoperability....32 Security....33 Alternatives to Python....34 VBA....34 Python in Excel....35 What's in This Book....36 PART I: GETTING STARTED WITH PYTHON....39 Chapter 1: Setting Up Your Coding Environment....41 Why a Well-Configured Installation Matters....41 Using the Command Line....42 Navigating Directories and Subdirectories....43 Manipulating Files....47 Customizing and Storing System Settings....48 Working Within Corporate IT Policies....51 Installing Python....52 Running the Installer....52 Verifying the Installation....54 Running Some Code....55 Getting Started with Python Libraries....56 Tools to Simplify Writing Code....57 Important Features....58 Popular Python Editors....59 VS Code Installation....59 Conclusion....61 Chapter 2: Coding Fundamentals Explained Through Excel....63 What Is Code?....64 Objects and Variables....65 Rules of Python Syntax....66 Case Sensitivity....66 Indentation....66 Comments....67 Statements and Assignments....67 Testing Examples in VS Code....68 Basic Data Types....69 Boolean Variables....69 Integers and Floats....69 Strings and Characters....70 None....72 Operators....72 Arithmetic Operators....72 Comparison Operators....73 Logical Operators....73 Composite Data Types....74 Lists....74 Tuples....75 Dictionaries....76 Functions and Methods....77 Common String Methods....78 Common List Methods....78 Common Dict Methods....79 Conditionals....79 if Statements....80 else Clauses....81 elif Clauses....81 Nested Conditionals....82 Representing Dates and Times....83 Internal Representation of Dates in Excel....83 The datetime Library....84 Conclusion....88 Chapter 3: Automating Tasks witih Python Scripts....89 What Are Scripts?....89 When to Use a Script....90 Parts of a Script....91 Loops....92 for Loops....92 List Comprehensions....94 Loop Controls....95 while Loops....97 User-Defined Functions....97 Arguments....98 Scope....100 Returns....100 Modifier Functions....102 Functionalizing Logic....103 Building and Running a Complete Script....104 Running a Script on the Command Line....107 Importing a Script as a Module....108 Using a Main Block....109 Passing System Arguments....110 Conclusion....111 Chapter 4: Tracking Changes with Version Control....113 Why Use Version Control?....114 What Is Git?....115 Why Git Works Best with Code....115 Git vs. GitHub....116 Working Within Git Repositories....116 Centralizing Your Work in a Single Folder....116 Navigating Local and Remote Repositories....118 Maintaining a .gitignore File....119 Setting Up a Git Repository....120 Installing Git....120 Configuring Git....120 Creating a New Git Repository....121 Tracking Changes to Files....122 Git's Staging Area....122 Commits....123 Syncing Local and Remote Repositories....124 fetch....124 pull....124 push....124 Managing Histories....125 The Main Branch....125 Feature Branches....125 Merges....126 Conflicts....126 Best Practices for Using Git....128 Troubleshooting with Status Messages....129 Integrating Git with VS Code....130 How Git Works....130 Directed Acyclic Graphs....130 Hashing....131 Conclusion....131 PART II: WORKING WITH DATA....133 Chapter 5: Data Analysis Made Interactive....135 What Are Jupyter Notebooks?....136 Code Cells....136 Markdown Cells....137 Raw Text Cells....138 The JupyterLab Code Editor....139 Installing JupyterLab....139 Launching JupyterLab....139 Creating a Jupyter Notebook....140 Closing a Jupyter Notebook....141 Building a Jupyter Notebook....141 Scripts and Notebooks Working Together....143 Version Control with Jupyter Notebooks....145 Conclusion....145 Chapter 6: Analyzing and Transforming Data....147 Efficient Data Analysis with pandas....148 Series and DataFrames....148 pandas and Jupyter Notebooks....149 Getting Started with pandas....149 Creating a DataFrame from Scratch....150 Reading Data from a File into a DataFrame....152 Working with pandas Data Types....153 Moving Between Excel and pandas....154 Using the Clipboard....155 Using openpyxl....156 Manipulating and Transforming Data....156 Accessing Values....156 Updating Values....158 Sorting Datasets....159 Manipulating Data....159 Handling Missing Data....162 Combining DataFrames....163 Aggregating Data....166 Easily Reshaping Data with pandas....167 Grouping....167 Pivoting....168 Conclusion....169 Chapter 7: Working with Databases....171 Why Use a Database?....172 How Databases Work....173 Incorporating Databases into Your Workflow....173 Where Databases Live....173 How to Access Corporate Databases....174 The Costs of a Database Connection....174 Relational Databases and SQL Workflows....175 Database Management Systems....175 Sandbox Environments....176 Creating a Practice Database....176 Installing PostgreSQL....176 Managing Users and Databases....178 Using PostgreSQL in VS Code....179 Creating and Editing Tables....180 Creating a Table....181 Altering a Table's Structure....182 Dropping a Table....183 Adding Rows....183 Updating Records....183 Deleting Data....184 Retrieving Database Data....184 Selections....184 Filters....186 Joins....188 Column Transformations....192 Aggregations....192 Groupings....193 Structuring Complex Queries....194 Automating Database Tasks with Python and SQL....195 Installing Psycopg 2....196 Connecting to PostgreSQL from Python....196 Running a Query from Python....197 Closing the Database Connection....198 Conclusion....198 Chapter 8: Retrieving Data from the Internet....199 What Is an API?....200 REST APIs....200 Other Types of APIs....201 Making Requests and Handling Responses....201 Internet Communication Protocols....201 Request Types....203 Response Types....204 Simplifying Requests with the Requests Library....207 Making an API Request....207 Adding Arguments....208 Building in Error Handling....209 try...except Blocks....210 Common Errors....211 Breaking Large Requests into Smaller Parts....212 Handling Authentication....214 API Keys....215 OAuth....217 Streamlining Your Requests with Reusable Functions....218 Conclusion....221 Chapter 9: Creating Charts and Visuals....223 Charting with Excel vs. Python....224 Integration into a Broader Workflow....224 Reusability....224 Version Control....225 Customization....225 Standardization....225 Charting Libraries in Python....226 Using Plotly in Jupyter Notebooks....227 Installing and Importing Plotly....227 Creating Simple Charts....227 An Example Dataset....228 A Basic Line Chart....229 A Multi-Trace Line Chart....231 A Basic Bar Chart....232 A Multi-Trace Bar Chart....234 Saving Charts as Images....236 Charting Multidimensional Data....236 Elevating Charts with Custom Styling....238 Layout Properties....239 Themes....243 Using Plotly's Interactivity Features....247 Conclusion....248 Chapter 10: Building Interactive Reports....249 Why Use Interactive Reports....249 Creating Interactive Reports with Dash....250 When Interactive Reports Make Sense....250 How to Choose the Right Framework....251 Dash Basics....252 Installing Dash....253 Setting Up a Dashboard....253 Deploying a Dashboard Locally....254 Web Development Concepts and Dash....254 HTML Basics....255 HTML in Dash....257 CSS Basics....259 CSS in Dash....260 Integrating Plotly and Dash....261 Functionalizing Plotly Charts....261 Embedding a Chart in a Dashboard....263 Enabling User Selections....264 Updating Charts with Callbacks....264 Putting It All Together....266 Sharing Your Reports with Others....268 Conclusion....269 PART III: WRITING BETTER CODE....271 Chapter 11: Organizing Your Code with Classes....273 From Rows and Columns to Classes....274 Examples of Classes in Common Python Libraries....275 Custom Classes....275 Programming with Objects to Stay Organized....277 Reusability....281 Extendability....282 Encapsulation....283 Inheritance....287 Conclusion....293 Chapter 12: Finding and Fixing Errors....295 The Art of Debugging....295 Debugging Inside the VS Code Editor....296 Breakpoints....297 The Debug Panel....298 The Debug Console....300 Debugging Code with Errors....301 The Step Out Command....301 The Step Into Command....302 Pausing Code at the Right Moment....303 Debugging with Different Inputs....304 The Process of Testing Code....305 Assertions....306 The unittest Module....307 Identifying Test Cases....309 Conclusion....310 Chapter 13: Three Good Coding Habits....311 Simpler Code Is Better Code....312 Too Many Conditions....312 Mixed Scopes....314 Inconsistency....315 Confusing Variable Names....315 Repetition....316 Making Code Manageable....317 The Right Amount of Modularization....318 Too Much Modularization....319 First Make It Run, Then Make It Better....320 Conclusion....321 Afterword....323 Index....325 Back cover....345