Table of Contents
CONTENTS
ACKNOWLEDGMENTS XIII
ABOUT THE AUTHOR XV
ABOUT THE TECHNICAL EDITORS XVII
INTRODUCTION XIX
CHAPTER 1: INTRODUCING PYTHON IN EXCEL 1
1.1 Introducing Python in Excel 1
1.2 How Python in Excel Works 2
1.2.1 The Azure Cloud 2
1.2.2 Security 3
1.2.3 Scalability 4
1.3 Why Python in Excel? 5
1.3.1 Reproducible Analytics 5
1.3.2 Advanced Data Visualization 6
1.3.3 Do-it-Yourself (DIY) Data Science 7
1.3.4 Copilot in Excel 10
1.4 Continue Your Learning 11
CHAPTER 2: DATA TYPES 13
2.1 Integers 15
2.1.1 What Are Integers? 15
2.1.2 Working with Integers 15
2.2 Floats 17
2.2.1 What Are Floats? 17
2.2.2 Working with Floats 17
2.2.3 Casting 19
2.3 Strings 19
2.3.1 What Are Strings? 19
2.3.2 Working with Strings 20
2.3.3 Formatting Strings 25
2.4 Booleans 26
2.4.1 What Are Booleans? 26
2.4.2 Checking Equivalence 27
2.4.3 Logical Comparisons 29
2.4.4 Zeros and Ones 29
2.4.5 Logical Operators 30
2.5 Continue Your Learning 31
CHAPTER 3: DATA STRUCTURES 33
3.1 Lists 33
3.1.1 What Are Lists? 35
3.1.2 Writing Lists 35
3.1.3 Nesting Lists 36
3.1.4 Empty Lists 38
3.1.5 Changing Lists 38
3.1.6 Accessing Lists 41
3.2 Dictionaries 41
3.2.1 What Are Dictionaries? 42
3.2.2 Writing Dictionaries 42
3.2.3 Accessing Dictionaries 44
3.2.4 Working with Keys 45
3.2.5 Missing Keys 46
3.2.6 Working with Values 47
3.2.7 Changing Dictionaries 47
3.3 Tuples 49
3.3.1 Writing Tuples 49
3.3.2 Accessing Tuples 50
3.3.3 Tuples Are Immutable 50
3.4 Sets 51
3.4.1 Writing Sets 52
3.4.2 Comparing Sets 52
3.4.3 Changing Sets 54
3.5 Slicing Data 55
3.5.1 Indexing 55
3.5.2 Slicing 56
3.5.3 Striding 59
3.6 Continue Your Learning 59
CHAPTER 4: CONTROL FLOW AND LOOPS 61
4.1 if/else Statements 61
4.1.1 Basic if 62
4.1.2 Adding else 63
4.1.3 Nesting if/else 63
4.1.4 elif 64
4.1.5 Logical Operators 65
4.1.6 Comparison Operators 68
4.2 for Loops 69
4.2.1 What Are for Loops? 70
4.2.2 Writing for Loops 70
4.2.3 Short-circuiting for Loops 72
4.2.4 Exiting for Loops 73
4.3 while Loops 73
4.3.1 Writing while Loops 74
4.3.2 while Loop Gotchas 74
4.3.3 Exiting while Loops 76
4.4 Comprehensions 76
4.4.1 List Comprehensions 77
4.4.2 Dictionary Comprehensions 80
4.5 Continue Your Learning 83
CHAPTER 5: FUNCTIONS 85
5.1 Introducing Functions 85
5.1.1 Defining Functions 86
5.1.2 Keyword Arguments 89
5.1.3 Returning Objects 90
5.1.4 Variable Scope 92
5.1.5 Why Write Your Own Functions? 96
5.2 Lambdas 96
5.2.1 Writing Lambdas 96
5.2.2 Using Lambdas 98
5.3 Continue Your Learning 99
CHAPTER 6: DATA TABLE FUNDAMENTALS 101
6.1 Introducing Pandas 101
6.1.1 AdventureWorks Data Analysis 102
6.1.2 Tables in Microsoft Excel 102
6.1.3 Tables Are Dataframe Objects 103
6.1.4 Columns Are Series Objects 103
6.1.5 Rows Are Series Objects 104
6.2 Loading Data 104
6.2.1 Loading Excel Cell Ranges 105
6.2.2 Loading Excel Tables 107
6.2.3 Loading from Power Query 108
6.3 Exploring Dataframes 109
6.3.1 The info() Method 109
6.3.2 The head() Method 111
6.3.3 The tail() Method 112
6.3.4 The describe() Method 114
6.3.5 Dataframe Indexes 117
6.4 The Workbook So Far 119
6.5 Continue Your Learning 120
CHAPTER 7: WORKING WITH COLUMNS 121
7.1 Exploring Columns 121
7.1.1 Accessing Columns 124
7.1.2 The info() Method 127
7.1.3 The head() and tail() Methods 128
7.1.4 Indexes 128
7.2 Numeric Columns 128
7.2.1 The count() Method 129
7.2.2 The size Attribute 130
7.2.3 The min() and max() Methods 131
7.2.4 The sum() Method 131
7.2.5 The gt() and lt() Methods 131
7.2.6 The mean() and median() Methods 132
7.2.7 The std() Method 134
7.2.8 The describe() Method 134
7.2.9 The value_counts() Method 135
7.2.10 The isna() and fillna() Methods 136
7.3 String Columns 138
7.3.1 The lower () and upper() Methods 139
7.3.2 The .cat() Method 141
7.3.3 The isalpha() Method 141
7.3.4 The startswith() and endswith() Methods 143
7.3.5 The contains() Method 144
7.3.6 The replace() Method 145
7.3.7 The slice() Method 146
7.3.8 The split() Method 146
7.3.9 The len() Method 147
7.3.10 The value_counts() Method 149
7.3.11 The isna() and fillna() Methods 150
7.4 Datetime Columns 151
7.4.1 Datetime Attributes 152
7.4.2 The month_name() and day_name() Methods 153
7.4.3 The is* Attributes 154
7.4.4 Calculating Elapsed Time 155
7.5 The Workbook So Far 158
7.6 Continue Your Learning 160
CHAPTER 8: WORKING WITH DATA TABLES 161
8.1 AdventureWorks Data Analysis 161
8.2 Changing Dataframes 161
8.2.1 Method Chaining 162
8.2.2 The assign() Method 163
8.2.3 Changing Columns with assign() 165
8.2.4 Adding Columns with assign() 166
8.2.5 Data Wrangling with assign() 170
8.2.6 Column Names with Spaces 173
8.3 Filtering Dataframes 174
8.3.1 Python Masks 175
8.3.2 Combining Masks 178
8.3.3 The isin() Method 182
8.3.4 The query() Method 183
8.4 Combining Dataframes 184
8.4.1 The merge() Method 186
8.4.2 Left Joins 186
8.4.3 Inner Joins 190
8.4.4 Additional Column Handling 191
8.5 Pivoting Dataframes 193
8.5.1 Aggregating By One Column 194
8.5.2 Aggregating By Multiple Columns 198
8.5.3 The pivot_table() Method 200
8.6 The Workbook So Far 205
8.7 Continue Your Learning 207
CHAPTER 9: DATA VISUALIZATION 209
9.1 Introducing Plotnine 209
9.1.1 The Grammar of Graphics 210
9.1.2 Coding Patterns 210
9.2 Categorical Visualizations 211
9.2.1 Initial Data Wrangling 212
9.2.2 Bar Charts 214
9.2.3 Proportional Bar Charts 218
9.2.4 Faceted Bar Charts 219
9.2.5 Column Charts 231
9.3 Time Series Visualizations 235
9.3.1 Time Series Data Wrangling 235
9.3.2 Line Charts 237
9.4 The Workbook 243
9.5 Continue Your Learning 243
CHAPTER 10: YOUR DIY DATA SCIENCE ROADMAP 245
10.1 You’ve Got This 245
10.2 The Roadmap 246
10.2.1 Stop #1: Decision Trees 246
10.2.2 Stop #2: Random Forests 247
10.2.3 Stop #3: K-means Clustering 249
10.2.4 Stop #4: DBSCAN Clustering 250
10.2.5 Stop #5: Logistic Regression 252
10.2.6 Stop #6: Linear Regression 253
10.3 AI with Copilot in Excel 254
10.4 Continue Your Learning 260
INDEX 261
