src/02_Pet_Stores_and_Services.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "from config import client_id, api_key\n",
    "\n",
    "import pandas as pd\n",
    "import requests\n",
    "import json\n",
    "import time\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Yelp Pet Stores and Pet Services in NYC (ETL): Extract"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#function to parse Yelp Fusion API response for a list of Yelp businesses\n",
    "def parse_response(response):\n",
    "    response_json = response.json()\n",
    "    businesses = []\n",
    "    \n",
    "    for business in response_json['businesses']:\n",
    "        business_json = {}\n",
    "        categories = []\n",
    "        \n",
    "        for category in business['categories']:\n",
    "            categories.append(category['title'])\n",
    "        \n",
    "        business_json['categories'] = categories\n",
    "        business_json['id'] = business['id']\n",
    "        business_json['name'] = business['name']\n",
    "        business_json['is_closed'] = business['is_closed']\n",
    "        business_json['review_count'] = business['review_count']\n",
    "        business_json['rating'] = business['rating']\n",
    "        business_json['zip_code'] = business['location']['zip_code']\n",
    "        \n",
    "        businesses.append(business_json)\n",
    "        \n",
    "    return businesses\n",
    "    \n",
    "#function to call Yelp Fusion API with given search term, location and max search results\n",
    "def call_yelp(term, location, search_max):\n",
    "    search_results = []\n",
    "    search_limit = 50\n",
    "    search_calls = int(search_max / search_limit)\n",
    "    \n",
    "    for i in range(search_calls):\n",
    "        search_offset = i * search_limit\n",
    "        url = 'https://api.yelp.com/v3/businesses/search'\n",
    "        headers = {'Authorization': 'Bearer {}'.format(api_key),}\n",
    "        url_params = {'term': term.replace(' ', '+'),\n",
    "                      'location': location.replace(' ', '+'),\n",
    "                      'limit': search_limit,\n",
    "                      'offset': search_offset\n",
    "                     }\n",
    "        \n",
    "        response = requests.get(url, headers=headers, params=url_params)\n",
    "        print('term: {}, offset: {}, response: {}'.format(term, search_offset, response))\n",
    "        \n",
    "        search_results.extend(parse_response(response))\n",
    "        time.sleep(2)\n",
    "        \n",
    "    return search_results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "term: Pet Stores, offset: 0, response: <Response [200]>\n",
      "term: Pet Stores, offset: 50, response: <Response [200]>\n",
      "term: Pet Stores, offset: 100, response: <Response [200]>\n",
      "term: Pet Stores, offset: 150, response: <Response [200]>\n",
      "term: Pet Stores, offset: 200, response: <Response [200]>\n",
      "term: Pet Stores, offset: 250, response: <Response [200]>\n",
      "term: Pet Stores, offset: 300, response: <Response [200]>\n",
      "term: Pet Stores, offset: 350, response: <Response [200]>\n",
      "term: Pet Stores, offset: 400, response: <Response [200]>\n",
      "term: Pet Stores, offset: 450, response: <Response [200]>\n",
      "term: Pet Stores, offset: 500, response: <Response [200]>\n",
      "term: Pet Stores, offset: 550, response: <Response [200]>\n",
      "term: Pet Stores, offset: 600, response: <Response [200]>\n",
      "term: Pet Stores, offset: 650, response: <Response [200]>\n",
      "term: Pet Stores, offset: 700, response: <Response [200]>\n",
      "term: Pet Stores, offset: 750, response: <Response [200]>\n",
      "term: Pet Stores, offset: 800, response: <Response [200]>\n",
      "term: Pet Stores, offset: 850, response: <Response [200]>\n",
      "term: Pet Stores, offset: 900, response: <Response [200]>\n",
      "term: Pet Stores, offset: 950, response: <Response [200]>\n"
     ]
    }
   ],
   "source": [
    "#pull and save 1,000 (max) pet stores in NYC\n",
    "pet_stores = 'Pet Stores'\n",
    "location = 'New York, NY'\n",
    "search_max = 1000\n",
    "\n",
    "yelp_pet_stores = call_yelp(pet_stores, location, search_max)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "term: Pet Services, offset: 0, response: <Response [200]>\n",
      "term: Pet Services, offset: 50, response: <Response [200]>\n",
      "term: Pet Services, offset: 100, response: <Response [200]>\n",
      "term: Pet Services, offset: 150, response: <Response [200]>\n",
      "term: Pet Services, offset: 200, response: <Response [200]>\n",
      "term: Pet Services, offset: 250, response: <Response [200]>\n",
      "term: Pet Services, offset: 300, response: <Response [200]>\n",
      "term: Pet Services, offset: 350, response: <Response [200]>\n",
      "term: Pet Services, offset: 400, response: <Response [200]>\n",
      "term: Pet Services, offset: 450, response: <Response [200]>\n",
      "term: Pet Services, offset: 500, response: <Response [200]>\n",
      "term: Pet Services, offset: 550, response: <Response [200]>\n",
      "term: Pet Services, offset: 600, response: <Response [200]>\n",
      "term: Pet Services, offset: 650, response: <Response [200]>\n",
      "term: Pet Services, offset: 700, response: <Response [200]>\n",
      "term: Pet Services, offset: 750, response: <Response [200]>\n",
      "term: Pet Services, offset: 800, response: <Response [200]>\n",
      "term: Pet Services, offset: 850, response: <Response [200]>\n",
      "term: Pet Services, offset: 900, response: <Response [200]>\n",
      "term: Pet Services, offset: 950, response: <Response [200]>\n"
     ]
    }
   ],
   "source": [
    "#pull and save 1,000 (max) pet services in NYC\n",
    "pet_services = 'Pet Services'\n",
    "location = 'New York, NY'\n",
    "search_max = 1000\n",
    "\n",
    "yelp_pet_services = call_yelp(pet_services, location, search_max)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Yelp Pet Stores and Pet Services in NYC (ETL): Transform"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "scrolled": true
   },
   "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>categories</th>\n",
       "      <th>id</th>\n",
       "      <th>is_closed</th>\n",
       "      <th>name</th>\n",
       "      <th>rating</th>\n",
       "      <th>review_count</th>\n",
       "      <th>zip_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[Pet Stores]</td>\n",
       "      <td>gxMLTap163ma5RJNK0EiNg</td>\n",
       "      <td>False</td>\n",
       "      <td>Hoboken Pet</td>\n",
       "      <td>4.5</td>\n",
       "      <td>30</td>\n",
       "      <td>07030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[Pet Stores]</td>\n",
       "      <td>eTqmm-qOh4nStAcR6Ezn7A</td>\n",
       "      <td>False</td>\n",
       "      <td>Zee.Dog - SoHo</td>\n",
       "      <td>4.5</td>\n",
       "      <td>14</td>\n",
       "      <td>10013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>[Pet Stores, Pet Groomers]</td>\n",
       "      <td>tYZEFqmbGEA0cN6SGz7Swg</td>\n",
       "      <td>False</td>\n",
       "      <td>Petropolis</td>\n",
       "      <td>4.5</td>\n",
       "      <td>76</td>\n",
       "      <td>10006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>[Pet Stores]</td>\n",
       "      <td>YtJXZdh3lnuH2JuDtXBqqQ</td>\n",
       "      <td>False</td>\n",
       "      <td>NYC Pet</td>\n",
       "      <td>2.5</td>\n",
       "      <td>15</td>\n",
       "      <td>11211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>[Pet Stores]</td>\n",
       "      <td>IMw1Jr7T96is-7lccQUT3w</td>\n",
       "      <td>False</td>\n",
       "      <td>Petopia</td>\n",
       "      <td>4.0</td>\n",
       "      <td>52</td>\n",
       "      <td>10009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   categories                      id  is_closed  \\\n",
       "0                [Pet Stores]  gxMLTap163ma5RJNK0EiNg      False   \n",
       "1                [Pet Stores]  eTqmm-qOh4nStAcR6Ezn7A      False   \n",
       "2  [Pet Stores, Pet Groomers]  tYZEFqmbGEA0cN6SGz7Swg      False   \n",
       "3                [Pet Stores]  YtJXZdh3lnuH2JuDtXBqqQ      False   \n",
       "4                [Pet Stores]  IMw1Jr7T96is-7lccQUT3w      False   \n",
       "\n",
       "             name  rating  review_count zip_code  \n",
       "0     Hoboken Pet     4.5            30    07030  \n",
       "1  Zee.Dog - SoHo     4.5            14    10013  \n",
       "2      Petropolis     4.5            76    10006  \n",
       "3         NYC Pet     2.5            15    11211  \n",
       "4         Petopia     4.0            52    10009  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create dataframe of pet stores\n",
    "pet_stores = pd.DataFrame.from_dict(yelp_pet_stores)\n",
    "pet_stores.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "scrolled": true
   },
   "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>categories</th>\n",
       "      <th>id</th>\n",
       "      <th>is_closed</th>\n",
       "      <th>name</th>\n",
       "      <th>rating</th>\n",
       "      <th>review_count</th>\n",
       "      <th>zip_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[Dog Walkers, Pet Sitting]</td>\n",
       "      <td>81v--_Id0CEOEPNe8AwhmA</td>\n",
       "      <td>False</td>\n",
       "      <td>Happy Pants</td>\n",
       "      <td>5.0</td>\n",
       "      <td>33</td>\n",
       "      <td>10014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[Dog Walkers, Pet Sitting]</td>\n",
       "      <td>2I0mcwFT6FZswHhb6ldaHw</td>\n",
       "      <td>False</td>\n",
       "      <td>Pet Sitting Pod</td>\n",
       "      <td>5.0</td>\n",
       "      <td>19</td>\n",
       "      <td>10002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>[Pet Sitting]</td>\n",
       "      <td>60kRIrCCxQCns1gIH-0CoQ</td>\n",
       "      <td>False</td>\n",
       "      <td>Paws &amp; Relax</td>\n",
       "      <td>4.5</td>\n",
       "      <td>33</td>\n",
       "      <td>10009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>[Pet Groomers, Pet Stores, Dog Walkers]</td>\n",
       "      <td>FUNFrflWo_F0sLeUM3bCgQ</td>\n",
       "      <td>False</td>\n",
       "      <td>Le Pet Spa</td>\n",
       "      <td>5.0</td>\n",
       "      <td>39</td>\n",
       "      <td>10280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>[Dog Walkers, Pet Sitting]</td>\n",
       "      <td>VbfjSxyG0VTe4MYUIKIfAQ</td>\n",
       "      <td>False</td>\n",
       "      <td>Frida's Premium Pet Services</td>\n",
       "      <td>5.0</td>\n",
       "      <td>26</td>\n",
       "      <td>07302</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                categories                      id  is_closed  \\\n",
       "0               [Dog Walkers, Pet Sitting]  81v--_Id0CEOEPNe8AwhmA      False   \n",
       "1               [Dog Walkers, Pet Sitting]  2I0mcwFT6FZswHhb6ldaHw      False   \n",
       "2                            [Pet Sitting]  60kRIrCCxQCns1gIH-0CoQ      False   \n",
       "3  [Pet Groomers, Pet Stores, Dog Walkers]  FUNFrflWo_F0sLeUM3bCgQ      False   \n",
       "4               [Dog Walkers, Pet Sitting]  VbfjSxyG0VTe4MYUIKIfAQ      False   \n",
       "\n",
       "                           name  rating  review_count zip_code  \n",
       "0                   Happy Pants     5.0            33    10014  \n",
       "1               Pet Sitting Pod     5.0            19    10002  \n",
       "2                  Paws & Relax     4.5            33    10009  \n",
       "3                    Le Pet Spa     5.0            39    10280  \n",
       "4  Frida's Premium Pet Services     5.0            26    07302  "
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create dataframe of pet services\n",
    "pet_services = pd.DataFrame.from_dict(yelp_pet_services)\n",
    "pet_services.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Accessories',\n",
       " 'Acupuncture',\n",
       " 'Animal Shelters',\n",
       " 'Aquarium Services',\n",
       " 'Aquariums',\n",
       " 'Art Galleries',\n",
       " 'Beer, Wine & Spirits',\n",
       " 'Bird Shops',\n",
       " 'Cafes',\n",
       " 'Child Care & Day Care',\n",
       " \"Children's Clothing\",\n",
       " 'Coffee & Tea',\n",
       " 'Community Service/Non-Profit',\n",
       " 'Couriers & Delivery Services',\n",
       " 'Cremation Services',\n",
       " 'Doctors',\n",
       " 'Dog Parks',\n",
       " 'Dog Walkers',\n",
       " 'Drugstores',\n",
       " 'Emergency Pet Hospital',\n",
       " 'Event Photography',\n",
       " 'Gift Shops',\n",
       " 'Grocery',\n",
       " 'Handyman',\n",
       " 'Holistic Animal Care',\n",
       " 'Home & Garden',\n",
       " 'Home Cleaning',\n",
       " 'Home Organization',\n",
       " 'Horse Boarding',\n",
       " 'Horseback Riding',\n",
       " 'Hospice',\n",
       " 'House Sitters',\n",
       " 'Ice Cream & Frozen Yogurt',\n",
       " 'Insurance',\n",
       " 'Landscaping',\n",
       " 'Lighting Fixtures & Equipment',\n",
       " 'Livestock Feed & Supply',\n",
       " 'Local Fish Stores',\n",
       " 'Office Cleaning',\n",
       " 'Personal Assistants',\n",
       " 'Pet Adoption',\n",
       " 'Pet Boarding',\n",
       " 'Pet Breeders',\n",
       " 'Pet Cremation Services',\n",
       " 'Pet Groomers',\n",
       " 'Pet Insurance',\n",
       " 'Pet Photography',\n",
       " 'Pet Services',\n",
       " 'Pet Sitting',\n",
       " 'Pet Stores',\n",
       " 'Pet Training',\n",
       " 'Pet Transportation',\n",
       " 'Pet Waste Removal',\n",
       " 'Pets',\n",
       " 'Pharmacy',\n",
       " 'Propane',\n",
       " 'Reiki',\n",
       " 'Reptile Shops',\n",
       " 'Session Photography',\n",
       " 'Summer Camps',\n",
       " 'Utilities',\n",
       " 'Veterinarians']"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#create list of unique pet store and service categoories\n",
    "pet_categories = []\n",
    "\n",
    "for row in range(pet_stores.shape[0]):\n",
    "    pet_categories.extend(pet_stores['categories'][row])\n",
    "    \n",
    "for row in range(pet_services.shape[0]):\n",
    "    pet_categories.extend(pet_services['categories'][row])\n",
    "    \n",
    "pet_categories = sorted(list(set(pet_categories)))\n",
    "pet_categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3274\n",
      "3274\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[49,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 48,\n",
       " 49,\n",
       " 47,\n",
       " 49,\n",
       " 47,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 50,\n",
       " 47,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 50,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 41,\n",
       " 44,\n",
       " 4,\n",
       " 61,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 0,\n",
       " 46,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 42,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 41,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 41,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 17,\n",
       " 49,\n",
       " 49,\n",
       " 48,\n",
       " 44,\n",
       " 44,\n",
       " 49,\n",
       " 50,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 48,\n",
       " 44,\n",
       " 49,\n",
       " 50,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 42,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 50,\n",
       " 44,\n",
       " 44,\n",
       " 17,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 50,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 17,\n",
       " 48,\n",
       " 49,\n",
       " 49,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 40,\n",
       " 2,\n",
       " 12,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 8,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 50,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 61,\n",
       " 49,\n",
       " 50,\n",
       " 44,\n",
       " 48,\n",
       " 44,\n",
       " 17,\n",
       " 49,\n",
       " 40,\n",
       " 44,\n",
       " 49,\n",
       " 44,\n",
       " 44,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 48,\n",
       " 49,\n",
       " 48,\n",
       " 44,\n",
       " 49,\n",
       " 48,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 17,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 40,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 41,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 47,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 48,\n",
       " 44,\n",
       " 49,\n",
       " 50,\n",
       " 40,\n",
       " 44,\n",
       " 17,\n",
       " 41,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 44,\n",
       " 17,\n",
       " 48,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 48,\n",
       " 44,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 50,\n",
       " 47,\n",
       " 49,\n",
       " 61,\n",
       " 49,\n",
       " 40,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 40,\n",
       " 50,\n",
       " 49,\n",
       " 44,\n",
       " 41,\n",
       " 2,\n",
       " 50,\n",
       " 40,\n",
       " 48,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 2,\n",
       " 44,\n",
       " 49,\n",
       " 41,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 48,\n",
       " 44,\n",
       " 49,\n",
       " 48,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 11,\n",
       " 48,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 44,\n",
       " 50,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 17,\n",
       " 48,\n",
       " 50,\n",
       " 49,\n",
       " 49,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 48,\n",
       " 44,\n",
       " 44,\n",
       " 49,\n",
       " 48,\n",
       " 48,\n",
       " 44,\n",
       " 50,\n",
       " 49,\n",
       " 61,\n",
       " 44,\n",
       " 44,\n",
       " 49,\n",
       " 50,\n",
       " 49,\n",
       " 44,\n",
       " 50,\n",
       " 50,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 44,\n",
       " 48,\n",
       " 44,\n",
       " 41,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 50,\n",
       " 44,\n",
       " 48,\n",
       " 44,\n",
       " 49,\n",
       " 48,\n",
       " 49,\n",
       " 3,\n",
       " 49,\n",
       " 61,\n",
       " 49,\n",
       " 11,\n",
       " 40,\n",
       " 44,\n",
       " 48,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 50,\n",
       " 44,\n",
       " 48,\n",
       " 44,\n",
       " 17,\n",
       " 48,\n",
       " 2,\n",
       " 40,\n",
       " 48,\n",
       " 44,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 50,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 48,\n",
       " 50,\n",
       " 44,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 10,\n",
       " 49,\n",
       " 49,\n",
       " 40,\n",
       " 44,\n",
       " 49,\n",
       " 3,\n",
       " 2,\n",
       " 40,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 48,\n",
       " 44,\n",
       " 49,\n",
       " 48,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 47,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 42,\n",
       " 48,\n",
       " 44,\n",
       " 49,\n",
       " 44,\n",
       " 42,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 50,\n",
       " 49,\n",
       " 49,\n",
       " 48,\n",
       " 44,\n",
       " 44,\n",
       " 50,\n",
       " 49,\n",
       " 49,\n",
       " 61,\n",
       " 61,\n",
       " 44,\n",
       " 50,\n",
       " 48,\n",
       " 50,\n",
       " 49,\n",
       " 49,\n",
       " 47,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 47,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 44,\n",
       " 41,\n",
       " 49,\n",
       " 44,\n",
       " 3,\n",
       " 44,\n",
       " 3,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 42,\n",
       " 44,\n",
       " 49,\n",
       " 50,\n",
       " 49,\n",
       " 49,\n",
       " 48,\n",
       " 61,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 48,\n",
       " 49,\n",
       " 49,\n",
       " 50,\n",
       " 44,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 48,\n",
       " 44,\n",
       " 17,\n",
       " 44,\n",
       " 49,\n",
       " 61,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 42,\n",
       " 49,\n",
       " 44,\n",
       " 48,\n",
       " 17,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 44,\n",
       " 49,\n",
       " 44,\n",
       " 48,\n",
       " 49,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 48,\n",
       " 44,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 2,\n",
       " 40,\n",
       " 12,\n",
       " 48,\n",
       " 44,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 48,\n",
       " 44,\n",
       " 17,\n",
       " 48,\n",
       " 50,\n",
       " 49,\n",
       " 44,\n",
       " 17,\n",
       " 41,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 61,\n",
       " 19,\n",
       " 61,\n",
       " 44,\n",
       " 40,\n",
       " 61,\n",
       " 49,\n",
       " 44,\n",
       " 48,\n",
       " 17,\n",
       " 40,\n",
       " 49,\n",
       " 61,\n",
       " 44,\n",
       " 49,\n",
       " 48,\n",
       " 44,\n",
       " 49,\n",
       " 50,\n",
       " 57,\n",
       " 4,\n",
       " 44,\n",
       " 48,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 40,\n",
       " 41,\n",
       " 49,\n",
       " 49,\n",
       " 17,\n",
       " 48,\n",
       " 50,\n",
       " 48,\n",
       " 50,\n",
       " 48,\n",
       " 49,\n",
       " 42,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 44,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 2,\n",
       " 44,\n",
       " 48,\n",
       " 50,\n",
       " 49,\n",
       " 3,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 50,\n",
       " 48,\n",
       " 44,\n",
       " 49,\n",
       " 48,\n",
       " 44,\n",
       " 44,\n",
       " 41,\n",
       " 49,\n",
       " 49,\n",
       " 17,\n",
       " 48,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 61,\n",
       " 61,\n",
       " 48,\n",
       " 49,\n",
       " 44,\n",
       " 44,\n",
       " 49,\n",
       " 44,\n",
       " 48,\n",
       " 3,\n",
       " 61,\n",
       " 47,\n",
       " 49,\n",
       " 61,\n",
       " 61,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 17,\n",
       " 48,\n",
       " 50,\n",
       " 49,\n",
       " 49,\n",
       " 48,\n",
       " 2,\n",
       " 40,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 50,\n",
       " 44,\n",
       " 44,\n",
       " 49,\n",
       " 61,\n",
       " 37,\n",
       " 57,\n",
       " 61,\n",
       " 44,\n",
       " 49,\n",
       " 2,\n",
       " 50,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 61,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 44,\n",
       " 48,\n",
       " 44,\n",
       " 49,\n",
       " 61,\n",
       " 49,\n",
       " 48,\n",
       " 44,\n",
       " 41,\n",
       " 49,\n",
       " 44,\n",
       " 47,\n",
       " 44,\n",
       " 61,\n",
       " 48,\n",
       " 49,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 2,\n",
       " 49,\n",
       " 61,\n",
       " 44,\n",
       " 50,\n",
       " 48,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 48,\n",
       " 44,\n",
       " 48,\n",
       " 44,\n",
       " 44,\n",
       " 41,\n",
       " 17,\n",
       " 61,\n",
       " 24,\n",
       " 19,\n",
       " 49,\n",
       " 17,\n",
       " 48,\n",
       " 44,\n",
       " 44,\n",
       " 50,\n",
       " 17,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 48,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 3,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 61,\n",
       " 44,\n",
       " 49,\n",
       " 50,\n",
       " 2,\n",
       " 44,\n",
       " 44,\n",
       " 49,\n",
       " 44,\n",
       " 44,\n",
       " 49,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 47,\n",
       " 48,\n",
       " 2,\n",
       " 40,\n",
       " 44,\n",
       " 49,\n",
       " 50,\n",
       " 49,\n",
       " 48,\n",
       " 49,\n",
       " 49,\n",
       " 61,\n",
       " 12,\n",
       " 2,\n",
       " 49,\n",
       " 48,\n",
       " 44,\n",
       " 48,\n",
       " 44,\n",
       " 50,\n",
       " 26,\n",
       " 27,\n",
       " 17,\n",
       " 44,\n",
       " 41,\n",
       " 47,\n",
       " 49,\n",
       " 44,\n",
       " 61,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 44,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 51,\n",
       " 17,\n",
       " 49,\n",
       " 61,\n",
       " 49,\n",
       " 3,\n",
       " 49,\n",
       " 50,\n",
       " 44,\n",
       " 44,\n",
       " 49,\n",
       " 44,\n",
       " 61,\n",
       " 61,\n",
       " 47,\n",
       " 17,\n",
       " 48,\n",
       " 50,\n",
       " 49,\n",
       " 44,\n",
       " 17,\n",
       " 41,\n",
       " 49,\n",
       " 50,\n",
       " 16,\n",
       " 44,\n",
       " 61,\n",
       " 47,\n",
       " 48,\n",
       " 44,\n",
       " 17,\n",
       " 49,\n",
       " 49,\n",
       " 49,\n",
       " 44,\n",
       " 48,\n",
       " 17,\n",
       " 44,\n",
       " 48,\n",
       " 17,\n",
       " 4,\n",
       " 37,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 50,\n",
       " 44,\n",
       " 44,\n",
       " 17,\n",
       " 49,\n",
       " 44,\n",
       " 49,\n",
       " 48,\n",
       " 44,\n",
       " 17,\n",
       " 49,\n",
       " 44,\n",
       " 4,\n",
       " 3,\n",
       " 37,\n",
       " 49,\n",
       " 50,\n",
       " 49,\n",
       " 44,\n",
       " 44,\n",
       " 61,\n",
       " 61,\n",
       " 44,\n",
       " 17,\n",
       " 48,\n",
       " 44,\n",
       " 50,\n",
       " 49,\n",
       " 32,\n",
       " 44,\n",
       " 24,\n",
       " 41,\n",
       " 3,\n",
       " 17,\n",
       " 48,\n",
       " 49,\n",
       " 50,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 61,\n",
       " 44,\n",
       " 48,\n",
       " 49,\n",
       " 61,\n",
       " 44,\n",
       " 50,\n",
       " 41,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 61,\n",
       " 48,\n",
       " 44,\n",
       " 44,\n",
       " 49,\n",
       " 44,\n",
       " 47,\n",
       " 49,\n",
       " 17,\n",
       " 50,\n",
       " 48,\n",
       " 49,\n",
       " 61,\n",
       " 48,\n",
       " 50,\n",
       " 44,\n",
       " 44,\n",
       " 41,\n",
       " 49,\n",
       " 50,\n",
       " 44,\n",
       " 2,\n",
       " 4,\n",
       " 3,\n",
       " 44,\n",
       " 49,\n",
       " 49,\n",
       " 50,\n",
       " 17,\n",
       " 48,\n",
       " 50,\n",
       " 17,\n",
       " 48,\n",
       " 44,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 61,\n",
       " 44,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 61,\n",
       " 48,\n",
       " 17,\n",
       " 44,\n",
       " 44,\n",
       " 41,\n",
       " 47,\n",
       " 7,\n",
       " 44,\n",
       " 50,\n",
       " 44,\n",
       " 49,\n",
       " 61,\n",
       " 48,\n",
       " 49,\n",
       " 17,\n",
       " 48,\n",
       " 44,\n",
       " 42,\n",
       " 61,\n",
       " 44,\n",
       " 49,\n",
       " 48,\n",
       " 44,\n",
       " 49,\n",
       " 50,\n",
       " 44,\n",
       " ...]"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#create junction table for businesses and categories\n",
    "business_ids = []\n",
    "category_ids = []\n",
    "\n",
    "for row in range(pet_stores.shape[0]):\n",
    "    for category in pet_stores['categories'][row]:\n",
    "        business_ids.append(pet_stores['id'][row])\n",
    "        category_ids.append(pet_categories.index(category))\n",
    "        \n",
    "for row in range(pet_services.shape[0]):\n",
    "    for category in pet_services['categories'][row]:\n",
    "        business_ids.append(pet_services['id'][row])\n",
    "        category_ids.append(pet_categories.index(category))\n",
    "        \n",
    "print(len(business_ids))\n",
    "print(len(category_ids))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Yelp Pet Stores and Pet Services in NYC (ETL): Load"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "#creating SQL connection\n",
    "conn = sqlite3.connect('../Data/pet_care_industry.db')\n",
    "c = conn.cursor()\n",
    "\n",
    "#function to create table\n",
    "def create_table(query):\n",
    "    c.execute(query)\n",
    "\n",
    "#function to close connection\n",
    "def close_c_conn():\n",
    "    c.close()\n",
    "    conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create pet stores and services table\n",
    "create_query = \"\"\"CREATE TABLE stores_and_services\n",
    "                (id TEXT PRIMARY KEY,\n",
    "                 Name TEXT,\n",
    "                 Rating REAL,\n",
    "                 Review_Count INTEGER,\n",
    "                 ZipCode INTEGER);\"\"\"\n",
    "\n",
    "c.execute('DROP TABLE IF EXISTS stores_and_services')\n",
    "create_table(create_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "#function to insert businesses into table\n",
    "def insert_businesses(businesses):\n",
    "    for i in range(len(businesses.index)):\n",
    "        if (not businesses.iloc[i]['is_closed']) & (businesses.iloc[i]['zip_code'].isnumeric()):\n",
    "            c.execute(\"\"\"INSERT OR REPLACE INTO stores_and_services\n",
    "                  (id,\n",
    "                   Name,\n",
    "                   Rating,\n",
    "                   Review_Count,\n",
    "                   ZipCode)\n",
    "                   VALUES\n",
    "                   (?,?,?,?,?)\"\"\",\n",
    "                   (businesses.iloc[i]['id'],\n",
    "                    businesses.iloc[i]['name'],\n",
    "                    float(businesses.iloc[i]['rating']),\n",
    "                    int(businesses.iloc[i]['review_count']),\n",
    "                    int(businesses.iloc[i]['zip_code'])))\n",
    "        \n",
    "    conn.commit()\n",
    "    \n",
    "#insert pet store and services into table\n",
    "insert_businesses(pet_stores)\n",
    "insert_businesses(pet_services)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "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>Rating</th>\n",
       "      <th>Review_Count</th>\n",
       "      <th>ZipCode</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>NYC Pet</th>\n",
       "      <td>2.5</td>\n",
       "      <td>15</td>\n",
       "      <td>11211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NYC Pet</th>\n",
       "      <td>5.0</td>\n",
       "      <td>8</td>\n",
       "      <td>11211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Petland Discounts</th>\n",
       "      <td>3.5</td>\n",
       "      <td>6</td>\n",
       "      <td>11237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Park slope Pet</th>\n",
       "      <td>5.0</td>\n",
       "      <td>10</td>\n",
       "      <td>11215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Petland Discounts</th>\n",
       "      <td>3.0</td>\n",
       "      <td>19</td>\n",
       "      <td>11216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pet Central</th>\n",
       "      <td>3.5</td>\n",
       "      <td>6</td>\n",
       "      <td>10128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Petland Discounts</th>\n",
       "      <td>2.5</td>\n",
       "      <td>9</td>\n",
       "      <td>11206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pet Valu</th>\n",
       "      <td>3.0</td>\n",
       "      <td>14</td>\n",
       "      <td>7002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Slope Pet Food Supplies, Inc</th>\n",
       "      <td>5.0</td>\n",
       "      <td>8</td>\n",
       "      <td>11215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dee's Pet Food</th>\n",
       "      <td>4.5</td>\n",
       "      <td>14</td>\n",
       "      <td>11238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Citipups Village</th>\n",
       "      <td>3.0</td>\n",
       "      <td>87</td>\n",
       "      <td>10014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pets Unlimited Ltd</th>\n",
       "      <td>1.5</td>\n",
       "      <td>35</td>\n",
       "      <td>11104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PetSmart</th>\n",
       "      <td>2.0</td>\n",
       "      <td>94</td>\n",
       "      <td>7094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Petland Discounts</th>\n",
       "      <td>3.5</td>\n",
       "      <td>5</td>\n",
       "      <td>7093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Petland Discounts</th>\n",
       "      <td>3.5</td>\n",
       "      <td>4</td>\n",
       "      <td>7306</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Petland Discounts</th>\n",
       "      <td>2.5</td>\n",
       "      <td>11</td>\n",
       "      <td>11226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Petland Discounts</th>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>7002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Unleashed</th>\n",
       "      <td>3.5</td>\n",
       "      <td>33</td>\n",
       "      <td>10065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABC Pet Supply</th>\n",
       "      <td>2.5</td>\n",
       "      <td>21</td>\n",
       "      <td>11215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Petland Discounts</th>\n",
       "      <td>3.0</td>\n",
       "      <td>18</td>\n",
       "      <td>11385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tropical Pets</th>\n",
       "      <td>2.5</td>\n",
       "      <td>5</td>\n",
       "      <td>11368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Citipups Chelsea</th>\n",
       "      <td>3.0</td>\n",
       "      <td>118</td>\n",
       "      <td>10011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Broadway Pigeon &amp; Pets Supplies</th>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>11207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Canine Styles - Westside</th>\n",
       "      <td>3.0</td>\n",
       "      <td>35</td>\n",
       "      <td>10024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Petco</th>\n",
       "      <td>3.0</td>\n",
       "      <td>104</td>\n",
       "      <td>10028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Petco</th>\n",
       "      <td>2.5</td>\n",
       "      <td>72</td>\n",
       "      <td>10025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brooklyn Cat Cafe</th>\n",
       "      <td>4.0</td>\n",
       "      <td>78</td>\n",
       "      <td>11201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Petland Discounts</th>\n",
       "      <td>2.5</td>\n",
       "      <td>14</td>\n",
       "      <td>11372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Animal Kingdom Pets</th>\n",
       "      <td>2.5</td>\n",
       "      <td>3</td>\n",
       "      <td>10312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cafe Bark</th>\n",
       "      <td>4.5</td>\n",
       "      <td>27</td>\n",
       "      <td>10033</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>David &amp; Rachel Pet Care</th>\n",
       "      <td>5.0</td>\n",
       "      <td>4</td>\n",
       "      <td>11218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Caribbean Pets</th>\n",
       "      <td>2.5</td>\n",
       "      <td>12</td>\n",
       "      <td>7307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Furry Fiends 2</th>\n",
       "      <td>4.5</td>\n",
       "      <td>9</td>\n",
       "      <td>10040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fetch! Pet Care</th>\n",
       "      <td>5.0</td>\n",
       "      <td>3</td>\n",
       "      <td>10603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Petland Discounts</th>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>11413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pooches Always Walking</th>\n",
       "      <td>5.0</td>\n",
       "      <td>1</td>\n",
       "      <td>10465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>La Bella Pooch Dog Training and Grooming</th>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>11214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Boulevard Animal Clinic</th>\n",
       "      <td>2.0</td>\n",
       "      <td>38</td>\n",
       "      <td>11375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arcadia Pets</th>\n",
       "      <td>3.0</td>\n",
       "      <td>14</td>\n",
       "      <td>10312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Patti's Pampered Pups</th>\n",
       "      <td>5.0</td>\n",
       "      <td>1</td>\n",
       "      <td>11109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Doggie Academy</th>\n",
       "      <td>5.0</td>\n",
       "      <td>14</td>\n",
       "      <td>11232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bark Avenue Grooming</th>\n",
       "      <td>5.0</td>\n",
       "      <td>34</td>\n",
       "      <td>10024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sunnyside Pet Healthcare Center</th>\n",
       "      <td>2.0</td>\n",
       "      <td>56</td>\n",
       "      <td>11104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Paw Prints</th>\n",
       "      <td>4.5</td>\n",
       "      <td>25</td>\n",
       "      <td>10023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>American Kennels</th>\n",
       "      <td>2.0</td>\n",
       "      <td>96</td>\n",
       "      <td>10065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Westside Animal Hospital</th>\n",
       "      <td>2.5</td>\n",
       "      <td>24</td>\n",
       "      <td>10036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABC's of Dog Training LLC</th>\n",
       "      <td>4.0</td>\n",
       "      <td>8</td>\n",
       "      <td>11370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Liberty Humane Society</th>\n",
       "      <td>3.5</td>\n",
       "      <td>53</td>\n",
       "      <td>7305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Queens Animal Health</th>\n",
       "      <td>2.5</td>\n",
       "      <td>93</td>\n",
       "      <td>11365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brownstone Veterinary Care</th>\n",
       "      <td>4.5</td>\n",
       "      <td>6</td>\n",
       "      <td>11218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coco And Toto</th>\n",
       "      <td>3.0</td>\n",
       "      <td>63</td>\n",
       "      <td>10019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Club Barks</th>\n",
       "      <td>3.0</td>\n",
       "      <td>54</td>\n",
       "      <td>7302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PetSmart</th>\n",
       "      <td>2.5</td>\n",
       "      <td>19</td>\n",
       "      <td>10010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bergen Veterinary Hospital</th>\n",
       "      <td>4.0</td>\n",
       "      <td>29</td>\n",
       "      <td>7666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mon-Ami Dog Grooming</th>\n",
       "      <td>5.0</td>\n",
       "      <td>6</td>\n",
       "      <td>11354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WufSter</th>\n",
       "      <td>5.0</td>\n",
       "      <td>8</td>\n",
       "      <td>7030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Canine Retreat by AKC - W42</th>\n",
       "      <td>3.5</td>\n",
       "      <td>4</td>\n",
       "      <td>10036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PetSmart</th>\n",
       "      <td>3.0</td>\n",
       "      <td>105</td>\n",
       "      <td>11201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Marine Park Veterinary Group</th>\n",
       "      <td>4.0</td>\n",
       "      <td>20</td>\n",
       "      <td>11234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>American Academy of Pet Grooming</th>\n",
       "      <td>3.5</td>\n",
       "      <td>32</td>\n",
       "      <td>10010</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1502 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          Rating  Review_Count  ZipCode\n",
       "Name                                                                   \n",
       "NYC Pet                                      2.5            15    11211\n",
       "NYC Pet                                      5.0             8    11211\n",
       "Petland Discounts                            3.5             6    11237\n",
       "Park slope Pet                               5.0            10    11215\n",
       "Petland Discounts                            3.0            19    11216\n",
       "Pet Central                                  3.5             6    10128\n",
       "Petland Discounts                            2.5             9    11206\n",
       "Pet Valu                                     3.0            14     7002\n",
       "Slope Pet Food Supplies, Inc                 5.0             8    11215\n",
       "Dee's Pet Food                               4.5            14    11238\n",
       "Citipups Village                             3.0            87    10014\n",
       "Pets Unlimited Ltd                           1.5            35    11104\n",
       "PetSmart                                     2.0            94     7094\n",
       "Petland Discounts                            3.5             5     7093\n",
       "Petland Discounts                            3.5             4     7306\n",
       "Petland Discounts                            2.5            11    11226\n",
       "Petland Discounts                            3.0             4     7002\n",
       "Unleashed                                    3.5            33    10065\n",
       "ABC Pet Supply                               2.5            21    11215\n",
       "Petland Discounts                            3.0            18    11385\n",
       "Tropical Pets                                2.5             5    11368\n",
       "Citipups Chelsea                             3.0           118    10011\n",
       "Broadway Pigeon & Pets Supplies              4.0             1    11207\n",
       "Canine Styles - Westside                     3.0            35    10024\n",
       "Petco                                        3.0           104    10028\n",
       "Petco                                        2.5            72    10025\n",
       "Brooklyn Cat Cafe                            4.0            78    11201\n",
       "Petland Discounts                            2.5            14    11372\n",
       "Animal Kingdom Pets                          2.5             3    10312\n",
       "Cafe Bark                                    4.5            27    10033\n",
       "...                                          ...           ...      ...\n",
       "David & Rachel Pet Care                      5.0             4    11218\n",
       "Caribbean Pets                               2.5            12     7307\n",
       "Furry Fiends 2                               4.5             9    10040\n",
       "Fetch! Pet Care                              5.0             3    10603\n",
       "Petland Discounts                            3.0             3    11413\n",
       "Pooches Always Walking                       5.0             1    10465\n",
       "La Bella Pooch Dog Training and Grooming     4.0             1    11214\n",
       "Boulevard Animal Clinic                      2.0            38    11375\n",
       "Arcadia Pets                                 3.0            14    10312\n",
       "Patti's Pampered Pups                        5.0             1    11109\n",
       "Doggie Academy                               5.0            14    11232\n",
       "Bark Avenue Grooming                         5.0            34    10024\n",
       "Sunnyside Pet Healthcare Center              2.0            56    11104\n",
       "Paw Prints                                   4.5            25    10023\n",
       "American Kennels                             2.0            96    10065\n",
       "Westside Animal Hospital                     2.5            24    10036\n",
       "ABC's of Dog Training LLC                    4.0             8    11370\n",
       "Liberty Humane Society                       3.5            53     7305\n",
       "Queens Animal Health                         2.5            93    11365\n",
       "Brownstone Veterinary Care                   4.5             6    11218\n",
       "Coco And Toto                                3.0            63    10019\n",
       "Club Barks                                   3.0            54     7302\n",
       "PetSmart                                     2.5            19    10010\n",
       "Bergen Veterinary Hospital                   4.0            29     7666\n",
       "Mon-Ami Dog Grooming                         5.0             6    11354\n",
       "WufSter                                      5.0             8     7030\n",
       "Canine Retreat by AKC - W42                  3.5             4    10036\n",
       "PetSmart                                     3.0           105    11201\n",
       "Marine Park Veterinary Group                 4.0            20    11234\n",
       "American Academy of Pet Grooming             3.5            32    10010\n",
       "\n",
       "[1502 rows x 3 columns]"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check SQL pet store and services table\n",
    "stores_and_services = pd.read_sql_query(\"\"\"SELECT Name, Rating, Review_Count, ZipCode\n",
    "                                        FROM stores_and_services;\"\"\", conn)\n",
    "stores_and_services = stores_and_services.set_index('Name')\n",
    "stores_and_services"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create unique categories table\n",
    "create_query = \"\"\"CREATE TABLE categories\n",
    "                (id TEXT PRIMARY KEY,\n",
    "                 Name TEXT);\"\"\"\n",
    "\n",
    "c.execute('DROP TABLE IF EXISTS categories')\n",
    "create_table(create_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "#function to insert categories into table\n",
    "def insert_categories(categories):\n",
    "    for i in range(len(categories)):\n",
    "        c.execute(\"\"\"INSERT INTO categories\n",
    "                  (id,\n",
    "                   Name)\n",
    "                   VALUES\n",
    "                   (?,?)\"\"\",\n",
    "                   (i,\n",
    "                    categories[i]))\n",
    "        \n",
    "    conn.commit()\n",
    "    \n",
    "#insert categories into table\n",
    "insert_categories(pet_categories)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "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>Name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Accessories</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Animal Shelters</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Aquarium Services</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Aquariums</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Art Galleries</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Beer, Wine &amp; Spirits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Bird Shops</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Cafes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Children's Clothing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Coffee &amp; Tea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Community Service/Non-Profit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Cremation Services</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Doctors</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Dog Parks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Dog Walkers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Drugstores</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Emergency Pet Hospital</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Gift Shops</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Grocery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Holistic Animal Care</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Home &amp; Garden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Home Cleaning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Home Organization</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Horse Boarding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Horseback Riding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Hospice</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Ice Cream &amp; Frozen Yogurt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Landscaping</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Lighting Fixtures &amp; Equipment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Livestock Feed &amp; Supply</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Local Fish Stores</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Pet Adoption</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Pet Boarding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Pet Breeders</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Pet Cremation Services</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Pet Groomers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Pet Photography</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Pet Services</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Pet Sitting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Pet Stores</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Pet Training</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Pet Transportation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Propane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Reptile Shops</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Summer Camps</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Utilities</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Veterinarians</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             Name\n",
       "id                               \n",
       "0                     Accessories\n",
       "1                 Animal Shelters\n",
       "2               Aquarium Services\n",
       "3                       Aquariums\n",
       "4                   Art Galleries\n",
       "5            Beer, Wine & Spirits\n",
       "6                      Bird Shops\n",
       "7                           Cafes\n",
       "8             Children's Clothing\n",
       "9                    Coffee & Tea\n",
       "10   Community Service/Non-Profit\n",
       "11             Cremation Services\n",
       "12                        Doctors\n",
       "13                      Dog Parks\n",
       "14                    Dog Walkers\n",
       "15                     Drugstores\n",
       "16         Emergency Pet Hospital\n",
       "17                     Gift Shops\n",
       "18                        Grocery\n",
       "19           Holistic Animal Care\n",
       "20                  Home & Garden\n",
       "21                  Home Cleaning\n",
       "22              Home Organization\n",
       "23                 Horse Boarding\n",
       "24               Horseback Riding\n",
       "25                        Hospice\n",
       "26      Ice Cream & Frozen Yogurt\n",
       "27                    Landscaping\n",
       "28  Lighting Fixtures & Equipment\n",
       "29        Livestock Feed & Supply\n",
       "30              Local Fish Stores\n",
       "31                   Pet Adoption\n",
       "32                   Pet Boarding\n",
       "33                   Pet Breeders\n",
       "34         Pet Cremation Services\n",
       "35                   Pet Groomers\n",
       "36                Pet Photography\n",
       "37                   Pet Services\n",
       "38                    Pet Sitting\n",
       "39                     Pet Stores\n",
       "40                   Pet Training\n",
       "41             Pet Transportation\n",
       "42                        Propane\n",
       "43                  Reptile Shops\n",
       "44                   Summer Camps\n",
       "45                      Utilities\n",
       "46                  Veterinarians"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check SQL categories table\n",
    "categories = pd.read_sql_query(\"\"\"SELECT * FROM categories;\"\"\", conn)\n",
    "categories = categories.set_index('id')\n",
    "categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "#defining SQL query to create junction table for businesses and categories\n",
    "create_query = \"\"\"CREATE TABLE IF NOT EXISTS businesses_categories\n",
    "(   business_id TEXT    NOT NULL,\n",
    "    category_id INTEGER NOT NULL,\n",
    "    PRIMARY KEY(business_id, category_id),\n",
    "    FOREIGN KEY(business_id) REFERENCES stores_and_services(id),\n",
    "    FOREIGN KEY(category_id) REFERENCES categories(id));\"\"\"\n",
    "\n",
    "c.execute('DROP TABLE IF EXISTS businesses_categories')\n",
    "create_table(create_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [],
   "source": [
    "#function to insert businesses and categories into table\n",
    "def insert_businesses_categories(business_ids, category_ids):\n",
    "    for i in range(len(business_ids)):\n",
    "        c.execute(\"\"\"INSERT OR REPLACE INTO businesses_categories\n",
    "                  (business_id,\n",
    "                   category_id)\n",
    "                   VALUES\n",
    "                   (?,?)\"\"\",\n",
    "                   (business_ids[i],\n",
    "                    category_ids[i]))\n",
    "        \n",
    "    conn.commit()\n",
    "    \n",
    "#insert categories into table\n",
    "insert_businesses_categories(business_ids, category_ids)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "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>category_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>YtJXZdh3lnuH2JuDtXBqqQ</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>yc0AK8DAKTQF3JuDI1Ta4w</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8AKa6ep2-fW4zFkvg_yhKw</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>o7ja0NftF0t8bIQJJgGP4Q</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>JwbfRncSOiv2akciJcwVHA</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>JwbfRncSOiv2akciJcwVHA</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>uBoaVbgLCPTwa2ab5890JA</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>uBoaVbgLCPTwa2ab5890JA</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>jYfy42Dx_in0SLYQIunehg</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>bUp8mGkEoz68vW4K_7ItVQ</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>UR41hufVO6BWxzxJMBvWJg</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>jXUi-DoJq-tNcuHNxO-aIg</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Twj04F1i8WB8V61lYOK0fQ</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Twj04F1i8WB8V61lYOK0fQ</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1Ry-X7zFcxW5NPwfCIZCkw</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>YqmKFtAH_Fys-ePkKyLW_Q</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>YqmKFtAH_Fys-ePkKyLW_Q</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>YqmKFtAH_Fys-ePkKyLW_Q</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>OT3naODWAE0-m6OoQ_0tRA</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>AKBKcoKyxNUeexb1gX7cHQ</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>n9jsV_3mbk2oeMuytkIKcQ</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>5vv_idKSCU2sReHeEJm3qQ</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>EZVb4-cEtC8rs3mk43vT7Q</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>EZVb4-cEtC8rs3mk43vT7Q</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>GbU7vGGNe0IXQi0uCnyDEw</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>z181nI-l_DnCcNh_yeOcEg</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>SO2lJjaNpazzZA6fczHxzQ</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>fVTneUCf9qIA17tgi34pOQ</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>fVTneUCf9qIA17tgi34pOQ</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Vkc-yW0PDF6M2_2H_P6BIQ</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2566</th>\n",
       "      <td>FfClvEPhq5pTb_L3SFLx0Q</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2567</th>\n",
       "      <td>CFo1R6v0duXgdMiLBiQbnw</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2568</th>\n",
       "      <td>q0dBXcYz4TTMOfYhBHBtnA</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2569</th>\n",
       "      <td>lYdp7J-MMKvXWhweHBO61A</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2570</th>\n",
       "      <td>cTHFkp-YOoEAjJXauSOqcA</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2571</th>\n",
       "      <td>cTHFkp-YOoEAjJXauSOqcA</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2572</th>\n",
       "      <td>W6aKUbDEGMnL3Dgu1H26-w</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2573</th>\n",
       "      <td>zWOLQh61LeOcn7OTeWDimg</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2574</th>\n",
       "      <td>sPpOHO1BK47ZpQFR3zMDsw</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2575</th>\n",
       "      <td>sPpOHO1BK47ZpQFR3zMDsw</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2576</th>\n",
       "      <td>sPpOHO1BK47ZpQFR3zMDsw</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2577</th>\n",
       "      <td>Yxp53oAMiZxT5pVdGSIV1A</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2578</th>\n",
       "      <td>Yxp53oAMiZxT5pVdGSIV1A</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2579</th>\n",
       "      <td>W6TsDZ5GetZjnVVa1Xl1Sg</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2580</th>\n",
       "      <td>W6TsDZ5GetZjnVVa1Xl1Sg</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2581</th>\n",
       "      <td>W6TsDZ5GetZjnVVa1Xl1Sg</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2582</th>\n",
       "      <td>gHbOl7wAaPSH59mqLU6Zpw</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2583</th>\n",
       "      <td>gHbOl7wAaPSH59mqLU6Zpw</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2584</th>\n",
       "      <td>gHbOl7wAaPSH59mqLU6Zpw</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2585</th>\n",
       "      <td>C5Q6YCvKo9fr_m6z4Hp2VA</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2586</th>\n",
       "      <td>KvcTyTsxFf4iJ6OcRV2VNA</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2587</th>\n",
       "      <td>dY__bGphANw66fd2y9qZZw</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2588</th>\n",
       "      <td>dY__bGphANw66fd2y9qZZw</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2589</th>\n",
       "      <td>dY__bGphANw66fd2y9qZZw</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2590</th>\n",
       "      <td>5FVL8RyBu7iYurjQ9vYITw</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2591</th>\n",
       "      <td>5FVL8RyBu7iYurjQ9vYITw</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2592</th>\n",
       "      <td>5FVL8RyBu7iYurjQ9vYITw</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2593</th>\n",
       "      <td>gzO6izOVtDH7S8pd1pXRoA</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2594</th>\n",
       "      <td>qFvfRZZqkjueb2pLXCq60Q</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2595</th>\n",
       "      <td>qFvfRZZqkjueb2pLXCq60Q</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2596 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 business_id  category_id\n",
       "0     YtJXZdh3lnuH2JuDtXBqqQ           49\n",
       "1     yc0AK8DAKTQF3JuDI1Ta4w           49\n",
       "2     8AKa6ep2-fW4zFkvg_yhKw           49\n",
       "3     o7ja0NftF0t8bIQJJgGP4Q           49\n",
       "4     JwbfRncSOiv2akciJcwVHA           47\n",
       "5     JwbfRncSOiv2akciJcwVHA           49\n",
       "6     uBoaVbgLCPTwa2ab5890JA           47\n",
       "7     uBoaVbgLCPTwa2ab5890JA           49\n",
       "8     jYfy42Dx_in0SLYQIunehg           49\n",
       "9     bUp8mGkEoz68vW4K_7ItVQ           49\n",
       "10    UR41hufVO6BWxzxJMBvWJg           49\n",
       "11    jXUi-DoJq-tNcuHNxO-aIg           49\n",
       "12    Twj04F1i8WB8V61lYOK0fQ           49\n",
       "13    Twj04F1i8WB8V61lYOK0fQ           42\n",
       "14    1Ry-X7zFcxW5NPwfCIZCkw           49\n",
       "15    YqmKFtAH_Fys-ePkKyLW_Q           49\n",
       "16    YqmKFtAH_Fys-ePkKyLW_Q           44\n",
       "17    YqmKFtAH_Fys-ePkKyLW_Q           50\n",
       "18    OT3naODWAE0-m6OoQ_0tRA           49\n",
       "19    AKBKcoKyxNUeexb1gX7cHQ           49\n",
       "20    n9jsV_3mbk2oeMuytkIKcQ           49\n",
       "21    5vv_idKSCU2sReHeEJm3qQ           49\n",
       "22    EZVb4-cEtC8rs3mk43vT7Q           49\n",
       "23    EZVb4-cEtC8rs3mk43vT7Q           50\n",
       "24    GbU7vGGNe0IXQi0uCnyDEw           49\n",
       "25    z181nI-l_DnCcNh_yeOcEg           49\n",
       "26    SO2lJjaNpazzZA6fczHxzQ           49\n",
       "27    fVTneUCf9qIA17tgi34pOQ           49\n",
       "28    fVTneUCf9qIA17tgi34pOQ           42\n",
       "29    Vkc-yW0PDF6M2_2H_P6BIQ           49\n",
       "...                      ...          ...\n",
       "2566  FfClvEPhq5pTb_L3SFLx0Q           48\n",
       "2567  CFo1R6v0duXgdMiLBiQbnw           49\n",
       "2568  q0dBXcYz4TTMOfYhBHBtnA           61\n",
       "2569  lYdp7J-MMKvXWhweHBO61A           50\n",
       "2570  cTHFkp-YOoEAjJXauSOqcA            2\n",
       "2571  cTHFkp-YOoEAjJXauSOqcA           49\n",
       "2572  W6aKUbDEGMnL3Dgu1H26-w           61\n",
       "2573  zWOLQh61LeOcn7OTeWDimg           61\n",
       "2574  sPpOHO1BK47ZpQFR3zMDsw           44\n",
       "2575  sPpOHO1BK47ZpQFR3zMDsw           17\n",
       "2576  sPpOHO1BK47ZpQFR3zMDsw           48\n",
       "2577  Yxp53oAMiZxT5pVdGSIV1A           48\n",
       "2578  Yxp53oAMiZxT5pVdGSIV1A           44\n",
       "2579  W6TsDZ5GetZjnVVa1Xl1Sg           49\n",
       "2580  W6TsDZ5GetZjnVVa1Xl1Sg           50\n",
       "2581  W6TsDZ5GetZjnVVa1Xl1Sg           40\n",
       "2582  gHbOl7wAaPSH59mqLU6Zpw           61\n",
       "2583  gHbOl7wAaPSH59mqLU6Zpw           44\n",
       "2584  gHbOl7wAaPSH59mqLU6Zpw           48\n",
       "2585  C5Q6YCvKo9fr_m6z4Hp2VA           44\n",
       "2586  KvcTyTsxFf4iJ6OcRV2VNA           17\n",
       "2587  dY__bGphANw66fd2y9qZZw           44\n",
       "2588  dY__bGphANw66fd2y9qZZw           50\n",
       "2589  dY__bGphANw66fd2y9qZZw           17\n",
       "2590  5FVL8RyBu7iYurjQ9vYITw           49\n",
       "2591  5FVL8RyBu7iYurjQ9vYITw           50\n",
       "2592  5FVL8RyBu7iYurjQ9vYITw           44\n",
       "2593  gzO6izOVtDH7S8pd1pXRoA           61\n",
       "2594  qFvfRZZqkjueb2pLXCq60Q           44\n",
       "2595  qFvfRZZqkjueb2pLXCq60Q           48\n",
       "\n",
       "[2596 rows x 2 columns]"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#querying SQL businesses and categories table\n",
    "businesses_categories = pd.read_sql_query(\"SELECT * FROM businesses_categories;\", conn)\n",
    "businesses_categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [],
   "source": [
    "#close connection\n",
    "close_c_conn()"
   ]
  },
  {
   "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.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}