The Fated and the Damned

A straightforward and engaging approach to data science that skips the jargon and focuses on the essentials In the newly revised second edition of Data Smart: Using Data Science to Transform Information into Insight, accomplished data scientist and speaker Jordan Goldmeier delivers an approachable and conversational approach to data science using Microsoft Excel’s easily understood features. The author also walks readers through the fundamentals of statistics, machine learning and powerful artificial intelligence concepts, focusing on how to learn by doing. You’ll also find: • Four-color data visualizations that highlight and illustrate the concepts discussed in the book • Tutorials explaining complicated data science using just Microsoft Excel • How to take what you’ve learned and apply it to everyday problems at work and life A must-read guide to data science for every day, non-technical professionals, Data Smart will earn a place on the bookshelves of students, analysts, data-driven managers, marketers, consultants, business intelligence analysts, demand forecasters, and revenue managers. Cover Page 1 Title Page 5 Copyright Page 6 About the Author 9 About the Technical Editors 11 Acknowledgments 13 Contents 15 Introduction 21 What Am I Doing Here? 21 What Is Data Science? 22 Do Data Scientists Actually Use Excel? 23 Conventions 25 Let’s Get Going 25 Chapter 1 Everything You Ever Needed to Know About Spreadsheets but Were Too Afraid to Ask 27 Some Sample Data 28 Accessing Quick Descriptive Statistics 29 Excel Tables 30 Filtering and Sorting 31 Table Formatting 33 Structured References 33 Adding Table Columns 36 Lookup Formulas 37 VLOOKUP 37 INDEX/MATCH 39 XLOOKUP 41 PivotTables 42 Using Array Formulas 45 Solving Stuff with Solver 46 Chapter 2 Set It and Forget It: An Introduction to Power Query 53 What Is Power Query? 53 Sample Data 54 Starting Power Query 55 Filtering Rows 58 Removing Columns 59 Find & Replace 60 Close & Load to. . .Table 61 Chapter 3 Naïve Bayes and the Incredible Lightness of Being an Idiot 65 The World’s Fastest Intro to Probability Theory 65 Totaling Conditional Probabilities 66 Joint Probability, the Chain Rule, and Independence 66 What Happens in a Dependent Situation? 67 Bayes Rule 68 Separating the Signal and the Noise 69 Using the Bayes Rule to Create an AI Model 70 High-Level Class Probabilities Are Often Assumed to Be Equal 71 A Couple More Odds and Ends 72 Let’s Get This Excel Party Started 73 Cleaning the Data with Power Query 74 Splitting on Spaces: Giving Each Word Its Due 76 Counting Tokens and Calculating Probabilities 81 We Have a Model! Let’s Use It 84 Chapter 4 Cluster Analysis Part 1: Using K-Means to Segment Your Customer Base 91 Dances at Summer Camp 91 Getting Real: K-Means Clustering Subscribers in Email Marketing 96 The Initial Dataset 97 Determining What to Measure 98 Start with Four Clusters 101 Euclidean Distance: Measuring Distances as the Crow Flies 102 Solving for the Cluster Centers 106 Making Sense of the Results 108 Getting the Top Deals by Cluster 109 The Silhouette: A Good Way to Let Different K Values Duke It Out 112 How About Five Clusters? 121 Solving for Five Clusters 122 Getting the Top Deals for All Five Clusters 122 Computing the Silhouette for 5-Means Clustering 125 K-Medians Clustering and Asymmetric Distance Measurements 126 Using K-Medians Clustering 126 Getting a More Appropriate Distance Metric 126 Putting It All in Excel 128 The Top Deals for the 5-Medians Clusters 130 Chapter 5 Cluster Analysis Part II: Network Graphs and Community Detection 135 What Is a Network Graph? 136 Visualizing a Simple Graph 136 Beyond GiGraph and Adjacency Lists 141 Building a Graph from the Wholesale Wine Data 143 Creating a Cosine Similarity Matrix 144 Producing an R-Neighborhood Graph 147 Introduction to Gephi 149 Creating a Static Adjacency Matrix 150 Bringing in Your R-Neighborhood Adjacency Matrix into Gephi 150 Node Degree 154 Touching the Graph Data 156 How Much Is an Edge Worth? Points and Penalties in Graph Modularity 158 What’s a Point, and What’s a Penalty? 159 Setting Up the Score Sheet 162 Let’s Get Clustering! 164 Split Number 1 164 Split 2: Electric Boogaloo 169 And. . .Split3: Split with a Vengeance 171 Encoding and Analyzing the Communities 172 There and Back Again: A Gephi Tale 177 Chapter 6 Regression: The Granddaddy of Supervised Artificial Intelligence 183 Predicting Pregnant Customers at RetailMart Using Linear Regression 184 The Feature Set 185 Assembling the Training Data 187 Creating Dummy Variables 189 Let’s Bake Our Own Linear Regression 191 Linear Regression Statistics: R-Squared, F-Tests, t-Tests 199 Making Predictions on Some New Data and Measuring Performance 208 Predicting Pregnant Customers at RetailMart Using Logistic Regression 218 First You Need a Link Function 218 Hooking Up the Logistic Function and Reoptimizing 219 Baking an Actual Logistic Regression 222 Chapter 7 Ensemble Models: A Whole Lot of Bad Pizza 229 Getting Started Using the Data from Chapter 6 229 Bagging: Randomize, Train, Repeat 230 Decision Stump is Another Name for a Weak Learner 230 Doesn’t Seem So Weak to Me! 230 You Need More Power! 233 Let’s Train It 234 Evaluating the Bagged Model 246 Boosting: If You Get It Wrong, Just Boost and Try Again 249 Training the Model—Every Feature Gets a Shot 250 Evaluating the Boosted Model 257 Chapter 8 Forecasting: Breathe Easy: You Can’t Win 261 The Sword Trade Is Hopping 262 Getting Acquainted with Time-Series Data 262 Starting Slow with Simple Exponential Smoothing 264 Setting Up the Simple Exponential Smoothing Forecast 266 You Might Have a Trend 275 Holt’s Trend-Corrected Exponential Smoothing 276 Setting Up Holt’s Trend-Corrected Smoothing in a Spreadsheet 278 So Are You Done? Looking at Autocorrelations 284 Multiplicative Holt-Winters Exponential Smoothing 292 Setting the Initial Values for Level, Trend, and Seasonality 294 Getting Rolling on the Forecast 300 And. . .Optimize! 306 Putting a Prediction Interval Around the Forecast 309 Creating a Fan Chart for Effect 313 Forecast Sheets in Excel 315 Chapter 9 Optimization Modeling: Because That “Fresh-Squeezed” Orange Juice Ain’t Gonna Blend Itself 319 Wait. . .Is This Data Science? 320 Starting with a Simple Trade-Off 321 Representing the Problem as a Polytope 322 Solving by Sliding the Level Set 323 The Simplex Method: Rooting Around the Corners 324 Working in Excel 326 Fresh from the Grove to Your Glass. . .with a Pit Stop Through a Blending Model 331 Let’s Start with Some Specs 333 Coming Back to Consistency 334 Putting the Data into Excel 335 Setting Up the Problem in Solver 337 Lowering Your Standards 340 Dead Squirrel Removal: the Minimax Formulation 343 If-Then and the “Big M” Constraint 346 Multiplying Variables: Cranking Up the Volume to 11,000 350 Modeling Risk 356 Normally Distributed Data 357 Chapter 10 Outlier Detection: Just Because They’re Odd Doesn’t Mean They’re Unimportant 365 Outliers Are (Bad?) People, Too 366 The Fascinating Case of Hadlum v. Hadlum 366 Tukey’s Fences 367 Applying Tukey’s Fences in a Spreadsheet 368 The Limitations of This Simple Approach 371 Terrible at Nothing, Bad at Everything 372 Preparing Data for Graphing 373 Creating a Graph 376 Getting the k-Nearest Neighbors 377 Graph Outlier Detection Method 1: Just Use the Indegree 378 Graph Outlier Detection Method 2: Getting Nuanced with k-Distance 381 Graph Outlier Detection Method 3: Local Outlier Factors Are Where It’s At 384 Chapter 11 Moving on From Spreadsheets 389 Getting Up and Running with R 390 A Crash Course in R-ing 392 Show Me the Numbers! Vector Math and Factoring 393 The Best Data Type of Them All: the Dataframe 396 How to Ask for Help in R 397 It Gets Even Better. . .Beyond Base R 398 Doing Some Actual Data Science 400 Reading Data into R 400 Spherical K-Means on Wine Data in Just a Few Lines 401 Building AI Models on the Pregnancy Data 407 Forecasting in R 415 Looking at Outlier Detection 419 Chapter 12 Conclusion 423 Where Am I? What Just Happened? 423 Before You Go-Go 423 Get to Know the Problem 424 We Need More Translators 424 Beware the Three-Headed Geek-Monster: Tools, Performance, and Mathematical Perfection 425 You Are Not the Most Important Function of Your Organization 427 Get Creative and Keep in Touch! 428 Index 429 EULA 445