Final Assignment - Data Collector 5.ipynb

{
    "nbformat_minor": 1, 
    "cells": [
        {
            "source": "<h1>Data Collector 5<br>Assignment By: Derek Evans<br>Submitted On: Jan. 20th, 2019</h1>\n\n<hr>", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 1, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "output_type": "stream", 
                    "name": "stdout", 
                    "text": "Requirement not upgraded as not directly required: foursquare in /opt/conda/envs/DSX-Python35/lib/python3.5/site-packages\nRequirement not upgraded as not directly required: six in /opt/conda/envs/DSX-Python35/lib/python3.5/site-packages (from foursquare)\nRequirement not upgraded as not directly required: requests>=2.1 in /opt/conda/envs/DSX-Python35/lib/python3.5/site-packages (from foursquare)\nRequirement not upgraded as not directly required: chardet<3.1.0,>=3.0.2 in /opt/conda/envs/DSX-Python35/lib/python3.5/site-packages (from requests>=2.1->foursquare)\nRequirement not upgraded as not directly required: idna<2.7,>=2.5 in /opt/conda/envs/DSX-Python35/lib/python3.5/site-packages (from requests>=2.1->foursquare)\nRequirement not upgraded as not directly required: urllib3<1.23,>=1.21.1 in /opt/conda/envs/DSX-Python35/lib/python3.5/site-packages (from requests>=2.1->foursquare)\nRequirement not upgraded as not directly required: certifi>=2017.4.17 in /opt/conda/envs/DSX-Python35/lib/python3.5/site-packages (from requests>=2.1->foursquare)\nFetching package metadata .............\nSolving package specifications: .\n\n# All requested packages already installed.\n# packages in environment at /opt/conda/envs/DSX-Python35:\n#\ngeopy                     1.18.1                     py_0    conda-forge\n"
                }
            ], 
            "source": "import types\nfrom ibm_botocore.client import Config\nimport ibm_boto3\nget_ipython().system(u'pip install foursquare')\nget_ipython().system(u'conda install -c conda-forge geopy --yes')\nfrom geopy.geocoders import Nominatim\nimport requests\nimport pandas as pd\nimport numpy as np\nfrom IPython.display import Image\nfrom IPython.core.display import HTML\n# transforming json file into a pandas dataframe\nfrom pandas.io.json import json_normalize\nfrom datetime import datetime, timezone\nimport pytz\nimport time\nimport ibm_db\nimport ibm_db_dbi"
        }, 
        {
            "execution_count": 2, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "# The code was removed by Watson Studio for sharing."
        }, 
        {
            "execution_count": 3, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "# The code was removed by Watson Studio for sharing."
        }, 
        {
            "execution_count": 4, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "main_city_lst = []\ncity_list1 = list(df_data_2.values.flatten())\n\ntemp_list1 = []\ntemp_list2 = []\ncount1 = 0"
        }, 
        {
            "execution_count": 5, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "for item in city_list1:\n    if count1 < 16:\n        temp_list1.append(item)\n        count1 += 1\n    elif count1 == 16:\n        temp_list1.append(item)\n        temp_list2.append(temp_list1)\n        temp_list1 = []\n        count1 = 0\n    elif count1 > 17:\n        temp_list1 = []\n        count1 = 0"
        }, 
        {
            "execution_count": 6, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "#  FOURSQUARE API URL VARIABLES -- IMPORTANT\nurl = ' ' \nurl1 = 'https://api.foursquare.com/v2/venues/trending?'\nurl2 = '&'\nurl3 = '%2C%20'\nurl4 = '%2C'\nurl5 = 'ne='\nurl6 = 'sw='\nurl7 = 'near='\nurl8 = 'v='\nurl9 = 'limit='\nurl10 = 'client_id='\nurl11 = 'client_secret='\n\n# DATE & TIME VARIABLES -- IMPORTANT\nIT = pytz.timezone('America/Creston')\ndate_now = datetime.now(IT)\ndate_date = date_now.strftime(\"%x\")\ndate_time = date_now.strftime(\"%X\")\ndate_weekday = date_now.strftime(\"%A\")\ndate_year = date_now.strftime(\"%Y\")\ndate_month = date_now.strftime(\"%B\")\ndate_ampm = date_now.strftime(\"%p\")\ndate_zone = date_now.strftime(\"%Z\")\n\n\ncount = 0\nfinal_city_list = []\ncity_error_list = []\ncity_list_pass1 = []"
        }, 
        {
            "execution_count": 7, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "for row in temp_list2:\n\n    # API ID\n    url12 = str(url10 + Foursquare_API_ID + url2)\n    # API Password\n    url13 = str(url11 + Foursquare_API_secret + url2)\n    # Near Parameter with City Name\n    url14 = str(url7 + str(row[1]) + url3 + str(row[9]) + url3 + str(row[12]) + url2)\n    # NE Lat & Long Map Boundary\n    url15 = str(url5 + str(row[13]) + url4 + str(row[14]) + url2)\n    # SE Lat & Long Map Boundary\n    url16 = str(url6 + str(row[15]) + url4 + str(row[16]) + url2)\n    # Version\n    url17 = str(url8 + Foursquare_API_version + url2)\n    # Limit for number of search results returned\n    url18 = str(url9 + str(15))\n\n    url = str(url1 + url12 + url13 + url14 + url15 + url16 + url17 + url18)\n\n    results = requests.get(url).json()\n\n    venue1 = results['response']['venues']\n    dataframe = json_normalize(venue1)\n    city_list = list(dataframe.values.flatten())\n\n    str_count1 = len(city_list)\n\n    if str_count1 > 0:\n        for item in city_list:\n            city_list_pass1.append(item)\n    else:\n        pass"
        }, 
        {
            "execution_count": 8, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "# IMPORTANT VARIABLES FOR PASS 1\ntemp_list_pass1 = []\ntemp_list_pass2 = []\ntemp_list_pass3 = []\npass_count = 0\npass_str1 = ' '\npass_str2 = ' '\npass_str3 = ' '\npass_str4 = ' '\npass_str5 = ' '\npass_str6 = ' '"
        }, 
        {
            "execution_count": 9, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "# PASS 2\n# FIRST THREE COLUMNS CREATED  == TIME & DATE COLUMNS CREATED \nfor item in city_list_pass1:\n    pass_str1 = str(item)\n    pass_str2 = pass_str1.find('shortName')\n    if pass_str2 != -1 and pass_count < 1 and len(temp_list_pass3) == 0:\n        pass_count += 1\n        pass_str3 = str(item)\n        pass_str4 = pass_str3.find('pluralName')\n        pass_str5 = pass_str3.find(', ', pass_str4)\n        pass_str6 = pass_str3[int(pass_str4 + 14):int(pass_str5 - 1)]\n        temp_list_pass1.append(pass_str6)\n        #\n        pass_str3 = str(item)\n        pass_str4 = pass_str3.find('shortName')\n        pass_str5 = pass_str3.find(', ', pass_str4)\n        pass_str6 = pass_str3[int(pass_str4 + 13):int(pass_str5 - 1)]\n        temp_list_pass1.append(pass_str6)\n        #\n        pass_str3 = str(item)\n        pass_str4 = pass_str3.find('name')\n        pass_str5 = pass_str3.find(', ', pass_str4)\n        pass_str6 = pass_str3[int(pass_str4 + 8):int(pass_str5 - 2)]\n        temp_list_pass1.append(pass_str6)\n    elif pass_str2 == -1 and pass_count == 1 and len(temp_list_pass3) == 0:\n        temp_list_pass1.append(item)\n    elif pass_str2 != -1 and pass_count == 1 and len(temp_list_pass3) == 0:\n        temp_list_pass1.append(date_date)\n        temp_list_pass1.append(date_time)\n        temp_list_pass1.append(date_weekday)\n        temp_list_pass1.append(date_year)\n        temp_list_pass1.append(date_month)\n        temp_list_pass1.append(date_ampm)\n        temp_list_pass2.append(temp_list_pass1)\n        temp_list_pass1 = []\n        pass_str3 = str(item)\n        pass_str4 = pass_str3.find('pluralName')\n        pass_str5 = pass_str3.find(', ', pass_str4)\n        pass_str6 = pass_str3[int(pass_str4 + 14):int(pass_str5 - 1)]\n        temp_list_pass3.append(pass_str6)\n        #\n        pass_str3 = str(item)\n        pass_str4 = pass_str3.find('shortName')\n        pass_str5 = pass_str3.find(', ', pass_str4)\n        pass_str6 = pass_str3[int(pass_str4 + 13):int(pass_str5 - 1)]\n        temp_list_pass3.append(pass_str6)\n        #\n        pass_str3 = str(item)\n        pass_str4 = pass_str3.find('name')\n        pass_str5 = pass_str3.find(', ', pass_str4)\n        pass_str6 = pass_str3[int(pass_str4 + 8):int(pass_str5 - 1)]\n        temp_list_pass3.append(pass_str6)\n    elif pass_str2 == -1 and pass_count == 1 and len(temp_list_pass1) == 0:\n        temp_list_pass3.append(item) \n    elif pass_str2 != -1 and pass_count == 1 and len(temp_list_pass1) == 0:\n        temp_list_pass3.append(date_date)\n        temp_list_pass3.append(date_time)\n        temp_list_pass3.append(date_weekday)\n        temp_list_pass3.append(date_year)\n        temp_list_pass3.append(date_month)\n        temp_list_pass3.append(date_ampm)\n        temp_list_pass2.append(temp_list_pass3)\n        temp_list_pass3 = []\n        #\n        pass_str3 = str(item)\n        pass_str4 = pass_str3.find('pluralName')\n        pass_str5 = pass_str3.find(', ', pass_str4)\n        pass_str6 = pass_str3[int(pass_str4 + 14):int(pass_str5 - 1)]\n        temp_list_pass1.append(pass_str6)\n        #\n        pass_str3 = str(item)\n        pass_str4 = pass_str3.find('shortName')\n        pass_str5 = pass_str3.find(', ', pass_str4)\n        pass_str6 = pass_str3[int(pass_str4 + 13):int(pass_str5 - 1)]\n        temp_list_pass1.append(pass_str6)\n        #\n        pass_str3 = str(item)\n        pass_str4 = pass_str3.find('name')\n        pass_str5 = pass_str3.find(', ', pass_str4)\n        pass_str6 = pass_str3[int(pass_str4 + 8):int(pass_str5 - 1)]\n        temp_list_pass1.append(pass_str6) "
        }, 
        {
            "execution_count": 10, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "# IMPORTANT VARIABLES FOR PASS  2\npass2_list1 = []\npass2_list2 = []\npass2_list3 = []\npass2_list4 = []\npass2_list5 = []\npass2_list6 = []\npass2_str1 = ' '\npass2_str2 = ' '\npass2_str3 = ' '\npass2_str4 = ' '\npass2_str5 = ' '\npass2_count = 0 "
        }, 
        {
            "execution_count": 11, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "# PASS  2\n# STRIP ADDRESS LINE & CONVERT TO STRING\nfor row in temp_list_pass2:\n    for item in row:\n        pass2_str1 = str(item)\n        pass2_str2 = pass2_str1.find(\"['\")\n        if str(pass2_str2) != \"-1\":\n            pass2_str1 = pass2_str1.strip(\"['\")\n            pass2_str1 = pass2_str1.strip(\"]'\")\n            pass2_str1 = pass2_str1.replace(\"', '\", ', ')\n            pass2_list1.append(pass2_str1)\n        elif str(pass2_str2) == \"-1\":\n            pass2_list1.append(item)\n    pass2_list2.append(pass2_list1)\n    pass2_list1 = []\n\nfor row in pass2_list2:\n    for item in row:\n        pass2_str3 = str(item)\n        pass2_str4 = pass2_str3.find(\"label\")\n        if pass2_str4 == -1:\n            pass2_list3.append(item)\n        elif pass2_str4 != -1:\n            pass\n    pass2_list4.append(pass2_list3)\n    pass2_list3 = []"
        }, 
        {
            "execution_count": 12, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "# IMPORTANT VARIABLES FOR PASS 3\npass3_list1 = []\npass3_list2 = []\npass3_str1 = ' '\npass3_str2 = ' '\npass3_counter = 0"
        }, 
        {
            "execution_count": 13, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "# PASS  3\n# STRIP ADDRESS LINE & CONVERT TO STRING\nfor row in pass2_list4:\n    pass3_counter = 0\n    pass3_str1 = len(row)\n    \n    if str(row[8]) == 'San Fransico':\n        if pass3_str1 == 24:\n            row.pop(3)\n            row.pop(3)\n            for item in row:\n                pass3_list1.append(str(pass3_str2))\n                pass3_counter += 1\n            pass3_list2.append(pass3_list1)\n            pass3_list1 = []\n\n        elif pass3_str1 == 25:\n            for item in row:\n                pass3_str2 = str(item)\n                row.pop(3)\n                row.pop(3)\n                row.pop(10)\n                for item in row:\n                    pass3_list1.append(str(pass3_str2))\n                    pass3_counter += 1\n                pass3_list2.append(pass3_list1)\n                pass3_list1 = []\n        else:\n            for item in row:\n                pass3_str2 = str(item)\n                pass3_list1.append(str(pass3_str2))\n                pass3_counter += 1\n            pass3_list2.append(pass3_list1)\n            pass3_list1 = []\n\n    elif str(row[14]) == 'San Fransico':\n        if pass3_str1 == 24 :\n            row.pop(3)\n            row.pop(3)\n            for item in row:\n                pass3_list1.append(str(pass3_str2))\n                pass3_counter += 1\n            pass3_list2.append(pass3_list1)\n            pass3_list1 = []\n\n        elif pass3_str1 == 25:\n            if str(row[3]) == 'nan' and str(row[4]) == 'nan':\n                row.pop(3)\n                row.pop(3)\n                row.pop(10)\n                for item in row:\n                    pass3_list1.append(str(pass3_str2))\n                    pass3_counter += 1\n                pass3_list2.append(pass3_list1)\n                pass3_list1 = []\n\n        else:\n            for item in row:\n                pass3_str2 = str(item)\n                pass3_list1.append(str(pass3_str2))\n                pass3_counter += 1\n            pass3_list2.append(pass3_list1)\n            pass3_list1 = []\n\n    elif str(row[1]) == 'Minneapolis':\n        if pass3_str1 == 10:\n            pass        \n        else:\n            for item in row:\n                pass3_str2 = str(item)\n                pass3_list1.append(str(pass3_str2))\n                pass3_counter += 1\n            pass3_list2.append(pass3_list1)\n            pass3_list1 = []\n\n    elif str(row[6]) == 'Minneapolis':\n        if pass3_str1 == 23:\n            for item in row:\n                pass3_str2 = str(item)            \n                if pass3_counter == 10:\n                    pass3_counter += 1\n                    pass\n                elif pass3_counter != 10:\n                    pass3_list1.append(str(pass3_str2))\n                    pass3_counter += 1\n            pass3_list2.append(pass3_list1)\n            pass3_list1 = []\n        else:\n            for item in row:\n                pass3_str2 = str(item)\n                pass3_list1.append(str(pass3_str2))\n                pass3_counter += 1\n            pass3_list2.append(pass3_list1)\n            pass3_list1 = []\n\n    elif str(row[6]) == 'Boston':\n        if pass3_str1 == 21:\n            for item in row:\n                pass3_str2 = str(item)            \n                if pass3_counter == 14:\n                    pass3_list1.append('blank')\n                    pass3_counter += 1\n                elif pass3_counter != 14:\n                    pass3_list1.append(str(pass3_str2))\n                    pass3_counter += 1\n            pass3_list2.append(pass3_list1)\n            pass3_list1 = []\n        else:\n            for item in row:\n                pass3_str2 = str(item)\n                pass3_list1.append(str(pass3_str2))\n                pass3_counter += 1\n            pass3_list2.append(pass3_list1)\n            pass3_list1 = []\n\n    elif str(row[6]) == 'Detroit':\n        if pass3_str1 == 20:\n            for item in row:\n                pass3_str2 = str(item)\n                if pass3_counter == 13:\n                    pass3_list1.append('blank')\n                    pass3_counter += 1\n                elif pass3_counter == 7:\n                    pass3_list1.append('blank')\n                    pass3_counter += 1\n                elif pass3_counter != 7 or pass3_counter != 13:\n                    pass3_list1.append(str(pass3_str2))\n                    pass3_counter += 1\n            pass3_list2.append(pass3_list1)\n            pass3_list1 = []\n        else:\n            for item in row:\n                pass3_str2 = str(item)\n                pass3_list1.append(str(pass3_str2))\n                pass3_counter += 1\n            pass3_list2.append(pass3_list1)\n            pass3_list1 = []\n \n    else:\n        for item in row:\n            pass3_str2 = str(item)\n            pass3_list1.append(str(pass3_str2))\n            pass3_counter += 1\n        pass3_list2.append(pass3_list1)\n        pass3_list1 = []\n"
        }, 
        {
            "execution_count": 14, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "# IMPORTANT VARIABLES FOR PASS 4\npass4_list1 = []\npass4_list2 = []\npass4_str1 = ' '\npass4_str2 = ' '\npass4_counter = 0"
        }, 
        {
            "execution_count": 15, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "# PASS  4\n#  COUNT ADJUSTOR\nfor row in pass3_list2:\n    pass4_str1 = int(len(row))\n    if pass4_str1 < 22:\n        pass4_str2 = int(22 - int(pass4_str1))\n        for item in range(pass4_str1):\n            pass4_list1.append(row.pop(0))\n        for item in range(pass4_str2):\n            pass4_list1.append('blank')\n        pass4_list2.append(pass4_list1)\n        pass4_list1 = []        \n    elif pass4_str1 > 22:\n        pass4_str2 = int(int(pass4_str1) -22)\n        for item in range(pass4_str1):\n            pass4_list1.append(row.pop(0))\n        for item in range(pass4_str2):\n            pass4_list1.append('blank')\n        pass4_list2.append(pass4_list1)\n        pass4_list1 = []            \n    elif pass4_str1 == 22:\n        pass4_str2 = 22\n        for item in row:\n            pass4_list1.append(item)\n        pass4_list2.append(pass4_list1)\n        pass4_list1 = []"
        }, 
        {
            "execution_count": 16, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "# IMPORTANT VARIABLES FOR PASS 4\npass5_list1 = []\npass5_list2 = []\npass5_str1 = ' '\npass5_str2 = ' '\npass5_counter = 0"
        }, 
        {
            "execution_count": 17, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "for row in pass4_list2:\n    pass5_str1 = int(len(row))\n    if pass5_str1 > 22:\n        for item in row[0:22]:\n            pass5_list1.append(item)\n        pass5_list2.append(pass5_list1)\n        pass5_list1 = []\n    else:\n        for item in row:\n            pass5_list1.append(item)\n        pass5_list2.append(pass5_list1)\n        pass5_list1 = []"
        }, 
        {
            "execution_count": 18, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "city_DF = pd.DataFrame(columns=['categories1', 'categories2', 'categories3', 'id', 'location.address', 'location.cc', 'location.city',\n                                                                         'location.country', 'location.crossStreet', 'location.formattedAddress',\n                                                                         'location.lat', 'location.lng', 'location.postalCode', 'location.state',\n                                                                         'name', 'venuePage.id', 'date', 'time', 'weekday', 'year', 'month', 'am_pm'])"
        }, 
        {
            "execution_count": 19, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "for row in pass5_list2:\n    city_DF = city_DF.append(pd.Series(row, index=['categories1', 'categories2', 'categories3', 'id', 'location.address', 'location.cc', 'location.city',\n                                                                                                         'location.country', 'location.crossStreet', 'location.formattedAddress',\n                                                                                                         'location.lat', 'location.lng', 'location.postalCode', 'location.state',\n                                                                                                         'name', 'venuePage.id', 'date', 'time', 'weekday', 'year', 'month', 'am_pm']),\n                                                          ignore_index=True)"
        }, 
        {
            "execution_count": 20, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 20, 
                    "metadata": {}, 
                    "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>categories1</th>\n      <th>categories2</th>\n      <th>categories3</th>\n      <th>id</th>\n      <th>location.address</th>\n      <th>location.cc</th>\n      <th>location.city</th>\n      <th>location.country</th>\n      <th>location.crossStreet</th>\n      <th>location.formattedAddress</th>\n      <th>...</th>\n      <th>location.postalCode</th>\n      <th>location.state</th>\n      <th>name</th>\n      <th>venuePage.id</th>\n      <th>date</th>\n      <th>time</th>\n      <th>weekday</th>\n      <th>year</th>\n      <th>month</th>\n      <th>am_pm</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Basketball Stadiums</td>\n      <td>Basketball</td>\n      <td>Basketball Stadiu</td>\n      <td>4ad4bff8f964a52002ea20e3</td>\n      <td>125 S Pennsylvania St</td>\n      <td>US</td>\n      <td>Indianapolis</td>\n      <td>United States</td>\n      <td>at E Georgia St</td>\n      <td>125 S Pennsylvania St (at E Georgia St), India...</td>\n      <td>...</td>\n      <td>46204</td>\n      <td>IN</td>\n      <td>Bankers Life Fieldhouse</td>\n      <td>42217987</td>\n      <td>01/20/19</td>\n      <td>17:32:20</td>\n      <td>Sunday</td>\n      <td>2019</td>\n      <td>January</td>\n      <td>PM</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Convention Centers</td>\n      <td>Convention Center</td>\n      <td>Convention Center</td>\n      <td>5c40f328345cbe002c99e458</td>\n      <td>US</td>\n      <td>San Jose</td>\n      <td>United States</td>\n      <td>San Jose, CA 95113, United States</td>\n      <td>37.329998</td>\n      <td>-121.889275</td>\n      <td>...</td>\n      <td>Further Confusion 2019</td>\n      <td>01/20/19</td>\n      <td>17:32:20</td>\n      <td>Sunday</td>\n      <td>2019</td>\n      <td>January</td>\n      <td>PM</td>\n      <td>blank</td>\n      <td>blank</td>\n      <td>blank</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Indie Movie Theaters</td>\n      <td>Indie Movies</td>\n      <td>Indie Movie Theater</td>\n      <td>nan</td>\n      <td>nan</td>\n      <td>4089ae00f964a520bff21ee3</td>\n      <td>429 Castro St</td>\n      <td>US</td>\n      <td>San Francisco</td>\n      <td>United States</td>\n      <td>...</td>\n      <td>nan</td>\n      <td>37.761991717624404</td>\n      <td>-122.43496924638747</td>\n      <td>94114</td>\n      <td>CA</td>\n      <td>Castro Theatre</td>\n      <td>01/20/19</td>\n      <td>17:32:20</td>\n      <td>Sunday</td>\n      <td>2019</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Movie Theaters</td>\n      <td>Movie Theater</td>\n      <td>Movie Theater</td>\n      <td>19.0</td>\n      <td>19 movies</td>\n      <td>4a5824e5f964a5206db71fe3</td>\n      <td>135 4th St Ste 3000</td>\n      <td>US</td>\n      <td>San Francisco</td>\n      <td>United States</td>\n      <td>...</td>\n      <td>nan</td>\n      <td>37.78457546024026</td>\n      <td>-122.40323635482677</td>\n      <td>94103</td>\n      <td>CA</td>\n      <td>AMC Metreon 16</td>\n      <td>01/20/19</td>\n      <td>17:32:20</td>\n      <td>Sunday</td>\n      <td>2019</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Sports Bars</td>\n      <td>Sports Bar</td>\n      <td>Sports Bar</td>\n      <td>nan</td>\n      <td>nan</td>\n      <td>57587782498e1367ff5c38ec</td>\n      <td>365 11th St</td>\n      <td>US</td>\n      <td>San Francisco</td>\n      <td>United States</td>\n      <td>...</td>\n      <td>37.77124505563008</td>\n      <td>-122.41299663242239</td>\n      <td>94103</td>\n      <td>CA</td>\n      <td>BuzzWorks</td>\n      <td>01/20/19</td>\n      <td>17:32:20</td>\n      <td>Sunday</td>\n      <td>2019</td>\n      <td>January</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Movie Theaters</td>\n      <td>Movie Theater</td>\n      <td>Movie Theater</td>\n      <td>16.0</td>\n      <td>16 movies</td>\n      <td>4390a026f964a5204d2b1fe3</td>\n      <td>1000 Van Ness Ave</td>\n      <td>US</td>\n      <td>San Francisco</td>\n      <td>United States</td>\n      <td>...</td>\n      <td>nan</td>\n      <td>37.78515072462566</td>\n      <td>-122.42012248044318</td>\n      <td>94109</td>\n      <td>CA</td>\n      <td>AMC Van Ness 14</td>\n      <td>01/20/19</td>\n      <td>17:32:20</td>\n      <td>Sunday</td>\n      <td>2019</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>Souvlaki Shops</td>\n      <td>Souvlaki</td>\n      <td>Souvlaki Shop</td>\n      <td>nan</td>\n      <td>nan</td>\n      <td>5748e1f0498e1c0c6d214098</td>\n      <td>531 Divisadero St</td>\n      <td>US</td>\n      <td>San Francisco</td>\n      <td>United States</td>\n      <td>...</td>\n      <td>37.77457655200334</td>\n      <td>-122.4378085167028</td>\n      <td>94117</td>\n      <td>CA</td>\n      <td>Souvla</td>\n      <td>01/20/19</td>\n      <td>17:32:20</td>\n      <td>Sunday</td>\n      <td>2019</td>\n      <td>January</td>\n    </tr>\n  </tbody>\n</table>\n<p>7 rows \u00d7 22 columns</p>\n</div>", 
                        "text/plain": "            categories1        categories2          categories3  \\\n0   Basketball Stadiums         Basketball    Basketball Stadiu   \n1    Convention Centers  Convention Center    Convention Center   \n2  Indie Movie Theaters       Indie Movies  Indie Movie Theater   \n3        Movie Theaters      Movie Theater        Movie Theater   \n4           Sports Bars         Sports Bar           Sports Bar   \n5        Movie Theaters      Movie Theater        Movie Theater   \n6        Souvlaki Shops           Souvlaki        Souvlaki Shop   \n\n                         id       location.address               location.cc  \\\n0  4ad4bff8f964a52002ea20e3  125 S Pennsylvania St                        US   \n1  5c40f328345cbe002c99e458                     US                  San Jose   \n2                       nan                    nan  4089ae00f964a520bff21ee3   \n3                      19.0              19 movies  4a5824e5f964a5206db71fe3   \n4                       nan                    nan  57587782498e1367ff5c38ec   \n5                      16.0              16 movies  4390a026f964a5204d2b1fe3   \n6                       nan                    nan  5748e1f0498e1c0c6d214098   \n\n         location.city                   location.country  \\\n0         Indianapolis                      United States   \n1        United States  San Jose, CA 95113, United States   \n2        429 Castro St                                 US   \n3  135 4th St Ste 3000                                 US   \n4          365 11th St                                 US   \n5    1000 Van Ness Ave                                 US   \n6    531 Divisadero St                                 US   \n\n  location.crossStreet                          location.formattedAddress  \\\n0      at E Georgia St  125 S Pennsylvania St (at E Georgia St), India...   \n1            37.329998                                        -121.889275   \n2        San Francisco                                      United States   \n3        San Francisco                                      United States   \n4        San Francisco                                      United States   \n5        San Francisco                                      United States   \n6        San Francisco                                      United States   \n\n    ...        location.postalCode       location.state  \\\n0   ...                      46204                   IN   \n1   ...     Further Confusion 2019             01/20/19   \n2   ...                        nan   37.761991717624404   \n3   ...                        nan    37.78457546024026   \n4   ...          37.77124505563008  -122.41299663242239   \n5   ...                        nan    37.78515072462566   \n6   ...          37.77457655200334   -122.4378085167028   \n\n                      name venuePage.id       date             time   weekday  \\\n0  Bankers Life Fieldhouse     42217987   01/20/19         17:32:20    Sunday   \n1                 17:32:20       Sunday       2019          January        PM   \n2      -122.43496924638747        94114         CA   Castro Theatre  01/20/19   \n3      -122.40323635482677        94103         CA   AMC Metreon 16  01/20/19   \n4                    94103           CA  BuzzWorks         01/20/19  17:32:20   \n5      -122.42012248044318        94109         CA  AMC Van Ness 14  01/20/19   \n6                    94117           CA     Souvla         01/20/19  17:32:20   \n\n       year    month    am_pm  \n0      2019  January       PM  \n1     blank    blank    blank  \n2  17:32:20   Sunday     2019  \n3  17:32:20   Sunday     2019  \n4    Sunday     2019  January  \n5  17:32:20   Sunday     2019  \n6    Sunday     2019  January  \n\n[7 rows x 22 columns]"
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "city_DF"
        }, 
        {
            "execution_count": 22, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "# The code was removed by Watson Studio for sharing."
        }, 
        {
            "execution_count": 23, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "output_type": "stream", 
                    "name": "stdout", 
                    "text": "<---- Connected to DataSciDB ---->\n"
                }
            ], 
            "source": "# The code was removed by Watson Studio for sharing."
        }, 
        {
            "execution_count": 24, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 24, 
                    "metadata": {}, 
                    "data": {
                        "text/plain": "7"
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "# The code was removed by Watson Studio for sharing."
        }, 
        {
            "execution_count": 25, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 25, 
                    "metadata": {}, 
                    "data": {
                        "text/plain": "True"
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "ibm_db.close(conn)"
        }
    ], 
    "metadata": {
        "kernelspec": {
            "display_name": "Python 3.5", 
            "name": "python3", 
            "language": "python"
        }, 
        "language_info": {
            "mimetype": "text/x-python", 
            "nbconvert_exporter": "python", 
            "version": "3.5.5", 
            "name": "python", 
            "file_extension": ".py", 
            "pygments_lexer": "ipython3", 
            "codemirror_mode": {
                "version": 3, 
                "name": "ipython"
            }
        }
    }, 
    "nbformat": 4
}