Data Science gets thrown around in the press like it's
magic. Major retailers are predicting everything from when their
customers are pregnant to when they want a new pair of Chuck
Taylors. It's a brave new world where seemingly meaningless data
can be transformed into valuable insight to drive smart business
decisions.

But how does one exactly do data science? Do you have to hire
one of these priests of the dark arts, the "data scientist," to
extract this gold from your data? Nope.

Data science is little more than using straight-forward steps to
process raw data into actionable insight. And in Data
Smart, author and data scientist John Foreman will show you how
that's done within the familiar environment of a
spreadsheet.

Why a spreadsheet? It's comfortable! You get to look at the data
every step of the way, building confidence as you learn the tricks
of the trade. Plus, spreadsheets are a vendor-neutral place to
learn data science without the hype.

But don't let the Excel sheets fool you. This is a book for
those serious about learning the analytic techniques, the math and
the magic, behind big data.

Each chapter will cover a different technique in a
spreadsheet so you can follow along:

* Mathematical optimization, including non-linear programming and
genetic algorithms

* Clustering via k-means, spherical k-means, and graph
modularity

* Data mining in graphs, such as outlier detection

* Supervised AI through logistic regression, ensemble models, and
bag-of-words models

* Forecasting, seasonal adjustments, and prediction intervals
through monte carlo simulation

* Moving from spreadsheets into the R programming language

You get your hands dirty as you work alongside John through each
technique. But never fear, the topics are readily applicable and
the author laces humor throughout. You'll even learn
what a dead squirrel has to do with optimization modeling, which
you no doubt are dying to know.

Data Science gets thrown around in the press like it's magic. Major retailers are predicting everything from when their customers are pregnant to when they want a new pair of Chuck Taylors. It's a brave new world where seemingly meaningless data can be transformed into valuable insight to drive smart business decisions.

But how does one exactly do data science? Do you have to hire one of these priests of the dark arts, the "data scientist," to extract this gold from your data? Nope.

Data science is little more than using straight-forward steps to process raw data into actionable insight. And in Data Smart, author and data scientist John Foreman will show you how that's done within the familiar environment of a spreadsheet.

Why a spreadsheet? It's comfortable! You get to look at the data every step of the way, building confidence as you learn the tricks of the trade. Plus, spreadsheets are a vendor-neutral place to learn data science without the hype.

But don't let the Excel sheets fool you. This is a book for those serious about learning the analytic techniques, the math and the magic, behind big data.

Each chapter will cover a different technique in a spreadsheet so you can follow along:

• Mathematical optimization, including non-linear programming and genetic algorithms
• Clustering via k-means, spherical k-means, and graph modularity
• Data mining in graphs, such as outlier detection
• Supervised AI through logistic regression, ensemble models, and bag-of-words models
• Forecasting, seasonal adjustments, and prediction intervals through monte carlo simulation
• Moving from spreadsheets into the R programming language

You get your hands dirty as you work alongside John through each technique. But never fear, the topics are readily applicable and the author laces humor throughout. You'll even learn what a dead squirrel has to do with optimization modeling, which you no doubt are dying to know.

Introduction xiii

1 Everything You Ever Needed to Know about Spreadsheets but Were Too Afraid to Ask 1

Some Sample Data 2

Moving Quickly with the Control Button 2

Copying Formulas and Data Quickly 4

Formatting Cells 5

Paste Special Values 7

Inserting Charts 8

Locating the Find and Replace Menus 9

Formulas for Locating and Pulling Values 10

Using VLOOKUP to Merge Data 12

Filtering and Sorting 13

Using PivotTables 16

Using Array Formulas 19

Solving Stuff with Solver 20

OpenSolver: I Wish We Didn't Need This, but We Do 26

Wrapping Up 27

2 Cluster Analysis Part I: Using K-Means to Segment Your Customer Base 29

Girls Dance with Girls, Boys Scratch Their Elbows 30

Getting Real: K-Means Clustering Subscribers in E-mail Marketing 35

Joey Bag O' Donuts Wholesale Wine Emporium 36

The Initial Dataset 36

Determining What to Measure 38

Start with Four Clusters 41

Euclidean Distance: Measuring Distances as the Crow Flies 41

Distances and Cluster Assignments for Everybody! 44

Solving for the Cluster Centers 46

Making Sense of the Results 49

Getting the Top Deals by Cluster 50

The Silhouette: A Good Way to Let Different K Values Duke It Out 53

How about Five Clusters? 60

Solving for Five Clusters 60

Getting the Top Deals for All Five Clusters 61

Computing the Silhouette for 5-Means Clustering 64

K-Medians Clustering and Asymmetric Distance Measurements 66

Using K-Medians Clustering 66

Getting a More Appropriate Distance Metric 67

Putting It All in Excel 69

The Top Deals for the 5-Medians Clusters 70

Wrapping Up 75

3 Naïve Bayes and the Incredible Lightness of Being an Idiot 77

When You Name a Product Mandrill, You're Going to Get Some Signal and Some Noise 77

The World's Fastest Intro to Probability Theory 79

Totaling Conditional Probabilities 80

Joint Probability, the Chain Rule, and Independence 80

What Happens in a Dependent Situation? 81

Bayes Rule 82

Using Bayes Rule to Create an AI Model 83

High-Level Class Probabilities Are Often Assumed to Be Equal 84

A Couple More Odds and Ends 85

Let's Get This Excel Party Started 87

Removing Extraneous Punctuation 87

Splitting on Spaces 88

Counting Tokens and Calculating Probabilities 92

And We Have a Model! Let's Use It 94

Wrapping Up 98

4 Optimization Modeling: Because That Fresh Squeezed Orange Juice Ain't Gonna Blend Itself 101

Why Should Data Scientists Know Optimization? 102

Starting with a Simple Trade-Off f 103

Representing the Problem as a Polytope 103

Solving by Sliding the Level Set 105

The Simplex Method: Rooting around the Corners 106

Working in Excel 108

There's a Monster at the End of This Chapter 117

Fresh from the Grove to Your Glasswith a Pit Stop Through a Blending Model 118

You Use a Blending Model 119

Let's Start with Some Specs 119

Coming Back to Consistency 121

Putting the Data into Excel 121

Setting Up the Problem in Solver 124

Lowering…