Disc Golf Analytics: Completed Games

cwellstech

Last time I wrote a query to pivot the default UDisc scorecard data, which opened a lot of possibilities for analysis, but also made it a little more difficult to decide whether a game had been completed.

Marking games as “complete” means that you played all holes on the course in one round. For traditional golf, this is almost always 18. For disc golf, it varies. It is an important metric because it shows improvements in skill, but also stamina and consistency. Playing a 21 hole course takes at least an hour solo, and is pretty exhausting.

The value for completed has to be computed in parts. First, we count the number of holes where the score was not 0, indicating that the hole was played:

        select "PlayerName" as "name", "Date" as "dt", 
        case when "Hole1"=0 then 0 else 1 end + 
        case when "Hole2"=0 then 0 else 1 end +
        case when "Hole3"=0 then 0 else 1 end +
        case when "Hole4"=0 then 0 else 1 end +
        case when "Hole5"=0 then 0 else 1 end +
        case when "Hole6"=0 then 0 else 1 end +
        case when "Hole7"=0 then 0 else 1 end +
        case when "Hole8"=0 then 0 else 1 end +
        case when "Hole9"=0 then 0 else 1 end +
        case when "Hole10"=0 then 0 else 1 end +
        case when "Hole11"=0 then 0 else 1 end +
        case when "Hole12"=0 then 0 else 1 end +
        case when "Hole13"=0 then 0 else 1 end +
        case when "Hole14"=0 then 0 else 1 end +
        case when "Hole15"=0 then 0 else 1 end +
        case when "Hole16"=0 then 0 else 1 end +
        case when "Hole17"=0 then 0 else 1 end +
        case when "Hole18"=0 then 0 else 1 end +
        case when "Hole19"=0 then 0 else 1 end +
        case when "Hole20"=0 then 0 else 1 end +
        case when "Hole21"=0 then 0 else 1 end
        as "holes played"
        from "UDiscScorecards"

In PostgreSQL, CASE statements work the way IF statements do in traditional programming. This can be translated as "if HoleX==0 then 0 else 1"

I’ve also appended a “Hole Count” column to the course data sheet:

Finally, we compare them and report the result

select "PlayerName", ...,  "holes played"="Hole Count" as "Completed" from ...

The “x”=”y” returns true or false implicitly, so we don’t need to use a CASE statement here.

So, stitching it all together, the new query is:

