main.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pymongo import MongoClient\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import json,requests\n",
    "import folium\n",
    "from folium.plugins import MeasureControl\n",
    "from folium.plugins import HeatMap\n",
    "import os\n",
    "from dotenv import load_dotenv\n",
    "\n",
    "%matplotlib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "client=MongoClient(\"mongodb://localhost:27017/\")\n",
    "db=client.companies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "companies=db.companies.find()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "datos= pd.DataFrame(companies)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "datos.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "datos[\"category_code\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = db.companies.find({\"$and\":[\n",
    "                               {\"founded_year\": {\"$gte\": 1990}},\n",
    "                               {\"number_of_employees\": {\"$gte\": 7}},\n",
    "                                {\"total_money_raised\":{\"$regex\":\"M\"}},\n",
    "                               {\"$or\": [\n",
    "                                   {\"category_code\": \"design\" } ,\n",
    "                                   {\"category_code\": \"biotech\" } ,\n",
    "                                   {\"category_code\":\"games_video\"},\n",
    "                                   {\"category_code\":\"web\"},\n",
    "                                   {\"category_code\": \"software\" } ,\n",
    "                                   {\"category_code\": \"education\" } ,\n",
    "                                   ]}\n",
    "                               ],\n",
    "                               },\n",
    "                                {\"_id\": 0, \"crunchbase_url\": 0, \"products\": 0, \"founded_month\": 0, \"founded_day\": 0,\n",
    "                                \"acquisition\": 0, \"acquisitions\": 0, \"video_embeds\": 0,\n",
    "                                \"screenshots\": 0, \"external_links\": 0, \"partners\": 0,\n",
    "                                \"image\": 0, \"blog_feed_url\": 0, \"blog_url\":0, \"deadpooled_day\":0,\n",
    "                                \"deadpooled_year\":0, \"deadpooled_month\":0, \"deadpooled_url\":0, \"ipo\":0,\n",
    "                                \"permalink\":0, \"homepage_url\":0, \"tag_list\":0, \"email_addres\": 0, \"providerships\": 0,\n",
    "                                \"milestones\": 0, \"twitter_username\": 0, \"updated_at\": 0, \"email_address\":0, \"investments\":0,\n",
    "                               }\n",
    "                                    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "datos= pd.DataFrame(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "datos.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "datos.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "datos.drop(columns=[\"alias_list\",\"created_at\"],inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "datos[\"description\"]=datos[\"description\"].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "datos=datos.replace(np.nan,0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "datos.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "datos.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "datos.dropna(how=\"all\",subset={\"offices\"},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "latitude = db.companies.find({},{\"offices.latitude\":1})\n",
    "longitude = db.companies.find({},{\"offices.longitude\":1})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def geopoint(x):\n",
    "   for ele in x:\n",
    "       longitude = ele[\"longitude\"]\n",
    "       latitude = ele[\"latitude\"]\n",
    "       return{\"type\": \"Point\", \"coordinates\": {\"longitude\":longitude,\"latitude\":latitude}}\n",
    "    \n",
    "datos[\"geo\"] = datos[\"offices\"].apply(geopoint)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "datos[\"geo\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "a=datos[datos[\"geo\"].isnull()].index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "datos.drop(a,axis=0,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "datos[\"geo\"].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "b=datos[datos[\"geo\"]=={'type': 'Point', 'coordinates': {'longitude': None, 'latitude': None}}].index\n",
    "datos.drop(b,axis=0,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "datos[\"geo\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "datos.to_json(r'./oficina.json', orient=\"records\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ahora traigo la coleccion limpiada y con el geoindice creado"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "db = client.companies\n",
    "consulta=db.indice.find()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cercania = pd.DataFrame(consulta)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cercania.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cercania.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cercania[\"offices\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ciudades=[e[0][\"city\"] for e in cercania[\"offices\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(ciudades)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cercania[\"ciudades\"]=ciudades"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def gps (eg):\n",
    "    cercania[\"longitude\"]=[e[\"coordinates\"][\"longitude\"] for e in cercania[\"geo\"]]\n",
    "    cercania[\"latitude\"]=[e[\"coordinates\"][\"latitude\"] for e in cercania[\"geo\"]]\n",
    "    return "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cercania[\"geo\"].apply(gps)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display(cercania)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def buscador (ciudad):\n",
    "    a=cercania[cercania[\"ciudades\"]==ciudad].index\n",
    "    centrar=cercania.iloc[a,14:17]\n",
    "    return centrar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "b=buscador(\"New York\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ahora busco la empresas que esten mas cerca del centro de Nueva York, lo cual devuelve que de toda mi coleccion ninguna esta a menos de 1km del centro."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "coords = [-73.950566, 40.671678,]\n",
    "\n",
    "cerca=db.indice.find({\n",
    "   \"geo\": {\n",
    "     \"$near\": {\n",
    "       \"$geometry\": {\n",
    "          \"type\": \"Point\" ,\n",
    "          \"coordinates\": coords\n",
    "       },\n",
    "       \"$maxDistance\": 1000,\n",
    "     }\n",
    "   }\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cerca=pd.DataFrame(cerca)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cerca.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Enriquecer con API\n",
    "Uso la API de foursquare para enriquecer mi base de datos.\n",
    "\n",
    "1) Starbucks cercanos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "load_dotenv(dotenv_path=\"./input/.env\")\n",
    "client_secret=os.getenv(\"client_secret\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://api.foursquare.com/v2/venues/explore'\n",
    "params = dict(\n",
    " client_id='BK1LUEVPQKUICZTO2Y2ZX1XZLFLTPD0AJVMXXPOT04AQ1W5P',\n",
    " client_secret=client_secret,\n",
    " v='20180323',\n",
    " ll='40.7243,-74.0018',\n",
    " query='Starbucks',\n",
    ")\n",
    "resp = requests.get(url=url, params=params)\n",
    "data_api = json.loads(resp.text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display(data_api)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def cafe(lugar):\n",
    "  nombre=[]\n",
    "  latitud=[]\n",
    "  longi=[]\n",
    "  for e in data_api['response']['groups'][0]['items']:\n",
    "      nombre.append(e['venue']['name'])\n",
    "      latitud.append(e['venue']['location']['lat'])\n",
    "      longi.append(e['venue']['location']['lng'])\n",
    "  lugar = {\"name\":nombre, \"latitude\":latitud, \"longitude\":longi}\n",
    "  return pd.DataFrame(lugar)\n",
    "cafe = cafe(data_api)\n",
    "cafe.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mapa= folium.Map(location=[40.722655, -73.998730], width=750, height=500, zoom_start=15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in cafe.iterrows():\n",
    "    folium.Marker([row['latitude'], row['longitude']],\n",
    "                        radius=9,\n",
    "                        popup=\"City: {}, latitude {}, longitude {}\".format(row['name'], row['latitude'], row['longitude']),\n",
    "                        icon=folium.Icon(icon_color='green',icon='cloud')\n",
    "                       ).add_to(mapa)\n",
    "    folium.Marker([40.722655,-73.998730],\n",
    "                        radius=2,\n",
    "                        icon=folium.Icon(icon='home'),\n",
    "                       ).add_to(mapa)\n",
    "    folium.Circle(location=[40.722655, -73.998730],\n",
    "                    radius=500\n",
    "                   ).add_to(mapa)\n",
    "mapa.add_child(MeasureControl())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2) Restaruantes veganos cercanos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://api.foursquare.com/v2/venues/explore'\n",
    "params = dict(\n",
    " client_id='BK1LUEVPQKUICZTO2Y2ZX1XZLFLTPD0AJVMXXPOT04AQ1W5P',\n",
    " client_secret=client_secret,\n",
    " v='20180323',\n",
    " ll='40.7243,-74.0018',\n",
    " query='4bf58dd8d48988d1d3941735',\n",
    ")\n",
    "resp = requests.get(url=url, params=params)\n",
    "data_api2 = json.loads(resp.text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display(data_api2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def vegano(lugar):\n",
    "  nombre=[]\n",
    "  latitud=[]\n",
    "  longi=[]\n",
    "  for e in data_api2['response']['groups'][0]['items']:\n",
    "      nombre.append(e['venue']['name'])\n",
    "      latitud.append(e['venue']['location']['lat'])\n",
    "      longi.append(e['venue']['location']['lng'])\n",
    "  lugar = {\"name\":nombre, \"latitude\":latitud, \"longitude\":longi}\n",
    "  return pd.DataFrame(lugar)\n",
    "vegano = vegano(data_api2)\n",
    "vegano.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in vegano.iterrows():\n",
    "    folium.Marker([row['latitude'], row['longitude']],\n",
    "                        radius=9,\n",
    "                        popup=\"City: {}, latitude {}, longitude {}\".format(row['name'], row['latitude'], row['longitude']),\n",
    "                  icon=folium.Icon(icon_color='white',icon='info-sign')\n",
    "                       ).add_to(mapa)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mapa"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}