Tutorial_2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Data Science at UCSB\n",
    "\n",
    "# Python for Data Science: Tabular Data\n",
    "\n",
    "## Jason Freeberg, Fall 2016 \n",
    "\n",
    "Tabular data is how a lot data is organized. It is not the *only* data format, but it is the easiest to work with because it is well-structured. Other data formats you will come across include [JSON](http://www.json.org/), [Relational and Non-Relational Databases](https://www.mongodb.com/scale/relational-vs-non-relational-database), images, and audio files. And believe it or not you are already familiar with tabular data, it's simply a table with columns and rows. Just like in Excel.\n",
    "\n",
    "As data scientists to-be, however, we need to make define some terms. We will often refer to rows as **observations** or **records**, and columns as **variables** or **features**. The *header* is the top row containing the names of our variables. In the example below our variables are country, salesperson, order id, and so on. Our observations are individual orders with those variable values. Our header, in this case, would be the row with index #1.\n",
    "\n",
    "![data_pic](http://mothimages.s3.amazonaws.com/tabular_data_1.png)\n",
    "\n",
    "In today's lab we will get acquainted with the [pandas module](http://pandas.pydata.org/) by loading a Comma Seperated Value (.csv) [file](https://archive.ics.uci.edu/ml/datasets/Forest+Fires) from the [UCI Machine Learning Repository](https://archive.ics.uci.edu/ml/index.html). We will then check it, coerce the variables to the correct format, check for missing values, and create aggregate reports by conditional selection. Then you'll follow the same pipeline on your own with a different dataset!\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Import the modules we'll need and assign the data's URL.\n",
    "\n",
    "# By the way, it's customary to include all module imports at the beginning of your script.\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from urllib.request import urlopen\n",
    "\n",
    "# line below just a constant for the URL\n",
    "UCI_data_URL = 'http://archive.ics.uci.edu/ml/machine-learning-databases/forest-fires/forestfires.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# A helper function to read the data from the url. \n",
    "# Just run this cell, but understand what the function is doing.\n",
    "\n",
    "def read_csv_from_url(URL):\n",
    "    \"\"\"\n",
    "    Takes as input a string containing the URL pointing to a dataset from the UCI data repository.\n",
    "    Returns a pandas dataframe containing the data with some columns coerced as strings.\n",
    "    \"\"\"\n",
    "    \n",
    "    response = urlopen(URL)\n",
    "    lines = pd.read_csv(response, \n",
    "                        header = 0,\n",
    "                        index_col = False,\n",
    "                        dtype = {'DMC' : str,\n",
    "                                 'temp' : str,\n",
    "                                 'area' : str}\n",
    "                       )\n",
    "    if (4 != \"4\"):\n",
    "        print(\"yes!\")\n",
    "    return pd.DataFrame(lines)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load and Check Data\n",
    "\n",
    "Using the URL and function above, let's load the data into our notebook as a pandas dataframe. We will then inspect dataframe's size, missing values, and variable types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "yes!\n",
      "Number of rows: 517\n",
      "Number of columns: 13\n",
      "   X  Y month  day  FFMC   DMC     DC  ISI  temp  RH  wind  rain area\n",
      "0  7  5   mar  fri  86.2  26.2   94.3  5.1   8.2  51   6.7   0.0    0\n",
      "1  7  4   oct  tue  90.6  35.4  669.1  6.7    18  33   0.9   0.0    0\n",
      "2  7  4   oct  sat  90.6  43.7  686.9  6.7  14.6  33   1.3   0.0    0\n",
      "3  8  6   mar  fri  91.7  33.3   77.5  9.0   8.3  97   4.0   0.2    0\n",
      "4  8  6   mar  sun  89.3  51.3  102.2  9.6  11.4  99   1.8   0.0    0\n"
     ]
    }
   ],
   "source": [
    "# Load the data and print the head of the dataframe.\n",
    "fire_df = read_csv_from_url(UCI_data_URL)\n",
    "\n",
    "# Let's check the head and size of our data\n",
    "print(\"Number of rows:\", fire_df.shape[0])\n",
    "print(\"Number of columns:\", fire_df.shape[1])\n",
    "print(fire_df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now have our data loaded and assigned as a pandas.DataFrame object. However, I made a *slight* adjustment and loaded some variables as **strings**. Know that the pandas DataFrame( ) method is very well built and could have inferred the correct types for all columns, but variable coersion is a common data preparation task so we will do it in this lab.\n",
    "\n",
    "**Right now we'll check for missing and incorrect values.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "X        0\n",
      "Y        0\n",
      "month    0\n",
      "day      0\n",
      "FFMC     0\n",
      "DMC      0\n",
      "DC       0\n",
      "ISI      0\n",
      "temp     0\n",
      "RH       0\n",
      "wind     0\n",
      "rain     0\n",
      "area     0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# .isnull() returns a dataframe of boolean entries where True = Is_Null and False = Not_Null.\n",
    "# We can use the sum() method to take the sums by each column. Remember that True = 1, False = 0.\n",
    "\n",
    "logical_dataframe = fire_df.isnull()\n",
    "print(logical_dataframe.sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So we don't have any **NaN** or **None** values in our columns. But we're not out of the woods yet. Let's take a look at our categorical variables and check that they're reasonable. By printing out the unique strings in each column, we'll be able to see if there are any inappropriate values like misspelled days or months."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Class of our returned column: <class 'pandas.core.series.Series'>\n",
      "['mar' 'oct' 'aug' 'sep' 'apr' 'jun' 'jul' 'feb' 'jan' 'dec' 'may' 'nov']\n",
      "['fri' 'tue' 'sat' 'sun' 'mon' 'wed' 'thu']\n"
     ]
    }
   ],
   "source": [
    "# The syntax, \"DataFrame.columnName\" will return a pandas Series object. \n",
    "# We can use the unique() method to get the distinct strings held in the Series object. \n",
    "\n",
    "print(\"Class of our returned column:\", type(fire_df.month))\n",
    "print(fire_df.month.unique())\n",
    "print(fire_df.day.unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Luckily for us the UCI datasets are often very clean. Although we didn't uncover any missing or incorrect values in this dataset, these types of checks will become routine when you start a project or intern at a company.\n",
    "\n",
    "**Now we'll look at our column types and make adjustments as necessary.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The data types of all features:\n",
      "X          int64\n",
      "Y          int64\n",
      "month     object\n",
      "day       object\n",
      "FFMC     float64\n",
      "DMC       object\n",
      "DC       float64\n",
      "ISI      float64\n",
      "temp      object\n",
      "RH         int64\n",
      "wind     float64\n",
      "rain     float64\n",
      "area      object\n",
      "dtype: object \n",
      "\n",
      "Our non-numeric variables:\n",
      "month    object\n",
      "day      object\n",
      "DMC      object\n",
      "temp     object\n",
      "area     object\n",
      "dtype: object \n",
      "\n",
      "---------- After Type Coercion ---------- \n",
      "\n",
      "FFMC    float64\n",
      "DMC     float64\n",
      "DC      float64\n",
      "ISI     float64\n",
      "temp    float64\n",
      "wind    float64\n",
      "rain    float64\n",
      "area    float64\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "# This will show our columns and their corresponding types.\n",
    "print('The data types of all features:')\n",
    "print(fire_df.dtypes, '\\n')\n",
    "\n",
    "# That's a lot to look at, let's narrow our search. This is a conditional selection, which we'll get to later.\n",
    "print('Our non-numeric variables:')\n",
    "print(fire_df.dtypes[fire_df.dtypes == 'object'], '\\n') # Condition is in the square brackets.\n",
    "\n",
    "# Month and day are okay being objects (strings), but those other three need to be converted to floats...\n",
    "fire_df.DMC = fire_df.DMC.astype(float)\n",
    "fire_df.area = fire_df.area.astype(float)\n",
    "fire_df.temp = fire_df.temp.astype(float)\n",
    "\n",
    "print('---------- After Type Coercion ----------', '\\n')\n",
    "\n",
    "print(fire_df.dtypes[fire_df.dtypes == 'float64'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Conditional Selection\n",
    "\n",
    "Now that we have vetted the data for discrepancies, we can create do some exploratory analysis. Let's first cover conditional selection. Our data is 517 x 13, but we often won't want to use the entire table all the time. We might only need a couple columns, or perhaps we only want to look at the data on Tuesdays. With pandas, it's easy to select columns and rows based on arbitrary conditions. \n",
    "\n",
    "- Here's the basic syntax: *dataframe*[*condition on **rows***]\\[*names or numbers of **columns***]\n",
    "- Alternatively... *dataframe*.ix[*condition on **rows***, *selection of **columns*** ]\n",
    "  - If you come from an R background, the .ix attribute syntax should be familiar\n",
    "- use *dataframe*.iloc**[ ]** for selecting rows and columns based on **purely numerical** indices\n",
    "\n",
    "Click [here](http://pandas.pydata.org/pandas-docs/stable/indexing.html) for the full documentation on slicing and dicing pandas DataFrames."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Golly Gee! Python is so cool!\n"
     ]
    }
   ],
   "source": [
    "# Filter rows...\n",
    "fire_df[:5]  # prints first ten rows (from 0 to 9)\n",
    "fire_df.ix[:5, :] # same thing\n",
    "fire_df.iloc[:5]  # same as above\n",
    "fire_df.iloc[[1,2,3,4,5]]  # same again\n",
    "fire_df[fire_df.area > 30]\n",
    "fire_df[ (fire_df.area > 30) & (fire_df.rain > 10) ]  # two conditions on rows\n",
    "\n",
    "# Select columns...\n",
    "fire_df.temp  # a single column\n",
    "fire_df[\"temp\"]  # also a single column \n",
    "fire_df.ix[:, ['temp']] # same as above\n",
    "fire_df[['day', 'area', 'rain']]  # multiple columns\n",
    "\n",
    "# Select columns AND filter rows...\n",
    "fire_df[fire_df.area > 30][['day', 'area', 'rain']]\n",
    "\n",
    "# Using '.ix' and making the same selection as above...\n",
    "fire_df.ix[ fire_df.area > 30, ['day', 'area', 'rain'] ]\n",
    "\n",
    "# Just to hide all output...\n",
    "print(\"Golly Gee! Python is so cool!\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Your turn\n",
    "\n",
    "Get in the driver's seat, because it's your turn to write some code. Look for the &lt;FILL IN&gt; bits. \n",
    "\n",
    "Good luck and be sure to ask Jason for clarification or help."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (<ipython-input-11-6ebe8cbd806a>, line 6)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;36m  File \u001b[0;32m\"<ipython-input-11-6ebe8cbd806a>\"\u001b[0;36m, line \u001b[0;32m6\u001b[0m\n\u001b[0;31m    response = <FILL IN>(<FILL IN>)\u001b[0m\n\u001b[0m               ^\u001b[0m\n\u001b[0;31mSyntaxError\u001b[0m\u001b[0;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "# URL of a different dataset\n",
    "newURL = \"http://archive.ics.uci.edu/ml/machine-learning-databases/autos/imports-85.data\"\n",
    "\n",
    "# Read the data from the URL, turn it into a pandas dataframe, and return it\n",
    "def myCSVReader(a_URL):\n",
    "    response = <FILL IN>(<FILL IN>)\n",
    "    lines = pd.<FILL IN>(<FILL IN>)\n",
    "  \n",
    "    return <FILL IN>\n",
    "\n",
    "carData = myCSVReader(newURL)\n",
    "\n",
    "carData.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   symboling normalized_losses         make fuel_type aspiration num_doors  \\\n",
      "0          3                 ?  alfa-romero       gas        std       two   \n",
      "1          1                 ?  alfa-romero       gas        std       two   \n",
      "2          2               164         audi       gas        std      four   \n",
      "3          2               164         audi       gas        std      four   \n",
      "4          2                 ?         audi       gas        std       two   \n",
      "\n",
      "    body_style drive_wheels engine_location  wheelbase  ...    engine_size  \\\n",
      "0  convertible          rwd           front       88.6  ...            130   \n",
      "1    hatchback          rwd           front       94.5  ...            152   \n",
      "2        sedan          fwd           front       99.8  ...            109   \n",
      "3        sedan          4wd           front       99.4  ...            136   \n",
      "4        sedan          fwd           front       99.8  ...            136   \n",
      "\n",
      "   fuel_system  bore  stroke compression_ratio horsepower  peak_rpm city_mpg  \\\n",
      "0         mpfi  3.47    2.68               9.0        111      5000       21   \n",
      "1         mpfi  2.68    3.47               9.0        154      5000       19   \n",
      "2         mpfi  3.19    3.40              10.0        102      5500       24   \n",
      "3         mpfi  3.19    3.40               8.0        115      5500       18   \n",
      "4         mpfi  3.19    3.40               8.5        110      5500       19   \n",
      "\n",
      "  highway_mpg  price  \n",
      "0          27  16500  \n",
      "1          26  16500  \n",
      "2          30  13950  \n",
      "3          22  17450  \n",
      "4          25  15250  \n",
      "\n",
      "[5 rows x 26 columns]\n"
     ]
    }
   ],
   "source": [
    "# Uh oh! Looks like our data is missing something, but what?\n",
    "# ... We're missing the header of the data (the column names)!\n",
    "# Here they are.\n",
    "newURL = \"http://archive.ics.uci.edu/ml/machine-learning-databases/autos/imports-85.data\"\n",
    "colNames = [\"symboling\", \"normalized_losses\", \"make\", \"fuel_type\", \n",
    "            \"aspiration\", \"num_doors\", \"body_style\", \"drive_wheels\", \n",
    "            \"engine_location\", \"wheelbase\", \"length\", \"width\", \"height\", \n",
    "            \"curb_weight\", \"engine_type\", \"num_cylinders\", \"engine_size\", \n",
    "            \"fuel_system\", \"bore\", \"stroke\", \"compression_ratio\", \"horsepower\", \n",
    "            \"peak_rpm\", \"city_mpg\", \"highway_mpg\", \"price\"]\n",
    "\n",
    "# Now edit our CSV reader to accept a list of column names and then use it to set the DataFrame's column names.\n",
    "# Look around online to find the DataFrame attribute you need to set ;)\n",
    "\n",
    "def myCSVReader(a_URL, column_names):\n",
    "    response = urlopen(a_URL)\n",
    "    data = pd.read_csv(response)\n",
    "    \n",
    "    data.columns = column_names\n",
    "  \n",
    "    return pd.DataFrame(data)\n",
    "\n",
    "carData = myCSVReader(newURL, colNames)\n",
    "carData.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0636990636472928e+234"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now let's practice some conditional selection\n",
    "\n",
    "# Select rows 1, 2, 3 using numeric indices -- all columns\n",
    "test1 = carData.iloc[<FILL IN>]\n",
    "\n",
    "# Select columns 4, 5, 6 using column numbers -- all rows\n",
    "test2 = carData.iloc[<FILL IN>, <FILL IN>\n",
    "\n",
    "# Subset the DataFrame to only sedans\n",
    "test3 = carData[<FILL IN> == <FILL IN>]\n",
    "\n",
    "# Now in those convertible cars, only keep the horsepower, engine size, city MPG, and highway MPG.\n",
    "test4 = test3[[<FILL IN>]]\n",
    "\n",
    "# Now find the mean horsepower!\n",
    "test4['horsepower'].<FILL IN>()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['111', '154', '102', '115', '110', '140', '160', '101', '121',\n",
       "       '182', '48', '70', '68', '88', '145', '58', '76', '60', '86', '100',\n",
       "       '78', '90', '176', '262', '135', '84', '64', '120', '72', '123',\n",
       "       '155', '184', '175', '116', '69', '55', '97', '152', '200', '95',\n",
       "       '142', '143', '207', '288', '?', '73', '82', '94', '62', '56',\n",
       "       '112', '92', '161', '156', '52', '85', '114', '162', '134', '106'], dtype=object)"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Uh-oh! That doesn't seem like a reasonable average horsepower for sedans!\n",
    "# There must be something fishy in the data; let's investigate.\n",
    "\n",
    "# Use .isnull() and .sum() to check for missing values by colum\n",
    "print( carData.<FILL IN> )\n",
    "\n",
    "# Hmmm... interesting. Print all unique values in the horsepower column\n",
    "print( carData[<FILL IN>].<FILL IN>() )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['?' '164' '158' '192' '188' '121' '98' '81' '118' '148' '110' '145' '137'\n",
      " '101' '78' '106' '85' '107' '104' '113' '150' '129' '115' '93' '142' '161'\n",
      " '153' '125' '128' '122' '103' '168' '108' '194' '231' '119' '154' '74'\n",
      " '186' '83' '102' '89' '87' '77' '91' '134' '65' '197' '90' '94' '256' '95']\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "symboling             0\n",
       "normalized_losses    40\n",
       "make                  0\n",
       "fuel_type             0\n",
       "aspiration            0\n",
       "num_doors             2\n",
       "body_style            0\n",
       "drive_wheels          0\n",
       "engine_location       0\n",
       "wheelbase             0\n",
       "length                0\n",
       "width                 0\n",
       "height                0\n",
       "curb_weight           0\n",
       "engine_type           0\n",
       "num_cylinders         0\n",
       "engine_size           0\n",
       "fuel_system           0\n",
       "bore                  4\n",
       "stroke                4\n",
       "compression_ratio     0\n",
       "horsepower            2\n",
       "peak_rpm              2\n",
       "city_mpg              0\n",
       "highway_mpg           0\n",
       "price                 4\n",
       "dtype: int64"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Ah-Ha! See the problem?! Let's see if that issue is in other columns as well.\n",
    "# Do the same check for normalized_losses\n",
    "print( carData[<FILL IN>].<FILL IN> )\n",
    "\n",
    "# Yup! The '?' must be their indicator for a missing value. \n",
    "# Let's replace '?' with np.nan in all rows\n",
    "carData.replace(to_replace = <FILL IN>, value = <FILL IN>, inplace = True)\n",
    "\n",
    "# Now let's check the number of missing values again\n",
    "print( carData.<FILL IN>.<FILL IN> )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "symboling              int64\n",
      "normalized_losses    float64\n",
      "make                  object\n",
      "fuel_type             object\n",
      "aspiration            object\n",
      "num_doors             object\n",
      "body_style            object\n",
      "drive_wheels          object\n",
      "engine_location       object\n",
      "wheelbase            float64\n",
      "length               float64\n",
      "width                float64\n",
      "height               float64\n",
      "curb_weight            int64\n",
      "engine_type           object\n",
      "num_cylinders         object\n",
      "engine_size            int64\n",
      "fuel_system           object\n",
      "bore                  object\n",
      "stroke                object\n",
      "compression_ratio    float64\n",
      "horsepower           float64\n",
      "peak_rpm             float64\n",
      "city_mpg               int64\n",
      "highway_mpg            int64\n",
      "price                float64\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "# Now that the '?' characters have been replaced, we need to change the data types for some columns.\n",
    "\n",
    "# Make a list of the horsepower, normalized_losses, prince, and peak_rpm column names\n",
    "stringToNumeric = [<FILL IN>]\n",
    "\n",
    "# Iterate over those columns and change the type\n",
    "for column in stringToNumeric:\n",
    "    carData[<FILL IN>] = carData[<FILL IN>].astype(np.float64)\n",
    "\n",
    "# Print the datatypes again to check\n",
    "print(carData.<FILL IN>)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "104.22277227722772"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now let's finally get that mean horsepower for sedans!\n",
    "\n",
    "carData[<FILL IN>].<FILL IN>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Great! Use any remaining time to work on DataCamp or Codecademy exercises."
   ]
  }
 ],
 "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
}