select "PlayerName", "CourseName", "LayoutName", "Date", "hole", "score", "Par", "score" - "Par" as "Over", "holes played"="Hole Count" as "Completed" 
from (
    select * from (
        select "PlayerName", "CourseName", "LayoutName", "Date", 1 as hole, "Hole1" as score from "UDiscScorecards" union
        select "PlayerName", "CourseName", "LayoutName", "Date", 2 as hole, "Hole2" as score from "UDiscScorecards" union
        select "PlayerName", "CourseName", "LayoutName", "Date", 3 as hole, "Hole3" as score from "UDiscScorecards" union
        select "PlayerName", "CourseName", "LayoutName", "Date", 4 as hole, "Hole4" as score from "UDiscScorecards" union
        select "PlayerName", "CourseName", "LayoutName", "Date", 5 as hole, "Hole5" as score from "UDiscScorecards" union
        select "PlayerName", "CourseName", "LayoutName", "Date", 6 as hole, "Hole6" as score from "UDiscScorecards" union
        select "PlayerName", "CourseName", "LayoutName", "Date", 7 as hole, "Hole7" as score from "UDiscScorecards" union
        select "PlayerName", "CourseName", "LayoutName", "Date", 8 as hole, "Hole8" as score from "UDiscScorecards" union
        select "PlayerName", "CourseName", "LayoutName", "Date", 9 as hole, "Hole9" as score from "UDiscScorecards" union
        select "PlayerName", "CourseName", "LayoutName", "Date", 10 as hole, "Hole10" as score from "UDiscScorecards" union
        select "PlayerName", "CourseName", "LayoutName", "Date", 11 as hole, "Hole11" as score from "UDiscScorecards" union
        select "PlayerName", "CourseName", "LayoutName", "Date", 12 as hole, "Hole12" as score from "UDiscScorecards" union
        select "PlayerName", "CourseName", "LayoutName", "Date", 13 as hole, "Hole13" as score from "UDiscScorecards" union
        select "PlayerName", "CourseName", "LayoutName", "Date", 14 as hole, "Hole14" as score from "UDiscScorecards" union
        select "PlayerName", "CourseName", "LayoutName", "Date", 15 as hole, "Hole15" as score from "UDiscScorecards" union
        select "PlayerName", "CourseName", "LayoutName", "Date", 16 as hole, "Hole16" as score from "UDiscScorecards" union
        select "PlayerName", "CourseName", "LayoutName", "Date", 17 as hole, "Hole17" as score from "UDiscScorecards" union
        select "PlayerName", "CourseName", "LayoutName", "Date", 18 as hole, "Hole18" as score from "UDiscScorecards" union
        select "PlayerName", "CourseName", "LayoutName", "Date", 19 as hole, "Hole19" as score from "UDiscScorecards" union
        select "PlayerName", "CourseName", "LayoutName", "Date", 20 as hole, "Hole20" as score from "UDiscScorecards" union
        select "PlayerName", "CourseName", "LayoutName", "Date", 21 as hole, "Hole21" as score from "UDiscScorecards" 
  ) pivoted 
  left join (
        select "PlayerName" as "name", "Date" as "dt", 
        case when "Hole1"=0 then 0 else 1 end + 
        case when "Hole2"=0 then 0 else 1 end +
        case when "Hole3"=0 then 0 else 1 end +
        case when "Hole4"=0 then 0 else 1 end +
        case when "Hole5"=0 then 0 else 1 end +
        case when "Hole6"=0 then 0 else 1 end +
        case when "Hole7"=0 then 0 else 1 end +
        case when "Hole8"=0 then 0 else 1 end +
        case when "Hole9"=0 then 0 else 1 end +
        case when "Hole10"=0 then 0 else 1 end +
        case when "Hole11"=0 then 0 else 1 end +
        case when "Hole12"=0 then 0 else 1 end +
        case when "Hole13"=0 then 0 else 1 end +
        case when "Hole14"=0 then 0 else 1 end +
        case when "Hole15"=0 then 0 else 1 end +
        case when "Hole16"=0 then 0 else 1 end +
        case when "Hole17"=0 then 0 else 1 end +
        case when "Hole18"=0 then 0 else 1 end +
        case when "Hole19"=0 then 0 else 1 end +
        case when "Hole20"=0 then 0 else 1 end +
        case when "Hole21"=0 then 0 else 1 end
        as "holes played"
        from "UDiscScorecards"
  ) completed 
  on "pivoted"."PlayerName" = "completed"."name"
  and "pivoted"."Date" = "completed"."dt"
  where "score" <> 0
  and "PlayerName" <> 'Par'
) cards 
left JOIN ( 
    select * from "CoursePars" 
) pars
on "cards"."hole" = "pars"."Hole"
and "cards"."CourseName" like "pars"."Course"

Definitely a mouthful, but pretty straightforward. If you know a more elegant way, leave a comment below.

Anyway, with the boring stuff out of the way, lets get down to the pretty charts.

The obvious first thing to check is how my best games have changed over time.

There seems to be a trend downward, although not a very quick one. The downward trend is more apparent in another useful metric: average throws over par, plotted against time. Note that this viz includes all holes played, regardless of game completion.

These trends are also a little obscured by the outlier bad game I had on August 6th, where I forgot my putter and had to use the wrong disc.

While we’re at it, lets revisit the flashy area chart from last post, where the width of each stripe represents the number of throws taken on that hole:

This is flashy and colorful, but we’re more concerned with problematic holes. Lets change width from number of throws to number of throws over par.

Awesome. This shows about what you’d expect. An interesting thing to notice is that while I’ve gotten better at hole 1, I’ve actually gotten worse at 9 (pale orange).

This might be because 9 is heavily wooded and honestly kind of a crapshoot… but the trend seems a little too strong. Furthermore, I changed my strategy probably around early July. Instead of attempting to weave my disc through the woods, I started tomahawking it over the tree tops. The data says this strat may not be working. Neat.

Last thing, lets make a leaderboard for completed games.

I play way more often than my friends, so the data might be skewed, aha.

Also shoutout to Duke for having the courage to play blue tees on his first game.

That’s all for now. Next time I’ll try to correlate hole length with average throws over par.

Until then, happy golfin’

Leave a comment