Data_cleaning_with_np_and_pd.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the dataset. The file `BL-Flickr-Images-Book.csv` is a CSV file containing information about books from the British Library."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('BL-Flickr-Images-Book.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Inspect the columns list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Drop some columns that are not of interest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "to_drop = ['Edition Statement',\n",
    "           'Corporate Author', \n",
    "           'Corporate Contributors', \n",
    "           'Former owner', \n",
    "           'Engraver', \n",
    "           'Contributors', \n",
    "           'Issuance type', \n",
    "           'Shelfmarks']\n",
    "df.drop(to_drop, inplace=True, axis=1)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check if the `Identifier` column contains entirely unique values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Identifier'].is_unique"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set the index to be the `Identifier` column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.set_index('Identifier')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can retrieve columns by referencing the index using `.loc[ ... ]`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[206]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we look at one particular colum, `Date of Publication`, we can see that there are a few different ways that dates are expressed in the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[1905:, 'Date of Publication']  # this selects record ID 1905 to the end of the dataset, for the given column only"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use the regular expression `^(\\d{4})` to select only the publication year from the data. We can test it on a copy of just that column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "extr = df['Date of Publication'].str.extract(r'^(\\d{4})', expand=False)\n",
    "extr.loc[1905:]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we know that it works, we can reset the column contents using the cleaned version."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Date of Publication'] = df['Date of Publication'].str.extract(r'^(\\d{4})', expand=False)\n",
    "df.loc[1905:]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You probably noticed that there's a lot of null values (`NaN` - Not a Number) in the column. We can calcuate the percentage of null values by summing up the null values and dividing by the number of rows in the dataset and multiply by 100."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Date of Publication'].isnull().sum() / len(df) * 100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a look at another colum now, `Place of Publication`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Place of Publication'].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see, there are also problem with this column! By inspecting these 10 rows, we can see that places that are supposed to be London and Oxford might be formatted differently. Let's also inspect two specific records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[4157862]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[4159587]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see from these records that these two books were published in the same place, but one `Place of Publication` entry uses hyphens while the other does not.\n",
    "\n",
    "One strategy to fix all of these is to look for all instances where `London` apppears inside the text and replace it specifically with the normalised text as only `London`, and the same for `Oxford`. We can also try and clean up the hyphens by replacing those with a single space."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Place of Publication'] = np.where(df['Place of Publication'].str.contains('London'), 'London', \n",
    "                                 np.where(df['Place of Publication'].str.contains('Oxford'), 'Oxford', \n",
    "                                     df['Place of Publication'].str.replace('-', ' ')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At this point, we have cleaned things up quite nicely, but it is by no means complete. We can check all the different place labels used by extracting a list of the unique values of that column. When we do this, we see that there is actually quite a lot more work needed to completely clean up that column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.Series(df['Place of Publication'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a look at a different example file to clean up, `university_towns.txt`. Note that this is a text file with lines of text, and not a CSV file representing a table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "uni_towns = open('university_towns.txt')\n",
    "uni_towns.readlines()[:20]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What we can see from looking at the first 20 lines of the file is that we have section labels that are names of US states with the text `[edit]` attached to it, followed by a list of names of university towns with the names of the universities that are situated in each of those towns in parentheses. We can take advantage of this pattern to preprocess the file into a CSV format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "uni_towns = []\n",
    "with open('university_towns.txt') as file:\n",
    "    for line in file:\n",
    "        if '[edit]' in line:\n",
    "            state = line\n",
    "        else:\n",
    "            uni_towns.append((state, line))\n",
    "uni_towns[:20]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can now load this into a DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "uni_towns_df = pd.DataFrame(uni_towns, columns=['State', 'RegionName'])\n",
    "uni_towns_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see now that the cells in our DataFrame need a lot more cleaning up. We could have done this in the loop we used above, but the Pandas library makes it very easy using the `applymap()` function.\n",
    "\n",
    "First we define a function that can independently clean up one cell's contents."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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": "markdown",
   "metadata": {},
   "source": [
    "Let's test this on a couple of example strings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "get_citystate('Wyoming[edit]\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "get_citystate('Eau Claire (University of Wisconsin–Eau Claire)\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With `applymap()` we can pass the function name so that it runs the function on all cells in the DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "uni_towns_df = uni_towns_df.applymap(get_citystate)\n",
    "uni_towns_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Somtimes datasets that you work with will either have column names that are not easy to understand, or unimportant information in the first or last few rows, for example definitions of terms in the dataset or some footnotes. In these cases we can rename columns or drop certain rows.\n",
    "\n",
    "Let's take a look at another example dataset in `olympics.csv`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "uni_towns = open('olympics.csv')\n",
    "uni_towns.readlines()[:20]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It looks like we can load this into a DataFrame straight away, so we will do that."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "olympics_df = pd.read_csv('olympics.csv')\n",
    "olympics_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that the top of this dataset is definitely messy! The first row seems to be an index of the columns (0, 1, 2, 3, etc.) and then the row that should be used as the column names looks like when it was written to disk that the special characters were not rendered correctly, and requires renaming across them.\n",
    "\n",
    "Pandas gives us an easy way to set the correct header. When we use `read_csv()` we can provide an extra parameter to tell it which row to use as the header."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "olympics_df = pd.read_csv('olympics.csv', header=1)\n",
    "olympics_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we can rename the column headers using the DataFrame's `rename()` method. This function allows us to rename any axis based on a mapping, which is represented using a Python dictionary (a `dict` datatype)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "name_mapping = {\n",
    "    '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'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "olympics_df = olympics_df.rename(columns=name_mapping)\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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}