Python in Excel Step-by-Step

Cover....1 Title Page ....3 Copyright ....4 Contents ....7 ACKNOWLEDGMENTS....13 ABOUT THE AUTHOR....13 ABOUT THE TECHNICAL EDITORS....14 INTRODUCTION....15 CHAPTER 1: INTRODUCING PYTHON IN EXCEL....19 1.1 Introducing Python in Excel....19 1.2 How Python in Excel Works....20 1.2.1 The Azure Cloud....20 1.2.2 Security....21 1.2.3 Scalability....22 1.3 Why Python in Excel? ....23 1.3.1 Reproducible Analytics....23 1.3.2 Advanced Data Visualization....24 1.3.3 Do-it-Yourself (DIY) Data Science....25 1.3.4 Copilot in Excel....28 1.4 Continue Your Learning....29 CHAPTER 2: DATA TYPES....31 2.1 Integers....33 2.1.1 What Are Integers? ....33 2.1.2 Working with Integers....33 2.2 Floats....35 2.2.1 What Are Floats? ....35 2.2.2 Working with Floats....35 2.2.3 Casting....37 2.3 Strings....37 2.3.1 What Are Strings? ....37 2.3.2 Working with Strings....38 2.3.3 Formatting Strings....43 2.4 Booleans....44 2.4.1 What Are Booleans? ....44 2.4.2 Checking Equivalence....45 2.4.3 Logical Comparisons....47 2.4.4 Zeros and Ones....47 2.4.5 Logical Operators....48 2.5 Continue Your Learning....49 CHAPTER 3: DATA STRUCTURES....51 3.1 Lists....51 3.1.1 What Are Lists? ....53 3.1.2 Writing Lists....53 3.1.3 Nesting Lists....54 3.1.4 Empty Lists....56 3.1.5 Changing Lists....56 3.1.6 Accessing Lists....59 3.2 Dictionaries....59 3.2.1 What Are Dictionaries? ....60 3.2.2 Writing Dictionaries....60 3.2.3 Accessing Dictionaries....62 3.2.4 Working with Keys....63 3.2.5 Missing Keys....64 3.2.6 Working with Values....65 3.2.7 Changing Dictionaries....65 3.3 Tuples....67 3.3.1 Writing Tuples....67 3.3.2 Accessing Tuples....68 3.3.3 Tuples Are Immutable....68 3.4 Sets....69 3.4.1 Writing Sets....70 3.4.2 Comparing Sets....70 3.4.3 Changing Sets....72 3.5 Slicing Data....73 3.5.1 Indexing....73 3.5.2 Slicing....74 3.5.3 Striding....77 3.6 Continue Your Learning....77 CHAPTER 4: CONTROL FLOW AND LOOPS....79 4.1 ifelse Statements....79 4.1.1 Basic if....80 4.1.2 Adding else....81 4.1.3 Nesting ifelse....81 4.1.4 elif....82 4.1.5 Logical Operators....83 4.1.6 Comparison Operators....86 4.2 for Loops....87 4.2.1 What Are for Loops? ....88 4.2.2 Writing for Loops....88 4.2.3 Short-circuiting for Loops....90 4.2.4 Exiting for Loops....91 4.3 while Loops....91 4.3.1 Writing while Loops....92 4.3.2 while Loop Gotchas....92 4.3.3 Exiting while Loops....94 4.4 Comprehensions....94 4.4.1 List Comprehensions....95 4.4.2 Dictionary Comprehensions....98 4.5 Continue Your Learning....101 CHAPTER 5: FUNCTIONS....103 5.1 Introducing Functions....103 5.1.1 Defining Functions....104 5.1.2 Keyword Arguments....107 5.1.3 Returning Objects....108 5.1.4 Variable Scope....110 5.1.5 Why Write Your Own Functions? ....114 5.2 Lambdas....114 5.2.1 Writing Lambdas....114 5.2.2 Using Lambdas....116 5.3 Continue Your Learning....117 CHAPTER 6: DATA TABLE FUNDAMENTALS....119 6.1 Introducing Pandas....119 6.1.1 AdventureWorks Data Analysis....120 6.1.2 Tables in Microsoft Excel....120 6.1.3 Tables Are Dataframe Objects....121 6.1.4 Columns Are Series Objects....121 6.1.5 Rows Are Series Objects....122 6.2 Loading Data....122 6.2.1 Loading Excel Cell Ranges....123 6.2.2 Loading Excel Tables....125 6.2.3 Loading from Power Query....126 6.3 Exploring Dataframes....127 6.3.1 The info() Method....127 6.3.2 The head() Method....129 6.3.3 The tail() Method....131 6.3.4 The describe() Method....132 6.3.5 Dataframe Indexes....134 6.4 The Workbook So Far....136 6.5 Continue Your Learning....137 CHAPTER 7: WORKING WITH COLUMNS....139 7.1 Exploring Columns....139 7.1.1 Accessing Columns....141 7.1.2 The info() Method....144 7.1.3 The head() and tail() Methods....145 7.1.4 Indexes....145 7.2 Numeric Columns....145 7.2.1 The count() Method....146 7.2.2 The size Attribute....147 7.2.3 The min() and max() Methods....147 7.2.4 The sum() Method....148 7.2.5 The gt() and lt() Methods....148 7.2.6 The mean() and median() Methods....150 7.2.7 The std() Method....150 7.2.8 The describe() Method....151 7.2.9 The value_counts() Method....152 7.2.10 The isna() and fillna() Methods....153 7.3 String Columns....155 7.3.1 The lower() and upper() Methods....155 7.3.2 The cat() Method....157 7.3.3 The isalpha() Method....158 7.3.4 The startswith() and endswith() Methods....159 7.3.5 The contains() Method....159 7.3.6 The replace() Method....161 7.3.7 The slice() Method....162 7.3.8 The split() Method....163 7.3.9 The len() Method....164 7.3.10 The value_counts() Method....165 7.3.11 The isna() and fillna() Methods....166 7.4 Datetime Columns....167 7.4.1 Datetime Attributes....167 7.4.2 The month_name() and day_name() Methods....168 7.4.3 The is Attributes....170 7.4.4 Calculating Elapsed Time....171 7.5 The Workbook So Far....173 7.6 Continue Your Learning....174 CHAPTER 8: WORKING WITH DATA TABLES....177 8.1 AdventureWorks Data Analysis....177 8.2 Changing Dataframes....177 8.2.1 Method Chaining....178 8.2.2 The assign() Method....179 8.2.3 Changing Columns with assign ( ) ....180 8.2.4 Adding Columns with assign ( ) ....182 8.2.5 Data Wrangling with assign ( ) ....185 8.2.6 Column Names with Spaces....188 8.3 Filtering Dataframes....189 8.3.1 Python Masks....190 8.3.2 Combining Masks....192 8.3.3 The isin() Method....196 8.3.4 The query() Method....197 8.4 Combining Dataframes....198 8.4.1 The merge() Method....199 8.4.2 Left Joins....200 8.4.3 Inner Joins....202 8.4.4 Additional Column Handling....204 8.5 Pivoting Dataframes....206 8.5.1 Aggregating by One Column....207 8.5.2 Aggregating by Multiple Columns....210 8.5.3 The pivot_table() Method....212 8.6 The Workbook So Far....217 8.7 Continue Your Learning....218 CHAPTER 9: DATA VISUALIZATION....219 9.1 Introducing Plotnine....219 9.1.1 The Grammar of Graphics....220 9.1.2 Coding Patterns....220 9.2 Categorical Visualizations....221 9.2.1 Initial Data Wrangling....222 9.2.2 Bar Charts....224 9.2.3 Proportional Bar Charts....227 9.2.4 Faceted Bar Charts....229 9.2.5 Column Charts....239 9.3 Time Series Visualizations....243 9.3.1 Time Series Data Wrangling....243 9.3.2 Line Charts....246 9.4 The Workbook....250 9.5 Continue Your Learning....250 CHAPTER 10: YOUR DIY DATA SCIENCE ROADMAP....253 10.1 Youve Got This....253 10.2 The Roadmap....254 10.2.1 Stop 1: Decision Trees....254 10.2.2 Stop 2: Random Forests....255 10.2.3 Stop 3: K-means Clustering....257 10.2.4 Stop 4: DBSCAN Clustering....258 10.2.5 Stop 5: Logistic Regression....260 10.2.6 Stop 6: Linear Regression....261 10.3 AI with Copilot in Excel....262 10.4 Continue Your Learning....267 INDEX....269 EULA....275