Testes.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0b077aed-7728-45dd-83d1-35429ab0b2d5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Por favor, insira o caminho do arquivo de entrada (ex: input.xlsx):  Planilha com informações de Sondagens.xlsx\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Colunas disponíveis no arquivo:\n",
      "1. Sondagem \n",
      "2. Rua / Avenida \n",
      "3. Número \n",
      "4. Trecho \n",
      "5. Bairro \n",
      "6. CEP\n",
      "7. Índice de Absorção do Solo\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Selecione as colunas de endereço pelo número, separadas por vírgula (ex: 1,2,3,4):  2,3,5,6\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\jairo\\AppData\\Local\\Temp\\ipykernel_31784\\1839482510.py:92: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value '-3.8044711' has dtype incompatible with float64, please explicitly cast to a compatible dtype first.\n",
      "  df_output.at[index, 'Latitude'] = latitude\n",
      "C:\\Users\\jairo\\AppData\\Local\\Temp\\ipykernel_31784\\1839482510.py:93: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value '-38.572465' has dtype incompatible with float64, please explicitly cast to a compatible dtype first.\n",
      "  df_output.at[index, 'Longitude'] = longitude\n"
     ]
    },
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[3], line 115\u001b[0m\n\u001b[0;32m    112\u001b[0m     \u001b[38;5;28mprint\u001b[39m(\u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mThe spreadsheet has been updated. Output file: \u001b[39m\u001b[38;5;132;01m{\u001b[39;00moutput_file\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m    114\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;18m__name__\u001b[39m \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m__main__\u001b[39m\u001b[38;5;124m\"\u001b[39m:\n\u001b[1;32m--> 115\u001b[0m     \u001b[43mmain\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n",
      "Cell \u001b[1;32mIn[3], line 109\u001b[0m, in \u001b[0;36mmain\u001b[1;34m()\u001b[0m\n\u001b[0;32m    106\u001b[0m driver \u001b[38;5;241m=\u001b[39m setup_selenium_edge()\n\u001b[0;32m    107\u001b[0m df_output, output_file \u001b[38;5;241m=\u001b[39m prepare_output_file(input_file)\n\u001b[1;32m--> 109\u001b[0m df_output \u001b[38;5;241m=\u001b[39m \u001b[43mprocess_addresses\u001b[49m\u001b[43m(\u001b[49m\u001b[43mdriver\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mdf_output\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mcache\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43maddress_column_names\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43moutput_file\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    110\u001b[0m update_cache(cache)\n\u001b[0;32m    111\u001b[0m logging\u001b[38;5;241m.\u001b[39minfo(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mGeocoding process completed.\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "Cell \u001b[1;32mIn[3], line 90\u001b[0m, in \u001b[0;36mprocess_addresses\u001b[1;34m(driver, df_output, cache, address_column_names, output_file)\u001b[0m\n\u001b[0;32m     88\u001b[0m address \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m'\u001b[39m\u001b[38;5;124m \u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;241m.\u001b[39mjoin(address_parts)\n\u001b[0;32m     89\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m address \u001b[38;5;129;01mand\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m row[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mProcessed\u001b[39m\u001b[38;5;124m'\u001b[39m]:\n\u001b[1;32m---> 90\u001b[0m     latitude, longitude, success \u001b[38;5;241m=\u001b[39m \u001b[43mget_lat_long\u001b[49m\u001b[43m(\u001b[49m\u001b[43mdriver\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43maddress\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mcache\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m     91\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m success:\n\u001b[0;32m     92\u001b[0m         df_output\u001b[38;5;241m.\u001b[39mat[index, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mLatitude\u001b[39m\u001b[38;5;124m'\u001b[39m] \u001b[38;5;241m=\u001b[39m latitude\n",
      "Cell \u001b[1;32mIn[3], line 73\u001b[0m, in \u001b[0;36mget_lat_long\u001b[1;34m(driver, address, cache, max_retries)\u001b[0m\n\u001b[0;32m     71\u001b[0m driver\u001b[38;5;241m.\u001b[39mget(\u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mhttps://www.google.com/maps/search/\u001b[39m\u001b[38;5;132;01m{\u001b[39;00m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m+\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;241m.\u001b[39mjoin(address\u001b[38;5;241m.\u001b[39msplit())\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m     72\u001b[0m wait \u001b[38;5;241m=\u001b[39m WebDriverWait(driver, \u001b[38;5;241m10\u001b[39m)\n\u001b[1;32m---> 73\u001b[0m \u001b[43mwait\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43muntil\u001b[49m\u001b[43m(\u001b[49m\u001b[43mEC\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43murl_contains\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43m@\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m     74\u001b[0m url \u001b[38;5;241m=\u001b[39m driver\u001b[38;5;241m.\u001b[39mcurrent_url\n\u001b[0;32m     75\u001b[0m coords \u001b[38;5;241m=\u001b[39m url\u001b[38;5;241m.\u001b[39msplit(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m@\u001b[39m\u001b[38;5;124m'\u001b[39m)[\u001b[38;5;241m1\u001b[39m]\u001b[38;5;241m.\u001b[39msplit(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m,\u001b[39m\u001b[38;5;124m'\u001b[39m)[\u001b[38;5;241m0\u001b[39m:\u001b[38;5;241m2\u001b[39m]\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\selenium\\webdriver\\support\\wait.py:102\u001b[0m, in \u001b[0;36mWebDriverWait.until\u001b[1;34m(self, method, message)\u001b[0m\n\u001b[0;32m    100\u001b[0m     screen \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mgetattr\u001b[39m(exc, \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mscreen\u001b[39m\u001b[38;5;124m\"\u001b[39m, \u001b[38;5;28;01mNone\u001b[39;00m)\n\u001b[0;32m    101\u001b[0m     stacktrace \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mgetattr\u001b[39m(exc, \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mstacktrace\u001b[39m\u001b[38;5;124m\"\u001b[39m, \u001b[38;5;28;01mNone\u001b[39;00m)\n\u001b[1;32m--> 102\u001b[0m \u001b[43mtime\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43msleep\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_poll\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    103\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m time\u001b[38;5;241m.\u001b[39mmonotonic() \u001b[38;5;241m>\u001b[39m end_time:\n\u001b[0;32m    104\u001b[0m     \u001b[38;5;28;01mbreak\u001b[39;00m\n",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from selenium import webdriver\n",
    "from selenium.webdriver.edge.service import Service\n",
    "from webdriver_manager.microsoft import EdgeChromiumDriverManager\n",
    "from selenium.webdriver.support.ui import WebDriverWait\n",
    "from selenium.webdriver.support import expected_conditions as EC\n",
    "import json\n",
    "import logging\n",
    "import os\n",
    "\n",
    "# Configuração inicial do logging\n",
    "LOG_FILE = \"geocoding.log\"\n",
    "CACHE_FILE = \"geocoding_cache.json\"\n",
    "logging.basicConfig(filename=LOG_FILE, level=logging.INFO, format='%(asctime)s:%(levelname)s:%(message)s')\n",
    "\n",
    "def setup_selenium_edge():\n",
    "    options = webdriver.EdgeOptions()\n",
    "    options.add_argument(\"--headless\")  # Executa o navegador em modo headless\n",
    "    service = Service(EdgeChromiumDriverManager().install())\n",
    "    driver = webdriver.Edge(service=service, options=options)\n",
    "    logging.info(\"WebDriver session started.\")\n",
    "    return driver\n",
    "\n",
    "def user_input_file():\n",
    "    input_file = input(\"Por favor, insira o caminho do arquivo de entrada (ex: input.xlsx): \")\n",
    "    return input_file\n",
    "\n",
    "def user_select_columns(df):\n",
    "    print(\"Colunas disponíveis no arquivo:\")\n",
    "    for i, col in enumerate(df.columns, 1):\n",
    "        print(f\"{i}. {col}\")\n",
    "    selected_columns_indexes = input(\"Selecione as colunas de endereço pelo número, separadas por vírgula (ex: 1,2,3,4): \")\n",
    "    selected_columns = [df.columns[int(index)-1] for index in selected_columns_indexes.split(',')]\n",
    "    return selected_columns\n",
    "\n",
    "def prepare_output_file(input_file):\n",
    "    output_file = os.path.splitext(input_file)[0] + \"_output.xlsx\"\n",
    "    if not os.path.exists(output_file):\n",
    "        df_output = pd.read_excel(input_file)\n",
    "        df_output = df_output.assign(Latitude=None, Longitude=None, Processed=False)\n",
    "        df_output.to_excel(output_file, index=False)\n",
    "    else:\n",
    "        df_output = pd.read_excel(output_file)\n",
    "    return df_output, output_file\n",
    "\n",
    "def load_or_create_cache():\n",
    "    if not os.path.isfile(CACHE_FILE):\n",
    "        with open(CACHE_FILE, 'w') as file:\n",
    "            json.dump({}, file)\n",
    "        logging.info(\"New cache file created.\")\n",
    "    try:\n",
    "        with open(CACHE_FILE, 'r') as file:\n",
    "            cache = json.load(file)\n",
    "    except json.JSONDecodeError:\n",
    "        logging.error(\"Cache file is corrupted. Creating a new one.\")\n",
    "        cache = {}\n",
    "    return cache\n",
    "\n",
    "def update_cache(cache):\n",
    "    with open(CACHE_FILE, 'w') as file:\n",
    "        json.dump(cache, file, indent=4)\n",
    "\n",
    "def get_lat_long(driver, address, cache, max_retries=3):\n",
    "    if address in cache:\n",
    "        logging.info(f\"Address '{address}' found in cache.\")\n",
    "        return cache[address]['latitude'], cache[address]['longitude'], True\n",
    "\n",
    "    retries = 0\n",
    "    while retries < max_retries:\n",
    "        try:\n",
    "            driver.get(f\"https://www.google.com/maps/search/{'+'.join(address.split())}\")\n",
    "            wait = WebDriverWait(driver, 10)\n",
    "            wait.until(EC.url_contains(\"@\"))\n",
    "            url = driver.current_url\n",
    "            coords = url.split('@')[1].split(',')[0:2]\n",
    "            latitude, longitude = coords[0], coords[1].split('!')[0]\n",
    "            cache[address] = {'latitude': latitude, 'longitude': longitude}\n",
    "            update_cache(cache)  # Atualiza o cache imediatamente após cada geocodificação bem-sucedida\n",
    "            return latitude, longitude, True\n",
    "        except Exception as e:\n",
    "            logging.error(f\"Retry {retries + 1} for address '{address}': {e}\")\n",
    "            retries += 1\n",
    "    return None, None, False\n",
    "\n",
    "def process_addresses(driver, df_output, cache, address_column_names, output_file):\n",
    "    for index, row in df_output[df_output['Processed'] == False].iterrows():\n",
    "        address_parts = [str(row[col]).strip() for col in address_column_names]\n",
    "        address = ' '.join(address_parts)\n",
    "        if address and not row['Processed']:\n",
    "            latitude, longitude, success = get_lat_long(driver, address, cache)\n",
    "            if success:\n",
    "                df_output.at[index, 'Latitude'] = latitude\n",
    "                df_output.at[index, 'Longitude'] = longitude\n",
    "                df_output.at[index, 'Processed'] = True\n",
    "                df_output.to_excel(output_file, index=False)  # Salva após cada geocodificação bem-sucedida\n",
    "\n",
    "    return df_output\n",
    "\n",
    "def main():\n",
    "    cache = load_or_create_cache()  # Garante a criação ou carga do arquivo de cache\n",
    "\n",
    "    input_file = user_input_file()\n",
    "    df_input = pd.read_excel(input_file)\n",
    "    address_column_names = user_select_columns(df_input)\n",
    "\n",
    "    driver = setup_selenium_edge()\n",
    "    df_output, output_file = prepare_output_file(input_file)\n",
    "\n",
    "    df_output = process_addresses(driver, df_output, cache, address_column_names, output_file)\n",
    "    update_cache(cache)\n",
    "    logging.info(\"Geocoding process completed.\")\n",
    "    print(f\"The spreadsheet has been updated. Output file: {output_file}\")\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    main()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18115bf6-66d3-49a2-b7b2-900723b38c42",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from selenium import webdriver\n",
    "from selenium.webdriver.edge.service import Service\n",
    "from webdriver_manager.microsoft import EdgeChromiumDriverManager\n",
    "from selenium.webdriver.support.ui import WebDriverWait\n",
    "from selenium.webdriver.support import expected_conditions as EC\n",
    "import json\n",
    "import logging\n",
    "import os\n",
    "\n",
    "# Configuração inicial do logging\n",
    "LOG_FILE = \"geocoding.log\"\n",
    "CACHE_FILE = \"geocoding_cache.json\"\n",
    "logging.basicConfig(filename=LOG_FILE, level=logging.INFO, format='%(asctime)s:%(levelname)s:%(message)s')\n",
    "\n",
    "def setup_selenium_edge():\n",
    "    options = webdriver.EdgeOptions()\n",
    "    options.add_argument(\"--headless\")  # Executa o navegador em modo headless\n",
    "    service = Service(EdgeChromiumDriverManager().install())\n",
    "    driver = webdriver.Edge(service=service, options=options)\n",
    "    logging.info(\"WebDriver session started.\")\n",
    "    return driver\n",
    "\n",
    "def user_input_file():\n",
    "    input_file = input(\"Por favor, insira o caminho do arquivo de entrada (ex: input.xlsx): \")\n",
    "    return input_file\n",
    "\n",
    "def user_select_columns(df):\n",
    "    print(\"Colunas disponíveis no arquivo:\")\n",
    "    for i, col in enumerate(df.columns, 1):\n",
    "        print(f\"{i}. {col}\")\n",
    "    selected_columns_indexes = input(\"Selecione as colunas de endereço pelo número, separadas por vírgula (ex: 1,2,3,4): \")\n",
    "    selected_columns = [df.columns[int(index)-1] for index in selected_columns_indexes.split(',')]\n",
    "    return selected_columns\n",
    "\n",
    "def prepare_output_file(input_file):\n",
    "    output_file = os.path.splitext(input_file)[0] + \"_output.xlsx\"\n",
    "    if not os.path.exists(output_file):\n",
    "        df_output = pd.read_excel(input_file)\n",
    "        df_output = df_output.assign(Latitude=None, Longitude=None, Processed=False)\n",
    "        df_output.to_excel(output_file, index=False)\n",
    "    else:\n",
    "        df_output = pd.read_excel(output_file)\n",
    "    return df_output, output_file\n",
    "\n",
    "def load_or_create_cache():\n",
    "    if not os.path.isfile(CACHE_FILE):\n",
    "        with open(CACHE_FILE, 'w') as file:\n",
    "            json.dump({}, file)\n",
    "        logging.info(\"New cache file created.\")\n",
    "    try:\n",
    "        with open(CACHE_FILE, 'r') as file:\n",
    "            cache = json.load(file)\n",
    "    except json.JSONDecodeError:\n",
    "        logging.error(\"Cache file is corrupted. Creating a new one.\")\n",
    "        cache = {}\n",
    "    return cache\n",
    "\n",
    "def update_cache(cache):\n",
    "    with open(CACHE_FILE, 'w') as file:\n",
    "        json.dump(cache, file, indent=4)\n",
    "\n",
    "def get_lat_long(driver, address, cache, max_retries=3):\n",
    "    if address in cache:\n",
    "        logging.info(f\"Address '{address}' found in cache.\")\n",
    "        return cache[address]['latitude'], cache[address]['longitude'], True\n",
    "\n",
    "    retries = 0\n",
    "    while retries < max_retries:\n",
    "        try:\n",
    "            driver.get(f\"https://www.google.com/maps/search/{'+'.join(address.split())}\")\n",
    "            wait = WebDriverWait(driver, 10)\n",
    "            wait.until(EC.url_contains(\"@\"))\n",
    "            url = driver.current_url\n",
    "            coords = url.split('@')[1].split(',')[0:2]\n",
    "            latitude, longitude = float(coords[0]), float(coords[1].split('!')[0])\n",
    "            cache[address] = {'latitude': latitude, 'longitude': longitude}\n",
    "            update_cache(cache)  # Atualiza o cache imediatamente após cada geocodificação bem-sucedida\n",
    "            return latitude, longitude, True\n",
    "        except Exception as e:\n",
    "            logging.error(f\"Retry {retries + 1} for address '{address}': {e}\")\n",
    "            retries += 1\n",
    "    return None, None, False\n",
    "\n",
    "def process_addresses(driver, df_output, cache, address_column_names, output_file):\n",
    "    for index, row in df_output[df_output['Processed'] == False].iterrows():\n",
    "        address_parts = [str(row[col]).strip() for col in address_column_names]\n",
    "        address = ' '.join(address_parts)\n",
    "        if address and not row['Processed']:\n",
    "            latitude, longitude, success = get_lat_long(driver, address, cache)\n",
    "            if success:\n",
    "                df_output.at[index, 'Latitude'] = latitude\n",
    "                df_output.at[index, 'Longitude'] = longitude\n",
    "                df_output.at[index, 'Processed'] = True\n",
    "                df_output.to_excel(output_file, index=False)  # Salva após cada geocodificação bem-sucedida\n",
    "\n",
    "    return df_output\n",
    "\n",
    "def main():\n",
    "    cache = load_or_create_cache()  # Garante a criação ou carga do arquivo de cache\n",
    "\n",
    "    input_file = user_input_file()\n",
    "    df_input = pd.read_excel(input_file)\n",
    "    address_column_names = user_select_columns(df_input)\n",
    "\n",
    "    driver = setup_selenium_edge()\n",
    "    df_output, output_file = prepare_output_file(input_file)\n",
    "\n",
    "    df_output = process_addresses(driver, df_output, cache, address_column_names, output_file)\n",
    "    update_cache(cache)\n",
    "    logging.info(\"Geocoding process completed.\")\n",
    "    print(f\"The spreadsheet has been updated. Output file: {output_file}\")\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    main()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "596b65cb-5206-47e6-b7a4-e10fc34cbc1f",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Exception in thread Thread-5 (geocode_addresses):\n",
      "Traceback (most recent call last):\n",
      "  File \"C:\\Users\\jairo\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\threading.py\", line 1052, in _bootstrap_inner\n",
      "    self.run()\n",
      "  File \"C:\\Users\\jairo\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\ipykernel\\ipkernel.py\", line 761, in run_closure\n",
      "    _threading_Thread_run(self)\n",
      "  File \"C:\\Users\\jairo\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\threading.py\", line 989, in run\n",
      "    self._target(*self._args, **self._kwargs)\n",
      "  File \"C:\\Users\\jairo\\AppData\\Local\\Temp\\ipykernel_31784\\1516050865.py\", line 80, in geocode_addresses\n",
      "  File \"C:\\Users\\jairo\\AppData\\Local\\Temp\\ipykernel_31784\\1516050865.py\", line 125, in update_progress\n",
      "  File \"C:\\Users\\jairo\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\tkinter\\__init__.py\", line 1713, in __setitem__\n",
      "    self.configure({key: value})\n",
      "  File \"C:\\Users\\jairo\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\tkinter\\__init__.py\", line 1702, in configure\n",
      "    return self._configure('configure', cnf, kw)\n",
      "           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n",
      "  File \"C:\\Users\\jairo\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\tkinter\\__init__.py\", line 1692, in _configure\n",
      "    self.tk.call(_flatten((self._w, cmd)) + self._options(cnf))\n",
      "RuntimeError: main thread is not in main loop\n"
     ]
    }
   ],
   "source": [
    "import tkinter as tk\n",
    "from tkinter import filedialog, messagebox, ttk\n",
    "import pandas as pd\n",
    "import threading\n",
    "from selenium import webdriver\n",
    "from selenium.webdriver.edge.service import Service\n",
    "from webdriver_manager.microsoft import EdgeChromiumDriverManager\n",
    "from selenium.webdriver.support.ui import WebDriverWait\n",
    "from selenium.webdriver.support import expected_conditions as EC\n",
    "import json\n",
    "import logging\n",
    "import os\n",
    "\n",
    "# Setup logging\n",
    "LOG_FILE = \"geocoding.log\"\n",
    "CACHE_FILE = \"geocoding_cache.json\"\n",
    "logging.basicConfig(filename=LOG_FILE, level=logging.INFO, format='%(asctime)s:%(levelname)s:%(message)s')\n",
    "\n",
    "# Functions for geocoding\n",
    "def setup_selenium_edge():\n",
    "    options = webdriver.EdgeOptions()\n",
    "    options.add_argument(\"--headless\")\n",
    "    service = Service(EdgeChromiumDriverManager().install())\n",
    "    driver = webdriver.Edge(service=service, options=options)\n",
    "    logging.info(\"WebDriver session started.\")\n",
    "    return driver\n",
    "\n",
    "def load_or_create_cache():\n",
    "    if not os.path.isfile(CACHE_FILE):\n",
    "        with open(CACHE_FILE, 'w') as file:\n",
    "            json.dump({}, file)\n",
    "        logging.info(\"New cache file created.\")\n",
    "    try:\n",
    "        with open(CACHE_FILE, 'r') as file:\n",
    "            cache = json.load(file)\n",
    "    except json.JSONDecodeError:\n",
    "        logging.error(\"Cache file is corrupted. Creating a new one.\")\n",
    "        cache = {}\n",
    "    return cache\n",
    "\n",
    "def update_cache(cache):\n",
    "    with open(CACHE_FILE, 'w') as file:\n",
    "        json.dump(cache, file, indent=4)\n",
    "\n",
    "def get_lat_long(driver, address, cache, max_retries=3):\n",
    "    if address in cache:\n",
    "        return cache[address]['latitude'], cache[address]['longitude'], True\n",
    "    retries = 0\n",
    "    while retries < max_retries:\n",
    "        try:\n",
    "            driver.get(f\"https://www.google.com/maps/search/{'+'.join(address.split())}\")\n",
    "            wait = WebDriverWait(driver, 10)\n",
    "            wait.until(EC.url_contains(\"@\"))\n",
    "            url = driver.current_url\n",
    "            coords = url.split('@')[1].split(',')[0:2]\n",
    "            latitude, longitude = float(coords[0]), float(coords[1].split('!')[0])\n",
    "            cache[address] = {'latitude': latitude, 'longitude': longitude}\n",
    "            update_cache(cache)\n",
    "            return latitude, longitude, True\n",
    "        except Exception as e:\n",
    "            logging.error(f\"Retry {retries + 1} for address '{address}': {e}\")\n",
    "            retries += 1\n",
    "    return None, None, False\n",
    "\n",
    "def geocode_addresses(input_file, column_indices, progress_callback):\n",
    "    address_column_names = column_indices.split(',')  # Assuming user enters comma-separated indices\n",
    "    df_input = pd.read_excel(input_file)\n",
    "    address_column_names = [df_input.columns[int(index)-1] for index in address_column_names]\n",
    "    \n",
    "    driver = setup_selenium_edge()\n",
    "    cache = load_or_create_cache()\n",
    "    \n",
    "    for index, row in df_input.iterrows():\n",
    "        address_parts = [str(row[col]).strip() for col in address_column_names]\n",
    "        address = ' '.join(address_parts)\n",
    "        latitude, longitude, success = get_lat_long(driver, address, cache)\n",
    "        if success:\n",
    "            df_input.at[index, 'Latitude'] = latitude\n",
    "            df_input.at[index, 'Longitude'] = longitude\n",
    "            progress_callback(index + 1, df_input.shape[0])\n",
    "    \n",
    "    update_cache(cache)\n",
    "    driver.quit()\n",
    "    \n",
    "    # Save the modified DataFrame\n",
    "    output_file = os.path.splitext(input_file)[0] + \"_geocoded.xlsx\"\n",
    "    df_input.to_excel(output_file, index=False)\n",
    "    return output_file\n",
    "\n",
    "# GUI\n",
    "class GeocodeApp(tk.Tk):\n",
    "    def __init__(self):\n",
    "        super().__init__()\n",
    "        self.title(\"Geocoding App\")\n",
    "        self.geometry(\"400x200\")\n",
    "        \n",
    "        self.create_widgets()\n",
    "        \n",
    "    def create_widgets(self):\n",
    "        self.file_label = tk.Label(self, text=\"No file selected\")\n",
    "        self.file_label.pack(pady=10)\n",
    "        \n",
    "        self.browse_button = tk.Button(self, text=\"Browse\", command=self.browse_file)\n",
    "        self.browse_button.pack()\n",
    "        \n",
    "        self.column_entry_label = tk.Label(self, text=\"Enter column indices (comma separated):\")\n",
    "        self.column_entry_label.pack(pady=10)\n",
    "        \n",
    "        self.column_entry = tk.Entry(self)\n",
    "        self.column_entry.pack()\n",
    "        \n",
    "        self.start_button = tk.Button(self, text=\"Start Geocoding\", command=self.start_geocoding)\n",
    "        self.start_button.pack(pady=10)\n",
    "        \n",
    "        self.progress = ttk.Progressbar(self, orient=tk.HORIZONTAL, length=300, mode='determinate')\n",
    "        self.progress.pack()\n",
    "        \n",
    "    def browse_file(self):\n",
    "        file_path = filedialog.askopenfilename(filetypes=[(\"Excel files\", \"*.xlsx\")])\n",
    "        if file_path:\n",
    "            self.file_label.config(text=file_path)\n",
    "            self.file_path = file_path\n",
    "    \n",
    "    def update_progress(self, current, total):\n",
    "        self.progress['value'] = (current / total) * 100\n",
    "        self.update_idletasks()\n",
    "    \n",
    "    def start_geocoding(self):\n",
    "        column_indices = self.column_entry.get()\n",
    "        if not hasattr(self, 'file_path') or not column_indices:\n",
    "            messagebox.showerror(\"Error\", \"Please select a file and enter column indices.\")\n",
    "            return\n",
    "        threading.Thread(target=geocode_addresses, args=(self.file_path, column_indices, self.update_progress)).start()\n",
    "        \n",
    "if __name__ == \"__main__\":\n",
    "    app = GeocodeApp()\n",
    "    app.mainloop()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "70c47527-238c-4510-a008-e92da7e48bc1",
   "metadata": {},
   "outputs": [],
   "source": [
    "import tkinter as tk\n",
    "from tkinter import filedialog, messagebox, ttk\n",
    "import pandas as pd\n",
    "import threading\n",
    "from selenium import webdriver\n",
    "from selenium.webdriver.edge.service import Service\n",
    "from webdriver_manager.microsoft import EdgeChromiumDriverManager\n",
    "from selenium.webdriver.support.ui import WebDriverWait\n",
    "from selenium.webdriver.support import expected_conditions as EC\n",
    "import json\n",
    "import logging\n",
    "import os\n",
    "\n",
    "# Setup logging\n",
    "LOG_FILE = \"geocoding.log\"\n",
    "CACHE_FILE = \"geocoding_cache.json\"\n",
    "logging.basicConfig(filename=LOG_FILE, level=logging.INFO, format='%(asctime)s:%(levelname)s:%(message)s')\n",
    "\n",
    "# Global flag to stop geocoding\n",
    "stop_geocoding = False\n",
    "\n",
    "def setup_selenium_edge():\n",
    "    options = webdriver.EdgeOptions()\n",
    "    options.add_argument(\"--headless\")\n",
    "    service = Service(EdgeChromiumDriverManager().install())\n",
    "    driver = webdriver.Edge(service=service, options=options)\n",
    "    logging.info(\"WebDriver session started.\")\n",
    "    return driver\n",
    "\n",
    "def load_or_create_cache():\n",
    "    if not os.path.isfile(CACHE_FILE):\n",
    "        with open(CACHE_FILE, 'w') as file:\n",
    "            json.dump({}, file)\n",
    "        logging.info(\"New cache file created.\")\n",
    "    try:\n",
    "        with open(CACHE_FILE, 'r') as file:\n",
    "            cache = json.load(file)\n",
    "    except json.JSONDecodeError:\n",
    "        logging.error(\"Cache file is corrupted. Creating a new one.\")\n",
    "        cache = {}\n",
    "    return cache\n",
    "\n",
    "def update_cache(cache):\n",
    "    with open(CACHE_FILE, 'w') as file:\n",
    "        json.dump(cache, file, indent=4)\n",
    "\n",
    "def get_lat_long(driver, address, cache, max_retries=3):\n",
    "    if address in cache:\n",
    "        return cache[address]['latitude'], cache[address]['longitude'], True\n",
    "    retries = 0\n",
    "    while retries < max_retries:\n",
    "        try:\n",
    "            driver.get(f\"https://www.google.com/maps/search/{'+'.join(address.split())}\")\n",
    "            wait = WebDriverWait(driver, 10)\n",
    "            wait.until(EC.url_contains(\"@\"))\n",
    "            url = driver.current_url\n",
    "            coords = url.split('@')[1].split(',')[0:2]\n",
    "            latitude, longitude = float(coords[0]), float(coords[1].split('!')[0])\n",
    "            cache[address] = {'latitude': latitude, 'longitude': longitude}\n",
    "            update_cache(cache)\n",
    "            return latitude, longitude, True\n",
    "        except Exception as e:\n",
    "            logging.error(f\"Retry {retries + 1} for address '{address}': {e}\")\n",
    "            retries += 1\n",
    "    return None, None, False\n",
    "\n",
    "def geocode_addresses(input_file, column_indices, progress_callback, finished_callback):\n",
    "    global stop_geocoding\n",
    "    address_column_names = column_indices.split(',')  # Assuming user enters comma-separated indices\n",
    "    df_input = pd.read_excel(input_file)\n",
    "    address_column_names = [df_input.columns[int(index)-1] for index in address_column_names]\n",
    "    \n",
    "    driver = setup_selenium_edge()\n",
    "    cache = load_or_create_cache()\n",
    "    \n",
    "    for index, row in df_input.iterrows():\n",
    "        if stop_geocoding:\n",
    "            break  # Exit loop if stop signal is received\n",
    "        address_parts = [str(row[col]).strip() for col in address_column_names]\n",
    "        address = ' '.join(address_parts)\n",
    "        latitude, longitude, success = get_lat_long(driver, address, cache)\n",
    "        if success:\n",
    "            df_input.at[index, 'Latitude'] = latitude\n",
    "            df_input.at[index, 'Longitude'] = longitude\n",
    "            progress_callback(index + 1, df_input.shape[0])\n",
    "    \n",
    "    update_cache(cache)\n",
    "    driver.quit()\n",
    "    \n",
    "    output_file = os.path.splitext(input_file)[0] + \"_geocoded.xlsx\"\n",
    "    df_input.to_excel(output_file, index=False)\n",
    "    finished_callback(output_file, not stop_geocoding)\n",
    "\n",
    "# GUI class with new features...\n",
    "class GeocodeApp(tk.Tk):\n",
    "    def __init__(self):\n",
    "        super().__init__()\n",
    "        self.title(\"Geocoding App\")\n",
    "        self.geometry(\"500x300\")\n",
    "        \n",
    "        self.create_widgets()\n",
    "        self.file_path = \"\"\n",
    "        \n",
    "    def create_widgets(self):\n",
    "        self.file_label = tk.Label(self, text=\"No file selected\")\n",
    "        self.file_label.pack(pady=10)\n",
    "        \n",
    "        self.browse_button = tk.Button(self, text=\"Browse\", command=self.browse_file)\n",
    "        self.browse_button.pack()\n",
    "\n",
    "        self.columns_label = tk.Label(self, text=\"\")\n",
    "        self.columns_label.pack(pady=10)\n",
    "        \n",
    "        self.column_entry_label = tk.Label(self, text=\"Enter column indices (comma separated):\")\n",
    "        self.column_entry_label.pack()\n",
    "        \n",
    "        self.column_entry = tk.Entry(self)\n",
    "        self.column_entry.pack()\n",
    "        \n",
    "        self.start_button = tk.Button(self, text=\"Start Geocoding\", command=self.start_geocoding)\n",
    "        self.start_button.pack(pady=5)\n",
    "        \n",
    "        self.stop_button = tk.Button(self, text=\"Stop Geocoding\", command=self.stop_geocoding, state=tk.DISABLED)\n",
    "        self.stop_button.pack(pady=5)\n",
    "\n",
    "        self.progress_label = tk.Label(self, text=\"Progress: 0/0\")\n",
    "        self.progress_label.pack(pady=5)\n",
    "        \n",
    "        self.progress = ttk.Progressbar(self, orient=tk.HORIZONTAL, length=300, mode='determinate')\n",
    "        self.progress.pack()\n",
    "\n",
    "    def browse_file(self):\n",
    "        global stop_geocoding\n",
    "        stop_geocoding = False  # Reset stop flag on file browse\n",
    "        file_path = filedialog.askopenfilename(filetypes=[(\"Excel files\", \"*.xlsx *.xls\")])\n",
    "        if file_path:\n",
    "            self.file_path = file_path\n",
    "            self.file_label.config(text=f\"File: {os.path.basename(file_path)}\")\n",
    "            df = pd.read_excel(file_path)\n",
    "            column_text = \"Columns: \" + \", \".join([f\"{i} - {col}\" for i, col in enumerate(df.columns, start=1)])\n",
    "            self.columns_label.config(text=column_text)\n",
    "\n",
    "    def update_progress(self, current, total):\n",
    "        self.progress['value'] = (current / total) * 100\n",
    "        self.progress_label.config(text=f\"Progress: {current}/{total}\")\n",
    "        self.update_idletasks()\n",
    "\n",
    "    def finished_geocoding(self, output_file, success):\n",
    "        if success:\n",
    "            messagebox.showinfo(\"Complete\", f\"Geocoding complete. Output saved to {output_file}\")\n",
    "        else:\n",
    "            messagebox.showinfo(\"Stopped\", \"Geocoding stopped by user.\")\n",
    "        self.start_button['state'] = tk.NORMAL\n",
    "        self.stop_button['state'] = tk.DISABLED\n",
    "\n",
    "    def start_geocoding(self):\n",
    "        global stop_geocoding\n",
    "        stop_geocoding = False\n",
    "        column_indices = self.column_entry.get()\n",
    "        if not self.file_path or not column_indices:\n",
    "            messagebox.showerror(\"Error\", \"Please select a file and enter column indices.\")\n",
    "            return\n",
    "        self.start_button['state'] = tk.DISABLED\n",
    "        self.stop_button['state'] = tk.NORMAL\n",
    "        threading.Thread(target=geocode_addresses, args=(self.file_path, column_indices, self.update_progress, self.finished_geocoding)).start()\n",
    "\n",
    "    def stop_geocoding(self):\n",
    "        global stop_geocoding\n",
    "        stop_geocoding = True\n",
    "        self.stop_button['state'] = tk.DISABLED\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    app = GeocodeApp()\n",
    "    app.mainloop()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7f5183b9-bb43-4592-983d-a41738fd38a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "import tkinter as tk\n",
    "from tkinter import filedialog, messagebox, ttk, scrolledtext\n",
    "import pandas as pd\n",
    "import threading\n",
    "from selenium import webdriver\n",
    "from selenium.webdriver.edge.service import Service\n",
    "from webdriver_manager.microsoft import EdgeChromiumDriverManager\n",
    "from selenium.webdriver.support.ui import WebDriverWait\n",
    "from selenium.webdriver.support import expected_conditions as EC\n",
    "import json\n",
    "import logging\n",
    "import os\n",
    "\n",
    "# Setup logging\n",
    "LOG_FILE = \"geocoding.log\"\n",
    "CACHE_FILE = \"geocoding_cache.json\"\n",
    "logging.basicConfig(filename=LOG_FILE, level=logging.INFO, format='%(asctime)s:%(levelname)s:%(message)s')\n",
    "\n",
    "# Global flag to stop geocoding\n",
    "stop_geocoding = False\n",
    "\n",
    "def setup_selenium_edge():\n",
    "    options = webdriver.EdgeOptions()\n",
    "    options.add_argument(\"--headless\")\n",
    "    service = Service(EdgeChromiumDriverManager().install())\n",
    "    driver = webdriver.Edge(service=service, options=options)\n",
    "    logging.info(\"WebDriver session started.\")\n",
    "    return driver\n",
    "\n",
    "def load_or_create_cache():\n",
    "    if not os.path.isfile(CACHE_FILE):\n",
    "        with open(CACHE_FILE, 'w') as file:\n",
    "            json.dump({}, file)\n",
    "        logging.info(\"New cache file created.\")\n",
    "    try:\n",
    "        with open(CACHE_FILE, 'r') as file:\n",
    "            cache = json.load(file)\n",
    "    except json.JSONDecodeError:\n",
    "        logging.error(\"Cache file is corrupted. Creating a new one.\")\n",
    "        cache = {}\n",
    "    return cache\n",
    "\n",
    "def update_cache(cache):\n",
    "    with open(CACHE_FILE, 'w') as file:\n",
    "        json.dump(cache, file, indent=4)\n",
    "\n",
    "def get_lat_long(driver, address, cache, max_retries=3):\n",
    "    if address in cache:\n",
    "        return cache[address]['latitude'], cache[address]['longitude'], True\n",
    "    retries = 0\n",
    "    while retries < max_retries:\n",
    "        try:\n",
    "            driver.get(f\"https://www.google.com/maps/search/{'+'.join(address.split())}\")\n",
    "            wait = WebDriverWait(driver, 10)\n",
    "            wait.until(EC.url_contains(\"@\"))\n",
    "            url = driver.current_url\n",
    "            coords = url.split('@')[1].split(',')[0:2]\n",
    "            latitude, longitude = float(coords[0]), float(coords[1].split('!')[0])\n",
    "            cache[address] = {'latitude': latitude, 'longitude': longitude}\n",
    "            update_cache(cache)\n",
    "            return latitude, longitude, True\n",
    "        except Exception as e:\n",
    "            logging.error(f\"Retry {retries + 1} for address '{address}': {e}\")\n",
    "            retries += 1\n",
    "    return None, None, False\n",
    "\n",
    "# Adjusted geocode_addresses to accept a list of column names\n",
    "def geocode_addresses(input_file, selected_columns, progress_callback, finished_callback):\n",
    "    global stop_geocoding\n",
    "    df_input = pd.read_excel(input_file)\n",
    "    \n",
    "    driver = setup_selenium_edge()\n",
    "    cache = load_or_create_cache()\n",
    "    \n",
    "    for index, row in df_input.iterrows():\n",
    "        if stop_geocoding:\n",
    "            break\n",
    "        address_parts = [str(row[col]).strip() for col in selected_columns if col in df_input.columns]\n",
    "        address = ' '.join(address_parts)\n",
    "        latitude, longitude, success = get_lat_long(driver, address, cache)\n",
    "        if success:\n",
    "            df_input.at[index, 'Latitude'] = latitude\n",
    "            df_input.at[index, 'Longitude'] = longitude\n",
    "            progress_callback(index + 1, df_input.shape[0])\n",
    "    \n",
    "    update_cache(cache)\n",
    "    driver.quit()\n",
    "    \n",
    "    output_file = os.path.splitext(input_file)[0] + \"_geocoded.xlsx\"\n",
    "    df_input.to_excel(output_file, index=False)\n",
    "    finished_callback(output_file, not stop_geocoding)\n",
    "\n",
    "class GeocodeApp(tk.Tk):\n",
    "    def __init__(self):\n",
    "        super().__init__()\n",
    "        self.title(\"Geocoding App\")\n",
    "        self.geometry(\"600x400\")\n",
    "        \n",
    "        self.create_widgets()\n",
    "        self.file_path = \"\"\n",
    "        self.column_checkboxes = []\n",
    "        self.selected_columns = []\n",
    "        \n",
    "    def create_widgets(self):\n",
    "        self.file_label = tk.Label(self, text=\"No file selected\")\n",
    "        self.file_label.pack(pady=10)\n",
    "        \n",
    "        self.browse_button = tk.Button(self, text=\"Browse\", command=self.browse_file)\n",
    "        self.browse_button.pack()\n",
    "\n",
    "        self.columns_frame = tk.LabelFrame(self, text=\"Columns\")\n",
    "        self.columns_scroll = scrolledtext.ScrolledText(self.columns_frame, width=40, height=10, wrap=tk.WORD)\n",
    "        self.columns_scroll.pack()\n",
    "        self.columns_frame.pack(pady=10, fill=\"both\", expand=\"yes\")\n",
    "        \n",
    "        self.start_button = tk.Button(self, text=\"Start Geocoding\", command=self.start_geocoding)\n",
    "        self.start_button.pack(pady=5)\n",
    "        \n",
    "        self.stop_button = tk.Button(self, text=\"Stop Geocoding\", command=self.stop_geocoding, state=tk.DISABLED)\n",
    "        self.stop_button.pack(pady=5)\n",
    "\n",
    "        self.progress_label = tk.Label(self, text=\"Progress: 0/0\")\n",
    "        self.progress_label.pack(pady=5)\n",
    "        \n",
    "        self.progress = ttk.Progressbar(self, orient=tk.HORIZONTAL, length=300, mode='determinate')\n",
    "        self.progress.pack()\n",
    "\n",
    "    def browse_file(self):\n",
    "        global stop_geocoding\n",
    "        stop_geocoding = False\n",
    "        file_path = filedialog.askopenfilename(filetypes=[(\"Excel files\", \"*.xlsx *.xls\")])\n",
    "        if file_path:\n",
    "            self.file_path = file_path\n",
    "            self.file_label.config(text=f\"File: {os.path.basename(file_path)}\")\n",
    "            self.display_column_checkboxes(file_path)\n",
    "\n",
    "    def display_column_checkboxes(self, file_path):\n",
    "        for widget in self.columns_frame.winfo_children():\n",
    "            widget.destroy()\n",
    "        \n",
    "        df = pd.read_excel(file_path)\n",
    "        self.selected_columns = []\n",
    "        for col in df.columns:\n",
    "            var = tk.BooleanVar()\n",
    "            chk = tk.Checkbutton(self.columns_frame, text=col, variable=var)\n",
    "            chk.pack(anchor='w')\n",
    "            self.column_checkboxes.append((var, col))\n",
    "    \n",
    "    def update_progress(self, current, total):\n",
    "        self.progress['value'] = (current / total) * 100\n",
    "        self.progress_label.config(text=f\"Progress: {current}/{total}\")\n",
    "        self.update_idletasks()\n",
    "\n",
    "    def finished_geocoding(self, output_file, success):\n",
    "        if success:\n",
    "            messagebox.showinfo(\"Complete\", f\"Geocoding complete. Output saved to {output_file}\")\n",
    "        else:\n",
    "            messagebox.showinfo(\"Stopped\", \"Geocoding stopped by user.\")\n",
    "        self.start_button['state'] = tk.NORMAL\n",
    "        self.stop_button['state'] = tk.DISABLED\n",
    "\n",
    "    def start_geocoding(self):\n",
    "        global stop_geocoding\n",
    "        stop_geocoding = False\n",
    "        selected_columns = [col for var, col in self.column_checkboxes if var.get()]\n",
    "        if not self.file_path or not selected_columns:\n",
    "            messagebox.showerror(\"Error\", \"Please select a file and at least one column.\")\n",
    "            return\n",
    "        self.start_button['state'] = tk.DISABLED\n",
    "        self.stop_button['state'] = tk.NORMAL\n",
    "        threading.Thread(target=geocode_addresses, args=(self.file_path, selected_columns, self.update_progress, self.finished_geocoding)).start()\n",
    "\n",
    "    def stop_geocoding(self):\n",
    "        global stop_geocoding\n",
    "        stop_geocoding = True\n",
    "        self.stop_button['state'] = tk.DISABLED\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    app = GeocodeApp()\n",
    "    app.mainloop()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "18ecefa7-6208-4afd-9ecc-628bed2483d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import tkinter as tk\n",
    "from tkinter import filedialog, messagebox, ttk, scrolledtext\n",
    "import pandas as pd\n",
    "import threading\n",
    "from selenium import webdriver\n",
    "from selenium.webdriver.edge.service import Service\n",
    "from webdriver_manager.microsoft import EdgeChromiumDriverManager\n",
    "from selenium.webdriver.support.ui import WebDriverWait\n",
    "from selenium.webdriver.support import expected_conditions as EC\n",
    "import json\n",
    "import logging\n",
    "import os\n",
    "\n",
    "# Setup logging\n",
    "LOG_FILE = \"geocoding.log\"\n",
    "CACHE_FILE = \"geocoding_cache.json\"\n",
    "logging.basicConfig(filename=LOG_FILE, level=logging.INFO, format='%(asctime)s:%(levelname)s:%(message)s')\n",
    "\n",
    "# Global flag to stop geocoding\n",
    "stop_geocoding = False\n",
    "\n",
    "def setup_selenium_edge():\n",
    "    options = webdriver.EdgeOptions()\n",
    "    options.add_argument(\"--headless\")\n",
    "    service = Service(EdgeChromiumDriverManager().install())\n",
    "    driver = webdriver.Edge(service=service, options=options)\n",
    "    logging.info(\"WebDriver session started.\")\n",
    "    return driver\n",
    "\n",
    "def load_or_create_cache():\n",
    "    if not os.path.isfile(CACHE_FILE):\n",
    "        with open(CACHE_FILE, 'w') as file:\n",
    "            json.dump({}, file)\n",
    "        logging.info(\"New cache file created.\")\n",
    "    try:\n",
    "        with open(CACHE_FILE, 'r') as file:\n",
    "            cache = json.load(file)\n",
    "    except json.JSONDecodeError:\n",
    "        logging.error(\"Cache file is corrupted. Creating a new one.\")\n",
    "        cache = {}\n",
    "    return cache\n",
    "\n",
    "def update_cache(cache):\n",
    "    with open(CACHE_FILE, 'w') as file:\n",
    "        json.dump(cache, file, indent=4)\n",
    "\n",
    "def get_lat_long(driver, address, cache, max_retries=3):\n",
    "    if address in cache:\n",
    "        return cache[address]['latitude'], cache[address]['longitude'], True\n",
    "    retries = 0\n",
    "    while retries < max_retries:\n",
    "        try:\n",
    "            driver.get(f\"https://www.google.com/maps/search/{'+'.join(address.split())}\")\n",
    "            wait = WebDriverWait(driver, 10)\n",
    "            wait.until(EC.url_contains(\"@\"))\n",
    "            url = driver.current_url\n",
    "            coords = url.split('@')[1].split(',')[0:2]\n",
    "            latitude, longitude = float(coords[0]), float(coords[1].split('!')[0])\n",
    "            cache[address] = {'latitude': latitude, 'longitude': longitude}\n",
    "            update_cache(cache)\n",
    "            return latitude, longitude, True\n",
    "        except Exception as e:\n",
    "            logging.error(f\"Retry {retries + 1} for address '{address}': {e}\")\n",
    "            retries += 1\n",
    "    return None, None, False\n",
    "\n",
    "def geocode_addresses(input_file, selected_columns, progress_callback, finished_callback, status_callback):\n",
    "    global stop_geocoding\n",
    "    status_callback(\"Preparing data... This may take some time...\")\n",
    "    df_input = pd.read_excel(input_file)\n",
    "    \n",
    "    driver = setup_selenium_edge()\n",
    "    cache = load_or_create_cache()\n",
    "    \n",
    "    status_callback(\"Geocoding data...\")\n",
    "    for index, row in df_input.iterrows():\n",
    "        if stop_geocoding:\n",
    "            break\n",
    "        address_parts = [str(row[col]).strip() for col in selected_columns if col in df_input.columns]\n",
    "        address = ' '.join(address_parts)\n",
    "        latitude, longitude, success = get_lat_long(driver, address, cache)\n",
    "        if success:\n",
    "            df_input.at[index, 'Latitude'] = latitude\n",
    "            df_input.at[index, 'Longitude'] = longitude\n",
    "            progress_callback(index + 1, df_input.shape[0])\n",
    "    \n",
    "    update_cache(cache)\n",
    "    driver.quit()\n",
    "    \n",
    "    output_file = os.path.splitext(input_file)[0] + \"_geocoded.xlsx\"\n",
    "    df_input.to_excel(output_file, index=False)\n",
    "    finished_callback(output_file, not stop_geocoding)\n",
    "\n",
    "class GeocodeApp(tk.Tk):\n",
    "    def __init__(self):\n",
    "        super().__init__()\n",
    "        self.title(\"Transporte e Meio Ambiente(Trama) - Geocoding with Google - App\")\n",
    "        self.geometry(\"600x490\")  # Increased height for better spacing\n",
    "        \n",
    "        self.create_widgets()\n",
    "        self.file_path = \"\"\n",
    "        self.column_checkboxes = []\n",
    "        self.selected_columns = []\n",
    "        \n",
    "    def create_widgets(self):\n",
    "        self.file_label = tk.Label(self, text=\"No file selected\")\n",
    "        self.file_label.pack(pady=10)\n",
    "        \n",
    "        self.browse_button = tk.Button(self, text=\"Browse\", command=self.browse_file)\n",
    "        self.browse_button.pack()\n",
    "\n",
    "        self.columns_frame = tk.LabelFrame(self, text=\"Columns\")\n",
    "        self.columns_scroll = scrolledtext.ScrolledText(self.columns_frame, width=40, height=10, wrap=tk.WORD)\n",
    "        self.columns_scroll.pack()\n",
    "        self.columns_frame.pack(pady=10, fill=\"both\", expand=\"yes\")\n",
    "        \n",
    "        self.status_label = tk.Label(self, text=\"\")\n",
    "        self.status_label.pack(pady=10)\n",
    "\n",
    "        self.start_button = tk.Button(self, text=\"Start Geocoding\", command=self.start_geocoding)\n",
    "        self.start_button.pack(pady=5)\n",
    "        \n",
    "        self.stop_button = tk.Button(self, text=\"Stop Geocoding\", command=self.stop_geocoding, state=tk.DISABLED)\n",
    "        self.stop_button.pack(pady=5)\n",
    "\n",
    "        self.progress_label = tk.Label(self, text=\"Progress: 0/0\")\n",
    "        self.progress_label.pack(pady=5)\n",
    "        \n",
    "        self.progress = ttk.Progressbar(self, orient=tk.HORIZONTAL, length=300, mode='determinate')\n",
    "        self.progress.pack(pady=20)  # Added padding for spacing from the bottom\n",
    "\n",
    "    def browse_file(self):\n",
    "        global stop_geocoding\n",
    "        stop_geocoding = False\n",
    "        file_path = filedialog.askopenfilename(filetypes=[(\"Excel files\", \"*.xlsx *.xls\")])\n",
    "        if file_path:\n",
    "            self.file_path = file_path\n",
    "            self.file_label.config(text=f\"File: {os.path.basename(file_path)}\")\n",
    "            self.display_column_checkboxes(file_path)\n",
    "\n",
    "    def display_column_checkboxes(self, file_path):\n",
    "        for widget in self.columns_frame.winfo_children():\n",
    "            widget.destroy()\n",
    "        \n",
    "        df = pd.read_excel(file_path)\n",
    "        self.selected_columns = []\n",
    "        for col in df.columns:\n",
    "            var = tk.BooleanVar()\n",
    "            chk = tk.Checkbutton(self.columns_frame, text=col, variable=var)\n",
    "            chk.pack(anchor='w')\n",
    "            self.column_checkboxes.append((var, col))\n",
    "    \n",
    "    def update_progress(self, current, total):\n",
    "        self.progress['value'] = (current / total) * 100\n",
    "        self.progress_label.config(text=f\"Progress: {current}/{total}\")\n",
    "        self.update_idletasks()\n",
    "\n",
    "    def finished_geocoding(self, output_file, success):\n",
    "        if success:\n",
    "            messagebox.showinfo(\"Complete\", f\"Geocoding complete. Output saved to {output_file}\")\n",
    "        else:\n",
    "            messagebox.showinfo(\"Stopped\", \"Geocoding stopped by user.\")\n",
    "        self.start_button['state'] = tk.NORMAL\n",
    "        self.stop_button['state'] = tk.DISABLED\n",
    "    \n",
    "    def update_status(self, message):\n",
    "        self.status_label.config(text=message)\n",
    "        self.update_idletasks()\n",
    "\n",
    "    def start_geocoding(self):\n",
    "        global stop_geocoding\n",
    "        stop_geocoding = False\n",
    "        selected_columns = [col for var, col in self.column_checkboxes if var.get()]\n",
    "        if not self.file_path or not selected_columns:\n",
    "            messagebox.showerror(\"Error\", \"Please select a file and at least one column.\")\n",
    "            return\n",
    "        self.start_button['state'] = tk.DISABLED\n",
    "        self.stop_button['state'] = tk.NORMAL\n",
    "        threading.Thread(target=geocode_addresses, args=(self.file_path, selected_columns, self.update_progress, self.finished_geocoding, self.update_status)).start()\n",
    "\n",
    "    def stop_geocoding(self):\n",
    "        global stop_geocoding\n",
    "        stop_geocoding = True\n",
    "        self.stop_button['state'] = tk.DISABLED\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    app = GeocodeApp()\n",
    "    app.mainloop()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "94a5ef7c-5296-4543-bcc8-c8c1e8a04ed7",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.12.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}