yelp_01dataCleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#!/usr/bin/env python\n",
    "# -*- coding: utf-8 -*-"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Python package imports</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Pandas version: 0.17.0\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import os\n",
    "import glob\n",
    "import pandas as pd\n",
    "import yelp_utils\n",
    "from yelp_utils import *\n",
    "import yelp_utils\n",
    "print \"Pandas version: \" + pd.__version__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pd.options.display.max_columns = 200"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "yelp_utils.make_sure_path_exists(yelp_utils.YELP_DATA_CSV_DIR)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DATA WRANGLING"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Convert the json files to flattened csv for clarity and ease of access. </b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# http://stackoverflow.com/questions/3207219/how-to-list-all-files-of-a-directory-in-python\n",
    "for json_file in glob.glob(os.path.join(yelp_utils.YELP_DATA_RAW_DIR, \"*.json\")):\n",
    "    csv_file = '{0}.csv'.format(os.path.join(yelp_utils.YELP_DATA_CSV_DIR,os.path.basename(json_file).split('.json')[0]))\n",
    "    if not os.path.isfile(csv_file):\n",
    "        %run json_to_csv_converter.py $json_file $YELP_DATA_CSV_DIR"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Read the csv to pandas dataframe </b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "*****SHAPE********\n",
      "(77445, 98)\n",
      "*****NULL PERCENTAGE*********\n",
      "attributes.Ambience.divey                               0.732068\n",
      "attributes.Dietary Restrictions.vegan                   0.997986\n",
      "attributes.Happy Hour                                   0.940965\n",
      "hours.Thursday.open                                     0.363019\n",
      "attributes.Order at Counter                             0.995145\n",
      "attributes.Hair Types Specialized In.africanamerican    0.994435\n",
      "attributes.Hair Types Specialized In.kids               0.994306\n",
      "attributes.BYOB                                         0.989037\n",
      "hours.Friday.open                                       0.365653\n",
      "attributes.Good For.latenight                           0.715127\n",
      "attributes.Outdoor Seating                              0.656518\n",
      "attributes.Alcohol                                      0.698780\n",
      "attributes.Ambience.classy                              0.723158\n",
      "attributes.By Appointment Only                          0.848357\n",
      "attributes.Parking.lot                                  0.424275\n",
      "business_id                                             0.000000\n",
      "attributes.Ambience.touristy                            0.723158\n",
      "attributes.Corkage                                      0.991852\n",
      "hours.Tuesday.open                                      0.372613\n",
      "attributes.Good For.brunch                              0.715863\n",
      "categories                                              0.000000\n",
      "attributes.Waiter Service                               0.724553\n",
      "hours.Monday.open                                       0.409813\n",
      "name                                                    0.000000\n",
      "attributes.Parking.street                               0.424275\n",
      "attributes.Ambience.hipster                             0.725263\n",
      "attributes.BYOB/Corkage                                 0.983020\n",
      "attributes.Hair Types Specialized In.straightperms      0.994435\n",
      "attributes.Music.live                                   0.963406\n",
      "attributes.Dietary Restrictions.dairy-free              0.997986\n",
      "                                                          ...   \n",
      "attributes.Coat Check                                   0.943211\n",
      "longitude                                               0.000000\n",
      "hours.Monday.close                                      0.409813\n",
      "attributes.Hair Types Specialized In.extensions         0.994306\n",
      "hours.Tuesday.close                                     0.372613\n",
      "hours.Saturday.close                                    0.432991\n",
      "attributes.Good for Kids                                0.608393\n",
      "attributes.Parking.validated                            0.429350\n",
      "hours.Sunday.open                                       0.581651\n",
      "attributes.Accepts Insurance                            0.992149\n",
      "attributes.Music.dj                                     0.951863\n",
      "attributes.Dietary Restrictions.soy-free                0.997986\n",
      "attributes.Has TV                                       0.706850\n",
      "hours.Sunday.close                                      0.581651\n",
      "attributes.Ambience.casual                              0.723158\n",
      "attributes.Hair Types Specialized In.perms              0.994306\n",
      "attributes.Dogs Allowed                                 0.949241\n",
      "attributes.Drive-Thru                                   0.957066\n",
      "attributes.Dietary Restrictions.vegetarian              0.997986\n",
      "hours.Wednesday.open                                    0.365731\n",
      "attributes.Noise Level                                  0.724902\n",
      "attributes.Smoking                                      0.938059\n",
      "attributes.Attire                                       0.696727\n",
      "attributes.Hair Types Specialized In.curly              0.994306\n",
      "attributes.Good For Groups                              0.664136\n",
      "neighborhoods                                           0.000000\n",
      "attributes.Open 24 Hours                                0.995855\n",
      "attributes.Ambience.romantic                            0.723158\n",
      "attributes.Music.jukebox                                0.963368\n",
      "attributes.Ambience.upscale                             0.724837\n",
      "dtype: float64\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>attributes.Ambience.divey</th>\n",
       "      <th>attributes.Dietary Restrictions.vegan</th>\n",
       "      <th>attributes.Happy Hour</th>\n",
       "      <th>hours.Thursday.open</th>\n",
       "      <th>attributes.Order at Counter</th>\n",
       "      <th>attributes.Hair Types Specialized In.africanamerican</th>\n",
       "      <th>attributes.Hair Types Specialized In.kids</th>\n",
       "      <th>attributes.BYOB</th>\n",
       "      <th>hours.Friday.open</th>\n",
       "      <th>attributes.Good For.latenight</th>\n",
       "      <th>attributes.Outdoor Seating</th>\n",
       "      <th>attributes.Alcohol</th>\n",
       "      <th>attributes.Ambience.classy</th>\n",
       "      <th>attributes.By Appointment Only</th>\n",
       "      <th>attributes.Parking.lot</th>\n",
       "      <th>business_id</th>\n",
       "      <th>attributes.Ambience.touristy</th>\n",
       "      <th>attributes.Corkage</th>\n",
       "      <th>hours.Tuesday.open</th>\n",
       "      <th>attributes.Good For.brunch</th>\n",
       "      <th>categories</th>\n",
       "      <th>attributes.Waiter Service</th>\n",
       "      <th>hours.Monday.open</th>\n",
       "      <th>name</th>\n",
       "      <th>attributes.Parking.street</th>\n",
       "      <th>attributes.Ambience.hipster</th>\n",
       "      <th>attributes.BYOB/Corkage</th>\n",
       "      <th>attributes.Hair Types Specialized In.straightperms</th>\n",
       "      <th>attributes.Music.live</th>\n",
       "      <th>attributes.Dietary Restrictions.dairy-free</th>\n",
       "      <th>attributes.Music.background_music</th>\n",
       "      <th>attributes.Price Range</th>\n",
       "      <th>attributes.Good For.breakfast</th>\n",
       "      <th>attributes.Parking.garage</th>\n",
       "      <th>attributes.Music.karaoke</th>\n",
       "      <th>attributes.Good For Dancing</th>\n",
       "      <th>review_count</th>\n",
       "      <th>attributes.Hair Types Specialized In.asian</th>\n",
       "      <th>state</th>\n",
       "      <th>attributes.Accepts Credit Cards</th>\n",
       "      <th>hours.Friday.close</th>\n",
       "      <th>attributes.Good For.lunch</th>\n",
       "      <th>attributes.Parking.valet</th>\n",
       "      <th>attributes.Take-out</th>\n",
       "      <th>full_address</th>\n",
       "      <th>hours.Thursday.close</th>\n",
       "      <th>attributes.Hair Types Specialized In.coloring</th>\n",
       "      <th>attributes.Good For.dessert</th>\n",
       "      <th>attributes.Music.video</th>\n",
       "      <th>attributes.Dietary Restrictions.halal</th>\n",
       "      <th>attributes.Takes Reservations</th>\n",
       "      <th>hours.Saturday.open</th>\n",
       "      <th>attributes.Ages Allowed</th>\n",
       "      <th>attributes.Ambience.trendy</th>\n",
       "      <th>attributes.Delivery</th>\n",
       "      <th>hours.Wednesday.close</th>\n",
       "      <th>attributes.Wi-Fi</th>\n",
       "      <th>open</th>\n",
       "      <th>city</th>\n",
       "      <th>attributes.Wheelchair Accessible</th>\n",
       "      <th>attributes.Dietary Restrictions.gluten-free</th>\n",
       "      <th>stars</th>\n",
       "      <th>attributes.Dietary Restrictions.kosher</th>\n",
       "      <th>type</th>\n",
       "      <th>attributes.Caters</th>\n",
       "      <th>attributes.Ambience.intimate</th>\n",
       "      <th>latitude</th>\n",
       "      <th>attributes.Good For.dinner</th>\n",
       "      <th>attributes.Coat Check</th>\n",
       "      <th>longitude</th>\n",
       "      <th>hours.Monday.close</th>\n",
       "      <th>attributes.Hair Types Specialized In.extensions</th>\n",
       "      <th>hours.Tuesday.close</th>\n",
       "      <th>hours.Saturday.close</th>\n",
       "      <th>attributes.Good for Kids</th>\n",
       "      <th>attributes.Parking.validated</th>\n",
       "      <th>hours.Sunday.open</th>\n",
       "      <th>attributes.Accepts Insurance</th>\n",
       "      <th>attributes.Music.dj</th>\n",
       "      <th>attributes.Dietary Restrictions.soy-free</th>\n",
       "      <th>attributes.Has TV</th>\n",
       "      <th>hours.Sunday.close</th>\n",
       "      <th>attributes.Ambience.casual</th>\n",
       "      <th>attributes.Hair Types Specialized In.perms</th>\n",
       "      <th>attributes.Dogs Allowed</th>\n",
       "      <th>attributes.Drive-Thru</th>\n",
       "      <th>attributes.Dietary Restrictions.vegetarian</th>\n",
       "      <th>hours.Wednesday.open</th>\n",
       "      <th>attributes.Noise Level</th>\n",
       "      <th>attributes.Smoking</th>\n",
       "      <th>attributes.Attire</th>\n",
       "      <th>attributes.Hair Types Specialized In.curly</th>\n",
       "      <th>attributes.Good For Groups</th>\n",
       "      <th>neighborhoods</th>\n",
       "      <th>attributes.Open 24 Hours</th>\n",
       "      <th>attributes.Ambience.romantic</th>\n",
       "      <th>attributes.Music.jukebox</th>\n",
       "      <th>attributes.Ambience.upscale</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11:00</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>none</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>5UmKMjUEUNdYWqANhGckJw</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11:00</td>\n",
       "      <td>False</td>\n",
       "      <td>['Fast Food', 'Restaurants']</td>\n",
       "      <td>False</td>\n",
       "      <td>11:00</td>\n",
       "      <td>Mr Hoagie</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PA</td>\n",
       "      <td>True</td>\n",
       "      <td>21:00</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>4734 Lebanon Church Rd\\nDravosburg, PA 15034</td>\n",
       "      <td>21:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>21:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>Dravosburg</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>business</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>40.354327</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-79.900706</td>\n",
       "      <td>21:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11:00</td>\n",
       "      <td>average</td>\n",
       "      <td>NaN</td>\n",
       "      <td>casual</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UsFtqoBl7naz8AVUBZMjQQ</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['Nightlife']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Clancy's Pub</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PA</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>202 McClure St\\nDravosburg, PA 15034</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>Dravosburg</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>business</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.350553</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-79.886814</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3eu6MEFlq2Dg7bQh8QbdOg</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['Auto Repair', 'Automotive']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Joe Cislo's Auto</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1 Ravine St\\nDravosburg, PA 15034</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>Dravosburg</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>business</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.350956</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-79.889059</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>cE27W9VPgO88Qxe4ol6y_g</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['Active Life', 'Mini Golf', 'Golf']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Cool Springs Golf Center</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1530 Hamilton Rd\\nBethel Park, PA 15234</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>Bethel Park</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>business</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.354116</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-80.014660</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>HZdLhv6COCleJMo7nPl-RA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['Shopping', 'Home Services', 'Internet Servic...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10:00</td>\n",
       "      <td>Verizon</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PA</td>\n",
       "      <td>False</td>\n",
       "      <td>17:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>301 South Hills Village\\nPittsburgh, PA 15241</td>\n",
       "      <td>17:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>business</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.357620</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-80.059980</td>\n",
       "      <td>21:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21:00</td>\n",
       "      <td>21:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>11:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  attributes.Ambience.divey attributes.Dietary Restrictions.vegan  \\\n",
       "0                     False                                   NaN   \n",
       "1                       NaN                                   NaN   \n",
       "2                       NaN                                   NaN   \n",
       "3                       NaN                                   NaN   \n",
       "4                       NaN                                   NaN   \n",
       "\n",
       "  attributes.Happy Hour hours.Thursday.open attributes.Order at Counter  \\\n",
       "0                   NaN               11:00                         NaN   \n",
       "1                  True                 NaN                         NaN   \n",
       "2                   NaN                 NaN                         NaN   \n",
       "3                   NaN                 NaN                         NaN   \n",
       "4                   NaN               10:00                         NaN   \n",
       "\n",
       "  attributes.Hair Types Specialized In.africanamerican  \\\n",
       "0                                                NaN     \n",
       "1                                                NaN     \n",
       "2                                                NaN     \n",
       "3                                                NaN     \n",
       "4                                                NaN     \n",
       "\n",
       "  attributes.Hair Types Specialized In.kids attributes.BYOB hours.Friday.open  \\\n",
       "0                                       NaN             NaN             11:00   \n",
       "1                                       NaN             NaN               NaN   \n",
       "2                                       NaN             NaN               NaN   \n",
       "3                                       NaN             NaN               NaN   \n",
       "4                                       NaN             NaN             10:00   \n",
       "\n",
       "  attributes.Good For.latenight attributes.Outdoor Seating attributes.Alcohol  \\\n",
       "0                         False                      False               none   \n",
       "1                           NaN                      False                NaN   \n",
       "2                           NaN                        NaN                NaN   \n",
       "3                           NaN                        NaN                NaN   \n",
       "4                           NaN                        NaN                NaN   \n",
       "\n",
       "  attributes.Ambience.classy attributes.By Appointment Only  \\\n",
       "0                      False                            NaN   \n",
       "1                        NaN                            NaN   \n",
       "2                        NaN                            NaN   \n",
       "3                        NaN                            NaN   \n",
       "4                        NaN                            NaN   \n",
       "\n",
       "  attributes.Parking.lot             business_id attributes.Ambience.touristy  \\\n",
       "0                  False  5UmKMjUEUNdYWqANhGckJw                        False   \n",
       "1                    NaN  UsFtqoBl7naz8AVUBZMjQQ                          NaN   \n",
       "2                    NaN  3eu6MEFlq2Dg7bQh8QbdOg                          NaN   \n",
       "3                    NaN  cE27W9VPgO88Qxe4ol6y_g                          NaN   \n",
       "4                  False  HZdLhv6COCleJMo7nPl-RA                          NaN   \n",
       "\n",
       "  attributes.Corkage hours.Tuesday.open attributes.Good For.brunch  \\\n",
       "0                NaN              11:00                      False   \n",
       "1                NaN                NaN                        NaN   \n",
       "2                NaN                NaN                        NaN   \n",
       "3                NaN                NaN                        NaN   \n",
       "4                NaN              10:00                        NaN   \n",
       "\n",
       "                                          categories  \\\n",
       "0                       ['Fast Food', 'Restaurants']   \n",
       "1                                      ['Nightlife']   \n",
       "2                      ['Auto Repair', 'Automotive']   \n",
       "3               ['Active Life', 'Mini Golf', 'Golf']   \n",
       "4  ['Shopping', 'Home Services', 'Internet Servic...   \n",
       "\n",
       "  attributes.Waiter Service hours.Monday.open                      name  \\\n",
       "0                     False             11:00                 Mr Hoagie   \n",
       "1                       NaN               NaN              Clancy's Pub   \n",
       "2                       NaN               NaN          Joe Cislo's Auto   \n",
       "3                       NaN               NaN  Cool Springs Golf Center   \n",
       "4                       NaN             10:00                   Verizon   \n",
       "\n",
       "  attributes.Parking.street attributes.Ambience.hipster  \\\n",
       "0                     False                       False   \n",
       "1                       NaN                         NaN   \n",
       "2                       NaN                         NaN   \n",
       "3                       NaN                         NaN   \n",
       "4                     False                         NaN   \n",
       "\n",
       "  attributes.BYOB/Corkage attributes.Hair Types Specialized In.straightperms  \\\n",
       "0                     NaN                                                NaN   \n",
       "1                     NaN                                                NaN   \n",
       "2                     NaN                                                NaN   \n",
       "3                     NaN                                                NaN   \n",
       "4                     NaN                                                NaN   \n",
       "\n",
       "  attributes.Music.live attributes.Dietary Restrictions.dairy-free  \\\n",
       "0                   NaN                                        NaN   \n",
       "1                   NaN                                        NaN   \n",
       "2                   NaN                                        NaN   \n",
       "3                   NaN                                        NaN   \n",
       "4                   NaN                                        NaN   \n",
       "\n",
       "  attributes.Music.background_music  attributes.Price Range  \\\n",
       "0                               NaN                       1   \n",
       "1                               NaN                       1   \n",
       "2                               NaN                     NaN   \n",
       "3                               NaN                     NaN   \n",
       "4                               NaN                       2   \n",
       "\n",
       "  attributes.Good For.breakfast attributes.Parking.garage  \\\n",
       "0                         False                     False   \n",
       "1                           NaN                       NaN   \n",
       "2                           NaN                       NaN   \n",
       "3                           NaN                       NaN   \n",
       "4                           NaN                     False   \n",
       "\n",
       "  attributes.Music.karaoke attributes.Good For Dancing  review_count  \\\n",
       "0                      NaN                         NaN             4   \n",
       "1                      NaN                         NaN             4   \n",
       "2                      NaN                         NaN             3   \n",
       "3                      NaN                         NaN             5   \n",
       "4                      NaN                         NaN             5   \n",
       "\n",
       "  attributes.Hair Types Specialized In.asian state  \\\n",
       "0                                        NaN    PA   \n",
       "1                                        NaN    PA   \n",
       "2                                        NaN    PA   \n",
       "3                                        NaN    PA   \n",
       "4                                        NaN    PA   \n",
       "\n",
       "  attributes.Accepts Credit Cards hours.Friday.close  \\\n",
       "0                            True              21:00   \n",
       "1                            True                NaN   \n",
       "2                             NaN                NaN   \n",
       "3                             NaN                NaN   \n",
       "4                           False              17:00   \n",
       "\n",
       "  attributes.Good For.lunch attributes.Parking.valet attributes.Take-out  \\\n",
       "0                     False                    False                True   \n",
       "1                       NaN                      NaN                 NaN   \n",
       "2                       NaN                      NaN                 NaN   \n",
       "3                       NaN                      NaN                 NaN   \n",
       "4                       NaN                    False                 NaN   \n",
       "\n",
       "                                    full_address hours.Thursday.close  \\\n",
       "0   4734 Lebanon Church Rd\\nDravosburg, PA 15034                21:00   \n",
       "1           202 McClure St\\nDravosburg, PA 15034                  NaN   \n",
       "2              1 Ravine St\\nDravosburg, PA 15034                  NaN   \n",
       "3        1530 Hamilton Rd\\nBethel Park, PA 15234                  NaN   \n",
       "4  301 South Hills Village\\nPittsburgh, PA 15241                17:00   \n",
       "\n",
       "  attributes.Hair Types Specialized In.coloring attributes.Good For.dessert  \\\n",
       "0                                           NaN                       False   \n",
       "1                                           NaN                         NaN   \n",
       "2                                           NaN                         NaN   \n",
       "3                                           NaN                         NaN   \n",
       "4                                           NaN                         NaN   \n",
       "\n",
       "  attributes.Music.video attributes.Dietary Restrictions.halal  \\\n",
       "0                    NaN                                   NaN   \n",
       "1                    NaN                                   NaN   \n",
       "2                    NaN                                   NaN   \n",
       "3                    NaN                                   NaN   \n",
       "4                    NaN                                   NaN   \n",
       "\n",
       "  attributes.Takes Reservations hours.Saturday.open attributes.Ages Allowed  \\\n",
       "0                         False                 NaN                     NaN   \n",
       "1                           NaN                 NaN                     NaN   \n",
       "2                           NaN                 NaN                     NaN   \n",
       "3                           NaN                 NaN                     NaN   \n",
       "4                           NaN               10:00                     NaN   \n",
       "\n",
       "  attributes.Ambience.trendy attributes.Delivery hours.Wednesday.close  \\\n",
       "0                      False               False                 21:00   \n",
       "1                        NaN                 NaN                   NaN   \n",
       "2                        NaN                 NaN                   NaN   \n",
       "3                        NaN                 NaN                   NaN   \n",
       "4                        NaN                 NaN                 21:00   \n",
       "\n",
       "  attributes.Wi-Fi   open         city attributes.Wheelchair Accessible  \\\n",
       "0              NaN   True   Dravosburg                              NaN   \n",
       "1              NaN   True   Dravosburg                              NaN   \n",
       "2              NaN   True   Dravosburg                              NaN   \n",
       "3              NaN  False  Bethel Park                              NaN   \n",
       "4              NaN   True   Pittsburgh                              NaN   \n",
       "\n",
       "  attributes.Dietary Restrictions.gluten-free  stars  \\\n",
       "0                                         NaN    4.5   \n",
       "1                                         NaN    3.5   \n",
       "2                                         NaN    5.0   \n",
       "3                                         NaN    2.5   \n",
       "4                                         NaN    2.5   \n",
       "\n",
       "  attributes.Dietary Restrictions.kosher      type attributes.Caters  \\\n",
       "0                                    NaN  business             False   \n",
       "1                                    NaN  business               NaN   \n",
       "2                                    NaN  business               NaN   \n",
       "3                                    NaN  business               NaN   \n",
       "4                                    NaN  business               NaN   \n",
       "\n",
       "  attributes.Ambience.intimate   latitude attributes.Good For.dinner  \\\n",
       "0                        False  40.354327                      False   \n",
       "1                          NaN  40.350553                        NaN   \n",
       "2                          NaN  40.350956                        NaN   \n",
       "3                          NaN  40.354116                        NaN   \n",
       "4                          NaN  40.357620                        NaN   \n",
       "\n",
       "  attributes.Coat Check  longitude hours.Monday.close  \\\n",
       "0                   NaN -79.900706              21:00   \n",
       "1                   NaN -79.886814                NaN   \n",
       "2                   NaN -79.889059                NaN   \n",
       "3                   NaN -80.014660                NaN   \n",
       "4                   NaN -80.059980              21:00   \n",
       "\n",
       "  attributes.Hair Types Specialized In.extensions hours.Tuesday.close  \\\n",
       "0                                             NaN               21:00   \n",
       "1                                             NaN                 NaN   \n",
       "2                                             NaN                 NaN   \n",
       "3                                             NaN                 NaN   \n",
       "4                                             NaN               21:00   \n",
       "\n",
       "  hours.Saturday.close attributes.Good for Kids attributes.Parking.validated  \\\n",
       "0                  NaN                     True                        False   \n",
       "1                  NaN                      NaN                          NaN   \n",
       "2                  NaN                      NaN                          NaN   \n",
       "3                  NaN                     True                          NaN   \n",
       "4                21:00                      NaN                        False   \n",
       "\n",
       "  hours.Sunday.open attributes.Accepts Insurance attributes.Music.dj  \\\n",
       "0               NaN                          NaN                 NaN   \n",
       "1               NaN                          NaN                 NaN   \n",
       "2               NaN                          NaN                 NaN   \n",
       "3               NaN                          NaN                 NaN   \n",
       "4             11:00                          NaN                 NaN   \n",
       "\n",
       "  attributes.Dietary Restrictions.soy-free attributes.Has TV  \\\n",
       "0                                      NaN             False   \n",
       "1                                      NaN               NaN   \n",
       "2                                      NaN               NaN   \n",
       "3                                      NaN               NaN   \n",
       "4                                      NaN               NaN   \n",
       "\n",
       "  hours.Sunday.close attributes.Ambience.casual  \\\n",
       "0                NaN                      False   \n",
       "1                NaN                        NaN   \n",
       "2                NaN                        NaN   \n",
       "3                NaN                        NaN   \n",
       "4              18:00                        NaN   \n",
       "\n",
       "  attributes.Hair Types Specialized In.perms attributes.Dogs Allowed  \\\n",
       "0                                        NaN                     NaN   \n",
       "1                                        NaN                     NaN   \n",
       "2                                        NaN                     NaN   \n",
       "3                                        NaN                     NaN   \n",
       "4                                        NaN                     NaN   \n",
       "\n",
       "  attributes.Drive-Thru attributes.Dietary Restrictions.vegetarian  \\\n",
       "0                 False                                        NaN   \n",
       "1                   NaN                                        NaN   \n",
       "2                   NaN                                        NaN   \n",
       "3                   NaN                                        NaN   \n",
       "4                   NaN                                        NaN   \n",
       "\n",
       "  hours.Wednesday.open attributes.Noise Level attributes.Smoking  \\\n",
       "0                11:00                average                NaN   \n",
       "1                  NaN                    NaN                NaN   \n",
       "2                  NaN                    NaN                NaN   \n",
       "3                  NaN                    NaN                NaN   \n",
       "4                10:00                    NaN                NaN   \n",
       "\n",
       "  attributes.Attire attributes.Hair Types Specialized In.curly  \\\n",
       "0            casual                                        NaN   \n",
       "1               NaN                                        NaN   \n",
       "2               NaN                                        NaN   \n",
       "3               NaN                                        NaN   \n",
       "4               NaN                                        NaN   \n",
       "\n",
       "  attributes.Good For Groups neighborhoods attributes.Open 24 Hours  \\\n",
       "0                       True            []                      NaN   \n",
       "1                       True            []                      NaN   \n",
       "2                        NaN            []                      NaN   \n",
       "3                        NaN            []                      NaN   \n",
       "4                        NaN            []                      NaN   \n",
       "\n",
       "  attributes.Ambience.romantic attributes.Music.jukebox  \\\n",
       "0                        False                      NaN   \n",
       "1                          NaN                      NaN   \n",
       "2                          NaN                      NaN   \n",
       "3                          NaN                      NaN   \n",
       "4                          NaN                      NaN   \n",
       "\n",
       "  attributes.Ambience.upscale  \n",
       "0                       False  \n",
       "1                         NaN  \n",
       "2                         NaN  \n",
       "3                         NaN  \n",
       "4                         NaN  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "business_csv_file = os.path.join(yelp_utils.YELP_DATA_CSV_DIR, 'yelp_academic_dataset_business.csv')\n",
    "df_business = pd.read_csv(business_csv_file, encoding='utf-8', engine='c', low_memory=False)\n",
    "yelp_utils.getDfInfo(df_business)\n",
    "df_business.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "*****SHAPE********\n",
      "(2225213, 10)\n",
      "*****NULL PERCENTAGE*********\n",
      "user_id         0\n",
      "review_id       0\n",
      "text            0\n",
      "votes.cool      0\n",
      "business_id     0\n",
      "votes.funny     0\n",
      "stars           0\n",
      "date            0\n",
      "type            0\n",
      "votes.useful    0\n",
      "dtype: float64\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>review_id</th>\n",
       "      <th>text</th>\n",
       "      <th>votes.cool</th>\n",
       "      <th>business_id</th>\n",
       "      <th>votes.funny</th>\n",
       "      <th>stars</th>\n",
       "      <th>date</th>\n",
       "      <th>type</th>\n",
       "      <th>votes.useful</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>PUFPaY9KxDAcGqfsorJp3Q</td>\n",
       "      <td>Ya85v4eqdd6k9Od8HbQjyA</td>\n",
       "      <td>Mr Hoagie is an institution. Walking in, it do...</td>\n",
       "      <td>0</td>\n",
       "      <td>5UmKMjUEUNdYWqANhGckJw</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2012-08-01</td>\n",
       "      <td>review</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Iu6AxdBYGR4A0wspR9BYHA</td>\n",
       "      <td>KPvLNJ21_4wbYNctrOwWdQ</td>\n",
       "      <td>Excellent food. Superb customer service. I mis...</td>\n",
       "      <td>0</td>\n",
       "      <td>5UmKMjUEUNdYWqANhGckJw</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2014-02-13</td>\n",
       "      <td>review</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>auESFwWvW42h6alXgFxAXQ</td>\n",
       "      <td>fFSoGV46Yxuwbr3fHNuZig</td>\n",
       "      <td>Yes this place is a little out dated and not o...</td>\n",
       "      <td>0</td>\n",
       "      <td>5UmKMjUEUNdYWqANhGckJw</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2015-10-31</td>\n",
       "      <td>review</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>uK8tzraOp4M5u3uYrqIBXg</td>\n",
       "      <td>Di3exaUCFNw1V4kSNW5pgA</td>\n",
       "      <td>All the food is great here. But the best thing...</td>\n",
       "      <td>0</td>\n",
       "      <td>UsFtqoBl7naz8AVUBZMjQQ</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2013-11-08</td>\n",
       "      <td>review</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>I_47G-R2_egp7ME5u_ltew</td>\n",
       "      <td>0Lua2-PbqEQMjD9r89-asw</td>\n",
       "      <td>We checked this place out this past Monday for...</td>\n",
       "      <td>0</td>\n",
       "      <td>UsFtqoBl7naz8AVUBZMjQQ</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2014-03-29</td>\n",
       "      <td>review</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  user_id               review_id  \\\n",
       "0  PUFPaY9KxDAcGqfsorJp3Q  Ya85v4eqdd6k9Od8HbQjyA   \n",
       "1  Iu6AxdBYGR4A0wspR9BYHA  KPvLNJ21_4wbYNctrOwWdQ   \n",
       "2  auESFwWvW42h6alXgFxAXQ  fFSoGV46Yxuwbr3fHNuZig   \n",
       "3  uK8tzraOp4M5u3uYrqIBXg  Di3exaUCFNw1V4kSNW5pgA   \n",
       "4  I_47G-R2_egp7ME5u_ltew  0Lua2-PbqEQMjD9r89-asw   \n",
       "\n",
       "                                                text  votes.cool  \\\n",
       "0  Mr Hoagie is an institution. Walking in, it do...           0   \n",
       "1  Excellent food. Superb customer service. I mis...           0   \n",
       "2  Yes this place is a little out dated and not o...           0   \n",
       "3  All the food is great here. But the best thing...           0   \n",
       "4  We checked this place out this past Monday for...           0   \n",
       "\n",
       "              business_id  votes.funny  stars        date    type  \\\n",
       "0  5UmKMjUEUNdYWqANhGckJw            0      4  2012-08-01  review   \n",
       "1  5UmKMjUEUNdYWqANhGckJw            0      5  2014-02-13  review   \n",
       "2  5UmKMjUEUNdYWqANhGckJw            0      5  2015-10-31  review   \n",
       "3  UsFtqoBl7naz8AVUBZMjQQ            0      5  2013-11-08  review   \n",
       "4  UsFtqoBl7naz8AVUBZMjQQ            0      3  2014-03-29  review   \n",
       "\n",
       "   votes.useful  \n",
       "0             0  \n",
       "1             0  \n",
       "2             0  \n",
       "3             0  \n",
       "4             0  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "review_csv_file = os.path.join(yelp_utils.YELP_DATA_CSV_DIR, 'yelp_academic_dataset_review.csv')\n",
    "df_review = pd.read_csv(review_csv_file, encoding='utf-8', engine='c') # didnt use low memory option as file is too large\n",
    "getDfInfo(df_review)\n",
    "df_review.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "*****SHAPE********\n",
      "(552339, 23)\n",
      "*****NULL PERCENTAGE*********\n",
      "yelping_since          0.000000\n",
      "compliments.plain      0.801376\n",
      "review_count           0.000000\n",
      "friends                0.000000\n",
      "compliments.cute       0.956192\n",
      "compliments.writer     0.868468\n",
      "fans                   0.000000\n",
      "compliments.note       0.835413\n",
      "type                   0.000000\n",
      "compliments.hot        0.876686\n",
      "compliments.cool       0.832554\n",
      "compliments.profile    0.959652\n",
      "average_stars          0.000000\n",
      "compliments.more       0.904267\n",
      "elite                  0.000000\n",
      "name                   0.000002\n",
      "user_id                0.000000\n",
      "votes.cool             0.000000\n",
      "compliments.list       0.977780\n",
      "votes.funny            0.000000\n",
      "compliments.photos     0.933347\n",
      "compliments.funny      0.884603\n",
      "votes.useful           0.000000\n",
      "dtype: float64\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yelping_since</th>\n",
       "      <th>compliments.plain</th>\n",
       "      <th>review_count</th>\n",
       "      <th>friends</th>\n",
       "      <th>compliments.cute</th>\n",
       "      <th>compliments.writer</th>\n",
       "      <th>fans</th>\n",
       "      <th>compliments.note</th>\n",
       "      <th>type</th>\n",
       "      <th>compliments.hot</th>\n",
       "      <th>compliments.cool</th>\n",
       "      <th>compliments.profile</th>\n",
       "      <th>average_stars</th>\n",
       "      <th>compliments.more</th>\n",
       "      <th>elite</th>\n",
       "      <th>name</th>\n",
       "      <th>user_id</th>\n",
       "      <th>votes.cool</th>\n",
       "      <th>compliments.list</th>\n",
       "      <th>votes.funny</th>\n",
       "      <th>compliments.photos</th>\n",
       "      <th>compliments.funny</th>\n",
       "      <th>votes.useful</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004-10</td>\n",
       "      <td>25</td>\n",
       "      <td>108</td>\n",
       "      <td>['rpOyqD_893cqmDAtJLbdog', '4U9kSBLuBDU391x6bx...</td>\n",
       "      <td>15</td>\n",
       "      <td>9</td>\n",
       "      <td>70</td>\n",
       "      <td>20</td>\n",
       "      <td>user</td>\n",
       "      <td>48</td>\n",
       "      <td>78</td>\n",
       "      <td>8</td>\n",
       "      <td>4.14</td>\n",
       "      <td>3</td>\n",
       "      <td>[2005, 2006]</td>\n",
       "      <td>Russel</td>\n",
       "      <td>18kPq7GPye-YQ3LyKyAZPw</td>\n",
       "      <td>245</td>\n",
       "      <td>NaN</td>\n",
       "      <td>167</td>\n",
       "      <td>14</td>\n",
       "      <td>11</td>\n",
       "      <td>280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2004-10</td>\n",
       "      <td>959</td>\n",
       "      <td>1274</td>\n",
       "      <td>['18kPq7GPye-YQ3LyKyAZPw', '4U9kSBLuBDU391x6bx...</td>\n",
       "      <td>206</td>\n",
       "      <td>327</td>\n",
       "      <td>1179</td>\n",
       "      <td>611</td>\n",
       "      <td>user</td>\n",
       "      <td>1094</td>\n",
       "      <td>1642</td>\n",
       "      <td>116</td>\n",
       "      <td>3.66</td>\n",
       "      <td>134</td>\n",
       "      <td>[2005, 2006, 2007, 2008, 2009, 2010, 2011, 201...</td>\n",
       "      <td>Jeremy</td>\n",
       "      <td>rpOyqD_893cqmDAtJLbdog</td>\n",
       "      <td>11093</td>\n",
       "      <td>38</td>\n",
       "      <td>7681</td>\n",
       "      <td>330</td>\n",
       "      <td>580</td>\n",
       "      <td>14199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2004-10</td>\n",
       "      <td>89</td>\n",
       "      <td>442</td>\n",
       "      <td>['18kPq7GPye-YQ3LyKyAZPw', 'rpOyqD_893cqmDAtJL...</td>\n",
       "      <td>23</td>\n",
       "      <td>24</td>\n",
       "      <td>100</td>\n",
       "      <td>83</td>\n",
       "      <td>user</td>\n",
       "      <td>101</td>\n",
       "      <td>145</td>\n",
       "      <td>9</td>\n",
       "      <td>3.60</td>\n",
       "      <td>19</td>\n",
       "      <td>[2005, 2006, 2007, 2008, 2009, 2010, 2011, 201...</td>\n",
       "      <td>Michael</td>\n",
       "      <td>4U9kSBLuBDU391x6bxU-YA</td>\n",
       "      <td>732</td>\n",
       "      <td>4</td>\n",
       "      <td>908</td>\n",
       "      <td>24</td>\n",
       "      <td>120</td>\n",
       "      <td>1483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11</td>\n",
       "      <td>['18kPq7GPye-YQ3LyKyAZPw', 'rpOyqD_893cqmDAtJL...</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>user</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[]</td>\n",
       "      <td>Ken</td>\n",
       "      <td>fHtTaujcyKvXglE33Z5yIw</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2004-10</td>\n",
       "      <td>2</td>\n",
       "      <td>66</td>\n",
       "      <td>['rpOyqD_893cqmDAtJLbdog', 'HDQixQ-WZEV0LVPJlI...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>user</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.80</td>\n",
       "      <td>1</td>\n",
       "      <td>[2005]</td>\n",
       "      <td>Katherine</td>\n",
       "      <td>SIBCL7HBkrP4llolm4SC2A</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  yelping_since  compliments.plain  review_count  \\\n",
       "0       2004-10                 25           108   \n",
       "1       2004-10                959          1274   \n",
       "2       2004-10                 89           442   \n",
       "3       2004-10                NaN            11   \n",
       "4       2004-10                  2            66   \n",
       "\n",
       "                                             friends  compliments.cute  \\\n",
       "0  ['rpOyqD_893cqmDAtJLbdog', '4U9kSBLuBDU391x6bx...                15   \n",
       "1  ['18kPq7GPye-YQ3LyKyAZPw', '4U9kSBLuBDU391x6bx...               206   \n",
       "2  ['18kPq7GPye-YQ3LyKyAZPw', 'rpOyqD_893cqmDAtJL...                23   \n",
       "3  ['18kPq7GPye-YQ3LyKyAZPw', 'rpOyqD_893cqmDAtJL...                 2   \n",
       "4  ['rpOyqD_893cqmDAtJLbdog', 'HDQixQ-WZEV0LVPJlI...                 2   \n",
       "\n",
       "   compliments.writer  fans  compliments.note  type  compliments.hot  \\\n",
       "0                   9    70                20  user               48   \n",
       "1                 327  1179               611  user             1094   \n",
       "2                  24   100                83  user              101   \n",
       "3                 NaN     2               NaN  user              NaN   \n",
       "4                   2     4                 1  user                1   \n",
       "\n",
       "   compliments.cool  compliments.profile  average_stars  compliments.more  \\\n",
       "0                78                    8           4.14                 3   \n",
       "1              1642                  116           3.66               134   \n",
       "2               145                    9           3.60                19   \n",
       "3               NaN                  NaN           4.64               NaN   \n",
       "4                 1                  NaN           3.80                 1   \n",
       "\n",
       "                                               elite       name  \\\n",
       "0                                       [2005, 2006]     Russel   \n",
       "1  [2005, 2006, 2007, 2008, 2009, 2010, 2011, 201...     Jeremy   \n",
       "2  [2005, 2006, 2007, 2008, 2009, 2010, 2011, 201...    Michael   \n",
       "3                                                 []        Ken   \n",
       "4                                             [2005]  Katherine   \n",
       "\n",
       "                  user_id  votes.cool  compliments.list  votes.funny  \\\n",
       "0  18kPq7GPye-YQ3LyKyAZPw         245               NaN          167   \n",
       "1  rpOyqD_893cqmDAtJLbdog       11093                38         7681   \n",
       "2  4U9kSBLuBDU391x6bxU-YA         732                 4          908   \n",
       "3  fHtTaujcyKvXglE33Z5yIw           5               NaN            1   \n",
       "4  SIBCL7HBkrP4llolm4SC2A          13               NaN           11   \n",
       "\n",
       "   compliments.photos  compliments.funny  votes.useful  \n",
       "0                  14                 11           280  \n",
       "1                 330                580         14199  \n",
       "2                  24                120          1483  \n",
       "3                 NaN                NaN            11  \n",
       "4                 NaN                NaN            34  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_csv_file = os.path.join(yelp_utils.YELP_DATA_CSV_DIR, 'yelp_academic_dataset_user.csv')\n",
    "df_user = pd.read_csv(user_csv_file, encoding='utf-8', engine='c', low_memory=False)\n",
    "getDfInfo(df_user)\n",
    "df_user.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DATA CLEANING"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Remove NAs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def renameRemoveNACols(df, string):\n",
    "    '''\n",
    "    Function to remove columns from dataframe which contain NA value and\n",
    "    rename columns with string appended at beginning of column name.\n",
    "    Input: \n",
    "        df: Pandas data frame\n",
    "        string: The string that has to be appended at beginning of all column name\n",
    "    Output: Pandas data frame \n",
    "    '''\n",
    "    df = df.dropna(axis='columns', how='any')\n",
    "    new_columns = df.columns.values\n",
    "    new_columns = string + new_columns \n",
    "    df.columns = new_columns\n",
    "    return(df)\n",
    "\n",
    "def removeNACols(df):\n",
    "    '''\n",
    "    Function to remove columns which contain NA value.\n",
    "    Input: \n",
    "        df: Pandas data frame\n",
    "    Output: Pandas data frame     \n",
    "    '''\n",
    "    df = df.dropna(axis='columns', how='any')\n",
    "    return(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_business = renameRemoveNACols(df_business, 'business_')\n",
    "df_business.rename(columns={'business_business_id': 'business_id'}, inplace=True)\n",
    "df_review = renameRemoveNACols(df_review, 'review_')\n",
    "df_review.rename(columns={'review_review_id': 'review_id', 'review_user_id':'user_id', 'review_business_id' :'business_id'}, \n",
    "                 inplace=True)\n",
    "df_user = renameRemoveNACols(df_user, 'user_')\n",
    "df_user.rename(columns={'user_user_id': 'user_id'}, inplace=True)\n",
    "\n",
    "#                                      left_on='business_id', right_on='review_business_id')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "*****SHAPE********\n",
      "(2225213, 20)\n",
      "*****NULL PERCENTAGE*********\n",
      "business_id               0\n",
      "business_categories       0\n",
      "business_name             0\n",
      "business_review_count     0\n",
      "business_full_address     0\n",
      "business_open             0\n",
      "business_stars            0\n",
      "business_type             0\n",
      "business_latitude         0\n",
      "business_longitude        0\n",
      "business_neighborhoods    0\n",
      "user_id                   0\n",
      "review_id                 0\n",
      "review_text               0\n",
      "review_votes.cool         0\n",
      "review_votes.funny        0\n",
      "review_stars              0\n",
      "review_date               0\n",
      "review_type               0\n",
      "review_votes.useful       0\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "df_merged_business_review = pd.merge(df_business, df_review, how ='inner', on='business_id')\n",
    "getDfInfo(df_merged_business_review)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "*****SHAPE********\n",
      "(2225213, 30)\n",
      "*****NULL PERCENTAGE*********\n",
      "business_id               0\n",
      "business_categories       0\n",
      "business_name             0\n",
      "business_review_count     0\n",
      "business_full_address     0\n",
      "business_open             0\n",
      "business_stars            0\n",
      "business_type             0\n",
      "business_latitude         0\n",
      "business_longitude        0\n",
      "business_neighborhoods    0\n",
      "user_id                   0\n",
      "review_id                 0\n",
      "review_text               0\n",
      "review_votes.cool         0\n",
      "review_votes.funny        0\n",
      "review_stars              0\n",
      "review_date               0\n",
      "review_type               0\n",
      "review_votes.useful       0\n",
      "user_yelping_since        0\n",
      "user_review_count         0\n",
      "user_friends              0\n",
      "user_fans                 0\n",
      "user_type                 0\n",
      "user_average_stars        0\n",
      "user_elite                0\n",
      "user_votes.cool           0\n",
      "user_votes.funny          0\n",
      "user_votes.useful         0\n",
      "dtype: float64\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>business_id</th>\n",
       "      <th>business_categories</th>\n",
       "      <th>business_name</th>\n",
       "      <th>business_review_count</th>\n",
       "      <th>business_full_address</th>\n",
       "      <th>business_open</th>\n",
       "      <th>business_stars</th>\n",
       "      <th>business_type</th>\n",
       "      <th>business_latitude</th>\n",
       "      <th>business_longitude</th>\n",
       "      <th>business_neighborhoods</th>\n",
       "      <th>user_id</th>\n",
       "      <th>review_id</th>\n",
       "      <th>review_text</th>\n",
       "      <th>review_votes.cool</th>\n",
       "      <th>review_votes.funny</th>\n",
       "      <th>review_stars</th>\n",
       "      <th>review_date</th>\n",
       "      <th>review_type</th>\n",
       "      <th>review_votes.useful</th>\n",
       "      <th>user_yelping_since</th>\n",
       "      <th>user_review_count</th>\n",
       "      <th>user_friends</th>\n",
       "      <th>user_fans</th>\n",
       "      <th>user_type</th>\n",
       "      <th>user_average_stars</th>\n",
       "      <th>user_elite</th>\n",
       "      <th>user_votes.cool</th>\n",
       "      <th>user_votes.funny</th>\n",
       "      <th>user_votes.useful</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5UmKMjUEUNdYWqANhGckJw</td>\n",
       "      <td>['Fast Food', 'Restaurants']</td>\n",
       "      <td>Mr Hoagie</td>\n",
       "      <td>4</td>\n",
       "      <td>4734 Lebanon Church Rd\\nDravosburg, PA 15034</td>\n",
       "      <td>True</td>\n",
       "      <td>4.5</td>\n",
       "      <td>business</td>\n",
       "      <td>40.354327</td>\n",
       "      <td>-79.900706</td>\n",
       "      <td>[]</td>\n",
       "      <td>PUFPaY9KxDAcGqfsorJp3Q</td>\n",
       "      <td>Ya85v4eqdd6k9Od8HbQjyA</td>\n",
       "      <td>Mr Hoagie is an institution. Walking in, it do...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2012-08-01</td>\n",
       "      <td>review</td>\n",
       "      <td>0</td>\n",
       "      <td>2009-05</td>\n",
       "      <td>60</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>user</td>\n",
       "      <td>3.08</td>\n",
       "      <td>[]</td>\n",
       "      <td>12</td>\n",
       "      <td>25</td>\n",
       "      <td>166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WaHXyBwljbKNPmmJZn5j8Q</td>\n",
       "      <td>['Food', 'Grocery']</td>\n",
       "      <td>Shop 'n Save</td>\n",
       "      <td>3</td>\n",
       "      <td>1886 Homeville Rd\\nWest Mifflin, PA 15122</td>\n",
       "      <td>True</td>\n",
       "      <td>3.0</td>\n",
       "      <td>business</td>\n",
       "      <td>40.373677</td>\n",
       "      <td>-79.873734</td>\n",
       "      <td>[]</td>\n",
       "      <td>PUFPaY9KxDAcGqfsorJp3Q</td>\n",
       "      <td>58tIa8PfEnUnPsIV_BUS3w</td>\n",
       "      <td>It is now a shop and save.</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2012-08-01</td>\n",
       "      <td>review</td>\n",
       "      <td>0</td>\n",
       "      <td>2009-05</td>\n",
       "      <td>60</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>user</td>\n",
       "      <td>3.08</td>\n",
       "      <td>[]</td>\n",
       "      <td>12</td>\n",
       "      <td>25</td>\n",
       "      <td>166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>u22QDAON1kk0wjoTliyftw</td>\n",
       "      <td>['Pizza', 'Restaurants']</td>\n",
       "      <td>Latina Pizza</td>\n",
       "      <td>3</td>\n",
       "      <td>4426 Kennywood Blvd\\nWest Mifflin, PA 15122</td>\n",
       "      <td>True</td>\n",
       "      <td>4.0</td>\n",
       "      <td>business</td>\n",
       "      <td>40.389192</td>\n",
       "      <td>-79.868305</td>\n",
       "      <td>[]</td>\n",
       "      <td>PUFPaY9KxDAcGqfsorJp3Q</td>\n",
       "      <td>Vn4Uxe0sLHDSAllDa2iWDw</td>\n",
       "      <td>I have to admit, the first time I hate here, I...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2012-08-05</td>\n",
       "      <td>review</td>\n",
       "      <td>4</td>\n",
       "      <td>2009-05</td>\n",
       "      <td>60</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>user</td>\n",
       "      <td>3.08</td>\n",
       "      <td>[]</td>\n",
       "      <td>12</td>\n",
       "      <td>25</td>\n",
       "      <td>166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4ykgzzzGEWjMD5lwk1-l9A</td>\n",
       "      <td>['Sporting Goods', 'Bikes', 'Shopping']</td>\n",
       "      <td>Big Bang Bicycles</td>\n",
       "      <td>16</td>\n",
       "      <td>347 Lebanon Rd\\nWest Mifflin, PA 15122</td>\n",
       "      <td>True</td>\n",
       "      <td>4.5</td>\n",
       "      <td>business</td>\n",
       "      <td>40.371539</td>\n",
       "      <td>-79.926492</td>\n",
       "      <td>[]</td>\n",
       "      <td>PUFPaY9KxDAcGqfsorJp3Q</td>\n",
       "      <td>iQdqWF_bgU61BZVcU4iHDQ</td>\n",
       "      <td>I've had mixed results here. I would definitel...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2009-05-20</td>\n",
       "      <td>review</td>\n",
       "      <td>0</td>\n",
       "      <td>2009-05</td>\n",
       "      <td>60</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>user</td>\n",
       "      <td>3.08</td>\n",
       "      <td>[]</td>\n",
       "      <td>12</td>\n",
       "      <td>25</td>\n",
       "      <td>166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2PfavOTufsPCRdYm-bFcpw</td>\n",
       "      <td>['Food', 'Ice Cream &amp; Frozen Yogurt', 'Desserts']</td>\n",
       "      <td>Page Dairy Mart</td>\n",
       "      <td>74</td>\n",
       "      <td>4600 E Carson St\\nSouth Side\\nPittsburgh, PA 1...</td>\n",
       "      <td>True</td>\n",
       "      <td>4.5</td>\n",
       "      <td>business</td>\n",
       "      <td>40.411509</td>\n",
       "      <td>-79.955886</td>\n",
       "      <td>['South Side']</td>\n",
       "      <td>PUFPaY9KxDAcGqfsorJp3Q</td>\n",
       "      <td>mINMFt512EchGlQ-CAKd0g</td>\n",
       "      <td>This place is a great Ice Cream Shop. My Favor...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2012-08-01</td>\n",
       "      <td>review</td>\n",
       "      <td>0</td>\n",
       "      <td>2009-05</td>\n",
       "      <td>60</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>user</td>\n",
       "      <td>3.08</td>\n",
       "      <td>[]</td>\n",
       "      <td>12</td>\n",
       "      <td>25</td>\n",
       "      <td>166</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              business_id                                business_categories  \\\n",
       "0  5UmKMjUEUNdYWqANhGckJw                       ['Fast Food', 'Restaurants']   \n",
       "1  WaHXyBwljbKNPmmJZn5j8Q                                ['Food', 'Grocery']   \n",
       "2  u22QDAON1kk0wjoTliyftw                           ['Pizza', 'Restaurants']   \n",
       "3  4ykgzzzGEWjMD5lwk1-l9A            ['Sporting Goods', 'Bikes', 'Shopping']   \n",
       "4  2PfavOTufsPCRdYm-bFcpw  ['Food', 'Ice Cream & Frozen Yogurt', 'Desserts']   \n",
       "\n",
       "       business_name  business_review_count  \\\n",
       "0          Mr Hoagie                      4   \n",
       "1       Shop 'n Save                      3   \n",
       "2       Latina Pizza                      3   \n",
       "3  Big Bang Bicycles                     16   \n",
       "4    Page Dairy Mart                     74   \n",
       "\n",
       "                               business_full_address business_open  \\\n",
       "0       4734 Lebanon Church Rd\\nDravosburg, PA 15034          True   \n",
       "1          1886 Homeville Rd\\nWest Mifflin, PA 15122          True   \n",
       "2        4426 Kennywood Blvd\\nWest Mifflin, PA 15122          True   \n",
       "3             347 Lebanon Rd\\nWest Mifflin, PA 15122          True   \n",
       "4  4600 E Carson St\\nSouth Side\\nPittsburgh, PA 1...          True   \n",
       "\n",
       "   business_stars business_type  business_latitude  business_longitude  \\\n",
       "0             4.5      business          40.354327          -79.900706   \n",
       "1             3.0      business          40.373677          -79.873734   \n",
       "2             4.0      business          40.389192          -79.868305   \n",
       "3             4.5      business          40.371539          -79.926492   \n",
       "4             4.5      business          40.411509          -79.955886   \n",
       "\n",
       "  business_neighborhoods                 user_id               review_id  \\\n",
       "0                     []  PUFPaY9KxDAcGqfsorJp3Q  Ya85v4eqdd6k9Od8HbQjyA   \n",
       "1                     []  PUFPaY9KxDAcGqfsorJp3Q  58tIa8PfEnUnPsIV_BUS3w   \n",
       "2                     []  PUFPaY9KxDAcGqfsorJp3Q  Vn4Uxe0sLHDSAllDa2iWDw   \n",
       "3                     []  PUFPaY9KxDAcGqfsorJp3Q  iQdqWF_bgU61BZVcU4iHDQ   \n",
       "4         ['South Side']  PUFPaY9KxDAcGqfsorJp3Q  mINMFt512EchGlQ-CAKd0g   \n",
       "\n",
       "                                         review_text  review_votes.cool  \\\n",
       "0  Mr Hoagie is an institution. Walking in, it do...                  0   \n",
       "1                         It is now a shop and save.                  0   \n",
       "2  I have to admit, the first time I hate here, I...                  0   \n",
       "3  I've had mixed results here. I would definitel...                  0   \n",
       "4  This place is a great Ice Cream Shop. My Favor...                  0   \n",
       "\n",
       "   review_votes.funny  review_stars review_date review_type  \\\n",
       "0                   0             4  2012-08-01      review   \n",
       "1                   0             3  2012-08-01      review   \n",
       "2                   0             4  2012-08-05      review   \n",
       "3                   0             3  2009-05-20      review   \n",
       "4                   0             5  2012-08-01      review   \n",
       "\n",
       "   review_votes.useful user_yelping_since  user_review_count user_friends  \\\n",
       "0                    0            2009-05                 60           []   \n",
       "1                    0            2009-05                 60           []   \n",
       "2                    4            2009-05                 60           []   \n",
       "3                    0            2009-05                 60           []   \n",
       "4                    0            2009-05                 60           []   \n",
       "\n",
       "   user_fans user_type  user_average_stars user_elite  user_votes.cool  \\\n",
       "0          0      user                3.08         []               12   \n",
       "1          0      user                3.08         []               12   \n",
       "2          0      user                3.08         []               12   \n",
       "3          0      user                3.08         []               12   \n",
       "4          0      user                3.08         []               12   \n",
       "\n",
       "   user_votes.funny  user_votes.useful  \n",
       "0                25                166  \n",
       "1                25                166  \n",
       "2                25                166  \n",
       "3                25                166  \n",
       "4                25                166  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged_business_review_user = pd.merge(df_merged_business_review, df_user, how ='inner', on='user_id')\n",
    "#                                           left_index ='review_user_id', right_index='user_id')\n",
    "getDfInfo(df_merged_business_review_user)\n",
    "df_merged_business_review_user.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(set(df_merged_business_review_user['review_id'])) == df_merged_business_review_user.shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Considering reviews only in US</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# https://answers.yahoo.com/question/index?qid=20070729220301AA6Ct4s\n",
    "# +48.987386 is the northern most latitude \n",
    "# +18.005611 is the southern most latitude \n",
    "# -124.626080 is the west most longitude \n",
    "# -62.361014 is a east most longitude\n",
    "\n",
    "df_merged_business_review_user[['business_latitude', 'business_longitude', 'business_full_address']]\n",
    "condition_latitude = (df_merged_business_review_user.business_latitude < 48.987386) \\\n",
    "                    & (df_merged_business_review_user.business_latitude > 18.005611) \n",
    "condition_longitude = (df_merged_business_review_user.business_longitude > -124.626080) \\\n",
    "                    & (df_merged_business_review_user.business_longitude < -62.361014)\n",
    "df_merged_business_review_user_only_us = df_merged_business_review_user.loc[condition_latitude & condition_longitude]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2225213, 30)\n",
      "(2192840, 30)\n"
     ]
    }
   ],
   "source": [
    "print df_merged_business_review_user.shape\n",
    "print df_merged_business_review_user_only_us.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Write business-review-user file\n",
    "write_filename = os.path.join(yelp_utils.YELP_DATA_CSV_DIR, 'business_review_user.csv')\n",
    "if not os.path.isfile(write_filename):\n",
    "    df_merged_business_review_user_only_us.to_csv(write_filename, encoding='utf-8', engine='c', index=False)\n",
    "else:\n",
    "    df_merged_business_review_user_only_us = pd.read_csv(write_filename, encoding='utf-8', engine='c');\n",
    "    print(\"read file\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create a sample dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>business_id</th>\n",
       "      <th>business_categories</th>\n",
       "      <th>business_name</th>\n",
       "      <th>business_review_count</th>\n",
       "      <th>business_full_address</th>\n",
       "      <th>business_open</th>\n",
       "      <th>business_stars</th>\n",
       "      <th>business_type</th>\n",
       "      <th>business_latitude</th>\n",
       "      <th>business_longitude</th>\n",
       "      <th>business_neighborhoods</th>\n",
       "      <th>user_id</th>\n",
       "      <th>review_id</th>\n",
       "      <th>review_text</th>\n",
       "      <th>review_votes.cool</th>\n",
       "      <th>review_votes.funny</th>\n",
       "      <th>review_stars</th>\n",
       "      <th>review_date</th>\n",
       "      <th>review_type</th>\n",
       "      <th>review_votes.useful</th>\n",
       "      <th>user_yelping_since</th>\n",
       "      <th>user_review_count</th>\n",
       "      <th>user_friends</th>\n",
       "      <th>user_fans</th>\n",
       "      <th>user_type</th>\n",
       "      <th>user_average_stars</th>\n",
       "      <th>user_elite</th>\n",
       "      <th>user_votes.cool</th>\n",
       "      <th>user_votes.funny</th>\n",
       "      <th>user_votes.useful</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>902304</th>\n",
       "      <td>FoHJy_ucYarA2DkCrAVIgw</td>\n",
       "      <td>['Automotive', 'Smog Check Stations', 'Registr...</td>\n",
       "      <td>Jiffy Smog</td>\n",
       "      <td>11</td>\n",
       "      <td>3061 St Rose Pkwy\\nSoutheast\\nLas Vegas, NV 89130</td>\n",
       "      <td>True</td>\n",
       "      <td>2.5</td>\n",
       "      <td>business</td>\n",
       "      <td>35.999862</td>\n",
       "      <td>-115.123105</td>\n",
       "      <td>['Southeast', 'Anthem']</td>\n",
       "      <td>ZPnjxhKUsBDhXEOBKY_n2g</td>\n",
       "      <td>t4_5FZu9lrW0GbFvtHDVRQ</td>\n",
       "      <td>It's located in the Chevron gas station lot. Q...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2015-03-09</td>\n",
       "      <td>review</td>\n",
       "      <td>1</td>\n",
       "      <td>2011-02</td>\n",
       "      <td>349</td>\n",
       "      <td>['KtNT8biTJlbAFh-APgO7-w', 'EhcZKrQJIS226xdm_q...</td>\n",
       "      <td>31</td>\n",
       "      <td>user</td>\n",
       "      <td>3.50</td>\n",
       "      <td>[2013, 2014, 2015]</td>\n",
       "      <td>767</td>\n",
       "      <td>781</td>\n",
       "      <td>1219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1308203</th>\n",
       "      <td>FqzgT9Y-Yu7jiWdHnGW-kQ</td>\n",
       "      <td>['Bars', 'American (Traditional)', 'Pubs', 'Ni...</td>\n",
       "      <td>The Vig</td>\n",
       "      <td>522</td>\n",
       "      <td>4041 N 40th St\\nPhoenix, AZ 85018</td>\n",
       "      <td>True</td>\n",
       "      <td>4.0</td>\n",
       "      <td>business</td>\n",
       "      <td>33.494489</td>\n",
       "      <td>-111.995106</td>\n",
       "      <td>[]</td>\n",
       "      <td>D_rLArbCVIDib94qEnLqrg</td>\n",
       "      <td>uQ_8mcs2GoZxQuOQ7_1_DQ</td>\n",
       "      <td>Great outdoor patio dining area. Great happy h...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2013-12-17</td>\n",
       "      <td>review</td>\n",
       "      <td>0</td>\n",
       "      <td>2008-01</td>\n",
       "      <td>859</td>\n",
       "      <td>['8_RB3fs5Ywn_ECTmg139Gg', 'pz97SxRe1Vk-5_K6EB...</td>\n",
       "      <td>34</td>\n",
       "      <td>user</td>\n",
       "      <td>3.68</td>\n",
       "      <td>[2009, 2010, 2011, 2012, 2013, 2014, 2015]</td>\n",
       "      <td>622</td>\n",
       "      <td>463</td>\n",
       "      <td>1227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138374</th>\n",
       "      <td>n5eQnMnVVt3FfrFENYoU0g</td>\n",
       "      <td>['Latin American', 'Mexican', 'Restaurants']</td>\n",
       "      <td>Cabo Fish Taco</td>\n",
       "      <td>672</td>\n",
       "      <td>3201 N Davidson St\\nNoDa\\nCharlotte, NC 28205</td>\n",
       "      <td>True</td>\n",
       "      <td>3.5</td>\n",
       "      <td>business</td>\n",
       "      <td>35.247251</td>\n",
       "      <td>-80.805827</td>\n",
       "      <td>['NoDa']</td>\n",
       "      <td>GgO4Q6d3mnHFwjSmpOg3Vw</td>\n",
       "      <td>uSP0VvT0YcHM1cyoMN3L1w</td>\n",
       "      <td>for the record... cabo fish taco has never bee...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2015-07-05</td>\n",
       "      <td>review</td>\n",
       "      <td>2</td>\n",
       "      <td>2012-09</td>\n",
       "      <td>6</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>user</td>\n",
       "      <td>3.40</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1269747</th>\n",
       "      <td>E2XPkjbbhdNY2yXBTwScQw</td>\n",
       "      <td>['Gluten-Free', 'Sandwiches', 'Salad', 'Restau...</td>\n",
       "      <td>Muscle Maker Grill</td>\n",
       "      <td>31</td>\n",
       "      <td>8386 W Thunderbird Rd\\nSte 103\\nPeoria, AZ 85381</td>\n",
       "      <td>True</td>\n",
       "      <td>4.0</td>\n",
       "      <td>business</td>\n",
       "      <td>33.610449</td>\n",
       "      <td>-112.239434</td>\n",
       "      <td>[]</td>\n",
       "      <td>7OrWLA2qylmcC1smi9LoQw</td>\n",
       "      <td>536JzbYMBl8jKA85_s8t3w</td>\n",
       "      <td>Came in with a groupon but will definitely ret...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2015-10-03</td>\n",
       "      <td>review</td>\n",
       "      <td>1</td>\n",
       "      <td>2011-04</td>\n",
       "      <td>20</td>\n",
       "      <td>['_mdXw4vDCsCW7VqRQWxDzA', 'k8Jv-CUjlB5L27h0VA...</td>\n",
       "      <td>0</td>\n",
       "      <td>user</td>\n",
       "      <td>3.76</td>\n",
       "      <td>[]</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>867797</th>\n",
       "      <td>hfUdBRgTTPTR4s4MOqux8Q</td>\n",
       "      <td>['Arts &amp; Entertainment', 'Stadiums &amp; Arenas']</td>\n",
       "      <td>Thomas &amp; Mack Center</td>\n",
       "      <td>96</td>\n",
       "      <td>Swenson &amp; Thomas and Mack Dr\\nUniversity\\nLas ...</td>\n",
       "      <td>True</td>\n",
       "      <td>4.0</td>\n",
       "      <td>business</td>\n",
       "      <td>36.104761</td>\n",
       "      <td>-115.144418</td>\n",
       "      <td>['University']</td>\n",
       "      <td>TfLjkiUNHWksThxZOHrcqw</td>\n",
       "      <td>dRbScHqVlEpr-MRn9_vipA</td>\n",
       "      <td>I love the Mack!\\n\\nI've come to the Mack for ...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2011-07-08</td>\n",
       "      <td>review</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-07</td>\n",
       "      <td>213</td>\n",
       "      <td>['R7-OHW_M_V4lYGbIT2hetQ', 'p24ECnwNOfdQMs6SDt...</td>\n",
       "      <td>4</td>\n",
       "      <td>user</td>\n",
       "      <td>4.00</td>\n",
       "      <td>[2011, 2012, 2013, 2014]</td>\n",
       "      <td>180</td>\n",
       "      <td>207</td>\n",
       "      <td>414</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    business_id  \\\n",
       "902304   FoHJy_ucYarA2DkCrAVIgw   \n",
       "1308203  FqzgT9Y-Yu7jiWdHnGW-kQ   \n",
       "138374   n5eQnMnVVt3FfrFENYoU0g   \n",
       "1269747  E2XPkjbbhdNY2yXBTwScQw   \n",
       "867797   hfUdBRgTTPTR4s4MOqux8Q   \n",
       "\n",
       "                                       business_categories  \\\n",
       "902304   ['Automotive', 'Smog Check Stations', 'Registr...   \n",
       "1308203  ['Bars', 'American (Traditional)', 'Pubs', 'Ni...   \n",
       "138374        ['Latin American', 'Mexican', 'Restaurants']   \n",
       "1269747  ['Gluten-Free', 'Sandwiches', 'Salad', 'Restau...   \n",
       "867797       ['Arts & Entertainment', 'Stadiums & Arenas']   \n",
       "\n",
       "                business_name  business_review_count  \\\n",
       "902304             Jiffy Smog                     11   \n",
       "1308203               The Vig                    522   \n",
       "138374         Cabo Fish Taco                    672   \n",
       "1269747    Muscle Maker Grill                     31   \n",
       "867797   Thomas & Mack Center                     96   \n",
       "\n",
       "                                     business_full_address business_open  \\\n",
       "902304   3061 St Rose Pkwy\\nSoutheast\\nLas Vegas, NV 89130          True   \n",
       "1308203                  4041 N 40th St\\nPhoenix, AZ 85018          True   \n",
       "138374       3201 N Davidson St\\nNoDa\\nCharlotte, NC 28205          True   \n",
       "1269747   8386 W Thunderbird Rd\\nSte 103\\nPeoria, AZ 85381          True   \n",
       "867797   Swenson & Thomas and Mack Dr\\nUniversity\\nLas ...          True   \n",
       "\n",
       "         business_stars business_type  business_latitude  business_longitude  \\\n",
       "902304              2.5      business          35.999862         -115.123105   \n",
       "1308203             4.0      business          33.494489         -111.995106   \n",
       "138374              3.5      business          35.247251          -80.805827   \n",
       "1269747             4.0      business          33.610449         -112.239434   \n",
       "867797              4.0      business          36.104761         -115.144418   \n",
       "\n",
       "          business_neighborhoods                 user_id  \\\n",
       "902304   ['Southeast', 'Anthem']  ZPnjxhKUsBDhXEOBKY_n2g   \n",
       "1308203                       []  D_rLArbCVIDib94qEnLqrg   \n",
       "138374                  ['NoDa']  GgO4Q6d3mnHFwjSmpOg3Vw   \n",
       "1269747                       []  7OrWLA2qylmcC1smi9LoQw   \n",
       "867797            ['University']  TfLjkiUNHWksThxZOHrcqw   \n",
       "\n",
       "                      review_id  \\\n",
       "902304   t4_5FZu9lrW0GbFvtHDVRQ   \n",
       "1308203  uQ_8mcs2GoZxQuOQ7_1_DQ   \n",
       "138374   uSP0VvT0YcHM1cyoMN3L1w   \n",
       "1269747  536JzbYMBl8jKA85_s8t3w   \n",
       "867797   dRbScHqVlEpr-MRn9_vipA   \n",
       "\n",
       "                                               review_text  review_votes.cool  \\\n",
       "902304   It's located in the Chevron gas station lot. Q...                  1   \n",
       "1308203  Great outdoor patio dining area. Great happy h...                  0   \n",
       "138374   for the record... cabo fish taco has never bee...                  0   \n",
       "1269747  Came in with a groupon but will definitely ret...                  0   \n",
       "867797   I love the Mack!\\n\\nI've come to the Mack for ...                  1   \n",
       "\n",
       "         review_votes.funny  review_stars review_date review_type  \\\n",
       "902304                    0             4  2015-03-09      review   \n",
       "1308203                   0             4  2013-12-17      review   \n",
       "138374                    0             5  2015-07-05      review   \n",
       "1269747                   0             5  2015-10-03      review   \n",
       "867797                    0             4  2011-07-08      review   \n",
       "\n",
       "         review_votes.useful user_yelping_since  user_review_count  \\\n",
       "902304                     1            2011-02                349   \n",
       "1308203                    0            2008-01                859   \n",
       "138374                     2            2012-09                  6   \n",
       "1269747                    1            2011-04                 20   \n",
       "867797                     0            2010-07                213   \n",
       "\n",
       "                                              user_friends  user_fans  \\\n",
       "902304   ['KtNT8biTJlbAFh-APgO7-w', 'EhcZKrQJIS226xdm_q...         31   \n",
       "1308203  ['8_RB3fs5Ywn_ECTmg139Gg', 'pz97SxRe1Vk-5_K6EB...         34   \n",
       "138374                                                  []          0   \n",
       "1269747  ['_mdXw4vDCsCW7VqRQWxDzA', 'k8Jv-CUjlB5L27h0VA...          0   \n",
       "867797   ['R7-OHW_M_V4lYGbIT2hetQ', 'p24ECnwNOfdQMs6SDt...          4   \n",
       "\n",
       "        user_type  user_average_stars  \\\n",
       "902304       user                3.50   \n",
       "1308203      user                3.68   \n",
       "138374       user                3.40   \n",
       "1269747      user                3.76   \n",
       "867797       user                4.00   \n",
       "\n",
       "                                         user_elite  user_votes.cool  \\\n",
       "902304                           [2013, 2014, 2015]              767   \n",
       "1308203  [2009, 2010, 2011, 2012, 2013, 2014, 2015]              622   \n",
       "138374                                           []                0   \n",
       "1269747                                          []                7   \n",
       "867797                     [2011, 2012, 2013, 2014]              180   \n",
       "\n",
       "         user_votes.funny  user_votes.useful  \n",
       "902304                781               1219  \n",
       "1308203               463               1227  \n",
       "138374                  0                  3  \n",
       "1269747                 6                 24  \n",
       "867797                207                414  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample_file = os.path.join(yelp_utils.YELP_DATA_CSV_DIR, 'business_review_user'+ yelp_utils.data_subset +'.csv')\n",
    "df_sample = df_merged_business_review_user_only_us.sample(frac = yelp_utils.data_frac, replace=False, random_state=yelp_utils.SEED_VAL)\n",
    "if not os.path.isfile(sample_file):\n",
    "    df_sample.to_csv(sample_file, encoding='utf-8', engine='c', index=False)\n",
    "df_sample.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Variable                                                                                                Type                    Data/Info\n",
      "-----------------------------------------------------------------------------------------------------------------------------------------\n",
      "CountVectorizer                                                                                         type                    <class 'sklearn.feature_e<...>on.text.CountVectorizer'>\n",
      "NLTK_STOPWORDS                                                                                          set                     set([u'all', u'just', u'b<...>he', u'having', u'once'])\n",
      "PorterStemmer                                                                                           type                    <class 'nltk.stem.porter.PorterStemmer'>\n",
      "SEED_VAL                                                                                                int                     200\n",
      "TfidfVectorizer                                                                                         type                    <class 'sklearn.feature_e<...>on.text.TfidfVectorizer'>\n",
      "WORK_DIR                                                                                                str                     D:\\_Active_Projects\\yelp\\yelp\n",
      "YELP_DATA_CSV_DIR                                                                                       str                     D:\\_Active_Projects\\yelp\\yelp\\data\\csv\n",
      "YELP_DATA_RAW_DIR                                                                                       str                     D:\\_Active_Projects\\yelp\\yelp\\data\\raw\n",
      "YELP_DATA_SPARSE_MATRIX_DIR                                                                             str                     D:\\_Active_Projects\\yelp\\yelp\\data\\sparse_matrix\n",
      "YELP_DATA_WORD_2_VEC_MODEL_DIR                                                                          str                     D:\\_Active_Projects\\yelp\\yelp\\data\\word2vec_model\n",
      "business_csv_file                                                                                       str                     D:\\_Active_Projects\\yelp\\<...>emic_dataset_business.csv\n",
      "condition_latitude                                                                                      Series                  0          True\\n1       <...>ess_latitude, dtype: bool\n",
      "condition_longitude                                                                                     Series                  0          True\\n1       <...>ss_longitude, dtype: bool\n",
      "csr_matrix                                                                                              type                    <class 'scipy.sparse.csr.csr_matrix'>\n",
      "csv_file                                                                                                str                     D:\\_Active_Projects\\yelp\\<...>academic_dataset_user.csv\n",
      "data_frac                                                                                               float                   0.001\n",
      "data_subset                                                                                             str                     _0_0_1Percent\n",
      "df_business                                                                                             DataFrame                                 busines<...>[77445 rows x 11 columns]\n",
      "df_merged_business_review                                                                               DataFrame                                   busin<...>225213 rows x 20 columns]\n",
      "df_merged_business_review_user                                                                          DataFrame                                   busin<...>225213 rows x 30 columns]\n",
      "df_merged_business_review_user_only_us                                                                  DataFrame                                   busin<...>192840 rows x 30 columns]\n",
      "df_review                                                                                               DataFrame                                       u<...>225213 rows x 10 columns]\n",
      "df_sample                                                                                               DataFrame                                   busin<...>n[2193 rows x 30 columns]\n",
      "df_user                                                                                                 DataFrame                      user_yelping_since<...>552339 rows x 11 columns]\n",
      "filter_out_more_stopwords                                                                               function                <function filter_out_more<...>ds at 0x000000001A55E128>\n",
      "getDfInfo                                                                                               function                <function getDfInfo at 0x000000001A54BD68>\n",
      "glob                                                                                                    module                  <module 'glob' from 'C:\\Anaconda\\lib\\glob.pyc'>\n",
      "json_file                                                                                               str                     D:\\_Active_Projects\\yelp\\<...>cademic_dataset_user.json\n",
      "load_sparse_csr                                                                                         function                <function load_sparse_csr at 0x000000001A54BE48>\n",
      "lowercase_remove_punctuation                                                                            function                <function lowercase_remov<...>on at 0x000000001A54BF28>\n",
      "lowercase_remove_punctuation_and_numbers_and_tokenize_and_filter_more_stopwords_and_stem_and_restring   function                <function lowercase_remov<...>ng at 0x000000001A55E278>\n",
      "make_sure_path_exists                                                                                   function                <function make_sure_path_<...>ts at 0x000000001A54BCF8>\n",
      "nltk                                                                                                    module                  <module 'nltk' from 'C:\\A<...>kages\\nltk\\__init__.pyc'>\n",
      "np                                                                                                      module                  <module 'numpy' from 'C:\\<...>ages\\numpy\\__init__.pyc'>\n",
      "os                                                                                                      module                  <module 'os' from 'C:\\Anaconda\\lib\\os.pyc'>\n",
      "pd                                                                                                      module                  <module 'pandas' from 'C:<...>ges\\pandas\\__init__.pyc'>\n",
      "removeNACols                                                                                            function                <function removeNACols at 0x00000000270DEB38>\n",
      "remove_number_map                                                                                       dict                    n=10\n",
      "remove_numbers_in_string                                                                                function                <function remove_numbers_<...>ng at 0x000000001A54BEB8>\n",
      "remove_punctuation_map                                                                                  dict                    n=32\n",
      "remove_stopwords                                                                                        function                <function remove_stopwords at 0x000000001A55E048>\n",
      "renameRemoveNACols                                                                                      function                <function renameRemoveNAC<...>ls at 0x00000000270DEBA8>\n",
      "restring_tokens                                                                                         function                <function restring_tokens at 0x000000001A55E208>\n",
      "review_csv_file                                                                                         str                     D:\\_Active_Projects\\yelp\\<...>ademic_dataset_review.csv\n",
      "sample_file                                                                                             str                     D:\\_Active_Projects\\yelp\\<...>iew_user_0_0_1Percent.csv\n",
      "save_sparse_csr                                                                                         function                <function save_sparse_csr at 0x000000001A54BDD8>\n",
      "stem_token_list                                                                                         function                <function stem_token_list at 0x000000001A55E198>\n",
      "stopwords                                                                                               WordListCorpusReader    <WordListCorpusReader in <...>ata\\\\corpora\\\\stopwords'>\n",
      "string                                                                                                  module                  <module 'string' from 'C:<...>Anaconda\\lib\\string.pyc'>\n",
      "user_csv_file                                                                                           str                     D:\\_Active_Projects\\yelp\\<...>academic_dataset_user.csv\n",
      "word_tokenize                                                                                           function                <function word_tokenize at 0x000000001A1B59E8>\n",
      "write_filename                                                                                          str                     D:\\_Active_Projects\\yelp\\<...>\\business_review_user.csv\n",
      "yelp_utils                                                                                              module                  <module 'yelp_utils' from 'yelp_utils.py'>\n"
     ]
    }
   ],
   "source": [
    "%whos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}