Challenge #73: Plinko Probabilities

The solution to last week's challenge is HERE!

Plinko Image.png

This year at Inspire, our attendees’ ability to win games of chance will be tested – and not just because of the Las Vegas venue! The ability to obtain the hottest Alteryx swag will be decided by a round of Plinko at the Community hub. Can you use Alteryx to break down the probabilities of our board and ensure your odds are the best possible?

There are no start files to this week’s challenge - any workflow or app that assigns relative probabilities to the final outcomes of the board above will suffice!

Hints below:

Spoiler

Formulaic approach

Reshaping approach

challenge_73_solution.yxwz

Parse

Join

Preparation

Data Analysis

Transform

Expert

Difficult

Accepted answers

All comments

patrick_digan

I've taken a very rudimentary approach...

Spoiler

I numbered each row in the board starting with 1 at the top and assigned the slots numbers starting with 1 at the left. So to start, you can put it in row 1 and slots 1-5. If you start in slot 1, then row2 could be either slot 1 or 2 with equal chance. If you end up in slot 1 or 6 in row2, then row3 must be slot 1 or 5 respectively based on how the board is designed. This pattern then continues all the way down to the bottom.

For my workflow, I used generate rows to handle the different possibilities. To ensure all the numbers work out, I had to use a formula tool as well. Here is my final output:

Start	1	2	3	4	5
1	49%	33%	14%	4%	0%
2	33%	30%	22%	11%	4%
3	14%	22%	27%	22%	14%
4	4%	11%	22%	30%	33%
5	0%	4%	14%	33%	49%

This is why the best prize is always in the middle. If you put the best prize in slot 1, then 49% of the time, people could start in slot 1 and it would end in slot 1. But to finish in slot3, your best bet is to start in slot3 with only a 27% chance of ending where you want. Oddly enough (pun intended), if you randomly selected a starting slot, you would have equal odds of ending up in any bucket (i.e. you would have a 20% chance of ending up in any of the 5 ending slots).

Plinko.yxmd

alex

Needless to say I learned a lot more about Pascal's Triangles with this week's challenge. After some false starts I decided to use an iterative macro to get my results. I'm posting my solution now, but will clean it up a bit, add details and then repost it. My %'s are different than @patrick_digan so we'll need some additional responses to get some sort of consensus on the %'s. There is a 13.4% chance of puck starting in the 4th slot and ending up in the 2nd bucket as per the picture.

Spoiler

I went with creating coordinates on a grid and then counting pathways to get to my Pascal Triangle. One of the tricky parts is bumping up against the edges. For this workflow 2 corresponds to the left most slot, 6 to the middle and 10 the slot on the far right.
Start

Start

Results
This is a view of the pathways if you started in slot 2.

workflow

Plinko Macro

This was definitely a challenge worthy of being presented during Inspire17!

Plinko2.yxzp

NicoleJohnson

Alright, @MattD - I will admit that I felt like it would be cheating to sit in on the Weekly Challenge session at Inspire this week. But then we started getting into Pascal's Triangles and API's and lions and tigers and bears... oh my. Not nice. Then @estherb47 and I spent the better part of the rest of Tuesday trying to figure out how to recreate Pascal's Triangle from scratch in Alteryx... So thanks for that

Anyway. Here's my solution! Bit of a different approach from @alex and @patrick_digan, I went with an app that prompts you for the number of Plinko slots & rows (so it's variable, you know, for bigger Plinko boards... because bigger boards = more cool prizes!). Then you select which slot you want to place your coin into, and it will spit out a report that tells you the probability of winning each prize.

Spoiler

To achieve this, I needed to recreate Pascal's triangle, so I brought back "College Freshman Statistics Class Nicole" back from the dead. Then I generated a "template" for the columns I would need (one for each slot, with "spacers" between each slot and at each end). Then I filled in the first "1" based on the slot selected in the app prompts, and wrote that info to an Excel file.

Then I created an iterative macro that took one row at a time.and determined the values in each column based on the values in the previous row, using some pivoting and a multi-row tool. With every iteration, the new row is appended to the original Excel file, and then the next iteration starts with that new row when determining the values for the next, up to the number of rows needed based on prompt selections.

