Analysing the cleaned dataframe.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#read in the cleaned dataframe\n",
    "df = pd.read_csv('../data/processed/Cleaned_dataframe-csv.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sort th evalues by the total\n",
    "df_sort_1 = df.sort_values(by='Total')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Column</th>\n",
       "      <th>Year</th>\n",
       "      <th>Age</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Participant</th>\n",
       "      <th>Casualty</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1151</th>\n",
       "      <td>1151</td>\n",
       "      <td>2008</td>\n",
       "      <td>Age unknown</td>\n",
       "      <td>Female</td>\n",
       "      <td>Car drivers</td>\n",
       "      <td>Killed</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1169</th>\n",
       "      <td>1169</td>\n",
       "      <td>2009</td>\n",
       "      <td>00 - 05 years</td>\n",
       "      <td>Female</td>\n",
       "      <td>Other road users</td>\n",
       "      <td>Killed</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1170</th>\n",
       "      <td>1170</td>\n",
       "      <td>2009</td>\n",
       "      <td>00 - 05 years</td>\n",
       "      <td>Female</td>\n",
       "      <td>Motor cyclists</td>\n",
       "      <td>Injured</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1171</th>\n",
       "      <td>1171</td>\n",
       "      <td>2009</td>\n",
       "      <td>00 - 05 years</td>\n",
       "      <td>Female</td>\n",
       "      <td>Motor cyclists</td>\n",
       "      <td>Killed</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1174</th>\n",
       "      <td>1174</td>\n",
       "      <td>2009</td>\n",
       "      <td>00 - 05 years</td>\n",
       "      <td>Female</td>\n",
       "      <td>Car drivers</td>\n",
       "      <td>Injured</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Column  Year            Age     Sex       Participant Casualty  Total\n",
       "1151    1151  2008    Age unknown  Female       Car drivers   Killed      0\n",
       "1169    1169  2009  00 - 05 years  Female  Other road users   Killed      0\n",
       "1170    1170  2009  00 - 05 years  Female    Motor cyclists  Injured      0\n",
       "1171    1171  2009  00 - 05 years  Female    Motor cyclists   Killed      0\n",
       "1174    1174  2009  00 - 05 years  Female       Car drivers  Injured      0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#show the top 5 results\n",
    "df_sort_1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Column</th>\n",
       "      <th>Year</th>\n",
       "      <th>Age</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Participant</th>\n",
       "      <th>Casualty</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1018</th>\n",
       "      <td>1018</td>\n",
       "      <td>2008</td>\n",
       "      <td>25 - 34 years</td>\n",
       "      <td>Male</td>\n",
       "      <td>Car drivers</td>\n",
       "      <td>Injured</td>\n",
       "      <td>477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>154</td>\n",
       "      <td>2005</td>\n",
       "      <td>25 - 34 years</td>\n",
       "      <td>Male</td>\n",
       "      <td>Car drivers</td>\n",
       "      <td>Injured</td>\n",
       "      <td>518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1318</th>\n",
       "      <td>1318</td>\n",
       "      <td>2009</td>\n",
       "      <td>25 - 34 years</td>\n",
       "      <td>Female</td>\n",
       "      <td>Car drivers</td>\n",
       "      <td>Injured</td>\n",
       "      <td>538</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1306</th>\n",
       "      <td>1306</td>\n",
       "      <td>2009</td>\n",
       "      <td>25 - 34 years</td>\n",
       "      <td>Male</td>\n",
       "      <td>Car drivers</td>\n",
       "      <td>Injured</td>\n",
       "      <td>562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1947</th>\n",
       "      <td>1947</td>\n",
       "      <td>2011</td>\n",
       "      <td>55 - 64 years</td>\n",
       "      <td>Male</td>\n",
       "      <td>Pedal cyclists</td>\n",
       "      <td>Killed</td>\n",
       "      <td>1010</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Column  Year            Age     Sex     Participant Casualty  Total\n",
       "1018    1018  2008  25 - 34 years    Male     Car drivers  Injured    477\n",
       "154      154  2005  25 - 34 years    Male     Car drivers  Injured    518\n",
       "1318    1318  2009  25 - 34 years  Female     Car drivers  Injured    538\n",
       "1306    1306  2009  25 - 34 years    Male     Car drivers  Injured    562\n",
       "1947    1947  2011  55 - 64 years    Male  Pedal cyclists   Killed   1010"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# show the bottom 5 resuts\n",
    "df_sort_1.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Owner\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:3: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Column</th>\n",
       "      <th>Year</th>\n",
       "      <th>Age</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Participant</th>\n",
       "      <th>Casualty</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td>219</td>\n",
       "      <td>2005</td>\n",
       "      <td>55 - 64 years</td>\n",
       "      <td>Male</td>\n",
       "      <td>Pedal cyclists</td>\n",
       "      <td>Killed</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>507</th>\n",
       "      <td>507</td>\n",
       "      <td>2006</td>\n",
       "      <td>55 - 64 years</td>\n",
       "      <td>Male</td>\n",
       "      <td>Pedal cyclists</td>\n",
       "      <td>Killed</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>795</th>\n",
       "      <td>795</td>\n",
       "      <td>2007</td>\n",
       "      <td>55 - 64 years</td>\n",
       "      <td>Male</td>\n",
       "      <td>Pedal cyclists</td>\n",
       "      <td>Killed</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1083</th>\n",
       "      <td>1083</td>\n",
       "      <td>2008</td>\n",
       "      <td>55 - 64 years</td>\n",
       "      <td>Male</td>\n",
       "      <td>Pedal cyclists</td>\n",
       "      <td>Killed</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1371</th>\n",
       "      <td>1371</td>\n",
       "      <td>2009</td>\n",
       "      <td>55 - 64 years</td>\n",
       "      <td>Male</td>\n",
       "      <td>Pedal cyclists</td>\n",
       "      <td>Killed</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1659</th>\n",
       "      <td>1659</td>\n",
       "      <td>2010</td>\n",
       "      <td>55 - 64 years</td>\n",
       "      <td>Male</td>\n",
       "      <td>Pedal cyclists</td>\n",
       "      <td>Killed</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1947</th>\n",
       "      <td>1947</td>\n",
       "      <td>2011</td>\n",
       "      <td>55 - 64 years</td>\n",
       "      <td>Male</td>\n",
       "      <td>Pedal cyclists</td>\n",
       "      <td>Killed</td>\n",
       "      <td>1010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2235</th>\n",
       "      <td>2235</td>\n",
       "      <td>2012</td>\n",
       "      <td>55 - 64 years</td>\n",
       "      <td>Male</td>\n",
       "      <td>Pedal cyclists</td>\n",
       "      <td>Killed</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Column  Year            Age   Sex     Participant Casualty  Total\n",
       "219      219  2005  55 - 64 years  Male  Pedal cyclists   Killed      1\n",
       "507      507  2006  55 - 64 years  Male  Pedal cyclists   Killed      2\n",
       "795      795  2007  55 - 64 years  Male  Pedal cyclists   Killed      3\n",
       "1083    1083  2008  55 - 64 years  Male  Pedal cyclists   Killed      2\n",
       "1371    1371  2009  55 - 64 years  Male  Pedal cyclists   Killed      0\n",
       "1659    1659  2010  55 - 64 years  Male  Pedal cyclists   Killed      0\n",
       "1947    1947  2011  55 - 64 years  Male  Pedal cyclists   Killed   1010\n",
       "2235    2235  2012  55 - 64 years  Male  Pedal cyclists   Killed      1"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# show rows where the person was Male, A pedal cyclist, killed and aged between 55 and 64 years\n",
    "\n",
    "df[df['Participant'] == 'Pedal cyclists'][df['Casualty'] == 'Killed'][df['Sex'] == 'Male'][df['Age'] == '55 - 64 years'].head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# I noticed that the Value 1010 was drasticly higher than anything seen in the previous years and was an extreme outlier\n",
    "# For this reason I knew this value was wrong so decided I needed to change it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.replace(to_replace=1010, value=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sort the values by the total again after replacing the value 1010\n",
    "df_sort_2 = df.sort_values(by='Total')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Column</th>\n",
       "      <th>Year</th>\n",
       "      <th>Age</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Participant</th>\n",
       "      <th>Casualty</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1151</th>\n",
       "      <td>1151</td>\n",
       "      <td>2008</td>\n",
       "      <td>Age unknown</td>\n",
       "      <td>Female</td>\n",
       "      <td>Car drivers</td>\n",
       "      <td>Killed</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1169</th>\n",
       "      <td>1169</td>\n",
       "      <td>2009</td>\n",
       "      <td>00 - 05 years</td>\n",
       "      <td>Female</td>\n",
       "      <td>Other road users</td>\n",
       "      <td>Killed</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1170</th>\n",
       "      <td>1170</td>\n",
       "      <td>2009</td>\n",
       "      <td>00 - 05 years</td>\n",
       "      <td>Female</td>\n",
       "      <td>Motor cyclists</td>\n",
       "      <td>Injured</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1171</th>\n",
       "      <td>1171</td>\n",
       "      <td>2009</td>\n",
       "      <td>00 - 05 years</td>\n",
       "      <td>Female</td>\n",
       "      <td>Motor cyclists</td>\n",
       "      <td>Killed</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1174</th>\n",
       "      <td>1174</td>\n",
       "      <td>2009</td>\n",
       "      <td>00 - 05 years</td>\n",
       "      <td>Female</td>\n",
       "      <td>Car drivers</td>\n",
       "      <td>Injured</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Column  Year            Age     Sex       Participant Casualty  Total\n",
       "1151    1151  2008    Age unknown  Female       Car drivers   Killed      0\n",
       "1169    1169  2009  00 - 05 years  Female  Other road users   Killed      0\n",
       "1170    1170  2009  00 - 05 years  Female    Motor cyclists  Injured      0\n",
       "1171    1171  2009  00 - 05 years  Female    Motor cyclists   Killed      0\n",
       "1174    1174  2009  00 - 05 years  Female       Car drivers  Injured      0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sort_2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Column</th>\n",
       "      <th>Year</th>\n",
       "      <th>Age</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Participant</th>\n",
       "      <th>Casualty</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>442</th>\n",
       "      <td>442</td>\n",
       "      <td>2006</td>\n",
       "      <td>25 - 34 years</td>\n",
       "      <td>Male</td>\n",
       "      <td>Car drivers</td>\n",
       "      <td>Injured</td>\n",
       "      <td>470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1018</th>\n",
       "      <td>1018</td>\n",
       "      <td>2008</td>\n",
       "      <td>25 - 34 years</td>\n",
       "      <td>Male</td>\n",
       "      <td>Car drivers</td>\n",
       "      <td>Injured</td>\n",
       "      <td>477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>154</td>\n",
       "      <td>2005</td>\n",
       "      <td>25 - 34 years</td>\n",
       "      <td>Male</td>\n",
       "      <td>Car drivers</td>\n",
       "      <td>Injured</td>\n",
       "      <td>518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1318</th>\n",
       "      <td>1318</td>\n",
       "      <td>2009</td>\n",
       "      <td>25 - 34 years</td>\n",
       "      <td>Female</td>\n",
       "      <td>Car drivers</td>\n",
       "      <td>Injured</td>\n",
       "      <td>538</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1306</th>\n",
       "      <td>1306</td>\n",
       "      <td>2009</td>\n",
       "      <td>25 - 34 years</td>\n",
       "      <td>Male</td>\n",
       "      <td>Car drivers</td>\n",
       "      <td>Injured</td>\n",
       "      <td>562</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Column  Year            Age     Sex  Participant Casualty  Total\n",
       "442      442  2006  25 - 34 years    Male  Car drivers  Injured    470\n",
       "1018    1018  2008  25 - 34 years    Male  Car drivers  Injured    477\n",
       "154      154  2005  25 - 34 years    Male  Car drivers  Injured    518\n",
       "1318    1318  2009  25 - 34 years  Female  Car drivers  Injured    538\n",
       "1306    1306  2009  25 - 34 years    Male  Car drivers  Injured    562"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sort_2.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# turn the dataframe into a CSV as the dinished product\n",
    "df.to_csv('../data/processed/Final_dataframe-csv.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2304 entries, 0 to 2303\n",
      "Data columns (total 7 columns):\n",
      "Column         2304 non-null int64\n",
      "Year           2304 non-null int64\n",
      "Age            2304 non-null object\n",
      "Sex            2304 non-null object\n",
      "Participant    2304 non-null object\n",
      "Casualty       2304 non-null object\n",
      "Total          2304 non-null int64\n",
      "dtypes: int64(3), object(4)\n",
      "memory usage: 126.1+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  }
 ],
 "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}