7.1 Extracting Lineup data and Computing raw + -

Loading...

From the course by University of Houston System

Math behind Moneyball

38 ratings

University of Houston System

Math behind Moneyball

38 ratings

Learn how probability, math, and statistics can be used to help baseball, football and basketball teams improve, player and lineup selection as well as in game strategy.

From the lesson

Module 7

You will learn about advanced basketball concepts such as Adjusted plus minus, ESPN’s RPM, SportVu data, and NBA in game decision-making.

7.1 Extracting Lineup data and Computing raw + -16:35

Meet the Instructors

Professor Wayne Winston
Visiting Professor
Bauer College of Business

So the idea of plus-minus comes from hockey, I believe, a long time ago.

How many goals does your team have before the other team by,

let's say per game, okay, excluding power plays?

Well, in basketball it'd simply be when you are on court,

when a player is on the court.

Put this up here.

When a player's on the court,

how many points per game do we outscore the opponent by?

Now, it's not a perfect measure of how good a player is.

My colleague Jeff Sacker and I I think were the first.

We'll sort of modify this and we'll get to that later.

Okay, but, where could we get some data on how lineups play?

Well, I think the easiest site is basketballreference.com.

And if I go to Play Index and

Lineup Finder, this is like the game index, the play index,

the play selection filter that we found on the great site footballreference.com.

So I could say 5-man lineups.

I could say 2-man lineups, but do cumulative season games.

Let's just say regular season, and let's just do the Warriors 2014.

Now teams play like over 500 lineups a season.

But cumulative will just get all the lineups, and

let's suppose I look at lineups that play at least five minutes.

So I can filter on anything, minutes played.

And I can do Warrior versus Cavs.

Whatever I want to.

This is really incredibly useful.

So I say cumulative season games.

I say get results, make sure you check that second box.

All right.

I guess, I thought I had answered survey questions, but I guess I have to.

And I did.

Okay. So you've got a lot of information here.

All I care about is the minutes played by a lineup, and

the points by which they won by.

So it gives the first 100 lineups here, and then you'd have to go to next page,

I think there's 30 more that played at least five minutes.

I guess I've got the answer.

Okay.

Okay.

Five man lineups that would score net points, okay.

I forgot minutes played greater than or equal to five, I'm sorry.

Okay, so we go here, we get the first hundred lineups on minutes played.

And then I'd go to the next page,

I think there's 30 more lineups there if I've got this right.

Okay.

Yeah, okay.

So now how do you download this stuff, I mean, I just select it.

Right-click copy, right-click paste, there used to be a thing called web queries but

it just doesn't work for right now.

Okay, so now I've got the Warriors data in this file, so

the question is what can I do with it?

Okay, I deleted all the columns except minutes played and points, and

the players playing, and so the question is,

I need to parse this stuff and find out each player in each lineup.

So one way to do this is to data text the columns.

So if I select these data here, where each cell contains five players,

Data > Text to Columns > so I say Delimited, Next, and

then I guess I've got that bar character there.

And then I'd say next, and let's put this over here.

That will be fine.

Okay, now I've got the players broken up there, but

now you'll see there's a problem here.

Okay, that's not obvious, but

it happens a lot when you download data from the Internet.

There's a something called character 160 which is invisible.

Okay, and you can see that basically

in the tenth character here I've got character 160.

Here in the first character, I've got character 160, and so like if I type in H,

let's see, if I type in A.Bogut.

because I want to really get the names right.

If I would say does this cell equal that cell it's going to say, I think, false.

And it looks like they're the same, and this can cause real problems.

So a little trick you need to know, I think, in analyzing data when you download

from the Internet, if things don't come out the way you expect,

is how to get rid of that stupid invisible character, which is character 160.

Okay, so let me insert five blank columns here with the final results here.

So say insert five blank columns, okay.

So I'm going to say substitute, there's a function in Excel called substitute,

which I can't spell.

There it is.

So okay, for character 160

I want to substitute a blank, okay?

Substitute, should be in this cell here, if I move that.

In M7, the alt-text is character 160, and then I want a blank.

I think that'll do it.

OK, so now I got Harrison Barnes and I got Andrew Bogut, I hope, so

now I type Andrew Bogut there, if I would say does this cell

equal that cell, it says true, and that's great.

And if I try to find that character 160, the stupid thing is gone.

So I get an error message because there's no character 160.

So now if I copy this stuff down here, I sort of got what I need.

Okay. I've got every player in each lineup.

Okay. So now I can do some math.

I can get plus-minus calculations.

I can even look at how pairs of players do.

And so I probably should put some more blank columns in.

All right, so let's see what happens with this.

Okay. So how do I find a list of the players

down the team?

We could do a pivot table, but we don't have all the players in one column.

So what I'm going to do, I'm just going to put all this stuff in one spreadsheet,

because it listed alphabetically so some players.

So I can have that first column of players and

then if I go back here, I'm just going to try and

put the players here layered on top and

then I can get a unique list of names.

So I go down here And then I'll, oops,

it's gotta be paste special values.

And then if I come down here to the third

