cleaning-data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "f8452e85-3312-4b49-942e-e413ba120d15",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "0e6cb79a-5987-42ec-86b8-e02bc4d7b2fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('Datasets/BL-Flickr-Images-Book.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "012ffd7f-b001-46f2-a6de-8782e9c22db4",
   "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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Identifier Edition Statement      Place of Publication Date of Publication  \\\n",
       "0         206               NaN                    London         1879 [1878]   \n",
       "1         216               NaN  London; Virtue & Yorston                1868   \n",
       "2         218               NaN                    London                1869   \n",
       "\n",
       "               Publisher                                              Title  \\\n",
       "0       S. Tinsley & Co.                  Walter Forbes. [A novel.] By A. A   \n",
       "1           Virtue & Co.  All for Greed. [A novel. The dedication signed...   \n",
       "2  Bradbury, Evans & Co.  Love the Avenger. By the author of “All for Gr...   \n",
       "\n",
       "      Author                                  Contributors  Corporate Author  \\\n",
       "0      A. A.                               FORBES, Walter.               NaN   \n",
       "1  A., A. A.  BLAZE DE BURY, Marie Pauline Rose - Baroness               NaN   \n",
       "2  A., A. A.  BLAZE DE BURY, Marie Pauline Rose - Baroness               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",
       "\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",
       "\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.  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "85845bab-226a-4509-9c54-6f38a07c275b",
   "metadata": {},
   "outputs": [],
   "source": [
    "to_drop = [\n",
    "    \"Edition Statement\",\n",
    "    \"Corporate Author\",\n",
    "    \"Corporate Contributors\",\n",
    "    \"Former owner\",\n",
    "    \"Engraver\",\n",
    "    \"Contributors\",\n",
    "    \"Issuance type\",\n",
    "    \"Shelfmarks\",\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "0193a99a-e88c-4bfc-b85c-45b4b0d31e6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(to_drop, inplace=True, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "a883a0fe-46de-40ff-bc58-a2016bcb8297",
   "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>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",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>206</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>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>216</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>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>218</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>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>472</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>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>480</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>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Identifier      Place of Publication Date of Publication  \\\n",
       "0         206                    London         1879 [1878]   \n",
       "1         216  London; Virtue & Yorston                1868   \n",
       "2         218                    London                1869   \n",
       "3         472                    London                1851   \n",
       "4         480                    London                1857   \n",
       "\n",
       "               Publisher                                              Title  \\\n",
       "0       S. Tinsley & Co.                  Walter Forbes. [A novel.] By A. A   \n",
       "1           Virtue & Co.  All for Greed. [A novel. The dedication signed...   \n",
       "2  Bradbury, Evans & Co.  Love the Avenger. By the author of “All for Gr...   \n",
       "3          James Darling  Welsh Sketches, chiefly ecclesiastical, to the...   \n",
       "4   Wertheim & Macintosh  [The World in which I live, and my place in it...   \n",
       "\n",
       "      Author                                         Flickr URL  \n",
       "0      A. A.  http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "1  A., A. A.  http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "2  A., A. A.  http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "3  A., E. S.  http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "4  A., E. S.  http://www.flickr.com/photos/britishlibrary/ta...  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "edd98439-a7b3-4833-8c93-9c29e29b0d65",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Identifier'].is_unique"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "fbc52e7d-680a-4ad9-bfa8-89c0623013b8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.set_index('Identifier')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "492ea3bb-cbd8-4804-9424-5f605d240a87",
   "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": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "e903dd88-3113-4d22-a0c4-a5a4c19e6162",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Place of Publication                                               London\n",
       "Date of Publication                                           1879 [1878]\n",
       "Publisher                                                S. Tinsley & Co.\n",
       "Title                                   Walter Forbes. [A novel.] By A. A\n",
       "Author                                                              A. A.\n",
       "Flickr URL              http://www.flickr.com/photos/britishlibrary/ta...\n",
       "Name: 206, dtype: object"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[206]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "80e4f226-d225-4ec0-ab96-b1b3dd8a3c06",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "object    6\n",
       "dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "d515bebb-bd47-484d-890d-ac77322c0f37",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Identifier\n",
       "1905           1888\n",
       "1929    1839, 38-54\n",
       "2836           1897\n",
       "2854           1865\n",
       "2956        1860-63\n",
       "2957           1873\n",
       "3017           1866\n",
       "3131           1899\n",
       "4598           1814\n",
       "4884           1820\n",
       "Name: Date of Publication, dtype: object"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[1905:, 'Date of Publication'].head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "4030a0df-41a7-4c1f-8282-9d426cb65d8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "extr = df['Date of Publication'].str.extract(r'^(\\d{4})', expand=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "73f02035-58ef-49ac-865d-fa98e4995aaa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Identifier\n",
       "206    1879\n",
       "216    1868\n",
       "218    1869\n",
       "472    1851\n",
       "480    1857\n",
       "Name: Date of Publication, dtype: object"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extr.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "44d262f8-0bb2-42c0-92b2-693013507e77",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Identifier\n",
       "206        1879.0\n",
       "216        1868.0\n",
       "218        1869.0\n",
       "472        1851.0\n",
       "480        1857.0\n",
       "            ...  \n",
       "4158088    1838.0\n",
       "4158128    1831.0\n",
       "4159563       NaN\n",
       "4159587    1834.0\n",
       "4160339    1834.0\n",
       "Name: Date of Publication, Length: 8287, dtype: float64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.to_numeric(extr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "fb636018-cf50-49c2-973d-e367da8dd987",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Date of Publication'] = pd.to_numeric(extr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "52a1adfd-058a-4308-9aea-179b0e16379d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('float64')"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Date of Publication'].dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "5f7610c2-52d3-4ff6-8482-533720ff5cee",
   "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>4117751</th>\n",
       "      <td>New York</td>\n",
       "      <td>1882.0</td>\n",
       "      <td>John Wiley &amp; Sons</td>\n",
       "      <td>Third Appendix to the fifth edition of Dana's ...</td>\n",
       "      <td>DANA, James Dwight.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4117752</th>\n",
       "      <td>London</td>\n",
       "      <td>1883.0</td>\n",
       "      <td>Trübner &amp; Co.</td>\n",
       "      <td>A System of Mineralogy ... Fifth edition, rewr...</td>\n",
       "      <td>DANA, James Dwight.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4156359</th>\n",
       "      <td>New York</td>\n",
       "      <td>1898.0</td>\n",
       "      <td>G. P. Putnam's Sons</td>\n",
       "      <td>History of the People of the Netherlands ... T...</td>\n",
       "      <td>BLOK, Pieter Johan.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4157746</th>\n",
       "      <td>London</td>\n",
       "      <td>1811.0</td>\n",
       "      <td>F. C. and J. Rivington</td>\n",
       "      <td>The New Chronicles of England and France, in t...</td>\n",
       "      <td>FABYAN, Robert.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4157862</th>\n",
       "      <td>Newcastle-upon-Tyne</td>\n",
       "      <td>1867.0</td>\n",
       "      <td>T. Fordyce</td>\n",
       "      <td>Local Records; or, Historical Register of rema...</td>\n",
       "      <td>FORDYCE, T. - Printer, of Newcastle-upon-Tyne</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4158088</th>\n",
       "      <td>London</td>\n",
       "      <td>1838.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>The Parochial History of Cornwall, founded on,...</td>\n",
       "      <td>GIDDY, afterwards GILBERT, Davies.</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4158128</th>\n",
       "      <td>Derby</td>\n",
       "      <td>1831.0</td>\n",
       "      <td>M. Mozley &amp; Son</td>\n",
       "      <td>The History and Gazetteer of the County of Der...</td>\n",
       "      <td>GLOVER, Stephen - of Derby</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4159563</th>\n",
       "      <td>London</td>\n",
       "      <td>NaN</td>\n",
       "      <td>T. Cadell and W. Davies</td>\n",
       "      <td>Magna Britannia; being a concise topographical...</td>\n",
       "      <td>LYSONS, Daniel - M.A., F.R.S., and LYSONS (Sam...</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4159587</th>\n",
       "      <td>Newcastle upon Tyne</td>\n",
       "      <td>1834.0</td>\n",
       "      <td>Mackenzie &amp; Dent</td>\n",
       "      <td>An historical, topographical and descriptive v...</td>\n",
       "      <td>Mackenzie, E. (Eneas)</td>\n",
       "      <td>http://www.flickr.com/photos/britishlibrary/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4160339</th>\n",
       "      <td>London</td>\n",
       "      <td>1834.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Collectanea Topographica et Genealogica. [Firs...</td>\n",
       "      <td>NaN</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",
       "4117751                New York               1882.0        John Wiley & Sons   \n",
       "4117752                  London               1883.0           Trübner & Co.   \n",
       "4156359                New York               1898.0      G. P. Putnam's Sons   \n",
       "4157746                  London               1811.0   F. C. and J. Rivington   \n",
       "4157862     Newcastle-upon-Tyne               1867.0               T. Fordyce   \n",
       "4158088                  London               1838.0                      NaN   \n",
       "4158128                   Derby               1831.0          M. Mozley & Son   \n",
       "4159563                  London                  NaN  T. Cadell and W. Davies   \n",
       "4159587     Newcastle upon Tyne               1834.0         Mackenzie & Dent   \n",
       "4160339                  London               1834.0                      NaN   \n",
       "\n",
       "                                                        Title  \\\n",
       "Identifier                                                      \n",
       "4117751     Third Appendix to the fifth edition of Dana's ...   \n",
       "4117752     A System of Mineralogy ... Fifth edition, rewr...   \n",
       "4156359     History of the People of the Netherlands ... T...   \n",
       "4157746     The New Chronicles of England and France, in t...   \n",
       "4157862     Local Records; or, Historical Register of rema...   \n",
       "4158088     The Parochial History of Cornwall, founded on,...   \n",
       "4158128     The History and Gazetteer of the County of Der...   \n",
       "4159563     Magna Britannia; being a concise topographical...   \n",
       "4159587     An historical, topographical and descriptive v...   \n",
       "4160339     Collectanea Topographica et Genealogica. [Firs...   \n",
       "\n",
       "                                                       Author  \\\n",
       "Identifier                                                      \n",
       "4117751                                   DANA, James Dwight.   \n",
       "4117752                                   DANA, James Dwight.   \n",
       "4156359                                   BLOK, Pieter Johan.   \n",
       "4157746                                       FABYAN, Robert.   \n",
       "4157862         FORDYCE, T. - Printer, of Newcastle-upon-Tyne   \n",
       "4158088                    GIDDY, afterwards GILBERT, Davies.   \n",
       "4158128                            GLOVER, Stephen - of Derby   \n",
       "4159563     LYSONS, Daniel - M.A., F.R.S., and LYSONS (Sam...   \n",
       "4159587                                 Mackenzie, E. (Eneas)   \n",
       "4160339                                                   NaN   \n",
       "\n",
       "                                                   Flickr URL  \n",
       "Identifier                                                     \n",
       "4117751     http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "4117752     http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "4156359     http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "4157746     http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "4157862     http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "4158088     http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "4158128     http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "4159563     http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "4159587     http://www.flickr.com/photos/britishlibrary/ta...  \n",
       "4160339     http://www.flickr.com/photos/britishlibrary/ta...  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "15296d2f-fe55-4179-92cd-4c04f3e47760",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.11717147339205986"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Date of Publication'].isnull().sum() / len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "712a6d19-28d4-4a6f-a441-55f5b19c0a72",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Identifier\n",
       "206                                  London\n",
       "216                London; Virtue & Yorston\n",
       "218                                  London\n",
       "472                                  London\n",
       "480                                  London\n",
       "481                                  London\n",
       "519                                  London\n",
       "667     pp. 40. G. Bryan & Co: Oxford, 1898\n",
       "874                                 London]\n",
       "1143                                 London\n",
       "Name: Place of Publication, dtype: object"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Place of Publication'].head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "0d5918a3-c1a9-447e-b075-4fa5202265d3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Place of Publication                                  Newcastle-upon-Tyne\n",
       "Date of Publication                                                1867.0\n",
       "Publisher                                                      T. Fordyce\n",
       "Title                   Local Records; or, Historical Register of rema...\n",
       "Author                      FORDYCE, T. - Printer, of Newcastle-upon-Tyne\n",
       "Flickr URL              http://www.flickr.com/photos/britishlibrary/ta...\n",
       "Name: 4157862, dtype: object"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[4157862]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "78acab5e-0d1d-4c14-87e3-eb79c70da7e7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Place of Publication                                  Newcastle upon Tyne\n",
       "Date of Publication                                                1834.0\n",
       "Publisher                                                Mackenzie & Dent\n",
       "Title                   An historical, topographical and descriptive v...\n",
       "Author                                              Mackenzie, E. (Eneas)\n",
       "Flickr URL              http://www.flickr.com/photos/britishlibrary/ta...\n",
       "Name: 4159587, dtype: object"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[4159587]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "68f04e2e-8d62-4498-be1f-c2797a3de0ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "pub = df['Place of Publication']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "fc3d5383-6305-4559-839f-1c0c0b205ca3",
   "metadata": {},
   "outputs": [],
   "source": [
    "london = pub.str.contains('London')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "9f70ece8-3983-4a52-93ad-d7d3428f73ee",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Identifier\n",
       "206    True\n",
       "216    True\n",
       "218    True\n",
       "472    True\n",
       "480    True\n",
       "Name: Place of Publication, dtype: bool"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "london[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "cd4a35c8-6906-42d5-b534-6c94bb472c1f",
   "metadata": {},
   "outputs": [],
   "source": [
    "oxford = pub.str.contains('Oxford')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "105747e3-0334-44fa-89d6-8e2eb3e3dda4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Identifier\n",
       "206        False\n",
       "216        False\n",
       "218        False\n",
       "472        False\n",
       "480        False\n",
       "           ...  \n",
       "4158088    False\n",
       "4158128    False\n",
       "4159563    False\n",
       "4159587    False\n",
       "4160339    False\n",
       "Name: Place of Publication, Length: 8287, dtype: bool"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "oxford"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "26bc95d8-fa2b-459c-b785-69a68c6b1cc8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Place of Publication'] = np.where(london, 'London',\n",
    "                                      np.where(oxford, 'Oxford',\n",
    "                                               pub.str.replace('-', ' ')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e494a2ff-0c4d-4c99-924e-fe3eb6d19584",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Place of Publication'].head(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "a9d8ae09-4cf0-4587-807d-2012bc1f0253",
   "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.0</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</td>\n",
       "      <td>1868.0</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.0</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.0</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.0</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              Publisher  \\\n",
       "Identifier                                                                    \n",
       "206                      London               1879.0       S. Tinsley & Co.   \n",
       "216                      London               1868.0           Virtue & Co.   \n",
       "218                      London               1869.0  Bradbury, Evans & Co.   \n",
       "472                      London               1851.0          James Darling   \n",
       "480                      London               1857.0   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": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "7c0e331c-2d32-4904-8061-57a4a1e47423",
   "metadata": {},
   "outputs": [],
   "source": [
    "university_towns = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "bef79b7d-1b24-4573-8532-fe5df03ba5cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('Datasets/university_towns.txt') as file:\n",
    "    for line in file:\n",
    "        if '[edit]' in line:\n",
    "            state = line\n",
    "        else:\n",
    "            university_towns.append((state, line))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "888726cb-426e-40ef-9993-92f82f9f5b4b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Alabama[edit]\\n', 'Auburn (Auburn University)[1]\\n'),\n",
       " ('Alabama[edit]\\n', 'Florence (University of North Alabama)\\n'),\n",
       " ('Alabama[edit]\\n', 'Jacksonville (Jacksonville State University)[2]\\n'),\n",
       " ('Alabama[edit]\\n', 'Livingston (University of West Alabama)[2]\\n'),\n",
       " ('Alabama[edit]\\n', 'Montevallo (University of Montevallo)[2]\\n')]"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "university_towns[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "7676a839-921a-4cac-b10e-69d27741bbef",
   "metadata": {},
   "outputs": [],
   "source": [
    "towns_df = pd.DataFrame(university_towns, columns=[\"State\", \"RegionName\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "d55c47b4-9fe5-4e97-86d0-fc71d3509e5f",
   "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": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "towns_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "d29f2a1d-c497-436e-bab4-280923300ae3",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_citystate(item):\n",
    "    if ' (' in item:\n",
    "        return item[:item.find(' (')]\n",
    "    elif '[' in item:\n",
    "        return item[:item.find('[')]\n",
    "    else:\n",
    "        return item"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "78ce17c1-53a7-4906-83a3-d6b33daae385",
   "metadata": {},
   "outputs": [],
   "source": [
    "towns_df = towns_df.applymap(get_citystate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "82b3fbc6-bb6f-4128-ae01-faf8fa36bf88",
   "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": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "towns_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "3e970016-f277-4d5d-9903-353ff356c482",
   "metadata": {},
   "outputs": [],
   "source": [
    "olympics_df = pd.read_csv('Datasets/olympics.csv', header=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "e9b42cd0-cac3-4dfb-afb5-97c1f2d82064",
   "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": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "olympics_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "15deb68c-7e84-4bf0-b509-25134aaa80ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "new_names =  {'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'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "d09ddde3-1c29-4cd1-b00a-bf82ac7fbfc8",
   "metadata": {},
   "outputs": [],
   "source": [
    "olympics_df.rename(columns=new_names, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "dbcf577b-5c07-4025-89b1-38d02ef408b2",
   "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": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "olympics_df.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}