4-intro-to-pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Power Tools: Python Pandas\n",
    "\n",
    "To demonstrate the usefulness of the `pandas` Python library, we'll walk through a real life use case. An extract-transform-load pipeline we built for working with the NYPL menus data:\n",
    "\n",
    "#### A Repeatable Extract-Tranform-Load Pipeline for NYPL Menus Data\n",
    "\n",
    "**Created:** 17 October 2014\n",
    "\n",
    "**Updated:** 28 October 2014, 19 November 2014, 3 December 2014\n",
    "\n",
    "**Authors:** Trevor Muñoz and Katie Rawson\n",
    "\n",
    "&nbsp;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Acquiring Data (Extract)\n",
    "\n",
    "For the purpose of this exercise, we'll grab a copy from our local `data` folder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import datetime\n",
    "import time\n",
    "import tarfile"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "DATA_FILE = '../data/menus-2015_07_16_07_01_00_data.tgz'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tar = tarfile.open(DATA_FILE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "for tf in tar.getmembers():\n",
    "    print('Name: {0} \\t Last Modified: {1}'.format(tf.name, time.ctime(tf.mtime)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "DATA_DIR = '../data/nypl_menus'\n",
    "tar.extractall(path=DATA_DIR)\n",
    "\n",
    "for f in os.listdir(DATA_DIR):\n",
    "    if f.endswith('csv'):\n",
    "        if os.path.isfile(os.path.join(DATA_DIR, f)) == True:\n",
    "            print('{0} … \\u2713'.format(f))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tar.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Working with Data in DataFrames (Tranform)\n",
    "\n",
    "&nbsp;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import re\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "LATEST_DISH_DATA_DF = pd.DataFrame.from_csv(os.path.join(DATA_DIR, 'Dish.csv'), \n",
    "                                            index_col='id')\n",
    "LATEST_ITEM_DATA_DF = pd.DataFrame.from_csv(os.path.join(DATA_DIR, 'MenuItem.csv'), \n",
    "                                            index_col='dish_id')\n",
    "LATEST_PAGE_DATA_DF = pd.DataFrame.from_csv(os.path.join(DATA_DIR, 'MenuPage.csv'), \n",
    "                                            index_col='id')\n",
    "LATEST_MENU_DATA_DF = pd.DataFrame.from_csv(os.path.join(DATA_DIR, 'Menu.csv'),\n",
    "                                             index_col='id')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Dish.csv\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "NULL_APPEARANCES = LATEST_DISH_DATA_DF[LATEST_DISH_DATA_DF.times_appeared == 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print('Data set contains {0} dishes that appear 0 times …'.format(\n",
    "    len(NULL_APPEARANCES))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "NON_NULL_DISH_DATA_DF = LATEST_DISH_DATA_DF[LATEST_DISH_DATA_DF.times_appeared != 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "discarded_columns = [n for n in NON_NULL_DISH_DATA_DF.columns if n not in \n",
    "                     ['name', 'menus_appeared', 'times_appeared']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print('Discarding columns from Dish.csv …')\n",
    "for discard in discarded_columns:\n",
    "    print('{0} … removed'.format(discard))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "TRIMMED_DISH_DATA_DF = NON_NULL_DISH_DATA_DF[['name', 'menus_appeared', 'times_appeared']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print('Dish.csv contains {0} potentially-unique dish names before any normalization'.\n",
    "                     format(TRIMMED_DISH_DATA_DF.name.nunique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def normalize_names(obj):\n",
    "    '''\n",
    "    Take a name as a string, converts the string\n",
    "    to lowercase, strips whitespace from beginning\n",
    "    and end, normalizes multiple internal whitespace\n",
    "    characters to a single space. E.g.:\n",
    "    \n",
    "    normalize_names('Chicken gumbo ') = 'chicken gumbo'\n",
    "    \n",
    "    '''\n",
    "    tokens = obj.strip().lower().split()\n",
    "    result = ' '.join(filter(None, tokens))\n",
    "    return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "TRIMMED_DISH_DATA_DF['normalized_name'] = TRIMMED_DISH_DATA_DF.name.map(normalize_names)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(\n",
    "    'Dish.csv contains {0} potentially-unique dish names after normalizing whitespace and punctuation'\n",
    "    .format(TRIMMED_DISH_DATA_DF.normalized_name.nunique())\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def fingerprint(obj):\n",
    "    \"\"\"\n",
    "    A modified version of the fingerprint clustering algorithm implemented by Open Refine.\n",
    "    See https://github.com/OpenRefine/OpenRefine/wiki/Clustering-In-Depth\n",
    "    This does not normalize to ASCII characters since diacritics may be significant in this dataset\n",
    "    \"\"\"\n",
    "    alphanumeric_tokens = filter(None, re.split('\\W', obj))\n",
    "    seen = set()\n",
    "    seen_add = seen.add\n",
    "    deduped = sorted([i for i in alphanumeric_tokens if i not in seen and not seen_add(i)])\n",
    "    fingerprint = ' '.join(deduped)\n",
    "    \n",
    "    return fingerprint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "TRIMMED_DISH_DATA_DF['fingerprint'] = TRIMMED_DISH_DATA_DF.normalized_name.map(fingerprint)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(\n",
    "    'Dish.csv contains {0} unique fingerprint values'\n",
    "    .format(TRIMMED_DISH_DATA_DF.fingerprint.nunique())\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "TRIMMED_DISH_DATA_DF.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### MenuItem.csv\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "discarded_columns2 = [n for n in LATEST_ITEM_DATA_DF.columns if n not in \n",
    "                      ['id', 'menu_page_id', 'xpos', 'ypos']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print('Discarding columns from MenuItem.csv …')\n",
    "for discard2 in discarded_columns2:\n",
    "    print('{0} … removed'.format(discard2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "TRIMMED_ITEM_DATA_DF = LATEST_ITEM_DATA_DF[['id', 'menu_page_id', 'xpos', 'ypos']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "TRIMMED_ITEM_DATA_DF.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### MenuPage.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "LATEST_PAGE_DATA_DF.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "LATEST_PAGE_DATA_DF[['full_height', 'full_width']].astype(int, raise_on_error=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Menu.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "LATEST_MENU_DATA_DF.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "discarded_columns3 = [n for n in LATEST_MENU_DATA_DF.columns if n not in \n",
    "                      ['sponsor', 'location', 'date', 'page_count', 'dish_count']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pipeline_logger.info('Discarding columns from Menu.csv …')\n",
    "for discard3 in discarded_columns3:\n",
    "    pipeline_logger.info('{0} … removed'.format(discard3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "TRIMMED_MENU_DATA_DF = LATEST_MENU_DATA_DF[['sponsor', 'location', 'date',\n",
    "                                            'page_count', 'dish_count']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "TRIMMED_MENU_DATA_DF.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Merging DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "MERGED_ITEM_PAGES_DF = pd.merge(TRIMMED_ITEM_DATA_DF, LATEST_PAGE_DATA_DF, \n",
    "                                left_on='menu_page_id', right_index=True, )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "MERGED_ITEM_PAGES_DF.columns = ['item_id', 'menu_page_id', 'xpos', 'ypos', \n",
    "                                'menu_id', 'page_number', \n",
    "                                'image_id', 'full_height', 'full_width', 'uuid']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#MERGED_ITEM_PAGES_DF.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "MERGED_ITEM_PAGES_MENUS_DF = pd.merge(TRIMMED_MENU_DATA_DF, MERGED_ITEM_PAGES_DF, \n",
    "                                      left_index=True, right_on='menu_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "FULL_MERGE = pd.merge(MERGED_ITEM_PAGES_MENUS_DF, TRIMMED_DISH_DATA_DF, \n",
    "                      left_index=True, right_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "FULL_MERGE.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "FOR_JSON_OUTPUT = FULL_MERGE.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "FOR_JSON_OUTPUT.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "renamed_columns = ['dish_id', 'menu_sponsor', 'menu_location', 'menu_date', 'menu_page_count', \n",
    "                   'menu_dish_count', 'item_id', 'menu_page_id', 'item_xpos', 'item_ypos', \n",
    "                   'menu_id', 'menu_page_number', 'image_id', \n",
    "                   'page_image_full_height', 'page_image_full_width', 'page_image_uuid', 'dish_name', \n",
    "                   'dish_menus_appeared', 'dish_times_appeared', 'dish_normalized_name', 'dish_name_fingerprint']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "FOR_JSON_OUTPUT.columns = renamed_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "FOR_JSON_OUTPUT[['menu_page_number', 'dish_id', 'item_id', 'menu_page_id', 'menu_id']].astype(int, raise_on_error=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "FOR_JSON_OUTPUT['dish_uri']= FOR_JSON_OUTPUT.dish_id.map(lambda x: 'http://menus.nypl.org/dishes/{0}'.format(int(x)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "FOR_JSON_OUTPUT['item_uri']= FOR_JSON_OUTPUT.item_id.map(lambda x: 'http://menus.nypl.org/menu_items/{0}/edit'\n",
    "                                               .format(int(x)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "FOR_JSON_OUTPUT['menu_page_uri'] = FOR_JSON_OUTPUT.menu_page_id.map(lambda x: 'http://menus.nypl.org/menu_pages/{0}'\n",
    "                                                          .format(int(x)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "FOR_JSON_OUTPUT['menu_uri'] = FOR_JSON_OUTPUT.menu_id.map(lambda x:'http://menus.nypl.org/menus/{0}'\n",
    "                                                .format(int(x)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "FOR_JSON_OUTPUT.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print('Generating JSON …')\n",
    "FOR_JSON_OUTPUT.to_json(path_or_buf='../data/nypl_menus/menus_all.json', orient='index', force_ascii=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "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.4.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}