Data Cleaning Tutorial - Real Python.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from functools import reduce"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cleaning specific columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "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>Identifier</th>\n",
       "      <th>Edition Statement</th>\n",
       "      <th>Place of Publication</th>\n",
       "      <th>Date of Publication</th>\n",
       "      <th>Publisher</th>\n",
       "      <th>Title</th>\n",
       "      <th>Author</th>\n",
       "      <th>Contributors</th>\n",
       "      <th>Corporate Author</th>\n",
       "      <th>Corporate Contributors</th>\n",
       "      <th>Former owner</th>\n",
       "      <th>Engraver</th>\n",
       "      <th>Issuance type</th>\n",
       "      <th>Flickr URL</th>\n",
       "      <th>Shelfmarks</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>206</td>\n",
       "      <td>NaN</td>\n",
       "      <td>London</td>\n",
       "      <td>1879 [1878]</td>\n",
       "      <td>S. Tinsley &amp; Co.</td>\n",
       "      <td>Walter Forbes. [A novel.] By A. A</td>\n",
       "      <td>A. A.</td>\n",
       "      <td>FORBES, Walter.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>monographic</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "      <td>British Library HMNTS 12641.b.30.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>216</td>\n",
       "      <td>NaN</td>\n",
       "      <td>London; Virtue &amp; Yorston</td>\n",
       "      <td>1868</td>\n",
       "      <td>Virtue &amp; Co.</td>\n",
       "      <td>All for Greed. [A novel. The dedication signed...</td>\n",
       "      <td>A., A. A.</td>\n",
       "      <td>BLAZE DE BURY, Marie Pauline Rose - Baroness</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>monographic</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "      <td>British Library HMNTS 12626.cc.2.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>218</td>\n",
       "      <td>NaN</td>\n",
       "      <td>London</td>\n",
       "      <td>1869</td>\n",
       "      <td>Bradbury, Evans &amp; Co.</td>\n",
       "      <td>Love the Avenger. By the author of “All for Gr...</td>\n",
       "      <td>A., A. A.</td>\n",
       "      <td>BLAZE DE BURY, Marie Pauline Rose - Baroness</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>monographic</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "      <td>British Library HMNTS 12625.dd.1.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>472</td>\n",
       "      <td>NaN</td>\n",
       "      <td>London</td>\n",
       "      <td>1851</td>\n",
       "      <td>James Darling</td>\n",
       "      <td>Welsh Sketches, chiefly ecclesiastical, to the...</td>\n",
       "      <td>A., E. S.</td>\n",
       "      <td>Appleyard, Ernest Silvanus.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>monographic</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "      <td>British Library HMNTS 10369.bbb.15.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>480</td>\n",
       "      <td>A new edition, revised, etc.</td>\n",
       "      <td>London</td>\n",
       "      <td>1857</td>\n",
       "      <td>Wertheim &amp; Macintosh</td>\n",
       "      <td>[The World in which I live, and my place in it...</td>\n",
       "      <td>A., E. S.</td>\n",
       "      <td>BROOME, John Henry.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>monographic</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "      <td>British Library HMNTS 9007.d.28.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Identifier             Edition Statement      Place of Publication  \\\n",
       "0         206                           NaN                    London   \n",
       "1         216                           NaN  London; Virtue & Yorston   \n",
       "2         218                           NaN                    London   \n",
       "3         472                           NaN                    London   \n",
       "4         480  A new edition, revised, etc.                    London   \n",
       "\n",
       "  Date of Publication              Publisher  \\\n",
       "0         1879 [1878]       S. Tinsley & Co.   \n",
       "1                1868           Virtue & Co.   \n",
       "2                1869  Bradbury, Evans & Co.   \n",
       "3                1851          James Darling   \n",
       "4                1857   Wertheim & Macintosh   \n",
       "\n",
       "                                               Title     Author  \\\n",
       "0                  Walter Forbes. [A novel.] By A. A      A. A.   \n",
       "1  All for Greed. [A novel. The dedication signed...  A., A. A.   \n",
       "2  Love the Avenger. By the author of “All for Gr...  A., A. A.   \n",
       "3  Welsh Sketches, chiefly ecclesiastical, to the...  A., E. S.   \n",
       "4  [The World in which I live, and my place in it...  A., E. S.   \n",
       "\n",
       "                                   Contributors  Corporate Author  \\\n",
       "0                               FORBES, Walter.               NaN   \n",
       "1  BLAZE DE BURY, Marie Pauline Rose - Baroness               NaN   \n",
       "2  BLAZE DE BURY, Marie Pauline Rose - Baroness               NaN   \n",
       "3                   Appleyard, Ernest Silvanus.               NaN   \n",
       "4                           BROOME, John Henry.               NaN   \n",
       "\n",
       "   Corporate Contributors Former owner  Engraver Issuance type  \\\n",
       "0                     NaN          NaN       NaN   monographic   \n",
       "1                     NaN          NaN       NaN   monographic   \n",
       "2                     NaN          NaN       NaN   monographic   \n",
       "3                     NaN          NaN       NaN   monographic   \n",
       "4                     NaN          NaN       NaN   monographic   \n",
       "\n",
       "                                          Flickr URL  \\\n",
       "0  http://www.flickr.com/photos/britishlibrary/ta...   \n",
       "1  http://www.flickr.com/photos/britishlibrary/ta...   \n",
       "2  http://www.flickr.com/photos/britishlibrary/ta...   \n",
       "3  http://www.flickr.com/photos/britishlibrary/ta...   \n",
       "4  http://www.flickr.com/photos/britishlibrary/ta...   \n",
       "\n",
       "                            Shelfmarks  \n",
       "0    British Library HMNTS 12641.b.30.  \n",
       "1    British Library HMNTS 12626.cc.2.  \n",
       "2    British Library HMNTS 12625.dd.1.  \n",
       "3  British Library HMNTS 10369.bbb.15.  \n",
       "4     British Library HMNTS 9007.d.28.  "
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('Datasets\\BL-Flickr-Images-Book.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "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>Place of Publication</th>\n",
       "      <th>Date of Publication</th>\n",
       "      <th>Publisher</th>\n",
       "      <th>Title</th>\n",
       "      <th>Author</th>\n",
       "      <th>Flickr URL</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Identifier</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>London</td>\n",
       "      <td>1879 [1878]</td>\n",
       "      <td>S. Tinsley &amp; Co.</td>\n",
       "      <td>Walter Forbes. [A novel.] By A. A</td>\n",
       "      <td>A. A.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>London; Virtue &amp; Yorston</td>\n",
       "      <td>1868</td>\n",
       "      <td>Virtue &amp; Co.</td>\n",
       "      <td>All for Greed. [A novel. The dedication signed...</td>\n",
       "      <td>A., A. A.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td>London</td>\n",
       "      <td>1869</td>\n",
       "      <td>Bradbury, Evans &amp; Co.</td>\n",
       "      <td>Love the Avenger. By the author of “All for Gr...</td>\n",
       "      <td>A., A. A.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>London</td>\n",
       "      <td>1851</td>\n",
       "      <td>James Darling</td>\n",
       "      <td>Welsh Sketches, chiefly ecclesiastical, to the...</td>\n",
       "      <td>A., E. S.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>480</th>\n",
       "      <td>London</td>\n",
       "      <td>1857</td>\n",
       "      <td>Wertheim &amp; Macintosh</td>\n",
       "      <td>[The World in which I live, and my place in it...</td>\n",
       "      <td>A., E. S.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Place of Publication Date of Publication  \\\n",
       "Identifier                                                 \n",
       "206                           London         1879 [1878]   \n",
       "216         London; Virtue & Yorston                1868   \n",
       "218                           London                1869   \n",
       "472                           London                1851   \n",
       "480                           London                1857   \n",
       "\n",
       "                        Publisher  \\\n",
       "Identifier                          \n",
       "206              S. Tinsley & Co.   \n",
       "216                  Virtue & Co.   \n",
       "218         Bradbury, Evans & Co.   \n",
       "472                 James Darling   \n",
       "480          Wertheim & Macintosh   \n",
       "\n",
       "                                                        Title     Author  \\\n",
       "Identifier                                                                 \n",
       "206                         Walter Forbes. [A novel.] By A. A      A. A.   \n",
       "216         All for Greed. [A novel. The dedication signed...  A., A. A.   \n",
       "218         Love the Avenger. By the author of “All for Gr...  A., A. A.   \n",
       "472         Welsh Sketches, chiefly ecclesiastical, to the...  A., E. S.   \n",
       "480         [The World in which I live, and my place in it...  A., E. S.   \n",
       "\n",
       "                                                   Flickr URL  \n",
       "Identifier                                                     \n",
       "206         http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "216         http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "218         http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "472         http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "480         http://www.flickr.com/photos/britishlibrary/ta...  "
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "to_drop = ['Edition Statement',\n",
    "           'Corporate Author',\n",
    "           'Corporate Contributors',\n",
    "           'Former owner',\n",
    "           'Engraver',\n",
    "           'Contributors',\n",
    "           'Issuance type',\n",
    "           'Shelfmarks']\n",
    "\n",
    "df.drop(to_drop, inplace = True, axis = 1)\n",
    "df = df.set_index('Identifier')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [],
   "source": [
    "unwanted_characters = ['[', ',', '-']\n",
    "\n",
    "def clean_dates(item):\n",
    "    dop= str(item.loc['Date of Publication'])\n",
    "    \n",
    "    if dop == 'nan' or dop[0] == '[':\n",
    "        return np.NaN\n",
    "    \n",
    "    for character in unwanted_characters:\n",
    "        if character in dop:\n",
    "            character_index = dop.find(character)\n",
    "            dop = dop[:character_index]\n",
    "    \n",
    "    return dop\n",
    "\n",
    "df['Date of Publication'] = df.apply(clean_dates, axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "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>Place of Publication</th>\n",
       "      <th>Date of Publication</th>\n",
       "      <th>Publisher</th>\n",
       "      <th>Title</th>\n",
       "      <th>Author</th>\n",
       "      <th>Flickr URL</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Identifier</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>London</td>\n",
       "      <td>1879</td>\n",
       "      <td>S. Tinsley &amp; Co.</td>\n",
       "      <td>Walter Forbes. [A novel.] By A. A</td>\n",
       "      <td>A. A.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>London; Virtue &amp; Yorston</td>\n",
       "      <td>1868</td>\n",
       "      <td>Virtue &amp; Co.</td>\n",
       "      <td>All for Greed. [A novel. The dedication signed...</td>\n",
       "      <td>A., A. A.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td>London</td>\n",
       "      <td>1869</td>\n",
       "      <td>Bradbury, Evans &amp; Co.</td>\n",
       "      <td>Love the Avenger. By the author of “All for Gr...</td>\n",
       "      <td>A., A. A.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>London</td>\n",
       "      <td>1851</td>\n",
       "      <td>James Darling</td>\n",
       "      <td>Welsh Sketches, chiefly ecclesiastical, to the...</td>\n",
       "      <td>A., E. S.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>480</th>\n",
       "      <td>London</td>\n",
       "      <td>1857</td>\n",
       "      <td>Wertheim &amp; Macintosh</td>\n",
       "      <td>[The World in which I live, and my place in it...</td>\n",
       "      <td>A., E. S.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Place of Publication Date of Publication  \\\n",
       "Identifier                                                 \n",
       "206                           London               1879    \n",
       "216         London; Virtue & Yorston                1868   \n",
       "218                           London                1869   \n",
       "472                           London                1851   \n",
       "480                           London                1857   \n",
       "\n",
       "                        Publisher  \\\n",
       "Identifier                          \n",
       "206              S. Tinsley & Co.   \n",
       "216                  Virtue & Co.   \n",
       "218         Bradbury, Evans & Co.   \n",
       "472                 James Darling   \n",
       "480          Wertheim & Macintosh   \n",
       "\n",
       "                                                        Title     Author  \\\n",
       "Identifier                                                                 \n",
       "206                         Walter Forbes. [A novel.] By A. A      A. A.   \n",
       "216         All for Greed. [A novel. The dedication signed...  A., A. A.   \n",
       "218         Love the Avenger. By the author of “All for Gr...  A., A. A.   \n",
       "472         Welsh Sketches, chiefly ecclesiastical, to the...  A., E. S.   \n",
       "480         [The World in which I live, and my place in it...  A., E. S.   \n",
       "\n",
       "                                                   Flickr URL  \n",
       "Identifier                                                     \n",
       "206         http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "216         http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "218         http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "472         http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "480         http://www.flickr.com/photos/britishlibrary/ta...  "
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_author_names(item):\n",
    "    \n",
    "    author = str(item.loc['Author'])\n",
    "    \n",
    "    if author == 'nan':\n",
    "        return np.NaN\n",
    "    \n",
    "    author = author.split(',')\n",
    "\n",
    "    if len(author) == 1:\n",
    "        name = filter(lambda x: x.isalpha(), author[0])\n",
    "        return reduce(lambda x, y: x + y, name)\n",
    "    \n",
    "    last_name, first_name = author[0], author[1]\n",
    "\n",
    "    first_name = first_name[:first_name.find('-')] if '-' in first_name else first_name\n",
    "    \n",
    "    if first_name.endswith(('.', '.|')):\n",
    "        parts = first_name.split('.')\n",
    "        \n",
    "        if len(parts) > 1:\n",
    "            first_occurence = first_name.find('.')\n",
    "            final_occurence = first_name.find('.', first_occurence + 1)\n",
    "            first_name = first_name[:final_occurence]\n",
    "        else:\n",
    "            first_name = first_name[:first_name.find('.')]\n",
    "    \n",
    "    last_name = last_name.capitalize()\n",
    "    \n",
    "    return f'{first_name} {last_name}'\n",
    "\n",
    "\n",
    "df['Author'] = df.apply(clean_author_names, axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "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>Place of Publication</th>\n",
       "      <th>Date of Publication</th>\n",
       "      <th>Publisher</th>\n",
       "      <th>Title</th>\n",
       "      <th>Author</th>\n",
       "      <th>Flickr URL</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Identifier</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>London</td>\n",
       "      <td>1879</td>\n",
       "      <td>S. Tinsley &amp; Co.</td>\n",
       "      <td>Walter Forbes. [A novel.] By A. A</td>\n",
       "      <td>AA</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>London; Virtue &amp; Yorston</td>\n",
       "      <td>1868</td>\n",
       "      <td>Virtue &amp; Co.</td>\n",
       "      <td>All for Greed. [A novel. The dedication signed...</td>\n",
       "      <td>A. A A.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td>London</td>\n",
       "      <td>1869</td>\n",
       "      <td>Bradbury, Evans &amp; Co.</td>\n",
       "      <td>Love the Avenger. By the author of “All for Gr...</td>\n",
       "      <td>A. A A.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>London</td>\n",
       "      <td>1851</td>\n",
       "      <td>James Darling</td>\n",
       "      <td>Welsh Sketches, chiefly ecclesiastical, to the...</td>\n",
       "      <td>E. S A.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>480</th>\n",
       "      <td>London</td>\n",
       "      <td>1857</td>\n",
       "      <td>Wertheim &amp; Macintosh</td>\n",
       "      <td>[The World in which I live, and my place in it...</td>\n",
       "      <td>E. S A.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Place of Publication Date of Publication  \\\n",
       "Identifier                                                 \n",
       "206                           London               1879    \n",
       "216         London; Virtue & Yorston                1868   \n",
       "218                           London                1869   \n",
       "472                           London                1851   \n",
       "480                           London                1857   \n",
       "\n",
       "                        Publisher  \\\n",
       "Identifier                          \n",
       "206              S. Tinsley & Co.   \n",
       "216                  Virtue & Co.   \n",
       "218         Bradbury, Evans & Co.   \n",
       "472                 James Darling   \n",
       "480          Wertheim & Macintosh   \n",
       "\n",
       "                                                        Title    Author  \\\n",
       "Identifier                                                                \n",
       "206                         Walter Forbes. [A novel.] By A. A        AA   \n",
       "216         All for Greed. [A novel. The dedication signed...   A. A A.   \n",
       "218         Love the Avenger. By the author of “All for Gr...   A. A A.   \n",
       "472         Welsh Sketches, chiefly ecclesiastical, to the...   E. S A.   \n",
       "480         [The World in which I live, and my place in it...   E. S A.   \n",
       "\n",
       "                                                   Flickr URL  \n",
       "Identifier                                                     \n",
       "206         http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "216         http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "218         http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "472         http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "480         http://www.flickr.com/photos/britishlibrary/ta...  "
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_publication_place(item):\n",
    "    place = str(item['Place of Publication'])\n",
    "    \n",
    "    if 'London' in place:\n",
    "        return 'London'\n",
    "    if 'Oxford' in place:\n",
    "        return 'Oxford'\n",
    "    if place == 'Newcastle upon Tyne':\n",
    "        return 'Newcastle-upon-Tyne'\n",
    "    \n",
    "    return place\n",
    "    \n",
    "df['Place of Publication'] = df.apply(clean_publication_place, axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "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>Place of Publication</th>\n",
       "      <th>Date of Publication</th>\n",
       "      <th>Publisher</th>\n",
       "      <th>Title</th>\n",
       "      <th>Author</th>\n",
       "      <th>Flickr URL</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Identifier</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>London</td>\n",
       "      <td>1879</td>\n",
       "      <td>S. Tinsley &amp; Co.</td>\n",
       "      <td>Walter Forbes. [A novel.] By A. A</td>\n",
       "      <td>AA</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>London</td>\n",
       "      <td>1868</td>\n",
       "      <td>Virtue &amp; Co.</td>\n",
       "      <td>All for Greed. [A novel. The dedication signed...</td>\n",
       "      <td>A. A A.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td>London</td>\n",
       "      <td>1869</td>\n",
       "      <td>Bradbury, Evans &amp; Co.</td>\n",
       "      <td>Love the Avenger. By the author of “All for Gr...</td>\n",
       "      <td>A. A A.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>London</td>\n",
       "      <td>1851</td>\n",
       "      <td>James Darling</td>\n",
       "      <td>Welsh Sketches, chiefly ecclesiastical, to the...</td>\n",
       "      <td>E. S A.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>480</th>\n",
       "      <td>London</td>\n",
       "      <td>1857</td>\n",
       "      <td>Wertheim &amp; Macintosh</td>\n",
       "      <td>[The World in which I live, and my place in it...</td>\n",
       "      <td>E. S A.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Place of Publication Date of Publication              Publisher  \\\n",
       "Identifier                                                                   \n",
       "206                      London               1879        S. Tinsley & Co.   \n",
       "216                      London                1868           Virtue & Co.   \n",
       "218                      London                1869  Bradbury, Evans & Co.   \n",
       "472                      London                1851          James Darling   \n",
       "480                      London                1857   Wertheim & Macintosh   \n",
       "\n",
       "                                                        Title    Author  \\\n",
       "Identifier                                                                \n",
       "206                         Walter Forbes. [A novel.] By A. A        AA   \n",
       "216         All for Greed. [A novel. The dedication signed...   A. A A.   \n",
       "218         Love the Avenger. By the author of “All for Gr...   A. A A.   \n",
       "472         Welsh Sketches, chiefly ecclesiastical, to the...   E. S A.   \n",
       "480         [The World in which I live, and my place in it...   E. S A.   \n",
       "\n",
       "                                                   Flickr URL  \n",
       "Identifier                                                     \n",
       "206         http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "216         http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "218         http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "472         http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "480         http://www.flickr.com/photos/britishlibrary/ta...  "
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_title(item):\n",
    "    title = str(item['Title'])\n",
    "    \n",
    "    if title == 'nan':\n",
    "        return np.NaN\n",
    "    \n",
    "    if title[0] == '[':\n",
    "        title = title[1: title.find(']')]\n",
    "        \n",
    "    if 'by' in title:\n",
    "        title = title[:title.find('by')]\n",
    "    elif 'By' in title:\n",
    "        title = title[:title.find('By')]\n",
    "        \n",
    "    if '[' in title:\n",
    "        title = title[:title.find('[')]\n",
    "\n",
    "    title = title[:-2]\n",
    "        \n",
    "    title = list(map(str.capitalize, title.split()))\n",
    "    return ' '.join(title)\n",
    "    \n",
    "df['Title'] = df.apply(clean_title, axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "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>Place of Publication</th>\n",
       "      <th>Date of Publication</th>\n",
       "      <th>Publisher</th>\n",
       "      <th>Title</th>\n",
       "      <th>Author</th>\n",
       "      <th>Flickr URL</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Identifier</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>London</td>\n",
       "      <td>1879</td>\n",
       "      <td>S. Tinsley &amp; Co.</td>\n",
       "      <td>Walter Forbes</td>\n",
       "      <td>AA</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>London</td>\n",
       "      <td>1868</td>\n",
       "      <td>Virtue &amp; Co.</td>\n",
       "      <td>All For Greed</td>\n",
       "      <td>A. A A.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td>London</td>\n",
       "      <td>1869</td>\n",
       "      <td>Bradbury, Evans &amp; Co.</td>\n",
       "      <td>Love The Avenger</td>\n",
       "      <td>A. A A.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>London</td>\n",
       "      <td>1851</td>\n",
       "      <td>James Darling</td>\n",
       "      <td>Welsh Sketches, Chiefly Ecclesiastical, To The...</td>\n",
       "      <td>E. S A.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>480</th>\n",
       "      <td>London</td>\n",
       "      <td>1857</td>\n",
       "      <td>Wertheim &amp; Macintosh</td>\n",
       "      <td>The World In Which I Live, And My Place In It</td>\n",
       "      <td>E. S A.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Place of Publication Date of Publication              Publisher  \\\n",
       "Identifier                                                                   \n",
       "206                      London               1879        S. Tinsley & Co.   \n",
       "216                      London                1868           Virtue & Co.   \n",
       "218                      London                1869  Bradbury, Evans & Co.   \n",
       "472                      London                1851          James Darling   \n",
       "480                      London                1857   Wertheim & Macintosh   \n",
       "\n",
       "                                                        Title    Author  \\\n",
       "Identifier                                                                \n",
       "206                                             Walter Forbes        AA   \n",
       "216                                             All For Greed   A. A A.   \n",
       "218                                          Love The Avenger   A. A A.   \n",
       "472         Welsh Sketches, Chiefly Ecclesiastical, To The...   E. S A.   \n",
       "480             The World In Which I Live, And My Place In It   E. S A.   \n",
       "\n",
       "                                                   Flickr URL  \n",
       "Identifier                                                     \n",
       "206         http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "216         http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "218         http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "472         http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "480         http://www.flickr.com/photos/britishlibrary/ta...  "
      ]
     },
     "execution_count": 180,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cleaning entire dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "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>State</th>\n",
       "      <th>RegionName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama[edit]\\n</td>\n",
       "      <td>Auburn (Auburn University)[1]\\n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama[edit]\\n</td>\n",
       "      <td>Florence (University of North Alabama)\\n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alabama[edit]\\n</td>\n",
       "      <td>Jacksonville (Jacksonville State University)[2]\\n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alabama[edit]\\n</td>\n",
       "      <td>Livingston (University of West Alabama)[2]\\n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alabama[edit]\\n</td>\n",
       "      <td>Montevallo (University of Montevallo)[2]\\n</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             State                                         RegionName\n",
       "0  Alabama[edit]\\n                    Auburn (Auburn University)[1]\\n\n",
       "1  Alabama[edit]\\n           Florence (University of North Alabama)\\n\n",
       "2  Alabama[edit]\\n  Jacksonville (Jacksonville State University)[2]\\n\n",
       "3  Alabama[edit]\\n       Livingston (University of West Alabama)[2]\\n\n",
       "4  Alabama[edit]\\n         Montevallo (University of Montevallo)[2]\\n"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "university_towns = []\n",
    "\n",
    "with open('Datasets\\\\university_towns.txt', 'r') as file:\n",
    "    items = file.readlines()\n",
    "    states = list(filter(lambda x: '[edit]' in x, items))\n",
    "    \n",
    "    for index, state in enumerate(states):\n",
    "        start = items.index(state) + 1\n",
    "        if index == 49: #since 50 states\n",
    "            end = len(items)\n",
    "        else:\n",
    "            end = items.index(states[index + 1])\n",
    "            \n",
    "        pairs = map(lambda x: [state, x], items[start:end])\n",
    "        university_towns.extend(pairs)\n",
    "        \n",
    "towns_df = pd.DataFrame(university_towns, columns = ['State', 'RegionName'])\n",
    "towns_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "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>State</th>\n",
       "      <th>RegionName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Auburn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Florence</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Jacksonville</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Livingston</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Montevallo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     State    RegionName\n",
       "0  Alabama        Auburn\n",
       "1  Alabama      Florence\n",
       "2  Alabama  Jacksonville\n",
       "3  Alabama    Livingston\n",
       "4  Alabama    Montevallo"
      ]
     },
     "execution_count": 169,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def clean_up(item):\n",
    "    if '(' in item:\n",
    "        return item[:item.find('(') - 1]\n",
    "    \n",
    "    if '[' in item:\n",
    "        return item[:item.find('[')]\n",
    "    \n",
    "\n",
    "towns_df =  towns_df.applymap(clean_up)\n",
    "towns_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Renaming columns and skipping rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "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>Unnamed: 0</th>\n",
       "      <th>? Summer</th>\n",
       "      <th>01 !</th>\n",
       "      <th>02 !</th>\n",
       "      <th>03 !</th>\n",
       "      <th>Total</th>\n",
       "      <th>? Winter</th>\n",
       "      <th>01 !.1</th>\n",
       "      <th>02 !.1</th>\n",
       "      <th>03 !.1</th>\n",
       "      <th>Total.1</th>\n",
       "      <th>? Games</th>\n",
       "      <th>01 !.2</th>\n",
       "      <th>02 !.2</th>\n",
       "      <th>03 !.2</th>\n",
       "      <th>Combined total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan (AFG)</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria (ALG)</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Argentina (ARG)</td>\n",
       "      <td>23</td>\n",
       "      <td>18</td>\n",
       "      <td>24</td>\n",
       "      <td>28</td>\n",
       "      <td>70</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>41</td>\n",
       "      <td>18</td>\n",
       "      <td>24</td>\n",
       "      <td>28</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Armenia (ARM)</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Australasia (ANZ) [ANZ]</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Unnamed: 0  ? Summer  01 !  02 !  03 !  Total  ? Winter  \\\n",
       "0        Afghanistan (AFG)        13     0     0     2      2         0   \n",
       "1            Algeria (ALG)        12     5     2     8     15         3   \n",
       "2          Argentina (ARG)        23    18    24    28     70        18   \n",
       "3            Armenia (ARM)         5     1     2     9     12         6   \n",
       "4  Australasia (ANZ) [ANZ]         2     3     4     5     12         0   \n",
       "\n",
       "   01 !.1  02 !.1  03 !.1  Total.1  ? Games  01 !.2  02 !.2  03 !.2  \\\n",
       "0       0       0       0        0       13       0       0       2   \n",
       "1       0       0       0        0       15       5       2       8   \n",
       "2       0       0       0        0       41      18      24      28   \n",
       "3       0       0       0        0       11       1       2       9   \n",
       "4       0       0       0        0        2       3       4       5   \n",
       "\n",
       "   Combined total  \n",
       "0               2  \n",
       "1              15  \n",
       "2              70  \n",
       "3              12  \n",
       "4              12  "
      ]
     },
     "execution_count": 178,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "olympics_df = pd.read_csv('Datasets\\olympics.csv', skiprows = 1, header = 0)\n",
    "olympics_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "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>Country</th>\n",
       "      <th>Summer Olympics</th>\n",
       "      <th>Gold</th>\n",
       "      <th>Silver</th>\n",
       "      <th>Bronze</th>\n",
       "      <th>Total</th>\n",
       "      <th>Winter Olympics</th>\n",
       "      <th>Gold.1</th>\n",
       "      <th>Silver.1</th>\n",
       "      <th>Bronze.1</th>\n",
       "      <th>Total.1</th>\n",
       "      <th># Games</th>\n",
       "      <th>Gold.2</th>\n",
       "      <th>Silver.2</th>\n",
       "      <th>Bronze.2</th>\n",
       "      <th>Combined total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan (AFG)</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria (ALG)</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Argentina (ARG)</td>\n",
       "      <td>23</td>\n",
       "      <td>18</td>\n",
       "      <td>24</td>\n",
       "      <td>28</td>\n",
       "      <td>70</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>41</td>\n",
       "      <td>18</td>\n",
       "      <td>24</td>\n",
       "      <td>28</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Armenia (ARM)</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Australasia (ANZ) [ANZ]</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Country  Summer Olympics  Gold  Silver  Bronze  Total  \\\n",
       "0        Afghanistan (AFG)               13     0       0       2      2   \n",
       "1            Algeria (ALG)               12     5       2       8     15   \n",
       "2          Argentina (ARG)               23    18      24      28     70   \n",
       "3            Armenia (ARM)                5     1       2       9     12   \n",
       "4  Australasia (ANZ) [ANZ]                2     3       4       5     12   \n",
       "\n",
       "   Winter Olympics  Gold.1  Silver.1  Bronze.1  Total.1  # Games  Gold.2  \\\n",
       "0                0       0         0         0        0       13       0   \n",
       "1                3       0         0         0        0       15       5   \n",
       "2               18       0         0         0        0       41      18   \n",
       "3                6       0         0         0        0       11       1   \n",
       "4                0       0         0         0        0        2       3   \n",
       "\n",
       "   Silver.2  Bronze.2  Combined total  \n",
       "0         0         2               2  \n",
       "1         2         8              15  \n",
       "2        24        28              70  \n",
       "3         2         9              12  \n",
       "4         4         5              12  "
      ]
     },
     "execution_count": 179,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def get_new_column_name(column_name):\n",
    "    return {'Unnamed: 0': 'Country',\n",
    "           '? Summer': 'Summer Olympics',\n",
    "           '01 !': 'Gold',\n",
    "           '02 !': 'Silver',\n",
    "           '03 !': 'Bronze',\n",
    "           '? Winter': 'Winter Olympics',\n",
    "           '01 !.1': 'Gold.1',\n",
    "           '02 !.1': 'Silver.1',\n",
    "           '03 !.1': 'Bronze.1',\n",
    "           '? Games': '# Games', \n",
    "           '01 !.2': 'Gold.2',\n",
    "           '02 !.2': 'Silver.2',\n",
    "           '03 !.2': 'Bronze.2'}.get(column_name)\n",
    "\n",
    "for column in olympics_df:\n",
    "    new_name = get_new_column_name(column)\n",
    "    if new_name:\n",
    "        olympics_df.rename({column: new_name}, axis = 1, inplace = True)\n",
    "        \n",
    "olympics_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "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.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}