eifo_webscraping_v1.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyN2mplTK5Hxb+l61fnqIvj9",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/tophercollins/eifo-data-extraction/blob/main/eifo_webscraping_v1.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# EIFO Country Risk & Cover Policies Web Scraping\n",
        "\n",
        "## Task\n",
        "*   Write a python script to scrape data from the EIFO website (https://subdomain.eifo.dk/en/countries).\n",
        "*   Scrape the Country Risk Classification & EIFO Cover Policies for Public Buyers, Private Buyers, and Banks for each country.\n",
        "*   Export the data into an Output Sheet in *.xlsm format.\n",
        "\n",
        "## Notes\n",
        "\n",
        "* https://subdomain.eifo.dk/en/countries - Initial page has a input box with the list of all countries to scrape\n",
        "* https://subdomain.eifo.dk/en/countries/germany - Country example, here Germany\n",
        "* https://subdomain.eifo.dk/en/countries/COUNTRY_NAME_HERE - Country url format\n",
        "\n",
        "Some have universal policies for all situations/timelines, see below.\n",
        "<img src='https://tophercollins.github.io/images/universal_policy.png'>\n",
        "\n",
        "Others have contextual policies for all situations/timelines, see below.\n",
        "<img src='https://tophercollins.github.io/images/contextual_policy.png'>\n",
        "\n",
        "## Steps\n",
        "1. Scrape list of countries.\n",
        "  * Countries list found in input box class `autocomplete__search-input`.\n",
        "  * Requires interaction to produce list of countries so will use selenium.\n",
        "  * Each country found in div class `autocomplete-suggestions__item`.\n",
        "  * Also contains info for rating/risk classification.\n",
        "2. Scrape a single country.\n",
        "  * Each set of policy info is found in div class `accordion-item`.\n",
        "  * Type found in div class `accordion-header`.\n",
        "  * Info found in div class `accordion-table`.\n",
        "3. Scrape all countries.\n",
        "  * Format country names containing symbols that won't convert to the URL.\n",
        "    * Spaces and single quote marks ' change to dash -\n",
        "    * Period . and curved brackets (  ) are removed\n",
        "  * After scraping format the policy information\n",
        "    * Provide universal policy where possible\n",
        "    * Provide policies with contexts in curved brackets ( ) for multi cases policies.\n",
        "4. Export Dataframe to xlsm output file called `Final.xlsm` with `Output` sheet.\n",
        "\n"
      ],
      "metadata": {
        "id": "G2ftggMaEyof"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 0. Setup"
      ],
      "metadata": {
        "id": "YdX0dcHna6IF"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Install Packages\n",
        "!pip install selenium\n",
        "!apt-get update\n",
        "!apt install -y chromium-chromedriver\n",
        "!cp /usr/lib/chromium-browser/chromedriver /usr/bin"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "WeXQdTfPa4Sv",
        "outputId": "b1089e76-59a3-4466-9511-c627468918bb"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Requirement already satisfied: selenium in /usr/local/lib/python3.10/dist-packages (4.22.0)\n",
            "Requirement already satisfied: urllib3[socks]<3,>=1.26 in /usr/local/lib/python3.10/dist-packages (from selenium) (2.0.7)\n",
            "Requirement already satisfied: trio~=0.17 in /usr/local/lib/python3.10/dist-packages (from selenium) (0.26.0)\n",
            "Requirement already satisfied: trio-websocket~=0.9 in /usr/local/lib/python3.10/dist-packages (from selenium) (0.11.1)\n",
            "Requirement already satisfied: certifi>=2021.10.8 in /usr/local/lib/python3.10/dist-packages (from selenium) (2024.7.4)\n",
            "Requirement already satisfied: typing_extensions>=4.9.0 in /usr/local/lib/python3.10/dist-packages (from selenium) (4.12.2)\n",
            "Requirement already satisfied: websocket-client>=1.8.0 in /usr/local/lib/python3.10/dist-packages (from selenium) (1.8.0)\n",
            "Requirement already satisfied: attrs>=23.2.0 in /usr/local/lib/python3.10/dist-packages (from trio~=0.17->selenium) (23.2.0)\n",
            "Requirement already satisfied: sortedcontainers in /usr/local/lib/python3.10/dist-packages (from trio~=0.17->selenium) (2.4.0)\n",
            "Requirement already satisfied: idna in /usr/local/lib/python3.10/dist-packages (from trio~=0.17->selenium) (3.7)\n",
            "Requirement already satisfied: outcome in /usr/local/lib/python3.10/dist-packages (from trio~=0.17->selenium) (1.3.0.post0)\n",
            "Requirement already satisfied: sniffio>=1.3.0 in /usr/local/lib/python3.10/dist-packages (from trio~=0.17->selenium) (1.3.1)\n",
            "Requirement already satisfied: exceptiongroup in /usr/local/lib/python3.10/dist-packages (from trio~=0.17->selenium) (1.2.1)\n",
            "Requirement already satisfied: wsproto>=0.14 in /usr/local/lib/python3.10/dist-packages (from trio-websocket~=0.9->selenium) (1.2.0)\n",
            "Requirement already satisfied: pysocks!=1.5.7,<2.0,>=1.5.6 in /usr/local/lib/python3.10/dist-packages (from urllib3[socks]<3,>=1.26->selenium) (1.7.1)\n",
            "Requirement already satisfied: h11<1,>=0.9.0 in /usr/local/lib/python3.10/dist-packages (from wsproto>=0.14->trio-websocket~=0.9->selenium) (0.14.0)\n",
            "Hit:1 https://cloud.r-project.org/bin/linux/ubuntu jammy-cran40/ InRelease\n",
            "Hit:2 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu2204/x86_64  InRelease\n",
            "Get:3 http://security.ubuntu.com/ubuntu jammy-security InRelease [129 kB]\n",
            "Ign:4 https://r2u.stat.illinois.edu/ubuntu jammy InRelease\n",
            "Get:5 https://r2u.stat.illinois.edu/ubuntu jammy Release [5,713 B]\n",
            "Hit:6 http://archive.ubuntu.com/ubuntu jammy InRelease\n",
            "Get:7 https://r2u.stat.illinois.edu/ubuntu jammy Release.gpg [793 B]\n",
            "Get:8 http://archive.ubuntu.com/ubuntu jammy-updates InRelease [128 kB]\n",
            "Hit:9 https://ppa.launchpadcontent.net/deadsnakes/ppa/ubuntu jammy InRelease\n",
            "Get:10 https://r2u.stat.illinois.edu/ubuntu jammy/main amd64 Packages [2,545 kB]\n",
            "Hit:11 https://ppa.launchpadcontent.net/graphics-drivers/ppa/ubuntu jammy InRelease\n",
            "Hit:12 https://ppa.launchpadcontent.net/ubuntugis/ppa/ubuntu jammy InRelease\n",
            "Hit:13 http://archive.ubuntu.com/ubuntu jammy-backports InRelease\n",
            "Get:14 https://r2u.stat.illinois.edu/ubuntu jammy/main all Packages [8,188 kB]\n",
            "Fetched 11.0 MB in 2s (5,622 kB/s)\n",
            "Reading package lists... Done\n",
            "W: Skipping acquire of configured file 'main/source/Sources' as repository 'https://r2u.stat.illinois.edu/ubuntu jammy InRelease' does not seem to provide it (sources.list entry misspelt?)\n",
            "Reading package lists... Done\n",
            "Building dependency tree... Done\n",
            "Reading state information... Done\n",
            "chromium-chromedriver is already the newest version (1:85.0.4183.83-0ubuntu2.22.04.1).\n",
            "0 upgraded, 0 newly installed, 0 to remove and 44 not upgraded.\n",
            "cp: '/usr/lib/chromium-browser/chromedriver' and '/usr/bin/chromedriver' are the same file\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Import Packages\n",
        "import pandas as pd\n",
        "import requests\n",
        "from bs4 import BeautifulSoup\n",
        "import time\n",
        "\n",
        "# Set up Chrome options for headless browsing\n",
        "from selenium import webdriver\n",
        "from selenium.webdriver.chrome.service import Service\n",
        "from selenium.webdriver.common.by import By\n",
        "from selenium.webdriver.common.keys import Keys\n",
        "from selenium.webdriver.support.ui import WebDriverWait\n",
        "from selenium.webdriver.support import expected_conditions as EC"
      ],
      "metadata": {
        "id": "eHCKHAAZGNyq"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Base url variable\n",
        "BASE_URL = \"https://subdomain.eifo.dk/en/countries\""
      ],
      "metadata": {
        "id": "AQB69gOwbVUP"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 1. Scrape list of countries"
      ],
      "metadata": {
        "id": "lT_yJ99DRluE"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Setup up the WebDriver\n",
        "options = webdriver.ChromeOptions()\n",
        "options.add_argument('--headless')\n",
        "options.add_argument('--no-sandbox')\n",
        "options.add_argument('--disable-dev-shm-usage')\n",
        "options.add_argument('--disable-gpu')\n",
        "options.add_argument('--window-size=1920x1080')\n",
        "options.add_argument('--ignore-certificate-errors')\n",
        "options.add_argument('--disable-extensions')\n",
        "options.add_argument('--disable-popup-blocking')\n",
        "options.add_argument('--disable-logging')\n",
        "\n",
        "driver = webdriver.Chrome(options=options)\n",
        "\n",
        "# Send driver to website\n",
        "driver.get(BASE_URL)\n",
        "\n",
        "try:\n",
        "    # Wait for the input box to be present\n",
        "    wait = WebDriverWait(driver, 10)\n",
        "    input_box = wait.until(EC.presence_of_element_located((By.ID, \"autocomplete__search-input\")))\n",
        "\n",
        "    # Interact with the input box (send keys to trigger the autocomplete)\n",
        "    input_box.send_keys(\"a\")\n",
        "    time.sleep(5)  # Wait for the autocomplete results to load\n",
        "\n",
        "    # Find all autocomplete suggestions\n",
        "    autocomplete_suggestions = wait.until(EC.presence_of_all_elements_located((By.CLASS_NAME, \"autocomplete-suggestions__item\")))\n",
        "\n",
        "    # Extract the text of each suggestion\n",
        "    output = []\n",
        "    for autocomplete_suggestion in autocomplete_suggestions:\n",
        "        output.append(autocomplete_suggestion.text)\n",
        "\n",
        "finally:\n",
        "    # Close the WebDriver\n",
        "    driver.quit()"
      ],
      "metadata": {
        "id": "xqc-Ze_8SeEo"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Check output\n",
        "print(output[:5])"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "IP2ktOkEaofO",
        "outputId": "68a2ff33-393f-4f61-d296-9dc2605dbab4"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "['azerbaijan\\n4', 'andorra\\n0', 'angola\\n6', 'argentina\\n7', 'austria\\n0']\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Upon inspection we receive both the country names and their risk rating from our initial scrape, so there is no need to scrape the risk rating from the individual country scraping later."
      ],
      "metadata": {
        "id": "mJduWxU6bi2h"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Make countries and ratings lists\n",
        "countries = []\n",
        "ratings = []\n",
        "\n",
        "for item in output:\n",
        "  country, rating = item.split(\"\\n\")\n",
        "  countries.append(country)\n",
        "  ratings.append(int(rating))\n",
        "\n",
        "print(countries[:5])\n",
        "print(ratings[:5])"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "eF1C6eevcdfB",
        "outputId": "7004268d-b4f5-4f21-92fa-fd772766ff87"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "['azerbaijan', 'andorra', 'angola', 'argentina', 'austria']\n",
            "[4, 0, 6, 7, 0]\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "We can make the pandas dataframe now that we have the ratings info already."
      ],
      "metadata": {
        "id": "ID4nUknziOZH"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Make a Pandas Dataframe with countries and ratings\n",
        "df = pd.DataFrame({'Country_Name': countries, 'Country_Risk_Classification': ratings})\n",
        "df.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "JG5vLUpOdjWD",
        "outputId": "c9928dd2-14fb-49de-9d1d-a7e22edb9b9f"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "  Country_Name  Country_Risk_Classification\n",
              "0   azerbaijan                            4\n",
              "1      andorra                            0\n",
              "2       angola                            6\n",
              "3    argentina                            7\n",
              "4      austria                            0"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-45534763-000a-4890-9cc6-c74ca506806d\" 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>Country_Name</th>\n",
              "      <th>Country_Risk_Classification</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>azerbaijan</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>andorra</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>angola</td>\n",
              "      <td>6</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>argentina</td>\n",
              "      <td>7</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>austria</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-45534763-000a-4890-9cc6-c74ca506806d')\"\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-45534763-000a-4890-9cc6-c74ca506806d 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-45534763-000a-4890-9cc6-c74ca506806d');\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-d6317c11-59a2-42d0-8fe6-d462921592b3\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-d6317c11-59a2-42d0-8fe6-d462921592b3')\"\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-d6317c11-59a2-42d0-8fe6-d462921592b3 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\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\": 163,\n  \"fields\": [\n    {\n      \"column\": \"Country_Name\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 163,\n        \"samples\": [\n          \"slovenia\",\n          \"cameroon\",\n          \"spain\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"Country_Risk_Classification\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 2,\n        \"min\": 0,\n        \"max\": 7,\n        \"num_unique_values\": 8,\n        \"samples\": [\n          0,\n          3,\n          4\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {},
          "execution_count": 54
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Sort dataframe alphabetically and readjust index\n",
        "df = df.sort_values(by='Country_Name')\n",
        "df = df.reset_index(drop=True)\n",
        "df.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "QZ6hWAg4ercB",
        "outputId": "afc2bbd4-4c9e-4b1c-f025-4c2486855fe7"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "  Country_Name  Country_Risk_Classification\n",
              "0  afghanistan                            7\n",
              "1      albania                            5\n",
              "2      algeria                            5\n",
              "3      andorra                            0\n",
              "4       angola                            6"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-597ce867-a10f-4f64-996d-f666acacfe16\" 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>Country_Name</th>\n",
              "      <th>Country_Risk_Classification</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>afghanistan</td>\n",
              "      <td>7</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>albania</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>algeria</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>andorra</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>angola</td>\n",
              "      <td>6</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-597ce867-a10f-4f64-996d-f666acacfe16')\"\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-597ce867-a10f-4f64-996d-f666acacfe16 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-597ce867-a10f-4f64-996d-f666acacfe16');\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-72365b1e-e2c8-4f7a-af8c-24d81a555ad1\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-72365b1e-e2c8-4f7a-af8c-24d81a555ad1')\"\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-72365b1e-e2c8-4f7a-af8c-24d81a555ad1 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\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\": 163,\n  \"fields\": [\n    {\n      \"column\": \"Country_Name\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 163,\n        \"samples\": [\n          \"slovak republic\",\n          \"pakistan\",\n          \"senegal\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"Country_Risk_Classification\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 2,\n        \"min\": 0,\n        \"max\": 7,\n        \"num_unique_values\": 8,\n        \"samples\": [\n          5,\n          2,\n          7\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {},
          "execution_count": 55
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 2. Scrape a single country"
      ],
      "metadata": {
        "id": "B5_ehvfAieWZ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Make request and soup\n",
        "SINGLE_COUNTRY_URL = BASE_URL + '/' + 'germany'\n",
        "response = requests.get(SINGLE_COUNTRY_URL)\n",
        "soup = BeautifulSoup(response.content, 'html.parser')\n",
        "\n",
        "# Find all policy groups\n",
        "policy_groups = soup.find_all('div', class_='accordion-item')\n",
        "\n",
        "# Initiate policy dictionaries\n",
        "public_buyer_policy = {}\n",
        "private_buyer_policy = {}\n",
        "bank_policy = {}\n",
        "\n",
        "# Loop through soups to add info to dictionaries\n",
        "for policy in policy_groups:\n",
        "    heading = policy.find('div', class_='accordion-header').text\n",
        "    content = policy.find_all('div', class_='accordion-table')\n",
        "    info = []\n",
        "    for item in content:\n",
        "      sentence = item.find_all('div', class_='cell')\n",
        "      for title, value in zip(sentence[0::2], sentence[1::2]):\n",
        "        if heading == 'Public buyer':\n",
        "          public_buyer_policy[title.text] = value.text\n",
        "        elif heading == 'Private buyer':\n",
        "          private_buyer_policy[title.text] = value.text\n",
        "        elif heading == 'Bank':\n",
        "          bank_policy[title.text] = value.text"
      ],
      "metadata": {
        "id": "daYudpLnjBlY"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Check dictionaries\n",
        "print(public_buyer_policy)\n",
        "print(private_buyer_policy)\n",
        "print(bank_policy)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "wUEiFG8FZorJ",
        "outputId": "71d6f891-217c-4b0f-9c83-8ca878eb22fb"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "{'Guarantees without credit': 'EIFO accepts guarantees from the Ministry of Finance. EIFO considers cover of other public buyers on a case-by-case basis.', 'Up to 1 year': 'EIFO accepts guarantees from the Ministry of Finance. EIFO considers cover of other public buyers on a case-by-case basis.', '1-5 years': 'EIFO accepts guarantees from the Ministry of Finance. EIFO considers cover of other public buyers on a case-by-case basis.', 'Over 5 years': 'EIFO accepts guarantees from the Ministry of Finance. EIFO considers cover of other public buyers on a case-by-case basis.'}\n",
            "{'Guarantees without credit': 'EIFO accepts all creditworthy buyers.', 'Up to 1 year': 'EIFO accepts all creditworthy buyers.', '1-5 years': 'EIFO accepts all creditworthy buyers.', 'Over 5 years': 'EIFO accepts all creditworthy buyers.'}\n",
            "{'Guarantees without credit': 'EIFO accepts creditworthy banks.', 'Up to 1 year': 'EIFO accepts creditworthy banks.', '1-5 years': 'EIFO accepts creditworthy banks.', 'Over 5 years': 'EIFO accepts creditworthy banks.'}\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 3. Scrape all countries"
      ],
      "metadata": {
        "id": "4tGw0qkJ-0fK"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Scrape all countries from df['Country_Name']\n",
        "public_buyer_policies_all_countries = []\n",
        "private_buyer_policies_all_countries = []\n",
        "bank_policies_all_countries = []\n",
        "\n",
        "for country in df['Country_Name']:\n",
        "\n",
        "  # Adjust country names with symbols not suitable for urls\n",
        "  country = country.replace(\" \", \"-\")\n",
        "  country = country.replace(\"'\", \"-\")\n",
        "  country = country.replace(\".\", \"\")\n",
        "  country = country.replace(\"(\", \"\")\n",
        "  country = country.replace(\")\", \"\")\n",
        "\n",
        "  # Make request and soup\n",
        "  COUNTRY_URL = BASE_URL + '/' + country\n",
        "  response = requests.get(COUNTRY_URL)\n",
        "  soup = BeautifulSoup(response.content, 'html.parser')\n",
        "\n",
        "  # Find all policy groups\n",
        "  policy_groups = soup.find_all('div', class_='accordion-item')\n",
        "\n",
        "  # Initiate policy dictionaries\n",
        "  public_buyer_policy = {}\n",
        "  private_buyer_policy = {}\n",
        "  bank_policy = {}\n",
        "\n",
        "  # Loop through soups to add info to dictionaries\n",
        "  for policy in policy_groups:\n",
        "    heading = policy.find('div', class_='accordion-header').text\n",
        "    content = policy.find_all('div', class_='accordion-table')\n",
        "    info = []\n",
        "    for item in content:\n",
        "      sentence = item.find_all('div', class_='cell')\n",
        "      for title, value in zip(sentence[0::2], sentence[1::2]):\n",
        "        if heading == 'Public buyer':\n",
        "          public_buyer_policy[title.text] = value.text\n",
        "        elif heading == 'Private buyer':\n",
        "          private_buyer_policy[title.text] = value.text\n",
        "        elif heading == 'Bank':\n",
        "          bank_policy[title.text] = value.text\n",
        "\n",
        "  # Append dictionaries to lists\n",
        "  public_buyer_policies_all_countries.append(public_buyer_policy)\n",
        "  private_buyer_policies_all_countries.append(private_buyer_policy)\n",
        "  bank_policies_all_countries.append(bank_policy)"
      ],
      "metadata": {
        "id": "j7Xaw9pW-641"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Check lists\n",
        "print(public_buyer_policies_all_countries[:2])\n",
        "print(private_buyer_policies_all_countries[:2])\n",
        "print(bank_policies_all_countries[:2])"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "_aVRlRyZZmNu",
        "outputId": "10451b6b-b772-4cd9-9816-6d4bfdbab7c5"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[{'Guarantees without credit': 'Off cover. EIFO provides no guarantee cover for business transactions with public-sector buyers.', 'Up to 1 year': 'Off cover. EIFO provides no guarantee cover for business transactions with public-sector buyers.', '1-5 years': 'Cover is suspended.', 'Over 5 years': 'Cover is suspended.'}, {'Guarantees without credit': 'EIFO accepts guarantees from the Ministry of Finance. EIFO considers cover of other public buyers on a case-by-case basis.', 'Up to 1 year': 'EIFO accepts guarantees from the Ministry of Finance. EIFO considers cover of other public buyers on a case-by-case basis.', '1-5 years': 'EIFO accepts guarantees from the Ministry of Finance. EIFO considers cover of other public buyers on a case-by-case basis.', 'Over 5 years': 'EIFO accepts guarantees from the Ministry of Finance. EIFO considers cover of other public buyers on a case-by-case basis.'}]\n",
            "[{'Guarantees without credit': 'EIFO does not provide cover for private buyers – for information on possible cover through the banking market, see the section “Bank”.', 'Up to 1 year': 'EIFO does not provide cover for private buyers – for information on possible cover through the banking market, see the section “Bank”.', '1-5 years': 'Cover is suspended.', 'Over 5 years': 'Cover is suspended.'}, {'Guarantees without credit': 'EIFO accepts all creditworthy buyers.', 'Up to 1 year': 'EIFO accepts all creditworthy buyers.', '1-5 years': 'EIFO accepts all creditworthy buyers.', 'Over 5 years': 'EIFO is open for private buyers with certain restrictions.'}]\n",
            "[{'Guarantees without credit': 'EIFO does not cover risks on banks in the country.', 'Up to 1 year': 'EIFO does not cover risks on banks in the country.', '1-5 years': 'Cover is suspended.', 'Over 5 years': 'Cover is suspended.'}, {'Guarantees without credit': 'EIFO only accepts the most solid banks.', 'Up to 1 year': 'EIFO only accepts the most solid banks.', '1-5 years': 'EIFO only accepts the most solid banks.', 'Over 5 years': 'EIFO only accepts the most solid banks.'}]\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "We should reformat the policy information to group together repetition of policies.\n",
        "\n",
        "Ideally providing the one universal policy across all contexts. Or providing the contexts in brackets when there are multiple policies."
      ],
      "metadata": {
        "id": "YmL8qc5zTuOH"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Reformat the policies\n",
        "def format_policies(policy_list):\n",
        "\n",
        "  formatted_policy_list = []\n",
        "\n",
        "  for info in policy_list:\n",
        "    # Flip the dictionaries so policies have grouped contexts\n",
        "    unique_values = {}\n",
        "    for key, value in info.items():\n",
        "      if value in unique_values.keys():\n",
        "        unique_values[value].append(key)\n",
        "      else:\n",
        "        unique_values[value] = [key]\n",
        "\n",
        "    if len(unique_values) == 1:\n",
        "      # Add single universal policy\n",
        "      formatted_policy_list.append(list(unique_values.keys())[0])\n",
        "    else:\n",
        "      # Add policies with context in curved brackets\n",
        "      combined_info = ''\n",
        "      for key, value in unique_values.items():\n",
        "        combined_info += f'{key} ({\", \".join(value)}), '\n",
        "      formatted_policy_list.append(combined_info[:-2]) # Remove the last ', '\n",
        "\n",
        "  return formatted_policy_list"
      ],
      "metadata": {
        "id": "XQuYaa1GML5n"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Format policy lists\n",
        "formatted_public_buyer_policies_all_countries = format_policies(public_buyer_policies_all_countries)\n",
        "formatted_private_buyer_policies_all_countries = format_policies(private_buyer_policies_all_countries)\n",
        "formatted_bank_policies_all_countries = format_policies(bank_policies_all_countries)"
      ],
      "metadata": {
        "id": "3OMjhsKURukM"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Add formatted policy lists to dataframe\n",
        "df['EIFOs_cover_policy(Public_Buyer)'] = formatted_public_buyer_policies_all_countries\n",
        "df['EIFOs_cover_policy(Private_Buyer)'] = formatted_private_buyer_policies_all_countries\n",
        "df['EIFOs_cover_policy (Bank)'] = formatted_bank_policies_all_countries"
      ],
      "metadata": {
        "id": "mWqYStrsAOZg"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Check dataframe\n",
        "df.head(10)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 363
        },
        "id": "haThSytoAYd7",
        "outputId": "758dd0c6-c435-4fda-ab69-1919e0306698"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "          Country_Name  Country_Risk_Classification  \\\n",
              "0          afghanistan                            7   \n",
              "1              albania                            5   \n",
              "2              algeria                            5   \n",
              "3              andorra                            0   \n",
              "4               angola                            6   \n",
              "5  antigua and barbuda                            7   \n",
              "6            argentina                            7   \n",
              "7              armenia                            6   \n",
              "8                aruba                            5   \n",
              "9            australia                            0   \n",
              "\n",
              "                    EIFOs_cover_policy(Public_Buyer)  \\\n",
              "0  Off cover. EIFO provides no guarantee cover fo...   \n",
              "1  EIFO accepts guarantees from the Ministry of F...   \n",
              "2  EIFO accepts guarantees from the Ministry of F...   \n",
              "3  EIFO accepts guarantees from the Ministry of F...   \n",
              "4  EIFO accepts guarantees from the Ministry of F...   \n",
              "5                     Not considered – contact EIFO.   \n",
              "6                  Usually off cover - contact EIFO.   \n",
              "7  Cover of public sector buyers is subject to so...   \n",
              "8  EIFO accepts guarantees from the Ministry of F...   \n",
              "9  EIFO accepts guarantees from the Ministry of F...   \n",
              "\n",
              "                   EIFOs_cover_policy(Private_Buyer)  \\\n",
              "0  EIFO does not provide cover for private buyers...   \n",
              "1  EIFO accepts all creditworthy buyers. (Guarant...   \n",
              "2  EIFO is open for private buyers with certain r...   \n",
              "3              EIFO accepts all creditworthy buyers.   \n",
              "4  EIFO is open to creditworthy buyers for transa...   \n",
              "5                     Not considered – contact EIFO.   \n",
              "6                  Usually off cover - contact EIFO.   \n",
              "7  EIFO accepts all creditworthy buyers. (Guarant...   \n",
              "8              EIFO accepts all creditworthy buyers.   \n",
              "9              EIFO accepts all creditworthy buyers.   \n",
              "\n",
              "                           EIFOs_cover_policy (Bank)  \n",
              "0  EIFO does not cover risks on banks in the coun...  \n",
              "1            EIFO only accepts the most solid banks.  \n",
              "2                   EIFO accepts creditworthy banks.  \n",
              "3                   EIFO accepts creditworthy banks.  \n",
              "4  EIFO is open to creditworthy banks for transac...  \n",
              "5                     Not considered – contact EIFO.  \n",
              "6  Usually off cover - contact EIFO. (Guarantees ...  \n",
              "7                   EIFO accepts creditworthy banks.  \n",
              "8                   EIFO accepts creditworthy banks.  \n",
              "9                   EIFO accepts creditworthy banks.  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-b0dcd6bf-d01d-4ae7-89db-4974b2bbb8d3\" 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>Country_Name</th>\n",
              "      <th>Country_Risk_Classification</th>\n",
              "      <th>EIFOs_cover_policy(Public_Buyer)</th>\n",
              "      <th>EIFOs_cover_policy(Private_Buyer)</th>\n",
              "      <th>EIFOs_cover_policy (Bank)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>afghanistan</td>\n",
              "      <td>7</td>\n",
              "      <td>Off cover. EIFO provides no guarantee cover fo...</td>\n",
              "      <td>EIFO does not provide cover for private buyers...</td>\n",
              "      <td>EIFO does not cover risks on banks in the coun...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>albania</td>\n",
              "      <td>5</td>\n",
              "      <td>EIFO accepts guarantees from the Ministry of F...</td>\n",
              "      <td>EIFO accepts all creditworthy buyers. (Guarant...</td>\n",
              "      <td>EIFO only accepts the most solid banks.</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>algeria</td>\n",
              "      <td>5</td>\n",
              "      <td>EIFO accepts guarantees from the Ministry of F...</td>\n",
              "      <td>EIFO is open for private buyers with certain r...</td>\n",
              "      <td>EIFO accepts creditworthy banks.</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>andorra</td>\n",
              "      <td>0</td>\n",
              "      <td>EIFO accepts guarantees from the Ministry of F...</td>\n",
              "      <td>EIFO accepts all creditworthy buyers.</td>\n",
              "      <td>EIFO accepts creditworthy banks.</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>angola</td>\n",
              "      <td>6</td>\n",
              "      <td>EIFO accepts guarantees from the Ministry of F...</td>\n",
              "      <td>EIFO is open to creditworthy buyers for transa...</td>\n",
              "      <td>EIFO is open to creditworthy banks for transac...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>antigua and barbuda</td>\n",
              "      <td>7</td>\n",
              "      <td>Not considered – contact EIFO.</td>\n",
              "      <td>Not considered – contact EIFO.</td>\n",
              "      <td>Not considered – contact EIFO.</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>argentina</td>\n",
              "      <td>7</td>\n",
              "      <td>Usually off cover - contact EIFO.</td>\n",
              "      <td>Usually off cover - contact EIFO.</td>\n",
              "      <td>Usually off cover - contact EIFO. (Guarantees ...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>armenia</td>\n",
              "      <td>6</td>\n",
              "      <td>Cover of public sector buyers is subject to so...</td>\n",
              "      <td>EIFO accepts all creditworthy buyers. (Guarant...</td>\n",
              "      <td>EIFO accepts creditworthy banks.</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>aruba</td>\n",
              "      <td>5</td>\n",
              "      <td>EIFO accepts guarantees from the Ministry of F...</td>\n",
              "      <td>EIFO accepts all creditworthy buyers.</td>\n",
              "      <td>EIFO accepts creditworthy banks.</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>australia</td>\n",
              "      <td>0</td>\n",
              "      <td>EIFO accepts guarantees from the Ministry of F...</td>\n",
              "      <td>EIFO accepts all creditworthy buyers.</td>\n",
              "      <td>EIFO accepts creditworthy banks.</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-b0dcd6bf-d01d-4ae7-89db-4974b2bbb8d3')\"\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-b0dcd6bf-d01d-4ae7-89db-4974b2bbb8d3 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-b0dcd6bf-d01d-4ae7-89db-4974b2bbb8d3');\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-f23f4b12-4483-41b0-8206-4c5663175a96\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-f23f4b12-4483-41b0-8206-4c5663175a96')\"\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-f23f4b12-4483-41b0-8206-4c5663175a96 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\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\": 163,\n  \"fields\": [\n    {\n      \"column\": \"Country_Name\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 163,\n        \"samples\": [\n          \"slovak republic\",\n          \"pakistan\",\n          \"senegal\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"Country_Risk_Classification\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 2,\n        \"min\": 0,\n        \"max\": 7,\n        \"num_unique_values\": 8,\n        \"samples\": [\n          5,\n          2,\n          7\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"EIFOs_cover_policy(Public_Buyer)\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 20,\n        \"samples\": [\n          \"Off cover. EIFO provides no guarantee cover for business transactions with public-sector buyers. (Guarantees without credit, Up to 1 year) , Cover is suspended. (1-5 years, Over 5 years)\",\n          \"Cover of public sector buyers is subject to sovereign guarantee, i.e. a guarantee from the Ministry of Finance. (Guarantees without credit, Up to 1 year, 1-5 years) , EIFO accepts guarantees from the Ministry of Finance for transactions up to DKK 25 million. (Over 5 years)\",\n          \"Cover of public sector buyers is subject to sovereign guarantee, i.e. a guarantee from the Ministry of Finance. (Guarantees without credit, Up to 1 year) , Off cover. EIFO provides no guarantee cover for business transactions with public-sector buyers. (1-5 years, Over 5 years)\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"EIFOs_cover_policy(Private_Buyer)\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 22,\n        \"samples\": [\n          \"EIFO does not provide cover for private buyers \\u2013 for information on possible cover through the banking market, see the section \\u201cBank\\u201d. (Guarantees without credit, Up to 1 year) , Cover is suspended. (1-5 years, Over 5 years)\",\n          \"EIFO is open for private buyers with certain restrictions. (Guarantees without credit) , EIFO does not provide cover for private buyers \\u2013 for information on possible cover through the banking market, see the section \\u201cBank\\u201d. (Up to 1 year) , Not considered \\u2013 contact EIFO. (1-5 years, Over 5 years)\",\n          \"Cover is suspended.\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"EIFOs_cover_policy (Bank)\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 28,\n        \"samples\": [\n          \"EIFO accepts creditworthy banks. (Guarantees without credit, Up to 1 year) , EIFO only accepts the most solid banks. (1-5 years, Over 5 years)\",\n          \"EIFO only accepts the most solid banks. (Guarantees without credit, Up to 1 year, 1-5 years) , Usually off cover - contact EIFO. (Over 5 years)\",\n          \"Cover is suspended.\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {},
          "execution_count": 67
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 4. Export data to xlsm"
      ],
      "metadata": {
        "id": "cf8LpXvqhcnV"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Export data to xlsm\n",
        "df.to_excel('Final.xlsm', sheet_name='Output', index=False)"
      ],
      "metadata": {
        "id": "kR3PWwrJevGh"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}