D03. Dataframes - Handling Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# D03: Dataframes - Handling Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Having learnt how to read data in to Pandas, the next logical step is to learn how to to work with dataframes to convert the dataframe structure and underlying data into something which can be visualised or analysed. This process takes a variety of names as follows:\n",
    "\n",
    "* Data Cleaning\n",
    "* Data Cleansing\n",
    "* Data Wrangling\n",
    "* Data Munging\n",
    "\n",
    "These terms all have slightly different meanings but all relate to the same process of 'removing erroneous records and getting data ready for consumption'\n",
    "\n",
    "We're going to explore this process using pandas now. Firstly we'll need some data..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "path = \"https://vincentarelbundock.github.io/Rdatasets/csv/multgee/arthritis.csv\" # Arthritis dataset\n",
    "df = pd.read_csv(path)\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Basic Data Wrangling"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "This dataset shows data from a double-blind clinical trial investigating a new treatment for rheumatoid arthritis. However at the moment the column names and data don't make sense to us because it's mostly coded. \n",
    "\n",
    "However we can use Pandas to do some basic work on the dataframe to improve this situtation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "new_names = {'Unnamed: 0':'Index',\n",
    "             'id':'Patient ID',\n",
    "             'y':'Arthritis Score',\n",
    "             'sex':'Gender',\n",
    "             'age':'Age',\n",
    "             'trt':'Treatment Type',\n",
    "             'baseline':'Baseline Arthritis Score',\n",
    "             'time':'Time of Score'}                                   # Dictionary of new column names\n",
    "\n",
    "order = ['Index','Patient ID','Gender','Age','Treatment Type',\n",
    "         'Baseline Arthritis Score','Arthritis Score','Time of Score'] # List of the new column order\n",
    "  \n",
    "# Refining the structure of the dataset:\n",
    "    \n",
    "df = df.rename(columns = new_names)                    # Applying the new names to the dataframe\n",
    "df = df[order]                                         # Ordering the columns in the dataframe.\n",
    "df = df.drop(['Index'],axis=1)                         # Dropping a column. The axis=1 is used to specify the vertical (column) axis\n",
    "df = df.sort_values(by=['Patient ID','Time of Score']) # Sorting the dataframe by 2 x variables\n",
    "df.head(6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So we've made the dataset make a little more sense through refining the structure, getting rid of unwanted variables and sorting. However some of the data is coded and since we might want to present some of this in tables or charts, it would be good to make it more meaningful to our audience."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Converting data with functions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One of the ways we can make the data more meaningful is to transform the data to change how it's displayed. We can do this with a function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Creating a new column with a function\n",
    "\n",
    "def gd_change (row):              # Creating the function - the 'row' parameter can be any word. Pandas will automatically assign a value based upon the index\n",
    "    if row['Gender'] == 1:\n",
    "        return 'M'\n",
    "    elif row['Gender'] == 2:\n",
    "        return 'F'\n",
    "    else:\n",
    "        return 'O'\n",
    "    \n",
    "df['Gender Desc'] = df.apply(gd_change,axis=1)     # Creating a new column using the apply method to apply the function to the dataframe\n",
    "df.head(6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see the lists, dictionaries and functions we learned about in the basics section have come in handy so far!\n",
    "\n",
    "In this case we've created a 'Gender Desc' variable to describe the gender of the patient. Let's do this for the treatment type too... "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def trt_change (row):              \n",
    "    if row['Treatment Type'] == 1:\n",
    "        return 'Treatment'\n",
    "    elif row['Treatment Type'] == 2:\n",
    "        return 'Placebo'\n",
    "    else:\n",
    "        return 'Other'\n",
    "    \n",
    "df['Treatment Desc'] = df.apply(trt_change,axis=1)\n",
    "df.head(6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Converting data types"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You'll notice that the Baseline Artritis Score and Artritis Score variables are in different formats with one being an integer and one being a float. We might want to change them to match but first we'll need to determine the range of values that exist for both variables. We can do this by using two methods in conjunction with one another:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "baseline_values = df['Arthritis Score'].unique()  # Returns the unique values contained in a column\n",
    "baseline_values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see here that there aren't any values to the right of the decimal so we would probably want to convert this variable to an integer. However we can also see a 'Nan' value. Nan stands for 'Not a Number' and is mostly used to denote missing data. If we were to try and convert the entire column to an integer, Pandas would give us an error as it cannot convert Nan values to integers.\n",
    "\n",
    "This gives us two choices:\n",
    "\n",
    "1) Write a function using try / except to only convert the floats and handle the exceptions that the Nan values will produce <br/>\n",
    "2) Convert the Nan values to something that the .astype() method can process. <br/>\n",
    "\n",
    "In this example we're going to go with option 2 because it's quicker and easier. We can use the fillna() method to determine how pandas will deal with Nan values:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df['Arthritis Score'] = df['Arthritis Score'].fillna(0)     # Replace Nan values with 0's"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before converting the field to an integer with the astype() method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df['Arthritis Score'] = df['Arthritis Score'].astype('int') # Converts the values in a column to integers\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Method Chaining"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the example above, we converted the df['Arthritis Score'] column from a float to an integer in two steps as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df['Arthritis Score'] = df['Arthritis Score'].fillna(0)\n",
    "df['Arthritis Score'] = df['Arthritis Score'].astype('int')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is inefficient both in terms of time spent typing and also in terms of processing. We can improve both these things with method chaining as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df['Arthritis Score'] = df['Arthritis Score'].fillna(0).astype('int')   # Example of method chaining"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Method chaining, according to wikipeida, is:"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "\"Method chaining, also known as named parameter idiom, is a common syntax for invoking multiple method calls in object-oriented programming languages. Each method returns an object, allowing the calls to be chained together in a single statement without requiring variables to store the intermediate results.\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This allows us to be more efficient with our code and processing which is a good thing. Just remember that you can only use methods that are applicable to that object and as a general rules, Python will execute the methods in the order in which they are chained.\n",
    "\n",
    "Also note that generally throughout the course we'll not be using much method chaining as it an make things less clear for those who are learning. That said you should always look to chain your methods when writing your own code as it's more efficient in terms of processing and typing."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating new columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also convert data and create some new columns with some simple code we met in the Basics section of the course."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df['Combined Score'] = df['Baseline Arthritis Score'] + df['Arthritis Score']                             # Basic Arithmetic\n",
    "df['Scores'] = df['Baseline Arthritis Score'].astype('str') + ',' + df['Arthritis Score'].astype('str')   # Using strings\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Further Reading"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a href = \"https://en.wikipedia.org/wiki/Method_chaining\">Method Chaining</a><br/>"
   ]
  }
 ],
 "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.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}