column, just take a minute.

Get to the bottom, right-click values.

Then I'll go to the bottom cause remove duplicates, we'll need that later on so

we might as well show it to you, even though it takes a little bit of time.

So, I'd say the forth column, Klay Thompson would

usually be last column because he's last alphabetically.

Okay, and then in the fifth column is the Spanish Civil War,

but the fifth column is out of here.

Okay.

So now if I come up to the top here, write the word names, and

then remove duplicates, if I select all this data, basically Data >

Remove Duplicates, will just leave me with a unique list of names.

The first value you see a person's name.

And there are all your players.

So I can copy those guys back to the data sheet

Okay, and let's insert some more blank columns.

Right, oops sorry.

Right-click > Insert columns.

Okay now I'm not sure.

I may have lost those guys, but I'll find them again.

So I've got the players here.

Okay, so now let's suppose I want to get the plus-minus stats for a given player.

So I could create a drop-down box with a list of these players names if I want.

So in other words, I could do the player's name.

And again you can go Data > Data Validation.

Let's create a list.

And the source is all these guys, so I can pick any player on the Warriors.

Okay.

So let's pick Curry, of course, the great Steph Curry.

The great Riley Curry, if you've been watching the press conferences.

Although maybe some people think we've seen a little too much of Riley.

So, is the player in?

Because if I know the player's in, in other words,

like here I can do a countif on the guy's name, and

if I get a 1 there I'll say he's in, and a 0, then the guy's not in.

And then I could do a sum of product and get the minutes they played.

And you can get their plus-minus, how much they outscored the other team by.

And then I can get a plus-minus per 48.

And I could do it for every player with a data table.

And I could do a lot more with this once I've analyzed this.

So is Steph Curry in?

So I'd say =countif in this range,

That guy, so it was Curry, and yeah he was in.

There are some lineups he's not in, let's see.

Okay, see that lineup doesn't have Curry.

And we'll talk more about analyzing line-ups with guys in and out,

in other words, we can see we can do a lot of interesting calculations here.

So now if I want to do the minutes, okay, now I guess I could use range names here.

Let me just say, let's call this player 1 to make it simpler.

Player 2.

So let me just name all of these columns here.

Okay.

So remember Formulas > Create from Selection.

Names in top row.

Okay, so the minutes, whenever there's a 1 here I should pick up Curry's minutes.

And these are lineups that only played five minutes or more.

I just don't want to deal with those lineups that didn't play much.

So by sum of product, okay.

Minutes played, with, I guess, F3 key.

Is the player in?

Okay, so that's how many minutes you're, I've got 2400 minutes of these lineups.

About 30 minutes per game that Curry was in with lineups that played at least five

minutes per game.

Now the plus-minus, you do a sum of product of ones and

zeros here with the points.

So I would have, here's the points.

And here's the player again.

Okay, so they won by 920 points in the 2,419 minutes Curry was in.

So per minute they won by this many points.

Plus-minus divided by minutes, and per 48 you'd make it this.

So when Curry was in they won by 18 points per game, which is pretty good.

So now I could get, now let's do a data table.

So I could put that formula here.

Okay.

And now if I just do a one-way data table that we learned about,

Data > What-if Analysis > Data table,

the column int would cell just throw those names into this cell.

I should get the plus-minus for every guy there.

Now they're all the same, I gotta hit F9.

Remember to change them, okay.

And actually I could do the minutes played, that would be nice.

So how, if a guy didn't play many minutes, I don't really care that much, so

I could put minutes played here, and I think I've got to change my data table.

Data > What-if Analysis > Data Table.

Plug this in here.

Gotta hit F9 to change that again, oops, I got the wrong,

okay so I2 Okay, see I screwed up there.

Because I made the input cell be G2, the input cell should be F2.

So let's start over there, it's okay to mess up, we all do.

So the plus-minus would be here.

And the minutes as an output cell would be here.

And now, when I do the data table, Data > What-if Analysis > Data Table,

the column input cell should be x, and that's where I screwed up.

Okay, I hit F9, and it should all change.

There we go.

Okay, so we've got for 48, oh this should be I2.

Got the wrong cell for plus-minus from 48.

Good to see mistakes cause then you can change things.

So if I go What-if Analysis again, Data Table,

the column input cell would be the guy's name.

Again, I gotta hit those F9s again.

Alll right that's right, so I've got the plus-minus,

I've got how many minutes the guy played, so Draymond Green was in

23 minutes, they won by 18 points, whereas Klay Thompson,

sorry where's Klay Thompson, the other Splash Brother?

They won by 16.

Now you can see when Livingston's in, they're just not near as good and

that I think is their Achilles heel.

Barbosa, they're not as good.

So when the Splash Brothers are out, there are issues.

So the key thing is learning how to substitute when players interact, and

we'll talk about that I think, hopefully in a later video.

Explore our Catalog

Join for free and get personalized recommendations, updates and offers.

Coursera provides universal access to the world’s best education, partnering with top universities and organizations to offer courses online.

© 2018 Coursera Inc. All rights reserved.

Download on the App Store

Get it on Google Play