Finally, I had to turn that lovely Pascal's triangle into probability %'s, so I brought it back into the original workflow after the macro was finished running, and then calculated the probabilities and final results. Packaged it all up at the end in a report telling you the odds of landing in each of the slots based on your selection.

Things that could still be improved upon/lingering questions:
1. I'm not convinced it will work from a report display perspective for combos that have lots of rows AND lots of slots, since there is a chance if you put a coin into the slot on the far right end, it won't be physically possible to make it all the way to the left side if there aren't enough rows... but the way I renumbered the columns for presentation purposes has it starting at #1 for the "left" column in every case. If someone wants to tinker with that idea & has suggestions, please do share

2. I was trying to figure out a way in the reporting tools to have a row rule that would highlight the top value(s) so that you could visually see where the highest probability final slot(s) might be, but didn't really see where you could do something like that in the tools. Does anyone know of a trick for that type of conditional formatting?

Phew. 6 days in Vegas + Challenge 73 = skin AND brain are officially fried! Thanks for the great challenge!

PS. @JoeM - Look! I did an iterative macro!!... But I still want to pick your brain, because I'm afraid this may have just been an "I just accidentally clicked the right combo of things" situation...

Cheers, all!

alex

I've cleaned up the original workflow and macro plus added descriptions of the formulas and processes I used to get my results. I also added options to change the number of slots and decision points (pegs). I couldn't have done it without modeling it and testing my thought process in Excel. I often have to first think about how I would create solutions in Excel to be able to create them in Alteryx.

Spoiler

I decided on a solution based on a grid pattern and counting paths. I assigned a coordinate to each cell based on the row and column and gave it a value of 1. The grid for the picture provided in the challenge would look like this. The green and yellow cells are drop slots and the red cells are the buckets in the bottom. The grey cells are open spots and the white cells are the pegs (includes the top of the buckets since they can deflect left or right).

If dropped in cell 106 (3rd slot) then it can only feed cells 205 and 207. Cells 205 can only be feed from cell 104 and 106, so on and so on. The workflow then assigns the cell 104 and 106 (subtracts 101 and 99 from 205) to the row that cell 205 is assigned to. The macro starts at row 3 and looks up one row to pull in the data for the cells assigned to to them. Cell 306 sums the path value from 205 and 207 (1+1=2).

