Otodom_data_transform.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "iMYRuXgK_Rps",
        "outputId": "38049409-242f-4911-f179-fdfb41a41389"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Requirement already satisfied: pandas in /usr/local/lib/python3.10/dist-packages (2.1.4)\n",
            "Requirement already satisfied: numpy<2,>=1.22.4 in /usr/local/lib/python3.10/dist-packages (from pandas) (1.26.4)\n",
            "Requirement already satisfied: python-dateutil>=2.8.2 in /usr/local/lib/python3.10/dist-packages (from pandas) (2.8.2)\n",
            "Requirement already satisfied: pytz>=2020.1 in /usr/local/lib/python3.10/dist-packages (from pandas) (2024.1)\n",
            "Requirement already satisfied: tzdata>=2022.1 in /usr/local/lib/python3.10/dist-packages (from pandas) (2024.1)\n",
            "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.10/dist-packages (from python-dateutil>=2.8.2->pandas) (1.16.0)\n",
            "Requirement already satisfied: SQLAlchemy in /usr/local/lib/python3.10/dist-packages (2.0.32)\n",
            "Requirement already satisfied: typing-extensions>=4.6.0 in /usr/local/lib/python3.10/dist-packages (from SQLAlchemy) (4.12.2)\n",
            "Requirement already satisfied: greenlet!=0.4.17 in /usr/local/lib/python3.10/dist-packages (from SQLAlchemy) (3.0.3)\n",
            "Requirement already satisfied: snowflake-connector-python[pandas] in /usr/local/lib/python3.10/dist-packages (3.12.0)\n",
            "Requirement already satisfied: asn1crypto<2.0.0,>0.24.0 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python[pandas]) (1.5.1)\n",
            "Requirement already satisfied: cffi<2.0.0,>=1.9 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python[pandas]) (1.17.0)\n",
            "Requirement already satisfied: cryptography<43.0.0,>=3.1.0 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python[pandas]) (42.0.8)\n",
            "Requirement already satisfied: pyOpenSSL<25.0.0,>=16.2.0 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python[pandas]) (24.2.1)\n",
            "Requirement already satisfied: pyjwt<3.0.0 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python[pandas]) (2.9.0)\n",
            "Requirement already satisfied: pytz in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python[pandas]) (2024.1)\n",
            "Requirement already satisfied: requests<3.0.0 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python[pandas]) (2.32.3)\n",
            "Requirement already satisfied: packaging in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python[pandas]) (24.1)\n",
            "Requirement already satisfied: charset-normalizer<4,>=2 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python[pandas]) (3.3.2)\n",
            "Requirement already satisfied: idna<4,>=2.5 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python[pandas]) (3.7)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python[pandas]) (2024.7.4)\n",
            "Requirement already satisfied: typing-extensions<5,>=4.3 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python[pandas]) (4.12.2)\n",
            "Requirement already satisfied: filelock<4,>=3.5 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python[pandas]) (3.15.4)\n",
            "Requirement already satisfied: sortedcontainers>=2.4.0 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python[pandas]) (2.4.0)\n",
            "Requirement already satisfied: platformdirs<5.0.0,>=2.6.0 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python[pandas]) (4.2.2)\n",
            "Requirement already satisfied: tomlkit in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python[pandas]) (0.13.0)\n",
            "Requirement already satisfied: pandas<3.0.0,>=1.0.0 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python[pandas]) (2.1.4)\n",
            "Requirement already satisfied: pyarrow in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python[pandas]) (14.0.2)\n",
            "Requirement already satisfied: pycparser in /usr/local/lib/python3.10/dist-packages (from cffi<2.0.0,>=1.9->snowflake-connector-python[pandas]) (2.22)\n",
            "Requirement already satisfied: numpy<2,>=1.22.4 in /usr/local/lib/python3.10/dist-packages (from pandas<3.0.0,>=1.0.0->snowflake-connector-python[pandas]) (1.26.4)\n",
            "Requirement already satisfied: python-dateutil>=2.8.2 in /usr/local/lib/python3.10/dist-packages (from pandas<3.0.0,>=1.0.0->snowflake-connector-python[pandas]) (2.8.2)\n",
            "Requirement already satisfied: tzdata>=2022.1 in /usr/local/lib/python3.10/dist-packages (from pandas<3.0.0,>=1.0.0->snowflake-connector-python[pandas]) (2024.1)\n",
            "Requirement already satisfied: urllib3<3,>=1.21.1 in /usr/local/lib/python3.10/dist-packages (from requests<3.0.0->snowflake-connector-python[pandas]) (2.0.7)\n",
            "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.10/dist-packages (from python-dateutil>=2.8.2->pandas<3.0.0,>=1.0.0->snowflake-connector-python[pandas]) (1.16.0)\n",
            "Collecting snowflake-sqlalchemy\n",
            "  Downloading snowflake_sqlalchemy-1.6.1-py3-none-any.whl.metadata (20 kB)\n",
            "Requirement already satisfied: snowflake-connector-python<4.0.0 in /usr/local/lib/python3.10/dist-packages (from snowflake-sqlalchemy) (3.12.0)\n",
            "Requirement already satisfied: sqlalchemy>=1.4.19 in /usr/local/lib/python3.10/dist-packages (from snowflake-sqlalchemy) (2.0.32)\n",
            "Requirement already satisfied: asn1crypto<2.0.0,>0.24.0 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python<4.0.0->snowflake-sqlalchemy) (1.5.1)\n",
            "Requirement already satisfied: cffi<2.0.0,>=1.9 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python<4.0.0->snowflake-sqlalchemy) (1.17.0)\n",
            "Requirement already satisfied: cryptography<43.0.0,>=3.1.0 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python<4.0.0->snowflake-sqlalchemy) (42.0.8)\n",
            "Requirement already satisfied: pyOpenSSL<25.0.0,>=16.2.0 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python<4.0.0->snowflake-sqlalchemy) (24.2.1)\n",
            "Requirement already satisfied: pyjwt<3.0.0 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python<4.0.0->snowflake-sqlalchemy) (2.9.0)\n",
            "Requirement already satisfied: pytz in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python<4.0.0->snowflake-sqlalchemy) (2024.1)\n",
            "Requirement already satisfied: requests<3.0.0 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python<4.0.0->snowflake-sqlalchemy) (2.32.3)\n",
            "Requirement already satisfied: packaging in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python<4.0.0->snowflake-sqlalchemy) (24.1)\n",
            "Requirement already satisfied: charset-normalizer<4,>=2 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python<4.0.0->snowflake-sqlalchemy) (3.3.2)\n",
            "Requirement already satisfied: idna<4,>=2.5 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python<4.0.0->snowflake-sqlalchemy) (3.7)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python<4.0.0->snowflake-sqlalchemy) (2024.7.4)\n",
            "Requirement already satisfied: typing-extensions<5,>=4.3 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python<4.0.0->snowflake-sqlalchemy) (4.12.2)\n",
            "Requirement already satisfied: filelock<4,>=3.5 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python<4.0.0->snowflake-sqlalchemy) (3.15.4)\n",
            "Requirement already satisfied: sortedcontainers>=2.4.0 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python<4.0.0->snowflake-sqlalchemy) (2.4.0)\n",
            "Requirement already satisfied: platformdirs<5.0.0,>=2.6.0 in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python<4.0.0->snowflake-sqlalchemy) (4.2.2)\n",
            "Requirement already satisfied: tomlkit in /usr/local/lib/python3.10/dist-packages (from snowflake-connector-python<4.0.0->snowflake-sqlalchemy) (0.13.0)\n",
            "Requirement already satisfied: greenlet!=0.4.17 in /usr/local/lib/python3.10/dist-packages (from sqlalchemy>=1.4.19->snowflake-sqlalchemy) (3.0.3)\n",
            "Requirement already satisfied: pycparser in /usr/local/lib/python3.10/dist-packages (from cffi<2.0.0,>=1.9->snowflake-connector-python<4.0.0->snowflake-sqlalchemy) (2.22)\n",
            "Requirement already satisfied: urllib3<3,>=1.21.1 in /usr/local/lib/python3.10/dist-packages (from requests<3.0.0->snowflake-connector-python<4.0.0->snowflake-sqlalchemy) (2.0.7)\n",
            "Downloading snowflake_sqlalchemy-1.6.1-py3-none-any.whl (43 kB)\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m43.7/43.7 kB\u001b[0m \u001b[31m2.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hInstalling collected packages: snowflake-sqlalchemy\n",
            "Successfully installed snowflake-sqlalchemy-1.6.1\n",
            "Requirement already satisfied: gspread in /usr/local/lib/python3.10/dist-packages (6.0.2)\n",
            "Requirement already satisfied: google-auth>=1.12.0 in /usr/local/lib/python3.10/dist-packages (from gspread) (2.27.0)\n",
            "Requirement already satisfied: google-auth-oauthlib>=0.4.1 in /usr/local/lib/python3.10/dist-packages (from gspread) (1.2.1)\n",
            "Requirement already satisfied: StrEnum==0.4.15 in /usr/local/lib/python3.10/dist-packages (from gspread) (0.4.15)\n",
            "Requirement already satisfied: cachetools<6.0,>=2.0.0 in /usr/local/lib/python3.10/dist-packages (from google-auth>=1.12.0->gspread) (5.4.0)\n",
            "Requirement already satisfied: pyasn1-modules>=0.2.1 in /usr/local/lib/python3.10/dist-packages (from google-auth>=1.12.0->gspread) (0.4.0)\n",
            "Requirement already satisfied: rsa<5,>=3.1.4 in /usr/local/lib/python3.10/dist-packages (from google-auth>=1.12.0->gspread) (4.9)\n",
            "Requirement already satisfied: requests-oauthlib>=0.7.0 in /usr/local/lib/python3.10/dist-packages (from google-auth-oauthlib>=0.4.1->gspread) (1.3.1)\n",
            "Requirement already satisfied: pyasn1<0.7.0,>=0.4.6 in /usr/local/lib/python3.10/dist-packages (from pyasn1-modules>=0.2.1->google-auth>=1.12.0->gspread) (0.6.0)\n",
            "Requirement already satisfied: oauthlib>=3.0.0 in /usr/local/lib/python3.10/dist-packages (from requests-oauthlib>=0.7.0->google-auth-oauthlib>=0.4.1->gspread) (3.2.2)\n",
            "Requirement already satisfied: requests>=2.0.0 in /usr/local/lib/python3.10/dist-packages (from requests-oauthlib>=0.7.0->google-auth-oauthlib>=0.4.1->gspread) (2.32.3)\n",
            "Requirement already satisfied: charset-normalizer<4,>=2 in /usr/local/lib/python3.10/dist-packages (from requests>=2.0.0->requests-oauthlib>=0.7.0->google-auth-oauthlib>=0.4.1->gspread) (3.3.2)\n",
            "Requirement already satisfied: idna<4,>=2.5 in /usr/local/lib/python3.10/dist-packages (from requests>=2.0.0->requests-oauthlib>=0.7.0->google-auth-oauthlib>=0.4.1->gspread) (3.7)\n",
            "Requirement already satisfied: urllib3<3,>=1.21.1 in /usr/local/lib/python3.10/dist-packages (from requests>=2.0.0->requests-oauthlib>=0.7.0->google-auth-oauthlib>=0.4.1->gspread) (2.0.7)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.10/dist-packages (from requests>=2.0.0->requests-oauthlib>=0.7.0->google-auth-oauthlib>=0.4.1->gspread) (2024.7.4)\n",
            "Requirement already satisfied: gspread-dataframe in /usr/local/lib/python3.10/dist-packages (3.3.1)\n",
            "Requirement already satisfied: gspread>=3.0.0 in /usr/local/lib/python3.10/dist-packages (from gspread-dataframe) (6.0.2)\n",
            "Requirement already satisfied: pandas>=0.24.0 in /usr/local/lib/python3.10/dist-packages (from gspread-dataframe) (2.1.4)\n",
            "Requirement already satisfied: six>=1.12.0 in /usr/local/lib/python3.10/dist-packages (from gspread-dataframe) (1.16.0)\n",
            "Requirement already satisfied: google-auth>=1.12.0 in /usr/local/lib/python3.10/dist-packages (from gspread>=3.0.0->gspread-dataframe) (2.27.0)\n",
            "Requirement already satisfied: google-auth-oauthlib>=0.4.1 in /usr/local/lib/python3.10/dist-packages (from gspread>=3.0.0->gspread-dataframe) (1.2.1)\n",
            "Requirement already satisfied: StrEnum==0.4.15 in /usr/local/lib/python3.10/dist-packages (from gspread>=3.0.0->gspread-dataframe) (0.4.15)\n",
            "Requirement already satisfied: numpy<2,>=1.22.4 in /usr/local/lib/python3.10/dist-packages (from pandas>=0.24.0->gspread-dataframe) (1.26.4)\n",
            "Requirement already satisfied: python-dateutil>=2.8.2 in /usr/local/lib/python3.10/dist-packages (from pandas>=0.24.0->gspread-dataframe) (2.8.2)\n",
            "Requirement already satisfied: pytz>=2020.1 in /usr/local/lib/python3.10/dist-packages (from pandas>=0.24.0->gspread-dataframe) (2024.1)\n",
            "Requirement already satisfied: tzdata>=2022.1 in /usr/local/lib/python3.10/dist-packages (from pandas>=0.24.0->gspread-dataframe) (2024.1)\n",
            "Requirement already satisfied: cachetools<6.0,>=2.0.0 in /usr/local/lib/python3.10/dist-packages (from google-auth>=1.12.0->gspread>=3.0.0->gspread-dataframe) (5.4.0)\n",
            "Requirement already satisfied: pyasn1-modules>=0.2.1 in /usr/local/lib/python3.10/dist-packages (from google-auth>=1.12.0->gspread>=3.0.0->gspread-dataframe) (0.4.0)\n",
            "Requirement already satisfied: rsa<5,>=3.1.4 in /usr/local/lib/python3.10/dist-packages (from google-auth>=1.12.0->gspread>=3.0.0->gspread-dataframe) (4.9)\n",
            "Requirement already satisfied: requests-oauthlib>=0.7.0 in /usr/local/lib/python3.10/dist-packages (from google-auth-oauthlib>=0.4.1->gspread>=3.0.0->gspread-dataframe) (1.3.1)\n",
            "Requirement already satisfied: pyasn1<0.7.0,>=0.4.6 in /usr/local/lib/python3.10/dist-packages (from pyasn1-modules>=0.2.1->google-auth>=1.12.0->gspread>=3.0.0->gspread-dataframe) (0.6.0)\n",
            "Requirement already satisfied: oauthlib>=3.0.0 in /usr/local/lib/python3.10/dist-packages (from requests-oauthlib>=0.7.0->google-auth-oauthlib>=0.4.1->gspread>=3.0.0->gspread-dataframe) (3.2.2)\n",
            "Requirement already satisfied: requests>=2.0.0 in /usr/local/lib/python3.10/dist-packages (from requests-oauthlib>=0.7.0->google-auth-oauthlib>=0.4.1->gspread>=3.0.0->gspread-dataframe) (2.32.3)\n",
            "Requirement already satisfied: charset-normalizer<4,>=2 in /usr/local/lib/python3.10/dist-packages (from requests>=2.0.0->requests-oauthlib>=0.7.0->google-auth-oauthlib>=0.4.1->gspread>=3.0.0->gspread-dataframe) (3.3.2)\n",
            "Requirement already satisfied: idna<4,>=2.5 in /usr/local/lib/python3.10/dist-packages (from requests>=2.0.0->requests-oauthlib>=0.7.0->google-auth-oauthlib>=0.4.1->gspread>=3.0.0->gspread-dataframe) (3.7)\n",
            "Requirement already satisfied: urllib3<3,>=1.21.1 in /usr/local/lib/python3.10/dist-packages (from requests>=2.0.0->requests-oauthlib>=0.7.0->google-auth-oauthlib>=0.4.1->gspread>=3.0.0->gspread-dataframe) (2.0.7)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.10/dist-packages (from requests>=2.0.0->requests-oauthlib>=0.7.0->google-auth-oauthlib>=0.4.1->gspread>=3.0.0->gspread-dataframe) (2024.7.4)\n"
          ]
        }
      ],
      "source": [
        "# Install the required python packages\n",
        "!pip install pandas\n",
        "!pip install SQLAlchemy\n",
        "!pip install \"snowflake-connector-python[pandas]\"\n",
        "!pip install snowflake-sqlalchemy\n",
        "!pip install gspread\n",
        "!pip install gspread-dataframe"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Additional packages for geocode & google translate\n",
        "!pip install geopy\n",
        "!pip install dask\n",
        "!pip install deep-translator"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "9KAjtTyd_pOM",
        "outputId": "66e5a11e-721d-4a91-bf76-9529d629871a"
      },
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Requirement already satisfied: geopy in /usr/local/lib/python3.10/dist-packages (2.4.1)\n",
            "Requirement already satisfied: geographiclib<3,>=1.52 in /usr/local/lib/python3.10/dist-packages (from geopy) (2.0)\n",
            "Requirement already satisfied: dask in /usr/local/lib/python3.10/dist-packages (2024.7.1)\n",
            "Requirement already satisfied: click>=8.1 in /usr/local/lib/python3.10/dist-packages (from dask) (8.1.7)\n",
            "Requirement already satisfied: cloudpickle>=1.5.0 in /usr/local/lib/python3.10/dist-packages (from dask) (2.2.1)\n",
            "Requirement already satisfied: fsspec>=2021.09.0 in /usr/local/lib/python3.10/dist-packages (from dask) (2024.6.1)\n",
            "Requirement already satisfied: packaging>=20.0 in /usr/local/lib/python3.10/dist-packages (from dask) (24.1)\n",
            "Requirement already satisfied: partd>=1.4.0 in /usr/local/lib/python3.10/dist-packages (from dask) (1.4.2)\n",
            "Requirement already satisfied: pyyaml>=5.3.1 in /usr/local/lib/python3.10/dist-packages (from dask) (6.0.2)\n",
            "Requirement already satisfied: toolz>=0.10.0 in /usr/local/lib/python3.10/dist-packages (from dask) (0.12.1)\n",
            "Requirement already satisfied: importlib-metadata>=4.13.0 in /usr/local/lib/python3.10/dist-packages (from dask) (8.2.0)\n",
            "Requirement already satisfied: zipp>=0.5 in /usr/local/lib/python3.10/dist-packages (from importlib-metadata>=4.13.0->dask) (3.19.2)\n",
            "Requirement already satisfied: locket in /usr/local/lib/python3.10/dist-packages (from partd>=1.4.0->dask) (1.0.0)\n",
            "Collecting deep-translator\n",
            "  Downloading deep_translator-1.11.4-py3-none-any.whl.metadata (30 kB)\n",
            "Requirement already satisfied: beautifulsoup4<5.0.0,>=4.9.1 in /usr/local/lib/python3.10/dist-packages (from deep-translator) (4.12.3)\n",
            "Requirement already satisfied: requests<3.0.0,>=2.23.0 in /usr/local/lib/python3.10/dist-packages (from deep-translator) (2.32.3)\n",
            "Requirement already satisfied: soupsieve>1.2 in /usr/local/lib/python3.10/dist-packages (from beautifulsoup4<5.0.0,>=4.9.1->deep-translator) (2.5)\n",
            "Requirement already satisfied: charset-normalizer<4,>=2 in /usr/local/lib/python3.10/dist-packages (from requests<3.0.0,>=2.23.0->deep-translator) (3.3.2)\n",
            "Requirement already satisfied: idna<4,>=2.5 in /usr/local/lib/python3.10/dist-packages (from requests<3.0.0,>=2.23.0->deep-translator) (3.7)\n",
            "Requirement already satisfied: urllib3<3,>=1.21.1 in /usr/local/lib/python3.10/dist-packages (from requests<3.0.0,>=2.23.0->deep-translator) (2.0.7)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.10/dist-packages (from requests<3.0.0,>=2.23.0->deep-translator) (2024.7.4)\n",
            "Downloading deep_translator-1.11.4-py3-none-any.whl (42 kB)\n",
            "\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m42.3/42.3 kB\u001b[0m \u001b[31m2.7 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hInstalling collected packages: deep-translator\n",
            "Successfully installed deep-translator-1.11.4\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from geopy.geocoders import Nominatim\n",
        "from pprint import pprint\n",
        "\n",
        "# Instantiate a new Nominatim client\n",
        "app = Nominatim(user_agent=\"tutorial\")\n",
        "\n",
        "# Get location raw data from the user\n",
        "your_loc = input(\"Enter your location: \")\n",
        "location = app.geocode(your_loc).raw\n",
        "\n",
        "# Print raw data\n",
        "pprint(location)\n",
        "print(\"\\n\\n\")\n",
        "pprint(app.reverse((52.23614,21.00817)).raw['address'])"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "20t1NzbtIbPT",
        "outputId": "9ee12fd9-2156-4fa7-d68e-5833d5590865"
      },
      "execution_count": 17,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Enter your location: 52.23614,21.00817\n",
            "{'addresstype': 'amenity',\n",
            " 'boundingbox': ['52.2357458', '52.2362462', '21.0081529', '21.0084925'],\n",
            " 'class': 'amenity',\n",
            " 'display_name': 'Marszałkowska, Centrum, Śródmieście Północne, Śródmieście, '\n",
            "                 'Warszawa, województwo mazowieckie, 00-005, Polska',\n",
            " 'importance': 8.366292820625647e-05,\n",
            " 'lat': '52.236080799999996',\n",
            " 'licence': 'Data © OpenStreetMap contributors, ODbL 1.0. '\n",
            "            'http://osm.org/copyright',\n",
            " 'lon': '21.00827373998952',\n",
            " 'name': '',\n",
            " 'osm_id': 1107718234,\n",
            " 'osm_type': 'way',\n",
            " 'place_id': 158262715,\n",
            " 'place_rank': 30,\n",
            " 'type': 'parking'}\n",
            "\n",
            "\n",
            "\n",
            "{'ISO3166-2-lvl4': 'PL-14',\n",
            " 'city': 'Warszawa',\n",
            " 'country': 'Polska',\n",
            " 'country_code': 'pl',\n",
            " 'neighbourhood': 'Centrum',\n",
            " 'postcode': '00-005',\n",
            " 'quarter': 'Śródmieście Północne',\n",
            " 'road': 'Marszałkowska',\n",
            " 'state': 'województwo mazowieckie',\n",
            " 'suburb': 'Śródmieście'}\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "import numpy as np\n",
        "from geopy.geocoders import Nominatim\n",
        "from geopy.point import Point\n",
        "from snowflake.sqlalchemy import URL\n",
        "from sqlalchemy import create_engine\n",
        "from snowflake.connector.pandas_tools import pd_writer\n",
        "import time\n",
        "import dask.dataframe as dd\n",
        "\n",
        "start_time = time.time()\n",
        "\n",
        "geolocator = Nominatim(user_agent=\"tutorial\")\n",
        "\n",
        "engine = create_engine(URL(\n",
        "                    account = 'hrwnkma-yd18638',\n",
        "                    user = 'ranjeet270',\n",
        "                    password = 'Ranjeet@123',\n",
        "                    database = 'demo',\n",
        "                    schema = 'public',\n",
        "                    warehouse = 'demo_wh'))"
      ],
      "metadata": {
        "id": "UYldUk_CN5iX"
      },
      "execution_count": 26,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "with engine.connect() as conn:\n",
        "    try:\n",
        "        query = \"\"\" SELECT RN, concat(latitude,',',longitude) as LOCATION\n",
        "                    FROM (SELECT RN\n",
        "                          , SUBSTR(location, REGEXP_INSTR(location,' ',1,4)+1) AS LATITUDE\n",
        "                          , SUBSTR(location, REGEXP_INSTR(location,' ',1,1)+1, (REGEXP_INSTR(location,' ',1,2) - REGEXP_INSTR(location,' ',1,1) - 1) ) AS LONGITUDE\n",
        "                        FROM otodom_data_flatten ORDER BY rn) \"\"\"\n",
        "        print(\"--- %s seconds ---\" % (time.time() - start_time))\n",
        "\n",
        "        df = pd.read_sql(query,conn)\n",
        "\n",
        "        df.columns = map(lambda x: str(x).upper(), df.columns)\n",
        "\n",
        "        ddf = dd.from_pandas(df,npartitions=100)\n",
        "        print(ddf.head(5,npartitions=-1))\n",
        "\n",
        "        ddf['ADDRESS'] = ddf['LOCATION'].apply(lambda x: geolocator.reverse(x).raw['address'],meta=(None, 'str'))\n",
        "        print(\"--- %s seconds ---\" % (time.time() - start_time))\n",
        "\n",
        "        pandas_df = ddf.compute()\n",
        "        print(pandas_df.head())\n",
        "        print(\"--- %s seconds ---\" % (time.time() - start_time))\n",
        "\n",
        "        pandas_df.to_sql('otodom_data_flatten_address', con=engine, if_exists='append', index=False, chunksize=16000, method=pd_writer)\n",
        "    except Exception as e:\n",
        "        print('--- Error --- ',e)\n",
        "    finally:\n",
        "        conn.close()\n",
        "engine.dispose()\n",
        "\n",
        "print(\"--- %s seconds ---\" % (time.time() - start_time))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "XgVRsnjdAIPN",
        "outputId": "ddce6ac0-1be7-443e-d78f-8c3d8b3d0d19"
      },
      "execution_count": 27,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "--- 39.72012376785278 seconds ---\n",
            "   RN                             LOCATION\n",
            "0   1                    52.23614,21.00817\n",
            "1   2                  52.336575,21.029306\n",
            "2   3  51.10710682881388,16.94346882507325\n",
            "3   4                    50.10361,20.00665\n",
            "4   5                  52.336575,21.029306\n",
            "--- 43.99381756782532 seconds ---\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "WARNING:urllib3.connectionpool:Retrying (Retry(total=1, connect=None, read=None, redirect=None, status=None)) after connection broken by 'ReadTimeoutError(\"HTTPSConnectionPool(host='nominatim.openstreetmap.org', port=443): Read timed out. (read timeout=1)\")': /reverse?lat=51.15563&lon=16.93648&format=json&addressdetails=1\n",
            "WARNING:urllib3.connectionpool:Retrying (Retry(total=1, connect=None, read=None, redirect=None, status=None)) after connection broken by 'ReadTimeoutError(\"HTTPSConnectionPool(host='nominatim.openstreetmap.org', port=443): Read timed out. (read timeout=1)\")': /reverse?lat=52.27617&lon=21.06015&format=json&addressdetails=1\n",
            "WARNING:urllib3.connectionpool:Retrying (Retry(total=0, connect=None, read=None, redirect=None, status=None)) after connection broken by 'ReadTimeoutError(\"HTTPSConnectionPool(host='nominatim.openstreetmap.org', port=443): Read timed out. (read timeout=1)\")': /reverse?lat=52.27617&lon=21.06015&format=json&addressdetails=1\n",
            "WARNING:urllib3.connectionpool:Retrying (Retry(total=1, connect=None, read=None, redirect=None, status=None)) after connection broken by 'ReadTimeoutError(\"HTTPSConnectionPool(host='nominatim.openstreetmap.org', port=443): Read timed out. (read timeout=1)\")': /reverse?lat=51.10946&lon=17.04069&format=json&addressdetails=1\n",
            "WARNING:urllib3.connectionpool:Retrying (Retry(total=1, connect=None, read=None, redirect=None, status=None)) after connection broken by 'ReadTimeoutError(\"HTTPSConnectionPool(host='nominatim.openstreetmap.org', port=443): Read timed out. (read timeout=1)\")': /reverse?lat=52.20997&lon=20.96727&format=json&addressdetails=1\n",
            "WARNING:urllib3.connectionpool:Retrying (Retry(total=0, connect=None, read=None, redirect=None, status=None)) after connection broken by 'ReadTimeoutError(\"HTTPSConnectionPool(host='nominatim.openstreetmap.org', port=443): Read timed out. (read timeout=1)\")': /reverse?lat=52.20997&lon=20.96727&format=json&addressdetails=1\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "--- Error ---  HTTPSConnectionPool(host='nominatim.openstreetmap.org', port=443): Max retries exceeded with url: /reverse?lat=52.20997&lon=20.96727&format=json&addressdetails=1 (Caused by ReadTimeoutError(\"HTTPSConnectionPool(host='nominatim.openstreetmap.org', port=443): Read timed out. (read timeout=1)\"))\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "WARNING:urllib3.connectionpool:Retrying (Retry(total=1, connect=None, read=None, redirect=None, status=None)) after connection broken by 'ReadTimeoutError(\"HTTPSConnectionPool(host='nominatim.openstreetmap.org', port=443): Read timed out. (read timeout=1)\")': /reverse?lat=52.19109&lon=20.99282&format=json&addressdetails=1\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "--- 52.55512452125549 seconds ---\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "conn"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "5RXFjRQZOTjV",
        "outputId": "c39a6372-4ad3-4e8c-a5cb-07112799a122"
      },
      "execution_count": 22,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlalchemy.engine.base.Connection at 0x7f3fa4253df0>"
            ]
          },
          "metadata": {},
          "execution_count": 22
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        " with engine.connect() as conn:\n",
        "  try:\n",
        "    query = \"\"\" SELECT RN, concat(latitude,',',longitude) as LOCATION\n",
        "                    FROM (SELECT RN\n",
        "                          , SUBSTR(location, REGEXP_INSTR(location,' ',1,4)+1) AS LATITUDE\n",
        "                          , SUBSTR(location, REGEXP_INSTR(location,' ',1,1)+1, (REGEXP_INSTR(location,' ',1,2) - REGEXP_INSTR(location,' ',1,1) - 1) ) AS LONGITUDE\n",
        "                        FROM otodom_data_flatten ORDER BY rn  ) \"\"\"\n",
        "    print(\"--- %s seconds ---\" % (time.time() - start_time))\n",
        "\n",
        "    df = pd.read_sql(query,conn)\n",
        "    df.columns = map(lambda x: str(x).upper(), df.columns)\n",
        "\n",
        "    print(df.head())\n",
        "\n",
        "\n",
        "  except Exception as e:\n",
        "        print('--- Error --- ',e)\n",
        "  finally:\n",
        "        conn.close()\n",
        "\n",
        ""
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "4NfGa6QYOY98",
        "outputId": "7e01f0d2-7a7d-46b5-d9b6-ad17758d1d89"
      },
      "execution_count": 25,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "--- 420.61012268066406 seconds ---\n",
            "   RN                             LOCATION\n",
            "0   1                    52.23614,21.00817\n",
            "1   2                  52.336575,21.029306\n",
            "2   3  51.10710682881388,16.94346882507325\n",
            "3   4                    50.10361,20.00665\n",
            "4   5                  52.336575,21.029306\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 423
        },
        "id": "bOZC5PGmIzdS",
        "outputId": "a80289b8-d734-45da-84b4-16654aff20c8"
      },
      "execution_count": 28,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "          RN                              LOCATION\n",
              "0          1                     52.23614,21.00817\n",
              "1          2                   52.336575,21.029306\n",
              "2          3   51.10710682881388,16.94346882507325\n",
              "3          4                     50.10361,20.00665\n",
              "4          5                   52.336575,21.029306\n",
              "...      ...                                   ...\n",
              "62811  62812                      52.16044,21.0784\n",
              "62812  62813  51.07641572477857,17.017576468725604\n",
              "62813  62814                       50.2559,19.0168\n",
              "62814  62815                   52.1570051,21.07894\n",
              "62815  62816                 52.1898046,21.0630231\n",
              "\n",
              "[62816 rows x 2 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-d4bef26c-01d6-4f57-8519-8d7006c4462e\" class=\"colab-df-container\">\n",
              "    <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>RN</th>\n",
              "      <th>LOCATION</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>52.23614,21.00817</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2</td>\n",
              "      <td>52.336575,21.029306</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>3</td>\n",
              "      <td>51.10710682881388,16.94346882507325</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4</td>\n",
              "      <td>50.10361,20.00665</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>5</td>\n",
              "      <td>52.336575,21.029306</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>62811</th>\n",
              "      <td>62812</td>\n",
              "      <td>52.16044,21.0784</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>62812</th>\n",
              "      <td>62813</td>\n",
              "      <td>51.07641572477857,17.017576468725604</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>62813</th>\n",
              "      <td>62814</td>\n",
              "      <td>50.2559,19.0168</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>62814</th>\n",
              "      <td>62815</td>\n",
              "      <td>52.1570051,21.07894</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>62815</th>\n",
              "      <td>62816</td>\n",
              "      <td>52.1898046,21.0630231</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>62816 rows × 2 columns</p>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-d4bef26c-01d6-4f57-8519-8d7006c4462e')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-d4bef26c-01d6-4f57-8519-8d7006c4462e button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-d4bef26c-01d6-4f57-8519-8d7006c4462e');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-f859a80f-a9cf-46a8-aad4-4a17c880fc83\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-f859a80f-a9cf-46a8-aad4-4a17c880fc83')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-f859a80f-a9cf-46a8-aad4-4a17c880fc83 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "  <div id=\"id_a392a870-d119-4246-809e-41f0b096b29c\">\n",
              "    <style>\n",
              "      .colab-df-generate {\n",
              "        background-color: #E8F0FE;\n",
              "        border: none;\n",
              "        border-radius: 50%;\n",
              "        cursor: pointer;\n",
              "        display: none;\n",
              "        fill: #1967D2;\n",
              "        height: 32px;\n",
              "        padding: 0 0 0 0;\n",
              "        width: 32px;\n",
              "      }\n",
              "\n",
              "      .colab-df-generate:hover {\n",
              "        background-color: #E2EBFA;\n",
              "        box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "        fill: #174EA6;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate {\n",
              "        background-color: #3B4455;\n",
              "        fill: #D2E3FC;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate:hover {\n",
              "        background-color: #434B5C;\n",
              "        box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "        filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "        fill: #FFFFFF;\n",
              "      }\n",
              "    </style>\n",
              "    <button class=\"colab-df-generate\" onclick=\"generateWithVariable('df')\"\n",
              "            title=\"Generate code using this dataframe.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M7,19H8.4L18.45,9,17,7.55,7,17.6ZM5,21V16.75L18.45,3.32a2,2,0,0,1,2.83,0l1.4,1.43a1.91,1.91,0,0,1,.58,1.4,1.91,1.91,0,0,1-.58,1.4L9.25,21ZM18.45,9,17,7.55Zm-12,3A5.31,5.31,0,0,0,4.9,8.1,5.31,5.31,0,0,0,1,6.5,5.31,5.31,0,0,0,4.9,4.9,5.31,5.31,0,0,0,6.5,1,5.31,5.31,0,0,0,8.1,4.9,5.31,5.31,0,0,0,12,6.5,5.46,5.46,0,0,0,6.5,12Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "    <script>\n",
              "      (() => {\n",
              "      const buttonEl =\n",
              "        document.querySelector('#id_a392a870-d119-4246-809e-41f0b096b29c button.colab-df-generate');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      buttonEl.onclick = () => {\n",
              "        google.colab.notebook.generateWithVariable('df');\n",
              "      }\n",
              "      })();\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "variable_name": "df",
              "summary": "{\n  \"name\": \"df\",\n  \"rows\": 62816,\n  \"fields\": [\n    {\n      \"column\": \"RN\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 18133,\n        \"min\": 1,\n        \"max\": 62816,\n        \"num_unique_values\": 62816,\n        \"samples\": [\n          18164,\n          51590,\n          27009\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"LOCATION\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 30575,\n        \"samples\": [\n          \"51.072161,17.0883\",\n          \"50.24044278637,19.011953974798\",\n          \"51.067008930856964,17.054477460327167\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {},
          "execution_count": 28
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "ddf['ADDRESS'] = ddf['LOCATION'].apply(lambda x: geolocator.reverse(x).raw['address'],meta=(None, 'str'))"
      ],
      "metadata": {
        "id": "p3q5KaXOI82o"
      },
      "execution_count": 29,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "Wti1YZn0U3H_"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "pandas_df = ddf.loc[0].compute()\n",
        "print(pandas_df.head())\n",
        "print(\"--- %s seconds ---\" % (time.time() - start_time))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "CxFaTutbI0Oh",
        "outputId": "99d7cfbd-ceee-4212-b7a2-de32fc6f7f7c"
      },
      "execution_count": 31,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "WARNING:urllib3.connectionpool:Retrying (Retry(total=1, connect=None, read=None, redirect=None, status=None)) after connection broken by 'ReadTimeoutError(\"HTTPSConnectionPool(host='nominatim.openstreetmap.org', port=443): Read timed out. (read timeout=1)\")': /reverse?lat=51.0568&lon=17.0521&format=json&addressdetails=1\n",
            "WARNING:urllib3.connectionpool:Retrying (Retry(total=1, connect=None, read=None, redirect=None, status=None)) after connection broken by 'ReadTimeoutError(\"HTTPSConnectionPool(host='nominatim.openstreetmap.org', port=443): Read timed out. (read timeout=1)\")': /reverse?lat=50.04763&lon=19.99413&format=json&addressdetails=1\n",
            "WARNING:urllib3.connectionpool:Retrying (Retry(total=1, connect=None, read=None, redirect=None, status=None)) after connection broken by 'ReadTimeoutError(\"HTTPSConnectionPool(host='nominatim.openstreetmap.org', port=443): Read timed out. (read timeout=1)\")': /reverse?lat=51.115&lon=17.01029&format=json&addressdetails=1\n",
            "WARNING:urllib3.connectionpool:Retrying (Retry(total=1, connect=None, read=None, redirect=None, status=None)) after connection broken by 'ReadTimeoutError(\"HTTPSConnectionPool(host='nominatim.openstreetmap.org', port=443): Read timed out. (read timeout=1)\")': /reverse?lat=50.0570916&lon=19.9300418&format=json&addressdetails=1\n",
            "WARNING:urllib3.connectionpool:Retrying (Retry(total=1, connect=None, read=None, redirect=None, status=None)) after connection broken by 'ReadTimeoutError(\"HTTPSConnectionPool(host='nominatim.openstreetmap.org', port=443): Read timed out. (read timeout=1)\")': /reverse?lat=52.15653&lon=21.0701&format=json&addressdetails=1\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "   RN           LOCATION                                            ADDRESS\n",
            "0   1  52.23614,21.00817  {'road': 'Marszałkowska', 'neighbourhood': 'Ce...\n",
            "--- 1954.9464910030365 seconds ---\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# pandas_df.to_sql('otodom_data_flatten_address', con=engine, if_exists='append', index=False, chunksize=16000, method=pd_writer)"
      ],
      "metadata": {
        "id": "C6YJVMLLJCUI"
      },
      "execution_count": 36,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "-0ocm-3GXCCz"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# df['ADDRESS'] = df['LOCATION'].apply(lambda x: geolocator.reverse(x).raw['address'])"
      ],
      "metadata": {
        "id": "_JfbDpAVXm8F"
      },
      "execution_count": 35,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "Ke0JniWkXEFo"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "QhJ3jfA2YSCg"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Translate polish title to english"
      ],
      "metadata": {
        "id": "ekjebFYnYSWp"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "from snowflake.sqlalchemy import URL\n",
        "from sqlalchemy import create_engine\n",
        "from snowflake.connector.pandas_tools import pd_writer\n",
        "import time\n",
        "import gspread\n",
        "from gspread_dataframe import get_as_dataframe, set_with_dataframe\n",
        "\n",
        "start_time = time.time()\n",
        "\n",
        "engine = create_engine(URL(\n",
        "                    account = 'ceiknnc-keb24333',\n",
        "                    user = 'thoufiq',\n",
        "                    password = 'Admin$1234',\n",
        "                    database = 'demo',\n",
        "                    schema = 'public',\n",
        "                    warehouse = 'demo_wh'))\n",
        "\n",
        "with engine.connect() as conn:\n",
        "    try:\n",
        "        query = \"\"\" SELECT RN, TITLE FROM otodom_data_flatten ORDER BY rn limit 300\"\"\"\n",
        "\n",
        "        df = pd.read_sql(query,conn)\n",
        "\n",
        "        gc = gspread.service_account()\n",
        "\n",
        "        loop_counter = 0\n",
        "        chunk_size = 100\n",
        "        file_name = 'OTODOM_ANALYSIS_'\n",
        "        user_email = 'demo.tfq@gmail.com'\n",
        "\n",
        "        for i in range(0,len(df),chunk_size):\n",
        "            loop_counter += 1\n",
        "            df_in = df.iloc[i:(i+chunk_size), :]\n",
        "\n",
        "            spreadsheet_title = file_name + str(loop_counter)\n",
        "            try:\n",
        "                locals()['sh'+str(loop_counter)] = gc.open(spreadsheet_title)\n",
        "            except gspread.SpreadsheetNotFound:\n",
        "                locals()['sh'+str(loop_counter)] = gc.create(spreadsheet_title)\n",
        "\n",
        "            locals()['sh'+str(loop_counter)].share(user_email, perm_type='user', role='writer')\n",
        "            wks = locals()['sh'+str(loop_counter)].get_worksheet(0)\n",
        "            wks.resize(len(df_in)+1)\n",
        "            set_with_dataframe(wks, df_in)\n",
        "\n",
        "            column = 'C'   # Column to apply the formula\n",
        "            start_row = 2  # Starting row to apply the formula\n",
        "            end_row = wks.row_count   # Ending row to apply the formula\n",
        "            cell_range = f'{column}{start_row}:{column}{end_row}'\n",
        "            curr_row = start_row\n",
        "            cell_list = wks.range(cell_range)\n",
        "\n",
        "            for cell in cell_list:\n",
        "                cell.value = f'=GOOGLETRANSLATE(B{curr_row},\"pl\",\"en\")'\n",
        "                curr_row += 1\n",
        "\n",
        "            # Update the worksheet with the modified cells\n",
        "            wks.update_cells(cell_list, value_input_option='USER_ENTERED')\n",
        "\n",
        "            print(f'Spreadsheet {spreadsheet_title} created!')\n",
        "\n",
        "            df_log = pd.DataFrame({'ID':[loop_counter], 'SPREADSHEET_NAME':[spreadsheet_title]})\n",
        "            df_log.to_sql('otodom_data_log', con=engine, if_exists='append', index=False, chunksize=16000, method=pd_writer)\n",
        "\n",
        "\n",
        "            # df_out = get_as_dataframe(wks, usecols=[0,1,2,3], nrows=end_row, header=None, skiprows=1)\n",
        "            # print(f'Spreadsheet {locals()[\"sh\"+str(loop_counter)]} loaded back to DataFrame!')\n",
        "\n",
        "            # df_out.columns = ['RN', 'TITLE', 'LOCATION', 'TITLE_ENG']\n",
        "\n",
        "            # df_out.to_sql('otodom_data_transformed', con=engine, if_exists='append', index=False, chunksize=16000, method=pd_writer)\n",
        "\n",
        "    except Exception as e:\n",
        "        print('--- Error --- ',e)\n",
        "    finally:\n",
        "        conn.close()\n",
        "engine.dispose()\n",
        "\n",
        "print(\"--- %s seconds ---\" % (time.time() - start_time))"
      ],
      "metadata": {
        "id": "eVwhC5K_YSpd"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Upload data into snowflake database\n",
        "\n"
      ],
      "metadata": {
        "id": "Zbd4PJC1YLGy"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "from snowflake.sqlalchemy import URL\n",
        "from sqlalchemy import create_engine\n",
        "from snowflake.connector.pandas_tools import pd_writer\n",
        "import time\n",
        "import gspread\n",
        "from gspread_dataframe import get_as_dataframe, set_with_dataframe\n",
        "\n",
        "start_time = time.time()\n",
        "\n",
        "engine = create_engine(URL(\n",
        "                    account = 'ceiknnc-keb24333',\n",
        "                    user = 'thoufiq',\n",
        "                    password = 'Admin$1234',\n",
        "                    database = 'demo',\n",
        "                    schema = 'public',\n",
        "                    warehouse = 'demo_wh'))\n",
        "\n",
        "with engine.connect() as conn:\n",
        "    try:\n",
        "        query = \"\"\" SELECT ID, SPREADSHEET_NAME FROM otodom_data_log \"\"\"\n",
        "        df = pd.read_sql(query,conn)\n",
        "        df.columns = map(lambda x: str(x).upper(), df.columns)\n",
        "\n",
        "        gc = gspread.service_account()\n",
        "        loop_counter = 0\n",
        "\n",
        "        for index, row in df.iterrows():\n",
        "            loop_counter += 1\n",
        "            locals()['sh'+str(loop_counter)] = gc.open(row['SPREADSHEET_NAME'])\n",
        "            wks = locals()['sh'+str(loop_counter)].get_worksheet(0)\n",
        "            df_out = get_as_dataframe(wks, usecols=[0,1,2], nrows=wks.row_count, header=None, skiprows=1, evaluate_formulas=True)\n",
        "            print('Spreadsheet '+row['SPREADSHEET_NAME']+' loaded back to DataFrame!')\n",
        "\n",
        "            df_out.columns = ['RN', 'TITLE', 'TITLE_ENG']\n",
        "            df_out.to_sql('otodom_data_flatten_translate', con=engine, if_exists='append', index=False, chunksize=16000, method=pd_writer)\n",
        "\n",
        "    except Exception as e:\n",
        "        print('--- Error --- ',e)\n",
        "    finally:\n",
        "        conn.close()\n",
        "engine.dispose()\n",
        "\n",
        "print(\"--- %s seconds ---\" % (time.time() - start_time))"
      ],
      "metadata": {
        "id": "DIEROmWuYLpA"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}