Part 2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Part 2 - Open your data & start exploring\n",
    "\n",
    "For this class, we're using two data sets, both of which should be in this repository. If you want the originals, see:\n",
    "\n",
    "* `PEP_2014_PEPANNRES_with_ann.csv` - US Census Population Estimates for populated places in Colorado - [from American Factfinder](http://factfinder.census.gov/bkmk/table/1.0/en/PEP/2014/PEPANNRES/0400000US08.16200)\n",
    "* `2014_gaz_place_08.txt` - [from the Census website](https://www.census.gov/geo/maps-data/data/gazetteer2014.html)\n",
    "\n",
    "As you read through this notebook, practice typing the given code into the next cell. Remember, _shift-enter_ runs the code and moves you to the next cell. Try to get comfortable using tab-completion (which even works for filenames and dictionary keys). \n",
    "\n",
    "Or you can copy/paste the code if you really want to. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading a data file\n",
    "\n",
    "\n",
    "Before anything else, we import `pandas`. By convention, it's imported as `pd` to save typing. Reading in a CSV is just as easy as you'd guess. We'll load the Colorado population estimates. \n",
    "\n",
    "```python\n",
    "import pandas as pd\n",
    "popest = pd.read_csv('PEP_2014_PEPANNRES_with_ann.csv') \n",
    "popest.head()\n",
    "```\n",
    "\n",
    "Remember, after you are done entering code, press _shift-tab_ to execute the code and move to the next cell. Also, you won't be able to tab-complete `read_csv` for this one because the `import` hasn't run, but you _can_ complete that long CSV filename. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Above you should see the first five rows of the data that you read in."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loading, refined\n",
    "\n",
    "Notice row `0` -- it's not a data row, it's a second header. This kind of thing is common in Excel, but it can tangle you up when you use python code for data analysis. Let's fix that. This might be a good one for copy/paste!\n",
    "\n",
    "```python\n",
    "popest = pd.read_csv('PEP_2014_PEPANNRES_with_ann.csv', \n",
    "                      skiprows=2, # we can't use row 0 and skip only row 1 so we skip both\n",
    "                      header=None, # don't treat the first non-skipped row as a header\n",
    "                      names=popest.columns) # use the columns we just loaded for simplicity\n",
    "popest.head()\n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check and correct\n",
    "\n",
    "Huh. Look at the `GEO.id2` column. How is it different from when you used the `head` command before?\n",
    "\n",
    "Hopefully you see that it lost the leading `0`. This happens a lot, so get used to looking for it. The most common way it plagues most of us in the US is with ZIP codes.\n",
    "\n",
    "If you're never going to use a column that has a problem like this, just keep moving on. But in our case, we know that the FIPS code will help us link this data to other datasets. We'll see this in action below, but for now, trust us that it's worth taking care of. To take care of it, we specify the `dtype`. We're going to skip past the nuances here and just specify that the type is `S` (for \"string\").\n",
    "\n",
    "One more little thing: Colorado is one of a few states which has a few place names that are not simple ASCII (e.g. Cañon City). `pandas` won't complain about this, but you should be on the lookout. We happen to know that the Census Bureau uses `Latin-1` to encode files which have names in them to handle this, so here we are also using the `encoding` argument to make sure strings are handled correctly.\n",
    "\n",
    "So, once more, from the top.\n",
    "```python\n",
    "popest = pd.read_csv('PEP_2014_PEPANNRES_with_ann.csv', \n",
    "                      skiprows=2, # we can't use row 0 and skip only row 1 so we skip both\n",
    "                      header=0, # and tell pandas that it shouldn't treat the first non-skipped row as a header\n",
    "                      names=popest.columns,\n",
    "                      encoding='latin-1',\n",
    "                      dtype={'GEO.id2': 'S'} \n",
    "                    )\n",
    "popest.head()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "OK, good, we have our leading zeros back.\n",
    "\n",
    "### Drop and rename columns\n",
    "\n",
    "That `GEO.id` column seems redundant. Also, while maybe important in some cases, we don't really need the Decennial 2010 estimate or the base value used for estimates. And some of those column names are ugly.\n",
    "\n",
    "```python\n",
    "# for 'drop', don't forget axis=1 if you're dropping columns; default is to try to drop a row.\n",
    "popest = popest.drop(['GEO.id', 'rescen42010', 'resbase42010'], axis=1) \n",
    "popest = popest.rename(columns={ # yes, we could have done this when we read in the CSV\n",
    "        'GEO.display-label': 'name',\n",
    "        'GEO.id2': 'fips',\n",
    "        'respop72010': '2010', \n",
    "        'respop72011': '2011', \n",
    "        'respop72012': '2012', \n",
    "        'respop72013': '2013', \n",
    "        'respop72014': '2014', \n",
    "    })\n",
    "popest.head()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note:** that `drop` command is the kind of thing that will give you errors if you run it a second time on a `DataFrame` that no longer has those columns. Don't panic.\n",
    "\n",
    "Ah, that‘s a lot tidier. One thing you should note: in both of the last two examples, we reassigned `popest` to the result of a function call. This is because these and many other operations in `pandas` don't change the original `DataFrame`. Instead, they return a new \"view\" of the same data. \n",
    "\n",
    "You will inevitably forget this and then wonder why that change you thought you made isn't there. This is why. The good news is that it will become a habit, mostly."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring your data\n",
    "\n",
    "### Sorting\n",
    "OK, now that we have the data loaded, what do we have? Maybe you want to look at the extremes of the list: which places have the most people? Which ones have the fewest? `DataFrame` has a `sort_values` function which makes this easy:\n",
    "\n",
    "```python\n",
    "popest.sort_values(by='2014').head(10)\n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default, sort is \"ascending\". If you want the ten biggest, you have to say so:\n",
    "\n",
    "```python\n",
    "popest.sort_values(by='2014',ascending=False).head(10)\n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Summary statistics\n",
    "We see there a pretty big range between the biggest and smallest places, but what about the distribution? The `describe` function shows basic summary stats for every numeric column in your `DataFrame`\n",
    "\n",
    "```python\n",
    "popest.describe()\n",
    "```\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that the average population is trending upward decisively, although the quartile values don't change that much. That suggests that the action is really concentrated in the largest cities. There are a lot of other ways you can get a feel for the data in a dataframe, including visualization techniques, but we'll save those for later."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Computing your own values\n",
    "\n",
    "Let's add a column reflecting the net change in population.\n",
    "\n",
    "```python\n",
    "popest['change'] = ( (popest['2014'] - popest['2010']) / popest['2010'] )\n",
    "popest.sort_values('change',ascending=False).head()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Timnath](https://en.wikipedia.org/wiki/Timnath,_Colorado) is booming! But maybe we want to focus on the bigger population centers. Can we just see those?\n",
    "\n",
    "### Filtering\n",
    "\n",
    "`pandas` syntax for selecting a subset of a `DataFrame` is a little weird, although if you've used `R` at all, you'll be familiar. Let's start with an example, and we'll explain after.\n",
    "\n",
    "Let's see just the places that were at least 100,000 people in 2014.\n",
    "\n",
    "```python\n",
    "popest[ popest['2014'] > 100000 ]\n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ok, but which ones changed the most? You can just chain methods together.\n",
    "\n",
    "```python\n",
    "popest[ popest['2014'] > 100000 ].sort_values(by='change',ascending=False).head()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### That filtering syntax\n",
    "\n",
    "Technically, when you filter, you pass a `Series` whose values are all `True` or `False`. The function returns a dataframe with all the same columns, but only including rows for which the matching value was `True`. \n",
    "\n",
    "Let's see how many places lost population?\n",
    "\n",
    "```python\n",
    "num_declines = len(popest[ popest['change'] < 0 ])\n",
    "print \"There were {} places which lost population.\".format(num_declines)\n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merging datasets\n",
    "\n",
    "Maybe you want to look on a map, to see if there are patterns to this growth. We're not going to get into mapping with `pandas` and `python`, but we'll grab another dataset which has the locations of these places, hook the two together and export the result so we can use it with another mapping tool.\n",
    "\n",
    "The Census bureau publishes [gazetteer files](https://www.census.gov/geo/maps-data/data/gazetteer2014.html) every year which provide the area of each shape and the coordinates of its centroid. \n",
    "\n",
    "### Loading data from the web\n",
    "\n",
    "Just because we can, here's an example of how to load data into a `DataFrame` via a URL.\n",
    "\n",
    "```python\n",
    "from urllib2 import urlopen\n",
    "url = 'http://www2.census.gov/geo/docs/maps-data/data/gazetteer/2014_Gazetteer/2014_gaz_place_08.txt'\n",
    "response = urlopen(url)\n",
    "# We happen to know that this file uses tab, not comma, to separate values,\n",
    "# and note we're controlling the encoding and the GEOID dtype\n",
    "# these are things you have to check out for yourself as you load data\n",
    "gaz = pd.read_csv(response,delimiter='\\t',dtype={'GEOID': 'S'},encoding='latin-1')\n",
    "gaz.head()\n",
    "```\n",
    "\n",
    "(If you're having trouble with the internet, a copy of this file is in the respository too.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Doing the actual merge\n",
    "\n",
    "This is super easy. There are a lot of options you can pursue if you may have rows in one `DataFrame` which don't match the other, but we're not faced with that.\n",
    "\n",
    "```python\n",
    "merged = pd.merge(left=popest,right=gaz,left_on='fips',right_on='GEOID')\n",
    "merged.head()\n",
    "```    \n",
    "\n",
    "If you have experience with SQL, you may be interested to read `pandas` [comparison with SQL](http://pandas.pydata.org/pandas-docs/stable/comparison_with_sql.html#compare-with-sql-join)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Another way to reduce the columns\n",
    "\n",
    "For this exercise, we only want a few of the columns. Besides the `drop` command which we saw before, you can pass in a list of column names to the dataframe to select a subset. We're doing a little bit of other cleanup here too.\n",
    "\n",
    "```python\n",
    "merged = merged.rename(columns=lambda x: x.strip()) # turns out one column name has bogus whitespace\n",
    "merged = merged.rename(columns={'INTPTLAT': 'lat', 'INTPTLONG': 'lng'})\n",
    "merged = merged[['fips','name','change','lat','lng']]\n",
    "merged.head()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exporting a dataset\n",
    "\n",
    "This is as easy as could be too. Just remember that we're dealing with place names like _Cañon city_ and keep track of the encoding. The default is ASCII so if you forgot the `encoding` argument with our current dataset, you'd get an error.\n",
    "\n",
    "```python\n",
    "merged.to_csv('ready_for_mapping.csv',encoding='utf-8')\n",
    "%ls -l ready*\n",
    "```\n",
    "\n",
    "If you have the `xlwt` library installed, you can also write directly to an Excel file:\n",
    "\n",
    "```python\n",
    "merged.to_excel('ready_for_mapping.xls')\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Next steps\n",
    "\n",
    "Now that you know some things about reading, writing, and exploring data with `pandas`, check out [part 3](Part%203.ipynb) to learn more ways to work with data."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}