The cells that are not available (you can't be at coordinate 203 if you are starting at 106), out of bounds (puck starts far left and may not reach far right bucket) or that are edges were filtered out in the workflow so there is nothing to add inside the macro.

The process after the macro is pretty straightforward. I used the option of creating a Total Column inside the first cross tab tool to use in calculating the percentages. I built in some ways to visually inspect the answers to make sure the model was working properly. I audited it for various scenarios and I didn't find one that wasn't handled properly by the model.

Hope you find this solution helpful. Thanks.

Alex

PlinkoGameOdds.yxwz

LordNeilLord

After driving myself crazy trying to build a macro to do this, I finally gave up and took an Old School approach...not my finest Alteryx workflow but I got there in the end...

Spoiler

challenge_73_LNL.yxmd

philipmannering

_METHOD 1_

Use the old combinatorial mathematics to workout the number of combinations for each slot and then divide by the total number of possible routes (2^8) for the probability. If there were no boundary the number of combinations would be 8C4 (=8!/4!/4!) for any slot directly under the placement, 8C3 for the slot left of the placement, 8C2 for the slot 2 places left of the placement, etc. Taking account of the boundary you need to add the combinations of the slot opposite the current slot if no boundary existed. So, for example, a counter placed in position 1 and landing in slot 1 the total probability would be (8C4 + 8C3)/2^8 and for position 1 and slot 2 the probability is (8C5 + 8C2)/2^8... Unfortunately I found it tricky to generalise for all cases. So I resorted to Method 2...

_METHOD 2_

Build your own Plinko Game in Alteryx and run it multiple times...

Plinko Probabilites.png

I simulate the 50/50 probability of the ball falling one way or the other using a filter tool with the expression rand() <0.5 and just union it all together in the right places. I set it as an iterative macro to run 1000 times and run in this workflow (see below) to get...

This method is numerical so results are approximate!

challenge_73_Plinko_Probabilities.yxmc

MattD

In case anyone is taking the formulaic approach and needs help calculating factorials! https://gallery.alteryx.com/#!app/Wolfram-Alpha-Connector/59405f3ea18e9e02406720db

We're leaving this exercise open until next Monday, so keep the solutions coming!

david_fetters

So i went with a low labor approach after thinking about ways to needlessly over complicate this. First challenge for me, so lets see how it plays out!

Spoiler

I started by observing that you can break the plinko board into 4 identical groups. Each group has 5 slots of input, goes through 6 slots in the middle, and then outputs through 5 slots in the bottom. The 5 output slots are also the 5 input slots of each next group. Then, assume that any input with two lower outputs (e.g. every input in the 5 slot row, and all but the two outside edges in the 6 slot row) has a 50% chance of going into either output, and that the edges in the 6 slot row have a 100% chance of going into the lower row. This means something entering in slot 1 of a 5 slot row has a 75% chance of exiting in slot 1 of the next 5 slot row, and a 25% chance of exiting in slot 2, and something entering slot 2 has a 25% chance of exiting in slot 1, 50% chance of exiting in slot 2, and 25% chance of exiting in slot 3.

I built a formula in excel for each of the 4 groups of 5-6-5 slots, and used a random value for each group to determine what the output would be. Each groups output is the input of the next group. I actually built this two ways, one way performed all of the math in a single formula node, while the second way used four separate formula nodes.

By using create rows, I was able to run a simulation of 1 million attempts for each starting position per workflow run. Method 1 took about 27.2 seconds, and method 2 took about 32 seconds. For the sake of comparing my results with other attempts, in both of my methods, a chip dropped into slot 4 at the top had a 10.9% chance of landing in slot 2 at the bottom.

As a side note: i used only greater/less thans, without anything to capture random numbers equaling .25 or .75. I set any of those results to a 9, and like to think of that as the weird case where it balances on the pin and doesn't fall all the way through. It seems to happens once every 5 million runs!

Plinko cap.PNG

Edit: hey i figured out spoilers!

david_fetters-Plinko.yxmd

david_fetters-Plinko2.yxmd

NicoleJohnson

Turns out, I never posted my macro & workflow, and for some reason it won't let me edit my original post... soooo here you go.

PS. Super impressed with the varied solutions so far on this one. I'm guessing Vegas lost some money on these savvy odds-calculating wizards last week...

challenge_73_NicoleJohnson.yxzp

alex

I think we all agree that this was a seriously hard challenge. Thanks @MattD! I've enjoyed looking thru the other solutions and being Inspired by what I've seen.

I'm a little short on billable work right now so I took another crack at Plinko Probabilities. In my previous post, I solved this challenge by using a pathways method and an iterative macro to calculate the number of paths to the bottom. In this solution, I’m using the formula n! / r!(n-r)! to calculate Pascal’s triangle for the bottom row - no macro needed. A number in Pascal’s triangle can be found by nCr (n Choose r) where n is the number of the row and r is the element in that row. This workflow allows you to solve for a variety of ranges in number of slots and levels to the bottom. Same goes for my previous workflow. Again, the tricky issues come up with the edges and how to fold them back to get the correct numbers at the bottom row. Excel to the rescue in terms of modeling the solution and then figuring out how to translate it to Alteryx. It's amazing what you can accomplish using both programs. The workflow does not have instructions or descriptions and currently only solves for one drop at a time. I tested it for many, but not all scenarios, and it provided the correct %'s each time. I believe both these solutions are like my girls - beautiful, challenging, inspiring, frustrating (at times) and I’m equally proud of both. Please feel free to PM me If you have any questions on either solution.

Note: I added an output to a csv file to the app and is now working fine.

Spoiler

Select number of slots, decision points and which slot to drop it in.
Create table with row and column info.
Create factorials using multi-row tool for the maximum range - if [n1]=1 THEN 1 ELSE [Row-1:Factorial]*[n1] ENDIF
Use factorials and n! / r!(n-r)! formula to build out Pascal’s Triangle
Use bottom row to start calculating odds.
Adjust for columns that fall off the edge.
Calculate adjusted totals, %, and output.

Pascal's triangle to 14th levelOdds when starting in second of 10 slots and 14 pegsFactorial to 14th level
Excel Modeling - green represents triangle when puck dropped in 5th out of 10 slots and 14 decision points (15 rows).
Visual PatternNumerical Pattern

PlinkoFormula.yxwz

Auditor

I actually learned a lot about Pascal's Triangle from this weekly challenge.

Calculation of the probabilities is based on the 9th line of the Triangle. Please see below result.

I am an Alteryx beginner, but hopefully the result is correct.

Interpretation:

If the chip starts in slot #4 (4th slot from the left hand side at the top):

the probability of landing in position # 1 (first position from the left hand side at the bottom) is 3.125 %
the probability of landing in position # 2 is 11.71875 %
the probability of landing in position # 3 is 25 %
the probability of landing in position # 4 is 38.28125 %
the probability of landing in position # 5 is 21.875 %

Plinko_WC_73_Auditor.yxmd

SeanAdams

Hey all,

My solution is a little different than those posted (seems that many folk have been inspired by Inspire to use Pascal's triangle).

Here's the dope:

Spoiler

If you divide the board into left/right regions or lanes (picture below) then at each step as the ball progresses down, it either moves left or right by one lane.
I've also assumed that pin-rows come in pairs. The first row has 1 more pin than the second row, and that's one row pair. That is helpful because the behaviour of these two rows is different:
- the first row is controlled so any ball will be in lane 2,4,6 etc (always even numbers).
- as you transition to the second row, you can't go out of bounds (because even if you're in lane 2, you can go to lane 1)
- in the second row, you're always in odd-numbered lanes
- as you transition back to the first row again you DO have to check for going out of bounds - e.g. if you're in lane 1, and the random generator wants to go left, that would take you out of bounds.

So - if you iterate through every row of the board, using an iterative macro, and a random number generator that either goes left or right - then you can put several thousand cases through this and get a reasonably robust answer.

Here's the iterative macro piece:

When you call this from a harness - you can specify the number of rows; columns; where the goal chute is; and how many balls to drop to test this

BallBouncer.yxmc

Challenge73_SeanSolution.yxmd

estherb47

Still working on the flexibility of my solution -- I'd like it to be an app where the user can insert the # columns and # rows. But, for now, this works for the 9 row, 5 column Plinko board that was at Inspire 17.

A little behind, but 75 achieved!!! Now to catch up to 76....

CalculatePlinko.yxzp

ggruccio

Struggled to understand this one - tried a macro - then tried multiple iterations using a formula. Assumed a pattern of alternating 5 and 6 slots.

12345

123456

12345

I guess the answer depends on how many iterations it runs thru....

challenge_73_gg_finish.yxmd

samN

I guess i approached this slightly differently than others. Used a few nested macros.

Spoiler

Used one to simulate a direction choice(right or left) and then counting the number of times that a chip went a certain way. Simple lookup table that finds an end value based on number of times it goes a certain way and the start location. Then just wrapped that in a loop that goes a thousand times and then simple summarize. My outermost macro gives the ability to choose the starting gate and then calculates the probability of landing at each end gate.

Fun stuff!

Solution attached.

This is by far is the most difficult challenge I've done. I struggled a lot with math and I got stuck trying to to find a more visual solution, and it is hard to top @PhilipMannering's beautifully constructed Plinko board.

Spoiler

I first went down similar path as @alex and @SeanAdams with constructing a visual board with possible and impossible moves of the ball. Since I wanted to provide a more dynamic solution, I got stuck trying to figure out some dynamic combination of multi-field and multi-row formulas. I massively overcomplicated everything and put this challenge on hold for a while. I didn't build an app but the input consists of just number of slots and rows, so it is easily changed if need be.

At the end I got rid of columns, and calculated the path in one column with a multi-row formula, depending on a random left/right direction with checks for the end of the board. Screen Shot 2018-01-07 at 01.18.37.png

My probabilities are a bit different from other's without smooth distributions of ~ 30%. However I did quickly checked my results in Tableau, and every chart reminds me of a histogram or a half-histogram, which makes sense to me.

Screen Shot 2018-01-07 at 02.34.23.png

challenge_73_Natasha.yxzp

JosephSerpis

Challenge Completed

Challenge_73_Joe_Serpis.yxwz

jamielaird

Extremely pleased to have cracked this one!

Here's how I solved it:

Spoiler

1) I started by creating a replica of the board in Excel and identifying the rules dictating whether a certain 'move' is permissible.

