Clean dataset.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Clean dataset\n",
    "\n",
    "In the previous notebook, we used Web Scraping to collect information from Wikipedia webpages and combine them together in a .CSV file named 'Dataset'. However, this dataset is not available in the most useful format and must be cleaned before any analysis could be performed. Such a scenario is expected to occur as the web pages aren't always designed to make extracting data easy.\n",
    "\n",
    "Here, we'll understand the dataset columns and try to remove any unnecessary strings and values that bring no value to the dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import libraries and read CSV file\n",
    "\n",
    "We'll first import the Pandas library which enables us to manipulate and work with the dataset. Then, we'll simply read the dataset file we created."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import re\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "dataset = pd.read_csv(\"Dataset.csv\")\n",
    "dataset.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Replace the headings\n",
    "\n",
    "We first begin by replacing the headings of the columns such that they reflect the data in them better. We change the text for the first column to 'Country', the third column to 'Percentage of World Population' and fourth column to 'Total Area (km2)'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "dataset.rename(columns={'Country(or dependent territory)': 'Country'}, inplace = True)\n",
    "dataset.rename(columns={'% of worldpopulation': 'Percentage of World Population'}, inplace = True)\n",
    "dataset.rename(columns={'Total Area': 'Total Area (km2)'}, inplace = True)\n",
    "dataset.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analysing the dataset\n",
    "\n",
    "We see that almost all columns have cells which have data inside parentheses and square brackets which is not required. Thus, we can first remove all paranthesis, square brackets and the content inside them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "for column in dataset.columns:\n",
    "    dataset[column] = dataset[column].str.replace(r\"\\(.*\\)\", \"\")\n",
    "    dataset[column] = dataset[column].str.replace(r\"\\[.*\\]\", \"\")\n",
    "\n",
    "dataset.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we do not need '%' sign in either column 3 or 5, thus, we can strip the cells of it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "dataset['Percentage of World Population'] = dataset['Percentage of World Population'].str.strip('%')\n",
    "dataset['Percentage Water'] = dataset['Percentage Water'].str.strip('%')\n",
    "dataset['Percentage Water'] = dataset['Percentage Water'].str.strip()\n",
    "\n",
    "dataset.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we remove commas from Population column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "dataset['Population'] = dataset['Population'].str.replace(',', '')\n",
    "\n",
    "dataset.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we will explore the area column. Initially, we see that the information is represented in two units: sq mi and km2. We need to convert all values to km2.\n",
    "\n",
    "The formula to convert 'sq mi' to km2 is to multiply the value by 2.58999.\n",
    "\n",
    "First, we check if the cell has the units as 'sq mi', then we multiply it with 2.589999, convert it to integer and save it back to the cell else we simply convert it into integer. Before this, on taking a closer look at the values, some cells have range of areas and as a result we need to split the data at '-' and then take the first value to continue further."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "dataset['Total Area (km2)'] = dataset['Total Area (km2)'].str.replace(',', '')\n",
    "\n",
    "for x in range(len(dataset['Total Area (km2)'])):\n",
    "    area = dataset.iloc[x]['Total Area (km2)']\n",
    "    if ('sq\\xa0mi' in area):\n",
    "        area = area.split('-')[0]\n",
    "        area = re.sub(r'[^0-9.]+', '', area)\n",
    "        area = int(float(area) * 2.58999)\n",
    "    else:\n",
    "        area = area.split('-')[0]\n",
    "        area = re.sub(r'[^0-9.]+', '', area)\n",
    "        area = int(float(area))\n",
    "    dataset.iloc[x]['Total Area (km2)'] = area\n",
    "\n",
    "dataset.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's analyse the 'Percentage Water' column further. \n",
    "For Algeria, Afghanistan, and some other countries, the value is negligible. Hence, in order to retain data and not drop these rows, we will mark these cells with 0.0.\n",
    "Chile has the character 'b' in the end which needs to be removed.\n",
    "For the columns where the value is more than 100, the actual values were missing and other content has been read instead. Thus, we must remove such rows due to lack of information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "dataset['Percentage Water'] = dataset['Percentage Water'].replace('negligible', '0.0')\n",
    "dataset['Percentage Water'] = dataset['Percentage Water'].replace('Negligible', '0.0')\n",
    "dataset['Percentage Water'] = dataset['Percentage Water'].str.replace(r'[^0-9.]+', '')\n",
    "\n",
    "dataset = dataset[dataset['Percentage Water'].astype(float) <= 100]\n",
    "\n",
    "dataset.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Total GDP includes the values in the form of trillions, billions and millions. We can remove '$' and convert the words to numbers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "dataset['Total Nominal GDP'] = dataset['Total Nominal GDP'].str.replace('$', '')\n",
    "\n",
    "for x in range(len(dataset['Total Nominal GDP'])):\n",
    "    gdp = dataset.iloc[x]['Total Nominal GDP']\n",
    "    if ('trillion' in dataset.iloc[x]['Total Nominal GDP']):\n",
    "        gdp = re.sub(r'[^0-9.]+', '', gdp)\n",
    "        gdp = int(float(gdp) * 1000000000000)\n",
    "    elif ('billion' in dataset.iloc[x]['Total Nominal GDP']):\n",
    "        gdp = re.sub(r'[^0-9.]+', '', gdp)\n",
    "        gdp = int(float(gdp) * 1000000000)\n",
    "    elif ('million' in dataset.iloc[x]['Total Nominal GDP']):\n",
    "        gdp = re.sub(r'[^0-9.]+', '', gdp)\n",
    "        gdp = int(float(gdp) * 1000000)\n",
    "    else:\n",
    "        gdp = int(re.sub(r'[^0-9.]+', '', gdp))\n",
    "    dataset.iloc[x]['Total Nominal GDP'] = gdp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can remove the '$' sign as well as comma from the Per Capita GDP as well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "dataset['Per Capita GDP'] = dataset['Per Capita GDP'].str.replace(r'[^0-9.]+', '')\n",
    "\n",
    "dataset.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "dataset.to_csv(\"Final_dataset.csv\", index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conclusion\n",
    "\n",
    "We have iterated through all columns of the dataset and removed unnecesaary characters, and unified all data into a common format. Each column presented its own set of difficulties that had to be tackled with.\n",
    "The final dataset is ready which we can use for further analysis."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}