Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Data Science

Machine learning & data science for beginners and experts alike.
KuoL
Alteryx Alumni (Retired)

OptimizationIcon.pngThe new Optimization tool has just come out in the Alteryx 10.6 Predictive Release (see Dr Dan's post on prescriptive analytics). With this single tool, we can solve linear programming, mixed integer linear programming and quadratic programming problems. The tool offers 3 input modes (manual input, file input and matrix input) to give you flexibility in defining your model. If you are a baseball fan and Alteryx fan, I have a great news for you! Today I'll show you how to use the Alteryx Optimization tool to solve a fantasy baseball daily lineup problem.

 

 

 

The fantasy baseball daily lineup problem is very straight-forward: given a fixed budget and 6 types of field positions to fill in, how should you draft players based on their projected points, salary and field positions? In other words, we want to

  1. maximize the total projected points of all drafted players.
  2. make sure the total salary for the 9 players is under budget.
  3. make sure each field postion is filled, i.e. we want 
    • 1 Pitcher (P)
    • 1 Base 1 player (1B)
    • 1 Base 2 player (2B)
    • 1 Short Stopper (SS)
    • 1 Base 3 player (3B)
    • 1 Catcher (C)
    • 3 Out Fielders (OF)

The decision we need to make is: which players to pick.

 

The tricky part is how to express the objective, constraints and decisions above mathematically. 
Let's denote

  • xi: the decision whether we will draft the i'th MLB player. For example, if x6 = 1, then the 6th player will be drafted, if x6 = 0, then the 6th player won't be drafted. 
  • Pti: the projected points the i'th player will earn.
  • Si: the salary of the i'th player.
  • Pi: whether the i'th player is a pitcher. For example, if P5 = 1, then the 5th player is a pitcher, if P5 = 0, then the 5th player is not a pitcher.
  • Similarly, 1Bi, 2Bi,  SSi3Bi, Ci, OFi : whether the i'th player plays Base 1, Base 2, Short Stop, Base 3, Catcher and Out Field respectively.

where, i  can be 1, 2, ... 384 (384 is the total number of MLB players in the data set we are going to use in the attached example.) Based on the notations and assume our budget is $25200, the mathematical formulation for the daily fantasy baseball problem is as follows:

equations1.png

Once we have constrcuted the formulation, it is very straight-forward to use the new Optimization tool in Alteryx, since the inputs for Optimization tool are essentially different parts of the formulation.

 

We first obtain baseball player data from http://www.rotowire.com/daily/mlb/optimizer.htm and load it into Alteryx. Let's take a look the first couple of rows of data:
player_data.png

 

It is easy to see that only the columns in red circles are relevant to our problem setup. (Note that Pos stands for "position" and Pts is short for "projected points".) Configuring the Optimization tool is simple. Since we will use the baseball player data as input, instead of an industry standard optimization modelling file (e.g CPLEX_LP, MathProg, or MPS format), we will use "matrix input" mode. Because our decision variables (x1, x, ..., x384) can only have values 0 or 1, this is a mixed integer programming problem. We use "Symphony" as the solver, due to its overall better performance on mixed integer linear programming over "GLPK".

configuration.png

 

For Optimization inputs, please see the comments in the attached workflow for details. Thanks to @ramnath and @cfreeman's hard work, the Optimization tool generates an interactive report of which players you should select, and more.

output.png


For more details, see Optimization Tool Help.

 

Comments
Ehatie
8 - Asteroid

Awesome learning workflow Kuo, it was extremely usefull to understand how the tool inputs need to be set up.  Just one quick question, what is Anchor Q?

KuoL
Alteryx Alumni (Retired)

@Ehatie The Anchor Q is only needed if you are solving a quadratic programming problem. It is not needed if you are solving a linear programing or mixed integer linear programing problem.  The help document has more details: Optimization Help .

 

I'll add an example of quadratic programming in the Predictive District in Alteryx Gallery soon.

Ehatie
8 - Asteroid

Hey @KuoL,

 

Thanks for the answer, I always try to look at the help first but for some reason Alteryx V10.6 when clicking on help send me to this link:

https://help.alteryx.com/10.6/index.htm#cshid=

 

Rather than this one:

https://help.alteryx.com/10.6/index.htm#Optimization.htm%3FTocPath%3DTools%7CPrescriptive%7C_____1

 

Hence why I was trying to look for information on how it was to be set up for the other types of models the tool supports.

 

Cheers, and thanks for the help!

KrisV
Alteryx Alumni (Retired)

Hi @Ehatie,

 

Just wanted to let you know we are aware of the issue where the help page for Optimization (and the other Prescriptive tools) does not load from within the product. This is currently noted in the Release Notes for 10.6, and it will be fixed in the next release.

 

Thanks,

Kris Vickland

Technical Writer

 

mceleavey
17 - Castor
17 - Castor

Great post, thanks for putting this together.

I've been playing with the prescriptive tool and I've noticed a couple of issues (or highlighted my incompetence). Firstly, there seems to be an issue where the constraints seem to be picked up in a given order, and that doesn't appear to be amendable.

Also, is there a restriction on the number of constraints, and are they all mutually exclusive?

 

Thanks,

mceleavey
17 - Castor
17 - Castor

I've figured it out.

the problem appeared to be my sausage fingers.

Thanks for this, it's going to be very useful!

robichat
5 - Atom

Would there be a way to apply this to a golf lineup. I need 6 players, each player has the same position (G).

JoeM
Alteryx Alumni (Retired)

@robichat Check out the following weekly challenge - this was a fake draft of users (no position delimitation), but does have financial constraints.

ZINNEL
5 - Atom

Hello,

I am trying to institute this for football but am running into issues on a "Flex" category. I took the positions where I need 1 QB, 2 RBs, 2WRs, 1 TE, and 1 Defense but there is also a "Flex" position that can be filled by a RB, WR, or TE. When I add in a column for Flex and give it a 1 value for any RB, WR, or TE, I get a "Solution is undefined". Assuming its due to having a 1 under both RB and Flex for example. Any thoughts?