2) I then initialised my workflow with 5 starting positions and used generate rows to create a version of the entire first row for each starting position. This includes paths that would not be valid, e.g. 1 --> 4 since we haven't yet applied any constraints.

3) Next I used the filter tool to only keep the valid paths. When moving from a start to row 1 position we can keep any paths where row 1 is equal to or 1 bigger than the start position.

4) To calculate the probability of each path we count the distinct start and row 1 positions for each start position, then divide these two numbers.

5) These probabilities are appended to the start position. The probabilities for each given start position always add up to 1, as the "plink" has to go somewhere.

6) The process then repeats for each row, with a slight variation in number of rows generated (5 or 6) and the filtering logic depending on whether we're moving from a wide to narrow or narrow to wide row.

7) After repeating for all rows, we end up with a table of data where each row represents one of the unique paths the "plink" could have taken, and the probability of that exact route occurring given the starting position, calculated by multiplying all of the individual probabilities across the row.

8) Finally, we summarise the data by start and end position and add together the probabilities for all paths with a common start and end position. Then we can convert that summary into a matrix.

9) Giving a final output of:

challenge_73_JL.yxmd

KOBoyle

Solution attached. I was disappointed that the solution provided did not respond to the question from @alex and give a definitive answer regarding the correct probabilities. I know I’m way late to the party, but I’ll weigh in and agree that Alex has the correct probabilities. It looks like the solution from @patrick_digan did not exclude paths that go out of bounds.

