Data Smart

JORDAN GOLDMEIER is an award-winning author in analytics, data science, and data visualization, and 11-time Microsoft MVP winner. Jordan has served analytics solutions for global organizations like NATO, The World Bank and Habitat for Humanity, and Fortune 500 companies likes Principal Financial and H&M. He has taught as an instructor for Wake Forest University, and served as a volunteer Emergency Medical Technician in New York City.

• Introduction xix1 Everything You Ever Needed to Know About Spreadsheets but Were Too Afraid to Ask 1Some Sample Data 2Accessing Quick Descriptive Statistics 3Excel Tables 4Filtering and Sorting 5Table Formatting 7Structured References 7Adding Table Columns 10Lookup Formulas 11VLOOKUP 11INDEX/MATCH 13XLOOKUP 15PivotTables 16Using Array Formulas 19Solving Stuff with Solver 202 Set It and Forget It: An Introduction to Power Query 27What Is Power Query? 27Sample Data 28Starting Power Query 29Filtering Rows 32Removing Columns 33Find & Replace 34Close & Load to Table 353 Naïve Bayes and the Incredible Lightness of Being an Idiot 39The World's Fastest Intro to Probability Theory 39Totaling Conditional Probabilities 40Joint Probability, the Chain Rule, and Independence 40What Happens in a Dependent Situation? 41Bayes Rule 42Separating the Signal and the Noise 43Using the Bayes Rule to Create an AI Model 44High-Level Class Probabilities Are Often Assumed to Be Equal 45A Couple More Odds and Ends 46Let's Get This Excel Party Started 47Cleaning the Data with Power Query 48Splitting on Spaces: Giving Each Word Its Due 50Counting Tokens and Calculating Probabilities 55We Have a Model! Let's Use It 584 Cluster Analysis Part 1: Using K-Means to Segment Your Customer Base 65Dances at Summer Camp 65Getting Real: K-Means Clustering Subscribers in Email Marketing 70The Initial Dataset 71Determining What to Measure 72Start with Four Clusters 75Euclidean Distance: Measuring Distances as the Crow Flies 76Solving for the Cluster Centers 80Making Sense of the Results 82Getting the Top Deals by Cluster 83The Silhouette: A Good Way to Let Different K Values Duke It Out 86How About Five Clusters? 95Solving for Five Clusters 96Getting the Top Deals for All Five Clusters 96Computing the Silhouette for 5-Means Clustering 99K-Medians Clustering and Asymmetric Distance Measurements 100Using K-Medians Clustering 100Getting a More Appropriate Distance Metric 100Putting It All in Excel 102The Top Deals for the 5-Medians Clusters 1045 Cluster Analysis Part II: Network Graphs and Community Detection 109What Is a Network Graph? 110Visualizing a Simple Graph 110Beyond GiGraph and Adjacency Lists 115Building a Graph from the Wholesale Wine Data 117Creating a Cosine Similarity Matrix 118Producing an R-Neighborhood Graph 121Introduction to Gephi 123Creating a Static Adjacency Matrix 124Bringing in Your R-Neighborhood Adjacency Matrix into Gephi 124Node Degree 128Touching the Graph Data 130How Much Is an Edge Worth? Points and Penalties in Graph Modularity 132What's a Point, and What's a Penalty? 133Setting Up the Score Sheet 136Let's Get Clustering! 138Split Number 1 138Split 2: Electric Boogaloo 143And. . .Split3: Split with a Vengeance 145Encoding and Analyzing the Communities 146There and Back Again: A Gephi Tale 1516 Regression: The Granddaddy of Supervised Artificial Intelligence 157Predicting Pregnant Customers at RetailMart Using Linear Regression 158The Feature Set 159Assembling the Training Data 161Creating Dummy Variables 163Let's Bake Our Own Linear Regression 165Linear Regression Statistics: R-Squared, F-Tests, t-Tests 173Making Predictions on Some New Data and Measuring Performance 182Predicting Pregnant Customers at RetailMart Using Logistic Regression 192First You Need a Link Function 192Hooking Up the Logistic Function and Reoptimizing 193Baking an Actual Logistic Regression 1967 Ensemble Models: A Whole Lot of Bad Pizza 203Getting Started Using the Data from Chapter 6 203Bagging: Randomize, Train, Repeat 204Decision Stump is Another Name for a Weak Learner 204Doesn't Seem So Weak to Me! 204You Need More Power! 207Let's Train It 208Evaluating the Bagged Model 220Boosting: If You Get It Wrong, Just Boost and Try Again 223Training the Model—Every Feature Gets a Shot 224Evaluating the Boosted Model 2318 Forecasting: Breathe Easy: You Can't Win 235The Sword Trade Is Hopping 236Getting Acquainted with Time-Series Data 236Starting Slow with Simple Exponential Smoothing 238Setting Up the Simple Exponential Smoothing Forecast 240You Might Have a Trend 249Holt's Trend-Corrected Exponential Smoothing 250Setting Up Holt's Trend-Corrected Smoothing in a Spreadsheet 252So Are You Done? Looking at Autocorrelations 258Multiplicative Holt-Winters Exponential Smoothing 266Setting the Initial Values for Level, Trend, and Seasonality 268Getting Rolling on the Forecast 274And. . .Optimize! 280Putting a Prediction Interval Around the Forecast 283Creating a Fan Chart for Effect 287Forecast Sheets in Excel 2899 Optimization Modeling: Because That "Fresh-Squeezed" Orange Juice Ain't Gonna Blend Itself 293Wait Is This Data Science? 294Starting with a Simple Trade-Off 295Representing the Problem as a Polytope 296Solving by Sliding the Level Set 297The Simplex Method: Rooting Around the Corners 298Working in Excel 300Fresh from the Grove to Your Glass with a Pit Stop Through a Blending Model 305Let's Start with Some Specs 307Coming Back to Consistency 308Putting the Data into Excel 309Setting Up the Problem in Solver 311Lowering Your Standards 314Dead Squirrel Removal: the Minimax Formulation 317If-Then and the "Big M" Constraint 320Multiplying Variables: Cranking Up the Volume to 11,000 324Modeling Risk 330Normally Distributed Data 33110 Outlier Detection: Just Because They're Odd Doesn't Mean They're Unimportant 339Outliers Are (Bad?) People, Too 340The Fascinating Case of Hadlum v Hadlum 340Tukey's Fences 341Applying Tukey's Fences in a Spreadsheet 342The Limitations of This Simple Approach 345Terrible at Nothing, Bad at Everything 346Preparing Data for Graphing 347Creating a Graph 350Getting the k-Nearest Neighbors 351Graph Outlier Detection Method 1: Just Use the Indegree 352Graph Outlier Detection Method 2: Getting Nuanced with k-Distance 355Graph Outlier Detection Method 3: Local Outlier Factors Are Where It's At 35811 Moving on From Spreadsheets 363Getting Up and Running with R 364A Crash Course in R-ing 366Show Me the Numbers! Vector Math and Factoring 367The Best Data Type of Them All: the Dataframe 370How to Ask for Help in R 371It Gets Even Better Beyond Base R 372Doing Some Actual Data Science 374Reading Data into R 374Spherical K-Means on Wine Data in Just a Few Lines 375Building AI Models on the Pregnancy Data 381Forecasting in R 389Looking at Outlier Detection 39312 Conclusion 397Where Am I? What Just Happened? 397Before You Go-Go 397Get to Know the Problem 398We Need More Translators 398Beware the Three-Headed Geek-Monster: Tools, Performance, and Mathematical Perfection 399You Are Not the Most Important Function of Your Organization 401Get Creative and Keep in Touch! 402Index 403