intro-to-pandas-1-Data-Structures.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction to pandas\n",
    "\n",
    "_Adapted from original materials by [Greg Reda](http://gregreda.com/)._\n",
    "\n",
    "## What is it?\n",
    "[pandas](http://pandas.pydata.org/) is an open source [Python](http://www.python.org/) library for data analysis. Python has always been great for prepping and munging data, but historically it has not been great for analysis - you'd usually end up using [R](http://www.r-project.org/) or loading it into a database and using SQL (or worse, Excel). pandas makes Python great for analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from IPython.display import display\n",
    "# Switch to truncated view if too many columns\n",
    "pd.set_option('max_columns', 50)\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Structures\n",
    "pandas introduces two new data structures to Python - [Series](http://pandas.pydata.org/pandas-docs/dev/dsintro.html#series) and [DataFrame](http://pandas.pydata.org/pandas-docs/dev/dsintro.html#dataframe), both of which are built on top of [NumPy](http://www.numpy.org/) (this means it's fast)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Series"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A Series is a one-dimensional object similar to an array, list, or column in a table. It will assign a labeled index to each item in the Series. By default, each item will receive an index label from 0 to N, where N is the length of the Series minus one."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# create a Series with an arbitrary list\n",
    "s = pd.Series([7, 'Heisenberg', 3.14, -1789710578, 'Happy Eating!'])\n",
    "s"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alternatively, you can specify an index to use when creating the Series."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "s = pd.Series([7, 'Heisenberg', 3.14, -1789710578, 'Happy Eating!'],\n",
    "              index=['A', 'Z', 'C', 'Y', 'E'])\n",
    "s"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The Series constructor can convert a dictonary as well, using the keys of the dictionary as its index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "d = {'Chicago': 1000, 'New York': 1300, 'Portland': 900, 'San Francisco': 1100,\n",
    "     'Austin': 450, 'Boston': None}\n",
    "cities = pd.Series(d)\n",
    "cities"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can use the index to select specific items from the Series ..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cities['Chicago']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cities[['Chicago', 'Portland', 'San Francisco']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or you can use boolean indexing for selection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cities[cities < 1000]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That last one might be a little weird, so let's make it more clear - `cities < 1000` returns a Series of True/False values, which we then pass to our Series `cities`, returning the corresponding True items."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "less_than_1000 = cities < 1000\n",
    "print(less_than_1000)\n",
    "print('\\n')\n",
    "print(cities[less_than_1000])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also change the values in a Series on the fly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# changing based on the index\n",
    "print('Old value:', cities['Chicago'])\n",
    "cities['Chicago'] = 1400\n",
    "print('New value:', cities['Chicago'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# changing values using boolean logic\n",
    "print(cities[cities < 1000])\n",
    "print('\\n')\n",
    "cities[cities < 1000] = 750\n",
    "\n",
    "print(cities[cities < 1000])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What if you aren't sure whether an item is in the Series?  You can check using idiomatic Python."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print('Seattle' in cities)\n",
    "print('San Francisco' in cities)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mathematical operations can be done using scalars and functions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# divide city values by 3\n",
    "cities/3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# square city values\n",
    "np.square(cities)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can add two Series together, which returns a union of the two Series with the addition occurring on the shared index values.  Values on either Series that did not have a shared index will produce a NULL/NaN (not a number)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(cities[['Chicago', 'New York', 'Portland']])\n",
    "print('\\n')\n",
    "print(cities[['Austin', 'New York']])\n",
    "print('\\n')\n",
    "print(cities[['Chicago', 'New York', 'Portland']] + cities[['Austin', 'New York']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that because Austin, Chicago, and Portland were not found in both Series, they were returned with NULL/NaN values.\n",
    "\n",
    "NULL checking can be performed with `isnull` and `notnull`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# returns a boolean series indicating which values aren't NULL\n",
    "print(cities)\n",
    "cities.notnull()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# use boolean logic to grab the NULL cities\n",
    "print(cities.isnull())\n",
    "print('\\n')\n",
    "print(cities[cities.isnull()])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrame\n",
    "\n",
    "A DataFrame is a tablular data structure comprised of rows and columns, akin to a spreadsheet, database table, or R's data.frame object. You can also think of a DataFrame as a group of Series objects that share an index (the column names).\n",
    "\n",
    "For the rest of the tutorial, we'll be primarily working with DataFrames."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading Data\n",
    "\n",
    "To create a DataFrame out of common Python data structures, we can pass a dictionary of lists to the DataFrame constructor.\n",
    "\n",
    "Using the `columns` parameter allows us to tell the constructor how we'd like the columns ordered. By default, the DataFrame constructor will order the columns alphabetically (though this isn't the case when reading from a file - more on that next)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "data = {'year': [2010, 2011, 2012, 2011, 2012, 2010, 2011, 2012],\n",
    "        'team': ['Bears', 'Bears', 'Bears', 'Packers', 'Packers', 'Lions', 'Lions', 'Lions'],\n",
    "        'wins': [11, 8, 10, 15, 11, 6, 10, 4],\n",
    "        'losses': [5, 8, 6, 1, 5, 10, 6, 12]}\n",
    "football = pd.DataFrame(data, columns=['year', 'team', 'wins', 'losses'])\n",
    "football"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Much more often, you'll have a dataset you want to read into a DataFrame. Let's go through several common ways of doing so."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**CSV**\n",
    "\n",
    "Reading a CSV is as simple as calling the *read_csv* function. By default, the *read_csv* function expects the column separator to be a comma, but you can change that using the `sep` parameter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%cd ./data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!ls -lrt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Source: baseball-reference.com/players/r/riverma01.shtml\n",
    "!head -n 5 mariano-rivera.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from_csv = pd.read_csv('mariano-rivera.csv')\n",
    "from_csv.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our file had headers, which the function inferred upon reading in the file. Had we wanted to be more explicit, we could have passed `header=None` to the function along with a list of column names to use:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Source: pro-football-reference.com/players/M/MannPe00/touchdowns/passing/2012/\n",
    "!head -n 5 peyton-passing-TDs-2012.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cols = ['num', 'game', 'date', 'team', 'home_away', 'opponent',\n",
    "        'result', 'quarter', 'distance', 'receiver', 'score_before',\n",
    "        'score_after']\n",
    "no_headers = pd.read_csv('peyton-passing-TDs-2012.csv', sep=',', header=None,\n",
    "                         names=cols)\n",
    "no_headers.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "pandas various *reader* functions have many parameters allowing you to do things like skipping lines of the file, parsing dates, or specifying how to handle NA/NULL datapoints.\n",
    "\n",
    "There's also a set of *writer* functions for writing to a variety of formats (CSVs, HTML tables, JSON).  They function exactly as you'd expect and are typically called `to_format`:\n",
    "\n",
    "```python\n",
    "my_dataframe.to_csv('path_to_file.csv')\n",
    "```\n",
    "\n",
    "[Take a look at the IO documentation](http://pandas.pydata.org/pandas-docs/stable/io.html) to familiarize yourself with file reading/writing functionality."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# You can also get help by appending ? to any command\n",
    "#pd.read_csv?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**_Exercise_**\n",
    "* Search the web for a sample CSV file (with/without headers, colon-separated, etc), save it, and load it into a pandas DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**PostgreSQL Database** \n",
    "\n",
    "pandas also has some support for reading/writing DataFrames directly from/to a database [[docs](http://pandas.pydata.org/pandas-docs/stable/io.html#sql-queries)].  You'll typically just need to pass a connection object to the `read_sql` or `to_sql` functions within the `pandas.io` module.\n",
    "\n",
    "Note that `to_sql` executes as a series of INSERT INTO statements and thus trades speed for simplicity. If you're writing a large DataFrame to a database, it might be quicker to write the DataFrame to CSV and load that directly using the database's file import arguments."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Substitute the variables below with the access credentials provided by David last Friday."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "engine = create_engine('postgresql://dsr:DSRinBerlin!@dsr-psql.cqgclgt070tr.eu-central-1.rds.amazonaws.com:5432/rentals')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "films = pd.read_sql_query('select * from film limit 200', con=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "films.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**_Exercise_**\n",
    "* Try reproducing some of the groupby and join exercises that you did with David last Friday, translated into pandas."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Clipboard**\n",
    "\n",
    "While the results of a query can be read directly into a DataFrame, I prefer to read the results directly from the clipboard. I'm often tweaking queries in my SQL client ([Sequel Pro](http://www.sequelpro.com/)), so I would rather see the results *before* I read it into pandas. Once I'm confident I have the data I want, then I'll read it into a DataFrame.\n",
    "\n",
    "This works just as well with any type of delimited data you've copied to your clipboard. The function does a good job of inferring the delimiter, but you can also use the `sep` parameter to be explicit.\n",
    "\n",
    "[Hank Aaron](http://www.baseball-reference.com/players/a/aaronha01.shtml)\n",
    "\n",
    "![hank-aaron-stats-screenshot](http://i.imgur.com/xiySJ2e.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "hank = pd.read_clipboard()\n",
    "hank.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**URL**\n",
    "\n",
    "With `read_table`, we can also read directly from a URL.\n",
    "\n",
    "Let's use the [best sandwiches data](https://raw.github.com/gjreda/best-sandwiches/master/data/best-sandwiches-geocode.tsv) that I [wrote about scraping](http://www.gregreda.com/2013/05/06/more-web-scraping-with-python/) a while back."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "url = 'https://raw.github.com/gjreda/best-sandwiches/master/data/best-sandwiches-geocode.tsv'\n",
    "\n",
    "# fetch the text from the URL and read it into a DataFrame\n",
    "from_url = pd.read_table(url, sep='\\t')\n",
    "from_url.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Google Analytics**\n",
    "\n",
    "pandas also has some integration with the Google Analytics API, though there is some setup required. I won't be covering it, but you can read more about it [here](http://blog.yhathq.com/posts/pandas-google-analytics.html) and [here](http://quantabee.wordpress.com/2012/12/17/google-analytics-pandas/)."
   ]
  }
 ],
 "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}