df_business_maker.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "import gmaps\n",
    "import gmaps.datasets\n",
    "import requests\n",
    "import pyspark\n",
    "import pandas as pd\n",
    "from pyspark.sql.functions import *\n",
    "from pyspark.sql.types import *\n",
    "from pyspark.sql import SQLContext\n",
    "import json\n",
    "import time\n",
    "from pyspark.sql.functions import mean, min, max\n",
    "from pyspark.sql import SparkSession\n",
    "from multiprocessing.dummy import Pool\n",
    "from gql import gql, Client\n",
    "from gql.transport.requests import RequestsHTTPTransport\n",
    "\n",
    "# export JAVA_HOME=$(/usr/libexec/java_home -v 1.8)\n",
    "# jupyter nbextension enable --py gmaps\n",
    "# jupyter nbextension enable --py widgetsnbextension\n",
    "headers = {\"Authorization\": \"Bearer IzOr-QX4Z7O0bNuxLzXvy3btTrdVJAjBsnZrNHAc6EVXcmI7jLs0lZYj0C3M3RzxKrr8hfY5VznFHUBDe6fd0ketGI-U2xYlrJrIoSCbqXFdhFW57hZdYFkoC1vNXXYx\",'Content-Type':\"application/json\"}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('apikey.txt') as f:\n",
    "    api_key = f.readline()\n",
    "    f.close"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "gmaps.configure(api_key=api_key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "spark = SparkSession.builder.appName(\"MinhaAplicacao\").getOrCreate()\n",
    "sc = spark.sparkContext\n",
    "sqlContext = pyspark.SQLContext(sc)\n",
    "\n",
    "df= spark.read.json('../yelp_academic_dataset_review.json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[business_id: string, cool: bigint, date: string, funny: bigint, review_id: string, stars: double, text: string, useful: bigint, user_id: string]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Agregação dos Business por métricas de avaliação"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+------------------+------------------+\n",
      "|         business_id|        avg(stars)|stddev_samp(stars)|\n",
      "+--------------------+------------------+------------------+\n",
      "|f4mh1Y0rnvbJRfQ3j...| 3.888888888888889|0.8831048713694643|\n",
      "|cKwg6HFaLYXl7Ar0r...|3.7777777777777777|1.8005082797253327|\n",
      "|jcpgiXF0PCyS9hrvq...| 4.333333333333333|1.2909944487358056|\n",
      "|R_M4P9XetEM-aLE7e...|3.9545454545454546|1.3682864437711102|\n",
      "|DEBqmgxv2yhJ93LqG...|3.8378378378378377|1.3852504895934594|\n",
      "|Cml4Yt5cTx64cOMan...| 2.933333333333333|1.9073791542572858|\n",
      "|bo3SQVtErnMOqO6lk...|3.7788461538461537|1.1902249992825316|\n",
      "|Cl-xl1vTUwHeaGgBx...|3.7883597883597884| 1.450484863054038|\n",
      "|oIEmXWLtoh5blz-iw...|3.7533333333333334| 1.725982520176626|\n",
      "|Op2IR4FffXZ5KXYFn...| 3.404494382022472|1.5791187208779367|\n",
      "|yB5FMuc9Y3oyhsOmu...|2.4782608695652173|1.8554481256654882|\n",
      "|cEqOh78v1g1RCWHyu...|3.4473684210526314| 1.266989801811655|\n",
      "|lt8IW9Bpy9GMeKGxy...|               3.8|1.6897255439251382|\n",
      "|uC3qwaxsOkdJzpOc0...|3.3283898305084745|1.2174418694373028|\n",
      "|686oeWNsbc-aczplC...| 3.727272727272727|1.1037127426019047|\n",
      "|gPuxh3HNvoVt8aWVW...| 3.357142857142857|1.0818177620697813|\n",
      "|mA27CG2U3ytmkxIGV...|              3.36|1.8230011885167086|\n",
      "|x6qH9HXhzuKM03jcZ...|3.1587301587301586| 1.547175878885401|\n",
      "|74LU6K2ro5AQXKT0J...|              4.25|  1.61245154965971|\n",
      "|TdefcbsFAj6WXHwlG...|3.3636363636363638|1.2862913567871996|\n",
      "+--------------------+------------------+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_business = df.groupBy('business_id').agg(avg('stars'),stddev('stars'))\n",
    "df_business.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Realizando requests para puxar as localizações"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Criando uma lista com os business_id para realizar uma consulta mais eficaz."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "list_business = df_business.select('business_id').rdd.flatMap(lambda x: x).collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Tentativa de multithreading(bloqueada pela api do yelp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# multi-threading test\n",
    "# val = [-4, -3, -2, -1]\n",
    "\n",
    "# geo_bus = pd.DataFrame(columns=['business_id','latitude','longitude'])\n",
    "# def reqs(x):\n",
    "#     while x <= len(list_business):\n",
    "#         print(list_business[x])\n",
    "#         x += 4\n",
    "#         r = requests.get(\"https://api.yelp.com/v3/businesses/\"+list_business[x],headers=headers)\n",
    "#         print(r)\n",
    "#         try:\n",
    "#             latitude = r.json()['coordinates']['latitude']\n",
    "#             longitude = r.json()['coordinates']['longitude']\n",
    "#             geo_bus.append({'business_id' : list_business[x] , 'latitude' : latitude, 'longitude' : longitude} , ignore_index=True)\n",
    "#         except:\n",
    "#             latitude = 'NaN'\n",
    "#             longitude = 'NaN' \n",
    "#             geo_bus.append({'business_id' : list_business[x] , 'latitude' : latitude, 'longitude' : longitude} , ignore_index=True)\n",
    "\n",
    "\n",
    "        \n",
    "            \n",
    "# pool = Pool(4)\n",
    "# pool.map(reqs, val)\n",
    "\n",
    "# pool.close()\n",
    "# pool.join()\n",
    "# print('FIM')\n",
    "\n",
    "            \n",
    "            "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Os requests foram feitos em menores quantidades pois a API não suportava muitos estabelecimentos de uma vez. Além disso, o iterador i foi incrementado na mão pois havia um limite de requests na nossa key de API, utilizamos varías e toda vez que travava inicializavamos o i no valor que havia travado. Por fim, os requests iam de 10 em 10 uma vez que alguns estabelecimentos não estão mais no yelp e quebravam a resposta, dessa forma escolhemos 10 para continuar sendo uma sequência de requsições rápidas porém quando 1 estabelecimento quebrasse a resposta não iríamos perder tantos dados."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "ename": "SSLError",
     "evalue": "HTTPSConnectionPool(host='api.yelp.com', port=443): Max retries exceeded with url: /v3/graphql (Caused by SSLError(SSLError(\"bad handshake: SysCallError(60, 'ETIMEDOUT')\")))",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mSysCallError\u001b[0m                              Traceback (most recent call last)",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/urllib3/contrib/pyopenssl.py\u001b[0m in \u001b[0;36mwrap_socket\u001b[0;34m(self, sock, server_side, do_handshake_on_connect, suppress_ragged_eofs, server_hostname)\u001b[0m\n\u001b[1;32m    452\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 453\u001b[0;31m                 \u001b[0mcnx\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdo_handshake\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    454\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mOpenSSL\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mSSL\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mWantReadError\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/OpenSSL/SSL.py\u001b[0m in \u001b[0;36mdo_handshake\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1914\u001b[0m         \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_lib\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mSSL_do_handshake\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_ssl\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1915\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_raise_ssl_error\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_ssl\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1916\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/OpenSSL/SSL.py\u001b[0m in \u001b[0;36m_raise_ssl_error\u001b[0;34m(self, ssl, result)\u001b[0m\n\u001b[1;32m   1638\u001b[0m                     \u001b[0;32mif\u001b[0m \u001b[0merrno\u001b[0m \u001b[0;34m!=\u001b[0m \u001b[0;36m0\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1639\u001b[0;31m                         \u001b[0;32mraise\u001b[0m \u001b[0mSysCallError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0merrno\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0merrorcode\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0merrno\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1640\u001b[0m                 \u001b[0;32mraise\u001b[0m \u001b[0mSysCallError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m-\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"Unexpected EOF\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mSysCallError\u001b[0m: (60, 'ETIMEDOUT')",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mSSLError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/urllib3/connectionpool.py\u001b[0m in \u001b[0;36murlopen\u001b[0;34m(self, method, url, body, headers, retries, redirect, assert_same_host, timeout, pool_timeout, release_conn, chunked, body_pos, **response_kw)\u001b[0m\n\u001b[1;32m    599\u001b[0m                                                   \u001b[0mbody\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mbody\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mheaders\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mheaders\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 600\u001b[0;31m                                                   chunked=chunked)\n\u001b[0m\u001b[1;32m    601\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/urllib3/connectionpool.py\u001b[0m in \u001b[0;36m_make_request\u001b[0;34m(self, conn, method, url, timeout, chunked, **httplib_request_kw)\u001b[0m\n\u001b[1;32m    342\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 343\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_validate_conn\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    344\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mSocketTimeout\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mBaseSSLError\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/urllib3/connectionpool.py\u001b[0m in \u001b[0;36m_validate_conn\u001b[0;34m(self, conn)\u001b[0m\n\u001b[1;32m    838\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mgetattr\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'sock'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m  \u001b[0;31m# AppEngine might not have  `.sock`\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 839\u001b[0;31m             \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    840\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/urllib3/connection.py\u001b[0m in \u001b[0;36mconnect\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    343\u001b[0m             \u001b[0mserver_hostname\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mserver_hostname\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 344\u001b[0;31m             ssl_context=context)\n\u001b[0m\u001b[1;32m    345\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/urllib3/util/ssl_.py\u001b[0m in \u001b[0;36mssl_wrap_socket\u001b[0;34m(sock, keyfile, certfile, cert_reqs, ca_certs, server_hostname, ssl_version, ciphers, ssl_context, ca_cert_dir)\u001b[0m\n\u001b[1;32m    343\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mHAS_SNI\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mserver_hostname\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 344\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwrap_socket\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msock\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mserver_hostname\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mserver_hostname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    345\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/urllib3/contrib/pyopenssl.py\u001b[0m in \u001b[0;36mwrap_socket\u001b[0;34m(self, sock, server_side, do_handshake_on_connect, suppress_ragged_eofs, server_hostname)\u001b[0m\n\u001b[1;32m    458\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mOpenSSL\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mSSL\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 459\u001b[0;31m                 \u001b[0;32mraise\u001b[0m \u001b[0mssl\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mSSLError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'bad handshake: %r'\u001b[0m \u001b[0;34m%\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    460\u001b[0m             \u001b[0;32mbreak\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mSSLError\u001b[0m: (\"bad handshake: SysCallError(60, 'ETIMEDOUT')\",)",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mMaxRetryError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/requests/adapters.py\u001b[0m in \u001b[0;36msend\u001b[0;34m(self, request, stream, timeout, verify, cert, proxies)\u001b[0m\n\u001b[1;32m    448\u001b[0m                     \u001b[0mretries\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmax_retries\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 449\u001b[0;31m                     \u001b[0mtimeout\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mtimeout\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    450\u001b[0m                 )\n",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/urllib3/connectionpool.py\u001b[0m in \u001b[0;36murlopen\u001b[0;34m(self, method, url, body, headers, retries, redirect, assert_same_host, timeout, pool_timeout, release_conn, chunked, body_pos, **response_kw)\u001b[0m\n\u001b[1;32m    637\u001b[0m             retries = retries.increment(method, url, error=e, _pool=self,\n\u001b[0;32m--> 638\u001b[0;31m                                         _stacktrace=sys.exc_info()[2])\n\u001b[0m\u001b[1;32m    639\u001b[0m             \u001b[0mretries\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msleep\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/urllib3/util/retry.py\u001b[0m in \u001b[0;36mincrement\u001b[0;34m(self, method, url, response, error, _pool, _stacktrace)\u001b[0m\n\u001b[1;32m    397\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mnew_retry\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mis_exhausted\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 398\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mMaxRetryError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0m_pool\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0murl\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0merror\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mResponseError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcause\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    399\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mMaxRetryError\u001b[0m: HTTPSConnectionPool(host='api.yelp.com', port=443): Max retries exceeded with url: /v3/graphql (Caused by SSLError(SSLError(\"bad handshake: SysCallError(60, 'ETIMEDOUT')\")))",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mSSLError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-119-4acf1d11f76e>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m     22\u001b[0m     \u001b[0mtransport\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mRequestsHTTPTransport\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0murl\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'https://api.yelp.com/v3/graphql'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mheaders\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mheaders\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0muse_json\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     23\u001b[0m     \u001b[0;31m# Create the client\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 24\u001b[0;31m     \u001b[0mclient\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mClient\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtransport\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mtransport\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfetch_schema_from_transport\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     25\u001b[0m     \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     26\u001b[0m         \u001b[0mreq\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mclient\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mgql\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquerys\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/gql/client.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, schema, introspection, type_def, transport, fetch_schema_from_transport, retries)\u001b[0m\n\u001b[1;32m     21\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mtransport\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mfetch_schema_from_transport\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     22\u001b[0m             \u001b[0;32massert\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mschema\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'Cant fetch the schema from transport if is already provided'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 23\u001b[0;31m             \u001b[0mintrospection\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mtransport\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mparse\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mintrospection_query\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdata\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     24\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mintrospection\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     25\u001b[0m             \u001b[0;32massert\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mschema\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'Cant provide introspection and schema at the same time'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/gql/transport/requests.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, document, variable_values, timeout)\u001b[0m\n\u001b[1;32m     35\u001b[0m             \u001b[0mdata_key\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mpayload\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     36\u001b[0m         }\n\u001b[0;32m---> 37\u001b[0;31m         \u001b[0mrequest\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mrequests\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpost\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0murl\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mpost_args\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     38\u001b[0m         \u001b[0mrequest\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_for_status\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     39\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/requests/api.py\u001b[0m in \u001b[0;36mpost\u001b[0;34m(url, data, json, **kwargs)\u001b[0m\n\u001b[1;32m    114\u001b[0m     \"\"\"\n\u001b[1;32m    115\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 116\u001b[0;31m     \u001b[0;32mreturn\u001b[0m \u001b[0mrequest\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'post'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0murl\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdata\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mdata\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mjson\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mjson\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    117\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    118\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/requests/api.py\u001b[0m in \u001b[0;36mrequest\u001b[0;34m(method, url, **kwargs)\u001b[0m\n\u001b[1;32m     58\u001b[0m     \u001b[0;31m# cases, and look like a memory leak in others.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     59\u001b[0m     \u001b[0;32mwith\u001b[0m \u001b[0msessions\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mSession\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0msession\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 60\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0msession\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrequest\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmethod\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0murl\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0murl\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     61\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     62\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/requests/sessions.py\u001b[0m in \u001b[0;36mrequest\u001b[0;34m(self, method, url, params, data, headers, cookies, files, auth, timeout, allow_redirects, proxies, hooks, stream, verify, cert, json)\u001b[0m\n\u001b[1;32m    531\u001b[0m         }\n\u001b[1;32m    532\u001b[0m         \u001b[0msend_kwargs\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mupdate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msettings\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 533\u001b[0;31m         \u001b[0mresp\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msend\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mprep\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0msend_kwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    534\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    535\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mresp\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/requests/sessions.py\u001b[0m in \u001b[0;36msend\u001b[0;34m(self, request, **kwargs)\u001b[0m\n\u001b[1;32m    644\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    645\u001b[0m         \u001b[0;31m# Send the request\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 646\u001b[0;31m         \u001b[0mr\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0madapter\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msend\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mrequest\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    647\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    648\u001b[0m         \u001b[0;31m# Total elapsed time of the request (approximately)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/lib/python3.7/site-packages/requests/adapters.py\u001b[0m in \u001b[0;36msend\u001b[0;34m(self, request, stream, timeout, verify, cert, proxies)\u001b[0m\n\u001b[1;32m    512\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreason\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0m_SSLError\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    513\u001b[0m                 \u001b[0;31m# This branch is for urllib3 v1.22 and later.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 514\u001b[0;31m                 \u001b[0;32mraise\u001b[0m \u001b[0mSSLError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mrequest\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mrequest\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    515\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    516\u001b[0m             \u001b[0;32mraise\u001b[0m \u001b[0mConnectionError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mrequest\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mrequest\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mSSLError\u001b[0m: HTTPSConnectionPool(host='api.yelp.com', port=443): Max retries exceeded with url: /v3/graphql (Caused by SSLError(SSLError(\"bad handshake: SysCallError(60, 'ETIMEDOUT')\")))"
     ]
    }
   ],
   "source": [
    "# reqs = []\n",
    "# indexs = []\n",
    "i = 0\n",
    "sep = ''','''\n",
    "while i<192606:\n",
    "    limite = i+10\n",
    "    for bid in range(i,limite):\n",
    "        sep = ''','''\n",
    "        if bid == i:\n",
    "            #print('0')\n",
    "            querys = '''{'''\n",
    "            query = '''bs%s:business(id: \"%s\") { coordinates{ latitude , longitude} categories {title} }''' % (str(bid),str(list_business[bid]))\n",
    "            querys = querys + query\n",
    "        else:\n",
    "            query = '''bs%s:business(id: \"%s\") { coordinates{ latitude , longitude} categories {title} }''' % (str(bid),str(list_business[bid]))\n",
    "\n",
    "            querys = querys + sep + query\n",
    "\n",
    "    querys = querys + '''}'''\n",
    "    \n",
    "    # Build the request framework\n",
    "    transport = RequestsHTTPTransport(url='https://api.yelp.com/v3/graphql', headers=headers, use_json=True)\n",
    "    # Create the client\n",
    "    client = Client(transport=transport, fetch_schema_from_transport=True)\n",
    "    try:\n",
    "        req = client.execute(gql(querys))\n",
    "        for i in req:\n",
    "            indexs.append(i[2:])\n",
    "            reqs.append(req[i])\n",
    "        #print(req)\n",
    "        #reqs.append(req)\n",
    "    except Exception as e:\n",
    "        pass\n",
    "        #print(e)\n",
    "    \n",
    "    i = limite\n",
    "    #print(i)\n",
    "    #time.sleep(5)\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Erro quando a key da API limitava os requestes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Depois foi criado um dataframe pandas com as informações coletadas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [],
   "source": [
    "valids_ids = []\n",
    "latitudes = []\n",
    "longitudes = []\n",
    "categorias = []\n",
    "for i in range(len(indexs)):\n",
    "    if reqs[i]['coordinates'] != None:\n",
    "        latitudes.append(reqs[i]['coordinates']['latitude'])\n",
    "        longitudes.append(reqs[i]['coordinates']['longitude'])\n",
    "        valids_ids.append(list_business[int(indexs[i])])\n",
    "        try:\n",
    "            categorias.append(reqs[i]['categories'][0]['title'])\n",
    "        except:\n",
    "            categorias.append('Null')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame()\n",
    "df['idBusiness'] = valids_ids\n",
    "df['Categorias'] = categorias\n",
    "df['Latitude'] = latitudes\n",
    "df['Longitude'] = longitudes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [],
   "source": [
    "Business.to_csv('business.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "teste_df = pd.read_csv(\"business.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "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>idBusiness</th>\n",
       "      <th>Categorias</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>f4mh1Y0rnvbJRfQ3jPkqzQ</td>\n",
       "      <td>Cinema</td>\n",
       "      <td>35.371907</td>\n",
       "      <td>-80.720647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>cKwg6HFaLYXl7Ar0rHojAQ</td>\n",
       "      <td>Garage Door Services</td>\n",
       "      <td>36.172607</td>\n",
       "      <td>-115.122047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>jcpgiXF0PCyS9hrvqseGEA</td>\n",
       "      <td>Archery</td>\n",
       "      <td>33.683247</td>\n",
       "      <td>-112.087412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>R_M4P9XetEM-aLE7eHdthw</td>\n",
       "      <td>Japanese</td>\n",
       "      <td>33.390602</td>\n",
       "      <td>-111.864378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DEBqmgxv2yhJ93LqGpKltg</td>\n",
       "      <td>Sandwiches</td>\n",
       "      <td>33.425173</td>\n",
       "      <td>-111.939737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Cml4Yt5cTx64cOManHe-Gw</td>\n",
       "      <td>Pizza</td>\n",
       "      <td>33.540123</td>\n",
       "      <td>-112.133850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>bo3SQVtErnMOqO6lkxWdkw</td>\n",
       "      <td>American (New)</td>\n",
       "      <td>41.139262</td>\n",
       "      <td>-81.863551</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Cl-xl1vTUwHeaGgBxzdTRA</td>\n",
       "      <td>Chinese</td>\n",
       "      <td>36.160407</td>\n",
       "      <td>-115.321600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>oIEmXWLtoh5blz-iwI8MZQ</td>\n",
       "      <td>Tires</td>\n",
       "      <td>33.475212</td>\n",
       "      <td>-111.926504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Op2IR4FffXZ5KXYFnDL2yA</td>\n",
       "      <td>American (New)</td>\n",
       "      <td>41.388740</td>\n",
       "      <td>-81.439410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>yB5FMuc9Y3oyhsOmuIZSpQ</td>\n",
       "      <td>Fast Food</td>\n",
       "      <td>33.582350</td>\n",
       "      <td>-112.183610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>cEqOh78v1g1RCWHyuHwQng</td>\n",
       "      <td>Buffets</td>\n",
       "      <td>51.108566</td>\n",
       "      <td>-114.040081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>lt8IW9Bpy9GMeKGxytefVA</td>\n",
       "      <td>Tires</td>\n",
       "      <td>36.166123</td>\n",
       "      <td>-115.259416</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>uC3qwaxsOkdJzpOc0v1Mog</td>\n",
       "      <td>American (Traditional)</td>\n",
       "      <td>43.073005</td>\n",
       "      <td>-89.453546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>686oeWNsbc-aczplCFa_0g</td>\n",
       "      <td>Grocery</td>\n",
       "      <td>33.525193</td>\n",
       "      <td>-112.115398</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>gPuxh3HNvoVt8aWVWhgGcA</td>\n",
       "      <td>Desserts</td>\n",
       "      <td>45.566562</td>\n",
       "      <td>-73.729290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>mA27CG2U3ytmkxIGVnk0jQ</td>\n",
       "      <td>Tanning</td>\n",
       "      <td>33.770994</td>\n",
       "      <td>-111.979369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>x6qH9HXhzuKM03jcZmg-ZQ</td>\n",
       "      <td>Sandwiches</td>\n",
       "      <td>33.619480</td>\n",
       "      <td>-111.898193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>74LU6K2ro5AQXKT0Jf0SPw</td>\n",
       "      <td>Medical Spas</td>\n",
       "      <td>36.197826</td>\n",
       "      <td>-115.279365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>TdefcbsFAj6WXHwlGQHIbA</td>\n",
       "      <td>Playgrounds</td>\n",
       "      <td>33.337690</td>\n",
       "      <td>-111.994510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>E9QTQ4DOKo1UsGNmMX8sGQ</td>\n",
       "      <td>Nail Salons</td>\n",
       "      <td>33.379450</td>\n",
       "      <td>-111.910510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>MEoDTsA3Af6TLzB7Z_7oDw</td>\n",
       "      <td>Sushi Bars</td>\n",
       "      <td>45.493778</td>\n",
       "      <td>-73.579543</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>kr5JmGtVl0pUq9-KB_91QA</td>\n",
       "      <td>Gyms</td>\n",
       "      <td>33.464980</td>\n",
       "      <td>-111.908773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>6QRY4tMQdHFOe2-_CUHMdA</td>\n",
       "      <td>Fast Food</td>\n",
       "      <td>33.480850</td>\n",
       "      <td>-111.998490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>09p3b5BCSz2FPvgR-8xz3A</td>\n",
       "      <td>Auto Repair</td>\n",
       "      <td>43.702960</td>\n",
       "      <td>-79.397860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>aAiD7Zsq2OeEFEOWmfQ8ug</td>\n",
       "      <td>Hair Salons</td>\n",
       "      <td>33.495046</td>\n",
       "      <td>-112.014701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>ipFreSFhjClfNETuMjBtrA</td>\n",
       "      <td>American (Traditional)</td>\n",
       "      <td>41.318920</td>\n",
       "      <td>-81.347068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>7coCBjZNMJ48BD2taPKgHQ</td>\n",
       "      <td>Waxing</td>\n",
       "      <td>36.101301</td>\n",
       "      <td>-115.301993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>9ypHY6MNp3CDDdQJ_d817A</td>\n",
       "      <td>Hookah Bars</td>\n",
       "      <td>36.012150</td>\n",
       "      <td>-115.153305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1UwaMUnVKeWcV14qvDmBRg</td>\n",
       "      <td>Japanese</td>\n",
       "      <td>43.720630</td>\n",
       "      <td>-79.455770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126486</th>\n",
       "      <td>IugRr6V7FZ9G4XNM6L571g</td>\n",
       "      <td>Home Cleaning</td>\n",
       "      <td>33.659664</td>\n",
       "      <td>-112.032310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126487</th>\n",
       "      <td>UqlMkfiUaB73ISSDm9SkIg</td>\n",
       "      <td>Italian</td>\n",
       "      <td>45.566843</td>\n",
       "      <td>-73.448524</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126488</th>\n",
       "      <td>nLTThR88xzfrnEkRTAKLSA</td>\n",
       "      <td>Indian</td>\n",
       "      <td>45.478740</td>\n",
       "      <td>-73.568380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126489</th>\n",
       "      <td>wh7ZGSC-EOOkMKig9jwX3g</td>\n",
       "      <td>Nail Salons</td>\n",
       "      <td>40.335358</td>\n",
       "      <td>-79.804008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126490</th>\n",
       "      <td>lsXRALbqJSZ_tkXaLZOumg</td>\n",
       "      <td>Specialty Food</td>\n",
       "      <td>33.455670</td>\n",
       "      <td>-112.255380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126491</th>\n",
       "      <td>PECpU6MA-jnZj2Y0yXD0SQ</td>\n",
       "      <td>Farmers Market</td>\n",
       "      <td>43.647923</td>\n",
       "      <td>-79.443784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126492</th>\n",
       "      <td>r6V0rVP_N2afc_BQNzfClA</td>\n",
       "      <td>Chocolatiers &amp; Shops</td>\n",
       "      <td>40.484852</td>\n",
       "      <td>-79.971619</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126493</th>\n",
       "      <td>Y-6XmbE3QFvQfcmpP1ix5Q</td>\n",
       "      <td>Physical Therapy</td>\n",
       "      <td>33.463469</td>\n",
       "      <td>-112.336850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126494</th>\n",
       "      <td>pzK_QvFj734swtpFyR2qhg</td>\n",
       "      <td>Insurance</td>\n",
       "      <td>40.498789</td>\n",
       "      <td>-79.866185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126495</th>\n",
       "      <td>76bWMQNr42N-XGilUXWhvw</td>\n",
       "      <td>Carpet Cleaning</td>\n",
       "      <td>33.539940</td>\n",
       "      <td>-112.177210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126496</th>\n",
       "      <td>uuyasMBXHhHXTBVyfuS_fQ</td>\n",
       "      <td>Web Design</td>\n",
       "      <td>51.048273</td>\n",
       "      <td>-114.065851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126497</th>\n",
       "      <td>YJ03uZuNX8huNmnCP3aICA</td>\n",
       "      <td>Driving Schools</td>\n",
       "      <td>41.171150</td>\n",
       "      <td>-81.860718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126498</th>\n",
       "      <td>uYdHAexheu3wim1ST5nrbw</td>\n",
       "      <td>Cannabis Clinics</td>\n",
       "      <td>51.097410</td>\n",
       "      <td>-114.141920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126499</th>\n",
       "      <td>-zt3P7AVdmvAk5X5QdFCVg</td>\n",
       "      <td>Body Shops</td>\n",
       "      <td>33.638791</td>\n",
       "      <td>-112.081948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126500</th>\n",
       "      <td>emMNb3q6cbEDZyc_JAgoww</td>\n",
       "      <td>Burgers</td>\n",
       "      <td>33.377488</td>\n",
       "      <td>-111.907314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126501</th>\n",
       "      <td>CK-Gv3vqIlWOrKP4fhT8_g</td>\n",
       "      <td>Pubs</td>\n",
       "      <td>40.432083</td>\n",
       "      <td>-79.893639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126502</th>\n",
       "      <td>Im0hMcWaqWIzuBM8xILDsA</td>\n",
       "      <td>American (New)</td>\n",
       "      <td>41.313931</td>\n",
       "      <td>-81.819294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126503</th>\n",
       "      <td>5O7qB4gNmr3NgdYAuYbP4Q</td>\n",
       "      <td>Indian</td>\n",
       "      <td>36.125600</td>\n",
       "      <td>-115.211210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126504</th>\n",
       "      <td>ejSvwvEfS6pjVrFR5WQ2pA</td>\n",
       "      <td>Comedy Clubs</td>\n",
       "      <td>36.102040</td>\n",
       "      <td>-115.168252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126505</th>\n",
       "      <td>cRuk2y-LeWL42UVEkLVU3A</td>\n",
       "      <td>American (Traditional)</td>\n",
       "      <td>43.651400</td>\n",
       "      <td>-79.383120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126506</th>\n",
       "      <td>kcF4zNo7dLECwHml9GOv_A</td>\n",
       "      <td>Yoga</td>\n",
       "      <td>51.035672</td>\n",
       "      <td>-114.036765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126507</th>\n",
       "      <td>FEST-KPp7yupwqkArWvm2g</td>\n",
       "      <td>Pubs</td>\n",
       "      <td>41.442741</td>\n",
       "      <td>-81.861610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126508</th>\n",
       "      <td>Om5mo4oWKPn_kPpLjNiV4Q</td>\n",
       "      <td>Breakfast &amp; Brunch</td>\n",
       "      <td>33.249638</td>\n",
       "      <td>-111.839916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126509</th>\n",
       "      <td>bbGU6_R4pp9UHOlWKItIbw</td>\n",
       "      <td>Jewelry</td>\n",
       "      <td>33.479926</td>\n",
       "      <td>-111.908404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126510</th>\n",
       "      <td>E9S47FGELIsOAyJQdKoIAQ</td>\n",
       "      <td>Garage Door Services</td>\n",
       "      <td>36.041183</td>\n",
       "      <td>-115.032065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126511</th>\n",
       "      <td>LnIO_JtoDS1xLfTHWuV_ug</td>\n",
       "      <td>Mexican</td>\n",
       "      <td>33.279530</td>\n",
       "      <td>-111.790250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126512</th>\n",
       "      <td>Y5AahskUCn0NT_4yQdGGcg</td>\n",
       "      <td>Grocery</td>\n",
       "      <td>36.165951</td>\n",
       "      <td>-115.063611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126513</th>\n",
       "      <td>VTfr9Lgt47E_zj0vN6KDMw</td>\n",
       "      <td>Performing Arts</td>\n",
       "      <td>33.445899</td>\n",
       "      <td>-112.071311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126514</th>\n",
       "      <td>caJnjD34bMXj3J4w9enBRQ</td>\n",
       "      <td>Auto Repair</td>\n",
       "      <td>41.488120</td>\n",
       "      <td>-81.596530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126515</th>\n",
       "      <td>O_YSJEMk2GzcmnwXWBj74g</td>\n",
       "      <td>Fast Food</td>\n",
       "      <td>36.172162</td>\n",
       "      <td>-115.146667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>126516 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    idBusiness              Categorias   Latitude   Longitude\n",
       "0       f4mh1Y0rnvbJRfQ3jPkqzQ                  Cinema  35.371907  -80.720647\n",
       "1       cKwg6HFaLYXl7Ar0rHojAQ    Garage Door Services  36.172607 -115.122047\n",
       "2       jcpgiXF0PCyS9hrvqseGEA                 Archery  33.683247 -112.087412\n",
       "3       R_M4P9XetEM-aLE7eHdthw                Japanese  33.390602 -111.864378\n",
       "4       DEBqmgxv2yhJ93LqGpKltg              Sandwiches  33.425173 -111.939737\n",
       "5       Cml4Yt5cTx64cOManHe-Gw                   Pizza  33.540123 -112.133850\n",
       "6       bo3SQVtErnMOqO6lkxWdkw          American (New)  41.139262  -81.863551\n",
       "7       Cl-xl1vTUwHeaGgBxzdTRA                 Chinese  36.160407 -115.321600\n",
       "8       oIEmXWLtoh5blz-iwI8MZQ                   Tires  33.475212 -111.926504\n",
       "9       Op2IR4FffXZ5KXYFnDL2yA          American (New)  41.388740  -81.439410\n",
       "10      yB5FMuc9Y3oyhsOmuIZSpQ               Fast Food  33.582350 -112.183610\n",
       "11      cEqOh78v1g1RCWHyuHwQng                 Buffets  51.108566 -114.040081\n",
       "12      lt8IW9Bpy9GMeKGxytefVA                   Tires  36.166123 -115.259416\n",
       "13      uC3qwaxsOkdJzpOc0v1Mog  American (Traditional)  43.073005  -89.453546\n",
       "14      686oeWNsbc-aczplCFa_0g                 Grocery  33.525193 -112.115398\n",
       "15      gPuxh3HNvoVt8aWVWhgGcA                Desserts  45.566562  -73.729290\n",
       "16      mA27CG2U3ytmkxIGVnk0jQ                 Tanning  33.770994 -111.979369\n",
       "17      x6qH9HXhzuKM03jcZmg-ZQ              Sandwiches  33.619480 -111.898193\n",
       "18      74LU6K2ro5AQXKT0Jf0SPw            Medical Spas  36.197826 -115.279365\n",
       "19      TdefcbsFAj6WXHwlGQHIbA             Playgrounds  33.337690 -111.994510\n",
       "20      E9QTQ4DOKo1UsGNmMX8sGQ             Nail Salons  33.379450 -111.910510\n",
       "21      MEoDTsA3Af6TLzB7Z_7oDw              Sushi Bars  45.493778  -73.579543\n",
       "22      kr5JmGtVl0pUq9-KB_91QA                    Gyms  33.464980 -111.908773\n",
       "23      6QRY4tMQdHFOe2-_CUHMdA               Fast Food  33.480850 -111.998490\n",
       "24      09p3b5BCSz2FPvgR-8xz3A             Auto Repair  43.702960  -79.397860\n",
       "25      aAiD7Zsq2OeEFEOWmfQ8ug             Hair Salons  33.495046 -112.014701\n",
       "26      ipFreSFhjClfNETuMjBtrA  American (Traditional)  41.318920  -81.347068\n",
       "27      7coCBjZNMJ48BD2taPKgHQ                  Waxing  36.101301 -115.301993\n",
       "28      9ypHY6MNp3CDDdQJ_d817A             Hookah Bars  36.012150 -115.153305\n",
       "29      1UwaMUnVKeWcV14qvDmBRg                Japanese  43.720630  -79.455770\n",
       "...                        ...                     ...        ...         ...\n",
       "126486  IugRr6V7FZ9G4XNM6L571g           Home Cleaning  33.659664 -112.032310\n",
       "126487  UqlMkfiUaB73ISSDm9SkIg                 Italian  45.566843  -73.448524\n",
       "126488  nLTThR88xzfrnEkRTAKLSA                  Indian  45.478740  -73.568380\n",
       "126489  wh7ZGSC-EOOkMKig9jwX3g             Nail Salons  40.335358  -79.804008\n",
       "126490  lsXRALbqJSZ_tkXaLZOumg          Specialty Food  33.455670 -112.255380\n",
       "126491  PECpU6MA-jnZj2Y0yXD0SQ          Farmers Market  43.647923  -79.443784\n",
       "126492  r6V0rVP_N2afc_BQNzfClA    Chocolatiers & Shops  40.484852  -79.971619\n",
       "126493  Y-6XmbE3QFvQfcmpP1ix5Q        Physical Therapy  33.463469 -112.336850\n",
       "126494  pzK_QvFj734swtpFyR2qhg               Insurance  40.498789  -79.866185\n",
       "126495  76bWMQNr42N-XGilUXWhvw         Carpet Cleaning  33.539940 -112.177210\n",
       "126496  uuyasMBXHhHXTBVyfuS_fQ              Web Design  51.048273 -114.065851\n",
       "126497  YJ03uZuNX8huNmnCP3aICA         Driving Schools  41.171150  -81.860718\n",
       "126498  uYdHAexheu3wim1ST5nrbw        Cannabis Clinics  51.097410 -114.141920\n",
       "126499  -zt3P7AVdmvAk5X5QdFCVg              Body Shops  33.638791 -112.081948\n",
       "126500  emMNb3q6cbEDZyc_JAgoww                 Burgers  33.377488 -111.907314\n",
       "126501  CK-Gv3vqIlWOrKP4fhT8_g                    Pubs  40.432083  -79.893639\n",
       "126502  Im0hMcWaqWIzuBM8xILDsA          American (New)  41.313931  -81.819294\n",
       "126503  5O7qB4gNmr3NgdYAuYbP4Q                  Indian  36.125600 -115.211210\n",
       "126504  ejSvwvEfS6pjVrFR5WQ2pA            Comedy Clubs  36.102040 -115.168252\n",
       "126505  cRuk2y-LeWL42UVEkLVU3A  American (Traditional)  43.651400  -79.383120\n",
       "126506  kcF4zNo7dLECwHml9GOv_A                    Yoga  51.035672 -114.036765\n",
       "126507  FEST-KPp7yupwqkArWvm2g                    Pubs  41.442741  -81.861610\n",
       "126508  Om5mo4oWKPn_kPpLjNiV4Q      Breakfast & Brunch  33.249638 -111.839916\n",
       "126509  bbGU6_R4pp9UHOlWKItIbw                 Jewelry  33.479926 -111.908404\n",
       "126510  E9S47FGELIsOAyJQdKoIAQ    Garage Door Services  36.041183 -115.032065\n",
       "126511  LnIO_JtoDS1xLfTHWuV_ug                 Mexican  33.279530 -111.790250\n",
       "126512  Y5AahskUCn0NT_4yQdGGcg                 Grocery  36.165951 -115.063611\n",
       "126513  VTfr9Lgt47E_zj0vN6KDMw         Performing Arts  33.445899 -112.071311\n",
       "126514  caJnjD34bMXj3J4w9enBRQ             Auto Repair  41.488120  -81.596530\n",
       "126515  O_YSJEMk2GzcmnwXWBj74g               Fast Food  36.172162 -115.146667\n",
       "\n",
       "[126516 rows x 4 columns]"
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "teste_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Juntando o dataframe criado com a API com dados coletados do dataframe do Yelp com um outer join e exportando para .csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "business_spark = df.groupBy('business_id').agg(avg('stars'),stddev('stars'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "business_df = spark.read.format(\"csv\").option(\"header\", \"true\").load(\"./tests_script/business.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Row(idBusiness='f4mh1Y0rnvbJRfQ3jPkqzQ', Categorias='Cinema', Latitude='35.3719073', Longitude='-80.7206466')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "business_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Row(business_id='f4mh1Y0rnvbJRfQ3jPkqzQ', avg(stars)=3.888888888888889, stddev_samp(stars)=0.8831048713694643)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "business_spark.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+------------------+------------------+--------------------+----------------+-----------------+\n",
      "|         business_id|        avg(stars)|stddev_samp(stars)|          Categorias|        Latitude|        Longitude|\n",
      "+--------------------+------------------+------------------+--------------------+----------------+-----------------+\n",
      "|--9e1ONYQuAa-CB_R...| 4.125827814569536|1.1076682299694773|         Steakhouses|       36.123183|       -115.16919|\n",
      "|-6c_bJblLXUwoWfmY...|2.3333333333333335|               2.0|         Pet Sitting|      43.6267627|      -79.4790443|\n",
      "|-ElDqujEn1u64ynQr...| 4.133333333333334| 1.641718031587061|Divorce & Family Law|        36.13009|       -115.22807|\n",
      "|-Gh9a15ijNii-8rnp...|3.3333333333333335|2.0816659994661326|         Auto Repair|43.6186366615189|-79.7059750578865|\n",
      "|-I06hkMFrX0KBqu61...|               5.0|               0.0|  Elementary Schools|         36.1954|       -115.15729|\n",
      "|-Qb6U1MotJfpt6OU_...|               5.0|               0.0|        Pet Training|      41.2406311|      -81.6383591|\n",
      "|-RRnldwSqCZT6OpuW...|               5.0|               0.0|      Men's Clothing|      33.4556656|     -112.0723114|\n",
      "|-TGwtTiieh_JLml5_...|               3.5|1.1952286093343936|        Coffee & Tea|       43.884114|       -79.441195|\n",
      "|-VAsjhmAbKF3Pb_-8...|               2.0|1.0540925533894598|             Burgers|       45.515767|       -73.558392|\n",
      "|-WGjtt88-6zBiIUiK...|2.6666666666666665| 2.081665999466133|         Nail Salons|      43.8594156|       -78.947639|\n",
      "|-cxD1NimFldATDUsN...| 2.217391304347826|1.1660547990838181|             Mexican|       36.102737|      -115.202016|\n",
      "|-m8v19CXrGEYr3PAL...|               4.0|               1.0|        Music & DVDs|        41.45178|        -81.80936|\n",
      "|-qL-wX-UuMaHDBA2X...|3.6989247311827955| 1.705393912398815|            Car Wash|        33.63937|       -111.87926|\n",
      "|-r8SvItXXG6_T3mP5...|               4.0|1.3416407864998738|             Chinese|      51.0340032|     -114.0707963|\n",
      "|-wby-wyqaq1ZDTFDT...|               3.0|1.9306145983268455|          Home Decor|          36.175|      -115.136389|\n",
      "|069TWjwxctY-3X_Ti...| 3.900763358778626|1.4876670524531976|         Hookah Bars|36.1029052734375|-115.155708312988|\n",
      "|0859wfd1BQHG46Zpw...| 4.441947565543071| 0.875416522271459|      American (New)|       33.474235|      -112.074607|\n",
      "|09OYbFNrS1n8u5gE6...|4.1923076923076925|1.0590271297461937|            Bakeries|        43.69412|        -79.34249|\n",
      "|09p3b5BCSz2FPvgR-...|2.4166666666666665|1.7816403745544227|         Auto Repair|        43.70296|        -79.39786|\n",
      "|0DwMrcy7_X_C_mP8_...|2.6666666666666665| 2.081665999466133|                null|            null|             null|\n",
      "+--------------------+------------------+------------------+--------------------+----------------+-----------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "outer_join = (business_spark.join(business_df, business_df.idBusiness == business_spark.business_id, how='outer')).drop('idBusiness')\n",
    "outer_join.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_business = outer_join.toPandas()  # Transforma em um df em Pandas pois é mais fácil de exportar para csv\n",
    "df_business.to_csv(\"./tests_script/df_business.csv\",index=False)"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}