FTE_Week2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Week 2 Data Manipulation - Data Assembly, Missing Data and Tidy Data\n",
    "\n",
    "Week 2 reading: **Pandas for Everyone** chapters 4 & 5 (pages 93 - 121)\n",
    "\n",
    "**Outline**\n",
    "\n",
    "* Chapter 4 - Data Assemply\n",
    "    1. Intro to Tidy Data\n",
    "    2. Adding Rows\n",
    "    3. SQL Interlude -- Untderstanding Joins\n",
    "    4. Adding Columns\n",
    "    5. Merging Data\n",
    "* Chapter 5 - Missing Data\n",
    "    1. What is missing data and where does it come from?\n",
    "    2. Dealing with missing data\n",
    "    \n",
    "\n",
    "## Overview\n",
    "\n",
    "Machine learning models are fit from single tables but many times data will come to us as meltiple CSV files or even results of multiple database queries. The **tidy data** concept introduced in Chapter 4 is the end result of adding rows and columns, merging dataframes, and dealing with missing data. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Chapter 4 - Data Assembly\n",
    "### 1.1 Tidy Data\n",
    "\n",
    "The idea of \"Tidy Data\" comes from RStudio developer Hadley Wickham in an article by the same name, published in 2014 by the Journal of Statistical Software. Wickham defined a \"framework\" set of 3 characteristics that all \"tidy,\" or easily analyzable, data share (Tidy Data, Section 2.3): \n",
    "\n",
    "1. Each variable forms a column.\n",
    "2. Each observation forms a row.\n",
    "3. Each type of observational unit forms a table.\n",
    "\n",
    "Wickham makes the observation that his three rules correspond to Edgar F. Codd's **3rd Normal Form (3NF)** that forms the backbone of relational database design (Wickham, 2014). We'll discuss relevant parts of 3NF in Section 1.3 below, and look at Tidy Data in more detail as we discuss Exploratory Data Analysis.\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.2 Adding Rows\n",
    "\n",
    "Section 4.3.1 discusses adding (concatenating) rows from three dataframes with identical column names, adding a series to a dataframe as well as converting the series to a dataframe before adding, and finally, adding dataframes while ignoring the indexes so numbering is continuous.\n",
    "\n",
    "Some quick examples follow."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    A   B   C   D\n",
      "0  a0  b0  c0  d0\n",
      "1  a1  b1  c1  d1\n",
      "2  a2  b2  c2  d2\n",
      "3  a3  b3  c3  d3\n",
      "\n",
      "    A   B   C   D\n",
      "0  a4  b4  c4  d4\n",
      "1  a5  b5  c5  d5\n",
      "2  a6  b6  c6  d6\n",
      "3  a7  b7  c7  d7\n",
      "\n",
      "     A    B    C    D\n",
      "0   a8   b8   c8   d8\n",
      "1   a9   b9   c9   d9\n",
      "2  a10  b10  c10  d10\n",
      "3  a11  b11  c11  d11\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df1 = pd.read_csv(\"../pandas_for_everyone/data/concat_1.csv\")\n",
    "df2 = pd.read_csv(\"../pandas_for_everyone/data/concat_2.csv\")\n",
    "df3 = pd.read_csv(\"../pandas_for_everyone/data/concat_3.csv\")\n",
    "\n",
    "print(f'{df1}\\n\\n{df2}\\n\\n{df3}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     A    B    C    D\n",
      "0   a0   b0   c0   d0\n",
      "1   a1   b1   c1   d1\n",
      "2   a2   b2   c2   d2\n",
      "3   a3   b3   c3   d3\n",
      "0   a4   b4   c4   d4\n",
      "1   a5   b5   c5   d5\n",
      "2   a6   b6   c6   d6\n",
      "3   a7   b7   c7   d7\n",
      "0   a8   b8   c8   d8\n",
      "1   a9   b9   c9   d9\n",
      "2  a10  b10  c10  d10\n",
      "3  a11  b11  c11  d11\n"
     ]
    }
   ],
   "source": [
    "print(pd.concat([df1, df2, df3]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     A    B    C    D    0\n",
      "0   a0   b0   c0   d0  NaN\n",
      "1   a1   b1   c1   d1  NaN\n",
      "2   a2   b2   c2   d2  NaN\n",
      "3   a3   b3   c3   d3  NaN\n",
      "0  NaN  NaN  NaN  NaN   n1\n",
      "1  NaN  NaN  NaN  NaN   n2\n",
      "2  NaN  NaN  NaN  NaN   h3\n",
      "3  NaN  NaN  NaN  NaN   n4\n"
     ]
    }
   ],
   "source": [
    "series_row = pd.Series(['n1', 'n2', 'h3', 'n4'])\n",
    "\n",
    "print(pd.concat([df1, series_row]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    A   B   C   D\n",
      "0  n1  n2  h3  n4\n"
     ]
    }
   ],
   "source": [
    "new_df = pd.DataFrame([['n1', 'n2', 'h3', 'n4']], columns = ['A','B','C','D'])\n",
    "print(new_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Besides the concat() function (for adding multiple things), you can also use append() to add just one object: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    A   B   C   D\n",
      "0  a0  b0  c0  d0\n",
      "1  a1  b1  c1  d1\n",
      "2  a2  b2  c2  d2\n",
      "3  a3  b3  c3  d3\n",
      "0  a4  b4  c4  d4\n",
      "1  a5  b5  c5  d5\n",
      "2  a6  b6  c6  d6\n",
      "3  a7  b7  c7  d7\n"
     ]
    }
   ],
   "source": [
    "print(df1.append(df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using `ignore_index=True` will allow the new dataframe to index properly:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    A   B   C   D\n",
      "0  a0  b0  c0  d0\n",
      "1  a1  b1  c1  d1\n",
      "2  a2  b2  c2  d2\n",
      "3  a3  b3  c3  d3\n",
      "4  a4  b4  c4  d4\n",
      "5  a5  b5  c5  d5\n",
      "6  a6  b6  c6  d6\n",
      "7  a7  b7  c7  d7\n"
     ]
    }
   ],
   "source": [
    "print(df1.append(df2, ignore_index=True))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.3 SQL Interlude -- Understanding Joins\n",
    "\n",
    "To understand joins in SQL, we should probably have a rudimentary understanding of *why* we have to join data.\n",
    "\n",
    "Consider an employee table in a fictitious company:\n",
    "\n",
    "name | department \n",
    "-----|-----------\n",
    "Tom| HR\n",
    "Mary | Development\n",
    "John | Marketing\n",
    "Tim | H.R.\n",
    "\n",
    "Notice that both Tom and Tim work in HR. **What happens if we want to change 'HR' to 'Human Resources?'** Of course it is easy to see we would change the department on those two rows, But what if we had 1,000 employees in our table? How would be find them all? Also notice how 'HR' is actually entered two different ways. Which one is correct? What happens if both Tim and Tom get fired and we delete those two rows? We lose the fact that an HR department even exists!\n",
    "\n",
    "To combat these problems, we **normalize** the table above into two tables, employees and departments.\n",
    "\n",
    "**employees**\n",
    "emp_id | emp_name | dept_id\n",
    "--|-----|-----------\n",
    "1 | Tom | 1\n",
    "2 | Mary | 2\n",
    "3 | John | 3\n",
    "4 | Tim | 1\n",
    "5 | Jenny | \n",
    "\n",
    "NOTE: Jenny is a new hire not assigned a department yet.<br>\n",
    "**departments**\n",
    "\n",
    "dept_id | dept_name\n",
    "--------|----------\n",
    "1 | HR\n",
    "2 | Development\n",
    "3 | Marketing\n",
    "\n",
    "By normalizing the employee table, we've eliminated the source of many errors as well as a lot of repeated data, **but** now we can't just look at one table to get all the employees -- department 3 won't mean anything to anyone other than than a database administrator. How do we solve this problem?\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Enter Joins\n",
    "\n",
    "As you can see in figure, there are many types of join:<br>\n",
    "<img align=\"right\" style=\"padding-right:10px;\" src=\"figures/500px-SQL_Joins_labels.png\"><br>\n",
    "\n",
    "In the world of database queries, the top 3 are by far the most common:\n",
    "* Inner join\n",
    "    * Return only the stuff that matches in both tables\n",
    "* Left join\n",
    "    * Return all the stuff in the left table and stuff from the right that matches a key (like an id)\n",
    "* Right join\n",
    "    * Return all the stuff in the right table and stuff from the left that matches a key\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Left Join**<br>\n",
    "`select employees.name, d.dept_name from employees left join departments d on employees.dept_id = d.dept_id;`\n",
    "\n",
    "Don't worry about the SQL syntax. The result of the left join is:\n",
    "\n",
    "name | dept_name\n",
    "-----|-----------\n",
    "Tom | HR\n",
    "Mary | Development\n",
    "John | Marketing\n",
    "Tim | HR\n",
    "Jenny | null\n",
    "    \n",
    "notice that left join gives us everything from the left table (employees) even if there is no match -- Jenny has invisible null for dept_name.\n",
    "    \n",
    "<hr>\n",
    "\n",
    "**Inner Join**<br>\n",
    "`select employees.name, d.dept_name from employees inner join departments d on employees.dept_id = d.dept_id;`\n",
    "\n",
    "name | dept_name\n",
    "-----|-----------\n",
    "Tom | HR\n",
    "Mary | Development\n",
    "John | Marketing\n",
    "Tim | HR\n",
    "    \n",
    "Inner join gives us what we expect, only the employees that have a matching dept_id in the departments table.\n",
    "    \n",
    "<hr>\n",
    "\n",
    "**Right Join**<br>\n",
    "Right join acts the same way as left join, with the effects reversed. To see it, let's add *sales* and *engineering* departments.\n",
    "\n",
    "`select employees.name, d.dept_name from employees right join departments d on employees.dept_id = d.dept_id;`\n",
    "\n",
    "name | dept_name\n",
    "-----|-----------\n",
    "Tom | HR\n",
    "Mary | Development\n",
    "John | Marketing\n",
    "Tim | HR\n",
    "null | Sales\n",
    "null |engineering\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.4 Adding Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the most part, adding columns with the concat() function works the same as rows, except we have to specify axis=1.\n",
    "\n",
    "One thing the book doesnot really mention at this stage is adding columns simply by naming them. Like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    A   B   C   D\n",
      "0  a0  b0  c0  d0\n",
      "1  a1  b1  c1  d1\n",
      "2  a2  b2  c2  d2\n",
      "3  a3  b3  c3  d3\n",
      "\n",
      "    A   B   C   D\n",
      "0  a4  b4  c4  d4\n",
      "1  a5  b5  c5  d5\n",
      "2  a6  b6  c6  d6\n",
      "3  a7  b7  c7  d7\n"
     ]
    }
   ],
   "source": [
    "print(f'{df1}\\n\\n{df2}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a0</td>\n",
       "      <td>b0</td>\n",
       "      <td>c0</td>\n",
       "      <td>d0</td>\n",
       "      <td>d4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a1</td>\n",
       "      <td>b1</td>\n",
       "      <td>c1</td>\n",
       "      <td>d1</td>\n",
       "      <td>d5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>a2</td>\n",
       "      <td>b2</td>\n",
       "      <td>c2</td>\n",
       "      <td>d2</td>\n",
       "      <td>d6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a3</td>\n",
       "      <td>b3</td>\n",
       "      <td>c3</td>\n",
       "      <td>d3</td>\n",
       "      <td>d7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A   B   C   D   E\n",
       "0  a0  b0  c0  d0  d4\n",
       "1  a1  b1  c1  d1  d5\n",
       "2  a2  b2  c2  d2  d6\n",
       "3  a3  b3  c3  d3  d7"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1['E'] = df2['D']\n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In most cases, that will be the easiest way to add a column.\n",
    "\n",
    "Pandas does support the \"join\" method of concatenating or merging dataframes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   emp_id emp_name  dept_id\n",
      "0       1      Tom        1\n",
      "1       2     Mary        2\n",
      "2       3     John        3\n",
      "3       4      Tim        1\n",
      "4       5    Jenny        0\n",
      "\n",
      "   dept_id    dept_name\n",
      "0        1           HR\n",
      "1        2  Development\n",
      "2        3    Marketing\n"
     ]
    }
   ],
   "source": [
    "# **employees**\n",
    "# emp_id | emp_name | dept_id\n",
    "# --|-----|-----------\n",
    "# 1 | Tom | 1\n",
    "# 2 | Mary | 2\n",
    "# 3 | John | 3\n",
    "# 4 | Tim | 1\n",
    "# 5 | Jenny | \n",
    "\n",
    "# NOTE: Jenny is a new hire not assigned a department yet.<br>\n",
    "# **departments**\n",
    "\n",
    "# dept_id | dept_name\n",
    "# --------|----------\n",
    "# 1 | HR\n",
    "# 2 | Development\n",
    "# 3 | Marketing\n",
    "import numpy as np\n",
    "\n",
    "# Have to put a 0 in for Jenny, then adjust it.\n",
    "emp_df = pd.DataFrame({'emp_id':[1,2,3,4,5], 'emp_name':['Tom', 'Mary','John', 'Tim', 'Jenny'], 'dept_id':[1,2, 3, 1, 0] })\n",
    "dept_df = pd.DataFrame({'dept_id':[1,2,3], 'dept_name':['HR','Development', 'Marketing']})\n",
    "\n",
    "# Workaround to get a null into Jenny's dept_id\n",
    "# emp_df.loc[emp_df['emp_name'] == 'Jenny', 'dept_id'] = np.nan\n",
    "\n",
    "print(f'{emp_df}\\n\\n{dept_df}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   emp_id emp_name  dept_id  dept_id    dept_name\n",
      "0       1      Tom        1      1.0           HR\n",
      "1       2     Mary        2      2.0  Development\n",
      "2       3     John        3      3.0    Marketing\n",
      "3       4      Tim        1      NaN          NaN\n",
      "4       5    Jenny        0      NaN          NaN\n"
     ]
    }
   ],
   "source": [
    "print(pd.concat([emp_df, dept_df], join='outer', axis=1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   emp_id emp_name  dept_id  dept_id    dept_name\n",
      "0       1      Tom        1        1           HR\n",
      "1       2     Mary        2        2  Development\n",
      "2       3     John        3        3    Marketing\n"
     ]
    }
   ],
   "source": [
    "print(pd.concat([emp_df, dept_df],  join='inner', axis=1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "However, concatenating is simply smashing two dataframes together based on index. Let's look at more of a true join with the merge() function."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.5 Merging Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The book does a good job of describing database concepts of \"one-to-one\", \"many-to-one\", and \"many-to-many.\" Our employee table is a one-to-one relationship -- one employee works in one department. \n",
    "\n",
    "The book describes parameters that can be used if column names are different, but in this case the developer was plannning ahead and made sure they match :)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>emp_id</th>\n",
       "      <th>emp_name</th>\n",
       "      <th>dept_id</th>\n",
       "      <th>dept_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Tom</td>\n",
       "      <td>1</td>\n",
       "      <td>HR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>Tim</td>\n",
       "      <td>1</td>\n",
       "      <td>HR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Mary</td>\n",
       "      <td>2</td>\n",
       "      <td>Development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>John</td>\n",
       "      <td>3</td>\n",
       "      <td>Marketing</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   emp_id emp_name  dept_id    dept_name\n",
       "0       1      Tom        1           HR\n",
       "1       4      Tim        1           HR\n",
       "2       2     Mary        2  Development\n",
       "3       3     John        3    Marketing"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "emp_df.merge(dept_df, on=\"dept_id\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice how Jenny, whose department ID doesn't match in the departments table, is omitted. **Inner join** is the default. https://chrisalbon.com/python/data_wrangling/pandas_join_merge_dataframe/ demonstrates concatenation and merging very well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Chapter 5 Missing Data\n",
    "### 2.1 What is missing data and where does it come from?\n",
    "\n",
    "**What**<br>\n",
    "Conceptually, missing values in the data are exactly that - missing. Nothing there. Not \"0\" and not \" \" (blank space). Non-existance.\n",
    "\n",
    "Programming languages generally have the concept of a \"null\" value, although a different word may be used. For example, pure Python uses the keyword *\"None\"* to express the concept. \n",
    "\n",
    "Much of Pandas is based on the NumPy high-performance mathematical library. As such, Pandas and NumPy both use **NaN** -- \"Not a Number\" to express missing or null values.\n",
    "\n",
    "**Where**<br>\n",
    "NaN or null values can creep into a data set in a variety of ways:\n",
    "\n",
    "* A value was not entered or entered incorrectly on a form.\n",
    "* An observation from a sensor was corrupt or missing.\n",
    "* Transcription error or typo.\n",
    "* Etc. \n",
    "\n",
    "**But wait, there's more**<br>\n",
    "A much more insidious and hard to detect type of \"null\" comes in the form of a substitute value. Perhaps the data was stored in a database that didn't allow nulls (common) or due to a data-entry convention, or some other reason, missing data is not allowed to be null.\n",
    "\n",
    "A prime example of this is the **\"Heart Disease Data Set\"** from the UCI Machine Learning Repository (https://archive.ics.uci.edu/ml/datasets/Heart+Disease). This data suffers from a number of problems, including the fact that it is delimited by spaces and one observation takes multiple rows (10). Pandas can't read it in its native form, and this data set will be the subject of scrutiny in MSDS 621. For now, let's just load the file and look at one observation (one row)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['1254 0 40 1 1 0 0\\n', '-9 2 140 0 289 -9 -9 -9\\n', '0 -9 -9 0 12 16 84 0\\n', '0 0 0 0 150 18 -9 7\\n', '172 86 200 110 140 86 0 0\\n', '0 -9 26 20 -9 -9 -9 -9\\n', '-9 -9 -9 -9 -9 -9 -9 12\\n', '20 84 0 -9 -9 -9 -9 -9\\n', '-9 -9 -9 -9 -9 1 1 1\\n', '1 1 -9. -9. name\\n']\n"
     ]
    }
   ],
   "source": [
    "hrt_data = []\n",
    "with open ('data/hungarian.data', 'r') as infile:\n",
    "    for line in infile:\n",
    "        hrt_data.append(line)\n",
    "\n",
    "print(hrt_data[:10])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data like the file above is a good example why you need to learn about your data. These -9s are the only negative numbers in the data set, but there are a lot of them. If they were allowed to stay -9s, they would drastically affect any analysis or machine learning algorithm."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pages 109 - 116 of **\"Pandas for Everyone\"** does a good job of showing NaN values and the properties they (don't) possess as well as various ways they can sneak into perfectly respectable data sets through joins, user input, re-indexing, etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2 Finding and Dealing with missing data\n",
    "\n",
    "What is that saying? *\"The first step is admitting you have a problem?\"* **\"Pandas for Everyone\"** discusses one method of finding nulls based on column counts and data set shape. We will present a slightly different method so you can choose which method you prefes.\n",
    "\n",
    "First, we will load in the data file:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Day</th>\n",
       "      <th>Cases_Guinea</th>\n",
       "      <th>Cases_Liberia</th>\n",
       "      <th>Cases_SierraLeone</th>\n",
       "      <th>Cases_Nigeria</th>\n",
       "      <th>Cases_Senegal</th>\n",
       "      <th>Cases_UnitedStates</th>\n",
       "      <th>Cases_Spain</th>\n",
       "      <th>Cases_Mali</th>\n",
       "      <th>Deaths_Guinea</th>\n",
       "      <th>Deaths_Liberia</th>\n",
       "      <th>Deaths_SierraLeone</th>\n",
       "      <th>Deaths_Nigeria</th>\n",
       "      <th>Deaths_Senegal</th>\n",
       "      <th>Deaths_UnitedStates</th>\n",
       "      <th>Deaths_Spain</th>\n",
       "      <th>Deaths_Mali</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1/5/2015</td>\n",
       "      <td>289</td>\n",
       "      <td>2776.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10030.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1786.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2977.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1/4/2015</td>\n",
       "      <td>288</td>\n",
       "      <td>2775.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9780.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1781.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2943.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1/3/2015</td>\n",
       "      <td>287</td>\n",
       "      <td>2769.0</td>\n",
       "      <td>8166.0</td>\n",
       "      <td>9722.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1767.0</td>\n",
       "      <td>3496.0</td>\n",
       "      <td>2915.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1/2/2015</td>\n",
       "      <td>286</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8157.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3496.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12/31/2014</td>\n",
       "      <td>284</td>\n",
       "      <td>2730.0</td>\n",
       "      <td>8115.0</td>\n",
       "      <td>9633.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1739.0</td>\n",
       "      <td>3471.0</td>\n",
       "      <td>2827.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  Day  Cases_Guinea  Cases_Liberia  Cases_SierraLeone  \\\n",
       "0    1/5/2015  289        2776.0            NaN            10030.0   \n",
       "1    1/4/2015  288        2775.0            NaN             9780.0   \n",
       "2    1/3/2015  287        2769.0         8166.0             9722.0   \n",
       "3    1/2/2015  286           NaN         8157.0                NaN   \n",
       "4  12/31/2014  284        2730.0         8115.0             9633.0   \n",
       "\n",
       "   Cases_Nigeria  Cases_Senegal  Cases_UnitedStates  Cases_Spain  Cases_Mali  \\\n",
       "0            NaN            NaN                 NaN          NaN         NaN   \n",
       "1            NaN            NaN                 NaN          NaN         NaN   \n",
       "2            NaN            NaN                 NaN          NaN         NaN   \n",
       "3            NaN            NaN                 NaN          NaN         NaN   \n",
       "4            NaN            NaN                 NaN          NaN         NaN   \n",
       "\n",
       "   Deaths_Guinea  Deaths_Liberia  Deaths_SierraLeone  Deaths_Nigeria  \\\n",
       "0         1786.0             NaN              2977.0             NaN   \n",
       "1         1781.0             NaN              2943.0             NaN   \n",
       "2         1767.0          3496.0              2915.0             NaN   \n",
       "3            NaN          3496.0                 NaN             NaN   \n",
       "4         1739.0          3471.0              2827.0             NaN   \n",
       "\n",
       "   Deaths_Senegal  Deaths_UnitedStates  Deaths_Spain  Deaths_Mali  \n",
       "0             NaN                  NaN           NaN          NaN  \n",
       "1             NaN                  NaN           NaN          NaN  \n",
       "2             NaN                  NaN           NaN          NaN  \n",
       "3             NaN                  NaN           NaN          NaN  \n",
       "4             NaN                  NaN           NaN          NaN  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ebola = pd.read_csv(\"../pandas_for_everyone/data/country_timeseries.csv\")\n",
    "ebola.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Simply looking at the first few rows clearly shows we have NaNs. \n",
    "\n",
    "Next, we will use the Pandas info() function to tell us about the columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 122 entries, 0 to 121\n",
      "Data columns (total 18 columns):\n",
      "Date                   122 non-null object\n",
      "Day                    122 non-null int64\n",
      "Cases_Guinea           93 non-null float64\n",
      "Cases_Liberia          83 non-null float64\n",
      "Cases_SierraLeone      87 non-null float64\n",
      "Cases_Nigeria          38 non-null float64\n",
      "Cases_Senegal          25 non-null float64\n",
      "Cases_UnitedStates     18 non-null float64\n",
      "Cases_Spain            16 non-null float64\n",
      "Cases_Mali             12 non-null float64\n",
      "Deaths_Guinea          92 non-null float64\n",
      "Deaths_Liberia         81 non-null float64\n",
      "Deaths_SierraLeone     87 non-null float64\n",
      "Deaths_Nigeria         38 non-null float64\n",
      "Deaths_Senegal         22 non-null float64\n",
      "Deaths_UnitedStates    18 non-null float64\n",
      "Deaths_Spain           16 non-null float64\n",
      "Deaths_Mali            12 non-null float64\n",
      "dtypes: float64(16), int64(1), object(1)\n",
      "memory usage: 17.2+ KB\n"
     ]
    }
   ],
   "source": [
    "ebola.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice the info() function tells us how many how many rows we have ( `RangeIndex: 122 entries` ), how many non-null values are in each column, and what type of data the column contains.\n",
    "\n",
    "The book proposes several standard ways of handling the missing data:\n",
    "* Recode/replace (recoding as 0 is used as an example)\n",
    "* Fill forward using the last known good value\n",
    "* Fill backward using the next good value\n",
    "* Interpolation (by equally spacing values)\n",
    "* Dropping rows with missing values\n",
    "\n",
    "Since much of the time we are doing our data wrangling as a precursor to building a machine learning model, we will take a quick look at scikit-learn's SimpleImputer that can be used for both numeric and categorical data, alone or as part of a preprocessing pipeline.\n",
    "\n",
    "SimpleImputer can recognize both NaNs and substitute values such as our -9s above and replace them with *a constant value* or:\n",
    "\n",
    "* mean for the column\n",
    "* median for the column\n",
    "* most frequent value for the column\n",
    "\n",
    "Let's demonstrate on the ebola dataframe we loaded earlier:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.impute import SimpleImputer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Scikit-learn is built to use NumPy 2D matrixes of numbers, so we will make our data set, X, out of the numeric values and check a slice:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[ 2776.,    nan, 10030.,    nan,    nan,    nan,    nan,    nan,\n",
       "         1786.,    nan,  2977.,    nan,    nan,    nan,    nan,    nan],\n",
       "       [ 2775.,    nan,  9780.,    nan,    nan,    nan,    nan,    nan,\n",
       "         1781.,    nan,  2943.,    nan,    nan,    nan,    nan,    nan],\n",
       "       [ 2769.,  8166.,  9722.,    nan,    nan,    nan,    nan,    nan,\n",
       "         1767.,  3496.,  2915.,    nan,    nan,    nan,    nan,    nan],\n",
       "       [   nan,  8157.,    nan,    nan,    nan,    nan,    nan,    nan,\n",
       "           nan,  3496.,    nan,    nan,    nan,    nan,    nan,    nan],\n",
       "       [ 2730.,  8115.,  9633.,    nan,    nan,    nan,    nan,    nan,\n",
       "         1739.,  3471.,  2827.,    nan,    nan,    nan,    nan,    nan]])"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "X = ebola.iloc[:,2:].values\n",
    "X[:5,:]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we will create our imputer, fit it to the data, then transform the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[ 2776.          2335.3373494  10030.            16.73684211\n",
      "      1.08           3.27777778     1.             3.5\n",
      "   1786.          1101.20987654  2977.             6.13157895\n",
      "      0.             0.83333333     0.1875         3.16666667]\n",
      " [ 2775.          2335.3373494   9780.            16.73684211\n",
      "      1.08           3.27777778     1.             3.5\n",
      "   1781.          1101.20987654  2943.             6.13157895\n",
      "      0.             0.83333333     0.1875         3.16666667]\n",
      " [ 2769.          8166.          9722.            16.73684211\n",
      "      1.08           3.27777778     1.             3.5\n",
      "   1767.          3496.          2915.             6.13157895\n",
      "      0.             0.83333333     0.1875         3.16666667]\n",
      " [  911.06451613  8157.          2427.36781609    16.73684211\n",
      "      1.08           3.27777778     1.             3.5\n",
      "    563.23913043  3496.           693.70114943     6.13157895\n",
      "      0.             0.83333333     0.1875         3.16666667]\n",
      " [ 2730.          8115.          9633.            16.73684211\n",
      "      1.08           3.27777778     1.             3.5\n",
      "   1739.          3471.          2827.             6.13157895\n",
      "      0.             0.83333333     0.1875         3.16666667]]\n"
     ]
    }
   ],
   "source": [
    "np.set_printoptions(suppress=True) # Suppresses scientific notation for small numbers\n",
    "imputer = SimpleImputer(missing_values=np.nan, strategy='mean')\n",
    "imputer.fit(X)  \n",
    "X_new = imputer.transform(X)\n",
    "print(X_new[:5,:])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see, there are no more NaNs in sight.\n",
    "\n",
    "Transformations such as scaling, normalization, and one-hot encoding (of categorical columns) are very common operations leading up to fitting a machine learning model. So common, in fact, that scikit-learn created a **pipeline** module to help bundle the transformations as a series of steps.\n",
    "\n",
    "For more information, please see the scikit-learn documentation:\n",
    "\n",
    "https://scikit-learn.org/stable/modules/generated/sklearn.impute.SimpleImputer.html\n",
    "\n",
    "https://scikit-learn.org/stable/data_transforms.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*References:*\n",
    "\n",
    "Wickham, H. (2014). Tidy data. Journal of Statistical Software, 59(10), 1-23."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}