{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f40e80c8",
   "metadata": {},
   "source": [
    "# Kilter Board: Hold Usage Analysis\n",
    "\n",
    "This notebook shifts attention from whole climbs to individual holds. The objective is to understand **which holds are being used**, **how often they are used**, and **how their use changes by role, material, and location on the board**.\n",
    "\n",
    "As I climb on a TB2 Mirror and it interests me, I restrict the analysis to the **Tension Board 2 Mirror**. Restricting to one board also makes later feature engineering cleaner: the same physical placement has the same geometric meaning throughout the notebook series.\n",
    "\n",
    "## Main questions\n",
    "\n",
    "1. Which holds appear most often?\n",
    "2. Which regions of the board are used most heavily?\n",
    "3. How different are hand and foot usage patterns?\n",
    "4. Are plastic or wood holds favoured over the other?\n",
    "\n",
    "The outputs here will feed directly into the next notebook, where hold usage is turned into hold-difficulty features.\n",
    "\n",
    "## Notebook Structure\n",
    "1. [Setup and Imports](#setup-and-imports)\n",
    "2. [Some Useful Functions](#some-useful-functions)\n",
    "3. [Holds Heatmaps](#holds-heatmaps)\n",
    "4. [Some other hold stats](#some-other-hold-stats)\n",
    "5. [Conclusion](#conclusion)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "135284a6",
   "metadata": {},
   "source": [
    "## Setup and Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "94e7a456",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "==================================\n",
    "Setup\n",
    "==================================\n",
    "\"\"\"\n",
    "# Imports\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import numpy as np\n",
    "import matplotlib.patches as mpatches\n",
    "\n",
    "import sqlite3\n",
    "\n",
    "import re\n",
    "from collections import defaultdict\n",
    "\n",
    "from PIL import Image\n",
    "\n",
    "# Set some display options\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.max_rows', 100)\n",
    "\n",
    "# Set style\n",
    "palette=['steelblue', 'coral', 'seagreen']  #(for multi-bar graphs)\n",
    "\n",
    "# Set board image for some visual analysis\n",
    "board_img = Image.open('../images/kilter-original-16x12_compose.png')\n",
    "\n",
    "# Connect to the database\n",
    "DB_PATH=\"../data/kilter.db\"\n",
    "conn = sqlite3.connect(DB_PATH)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "63352d8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "==================================\n",
    "Loading the data\n",
    "==================================\n",
    "\n",
    "This time we restrict to where `layout_id=1` for the Kilter Board Original.\n",
    "We also put the date past 2016 as to not get that test climb from 2006.\n",
    "\n",
    "There are also some phantom placements with y-value > 156. We'll filter those out in our query.\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "# Query climbs data\n",
    "climbs_query = \"\"\"\n",
    "SELECT\n",
    "    c.uuid,\n",
    "    c.name AS climb_name,\n",
    "    c.setter_username,\n",
    "    c.layout_id AS layout_id,\n",
    "    c.description,\n",
    "    c.is_nomatch,\n",
    "    c.is_listed,\n",
    "    l.name AS layout_name,\n",
    "    p.name AS board_name,\n",
    "    c.frames,\n",
    "    cs.angle,\n",
    "    cs.display_difficulty,\n",
    "    dg.boulder_name AS boulder_grade,\n",
    "    cs.ascensionist_count,\n",
    "    cs.quality_average,\n",
    "    cs.fa_at\n",
    "    \n",
    "FROM climbs c\n",
    "JOIN layouts l ON c.layout_id = l.id\n",
    "JOIN products p ON l.product_id = p.id\n",
    "JOIN climb_stats cs ON c.uuid = cs.climb_uuid\n",
    "JOIN difficulty_grades dg ON ROUND(cs.display_difficulty) = dg.difficulty\n",
    "WHERE cs.display_difficulty IS NOT NULL AND c.is_listed=1 AND c.layout_id=1 AND cs.fa_at > '2016-01-01'\n",
    "\"\"\"\n",
    "\n",
    "# Query information about placements (and their mirrors)\n",
    "placements_query = \"\"\"\n",
    "SELECT\n",
    "    p.id AS placement_id,\n",
    "    h.x,\n",
    "    h.y,\n",
    "    p.default_placement_role_id AS default_role_id,\n",
    "    p.set_id AS set_id,\n",
    "    s.name AS set_name\n",
    "FROM placements p\n",
    "JOIN holes h ON p.hole_id = h.id\n",
    "JOIN sets s ON p.set_id = s.id\n",
    "WHERE p.layout_id = 1 AND y <=156\n",
    "\"\"\"\n",
    "\n",
    "# Load it into a DataFrame\n",
    "df_climbs = pd.read_sql_query(climbs_query, conn)\n",
    "df_placements = pd.read_sql_query(placements_query, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "670aa500",
   "metadata": {},
   "source": [
    "First let's see how many climbs we're working with."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "26a6ec05",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(df_climbs)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bbf913c2",
   "metadata": {},
   "source": [
    "So we have about 287k climbs, but this has some repetition to due to angle."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc682905",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(df_climbs['frames'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "467097aa",
   "metadata": {},
   "source": [
    "Great, so 173k unique different routes to analyze.  Let's see what our `df_placements` DataFrame looks like."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e97eaddd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Our climbs DataFrame will look the same as in 01, although we are just restricting to the TB2 Mirror.\n",
    "# Let's see what our placements DataFrame looks like.\n",
    "\n",
    "df_placements.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11beff1c",
   "metadata": {},
   "source": [
    "Now let's set our board boundaries and some basical mapping rules. First let's take a look at the `placement_roles` table in the databse."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e7320eac",
   "metadata": {},
   "outputs": [],
   "source": [
    "led_color_query = \"\"\"\n",
    "SELECT * FROM placement_roles ORDER BY id;\n",
    "\"\"\"\n",
    "\n",
    "df_placement_roles = pd.read_sql_query(led_color_query, conn)\n",
    "\n",
    "display(df_placement_roles)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9d3eb97b",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "==================================\n",
    "Mappings and more setup\n",
    "==================================\n",
    "\"\"\"\n",
    "\n",
    "role_names = df_placement_roles.name[4:].tolist()\n",
    "role_colors = df_placement_roles.led_color[4:].tolist()\n",
    "sets = df_placements['set_name'].unique().tolist()\n",
    "\n",
    "# Placement coordinates pX -> (x,y)\n",
    "placement_coordinates = dict(zip(df_placements['placement_id'],\n",
    "                                 zip(df_placements['x'], df_placements['y'])))\n",
    "\n",
    "# Placement set: is it wood or plastic?\n",
    "placement_sets = dict(zip(df_placements['placement_id'], df_placements['set_name']))\n",
    "\n",
    "\n",
    "# Role map name. Takes rY to foot/start/finish/middle\n",
    "role_name_map = {i+12: name for i, name in enumerate(role_names)}\n",
    "\n",
    "# Role map color. Takes rY to the appropriate color for the LED\n",
    "role_color_map = {i+12: f\"#{led_color}\" for i, led_color in enumerate(role_colors)}\n",
    "\n",
    "# Figure out whether a hold is a hand or a foot\n",
    "role_type_map = {12: 'hand', 13: 'hand', 14: 'hand', 15: 'foot'}\n",
    "\n",
    "\"\"\"\n",
    "SELECT * FROM product_sizes WHERE id=28;\n",
    "id|product_id|edge_left|edge_right|edge_bottom|edge_top|name   |description|image_filename      |position|is_listed|\n",
    "--+----------+---------+----------+-----------+--------+-------+-----------+--------------------+--------+---------+\n",
    "28|         1|      -24|       168|          0|     156|16 x 12|Super Wide |product_sizes/28.png|       5|        1|\n",
    "\"\"\"\n",
    "x_min, x_max = -24, 168 # add 4 inches to the end of each side for width\n",
    "y_min, y_max = 0, 156 # add 4 inches to the end of each side for height"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96620735",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_placements['default_role_type'] = df_placements['default_role_id'].map(role_type_map)\n",
    "\n",
    "df_placements"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54d35d91",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa735a35",
   "metadata": {},
   "source": [
    "# Some useful functions\n",
    "\n",
    "Here we create some useful functions. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1876286",
   "metadata": {},
   "source": [
    "## Extract Placements and Roles\n",
    "\n",
    "First, we will want a quicker way to look at the placements in the frames. We'll do this by just quickly making a list of the placements in the frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0c6f3d87",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\"\"\"\n",
    "==================================\n",
    "Create a list of placements from a climb\n",
    "==================================\n",
    "\"\"\"\n",
    "\n",
    "# Parse the placements\n",
    "def parse_frames_p(frames):\n",
    "    \"\"\"Returns a list of the placement ID's\"\"\"\n",
    "    if not frames:\n",
    "        return []\n",
    "    \n",
    "    # Find all 'p<number>' patterns\n",
    "    matches = re.findall(r'p(\\d+)', frames)\n",
    "    return [int(m) for m in matches]\n",
    "\n",
    "# Parse the placements, together with role\n",
    "def parse_frames_pr(frames):\n",
    "    \"\"\"Returns a list of tuples containing the placement ID and the role ID\"\"\"\n",
    "    if not frames:\n",
    "        return defaultdict()\n",
    "    \n",
    "    # Find all 'p<number>' patterns\n",
    "    matches = re.findall(r'p(\\d+)r(\\d+)', frames)\n",
    "    return [(int(p), int(r)) for p,r in matches]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18f99b4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "### Tests\n",
    "# Let's just take one row from out data frame\n",
    "test_climb = df_climbs.iloc[0]\n",
    "\n",
    "\n",
    "\n",
    "# Isolate the frames feature\n",
    "test_frames = test_climb['frames']\n",
    "\n",
    "display(parse_frames_p(test_frames))\n",
    "\n",
    "display(parse_frames_pr(test_frames))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1585ff55",
   "metadata": {},
   "source": [
    "\n",
    "## Creating a per-climb DataFrame\n",
    "Second, we create a dataframe for any specific climb. This will just be useful if we want to map a specific climb to the board."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fef2d333",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "==================================\n",
    "Create a dataframe from a climb\n",
    "==================================\n",
    "\"\"\"\n",
    "\n",
    "def climb_to_DataFrame(frames):\n",
    "    \"\"\"Extract placement IDs, roles, and coordinates from frames string efficiently.\n",
    "    \n",
    "    Parameters: a `frames` string. This is a string of the form px_1ry_1px_2ry_2...\n",
    "    - The px_i will tell you that we are dealing with a hold with placement ID x_i.\n",
    "    - The ry_i will tell you that we are dealing with a hold with role ID y_i.\"\"\"\n",
    "    if not frames or not isinstance(frames, str):\n",
    "        return pd.DataFrame(columns=['placement_id', 'role_id', 'role_name', 'x', 'y', 'set_name'])\n",
    "    \n",
    "    # Parse all at once\n",
    "    matches = re.findall(r'p(\\d+)r(\\d+)', frames)\n",
    "    \n",
    "    if not matches:\n",
    "        return pd.DataFrame(columns=['placement_id', 'role_id', 'role_name', 'x', 'y', 'set_name'])\n",
    "    \n",
    "    # Convert to numpy arrays\n",
    "    placements = np.array([int(m[0]) for m in matches])\n",
    "    roles = np.array([int(m[1]) for m in matches])\n",
    "    \n",
    "    # Create DataFrame\n",
    "    df = pd.DataFrame({\n",
    "        'placement_id': placements,\n",
    "        'role_id': roles\n",
    "    })\n",
    "    \n",
    "    # Map role names & colors\n",
    "    df['role_name'] = df['role_id'].map(role_name_map)\n",
    "    df['role_kind'] = df['role_id'].map(role_type_map)\n",
    "    df['led_color'] = df['role_id'].map(role_color_map)\n",
    "    df['set_name'] = df['placement_id'].map(placement_sets)\n",
    "    \n",
    "    # Map coordinates (using get with default for safety)\n",
    "    df['(x,y)'] = df['placement_id'].apply(lambda p: placement_coordinates.get(p, (np.nan, np.nan)))\n",
    "    df['x'] = df['placement_id'].apply(lambda p: placement_coordinates.get(p, (np.nan, np.nan))[0])\n",
    "    df['y'] = df['placement_id'].apply(lambda p: placement_coordinates.get(p, (np.nan, np.nan))[1])\n",
    "\n",
    "    # Map set name\n",
    "    df['set_name'] = df['placement_id'].apply(lambda p: placement_sets.get(p, (np.nan, np.nan)))\n",
    "    \n",
    "    return df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f6b6fb92",
   "metadata": {},
   "outputs": [],
   "source": [
    "### Tests\n",
    "# Let's just take one row from out data frame\n",
    "test_climb = df_climbs.iloc[10000]\n",
    "\n",
    "# Isolate the frames feature\n",
    "test_frames = test_climb['frames']\n",
    "\n",
    "print(test_climb['climb_name'])\n",
    "display(climb_to_DataFrame(test_frames))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "155d841e",
   "metadata": {},
   "source": [
    "## Visualizing the climb from the data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f7af9b4",
   "metadata": {},
   "source": [
    "\n",
    "We'll use our `climb_to_DataFrame` function and then just map the data using a scatter plot overlayed on the image of the TB2."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "899291e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "==================================\n",
    "Mapping frames to the boad\n",
    "==================================\n",
    "\"\"\"\n",
    "\n",
    "def map_climb(climb_name, climb_frames):\n",
    "    # Create figure\n",
    "    fig,ax = plt.subplots(figsize=(17,12))\n",
    "\n",
    "    # Show board image as background\n",
    "    ax.imshow(board_img, extent=[x_min,x_max, y_min, y_max], aspect='equal')\n",
    "\n",
    "    df = climb_to_DataFrame(climb_frames)\n",
    "\n",
    "    # Create heatmap using scatter (only at hold positions)\n",
    "    scatter = ax.scatter(\n",
    "        df['x'],\n",
    "        df['y'],\n",
    "        c=df['led_color'],\n",
    "        alpha=0.85,\n",
    "        edgecolors='black',\n",
    "        linewidths=0.5\n",
    "    )\n",
    "\n",
    "    # Labels\n",
    "    ax.set_title(climb_name)\n",
    "\n",
    "\n",
    "    return fig, ax"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "be1ff408",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_climbs.iloc[10000]['frames']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7fb09aea",
   "metadata": {},
   "outputs": [],
   "source": [
    "test_climb = df_climbs.iloc[10000]\n",
    "\n",
    "map_climb(test_climb['climb_name'], test_climb['frames'])\n",
    "\n",
    "plt.savefig('../images/02_hold_stats/Anna_Got_Me_Clickin', dpi=150, bbox_inches='tight')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd96b297",
   "metadata": {},
   "source": [
    "One can also use the above function to figure out what hold is what by doing something like `map_climb('', p370r6)`. We'll do one better, and display the placement ID on the hold."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dffbf453",
   "metadata": {},
   "source": [
    "## Which placement ID corresponds to which hold?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5c2675be",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "==================================\n",
    "Mapping placement IDs to the board\n",
    "==================================\n",
    "\"\"\"\n",
    "\n",
    "def map_hold_labels(placements):\n",
    "    \"\"\"\n",
    "    Map a climb with placement IDs displayed as text labels instead of colors.\n",
    "    \"\"\"\n",
    "    # Make our placements into string\n",
    "    placements = [str(p) for p in placements]\n",
    "    \n",
    "    # Make an associated frame\n",
    "    frames = 'p'\n",
    "    frames += 'r15p'.join(placements)\n",
    "    frames += 'r15'\n",
    "\n",
    "    # Create figure\n",
    "    fig, ax = plt.subplots(figsize=(17, 12))\n",
    "\n",
    "    # Make a dataframe\n",
    "    df = climb_to_DataFrame(frames)\n",
    "\n",
    "    # Show board image as background\n",
    "    ax.imshow(board_img, extent=[x_min, x_max, y_min, y_max], aspect='auto')\n",
    "    \n",
    "    # Plot text labels instead of scatter points\n",
    "    for _, row in df.iterrows():\n",
    "        ax.text(\n",
    "            row['x'],\n",
    "            row['y'],\n",
    "            str(row['placement_id']),  # The text to display\n",
    "            ha='center',               # Horizontal alignment\n",
    "            va='center',               # Vertical alignment\n",
    "            fontsize=5,\n",
    "            fontweight='bold',\n",
    "            color='white',\n",
    "            bbox=dict(\n",
    "                boxstyle='circle,pad=0.1',  # Circle background\n",
    "                alpha=0.2,\n",
    "                edgecolor='white',\n",
    "                linewidth=1\n",
    "            )\n",
    "        )\n",
    "\n",
    "    # Labels\n",
    "    ax.set_title('', fontsize=16)\n",
    "\n",
    "    return fig, ax\n",
    "\n",
    "\n",
    "def map_single_hold_label(placement, role=6):\n",
    "    \"\"\"\n",
    "    Map a single hold with its placement ID displayed.\n",
    "    \"\"\"\n",
    "    frame_string = f\"p{placement}r{role}\"\n",
    "    return map_hold_labels(f\"Placement {placement}\", frame_string)\n",
    "\n",
    "\n",
    "# Test it\n",
    "fig, ax = map_hold_labels([1175, 1180, 1200])\n",
    "fig.savefig('../images/02_hold_stats/placements_1175_1180_1200.png', dpi=150, bbox_inches='tight')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5117d01",
   "metadata": {},
   "source": [
    "Let's see how the whole board looks. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b0d7fe6",
   "metadata": {},
   "outputs": [],
   "source": [
    "placements = df_placements['placement_id'].tolist()\n",
    "\n",
    "\n",
    "fig, ax = map_hold_labels(placements)\n",
    "fig.savefig('../images/02_hold_stats/all_placement_ids.png', dpi=150, bbox_inches='tight')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf63036d",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49827fce",
   "metadata": {},
   "source": [
    "# Holds Heatmaps"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c2f2fc7",
   "metadata": {},
   "source": [
    "Here we produce a heatmap of the holds on the Tension Board 2 Mirror. We will, under some restrictions if necessary, count how many times a hold appears in the list of unique climbs. We then use this information to create a heat map (subject to those restrictions -- e.g., grade, grade range, hold type, etc.)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a32575cf",
   "metadata": {},
   "source": [
    "We use the usage_count (or hand_usage_count/foot_usage_count/start_usage_count) to create a heatmap overlayed on an image of the TB2 12x12, potentially with a specified grade."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4722ab15",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "==================================\n",
    "Holds heatmap\n",
    "==================================\n",
    "\"\"\"\n",
    "\n",
    "def plot_heatmap(boulder_name=None, grade_range=None, hold_type='all', df_source=df_climbs, board_image=board_img, title_suffix=\"\"):\n",
    "    \"\"\"\n",
    "    Plots a hold usage heatmap for a specific grade, grade range, and hold type.\n",
    "    \n",
    "    Parameters:\n",
    "    -----------\n",
    "    boulder_name : str, optional\n",
    "        Specific boulder name to filter (e.g., '6b/V4', '7a/V6').\n",
    "    grade_range : tuple, optional\n",
    "        Range of numeric difficulties (e.g., (16, 18) for V3-V4).\n",
    "    hold_type : str, optional\n",
    "        Type of hold to visualize. Options: 'all', 'hand', 'foot', 'start', 'middle', 'finish'.\n",
    "    df_source : DataFrame\n",
    "        The source dataframe containing climb data.\n",
    "    board_image : array\n",
    "        The board image to overlay.\n",
    "    title_suffix : str\n",
    "        Extra text for the title.\n",
    "    \"\"\"\n",
    "    \n",
    "    # 1. Define Role Mappings (TB2: 5-8)\n",
    "    role_map = {\n",
    "        'all': {12, 13, 14, 15},\n",
    "        'hand': {12, 13, 14},\n",
    "        'foot': {15},\n",
    "        'start': {12},\n",
    "        'middle': {13},\n",
    "        'finish': {14}\n",
    "    }\n",
    "    \n",
    "    if hold_type not in role_map:\n",
    "        print(f\"Invalid hold_type '{hold_type}'. Use: {list(role_map.keys())}\")\n",
    "        return\n",
    "    \n",
    "    allowed_roles = role_map[hold_type]\n",
    "    \n",
    "    # 2. Filter Data by Grade\n",
    "    if boulder_name:\n",
    "        df_filtered = df_source[df_source['boulder_grade'] == boulder_name]\n",
    "        grade_label = boulder_name\n",
    "    elif grade_range:\n",
    "        min_diff, max_diff = grade_range\n",
    "        df_filtered = df_source[\n",
    "            (df_source['display_difficulty'] >= min_diff) & \n",
    "            (df_source['display_difficulty'] <= max_diff)\n",
    "        ]\n",
    "        # Create readable label from boulder_name range\n",
    "        min_name = df_filtered.groupby('display_difficulty')['boulder_grade'].first().get(min_diff, f\"V{min_diff-10}\")\n",
    "        max_name = df_filtered.groupby('display_difficulty')['boulder_grade'].first().get(max_diff, f\"V{max_diff-10}\")\n",
    "        grade_label = f\"{min_name} to {max_name}\"\n",
    "    else:\n",
    "        df_filtered = df_source\n",
    "        grade_label = \"All Grades\"\n",
    "\n",
    "    if df_filtered.empty:\n",
    "        print(f\"No climbs found for: {boulder_name or grade_range}\")\n",
    "        return\n",
    "\n",
    "    # 3. Count Placement Usage (Filtered by Role)\n",
    "    placement_counts = {}\n",
    "    \n",
    "    for frames in df_filtered['frames'].dropna().unique():\n",
    "        matches = re.findall(r'p(\\d+)r(\\d+)', frames)\n",
    "        \n",
    "        for p_str, r_str in matches:\n",
    "            p_id = int(p_str)\n",
    "            r_id = int(r_str)\n",
    "            \n",
    "            if r_id in allowed_roles:\n",
    "                placement_counts[p_id] = placement_counts.get(p_id, 0) + 1\n",
    "            \n",
    "    # 4. Prepare Data for Plotting\n",
    "    plot_data = []\n",
    "    for pid, count in placement_counts.items():\n",
    "        if pid in placement_coordinates:\n",
    "            x, y = placement_coordinates[pid]\n",
    "            plot_data.append({'x': x, 'y': y, 'count': count})\n",
    "            \n",
    "    if not plot_data:\n",
    "        print(f\"No placements found for hold_type '{hold_type}' in this grade range.\")\n",
    "        return\n",
    "        \n",
    "    df_plot = pd.DataFrame(plot_data)\n",
    "    \n",
    "    # 5. Plot\n",
    "    fig, ax = plt.subplots(figsize=(18, 14))\n",
    "    \n",
    "    ax.imshow(board_image, extent=[x_min, x_max, y_min, y_max], aspect='auto')\n",
    "    \n",
    "    max_count = df_plot['count'].max()\n",
    "    size_scale = 20 + 200 * (df_plot['count'] / max_count if max_count > 0 else 0)\n",
    "    \n",
    "    scatter = ax.scatter(\n",
    "        df_plot['x'],\n",
    "        df_plot['y'],\n",
    "        c=df_plot['count'],\n",
    "        s=size_scale,\n",
    "        cmap='plasma',\n",
    "        alpha=0.8,\n",
    "        edgecolors='black',\n",
    "        linewidths=0.5\n",
    "    )\n",
    "    \n",
    "    ax.set_xlabel('X Position (inches)')\n",
    "    ax.set_ylabel('Y Position (inches)')\n",
    "    \n",
    "    title = f\"{hold_type.capitalize()} Hold Usage - {grade_label} {title_suffix}\".strip()\n",
    "    ax.set_title(title, fontsize=16)\n",
    "    \n",
    "    cbar = plt.colorbar(scatter, ax=ax, shrink=0.5)\n",
    "    cbar.set_label('Usage Count')\n",
    "    \n",
    "    \n",
    "    return fig, ax"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bfd88ce5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# All grade, all holds\n",
    "fig, ax = plot_heatmap()\n",
    "fig.savefig('../images/02_hold_stats/all_holds_all_grades_heatmap.png', dpi=150, bbox_inches='tight')\n",
    "plt.show()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86be9207",
   "metadata": {},
   "source": [
    "There is a distinct lack of usage on holds that are on the left and right edges. This is because the 16ft x 12ft Kilter Board is quite rare. While the middle portion of the climb encompasses all other boards. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c32a1dda",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Specific boulder_name\n",
    "fig, ax = plot_heatmap(boulder_name='6b/V4')\n",
    "fig.savefig('../images/02_hold_stats/all_holds_6b_V4_heatmap.png', dpi=150, bbox_inches='tight')\n",
    "\n",
    "fig, ax = plot_heatmap(boulder_name='7a/V6')\n",
    "fig.savefig('../images/02_hold_stats/all_holds_7a_V6_heatmap.png', dpi=150, bbox_inches='tight')\n",
    "\n",
    "# Specific boulder_name + hold type\n",
    "fig, ax = plot_heatmap(boulder_name='6b/V4', hold_type='start')\n",
    "fig.savefig('../images/02_hold_stats/start_holds_6b_V4_heatmap.png', dpi=150, bbox_inches='tight')\n",
    "\n",
    "fig, ax = plot_heatmap(boulder_name='7a+/V7', hold_type='foot')\n",
    "fig.savefig('../images/02_hold_stats/foot_holds_7a+_V7_heatmap.png', dpi=150, bbox_inches='tight')\n",
    "\n",
    "\n",
    "# Grade range (still works, now shows boulder_name in label)\n",
    "fig, ax = plot_heatmap(grade_range=(18, 20), hold_type='hand')\n",
    "fig.savefig('../images/02_hold_stats/hand_holds_18-20_heatmap.png', dpi=150, bbox_inches='tight')\n",
    "\n",
    "# All grades, specific hold type\n",
    "fig, ax = plot_heatmap(hold_type='finish')\n",
    "fig.savefig('../images/02_hold_stats/finish_holds_all_grades_heatmap.png', dpi=150, bbox_inches='tight')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec96087b",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c68eb1fe",
   "metadata": {},
   "source": [
    "# Default holds\n",
    "\n",
    "Each hold has a default role. Let's see the default roles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7a43767d",
   "metadata": {},
   "outputs": [],
   "source": [
    "holds_string = ''.join([f\"p{pid}r{rid}\" for pid, rid in zip(df_placements['placement_id'], df_placements['default_role_id'])])\n",
    "\n",
    "\n",
    "fig, ax = map_climb('Default Roles', holds_string)\n",
    "\n",
    "fig.savefig('../images/02_hold_stats/default_holds.png')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1495cd5",
   "metadata": {},
   "source": [
    "All of the green, blue, or pink holds are hand holds, and the orange holds are foot holds by default. Let's do a count of how many of each we have. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "87cd49ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_placements.groupby(['default_role_id']).size().reset_index(name='usage_count')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5960bdb",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a95141b1",
   "metadata": {},
   "source": [
    "# Conclusion\n",
    "\n",
    "The main result of this notebook is a geometric picture of the board: some holds and regions appear far more often than others, and hand/foot usage is not distributed uniformly across the wall.\n",
    "\n",
    "That matters for modelling. A hold is not only a location; it is also part of an empirical usage pattern. In the next notebook I build on this by assigning **difficulty information** to individual holds, first globally and then by role and angle."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.14.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}