Spoiler

Assuming that the question is which start slot has the highest probability of landing the Alteryx chip where it is in the diagram (i.e. Finish Slot 2), then the answer would be Start Slot 1 with a probability of 38.1%.

challenge_73_solution_KO App.yxwz

Kenda

Spoiler

My approach was very brute force and not dynamic to varying columns/rows, but I was able to get to the right answer nonetheless!

Basically, I used lots of Text Input, Append Fields, Filter, and Formula tools repeatedly.

In the Formula tools, I accounted for the fact that, on the even rows, the first slot can only go to the first slot and the sixth slot can only go to the fifth slot. Because of this, it is necessary to force both of slot 1's options to slot 1 in the next row and both of slot 6's options to slot 5 in the next row. By changing slot 1 and 6's values to be 1 and 5, respectively, it looks like those rows are double counted but this is required in order to get the full probability for that transition. Because of the way that the probabilities are calculated at the end, based on counts, both rows 1 and 2 & rows 19 and 20 in the below screenshot are required to account for the fact that 1 will either go to 1 or 1 and 6 will either go to 5 or 5, just as you would expect slot 2 to go to either slot 1 or slot 2.
73.2.PNG

Another way to think about it would be that 3/4 (or (0.5*1)+(0.5*0.5)) of the options need to be assigned to slot one in the third row, when starting from slot 1.
73.5.PNG

Along the workflow, I checked the counts to ensure nothing was dropping off.
73.3.PNG

Lastly, just some normal Alteryx transformations to calculate the percentages and get the output in the right format.
73.4.PNG

Looks like my answer matches @patrick_digan! This was a tough one to think through, but we got it!

EDIT: @jamielaird it looks like we got the same solution, too! wtg

challenge 73.yxmd

KOBoyle

@Kenda did you have a chance to look at the second hint provided with the original problem labeled "Reshaping approach"? It explains the issue with the approach you described.

Spoiler

