Reproducible-Analytics-Workshop-World-Bank-01-10-2018-Part2-Data-Analysis-In-Python-Student-Version.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 2 - Data Analysis in Python\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The material for this workshop was developed using the following sources:\n",
    " \n",
    "[Data Carpentry](https://datacarpentry.org)\n",
    "[Github Resources](https://github.com/Reproducible-Science-Curriculum)\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Learning objectives\n",
    "Use the Python Pandas library in the Jupyter Notebook to:\n",
    "* Assess the structure and cleanliness of a dataset, including the size and shape of the data, and the number of variables of each type.\n",
    "* Describe findings, translate results from code to text using Markdown comments in the Jupyter Notebook, summarizing your thought process in a narrative.\n",
    "* Modify raw data to prepare a clean data set -- including copying data, removing or replacing missing and incoherent data, dropping columns, removing duplicates.\n",
    "* Assess whether data is “Tidy” and identify appropriate steps and write and  execute code to arrange it into a tidy format - including merging, reshaping, subsetting, grouping, sorting, and making appropriate new columns.\n",
    "* Identify several relevant summary measures\n",
    "* Illustrate data in plots and determine the need for repeated or further analysis.\n",
    "* Justify these decisions in Markdown in the Jupyter Notebook.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Setting up the notebook\n",
    "\n",
    "## About Libraries in Python\n",
    "\n",
    "A library in Python contains a set of tools (called functions) that perform tasks on our data. Importing a library is like getting a piece of lab equipment out of a storage locker and setting it up on the bench for use in a project. Once a library is imported, it can be used or called to perform many tasks.\n",
    "\n",
    "Python doesn’t load all of the libraries available to it by default. We have to add an import statement to our code in order to use library functions. To import a library, we use the syntax `import libraryName`. If we want to give the library a nickname to shorten the command, we can add `as nickNameHere`. An example of importing the Pandas library using the common nickname `pd` is below.\n",
    "\n",
    "**`import`** `pandas` **`as`** `pd`\n",
    "\n",
    "## matplotlib and other plotting libraries\n",
    "\n",
    "matplotlib is the most widely used Python library for plotting.  We can run it in the notebook using the magic command `%matplotlib inline`. If you do not use `%matplotlib inline`, your plots will be generated outside of the notebook and may be difficult to find.  See [the IPython docs](http://ipython.readthedocs.io/en/stable/interactive/plotting.html) for other IPython magics commands.\n",
    "\n",
    "In this lesson, we will only use matplotlib and Seaborn, another package that works in tandem with matplotlib to make nice graphics.  There is a whole range of graphics packages in Python, ranging from basic visualizations to fancy, interactive graphics like [Bokeh](http://bokeh.pydata.org/en/latest/) and [Plotly](https://plot.ly/python/).  \n",
    "\n",
    "We encourage you to explore on your own!  Chances are, if you can imagine a plot you'd like to make, somebody else has written a package to do it.\n",
    "\n",
    "## Markdown\n",
    "Text can be added to Jupyter Notebooks using Markdown cells. Markdown is a popular markup language that is a superset of HTML. To learn more, see [Jupyter's Markdown guide](http://jupyter-notebook.readthedocs.io/en/latest/examples/Notebook/Working%20With%20Markdown%20Cells.html) or revisit the [Reproducible Research lesson on Markdown](https://github.com/Reproducible-Science-Curriculum/introduction-RR-Jupyter/blob/master/notebooks/Navigating%20the%20notebook%20-%20instructor%20script.ipynb). \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The Pandas Library\n",
    "\n",
    "One of the best options for working with tabular data in Python is the Python Data Analysis Library (a.k.a. Pandas). The Pandas library is built on top of the NumPy package (another Python library). Pandas provides data structures, produces high quality plots with matplotlib, and integrates nicely with other libraries that use NumPy arrays. Those familiar with spreadsheets should become comfortable with Pandas data structures."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each time we call a function that’s in a library, we use the syntax `LibraryName.FunctionName`. Adding the library name with a `.` before the function name tells Python where to find the function. In the example above, we have imported Pandas as `pd`. This means we don’t have to type out `pandas` each time we call a Pandas function.\n",
    "\n",
    "See this free [Pandas cheat sheet](https://www.datacamp.com/community/blog/python-pandas-cheat-sheet) from DataCamp for the most common Pandas commands. \n",
    "\n",
    "# Getting data into the notebook\n",
    "\n",
    "We will begin by locating and reading our data which are in a table format as a tab-delimited file. We will use Pandas’ `read_table` function to pull the file directly into a `DataFrame`.\n",
    "\n",
    "## What’s a `DataFrame`?\n",
    "A `DataFrame` is a 2-dimensional data structure that can store in columns data of different types (including characters, integers, floating point values, factors and more). It is similar to a spreadsheet or a SQL table or data.frame in R. A `DataFrame` always has an index (0-based). An index refers to the position of an element in the data structure.\n",
    "\n",
    "Note that we use `pd.read_table`, not just `read_table` or `pandas.read_table`, because we imported Pandas as `pd`.\n",
    "\n",
    "In our original file, the columns in the data set are separated by a TAB. We need to tell the `read_table` function in Pandas that that is the delimiter with `sep = ‘\\t’`.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "try:\n",
    "    url = \"https://raw.githubusercontent.com/Reproducible-Science-Curriculum/data-exploration-RR-Jupyter/master/gapminderDataFiveYear_superDirty.txt\"\n",
    "    gapminder = pd.read_table(url, sep = \"\\t\")\n",
    "except:\n",
    "    #You can also read your table in from a file directory\n",
    "    url = 'gapminderDataFiveYear_superDirty.txt'\n",
    "    gapminder = pd.read_table(url, sep = \"\\t\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The first thing to do when loading data into the notebook is to actually \"look\" at it.  How many rows and columns are there?  What types of variables are in it and what values can they take?\n",
    "\n",
    "There are usually too many rows to print to the screen.  By default, when you type the name of the `DataFrame` and run a cell, Pandas knows to not print the whole thing.  Instead, you will see the first and last few rows with dots in between.  A neater way to see a preview of the dataset is the `head()` method.  Calling `dataset.head()` will display the first 5 rows of the data.  You can specify how many rows you want to see as an argument, like `dataset.head(10)`.  The `tail()` method does the same with the last rows of the `DataFrame`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#head"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#tail"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#gapminder"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sometimes the table has too many columns to print on screen. Calling `df.columns.values` will print all the column names in an array."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Assess the structure and cleanliness\n",
    "\n",
    "\n",
    "## How many rows and columns are in the data?\n",
    "We often want to know how many rows and columns are in the data -- what is the \"shape\" of the `DataFrame`. Shape is an attribute of the `DataFrame`. Pandas has a convenient way for getting that information by using `DataFrame.shape`  (using `DataFrame` here as a generic name for your `DataFrame`). This returns a tuple (immutable values separated by commas) representing the dimensions of the `DataFrame` (rows, columns).<p>\n",
    "To get the shape of the gapminder `DataFrame`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can learn even more about our `DataFrame`. The `info()` method gives a few useful pieces of information, including the shape of the `DataFrame`, the variable type of each column, and the amount of memory stored.\n",
    "\n",
    "The output from `info()` displayed below shows that the fields ‘year’ and ‘pop’ (population) are represented as ‘float’ (that is: numbers with a decimal point). This is not appropriate: year and population should be integers or whole numbers. We can change the data-type with the function `astype()`. The code for `astype()` is shown below; however, we will change the data types later in this lesson."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#info"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `describe()` method will take the numeric columns and provide a summary of their values. This is useful for getting a sense of the ranges of values and seeing if there are any unusual or suspicious numbers.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#describe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `DataFrame` function `describe()` just blindly looks at all numeric variables. We wouldn't actually want to take the mean year. Additionally, we obtain ‘NaN’ values for our quartiles. This suggests we might have missing data which we can (and will) deal with shortly when we begin to clean our data.\n",
    "\n",
    "For now, let's pull out only the columns that are truly continuous numbers (i.e. ignore the description for ‘year’). This is a preview of selecting columns from the data; we'll talk more about how to do it later in the lesson."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#describe continuous"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also extract one specific variable metric at a time if we wish:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#min, max, mean, std, count"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Values in columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, let's say you want to see all the unique values for the `region` column. One way to do this is:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#unique"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This output is useful, but it looks like there may be some formatting issues causing the same region to be counted more than once. Let's take it a step further and find out to be sure. \n",
    "\n",
    "As mentioned previously, the command `value_counts()` gives you a first global idea of your categorical data such as strings. In this case that is the column `region`. Run the code below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# How many unique regions are in the data?\n",
    "# use len\n",
    "\n",
    "# How many times does each unique region occur?\n",
    "# region counts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The table reveals some problems in our data set. The data set covers 12 years, so each ‘region’ should appear 12 times, but some regions appear more than 12 times and others fewer than 12 times. We also see inconsistencies in the region names (string variables are very susceptible to those), for instance:\n",
    "\n",
    "Asia_china\tvs. Asia_China\n",
    "\n",
    "Another type of problem we see is the various names of 'Congo'. In order to analyze this dataset appropriately we need to take care of these issues. We will fix them in the next section on data cleaning."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercises\n",
    "Are there other columns in our `DataFrame` that have categorical variables? If so, run some code to list the categories below. Save your list to a variable and count the number of unique categories using `len`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is the outcome when you run `value_counts()`?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Referencing objects vs copying objects\n",
    "Before we get started with cleaning our data, let's practice good data hygiene by first creating a copy of our original data set. Often, you want to leave the original data untouched.  To protect your original, you can make a copy of your data (and save it to a new `DataFrame` variable) before operating on the data or a subset of the data.  This will ensure that a new version of the original data is created and your original is preserved.\n",
    "\n",
    "###### Why this is important\n",
    "Suppose you take a subset of your `DataFrame` and store it in a new variable, like `gapminder_early = gapminder[gapminder['year'] < 1970]`.  Doing this does not actually create a new object. Instead, you have just given a name to that subset of the original data: `gapminder_early`. This subset still points to the original rows of `gapminder`.  Any changes you make to the new `DataFrame` `gapminder_early` will appear in the corresponding rows of your original `gapminder` `DataFrame` too.  \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "gapminder = pd.read_table(url, sep = \"\\t\")\n",
    "gapminder_copy = gapminder.copy()\n",
    "gapminder_copy.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Handling Missing Data\n",
    "\n",
    "Missing data (often denoted as 'NaN'- not a number- in Pandas, or as 'null') is an important issue to handle because Pandas cannot compute on rows or columns with missing data. 'NaN' or 'null' does not mean the value at that position is zero, it means that there is no information at that position. Ignoring missing data doesn't make it go away. There are different ways of dealing with it which include:\n",
    "\n",
    "* analyzing only the available data (i.e. ignore the missing data)\n",
    "* input the missing data with replacement values and treating these as though they were observed\n",
    "* input the missing data and account for the fact that these were inputed with uncertainty (ex: create a new boolean variable so you know that these values were not actually observed)\n",
    "* use statistical models to allow for missing data--make assumptions about their relationships with the available data as necessary\n",
    "\n",
    "For our purposes with the dirty gapminder data set, we know our missing data is excess (and unnecessary) and we are going to choose to analyze only the available data. To do this, we will simply remove rows with missing values.\n",
    "\n",
    "This is incredibly easy to do because Pandas allows you to either remove all instances with null data or replace them with a particular value.\n",
    "\n",
    "`df = df.dropna()` drops rows with any column having NA/null data.  `df = df.fillna(value)` replaces all NA/null data with the argument `value`.\n",
    "\n",
    "For more fine-grained control of which rows (or columns) to drop, you can use `how` or `thresh`. These are more advanced topics and are not covered in this lesson; you are encouraged to explore them on your own."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# drop na"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Changing Data Types\n",
    "We can change the data-type with the function `astype()`. The code for `astype()` is shown below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#astype()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Handling (Unwanted) Repetitive Data\n",
    "You can identify which observations are duplicates.\n",
    "The call `df.duplicated()` will return boolean values for each row in the `DataFrame` telling you whether or not a row is repeated.\n",
    "\n",
    "In cases where you don’t want repeated values (we wouldn’t--we only want each country to be represented once for every relevant year), you can easily drop such duplicate rows with the call `df.drop_duplicates()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# duplicated() #shows we have a repetition within the first __ rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look at the first five rows of our data set again (remember we removed the NaNs):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# How do we look at the first 5 rows?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our statement from above is correct, rows 1 & 2 are duplicated. Let's fix that:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# df.drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reindexing with `reset_index()`\n",
    "Now we have 1704 rows, but our indexes are off because we removed duplicate rows. We can reset our indices easily with the call `reset_index(drop=True)`. Remember, Python is 0-indexed so our indices will be valued 0-1703.\n",
    "\n",
    "The concept of reindexing is important. When we removed some of the messier, unwanted data, we had \"gaps\" in our index values. By correcting this, we can improve our search functionality and our ability to perform iterative functions on our cleaned data set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## Handling Inconsistent Data\n",
    "\n",
    "The `region` column is a bit too messy for what we'd like to do.\n",
    "The `value_counts()` operation above revealed some issues that we can solve with several different techniques.\n",
    "\n",
    "### String manipulations\n",
    "\n",
    "Common problems with string variables are leading and trailing white space and upper case vs. lower case in the same data set.\n",
    "\n",
    "The following three commands remove all such lingering spaces (left and right) and put everything in lowercase. If you prefer, the three commands can be written in one single line (which is a concept called chaining). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "gapminder_copy['region'] = gapminder_copy['region'].str.lstrip() # Strip white space on left\n",
    "gapminder_copy['region'] = gapminder_copy['region'].str.rstrip() # Strip white space on right\n",
    "gapminder_copy['region'] = gapminder_copy['region'].str.lower() # Convert to lowercase\n",
    "gapminder_copy['region'].value_counts() # How many times does each unique region occur?\n",
    "\n",
    "# We could have done this in one line!\n",
    "# gapminder_copy['region'] = gapminder_copy['region'].str.lstrip().str.rstrip().lower()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### regex + `replace()`\n",
    "\n",
    "A regular expression, a.k.a. regex, is a sequence of characters that define a search pattern. In a regular expression, the symbol “*” matches the preceding character 0 or more times, whereas “+” matches the preceding character 1 or more times. “.” matches any single character. Writing “x|y” means to match either ‘x’ or ‘y’.\n",
    "\n",
    "For more regex shortcuts (cheatsheet): https://www.shortcutfoo.com/app/dojos/regex/cheatsheet\n",
    "\n",
    "To play \"regex golf,\" check out this [tutorial by Peter Norvig](https://www.oreilly.com/learning/regex-golf-with-peter-norvig) (you may need an O'Reilly or social media account to play).\n",
    "\n",
    "Pandas allows you to use `regex` in its `replace()` function -- when a regex term is found in an element, the element is then replaced with the specified replacement term. In order for it to appropriately correct elements, both regex and inplace variables need to be set to `True` (as their defaults are False). This ensures that the initial input string is read as a regular expression and that the elements will be modified in place.\n",
    "\n",
    "For more documentation on the replace method: http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.replace.html\n",
    "\n",
    "Here's an incorrect regex example: we create a temporary `DataFrame` in which a regex pulls all values that contain the term “congo”. Unfortunately, this creates 24 instances of the Democratic Republic of the Congo -- this is an error in our cleaning! We can revert back to the non-temporary `DataFrame` and correctly modify our regex to isolate only the Democratic Republic instances (as opposed to including the Republic as well)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# This gives a problem -- 24 values of the congo!\n",
    "temp = gapminder_copy['region'].replace(\".*congo.*\", \"africa_dem rep congo\", regex=True)\n",
    "temp.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# What happened? This shows all the rows that have congo in the name.\n",
    "gapminder_copy[gapminder_copy[\"region\"].str.contains('congo')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using regex to correctly consolidate the Congo regions...\n",
    "\n",
    "As noted above, regular expressions (often simply \"regex\") provide a powerful \n",
    "tool for fixing errors that arise in strings. In order to correctly label the \n",
    "two different countries that include the word \"congo\", we need to design and\n",
    "use (via `pd.df.replace()`) a regex that correctly differentiates between the\n",
    "two countries.\n",
    "\n",
    "Recall that the \".\" is the wildcard (matching any single character); combining \n",
    "this with \"*\" allows us to match any number of single characters an unspecified \n",
    "number of times. By combining these characters with substrings corresponding to\n",
    "variations in the naming of the Democratic Republic of the Congo, we can\n",
    "correctly normalize the name.\n",
    "\n",
    "If you feel that the use of regex is not particularly straightforward, you are\n",
    "correct -- appropriately using these tools takes a great deal of time to master.\n",
    "When designing regex for these sorts of tasks, you might find the following\n",
    "prototyper helpful: https://regex101.com/  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "gapminder_copy['region'].replace(\".*congo, dem.*\", \"africa_dem rep congo\", regex=True, inplace=True)\n",
    "gapminder_copy['region'].replace(\".*_democratic republic of the congo\", \"africa_dem rep congo\", regex=True, inplace=True)\n",
    "gapminder_copy['region'].value_counts() # Now it's fixed."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise (regex):\n",
    "\n",
    "Now that we've taken a close look at how to properly design and use regex to\n",
    "clean string entries in our data, let's try to normalize the naming of a few\n",
    "other countries. Using the pandas code we constructed above as a template,\n",
    "construct similar code (using `pd.df.replace()`) to set the naming of the Ivory\n",
    "Coast and Canada to \"africa_cote d'ivoire\" and \"americas_canada\", respectively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Try this on your own"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tidy data\n",
    "\n",
    "Having what is called a \"_Tidy_ data set\" can make cleaning, analyzing, and visualizing your data much easier. You should aim for having Tidy data when cleaning and preparing your data set for analysis. Two of the important aspects of Tidy data are:\n",
    "* every variable has its own column\n",
    "* every observation has its own row\n",
    "\n",
    "(There are other aspects of Tidy data, here is a good blog post about Tidy data in Python: http://www.jeannicholashould.com/tidy-data-in-python.html)\n",
    "\n",
    "Currently the gapminder dataset has a single column for continent and country (the ‘region’ column). We can split that column into two, by using the underscore that separates continent from country.\n",
    "We can create a new column in the `DataFrame` by naming it before the = sign:\n",
    "`gapminder['country'] = `\n",
    "\n",
    "The following commands use the function `split()` to split the string at the underscore (the first argument), which results in a list of two elements: before and after the \\_. The second argument tells `split()` that the split should take place only at the first occurrence of the underscore."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "gapminder_copy['country']=gapminder_copy['region'].str.split('_', 1).str[1]\n",
    "gapminder_copy['continent']=gapminder_copy['region'].str.split('_', 1).str[0]\n",
    "gapminder_copy.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Removing and renaming columns\n",
    "\n",
    "We have now added the columns `country` and `continent`, but we still have the old `region` column as well. In order to remove that column we use the `drop()` command. The first argument of the `drop()` command is the name of the element to be dropped. The second argument is the *axis* number:  \n",
    "*0 for row, 1 for column*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# drop()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, it is a good idea to look critically at your column names. Use lowercase for all column names to avoid confusing `gdppercap` with `gdpPercap` or `GDPpercap`. Avoid spaces in column names to simplify manipulating your data - look out for lingering white space at the beginning or end of your column names. The following code turns all column names to lowercase. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# str.lower()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We also want to remove the space from the `life exp` column name. We can do that with Pandas `rename` method. It takes a dictionary as its argument, with the old column names as keys and new column names as values.\n",
    "\n",
    "If you're unfamiliar with dictionaries, they are a very useful data structure in Python.  You can read more about them [here](https://docs.python.org/3/tutorial/datastructures.html#dictionaries)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# rename columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merging data\n",
    "\n",
    "Often we have more than one `DataFrame` that contains parts of our data set and we want to put them together. This is known as merging the data.\n",
    "\n",
    "Our advisor now wants us to add a new country called The People's Republic of Berkeley to the gapminder data set that we have cleaned up. Our goal is to get this new data into the same `DataFrame` in the same format as the gapminder data and, in this case, we want to concatenate (add) it onto the end of the gapminder data.\n",
    "\n",
    "Concatentating is a simple form of merging, there are many useful (and more complicated) ways to merge data.  If you are interested in more information, the [Pandas Documentation](http://pandas.pydata.org/pandas-docs/stable/merging.html) is useful."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "try:\n",
    "    url2 = 'https://raw.githubusercontent.com/Reproducible-Science-Curriculum/data-exploration-RR-Jupyter/master/PRB_data.txt'\n",
    "    PRB = pd.read_table(url2, sep = \"\\t\")\n",
    "except:\n",
    "    url2 = 'PRB_data.txt'\n",
    "    PRB = pd.read_table(url2, sep = \"\\t\")\n",
    "    \n",
    "PRB.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## bring in PRB data (no major problems) and make it conform to the gapminder at this point\n",
    "# clean the data to look like the current gapminder\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# double check that the gapminder is the same\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# combine the data sets with concat\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that the `DataFrames` have been concatenated, notice that the index is funky. It repeats the numbers 0 - 11 in the `peoples republic of berkeley data`. <p>\n",
    "#### **Exercise:** fix the index.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# our code for fixing index\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Subsetting and sorting\n",
    "\n",
    "There are many ways in which you can manipulate a Pandas `DataFrame` - here we will discuss two approaches: subsetting and sorting.\n",
    "\n",
    "##### Subsetting\n",
    "We can subset (or slice) by giving the numbers of the rows you want to see between square brackets.\n",
    "\n",
    "*REMINDER:* Python uses 0-based indexing. This means that the first element in an object is located at position 0. this is different from other tools like R and Matlab that index elements within objects starting at 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Select the first 15 rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Use a different way to select the first 15 rows\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Select the last 10 rows\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise\n",
    "\n",
    "*What does the negative number (in the third cell) mean?*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Answer:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*What happens when you leave the space before or after the colon empty?*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Answer:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Subsetting can also be done by selecting for a particular column or for a particular value in a column; for instance select the rows that have ‘africa’ in the column ‘continent. Note the double equal sign: single equal signs are used in Python to assign something to a variable. The double equal sign is a comparison: the variable to the left has to be exactly equal to the string to the right.\n",
    "\n",
    "**There other ways of subsetting that are worth knowing about. Do an independent reading of using .loc/.iloc with `DataFrames`**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Select for a particular column\n",
    "\n",
    "\n",
    "#this syntax, calling the column as an attribute, gives you the same output\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also create a new object that contains the data within the `continent` column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Sorting\n",
    "Sorting may help to further organize and inspect your data. The command `sort_values()` takes a number of arguments; the most important ones are `by` and `ascending.` The following command will sort your `DataFrame` by year, beginning with the most recent."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#sort_values()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise\n",
    "\n",
    "Organize your data set by country, from ‘Afganistan’ to ‘Zimbabwe’."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summarize and plot\n",
    "\n",
    "Summaries (but can’t *say* statistics…)\n",
    "* Sort data\n",
    "* Basic summaries\n",
    "\n",
    "\n",
    "Plots \n",
    "* of subsets \n",
    "* single variables\n",
    "* pairs of variables\n",
    "* Matplotlib syntax (w/ Seaborn for defaults (prettier, package also good for more analysis later...))\n",
    "\n",
    "Exploring is often iterative - summarize, plot, summarize, plot, etc. - sometimes it branches…\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Summarizing data\n",
    "\n",
    "Remember that the `info()` method gives a few useful pieces of information, including the shape of the `DataFrame`, the variable type of each column, and the amount of memory stored. We can see many of our changes (continent and country columns instead of region, higher number of rows, etc.) reflected in the output of the `info()` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "gapminder_comb.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We also saw above that the `describe()` method will take the numeric columns and give a summary of their values. We have to remember that we changed the column names and this time it shouldn't have NaNs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "gapminder_comb[['pop', 'lifeexp', 'gdppercap']].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### More summaries\n",
    "\n",
    "What if we just want a single value, like the mean of the population? We can call mean on a single column this way:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What if we want to know the mean population by _continent_? Then we need to use the Pandas `groupby()` method and tell it which column we want to group by.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What if we want to know the median population by continent?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or the number of entries (rows) per continent?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sometimes we don't want a whole `DataFrame`. Here is another way to do this that produces a `Series` that tells us number of entries (rows) as opposed to a `DataFrame`. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also look at the mean GDP per capita of each country: \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What if we wanted a new `DataFrame` that just contained these summaries? This could be a table in a report, for example."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "continent_mean_pop = gapminder_comb[['continent', 'pop']].groupby(by='continent').mean()\n",
    "continent_mean_pop = continent_mean_pop.rename(columns = {'pop':'meanpop'})\n",
    "continent_row_ct = gapminder_comb[['continent', 'country']].groupby(by='continent').count()\n",
    "continent_row_ct = continent_row_ct.rename(columns = {'country':'nrows'})\n",
    "continent_median_pop = gapminder_comb[['continent', 'pop']].groupby(by='continent').median()\n",
    "continent_median_pop = continent_median_pop.rename(columns = {'pop':'medianpop'})\n",
    "gapminder_summs = pd.concat([continent_row_ct,continent_mean_pop,continent_median_pop], axis=1)\n",
    "gapminder_summs = gapminder_summs.rename(columns = {'y':'year'})\n",
    "gapminder_summs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Visualization with `matplotlib`\n",
    "\n",
    "Recall that [matplotlib](http://matplotlib.org) is Python's main visualization \n",
    "library. It provides a range of tools for constructing plots and numerous \n",
    "high-level plotting libraries (e.g., [Seaborn](http://seaborn.pydata.org)) are \n",
    "built with matplotlib in mind. When we were in the early stages of setting up \n",
    "our analysis, we loaded these libraries like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "import seaborn as sns\n",
    "sns.set()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Consider the above three commands to be essential practice for plotting (as\n",
    "essential as **`import`** `pandas` **`as`** `pd` is for data munging).*\n",
    "\n",
    "Now, let's turn to data visualization. In order to get a feel for the properties\n",
    "of the data set we are working with, data visualization is key. While, we will\n",
    "focus only on the essentials of how to properly construct plots in univariate\n",
    "and bivariate settings here, it's worth noting that both matplotlib and Seaborn\n",
    "support a diversity of plots: [matplotlib \n",
    "gallery](http://matplotlib.org/gallery.html), [Seaborn\n",
    "gallery](http://seaborn.pydata.org/examples/). \n",
    "\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Single variables\n",
    "\n",
    "* __Histograms__ - provide a quick way of visualizing the distribution of numerical\n",
    "  data, or the frequencies of observations for categorical variables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#import numpy as np\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* __Boxplots__ - provide a way of comparing the summary measures (e.g., max, min,\n",
    "  quartiles) across variables in a data set. Boxplots can be particularly useful with larger data sets.\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pairs of variables\n",
    "\n",
    "* __Scatterplots__ - visualization of relationships across two variables..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# scatter plot goes here\n",
    "\n",
    "plt.scatter(gapminder_copy['gdppercap'], gapminder_copy['lifeexp'])\n",
    "plt.xlabel('gdppercap')\n",
    "plt.ylabel('lifeexp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# let's try plotting the log of x\n",
    "plt.scatter(gapminder_copy['gdppercap'], gapminder_copy['lifeexp'])\n",
    "plt.xscale('log')\n",
    "plt.xlabel('gdppercap')\n",
    "plt.ylabel('lifeexp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Try creating a plot on your own"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "### Why use `seaborn`?\n",
    "\n",
    "As noted above, Seaborn is a high-level plotting library for statistical data \n",
    "visualization. In addition to simplifying plotting, it also provides facilities \n",
    "for customizing matplotlib plots (accessible via `sns.set()`)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Saving your plots as image files  \n",
    "If you'd like to save your plots as an image file, you can run `fig.savefig('my_figure.png')` where `\"my_figure\"` is the file name.    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Interpret plots and summaries\n",
    "\n",
    "### Exploration is an iterative process\n",
    "\n",
    "In this lesson, we've taken the raw data and worked through steps to prepare it for analysis, but we have not yet done any \"data analysis\".  This part of the data workflow can be thought of as \"exploratory data analysis\", or EDA.  Many of the steps we've shown are aimed at uncovering interesting or problematic things in the dataset that are not immediately obvious.  We want to stress that when you're doing EDA, it will not necessarily be a linear workflow like what we have shown.  When you plot or summarize your data, you may uncover new issues: for example, we saw this when we made a mistake fixing the naming conventions for the Democratic Republic of Congo.  You might discover outliers, unusually large values, or points that don't make sense in your plots.  Clearly, the work here isn't done: you'll have to investigate these points, decide how to fix any potential problems, document the reasoning for your actions, and check that your fix actually worked.\n",
    "\n",
    "On the other hand, plots and summaries might reveal interesting questions about your data.  You may return to the cleaning and prepping steps in order to dig deeper into these questions.  You should continuously refine your plots to give the clearest picture of your hypotheses.\n",
    "\n",
    "### Interesting findings\n",
    "\n",
    "What's interesting about this data set? What would you like to investigate further?\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Answer:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Putting it all together\n",
    "\n",
    "On your own or with a partner, using the techniques you've learned in this lesson, explore one or both of the data sets provided in Data Carpentry's Lessons [Introduction to Genomics](http://www.datacarpentry.org/introduction-genomics/02-examining-sra-runtable.html) and [Python for Ecologists](http://www.datacarpentry.org/python-ecology-lesson/01-starting-with-data/). We've provided headers to guide you through the process.\n",
    "\n",
    "* The Genomics Lesson uses the [Lenski dataset](http://www.ncbi.nlm.nih.gov/sra?term=SRA026813). Follow section A. in [these directions](http://www.datacarpentry.org/introduction-genomics/02-examining-sra-runtable.html) to download the .csv file.\n",
    "* The Ecology Lesson uses [surveys.csv](https://ndownloader.figshare.com/files/2292172) from the Portal Teaching data, a subset of the data from [Ernst et al Long-term monitoring and experimental manipulation of a Chihuahuan Desert ecosystem near Portal, Arizona, USA](http://www.esapubs.org/archive/ecol/E090/118/default.htm). "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Import your data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Describe your data set here using *Markdown*\n",
    "What is the general shape of your `DataFrame`? What are the datatypes? Are there missing values? What questions do you have about your data set and how will you answer those questions?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Answers:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Calculate summary statistics for your data set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Is this a tidy data set? Why or why not?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Create bar, box, and scatter plots of your data set. What insights do these plots provide?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [default]",
   "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}