preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "goal: predict if the restaurant will close or not\n",
    "'''\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_table('business.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\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>name</th>\n",
       "      <th>neighborhood</th>\n",
       "      <th>address</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>stars</th>\n",
       "      <th>review_count</th>\n",
       "      <th>is_open</th>\n",
       "      <th>attributes</th>\n",
       "      <th>categories</th>\n",
       "      <th>hours</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>156634</th>\n",
       "      <td>MW4yO70jP70b9uNjZF2OVw</td>\n",
       "      <td>Telegramme Prints</td>\n",
       "      <td>Leslieville</td>\n",
       "      <td>1103 Queen Street E</td>\n",
       "      <td>Toronto</td>\n",
       "      <td>ON</td>\n",
       "      <td>M4M 1K7</td>\n",
       "      <td>43.661834</td>\n",
       "      <td>-79.335638</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>{'BusinessParking': {'garage': False, 'street'...</td>\n",
       "      <td>['Art Galleries', 'Flowers &amp; Gifts', 'Event Pl...</td>\n",
       "      <td>{'Tuesday': '12:00-18:00', 'Friday': '12:00-18...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156635</th>\n",
       "      <td>9QtHDX71JnaQbmzWWVbi-A</td>\n",
       "      <td>Quark's Bar &amp; Restaurant</td>\n",
       "      <td>Eastside</td>\n",
       "      <td>Star Trek The Experience, 3000 Paradise Rd</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>NV</td>\n",
       "      <td>89109</td>\n",
       "      <td>36.137112</td>\n",
       "      <td>-115.152919</td>\n",
       "      <td>4.0</td>\n",
       "      <td>42</td>\n",
       "      <td>0</td>\n",
       "      <td>{'GoodForMeal': {'dessert': False, 'latenight'...</td>\n",
       "      <td>['American (New)', 'Restaurants']</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156636</th>\n",
       "      <td>pj97DoTfO6FcJpW9PnyWtQ</td>\n",
       "      <td>Bill's Bimmers Bmw Service</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1833 E 3rd St</td>\n",
       "      <td>Tempe</td>\n",
       "      <td>AZ</td>\n",
       "      <td>85281</td>\n",
       "      <td>33.426949</td>\n",
       "      <td>-111.907623</td>\n",
       "      <td>3.5</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>{}</td>\n",
       "      <td>['Auto Repair', 'Automotive']</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156637</th>\n",
       "      <td>O6BhCUuMA6sp0N24oEIjfw</td>\n",
       "      <td>Black Isle wildlife park</td>\n",
       "      <td>NaN</td>\n",
       "      <td>The Croft Drumsmittal</td>\n",
       "      <td>Inverness</td>\n",
       "      <td>HLD</td>\n",
       "      <td>IV1 1XF</td>\n",
       "      <td>57.519612</td>\n",
       "      <td>-4.260682</td>\n",
       "      <td>3.5</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>{}</td>\n",
       "      <td>['Zoos', 'Active Life']</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156638</th>\n",
       "      <td>scMIE4jyGp7FkWrMKAgjxA</td>\n",
       "      <td>Walmart</td>\n",
       "      <td>NaN</td>\n",
       "      <td>540 Marks St</td>\n",
       "      <td>Henderson</td>\n",
       "      <td>NV</td>\n",
       "      <td>89014</td>\n",
       "      <td>36.060708</td>\n",
       "      <td>-115.033241</td>\n",
       "      <td>2.5</td>\n",
       "      <td>42</td>\n",
       "      <td>0</td>\n",
       "      <td>{'BusinessAcceptsCreditCards': True, 'Restaura...</td>\n",
       "      <td>['Fashion', 'Shopping', 'Food', 'Department St...</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   business_id                        name neighborhood  \\\n",
       "156634  MW4yO70jP70b9uNjZF2OVw           Telegramme Prints  Leslieville   \n",
       "156635  9QtHDX71JnaQbmzWWVbi-A    Quark's Bar & Restaurant     Eastside   \n",
       "156636  pj97DoTfO6FcJpW9PnyWtQ  Bill's Bimmers Bmw Service          NaN   \n",
       "156637  O6BhCUuMA6sp0N24oEIjfw    Black Isle wildlife park          NaN   \n",
       "156638  scMIE4jyGp7FkWrMKAgjxA                     Walmart          NaN   \n",
       "\n",
       "                                           address       city state  \\\n",
       "156634                         1103 Queen Street E    Toronto    ON   \n",
       "156635  Star Trek The Experience, 3000 Paradise Rd  Las Vegas    NV   \n",
       "156636                               1833 E 3rd St      Tempe    AZ   \n",
       "156637                       The Croft Drumsmittal  Inverness   HLD   \n",
       "156638                                540 Marks St  Henderson    NV   \n",
       "\n",
       "       postal_code   latitude   longitude  stars  review_count  is_open  \\\n",
       "156634     M4M 1K7  43.661834  -79.335638    5.0             4        0   \n",
       "156635       89109  36.137112 -115.152919    4.0            42        0   \n",
       "156636       85281  33.426949 -111.907623    3.5             3        1   \n",
       "156637     IV1 1XF  57.519612   -4.260682    3.5             8        1   \n",
       "156638       89014  36.060708 -115.033241    2.5            42        0   \n",
       "\n",
       "                                               attributes  \\\n",
       "156634  {'BusinessParking': {'garage': False, 'street'...   \n",
       "156635  {'GoodForMeal': {'dessert': False, 'latenight'...   \n",
       "156636                                                 {}   \n",
       "156637                                                 {}   \n",
       "156638  {'BusinessAcceptsCreditCards': True, 'Restaura...   \n",
       "\n",
       "                                               categories  \\\n",
       "156634  ['Art Galleries', 'Flowers & Gifts', 'Event Pl...   \n",
       "156635                  ['American (New)', 'Restaurants']   \n",
       "156636                      ['Auto Repair', 'Automotive']   \n",
       "156637                            ['Zoos', 'Active Life']   \n",
       "156638  ['Fashion', 'Shopping', 'Food', 'Department St...   \n",
       "\n",
       "                                                    hours  \n",
       "156634  {'Tuesday': '12:00-18:00', 'Friday': '12:00-18...  \n",
       "156635                                                 {}  \n",
       "156636                                                 {}  \n",
       "156637                                                 {}  \n",
       "156638                                                 {}  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 out of 156638 have been already processed...\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda/lib/python3.6/site-packages/pandas/core/indexing.py:194: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self._setitem_with_indexer(indexer, value)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5000 out of 156638 have been already processed...\n",
      "10000 out of 156638 have been already processed...\n",
      "15000 out of 156638 have been already processed...\n",
      "20000 out of 156638 have been already processed...\n",
      "25000 out of 156638 have been already processed...\n",
      "30000 out of 156638 have been already processed...\n",
      "35000 out of 156638 have been already processed...\n",
      "40000 out of 156638 have been already processed...\n",
      "45000 out of 156638 have been already processed...\n",
      "50000 out of 156638 have been already processed...\n",
      "55000 out of 156638 have been already processed...\n",
      "60000 out of 156638 have been already processed...\n",
      "65000 out of 156638 have been already processed...\n",
      "70000 out of 156638 have been already processed...\n",
      "75000 out of 156638 have been already processed...\n",
      "80000 out of 156638 have been already processed...\n",
      "85000 out of 156638 have been already processed...\n",
      "90000 out of 156638 have been already processed...\n",
      "95000 out of 156638 have been already processed...\n",
      "100000 out of 156638 have been already processed...\n",
      "105000 out of 156638 have been already processed...\n",
      "110000 out of 156638 have been already processed...\n",
      "115000 out of 156638 have been already processed...\n",
      "120000 out of 156638 have been already processed...\n",
      "125000 out of 156638 have been already processed...\n",
      "130000 out of 156638 have been already processed...\n",
      "135000 out of 156638 have been already processed...\n",
      "140000 out of 156638 have been already processed...\n",
      "145000 out of 156638 have been already processed...\n",
      "150000 out of 156638 have been already processed...\n",
      "155000 out of 156638 have been already processed...\n"
     ]
    }
   ],
   "source": [
    "# first get only restaurant\n",
    "df['is_restaurant'] = 0\n",
    "for i in range(len(df)):\n",
    "    if 'Restaurants' in df.iloc[i].categories:\n",
    "        df.is_restaurant.iloc[i] = 1\n",
    "    if i % 5000 == 0:\n",
    "        print(i, \"out of 156638 have been already processed...\")\n",
    "df.to_csv('is_restaurant.txt', sep='\\t', encoding='utf-8', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_table('is_restaurant.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\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>name</th>\n",
       "      <th>neighborhood</th>\n",
       "      <th>address</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>stars</th>\n",
       "      <th>review_count</th>\n",
       "      <th>is_open</th>\n",
       "      <th>attributes</th>\n",
       "      <th>categories</th>\n",
       "      <th>hours</th>\n",
       "      <th>is_restaurant</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>156634</th>\n",
       "      <td>MW4yO70jP70b9uNjZF2OVw</td>\n",
       "      <td>Telegramme Prints</td>\n",
       "      <td>Leslieville</td>\n",
       "      <td>1103 Queen Street E</td>\n",
       "      <td>Toronto</td>\n",
       "      <td>ON</td>\n",
       "      <td>M4M 1K7</td>\n",
       "      <td>43.661834</td>\n",
       "      <td>-79.335638</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>{'BusinessParking': {'garage': False, 'street'...</td>\n",
       "      <td>['Art Galleries', 'Flowers &amp; Gifts', 'Event Pl...</td>\n",
       "      <td>{'Tuesday': '12:00-18:00', 'Friday': '12:00-18...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156635</th>\n",
       "      <td>9QtHDX71JnaQbmzWWVbi-A</td>\n",
       "      <td>Quark's Bar &amp; Restaurant</td>\n",
       "      <td>Eastside</td>\n",
       "      <td>Star Trek The Experience, 3000 Paradise Rd</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>NV</td>\n",
       "      <td>89109</td>\n",
       "      <td>36.137112</td>\n",
       "      <td>-115.152919</td>\n",
       "      <td>4.0</td>\n",
       "      <td>42</td>\n",
       "      <td>0</td>\n",
       "      <td>{'GoodForMeal': {'dessert': False, 'latenight'...</td>\n",
       "      <td>['American (New)', 'Restaurants']</td>\n",
       "      <td>{}</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156636</th>\n",
       "      <td>pj97DoTfO6FcJpW9PnyWtQ</td>\n",
       "      <td>Bill's Bimmers Bmw Service</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1833 E 3rd St</td>\n",
       "      <td>Tempe</td>\n",
       "      <td>AZ</td>\n",
       "      <td>85281</td>\n",
       "      <td>33.426949</td>\n",
       "      <td>-111.907623</td>\n",
       "      <td>3.5</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>{}</td>\n",
       "      <td>['Auto Repair', 'Automotive']</td>\n",
       "      <td>{}</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156637</th>\n",
       "      <td>O6BhCUuMA6sp0N24oEIjfw</td>\n",
       "      <td>Black Isle wildlife park</td>\n",
       "      <td>NaN</td>\n",
       "      <td>The Croft Drumsmittal</td>\n",
       "      <td>Inverness</td>\n",
       "      <td>HLD</td>\n",
       "      <td>IV1 1XF</td>\n",
       "      <td>57.519612</td>\n",
       "      <td>-4.260682</td>\n",
       "      <td>3.5</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>{}</td>\n",
       "      <td>['Zoos', 'Active Life']</td>\n",
       "      <td>{}</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156638</th>\n",
       "      <td>scMIE4jyGp7FkWrMKAgjxA</td>\n",
       "      <td>Walmart</td>\n",
       "      <td>NaN</td>\n",
       "      <td>540 Marks St</td>\n",
       "      <td>Henderson</td>\n",
       "      <td>NV</td>\n",
       "      <td>89014</td>\n",
       "      <td>36.060708</td>\n",
       "      <td>-115.033241</td>\n",
       "      <td>2.5</td>\n",
       "      <td>42</td>\n",
       "      <td>0</td>\n",
       "      <td>{'BusinessAcceptsCreditCards': True, 'Restaura...</td>\n",
       "      <td>['Fashion', 'Shopping', 'Food', 'Department St...</td>\n",
       "      <td>{}</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   business_id                        name neighborhood  \\\n",
       "156634  MW4yO70jP70b9uNjZF2OVw           Telegramme Prints  Leslieville   \n",
       "156635  9QtHDX71JnaQbmzWWVbi-A    Quark's Bar & Restaurant     Eastside   \n",
       "156636  pj97DoTfO6FcJpW9PnyWtQ  Bill's Bimmers Bmw Service          NaN   \n",
       "156637  O6BhCUuMA6sp0N24oEIjfw    Black Isle wildlife park          NaN   \n",
       "156638  scMIE4jyGp7FkWrMKAgjxA                     Walmart          NaN   \n",
       "\n",
       "                                           address       city state  \\\n",
       "156634                         1103 Queen Street E    Toronto    ON   \n",
       "156635  Star Trek The Experience, 3000 Paradise Rd  Las Vegas    NV   \n",
       "156636                               1833 E 3rd St      Tempe    AZ   \n",
       "156637                       The Croft Drumsmittal  Inverness   HLD   \n",
       "156638                                540 Marks St  Henderson    NV   \n",
       "\n",
       "       postal_code   latitude   longitude  stars  review_count  is_open  \\\n",
       "156634     M4M 1K7  43.661834  -79.335638    5.0             4        0   \n",
       "156635       89109  36.137112 -115.152919    4.0            42        0   \n",
       "156636       85281  33.426949 -111.907623    3.5             3        1   \n",
       "156637     IV1 1XF  57.519612   -4.260682    3.5             8        1   \n",
       "156638       89014  36.060708 -115.033241    2.5            42        0   \n",
       "\n",
       "                                               attributes  \\\n",
       "156634  {'BusinessParking': {'garage': False, 'street'...   \n",
       "156635  {'GoodForMeal': {'dessert': False, 'latenight'...   \n",
       "156636                                                 {}   \n",
       "156637                                                 {}   \n",
       "156638  {'BusinessAcceptsCreditCards': True, 'Restaura...   \n",
       "\n",
       "                                               categories  \\\n",
       "156634  ['Art Galleries', 'Flowers & Gifts', 'Event Pl...   \n",
       "156635                  ['American (New)', 'Restaurants']   \n",
       "156636                      ['Auto Repair', 'Automotive']   \n",
       "156637                            ['Zoos', 'Active Life']   \n",
       "156638  ['Fashion', 'Shopping', 'Food', 'Department St...   \n",
       "\n",
       "                                                    hours  is_restaurant  \n",
       "156634  {'Tuesday': '12:00-18:00', 'Friday': '12:00-18...              0  \n",
       "156635                                                 {}              1  \n",
       "156636                                                 {}              0  \n",
       "156637                                                 {}              0  \n",
       "156638                                                 {}              0  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# only get restaurant\n",
    "df = df[df['is_restaurant']==1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\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>name</th>\n",
       "      <th>neighborhood</th>\n",
       "      <th>address</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>stars</th>\n",
       "      <th>review_count</th>\n",
       "      <th>is_open</th>\n",
       "      <th>attributes</th>\n",
       "      <th>categories</th>\n",
       "      <th>hours</th>\n",
       "      <th>is_restaurant</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>156625</th>\n",
       "      <td>7IQoE-EXnRCBUzaVHYlhmw</td>\n",
       "      <td>Papa John's Pizza</td>\n",
       "      <td>University City</td>\n",
       "      <td>9335 N Tryon St, Ste 102</td>\n",
       "      <td>Charlotte</td>\n",
       "      <td>NC</td>\n",
       "      <td>28262</td>\n",
       "      <td>35.313384</td>\n",
       "      <td>-80.743157</td>\n",
       "      <td>2.0</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>{'RestaurantsTableService': False, 'HasTV': Fa...</td>\n",
       "      <td>['Pizza', 'Restaurants']</td>\n",
       "      <td>{'Monday': '10:00-1:00', 'Tuesday': '10:00-1:0...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156626</th>\n",
       "      <td>KcFfciiA1lmui6r_h5OwTg</td>\n",
       "      <td>Steve's Deli</td>\n",
       "      <td>Strip District</td>\n",
       "      <td>2401 Penn Ave</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15222</td>\n",
       "      <td>40.453845</td>\n",
       "      <td>-79.980478</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>{'RestaurantsTableService': False, 'GoodForMea...</td>\n",
       "      <td>['Restaurants', 'Delis', 'Sandwiches', 'Food S...</td>\n",
       "      <td>{'Sunday': '10:00-16:00', 'Friday': '10:00-16:...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156631</th>\n",
       "      <td>HzUxQ1WpeNmeecXN-HPlPw</td>\n",
       "      <td>Bubble Thé Resto-Lounge</td>\n",
       "      <td>Ville-Marie</td>\n",
       "      <td>1925 St.Catherine W</td>\n",
       "      <td>Montréal</td>\n",
       "      <td>QC</td>\n",
       "      <td>H3H 1M3</td>\n",
       "      <td>45.492610</td>\n",
       "      <td>-73.580954</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>{'GoodForMeal': {'dessert': False, 'latenight'...</td>\n",
       "      <td>['Food', 'Taiwanese', 'Restaurants', 'Coffee &amp;...</td>\n",
       "      <td>{'Monday': '11:00-0:00', 'Tuesday': '11:00-0:0...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156632</th>\n",
       "      <td>hdNOL59QqvGlg70E4yHrsQ</td>\n",
       "      <td>Gourmet Burger Co</td>\n",
       "      <td>Mount Pleasant and Davisville</td>\n",
       "      <td>2419 Yonge Street</td>\n",
       "      <td>Toronto</td>\n",
       "      <td>ON</td>\n",
       "      <td>M4P 2E7</td>\n",
       "      <td>43.710603</td>\n",
       "      <td>-79.399053</td>\n",
       "      <td>3.0</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>{'GoodForMeal': {'dessert': False, 'latenight'...</td>\n",
       "      <td>['Restaurants', 'Burgers']</td>\n",
       "      <td>{'Monday': '11:30-21:00', 'Tuesday': '11:30-21...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156635</th>\n",
       "      <td>9QtHDX71JnaQbmzWWVbi-A</td>\n",
       "      <td>Quark's Bar &amp; Restaurant</td>\n",
       "      <td>Eastside</td>\n",
       "      <td>Star Trek The Experience, 3000 Paradise Rd</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>NV</td>\n",
       "      <td>89109</td>\n",
       "      <td>36.137112</td>\n",
       "      <td>-115.152919</td>\n",
       "      <td>4.0</td>\n",
       "      <td>42</td>\n",
       "      <td>0</td>\n",
       "      <td>{'GoodForMeal': {'dessert': False, 'latenight'...</td>\n",
       "      <td>['American (New)', 'Restaurants']</td>\n",
       "      <td>{}</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   business_id                      name  \\\n",
       "156625  7IQoE-EXnRCBUzaVHYlhmw         Papa John's Pizza   \n",
       "156626  KcFfciiA1lmui6r_h5OwTg              Steve's Deli   \n",
       "156631  HzUxQ1WpeNmeecXN-HPlPw   Bubble Thé Resto-Lounge   \n",
       "156632  hdNOL59QqvGlg70E4yHrsQ         Gourmet Burger Co   \n",
       "156635  9QtHDX71JnaQbmzWWVbi-A  Quark's Bar & Restaurant   \n",
       "\n",
       "                         neighborhood  \\\n",
       "156625                University City   \n",
       "156626                 Strip District   \n",
       "156631                    Ville-Marie   \n",
       "156632  Mount Pleasant and Davisville   \n",
       "156635                       Eastside   \n",
       "\n",
       "                                           address        city state  \\\n",
       "156625                    9335 N Tryon St, Ste 102   Charlotte    NC   \n",
       "156626                               2401 Penn Ave  Pittsburgh    PA   \n",
       "156631                         1925 St.Catherine W    Montréal    QC   \n",
       "156632                           2419 Yonge Street     Toronto    ON   \n",
       "156635  Star Trek The Experience, 3000 Paradise Rd   Las Vegas    NV   \n",
       "\n",
       "       postal_code   latitude   longitude  stars  review_count  is_open  \\\n",
       "156625       28262  35.313384  -80.743157    2.0             9        1   \n",
       "156626       15222  40.453845  -79.980478    4.5             3        0   \n",
       "156631     H3H 1M3  45.492610  -73.580954    3.0             8        0   \n",
       "156632     M4P 2E7  43.710603  -79.399053    3.0            22        0   \n",
       "156635       89109  36.137112 -115.152919    4.0            42        0   \n",
       "\n",
       "                                               attributes  \\\n",
       "156625  {'RestaurantsTableService': False, 'HasTV': Fa...   \n",
       "156626  {'RestaurantsTableService': False, 'GoodForMea...   \n",
       "156631  {'GoodForMeal': {'dessert': False, 'latenight'...   \n",
       "156632  {'GoodForMeal': {'dessert': False, 'latenight'...   \n",
       "156635  {'GoodForMeal': {'dessert': False, 'latenight'...   \n",
       "\n",
       "                                               categories  \\\n",
       "156625                           ['Pizza', 'Restaurants']   \n",
       "156626  ['Restaurants', 'Delis', 'Sandwiches', 'Food S...   \n",
       "156631  ['Food', 'Taiwanese', 'Restaurants', 'Coffee &...   \n",
       "156632                         ['Restaurants', 'Burgers']   \n",
       "156635                  ['American (New)', 'Restaurants']   \n",
       "\n",
       "                                                    hours  is_restaurant  \n",
       "156625  {'Monday': '10:00-1:00', 'Tuesday': '10:00-1:0...              1  \n",
       "156626  {'Sunday': '10:00-16:00', 'Friday': '10:00-16:...              1  \n",
       "156631  {'Monday': '11:00-0:00', 'Tuesday': '11:00-0:0...              1  \n",
       "156632  {'Monday': '11:30-21:00', 'Tuesday': '11:30-21...              1  \n",
       "156635                                                 {}              1  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "51613"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "38657"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[df['is_open']==1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12956"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[df['is_open']==0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('is_restaurant.txt', sep='\\t', encoding='utf-8', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# then get corresponding review (match with business id)\n",
    "review = pd.read_table('/Users/luxiaopeng/Documents/aProject/Kim/data/review.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4736897"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(review)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "business_id = df[['business_id']]\n",
    "out = review.merge(business_id, on='business_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2927731"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(out)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "out.to_csv(\"restaurant_review.txt\", sep='\\t', encoding='utf-8', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "0330\n",
    "goal: balance closed and unclosed restaurant\n",
    "'''\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "busi = pd.read_table('restaurant_balanced.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12956"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(busi[busi['is_open']==0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "25912"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "is_closed = busi[busi['is_open'] == 0]\n",
    "is_open = busi[busi['is_open'] == 1].sample(len(is_closed))\n",
    "busi_balance = is_closed.append(is_open, ignore_index=True)\n",
    "len(busi_balance)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\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>name</th>\n",
       "      <th>neighborhood</th>\n",
       "      <th>address</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>stars</th>\n",
       "      <th>review_count</th>\n",
       "      <th>is_open</th>\n",
       "      <th>attributes</th>\n",
       "      <th>categories</th>\n",
       "      <th>hours</th>\n",
       "      <th>is_restaurant</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>25907</th>\n",
       "      <td>uJMy2VGq7HHW25PwROUEkg</td>\n",
       "      <td>Zum Löwen</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Stuttgarter Str. 1</td>\n",
       "      <td>Steinenbronn</td>\n",
       "      <td>BW</td>\n",
       "      <td>71144</td>\n",
       "      <td>48.660300</td>\n",
       "      <td>9.119350</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>{'DogsAllowed': False, 'BusinessParking': {'ga...</td>\n",
       "      <td>['Hotels', 'Meat Shops', 'Restaurants', 'Food'...</td>\n",
       "      <td>{'Monday': '17:00-23:00', 'Tuesday': '17:00-23...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25908</th>\n",
       "      <td>--Ni3oJ4VOqfOEu7Sj2Vzg</td>\n",
       "      <td>KFC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1245 Pearl Road</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>OH</td>\n",
       "      <td>44212</td>\n",
       "      <td>41.241900</td>\n",
       "      <td>-81.841100</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>{'GoodForMeal': {'dessert': False, 'latenight'...</td>\n",
       "      <td>['Chicken Wings', 'Fast Food', 'Restaurants']</td>\n",
       "      <td>{'Monday': '10:30-23:00', 'Tuesday': '10:30-23...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25909</th>\n",
       "      <td>BvXzUB7QcfCF1gcQwi9oJA</td>\n",
       "      <td>China II</td>\n",
       "      <td>NaN</td>\n",
       "      <td>757 Crossroads Plz</td>\n",
       "      <td>Fort Mill</td>\n",
       "      <td>SC</td>\n",
       "      <td>29708</td>\n",
       "      <td>35.059131</td>\n",
       "      <td>-80.989442</td>\n",
       "      <td>2.5</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>{'GoodForMeal': {'dessert': False, 'latenight'...</td>\n",
       "      <td>['Chinese', 'Restaurants']</td>\n",
       "      <td>{}</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25910</th>\n",
       "      <td>RPjBB_uhHQ4oxp39Jqm_HQ</td>\n",
       "      <td>Capriotti's Sandwich Shop</td>\n",
       "      <td>Anthem</td>\n",
       "      <td>11155 S Eastern Ave</td>\n",
       "      <td>Henderson</td>\n",
       "      <td>NV</td>\n",
       "      <td>89052</td>\n",
       "      <td>35.989740</td>\n",
       "      <td>-115.101585</td>\n",
       "      <td>3.5</td>\n",
       "      <td>55</td>\n",
       "      <td>1</td>\n",
       "      <td>{'RestaurantsTableService': False, 'GoodForMea...</td>\n",
       "      <td>['Sandwiches', 'Restaurants']</td>\n",
       "      <td>{'Monday': '10:00-21:00', 'Tuesday': '10:00-21...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25911</th>\n",
       "      <td>aait_GoXPpYOjS4Hgj74Iw</td>\n",
       "      <td>Nakato Japanese Steakhouse &amp; Sushi Bar</td>\n",
       "      <td>University City</td>\n",
       "      <td>3601 University Executive Park Dr</td>\n",
       "      <td>Charlotte</td>\n",
       "      <td>NC</td>\n",
       "      <td>28202</td>\n",
       "      <td>35.305635</td>\n",
       "      <td>-80.754232</td>\n",
       "      <td>3.5</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>{'GoodForMeal': {'dessert': False, 'latenight'...</td>\n",
       "      <td>['Sushi Bars', 'Japanese', 'Restaurants']</td>\n",
       "      <td>{}</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  business_id                                    name  \\\n",
       "25907  uJMy2VGq7HHW25PwROUEkg                               Zum Löwen   \n",
       "25908  --Ni3oJ4VOqfOEu7Sj2Vzg                                     KFC   \n",
       "25909  BvXzUB7QcfCF1gcQwi9oJA                                China II   \n",
       "25910  RPjBB_uhHQ4oxp39Jqm_HQ               Capriotti's Sandwich Shop   \n",
       "25911  aait_GoXPpYOjS4Hgj74Iw  Nakato Japanese Steakhouse & Sushi Bar   \n",
       "\n",
       "          neighborhood                            address          city state  \\\n",
       "25907              NaN                 Stuttgarter Str. 1  Steinenbronn    BW   \n",
       "25908              NaN                    1245 Pearl Road     Brunswick    OH   \n",
       "25909              NaN                 757 Crossroads Plz     Fort Mill    SC   \n",
       "25910           Anthem                11155 S Eastern Ave     Henderson    NV   \n",
       "25911  University City  3601 University Executive Park Dr     Charlotte    NC   \n",
       "\n",
       "      postal_code   latitude   longitude  stars  review_count  is_open  \\\n",
       "25907       71144  48.660300    9.119350    4.0             4        1   \n",
       "25908       44212  41.241900  -81.841100    2.0             6        1   \n",
       "25909       29708  35.059131  -80.989442    2.5             7        1   \n",
       "25910       89052  35.989740 -115.101585    3.5            55        1   \n",
       "25911       28202  35.305635  -80.754232    3.5             5        1   \n",
       "\n",
       "                                              attributes  \\\n",
       "25907  {'DogsAllowed': False, 'BusinessParking': {'ga...   \n",
       "25908  {'GoodForMeal': {'dessert': False, 'latenight'...   \n",
       "25909  {'GoodForMeal': {'dessert': False, 'latenight'...   \n",
       "25910  {'RestaurantsTableService': False, 'GoodForMea...   \n",
       "25911  {'GoodForMeal': {'dessert': False, 'latenight'...   \n",
       "\n",
       "                                              categories  \\\n",
       "25907  ['Hotels', 'Meat Shops', 'Restaurants', 'Food'...   \n",
       "25908      ['Chicken Wings', 'Fast Food', 'Restaurants']   \n",
       "25909                         ['Chinese', 'Restaurants']   \n",
       "25910                      ['Sandwiches', 'Restaurants']   \n",
       "25911          ['Sushi Bars', 'Japanese', 'Restaurants']   \n",
       "\n",
       "                                                   hours  is_restaurant  \n",
       "25907  {'Monday': '17:00-23:00', 'Tuesday': '17:00-23...              1  \n",
       "25908  {'Monday': '10:30-23:00', 'Tuesday': '10:30-23...              1  \n",
       "25909                                                 {}              1  \n",
       "25910  {'Monday': '10:00-21:00', 'Tuesday': '10:00-21...              1  \n",
       "25911                                                 {}              1  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "busi_balance.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2728: DtypeWarning: Columns (0) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "# apply to review\n",
    "review = pd.read_table('restaurant_review.txt')\n",
    "business_id = busi_balance[['business_id']]\n",
    "out = review.merge(business_id, on='business_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "busi_balance.to_csv('restaurant_balanced.txt', sep = '\\t', encoding= 'utf-8', index = False)\n",
    "# out.to_csv('is_restaurant_balanced.txt', sep = '\\t', encoding= 'utf-8', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}