02_data_wrangling.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data wrangling 1\n",
    "\n",
    "In this part, we preprocess data for EDA and predictive modeling.\n",
    "\n",
    "Specifically, we do the following:\n",
    "1. Check and clean duplicate reviews.\n",
    "2. Remove barbershops since we are interested in restaurants in this project.\n",
    "3. Remove closed restaurants.\n",
    "4. Convert user_id and business_id to integers (to save space and speed up computation)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "from IPython.display import display\n",
    "\n",
    "import matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "import seaborn as sns\n",
    "pd.set_option('display.width', 15000)\n",
    "pd.set_option('display.max_columns', 100)\n",
    "sns.set_style(\"whitegrid\", {'axes.grid' : False})\n",
    "sns.set_context('poster')\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "class id2num:\n",
    "    def __init__(self):\n",
    "        pass\n",
    "    \n",
    "    def _id2num_dict(self, df, col):\n",
    "        _num2id = df[col].values\n",
    "        _id2num = {v:n for n, v in enumerate(_num2id)}\n",
    "        return _num2id, _id2num\n",
    "    \n",
    "    def _id2num(self, df, col, i2n):\n",
    "        def transform(v):\n",
    "            return i2n[v]\n",
    "        df[col] = df[col].apply(transform)\n",
    "        return df\n",
    "    \n",
    "    def fit_transform(self, dfr, dfb, dfu):\n",
    "        bus_n2i, bus_i2n = self._id2num_dict(dfb, 'business_id') \n",
    "        user_n2i, user_i2n = self._id2num_dict(dfu, 'user_id')\n",
    "        dfb = self._id2num(dfb, 'business_id', bus_i2n)\n",
    "        dfu = self._id2num(dfu, 'user_id', user_i2n)\n",
    "        dfr = self._id2num(dfr, 'business_id', bus_i2n)\n",
    "        dfr = self._id2num(dfr, 'user_id', user_i2n)\n",
    "        return dfr, dfb, dfu"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 1min\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# Load data and convert user_id and business_id to integers\n",
    "\n",
    "raw_dir = 'raw/'\n",
    "df_business = pd.read_csv(raw_dir + 'business.csv')\n",
    "df_review = pd.read_csv(raw_dir + 'review.csv')\n",
    "df_user = pd.read_csv(raw_dir + 'user.csv')\n",
    "\n",
    "df_review, df_business, df_user = id2num().fit_transform(df_review, df_business, df_user)\n",
    "data_review = df_review[['user_id', 'business_id', 'stars']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The number of missing values in review.stars is 0.\n"
     ]
    }
   ],
   "source": [
    "print('The number of missing values in review.stars is {}.'.format(np.sum(df_review.stars.isnull())))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>business_id</th>\n",
       "      <th>stars</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1773114</th>\n",
       "      <td>98431.0</td>\n",
       "      <td>63954.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         user_id  business_id  stars\n",
       "1773114  98431.0      63954.0    2.0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check duplicates\n",
    "\n",
    "df_review.groupby(['user_id', 'business_id'], as_index=False).count()\\\n",
    "[df_review.groupby(['user_id', 'business_id'], as_index=False).count() != 1].dropna()[['user_id', 'business_id', 'stars']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>funny</th>\n",
       "      <th>user_id</th>\n",
       "      <th>review_id</th>\n",
       "      <th>text</th>\n",
       "      <th>business_id</th>\n",
       "      <th>stars</th>\n",
       "      <th>date</th>\n",
       "      <th>useful</th>\n",
       "      <th>cool</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4291340</th>\n",
       "      <td>0</td>\n",
       "      <td>98431</td>\n",
       "      <td>1l-mU-BwQsZRW7BKdHVwZw</td>\n",
       "      <td>This place continues to be a shit hole, with P...</td>\n",
       "      <td>63954</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-05-02</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4291341</th>\n",
       "      <td>0</td>\n",
       "      <td>98431</td>\n",
       "      <td>IcpPtd37iJEthZybuasS4g</td>\n",
       "      <td>My mother recently spent a good deal of time i...</td>\n",
       "      <td>63954</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-02-06</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         funny  user_id               review_id                                               text  business_id  stars        date  useful  cool\n",
       "4291340      0    98431  1l-mU-BwQsZRW7BKdHVwZw  This place continues to be a shit hole, with P...        63954      1  2017-05-02       0     0\n",
       "4291341      0    98431  IcpPtd37iJEthZybuasS4g  My mother recently spent a good deal of time i...        63954      1  2015-02-06       9     0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_review.query('user_id == 98431 & business_id == 63954')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see, there is only one case where a user rate the same restaurant more than once and the ratings are the same in this case. We can simply drop the duplicate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>funny</th>\n",
       "      <th>user_id</th>\n",
       "      <th>review_id</th>\n",
       "      <th>text</th>\n",
       "      <th>business_id</th>\n",
       "      <th>stars</th>\n",
       "      <th>date</th>\n",
       "      <th>useful</th>\n",
       "      <th>cool</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4291340</th>\n",
       "      <td>0</td>\n",
       "      <td>98431</td>\n",
       "      <td>1l-mU-BwQsZRW7BKdHVwZw</td>\n",
       "      <td>This place continues to be a shit hole, with P...</td>\n",
       "      <td>63954</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-05-02</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         funny  user_id               review_id                                               text  business_id  stars        date  useful  cool\n",
       "4291340      0    98431  1l-mU-BwQsZRW7BKdHVwZw  This place continues to be a shit hole, with P...        63954      1  2017-05-02       0     0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_review = df_review.drop(4291341)\n",
    "df_review.query('user_id == 98431 & business_id == 63954')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def remove_barbershop(df_business=df_business, df_review=df_review, df_user=df_user):\n",
    "    b_cols = [col for col in df_business.columns if 'HairSpecializesIn' in col]\n",
    "    df_business = df_business[np.all(df_business[b_cols].isnull(), axis=1)].drop(156300)\n",
    "    for col in b_cols:\n",
    "        del df_business[col]\n",
    "    df_review = pd.merge(df_business[['business_id']], df_review, how='left', on='business_id')\n",
    "    data_review = df_review[['user_id', 'business_id', 'stars']]\n",
    "    df_user = pd.merge(pd.DataFrame(data_review['user_id'].unique(), \\\n",
    "                                    columns=['user_id']), df_user, how='left', on='user_id')\n",
    "    return df_business, df_review, df_user, data_review"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 10.7 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_business, df_review, df_user, data_review = remove_barbershop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def remove_closed(df_business=df_business, df_review=df_review, df_user=df_user):\n",
    "    df_business = df_business[df_business['is_open'] == 1]\n",
    "    df_review = pd.merge(df_business[['business_id']], df_review, how='left', on='business_id')\n",
    "    df_user = pd.merge(pd.DataFrame(df_review['user_id'].unique(), \\\n",
    "                                    columns=['user_id']), df_user, how='left', on='user_id')\n",
    "    df_review, df_business, df_user = id2num().fit_transform(df_review, df_business, df_user)\n",
    "    data_review = df_review[['user_id', 'business_id', 'stars']]\n",
    "    return df_business, df_review, df_user, data_review"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Weidong\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:13: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  del sys.path[0]\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 13.5 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_business, df_review, df_user, data_review = remove_closed()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 34.3 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# Save data\n",
    "data_dir = 'data/Full/'\n",
    "df_review.to_pickle(data_dir + 'review.pkl')\n",
    "df_business.to_pickle(data_dir + 'business.pkl')\n",
    "df_user.to_pickle(data_dir + 'user.pkl')\n",
    "data_review.to_pickle(data_dir + 'data_review.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>city</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>381</th>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>20434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>632</th>\n",
       "      <td>Phoenix</td>\n",
       "      <td>13152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>880</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>12118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>Charlotte</td>\n",
       "      <td>6347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>780</th>\n",
       "      <td>Scottsdale</td>\n",
       "      <td>6037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651</th>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>4745</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>Mesa</td>\n",
       "      <td>4470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>508</th>\n",
       "      <td>Montréal</td>\n",
       "      <td>4308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303</th>\n",
       "      <td>Henderson</td>\n",
       "      <td>3465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>872</th>\n",
       "      <td>Tempe</td>\n",
       "      <td>3156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>Chandler</td>\n",
       "      <td>3008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>Edinburgh</td>\n",
       "      <td>2925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>Cleveland</td>\n",
       "      <td>2500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267</th>\n",
       "      <td>Gilbert</td>\n",
       "      <td>2442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>273</th>\n",
       "      <td>Glendale</td>\n",
       "      <td>2405</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>429</th>\n",
       "      <td>Madison</td>\n",
       "      <td>2370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>490</th>\n",
       "      <td>Mississauga</td>\n",
       "      <td>2049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>852</th>\n",
       "      <td>Stuttgart</td>\n",
       "      <td>1718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>624</th>\n",
       "      <td>Peoria</td>\n",
       "      <td>1341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>437</th>\n",
       "      <td>Markham</td>\n",
       "      <td>1118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>569</th>\n",
       "      <td>North Las Vegas</td>\n",
       "      <td>1093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>Champaign</td>\n",
       "      <td>878</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>866</th>\n",
       "      <td>Surprise</td>\n",
       "      <td>821</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>580</th>\n",
       "      <td>North York</td>\n",
       "      <td>798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>766</th>\n",
       "      <td>Scarborough</td>\n",
       "      <td>747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>Concord</td>\n",
       "      <td>645</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>279</th>\n",
       "      <td>Goodyear</td>\n",
       "      <td>619</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>Brampton</td>\n",
       "      <td>612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>683</th>\n",
       "      <td>Richmond Hill</td>\n",
       "      <td>598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>904</th>\n",
       "      <td>Vaughan</td>\n",
       "      <td>560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>524</th>\n",
       "      <td>Mount Royal</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>547</th>\n",
       "      <td>N. Ridgeville</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>546</th>\n",
       "      <td>N. Randall</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>545</th>\n",
       "      <td>N. Olmsted</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>544</th>\n",
       "      <td>N. OLMSTED</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>542</th>\n",
       "      <td>N W Las Vegas</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>539</th>\n",
       "      <td>N E Las Vegas</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>538</th>\n",
       "      <td>Münster</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>535</th>\n",
       "      <td>Murrysville (Monroeville)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>532</th>\n",
       "      <td>Municipality of Murrysville</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>530</th>\n",
       "      <td>Mt. Oliver Boro</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>528</th>\n",
       "      <td>Mt. Horeb</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>526</th>\n",
       "      <td>Mt Albert</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>525</th>\n",
       "      <td>Mount Washington</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518</th>\n",
       "      <td>Morriston</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>Meza</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>512</th>\n",
       "      <td>Mooers</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>511</th>\n",
       "      <td>Montréal-Ouest</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>510</th>\n",
       "      <td>Montréal-Nord</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>509</th>\n",
       "      <td>Montréal (Québec)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>507</th>\n",
       "      <td>Montrose</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502</th>\n",
       "      <td>Montral</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497</th>\n",
       "      <td>Monroeville - Pit.</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>495</th>\n",
       "      <td>Monreoville</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>491</th>\n",
       "      <td>Mississuaga</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AGINCOURT</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>489</th>\n",
       "      <td>Mississauaga</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>485</th>\n",
       "      <td>Mint  Hill</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>479</th>\n",
       "      <td>Middleburg Hts.</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>979</th>\n",
       "      <td>Île-des-Soeurs</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>980 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            city   name\n",
       "381                    Las Vegas  20434\n",
       "632                      Phoenix  13152\n",
       "880                      Toronto  12118\n",
       "128                    Charlotte   6347\n",
       "780                   Scottsdale   6037\n",
       "651                   Pittsburgh   4745\n",
       "473                         Mesa   4470\n",
       "508                     Montréal   4308\n",
       "303                    Henderson   3465\n",
       "872                        Tempe   3156\n",
       "123                     Chandler   3008\n",
       "215                    Edinburgh   2925\n",
       "144                    Cleveland   2500\n",
       "267                      Gilbert   2442\n",
       "273                     Glendale   2405\n",
       "429                      Madison   2370\n",
       "490                  Mississauga   2049\n",
       "852                    Stuttgart   1718\n",
       "624                       Peoria   1341\n",
       "437                      Markham   1118\n",
       "569              North Las Vegas   1093\n",
       "120                    Champaign    878\n",
       "866                     Surprise    821\n",
       "580                   North York    798\n",
       "766                  Scarborough    747\n",
       "150                      Concord    645\n",
       "279                     Goodyear    619\n",
       "74                      Brampton    612\n",
       "683                Richmond Hill    598\n",
       "904                      Vaughan    560\n",
       "..                           ...    ...\n",
       "524                  Mount Royal      1\n",
       "547                N. Ridgeville      1\n",
       "546                   N. Randall      1\n",
       "545                   N. Olmsted      1\n",
       "544                   N. OLMSTED      1\n",
       "542                N W Las Vegas      1\n",
       "539                N E Las Vegas      1\n",
       "538                      Münster      1\n",
       "535    Murrysville (Monroeville)      1\n",
       "532  Municipality of Murrysville      1\n",
       "530              Mt. Oliver Boro      1\n",
       "528                    Mt. Horeb      1\n",
       "526                    Mt Albert      1\n",
       "525             Mount Washington      1\n",
       "518                    Morriston      1\n",
       "476                         Meza      1\n",
       "512                       Mooers      1\n",
       "511               Montréal-Ouest      1\n",
       "510                Montréal-Nord      1\n",
       "509            Montréal (Québec)      1\n",
       "507                     Montrose      1\n",
       "502                      Montral      1\n",
       "497           Monroeville - Pit.      1\n",
       "495                  Monreoville      1\n",
       "491                  Mississuaga      1\n",
       "1                      AGINCOURT      1\n",
       "489                 Mississauaga      1\n",
       "485                   Mint  Hill      1\n",
       "479              Middleburg Hts.      1\n",
       "979               Île-des-Soeurs      1\n",
       "\n",
       "[980 rows x 2 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_business[['city', 'name']].groupby('city', as_index=False).count().sort_values(by='name', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_city(city, df_business=df_business, df_review=df_review, df_user=df_user):\n",
    "    dfb = df_business[df_business['city'] == city]\n",
    "    dfr = pd.merge(dfb[['business_id']], df_review, how='left', on='business_id')\n",
    "    datar = dfr[['user_id', 'business_id', 'stars']]\n",
    "    dfu = pd.merge(pd.DataFrame(datar['user_id'].unique(), columns=['user_id']), df_user, how='left', on='user_id')\n",
    "    return dfb, dfr, dfu, datar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1280896, 9)\n",
      "Wall time: 15 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "dfb, dfr, dfu, datar = get_city('Las Vegas')\n",
    "print(dfr.shape)\n",
    "data_dir = 'data/Las_Vegas/'\n",
    "dfr.to_pickle(data_dir + 'review.pkl')\n",
    "dfb.to_pickle(data_dir + 'business.pkl')\n",
    "dfu.to_pickle(data_dir + 'user.pkl')\n",
    "datar.to_pickle(data_dir + 'data_review.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(20571, 9)\n",
      "Wall time: 1.3 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "dfb, dfr, dfu, datar = get_city('Champaign')\n",
    "print(dfr.shape)\n",
    "data_dir = 'data/Champaign/'\n",
    "dfr.to_pickle(data_dir + 'review.pkl')\n",
    "dfb.to_pickle(data_dir + 'business.pkl')\n",
    "dfu.to_pickle(data_dir + 'user.pkl')\n",
    "datar.to_pickle(data_dir + 'data_review.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(75932, 9)\n",
      "Wall time: 1.97 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "dfb, dfr, dfu, datar = get_city('Cleveland')\n",
    "print(dfr.shape)\n",
    "data_dir = 'data/Cleveland/'\n",
    "dfr.to_pickle(data_dir + 'review.pkl')\n",
    "dfb.to_pickle(data_dir + 'business.pkl')\n",
    "dfu.to_pickle(data_dir + 'user.pkl')\n",
    "datar.to_pickle(data_dir + 'data_review.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(143682, 9)\n",
      "Wall time: 3.12 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "dfb, dfr, dfu, datar = get_city('Pittsburgh')\n",
    "print(dfr.shape)\n",
    "data_dir = 'data/Pittsburgh/'\n",
    "dfr.to_pickle(data_dir + 'review.pkl')\n",
    "dfb.to_pickle(data_dir + 'business.pkl')\n",
    "dfu.to_pickle(data_dir + 'user.pkl')\n",
    "datar.to_pickle(data_dir + 'data_review.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(331407, 9)\n",
      "Wall time: 4.34 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "dfb, dfr, dfu, datar = get_city('Toronto')\n",
    "print(dfr.shape)\n",
    "data_dir = 'data/Toronto/'\n",
    "dfr.to_pickle(data_dir + 'review.pkl')\n",
    "dfb.to_pickle(data_dir + 'business.pkl')\n",
    "dfu.to_pickle(data_dir + 'user.pkl')\n",
    "datar.to_pickle(data_dir + 'data_review.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}