geoplot.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "parliamentary-nebraska",
   "metadata": {},
   "outputs": [],
   "source": [
    "# First install geopy - for some reason I had issues installing with a pip command in the terminal\n",
    "# This python code finally got it to install where it was needed\n",
    "# !pip install geopy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "organizational-prayer",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Initial imports\n",
    "import pandas as pd\n",
    "from geopy.geocoders import Nominatim"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "accepted-parts",
   "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>Operation #</th>\n",
       "      <th>Agency Number</th>\n",
       "      <th>Operation/Caregiver Name</th>\n",
       "      <th>Address</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Zip</th>\n",
       "      <th>County</th>\n",
       "      <th>Phone</th>\n",
       "      <th>Type</th>\n",
       "      <th>Status</th>\n",
       "      <th>Issue Date</th>\n",
       "      <th>Capacity</th>\n",
       "      <th>Email Address</th>\n",
       "      <th>Facility ID</th>\n",
       "      <th>Monitoring Frequency</th>\n",
       "      <th>Infant</th>\n",
       "      <th>Toddler</th>\n",
       "      <th>Preschool</th>\n",
       "      <th>School</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1239246</td>\n",
       "      <td>NaN</td>\n",
       "      <td>K\" Street Learning Center LLC\"</td>\n",
       "      <td>19290 K ST</td>\n",
       "      <td>SOMERSET</td>\n",
       "      <td>TX</td>\n",
       "      <td>78069.0</td>\n",
       "      <td>BEXAR</td>\n",
       "      <td>830-429-1010</td>\n",
       "      <td>Licensed Center - Child Care Program</td>\n",
       "      <td>Full Permit</td>\n",
       "      <td>10/13/2011</td>\n",
       "      <td>82</td>\n",
       "      <td>sylvia@kstreetlearningcenter.com</td>\n",
       "      <td>773628</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1687698</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.2..me Kidz</td>\n",
       "      <td>1222 BABCOCK RD</td>\n",
       "      <td>SAN ANTONIO</td>\n",
       "      <td>TX</td>\n",
       "      <td>78201.0</td>\n",
       "      <td>BEXAR</td>\n",
       "      <td>210-310-3990</td>\n",
       "      <td>Licensed Center - Child Care Program</td>\n",
       "      <td>Full Permit</td>\n",
       "      <td>3/21/2019</td>\n",
       "      <td>81</td>\n",
       "      <td>kidz1.2me@yahoo.com</td>\n",
       "      <td>1325841</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Operation #  Agency Number        Operation/Caregiver Name  \\\n",
       "0      1239246            NaN  K\" Street Learning Center LLC\"   \n",
       "1      1687698            NaN                    1.2..me Kidz   \n",
       "\n",
       "            Address         City State      Zip County         Phone  \\\n",
       "0       19290 K ST      SOMERSET    TX  78069.0  BEXAR  830-429-1010   \n",
       "1  1222 BABCOCK RD   SAN ANTONIO    TX  78201.0  BEXAR  210-310-3990   \n",
       "\n",
       "                                   Type       Status  Issue Date  Capacity  \\\n",
       "0  Licensed Center - Child Care Program  Full Permit  10/13/2011        82   \n",
       "1  Licensed Center - Child Care Program  Full Permit   3/21/2019        81   \n",
       "\n",
       "                      Email Address  Facility ID  Monitoring Frequency Infant  \\\n",
       "0  sylvia@kstreetlearningcenter.com       773628                   NaN      Y   \n",
       "1               kidz1.2me@yahoo.com      1325841                   NaN      Y   \n",
       "\n",
       "  Toddler Preschool School  \n",
       "0       Y         Y      Y  \n",
       "1       Y         Y      Y  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load the child care data set\n",
    "file_path = \"ChildCareSearchResults.csv\"\n",
    "care_df = pd.read_csv(file_path)\n",
    "#print header to confirm import\n",
    "care_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "overhead-straight",
   "metadata": {},
   "outputs": [],
   "source": [
    "# The Address column of data has whitespaces and would mess up stringing the address together, whitespaces removed\n",
    "care_df['Address'] = care_df['Address'].str.strip()\n",
    "care_df['City'] = care_df['City'].str.strip()\n",
    "care_df['State'] = care_df['State'].str.strip()\n",
    "care_df['County'] = care_df['County'].str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "sonic-mounting",
   "metadata": {},
   "outputs": [],
   "source": [
    "# The zip code imported with a decimal that had to be reformatted \n",
    "care_df['Zip'] = care_df['Zip'].apply('{0:g}'.format)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "final-transsexual",
   "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>Operation #</th>\n",
       "      <th>Agency Number</th>\n",
       "      <th>Operation/Caregiver Name</th>\n",
       "      <th>Address</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Zip</th>\n",
       "      <th>County</th>\n",
       "      <th>Phone</th>\n",
       "      <th>Type</th>\n",
       "      <th>...</th>\n",
       "      <th>Issue Date</th>\n",
       "      <th>Capacity</th>\n",
       "      <th>Email Address</th>\n",
       "      <th>Facility ID</th>\n",
       "      <th>Monitoring Frequency</th>\n",
       "      <th>Infant</th>\n",
       "      <th>Toddler</th>\n",
       "      <th>Preschool</th>\n",
       "      <th>School</th>\n",
       "      <th>Full_Address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1239246</td>\n",
       "      <td>NaN</td>\n",
       "      <td>K\" Street Learning Center LLC\"</td>\n",
       "      <td>19290 K ST</td>\n",
       "      <td>SOMERSET</td>\n",
       "      <td>TX</td>\n",
       "      <td>78069</td>\n",
       "      <td>BEXAR</td>\n",
       "      <td>830-429-1010</td>\n",
       "      <td>Licensed Center - Child Care Program</td>\n",
       "      <td>...</td>\n",
       "      <td>10/13/2011</td>\n",
       "      <td>82</td>\n",
       "      <td>sylvia@kstreetlearningcenter.com</td>\n",
       "      <td>773628</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>19290 K ST, SOMERSET, TX, 78069</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1687698</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.2..me Kidz</td>\n",
       "      <td>1222 BABCOCK RD</td>\n",
       "      <td>SAN ANTONIO</td>\n",
       "      <td>TX</td>\n",
       "      <td>78201</td>\n",
       "      <td>BEXAR</td>\n",
       "      <td>210-310-3990</td>\n",
       "      <td>Licensed Center - Child Care Program</td>\n",
       "      <td>...</td>\n",
       "      <td>3/21/2019</td>\n",
       "      <td>81</td>\n",
       "      <td>kidz1.2me@yahoo.com</td>\n",
       "      <td>1325841</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>1222 BABCOCK RD, SAN ANTONIO, TX, 78201</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Operation #  Agency Number        Operation/Caregiver Name  \\\n",
       "0      1239246            NaN  K\" Street Learning Center LLC\"   \n",
       "1      1687698            NaN                    1.2..me Kidz   \n",
       "\n",
       "           Address         City State    Zip County         Phone  \\\n",
       "0       19290 K ST     SOMERSET    TX  78069  BEXAR  830-429-1010   \n",
       "1  1222 BABCOCK RD  SAN ANTONIO    TX  78201  BEXAR  210-310-3990   \n",
       "\n",
       "                                   Type  ...  Issue Date Capacity  \\\n",
       "0  Licensed Center - Child Care Program  ...  10/13/2011       82   \n",
       "1  Licensed Center - Child Care Program  ...   3/21/2019       81   \n",
       "\n",
       "                      Email Address Facility ID  Monitoring Frequency  Infant  \\\n",
       "0  sylvia@kstreetlearningcenter.com      773628                   NaN       Y   \n",
       "1               kidz1.2me@yahoo.com     1325841                   NaN       Y   \n",
       "\n",
       "  Toddler Preschool School                             Full_Address  \n",
       "0       Y         Y      Y          19290 K ST, SOMERSET, TX, 78069  \n",
       "1       Y         Y      Y  1222 BABCOCK RD, SAN ANTONIO, TX, 78201  \n",
       "\n",
       "[2 rows x 21 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Not the most efficient way to concantenate several columns to one column, but I got the data the way I needed it\n",
    "care_df['Full_Address'] = care_df['Address'].map(str) + ', ' + care_df['City'].map(str) + ', ' + care_df['State'].map(str)+ ', ' + care_df['Zip'].map(str)\n",
    "care_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "neither-arrow",
   "metadata": {},
   "outputs": [],
   "source": [
    "#export the file to run through a lat-long program\n",
    "care_df.to_excel('ChildCareSearchResults.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "conditional-kernel",
   "metadata": {},
   "source": [
    "## Rest of code is troubleshooting a free way to get lat-long using Geopy and Nominatim. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "handmade-coaching",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Test an example\n",
    "locator = Nominatim(user_agent=\"insert your email\")\n",
    "location = locator.geocode(\"Champ de Mars, Paris, France\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "faced-filter",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Latitude = 48.85614465, Longitude = 2.297820393322227\n"
     ]
    }
   ],
   "source": [
    "# Success!  Nominatim works for small batches\n",
    "print(\"Latitude = {}, Longitude = {}\".format(location.latitude, location.longitude))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "welcome-dimension",
   "metadata": {},
   "outputs": [],
   "source": [
    "## code works, however, some addresses with STE would not process for some unknown reason.\n",
    "\n",
    "# from geopy.extra.rate_limiter import RateLimiter\n",
    "\n",
    "# # The rate limiter function delays between geocoding calls\n",
    "# geocode = RateLimiter(locator.geocode, min_delay_seconds=1)\n",
    "# # Create location column from the full address column\n",
    "# care_df['location'] = care_df['Full_Address'].apply(geocode)\n",
    "\n",
    "# # Create point column with longitude, latitude and altitude from location column (returns tuple)\n",
    "# care_df['point'] = care_df['location'].apply(lambda loc: tuple(loc.point) if loc else None)\n",
    "# # Split point column into latitude, longitude and altitude columns\n",
    "# care_df[['latitude', 'longitude', 'altitude']] = pd.DataFrame(care_df['point'].tolist(), index=care_df.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "innocent-discount",
   "metadata": {},
   "outputs": [],
   "source": [
    "care_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "manufactured-aaron",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ended up using Geocodio to convert address to lat-longs, can do 2000 a day for free"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "comparable-relationship",
   "metadata": {},
   "source": [
    "## Unused code and sources "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "furnished-marine",
   "metadata": {},
   "outputs": [],
   "source": [
    "# from geopy import Nominatim\n",
    "# import time\n",
    "\n",
    "# geolocator = Nominatim(user_agent=\"mathgirl37@hotmail.com\")\n",
    "\n",
    "# with open(\"ChildCareSearchResults\",'r') as fp:\n",
    "#     for line in fp:\n",
    "#         location = geolocator.geocode(line)\n",
    "# print (location.latitude, location.longitude)\n",
    "# time.sleep(1)\n",
    "# fp.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "graphic-rapid",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Source: https://towardsdatascience.com/geocode-with-python-161ec1e62b89\n",
    "# # pip install geopy\n",
    "# #locator = Nominatim(user_agent=\"childcare\")\n",
    "# # from geopy.extra.rate_limiter import RateLimiter\n",
    "\n",
    "# # 1 - conveneint function to delay between geocoding calls\n",
    "# # geocode = RateLimiter(locator.geocode, min_delay_seconds=1)\n",
    "# # 2- - create location column\n",
    "# care_df['location'] = care_df['Full_Address'].apply(geocode)\n",
    "# # 3 - create longitude, laatitude and altitude from location column (returns tuple)\n",
    "# care_df['point'] = care_df['location'].apply(lambda loc: tuple(loc.point) if loc else None)\n",
    "# # 4 - split point column into latitude, longitude and altitude columns\n",
    "# care_df[['latitude', 'longitude', 'altitude']] = pd.DataFrame(care_df['point'].tolist(), index=df.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "significant-encyclopedia",
   "metadata": {},
   "outputs": [],
   "source": [
    "# from geopy.geocoders import Photon\n",
    "# geolocator = Nominatim(user_agent=\"childcare\")\n",
    "# care_df['location'] = care_df['Full_Address'].apply(geocode)\n",
    "# care_df['point'] = care_df['location'].apply(lambda loc: tuple(loc.point) if loc else None)\n",
    "\n",
    "\n",
    "# location = geolocator.geocode(\"4200 Grim Ave, Waco, TX, 76710\", timeout=None)\n",
    "# print(location.address)\n",
    "# Flatiron Building, 175, 5th Avenue, Flatiron, New York, NYC, New York, ...\n",
    "# >>> print((location.latitude, location.longitude))\n",
    "# (40.7410861, -73.9896297241625)\n",
    "# >>> print(location.raw)\n",
    "# {'place_id': '9167009604', 'type': 'attraction', ...}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "acquired-scanner",
   "metadata": {},
   "outputs": [],
   "source": [
    "# care_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "three-force",
   "metadata": {},
   "outputs": [],
   "source": [
    "# from geopy.geocoders import Photon\n",
    "# geocoder = Nominatim(user_agent = 'your_app_name')\n",
    "# from geopy.extra.rate_limiter import RateLimiter\n",
    "# geocode = RateLimiter(geocoder.geocode, min_delay_seconds = 1,   return_value_on_exception = None) \n",
    "# # adding 1 second padding between calls\n",
    "\n",
    "# # after initiating geocoder\n",
    "# location = geocode(address)\n",
    "# # returns location object with longitude, latitude and altitude instances\n",
    "# (location.latitude, location.longitude)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "everyday-throw",
   "metadata": {},
   "outputs": [],
   "source": [
    "# #Importing the Nominatim geocoder class \n",
    "# from geopy.geocoders import Nominatim\n",
    " \n",
    "# #address we need to geocode\n",
    "# loc = 'Taj Mahal, Agra, Uttar Pradesh 282001'\n",
    " \n",
    "# #making an instance of Nominatim class\n",
    "# geolocator = Nominatim(user_agent=\"try_to_get_to_work\")\n",
    " \n",
    "# #applying geocode method to get the location\n",
    "# location = geolocator.geocode(loc)\n",
    " \n",
    "# #printing address and coordinates\n",
    "# print(location.address)\n",
    "# print((location.latitude, location.longitude))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "conventional-sheffield",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Notes\n",
    "# https://towardsdatascience.com/geocode-with-python-161ec1e62b89\n",
    "# https://geopy.readthedocs.io/en/stable/#photon\n",
    "# https://www.askpython.com/python/python-geopy-to-find-geocode-of-an-address\n",
    "# https://www.geocod.io/\n",
    "# https://geloky.com/geocoding/\n",
    "# https://rigorousthemes.com/blog/15-best-tableau-dashboard-examples/\n",
    "# https://public.tableau.com/app/profile/tableau.for.sales.analytics/viz/AccountManagement-360DegreeCustomerView/StrategicEnterpriseAccountProfile\n",
    "# https://www.dataplusscience.com/SimpleOrgChart.html\n",
    "# https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_create.htm"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "mlenv",
   "language": "python",
   "name": "mlenv"
  },
  "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.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}