Dataset cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Module 2 - Mini-project 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sklearn.feature_extraction.text import CountVectorizer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Concaténation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.read_csv(\"C:/Users/amandine.gauberville/dataV2-labs/module-2/mini-project2/route-finder-3rd-5.6.csv\")\n",
    "df2 = pd.read_csv(\"C:/Users/amandine.gauberville/dataV2-labs/module-2/mini-project2/route-finder-rock-5.10-5.12d.csv\")\n",
    "df3 = pd.read_csv(\"C:/Users/amandine.gauberville/dataV2-labs/module-2/mini-project2/route-finder-rock-5.7-5.10a-b.csv\")\n",
    "df4 = pd.read_csv(\"C:/Users/amandine.gauberville/dataV2-labs/module-2/mini-project2/route-finder-rock-5.13a-5.14d.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat([df1,df2,df3,df4])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Route', 'Location', 'URL', 'Avg Stars', 'Your Stars', 'Route Type',\n",
       "       'Rating', 'Pitches', 'Length', 'Area Latitude', 'Area Longitude'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 3279 entries, 0 to 278\n",
      "Data columns (total 11 columns):\n",
      " #   Column          Non-Null Count  Dtype  \n",
      "---  ------          --------------  -----  \n",
      " 0   Route           3279 non-null   object \n",
      " 1   Location        3279 non-null   object \n",
      " 2   URL             3279 non-null   object \n",
      " 3   Avg Stars       3279 non-null   float64\n",
      " 4   Your Stars      3279 non-null   int64  \n",
      " 5   Route Type      3279 non-null   object \n",
      " 6   Rating          3279 non-null   object \n",
      " 7   Pitches         3279 non-null   int64  \n",
      " 8   Length          2834 non-null   float64\n",
      " 9   Area Latitude   3279 non-null   float64\n",
      " 10  Area Longitude  3279 non-null   float64\n",
      "dtypes: float64(4), int64(2), object(5)\n",
      "memory usage: 307.4+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.rename(columns={'Avg Stars': 'Stars_Avg'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Vérification des données vide"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([False, False, False, False, False, False, False, False, False,\n",
       "       False, False])"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns.isna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.set_index('Route', inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Création d'une liste de pays"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries_list = [\"Albania\", \"Austria\", \"Belgium\", \"Bosnia-Herzegovina\", \"Bulgaria\", \"Croatia\", \"Cyprus\", \"Czech Republic\", \"Denmark\", \"Faroe Islands\", \"Finland\", \"France\", \"Germany\", \"Gibraltar\", \"Greece\", \"Hungary\", \"Iceland\", \"Ireland\", \"Italy\", \"Kosovo\", \"Lichtenstein\", \"Lithuania\", \"Luxembourg\", \"Malta\", \"Montenegro\", \"Netherlands\", \"North Macedonia\", \"Northern Ireland\", \"Norway\", \"Poland\", \"Portugal\", \"Romania\", \"Russia\", \"Serbia\", \"Slovakia\", \"Slovenia\", \"Spain\", \"Sweden\", \"Switzerland\", \"Ukraine\",\"United Kingdom\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def find_country(col):\n",
    "    for country in countries_list:\n",
    "        if country in col:\n",
    "            return country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Country'] = df.Location.apply(find_country)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Location</th>\n",
       "      <th>URL</th>\n",
       "      <th>Stars_Avg</th>\n",
       "      <th>Your Stars</th>\n",
       "      <th>Route Type</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Pitches</th>\n",
       "      <th>Length</th>\n",
       "      <th>Area Latitude</th>\n",
       "      <th>Area Longitude</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Route</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Cosmiques Arête</th>\n",
       "      <td>Vallée Blanche Basin &gt; Mont Blanc Range &gt; .Sno...</td>\n",
       "      <td>https://www.mountainproject.com/route/10749708...</td>\n",
       "      <td>3.8</td>\n",
       "      <td>-1</td>\n",
       "      <td>Trad, Mixed, Ice, Snow, Alpine</td>\n",
       "      <td>5.6 AI2 M4 Mod. Snow</td>\n",
       "      <td>2</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>45.87882</td>\n",
       "      <td>6.88555</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hörnligrat Route</th>\n",
       "      <td>Matterhorn &gt; Zermatt area &gt; Switzerland &gt; Euro...</td>\n",
       "      <td>https://www.mountainproject.com/route/11132447...</td>\n",
       "      <td>3.9</td>\n",
       "      <td>-1</td>\n",
       "      <td>Trad, Alpine</td>\n",
       "      <td>5.4 PG13</td>\n",
       "      <td>1</td>\n",
       "      <td>3600.0</td>\n",
       "      <td>45.97648</td>\n",
       "      <td>7.65921</td>\n",
       "      <td>Switzerland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ferrata Brigata Tridentina</th>\n",
       "      <td>Via Ferrata &gt; Passo Gardena area (Grodner Joch...</td>\n",
       "      <td>https://www.mountainproject.com/route/11128278...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>TR, Alpine</td>\n",
       "      <td>4th</td>\n",
       "      <td>1</td>\n",
       "      <td>1600.0</td>\n",
       "      <td>46.51870</td>\n",
       "      <td>11.82130</td>\n",
       "      <td>Italy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Troutdale Pinnacle</th>\n",
       "      <td>Black Crag &gt; Borrowdale Valley &gt; The Lake Dist...</td>\n",
       "      <td>https://www.mountainproject.com/route/10663200...</td>\n",
       "      <td>3.9</td>\n",
       "      <td>-1</td>\n",
       "      <td>Trad</td>\n",
       "      <td>5.6</td>\n",
       "      <td>6</td>\n",
       "      <td>350.0</td>\n",
       "      <td>54.54424</td>\n",
       "      <td>-3.14018</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Creagh Dhu Wall</th>\n",
       "      <td>Main Wall &gt; Craig y Castell - Crag of the Cast...</td>\n",
       "      <td>https://www.mountainproject.com/route/10663944...</td>\n",
       "      <td>3.9</td>\n",
       "      <td>-1</td>\n",
       "      <td>Trad</td>\n",
       "      <td>5.6</td>\n",
       "      <td>3</td>\n",
       "      <td>180.0</td>\n",
       "      <td>52.94180</td>\n",
       "      <td>-4.14660</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                     Location  \\\n",
       "Route                                                                           \n",
       "Cosmiques Arête             Vallée Blanche Basin > Mont Blanc Range > .Sno...   \n",
       "Hörnligrat Route            Matterhorn > Zermatt area > Switzerland > Euro...   \n",
       "Ferrata Brigata Tridentina  Via Ferrata > Passo Gardena area (Grodner Joch...   \n",
       "Troutdale Pinnacle          Black Crag > Borrowdale Valley > The Lake Dist...   \n",
       "Creagh Dhu Wall             Main Wall > Craig y Castell - Crag of the Cast...   \n",
       "\n",
       "                                                                          URL  \\\n",
       "Route                                                                           \n",
       "Cosmiques Arête             https://www.mountainproject.com/route/10749708...   \n",
       "Hörnligrat Route            https://www.mountainproject.com/route/11132447...   \n",
       "Ferrata Brigata Tridentina  https://www.mountainproject.com/route/11128278...   \n",
       "Troutdale Pinnacle          https://www.mountainproject.com/route/10663200...   \n",
       "Creagh Dhu Wall             https://www.mountainproject.com/route/10663944...   \n",
       "\n",
       "                            Stars_Avg  Your Stars  \\\n",
       "Route                                               \n",
       "Cosmiques Arête                   3.8          -1   \n",
       "Hörnligrat Route                  3.9          -1   \n",
       "Ferrata Brigata Tridentina        4.0          -1   \n",
       "Troutdale Pinnacle                3.9          -1   \n",
       "Creagh Dhu Wall                   3.9          -1   \n",
       "\n",
       "                                                Route Type  \\\n",
       "Route                                                        \n",
       "Cosmiques Arête             Trad, Mixed, Ice, Snow, Alpine   \n",
       "Hörnligrat Route                              Trad, Alpine   \n",
       "Ferrata Brigata Tridentina                      TR, Alpine   \n",
       "Troutdale Pinnacle                                    Trad   \n",
       "Creagh Dhu Wall                                       Trad   \n",
       "\n",
       "                                          Rating  Pitches  Length  \\\n",
       "Route                                                               \n",
       "Cosmiques Arête             5.6 AI2 M4 Mod. Snow        2  1000.0   \n",
       "Hörnligrat Route                        5.4 PG13        1  3600.0   \n",
       "Ferrata Brigata Tridentina                   4th        1  1600.0   \n",
       "Troutdale Pinnacle                           5.6        6   350.0   \n",
       "Creagh Dhu Wall                              5.6        3   180.0   \n",
       "\n",
       "                            Area Latitude  Area Longitude         Country  \n",
       "Route                                                                      \n",
       "Cosmiques Arête                  45.87882         6.88555          France  \n",
       "Hörnligrat Route                 45.97648         7.65921     Switzerland  \n",
       "Ferrata Brigata Tridentina       46.51870        11.82130           Italy  \n",
       "Troutdale Pinnacle               54.54424        -3.14018  United Kingdom  \n",
       "Creagh Dhu Wall                  52.94180        -4.14660  United Kingdom  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Création d'une échelle de mesures avec 1 dictionnaire, on s'arrêtera au niveau 7 :\n",
    "\n",
    "cotation_measure = {\"5.4\":1, \"5.5\":2, \"5.6\":3, \"5.7\":4, \"5.8\":5, \"5.9\":6, \"5.10\":7}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def find_cotation(col):\n",
    "    for k,v in cotation_measure.items():\n",
    "        if col[:3] in k:\n",
    "            return v"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['cotation'] = df.Rating.apply(find_cotation)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 3279 entries, Cosmiques Arête to El Woppo\n",
      "Data columns (total 12 columns):\n",
      " #   Column          Non-Null Count  Dtype  \n",
      "---  ------          --------------  -----  \n",
      " 0   Location        3279 non-null   object \n",
      " 1   URL             3279 non-null   object \n",
      " 2   Stars_Avg       3279 non-null   float64\n",
      " 3   Your Stars      3279 non-null   int64  \n",
      " 4   Route Type      3279 non-null   object \n",
      " 5   Rating          3279 non-null   object \n",
      " 6   Pitches         3279 non-null   int64  \n",
      " 7   Length          2834 non-null   float64\n",
      " 8   Area Latitude   3279 non-null   float64\n",
      " 9   Area Longitude  3279 non-null   float64\n",
      " 10  Country         3279 non-null   object \n",
      " 11  cotation        3092 non-null   float64\n",
      "dtypes: float64(5), int64(2), object(5)\n",
      "memory usage: 333.0+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Suppression des données nulles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Suppression des \n",
    "\n",
    "df = df[df['cotation'].notna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df['Length'].notna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 2655 entries, Cosmiques Arête to Nerfetiti\n",
      "Data columns (total 17 columns):\n",
      " #   Column          Non-Null Count  Dtype  \n",
      "---  ------          --------------  -----  \n",
      " 0   Stars_Avg       2655 non-null   float64\n",
      " 1   Route Type      2655 non-null   object \n",
      " 2   Pitches         2655 non-null   int64  \n",
      " 3   Length          2655 non-null   float64\n",
      " 4   Area Latitude   2655 non-null   float64\n",
      " 5   Area Longitude  2655 non-null   float64\n",
      " 6   Country         2655 non-null   object \n",
      " 7   cotation        2655 non-null   float64\n",
      " 8   aid             2655 non-null   int64  \n",
      " 9   alpine          2655 non-null   int64  \n",
      " 10  boulder         2655 non-null   int64  \n",
      " 11  ice             2655 non-null   int64  \n",
      " 12  mixed           2655 non-null   int64  \n",
      " 13  snow            2655 non-null   int64  \n",
      " 14  sport           2655 non-null   int64  \n",
      " 15  tr              2655 non-null   int64  \n",
      " 16  trad            2655 non-null   int64  \n",
      "dtypes: float64(5), int64(10), object(2)\n",
      "memory usage: 373.4+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Plus que 2655 enregistrements"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Supprimer les voies d'escalade sans aucun vote"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list_index = df.query('Stars_Avg <= 0').index\n",
    "len(list_index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Suppression des 12 enregistrements :\n",
    "\n",
    "df.drop(list_index, axis=0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Location</th>\n",
       "      <th>URL</th>\n",
       "      <th>Stars_Avg</th>\n",
       "      <th>Your Stars</th>\n",
       "      <th>Route Type</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Pitches</th>\n",
       "      <th>Length</th>\n",
       "      <th>Area Latitude</th>\n",
       "      <th>Area Longitude</th>\n",
       "      <th>Country</th>\n",
       "      <th>cotation</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Route</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Location, URL, Stars_Avg, Your Stars, Route Type, Rating, Pitches, Length, Area Latitude, Area Longitude, Country, cotation]\n",
       "Index: []"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Vérification de la suppression\n",
    "\n",
    "df.query('Stars_Avg <= 0')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Supprimer les voies d'escalade qui ont emplacement inférieur strictement à 0 (impossible)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list_index_pitches = df.query('Pitches < 0').index\n",
    "len(list_index_pitches)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Suppression des 2 enregistrements :\n",
    "\n",
    "df.drop(list_index_pitches, axis=0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Location</th>\n",
       "      <th>URL</th>\n",
       "      <th>Stars_Avg</th>\n",
       "      <th>Your Stars</th>\n",
       "      <th>Route Type</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Pitches</th>\n",
       "      <th>Length</th>\n",
       "      <th>Area Latitude</th>\n",
       "      <th>Area Longitude</th>\n",
       "      <th>Country</th>\n",
       "      <th>cotation</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Route</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Location, URL, Stars_Avg, Your Stars, Route Type, Rating, Pitches, Length, Area Latitude, Area Longitude, Country, cotation]\n",
       "Index: []"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('Pitches < 0')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conversion de la longueur de la voie d'escalade en mètres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"Length\"] = df.Length.apply(lambda x: x*0.3048)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Création d'un \"bag of words\" pour analyser les données des types de voies d'escalade"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Les différents types de voies :"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Aid = Type Via-Ferrata, des systèmes d'échelle permanente, des cordes prédisposées avec ligne de vie pour aider le grimpeur sur sa voie (Aid of etrier / aid-ladder)\n",
    "\n",
    "Boulder = bloc, consiste à grimper des blocs de faible hauteur et sans assurage \n",
    "\n",
    "Sport = L'escalade sportive est un style d'escalade qui repose sur des ancrages permanents fixés sur le rocher\n",
    "\n",
    "Trad = Le grimpeur part du bas de la voie et place ses protections au fur et à mesure (ancrages amovibles comme les coinceurs)\n",
    "\n",
    "Ice = Escalade sur glacier\n",
    "\n",
    "Snow = Voie avec de la neige\n",
    "\n",
    "Mixed = L'escalade mixte ou le mixte est une méthode de progression en escalade qui utilise des crampons et des piolets pour grimper sur un environnement mixte composé de glace et de rocher\n",
    "\n",
    "Alpine = Les rochers alpins diffèrent par leur qualité, leur comportement face aux intempéries et à la rupture, ainsi que leur forme. La connaissance des roches est donc nécessaire pour la sécurité de chaque grimpeur (les protections sont donc différentes et répondent à des normes supplémentaires)\n",
    "\n",
    "TR = Top Rope, technique de corde supérieure pour pratiquer l'escalade en solo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Trad, Mixed, Ice, Snow, Alpine', 'Trad, Alpine', 'Trad', ...,\n",
       "       'Trad', 'Trad', 'Trad'], dtype=object)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "text_data = np.array(df['Route Type'].values)\n",
    "text_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[0, 1, 0, ..., 0, 0, 1],\n",
       "       [0, 1, 0, ..., 0, 0, 1],\n",
       "       [0, 0, 0, ..., 0, 0, 1],\n",
       "       ...,\n",
       "       [0, 0, 0, ..., 0, 0, 1],\n",
       "       [0, 0, 0, ..., 0, 0, 1],\n",
       "       [0, 0, 0, ..., 0, 0, 1]], dtype=int64)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create the bag of words feature matrix\n",
    "count = CountVectorizer()\n",
    "bag_of_words = count.fit_transform(text_data)\n",
    "\n",
    "# Show feature matrix\n",
    "bag_of_words.toarray()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "feature_names = count.get_feature_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>aid</th>\n",
       "      <th>alpine</th>\n",
       "      <th>boulder</th>\n",
       "      <th>ice</th>\n",
       "      <th>mixed</th>\n",
       "      <th>snow</th>\n",
       "      <th>sport</th>\n",
       "      <th>tr</th>\n",
       "      <th>trad</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2650</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2651</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2652</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2653</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2654</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2655 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      aid  alpine  boulder  ice  mixed  snow  sport  tr  trad\n",
       "0       0       1        0    1      1     1      0   0     1\n",
       "1       0       1        0    0      0     0      0   0     1\n",
       "2       0       0        0    0      0     0      0   0     1\n",
       "3       0       0        0    0      0     0      0   0     1\n",
       "4       0       0        0    0      0     0      0   0     1\n",
       "...   ...     ...      ...  ...    ...   ...    ...  ..   ...\n",
       "2650    0       0        0    0      0     0      1   0     0\n",
       "2651    0       0        0    0      0     0      0   0     1\n",
       "2652    0       0        0    0      0     0      0   0     1\n",
       "2653    0       0        0    0      0     0      0   0     1\n",
       "2654    0       0        0    0      0     0      0   0     1\n",
       "\n",
       "[2655 rows x 9 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_types_one_hot_encoding = pd.DataFrame(bag_of_words.toarray(), columns=feature_names)\n",
    "df_types_one_hot_encoding"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Concaténation du one-hot-encoding des types de voies d'escalade dans le dataframe initial"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Le one hot encoding ne fonctionnait pas sans le rapprochement des index numérotés, re-création des index pour concaténation :\n",
    "\n",
    "df = df.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat([df, df_types_one_hot_encoding], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Suppression des colonnes inutiles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(columns=['Location','Rating','Your Stars'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(columns='URL', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.set_index('Route', inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Premier aperçu résumé de la Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Stars_Avg</th>\n",
       "      <th>Pitches</th>\n",
       "      <th>Length</th>\n",
       "      <th>Area Latitude</th>\n",
       "      <th>Area Longitude</th>\n",
       "      <th>cotation</th>\n",
       "      <th>aid</th>\n",
       "      <th>alpine</th>\n",
       "      <th>boulder</th>\n",
       "      <th>ice</th>\n",
       "      <th>mixed</th>\n",
       "      <th>snow</th>\n",
       "      <th>sport</th>\n",
       "      <th>tr</th>\n",
       "      <th>trad</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2655.000000</td>\n",
       "      <td>2655.000000</td>\n",
       "      <td>2655.000000</td>\n",
       "      <td>2655.000000</td>\n",
       "      <td>2655.000000</td>\n",
       "      <td>2655.000000</td>\n",
       "      <td>2655.000000</td>\n",
       "      <td>2655.000000</td>\n",
       "      <td>2655.000000</td>\n",
       "      <td>2655.000000</td>\n",
       "      <td>2655.000000</td>\n",
       "      <td>2655.000000</td>\n",
       "      <td>2655.000000</td>\n",
       "      <td>2655.000000</td>\n",
       "      <td>2655.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>3.204934</td>\n",
       "      <td>2.254614</td>\n",
       "      <td>66.090857</td>\n",
       "      <td>45.983433</td>\n",
       "      <td>8.384993</td>\n",
       "      <td>5.359699</td>\n",
       "      <td>0.007156</td>\n",
       "      <td>0.071563</td>\n",
       "      <td>0.000377</td>\n",
       "      <td>0.001507</td>\n",
       "      <td>0.001883</td>\n",
       "      <td>0.001883</td>\n",
       "      <td>0.683616</td>\n",
       "      <td>0.039925</td>\n",
       "      <td>0.312618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.897284</td>\n",
       "      <td>3.137149</td>\n",
       "      <td>146.192864</td>\n",
       "      <td>6.761746</td>\n",
       "      <td>10.070987</td>\n",
       "      <td>2.076666</td>\n",
       "      <td>0.084308</td>\n",
       "      <td>0.257812</td>\n",
       "      <td>0.019407</td>\n",
       "      <td>0.038793</td>\n",
       "      <td>0.043364</td>\n",
       "      <td>0.043364</td>\n",
       "      <td>0.465152</td>\n",
       "      <td>0.195819</td>\n",
       "      <td>0.463647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.828800</td>\n",
       "      <td>28.155560</td>\n",
       "      <td>-98.792090</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>15.240000</td>\n",
       "      <td>41.259500</td>\n",
       "      <td>0.074460</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>3.500000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>24.384000</td>\n",
       "      <td>46.163600</td>\n",
       "      <td>8.472100</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>4.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>39.624000</td>\n",
       "      <td>50.030600</td>\n",
       "      <td>12.466560</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>4.000000</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>4572.000000</td>\n",
       "      <td>68.298410</td>\n",
       "      <td>43.317800</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Stars_Avg      Pitches       Length  Area Latitude  Area Longitude  \\\n",
       "count  2655.000000  2655.000000  2655.000000    2655.000000     2655.000000   \n",
       "mean      3.204934     2.254614    66.090857      45.983433        8.384993   \n",
       "std       0.897284     3.137149   146.192864       6.761746       10.070987   \n",
       "min       1.000000     1.000000     1.828800      28.155560      -98.792090   \n",
       "25%       3.000000     1.000000    15.240000      41.259500        0.074460   \n",
       "50%       3.500000     1.000000    24.384000      46.163600        8.472100   \n",
       "75%       4.000000     1.000000    39.624000      50.030600       12.466560   \n",
       "max       4.000000    32.000000  4572.000000      68.298410       43.317800   \n",
       "\n",
       "          cotation          aid       alpine      boulder          ice  \\\n",
       "count  2655.000000  2655.000000  2655.000000  2655.000000  2655.000000   \n",
       "mean      5.359699     0.007156     0.071563     0.000377     0.001507   \n",
       "std       2.076666     0.084308     0.257812     0.019407     0.038793   \n",
       "min       1.000000     0.000000     0.000000     0.000000     0.000000   \n",
       "25%       3.000000     0.000000     0.000000     0.000000     0.000000   \n",
       "50%       7.000000     0.000000     0.000000     0.000000     0.000000   \n",
       "75%       7.000000     0.000000     0.000000     0.000000     0.000000   \n",
       "max       7.000000     1.000000     1.000000     1.000000     1.000000   \n",
       "\n",
       "             mixed         snow        sport           tr         trad  \n",
       "count  2655.000000  2655.000000  2655.000000  2655.000000  2655.000000  \n",
       "mean      0.001883     0.001883     0.683616     0.039925     0.312618  \n",
       "std       0.043364     0.043364     0.465152     0.195819     0.463647  \n",
       "min       0.000000     0.000000     0.000000     0.000000     0.000000  \n",
       "25%       0.000000     0.000000     0.000000     0.000000     0.000000  \n",
       "50%       0.000000     0.000000     1.000000     0.000000     0.000000  \n",
       "75%       0.000000     0.000000     1.000000     0.000000     1.000000  \n",
       "max       1.000000     1.000000     1.000000     1.000000     1.000000  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Stars_Avg</th>\n",
       "      <th>Route Type</th>\n",
       "      <th>Pitches</th>\n",
       "      <th>Length</th>\n",
       "      <th>Area Latitude</th>\n",
       "      <th>Area Longitude</th>\n",
       "      <th>Country</th>\n",
       "      <th>cotation</th>\n",
       "      <th>aid</th>\n",
       "      <th>alpine</th>\n",
       "      <th>boulder</th>\n",
       "      <th>ice</th>\n",
       "      <th>mixed</th>\n",
       "      <th>snow</th>\n",
       "      <th>sport</th>\n",
       "      <th>tr</th>\n",
       "      <th>trad</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Route</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Cosmiques Arête</th>\n",
       "      <td>3.8</td>\n",
       "      <td>Trad, Mixed, Ice, Snow, Alpine</td>\n",
       "      <td>2</td>\n",
       "      <td>304.800</td>\n",
       "      <td>45.87882</td>\n",
       "      <td>6.88555</td>\n",
       "      <td>France</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hörnligrat Route</th>\n",
       "      <td>3.9</td>\n",
       "      <td>Trad, Alpine</td>\n",
       "      <td>1</td>\n",
       "      <td>1097.280</td>\n",
       "      <td>45.97648</td>\n",
       "      <td>7.65921</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Troutdale Pinnacle</th>\n",
       "      <td>3.9</td>\n",
       "      <td>Trad</td>\n",
       "      <td>6</td>\n",
       "      <td>106.680</td>\n",
       "      <td>54.54424</td>\n",
       "      <td>-3.14018</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Creagh Dhu Wall</th>\n",
       "      <td>3.9</td>\n",
       "      <td>Trad</td>\n",
       "      <td>3</td>\n",
       "      <td>54.864</td>\n",
       "      <td>52.94180</td>\n",
       "      <td>-4.14660</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bosigran Ridge a.k.a. Commando Ridge</th>\n",
       "      <td>4.0</td>\n",
       "      <td>Trad</td>\n",
       "      <td>8</td>\n",
       "      <td>152.400</td>\n",
       "      <td>50.17590</td>\n",
       "      <td>-5.62000</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      Stars_Avg  \\\n",
       "Route                                             \n",
       "Cosmiques Arête                             3.8   \n",
       "Hörnligrat Route                            3.9   \n",
       "Troutdale Pinnacle                          3.9   \n",
       "Creagh Dhu Wall                             3.9   \n",
       "Bosigran Ridge a.k.a. Commando Ridge        4.0   \n",
       "\n",
       "                                                          Route Type  Pitches  \\\n",
       "Route                                                                           \n",
       "Cosmiques Arête                       Trad, Mixed, Ice, Snow, Alpine        2   \n",
       "Hörnligrat Route                                        Trad, Alpine        1   \n",
       "Troutdale Pinnacle                                              Trad        6   \n",
       "Creagh Dhu Wall                                                 Trad        3   \n",
       "Bosigran Ridge a.k.a. Commando Ridge                            Trad        8   \n",
       "\n",
       "                                        Length  Area Latitude  Area Longitude  \\\n",
       "Route                                                                           \n",
       "Cosmiques Arête                        304.800       45.87882         6.88555   \n",
       "Hörnligrat Route                      1097.280       45.97648         7.65921   \n",
       "Troutdale Pinnacle                     106.680       54.54424        -3.14018   \n",
       "Creagh Dhu Wall                         54.864       52.94180        -4.14660   \n",
       "Bosigran Ridge a.k.a. Commando Ridge   152.400       50.17590        -5.62000   \n",
       "\n",
       "                                             Country  cotation  aid  alpine  \\\n",
       "Route                                                                         \n",
       "Cosmiques Arête                               France       3.0    0       1   \n",
       "Hörnligrat Route                         Switzerland       1.0    0       1   \n",
       "Troutdale Pinnacle                    United Kingdom       3.0    0       0   \n",
       "Creagh Dhu Wall                       United Kingdom       3.0    0       0   \n",
       "Bosigran Ridge a.k.a. Commando Ridge  United Kingdom       1.0    0       0   \n",
       "\n",
       "                                      boulder  ice  mixed  snow  sport  tr  \\\n",
       "Route                                                                        \n",
       "Cosmiques Arête                             0    1      1     1      0   0   \n",
       "Hörnligrat Route                            0    0      0     0      0   0   \n",
       "Troutdale Pinnacle                          0    0      0     0      0   0   \n",
       "Creagh Dhu Wall                             0    0      0     0      0   0   \n",
       "Bosigran Ridge a.k.a. Commando Ridge        0    0      0     0      0   0   \n",
       "\n",
       "                                      trad  \n",
       "Route                                       \n",
       "Cosmiques Arête                          1  \n",
       "Hörnligrat Route                         1  \n",
       "Troutdale Pinnacle                       1  \n",
       "Creagh Dhu Wall                          1  \n",
       "Bosigran Ridge a.k.a. Commando Ridge     1  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Compter le nombre de fois qu'apparaît un type de voie d'escalade"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    2465\n",
       "1     190\n",
       "Name: alpine, dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['alpine'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Suppression d'un outlier dans la hauteur + Vérification hauteur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Peuterey Integrale'], dtype='object', name='Route')"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('Length > 4000').index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop('Peuterey Integrale', axis=0, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Vérification ok sur le site source de la possibilité qu'une voie d'escalade soit d'une longueur inférieure à 5 mètres, notamment un minimum à 1m82 (ex : Napes Needle https://www.mountainproject.com/route/106641638/napes-needle)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 2654 entries, Cosmiques Arête to Nerfetiti\n",
      "Data columns (total 17 columns):\n",
      " #   Column          Non-Null Count  Dtype  \n",
      "---  ------          --------------  -----  \n",
      " 0   Stars_Avg       2654 non-null   float64\n",
      " 1   Route Type      2654 non-null   object \n",
      " 2   Pitches         2654 non-null   int64  \n",
      " 3   Length          2654 non-null   float64\n",
      " 4   Area Latitude   2654 non-null   float64\n",
      " 5   Area Longitude  2654 non-null   float64\n",
      " 6   Country         2654 non-null   object \n",
      " 7   cotation        2654 non-null   float64\n",
      " 8   aid             2654 non-null   int64  \n",
      " 9   alpine          2654 non-null   int64  \n",
      " 10  boulder         2654 non-null   int64  \n",
      " 11  ice             2654 non-null   int64  \n",
      " 12  mixed           2654 non-null   int64  \n",
      " 13  snow            2654 non-null   int64  \n",
      " 14  sport           2654 non-null   int64  \n",
      " 15  tr              2654 non-null   int64  \n",
      " 16  trad            2654 non-null   int64  \n",
      "dtypes: float64(5), int64(10), object(2)\n",
      "memory usage: 453.2+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notre dataset comprend désormais 2654 voies d'escalade et 17 colonnes qui caractérisent ces voies."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export du Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\"C:/Users/amandine.gauberville/dataV2-labs/module-2/mini-project2/route-finder-rock-clean.csv\", index=True)"
   ]
  }
 ],
 "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}