Assignment 1 - Philip Toolan C17433026.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Philip Toolan"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Import dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('./data/dataset.csv', header=None, na_values = [' ?'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Insert headings of the dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "headings = []\n",
    "\n",
    "with open('./data/feature_names.txt', 'r') as fo:\n",
    "    for line in fo:\n",
    "        if line !='\\n':\n",
    "            headings.append(line.strip())\n",
    "df.columns = headings"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Create shortcuts for each column "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "age = df['age']\n",
    "work = df['workclass']\n",
    "fnlwgt = df['fnlwgt']\n",
    "edu = df['education']\n",
    "e_num = df['education-num']\n",
    "mar = df['marital-status']\n",
    "occ = df['occupation']\n",
    "rel = df['relationship']\n",
    "race = df['race']\n",
    "sex = df['sex']\n",
    "cap_g = df['capital-gain']\n",
    "cap_l = df['capital-loss']\n",
    "hrs = df['hours-per-week']\n",
    "nc = df['native-country']\n",
    "tar = df['target']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Get description of each data type to figure out if it is continuous or categorical"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    30940.000000\n",
       "mean        38.560763\n",
       "std         13.639403\n",
       "min         17.000000\n",
       "25%         28.000000\n",
       "50%         37.000000\n",
       "75%         48.000000\n",
       "max         90.000000\n",
       "Name: age, dtype: float64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "age.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count        29205\n",
       "unique           8\n",
       "top        Private\n",
       "freq         21576\n",
       "Name: workclass, dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "work.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    3.094000e+04\n",
       "mean     1.897864e+05\n",
       "std      1.054064e+05\n",
       "min      1.228500e+04\n",
       "25%      1.178490e+05\n",
       "50%      1.783840e+05\n",
       "75%      2.373180e+05\n",
       "max      1.484705e+06\n",
       "Name: fnlwgt, dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fnlwgt.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count        30940\n",
       "unique          16\n",
       "top        HS-grad\n",
       "freq          9976\n",
       "Name: education, dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "edu.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    30940.000000\n",
       "mean        10.081254\n",
       "std          2.569967\n",
       "min          1.000000\n",
       "25%          9.000000\n",
       "50%         10.000000\n",
       "75%         12.000000\n",
       "max         16.000000\n",
       "Name: education-num, dtype: float64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "e_num.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count                   30940\n",
       "unique                      7\n",
       "top        Married-civ-spouse\n",
       "freq                    14201\n",
       "Name: marital-status, dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mar.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count               29198\n",
       "unique                 14\n",
       "top        Prof-specialty\n",
       "freq                 3932\n",
       "Name: occupation, dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "occ.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count        30940\n",
       "unique           6\n",
       "top        Husband\n",
       "freq         12496\n",
       "Name: relationship, dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rel.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count      30940\n",
       "unique         5\n",
       "top        White\n",
       "freq       26442\n",
       "Name: race, dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "race.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     30940\n",
       "unique        2\n",
       "top        Male\n",
       "freq      20705\n",
       "Name: sex, dtype: object"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sex.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    30940.000000\n",
       "mean      1081.812993\n",
       "std       7443.773041\n",
       "min          0.000000\n",
       "25%          0.000000\n",
       "50%          0.000000\n",
       "75%          0.000000\n",
       "max      99999.000000\n",
       "Name: capital-gain, dtype: float64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cap_g.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    30940.000000\n",
       "mean        86.569974\n",
       "std        401.706023\n",
       "min          0.000000\n",
       "25%          0.000000\n",
       "50%          0.000000\n",
       "75%          0.000000\n",
       "max       4356.000000\n",
       "Name: capital-loss, dtype: float64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cap_l.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    30940.000000\n",
       "mean        40.408920\n",
       "std         12.336945\n",
       "min          1.000000\n",
       "25%         40.000000\n",
       "50%         40.000000\n",
       "75%         45.000000\n",
       "max         99.000000\n",
       "Name: hours-per-week, dtype: float64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hrs.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count              30386\n",
       "unique                41\n",
       "top        United-States\n",
       "freq               27719\n",
       "Name: native-country, dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nc.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count      30940\n",
       "unique         2\n",
       "top        <=50K\n",
       "freq       23506\n",
       "Name: target, dtype: object"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tar.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Automate for all the continuos features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Had problems using the variable names from earlier\n",
    "#con_feat = age, fnlwgt, cap_g, cap_l, hrs\n",
    "\n",
    "con_feat = ['age', 'fnlwgt', 'capital-gain', 'capital-loss', 'hours-per-week']\n",
    "\n",
    "con_header = ['Count', '% Miss', 'Card.', 'Min.', '1st Qrt.', 'Mean', 'Median', '3rd Qrt.', 'Max.', 'Std. Dev.']\n",
    "\n",
    "con_df = pd.DataFrame(index=con_feat, columns=con_header)\n",
    "con_df.index.name = 'FEATURENAME'\n",
    "columns = df[con_feat]\n",
    "\n",
    "\n",
    "#Max\n",
    "con_df[con_header[8]] = columns.max()\n",
    "\n",
    "#Min\n",
    "con_df[con_header[3]] = columns.min()\n",
    "\n",
    "#Standard deviation (Standard Deviation is rounded to 1 like in notes)\n",
    "con_df[con_header[9]] = round(columns.std(), 1)\n",
    "\n",
    "#Count\n",
    "con_df[con_header[0]] = columns.count()\n",
    "\n",
    "#First quartile\n",
    "con_df[con_header[4]] = columns.quantile(0.25)\n",
    "\n",
    "#Third quartile\n",
    "con_df[con_header[7]] = columns.quantile(0.75)\n",
    "\n",
    "#Cardinality\n",
    "con_df[con_header[2]] = columns.nunique()\n",
    "\n",
    "#Mean (Mean is rounded to 1 like in notes)\n",
    "con_df[con_header[5]] = round(columns.mean(), 1)\n",
    "\n",
    "#Median\n",
    "con_df[con_header[6]] = columns.median()\n",
    "\n",
    "#Percentage of values missing\n",
    "missing = columns.isna()\n",
    "con_df[con_header[1]] = missing.sum() / len(columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Automate for all Categorical Features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "cat_feat = ['workclass', 'education', 'education-num', 'marital-status', 'occupation', 'relationship', 'race', 'sex', 'native-country', 'target']\n",
    "\n",
    "cat_header = ['Count', '% Miss', 'Card.', 'Mode', 'Mode Freq.', 'Mode %', '2nd Mode', '2nd Mode Freq.', '2nd Mode %']\n",
    "\n",
    "cat_df = pd.DataFrame(index=cat_feat, columns=cat_header)\n",
    "cat_df.index.name = 'FEATURENAME'\n",
    "columns = df[cat_feat]\n",
    "\n",
    "\n",
    "#Count\n",
    "cat_df[cat_header[0]] = columns.count()\n",
    "\n",
    "#Used later in the for loop to get mode %\n",
    "count = columns.count()\n",
    "\n",
    "#Percentage of values missing\n",
    "missing = columns.isna()\n",
    "cat_df[cat_header[1]] = round((missing.sum() / len(columns)) * 100, 1)\n",
    "\n",
    "#Cardinality\n",
    "cat_df[cat_header[2]] = columns.nunique()\n",
    "\n",
    "#Preparing arrays to store data, multiply by amount to give the arrays the correct length\n",
    "amount = len(cat_feat)\n",
    "mode_f = [''] * amount\n",
    "modes = [''] * amount\n",
    "modes_2 = [''] * amount\n",
    "mode_p = [''] * amount\n",
    "mode_f2 = [''] * amount\n",
    "mode_p2 = [''] * amount\n",
    "    \n",
    "#Have to use for loop for modes as the index was not being handled correctly in my original idea\n",
    "for col in columns:\n",
    "    values = columns[col].value_counts()\n",
    "    index = cat_feat.index(col)\n",
    "    \n",
    "    #Mode\n",
    "    mode = values.index[0]\n",
    "    modes[index] = mode\n",
    "\n",
    "    #Mode Freq.\n",
    "    mode_count = values.loc[mode]\n",
    "    mode_f[index] = mode_count\n",
    "    \n",
    "    #Mode %\n",
    "    mode_per = (mode_count/(count[index])) * 100\n",
    "    mode_p[index] = round(mode_per, 1)\n",
    "    \n",
    "    #Mode 2\n",
    "    mode_2 = values.index[1]\n",
    "    modes_2[index] = mode_2\n",
    "    \n",
    "    #Mode 2 Freq.\n",
    "    mode_count2 = values.loc[mode_2]\n",
    "    mode_f2[index] = mode_count2\n",
    "    \n",
    "    #Mode 2 %\n",
    "    mode_per2 = (mode_count2/(count[index]))*100\n",
    "    mode_p2[index] = round(mode_per2, 1)\n",
    "    \n",
    "#assign the values \n",
    "cat_df[cat_header[3]] = modes\n",
    "cat_df[cat_header[4]] = mode_f\n",
    "cat_df[cat_header[5]] = mode_p\n",
    "cat_df[cat_header[6]] = modes_2\n",
    "cat_df[cat_header[7]] = mode_f2\n",
    "cat_df[cat_header[8]] = mode_p2\n",
    "\n",
    "# Original idea, needed to do a loop to complete correctly\n",
    "#Mode\n",
    "#cat_df[cat_header[3]] = columns.value_counts().index[0]\n",
    "#print(columns.value_counts().index[0])\n",
    "#Mode Freq.\n",
    "#cat_df[cat_header[4]] = columns.value_counts()[0]\n",
    "#print(columns.value_counts()[0])\n",
    "#Mode %\n",
    "#2nd Mode\n",
    "#cat_df[cat_header[6]] = columns.value_counts().index[1]\n",
    "#print(columns.value_counts().index[1])\n",
    "#2nd Mode Freq.\n",
    "#cat_df[cat_header[7]] = columns.value_counts()[1]\n",
    "#print (columns.value_counts()[1])\n",
    "#2nd Mode %"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "con_df.to_csv(\"./C17433026CONT.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "cat_df.to_csv(\"./C17433026CAT.csv\")"
   ]
  }
 ],
 "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.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}