10-Pandas 5.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas Continued\n",
    "\n",
    "Today we will cover how to clean your dataset by handling missing values and changing data types of columns.\n",
    "\n",
    "\n",
    "### Handling missing values after data is loaded"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv(\"data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we've seen in previous tutorials we could use dropna to remove missing data values.\n",
    "\n",
    "What rows do you think will be removed? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "dropna method only knows to remove NaN values as these are seen as mathimatic values of \"not a number\" while the rest are just seen as strings. \n",
    "\n",
    "Sometimes your data set will come with unique missing value term which we will show how to remove later on.\n",
    "\n",
    "First lets look at the default arguments dropna is using, to better understand how it works."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dropna(axis=\"index\", how=\"any\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "dropna has two parameters of interest which is axis and how. \n",
    "\n",
    "axis defaults to index which means it will drop rows where there are missing values.\n",
    "\n",
    "how defaults to any which means it will drop a row if any of the columns contain NaN. \n",
    "\n",
    "Lets show how the results change if we pass different values to these parameters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dropna(axis=\"index\", how=\"all\") # Only drops rows where all values are missing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dropna(axis=\"columns\", how=\"all\") # Only drops columns where all values in the column are NaN, which this data set does not have."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dropna(axis=\"columns\", how=\"any\") # Drops columns where there is at least one NaN. In this case all columns."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sometimes you only want to drop rows which there is missing value in a specific column, you can do this by passing a subset arguement which accepts a list.\n",
    "\n",
    "In this example lets say we must have data in the id column for the data to be useful."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dropna(axis=\"index\", how=\"any\", subset=[\"id\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you see it only removes rows where NaN is found in the id column.\n",
    "\n",
    "Lets also pass test_1 into the subset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dropna(axis=\"index\", how=\"any\", subset=[\"id\",\"test_1\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we've seen already dropna doesn't remove these custom missing value strings of Missing value and Missing. Let's see how to remove these, first we will show how to remove it once loaded and then we'll show how to remove when you load the file in."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to import numpy as it gives us access to the value NaN which pandas recognises.\n",
    "\n",
    "We will then use the method replace to convert the custom missing data strings to a NaN value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.replace(\"Missing\", np.nan, inplace=True)\n",
    "df.replace(\"Missing value\", np.nan, inplace=True)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can use dropna to remove all these custom values of missing data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dropna(axis=\"index\", how=\"any\", subset=[\"id\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas has a method called isna which will create a new table where it returns True or False if the value is NaN or not. This could be used as a mask which we have seen in previous tutorials."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.isna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sometimes you don't want to remove the NaN but replace it with a value. Lets say for this data set any NaN in the test columns should be a 0 as they never sat the test so there score was 0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see this replaces all NaN with 0 which doesn't make sense for the name and id column. To replace only the test columns, filter on the 2 test columns and assign that new table to 2 columns in the data frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[[\"test_1\",\"test_2\"]].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[[\"test_1\",\"test_2\"]] = df[[\"test_1\",\"test_2\"]].fillna(0)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see the dataframe makes sense as only the test column's NaN are changed to 0.\n",
    "\n",
    "Let's now try and find the average of both test_1 and test_2."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"test_1\"].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see the number in the test column as a number but the data type in pandas is a string, we need to convert it to a float for us to be able to use mathimatical methods."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see here both test columns are objects, which we previously discussed usually means its a string or a mix of strings and numbers.\n",
    "\n",
    "Using the method astype we can convert data types, in this example we are converting from object to a float and as you can see the results look no different to us but it will now allow us to use mathimatical functions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[[\"test_1\",\"test_2\"]] .astype(\"float\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[[\"test_1\",\"test_2\"]] = df[[\"test_1\",\"test_2\"]] .astype(\"float\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"test_1\"].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"test_2\"].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[[\"test_1\",\"test_2\"]].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see above the data type is a float.\n",
    "\n",
    "Let's see the results if you were to keep NaN values in the data frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.read_csv(\"data.csv\")\n",
    "df2.replace([\"Missing value\",\"Missing\"], inplace=True)\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2[\"test_1\"].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2[[\"test_1\",\"test_2\"]] = df2[[\"test_1\",\"test_2\"]].astype(\"float\")\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2[\"test_1\"].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see the above results differes, as it does not count NaN as a value and finds only the average of numbers available not the number of rows.\n",
    "\n",
    "### Handling missing values when loading in data\n",
    "\n",
    "If you know the custom missing value strings we can pass this into read_csv when we are loading the data and it will convert them automatically to NaN."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Custom_NA = [\"Missing value\", \"Missing\"]\n",
    "df = pd.read_csv(\"data.csv\", na_values=Custom_NA)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's imagine the dataset didn't tell you all the custom strings for NaN but you wanted to know them, you could use the method unique which returns a list of all the unique values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"data.csv\")\n",
    "df[\"test_1\"].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see we now know the custom missing value strings and we can now pass them into the read_csv again to automatically handle them when loading the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"data.csv\", na_values=[\"Missing value\",\"Missing\"])\n",
    "df"
   ]
  },
  {
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}