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

An intuitive guide for professionals wanting to prepare for the future of Microsoft Excel by building Python in Excel skills and unleashing the power of their data.

A hands-on guide to the foundational Python in Excel skills you’ll need to understand and use this powerful analytics tool, Python in Excel Step-by-Step is for current Excel users interested in expanding their data analysis skillset with Python. Analytics educator and Microsoft Excel MVP David Langer demonstrates how to use Python in Excel, to unlock new analytics capabilities in Excel, and build your foundation for the future of Excel: do-it-yourself (DIY) data science.

The book leverages your existing Excel knowledge to learn the Python foundation you can apply right away. This is the same approach David has used to successfully teach more than 1,000 professionals Python – even if you’ve never written code before. David also includes:

• Targeted coverage of the Python fundamentals required for analytics – learn just what you need fast
• How to use the powerful pandas and plotnine libraries to facilitate data manipulation and visualization using Python in Excel
• A DIY data science roadmap for you to build the skills you need to unleash the power of your data to have more impact at work

Perfect for professionals use Microsoft Excel for data analysis, like marketing managers, financial analysts, and supply chain manager, Python in Excel Step-by-Step is an invaluable new resource for all business professionals who use Excel and want to build skills for Excel’s AI-powered future.