In your example of Start Slot 1 and row 3, one of the 4 possible paths goes out of bounds, but in your picture you have pointed it back in bounds and counted the same path twice. When starting in Slot 1, there are only three possible paths to row 3, not 4, so the probability that you calculated should be 2/3, not 3/4. Here is an excerpt from the hint that explains it, using row 4 as an example. As it shows, on each row you need to subtract paths that go out of bounds and account for the adjustments in all subsequent calcs. Hope that is helpful.

kat

Great challenge

Spoiler

Challenge #73.yxzp

dsmdavid

I've been keeping this challenge on hold, as when I first came to it I thought it would be a nice use case for the Python SDK tool. Now that I more or less have it, I should try with the new Python tool...

Spoiler

Config of the Plinko Tool

What happens if the token starts more or less in the middle

Biased when the token starts in one of the end slots

Main Workflow using the Plinko Tool

(the tool needs some polishing --and clarification of what the "position" actually means-- but that's for another day)

PlinkoSDK.yxi

solved#73.yxmd

Laurap1228

This one was really difficult.

challenge_73_LP.yxzp

JoBen

Cheers!

Spoiler

Here is what I came up with. I basically determined how many viable paths could be taken from each starting point to an end point, and then based on all the viable paths, I determined the likelihood for each starting to ending point.

Challenge_73_Joben_Solution.yxmd

justynam

Here is my solution - short, simple and generic

All you need to change are 2 parameters in Text input tool: Number of slots and Number of rows

Spoiler

Main workflow to run batch macro with list of slots in initial row as control parameter

New Picture (6).jpg

Batch macro that runs iterative macro for every slot in initial row

New Picture (7).jpg

And finally iterative macro that takes possible slots with their probabilities in previous row and calculate possible slots and their probabilities in the next row

New Picture (8).jpg

73_solution_JM.yxzp

jssandom

Tried to not over complicate this too much!

challenge_73_solution_JSS.yxmd

OldDogNewTricks

Here is a solution

challenge_73_completed.yxzp

danilang

I modeled the board and then Monte Carlo'd until the pins wore off.

Like @patrick_digan, @jamielaird and @Kenda, I found the bounded model produces higher probabilities that token will end up in Bucket 1 or 5 if started in those columns

Spoiler

Because I modeled the board in the data input, I only needed 2 tools in the macro to simulate a run.
Main

Dan

Challenge_73_solution_DL.yxzp

jbranaum

My solution.

Ummm wow, this one was exhausting for me.

Glad to know the posted solution looked up a pascal triangle because I spent a good half hour trying to manually count the paths to each space and gave up after a bit. Definitely not proud of my efficiency on this one. I walked away and came back to it on and off for 3 days researching different articles on Plinko probability.

So here it is, Didn't bother with the bonus, mostly cause i was burnt out by the time i got to the solution. Just glad to just check the box on this one and be done. No offense

I imagine you could add some interface tools on this approach to adjust the size of triangle you put through the calculation.

Cheers!

Challenge 73.yxmd

ipeng

I followed the posted solution for this exercise....

IP_challenge_73.yxzp

pasccout

Finally got it (I think...)

You input start column (in letters - A to E) and it gives you the output...

Spoiler

challenge_73_my_solution.yxzp

OllieClarke

I went with an iterative macro which runs 1000 simulations for each starting position, and then works out the % of iterations which ended in the winning position. Worked with @George_Walker on this one

Spoiler

Starting in the second position seems to be the best place - ending in the winning spot ~ 26% of the time.

The formula updating the position in the macro uses whether a random integer is odd or even to decide whether to go left or right. We also need to appreciate that if a row has 6 blocks, (what I called a T(ight) row) and we are in the outer positions, we can only stay in the outer position. If we are in a row with 5 blocks (what I called a L(oose) row) we can always move left or right.
Challenge 73.png

challenge_73_OC_solution.yxzp

RichoBsJ

Hi! Here my solution

Spoiler

I developed an app that ask two questions to the user: how many rows and the starting position.

Considering the game as an iteration of groups of two rows (a 4-pin row and a 5-pin row) plus a possible 5-pin row, I calculated the probabilities for a 2-row group and iterated as many times as groups existing; then, applying the posible 5-pin row probabilities.

challenge_73_richobsj.yxzp

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167