Challenge #18: Predicting Baseball Wins

This week let’s have some fun and look at predicting baseball wins. For those looking for the solution to last week’s (challenge #17) the link is HERE.

The use case: The Baseball season has completed and it's time to project next year's win totals.

The objective: Determine the top 10 variables that correlate to wins (excluding [Win_Pct] and [Games] from the correlation). Leverage those top 10 variables to predict the # of wins the team will have in next year’s season.

Isolate the teams to only use Boston - BOS, Los Angles of Anaheim - LAA, Chicago Cub - CHC, San Francisco Giants - SFG, Colorado Rockies - COL and Texas Rangers - TEX.

Create what the final standing will be and how many games out of first place each team is assuming each team plays 162 games.

Good luck, I hope you are having fun with these exercises and expanding your knowledge of Alteryx. Thanks to all that have provided feedback

challenge_18_start_file.yxzp

challenge_18_solution.yxzp

Join

Preparation

Predictive Analysis

Data Investigation

Intermediate

Transform

Predictive

Expert

Accepted answers

All comments

TaraM

A solution has been posted to this article.

Spoiler

2016-03-28 09_41_48-Alteryx Designer x64 - Predictive_Predicting_Wins_Intermediate_Solution.yxmd_.png

alex

I pretty much had the same solution except I used a multi-row tool instead of Summarize/Append to calculate games behind.

Spoiler

SeanAdams

Pretty much the same solution as @GeneR / @TaraM and @alex. @alex's method of using a join to filter instead of a formula is much more scalable on large data.

Request to @TaraM - some of the terminology in this one caught me - for example "how many games out of first place", which is probably a natural way of describing the question for folks who grew up with baseball.

Would you mind amending the challenge text a little to say something like "what is the difference in projected win-count between the overall winner and each other team" or something similar?

Good challenge - useful to have an excuse to use the predictive tools!

challenge_18_SeanSolution.yxmd

estherb47

Fun!!

challenge_18_EHB_Solution.yxmd

Laurap1228

There are a few different ways to determine the top 10 metrics for predicting wins. I used the Association Analysis tool.

Spoiler

challenge_18_LP.yxmd

LordNeilLord

First time using the "association analysis"....worked well

Spoiler

NicoleJohnson

My solution! I saved this one till last because I didn't read the question all the way (or at all) and for some reason thought it had something to do with drafting a Fantasy Baseball team, and I was hoping to modify it later to help get a leg up on Fantasy Football drafting later this summer... turns out, that was not at all what the challenge was about. Oops. However, dearest Alteryx Challenge-Generating Team, I do believe a Fantasy Football Drafting Challenge would be a lovely idea for an early summer brain teaser... just sayin'...

Anyway. I originally had more or less the same workflow as others, but decided to take a slightly different detour because I don't like having to go in and manually select things (like variables) in my workflows. So I figured out a (rather rambling) path that more or less allowed me to select all variables (except Tm & Wins) in the Linear Regression tool by narrowing it down to just the 10 top variables in the data being used in the tool first (rather than solving for the 10 variables first and then manually picking them from the list of all variables).

(It's the little things in life that bring me joy...)

And on that note... I believe I have officially finished all 70 of the current Weekly Challenges to date!! Race you to home plate (i.e. 75 Challenges), @SeanAdams & @estherb47 !

Spoiler

challenge_18_NicoleJohnson.yxmd

SeanAdams

Game on @NicoleJohnson . Will be interesting to see the action replays of the photo finish for the 75th one.

Any other takers in the community to make this a 4 or 5 way sprint to the finish line?

estherb47

@SeanAdams, @NicoleJohnson, don't know about racing to the finish (I can't always get to the challenges when they're posted) but I will 100% cross the finish line with you!!!

Are you going to Inspire this year? I'd love to meet up in person if you are. I will be there, and cannot wait. Last year was, well, quite inspiring

NicoleJohnson

I will be at Inspire! Let's definitely meet up!! I'll send you a message

DE0413

This was a difficult, but fun challenge. Solution is similar to others, but probably could be condensed.

AlteryxWeeklyChallengeWeek18.yxzp

LordNeilLord

Workflow

challenge_18_LNL.yxzp

ADerbak

I liked this one a lot! Solution attached.

challenge_18_completed_AD.yxmd

jamielaird

Here's my solution. I spotted an anomaly in the dataset in that CHC has 161 games historically but we're asked to assume 162 games for the prediction. I corrected it using a formula tool but it actually makes no difference at all to the scores (even when you look to multiple decimal places). Interesting!

**EDIT: Thinking about this, Games isn't included in the model so why would it make a difference anyway? Eh Jamie? What were you thinking? Anyway, I'll do the right thing and leave this here as a memorial to that one time I got it wrong.**

Spoiler

challenge_18_JL.yxzp

patrick_digan

Spoiler

challenge_18_start_file.yxmd

MaxGiegerich

Tough but a useful push into learning some of the core tools of Alteryx. Score is the one to remember by the looks of things!

Weekly Challenge #18 MG.yxmd

samjohnson

This was a challenge that I burned too many brain cells over, for sure.

UPDATE: I challenged Alteryx and myself to find a better solution than a multivariate linear regression model, so I've included tests of five separate supervised algorithms and used the model comparison tool to select the best model. The winner was the Neural Net using the three principal components. There are advantages to using PCA results with neural nets, as can be found in an answer here: https://stats.stackexchange.com/questions/67986/does-neural-networks-based-classification-need-a-dimension-reduction. The neural net model has a mean absolute error of LT 2, which is both fantastic and raises some skepticism that it's overfitting and even memorizing the data, and the root mean squared error is still 2 1/2x less than the Random Forest model to almost 4x less than any other model.

The team statistics are normally distributed, a plus. But, there are a lot of highly correlated attributes as baseball statistics are a mix of raw compilations and computed statistics; for example, OPS is On-base Percentage (OBP) + Slugging Percentage (Slug). Highly correlated variables create collinearity, which makes regression models freak out by making some variables more important or less important than they should be. There are a number of ways to handle highly correlated predictor variables, but to follow this challenge's directions I highlighted the top 10 variables (which as a data scientist I would not do), and then I noted that those variables created collinearity; for example, Runs and Runs per Game are really the same and highly correlated with one another. I handled this problem by creating principal components, and found that 2 PCs accounted for 93+% of the variance within the data, an incredibly high coverage.

My wins predictions are somewhat different from most other solutions, but I believe this is true because my regression model's R-squared of 0.3726 and adjusted R-squared of 0.3002 indicate the reduced collinearity created by the tightly correlated top 10 variables, where other solutions indicated results of an R-squared of 0.5571 and adjusted R-squared of 0.3241, indicative of an internal collinearity issue due to the adjustment. The model is confused as to which variables are the real indicators of predicted wins.

challenge_18_samjohnson.yxmd

JacobR77

This was new for me! Had a look at the solution to help me along the way.

Challenge 18- Predicting Baseball Wins.yxmd

JORGE4900

Without taking a peak at anyone's work, I selected those ten highly correlated variables and create the linear regression; if you add the Win_Pct to the variables, it basically returns the same amount of wins. In addition of the R squared and adjusted R squared values being very low, almost none of the variable have good Pr values which makes them a low confidence predictors.

But the results match what was requested.

challenge_18_Jorge_Solution.yxmd

JORGE4900

Hello there @samjohnson

I enjoyed looking at your solution; I would have liked to see the macro you mention work, but I do not have installed on my computer. I do like the fact you point out that choosing highly correlated variable may push the model not to correctly predict. It was nice seeing those PCAs in action; thanks for sharing.

JoshKushner

Perhaps an alternative to this correlation analysis could be a multiple regression.

A multiple regression analysis would be done with independent variables that have a correlation coefficient less than 0.7 (as multicollinearity can cause other independent variable to seem statistically insignificant from 0) to find a statistically significant result.

challenge_18_start_file.yxzp

dominiklz

Spoiler

challenge_18_Dominik.yxmd

dsmdavid

Took me some time to figure out how to bring back the 10 selected variables...still not used to transpose/crosstab

Spoiler

SPOILER

solved#18.yxzp

Amarendra

This was a fun one!

challenge_18_start_Amar.yxmd

samN

Wasn't sure what games back meant until i looked at the desired output. Not too bad for first time using predictive tools

challenge_18.yxmd

ggruccio

Love the predictive tools! This was fun.

challenge_18_gg_finish.yxzp

edorlando07

Challenge #18 solved!

It was enlightening to see that you could derive the top 10 correlation coefficients from the Spearman rank correlation and the Pearson correlation outputs.

However, I was not able to get the predictors' p-values derived in the output.

It would be nice if Alteryx gave you the option of viewing different values from the R output provided. Measuring the significance of the predictors is one of the metrics to consider when building a linear prediction model.

challenge_18_start_file_solved2.yxmd

LandonG

Had never used the Score tool before this--I like it!

challenge_18_start_file.yxzp

philipmannering

I know sod all about building and validating regression models - so a very useful challenge. Solution attached.

Spoiler

challenge_18 - Predicting Baseball Wins.yxzp

jasperlch

A good test on Predictive tools.

challenge_18_JL.yxzp

aclaxton

Took longer than I likely needed to to get that dang hyphen...

Spoiler

michalsicak

I tried to make the selection of variables for the linear regression tool dynamic, but only managed to narrow the list down. the clicking is still a manual process.

Spoiler

challenge_18_start_file.yxwz

Natasha

Catching up on the old ones. I was curious to compare that in all three methods top 10 predictor variables are actually the same

Spoiler

challenge_18_Natasha.yxmd

JosephSerpis

Challenge Completed

Challenge_18_Joe_Serpis.yxmd

CaraI

Fun predictive exercise! Straight in line with what most people did.

Spoiler

Spoiler -

challenge_18_CaraI.yxmd

Mike_Hinz

I defiantly need more work on the predictive tools. This exercise was a good challenge.

Spoiler

challenge_18_mhinz.yxmd

steven4320555

I clicked reply from here: https://community.alteryx.com/t5/Weekly-Challenge/Challenge-18-Predicting-Baseball-Wins/m-p/79508/highlight/true#M1683

Spoiler

Hi Jamie,

I was having similar thoughts for a while, about the 161 games 🙂

It turns out the assumption for 162 games is used to calculate the predicted losses from predicted wins.

Steven

But it appeared at the end of the thread.

steven4320555

Wow!!! Such a nice and comprehensive explanation! Is for @samjohnson

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-18-Predicting-Baseball-Wins/m-p/86248/highlight/true#M2104

Goes very well with

Interactive Lessons: Predictive Modeling

https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Predictive%20Modeling

Without domain knowledge, building a practical model can be tough. I guess, this is part of reason why most people just used a linear regression by default.

Thanks!

Steven

SaiKrishna2589

Lot of solutions didn't seem to have considered multi-collinearity in predictor variables (top 10)

for instance R and R_G are highly co-related although they both are significant. It makes sense to use only 1 f these variables to avoid multi-collinearity issue. Similarly, it makes sense to remove 'PA' as well for the same reason.

challenge_18_baseball games wins prediction linear regression.yxzp

gawa

I suppose that correlation strength should be evaluated based on absolute value, not just descending order, although doing so will not match solution. In order to fit output, I have to compromise about it anyway...

Spoiler

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280