yelp_csv_cleaner.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:84b31c9506efd41282aee1308619295ebaf4484ef6b7771c465d1d8c2f3653d2"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import numpy as np\n",
      "%matplotlib inline\n",
      "from matplotlib import pyplot as plt\n",
      "import re"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "df = pd.DataFrame.from_csv('yelp_data/yelp_raw.csv', index_col = False)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 2
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "'''Drop repeated elements'''\n",
      "df = df.drop_duplicates('id')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 3
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print df.columns"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Index([u'address', u'categories', u'city', u'country_code', u'cross_streets', u'deals', u'display_address', u'display_phone', u'gift_certificates', u'id', u'image_url', u'is_claimed', u'is_closed', u'menu_date_updated', u'menu_provider', u'mobile_url', u'name', u'neighborhoods', u'phone', u'postal_code', u'rating', u'rating_img_url', u'rating_img_url_large', u'rating_img_url_small', u'review_count', u'snippet_image_url', u'snippet_text', u'state_code', u'url'], dtype='object')\n"
       ]
      }
     ],
     "prompt_number": 4
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "'''Remove non-US listings as these were picked because of incorrect latitude / longitude information'''\n",
      "#example: http://www.yelp.com/map/subaru-sushi-xpress-madrid\n",
      "#also gets rid of the column\n",
      "df = df[(df.country_code == 'US')]\n",
      "df = df.drop('country_code', 1)\n",
      "print df.columns"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Index([u'address', u'categories', u'city', u'cross_streets', u'deals', u'display_address', u'display_phone', u'gift_certificates', u'id', u'image_url', u'is_claimed', u'is_closed', u'menu_date_updated', u'menu_provider', u'mobile_url', u'name', u'neighborhoods', u'phone', u'postal_code', u'rating', u'rating_img_url', u'rating_img_url_large', u'rating_img_url_small', u'review_count', u'snippet_image_url', u'snippet_text', u'state_code', u'url'], dtype='object')\n"
       ]
      }
     ],
     "prompt_number": 5
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "'''Removing non-CA listings, same issues as above'''\n",
      "#http://www.yelp.com/biz/kelley-premeaux-tattoo-austin-4\n",
      "#http://www.yelp.com/biz/shawntays-creative-nail-boutique-baltimore-2\n",
      "#http://www.yelp.com/biz/gourmet2yourdoor-baltimore-2\n",
      "#http://www.yelp.com/biz/precision-plus-plumbing-philadelphia\n",
      "#http://www.yelp.com/biz/boston-baby-nurse-boston-2\n",
      "#http://www.yelp.com/biz/cinderellas-cleaning-richmond\n",
      "#http://www.yelp.com/biz/ai-kah-garden-lake-milton\n",
      "#http://www.yelp.com/biz/sam-adatos-drums-eugene\n",
      "df = df[(df.state_code == 'CA')]\n",
      "df = df.drop('state_code', 1)\n",
      "print df.columns"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Index([u'address', u'categories', u'city', u'cross_streets', u'deals', u'display_address', u'display_phone', u'gift_certificates', u'id', u'image_url', u'is_claimed', u'is_closed', u'menu_date_updated', u'menu_provider', u'mobile_url', u'name', u'neighborhoods', u'phone', u'postal_code', u'rating', u'rating_img_url', u'rating_img_url_large', u'rating_img_url_small', u'review_count', u'snippet_image_url', u'snippet_text', u'url'], dtype='object')\n"
       ]
      }
     ],
     "prompt_number": 6
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "'''Drop entries without a postal code, these have malformed addresses'''\n",
      "df = df[df.postal_code.notnull()]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 7
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "'''Removing obviously incorrect postal codes'''\n",
      "df = df[np.logical_and(df.postal_code >= 94000, df.postal_code <= 96000)]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 8
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#drop is closed as they are all false\n",
      "#drop menu_date_updated' as it's not useful\n",
      "#drop menu provider as it's not useful\n",
      "#drop mobile url as we don't need it\n",
      "#don't need display phone, or is_claimed, or snipper information\n",
      "df = df.drop(['is_closed','menu_date_updated','menu_provider','mobile_url','display_phone', 'is_claimed', 'snippet_image_url','snippet_text'], 1)\n",
      "print df.columns"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Index([u'address', u'categories', u'city', u'cross_streets', u'deals', u'display_address', u'gift_certificates', u'id', u'image_url', u'name', u'neighborhoods', u'phone', u'postal_code', u'rating', u'rating_img_url', u'rating_img_url_large', u'rating_img_url_small', u'review_count', u'url'], dtype='object')\n"
       ]
      }
     ],
     "prompt_number": 9
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.review_count.hist(bins = np.linspace(0,500, 20))\n",
      "plt.show()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "display_data",
       "png": "iVBORw0KGgoAAAANSUhEUgAAAYgAAAEACAYAAACpoOGTAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAIABJREFUeJzt3W1MVGf6BvALM8T8262spTooQ3ZaGMBRBFoLZJNNcOng\ny1bUYEG3q9CSZpVsQ/lgaP2ybTZ1pm02fdmV/dBOV+KmoOkHNc06S7VOalrFLWjaFTfMrqOFYZy0\nUFxkFQTu/wfxrJapvJyhnPP0+iUEzsN5Zp6rIXM7z33ONE5EBERERN8yZ7YXQERExsQCQUREUbFA\nEBFRVCwQREQUFQsEERFFxQJBRERRTapA9PX1YdOmTViyZAmcTidaWlrQ29sLl8uF9PR0FBcXo6+v\nTzvf7XbD4XAgMzMTzc3N2nhrayuysrLgcDhQU1OjjQ8ODqK8vBwOhwMFBQW4dOlSDCMSEdF0TKpA\n1NTUYO3atTh//jw+//xzZGZmwuPxwOVyoaOjA0VFRfB4PACA9vZ27N+/H+3t7fD5fKiursatWy12\n7NgBr9eLQCCAQCAAn88HAPB6vUhMTEQgEEBtbS3q6upmKC4REU3WhAXiypUrOHHiBJ5++mkAgMVi\nQUJCAg4fPoyKigoAQEVFBQ4ePAgAOHToELZs2YL4+HjY7XakpaWhpaUF4XAY/f39yMvLAwBs27ZN\nm3P7Y5WWluLYsWOxT0pERFMyYYEIBoNYsGABnnrqKTz88MN45plnMDAwgEgkAqvVCgCwWq2IRCIA\ngO7ubthsNm2+zWZDKBQaN56cnIxQKAQACIVCSElJAfC/AtTb2xu7lERENGUTFojh4WG0tbWhuroa\nbW1tuPfee7XtpFvi4uIQFxc3Y4skIqLvn2WiE2w2G2w2Gx599FEAwKZNm+B2u5GUlITLly8jKSkJ\n4XAYCxcuBHDznUFnZ6c2v6urCzabDcnJyejq6ho3fmvOl19+icWLF2N4eBhXrlzB/ffff8c6kpOT\n0d3drT8xEdEPSGpqKv71r39Na+6E7yCSkpKQkpKCjo4OAMDRo0exdOlSrFu3Dg0NDQCAhoYGbNiw\nAQBQUlKCpqYmDA0NIRgMIhAIIC8vD0lJSZg3bx5aWlogIti3bx/Wr1+vzbn1WO+//z6KiorGraO7\nuxsiouzXb3/721lfA7MxH/Op9/Xvf/97WsUBmMQ7CAD4wx/+gCeffBJDQ0NITU3Fn//8Z4yMjKCs\nrAxerxd2ux0HDhwAADidTpSVlcHpdMJisaC+vl7bfqqvr0dlZSWuXbuGtWvXYvXq1QCAqqoqbN26\nFQ6HA4mJiWhqapp2ILO6ePHibC9hxqicDWA+s1M9nx6TKhDZ2dn4+9//Pm786NGjUc/ftWsXdu3a\nNW78kUcewRdffDFufO7cuVqBISIiY+Cd1AZRWVk520uYMSpnA5jP7FTPp0eciJjifxgUFxcHkyyV\niMgw9Lx28h2EQfj9/tlewoxRORvAfGanej49WCCIiCgqbjERESmMW0xERBRzLBAGofI+qMrZAOYz\nO9Xz6cECQUREUbEHQUSkMPYgiIgo5lggDELlfVCVswHMZ3aq59ODBYKIiKJiD4KISGHsQRARUcyx\nQBiEyvugKmcDmM/sVM+nBwsEERFFxR4EEZHC2IMgIqKYY4EwCJX3QVXOBjCf2ameTw8WCCIiioo9\nCCIihbEHQUREMWeZ7QVMxTvvvDPlOffeey82b96MuLi4GVhR7Pj9fhQWFs72MmaEytkA5jM71fPp\nYaoCUVNzaspzBgf3YdWqVbj//vtnYEVEROoyVQ8CmPpS5869H93d/2KBIKIfJPYgiIgo5lggDELl\na7FVzgYwn9mpnk8PFggiIopqUgXCbrdj+fLlyM3NRV5eHgCgt7cXLpcL6enpKC4uRl9fn3a+2+2G\nw+FAZmYmmpubtfHW1lZkZWXB4XCgpqZGGx8cHER5eTkcDgcKCgpw6dKlWOUzDZWvolA5G8B8Zqd6\nPj0mVSDi4uLg9/tx5swZnD59GgDg8XjgcrnQ0dGBoqIieDweAEB7ezv279+P9vZ2+Hw+VFdXaw2S\nHTt2wOv1IhAIIBAIwOfzAQC8Xi8SExMRCARQW1uLurq6mchKRERTMOktpm93wQ8fPoyKigoAQEVF\nBQ4ePAgAOHToELZs2YL4+HjY7XakpaWhpaUF4XAY/f392juQbdu2aXNuf6zS0lIcO3ZMfzKTUXkf\nVOVsAPOZner59Jj0O4jHHnsMK1aswNtvvw0AiEQisFqtAACr1YpIJAIA6O7uhs1m0+babDaEQqFx\n48nJyQiFQgCAUCiElJQUAIDFYkFCQgJ6e3tjEI+IiKZrUjfKffLJJ1i0aBG++uoruFwuZGZm3vH7\nuLg4w9+pbHQq74OqnA1gPrNTPZ8ekyoQixYtAgAsWLAAGzduxOnTp2G1WnH58mUkJSUhHA5j4cKF\nAG6+M+js7NTmdnV1wWazITk5GV1dXePGb8358ssvsXjxYgwPD+PKlSvfcWNbJQD72M8/BpADoHDs\n2D/2/dvHY0djbyNv/THwmMc85rGKx36/H3v37gVw8wIjXWQCAwMD8p///EdERK5evSo//elP5W9/\n+5vs3LlTPB6PiIi43W6pq6sTEZFz585Jdna2DA4OyoULF+Shhx6S0dFRERHJy8uTU6dOyejoqKxZ\ns0aOHDkiIiJ79uyR7du3i4hIY2OjlJeXj1sHAAFkyl9z586Xnp6eiWLOuuPHj8/2EmaMytlEmM/s\nVM83iZf57zThO4hIJIKNGzcCAIaHh/Hkk0+iuLgYK1asQFlZGbxeL+x2Ow4cOAAAcDqdKCsrg9Pp\nhMViQX19vbb9VF9fj8rKSly7dg1r167F6tWrAQBVVVXYunUrHA4HEhMT0dTUpK/qERGRbvwsJiIi\nhfGzmIiIKOZYIAziVpNJRSpnA5jP7FTPpwcLBBERRcUeBBGRwtiDICKimGOBMAiV90FVzgYwn9mp\nnk8PFggiIoqKPQgiIoWxB0FERDHHAmEQKu+DqpwNYD6zUz2fHiwQREQUFXsQREQKYw+CiIhijgXC\nIFTeB1U5G8B8Zqd6Pj1YIIiIKCr2IIiIFMYeBBERxRwLhEGovA+qcjaA+cxO9Xx6sEAQEVFU7EEQ\nESmMPQgiIoo5FgiDUHkfVOVsAPOZner59GCBICKiqNiDICJSGHsQREQUcywQBqHyPqjK2QDmMzvV\n8+nBAkFERFGxB0FEpDD2IIiIKOYmVSBGRkaQm5uLdevWAQB6e3vhcrmQnp6O4uJi9PX1aee63W44\nHA5kZmaiublZG29tbUVWVhYcDgdqamq08cHBQZSXl8PhcKCgoACXLl2KVTZTUXkfVOVsAPOZner5\n9JhUgXjzzTfhdDrHtnkAj8cDl8uFjo4OFBUVwePxAADa29uxf/9+tLe3w+fzobq6Wntrs2PHDni9\nXgQCAQQCAfh8PgCA1+tFYmIiAoEAamtrUVdXNxM5iYhoqmQCnZ2dUlRUJB999JE8/vjjIiKSkZEh\nly9fFhGRcDgsGRkZIiKye/du8Xg82txVq1bJyZMnpbu7WzIzM7XxxsZG+fWvf62dc+rUKRERuXHj\nhjzwwANR1wFAAJny19y586Wnp2eimERESprEy/x3mvAdRG1tLV577TXMmfO/UyORCKxWKwDAarUi\nEokAALq7u2Gz2bTzbDYbQqHQuPHk5GSEQiEAQCgUQkpKCgDAYrEgISEBvb29ugsfERHpY7nbLz/4\n4AMsXLgQubm537lPFxcXp209zbxKAPaxn38MIAdA4dixf+z7t4/HjsbWX1hYaMjjN954Azk5OYZZ\nTyyPb//bMcJ6mI/5VM7n9/uxd+9eAIDdbocud3t78cILL4jNZhO73S5JSUlyzz33yK9+9SvJyMiQ\ncDgsIiLd3d3aFpPb7Ra3263Nv7V9FA6H79hieu+992T79u3aOSdPnhSRH/YW0/Hjx2d7CTNG5Wwi\nzGd2queb4GX+7nMne6Lf79d6EDt37tR6DW63W+rq6kRE5Ny5c5KdnS2Dg4Ny4cIFeeihh2R0dFRE\nRPLy8uTUqVMyOjoqa9askSNHjoiIyJ49e7Ri0djYKOXl5dEXqniBICKaCXoKxF23mL7t1lbS888/\nj7KyMni9Xtjtdhw4cAAA4HQ6UVZWBqfTCYvFgvr6em1OfX09Kisrce3aNaxduxarV68GAFRVVWHr\n1q1wOBxITExEU1OTvrdEREQUE7yT2iD8fr+2n6galbMBzGd2qufjndRERBRzfAdBRKQwvoMgIqKY\nY4EwiNuvxVaNytkA5jM71fPpwQJBRERRsQdBRKQw9iCIiCjmWCAMQuV9UJWzAcxndqrn04MFgoiI\nomIPgohIYexBEBFRzLFAGITK+6AqZwOYz+xUz6cHCwQREUXFHgQRkcLYgyAiophjgTAIlfdBVc4G\nMJ/ZqZ5PDxYIIiKKij0IIiKFsQdBREQxxwJhECrvg6qcDWA+s1M9nx4sEEREFBV7EERECmMPgoiI\nYo4FwiBU3gdVORvAfGanej49WCCIiCgq9iCIiBTGHgQREcUcC4RBqLwPqnI2gPnMTvV8ety1QFy/\nfh35+fnIycmB0+nECy+8AADo7e2Fy+VCeno6iouL0dfXp81xu91wOBzIzMxEc3OzNt7a2oqsrCw4\nHA7U1NRo44ODgygvL4fD4UBBQQEuXboU64xERDQdMoGBgQEREblx44bk5+fLiRMnZOfOnfLKK6+I\niIjH45G6ujoRETl37pxkZ2fL0NCQBINBSU1NldHRURERefTRR6WlpUVERNasWSNHjhwREZE9e/bI\njh07RESkqalJysvLo64DgAAy5a+5c+dLT0/PRDGJiJQ0iZf57zThFtM999wDABgaGsLIyAjmz5+P\nw4cPo6KiAgBQUVGBgwcPAgAOHTqELVu2ID4+Hna7HWlpaWhpaUE4HEZ/fz/y8vIAANu2bdPm3P5Y\npaWlOHbsWKxrIBERTcOEBWJ0dBQ5OTmwWq1YuXIlli5dikgkAqvVCgCwWq2IRCIAgO7ubthsNm2u\nzWZDKBQaN56cnIxQKAQACIVCSElJAQBYLBYkJCSgt7c3dglNQuV9UJWzAcxndqrn08My0Qlz5szB\n2bNnceXKFaxatQrHjx+/4/dxcXFjl6B+HyoB2Md+/jGAHACFY8f+se/fPh47GvsjKCwsNOTx2bNn\nDbUeHvOYx+Y89vv92Lt3LwDAbrdDjyndB/G73/0O//d//4d33nkHfr8fSUlJCIfDWLlyJf75z3/C\n4/EAAJ5//nkAwOrVq/HSSy/hJz/5CVauXInz588DABobG/Hxxx/jT3/6E1avXo0XX3wRBQUFGB4e\nxqJFi/DVV1+NXyjvgyAimrIZuw/i66+/1q5QunbtGj788EPk5uaipKQEDQ0NAICGhgZs2LABAFBS\nUoKmpiYMDQ0hGAwiEAggLy8PSUlJmDdvHlpaWiAi2LdvH9avX6/NufVY77//PoqKiqYVhIiIYuxu\nHezPP/9ccnNzJTs7W7KysuTVV18VEZGenh4pKioSh8MhLpdLvvnmG23Oyy+/LKmpqZKRkSE+n08b\n/+yzz2TZsmWSmpoqzz77rDZ+/fp1eeKJJyQtLU3y8/MlGAxGXQsUv4rp+PHjs72EGaNyNhHmMzvV\n803wMn9Xd+1BZGVloa2tbdz4/fffj6NHj0ads2vXLuzatWvc+COPPIIvvvhi3PjcuXNx4MCByVUz\nIiL63vCzmIiIFMbPYiIiophjgTCIW5epqUjlbADzmZ3q+fRggSAioqjYgyAiUhh7EEREFHMsEAah\n8j6oytkA5jM71fPpwQJBRERRsQdBRKQw9iCIiCjmWCAMQuV9UJWzAcxndqrn04MFgoiIomIPgohI\nYexBEBFRzLFAGITK+6AqZwOYz+xUz6cHCwQREUXFHgQRkcLYgyAiophjgTAIlfdBVc4GMJ/ZqZ5P\nDxYIIiKKij0IIiKFsQdBREQxxwJhECrvg6qcDWA+s1M9nx4sEEREFBV7EERECmMPgoiIYo4FwiBU\n3gdVORvAfGanej49WCCIiCiqCQtEZ2cnVq5ciaVLl2LZsmV46623AAC9vb1wuVxIT09HcXEx+vr6\ntDlutxsOhwOZmZlobm7WxltbW5GVlQWHw4GamhptfHBwEOXl5XA4HCgoKMClS5dimdEUCgsLZ3sJ\nM0blbADzmZ3q+fSYsEDEx8fj9ddfx7lz53Dq1Cns2bMH58+fh8fjgcvlQkdHB4qKiuDxeAAA7e3t\n2L9/P9rb2+Hz+VBdXa01SHbs2AGv14tAIIBAIACfzwcA8Hq9SExMRCAQQG1tLerq6mYwMhERTcaE\nBSIpKQk5OTkAgB/96EdYsmQJQqEQDh8+jIqKCgBARUUFDh48CAA4dOgQtmzZgvj4eNjtdqSlpaGl\npQXhcBj9/f3Iy8sDAGzbtk2bc/tjlZaW4tixY7FPanAq74OqnA1gPrNTPZ8eU+pBXLx4EWfOnEF+\nfj4ikQisVisAwGq1IhKJAAC6u7ths9m0OTabDaFQaNx4cnIyQqEQACAUCiElJQUAYLFYkJCQgN7e\nXn3JiIhIF8tkT7x69SpKS0vx5ptv4r777rvjd3FxcWP3Kcy0SgD2sZ9/DCAHQOHYsX/s+7ePx47G\n/pVwa7/RaMe3xoyynlgeFxYWGmo9zMd8Kufz+/3Yu3cvAMBut0MXmYShoSEpLi6W119/XRvLyMiQ\ncDgsIiLd3d2SkZEhIiJut1vcbrd23qpVq+TUqVMSDoclMzNTG3/vvfdk+/bt2jknT54UEZEbN27I\nAw88MG4NAASQKX/NnTtfenp6JhOTiEg5k3yZj2rCLSYRQVVVFZxOJ5577jltvKSkBA0NDQCAhoYG\nbNiwQRtvamrC0NAQgsEgAoEA8vLykJSUhHnz5qGlpQUign379mH9+vXjHuv9999HUVGRvqpnQrf+\nBaAilbMBzGd2qufTY8Itpk8++QR/+ctfsHz5cuTm5gK4eRnr888/j7KyMni9Xtjtdhw4cAAA4HQ6\nUVZWBqfTCYvFgvr6em37qb6+HpWVlbh27RrWrl2L1atXAwCqqqqwdetWOBwOJCYmoqmpaabyEhHR\nJPGzmIiIFMbPYiIiophjgTAIlfdBVc4GMJ/ZqZ5PDxYIIiKKij0IIiKFsQdBREQxxwJhECrvg6qc\nDWA+s1M9nx4sEEREFBV7EERECmMPgoiIYo4FwiBU3gdVORvAfGanej49WCCIiCgq9iCIiBTGHgQR\nEcUcC4RBqLwPqnI2gPnMTvV8erBAEBFRVOxBEBEpjD0IIiKKORYIg1B5H1TlbADzmZ3q+fRggSAi\noqjYgyAiUhh7EEREFHMsEAah8j6oytkA5jM71fPpwQJBRERRsQdBRKQw9iCIiCjmWCAMQuV9UJWz\nAcxndqrn04MFgoiIomIPgohIYTPag3j66adhtVqRlZWljfX29sLlciE9PR3FxcXo6+vTfud2u+Fw\nOJCZmYnm5mZtvLW1FVlZWXA4HKipqdHGBwcHUV5eDofDgYKCAly6dGlaQYiIKLYmLBBPPfUUfD7f\nHWMejwculwsdHR0oKiqCx+MBALS3t2P//v1ob2+Hz+dDdXW1Vrl27NgBr9eLQCCAQCCgPabX60Vi\nYiICgQBqa2tRV1cX64ymoPI+qMrZAOYzO9Xz6TFhgfjZz36G+fPn3zF2+PBhVFRUAAAqKipw8OBB\nAMChQ4ewZcsWxMfHw263Iy0tDS0tLQiHw+jv70deXh4AYNu2bdqc2x+rtLQUx44di106IiKatmk1\nqSORCKxWKwDAarUiEokAALq7u2Gz2bTzbDYbQqHQuPHk5GSEQiEAQCgUQkpKCgDAYrEgISEBvb29\n00tjYoWFhbO9hBmjcjaA+cxO9Xx66L6KKS4ubqyBTEREKrFMZ5LVasXly5eRlJSEcDiMhQsXArj5\nzqCzs1M7r6urCzabDcnJyejq6ho3fmvOl19+icWLF2N4eBhXrly5yxVHlQDsYz//GEAOgMKxY//Y\n928fjx2N7TPe+teC0Y7feOMN5OTkGGY9sTy+fY/XCOthPuZTOZ/f78fevXsBAHa7HbrIJASDQVm2\nbJl2vHPnTvF4PCIi4na7pa6uTkREzp07J9nZ2TI4OCgXLlyQhx56SEZHR0VEJC8vT06dOiWjo6Oy\nZs0aOXLkiIiI7NmzR7Zv3y4iIo2NjVJeXh51DQAEkCl/zZ07X3p6eiYTc1YdP358tpcwY1TOJsJ8\nZqd6vkm+zEefO9EJmzdvlkWLFkl8fLzYbDZ59913paenR4qKisThcIjL5ZJvvvlGO//ll1+W1NRU\nycjIEJ/Pp41/9tlnsmzZMklNTZVnn31WG79+/bo88cQTkpaWJvn5+RIMBr8zpMoFgohoJugpELxR\njohIYfywPgXcvg+qGpWzAcxndqrn04MFgoiIouIWExGRwrjFREREMccCYRAq74OqnA1gPrNTPZ8e\nLBBERBQVexBERApjD4KIiGKOBcIgVN4HVTkbwHxmp3o+PX4QBeInP0nVPnV2Kl/z5nFbioh+uH4Q\nPYjBwW+mNReY/t4dEZERsAdBREQxxwJhECrvg6qcDWA+s1M9nx4sEEREFBV7EHd/VvYgiMjU2IMg\nIqKYY4EwCJX3QVXOBjCf2ameTw8WCCIiioo9iLs/K3sQRGRq7EEQEVHMsUAYhMr7oCpnA5jP7FTP\npwcLxF1ZpvUZTvwcJyJSAXsQd3/Wac67Odck/2mJSGHsQRARUcyxQBiEyvugKmcDmM/sVM+nBwsE\nERFFxR7E3Z91mvMAIB7A8LRm3nfffPznP73TfF4iov/R04OwxHgtpBnGdItLf39cbJdCRDQNhtli\n8vl8yMzMhMPhwCuvvDLby5kF/tt+nt7ltUa9tFb1PV7mMzfV8+lhiAIxMjKC3/zmN/D5fGhvb0dj\nYyPOnz8/28v6np297edb7z6m9tXf32/I+zbOnj078Ukmxnzmpno+PQxRIE6fPo20tDTY7XbEx8dj\n8+bNOHTo0Gwv63vWF4PHmF5hmeni0tcXi2zGxXzmpno+PQzRgwiFQkhJSdGObTYbWlpaZnFFP0R6\neibxYxcRfLeXXnopymg8gBvTes7ZmMuLB+iHxhAFYqIXl1vmzVs35ce+erV/ynNmx8XZXoAOExWX\nSgB7o4zru1P9+557t0IYvQDebjaKYeyec+J8M/O838dzWizxePHFF6c1V3WGKBDJycno7OzUjjs7\nO2Gz2e44JzU1Ff/+9wc6nmW6VwbpuaJoqnMbYvC83+d6pzK34TvGjbreWJvuC6aeubPxnLM1d/rP\nOTx8Y9L/SDWj1NTUac81xH0Qw8PDyMjIwLFjx7B48WLk5eWhsbERS5Ysme2lERH9YBniHYTFYsEf\n//hHrFq1CiMjI6iqqmJxICKaZYZ4B0FERMZjiMtc70aFG+iefvppWK1WZGVlaWO9vb1wuVxIT09H\ncXHxHZfaud1uOBwOZGZmorm5eTaWPCWdnZ1YuXIlli5dimXLluGtt94CoEbG69evIz8/Hzk5OXA6\nnXjhhRcAqJHtdiMjI8jNzcW6dTcvBFEpn91ux/Lly5Gbm4u8vDwAauXr6+vDpk2bsGTJEjidTrS0\ntMQunxjY8PCwpKamSjAYlKGhIcnOzpb29vbZXtaUffzxx9LW1ibLli3Txnbu3CmvvPKKiIh4PB6p\nq6sTEZFz585Jdna2DA0NSTAYlNTUVBkZGZmVdU9WOByWM2fOiIhIf3+/pKenS3t7uzIZBwYGRETk\nxo0bkp+fLydOnFAm2y2///3v5Ze//KWsW7dORNT6+7Tb7dLT03PHmEr5tm3bJl6vV0Ru/o329fXF\nLJ+hC8Snn34qq1at0o7dbre43e5ZXNH0BYPBOwpERkaGXL58WURuvsBmZGSIiMju3bvF4/Fo561a\ntUpOnjz5/S5Wp/Xr18uHH36oXMaBgQFZsWKF/OMf/1AqW2dnpxQVFclHH30kjz/+uIio9fdpt9vl\n66+/vmNMlXx9fX3y4IMPjhuPVT5DbzFFu4EuFArN4opiJxKJwGq1AgCsVisikQgAoLu7+45LfM2W\n+eLFizhz5gzy8/OVyTg6OoqcnBxYrVZtK02VbABQW1uL1157DXPm/O/lQKV8cXFxeOyxx7BixQq8\n/fbbANTJFwwGsWDBAjz11FN4+OGH8cwzz2BgYCBm+QxdIFS+Nvl2tz6y4m6/N4OrV6+itLQUb775\nJu677747fmfmjHPmzMHZs2fR1dWFjz/+GMePH7/j92bO9sEHH2DhwoXIzc39zo+ENnM+APjkk09w\n5swZHDlyBHv27MGJEyfu+L2Z8w0PD6OtrQ3V1dVoa2vDvffeC4/Hc8c5evIZukBM5gY6s7Jarbh8\n+TIAIBwOY+HChQDGZ+7q6kJycvKsrHEqbty4gdLSUmzduhUbNmwAoF7GhIQE/OIXv0Bra6sy2T79\n9FMcPnwYDz74ILZs2YKPPvoIW7duVSYfACxatAgAsGDBAmzcuBGnT59WJp/NZoPNZsOjjz4KANi0\naRPa2tqQlJQUk3yGLhArVqxAIBDAxYsXMTQ0hP3796OkpGS2lxUTJSUlaGi4eXdxQ0OD9qJaUlKC\npqYmDA0NIRgMIhAIaFdeGJWIoKqqCk6nE88995w2rkLGr7/+WrsC5Nq1a/jwww+Rm5urRDYA2L17\nNzo7OxEMBtHU1ISf//zn2LdvnzL5/vvf/6K//+bH7QwMDKC5uRlZWVnK5EtKSkJKSgo6OjoAAEeP\nHsXSpUuxbt262OSLacdkBvz1r3+V9PR0SU1Nld27d8/2cqZl8+bNsmjRIomPjxebzSbvvvuu9PT0\nSFFRkTgcDnG5XPLNN99o57/88suSmpoqGRkZ4vP5ZnHlk3PixAmJi4uT7OxsycnJkZycHDly5IgS\nGT///HPJzc2V7OxsycrKkldffVVERIls3+b3+7WrmFTJd+HCBcnOzpbs7GxZunSp9hqiSj4RkbNn\nz8qKFStk+fLlsnHjRunr64tZPt4oR0REURl6i4mIiGYPCwQREUXFAkFERFGxQBARUVQsEEREFBUL\nBBERRcU1SkZYAAAAEUlEQVQCQUREUbFAEBFRVP8PBJ3xar7tnG4AAAAASUVORK5CYII=\n",
       "text": [
        "<matplotlib.figure.Figure at 0x10ea13690>"
       ]
      }
     ],
     "prompt_number": 10
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#only keeping the large image url, don't need neighborhoods\n",
      "df = df.drop(['rating_img_url','rating_img_url_small','neighborhoods','gift_certificates','cross_streets','deals'], 1)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 11
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#drop entries with no address:\n",
      "df = df[df.address != '[]']"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 12
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "_digits = re.compile('\\d')\n",
      "def contains_digits(d):\n",
      "    return bool(_digits.search(d))\n",
      "\n",
      "def lookupable_adddress(x):\n",
      "    '''\n",
      "    takes the 'address' entry and tries to format it so it can be looked up with mapquest API\n",
      "    '''\n",
      "    try:\n",
      "        l = eval(x)\n",
      "    except Exception as e:\n",
      "        print e\n",
      "        return np.nan\n",
      "    else:\n",
      "        if len(l) == 1:\n",
      "            return l[0]\n",
      "        #first entry left to right to contain digits is the street address\n",
      "        else:\n",
      "            for entry in l:\n",
      "                if contains_digits(entry):\n",
      "                    return entry\n",
      "        return np.nan\n",
      "    \n",
      "\n",
      "df['lookup_address'] = df.address.apply(lookupable_adddress)\n",
      "#drop bad lookup addresses\n",
      "df = df[df.lookup_address.notnull()]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 13
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def add_postal(row):\n",
      "    try:\n",
      "        result = '{0}, {1:n}'.format(row.lookup_address, row.postal_code)\n",
      "    except UnicodeEncodeError as e:\n",
      "        #there are a few cases with accents in the name\n",
      "        return np.nan\n",
      "    else:\n",
      "        return result\n",
      "\n",
      "df['complete_lookup_address'] = df.apply(add_postal, axis = 1)\n",
      "df = df.drop('lookup_address', 1)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 14
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df[df.name == 'California Pizza Kitchen'].complete_lookup_address"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 15,
       "text": [
        "51937        1328 Stoneridge Mall Road, 94588\n",
        "56581                  53 Third Street, 94103\n",
        "57305     347 Corte Madera Town Center, 94925\n",
        "58115    100 Hillsdale Shopping Center, 94403\n",
        "58366                      5614 Bay St, 94608\n",
        "60737                     1 Airport Dr, 94621\n",
        "61231            136 Stanford Shopping, 94304\n",
        "61449                531 Cowper Street, 94301\n",
        "63172                1120 Broadway Plz, 94596\n",
        "Name: complete_lookup_address, dtype: object"
       ]
      }
     ],
     "prompt_number": 15
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "all_categories = set()\n",
      "def list_categories(r):\n",
      "    try:\n",
      "        l = eval(r)\n",
      "    except Exception as e:\n",
      "        print r\n",
      "        print e\n",
      "    else:\n",
      "        for full_name,short_name in l:\n",
      "            all_categories.add(full_name)\n",
      "\n",
      "#drop where categories are nan\n",
      "df = df[df.categories.notnull()]\n",
      "df.categories.apply(list_categories)\n",
      "print all_categories"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "set([u'Roofing', u'Truck Rental', u'Drugstores', u'Dry Cleaning & Laundry', u'Buffets', u'Boating', u'Child Care & Day Care', u'Creperies', u'Pretzels', u'Tapas Bars', u'Pool & Hot Tub Service', u'Comedy Clubs', u'Architects', u'Juice Bars & Smoothies', u'Pet Boarding/Pet Sitting', u'Grocery', u'Hair Removal', u'Dog Parks', u'Adult Education', u'Stadiums & Arenas', u'Community Service/Non-Profit', u'Bowling', u'Restaurants', u'DJs', u'Formal Wear', u'Hiking', u'Fabric Stores', u'Bankruptcy Law', u'Outdoor Gear', u'Delis', u'Bed & Breakfast', u'Tanning', u'Halal', u'Cantonese', u'Herbs & Spices', u'Telecommunications', u'Real Estate Law', u'Chicken Wings', u'Laser Eye Surgery/Lasik', u'Gold Buyers', u'Antiques', u'Food Stands', u'Shopping Centers', u'Mountain Biking', u'Nail Salons', u'Elementary Schools', u'Automotive', u'Interior Design', u'Cosmetic Surgeons', u'Travel Services', u'Lounges', u'Persian/Iranian', u'Shoe Repair', u'Animal Shelters', u'Cajun/Creole', u\"Men's Hair Salons\", u'Hobby Shops', u'Gyms', u'Scandinavian', u'Greek', u'Hardware Stores', u'Art Galleries', u'Watch Repair', u'Limos', u'Chinese', u'Burgers', u'Home Decor', u'Food Trucks', u'Health & Medical', u'Bikes', u'Yoga', u'Ophthalmologists', u'Hookah Bars', u'Middle Eastern', u'Paddleboarding', u'French', u'Brazilian', u'Building Supplies', u'Landscape Architects', u'Legal Services', u'Shanghainese', u'Nutritionists', u'Divorce & Family Law', u'Hungarian', u'Landmarks & Historical Buildings', u'Costumes', u'Vocational & Technical School', u'Diners', u'Sports Bars', u'Donuts', u'Russian', u'Auto Parts & Supplies', u'Seafood Markets', u'Firewood', u'Public Relations', u'Vegetarian', u'Piercing', u'Kosher', u'Flea Markets', u'Climbing', u'Libraries', u'Couriers & Delivery Services', u'German', u'Tobacco Shops', u'Taiwanese', u'Laotian', u'Appliances', u'Basque', u'Art Classes', u'Home Cleaning', u'Pilates', u'Hypnosis/Hypnotherapy', u'Vietnamese', u'Massage Schools', u'Smog Check Stations', u'Home Services', u'Venezuelan', u'Pizza', u'Eyewear & Opticians', u'Marketing', u'Electricians', u'Doulas', u'Movers', u'Office Cleaning', u'Blow Dry/Out Services', u'Falafel', u'Food Court', u'Furniture Reupholstery', u'Music Venues', u'Archery', u'Web Design', u'Seafood', u'British', u'Discount Store', u'Professional Sports Teams', u'Screen Printing', u'South African', u'Bail Bondsmen', u'Boat Charters', u'Japanese', u'Books, Mags, Music & Video', u'Badminton', u'Lice Services', u'Hot Dogs', u'Beer, Wine & Spirits', u'Festivals', u'Salvadoran', u'Drywall Installation & Repair', u'Sandwiches', u'Shaved Ice', u'Video/Film Production', u'Dance Studios', u'Chiropractors', u'Shades & Blinds', u'Dim Sum', u'Fitness & Instruction', u'Summer Camps', u'RV Dealers', u'Armenian', u\"Men's Clothing\", u'Music & DVDs', u'Basketball Courts', u'Nightlife', u'Cocktail Bars', u'Dive Bars', u'Playgrounds', u'Mortgage Brokers', u'Television Stations', u'Pressure Washers', u'Insulation Installation', u'Hats', u'Weight Loss Centers', u'Accessories', u'Watches', u'Active Life', u'Hospitals', u'Window Washing', u'Latin American', u'Beauty & Spas', u'Butcher', u'Acupuncture', u'Damage Restoration', u'Guest Houses', u'Distilleries', u'Hair Salons', u'Comic Books', u'Car Wash', u'Lawyers', u'Mass Media', u'Coffee & Tea', u'Optometrists', u'Cheesesteaks', u'Tattoo Removal', u'Estate Planning Law', u'Occupational Therapy', u'Life Coach', u'Szechuan', u'Contractors', u'Midwives', u'Hearing Aid Providers', u'Amateur Sports Teams', u'Body Shops', u'Cuban', u'Dog Walkers', u'Dance Schools', u'Auction Houses', u'CSA', u'Appliances & Repair', u'Museums', u'Senegalese', u'Orthopedists', u'Lingerie', u'Private Investigation', u'Solar Installation', u'Employment Agencies', u'Massage', u'General Litigation', u'Uniforms', u'Pool Halls', u'Tex-Mex', u'Rafting/Kayaking', u'Keys & Locksmiths', u'Real Estate', u'Hospice', u'Diving', u'Professional Services', u'Scuba Diving', u'Performing Arts', u'Cupcakes', u'Sports Medicine', u'Office Equipment', u'Swimwear', u'Trainers', u'Indian', u'Bubble Tea', u'Fireplace Services', u'Barbeque', u'Recreation Centers', u'Nurseries & Gardening', u'Transportation', u'Home Theatre Installation', u'Catalan', u'Fireworks', u'Retirement Homes', u'Tax Services', u'Afghan', u'Boot Camps', u'Adult', u'Caterers', u'Party & Event Planning', u'First Aid Classes', u'Waxing', u'Art Schools', u'Printing Services', u'Bookstores', u'Plumbing', u'Bartenders', u'Naturopathic/Holistic', u'Apartments', u'American (Traditional)', u'Home Inspectors', u'Rolfing', u'Vape Shops', u'Martial Arts', u'Television Service Providers', u'Knitting Supplies', u'Hot Tub & Pool', u'Soccer', u'Piano Bars', u'Religious Schools', u'Furniture Stores', u'Food', u'Heating & Air Conditioning/HVAC', u'Embroidery & Crochet', u'Photographers', u'Maternity Wear', u'Tree Services', u'Shared Office Spaces', u'Print Media', u'Moroccan', u'Venues & Event Spaces', u'Surfing', u'Ethnic Food', u'Jazz & Blues', u'Day Spas', u'Computers', u'Local Services', u'Tutoring Centers', u'Reflexology', u'Advertising', u'Criminal Defense Law', u'Convenience Stores', u'Comfort Food', u'Portuguese', u'Special Education', u'Colleges & Universities', u'Medical Spas', u'Bespoke Clothing', u'Art Supplies', u'Auto Detailing', u'Arcades', u'Fishing', u'Language Schools', u'Cafeteria', u'Health Retreats', u'Soul Food', u'IT Services & Computer Repair', u'Tanning Beds', u'Gluten-Free', u'Arts & Entertainment', u'Home Window Tinting', u'Musicians', u'Motorcycle Gear', u'Sushi Bars', u'Tea Rooms', u'Nanny Services', u'Newspapers & Magazines', u'Permanent Makeup', u'Cosmetics & Beauty Supply', u'Fondue', u'Hair Extensions', u'Session Photography', u'Educational Services', u'Gelato', u'Irish', u'Translation Services', u'Food Delivery Services', u'Garage Door Services', u'Videographers', u'Sporting Goods', u'Amusement Parks', u'Door Sales/Installation', u'Spray Tanning', u'Specialty Schools', u'Fertility', u'Italian', u'Personal Assistants', u'Metal Fabricators', u'Synagogues', u'Mediterranean', u'Department Stores', u'Motorcycle Dealers', u'Self Storage', u'Medical Centers', u'Austrian', u'Education', u'Recycling Center', u'Internet Cafes', u'Personal Chefs', u'Irish Pub', u'Caribbean', u'Dance Clubs', u'American (New)', u'Investing', u'Cosmetology Schools', u'Breakfast & Brunch', u'Radio Stations', u'Paintball', u'Do-It-Yourself Food', u\"Children's Clothing\", u'Junk Removal & Hauling', u'Gas & Service Stations', u'Indonesian', u'Shopping', u'Hawaiian', u'Utilities', u'Golf Equipment', u'Argentine', u'Handyman', u'Screen Printing/T-Shirt Printing', u'Thai', u'Fish & Chips', u'Skin Care', u'Burmese', u'Farmers Market', u'African', u'Middle Schools & High Schools', u'Outlet Stores', u'Singaporean', u'Vehicle Shipping', u'Cinema', u'Asian Fusion', u'Mattresses', u'Arts & Crafts', u'Gutter Services', u'Tours', u'Fences & Gates', u'Gun/Rifle Ranges', u'Pool Cleaners', u'Makeup Artists', u'Commercial Real Estate', u'Turkish', u'Laser Hair Removal', u'Live/Raw Food', u'Gay Bars', u'Preschools', u'Home Organization', u'Internet Service Providers', u'Guns & Ammo', u'Southern', u'Sewing & Alterations', u'Pediatricians', u'Yelp Events', u'Doctors', u'Cooking Schools', u'Used, Vintage & Consignment', u'Physical Therapy', u'University Housing', u'Leisure Centers', u'Mobile Phone Repair', u'Hot Pot', u'Post Offices', u'Casinos', u'Champagne Bars', u'Pawn Shops', u'Fire Departments', u'Cards & Stationery', u'Irrigation', u'Candy Stores', u'Vinyl Records', u'Karaoke', u'Tires', u'Property Management', u'Mongolian', u'Wine Bars', u'Hotels', u'Golf Equipment Shops', u'Shipping Centers', u'Ethiopian', u'Bagels', u'Tennis', u'Social Clubs', u'Painters', u'Buddhist Temples', u'Towing', u'Bridal', u'Vacation Rentals', u'Musical Instruments & Teachers', u'Salad', u'Structural Engineers', u'Massage Therapy', u'Himalayan/Nepalese', u'Accountants', u'Financial Advising', u'Career Counseling', u'Local Flavor', u'Home Health Care', u'Hotels & Travel', u'Lebanese', u'Swimming Lessons/Schools', u'Luggage', u'Flowers & Gifts', u'Soup', u'Car Rental', u'Hair Stylists', u'Wedding Planning', u'Counseling & Mental Health', u'Masonry/Concrete', u'Auto Repair', u'Peruvian', u'Talent Agencies', u'Mobile Phones', u'Motorcycle Repair', u'Rugs', u'Chimney Sweeps', u'Notaries', u'Street Vendors', u'Tapas/Small Plates', u'Jewelry Repair', u'Signmaking', u'Wholesale Stores', u'Arabian', u'Pets', u'Churches', u'Wigs', u'Belgian', u'Propane', u'Skating Rinks', u'Day Camps', u'Reiki', u'Baby Gear & Furniture', u'Party Supplies', u'Boat Repair', u'Filipino', u'Toy Stores', u'Shoe Stores', u'Brewing Supplies', u'Data Recovery', u'Dermatologists', u'Immigration Law', u'Driving Schools', u'Leather Goods', u'Steakhouses', u'Fashion', u'Thrift Stores', u'Recording & Rehearsal Studios', u'Public Services & Government', u'Desserts', u'Puerto Rican', u'Beaches', u'Oncologist', u'Meditation Centers', u'Polish', u'Event Planning & Services', u'Obstetricians & Gynecologists', u'Jewelry', u'Home & Garden', u'Fruits & Veggies', u'Sports Wear', u'Carpeting', u'Banks & Credit Unions', u'Auto Glass Services', u'Laboratory Testing', u'Dentists', u'Family Practice', u'Magicians', u'Carpet Installation', u'Bike Rentals', u'Cafes', u\"Women's Clothing\", u'Plus Size Fashion', u'Wineries', u'Lighting Fixtures & Equipment', u'Kitchen & Bath', u'Ice Cream & Frozen Yogurt', u'Eyelash Service', u'Bars', u'Cabinetry', u'Pop-up Shops', u'Veterinarians', u'Health Markets', u'Event Photography', u'Cosmetic Dentists', u'Tattoo', u'Electronics Repair', u'Adult Entertainment', u'Carpet Cleaning', u'Boat Dealers', u'Water Delivery', u'Real Estate Services', u'Pet Services', u'Korean', u'Pakistani', u'Personal Shopping', u'Trophy Shops', u'Pest Control', u'Pet Stores', u'Check Cashing/Pay-day Loans', u'Security Systems', u'Brasseries', u'Cultural Center', u'Vegan', u'Pubs', u'Landscaping', u'Mini Golf', u'Framing', u'Gastropubs', u'Car Stereo Installation', u'Bakeries', u'Cooking Classes', u'Religious Organizations', u'Surf Shop', u'Swimming Pools', u'Breweries', u'Malaysian', u'Funeral Services & Cemeteries', u'Graphic Design', u'Kiteboarding', u'Modern European', u'Photography Stores & Services', u'Private Tutors', u'Party Equipment Rentals', u'Psychics & Astrologers', u'Barbers', u'Golf', u'Czech', u'Ramen', u'Chocolatiers & Shops', u'Rehabilitation Center', u'Cannabis Clinics', u'Real Estate Agents', u'Horseback Riding', u'Parking', u'Videos & Video Game Rental', u'Windshield Installation & Repair', u'Insurance', u'Flooring', u'Cheese Shops', u'Surgeons', u'Parks', u'CPR Classes', u'Fast Food', u'Electronics', u'Lactation Services', u'Colombian', u'Bike Repair/Maintenance', u'Medical Supplies', u'Sports Clubs', u'Gardeners', u'Glass & Mirrors', u'Home Staging', u'Financial Services', u'Florists', u'Gift Shops', u'Windows Installation', u'Internal Medicine', u'Mexican', u'Car Dealers', u'Ticket Sales', u'Meat Shops', u'Traditional Chinese Medicine', u'Cambodian', u'Specialty Food', u'Spanish', u'Free Diving'])\n"
       ]
      }
     ],
     "prompt_number": 16
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "len(all_categories)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 17,
       "text": [
        "615"
       ]
      }
     ],
     "prompt_number": 17
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.complete_lookup_address"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 18,
       "text": [
        "0        3201 California St, 94118\n",
        "1        565 Buckingham Way, 94132\n",
        "2       1860 El Camino Real, 94010\n",
        "3         209 Broadway Ave., 94030\n",
        "4         241 California St, 94111\n",
        "5         311 California St, 94104\n",
        "6               101 Pine St, 94111\n",
        "7             405 Howard St, 94105\n",
        "8     321 Gellert Boulevard, 94015\n",
        "9        701 Sacramento St., 94108\n",
        "10          4669 Mission St, 94112\n",
        "11            557 Castro St, 94114\n",
        "12       490 El Camino Real, 94002\n",
        "13            460 Sutter St, 94108\n",
        "14          1405 Noriega St, 94122\n",
        "...\n",
        "79260           900 Andersen Drive, 94901\n",
        "79261         75 Bellam Blvd Ste C, 94901\n",
        "79262               75 Bellam Blvd, 94901\n",
        "79263    2201 Larkspur Landing Cir, 94977\n",
        "79265              28 Bellam Blvd., 94901\n",
        "79266    2229 Larkspur Landing Cir, 94939\n",
        "79267      863 East Francisco Blvd, 94901\n",
        "79269               75 Bellam Blvd, 94901\n",
        "79270              4027 Noriega St, 94122\n",
        "79271               75 Bellam Blvd, 94901\n",
        "79272                 45 Vivian St, 94901\n",
        "79273               75 Bellam Blvd, 94901\n",
        "79274    2009 Larkspur Landing Cir, 94939\n",
        "79275    2413 Larkspur Landing Cir, 94939\n",
        "79276                  138 Gary Pl, 94901\n",
        "Name: complete_lookup_address, Length: 66328, dtype: object"
       ]
      }
     ],
     "prompt_number": 18
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "'''Export df for location lookup'''\n",
      "# filename = 'location_frame_1.txt'\n",
      "# #create a reduced dataframe with only the needed fields\n",
      "# locationdf = df[['id','complete_lookup_address']]\n",
      "# #add columns we'll need:\n",
      "# locationdf['latitude'] = np.nan\n",
      "# locationdf['longitude'] = np.nan\n",
      "# locationdf['latlng_confidence'] = np.nan\n",
      "# pd.to_pickle(locationdf, filename)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 21,
       "text": [
        "'Export df for location lookup'"
       ]
      }
     ],
     "prompt_number": 21
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#load location data (which as been processed with clean_location_data notebook)\n",
      "location = pd.read_pickle('location_frame_0.txt')\n",
      "print location.head()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "                                        id     complete_lookup_address  \\\n",
        "0       sf-fire-credit-union-san-francisco   3201 California St, 94118   \n",
        "1     sf-fire-credit-union-san-francisco-2   565 Buckingham Way, 94132   \n",
        "2  united-health-credit-union-burlingame-2  1860 El Camino Real, 94010   \n",
        "3          provident-credit-union-millbrae    209 Broadway Ave., 94030   \n",
        "4     redwood-credit-union-san-francisco-2    241 California St, 94111   \n",
        "\n",
        "    latitude   longitude latlng_confidence  \n",
        "0  37.787171 -122.447225             P1AAA  \n",
        "1  37.730150 -122.476210             L1AAA  \n",
        "2  37.596147 -122.384672             P1AAA  \n",
        "3  37.599988 -122.390680             L1AAA  \n",
        "4  37.793305 -122.399275             L1AAA  \n",
        "\n",
        "[5 rows x 5 columns]\n"
       ]
      }
     ],
     "prompt_number": 19
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#merge the two datasets on matching ids\n",
      "final = pd.merge(df, location)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 20
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#make a separate list of categories\n",
      "cat_listing  = []\n",
      "\n",
      "def process_categories(row):\n",
      "    ident, cats = row\n",
      "    try:\n",
      "        l = eval(cats)\n",
      "    except Exception as e:\n",
      "        print l\n",
      "        print e\n",
      "    else:\n",
      "        for full_name,short_name in l:\n",
      "            cat_listing.append((ident, short_name, full_name))\n",
      "    \n",
      "final[['id','categories']].apply(process_categories, axis = 1)\n",
      "categories = pd.DataFrame.from_records(cat_listing, columns = ['id', 'short_category','full_category'])\n",
      "print categories.head()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "                                        id short_category  \\\n",
        "0       sf-fire-credit-union-san-francisco          banks   \n",
        "1       sf-fire-credit-union-san-francisco      investing   \n",
        "2     sf-fire-credit-union-san-francisco-2          banks   \n",
        "3  united-health-credit-union-burlingame-2          banks   \n",
        "4          provident-credit-union-millbrae          banks   \n",
        "\n",
        "           full_category  \n",
        "0  Banks & Credit Unions  \n",
        "1              Investing  \n",
        "2  Banks & Credit Unions  \n",
        "3  Banks & Credit Unions  \n",
        "4  Banks & Credit Unions  \n",
        "\n",
        "[5 rows x 3 columns]\n"
       ]
      }
     ],
     "prompt_number": 21
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "\"\"\"Clean up display_address columns\"\"\"\n",
      "\n",
      "def clean_address(parts):\n",
      "    try:\n",
      "        parts = eval(parts)\n",
      "        return ', '.join(parts)\n",
      "    except Exception as e:\n",
      "        return pd.np.nan\n",
      "\n",
      "final.display_address = final.display_address.apply(clean_address)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 22
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "'''Save everything'''\n",
      "final.to_csv('yelp.csv')\n",
      "categories.to_csv('categories.csv')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 37
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}