Grading_Equation_College_Comparison.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "# Pull the data from the Google Drive and Move it to the folder 'New Data'\n",
    "\n",
    "import os\n",
    "import shutil\n",
    "\n",
    "# Source and destination directory paths\n",
    "source_dir = r\"G:\\My Drive\\Pitching Data\\New Data\"\n",
    "destination_dir = r\"C:\\Users\\Biomechanics\\PycharmProjects\\IRvsPro\\files\\Pitching Data\\New Data\"\n",
    "\n",
    "# Iterate over files in the source directory\n",
    "for file_name in os.listdir(source_dir):\n",
    "    if file_name.endswith('.xml'):\n",
    "        source_file_path = os.path.join(source_dir, file_name)\n",
    "        destination_file_path = os.path.join(destination_dir, file_name)\n",
    "        shutil.move(source_file_path, destination_file_path)\n",
    "        print(f\"File '{file_name}' moved to: {destination_file_path}\")\n",
    "\n",
    "print(\"XML files copied successfully!\")\n"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "939ad176ecfe11ca"
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "# This code is *chef's kiss* perfect. Does everything for parsing for variables, iterates over it \n",
    "# multiple times, and includes the specific filename for each trial. I love it. \n",
    "\n",
    "import sqlite3\n",
    "import xml.etree.ElementTree as ET\n",
    "import os\n",
    "import re\n",
    "\n",
    "# Connect to the SQLite database\n",
    "conn = sqlite3.connect(\"grading_equation_new.db\")\n",
    "cursor = conn.cursor()\n",
    "\n",
    "# Drop the table if it exists\n",
    "cursor.execute('DROP TABLE IF EXISTS variables')\n",
    "\n",
    "# Create the variables table\n",
    "cursor.execute('''\n",
    "    CREATE TABLE variables (\n",
    "        id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "        session_data_id TEXT NOT NULL,\n",
    "        file_name TEXT NOT NULL,\n",
    "        Pitch TEXT,\n",
    "        Score REAL,\n",
    "        Linear_Pelvis_Speed REAL,\n",
    "        HSS_Footplant REAL,\n",
    "        Pelvis_Ang_Footplant REAL,\n",
    "        Trunk_Ang_Footplant REAL,\n",
    "        Pelvic_Obl REAL,\n",
    "        Front_Leg_Brace REAL,\n",
    "        Front_Leg_Var_Val REAL,\n",
    "        Lead_Leg_GRF_y REAL,\n",
    "        Lead_Leg_GRF_z REAL,\n",
    "        Lead_Leg_GRF_x REAL,\n",
    "        Horizontal_Abduction REAL,\n",
    "        Shld_ER_Footplant REAL,\n",
    "        Shld_ER_Max REAL,\n",
    "        Lateral_Trunk_Tilt REAL,\n",
    "        Pelvis_Ang_Velo REAL,\n",
    "        Torso_Ang_Velo REAL,\n",
    "        Arm_Ang_Velo REAL,\n",
    "        MPH REAL\n",
    "    )\n",
    "''')\n",
    "\n",
    "# Commit the changes before proceeding to parsing XML files\n",
    "conn.commit()\n",
    "\n",
    "# List of XML files to parse (replace this with your actual directory path)\n",
    "directory_path = \"C:\\\\Users\\\\Biomechanics\\\\PycharmProjects\\\\IRvsPro\\\\files\\\\Pitching Data\\\\New Data\"\n",
    "\n",
    "while True:\n",
    "    # Get a list of XML files in the specified directory\n",
    "    xml_files = [os.path.join(directory_path, file) for file in os.listdir(directory_path) if file.endswith(\".xml\")]\n",
    "\n",
    "    # Track the number of new data entries\n",
    "    new_data_entries = 0\n",
    "\n",
    "    for file_name in xml_files:\n",
    "        try:\n",
    "            # Check if the file has already been processed\n",
    "            cursor.execute('SELECT COUNT(*) FROM variables WHERE file_name = ?', (file_name,))\n",
    "            count = cursor.fetchone()[0]\n",
    "\n",
    "            if count == 0:\n",
    "                # Parse the XML file\n",
    "                tree = ET.parse(file_name)\n",
    "                root = tree.getroot()\n",
    "\n",
    "                # Extract session_data_id from the file name\n",
    "                session_data_id_match = re.search(r\"session_data_(.+)\\.xml\", file_name)\n",
    "                if session_data_id_match:\n",
    "                    session_data_id = session_data_id_match.group(1)\n",
    "                else:\n",
    "                    session_data_id = None\n",
    "\n",
    "                # Check if session_data_id is None, and skip the file if it is\n",
    "                if session_data_id is not None:\n",
    "                    # Iterate over all occurrences of \"owner\" element with a value containing \"Fastball\"\n",
    "                    for owner_element in root.findall('.//owner'):\n",
    "                        if 'Fastball' in owner_element.get('value', ''):\n",
    "                            fastball_owner = owner_element\n",
    "                            # Extract and insert data into the database\n",
    "                            linear_pelvis_speed = None\n",
    "                            hss_footplant = None\n",
    "                            pelvis_ang_fp = None\n",
    "                            trunk_ang_fp = None\n",
    "                            pelvis_obl = None\n",
    "                            front_leg_brace = None\n",
    "                            front_leg_var_val = None\n",
    "                            lead_leg_grf_y = None\n",
    "                            lead_leg_grf_z = None\n",
    "                            lead_leg_grf_x = None\n",
    "                            horizontal_abduction = None\n",
    "                            shld_er_fp = None\n",
    "                            shld_er_max = None\n",
    "                            lateral_trunk_tilt = None\n",
    "                            pelvis_ang_velo = None\n",
    "                            torso_ang_velo = None\n",
    "                            arm_ang_velo = None\n",
    "                            \n",
    "                            # Extract the value of \"owner\"\n",
    "                            pitch_value = fastball_owner.get('value', '')\n",
    "    \n",
    "                            for variable_element in fastball_owner.findall('.//name[@value]'):\n",
    "                                variable_name = variable_element.attrib['value']\n",
    "                                component_x_element = variable_element.find('./component[@value=\"X\"]')\n",
    "                                component_y_element = variable_element.find('./component[@value=\"Y\"]')\n",
    "                                component_z_element = variable_element.find('./component[@value=\"Z\"]')                           \n",
    "    \n",
    "                                if variable_name == \"MaxPelvisLinearVel_MPH\" and component_y_element is not None:\n",
    "                                    linear_pelvis_speed = float(component_y_element.attrib['data'].replace(',', '.'))\n",
    "                                elif variable_name == \"Hip Shoulders Sep@Footstrike\" and component_z_element is not None:\n",
    "                                    hss_footplant = float(component_z_element.attrib['data'].replace(',', '.'))\n",
    "                                elif variable_name == \"Pelvis_Angle@Footstrike\" and component_z_element is not None:\n",
    "                                    pelvis_ang_fp = float(component_z_element.attrib['data'].replace(',', '.'))\n",
    "                                elif variable_name == \"Trunk_Angle@Footstrike\" and component_z_element is not None:\n",
    "                                    trunk_ang_fp = float(component_z_element.attrib['data'].replace(',', '.'))\n",
    "                                elif variable_name == \"Lead_Knee_Angle@Footstrike\" and component_x_element is not None:\n",
    "                                    lead_knee_ang_at_footstrike = float(component_x_element.attrib['data'].replace(',', '.'))\n",
    "                                    lead_knee_ang_at_release = float(root.find('.//name[@value=\"Lead_Knee_Angle@Release\"]/component[@value=\"X\"]').attrib['data'].replace(',', '.'))\n",
    "                                    front_leg_brace = lead_knee_ang_at_footstrike - lead_knee_ang_at_release\n",
    "                                if variable_name == \"Lead_Knee_Angle@Footstrike\" and component_y_element is not None:\n",
    "                                    lead_knee_var_val_fp = float(component_y_element.attrib['data'].replace(',', '.'))\n",
    "                                elif variable_name == \"Lead_Knee_Angle@Release\" and component_y_element is not None:\n",
    "                                    lead_knee_var_val_rel = float(component_y_element.attrib['data'].replace(',', '.'))\n",
    "                                    # Calculate the difference and store in front_leg_var_val\n",
    "                                    front_leg_var_val = lead_knee_var_val_fp - lead_knee_var_val_rel\n",
    "                                if variable_name == \"Pelvis_Angle@Footstrike\" and component_z_element is not None:\n",
    "                                    pelvis_obl_fp = float(component_y_element.attrib['data'].replace(',', '.'))\n",
    "                                    pelvis_angle_release_element = root.find('.//name[@value=\"Pelvis_Angle@Release\"]/component[@value=\"Y\"]')\n",
    "                                    if pelvis_angle_release_element is not None:\n",
    "                                        pelvis_obl_release = float(pelvis_angle_release_element.attrib['data'].replace(',', '.'))\n",
    "                                        pelvis_obl = pelvis_obl_release - pelvis_obl_fp\n",
    "                                elif variable_name == \"Lead_Leg_GRF_min\" and component_y_element is not None:\n",
    "                                    lead_leg_grf_y = abs(float(component_y_element.attrib['data'].replace(',', '.')))\n",
    "                                elif variable_name == \"Lead_Leg_GRF_max\" and component_z_element is not None:\n",
    "                                    lead_leg_grf_z = float(component_z_element.attrib['data'].replace(',', '.'))\n",
    "                                if variable_name == \"Lead_Leg_GRF_max\" and component_x_element is not None:\n",
    "                                    lat_force_max = float(component_x_element.attrib['data'].replace(',', '.'))\n",
    "                                elif variable_name == \"Lead_Leg_GRF_min\" and component_x_element is not None:\n",
    "                                    lat_force_min = float(component_x_element.attrib['data'].replace(',', '.'))\n",
    "                                    # Calculate the sum of absolute values and store in lead_leg_grf_x\n",
    "                                    lead_leg_grf_x = abs(lat_force_max) + abs(lat_force_min)\n",
    "                                elif variable_name == \"Pitching_Shoulder_Angle@Footstrike\" and component_x_element is not None:\n",
    "                                    horizontal_abduction = abs(float(component_x_element.attrib['data'].replace(',', '.')))\n",
    "                                if variable_name == \"Pitching_Shoulder_Angle@Footstrike\":\n",
    "                                    if component_z_element is not None:\n",
    "                                        shld_er_fp = float(component_z_element.attrib['data'].replace(',', '.'))\n",
    "                                elif variable_name == \"Pitching_Shoulder_Angle_Max\" and component_z_element is not None:\n",
    "                                    shld_er_max = abs(float(component_z_element.attrib['data'].replace(',', '.')))\n",
    "                                elif variable_name == \"Trunk_wrt_Pelvis_FE@Release\" and component_y_element is not None:\n",
    "                                    lateral_trunk_tilt = float(component_y_element.attrib['data'].replace(',', '.'))\n",
    "                                elif variable_name == \"Pelvis_Ang_Vel_max\" and component_x_element is not None:\n",
    "                                    pelvis_ang_velo = float(component_x_element.attrib['data'].replace(',', '.'))\n",
    "                                elif variable_name == \"Thorax_Ang_Vel_max\" and component_x_element is not None:\n",
    "                                    torso_ang_velo = float(component_x_element.attrib['data'].replace(',', '.'))\n",
    "                                elif variable_name == \"Pitching_Humerus_Ang_Vel_max\" and component_x_element is not None:\n",
    "                                    arm_ang_velo = float(component_x_element.attrib['data'].replace(',', '.'))\n",
    "                                \n",
    "                                linear_pelvis_speed = linear_pelvis_speed if linear_pelvis_speed is not None else 4\n",
    "                                hss_footplant = hss_footplant if hss_footplant is not None else 30\n",
    "                                pelvis_ang_fp = pelvis_ang_fp if pelvis_ang_fp is not None else 60\n",
    "                                trunk_ang_fp = trunk_ang_fp if trunk_ang_fp is not None else 90\n",
    "                                pelvis_obl = pelvis_obl if pelvis_obl is not None else 2\n",
    "                                front_leg_brace = front_leg_brace if front_leg_brace is not None else 8\n",
    "                                front_leg_var_val = front_leg_var_val if front_leg_var_val is not None else 0\n",
    "                                lead_leg_grf_y = lead_leg_grf_y if lead_leg_grf_y is not None else .7\n",
    "                                lead_leg_grf_z = lead_leg_grf_z if lead_leg_grf_z is not None else 1.5\n",
    "                                lead_leg_grf_x = lead_leg_grf_x if lead_leg_grf_x is not None else .25\n",
    "                                horizontal_abduction = horizontal_abduction if horizontal_abduction is not None else 20\n",
    "                                shld_er_fp = shld_er_fp if shld_er_fp is not None else 45\n",
    "                                lateral_trunk_tilt = lateral_trunk_tilt if lateral_trunk_tilt is not None else 35\n",
    "                                shld_er_max = shld_er_max if shld_er_max is not None else 160\n",
    "                                pelvis_ang_velo = pelvis_ang_velo if pelvis_ang_velo is not None else 600\n",
    "                                torso_ang_velo = torso_ang_velo if torso_ang_velo is not None else 1000\n",
    "                                arm_ang_velo = arm_ang_velo if arm_ang_velo is not None else 5000\n",
    "    \n",
    "                                # Insert the data into the database\n",
    "                            cursor.execute('''\n",
    "                                INSERT INTO variables (\n",
    "                                    session_data_id,\n",
    "                                    file_name,\n",
    "                                    Pitch,\n",
    "                                    Linear_Pelvis_Speed,\n",
    "                                    HSS_Footplant,\n",
    "                                    Pelvis_Ang_Footplant,\n",
    "                                    Trunk_Ang_Footplant,\n",
    "                                    Pelvic_Obl,\n",
    "                                    Front_Leg_Brace,\n",
    "                                    Front_Leg_Var_Val,\n",
    "                                    Lead_Leg_GRF_y,\n",
    "                                    Lead_Leg_GRF_z,\n",
    "                                    Lead_Leg_GRF_x,\n",
    "                                    Horizontal_Abduction,\n",
    "                                    Shld_ER_Footplant,\n",
    "                                    Shld_ER_Max,\n",
    "                                    Lateral_Trunk_Tilt,\n",
    "                                    Pelvis_Ang_Velo,\n",
    "                                    Torso_Ang_Velo,\n",
    "                                    Arm_Ang_Velo,\n",
    "                                    MPH\n",
    "                                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n",
    "                            ''', (session_data_id, file_name, pitch_value, linear_pelvis_speed, hss_footplant, pelvis_ang_fp, trunk_ang_fp, pelvis_obl, front_leg_brace, front_leg_var_val, lead_leg_grf_y, lead_leg_grf_z, lead_leg_grf_x, horizontal_abduction, shld_er_fp, shld_er_max, lateral_trunk_tilt, pelvis_ang_velo, torso_ang_velo, arm_ang_velo, None))\n",
    "    \n",
    "                            # Increment the count of new data entries\n",
    "                            new_data_entries += 1\n",
    "                else:\n",
    "                    print(f\"Skipping file {file_name} due to missing session_data_id.\")\n",
    "\n",
    "        except Exception as e:\n",
    "            # Print the error and the file name where it occurred\n",
    "            print(f\"Error processing file {file_name}: {e}\")\n",
    "\n",
    "    # Commit the changes for each file\n",
    "    conn.commit()\n",
    "\n",
    "    # If no new data entries, break out of the loop\n",
    "    if new_data_entries == 0:\n",
    "        break\n",
    "\n",
    "# Close the connection\n",
    "conn.close()\n",
    "\n",
    "print(\"Data inserted into the database.\")"
   ],
   "metadata": {
    "collapsed": true
   },
   "id": "initial_id",
   "execution_count": 0
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "# Successfully adds MPH to the appropriate spot in the database\n",
    "\n",
    "import os\n",
    "import xml.etree.ElementTree as ET\n",
    "import re\n",
    "import sqlite3\n",
    "\n",
    "# Replace this with your actual directory paths\n",
    "directory_path = \"C:\\\\Users\\\\Biomechanics\\\\PycharmProjects\\\\IRvsPro\\\\files\\\\Pitching Data\\\\New Data\"\n",
    "\n",
    "# Connect to the database\n",
    "conn = sqlite3.connect(\"grading_equation_new.db\")\n",
    "cursor = conn.cursor()\n",
    "\n",
    "# Get a list of XML files in the specified directory\n",
    "xml_files = [os.path.join(directory_path, file) for file in os.listdir(directory_path) if file.endswith(\".xml\")]\n",
    "\n",
    "# Track the number of new data entries\n",
    "new_data_entries = 0\n",
    "\n",
    "for file_name in xml_files:\n",
    "    try:\n",
    "        # Check if the file has already been processed\n",
    "        cursor.execute('SELECT COUNT(*) FROM variables WHERE file_name = ?', (file_name,))\n",
    "        count = cursor.fetchone()[0]\n",
    "\n",
    "        if count == 0:\n",
    "            # Parse the XML file\n",
    "            tree = ET.parse(file_name)\n",
    "            root = tree.getroot()\n",
    "\n",
    "            # Extract session_data_id from the file name\n",
    "            session_data_id_match = re.search(r\"session_(.+)\\.xml\", file_name)\n",
    "            if session_data_id_match:\n",
    "                session_data_id = session_data_id_match.group(1)\n",
    "            else:\n",
    "                session_data_id = None\n",
    "\n",
    "            # Check if session_data_id is None, and skip the file if it is\n",
    "            if session_data_id is not None:\n",
    "                # Iterate over all occurrences of \"Measurement\" element\n",
    "                for measurement_element in root.findall('.//Measurement'):\n",
    "                    pitch_value = measurement_element.get('Filename')\n",
    "                    used_element = measurement_element.find('./Fields/Used')\n",
    "                    \n",
    "                    if pitch_value is not None and used_element is not None:\n",
    "                        # Replace \"qtm\" with \"c3d\" in pitch_value\n",
    "                        pitch_value = pitch_value.replace(\"qtm\", \"c3d\")\n",
    "\n",
    "                        # Print statement for debugging\n",
    "                        print(f\"Filename: {pitch_value}, Used: {used_element.text}\")\n",
    "\n",
    "                        if 'Fastball' in pitch_value and used_element.text == 'True':\n",
    "                            # Extract the values of \"Comments\" and \"Ball_speed\"\n",
    "                            comments_value_element = measurement_element.find('./Fields/Comments')\n",
    "\n",
    "                            if comments_value_element is not None:\n",
    "                                print(f\"MPH: {comments_value_element.text}\")\n",
    "                                print(f\"Session ID: {session_data_id}\")\n",
    "\n",
    "                                mph_value = float(comments_value_element.text)\n",
    "\n",
    "                                # Update the MPH value in the \"variables\" table\n",
    "                                cursor.execute('''\n",
    "                                    UPDATE variables\n",
    "                                    SET MPH = ?\n",
    "                                    WHERE session_data_id = ? AND Pitch = ?\n",
    "                                ''', (mph_value, session_data_id, pitch_value))\n",
    "\n",
    "                                # Increment the new data entries counter\n",
    "                                new_data_entries += 1\n",
    "                    else:\n",
    "                        print(\"Filename or Used element not found.\")\n",
    "\n",
    "    except Exception as e:\n",
    "        print(f\"Error processing file {file_name}: {e}\")\n",
    "\n",
    "# Commit the changes\n",
    "conn.commit()\n",
    "\n",
    "# Close the connection\n",
    "conn.close()\n",
    "\n",
    "print(f\"Number of new data entries: {new_data_entries}\")\n",
    "print(\"Data inserted into the database.\")\n"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "ec8c85b819200cd2"
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "# Generates Score\n",
    "\n",
    "import sqlite3\n",
    "\n",
    "# Connect to the SQLite database\n",
    "conn = sqlite3.connect(\"grading_equation_new.db\")\n",
    "cursor = conn.cursor()\n",
    "\n",
    "# Select all rows from the 'variables' table\n",
    "cursor.execute('SELECT * FROM variables')\n",
    "rows = cursor.fetchall()\n",
    "\n",
    "# Iterate through each row and calculate the score\n",
    "for row in rows:\n",
    "    linear_pelvis_speed = row[5]\n",
    "    hss_footplant = row[6]\n",
    "    pelvis_ang_fp = row[7]\n",
    "    trunk_ang_fp = row[8]\n",
    "    pelvis_obl = row[9]\n",
    "    front_leg_brace = row[10]\n",
    "    front_leg_var_val = row[11]\n",
    "    lead_leg_grf_y = row[12]\n",
    "    lead_leg_grf_z = row[13]\n",
    "    lead_leg_grf_x = row[14]\n",
    "    horizontal_abduction = row[15]\n",
    "    shld_er_fp = row[16]\n",
    "    shld_er_max = row[17]\n",
    "    lateral_trunk_tilt = row[18]\n",
    "    pelvis_ang_velo = row[19]\n",
    "    torso_ang_velo = row[20]\n",
    "    arm_ang_velo = row[21]\n",
    "    MPH = row[22]\n",
    "\n",
    "    # Check for None values and replace with 0\n",
    "    linear_pelvis_speed = linear_pelvis_speed if linear_pelvis_speed is not None else 4\n",
    "    hss_footplant = hss_footplant if hss_footplant is not None else 30\n",
    "    pelvis_ang_fp = pelvis_ang_fp if pelvis_ang_fp is not None else 60\n",
    "    trunk_ang_fp = trunk_ang_fp if trunk_ang_fp is not None else 90\n",
    "    pelvis_obl = pelvis_obl if pelvis_obl is not None else 2\n",
    "    front_leg_brace = front_leg_brace if front_leg_brace is not None else 8\n",
    "    front_leg_var_val = front_leg_var_val if front_leg_var_val is not None else 0\n",
    "    lead_leg_grf_y = lead_leg_grf_y if lead_leg_grf_y is not None else .7\n",
    "    lead_leg_grf_z = lead_leg_grf_z if lead_leg_grf_z is not None else 1.5\n",
    "    lead_leg_grf_x = lead_leg_grf_x if lead_leg_grf_x is not None else .25\n",
    "    horizontal_abduction = horizontal_abduction if horizontal_abduction is not None else 20\n",
    "    shld_er_fp = shld_er_fp if shld_er_fp is not None else 45\n",
    "    lateral_trunk_tilt = lateral_trunk_tilt if lateral_trunk_tilt is not None else 35\n",
    "    shld_er_max = shld_er_max if shld_er_max is not None else 160\n",
    "    pelvis_ang_velo = pelvis_ang_velo if pelvis_ang_velo is not None else 600\n",
    "    torso_ang_velo = torso_ang_velo if torso_ang_velo is not None else 1000\n",
    "    arm_ang_velo = arm_ang_velo if arm_ang_velo is not None else 5000\n",
    "    MPH = MPH if MPH is not None else 75\n",
    "\n",
    "    # Calculate the score using the specified equation\n",
    "    score = (0.05 * linear_pelvis_speed) + (0.1 * front_leg_brace) + \\\n",
    "            (2 * lead_leg_grf_y) + (.05 * lead_leg_grf_z) + (2 * lead_leg_grf_x) + \\\n",
    "            (0.5 * horizontal_abduction) - (0.05 * lateral_trunk_tilt) + (0.01 * torso_ang_velo) + (.1 * pelvis_obl) + (.2 * trunk_ang_fp) - (.15 * pelvis_ang_fp) + (.05 * shld_er_max) + (.1 * front_leg_var_val) + (.01 * pelvis_ang_velo) + MPH\n",
    "\n",
    "    # Update the 'Score' column in the database\n",
    "    cursor.execute('UPDATE variables SET Score = ? WHERE id = ?', (score, row[0]))\n",
    "\n",
    "# Commit the changes and close the connection\n",
    "conn.commit()\n",
    "conn.close()\n",
    "\n",
    "print(\"Scores updated in the database.\")"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "5fbcd08929ca7e6c"
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "# Generates percentile graphs and exports both and PDF and Word Doc for the person\n",
    "\n",
    "import os\n",
    "import sqlite3\n",
    "import scipy.stats as stats\n",
    "import matplotlib.pyplot as plt\n",
    "from docx import Document\n",
    "from matplotlib.backends.backend_pdf import PdfPages\n",
    "\n",
    "# Connect to the SQLite databases\n",
    "conn_new = sqlite3.connect(\"grading_equation_new.db\")\n",
    "conn_reference = sqlite3.connect(\"grading_equation_reference_data_college.db\")\n",
    "cursor_new = conn_new.cursor()\n",
    "cursor_reference = conn_reference.cursor()\n",
    "\n",
    "# Define the directory to save exported graphs\n",
    "export_dir = \"C:\\\\Users\\\\Biomechanics\\\\PycharmProjects\\\\IRvsPro\\\\data\\\\Exported Data\"\n",
    "\n",
    "try:\n",
    "    # Get the session_data_id for naming the Word document\n",
    "    cursor_new.execute('SELECT session_data_id FROM variables LIMIT 1')\n",
    "    session_data_id = cursor_new.fetchone()[0]\n",
    "\n",
    "    # Define the filename for the Word document\n",
    "    docx_filename = os.path.join(export_dir, f\"plots_{session_data_id}.docx\")\n",
    "\n",
    "    # Create a Word document to save the plots\n",
    "    doc = Document()\n",
    "\n",
    "    # Create a PDF file to save the plots\n",
    "    pdf_filename = os.path.join(export_dir, f\"plots_{session_data_id}.pdf\")\n",
    "    pdf_pages = PdfPages(pdf_filename)\n",
    "\n",
    "    # Get the list of columns in the new database\n",
    "    cursor_new.execute(\"PRAGMA table_info(variables)\")\n",
    "    column_names = [row[1] for row in cursor_new.fetchall() if row[1] not in ('id', 'session_data_id', 'file_name', 'Pitch')]\n",
    "    \n",
    "    # Retrieve the row with the highest score from the new database\n",
    "    cursor_new.execute('SELECT * FROM variables ORDER BY Score DESC LIMIT 1')\n",
    "    highest_score_row = cursor_new.fetchone()\n",
    "    \n",
    "    # Get the values from the highest scoring row\n",
    "    highest_score_values = highest_score_row[4:]  # Exclude 'id', 'session_data_id', 'file_name', and 'Pitch'\n",
    "    \n",
    "    for i, column_name in enumerate(column_names):\n",
    "        # Retrieve the reference values from the reference database\n",
    "        cursor_reference.execute(f\"SELECT {column_name} FROM variables\")\n",
    "        reference_values = cursor_reference.fetchall()\n",
    "        reference_values = [value[0] for value in reference_values if value[0] is not None]\n",
    "        \n",
    "        # Calculate the percentile for the current variable\n",
    "        percentile = stats.percentileofscore(reference_values, highest_score_values[i], kind='mean')\n",
    "        \n",
    "        # Create a new figure with black background\n",
    "        fig = plt.figure(facecolor='#181818')\n",
    "        \n",
    "        # Create axes with charcoal background\n",
    "        ax = fig.add_subplot(111, facecolor='#303030')\n",
    "        \n",
    "        # Adjust the size of the figure\n",
    "        plt.subplots_adjust(bottom=0.2)  # Increase the bottom margin to create space for annotation\n",
    "\n",
    "        # Create a histogram for the reference values with blue bars\n",
    "        ax.hist(reference_values, bins=20, color='cornflowerblue', alpha=0.7, label='Reference Data')\n",
    "        \n",
    "        # Add a vertical line for the highest scoring value\n",
    "        ax.axvline(x=highest_score_values[i], color='red', linestyle='dashed', linewidth=2, label='Highest Score')\n",
    "        \n",
    "        # Add labels and title with white color\n",
    "        ax.set_xlabel(column_name, color='slategrey')\n",
    "        ax.set_ylabel('Frequency', color='slategrey')\n",
    "        ax.set_title(f'Histogram of {column_name}', color='lightgrey')\n",
    "        \n",
    "        # Set the color of the gridlines\n",
    "        ax.grid(color='dimgrey')\n",
    "\n",
    "        # Set the color of ticks and spines\n",
    "        ax.tick_params(axis='x', colors='dimgrey')\n",
    "        ax.tick_params(axis='y', colors='dimgrey')\n",
    "        ax.spines['bottom'].set_color('slategrey')\n",
    "        ax.spines['top'].set_color('slategrey')\n",
    "        ax.spines['left'].set_color('slategrey')\n",
    "        ax.spines['right'].set_color('slategrey')\n",
    "        \n",
    "        # Add legend with white color\n",
    "        ax.legend(facecolor='black', edgecolor='grey', prop={'size': 'small'}, labelcolor='grey')\n",
    "        \n",
    "        # Round the percentile score and the corresponding value\n",
    "        rounded_percentile = round(percentile, 2)\n",
    "        rounded_value = round(highest_score_values[i], 2)\n",
    "\n",
    "        # Add annotation for percentile score and corresponding value\n",
    "        ax.annotate(f'Percentile: {rounded_percentile}%\\nValue: {rounded_value}',\n",
    "            xy=(highest_score_values[i], 0),\n",
    "            xytext=(1.0, -0.15),  # Adjusted position below x-axis label in bottom right\n",
    "            textcoords='axes fraction',  # Coordinates relative to axes\n",
    "            ha='right', va='top',  # Horizontal and vertical alignment\n",
    "            fontsize=12, color='white')\n",
    "        \n",
    "        # Save the graph to a file (PNG)\n",
    "        filename = os.path.join(export_dir, f'{column_name}.png')\n",
    "        plt.savefig(filename)\n",
    "        \n",
    "        # Add the graph to the Word document\n",
    "        doc.add_picture(filename)\n",
    "        \n",
    "        # Save the graph to the PDF\n",
    "        pdf_pages.savefig(fig)\n",
    "        \n",
    "        # Clear the current figure to create a new one for the next variable\n",
    "        plt.clf()\n",
    "        \n",
    "        print(f\"Graph for {column_name} exported successfully.\")\n",
    "        \n",
    "    # Close the PDF pages\n",
    "    pdf_pages.close()\n",
    "\n",
    "    # Save the Word document\n",
    "    doc.save(docx_filename)\n",
    "    \n",
    "except sqlite3.Error as e:\n",
    "    print(\"Error:\", e)\n",
    "    \n",
    "finally:\n",
    "    # Close the connections\n",
    "    conn_new.close()\n",
    "    conn_reference.close()\n"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "480065100310960a",
   "execution_count": null
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "import os\n",
    "import shutil\n",
    "\n",
    "# Source directory path\n",
    "source_dir = r\"C:\\Users\\Biomechanics\\PycharmProjects\\IRvsPro\\files\\Pitching Data\\New Data\"\n",
    "\n",
    "# Destination directory paths\n",
    "kinematic_data_dir = r\"C:\\Users\\Biomechanics\\PycharmProjects\\IRvsPro\\files\\Pitching Data\\Kinematic Data\\College\"\n",
    "mph_data_dir = r\"C:\\Users\\Biomechanics\\PycharmProjects\\IRvsPro\\files\\Pitching Data\\MPH Data\\College\"\n",
    "\n",
    "# Iterate over files in the source directory\n",
    "for file_name in os.listdir(source_dir):\n",
    "    if 'data' in file_name.lower():\n",
    "        source_file_path = os.path.join(source_dir, file_name)\n",
    "        destination_file_path = os.path.join(kinematic_data_dir, file_name)\n",
    "        shutil.move(source_file_path, destination_file_path)\n",
    "        print(f\"File '{file_name}' moved to: {destination_file_path}\")\n",
    "    else:\n",
    "        source_file_path = os.path.join(source_dir, file_name)\n",
    "        destination_file_path = os.path.join(mph_data_dir, file_name)\n",
    "        shutil.move(source_file_path, destination_file_path)\n",
    "        print(f\"File '{file_name}' moved to: {destination_file_path}\")\n",
    "\n",
    "print(\"Files moved successfully!\")\n"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "e9413acfc8706119"
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "# Generates percentiles\n",
    "\n",
    "import os\n",
    "import sqlite3\n",
    "import scipy.stats as stats\n",
    "\n",
    "# Connect to the SQLite databases\n",
    "conn_new = sqlite3.connect(\"grading_equation_new.db\")\n",
    "conn_reference = sqlite3.connect(\"grading_equation_reference_data_College.db\")\n",
    "cursor_new = conn_new.cursor()\n",
    "cursor_reference = conn_reference.cursor()\n",
    "\n",
    "try:\n",
    "    # Get the list of columns in the new database\n",
    "    cursor_new.execute(\"PRAGMA table_info(variables)\")\n",
    "    column_names = [row[1] for row in cursor_new.fetchall() if row[1] not in ('id', 'session_data_id', 'file_name', 'Pitch')]\n",
    "    \n",
    "    # Retrieve the row with the highest score from the new database\n",
    "    cursor_new.execute('SELECT * FROM variables ORDER BY Score DESC LIMIT 1')\n",
    "    highest_score_row = cursor_new.fetchone()\n",
    "    \n",
    "    # Get the values from the highest scoring row\n",
    "    highest_score_values = highest_score_row[4:]  # Exclude 'id', 'session_data_id', 'file_name', and 'Pitch'\n",
    "    \n",
    "    # Iterate over each column starting from index 0\n",
    "    for i, column_name in enumerate(column_names):\n",
    "        # Retrieve the reference values from the reference database\n",
    "        cursor_reference.execute(f\"SELECT {column_name} FROM variables\")\n",
    "        reference_values = cursor_reference.fetchall()\n",
    "        reference_values = [value[0] for value in reference_values if value[0] is not None]\n",
    "        \n",
    "        # Calculate the percentile of the variable value compared to reference data\n",
    "        percentile = stats.percentileofscore(reference_values, highest_score_values[i], kind='mean')\n",
    "        \n",
    "        # Print the percentile\n",
    "        print(f\"Percentile for {column_name}: {percentile}%\")\n",
    "        \n",
    "except sqlite3.Error as e:\n",
    "    print(\"Error:\", e)\n",
    "    \n",
    "finally:\n",
    "    # Close the connections\n",
    "    conn_new.close()\n",
    "    conn_reference.close()\n"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "ba44d93d73229fb3"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}