Restaurants_yelp_API_for_GoogleSearchResults.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#import needed libraries\n",
    "import pandas as pd\n",
    "import json\n",
    "import requests\n",
    "from urllib.error import HTTPError\n",
    "from urllib.parse import quote\n",
    "from urllib.parse import urlencode\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>business_id</th>\n",
       "      <th>categories</th>\n",
       "      <th>city</th>\n",
       "      <th>full_address</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>name</th>\n",
       "      <th>neighborhoods</th>\n",
       "      <th>open</th>\n",
       "      <th>review_count</th>\n",
       "      <th>stars</th>\n",
       "      <th>state</th>\n",
       "      <th>type</th>\n",
       "      <th>found</th>\n",
       "      <th>google_term</th>\n",
       "      <th>google_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>gA5CuBxF-0CnOpGnryWJdQ</td>\n",
       "      <td>[Mexican, Restaurants]</td>\n",
       "      <td>Phoenix</td>\n",
       "      <td>519 W Thomas Rd\\nPhoenix, AZ 85013</td>\n",
       "      <td>33.480105</td>\n",
       "      <td>-112.081361</td>\n",
       "      <td>La Paloma Mexican Food</td>\n",
       "      <td>[]</td>\n",
       "      <td>True</td>\n",
       "      <td>5</td>\n",
       "      <td>4.0</td>\n",
       "      <td>AZ</td>\n",
       "      <td>business</td>\n",
       "      <td>0</td>\n",
       "      <td>La Paloma Mexican Food 519 W Thomas Rd Phoenix...</td>\n",
       "      <td>la-paloma-mexican-food-phoenix</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>sygb2W4NRadVXiWhW8bnFQ</td>\n",
       "      <td>[Mexican, Restaurants]</td>\n",
       "      <td>Gila Bend</td>\n",
       "      <td>616 E Pima Street\\nGila Bend, AZ 85337</td>\n",
       "      <td>32.948295</td>\n",
       "      <td>-112.711322</td>\n",
       "      <td>Sofia's Mexican Food</td>\n",
       "      <td>[]</td>\n",
       "      <td>True</td>\n",
       "      <td>10</td>\n",
       "      <td>3.5</td>\n",
       "      <td>AZ</td>\n",
       "      <td>business</td>\n",
       "      <td>0</td>\n",
       "      <td>Sofia's Mexican Food 616 E Pima Street Gila Be...</td>\n",
       "      <td>sofias-mexican-food-gila-bend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>MG1IbQUurcXn8NQgXGM34Q</td>\n",
       "      <td>[Delis, Restaurants]</td>\n",
       "      <td>Mesa</td>\n",
       "      <td>4410 E University Dr\\nMesa, AZ 85205</td>\n",
       "      <td>33.423805</td>\n",
       "      <td>-111.735789</td>\n",
       "      <td>Yee-haws</td>\n",
       "      <td>[]</td>\n",
       "      <td>True</td>\n",
       "      <td>3</td>\n",
       "      <td>3.5</td>\n",
       "      <td>AZ</td>\n",
       "      <td>business</td>\n",
       "      <td>0</td>\n",
       "      <td>Yee-haws 4410 E University Dr Mesa, AZ 85205</td>\n",
       "      <td>yee-haws-mesa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>KqYY2rwD8ymjU9mVJwiuRQ</td>\n",
       "      <td>[Chinese, Restaurants]</td>\n",
       "      <td>Phoenix</td>\n",
       "      <td>9020 N 19th Ave\\nPhoenix, AZ 85021</td>\n",
       "      <td>33.568297</td>\n",
       "      <td>-112.099840</td>\n",
       "      <td>Panda Gourmet</td>\n",
       "      <td>[]</td>\n",
       "      <td>True</td>\n",
       "      <td>3</td>\n",
       "      <td>3.5</td>\n",
       "      <td>AZ</td>\n",
       "      <td>business</td>\n",
       "      <td>0</td>\n",
       "      <td>Panda Gourmet 9020 N 19th Ave Phoenix, AZ 85021</td>\n",
       "      <td>panda-gourmet-phoenix</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>jvvh4Q00Hq2XyIcfmAAT2A</td>\n",
       "      <td>[Burgers, Restaurants]</td>\n",
       "      <td>Scottsdale</td>\n",
       "      <td>7014-590 E Camelback Rd\\nScottsdale, AZ 85251</td>\n",
       "      <td>33.503323</td>\n",
       "      <td>-111.928457</td>\n",
       "      <td>Five Guys Burger and Fries</td>\n",
       "      <td>[]</td>\n",
       "      <td>True</td>\n",
       "      <td>52</td>\n",
       "      <td>3.5</td>\n",
       "      <td>AZ</td>\n",
       "      <td>business</td>\n",
       "      <td>0</td>\n",
       "      <td>Five Guys Burger and Fries 7014-590 E Camelbac...</td>\n",
       "      <td>five-guys-scottsdale-2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               business_id              categories        city  \\\n",
       "2   gA5CuBxF-0CnOpGnryWJdQ  [Mexican, Restaurants]     Phoenix   \n",
       "7   sygb2W4NRadVXiWhW8bnFQ  [Mexican, Restaurants]   Gila Bend   \n",
       "10  MG1IbQUurcXn8NQgXGM34Q    [Delis, Restaurants]        Mesa   \n",
       "11  KqYY2rwD8ymjU9mVJwiuRQ  [Chinese, Restaurants]     Phoenix   \n",
       "12  jvvh4Q00Hq2XyIcfmAAT2A  [Burgers, Restaurants]  Scottsdale   \n",
       "\n",
       "                                     full_address   latitude   longitude  \\\n",
       "2              519 W Thomas Rd\\nPhoenix, AZ 85013  33.480105 -112.081361   \n",
       "7          616 E Pima Street\\nGila Bend, AZ 85337  32.948295 -112.711322   \n",
       "10           4410 E University Dr\\nMesa, AZ 85205  33.423805 -111.735789   \n",
       "11             9020 N 19th Ave\\nPhoenix, AZ 85021  33.568297 -112.099840   \n",
       "12  7014-590 E Camelback Rd\\nScottsdale, AZ 85251  33.503323 -111.928457   \n",
       "\n",
       "                          name neighborhoods  open  review_count  stars state  \\\n",
       "2       La Paloma Mexican Food            []  True             5    4.0    AZ   \n",
       "7         Sofia's Mexican Food            []  True            10    3.5    AZ   \n",
       "10                    Yee-haws            []  True             3    3.5    AZ   \n",
       "11               Panda Gourmet            []  True             3    3.5    AZ   \n",
       "12  Five Guys Burger and Fries            []  True            52    3.5    AZ   \n",
       "\n",
       "        type  found                                        google_term  \\\n",
       "2   business      0  La Paloma Mexican Food 519 W Thomas Rd Phoenix...   \n",
       "7   business      0  Sofia's Mexican Food 616 E Pima Street Gila Be...   \n",
       "10  business      0       Yee-haws 4410 E University Dr Mesa, AZ 85205   \n",
       "11  business      0    Panda Gourmet 9020 N 19th Ave Phoenix, AZ 85021   \n",
       "12  business      0  Five Guys Burger and Fries 7014-590 E Camelbac...   \n",
       "\n",
       "                         google_id  \n",
       "2   la-paloma-mexican-food-phoenix  \n",
       "7    sofias-mexican-food-gila-bend  \n",
       "10                   yee-haws-mesa  \n",
       "11           panda-gourmet-phoenix  \n",
       "12          five-guys-scottsdale-2  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read training data for restaurants that were open in 2013 in AZ and were not matched with Yelp API\n",
    "restaurants_old_notmerged = pd.read_pickle('./yelp_dataset_processed/yelp_df_notmerged_id.pkl')\n",
    "restaurants_old_notmerged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "restaurants_old_notmerged.reset_index(drop=True,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>business_id</th>\n",
       "      <th>categories</th>\n",
       "      <th>city</th>\n",
       "      <th>full_address</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>name</th>\n",
       "      <th>neighborhoods</th>\n",
       "      <th>open</th>\n",
       "      <th>review_count</th>\n",
       "      <th>stars</th>\n",
       "      <th>state</th>\n",
       "      <th>type</th>\n",
       "      <th>found</th>\n",
       "      <th>google_term</th>\n",
       "      <th>google_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>gA5CuBxF-0CnOpGnryWJdQ</td>\n",
       "      <td>[Mexican, Restaurants]</td>\n",
       "      <td>Phoenix</td>\n",
       "      <td>519 W Thomas Rd\\nPhoenix, AZ 85013</td>\n",
       "      <td>33.480105</td>\n",
       "      <td>-112.081361</td>\n",
       "      <td>La Paloma Mexican Food</td>\n",
       "      <td>[]</td>\n",
       "      <td>True</td>\n",
       "      <td>5</td>\n",
       "      <td>4.0</td>\n",
       "      <td>AZ</td>\n",
       "      <td>business</td>\n",
       "      <td>0</td>\n",
       "      <td>La Paloma Mexican Food 519 W Thomas Rd Phoenix...</td>\n",
       "      <td>la-paloma-mexican-food-phoenix</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sygb2W4NRadVXiWhW8bnFQ</td>\n",
       "      <td>[Mexican, Restaurants]</td>\n",
       "      <td>Gila Bend</td>\n",
       "      <td>616 E Pima Street\\nGila Bend, AZ 85337</td>\n",
       "      <td>32.948295</td>\n",
       "      <td>-112.711322</td>\n",
       "      <td>Sofia's Mexican Food</td>\n",
       "      <td>[]</td>\n",
       "      <td>True</td>\n",
       "      <td>10</td>\n",
       "      <td>3.5</td>\n",
       "      <td>AZ</td>\n",
       "      <td>business</td>\n",
       "      <td>0</td>\n",
       "      <td>Sofia's Mexican Food 616 E Pima Street Gila Be...</td>\n",
       "      <td>sofias-mexican-food-gila-bend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MG1IbQUurcXn8NQgXGM34Q</td>\n",
       "      <td>[Delis, Restaurants]</td>\n",
       "      <td>Mesa</td>\n",
       "      <td>4410 E University Dr\\nMesa, AZ 85205</td>\n",
       "      <td>33.423805</td>\n",
       "      <td>-111.735789</td>\n",
       "      <td>Yee-haws</td>\n",
       "      <td>[]</td>\n",
       "      <td>True</td>\n",
       "      <td>3</td>\n",
       "      <td>3.5</td>\n",
       "      <td>AZ</td>\n",
       "      <td>business</td>\n",
       "      <td>0</td>\n",
       "      <td>Yee-haws 4410 E University Dr Mesa, AZ 85205</td>\n",
       "      <td>yee-haws-mesa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>KqYY2rwD8ymjU9mVJwiuRQ</td>\n",
       "      <td>[Chinese, Restaurants]</td>\n",
       "      <td>Phoenix</td>\n",
       "      <td>9020 N 19th Ave\\nPhoenix, AZ 85021</td>\n",
       "      <td>33.568297</td>\n",
       "      <td>-112.099840</td>\n",
       "      <td>Panda Gourmet</td>\n",
       "      <td>[]</td>\n",
       "      <td>True</td>\n",
       "      <td>3</td>\n",
       "      <td>3.5</td>\n",
       "      <td>AZ</td>\n",
       "      <td>business</td>\n",
       "      <td>0</td>\n",
       "      <td>Panda Gourmet 9020 N 19th Ave Phoenix, AZ 85021</td>\n",
       "      <td>panda-gourmet-phoenix</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>jvvh4Q00Hq2XyIcfmAAT2A</td>\n",
       "      <td>[Burgers, Restaurants]</td>\n",
       "      <td>Scottsdale</td>\n",
       "      <td>7014-590 E Camelback Rd\\nScottsdale, AZ 85251</td>\n",
       "      <td>33.503323</td>\n",
       "      <td>-111.928457</td>\n",
       "      <td>Five Guys Burger and Fries</td>\n",
       "      <td>[]</td>\n",
       "      <td>True</td>\n",
       "      <td>52</td>\n",
       "      <td>3.5</td>\n",
       "      <td>AZ</td>\n",
       "      <td>business</td>\n",
       "      <td>0</td>\n",
       "      <td>Five Guys Burger and Fries 7014-590 E Camelbac...</td>\n",
       "      <td>five-guys-scottsdale-2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              business_id              categories        city  \\\n",
       "0  gA5CuBxF-0CnOpGnryWJdQ  [Mexican, Restaurants]     Phoenix   \n",
       "1  sygb2W4NRadVXiWhW8bnFQ  [Mexican, Restaurants]   Gila Bend   \n",
       "2  MG1IbQUurcXn8NQgXGM34Q    [Delis, Restaurants]        Mesa   \n",
       "3  KqYY2rwD8ymjU9mVJwiuRQ  [Chinese, Restaurants]     Phoenix   \n",
       "4  jvvh4Q00Hq2XyIcfmAAT2A  [Burgers, Restaurants]  Scottsdale   \n",
       "\n",
       "                                    full_address   latitude   longitude  \\\n",
       "0             519 W Thomas Rd\\nPhoenix, AZ 85013  33.480105 -112.081361   \n",
       "1         616 E Pima Street\\nGila Bend, AZ 85337  32.948295 -112.711322   \n",
       "2           4410 E University Dr\\nMesa, AZ 85205  33.423805 -111.735789   \n",
       "3             9020 N 19th Ave\\nPhoenix, AZ 85021  33.568297 -112.099840   \n",
       "4  7014-590 E Camelback Rd\\nScottsdale, AZ 85251  33.503323 -111.928457   \n",
       "\n",
       "                         name neighborhoods  open  review_count  stars state  \\\n",
       "0      La Paloma Mexican Food            []  True             5    4.0    AZ   \n",
       "1        Sofia's Mexican Food            []  True            10    3.5    AZ   \n",
       "2                    Yee-haws            []  True             3    3.5    AZ   \n",
       "3               Panda Gourmet            []  True             3    3.5    AZ   \n",
       "4  Five Guys Burger and Fries            []  True            52    3.5    AZ   \n",
       "\n",
       "       type  found                                        google_term  \\\n",
       "0  business      0  La Paloma Mexican Food 519 W Thomas Rd Phoenix...   \n",
       "1  business      0  Sofia's Mexican Food 616 E Pima Street Gila Be...   \n",
       "2  business      0       Yee-haws 4410 E University Dr Mesa, AZ 85205   \n",
       "3  business      0    Panda Gourmet 9020 N 19th Ave Phoenix, AZ 85021   \n",
       "4  business      0  Five Guys Burger and Fries 7014-590 E Camelbac...   \n",
       "\n",
       "                        google_id  \n",
       "0  la-paloma-mexican-food-phoenix  \n",
       "1   sofias-mexican-food-gila-bend  \n",
       "2                   yee-haws-mesa  \n",
       "3           panda-gourmet-phoenix  \n",
       "4          five-guys-scottsdale-2  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "restaurants_old_notmerged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# define functions to call yelp API\n",
    "def request(host, path, business_id, bearer_token, url_params=None):\n",
    "    \"\"\"Given a bearer token, send a GET request to the API.\n",
    "    Args:\n",
    "        host (str): The domain host of the API.\n",
    "        path (str): The path of the API after the domain.\n",
    "        bearer_token (str): OAuth bearer token, obtained using client_id and client_secret.\n",
    "        url_params (dict): An optional set of query parameters in the request.\n",
    "    Returns:\n",
    "        dict: The JSON response from the request.\n",
    "    Raises:\n",
    "        HTTPError: An error occurs from the HTTP request.\n",
    "    \"\"\"\n",
    "    url_params = url_params or {}\n",
    "    url = '{0}{1}{2}'.format(host, quote(path.encode('utf8')), quote(business_id.encode('utf8')))\n",
    "    headers = {\n",
    "        'Authorization': 'Bearer %s' % bearer_token,\n",
    "    }\n",
    "\n",
    "    #print(u'Querying {0} ...'.format(url))\n",
    "    \n",
    "    i = 0\n",
    "    timed_out = True\n",
    "    while (timed_out == True) and (i <= 5):\n",
    "        timed_out = False\n",
    "        response = requests.request('GET', url, headers=headers, params=url_params)\n",
    "\n",
    "        if 'error' in response.json():\n",
    "            if response.json()['error']['code'] == 'REQUEST_TIMED_OUT':\n",
    "                # try again up to 5 times\n",
    "                timed_out = True\n",
    "                i += 1\n",
    "\n",
    "    return response.json()\n",
    "\n",
    "def obtain_bearer_token(host, path):\n",
    "    \"\"\"Given a bearer token, send a GET request to the API.\n",
    "    Args:\n",
    "        host (str): The domain host of the API.\n",
    "        path (str): The path of the API after the domain.\n",
    "        url_params (dict): An optional set of query parameters in the request.\n",
    "    Returns:\n",
    "        str: OAuth bearer token, obtained using client_id and client_secret.\n",
    "    Raises:\n",
    "        HTTPError: An error occurs from the HTTP request.\n",
    "    \"\"\"\n",
    "    url = '{0}{1}'.format(host, quote(path.encode('utf8')))\n",
    "    assert CLIENT_ID, \"Please supply your client_id.\"\n",
    "    assert CLIENT_SECRET, \"Please supply your client_secret.\"\n",
    "    data = urlencode({\n",
    "        'client_id': CLIENT_ID,\n",
    "        'client_secret': CLIENT_SECRET,\n",
    "        'grant_type': GRANT_TYPE,\n",
    "    })\n",
    "    headers = {\n",
    "        'content-type': 'application/x-www-form-urlencoded',\n",
    "    }\n",
    "    response = requests.request('POST', url, data=data, headers=headers)\n",
    "    bearer_token = response.json()['access_token']\n",
    "    return bearer_token"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# get authentication and keys\n",
    "with open('yelp_credentials.json') as data_file:    \n",
    "    cred = json.load(data_file)\n",
    "\n",
    "\n",
    "CLIENT_ID = cred['Client ID']\n",
    "CLIENT_SECRET = cred['Client Secret']\n",
    "API_HOST = 'https://api.yelp.com'\n",
    "SEARCH_PATH = '/v3/businesses/search'\n",
    "BUSINESS_PATH = '/v3/businesses/'  # Business ID will come after slash.\n",
    "TOKEN_PATH = '/oauth2/token'\n",
    "GRANT_TYPE = 'client_credentials'\n",
    "\n",
    "bearer_token = obtain_bearer_token(API_HOST, TOKEN_PATH)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "100\n",
      "200\n",
      "300\n",
      "400\n",
      "500\n",
      "600\n",
      "700\n",
      "800\n",
      "900\n",
      "1000\n",
      "1100\n",
      "1200\n"
     ]
    }
   ],
   "source": [
    "for i, restaurant in restaurants_old_notmerged.iterrows():\n",
    "\n",
    "\n",
    "    result = request(API_HOST, BUSINESS_PATH, restaurant['google_id'], bearer_token)\n",
    "\n",
    "    if i == 0:\n",
    "        restaurants_new_notmerged = pd.DataFrame(pd.io.json.json_normalize(result))\n",
    "    else:\n",
    "        df_new = pd.io.json.json_normalize(result)\n",
    "        restaurants_new_notmerged = restaurants_new_notmerged.append(df_new,ignore_index=True)\n",
    "    if i % 100 == 0:\n",
    "        print(i)\n",
    "        \n",
    "    # Sometimes API gets stuck after many consecutive call, wait after each call\n",
    "    time.sleep(0.1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'https://api.yelp.com/v3/businesses/search'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'{0}{1}'.format(API_HOST, quote(SEARCH_PATH.encode('utf8')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>categories</th>\n",
       "      <th>coordinates.latitude</th>\n",
       "      <th>coordinates.longitude</th>\n",
       "      <th>display_phone</th>\n",
       "      <th>hours</th>\n",
       "      <th>id</th>\n",
       "      <th>image_url</th>\n",
       "      <th>is_claimed</th>\n",
       "      <th>is_closed</th>\n",
       "      <th>location.address1</th>\n",
       "      <th>...</th>\n",
       "      <th>location.state</th>\n",
       "      <th>location.zip_code</th>\n",
       "      <th>name</th>\n",
       "      <th>phone</th>\n",
       "      <th>photos</th>\n",
       "      <th>price</th>\n",
       "      <th>rating</th>\n",
       "      <th>review_count</th>\n",
       "      <th>transactions</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[{'alias': 'mexican', 'title': 'Mexican'}]</td>\n",
       "      <td>32.945603</td>\n",
       "      <td>-112.725909</td>\n",
       "      <td>(928) 683-6382</td>\n",
       "      <td>[{'open': [{'is_overnight': False, 'start': '0...</td>\n",
       "      <td>sofias-mexican-food-gila-bend</td>\n",
       "      <td>https://s3-media2.fl.yelpcdn.com/bphoto/WddzkO...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>530 W Pima St</td>\n",
       "      <td>...</td>\n",
       "      <td>AZ</td>\n",
       "      <td>85337</td>\n",
       "      <td>Sofia's Mexican Food</td>\n",
       "      <td>+19286836382</td>\n",
       "      <td>[https://s3-media2.fl.yelpcdn.com/bphoto/Wddzk...</td>\n",
       "      <td>$</td>\n",
       "      <td>4.0</td>\n",
       "      <td>86</td>\n",
       "      <td>[]</td>\n",
       "      <td>https://www.yelp.com/biz/sofias-mexican-food-g...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   categories  coordinates.latitude  \\\n",
       "0  [{'alias': 'mexican', 'title': 'Mexican'}]             32.945603   \n",
       "\n",
       "   coordinates.longitude   display_phone  \\\n",
       "0            -112.725909  (928) 683-6382   \n",
       "\n",
       "                                               hours  \\\n",
       "0  [{'open': [{'is_overnight': False, 'start': '0...   \n",
       "\n",
       "                              id  \\\n",
       "0  sofias-mexican-food-gila-bend   \n",
       "\n",
       "                                           image_url  is_claimed  is_closed  \\\n",
       "0  https://s3-media2.fl.yelpcdn.com/bphoto/WddzkO...       False      False   \n",
       "\n",
       "  location.address1                        ...                          \\\n",
       "0     530 W Pima St                        ...                           \n",
       "\n",
       "  location.state location.zip_code                  name         phone  \\\n",
       "0             AZ             85337  Sofia's Mexican Food  +19286836382   \n",
       "\n",
       "                                              photos price rating  \\\n",
       "0  [https://s3-media2.fl.yelpcdn.com/bphoto/Wddzk...     $    4.0   \n",
       "\n",
       "  review_count transactions                                                url  \n",
       "0           86           []  https://www.yelp.com/biz/sofias-mexican-food-g...  \n",
       "\n",
       "[1 rows x 26 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.io.json.json_normalize(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'categories': [{'alias': 'mexican', 'title': 'Mexican'}],\n",
       " 'coordinates': {'latitude': 32.9456032090449, 'longitude': -112.725908617791},\n",
       " 'display_phone': '(928) 683-6382',\n",
       " 'hours': [{'hours_type': 'REGULAR',\n",
       "   'is_open_now': True,\n",
       "   'open': [{'day': 0, 'end': '2100', 'is_overnight': False, 'start': '0600'},\n",
       "    {'day': 1, 'end': '2100', 'is_overnight': False, 'start': '0600'},\n",
       "    {'day': 2, 'end': '2100', 'is_overnight': False, 'start': '0600'},\n",
       "    {'day': 3, 'end': '2100', 'is_overnight': False, 'start': '0600'},\n",
       "    {'day': 4, 'end': '2100', 'is_overnight': False, 'start': '0600'},\n",
       "    {'day': 5, 'end': '2100', 'is_overnight': False, 'start': '0700'},\n",
       "    {'day': 6, 'end': '2100', 'is_overnight': False, 'start': '0700'}]}],\n",
       " 'id': 'sofias-mexican-food-gila-bend',\n",
       " 'image_url': 'https://s3-media2.fl.yelpcdn.com/bphoto/WddzkO2_ITK7AWMdhm74JQ/o.jpg',\n",
       " 'is_claimed': False,\n",
       " 'is_closed': False,\n",
       " 'location': {'address1': '530 W Pima St',\n",
       "  'address2': '',\n",
       "  'address3': '',\n",
       "  'city': 'Gila Bend',\n",
       "  'country': 'US',\n",
       "  'cross_streets': '',\n",
       "  'display_address': ['530 W Pima St', 'Gila Bend, AZ 85337'],\n",
       "  'state': 'AZ',\n",
       "  'zip_code': '85337'},\n",
       " 'name': \"Sofia's Mexican Food\",\n",
       " 'phone': '+19286836382',\n",
       " 'photos': ['https://s3-media2.fl.yelpcdn.com/bphoto/WddzkO2_ITK7AWMdhm74JQ/o.jpg',\n",
       "  'https://s3-media3.fl.yelpcdn.com/bphoto/HfIA4UM2g8jgcRL503qHog/o.jpg',\n",
       "  'https://s3-media4.fl.yelpcdn.com/bphoto/3AfaU5HesLmIGBjvtIhJbQ/o.jpg'],\n",
       " 'price': '$',\n",
       " 'rating': 4.0,\n",
       " 'review_count': 86,\n",
       " 'transactions': [],\n",
       " 'url': 'https://www.yelp.com/biz/sofias-mexican-food-gila-bend?adjust_creative=dij3dbMptmNdYMhDZHUXLQ&utm_campaign=yelp_api_v3&utm_medium=api_v3_business_lookup&utm_source=dij3dbMptmNdYMhDZHUXLQ'}"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "i"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "restaurants_new_notmerged.to_pickle('./yelp_dataset_processed/yelp_df_notmerged_new.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Repeat the same about the merged restaurants\n",
    "\n",
    "restaurants_merged = pd.read_pickle('./yelp_dataset_processed/yelp_df.pkl')"
   ]
  },
  {
   "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>categories_new</th>\n",
       "      <th>coordinates</th>\n",
       "      <th>display_phone</th>\n",
       "      <th>distance</th>\n",
       "      <th>id</th>\n",
       "      <th>image_url</th>\n",
       "      <th>is_closed</th>\n",
       "      <th>location</th>\n",
       "      <th>name_new</th>\n",
       "      <th>phone</th>\n",
       "      <th>...</th>\n",
       "      <th>neighborhoods</th>\n",
       "      <th>open</th>\n",
       "      <th>review_count</th>\n",
       "      <th>stars</th>\n",
       "      <th>state</th>\n",
       "      <th>type</th>\n",
       "      <th>zprice</th>\n",
       "      <th>zreview_count</th>\n",
       "      <th>zstar</th>\n",
       "      <th>unique</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[{'alias': 'bagels', 'title': 'Bagels'}, {'ali...</td>\n",
       "      <td>{'latitude': 33.7135958370734, 'longitude': -1...</td>\n",
       "      <td>(623) 825-0355</td>\n",
       "      <td>11.3292</td>\n",
       "      <td>hot-bagels-and-deli-glendale-az</td>\n",
       "      <td>https://s3-media4.fl.yelpcdn.com/bphoto/efZ-8J...</td>\n",
       "      <td>False</td>\n",
       "      <td>{'address1': '6520 W Happy Valley Rd', 'addres...</td>\n",
       "      <td>Hot Bagels &amp; Deli</td>\n",
       "      <td>+16238250355</td>\n",
       "      <td>...</td>\n",
       "      <td>[]</td>\n",
       "      <td>True</td>\n",
       "      <td>14</td>\n",
       "      <td>3.5</td>\n",
       "      <td>AZ</td>\n",
       "      <td>business</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[{'alias': 'sandwiches', 'title': 'Sandwiches'...</td>\n",
       "      <td>{'latitude': 33.3786951, 'longitude': -111.812...</td>\n",
       "      <td>(480) 632-6453</td>\n",
       "      <td>3.4065</td>\n",
       "      <td>jersey-mikes-subs-gilbert-2</td>\n",
       "      <td>https://s3-media1.fl.yelpcdn.com/bphoto/BYIdRj...</td>\n",
       "      <td>False</td>\n",
       "      <td>{'address1': '891 E Baseline Rd', 'address2': ...</td>\n",
       "      <td>Jersey Mike's Subs</td>\n",
       "      <td>+14806326453</td>\n",
       "      <td>...</td>\n",
       "      <td>[]</td>\n",
       "      <td>True</td>\n",
       "      <td>10</td>\n",
       "      <td>3.5</td>\n",
       "      <td>AZ</td>\n",
       "      <td>business</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.122468</td>\n",
       "      <td>-0.083333</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>[{'alias': 'italian', 'title': 'Italian'}, {'a...</td>\n",
       "      <td>{'latitude': 33.61746, 'longitude': -111.92626}</td>\n",
       "      <td>(480) 321-8800</td>\n",
       "      <td>4.31233</td>\n",
       "      <td>sauce-pizza-and-wine-scottsdale-2</td>\n",
       "      <td>https://s3-media2.fl.yelpcdn.com/bphoto/1lTHQh...</td>\n",
       "      <td>False</td>\n",
       "      <td>{'address1': '14418 N Scottsdale Rd', 'address...</td>\n",
       "      <td>Sauce Pizza and Wine</td>\n",
       "      <td>+14803218800</td>\n",
       "      <td>...</td>\n",
       "      <td>[]</td>\n",
       "      <td>True</td>\n",
       "      <td>55</td>\n",
       "      <td>4</td>\n",
       "      <td>AZ</td>\n",
       "      <td>business</td>\n",
       "      <td>-0.071429</td>\n",
       "      <td>0.064650</td>\n",
       "      <td>0.057143</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>[{'alias': 'burgers', 'title': 'Burgers'}, {'a...</td>\n",
       "      <td>{'latitude': 33.5669898986816, 'longitude': -1...</td>\n",
       "      <td>(602) 870-1111</td>\n",
       "      <td>17.4581</td>\n",
       "      <td>fuddruckers-phoenix-3</td>\n",
       "      <td>https://s3-media4.fl.yelpcdn.com/bphoto/gcmpOL...</td>\n",
       "      <td>False</td>\n",
       "      <td>{'address1': '8941 North Black Canyon Hwy', 'a...</td>\n",
       "      <td>Fuddruckers</td>\n",
       "      <td>+16028701111</td>\n",
       "      <td>...</td>\n",
       "      <td>[]</td>\n",
       "      <td>True</td>\n",
       "      <td>23</td>\n",
       "      <td>4</td>\n",
       "      <td>AZ</td>\n",
       "      <td>business</td>\n",
       "      <td>0.055556</td>\n",
       "      <td>0.488650</td>\n",
       "      <td>0.066667</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>[{'alias': 'hotdogs', 'title': 'Fast Food'}, {...</td>\n",
       "      <td>{'latitude': 33.5818672180176, 'longitude': -1...</td>\n",
       "      <td>(480) 451-1803</td>\n",
       "      <td>1.93633</td>\n",
       "      <td>mcdonalds-scottsdale-5</td>\n",
       "      <td>https://s3-media4.fl.yelpcdn.com/bphoto/ofHjsT...</td>\n",
       "      <td>False</td>\n",
       "      <td>{'address1': '9251 E Shea Blvd', 'address2': '...</td>\n",
       "      <td>McDonald's</td>\n",
       "      <td>+14804511803</td>\n",
       "      <td>...</td>\n",
       "      <td>[]</td>\n",
       "      <td>True</td>\n",
       "      <td>3</td>\n",
       "      <td>2.5</td>\n",
       "      <td>AZ</td>\n",
       "      <td>business</td>\n",
       "      <td>-0.022727</td>\n",
       "      <td>-0.892338</td>\n",
       "      <td>-0.100000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      categories_new  \\\n",
       "0  [{'alias': 'bagels', 'title': 'Bagels'}, {'ali...   \n",
       "1  [{'alias': 'sandwiches', 'title': 'Sandwiches'...   \n",
       "2  [{'alias': 'italian', 'title': 'Italian'}, {'a...   \n",
       "3  [{'alias': 'burgers', 'title': 'Burgers'}, {'a...   \n",
       "4  [{'alias': 'hotdogs', 'title': 'Fast Food'}, {...   \n",
       "\n",
       "                                         coordinates   display_phone distance  \\\n",
       "0  {'latitude': 33.7135958370734, 'longitude': -1...  (623) 825-0355  11.3292   \n",
       "1  {'latitude': 33.3786951, 'longitude': -111.812...  (480) 632-6453   3.4065   \n",
       "2    {'latitude': 33.61746, 'longitude': -111.92626}  (480) 321-8800  4.31233   \n",
       "3  {'latitude': 33.5669898986816, 'longitude': -1...  (602) 870-1111  17.4581   \n",
       "4  {'latitude': 33.5818672180176, 'longitude': -1...  (480) 451-1803  1.93633   \n",
       "\n",
       "                                  id  \\\n",
       "0    hot-bagels-and-deli-glendale-az   \n",
       "1        jersey-mikes-subs-gilbert-2   \n",
       "2  sauce-pizza-and-wine-scottsdale-2   \n",
       "3              fuddruckers-phoenix-3   \n",
       "4             mcdonalds-scottsdale-5   \n",
       "\n",
       "                                           image_url is_closed  \\\n",
       "0  https://s3-media4.fl.yelpcdn.com/bphoto/efZ-8J...     False   \n",
       "1  https://s3-media1.fl.yelpcdn.com/bphoto/BYIdRj...     False   \n",
       "2  https://s3-media2.fl.yelpcdn.com/bphoto/1lTHQh...     False   \n",
       "3  https://s3-media4.fl.yelpcdn.com/bphoto/gcmpOL...     False   \n",
       "4  https://s3-media4.fl.yelpcdn.com/bphoto/ofHjsT...     False   \n",
       "\n",
       "                                            location              name_new  \\\n",
       "0  {'address1': '6520 W Happy Valley Rd', 'addres...     Hot Bagels & Deli   \n",
       "1  {'address1': '891 E Baseline Rd', 'address2': ...    Jersey Mike's Subs   \n",
       "2  {'address1': '14418 N Scottsdale Rd', 'address...  Sauce Pizza and Wine   \n",
       "3  {'address1': '8941 North Black Canyon Hwy', 'a...           Fuddruckers   \n",
       "4  {'address1': '9251 E Shea Blvd', 'address2': '...            McDonald's   \n",
       "\n",
       "          phone  ...    neighborhoods  open review_count stars state  \\\n",
       "0  +16238250355  ...               []  True           14   3.5    AZ   \n",
       "1  +14806326453  ...               []  True           10   3.5    AZ   \n",
       "2  +14803218800  ...               []  True           55     4    AZ   \n",
       "3  +16028701111  ...               []  True           23     4    AZ   \n",
       "4  +14804511803  ...               []  True            3   2.5    AZ   \n",
       "\n",
       "       type    zprice zreview_count     zstar unique  \n",
       "0  business  0.000000      0.000000  0.000000    1.0  \n",
       "1  business  0.000000     -1.122468 -0.083333    0.0  \n",
       "2  business -0.071429      0.064650  0.057143    0.0  \n",
       "3  business  0.055556      0.488650  0.066667    0.0  \n",
       "4  business -0.022727     -0.892338 -0.100000    0.0  \n",
       "\n",
       "[5 rows x 32 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "restaurants_merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "100\n",
      "200\n",
      "300\n",
      "400\n",
      "500\n",
      "600\n",
      "700\n",
      "800\n",
      "900\n",
      "1000\n",
      "1100\n",
      "1200\n",
      "1300\n",
      "1400\n",
      "1500\n",
      "1600\n",
      "1700\n",
      "1800\n",
      "1900\n",
      "2000\n",
      "2100\n",
      "2200\n",
      "2300\n"
     ]
    }
   ],
   "source": [
    "for i, restaurant in restaurants_merged.iterrows():\n",
    "\n",
    "\n",
    "    result = request(API_HOST, BUSINESS_PATH, restaurant['id'], bearer_token)\n",
    "\n",
    "    if i == 0:\n",
    "        restaurants_new_merged = pd.DataFrame(pd.io.json.json_normalize(result))\n",
    "    else:\n",
    "        df_new = pd.io.json.json_normalize(result)\n",
    "        restaurants_new_merged = restaurants_new_merged.append(df_new,ignore_index=True)\n",
    "    if i % 100 == 0:\n",
    "        print(i)\n",
    "        \n",
    "    # Sometimes API gets stuck after many consecutive call, wait after each call\n",
    "    time.sleep(0.1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>categories</th>\n",
       "      <th>coordinates.latitude</th>\n",
       "      <th>coordinates.longitude</th>\n",
       "      <th>display_phone</th>\n",
       "      <th>error.code</th>\n",
       "      <th>error.description</th>\n",
       "      <th>hours</th>\n",
       "      <th>id</th>\n",
       "      <th>image_url</th>\n",
       "      <th>is_claimed</th>\n",
       "      <th>...</th>\n",
       "      <th>location.state</th>\n",
       "      <th>location.zip_code</th>\n",
       "      <th>name</th>\n",
       "      <th>phone</th>\n",
       "      <th>photos</th>\n",
       "      <th>price</th>\n",
       "      <th>rating</th>\n",
       "      <th>review_count</th>\n",
       "      <th>transactions</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[{'alias': 'bagels', 'title': 'Bagels'}, {'ali...</td>\n",
       "      <td>33.713596</td>\n",
       "      <td>-112.200125</td>\n",
       "      <td>(623) 825-0355</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'open': [{'is_overnight': False, 'start': '0...</td>\n",
       "      <td>hot-bagels-and-deli-glendale-az</td>\n",
       "      <td>https://s3-media4.fl.yelpcdn.com/bphoto/efZ-8J...</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>AZ</td>\n",
       "      <td>85310</td>\n",
       "      <td>Hot Bagels &amp; Deli</td>\n",
       "      <td>+16238250355</td>\n",
       "      <td>[https://s3-media4.fl.yelpcdn.com/bphoto/efZ-8...</td>\n",
       "      <td>$</td>\n",
       "      <td>3.5</td>\n",
       "      <td>77.0</td>\n",
       "      <td>[]</td>\n",
       "      <td>https://www.yelp.com/biz/hot-bagels-and-deli-g...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[{'alias': 'sandwiches', 'title': 'Sandwiches'...</td>\n",
       "      <td>33.378695</td>\n",
       "      <td>-111.812692</td>\n",
       "      <td>(480) 632-6453</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'open': [{'is_overnight': False, 'start': '1...</td>\n",
       "      <td>jersey-mikes-subs-gilbert-2</td>\n",
       "      <td>https://s3-media1.fl.yelpcdn.com/bphoto/BYIdRj...</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>AZ</td>\n",
       "      <td>85233</td>\n",
       "      <td>Jersey Mike's Subs</td>\n",
       "      <td>+14806326453</td>\n",
       "      <td>[https://s3-media2.fl.yelpcdn.com/bphoto/eTDf8...</td>\n",
       "      <td>$</td>\n",
       "      <td>3.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>[pickup]</td>\n",
       "      <td>https://www.yelp.com/biz/jersey-mikes-subs-gil...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>[{'alias': 'italian', 'title': 'Italian'}, {'a...</td>\n",
       "      <td>33.617460</td>\n",
       "      <td>-111.926260</td>\n",
       "      <td>(480) 321-8800</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'open': [{'is_overnight': False, 'start': '1...</td>\n",
       "      <td>sauce-pizza-and-wine-scottsdale-2</td>\n",
       "      <td>https://s3-media2.fl.yelpcdn.com/bphoto/1lTHQh...</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>AZ</td>\n",
       "      <td>85254</td>\n",
       "      <td>Sauce Pizza and Wine</td>\n",
       "      <td>+14803218800</td>\n",
       "      <td>[https://s3-media2.fl.yelpcdn.com/bphoto/1lTHQ...</td>\n",
       "      <td>$$</td>\n",
       "      <td>3.5</td>\n",
       "      <td>163.0</td>\n",
       "      <td>[pickup]</td>\n",
       "      <td>https://www.yelp.com/biz/sauce-pizza-and-wine-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>[{'alias': 'burgers', 'title': 'Burgers'}, {'a...</td>\n",
       "      <td>33.566990</td>\n",
       "      <td>-112.116241</td>\n",
       "      <td>(602) 870-1111</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>fuddruckers-phoenix-3</td>\n",
       "      <td>https://s3-media4.fl.yelpcdn.com/bphoto/gcmpOL...</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>AZ</td>\n",
       "      <td>85021</td>\n",
       "      <td>Fuddruckers</td>\n",
       "      <td>+16028701111</td>\n",
       "      <td>[https://s3-media4.fl.yelpcdn.com/bphoto/gcmpO...</td>\n",
       "      <td>$$</td>\n",
       "      <td>3.5</td>\n",
       "      <td>81.0</td>\n",
       "      <td>[]</td>\n",
       "      <td>https://www.yelp.com/biz/fuddruckers-phoenix-3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>[{'alias': 'hotdogs', 'title': 'Fast Food'}, {...</td>\n",
       "      <td>33.581867</td>\n",
       "      <td>-111.881622</td>\n",
       "      <td>(480) 451-1803</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>mcdonalds-scottsdale-5</td>\n",
       "      <td>https://s3-media4.fl.yelpcdn.com/bphoto/ofHjsT...</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>AZ</td>\n",
       "      <td>85258</td>\n",
       "      <td>McDonald's</td>\n",
       "      <td>+14804511803</td>\n",
       "      <td>[https://s3-media4.fl.yelpcdn.com/bphoto/ofHjs...</td>\n",
       "      <td>$</td>\n",
       "      <td>2.5</td>\n",
       "      <td>16.0</td>\n",
       "      <td>[]</td>\n",
       "      <td>https://www.yelp.com/biz/mcdonalds-scottsdale-...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          categories  coordinates.latitude  \\\n",
       "0  [{'alias': 'bagels', 'title': 'Bagels'}, {'ali...             33.713596   \n",
       "1  [{'alias': 'sandwiches', 'title': 'Sandwiches'...             33.378695   \n",
       "2  [{'alias': 'italian', 'title': 'Italian'}, {'a...             33.617460   \n",
       "3  [{'alias': 'burgers', 'title': 'Burgers'}, {'a...             33.566990   \n",
       "4  [{'alias': 'hotdogs', 'title': 'Fast Food'}, {...             33.581867   \n",
       "\n",
       "   coordinates.longitude   display_phone error.code error.description  \\\n",
       "0            -112.200125  (623) 825-0355        NaN               NaN   \n",
       "1            -111.812692  (480) 632-6453        NaN               NaN   \n",
       "2            -111.926260  (480) 321-8800        NaN               NaN   \n",
       "3            -112.116241  (602) 870-1111        NaN               NaN   \n",
       "4            -111.881622  (480) 451-1803        NaN               NaN   \n",
       "\n",
       "                                               hours  \\\n",
       "0  [{'open': [{'is_overnight': False, 'start': '0...   \n",
       "1  [{'open': [{'is_overnight': False, 'start': '1...   \n",
       "2  [{'open': [{'is_overnight': False, 'start': '1...   \n",
       "3                                                NaN   \n",
       "4                                                NaN   \n",
       "\n",
       "                                  id  \\\n",
       "0    hot-bagels-and-deli-glendale-az   \n",
       "1        jersey-mikes-subs-gilbert-2   \n",
       "2  sauce-pizza-and-wine-scottsdale-2   \n",
       "3              fuddruckers-phoenix-3   \n",
       "4             mcdonalds-scottsdale-5   \n",
       "\n",
       "                                           image_url is_claimed  \\\n",
       "0  https://s3-media4.fl.yelpcdn.com/bphoto/efZ-8J...       True   \n",
       "1  https://s3-media1.fl.yelpcdn.com/bphoto/BYIdRj...       True   \n",
       "2  https://s3-media2.fl.yelpcdn.com/bphoto/1lTHQh...       True   \n",
       "3  https://s3-media4.fl.yelpcdn.com/bphoto/gcmpOL...       True   \n",
       "4  https://s3-media4.fl.yelpcdn.com/bphoto/ofHjsT...      False   \n",
       "\n",
       "                         ...                         location.state  \\\n",
       "0                        ...                                     AZ   \n",
       "1                        ...                                     AZ   \n",
       "2                        ...                                     AZ   \n",
       "3                        ...                                     AZ   \n",
       "4                        ...                                     AZ   \n",
       "\n",
       "  location.zip_code                  name         phone  \\\n",
       "0             85310     Hot Bagels & Deli  +16238250355   \n",
       "1             85233    Jersey Mike's Subs  +14806326453   \n",
       "2             85254  Sauce Pizza and Wine  +14803218800   \n",
       "3             85021           Fuddruckers  +16028701111   \n",
       "4             85258            McDonald's  +14804511803   \n",
       "\n",
       "                                              photos price rating  \\\n",
       "0  [https://s3-media4.fl.yelpcdn.com/bphoto/efZ-8...     $    3.5   \n",
       "1  [https://s3-media2.fl.yelpcdn.com/bphoto/eTDf8...     $    3.0   \n",
       "2  [https://s3-media2.fl.yelpcdn.com/bphoto/1lTHQ...    $$    3.5   \n",
       "3  [https://s3-media4.fl.yelpcdn.com/bphoto/gcmpO...    $$    3.5   \n",
       "4  [https://s3-media4.fl.yelpcdn.com/bphoto/ofHjs...     $    2.5   \n",
       "\n",
       "  review_count transactions                                                url  \n",
       "0         77.0           []  https://www.yelp.com/biz/hot-bagels-and-deli-g...  \n",
       "1         29.0     [pickup]  https://www.yelp.com/biz/jersey-mikes-subs-gil...  \n",
       "2        163.0     [pickup]  https://www.yelp.com/biz/sauce-pizza-and-wine-...  \n",
       "3         81.0           []  https://www.yelp.com/biz/fuddruckers-phoenix-3...  \n",
       "4         16.0           []  https://www.yelp.com/biz/mcdonalds-scottsdale-...  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "restaurants_new_merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>categories</th>\n",
       "      <th>coordinates.latitude</th>\n",
       "      <th>coordinates.longitude</th>\n",
       "      <th>display_phone</th>\n",
       "      <th>error.code</th>\n",
       "      <th>error.description</th>\n",
       "      <th>hours</th>\n",
       "      <th>id</th>\n",
       "      <th>image_url</th>\n",
       "      <th>is_claimed</th>\n",
       "      <th>...</th>\n",
       "      <th>location.state</th>\n",
       "      <th>location.zip_code</th>\n",
       "      <th>name</th>\n",
       "      <th>phone</th>\n",
       "      <th>photos</th>\n",
       "      <th>price</th>\n",
       "      <th>rating</th>\n",
       "      <th>review_count</th>\n",
       "      <th>transactions</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[{'alias': 'mexican', 'title': 'Mexican'}]</td>\n",
       "      <td>33.480195</td>\n",
       "      <td>-112.081313</td>\n",
       "      <td>(602) 266-1423</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'open': [{'is_overnight': False, 'start': '0...</td>\n",
       "      <td>la-paloma-mexican-food-phoenix</td>\n",
       "      <td>https://s3-media2.fl.yelpcdn.com/bphoto/qUjY7x...</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>AZ</td>\n",
       "      <td>85013</td>\n",
       "      <td>La Paloma Mexican Food</td>\n",
       "      <td>+16022661423</td>\n",
       "      <td>[https://s3-media2.fl.yelpcdn.com/bphoto/qUjY7...</td>\n",
       "      <td>$</td>\n",
       "      <td>3.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>[]</td>\n",
       "      <td>https://www.yelp.com/biz/la-paloma-mexican-foo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[{'alias': 'mexican', 'title': 'Mexican'}]</td>\n",
       "      <td>32.945603</td>\n",
       "      <td>-112.725909</td>\n",
       "      <td>(928) 683-6382</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'open': [{'is_overnight': False, 'start': '0...</td>\n",
       "      <td>sofias-mexican-food-gila-bend</td>\n",
       "      <td>https://s3-media2.fl.yelpcdn.com/bphoto/WddzkO...</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>AZ</td>\n",
       "      <td>85337</td>\n",
       "      <td>Sofia's Mexican Food</td>\n",
       "      <td>+19286836382</td>\n",
       "      <td>[https://s3-media2.fl.yelpcdn.com/bphoto/Wddzk...</td>\n",
       "      <td>$</td>\n",
       "      <td>4.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>[]</td>\n",
       "      <td>https://www.yelp.com/biz/sofias-mexican-food-g...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>[{'alias': 'delis', 'title': 'Delis'}]</td>\n",
       "      <td>33.423805</td>\n",
       "      <td>-111.735786</td>\n",
       "      <td>(480) 641-4170</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'open': [{'is_overnight': False, 'start': '0...</td>\n",
       "      <td>yee-haws-mesa</td>\n",
       "      <td>https://s3-media2.fl.yelpcdn.com/bphoto/5HfVEs...</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>AZ</td>\n",
       "      <td>85205</td>\n",
       "      <td>Yee-haws</td>\n",
       "      <td>+14806414170</td>\n",
       "      <td>[https://s3-media2.fl.yelpcdn.com/bphoto/5HfVE...</td>\n",
       "      <td>$</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>[]</td>\n",
       "      <td>https://www.yelp.com/biz/yee-haws-mesa?adjust_...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>[{'alias': 'chinese', 'title': 'Chinese'}]</td>\n",
       "      <td>33.568398</td>\n",
       "      <td>-112.100121</td>\n",
       "      <td>(602) 678-4293</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'open': [{'is_overnight': False, 'start': '1...</td>\n",
       "      <td>panda-gourmet-phoenix</td>\n",
       "      <td>https://s3-media3.fl.yelpcdn.com/bphoto/eIsbpn...</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>AZ</td>\n",
       "      <td>85021</td>\n",
       "      <td>Panda Gourmet</td>\n",
       "      <td>+16026784293</td>\n",
       "      <td>[https://s3-media3.fl.yelpcdn.com/bphoto/eIsbp...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.5</td>\n",
       "      <td>5.0</td>\n",
       "      <td>[]</td>\n",
       "      <td>https://www.yelp.com/biz/panda-gourmet-phoenix...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>[{'alias': 'burgers', 'title': 'Burgers'}, {'a...</td>\n",
       "      <td>33.504062</td>\n",
       "      <td>-111.929431</td>\n",
       "      <td>(480) 945-1928</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'open': [{'is_overnight': False, 'start': '1...</td>\n",
       "      <td>five-guys-scottsdale-2</td>\n",
       "      <td>https://s3-media2.fl.yelpcdn.com/bphoto/xjJkzA...</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>AZ</td>\n",
       "      <td>85251</td>\n",
       "      <td>Five Guys</td>\n",
       "      <td>+14809451928</td>\n",
       "      <td>[https://s3-media2.fl.yelpcdn.com/bphoto/xjJkz...</td>\n",
       "      <td>$$</td>\n",
       "      <td>3.5</td>\n",
       "      <td>87.0</td>\n",
       "      <td>[]</td>\n",
       "      <td>https://www.yelp.com/biz/five-guys-scottsdale-...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          categories  coordinates.latitude  \\\n",
       "0         [{'alias': 'mexican', 'title': 'Mexican'}]             33.480195   \n",
       "1         [{'alias': 'mexican', 'title': 'Mexican'}]             32.945603   \n",
       "2             [{'alias': 'delis', 'title': 'Delis'}]             33.423805   \n",
       "3         [{'alias': 'chinese', 'title': 'Chinese'}]             33.568398   \n",
       "4  [{'alias': 'burgers', 'title': 'Burgers'}, {'a...             33.504062   \n",
       "\n",
       "   coordinates.longitude   display_phone error.code error.description  \\\n",
       "0            -112.081313  (602) 266-1423        NaN               NaN   \n",
       "1            -112.725909  (928) 683-6382        NaN               NaN   \n",
       "2            -111.735786  (480) 641-4170        NaN               NaN   \n",
       "3            -112.100121  (602) 678-4293        NaN               NaN   \n",
       "4            -111.929431  (480) 945-1928        NaN               NaN   \n",
       "\n",
       "                                               hours  \\\n",
       "0  [{'open': [{'is_overnight': False, 'start': '0...   \n",
       "1  [{'open': [{'is_overnight': False, 'start': '0...   \n",
       "2  [{'open': [{'is_overnight': False, 'start': '0...   \n",
       "3  [{'open': [{'is_overnight': False, 'start': '1...   \n",
       "4  [{'open': [{'is_overnight': False, 'start': '1...   \n",
       "\n",
       "                               id  \\\n",
       "0  la-paloma-mexican-food-phoenix   \n",
       "1   sofias-mexican-food-gila-bend   \n",
       "2                   yee-haws-mesa   \n",
       "3           panda-gourmet-phoenix   \n",
       "4          five-guys-scottsdale-2   \n",
       "\n",
       "                                           image_url is_claimed  \\\n",
       "0  https://s3-media2.fl.yelpcdn.com/bphoto/qUjY7x...      False   \n",
       "1  https://s3-media2.fl.yelpcdn.com/bphoto/WddzkO...      False   \n",
       "2  https://s3-media2.fl.yelpcdn.com/bphoto/5HfVEs...      False   \n",
       "3  https://s3-media3.fl.yelpcdn.com/bphoto/eIsbpn...       True   \n",
       "4  https://s3-media2.fl.yelpcdn.com/bphoto/xjJkzA...       True   \n",
       "\n",
       "                         ...                         location.state  \\\n",
       "0                        ...                                     AZ   \n",
       "1                        ...                                     AZ   \n",
       "2                        ...                                     AZ   \n",
       "3                        ...                                     AZ   \n",
       "4                        ...                                     AZ   \n",
       "\n",
       "  location.zip_code                    name         phone  \\\n",
       "0             85013  La Paloma Mexican Food  +16022661423   \n",
       "1             85337    Sofia's Mexican Food  +19286836382   \n",
       "2             85205                Yee-haws  +14806414170   \n",
       "3             85021           Panda Gourmet  +16026784293   \n",
       "4             85251               Five Guys  +14809451928   \n",
       "\n",
       "                                              photos price rating  \\\n",
       "0  [https://s3-media2.fl.yelpcdn.com/bphoto/qUjY7...     $    3.0   \n",
       "1  [https://s3-media2.fl.yelpcdn.com/bphoto/Wddzk...     $    4.0   \n",
       "2  [https://s3-media2.fl.yelpcdn.com/bphoto/5HfVE...     $    4.5   \n",
       "3  [https://s3-media3.fl.yelpcdn.com/bphoto/eIsbp...   NaN    3.5   \n",
       "4  [https://s3-media2.fl.yelpcdn.com/bphoto/xjJkz...    $$    3.5   \n",
       "\n",
       "  review_count transactions                                                url  \n",
       "0         18.0           []  https://www.yelp.com/biz/la-paloma-mexican-foo...  \n",
       "1         86.0           []  https://www.yelp.com/biz/sofias-mexican-food-g...  \n",
       "2          3.0           []  https://www.yelp.com/biz/yee-haws-mesa?adjust_...  \n",
       "3          5.0           []  https://www.yelp.com/biz/panda-gourmet-phoenix...  \n",
       "4         87.0           []  https://www.yelp.com/biz/five-guys-scottsdale-...  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "restaurants_new_notmerged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['categories', 'coordinates.latitude', 'coordinates.longitude',\n",
       "       'display_phone', 'error.code', 'error.description', 'hours', 'id',\n",
       "       'image_url', 'is_claimed', 'is_closed', 'location.address1',\n",
       "       'location.address2', 'location.address3', 'location.city',\n",
       "       'location.country', 'location.cross_streets',\n",
       "       'location.display_address', 'location.state', 'location.zip_code',\n",
       "       'name', 'phone', 'photos', 'price', 'rating', 'review_count',\n",
       "       'transactions', 'url'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "restaurants_new_notmerged.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "restaurants_new_merged.to_pickle('./yelp_dataset_processed/yelp_df_merged_new.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}