notebooks/clean.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cleaning the Dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup\n",
    "\n",
    "Import the required packages and data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "sys.path.append('..')\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "import matplotlib as mpl\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "plt.style.use('ggplot')\n",
    "import json\n",
    "import os.path\n",
    "import string\n",
    "from scripts.scrape import get_json, get_metadata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_folder = os.path.join('..', 'data')\n",
    "\n",
    "dirty_filename = os.path.join(data_folder, 'berlin_marathon_times_dirty.csv')\n",
    "clean_filename = os.path.join(data_folder, 'berlin_marathon_times.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>idx</th>\n",
       "      <th>year</th>\n",
       "      <th>id</th>\n",
       "      <th>place</th>\n",
       "      <th>bib</th>\n",
       "      <th>surname</th>\n",
       "      <th>forename</th>\n",
       "      <th>team</th>\n",
       "      <th>nationality</th>\n",
       "      <th>yob</th>\n",
       "      <th>sex</th>\n",
       "      <th>age_class</th>\n",
       "      <th>age_class_place</th>\n",
       "      <th>net_time</th>\n",
       "      <th>clock_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2005</td>\n",
       "      <td>2</td>\n",
       "      <td>19431</td>\n",
       "      <td>2002</td>\n",
       "      <td>Ohnemueller</td>\n",
       "      <td>Bert Martin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GER</td>\n",
       "      <td>1959</td>\n",
       "      <td>M</td>\n",
       "      <td>M45</td>\n",
       "      <td>2758</td>\n",
       "      <td>04:23:26</td>\n",
       "      <td>04:23:48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>10609</td>\n",
       "      <td>29356</td>\n",
       "      <td>Klinginger</td>\n",
       "      <td>Kurt</td>\n",
       "      <td>smart running team</td>\n",
       "      <td>GER</td>\n",
       "      <td>1947</td>\n",
       "      <td>M</td>\n",
       "      <td>M55</td>\n",
       "      <td>344</td>\n",
       "      <td>03:53:01</td>\n",
       "      <td>03:56:43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2005</td>\n",
       "      <td>4</td>\n",
       "      <td>23031</td>\n",
       "      <td>2034</td>\n",
       "      <td>Eleftheriadis</td>\n",
       "      <td>Georgios</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GRE</td>\n",
       "      <td>1962</td>\n",
       "      <td>M</td>\n",
       "      <td>M40</td>\n",
       "      <td>4470</td>\n",
       "      <td>04:38:47</td>\n",
       "      <td>04:42:41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2005</td>\n",
       "      <td>5</td>\n",
       "      <td>14104</td>\n",
       "      <td>2035</td>\n",
       "      <td>Scholz</td>\n",
       "      <td>Detlef</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GER</td>\n",
       "      <td>1970</td>\n",
       "      <td>M</td>\n",
       "      <td>M35</td>\n",
       "      <td>2573</td>\n",
       "      <td>04:03:50</td>\n",
       "      <td>04:05:14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2005</td>\n",
       "      <td>6</td>\n",
       "      <td>30239</td>\n",
       "      <td>F105</td>\n",
       "      <td>Frischke</td>\n",
       "      <td>Lucie</td>\n",
       "      <td>LAV Tempelhof Berlin</td>\n",
       "      <td>GER</td>\n",
       "      <td>1932</td>\n",
       "      <td>W</td>\n",
       "      <td>W70</td>\n",
       "      <td>2</td>\n",
       "      <td>06:07:10</td>\n",
       "      <td>06:09:04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   idx  year  id  place    bib        surname     forename  \\\n",
       "0    0  2005   2  19431   2002    Ohnemueller  Bert Martin   \n",
       "1    1  2005   3  10609  29356     Klinginger         Kurt   \n",
       "2    2  2005   4  23031   2034  Eleftheriadis     Georgios   \n",
       "3    3  2005   5  14104   2035         Scholz       Detlef   \n",
       "4    4  2005   6  30239   F105       Frischke        Lucie   \n",
       "\n",
       "                   team nationality   yob sex age_class  age_class_place  \\\n",
       "0                   NaN         GER  1959   M       M45             2758   \n",
       "1    smart running team         GER  1947   M       M55              344   \n",
       "2                   NaN         GRE  1962   M       M40             4470   \n",
       "3                   NaN         GER  1970   M       M35             2573   \n",
       "4  LAV Tempelhof Berlin         GER  1932   W       W70                2   \n",
       "\n",
       "   net_time clock_time  \n",
       "0  04:23:26   04:23:48  \n",
       "1  03:53:01   03:56:43  \n",
       "2  04:38:47   04:42:41  \n",
       "3  04:03:50   04:05:14  \n",
       "4  06:07:10   06:09:04  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(dirty_filename).sort_values(['year', 'id']).reset_index(drop=True)\n",
    "df = df[(df.year >= 2005) & (df.year <= 2016)]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Completeness Check\n",
    "\n",
    "Our csv file has x rows and the json metadata report a total of y rows.  We check here that $x = y$."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2005    30373\n",
       "2006    30182\n",
       "2007    32486\n",
       "2008    35731\n",
       "2009    35015\n",
       "2010    34001\n",
       "2011    32914\n",
       "2012    34270\n",
       "2013    36473\n",
       "2014    28945\n",
       "2015    36753\n",
       "2016    35991\n",
       "Name: year, dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "participants_year = df.year.value_counts().sort_index()\n",
    "participants_year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2005    30373\n",
       "2006    30182\n",
       "2007    32486\n",
       "2008    35731\n",
       "2009    35015\n",
       "2010    34001\n",
       "2011    32914\n",
       "2012    34270\n",
       "2013    36473\n",
       "2014    28945\n",
       "2015    36753\n",
       "2016    35991\n",
       "Name: year, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "participants_year_meta = participants_year.copy()\n",
    "\n",
    "for year in participants_year.index:\n",
    "    with open('../data/{0}-1.json'.format(year)) as f:\n",
    "        j = json.load(f)\n",
    "        m = get_metadata(j)\n",
    "        participants_year_meta[year] = m.n_rows\n",
    "        \n",
    "participants_year_meta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(participants_year[participants_year != participants_year_meta])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overview"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "403134"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "idx                 int64\n",
       "year                int64\n",
       "id                  int64\n",
       "place               int64\n",
       "bib                object\n",
       "surname            object\n",
       "forename           object\n",
       "team               object\n",
       "nationality        object\n",
       "yob                 int64\n",
       "sex                object\n",
       "age_class          object\n",
       "age_class_place     int64\n",
       "net_time           object\n",
       "clock_time         object\n",
       "dtype: object"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>idx</th>\n",
       "      <th>year</th>\n",
       "      <th>id</th>\n",
       "      <th>place</th>\n",
       "      <th>yob</th>\n",
       "      <th>age_class_place</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>403134.000000</td>\n",
       "      <td>403134.000000</td>\n",
       "      <td>403134.000000</td>\n",
       "      <td>403134.000000</td>\n",
       "      <td>403134.000000</td>\n",
       "      <td>403134.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>201566.500000</td>\n",
       "      <td>2010.619734</td>\n",
       "      <td>26240.607041</td>\n",
       "      <td>16899.358593</td>\n",
       "      <td>1967.243013</td>\n",
       "      <td>1596.684058</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>116374.906047</td>\n",
       "      <td>3.426661</td>\n",
       "      <td>25441.663187</td>\n",
       "      <td>9861.602089</td>\n",
       "      <td>47.518969</td>\n",
       "      <td>1321.932040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>2005.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>100783.250000</td>\n",
       "      <td>2008.000000</td>\n",
       "      <td>10150.000000</td>\n",
       "      <td>8402.000000</td>\n",
       "      <td>1962.000000</td>\n",
       "      <td>517.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>201566.500000</td>\n",
       "      <td>2011.000000</td>\n",
       "      <td>20216.000000</td>\n",
       "      <td>16804.000000</td>\n",
       "      <td>1968.000000</td>\n",
       "      <td>1208.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>302349.750000</td>\n",
       "      <td>2014.000000</td>\n",
       "      <td>31350.000000</td>\n",
       "      <td>25206.000000</td>\n",
       "      <td>1976.000000</td>\n",
       "      <td>2456.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>403133.000000</td>\n",
       "      <td>2016.000000</td>\n",
       "      <td>141120.000000</td>\n",
       "      <td>36768.000000</td>\n",
       "      <td>1998.000000</td>\n",
       "      <td>6101.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 idx           year             id          place  \\\n",
       "count  403134.000000  403134.000000  403134.000000  403134.000000   \n",
       "mean   201566.500000    2010.619734   26240.607041   16899.358593   \n",
       "std    116374.906047       3.426661   25441.663187    9861.602089   \n",
       "min         0.000000    2005.000000       2.000000       1.000000   \n",
       "25%    100783.250000    2008.000000   10150.000000    8402.000000   \n",
       "50%    201566.500000    2011.000000   20216.000000   16804.000000   \n",
       "75%    302349.750000    2014.000000   31350.000000   25206.000000   \n",
       "max    403133.000000    2016.000000  141120.000000   36768.000000   \n",
       "\n",
       "                 yob  age_class_place  \n",
       "count  403134.000000    403134.000000  \n",
       "mean     1967.243013      1596.684058  \n",
       "std        47.518969      1321.932040  \n",
       "min         0.000000         0.000000  \n",
       "25%      1962.000000       517.000000  \n",
       "50%      1968.000000      1208.000000  \n",
       "75%      1976.000000      2456.000000  \n",
       "max      1998.000000      6101.000000  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 403134 entries, 0 to 403133\n",
      "Data columns (total 15 columns):\n",
      "idx                403134 non-null int64\n",
      "year               403134 non-null int64\n",
      "id                 403134 non-null int64\n",
      "place              403134 non-null int64\n",
      "bib                403127 non-null object\n",
      "surname            403096 non-null object\n",
      "forename           403089 non-null object\n",
      "team               181573 non-null object\n",
      "nationality        403120 non-null object\n",
      "yob                403134 non-null int64\n",
      "sex                403132 non-null object\n",
      "age_class          403105 non-null object\n",
      "age_class_place    403134 non-null int64\n",
      "net_time           403134 non-null object\n",
      "clock_time         403134 non-null object\n",
      "dtypes: int64(6), object(9)\n",
      "memory usage: 49.2+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Unique IDs\n",
    "\n",
    "It can be dangerous to assume properties implied by the column name, especially in the case of IDs. We check the extent to which a row is identified by the ID column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "58616"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "id_counts = df.groupby('id').year.count()\n",
    "id_problems = id_counts[id_counts > 1]\n",
    "len(id_problems)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    58616.000000\n",
       "mean         6.602378\n",
       "std          3.688039\n",
       "min          2.000000\n",
       "25%          2.000000\n",
       "50%          8.000000\n",
       "75%         10.000000\n",
       "max         12.000000\n",
       "Name: year, dtype: float64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "id_problems.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ids = df.groupby(['id', 'year']).place.count()\n",
    "ids_problems = ids[ids > 1]\n",
    "len(ids_problems)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Place"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[df.place.isnull()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    403134.000000\n",
       "mean      16899.358593\n",
       "std        9861.602089\n",
       "min           1.000000\n",
       "25%        8402.000000\n",
       "50%       16804.000000\n",
       "75%       25206.000000\n",
       "max       36768.000000\n",
       "Name: place, dtype: float64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.place.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Times"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[df.net_time.isnull()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[df.clock_time.isnull()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count                    403134\n",
       "mean     0 days 04:09:00.199402\n",
       "std      0 days 00:42:32.086175\n",
       "min             0 days 00:00:00\n",
       "25%             0 days 03:39:12\n",
       "50%             0 days 04:04:23\n",
       "75%             0 days 04:35:43\n",
       "max             0 days 08:41:33\n",
       "Name: net_time, dtype: object"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.net_time = pd.to_timedelta(df.net_time)\n",
    "df.clock_time = pd.to_timedelta(df.clock_time)\n",
    "df.net_time.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count                    403134\n",
       "mean     0 days 04:19:57.418917\n",
       "std      0 days 00:48:21.491980\n",
       "min             0 days 00:00:00\n",
       "25%             0 days 03:45:24\n",
       "50%             0 days 04:16:00\n",
       "75%      0 days 04:51:08.750000\n",
       "max             0 days 11:01:11\n",
       "Name: clock_time, dtype: object"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.clock_time.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A time of zero seconds doesn't make sense so we replace it with null."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zero = pd.Timedelta(0)\n",
    "len(df[df.net_time == zero])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.net_time = df.net_time.replace(zero, np.nan)\n",
    "len(df[df.net_time == zero])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[df.clock_time == zero])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.clock_time = df.clock_time.replace(zero, np.nan)\n",
    "len(df[df.clock_time == zero])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are two ways to measure time taken to finish:\n",
    "    \n",
    "* net time (aka chip time): start the timer when the runner crosses the start line\n",
    "* clock time (aka gun time): start the timer at the official start time.\n",
    "    \n",
    "Since it can sometimes take a few minutes to cross the start line after the official start time, these two measures are usually different.  However, the net time should always be larger than the clock time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>idx</th>\n",
       "      <th>year</th>\n",
       "      <th>id</th>\n",
       "      <th>place</th>\n",
       "      <th>bib</th>\n",
       "      <th>surname</th>\n",
       "      <th>forename</th>\n",
       "      <th>team</th>\n",
       "      <th>nationality</th>\n",
       "      <th>yob</th>\n",
       "      <th>sex</th>\n",
       "      <th>age_class</th>\n",
       "      <th>age_class_place</th>\n",
       "      <th>net_time</th>\n",
       "      <th>clock_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>58489</th>\n",
       "      <td>58489</td>\n",
       "      <td>2006</td>\n",
       "      <td>41853</td>\n",
       "      <td>22138</td>\n",
       "      <td>F4217</td>\n",
       "      <td>Vogel</td>\n",
       "      <td>Esther</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1973</td>\n",
       "      <td>W</td>\n",
       "      <td>W30</td>\n",
       "      <td>524</td>\n",
       "      <td>04:41:51</td>\n",
       "      <td>04:26:20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>312161</th>\n",
       "      <td>312161</td>\n",
       "      <td>2014</td>\n",
       "      <td>11182</td>\n",
       "      <td>2673</td>\n",
       "      <td>13549</td>\n",
       "      <td>Sowisnki</td>\n",
       "      <td>Fabrice</td>\n",
       "      <td>SAINT VENANT ATHLETISME</td>\n",
       "      <td>FRA</td>\n",
       "      <td>1974</td>\n",
       "      <td>M</td>\n",
       "      <td>40</td>\n",
       "      <td>595</td>\n",
       "      <td>03:13:55</td>\n",
       "      <td>02:58:59</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           idx  year     id  place    bib   surname forename  \\\n",
       "58489    58489  2006  41853  22138  F4217     Vogel   Esther   \n",
       "312161  312161  2014  11182   2673  13549  Sowisnki  Fabrice   \n",
       "\n",
       "                           team nationality   yob sex age_class  \\\n",
       "58489                       NaN         DEU  1973   W       W30   \n",
       "312161  SAINT VENANT ATHLETISME         FRA  1974   M        40   \n",
       "\n",
       "        age_class_place net_time clock_time  \n",
       "58489               524 04:41:51   04:26:20  \n",
       "312161              595 03:13:55   02:58:59  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "times = df[df.net_time > df.clock_time]\n",
    "times"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ev = df.loc[58489]\n",
    "fs = df.loc[312161]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>idx</th>\n",
       "      <th>year</th>\n",
       "      <th>id</th>\n",
       "      <th>place</th>\n",
       "      <th>bib</th>\n",
       "      <th>surname</th>\n",
       "      <th>forename</th>\n",
       "      <th>team</th>\n",
       "      <th>nationality</th>\n",
       "      <th>yob</th>\n",
       "      <th>sex</th>\n",
       "      <th>age_class</th>\n",
       "      <th>age_class_place</th>\n",
       "      <th>net_time</th>\n",
       "      <th>clock_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>38966</th>\n",
       "      <td>38966</td>\n",
       "      <td>2006</td>\n",
       "      <td>9778</td>\n",
       "      <td>22135</td>\n",
       "      <td>12254</td>\n",
       "      <td>von Blume</td>\n",
       "      <td>Marc</td>\n",
       "      <td>SV Blitzenreute</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1962</td>\n",
       "      <td>M</td>\n",
       "      <td>M40</td>\n",
       "      <td>4357</td>\n",
       "      <td>04:41:50</td>\n",
       "      <td>04:43:48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42175</th>\n",
       "      <td>42175</td>\n",
       "      <td>2006</td>\n",
       "      <td>13258</td>\n",
       "      <td>22136</td>\n",
       "      <td>8523</td>\n",
       "      <td>Kolke</td>\n",
       "      <td>Reinhard</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1968</td>\n",
       "      <td>M</td>\n",
       "      <td>M35</td>\n",
       "      <td>3544</td>\n",
       "      <td>04:41:50</td>\n",
       "      <td>04:43:41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52108</th>\n",
       "      <td>52108</td>\n",
       "      <td>2006</td>\n",
       "      <td>26030</td>\n",
       "      <td>22137</td>\n",
       "      <td>26910</td>\n",
       "      <td>Wojczewski</td>\n",
       "      <td>Axel-Rainer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1949</td>\n",
       "      <td>M</td>\n",
       "      <td>M55</td>\n",
       "      <td>936</td>\n",
       "      <td>04:41:51</td>\n",
       "      <td>04:46:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46026</th>\n",
       "      <td>46026</td>\n",
       "      <td>2006</td>\n",
       "      <td>17899</td>\n",
       "      <td>22138</td>\n",
       "      <td>F3482</td>\n",
       "      <td>Lorch</td>\n",
       "      <td>Renate</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1960</td>\n",
       "      <td>W</td>\n",
       "      <td>W45</td>\n",
       "      <td>579</td>\n",
       "      <td>04:41:51</td>\n",
       "      <td>04:47:42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58489</th>\n",
       "      <td>58489</td>\n",
       "      <td>2006</td>\n",
       "      <td>41853</td>\n",
       "      <td>22138</td>\n",
       "      <td>F4217</td>\n",
       "      <td>Vogel</td>\n",
       "      <td>Esther</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1973</td>\n",
       "      <td>W</td>\n",
       "      <td>W30</td>\n",
       "      <td>524</td>\n",
       "      <td>04:41:51</td>\n",
       "      <td>04:26:20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43553</th>\n",
       "      <td>43553</td>\n",
       "      <td>2006</td>\n",
       "      <td>14787</td>\n",
       "      <td>22140</td>\n",
       "      <td>16772</td>\n",
       "      <td>Kaspar</td>\n",
       "      <td>Werner</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1953</td>\n",
       "      <td>M</td>\n",
       "      <td>M50</td>\n",
       "      <td>1985</td>\n",
       "      <td>04:41:52</td>\n",
       "      <td>04:45:27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59977</th>\n",
       "      <td>59977</td>\n",
       "      <td>2006</td>\n",
       "      <td>43398</td>\n",
       "      <td>22141</td>\n",
       "      <td>13745</td>\n",
       "      <td>Ruholl</td>\n",
       "      <td>Steve</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1979</td>\n",
       "      <td>M</td>\n",
       "      <td>MH</td>\n",
       "      <td>1817</td>\n",
       "      <td>04:41:52</td>\n",
       "      <td>04:45:31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53329</th>\n",
       "      <td>53329</td>\n",
       "      <td>2006</td>\n",
       "      <td>28360</td>\n",
       "      <td>22142</td>\n",
       "      <td>29253</td>\n",
       "      <td>Fortune</td>\n",
       "      <td>Jean Pierre</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FRA</td>\n",
       "      <td>1955</td>\n",
       "      <td>M</td>\n",
       "      <td>M50</td>\n",
       "      <td>1986</td>\n",
       "      <td>04:41:52</td>\n",
       "      <td>04:46:23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         idx  year     id  place    bib     surname     forename  \\\n",
       "38966  38966  2006   9778  22135  12254   von Blume         Marc   \n",
       "42175  42175  2006  13258  22136   8523       Kolke     Reinhard   \n",
       "52108  52108  2006  26030  22137  26910  Wojczewski  Axel-Rainer   \n",
       "46026  46026  2006  17899  22138  F3482       Lorch       Renate   \n",
       "58489  58489  2006  41853  22138  F4217       Vogel       Esther   \n",
       "43553  43553  2006  14787  22140  16772      Kaspar       Werner   \n",
       "59977  59977  2006  43398  22141  13745      Ruholl        Steve   \n",
       "53329  53329  2006  28360  22142  29253     Fortune  Jean Pierre   \n",
       "\n",
       "                  team nationality   yob sex age_class  age_class_place  \\\n",
       "38966  SV Blitzenreute         DEU  1962   M       M40             4357   \n",
       "42175              NaN         DEU  1968   M       M35             3544   \n",
       "52108              NaN         DEU  1949   M       M55              936   \n",
       "46026              NaN         DEU  1960   W       W45              579   \n",
       "58489              NaN         DEU  1973   W       W30              524   \n",
       "43553              NaN         DEU  1953   M       M50             1985   \n",
       "59977              NaN         DEU  1979   M        MH             1817   \n",
       "53329              NaN         FRA  1955   M       M50             1986   \n",
       "\n",
       "      net_time clock_time  \n",
       "38966 04:41:50   04:43:48  \n",
       "42175 04:41:50   04:43:41  \n",
       "52108 04:41:51   04:46:29  \n",
       "46026 04:41:51   04:47:42  \n",
       "58489 04:41:51   04:26:20  \n",
       "43553 04:41:52   04:45:27  \n",
       "59977 04:41:52   04:45:31  \n",
       "53329 04:41:52   04:46:23  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[(df.year == ev.year) & (ev.place - 4 < df.place) & (df.place < ev.place + 5)].sort_values('place')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It seems likely that the clock time is off by 20 minutes, since changing one digit makes the clock times more consistent while keeping the difference of net/clock times within a few minutes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "idx                          58489\n",
       "year                          2006\n",
       "id                           41853\n",
       "place                        22138\n",
       "bib                          F4217\n",
       "surname                      Vogel\n",
       "forename                    Esther\n",
       "team                           NaN\n",
       "nationality                    DEU\n",
       "yob                           1973\n",
       "sex                              W\n",
       "age_class                      W30\n",
       "age_class_place                524\n",
       "net_time           0 days 04:41:51\n",
       "clock_time         0 days 04:46:20\n",
       "Name: 58489, dtype: object"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.set_value(ev.name, 'clock_time', ev.clock_time + pd.Timedelta('20 min'))\n",
    "df.loc[ev.name]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>idx</th>\n",
       "      <th>year</th>\n",
       "      <th>id</th>\n",
       "      <th>place</th>\n",
       "      <th>bib</th>\n",
       "      <th>surname</th>\n",
       "      <th>forename</th>\n",
       "      <th>team</th>\n",
       "      <th>nationality</th>\n",
       "      <th>yob</th>\n",
       "      <th>sex</th>\n",
       "      <th>age_class</th>\n",
       "      <th>age_class_place</th>\n",
       "      <th>net_time</th>\n",
       "      <th>clock_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>305127</th>\n",
       "      <td>305127</td>\n",
       "      <td>2014</td>\n",
       "      <td>3965</td>\n",
       "      <td>2670</td>\n",
       "      <td>6318</td>\n",
       "      <td>Borck</td>\n",
       "      <td>Jan</td>\n",
       "      <td>Der Kleine Muck</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1964</td>\n",
       "      <td>M</td>\n",
       "      <td>50</td>\n",
       "      <td>200</td>\n",
       "      <td>03:13:55</td>\n",
       "      <td>03:14:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>308016</th>\n",
       "      <td>308016</td>\n",
       "      <td>2014</td>\n",
       "      <td>6920</td>\n",
       "      <td>2671</td>\n",
       "      <td>26033</td>\n",
       "      <td>Schuller</td>\n",
       "      <td>Eelco</td>\n",
       "      <td>PAC</td>\n",
       "      <td>NLD</td>\n",
       "      <td>1986</td>\n",
       "      <td>M</td>\n",
       "      <td>H</td>\n",
       "      <td>264</td>\n",
       "      <td>03:13:55</td>\n",
       "      <td>03:15:41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329255</th>\n",
       "      <td>329255</td>\n",
       "      <td>2014</td>\n",
       "      <td>28776</td>\n",
       "      <td>2672</td>\n",
       "      <td>6185</td>\n",
       "      <td>Boge</td>\n",
       "      <td>Georg</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1980</td>\n",
       "      <td>M</td>\n",
       "      <td>30</td>\n",
       "      <td>464</td>\n",
       "      <td>03:13:55</td>\n",
       "      <td>03:18:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>312161</th>\n",
       "      <td>312161</td>\n",
       "      <td>2014</td>\n",
       "      <td>11182</td>\n",
       "      <td>2673</td>\n",
       "      <td>13549</td>\n",
       "      <td>Sowisnki</td>\n",
       "      <td>Fabrice</td>\n",
       "      <td>SAINT VENANT ATHLETISME</td>\n",
       "      <td>FRA</td>\n",
       "      <td>1974</td>\n",
       "      <td>M</td>\n",
       "      <td>40</td>\n",
       "      <td>595</td>\n",
       "      <td>03:13:55</td>\n",
       "      <td>02:58:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306101</th>\n",
       "      <td>306101</td>\n",
       "      <td>2014</td>\n",
       "      <td>4960</td>\n",
       "      <td>2674</td>\n",
       "      <td>F597</td>\n",
       "      <td>Aljancic</td>\n",
       "      <td>Natasa</td>\n",
       "      <td>AK DOMALE</td>\n",
       "      <td>SVN</td>\n",
       "      <td>1969</td>\n",
       "      <td>W</td>\n",
       "      <td>45</td>\n",
       "      <td>13</td>\n",
       "      <td>03:13:56</td>\n",
       "      <td>03:15:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>304271</th>\n",
       "      <td>304271</td>\n",
       "      <td>2014</td>\n",
       "      <td>3080</td>\n",
       "      <td>2675</td>\n",
       "      <td>7519</td>\n",
       "      <td>Chambers</td>\n",
       "      <td>Dion</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GBR</td>\n",
       "      <td>1989</td>\n",
       "      <td>M</td>\n",
       "      <td>H</td>\n",
       "      <td>265</td>\n",
       "      <td>03:13:56</td>\n",
       "      <td>03:14:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306792</th>\n",
       "      <td>306792</td>\n",
       "      <td>2014</td>\n",
       "      <td>5671</td>\n",
       "      <td>2676</td>\n",
       "      <td>7933</td>\n",
       "      <td>Colsman</td>\n",
       "      <td>Philipp</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1967</td>\n",
       "      <td>M</td>\n",
       "      <td>45</td>\n",
       "      <td>442</td>\n",
       "      <td>03:13:57</td>\n",
       "      <td>03:16:41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327244</th>\n",
       "      <td>327244</td>\n",
       "      <td>2014</td>\n",
       "      <td>26721</td>\n",
       "      <td>2677</td>\n",
       "      <td>13784</td>\n",
       "      <td>Hinrichsen</td>\n",
       "      <td>Thomas</td>\n",
       "      <td>HRLK / BLL</td>\n",
       "      <td>DNK</td>\n",
       "      <td>1975</td>\n",
       "      <td>M</td>\n",
       "      <td>35</td>\n",
       "      <td>517</td>\n",
       "      <td>03:13:58</td>\n",
       "      <td>03:14:12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           idx  year     id  place    bib     surname forename  \\\n",
       "305127  305127  2014   3965   2670   6318       Borck      Jan   \n",
       "308016  308016  2014   6920   2671  26033    Schuller    Eelco   \n",
       "329255  329255  2014  28776   2672   6185        Boge    Georg   \n",
       "312161  312161  2014  11182   2673  13549    Sowisnki  Fabrice   \n",
       "306101  306101  2014   4960   2674   F597    Aljancic   Natasa   \n",
       "304271  304271  2014   3080   2675   7519    Chambers     Dion   \n",
       "306792  306792  2014   5671   2676   7933     Colsman  Philipp   \n",
       "327244  327244  2014  26721   2677  13784  Hinrichsen   Thomas   \n",
       "\n",
       "                           team nationality   yob sex age_class  \\\n",
       "305127          Der Kleine Muck         DEU  1964   M        50   \n",
       "308016                      PAC         NLD  1986   M         H   \n",
       "329255                      NaN         DEU  1980   M        30   \n",
       "312161  SAINT VENANT ATHLETISME         FRA  1974   M        40   \n",
       "306101               AK DOMALE         SVN  1969   W        45   \n",
       "304271                      NaN         GBR  1989   M         H   \n",
       "306792                      NaN         DEU  1967   M        45   \n",
       "327244               HRLK / BLL         DNK  1975   M        35   \n",
       "\n",
       "        age_class_place net_time clock_time  \n",
       "305127              200 03:13:55   03:14:12  \n",
       "308016              264 03:13:55   03:15:41  \n",
       "329255              464 03:13:55   03:18:29  \n",
       "312161              595 03:13:55   02:58:59  \n",
       "306101               13 03:13:56   03:15:45  \n",
       "304271              265 03:13:56   03:14:53  \n",
       "306792              442 03:13:57   03:16:41  \n",
       "327244              517 03:13:58   03:14:12  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[(df.year == fs.year) & (fs.place - 4 < df.place) & (df.place < fs.place + 5)].sort_values('place')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "idx                                 312161\n",
       "year                                  2014\n",
       "id                                   11182\n",
       "place                                 2673\n",
       "bib                                  13549\n",
       "surname                           Sowisnki\n",
       "forename                           Fabrice\n",
       "team               SAINT VENANT ATHLETISME\n",
       "nationality                            FRA\n",
       "yob                                   1974\n",
       "sex                                      M\n",
       "age_class                               40\n",
       "age_class_place                        595\n",
       "net_time                   0 days 03:13:55\n",
       "clock_time                 0 days 04:46:20\n",
       "Name: 312161, dtype: object"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.set_value(fs.name, 'clock_time', ev.clock_time + pd.Timedelta('20 min'))\n",
    "df.loc[fs.name]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[df.net_time > df.clock_time])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     403132\n",
       "unique         2\n",
       "top            M\n",
       "freq      313715\n",
       "Name: sex, dtype: object"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sex.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "M    313715\n",
       "W     89417\n",
       "Name: sex, dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sex.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "no_sex = df[df.sex.isnull()]\n",
    "len(no_sex)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>idx</th>\n",
       "      <th>year</th>\n",
       "      <th>id</th>\n",
       "      <th>place</th>\n",
       "      <th>bib</th>\n",
       "      <th>surname</th>\n",
       "      <th>forename</th>\n",
       "      <th>team</th>\n",
       "      <th>nationality</th>\n",
       "      <th>yob</th>\n",
       "      <th>sex</th>\n",
       "      <th>age_class</th>\n",
       "      <th>age_class_place</th>\n",
       "      <th>net_time</th>\n",
       "      <th>clock_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>391937</th>\n",
       "      <td>391937</td>\n",
       "      <td>2016</td>\n",
       "      <td>77124</td>\n",
       "      <td>27472</td>\n",
       "      <td>6956</td>\n",
       "      <td>Barris</td>\n",
       "      <td>Wolfram</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1951</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>191</td>\n",
       "      <td>04:43:58</td>\n",
       "      <td>05:00:42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>392473</th>\n",
       "      <td>392473</td>\n",
       "      <td>2016</td>\n",
       "      <td>78774</td>\n",
       "      <td>26936</td>\n",
       "      <td>40651</td>\n",
       "      <td>Schnetzer</td>\n",
       "      <td>Wolfgang</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1961</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1509</td>\n",
       "      <td>04:41:32</td>\n",
       "      <td>05:15:56</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           idx  year     id  place    bib    surname  forename team  \\\n",
       "391937  391937  2016  77124  27472   6956     Barris   Wolfram  NaN   \n",
       "392473  392473  2016  78774  26936  40651  Schnetzer  Wolfgang  NaN   \n",
       "\n",
       "       nationality   yob  sex age_class  age_class_place net_time clock_time  \n",
       "391937         NaN  1951  NaN       NaN              191 04:43:58   05:00:42  \n",
       "392473         NaN  1961  NaN       NaN             1509 04:41:32   05:15:56  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "no_sex"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These are male forenames, so we set the sex to 'M'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sex = df.sex.fillna('M').astype('category')\n",
    "len(df[df.sex.isnull()])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Year of Birth (YOB)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[df.yob.isnull()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    403134.000000\n",
       "mean       1967.243013\n",
       "std          47.518969\n",
       "min           0.000000\n",
       "25%        1962.000000\n",
       "50%        1968.000000\n",
       "75%        1976.000000\n",
       "max        1998.000000\n",
       "Name: yob, dtype: float64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.yob.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "'0' doesn't make any sense as a YOB, so we'd be better off setting it to null."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    402910.000000\n",
       "mean       1968.336713\n",
       "std          10.321517\n",
       "min        1901.000000\n",
       "25%        1962.000000\n",
       "50%        1968.000000\n",
       "75%        1976.000000\n",
       "max        1998.000000\n",
       "Name: yob, dtype: float64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.yob = df.yob.replace(0, np.nan)\n",
    "df.yob.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is unlikely to have a YOB of 1901 - this was probably used as a default value for unknown or corrupted data.  Please never do this, it's very misleading."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1901.0    22\n",
       "1902.0     3\n",
       "1923.0     2\n",
       "1925.0     2\n",
       "1926.0     5\n",
       "1927.0    12\n",
       "1928.0    12\n",
       "1929.0     8\n",
       "1930.0    11\n",
       "1931.0    22\n",
       "Name: yob, dtype: int64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.yob.value_counts().sort_index().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>yob</th>\n",
       "      <th>age</th>\n",
       "      <th>age_class</th>\n",
       "      <th>net_time</th>\n",
       "      <th>place</th>\n",
       "      <th>age_class_place</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>393222</th>\n",
       "      <td>2016</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>115.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>03:05:59</td>\n",
       "      <td>2126</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168821</th>\n",
       "      <td>2010</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>W</td>\n",
       "      <td>04:00:01</td>\n",
       "      <td>16646</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171914</th>\n",
       "      <td>2010</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>M</td>\n",
       "      <td>04:09:04</td>\n",
       "      <td>19394</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172694</th>\n",
       "      <td>2010</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>M</td>\n",
       "      <td>03:54:42</td>\n",
       "      <td>14287</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179028</th>\n",
       "      <td>2010</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>M</td>\n",
       "      <td>04:50:29</td>\n",
       "      <td>29460</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179175</th>\n",
       "      <td>2010</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>W</td>\n",
       "      <td>05:02:11</td>\n",
       "      <td>31018</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180154</th>\n",
       "      <td>2010</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>M</td>\n",
       "      <td>04:09:35</td>\n",
       "      <td>19565</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183176</th>\n",
       "      <td>2010</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>M</td>\n",
       "      <td>03:08:16</td>\n",
       "      <td>2071</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189399</th>\n",
       "      <td>2010</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>M</td>\n",
       "      <td>03:40:54</td>\n",
       "      <td>9474</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190564</th>\n",
       "      <td>2010</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>M</td>\n",
       "      <td>03:41:56</td>\n",
       "      <td>9751</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190944</th>\n",
       "      <td>2010</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>W</td>\n",
       "      <td>05:01:29</td>\n",
       "      <td>30959</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192247</th>\n",
       "      <td>2010</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>M</td>\n",
       "      <td>02:49:48</td>\n",
       "      <td>460</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193183</th>\n",
       "      <td>2010</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>M</td>\n",
       "      <td>03:37:32</td>\n",
       "      <td>8467</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195244</th>\n",
       "      <td>2010</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>W</td>\n",
       "      <td>04:47:52</td>\n",
       "      <td>29034</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130881</th>\n",
       "      <td>2009</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>MH</td>\n",
       "      <td>04:33:53</td>\n",
       "      <td>24617</td>\n",
       "      <td>2274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139199</th>\n",
       "      <td>2009</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>MH</td>\n",
       "      <td>04:40:52</td>\n",
       "      <td>26378</td>\n",
       "      <td>2405</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155445</th>\n",
       "      <td>2009</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>MH</td>\n",
       "      <td>03:57:45</td>\n",
       "      <td>13536</td>\n",
       "      <td>1340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156096</th>\n",
       "      <td>2009</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>WH</td>\n",
       "      <td>04:43:34</td>\n",
       "      <td>27056</td>\n",
       "      <td>652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157318</th>\n",
       "      <td>2009</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>MH</td>\n",
       "      <td>04:06:50</td>\n",
       "      <td>16460</td>\n",
       "      <td>1599</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157658</th>\n",
       "      <td>2009</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>MH</td>\n",
       "      <td>03:51:03</td>\n",
       "      <td>11064</td>\n",
       "      <td>1111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163345</th>\n",
       "      <td>2009</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>MH</td>\n",
       "      <td>04:24:10</td>\n",
       "      <td>21907</td>\n",
       "      <td>2071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163347</th>\n",
       "      <td>2009</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>MH</td>\n",
       "      <td>03:27:01</td>\n",
       "      <td>4755</td>\n",
       "      <td>557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>174163</th>\n",
       "      <td>2010</td>\n",
       "      <td>1902.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>M</td>\n",
       "      <td>03:52:07</td>\n",
       "      <td>13285</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179851</th>\n",
       "      <td>2010</td>\n",
       "      <td>1902.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>W</td>\n",
       "      <td>05:05:23</td>\n",
       "      <td>31290</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159836</th>\n",
       "      <td>2009</td>\n",
       "      <td>1902.0</td>\n",
       "      <td>107.0</td>\n",
       "      <td>MH</td>\n",
       "      <td>03:13:28</td>\n",
       "      <td>2460</td>\n",
       "      <td>309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145928</th>\n",
       "      <td>2009</td>\n",
       "      <td>1923.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>M80</td>\n",
       "      <td>06:42:35</td>\n",
       "      <td>35006</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>2005</td>\n",
       "      <td>1923.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>M80</td>\n",
       "      <td>06:55:52</td>\n",
       "      <td>30381</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64392</th>\n",
       "      <td>2007</td>\n",
       "      <td>1925.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>W80</td>\n",
       "      <td>06:01:59</td>\n",
       "      <td>32286</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5959</th>\n",
       "      <td>2005</td>\n",
       "      <td>1925.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>M80</td>\n",
       "      <td>04:31:53</td>\n",
       "      <td>21492</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        year     yob    age age_class net_time  place  age_class_place\n",
       "393222  2016  1901.0  115.0       NaN 03:05:59   2126                0\n",
       "168821  2010  1901.0  109.0         W 04:00:01  16646                0\n",
       "171914  2010  1901.0  109.0         M 04:09:04  19394                0\n",
       "172694  2010  1901.0  109.0         M 03:54:42  14287                0\n",
       "179028  2010  1901.0  109.0         M 04:50:29  29460                0\n",
       "179175  2010  1901.0  109.0         W 05:02:11  31018                0\n",
       "180154  2010  1901.0  109.0         M 04:09:35  19565                0\n",
       "183176  2010  1901.0  109.0         M 03:08:16   2071                0\n",
       "189399  2010  1901.0  109.0         M 03:40:54   9474                0\n",
       "190564  2010  1901.0  109.0         M 03:41:56   9751                0\n",
       "190944  2010  1901.0  109.0         W 05:01:29  30959                0\n",
       "192247  2010  1901.0  109.0         M 02:49:48    460                0\n",
       "193183  2010  1901.0  109.0         M 03:37:32   8467                0\n",
       "195244  2010  1901.0  109.0         W 04:47:52  29034                0\n",
       "130881  2009  1901.0  108.0        MH 04:33:53  24617             2274\n",
       "139199  2009  1901.0  108.0        MH 04:40:52  26378             2405\n",
       "155445  2009  1901.0  108.0        MH 03:57:45  13536             1340\n",
       "156096  2009  1901.0  108.0        WH 04:43:34  27056              652\n",
       "157318  2009  1901.0  108.0        MH 04:06:50  16460             1599\n",
       "157658  2009  1901.0  108.0        MH 03:51:03  11064             1111\n",
       "163345  2009  1901.0  108.0        MH 04:24:10  21907             2071\n",
       "163347  2009  1901.0  108.0        MH 03:27:01   4755              557\n",
       "174163  2010  1902.0  108.0         M 03:52:07  13285                0\n",
       "179851  2010  1902.0  108.0         W 05:05:23  31290                0\n",
       "159836  2009  1902.0  107.0        MH 03:13:28   2460              309\n",
       "145928  2009  1923.0   86.0       M80 06:42:35  35006                5\n",
       "103     2005  1923.0   82.0       M80 06:55:52  30381                2\n",
       "64392   2007  1925.0   82.0       W80 06:01:59  32286                1\n",
       "5959    2005  1925.0   80.0       M80 04:31:53  21492                1"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['age'] = df.year - df.yob\n",
    "df[df.yob <= 1925].sort_values(['yob', 'age'], ascending=[True, False])[['year', 'yob', 'age', 'age_class', 'net_time', 'place', 'age_class_place']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Either a 109 year old run a 2:49 marathon or 1901/1902 are being used as nulls.  Note that the use of '0' as the age class supports this theory (we'll deal with age class later).  The values for those born after 1922 seem more believable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.yob = df.yob.replace(1901, np.nan).replace(1902, np.nan)\n",
    "df['age'] = df.year - df.yob"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once we have cleaned up the age class, we can use this information to guess the YOB."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Are some listed as too young to participate?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>idx</th>\n",
       "      <th>year</th>\n",
       "      <th>id</th>\n",
       "      <th>place</th>\n",
       "      <th>bib</th>\n",
       "      <th>surname</th>\n",
       "      <th>forename</th>\n",
       "      <th>team</th>\n",
       "      <th>nationality</th>\n",
       "      <th>yob</th>\n",
       "      <th>sex</th>\n",
       "      <th>age_class</th>\n",
       "      <th>age_class_place</th>\n",
       "      <th>net_time</th>\n",
       "      <th>clock_time</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2291</th>\n",
       "      <td>2291</td>\n",
       "      <td>2005</td>\n",
       "      <td>2685</td>\n",
       "      <td>7246</td>\n",
       "      <td>5257</td>\n",
       "      <td>Koepke</td>\n",
       "      <td>Mathias</td>\n",
       "      <td>Mahlower SV</td>\n",
       "      <td>GER</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>M</td>\n",
       "      <td>MJA</td>\n",
       "      <td>18</td>\n",
       "      <td>03:40:18</td>\n",
       "      <td>03:40:58</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4943</th>\n",
       "      <td>4943</td>\n",
       "      <td>2005</td>\n",
       "      <td>5704</td>\n",
       "      <td>26060</td>\n",
       "      <td>5466</td>\n",
       "      <td>Jacobsen</td>\n",
       "      <td>Eystein André</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NOR</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>M</td>\n",
       "      <td>MJA</td>\n",
       "      <td>97</td>\n",
       "      <td>04:55:34</td>\n",
       "      <td>04:59:23</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25172</th>\n",
       "      <td>25172</td>\n",
       "      <td>2005</td>\n",
       "      <td>31437</td>\n",
       "      <td>4107</td>\n",
       "      <td>29736</td>\n",
       "      <td>Petrausch</td>\n",
       "      <td>Janek</td>\n",
       "      <td>Schenker-TEAM</td>\n",
       "      <td>GER</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>M</td>\n",
       "      <td>MJA</td>\n",
       "      <td>9</td>\n",
       "      <td>03:26:21</td>\n",
       "      <td>03:28:55</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61428</th>\n",
       "      <td>61428</td>\n",
       "      <td>2007</td>\n",
       "      <td>915</td>\n",
       "      <td>20059</td>\n",
       "      <td>2446</td>\n",
       "      <td>Musielak</td>\n",
       "      <td>Norman</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>M</td>\n",
       "      <td>MJA</td>\n",
       "      <td>84</td>\n",
       "      <td>04:15:32</td>\n",
       "      <td>04:15:52</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61659</th>\n",
       "      <td>61659</td>\n",
       "      <td>2007</td>\n",
       "      <td>1154</td>\n",
       "      <td>30028</td>\n",
       "      <td>F1332</td>\n",
       "      <td>Scharpf</td>\n",
       "      <td>Maria</td>\n",
       "      <td>Scharpf-Family</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>W</td>\n",
       "      <td>WJA</td>\n",
       "      <td>23</td>\n",
       "      <td>05:09:06</td>\n",
       "      <td>05:15:09</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77385</th>\n",
       "      <td>77385</td>\n",
       "      <td>2007</td>\n",
       "      <td>17477</td>\n",
       "      <td>32343</td>\n",
       "      <td>F6340</td>\n",
       "      <td>Garbe</td>\n",
       "      <td>Tabitha</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>W</td>\n",
       "      <td>WJA</td>\n",
       "      <td>29</td>\n",
       "      <td>06:07:53</td>\n",
       "      <td>06:10:04</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139060</th>\n",
       "      <td>139060</td>\n",
       "      <td>2009</td>\n",
       "      <td>10843</td>\n",
       "      <td>24928</td>\n",
       "      <td>22567</td>\n",
       "      <td>Oppermann</td>\n",
       "      <td>Patrick</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1995.0</td>\n",
       "      <td>M</td>\n",
       "      <td>MJA</td>\n",
       "      <td>79</td>\n",
       "      <td>04:35:04</td>\n",
       "      <td>04:42:02</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167596</th>\n",
       "      <td>167596</td>\n",
       "      <td>2010</td>\n",
       "      <td>4134</td>\n",
       "      <td>20039</td>\n",
       "      <td>F8916</td>\n",
       "      <td>Forsberg</td>\n",
       "      <td>Sofie</td>\n",
       "      <td>MTC</td>\n",
       "      <td>DNK</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>W</td>\n",
       "      <td>WJA</td>\n",
       "      <td>7</td>\n",
       "      <td>04:11:04</td>\n",
       "      <td>04:33:41</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           idx  year     id  place    bib    surname       forename  \\\n",
       "2291      2291  2005   2685   7246   5257     Koepke        Mathias   \n",
       "4943      4943  2005   5704  26060   5466   Jacobsen  Eystein André   \n",
       "25172    25172  2005  31437   4107  29736  Petrausch          Janek   \n",
       "61428    61428  2007    915  20059   2446   Musielak         Norman   \n",
       "61659    61659  2007   1154  30028  F1332    Scharpf          Maria   \n",
       "77385    77385  2007  17477  32343  F6340      Garbe        Tabitha   \n",
       "139060  139060  2009  10843  24928  22567  Oppermann        Patrick   \n",
       "167596  167596  2010   4134  20039  F8916   Forsberg          Sofie   \n",
       "\n",
       "                  team nationality     yob sex age_class  age_class_place  \\\n",
       "2291       Mahlower SV         GER  1988.0   M       MJA               18   \n",
       "4943               NaN         NOR  1988.0   M       MJA               97   \n",
       "25172    Schenker-TEAM         GER  1988.0   M       MJA                9   \n",
       "61428              NaN         DEU  1990.0   M       MJA               84   \n",
       "61659   Scharpf-Family         DEU  1990.0   W       WJA               23   \n",
       "77385              NaN         DEU  1990.0   W       WJA               29   \n",
       "139060             NaN         DEU  1995.0   M       MJA               79   \n",
       "167596             MTC         DNK  1993.0   W       WJA                7   \n",
       "\n",
       "       net_time clock_time   age  \n",
       "2291   03:40:18   03:40:58  17.0  \n",
       "4943   04:55:34   04:59:23  17.0  \n",
       "25172  03:26:21   03:28:55  17.0  \n",
       "61428  04:15:32   04:15:52  17.0  \n",
       "61659  05:09:06   05:15:09  17.0  \n",
       "77385  06:07:53   06:10:04  17.0  \n",
       "139060 04:35:04   04:42:02  14.0  \n",
       "167596 04:11:04   04:33:41  17.0  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.age < 18]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The 'J' probably stands for 'junior', so it seems there may have been a kid's run in the past."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Age Class"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "30     25394\n",
       "35     30177\n",
       "40     38211\n",
       "45     38433\n",
       "50     28243\n",
       "55     13845\n",
       "60      6461\n",
       "65      2477\n",
       "70       971\n",
       "75       255\n",
       "80        37\n",
       "H      20298\n",
       "JA       420\n",
       "M         25\n",
       "M30    18301\n",
       "M35    26373\n",
       "M40    33912\n",
       "M45    27643\n",
       "M50    17945\n",
       "M55     9114\n",
       "M60     4376\n",
       "M65     2058\n",
       "M70      597\n",
       "M75       82\n",
       "M80       15\n",
       "MH     16315\n",
       "MJA      724\n",
       "U20       95\n",
       "W          8\n",
       "W30     5474\n",
       "W35     6598\n",
       "W40     8675\n",
       "W45     7093\n",
       "W50     4033\n",
       "W55     1618\n",
       "W60      584\n",
       "W65      251\n",
       "W70       50\n",
       "W75        3\n",
       "W80        5\n",
       "WH      5742\n",
       "WJA      174\n",
       "Name: age_class, dtype: int64"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.age_class.value_counts().sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The age class groups are very inconsistent in their naming and many also contain information about the sex of the participant.  Before removing the sex from the age class, we can use it as a consistency check for the sex column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "197788"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[df.age_class.str.contains(r'[MW]', na=False)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "34"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "age_class_sex = df[df.age_class.str.contains(r'[MW]', na=False)].age_class.str.get(0)\n",
    "inconsistent_sex0 = df.loc[age_class_sex.index, 'sex'] != age_class_sex\n",
    "inconsistent_sex = df.loc[inconsistent_sex0[inconsistent_sex0 == True].index, :]\n",
    "len(inconsistent_sex)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>idx</th>\n",
       "      <th>year</th>\n",
       "      <th>id</th>\n",
       "      <th>place</th>\n",
       "      <th>bib</th>\n",
       "      <th>surname</th>\n",
       "      <th>forename</th>\n",
       "      <th>team</th>\n",
       "      <th>nationality</th>\n",
       "      <th>yob</th>\n",
       "      <th>sex</th>\n",
       "      <th>age_class</th>\n",
       "      <th>age_class_place</th>\n",
       "      <th>net_time</th>\n",
       "      <th>clock_time</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9201</th>\n",
       "      <td>9201</td>\n",
       "      <td>2005</td>\n",
       "      <td>10341</td>\n",
       "      <td>1532</td>\n",
       "      <td>F3923</td>\n",
       "      <td>Lohmann</td>\n",
       "      <td>Henrik</td>\n",
       "      <td>Sparta</td>\n",
       "      <td>DEN</td>\n",
       "      <td>1964.0</td>\n",
       "      <td>W</td>\n",
       "      <td>M40</td>\n",
       "      <td>353</td>\n",
       "      <td>03:06:49</td>\n",
       "      <td>03:07:37</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10108</th>\n",
       "      <td>10108</td>\n",
       "      <td>2005</td>\n",
       "      <td>11280</td>\n",
       "      <td>14344</td>\n",
       "      <td>F5717</td>\n",
       "      <td>Sanchez</td>\n",
       "      <td>Raul</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SUI</td>\n",
       "      <td>1962.0</td>\n",
       "      <td>W</td>\n",
       "      <td>M40</td>\n",
       "      <td>3067</td>\n",
       "      <td>04:04:49</td>\n",
       "      <td>04:08:49</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19935</th>\n",
       "      <td>19935</td>\n",
       "      <td>2005</td>\n",
       "      <td>23704</td>\n",
       "      <td>10930</td>\n",
       "      <td>F4420</td>\n",
       "      <td>Kachlik</td>\n",
       "      <td>Stephan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GER</td>\n",
       "      <td>1963.0</td>\n",
       "      <td>W</td>\n",
       "      <td>M40</td>\n",
       "      <td>2410</td>\n",
       "      <td>03:54:04</td>\n",
       "      <td>03:56:47</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20122</th>\n",
       "      <td>20122</td>\n",
       "      <td>2005</td>\n",
       "      <td>23897</td>\n",
       "      <td>7434</td>\n",
       "      <td>F5646</td>\n",
       "      <td>Kirchhoff</td>\n",
       "      <td>Jochen</td>\n",
       "      <td>TSV Bollensen</td>\n",
       "      <td>GER</td>\n",
       "      <td>1957.0</td>\n",
       "      <td>W</td>\n",
       "      <td>M45</td>\n",
       "      <td>1085</td>\n",
       "      <td>03:41:07</td>\n",
       "      <td>03:44:33</td>\n",
       "      <td>48.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23362</th>\n",
       "      <td>23362</td>\n",
       "      <td>2005</td>\n",
       "      <td>28829</td>\n",
       "      <td>9129</td>\n",
       "      <td>F6506</td>\n",
       "      <td>Pape</td>\n",
       "      <td>Gerald</td>\n",
       "      <td>TriFrogs Gellersen</td>\n",
       "      <td>GER</td>\n",
       "      <td>1965.0</td>\n",
       "      <td>W</td>\n",
       "      <td>M40</td>\n",
       "      <td>2017</td>\n",
       "      <td>03:47:41</td>\n",
       "      <td>03:49:22</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26631</th>\n",
       "      <td>26631</td>\n",
       "      <td>2005</td>\n",
       "      <td>33336</td>\n",
       "      <td>21772</td>\n",
       "      <td>F8928</td>\n",
       "      <td>Tricoche</td>\n",
       "      <td>Elisabeth</td>\n",
       "      <td>Defi Horizon</td>\n",
       "      <td>FRA</td>\n",
       "      <td>1949.0</td>\n",
       "      <td>W</td>\n",
       "      <td>M55</td>\n",
       "      <td>840</td>\n",
       "      <td>04:33:09</td>\n",
       "      <td>04:37:12</td>\n",
       "      <td>56.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30198</th>\n",
       "      <td>30198</td>\n",
       "      <td>2005</td>\n",
       "      <td>37786</td>\n",
       "      <td>6827</td>\n",
       "      <td>32681</td>\n",
       "      <td>Filippo</td>\n",
       "      <td>Proietti</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ITA</td>\n",
       "      <td>1967.0</td>\n",
       "      <td>M</td>\n",
       "      <td>W35</td>\n",
       "      <td>102</td>\n",
       "      <td>03:38:31</td>\n",
       "      <td>03:40:10</td>\n",
       "      <td>38.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30200</th>\n",
       "      <td>30200</td>\n",
       "      <td>2005</td>\n",
       "      <td>37789</td>\n",
       "      <td>20204</td>\n",
       "      <td>32699</td>\n",
       "      <td>Demetrio</td>\n",
       "      <td>Alberto</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BRA</td>\n",
       "      <td>1965.0</td>\n",
       "      <td>M</td>\n",
       "      <td>W40</td>\n",
       "      <td>677</td>\n",
       "      <td>04:26:27</td>\n",
       "      <td>04:30:40</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34335</th>\n",
       "      <td>34335</td>\n",
       "      <td>2006</td>\n",
       "      <td>4614</td>\n",
       "      <td>2110</td>\n",
       "      <td>F3676</td>\n",
       "      <td>Aeby</td>\n",
       "      <td>Pascal Laurent</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1966.0</td>\n",
       "      <td>W</td>\n",
       "      <td>M40</td>\n",
       "      <td>495</td>\n",
       "      <td>03:16:09</td>\n",
       "      <td>03:16:17</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44551</th>\n",
       "      <td>44551</td>\n",
       "      <td>2006</td>\n",
       "      <td>16028</td>\n",
       "      <td>106</td>\n",
       "      <td>F7055</td>\n",
       "      <td>Nordgaard</td>\n",
       "      <td>Haavard</td>\n",
       "      <td>Nike</td>\n",
       "      <td>NOR</td>\n",
       "      <td>1961.0</td>\n",
       "      <td>W</td>\n",
       "      <td>M45</td>\n",
       "      <td>3</td>\n",
       "      <td>02:38:57</td>\n",
       "      <td>02:39:04</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58571</th>\n",
       "      <td>58571</td>\n",
       "      <td>2006</td>\n",
       "      <td>41938</td>\n",
       "      <td>19813</td>\n",
       "      <td>32282</td>\n",
       "      <td>Filho</td>\n",
       "      <td>Lourival</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>BRA</td>\n",
       "      <td>1957.0</td>\n",
       "      <td>M</td>\n",
       "      <td>W45</td>\n",
       "      <td>460</td>\n",
       "      <td>04:31:24</td>\n",
       "      <td>04:33:18</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60290</th>\n",
       "      <td>60290</td>\n",
       "      <td>2006</td>\n",
       "      <td>43722</td>\n",
       "      <td>1571</td>\n",
       "      <td>F7523</td>\n",
       "      <td>Basdekis</td>\n",
       "      <td>Pavlos</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GRC</td>\n",
       "      <td>1946.0</td>\n",
       "      <td>W</td>\n",
       "      <td>M60</td>\n",
       "      <td>5</td>\n",
       "      <td>03:11:03</td>\n",
       "      <td>03:11:27</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60849</th>\n",
       "      <td>60849</td>\n",
       "      <td>2007</td>\n",
       "      <td>306</td>\n",
       "      <td>19484</td>\n",
       "      <td>F9642</td>\n",
       "      <td>Liden</td>\n",
       "      <td>Mikael</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SWE</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>W</td>\n",
       "      <td>M35</td>\n",
       "      <td>3192</td>\n",
       "      <td>04:13:29</td>\n",
       "      <td>04:14:52</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63342</th>\n",
       "      <td>63342</td>\n",
       "      <td>2007</td>\n",
       "      <td>2922</td>\n",
       "      <td>25182</td>\n",
       "      <td>F9645</td>\n",
       "      <td>Knaack</td>\n",
       "      <td>Oliver</td>\n",
       "      <td>Berlin Sluggers e.V.</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1969.0</td>\n",
       "      <td>W</td>\n",
       "      <td>M35</td>\n",
       "      <td>3830</td>\n",
       "      <td>04:35:38</td>\n",
       "      <td>04:36:56</td>\n",
       "      <td>38.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66246</th>\n",
       "      <td>66246</td>\n",
       "      <td>2007</td>\n",
       "      <td>5922</td>\n",
       "      <td>12248</td>\n",
       "      <td>F2071</td>\n",
       "      <td>Himmelstein</td>\n",
       "      <td>Ari</td>\n",
       "      <td>The Team R&amp;F</td>\n",
       "      <td>BRA</td>\n",
       "      <td>1961.0</td>\n",
       "      <td>W</td>\n",
       "      <td>M45</td>\n",
       "      <td>1914</td>\n",
       "      <td>03:52:12</td>\n",
       "      <td>03:55:13</td>\n",
       "      <td>46.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66879</th>\n",
       "      <td>66879</td>\n",
       "      <td>2007</td>\n",
       "      <td>6571</td>\n",
       "      <td>3335</td>\n",
       "      <td>F9637</td>\n",
       "      <td>Holmes</td>\n",
       "      <td>Ben</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GBR</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>W</td>\n",
       "      <td>MH</td>\n",
       "      <td>357</td>\n",
       "      <td>03:18:26</td>\n",
       "      <td>03:19:08</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69290</th>\n",
       "      <td>69290</td>\n",
       "      <td>2007</td>\n",
       "      <td>9038</td>\n",
       "      <td>32171</td>\n",
       "      <td>F9640</td>\n",
       "      <td>Wrzesien</td>\n",
       "      <td>Szymon</td>\n",
       "      <td>Auto Buniks</td>\n",
       "      <td>POL</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>W</td>\n",
       "      <td>MJA</td>\n",
       "      <td>119</td>\n",
       "      <td>05:54:43</td>\n",
       "      <td>05:56:55</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69574</th>\n",
       "      <td>69574</td>\n",
       "      <td>2007</td>\n",
       "      <td>9330</td>\n",
       "      <td>29549</td>\n",
       "      <td>F9643</td>\n",
       "      <td>Bergström</td>\n",
       "      <td>Fredrik</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SWE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>W</td>\n",
       "      <td>MH</td>\n",
       "      <td>2558</td>\n",
       "      <td>05:03:28</td>\n",
       "      <td>05:04:51</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69640</th>\n",
       "      <td>69640</td>\n",
       "      <td>2007</td>\n",
       "      <td>9396</td>\n",
       "      <td>4775</td>\n",
       "      <td>33909</td>\n",
       "      <td>Ángel Ávila</td>\n",
       "      <td>Juan Manuel</td>\n",
       "      <td>Nacho Sport</td>\n",
       "      <td>ESP</td>\n",
       "      <td>1963.0</td>\n",
       "      <td>M</td>\n",
       "      <td>W40</td>\n",
       "      <td>58</td>\n",
       "      <td>03:26:09</td>\n",
       "      <td>03:28:44</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69739</th>\n",
       "      <td>69739</td>\n",
       "      <td>2007</td>\n",
       "      <td>9497</td>\n",
       "      <td>24573</td>\n",
       "      <td>34121</td>\n",
       "      <td>Hyde</td>\n",
       "      <td>Kyle</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GBR</td>\n",
       "      <td>1982.0</td>\n",
       "      <td>M</td>\n",
       "      <td>WH</td>\n",
       "      <td>549</td>\n",
       "      <td>04:32:42</td>\n",
       "      <td>04:33:03</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80021</th>\n",
       "      <td>80021</td>\n",
       "      <td>2007</td>\n",
       "      <td>20252</td>\n",
       "      <td>15106</td>\n",
       "      <td>F9646</td>\n",
       "      <td>Eastman</td>\n",
       "      <td>Oliver</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GBR</td>\n",
       "      <td>1975.0</td>\n",
       "      <td>W</td>\n",
       "      <td>M30</td>\n",
       "      <td>1733</td>\n",
       "      <td>03:58:57</td>\n",
       "      <td>04:02:33</td>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80944</th>\n",
       "      <td>80944</td>\n",
       "      <td>2007</td>\n",
       "      <td>21219</td>\n",
       "      <td>1270</td>\n",
       "      <td>F8957</td>\n",
       "      <td>Sørensen</td>\n",
       "      <td>Jytte Vestergaard</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DNK</td>\n",
       "      <td>1960.0</td>\n",
       "      <td>W</td>\n",
       "      <td>M45</td>\n",
       "      <td>159</td>\n",
       "      <td>03:01:18</td>\n",
       "      <td>03:02:26</td>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86445</th>\n",
       "      <td>86445</td>\n",
       "      <td>2007</td>\n",
       "      <td>29757</td>\n",
       "      <td>8923</td>\n",
       "      <td>34146</td>\n",
       "      <td>Frank</td>\n",
       "      <td>Vandenhole</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FRA</td>\n",
       "      <td>1968.0</td>\n",
       "      <td>M</td>\n",
       "      <td>W35</td>\n",
       "      <td>129</td>\n",
       "      <td>03:41:50</td>\n",
       "      <td>03:42:01</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86616</th>\n",
       "      <td>86616</td>\n",
       "      <td>2007</td>\n",
       "      <td>29964</td>\n",
       "      <td>26057</td>\n",
       "      <td>F9638</td>\n",
       "      <td>Richter</td>\n",
       "      <td>Frank</td>\n",
       "      <td>Wilde Wölfe Holzweißig</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1956.0</td>\n",
       "      <td>W</td>\n",
       "      <td>M50</td>\n",
       "      <td>2290</td>\n",
       "      <td>04:40:06</td>\n",
       "      <td>04:42:31</td>\n",
       "      <td>51.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89023</th>\n",
       "      <td>89023</td>\n",
       "      <td>2007</td>\n",
       "      <td>33174</td>\n",
       "      <td>28164</td>\n",
       "      <td>F9639</td>\n",
       "      <td>Welber</td>\n",
       "      <td>Adam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>W</td>\n",
       "      <td>M35</td>\n",
       "      <td>4116</td>\n",
       "      <td>04:52:41</td>\n",
       "      <td>04:54:15</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92208</th>\n",
       "      <td>92208</td>\n",
       "      <td>2007</td>\n",
       "      <td>37911</td>\n",
       "      <td>20212</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Middeke</td>\n",
       "      <td>Jasmin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>M</td>\n",
       "      <td>WJA</td>\n",
       "      <td>10</td>\n",
       "      <td>04:15:58</td>\n",
       "      <td>04:17:41</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96128</th>\n",
       "      <td>96128</td>\n",
       "      <td>2008</td>\n",
       "      <td>3667</td>\n",
       "      <td>4703</td>\n",
       "      <td>F1025</td>\n",
       "      <td>Maison</td>\n",
       "      <td>Sylvie</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FRA</td>\n",
       "      <td>1964.0</td>\n",
       "      <td>W</td>\n",
       "      <td>M40</td>\n",
       "      <td>1082</td>\n",
       "      <td>03:22:29</td>\n",
       "      <td>03:22:55</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99806</th>\n",
       "      <td>99806</td>\n",
       "      <td>2008</td>\n",
       "      <td>7472</td>\n",
       "      <td>15536</td>\n",
       "      <td>37917</td>\n",
       "      <td>Macedo</td>\n",
       "      <td>Guilherme</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BRA</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>M</td>\n",
       "      <td>WH</td>\n",
       "      <td>258</td>\n",
       "      <td>03:56:58</td>\n",
       "      <td>04:01:31</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102182</th>\n",
       "      <td>102182</td>\n",
       "      <td>2008</td>\n",
       "      <td>9951</td>\n",
       "      <td>23810</td>\n",
       "      <td>12454</td>\n",
       "      <td>Hautamaa</td>\n",
       "      <td>Anne</td>\n",
       "      <td>Himanka</td>\n",
       "      <td>FIN</td>\n",
       "      <td>1959.0</td>\n",
       "      <td>M</td>\n",
       "      <td>W45</td>\n",
       "      <td>589</td>\n",
       "      <td>04:22:10</td>\n",
       "      <td>04:24:49</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112288</th>\n",
       "      <td>112288</td>\n",
       "      <td>2008</td>\n",
       "      <td>20854</td>\n",
       "      <td>15085</td>\n",
       "      <td>37913</td>\n",
       "      <td>Michele</td>\n",
       "      <td>Tolin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ITA</td>\n",
       "      <td>1969.0</td>\n",
       "      <td>M</td>\n",
       "      <td>W35</td>\n",
       "      <td>283</td>\n",
       "      <td>03:55:55</td>\n",
       "      <td>03:58:28</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120566</th>\n",
       "      <td>120566</td>\n",
       "      <td>2008</td>\n",
       "      <td>31461</td>\n",
       "      <td>22967</td>\n",
       "      <td>F5968</td>\n",
       "      <td>Morlang</td>\n",
       "      <td>René</td>\n",
       "      <td>BSG Deutscher Ring</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1969.0</td>\n",
       "      <td>W</td>\n",
       "      <td>M35</td>\n",
       "      <td>3402</td>\n",
       "      <td>04:19:17</td>\n",
       "      <td>04:21:33</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124007</th>\n",
       "      <td>124007</td>\n",
       "      <td>2008</td>\n",
       "      <td>36818</td>\n",
       "      <td>4724</td>\n",
       "      <td>F7099</td>\n",
       "      <td>Rogoz</td>\n",
       "      <td>Grzegorz</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1969.0</td>\n",
       "      <td>W</td>\n",
       "      <td>M35</td>\n",
       "      <td>981</td>\n",
       "      <td>03:22:37</td>\n",
       "      <td>03:24:11</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124745</th>\n",
       "      <td>124745</td>\n",
       "      <td>2008</td>\n",
       "      <td>38362</td>\n",
       "      <td>16994</td>\n",
       "      <td>24442</td>\n",
       "      <td>Neuhaus</td>\n",
       "      <td>Svenja</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1976.0</td>\n",
       "      <td>M</td>\n",
       "      <td>W35</td>\n",
       "      <td>2725</td>\n",
       "      <td>03:59:50</td>\n",
       "      <td>04:14:42</td>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126245</th>\n",
       "      <td>126245</td>\n",
       "      <td>2008</td>\n",
       "      <td>42364</td>\n",
       "      <td>33949</td>\n",
       "      <td>F9289</td>\n",
       "      <td>de Pape</td>\n",
       "      <td>Aldo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NLD</td>\n",
       "      <td>1979.0</td>\n",
       "      <td>W</td>\n",
       "      <td>MH</td>\n",
       "      <td>2859</td>\n",
       "      <td>05:20:02</td>\n",
       "      <td>05:24:52</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           idx  year     id  place    bib      surname           forename  \\\n",
       "9201      9201  2005  10341   1532  F3923      Lohmann             Henrik   \n",
       "10108    10108  2005  11280  14344  F5717      Sanchez               Raul   \n",
       "19935    19935  2005  23704  10930  F4420      Kachlik            Stephan   \n",
       "20122    20122  2005  23897   7434  F5646    Kirchhoff             Jochen   \n",
       "23362    23362  2005  28829   9129  F6506         Pape             Gerald   \n",
       "26631    26631  2005  33336  21772  F8928     Tricoche          Elisabeth   \n",
       "30198    30198  2005  37786   6827  32681      Filippo           Proietti   \n",
       "30200    30200  2005  37789  20204  32699     Demetrio            Alberto   \n",
       "34335    34335  2006   4614   2110  F3676         Aeby     Pascal Laurent   \n",
       "44551    44551  2006  16028    106  F7055    Nordgaard            Haavard   \n",
       "58571    58571  2006  41938  19813  32282        Filho           Lourival   \n",
       "60290    60290  2006  43722   1571  F7523     Basdekis             Pavlos   \n",
       "60849    60849  2007    306  19484  F9642        Liden             Mikael   \n",
       "63342    63342  2007   2922  25182  F9645       Knaack             Oliver   \n",
       "66246    66246  2007   5922  12248  F2071  Himmelstein                Ari   \n",
       "66879    66879  2007   6571   3335  F9637       Holmes                Ben   \n",
       "69290    69290  2007   9038  32171  F9640     Wrzesien             Szymon   \n",
       "69574    69574  2007   9330  29549  F9643    Bergström            Fredrik   \n",
       "69640    69640  2007   9396   4775  33909  Ángel Ávila        Juan Manuel   \n",
       "69739    69739  2007   9497  24573  34121         Hyde               Kyle   \n",
       "80021    80021  2007  20252  15106  F9646      Eastman             Oliver   \n",
       "80944    80944  2007  21219   1270  F8957     Sørensen  Jytte Vestergaard   \n",
       "86445    86445  2007  29757   8923  34146        Frank         Vandenhole   \n",
       "86616    86616  2007  29964  26057  F9638      Richter              Frank   \n",
       "89023    89023  2007  33174  28164  F9639       Welber               Adam   \n",
       "92208    92208  2007  37911  20212    NaN      Middeke             Jasmin   \n",
       "96128    96128  2008   3667   4703  F1025       Maison             Sylvie   \n",
       "99806    99806  2008   7472  15536  37917       Macedo          Guilherme   \n",
       "102182  102182  2008   9951  23810  12454     Hautamaa               Anne   \n",
       "112288  112288  2008  20854  15085  37913      Michele              Tolin   \n",
       "120566  120566  2008  31461  22967  F5968      Morlang               René   \n",
       "124007  124007  2008  36818   4724  F7099        Rogoz           Grzegorz   \n",
       "124745  124745  2008  38362  16994  24442      Neuhaus             Svenja   \n",
       "126245  126245  2008  42364  33949  F9289      de Pape               Aldo   \n",
       "\n",
       "                          team nationality     yob sex age_class  \\\n",
       "9201                    Sparta         DEN  1964.0   W       M40   \n",
       "10108                      NaN         SUI  1962.0   W       M40   \n",
       "19935                      NaN         GER  1963.0   W       M40   \n",
       "20122            TSV Bollensen         GER  1957.0   W       M45   \n",
       "23362       TriFrogs Gellersen         GER  1965.0   W       M40   \n",
       "26631             Defi Horizon         FRA  1949.0   W       M55   \n",
       "30198                      NaN         ITA  1967.0   M       W35   \n",
       "30200                      NaN         BRA  1965.0   M       W40   \n",
       "34335                      NaN         DEU  1966.0   W       M40   \n",
       "44551                     Nike         NOR  1961.0   W       M45   \n",
       "58571                   Brazil         BRA  1957.0   M       W45   \n",
       "60290                      NaN         GRC  1946.0   W       M60   \n",
       "60849                      NaN         SWE  1970.0   W       M35   \n",
       "63342     Berlin Sluggers e.V.         DEU  1969.0   W       M35   \n",
       "66246             The Team R&F         BRA  1961.0   W       M45   \n",
       "66879                      NaN         GBR  1980.0   W        MH   \n",
       "69290              Auto Buniks         POL  1988.0   W       MJA   \n",
       "69574                      NaN         SWE     NaN   W        MH   \n",
       "69640              Nacho Sport         ESP  1963.0   M       W40   \n",
       "69739                      NaN         GBR  1982.0   M        WH   \n",
       "80021                      NaN         GBR  1975.0   W       M30   \n",
       "80944                      NaN         DNK  1960.0   W       M45   \n",
       "86445                      NaN         FRA  1968.0   M       W35   \n",
       "86616   Wilde Wölfe Holzweißig         DEU  1956.0   W       M50   \n",
       "89023                      NaN         DEU  1970.0   W       M35   \n",
       "92208                      NaN         DEU  1988.0   M       WJA   \n",
       "96128                      NaN         FRA  1964.0   W       M40   \n",
       "99806                      NaN         BRA  1980.0   M        WH   \n",
       "102182                 Himanka         FIN  1959.0   M       W45   \n",
       "112288                     NaN         ITA  1969.0   M       W35   \n",
       "120566      BSG Deutscher Ring         DEU  1969.0   W       M35   \n",
       "124007                     NaN         DEU  1969.0   W       M35   \n",
       "124745                     NaN         DEU  1976.0   M       W35   \n",
       "126245                     NaN         NLD  1979.0   W        MH   \n",
       "\n",
       "        age_class_place net_time clock_time   age  \n",
       "9201                353 03:06:49   03:07:37  41.0  \n",
       "10108              3067 04:04:49   04:08:49  43.0  \n",
       "19935              2410 03:54:04   03:56:47  42.0  \n",
       "20122              1085 03:41:07   03:44:33  48.0  \n",
       "23362              2017 03:47:41   03:49:22  40.0  \n",
       "26631               840 04:33:09   04:37:12  56.0  \n",
       "30198               102 03:38:31   03:40:10  38.0  \n",
       "30200               677 04:26:27   04:30:40  40.0  \n",
       "34335               495 03:16:09   03:16:17  40.0  \n",
       "44551                 3 02:38:57   02:39:04  45.0  \n",
       "58571               460 04:31:24   04:33:18  49.0  \n",
       "60290                 5 03:11:03   03:11:27  60.0  \n",
       "60849              3192 04:13:29   04:14:52  37.0  \n",
       "63342              3830 04:35:38   04:36:56  38.0  \n",
       "66246              1914 03:52:12   03:55:13  46.0  \n",
       "66879               357 03:18:26   03:19:08  27.0  \n",
       "69290               119 05:54:43   05:56:55  19.0  \n",
       "69574              2558 05:03:28   05:04:51   NaN  \n",
       "69640                58 03:26:09   03:28:44  44.0  \n",
       "69739               549 04:32:42   04:33:03  25.0  \n",
       "80021              1733 03:58:57   04:02:33  32.0  \n",
       "80944               159 03:01:18   03:02:26  47.0  \n",
       "86445               129 03:41:50   03:42:01  39.0  \n",
       "86616              2290 04:40:06   04:42:31  51.0  \n",
       "89023              4116 04:52:41   04:54:15  37.0  \n",
       "92208                10 04:15:58   04:17:41  19.0  \n",
       "96128              1082 03:22:29   03:22:55  44.0  \n",
       "99806               258 03:56:58   04:01:31  28.0  \n",
       "102182              589 04:22:10   04:24:49  49.0  \n",
       "112288              283 03:55:55   03:58:28  39.0  \n",
       "120566             3402 04:19:17   04:21:33  39.0  \n",
       "124007              981 03:22:37   03:24:11  39.0  \n",
       "124745             2725 03:59:50   04:14:42  32.0  \n",
       "126245             2859 05:20:02   05:24:52  29.0  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inconsistent_sex"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Judging by the names, the real sex is usually in the age class."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>idx</th>\n",
       "      <th>year</th>\n",
       "      <th>id</th>\n",
       "      <th>place</th>\n",
       "      <th>bib</th>\n",
       "      <th>surname</th>\n",
       "      <th>forename</th>\n",
       "      <th>team</th>\n",
       "      <th>nationality</th>\n",
       "      <th>yob</th>\n",
       "      <th>sex</th>\n",
       "      <th>age_class</th>\n",
       "      <th>age_class_place</th>\n",
       "      <th>net_time</th>\n",
       "      <th>clock_time</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9201</th>\n",
       "      <td>9201</td>\n",
       "      <td>2005</td>\n",
       "      <td>10341</td>\n",
       "      <td>1532</td>\n",
       "      <td>F3923</td>\n",
       "      <td>Lohmann</td>\n",
       "      <td>Henrik</td>\n",
       "      <td>Sparta</td>\n",
       "      <td>DEN</td>\n",
       "      <td>1964.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M40</td>\n",
       "      <td>353</td>\n",
       "      <td>03:06:49</td>\n",
       "      <td>03:07:37</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10108</th>\n",
       "      <td>10108</td>\n",
       "      <td>2005</td>\n",
       "      <td>11280</td>\n",
       "      <td>14344</td>\n",
       "      <td>F5717</td>\n",
       "      <td>Sanchez</td>\n",
       "      <td>Raul</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SUI</td>\n",
       "      <td>1962.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M40</td>\n",
       "      <td>3067</td>\n",
       "      <td>04:04:49</td>\n",
       "      <td>04:08:49</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19935</th>\n",
       "      <td>19935</td>\n",
       "      <td>2005</td>\n",
       "      <td>23704</td>\n",
       "      <td>10930</td>\n",
       "      <td>F4420</td>\n",
       "      <td>Kachlik</td>\n",
       "      <td>Stephan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GER</td>\n",
       "      <td>1963.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M40</td>\n",
       "      <td>2410</td>\n",
       "      <td>03:54:04</td>\n",
       "      <td>03:56:47</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20122</th>\n",
       "      <td>20122</td>\n",
       "      <td>2005</td>\n",
       "      <td>23897</td>\n",
       "      <td>7434</td>\n",
       "      <td>F5646</td>\n",
       "      <td>Kirchhoff</td>\n",
       "      <td>Jochen</td>\n",
       "      <td>TSV Bollensen</td>\n",
       "      <td>GER</td>\n",
       "      <td>1957.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M45</td>\n",
       "      <td>1085</td>\n",
       "      <td>03:41:07</td>\n",
       "      <td>03:44:33</td>\n",
       "      <td>48.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23362</th>\n",
       "      <td>23362</td>\n",
       "      <td>2005</td>\n",
       "      <td>28829</td>\n",
       "      <td>9129</td>\n",
       "      <td>F6506</td>\n",
       "      <td>Pape</td>\n",
       "      <td>Gerald</td>\n",
       "      <td>TriFrogs Gellersen</td>\n",
       "      <td>GER</td>\n",
       "      <td>1965.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M40</td>\n",
       "      <td>2017</td>\n",
       "      <td>03:47:41</td>\n",
       "      <td>03:49:22</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26631</th>\n",
       "      <td>26631</td>\n",
       "      <td>2005</td>\n",
       "      <td>33336</td>\n",
       "      <td>21772</td>\n",
       "      <td>F8928</td>\n",
       "      <td>Tricoche</td>\n",
       "      <td>Elisabeth</td>\n",
       "      <td>Defi Horizon</td>\n",
       "      <td>FRA</td>\n",
       "      <td>1949.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M55</td>\n",
       "      <td>840</td>\n",
       "      <td>04:33:09</td>\n",
       "      <td>04:37:12</td>\n",
       "      <td>56.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30198</th>\n",
       "      <td>30198</td>\n",
       "      <td>2005</td>\n",
       "      <td>37786</td>\n",
       "      <td>6827</td>\n",
       "      <td>32681</td>\n",
       "      <td>Filippo</td>\n",
       "      <td>Proietti</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ITA</td>\n",
       "      <td>1967.0</td>\n",
       "      <td>W</td>\n",
       "      <td>W35</td>\n",
       "      <td>102</td>\n",
       "      <td>03:38:31</td>\n",
       "      <td>03:40:10</td>\n",
       "      <td>38.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30200</th>\n",
       "      <td>30200</td>\n",
       "      <td>2005</td>\n",
       "      <td>37789</td>\n",
       "      <td>20204</td>\n",
       "      <td>32699</td>\n",
       "      <td>Demetrio</td>\n",
       "      <td>Alberto</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BRA</td>\n",
       "      <td>1965.0</td>\n",
       "      <td>W</td>\n",
       "      <td>W40</td>\n",
       "      <td>677</td>\n",
       "      <td>04:26:27</td>\n",
       "      <td>04:30:40</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34335</th>\n",
       "      <td>34335</td>\n",
       "      <td>2006</td>\n",
       "      <td>4614</td>\n",
       "      <td>2110</td>\n",
       "      <td>F3676</td>\n",
       "      <td>Aeby</td>\n",
       "      <td>Pascal Laurent</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1966.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M40</td>\n",
       "      <td>495</td>\n",
       "      <td>03:16:09</td>\n",
       "      <td>03:16:17</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44551</th>\n",
       "      <td>44551</td>\n",
       "      <td>2006</td>\n",
       "      <td>16028</td>\n",
       "      <td>106</td>\n",
       "      <td>F7055</td>\n",
       "      <td>Nordgaard</td>\n",
       "      <td>Haavard</td>\n",
       "      <td>Nike</td>\n",
       "      <td>NOR</td>\n",
       "      <td>1961.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M45</td>\n",
       "      <td>3</td>\n",
       "      <td>02:38:57</td>\n",
       "      <td>02:39:04</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58571</th>\n",
       "      <td>58571</td>\n",
       "      <td>2006</td>\n",
       "      <td>41938</td>\n",
       "      <td>19813</td>\n",
       "      <td>32282</td>\n",
       "      <td>Filho</td>\n",
       "      <td>Lourival</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>BRA</td>\n",
       "      <td>1957.0</td>\n",
       "      <td>W</td>\n",
       "      <td>W45</td>\n",
       "      <td>460</td>\n",
       "      <td>04:31:24</td>\n",
       "      <td>04:33:18</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60290</th>\n",
       "      <td>60290</td>\n",
       "      <td>2006</td>\n",
       "      <td>43722</td>\n",
       "      <td>1571</td>\n",
       "      <td>F7523</td>\n",
       "      <td>Basdekis</td>\n",
       "      <td>Pavlos</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GRC</td>\n",
       "      <td>1946.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M60</td>\n",
       "      <td>5</td>\n",
       "      <td>03:11:03</td>\n",
       "      <td>03:11:27</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60849</th>\n",
       "      <td>60849</td>\n",
       "      <td>2007</td>\n",
       "      <td>306</td>\n",
       "      <td>19484</td>\n",
       "      <td>F9642</td>\n",
       "      <td>Liden</td>\n",
       "      <td>Mikael</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SWE</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M35</td>\n",
       "      <td>3192</td>\n",
       "      <td>04:13:29</td>\n",
       "      <td>04:14:52</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63342</th>\n",
       "      <td>63342</td>\n",
       "      <td>2007</td>\n",
       "      <td>2922</td>\n",
       "      <td>25182</td>\n",
       "      <td>F9645</td>\n",
       "      <td>Knaack</td>\n",
       "      <td>Oliver</td>\n",
       "      <td>Berlin Sluggers e.V.</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1969.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M35</td>\n",
       "      <td>3830</td>\n",
       "      <td>04:35:38</td>\n",
       "      <td>04:36:56</td>\n",
       "      <td>38.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66246</th>\n",
       "      <td>66246</td>\n",
       "      <td>2007</td>\n",
       "      <td>5922</td>\n",
       "      <td>12248</td>\n",
       "      <td>F2071</td>\n",
       "      <td>Himmelstein</td>\n",
       "      <td>Ari</td>\n",
       "      <td>The Team R&amp;F</td>\n",
       "      <td>BRA</td>\n",
       "      <td>1961.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M45</td>\n",
       "      <td>1914</td>\n",
       "      <td>03:52:12</td>\n",
       "      <td>03:55:13</td>\n",
       "      <td>46.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66879</th>\n",
       "      <td>66879</td>\n",
       "      <td>2007</td>\n",
       "      <td>6571</td>\n",
       "      <td>3335</td>\n",
       "      <td>F9637</td>\n",
       "      <td>Holmes</td>\n",
       "      <td>Ben</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GBR</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>M</td>\n",
       "      <td>MH</td>\n",
       "      <td>357</td>\n",
       "      <td>03:18:26</td>\n",
       "      <td>03:19:08</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69290</th>\n",
       "      <td>69290</td>\n",
       "      <td>2007</td>\n",
       "      <td>9038</td>\n",
       "      <td>32171</td>\n",
       "      <td>F9640</td>\n",
       "      <td>Wrzesien</td>\n",
       "      <td>Szymon</td>\n",
       "      <td>Auto Buniks</td>\n",
       "      <td>POL</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>M</td>\n",
       "      <td>MJA</td>\n",
       "      <td>119</td>\n",
       "      <td>05:54:43</td>\n",
       "      <td>05:56:55</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69574</th>\n",
       "      <td>69574</td>\n",
       "      <td>2007</td>\n",
       "      <td>9330</td>\n",
       "      <td>29549</td>\n",
       "      <td>F9643</td>\n",
       "      <td>Bergström</td>\n",
       "      <td>Fredrik</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SWE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>MH</td>\n",
       "      <td>2558</td>\n",
       "      <td>05:03:28</td>\n",
       "      <td>05:04:51</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69640</th>\n",
       "      <td>69640</td>\n",
       "      <td>2007</td>\n",
       "      <td>9396</td>\n",
       "      <td>4775</td>\n",
       "      <td>33909</td>\n",
       "      <td>Ángel Ávila</td>\n",
       "      <td>Juan Manuel</td>\n",
       "      <td>Nacho Sport</td>\n",
       "      <td>ESP</td>\n",
       "      <td>1963.0</td>\n",
       "      <td>W</td>\n",
       "      <td>W40</td>\n",
       "      <td>58</td>\n",
       "      <td>03:26:09</td>\n",
       "      <td>03:28:44</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69739</th>\n",
       "      <td>69739</td>\n",
       "      <td>2007</td>\n",
       "      <td>9497</td>\n",
       "      <td>24573</td>\n",
       "      <td>34121</td>\n",
       "      <td>Hyde</td>\n",
       "      <td>Kyle</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GBR</td>\n",
       "      <td>1982.0</td>\n",
       "      <td>W</td>\n",
       "      <td>WH</td>\n",
       "      <td>549</td>\n",
       "      <td>04:32:42</td>\n",
       "      <td>04:33:03</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80021</th>\n",
       "      <td>80021</td>\n",
       "      <td>2007</td>\n",
       "      <td>20252</td>\n",
       "      <td>15106</td>\n",
       "      <td>F9646</td>\n",
       "      <td>Eastman</td>\n",
       "      <td>Oliver</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GBR</td>\n",
       "      <td>1975.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M30</td>\n",
       "      <td>1733</td>\n",
       "      <td>03:58:57</td>\n",
       "      <td>04:02:33</td>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80944</th>\n",
       "      <td>80944</td>\n",
       "      <td>2007</td>\n",
       "      <td>21219</td>\n",
       "      <td>1270</td>\n",
       "      <td>F8957</td>\n",
       "      <td>Sørensen</td>\n",
       "      <td>Jytte Vestergaard</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DNK</td>\n",
       "      <td>1960.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M45</td>\n",
       "      <td>159</td>\n",
       "      <td>03:01:18</td>\n",
       "      <td>03:02:26</td>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86445</th>\n",
       "      <td>86445</td>\n",
       "      <td>2007</td>\n",
       "      <td>29757</td>\n",
       "      <td>8923</td>\n",
       "      <td>34146</td>\n",
       "      <td>Frank</td>\n",
       "      <td>Vandenhole</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FRA</td>\n",
       "      <td>1968.0</td>\n",
       "      <td>W</td>\n",
       "      <td>W35</td>\n",
       "      <td>129</td>\n",
       "      <td>03:41:50</td>\n",
       "      <td>03:42:01</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86616</th>\n",
       "      <td>86616</td>\n",
       "      <td>2007</td>\n",
       "      <td>29964</td>\n",
       "      <td>26057</td>\n",
       "      <td>F9638</td>\n",
       "      <td>Richter</td>\n",
       "      <td>Frank</td>\n",
       "      <td>Wilde Wölfe Holzweißig</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1956.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M50</td>\n",
       "      <td>2290</td>\n",
       "      <td>04:40:06</td>\n",
       "      <td>04:42:31</td>\n",
       "      <td>51.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89023</th>\n",
       "      <td>89023</td>\n",
       "      <td>2007</td>\n",
       "      <td>33174</td>\n",
       "      <td>28164</td>\n",
       "      <td>F9639</td>\n",
       "      <td>Welber</td>\n",
       "      <td>Adam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M35</td>\n",
       "      <td>4116</td>\n",
       "      <td>04:52:41</td>\n",
       "      <td>04:54:15</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92208</th>\n",
       "      <td>92208</td>\n",
       "      <td>2007</td>\n",
       "      <td>37911</td>\n",
       "      <td>20212</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Middeke</td>\n",
       "      <td>Jasmin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>W</td>\n",
       "      <td>WJA</td>\n",
       "      <td>10</td>\n",
       "      <td>04:15:58</td>\n",
       "      <td>04:17:41</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96128</th>\n",
       "      <td>96128</td>\n",
       "      <td>2008</td>\n",
       "      <td>3667</td>\n",
       "      <td>4703</td>\n",
       "      <td>F1025</td>\n",
       "      <td>Maison</td>\n",
       "      <td>Sylvie</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FRA</td>\n",
       "      <td>1964.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M40</td>\n",
       "      <td>1082</td>\n",
       "      <td>03:22:29</td>\n",
       "      <td>03:22:55</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99806</th>\n",
       "      <td>99806</td>\n",
       "      <td>2008</td>\n",
       "      <td>7472</td>\n",
       "      <td>15536</td>\n",
       "      <td>37917</td>\n",
       "      <td>Macedo</td>\n",
       "      <td>Guilherme</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BRA</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>W</td>\n",
       "      <td>WH</td>\n",
       "      <td>258</td>\n",
       "      <td>03:56:58</td>\n",
       "      <td>04:01:31</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102182</th>\n",
       "      <td>102182</td>\n",
       "      <td>2008</td>\n",
       "      <td>9951</td>\n",
       "      <td>23810</td>\n",
       "      <td>12454</td>\n",
       "      <td>Hautamaa</td>\n",
       "      <td>Anne</td>\n",
       "      <td>Himanka</td>\n",
       "      <td>FIN</td>\n",
       "      <td>1959.0</td>\n",
       "      <td>W</td>\n",
       "      <td>W45</td>\n",
       "      <td>589</td>\n",
       "      <td>04:22:10</td>\n",
       "      <td>04:24:49</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112288</th>\n",
       "      <td>112288</td>\n",
       "      <td>2008</td>\n",
       "      <td>20854</td>\n",
       "      <td>15085</td>\n",
       "      <td>37913</td>\n",
       "      <td>Michele</td>\n",
       "      <td>Tolin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ITA</td>\n",
       "      <td>1969.0</td>\n",
       "      <td>W</td>\n",
       "      <td>W35</td>\n",
       "      <td>283</td>\n",
       "      <td>03:55:55</td>\n",
       "      <td>03:58:28</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120566</th>\n",
       "      <td>120566</td>\n",
       "      <td>2008</td>\n",
       "      <td>31461</td>\n",
       "      <td>22967</td>\n",
       "      <td>F5968</td>\n",
       "      <td>Morlang</td>\n",
       "      <td>René</td>\n",
       "      <td>BSG Deutscher Ring</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1969.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M35</td>\n",
       "      <td>3402</td>\n",
       "      <td>04:19:17</td>\n",
       "      <td>04:21:33</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124007</th>\n",
       "      <td>124007</td>\n",
       "      <td>2008</td>\n",
       "      <td>36818</td>\n",
       "      <td>4724</td>\n",
       "      <td>F7099</td>\n",
       "      <td>Rogoz</td>\n",
       "      <td>Grzegorz</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1969.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M35</td>\n",
       "      <td>981</td>\n",
       "      <td>03:22:37</td>\n",
       "      <td>03:24:11</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124745</th>\n",
       "      <td>124745</td>\n",
       "      <td>2008</td>\n",
       "      <td>38362</td>\n",
       "      <td>16994</td>\n",
       "      <td>24442</td>\n",
       "      <td>Neuhaus</td>\n",
       "      <td>Svenja</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1976.0</td>\n",
       "      <td>W</td>\n",
       "      <td>W35</td>\n",
       "      <td>2725</td>\n",
       "      <td>03:59:50</td>\n",
       "      <td>04:14:42</td>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126245</th>\n",
       "      <td>126245</td>\n",
       "      <td>2008</td>\n",
       "      <td>42364</td>\n",
       "      <td>33949</td>\n",
       "      <td>F9289</td>\n",
       "      <td>de Pape</td>\n",
       "      <td>Aldo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NLD</td>\n",
       "      <td>1979.0</td>\n",
       "      <td>M</td>\n",
       "      <td>MH</td>\n",
       "      <td>2859</td>\n",
       "      <td>05:20:02</td>\n",
       "      <td>05:24:52</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           idx  year     id  place    bib      surname           forename  \\\n",
       "9201      9201  2005  10341   1532  F3923      Lohmann             Henrik   \n",
       "10108    10108  2005  11280  14344  F5717      Sanchez               Raul   \n",
       "19935    19935  2005  23704  10930  F4420      Kachlik            Stephan   \n",
       "20122    20122  2005  23897   7434  F5646    Kirchhoff             Jochen   \n",
       "23362    23362  2005  28829   9129  F6506         Pape             Gerald   \n",
       "26631    26631  2005  33336  21772  F8928     Tricoche          Elisabeth   \n",
       "30198    30198  2005  37786   6827  32681      Filippo           Proietti   \n",
       "30200    30200  2005  37789  20204  32699     Demetrio            Alberto   \n",
       "34335    34335  2006   4614   2110  F3676         Aeby     Pascal Laurent   \n",
       "44551    44551  2006  16028    106  F7055    Nordgaard            Haavard   \n",
       "58571    58571  2006  41938  19813  32282        Filho           Lourival   \n",
       "60290    60290  2006  43722   1571  F7523     Basdekis             Pavlos   \n",
       "60849    60849  2007    306  19484  F9642        Liden             Mikael   \n",
       "63342    63342  2007   2922  25182  F9645       Knaack             Oliver   \n",
       "66246    66246  2007   5922  12248  F2071  Himmelstein                Ari   \n",
       "66879    66879  2007   6571   3335  F9637       Holmes                Ben   \n",
       "69290    69290  2007   9038  32171  F9640     Wrzesien             Szymon   \n",
       "69574    69574  2007   9330  29549  F9643    Bergström            Fredrik   \n",
       "69640    69640  2007   9396   4775  33909  Ángel Ávila        Juan Manuel   \n",
       "69739    69739  2007   9497  24573  34121         Hyde               Kyle   \n",
       "80021    80021  2007  20252  15106  F9646      Eastman             Oliver   \n",
       "80944    80944  2007  21219   1270  F8957     Sørensen  Jytte Vestergaard   \n",
       "86445    86445  2007  29757   8923  34146        Frank         Vandenhole   \n",
       "86616    86616  2007  29964  26057  F9638      Richter              Frank   \n",
       "89023    89023  2007  33174  28164  F9639       Welber               Adam   \n",
       "92208    92208  2007  37911  20212    NaN      Middeke             Jasmin   \n",
       "96128    96128  2008   3667   4703  F1025       Maison             Sylvie   \n",
       "99806    99806  2008   7472  15536  37917       Macedo          Guilherme   \n",
       "102182  102182  2008   9951  23810  12454     Hautamaa               Anne   \n",
       "112288  112288  2008  20854  15085  37913      Michele              Tolin   \n",
       "120566  120566  2008  31461  22967  F5968      Morlang               René   \n",
       "124007  124007  2008  36818   4724  F7099        Rogoz           Grzegorz   \n",
       "124745  124745  2008  38362  16994  24442      Neuhaus             Svenja   \n",
       "126245  126245  2008  42364  33949  F9289      de Pape               Aldo   \n",
       "\n",
       "                          team nationality     yob sex age_class  \\\n",
       "9201                    Sparta         DEN  1964.0   M       M40   \n",
       "10108                      NaN         SUI  1962.0   M       M40   \n",
       "19935                      NaN         GER  1963.0   M       M40   \n",
       "20122            TSV Bollensen         GER  1957.0   M       M45   \n",
       "23362       TriFrogs Gellersen         GER  1965.0   M       M40   \n",
       "26631             Defi Horizon         FRA  1949.0   M       M55   \n",
       "30198                      NaN         ITA  1967.0   W       W35   \n",
       "30200                      NaN         BRA  1965.0   W       W40   \n",
       "34335                      NaN         DEU  1966.0   M       M40   \n",
       "44551                     Nike         NOR  1961.0   M       M45   \n",
       "58571                   Brazil         BRA  1957.0   W       W45   \n",
       "60290                      NaN         GRC  1946.0   M       M60   \n",
       "60849                      NaN         SWE  1970.0   M       M35   \n",
       "63342     Berlin Sluggers e.V.         DEU  1969.0   M       M35   \n",
       "66246             The Team R&F         BRA  1961.0   M       M45   \n",
       "66879                      NaN         GBR  1980.0   M        MH   \n",
       "69290              Auto Buniks         POL  1988.0   M       MJA   \n",
       "69574                      NaN         SWE     NaN   M        MH   \n",
       "69640              Nacho Sport         ESP  1963.0   W       W40   \n",
       "69739                      NaN         GBR  1982.0   W        WH   \n",
       "80021                      NaN         GBR  1975.0   M       M30   \n",
       "80944                      NaN         DNK  1960.0   M       M45   \n",
       "86445                      NaN         FRA  1968.0   W       W35   \n",
       "86616   Wilde Wölfe Holzweißig         DEU  1956.0   M       M50   \n",
       "89023                      NaN         DEU  1970.0   M       M35   \n",
       "92208                      NaN         DEU  1988.0   W       WJA   \n",
       "96128                      NaN         FRA  1964.0   M       M40   \n",
       "99806                      NaN         BRA  1980.0   W        WH   \n",
       "102182                 Himanka         FIN  1959.0   W       W45   \n",
       "112288                     NaN         ITA  1969.0   W       W35   \n",
       "120566      BSG Deutscher Ring         DEU  1969.0   M       M35   \n",
       "124007                     NaN         DEU  1969.0   M       M35   \n",
       "124745                     NaN         DEU  1976.0   W       W35   \n",
       "126245                     NaN         NLD  1979.0   M        MH   \n",
       "\n",
       "        age_class_place net_time clock_time   age  \n",
       "9201                353 03:06:49   03:07:37  41.0  \n",
       "10108              3067 04:04:49   04:08:49  43.0  \n",
       "19935              2410 03:54:04   03:56:47  42.0  \n",
       "20122              1085 03:41:07   03:44:33  48.0  \n",
       "23362              2017 03:47:41   03:49:22  40.0  \n",
       "26631               840 04:33:09   04:37:12  56.0  \n",
       "30198               102 03:38:31   03:40:10  38.0  \n",
       "30200               677 04:26:27   04:30:40  40.0  \n",
       "34335               495 03:16:09   03:16:17  40.0  \n",
       "44551                 3 02:38:57   02:39:04  45.0  \n",
       "58571               460 04:31:24   04:33:18  49.0  \n",
       "60290                 5 03:11:03   03:11:27  60.0  \n",
       "60849              3192 04:13:29   04:14:52  37.0  \n",
       "63342              3830 04:35:38   04:36:56  38.0  \n",
       "66246              1914 03:52:12   03:55:13  46.0  \n",
       "66879               357 03:18:26   03:19:08  27.0  \n",
       "69290               119 05:54:43   05:56:55  19.0  \n",
       "69574              2558 05:03:28   05:04:51   NaN  \n",
       "69640                58 03:26:09   03:28:44  44.0  \n",
       "69739               549 04:32:42   04:33:03  25.0  \n",
       "80021              1733 03:58:57   04:02:33  32.0  \n",
       "80944               159 03:01:18   03:02:26  47.0  \n",
       "86445               129 03:41:50   03:42:01  39.0  \n",
       "86616              2290 04:40:06   04:42:31  51.0  \n",
       "89023              4116 04:52:41   04:54:15  37.0  \n",
       "92208                10 04:15:58   04:17:41  19.0  \n",
       "96128              1082 03:22:29   03:22:55  44.0  \n",
       "99806               258 03:56:58   04:01:31  28.0  \n",
       "102182              589 04:22:10   04:24:49  49.0  \n",
       "112288              283 03:55:55   03:58:28  39.0  \n",
       "120566             3402 04:19:17   04:21:33  39.0  \n",
       "124007              981 03:22:37   03:24:11  39.0  \n",
       "124745             2725 03:59:50   04:14:42  32.0  \n",
       "126245             2859 05:20:02   05:24:52  29.0  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inconsistent_sex.sex = inconsistent_sex.age_class.str.get(0)\n",
    "inconsistent_sex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>idx</th>\n",
       "      <th>year</th>\n",
       "      <th>id</th>\n",
       "      <th>place</th>\n",
       "      <th>bib</th>\n",
       "      <th>surname</th>\n",
       "      <th>forename</th>\n",
       "      <th>team</th>\n",
       "      <th>nationality</th>\n",
       "      <th>yob</th>\n",
       "      <th>sex</th>\n",
       "      <th>age_class</th>\n",
       "      <th>age_class_place</th>\n",
       "      <th>net_time</th>\n",
       "      <th>clock_time</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9201</th>\n",
       "      <td>9201</td>\n",
       "      <td>2005</td>\n",
       "      <td>10341</td>\n",
       "      <td>1532</td>\n",
       "      <td>F3923</td>\n",
       "      <td>Lohmann</td>\n",
       "      <td>Henrik</td>\n",
       "      <td>Sparta</td>\n",
       "      <td>DEN</td>\n",
       "      <td>1964.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M40</td>\n",
       "      <td>353</td>\n",
       "      <td>03:06:49</td>\n",
       "      <td>03:07:37</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10108</th>\n",
       "      <td>10108</td>\n",
       "      <td>2005</td>\n",
       "      <td>11280</td>\n",
       "      <td>14344</td>\n",
       "      <td>F5717</td>\n",
       "      <td>Sanchez</td>\n",
       "      <td>Raul</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SUI</td>\n",
       "      <td>1962.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M40</td>\n",
       "      <td>3067</td>\n",
       "      <td>04:04:49</td>\n",
       "      <td>04:08:49</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19935</th>\n",
       "      <td>19935</td>\n",
       "      <td>2005</td>\n",
       "      <td>23704</td>\n",
       "      <td>10930</td>\n",
       "      <td>F4420</td>\n",
       "      <td>Kachlik</td>\n",
       "      <td>Stephan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GER</td>\n",
       "      <td>1963.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M40</td>\n",
       "      <td>2410</td>\n",
       "      <td>03:54:04</td>\n",
       "      <td>03:56:47</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20122</th>\n",
       "      <td>20122</td>\n",
       "      <td>2005</td>\n",
       "      <td>23897</td>\n",
       "      <td>7434</td>\n",
       "      <td>F5646</td>\n",
       "      <td>Kirchhoff</td>\n",
       "      <td>Jochen</td>\n",
       "      <td>TSV Bollensen</td>\n",
       "      <td>GER</td>\n",
       "      <td>1957.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M45</td>\n",
       "      <td>1085</td>\n",
       "      <td>03:41:07</td>\n",
       "      <td>03:44:33</td>\n",
       "      <td>48.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23362</th>\n",
       "      <td>23362</td>\n",
       "      <td>2005</td>\n",
       "      <td>28829</td>\n",
       "      <td>9129</td>\n",
       "      <td>F6506</td>\n",
       "      <td>Pape</td>\n",
       "      <td>Gerald</td>\n",
       "      <td>TriFrogs Gellersen</td>\n",
       "      <td>GER</td>\n",
       "      <td>1965.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M40</td>\n",
       "      <td>2017</td>\n",
       "      <td>03:47:41</td>\n",
       "      <td>03:49:22</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26631</th>\n",
       "      <td>26631</td>\n",
       "      <td>2005</td>\n",
       "      <td>33336</td>\n",
       "      <td>21772</td>\n",
       "      <td>F8928</td>\n",
       "      <td>Tricoche</td>\n",
       "      <td>Elisabeth</td>\n",
       "      <td>Defi Horizon</td>\n",
       "      <td>FRA</td>\n",
       "      <td>1949.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M55</td>\n",
       "      <td>840</td>\n",
       "      <td>04:33:09</td>\n",
       "      <td>04:37:12</td>\n",
       "      <td>56.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30198</th>\n",
       "      <td>30198</td>\n",
       "      <td>2005</td>\n",
       "      <td>37786</td>\n",
       "      <td>6827</td>\n",
       "      <td>32681</td>\n",
       "      <td>Filippo</td>\n",
       "      <td>Proietti</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ITA</td>\n",
       "      <td>1967.0</td>\n",
       "      <td>M</td>\n",
       "      <td>W35</td>\n",
       "      <td>102</td>\n",
       "      <td>03:38:31</td>\n",
       "      <td>03:40:10</td>\n",
       "      <td>38.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30200</th>\n",
       "      <td>30200</td>\n",
       "      <td>2005</td>\n",
       "      <td>37789</td>\n",
       "      <td>20204</td>\n",
       "      <td>32699</td>\n",
       "      <td>Demetrio</td>\n",
       "      <td>Alberto</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BRA</td>\n",
       "      <td>1965.0</td>\n",
       "      <td>M</td>\n",
       "      <td>W40</td>\n",
       "      <td>677</td>\n",
       "      <td>04:26:27</td>\n",
       "      <td>04:30:40</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34335</th>\n",
       "      <td>34335</td>\n",
       "      <td>2006</td>\n",
       "      <td>4614</td>\n",
       "      <td>2110</td>\n",
       "      <td>F3676</td>\n",
       "      <td>Aeby</td>\n",
       "      <td>Pascal Laurent</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1966.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M40</td>\n",
       "      <td>495</td>\n",
       "      <td>03:16:09</td>\n",
       "      <td>03:16:17</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44551</th>\n",
       "      <td>44551</td>\n",
       "      <td>2006</td>\n",
       "      <td>16028</td>\n",
       "      <td>106</td>\n",
       "      <td>F7055</td>\n",
       "      <td>Nordgaard</td>\n",
       "      <td>Haavard</td>\n",
       "      <td>Nike</td>\n",
       "      <td>NOR</td>\n",
       "      <td>1961.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M45</td>\n",
       "      <td>3</td>\n",
       "      <td>02:38:57</td>\n",
       "      <td>02:39:04</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58571</th>\n",
       "      <td>58571</td>\n",
       "      <td>2006</td>\n",
       "      <td>41938</td>\n",
       "      <td>19813</td>\n",
       "      <td>32282</td>\n",
       "      <td>Filho</td>\n",
       "      <td>Lourival</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>BRA</td>\n",
       "      <td>1957.0</td>\n",
       "      <td>M</td>\n",
       "      <td>W45</td>\n",
       "      <td>460</td>\n",
       "      <td>04:31:24</td>\n",
       "      <td>04:33:18</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60290</th>\n",
       "      <td>60290</td>\n",
       "      <td>2006</td>\n",
       "      <td>43722</td>\n",
       "      <td>1571</td>\n",
       "      <td>F7523</td>\n",
       "      <td>Basdekis</td>\n",
       "      <td>Pavlos</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GRC</td>\n",
       "      <td>1946.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M60</td>\n",
       "      <td>5</td>\n",
       "      <td>03:11:03</td>\n",
       "      <td>03:11:27</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60849</th>\n",
       "      <td>60849</td>\n",
       "      <td>2007</td>\n",
       "      <td>306</td>\n",
       "      <td>19484</td>\n",
       "      <td>F9642</td>\n",
       "      <td>Liden</td>\n",
       "      <td>Mikael</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SWE</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M35</td>\n",
       "      <td>3192</td>\n",
       "      <td>04:13:29</td>\n",
       "      <td>04:14:52</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63342</th>\n",
       "      <td>63342</td>\n",
       "      <td>2007</td>\n",
       "      <td>2922</td>\n",
       "      <td>25182</td>\n",
       "      <td>F9645</td>\n",
       "      <td>Knaack</td>\n",
       "      <td>Oliver</td>\n",
       "      <td>Berlin Sluggers e.V.</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1969.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M35</td>\n",
       "      <td>3830</td>\n",
       "      <td>04:35:38</td>\n",
       "      <td>04:36:56</td>\n",
       "      <td>38.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66246</th>\n",
       "      <td>66246</td>\n",
       "      <td>2007</td>\n",
       "      <td>5922</td>\n",
       "      <td>12248</td>\n",
       "      <td>F2071</td>\n",
       "      <td>Himmelstein</td>\n",
       "      <td>Ari</td>\n",
       "      <td>The Team R&amp;F</td>\n",
       "      <td>BRA</td>\n",
       "      <td>1961.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M45</td>\n",
       "      <td>1914</td>\n",
       "      <td>03:52:12</td>\n",
       "      <td>03:55:13</td>\n",
       "      <td>46.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66879</th>\n",
       "      <td>66879</td>\n",
       "      <td>2007</td>\n",
       "      <td>6571</td>\n",
       "      <td>3335</td>\n",
       "      <td>F9637</td>\n",
       "      <td>Holmes</td>\n",
       "      <td>Ben</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GBR</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>M</td>\n",
       "      <td>MH</td>\n",
       "      <td>357</td>\n",
       "      <td>03:18:26</td>\n",
       "      <td>03:19:08</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69290</th>\n",
       "      <td>69290</td>\n",
       "      <td>2007</td>\n",
       "      <td>9038</td>\n",
       "      <td>32171</td>\n",
       "      <td>F9640</td>\n",
       "      <td>Wrzesien</td>\n",
       "      <td>Szymon</td>\n",
       "      <td>Auto Buniks</td>\n",
       "      <td>POL</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>M</td>\n",
       "      <td>MJA</td>\n",
       "      <td>119</td>\n",
       "      <td>05:54:43</td>\n",
       "      <td>05:56:55</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69574</th>\n",
       "      <td>69574</td>\n",
       "      <td>2007</td>\n",
       "      <td>9330</td>\n",
       "      <td>29549</td>\n",
       "      <td>F9643</td>\n",
       "      <td>Bergström</td>\n",
       "      <td>Fredrik</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SWE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>MH</td>\n",
       "      <td>2558</td>\n",
       "      <td>05:03:28</td>\n",
       "      <td>05:04:51</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69640</th>\n",
       "      <td>69640</td>\n",
       "      <td>2007</td>\n",
       "      <td>9396</td>\n",
       "      <td>4775</td>\n",
       "      <td>33909</td>\n",
       "      <td>Ángel Ávila</td>\n",
       "      <td>Juan Manuel</td>\n",
       "      <td>Nacho Sport</td>\n",
       "      <td>ESP</td>\n",
       "      <td>1963.0</td>\n",
       "      <td>M</td>\n",
       "      <td>W40</td>\n",
       "      <td>58</td>\n",
       "      <td>03:26:09</td>\n",
       "      <td>03:28:44</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69739</th>\n",
       "      <td>69739</td>\n",
       "      <td>2007</td>\n",
       "      <td>9497</td>\n",
       "      <td>24573</td>\n",
       "      <td>34121</td>\n",
       "      <td>Hyde</td>\n",
       "      <td>Kyle</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GBR</td>\n",
       "      <td>1982.0</td>\n",
       "      <td>W</td>\n",
       "      <td>WH</td>\n",
       "      <td>549</td>\n",
       "      <td>04:32:42</td>\n",
       "      <td>04:33:03</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80021</th>\n",
       "      <td>80021</td>\n",
       "      <td>2007</td>\n",
       "      <td>20252</td>\n",
       "      <td>15106</td>\n",
       "      <td>F9646</td>\n",
       "      <td>Eastman</td>\n",
       "      <td>Oliver</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GBR</td>\n",
       "      <td>1975.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M30</td>\n",
       "      <td>1733</td>\n",
       "      <td>03:58:57</td>\n",
       "      <td>04:02:33</td>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80944</th>\n",
       "      <td>80944</td>\n",
       "      <td>2007</td>\n",
       "      <td>21219</td>\n",
       "      <td>1270</td>\n",
       "      <td>F8957</td>\n",
       "      <td>Sørensen</td>\n",
       "      <td>Jytte Vestergaard</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DNK</td>\n",
       "      <td>1960.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M45</td>\n",
       "      <td>159</td>\n",
       "      <td>03:01:18</td>\n",
       "      <td>03:02:26</td>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86445</th>\n",
       "      <td>86445</td>\n",
       "      <td>2007</td>\n",
       "      <td>29757</td>\n",
       "      <td>8923</td>\n",
       "      <td>34146</td>\n",
       "      <td>Frank</td>\n",
       "      <td>Vandenhole</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FRA</td>\n",
       "      <td>1968.0</td>\n",
       "      <td>M</td>\n",
       "      <td>W35</td>\n",
       "      <td>129</td>\n",
       "      <td>03:41:50</td>\n",
       "      <td>03:42:01</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86616</th>\n",
       "      <td>86616</td>\n",
       "      <td>2007</td>\n",
       "      <td>29964</td>\n",
       "      <td>26057</td>\n",
       "      <td>F9638</td>\n",
       "      <td>Richter</td>\n",
       "      <td>Frank</td>\n",
       "      <td>Wilde Wölfe Holzweißig</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1956.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M50</td>\n",
       "      <td>2290</td>\n",
       "      <td>04:40:06</td>\n",
       "      <td>04:42:31</td>\n",
       "      <td>51.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89023</th>\n",
       "      <td>89023</td>\n",
       "      <td>2007</td>\n",
       "      <td>33174</td>\n",
       "      <td>28164</td>\n",
       "      <td>F9639</td>\n",
       "      <td>Welber</td>\n",
       "      <td>Adam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M35</td>\n",
       "      <td>4116</td>\n",
       "      <td>04:52:41</td>\n",
       "      <td>04:54:15</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92208</th>\n",
       "      <td>92208</td>\n",
       "      <td>2007</td>\n",
       "      <td>37911</td>\n",
       "      <td>20212</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Middeke</td>\n",
       "      <td>Jasmin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>W</td>\n",
       "      <td>WJA</td>\n",
       "      <td>10</td>\n",
       "      <td>04:15:58</td>\n",
       "      <td>04:17:41</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96128</th>\n",
       "      <td>96128</td>\n",
       "      <td>2008</td>\n",
       "      <td>3667</td>\n",
       "      <td>4703</td>\n",
       "      <td>F1025</td>\n",
       "      <td>Maison</td>\n",
       "      <td>Sylvie</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FRA</td>\n",
       "      <td>1964.0</td>\n",
       "      <td>W</td>\n",
       "      <td>M40</td>\n",
       "      <td>1082</td>\n",
       "      <td>03:22:29</td>\n",
       "      <td>03:22:55</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99806</th>\n",
       "      <td>99806</td>\n",
       "      <td>2008</td>\n",
       "      <td>7472</td>\n",
       "      <td>15536</td>\n",
       "      <td>37917</td>\n",
       "      <td>Macedo</td>\n",
       "      <td>Guilherme</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BRA</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>M</td>\n",
       "      <td>WH</td>\n",
       "      <td>258</td>\n",
       "      <td>03:56:58</td>\n",
       "      <td>04:01:31</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102182</th>\n",
       "      <td>102182</td>\n",
       "      <td>2008</td>\n",
       "      <td>9951</td>\n",
       "      <td>23810</td>\n",
       "      <td>12454</td>\n",
       "      <td>Hautamaa</td>\n",
       "      <td>Anne</td>\n",
       "      <td>Himanka</td>\n",
       "      <td>FIN</td>\n",
       "      <td>1959.0</td>\n",
       "      <td>W</td>\n",
       "      <td>W45</td>\n",
       "      <td>589</td>\n",
       "      <td>04:22:10</td>\n",
       "      <td>04:24:49</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112288</th>\n",
       "      <td>112288</td>\n",
       "      <td>2008</td>\n",
       "      <td>20854</td>\n",
       "      <td>15085</td>\n",
       "      <td>37913</td>\n",
       "      <td>Michele</td>\n",
       "      <td>Tolin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ITA</td>\n",
       "      <td>1969.0</td>\n",
       "      <td>W</td>\n",
       "      <td>W35</td>\n",
       "      <td>283</td>\n",
       "      <td>03:55:55</td>\n",
       "      <td>03:58:28</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120566</th>\n",
       "      <td>120566</td>\n",
       "      <td>2008</td>\n",
       "      <td>31461</td>\n",
       "      <td>22967</td>\n",
       "      <td>F5968</td>\n",
       "      <td>Morlang</td>\n",
       "      <td>René</td>\n",
       "      <td>BSG Deutscher Ring</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1969.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M35</td>\n",
       "      <td>3402</td>\n",
       "      <td>04:19:17</td>\n",
       "      <td>04:21:33</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124007</th>\n",
       "      <td>124007</td>\n",
       "      <td>2008</td>\n",
       "      <td>36818</td>\n",
       "      <td>4724</td>\n",
       "      <td>F7099</td>\n",
       "      <td>Rogoz</td>\n",
       "      <td>Grzegorz</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1969.0</td>\n",
       "      <td>M</td>\n",
       "      <td>M35</td>\n",
       "      <td>981</td>\n",
       "      <td>03:22:37</td>\n",
       "      <td>03:24:11</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124745</th>\n",
       "      <td>124745</td>\n",
       "      <td>2008</td>\n",
       "      <td>38362</td>\n",
       "      <td>16994</td>\n",
       "      <td>24442</td>\n",
       "      <td>Neuhaus</td>\n",
       "      <td>Svenja</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1976.0</td>\n",
       "      <td>W</td>\n",
       "      <td>W35</td>\n",
       "      <td>2725</td>\n",
       "      <td>03:59:50</td>\n",
       "      <td>04:14:42</td>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126245</th>\n",
       "      <td>126245</td>\n",
       "      <td>2008</td>\n",
       "      <td>42364</td>\n",
       "      <td>33949</td>\n",
       "      <td>F9289</td>\n",
       "      <td>de Pape</td>\n",
       "      <td>Aldo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NLD</td>\n",
       "      <td>1979.0</td>\n",
       "      <td>M</td>\n",
       "      <td>MH</td>\n",
       "      <td>2859</td>\n",
       "      <td>05:20:02</td>\n",
       "      <td>05:24:52</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           idx  year     id  place    bib      surname           forename  \\\n",
       "9201      9201  2005  10341   1532  F3923      Lohmann             Henrik   \n",
       "10108    10108  2005  11280  14344  F5717      Sanchez               Raul   \n",
       "19935    19935  2005  23704  10930  F4420      Kachlik            Stephan   \n",
       "20122    20122  2005  23897   7434  F5646    Kirchhoff             Jochen   \n",
       "23362    23362  2005  28829   9129  F6506         Pape             Gerald   \n",
       "26631    26631  2005  33336  21772  F8928     Tricoche          Elisabeth   \n",
       "30198    30198  2005  37786   6827  32681      Filippo           Proietti   \n",
       "30200    30200  2005  37789  20204  32699     Demetrio            Alberto   \n",
       "34335    34335  2006   4614   2110  F3676         Aeby     Pascal Laurent   \n",
       "44551    44551  2006  16028    106  F7055    Nordgaard            Haavard   \n",
       "58571    58571  2006  41938  19813  32282        Filho           Lourival   \n",
       "60290    60290  2006  43722   1571  F7523     Basdekis             Pavlos   \n",
       "60849    60849  2007    306  19484  F9642        Liden             Mikael   \n",
       "63342    63342  2007   2922  25182  F9645       Knaack             Oliver   \n",
       "66246    66246  2007   5922  12248  F2071  Himmelstein                Ari   \n",
       "66879    66879  2007   6571   3335  F9637       Holmes                Ben   \n",
       "69290    69290  2007   9038  32171  F9640     Wrzesien             Szymon   \n",
       "69574    69574  2007   9330  29549  F9643    Bergström            Fredrik   \n",
       "69640    69640  2007   9396   4775  33909  Ángel Ávila        Juan Manuel   \n",
       "69739    69739  2007   9497  24573  34121         Hyde               Kyle   \n",
       "80021    80021  2007  20252  15106  F9646      Eastman             Oliver   \n",
       "80944    80944  2007  21219   1270  F8957     Sørensen  Jytte Vestergaard   \n",
       "86445    86445  2007  29757   8923  34146        Frank         Vandenhole   \n",
       "86616    86616  2007  29964  26057  F9638      Richter              Frank   \n",
       "89023    89023  2007  33174  28164  F9639       Welber               Adam   \n",
       "92208    92208  2007  37911  20212    NaN      Middeke             Jasmin   \n",
       "96128    96128  2008   3667   4703  F1025       Maison             Sylvie   \n",
       "99806    99806  2008   7472  15536  37917       Macedo          Guilherme   \n",
       "102182  102182  2008   9951  23810  12454     Hautamaa               Anne   \n",
       "112288  112288  2008  20854  15085  37913      Michele              Tolin   \n",
       "120566  120566  2008  31461  22967  F5968      Morlang               René   \n",
       "124007  124007  2008  36818   4724  F7099        Rogoz           Grzegorz   \n",
       "124745  124745  2008  38362  16994  24442      Neuhaus             Svenja   \n",
       "126245  126245  2008  42364  33949  F9289      de Pape               Aldo   \n",
       "\n",
       "                          team nationality     yob sex age_class  \\\n",
       "9201                    Sparta         DEN  1964.0   M       M40   \n",
       "10108                      NaN         SUI  1962.0   M       M40   \n",
       "19935                      NaN         GER  1963.0   M       M40   \n",
       "20122            TSV Bollensen         GER  1957.0   M       M45   \n",
       "23362       TriFrogs Gellersen         GER  1965.0   M       M40   \n",
       "26631             Defi Horizon         FRA  1949.0   M       M55   \n",
       "30198                      NaN         ITA  1967.0   M       W35   \n",
       "30200                      NaN         BRA  1965.0   M       W40   \n",
       "34335                      NaN         DEU  1966.0   M       M40   \n",
       "44551                     Nike         NOR  1961.0   M       M45   \n",
       "58571                   Brazil         BRA  1957.0   M       W45   \n",
       "60290                      NaN         GRC  1946.0   M       M60   \n",
       "60849                      NaN         SWE  1970.0   M       M35   \n",
       "63342     Berlin Sluggers e.V.         DEU  1969.0   M       M35   \n",
       "66246             The Team R&F         BRA  1961.0   M       M45   \n",
       "66879                      NaN         GBR  1980.0   M        MH   \n",
       "69290              Auto Buniks         POL  1988.0   M       MJA   \n",
       "69574                      NaN         SWE     NaN   M        MH   \n",
       "69640              Nacho Sport         ESP  1963.0   M       W40   \n",
       "69739                      NaN         GBR  1982.0   W        WH   \n",
       "80021                      NaN         GBR  1975.0   M       M30   \n",
       "80944                      NaN         DNK  1960.0   M       M45   \n",
       "86445                      NaN         FRA  1968.0   M       W35   \n",
       "86616   Wilde Wölfe Holzweißig         DEU  1956.0   M       M50   \n",
       "89023                      NaN         DEU  1970.0   M       M35   \n",
       "92208                      NaN         DEU  1988.0   W       WJA   \n",
       "96128                      NaN         FRA  1964.0   W       M40   \n",
       "99806                      NaN         BRA  1980.0   M        WH   \n",
       "102182                 Himanka         FIN  1959.0   W       W45   \n",
       "112288                     NaN         ITA  1969.0   W       W35   \n",
       "120566      BSG Deutscher Ring         DEU  1969.0   M       M35   \n",
       "124007                     NaN         DEU  1969.0   M       M35   \n",
       "124745                     NaN         DEU  1976.0   W       W35   \n",
       "126245                     NaN         NLD  1979.0   M        MH   \n",
       "\n",
       "        age_class_place net_time clock_time   age  \n",
       "9201                353 03:06:49   03:07:37  41.0  \n",
       "10108              3067 04:04:49   04:08:49  43.0  \n",
       "19935              2410 03:54:04   03:56:47  42.0  \n",
       "20122              1085 03:41:07   03:44:33  48.0  \n",
       "23362              2017 03:47:41   03:49:22  40.0  \n",
       "26631               840 04:33:09   04:37:12  56.0  \n",
       "30198               102 03:38:31   03:40:10  38.0  \n",
       "30200               677 04:26:27   04:30:40  40.0  \n",
       "34335               495 03:16:09   03:16:17  40.0  \n",
       "44551                 3 02:38:57   02:39:04  45.0  \n",
       "58571               460 04:31:24   04:33:18  49.0  \n",
       "60290                 5 03:11:03   03:11:27  60.0  \n",
       "60849              3192 04:13:29   04:14:52  37.0  \n",
       "63342              3830 04:35:38   04:36:56  38.0  \n",
       "66246              1914 03:52:12   03:55:13  46.0  \n",
       "66879               357 03:18:26   03:19:08  27.0  \n",
       "69290               119 05:54:43   05:56:55  19.0  \n",
       "69574              2558 05:03:28   05:04:51   NaN  \n",
       "69640                58 03:26:09   03:28:44  44.0  \n",
       "69739               549 04:32:42   04:33:03  25.0  \n",
       "80021              1733 03:58:57   04:02:33  32.0  \n",
       "80944               159 03:01:18   03:02:26  47.0  \n",
       "86445               129 03:41:50   03:42:01  39.0  \n",
       "86616              2290 04:40:06   04:42:31  51.0  \n",
       "89023              4116 04:52:41   04:54:15  37.0  \n",
       "92208                10 04:15:58   04:17:41  19.0  \n",
       "96128              1082 03:22:29   03:22:55  44.0  \n",
       "99806               258 03:56:58   04:01:31  28.0  \n",
       "102182              589 04:22:10   04:24:49  49.0  \n",
       "112288              283 03:55:55   03:58:28  39.0  \n",
       "120566             3402 04:19:17   04:21:33  39.0  \n",
       "124007              981 03:22:37   03:24:11  39.0  \n",
       "124745             2725 03:59:50   04:14:42  32.0  \n",
       "126245             2859 05:20:02   05:24:52  29.0  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inconsistent_sex.set_value(99806, 'sex', 'M')\n",
    "inconsistent_sex.set_value(96128, 'sex', 'W')\n",
    "inconsistent_sex.set_value(86445, 'sex', 'M')\n",
    "inconsistent_sex.set_value(69640, 'sex', 'M')\n",
    "inconsistent_sex.set_value(58571, 'sex', 'M')\n",
    "inconsistent_sex.set_value(30200, 'sex', 'M')\n",
    "inconsistent_sex.set_value(30198, 'sex', 'M')\n",
    "\n",
    "df.loc[inconsistent_sex.index, 'sex'] = inconsistent_sex.sex\n",
    "df.loc[inconsistent_sex.index, :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can remove the sex from the age class."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "          33\n",
       "30     49169\n",
       "35     63148\n",
       "40     80798\n",
       "45     73169\n",
       "50     50221\n",
       "55     24577\n",
       "60     11421\n",
       "65      4786\n",
       "70      1618\n",
       "75       340\n",
       "80        57\n",
       "H      42355\n",
       "JA      1318\n",
       "U20       95\n",
       "Name: age_class, dtype: int64"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.age_class = df.age_class.str.replace(r'[MW]?(.*)', r'\\1')\n",
    "df.age_class.value_counts().sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "33"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[(df.age_class == \"\")])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.age_class = df.age_class.replace('', np.nan)\n",
    "len(df[df.age_class == \"\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Are the ages consistent with the age class?  For problem cases, we assume that the YOB is correct since this is the source of information supplied by the participant (everything else is derived by the organisers)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>age_class</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>30.0</td>\n",
       "      <td>54.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>32.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>39.0</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>45.0</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>47.0</td>\n",
       "      <td>54.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>55.0</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>60.0</td>\n",
       "      <td>64.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>65.0</td>\n",
       "      <td>69.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>60.0</td>\n",
       "      <td>74.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>75.0</td>\n",
       "      <td>79.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>80.0</td>\n",
       "      <td>86.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>H</th>\n",
       "      <td>20.0</td>\n",
       "      <td>48.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JA</th>\n",
       "      <td>14.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>U20</th>\n",
       "      <td>18.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            min   max\n",
       "age_class            \n",
       "30         30.0  54.0\n",
       "35         32.0  40.0\n",
       "40         39.0  44.0\n",
       "45         45.0  49.0\n",
       "50         47.0  54.0\n",
       "55         55.0  59.0\n",
       "60         60.0  64.0\n",
       "65         65.0  69.0\n",
       "70         60.0  74.0\n",
       "75         75.0  79.0\n",
       "80         80.0  86.0\n",
       "H          20.0  48.0\n",
       "JA         14.0  19.0\n",
       "U20        18.0  19.0"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('age_class').age.aggregate(['min', 'max'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>idx</th>\n",
       "      <th>year</th>\n",
       "      <th>id</th>\n",
       "      <th>place</th>\n",
       "      <th>bib</th>\n",
       "      <th>surname</th>\n",
       "      <th>forename</th>\n",
       "      <th>team</th>\n",
       "      <th>nationality</th>\n",
       "      <th>yob</th>\n",
       "      <th>sex</th>\n",
       "      <th>age_class</th>\n",
       "      <th>age_class_place</th>\n",
       "      <th>net_time</th>\n",
       "      <th>clock_time</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>122666</th>\n",
       "      <td>122666</td>\n",
       "      <td>2008</td>\n",
       "      <td>34733</td>\n",
       "      <td>4928</td>\n",
       "      <td>36826</td>\n",
       "      <td>Silva</td>\n",
       "      <td>Usiel</td>\n",
       "      <td>Rho Delta Trave</td>\n",
       "      <td>BRA</td>\n",
       "      <td>1954.0</td>\n",
       "      <td>M</td>\n",
       "      <td>30</td>\n",
       "      <td>677</td>\n",
       "      <td>03:23:32</td>\n",
       "      <td>03:23:38</td>\n",
       "      <td>54.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           idx  year     id  place    bib surname forename             team  \\\n",
       "122666  122666  2008  34733   4928  36826   Silva    Usiel  Rho Delta Trave   \n",
       "\n",
       "       nationality     yob sex age_class  age_class_place net_time clock_time  \\\n",
       "122666         BRA  1954.0   M        30              677 03:23:32   03:23:38   \n",
       "\n",
       "         age  \n",
       "122666  54.0  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[(df.age_class == '30') & (df.age >= 35)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>idx</th>\n",
       "      <th>year</th>\n",
       "      <th>id</th>\n",
       "      <th>place</th>\n",
       "      <th>bib</th>\n",
       "      <th>surname</th>\n",
       "      <th>forename</th>\n",
       "      <th>team</th>\n",
       "      <th>nationality</th>\n",
       "      <th>yob</th>\n",
       "      <th>sex</th>\n",
       "      <th>age_class</th>\n",
       "      <th>age_class_place</th>\n",
       "      <th>net_time</th>\n",
       "      <th>clock_time</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>124745</th>\n",
       "      <td>124745</td>\n",
       "      <td>2008</td>\n",
       "      <td>38362</td>\n",
       "      <td>16994</td>\n",
       "      <td>24442</td>\n",
       "      <td>Neuhaus</td>\n",
       "      <td>Svenja</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1976.0</td>\n",
       "      <td>W</td>\n",
       "      <td>35</td>\n",
       "      <td>2725</td>\n",
       "      <td>03:59:50</td>\n",
       "      <td>04:14:42</td>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           idx  year     id  place    bib  surname forename team nationality  \\\n",
       "124745  124745  2008  38362  16994  24442  Neuhaus   Svenja  NaN         DEU   \n",
       "\n",
       "           yob sex age_class  age_class_place net_time clock_time   age  \n",
       "124745  1976.0   W        35             2725 03:59:50   04:14:42  32.0  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.set_value(122666, 'age_class', '50')\n",
    "\n",
    "df[(df.age_class == '35') & (df.age < 35)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>idx</th>\n",
       "      <th>year</th>\n",
       "      <th>id</th>\n",
       "      <th>place</th>\n",
       "      <th>bib</th>\n",
       "      <th>surname</th>\n",
       "      <th>forename</th>\n",
       "      <th>team</th>\n",
       "      <th>nationality</th>\n",
       "      <th>yob</th>\n",
       "      <th>sex</th>\n",
       "      <th>age_class</th>\n",
       "      <th>age_class_place</th>\n",
       "      <th>net_time</th>\n",
       "      <th>clock_time</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>101200</th>\n",
       "      <td>101200</td>\n",
       "      <td>2008</td>\n",
       "      <td>8907</td>\n",
       "      <td>2532</td>\n",
       "      <td>17349</td>\n",
       "      <td>Lameir</td>\n",
       "      <td>Christian</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1959.0</td>\n",
       "      <td>M</td>\n",
       "      <td>50</td>\n",
       "      <td>155</td>\n",
       "      <td>03:10:18</td>\n",
       "      <td>03:10:26</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>261808</th>\n",
       "      <td>261808</td>\n",
       "      <td>2012</td>\n",
       "      <td>32192</td>\n",
       "      <td>20225</td>\n",
       "      <td>13846</td>\n",
       "      <td>Hundertmark</td>\n",
       "      <td>Peter</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1965.0</td>\n",
       "      <td>M</td>\n",
       "      <td>50</td>\n",
       "      <td>2125</td>\n",
       "      <td>04:12:00</td>\n",
       "      <td>04:22:19</td>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           idx  year     id  place    bib      surname   forename team  \\\n",
       "101200  101200  2008   8907   2532  17349       Lameir  Christian  NaN   \n",
       "261808  261808  2012  32192  20225  13846  Hundertmark      Peter  NaN   \n",
       "\n",
       "       nationality     yob sex age_class  age_class_place net_time clock_time  \\\n",
       "101200         DEU  1959.0   M        50              155 03:10:18   03:10:26   \n",
       "261808         DEU  1965.0   M        50             2125 04:12:00   04:22:19   \n",
       "\n",
       "         age  \n",
       "101200  49.0  \n",
       "261808  47.0  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.set_value(124745, 'age_class', '30')\n",
    "\n",
    "df[(df.age_class == '50') & (df.age < 50)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>idx</th>\n",
       "      <th>year</th>\n",
       "      <th>id</th>\n",
       "      <th>place</th>\n",
       "      <th>bib</th>\n",
       "      <th>surname</th>\n",
       "      <th>forename</th>\n",
       "      <th>team</th>\n",
       "      <th>nationality</th>\n",
       "      <th>yob</th>\n",
       "      <th>sex</th>\n",
       "      <th>age_class</th>\n",
       "      <th>age_class_place</th>\n",
       "      <th>net_time</th>\n",
       "      <th>clock_time</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>144135</th>\n",
       "      <td>144135</td>\n",
       "      <td>2009</td>\n",
       "      <td>16097</td>\n",
       "      <td>28932</td>\n",
       "      <td>F174</td>\n",
       "      <td>Kiederle</td>\n",
       "      <td>Inger</td>\n",
       "      <td>Atletica</td>\n",
       "      <td>DNK</td>\n",
       "      <td>1942.0</td>\n",
       "      <td>W</td>\n",
       "      <td>70</td>\n",
       "      <td>3</td>\n",
       "      <td>04:52:07</td>\n",
       "      <td>05:07:35</td>\n",
       "      <td>67.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160277</th>\n",
       "      <td>160277</td>\n",
       "      <td>2009</td>\n",
       "      <td>32876</td>\n",
       "      <td>20390</td>\n",
       "      <td>37361</td>\n",
       "      <td>Thiel</td>\n",
       "      <td>Peter</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1949.0</td>\n",
       "      <td>M</td>\n",
       "      <td>70</td>\n",
       "      <td>19</td>\n",
       "      <td>04:19:01</td>\n",
       "      <td>04:30:43</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           idx  year     id  place    bib   surname forename      team  \\\n",
       "144135  144135  2009  16097  28932   F174  Kiederle    Inger  Atletica   \n",
       "160277  160277  2009  32876  20390  37361     Thiel    Peter       NaN   \n",
       "\n",
       "       nationality     yob sex age_class  age_class_place net_time clock_time  \\\n",
       "144135         DNK  1942.0   W        70                3 04:52:07   05:07:35   \n",
       "160277         DEU  1949.0   M        70               19 04:19:01   04:30:43   \n",
       "\n",
       "         age  \n",
       "144135  67.0  \n",
       "160277  60.0  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.set_value(261808, 'age_class', '45')\n",
    "\n",
    "df[(df.age_class == '70') & (df.age < 70)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>idx</th>\n",
       "      <th>year</th>\n",
       "      <th>id</th>\n",
       "      <th>place</th>\n",
       "      <th>bib</th>\n",
       "      <th>surname</th>\n",
       "      <th>forename</th>\n",
       "      <th>team</th>\n",
       "      <th>nationality</th>\n",
       "      <th>yob</th>\n",
       "      <th>sex</th>\n",
       "      <th>age_class</th>\n",
       "      <th>age_class_place</th>\n",
       "      <th>net_time</th>\n",
       "      <th>clock_time</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>81816</th>\n",
       "      <td>81816</td>\n",
       "      <td>2007</td>\n",
       "      <td>22121</td>\n",
       "      <td>41</td>\n",
       "      <td>84</td>\n",
       "      <td>Güssow</td>\n",
       "      <td>Christian</td>\n",
       "      <td>Hamburger SV</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1976.0</td>\n",
       "      <td>M</td>\n",
       "      <td>H</td>\n",
       "      <td>17</td>\n",
       "      <td>02:27:05</td>\n",
       "      <td>02:27:08</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147825</th>\n",
       "      <td>147825</td>\n",
       "      <td>2009</td>\n",
       "      <td>19946</td>\n",
       "      <td>14485</td>\n",
       "      <td>23852</td>\n",
       "      <td>Pranzner</td>\n",
       "      <td>Andreas</td>\n",
       "      <td>Berliner Feuerwehr</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1961.0</td>\n",
       "      <td>M</td>\n",
       "      <td>H</td>\n",
       "      <td>1415</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>04:19:35</td>\n",
       "      <td>48.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           idx  year     id  place    bib   surname   forename  \\\n",
       "81816    81816  2007  22121     41     84    Güssow  Christian   \n",
       "147825  147825  2009  19946  14485  23852  Pranzner    Andreas   \n",
       "\n",
       "                      team nationality     yob sex age_class  age_class_place  \\\n",
       "81816         Hamburger SV         DEU  1976.0   M         H               17   \n",
       "147825  Berliner Feuerwehr         DEU  1961.0   M         H             1415   \n",
       "\n",
       "       net_time clock_time   age  \n",
       "81816  02:27:05   02:27:08  31.0  \n",
       "147825 04:00:00   04:19:35  48.0  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.set_value(144135, 'age_class', '65')\n",
    "df.set_value(160277, 'age_class', '60')\n",
    "\n",
    "df[(df.age_class == 'H') & (df.age >= 30)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>age_class</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0-19</th>\n",
       "      <td>14.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20-29</th>\n",
       "      <td>20.0</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30-34</th>\n",
       "      <td>30.0</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35-39</th>\n",
       "      <td>35.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40-44</th>\n",
       "      <td>39.0</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45-49</th>\n",
       "      <td>45.0</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50-54</th>\n",
       "      <td>49.0</td>\n",
       "      <td>54.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55-59</th>\n",
       "      <td>55.0</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60-64</th>\n",
       "      <td>60.0</td>\n",
       "      <td>64.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65-69</th>\n",
       "      <td>65.0</td>\n",
       "      <td>69.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70-74</th>\n",
       "      <td>70.0</td>\n",
       "      <td>74.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75-79</th>\n",
       "      <td>75.0</td>\n",
       "      <td>79.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80+</th>\n",
       "      <td>80.0</td>\n",
       "      <td>86.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            min   max\n",
       "age_class            \n",
       "0-19       14.0  19.0\n",
       "20-29      20.0  29.0\n",
       "30-34      30.0  34.0\n",
       "35-39      35.0  40.0\n",
       "40-44      39.0  44.0\n",
       "45-49      45.0  49.0\n",
       "50-54      49.0  54.0\n",
       "55-59      55.0  59.0\n",
       "60-64      60.0  64.0\n",
       "65-69      65.0  69.0\n",
       "70-74      70.0  74.0\n",
       "75-79      75.0  79.0\n",
       "80+        80.0  86.0"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.set_value(81816, 'age_class', '30')\n",
    "df.set_value(147825, 'age_class', '45')\n",
    "\n",
    "def upper(match):\n",
    "    if match.group(2) == '0':\n",
    "        d = '4'\n",
    "    else:\n",
    "        d = '9'\n",
    "    return '{0}-{1}{2}'.format(match.group(0), match.group(1), d)\n",
    "\n",
    "df.age_class = df.age_class.str.replace(r'^(JA|U20)$', '0-19').str.replace('^H$', '20-29').str.replace(r'^8.*', '80+').str.replace(r'^(\\d)(\\d)$', upper)\n",
    "df.groupby('age_class').age.aggregate(['min', 'max'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If a participant has a YOB but no age class, then we can determine the correct class."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "yob_no_ac = df[(df.age_class.isnull()) & (~df.yob.isnull())]\n",
    "len(yob_no_ac)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/brian/.virtualenvs/generic/lib/python3.5/site-packages/pandas/core/generic.py:3110: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self[name] = value\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>idx</th>\n",
       "      <th>year</th>\n",
       "      <th>id</th>\n",
       "      <th>place</th>\n",
       "      <th>bib</th>\n",
       "      <th>surname</th>\n",
       "      <th>forename</th>\n",
       "      <th>team</th>\n",
       "      <th>nationality</th>\n",
       "      <th>yob</th>\n",
       "      <th>sex</th>\n",
       "      <th>age_class</th>\n",
       "      <th>age_class_place</th>\n",
       "      <th>net_time</th>\n",
       "      <th>clock_time</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>177069</th>\n",
       "      <td>177069</td>\n",
       "      <td>2010</td>\n",
       "      <td>13887</td>\n",
       "      <td>9534</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Coll</td>\n",
       "      <td>Florian</td>\n",
       "      <td>Cours Forest</td>\n",
       "      <td>FRA</td>\n",
       "      <td>1983.0</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>03:41:07</td>\n",
       "      <td>03:52:11</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250216</th>\n",
       "      <td>250216</td>\n",
       "      <td>2012</td>\n",
       "      <td>20259</td>\n",
       "      <td>12554</td>\n",
       "      <td>3612</td>\n",
       "      <td>Adriaansen</td>\n",
       "      <td>Dave</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NLD</td>\n",
       "      <td>1977.0</td>\n",
       "      <td>M</td>\n",
       "      <td>35-39</td>\n",
       "      <td>0</td>\n",
       "      <td>03:49:46</td>\n",
       "      <td>04:00:05</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>367138</th>\n",
       "      <td>367138</td>\n",
       "      <td>2015</td>\n",
       "      <td>141090</td>\n",
       "      <td>36483</td>\n",
       "      <td>F12454</td>\n",
       "      <td>Stasch</td>\n",
       "      <td>Christa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GER</td>\n",
       "      <td>1956.0</td>\n",
       "      <td>W</td>\n",
       "      <td>55-59</td>\n",
       "      <td>0</td>\n",
       "      <td>06:10:32</td>\n",
       "      <td>06:46:46</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>367139</th>\n",
       "      <td>367139</td>\n",
       "      <td>2015</td>\n",
       "      <td>141108</td>\n",
       "      <td>35431</td>\n",
       "      <td>38838</td>\n",
       "      <td>Wang</td>\n",
       "      <td>Hsi-Tsang</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TPE</td>\n",
       "      <td>1976.0</td>\n",
       "      <td>M</td>\n",
       "      <td>35-39</td>\n",
       "      <td>0</td>\n",
       "      <td>05:36:47</td>\n",
       "      <td>06:16:10</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>367140</th>\n",
       "      <td>367140</td>\n",
       "      <td>2015</td>\n",
       "      <td>141111</td>\n",
       "      <td>9428</td>\n",
       "      <td>38825</td>\n",
       "      <td>Heinrichs</td>\n",
       "      <td>Markus</td>\n",
       "      <td>SCW München Triathlon</td>\n",
       "      <td>GER</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>M</td>\n",
       "      <td>35-39</td>\n",
       "      <td>0</td>\n",
       "      <td>03:36:51</td>\n",
       "      <td>03:56:07</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>367141</th>\n",
       "      <td>367141</td>\n",
       "      <td>2015</td>\n",
       "      <td>141114</td>\n",
       "      <td>19328</td>\n",
       "      <td>38827</td>\n",
       "      <td>Huai</td>\n",
       "      <td>Qianjiang</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CHN</td>\n",
       "      <td>1975.0</td>\n",
       "      <td>M</td>\n",
       "      <td>40-44</td>\n",
       "      <td>0</td>\n",
       "      <td>04:04:35</td>\n",
       "      <td>04:24:01</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>367142</th>\n",
       "      <td>367142</td>\n",
       "      <td>2015</td>\n",
       "      <td>141120</td>\n",
       "      <td>18143</td>\n",
       "      <td>38828</td>\n",
       "      <td>Kraus</td>\n",
       "      <td>Martin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GER</td>\n",
       "      <td>1975.0</td>\n",
       "      <td>M</td>\n",
       "      <td>40-44</td>\n",
       "      <td>0</td>\n",
       "      <td>04:00:32</td>\n",
       "      <td>04:23:56</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391937</th>\n",
       "      <td>391937</td>\n",
       "      <td>2016</td>\n",
       "      <td>77124</td>\n",
       "      <td>27472</td>\n",
       "      <td>6956</td>\n",
       "      <td>Barris</td>\n",
       "      <td>Wolfram</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1951.0</td>\n",
       "      <td>M</td>\n",
       "      <td>65-69</td>\n",
       "      <td>191</td>\n",
       "      <td>04:43:58</td>\n",
       "      <td>05:00:42</td>\n",
       "      <td>65.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>392473</th>\n",
       "      <td>392473</td>\n",
       "      <td>2016</td>\n",
       "      <td>78774</td>\n",
       "      <td>26936</td>\n",
       "      <td>40651</td>\n",
       "      <td>Schnetzer</td>\n",
       "      <td>Wolfgang</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1961.0</td>\n",
       "      <td>M</td>\n",
       "      <td>55-59</td>\n",
       "      <td>1509</td>\n",
       "      <td>04:41:32</td>\n",
       "      <td>05:15:56</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           idx  year      id  place     bib     surname   forename  \\\n",
       "177069  177069  2010   13887   9534     NaN        Coll    Florian   \n",
       "250216  250216  2012   20259  12554    3612  Adriaansen       Dave   \n",
       "367138  367138  2015  141090  36483  F12454      Stasch    Christa   \n",
       "367139  367139  2015  141108  35431   38838        Wang  Hsi-Tsang   \n",
       "367140  367140  2015  141111   9428   38825   Heinrichs     Markus   \n",
       "367141  367141  2015  141114  19328   38827        Huai  Qianjiang   \n",
       "367142  367142  2015  141120  18143   38828       Kraus     Martin   \n",
       "391937  391937  2016   77124  27472    6956      Barris    Wolfram   \n",
       "392473  392473  2016   78774  26936   40651   Schnetzer   Wolfgang   \n",
       "\n",
       "                         team nationality     yob sex age_class  \\\n",
       "177069           Cours Forest         FRA  1983.0   M       NaN   \n",
       "250216                    NaN         NLD  1977.0   M     35-39   \n",
       "367138                    NaN         GER  1956.0   W     55-59   \n",
       "367139                    NaN         TPE  1976.0   M     35-39   \n",
       "367140  SCW München Triathlon         GER  1980.0   M     35-39   \n",
       "367141                    NaN         CHN  1975.0   M     40-44   \n",
       "367142                    NaN         GER  1975.0   M     40-44   \n",
       "391937                    NaN         NaN  1951.0   M     65-69   \n",
       "392473                    NaN         NaN  1961.0   M     55-59   \n",
       "\n",
       "        age_class_place net_time clock_time   age  \n",
       "177069                0 03:41:07   03:52:11  27.0  \n",
       "250216                0 03:49:46   04:00:05  35.0  \n",
       "367138                0 06:10:32   06:46:46  59.0  \n",
       "367139                0 05:36:47   06:16:10  39.0  \n",
       "367140                0 03:36:51   03:56:07  35.0  \n",
       "367141                0 04:04:35   04:24:01  40.0  \n",
       "367142                0 04:00:32   04:23:56  40.0  \n",
       "391937              191 04:43:58   05:00:42  65.0  \n",
       "392473             1509 04:41:32   05:15:56  55.0  "
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "age_classes = pd.unique(df.age_class.dropna().sort_values())\n",
    "yob_no_ac.age_class = (5 * (yob_no_ac.age // 5)).astype('int').astype('str').str.replace(r'^(\\d)(\\d)$', upper).astype('category', categories=age_classes)\n",
    "yob_no_ac"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.loc[yob_no_ac.index, 'age_class'] = yob_no_ac.age_class"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, we make the age class a category type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.age_class = df.age_class.astype('category', categories=age_classes, ordered=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Age Class Place"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[df.age_class_place.isnull()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    403134.000000\n",
       "mean       1596.684058\n",
       "std        1321.932040\n",
       "min           0.000000\n",
       "25%         517.000000\n",
       "50%        1208.000000\n",
       "75%        2456.000000\n",
       "max        6101.000000\n",
       "Name: age_class_place, dtype: float64"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.age_class_place.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can't have zeroth place - this should be null."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.age_class_place = df.age_class_place.replace(0, np.nan)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Teams"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count       181573\n",
       "unique       68777\n",
       "top       Terramia\n",
       "freq          1908\n",
       "Name: team, dtype: object"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.team.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Nationality"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     403120\n",
       "unique       250\n",
       "top          DEU\n",
       "freq      155501\n",
       "Name: nationality, dtype: object"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.nationality.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DEU    155501\n",
       "GER     36733\n",
       "DNK     36167\n",
       "GBR     23524\n",
       "FRA     18309\n",
       "Name: nationality, dtype: int64"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.nationality.value_counts().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There seems to be no consistent abbreviation system used for nationalities, which makes this column almost useless for making any nationality-based aggregate stats. We make the assumption that any abbreviation in our dataset which exists in the ISO 3166 1 standard uses that standard.  For the rest, we make an educated guess.  See the [countries](../data/countries.csv) dataset for the mappings.\n",
    "\n",
    "* [ISO](https://en.wikipedia.org/wiki/ISO_3166-1)\n",
    "* [FIFA](https://en.wikipedia.org/wiki/List_of_FIFA_country_codes)\n",
    "* [IOC](https://en.wikipedia.org/wiki/List_of_IOC_country_codes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>iso3166_1alpha_3</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ABW</td>\n",
       "      <td>ABW</td>\n",
       "      <td>Aruba</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AFG</td>\n",
       "      <td>AFG</td>\n",
       "      <td>Afghanistan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AGO</td>\n",
       "      <td>AGO</td>\n",
       "      <td>Angola</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AHO</td>\n",
       "      <td>AHO</td>\n",
       "      <td>Netherlands Antilles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ALB</td>\n",
       "      <td>ALB</td>\n",
       "      <td>Albania</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  abbreviation iso3166_1alpha_3               country\n",
       "0          ABW              ABW                 Aruba\n",
       "1          AFG              AFG           Afghanistan\n",
       "2          AGO              AGO                Angola\n",
       "3          AHO              AHO  Netherlands Antilles\n",
       "4          ALB              ALB               Albania"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nat = pd.read_csv('../data/countries.csv')\n",
    "nat.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['RKS']"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sorted(set(df.nationality.dropna()) - set(nat.abbreviation))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfnat = pd.merge(df, nat, left_on='nationality', right_on='abbreviation', how='left').sort_values(['year', 'id']) #.reindex(df.index)\n",
    "(df.index == dfnat.index).min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     403117\n",
       "unique       199\n",
       "top          DEU\n",
       "freq      192234\n",
       "Name: nationality, dtype: object"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.nationality = dfnat.iso3166_1alpha_3\n",
    "df.nationality.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     403089\n",
       "unique     37436\n",
       "top       Thomas\n",
       "freq        7865\n",
       "Name: forename, dtype: object"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.forename.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "45"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[df.forename.isnull()])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If one name exists but the other doesn't, we can check to see if both names have been entered into one column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>idx</th>\n",
       "      <th>year</th>\n",
       "      <th>id</th>\n",
       "      <th>place</th>\n",
       "      <th>bib</th>\n",
       "      <th>surname</th>\n",
       "      <th>forename</th>\n",
       "      <th>team</th>\n",
       "      <th>nationality</th>\n",
       "      <th>yob</th>\n",
       "      <th>sex</th>\n",
       "      <th>age_class</th>\n",
       "      <th>age_class_place</th>\n",
       "      <th>net_time</th>\n",
       "      <th>clock_time</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>60554</th>\n",
       "      <td>60554</td>\n",
       "      <td>2006</td>\n",
       "      <td>44151</td>\n",
       "      <td>15749</td>\n",
       "      <td>29772</td>\n",
       "      <td>Chávez-Fernández Goyburu,Juan José</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Regatas Lima</td>\n",
       "      <td>PER</td>\n",
       "      <td>1967.0</td>\n",
       "      <td>M</td>\n",
       "      <td>35-39</td>\n",
       "      <td>2771.0</td>\n",
       "      <td>04:15:47</td>\n",
       "      <td>04:18:00</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60901</th>\n",
       "      <td>60901</td>\n",
       "      <td>2007</td>\n",
       "      <td>359</td>\n",
       "      <td>31004</td>\n",
       "      <td>F1115</td>\n",
       "      <td>Mackenzie,</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NLD</td>\n",
       "      <td>1962.0</td>\n",
       "      <td>W</td>\n",
       "      <td>45-49</td>\n",
       "      <td>1028.0</td>\n",
       "      <td>05:21:45</td>\n",
       "      <td>05:29:38</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94924</th>\n",
       "      <td>94924</td>\n",
       "      <td>2008</td>\n",
       "      <td>2386</td>\n",
       "      <td>22570</td>\n",
       "      <td>F2721</td>\n",
       "      <td>Elvers-Schreiber,</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DAV Berlin</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1951.0</td>\n",
       "      <td>W</td>\n",
       "      <td>55-59</td>\n",
       "      <td>86.0</td>\n",
       "      <td>04:17:47</td>\n",
       "      <td>04:22:01</td>\n",
       "      <td>57.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122234</th>\n",
       "      <td>122234</td>\n",
       "      <td>2008</td>\n",
       "      <td>34063</td>\n",
       "      <td>17214</td>\n",
       "      <td>F2727</td>\n",
       "      <td>Emmanuelle Rochet-Blanc,</td>\n",
       "      <td>NaN</td>\n",
       "      <td>les chauffe la semelle</td>\n",
       "      <td>FRA</td>\n",
       "      <td>1968.0</td>\n",
       "      <td>W</td>\n",
       "      <td>40-44</td>\n",
       "      <td>433.0</td>\n",
       "      <td>04:00:22</td>\n",
       "      <td>04:06:18</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124378</th>\n",
       "      <td>124378</td>\n",
       "      <td>2008</td>\n",
       "      <td>37491</td>\n",
       "      <td>34879</td>\n",
       "      <td>37896</td>\n",
       "      <td>Williams,</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>20-29</td>\n",
       "      <td>2921.0</td>\n",
       "      <td>05:38:09</td>\n",
       "      <td>05:44:09</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124379</th>\n",
       "      <td>124379</td>\n",
       "      <td>2008</td>\n",
       "      <td>37492</td>\n",
       "      <td>27883</td>\n",
       "      <td>37897</td>\n",
       "      <td>Keith,</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GBR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>20-29</td>\n",
       "      <td>2452.0</td>\n",
       "      <td>04:37:34</td>\n",
       "      <td>04:41:15</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125273</th>\n",
       "      <td>125273</td>\n",
       "      <td>2008</td>\n",
       "      <td>39885</td>\n",
       "      <td>4790</td>\n",
       "      <td>37898</td>\n",
       "      <td>Witfield,</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GBR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>20-29</td>\n",
       "      <td>503.0</td>\n",
       "      <td>03:22:56</td>\n",
       "      <td>03:24:39</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132211</th>\n",
       "      <td>132211</td>\n",
       "      <td>2009</td>\n",
       "      <td>3741</td>\n",
       "      <td>836</td>\n",
       "      <td>6092</td>\n",
       "      <td>Bosgoed</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ARO'88</td>\n",
       "      <td>NLD</td>\n",
       "      <td>1966.0</td>\n",
       "      <td>M</td>\n",
       "      <td>40-44</td>\n",
       "      <td>183.0</td>\n",
       "      <td>02:57:05</td>\n",
       "      <td>02:57:29</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>266361</th>\n",
       "      <td>266361</td>\n",
       "      <td>2013</td>\n",
       "      <td>1602</td>\n",
       "      <td>6254</td>\n",
       "      <td>10087</td>\n",
       "      <td>Gestin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CACL Courbevoie</td>\n",
       "      <td>FRA</td>\n",
       "      <td>1967.0</td>\n",
       "      <td>M</td>\n",
       "      <td>45-49</td>\n",
       "      <td>1086.0</td>\n",
       "      <td>03:27:16</td>\n",
       "      <td>03:30:36</td>\n",
       "      <td>46.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           idx  year     id  place    bib                             surname  \\\n",
       "60554    60554  2006  44151  15749  29772  Chávez-Fernández Goyburu,Juan José   \n",
       "60901    60901  2007    359  31004  F1115                          Mackenzie,   \n",
       "94924    94924  2008   2386  22570  F2721                   Elvers-Schreiber,   \n",
       "122234  122234  2008  34063  17214  F2727            Emmanuelle Rochet-Blanc,   \n",
       "124378  124378  2008  37491  34879  37896                           Williams,   \n",
       "124379  124379  2008  37492  27883  37897                              Keith,   \n",
       "125273  125273  2008  39885   4790  37898                           Witfield,   \n",
       "132211  132211  2009   3741    836   6092                             Bosgoed   \n",
       "266361  266361  2013   1602   6254  10087                              Gestin   \n",
       "\n",
       "       forename                    team nationality     yob sex age_class  \\\n",
       "60554       NaN            Regatas Lima         PER  1967.0   M     35-39   \n",
       "60901       NaN                     NaN         NLD  1962.0   W     45-49   \n",
       "94924       NaN              DAV Berlin         DEU  1951.0   W     55-59   \n",
       "122234      NaN  les chauffe la semelle         FRA  1968.0   W     40-44   \n",
       "124378      NaN                     NaN         DEU     NaN   M     20-29   \n",
       "124379      NaN                     NaN         GBR     NaN   M     20-29   \n",
       "125273      NaN                     NaN         GBR     NaN   M     20-29   \n",
       "132211      NaN                  ARO'88         NLD  1966.0   M     40-44   \n",
       "266361      NaN         CACL Courbevoie         FRA  1967.0   M     45-49   \n",
       "\n",
       "        age_class_place net_time clock_time   age  \n",
       "60554            2771.0 04:15:47   04:18:00  39.0  \n",
       "60901            1028.0 05:21:45   05:29:38  45.0  \n",
       "94924              86.0 04:17:47   04:22:01  57.0  \n",
       "122234            433.0 04:00:22   04:06:18  40.0  \n",
       "124378           2921.0 05:38:09   05:44:09   NaN  \n",
       "124379           2452.0 04:37:34   04:41:15   NaN  \n",
       "125273            503.0 03:22:56   03:24:39   NaN  \n",
       "132211            183.0 02:57:05   02:57:29  43.0  \n",
       "266361           1086.0 03:27:16   03:30:36  46.0  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[(df.forename.isnull()) & (~df.surname.isnull())]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count      403096\n",
       "unique     127265\n",
       "top       Nielsen\n",
       "freq         1648\n",
       "Name: surname, dtype: object"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.set_value(60554, 'forename', 'Juan José')\n",
    "df.set_value(60554, 'surname', 'Chávez-Fernández Goyburu')\n",
    "\n",
    "df.surname.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "38"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[df.surname.isnull()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>idx</th>\n",
       "      <th>year</th>\n",
       "      <th>id</th>\n",
       "      <th>place</th>\n",
       "      <th>bib</th>\n",
       "      <th>surname</th>\n",
       "      <th>forename</th>\n",
       "      <th>team</th>\n",
       "      <th>nationality</th>\n",
       "      <th>yob</th>\n",
       "      <th>sex</th>\n",
       "      <th>age_class</th>\n",
       "      <th>age_class_place</th>\n",
       "      <th>net_time</th>\n",
       "      <th>clock_time</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>30385</th>\n",
       "      <td>30385</td>\n",
       "      <td>2006</td>\n",
       "      <td>16</td>\n",
       "      <td>12730</td>\n",
       "      <td>32232</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Benjamin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1982.0</td>\n",
       "      <td>M</td>\n",
       "      <td>20-29</td>\n",
       "      <td>1134.0</td>\n",
       "      <td>04:04:33</td>\n",
       "      <td>04:10:01</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148108</th>\n",
       "      <td>148108</td>\n",
       "      <td>2009</td>\n",
       "      <td>20236</td>\n",
       "      <td>223</td>\n",
       "      <td>26347</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Christoph</td>\n",
       "      <td>LAV Asics Tübingen</td>\n",
       "      <td>DEU</td>\n",
       "      <td>1984.0</td>\n",
       "      <td>M</td>\n",
       "      <td>20-29</td>\n",
       "      <td>53.0</td>\n",
       "      <td>02:41:58</td>\n",
       "      <td>02:42:06</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           idx  year     id  place    bib surname   forename  \\\n",
       "30385    30385  2006     16  12730  32232     NaN   Benjamin   \n",
       "148108  148108  2009  20236    223  26347     NaN  Christoph   \n",
       "\n",
       "                      team nationality     yob sex age_class  age_class_place  \\\n",
       "30385                  NaN         DEU  1982.0   M     20-29           1134.0   \n",
       "148108  LAV Asics Tübingen         DEU  1984.0   M     20-29             53.0   \n",
       "\n",
       "       net_time clock_time   age  \n",
       "30385  04:04:33   04:10:01  24.0  \n",
       "148108 02:41:58   02:42:06  25.0  "
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[(df.surname.isnull()) & (~df.forename.isnull())]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.surname = df.surname.str.strip(string.punctuation)\n",
    "df.forename = df.forename.str.strip(string.punctuation)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bibs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     403127\n",
       "unique     59339\n",
       "top        F1024\n",
       "freq          37\n",
       "Name: bib, dtype: object"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.bib.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[df.bib.isnull()])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save the Clean Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 403134 entries, 0 to 403133\n",
      "Data columns (total 16 columns):\n",
      "idx                403134 non-null int64\n",
      "year               403134 non-null int64\n",
      "id                 403134 non-null int64\n",
      "place              403134 non-null int64\n",
      "bib                403127 non-null object\n",
      "surname            403096 non-null object\n",
      "forename           403090 non-null object\n",
      "team               181573 non-null object\n",
      "nationality        403117 non-null object\n",
      "yob                402885 non-null float64\n",
      "sex                403134 non-null category\n",
      "age_class          403080 non-null category\n",
      "age_class_place    403071 non-null float64\n",
      "net_time           403132 non-null timedelta64[ns]\n",
      "clock_time         403128 non-null timedelta64[ns]\n",
      "age                402885 non-null float64\n",
      "dtypes: category(2), float64(3), int64(4), object(5), timedelta64[ns](2)\n",
      "memory usage: 56.9+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.net_time = df.net_time.astype(str).str.split().str[-1].str.rsplit('.').str[0]\n",
    "df.clock_time = df.clock_time.astype(str).str.split().str[-1].str.rsplit('.').str[0]\n",
    "\n",
    "df.sort_values(['year', 'id']).to_csv(clean_filename, index=True, index_label='idx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}