Colab_SDG_15_4_2_Sub_A_Default_values.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "view-in-github"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/andyarnell/sepal_mgci/blob/master/SDG_15_4_2_Sub_A_Default_values.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "82FWR5yMh0HV"
      },
      "source": [
        "# **SDG 15.4.2 Sub-indicator A: Calculate Global Default Values**\n",
        "\n",
        "* This script allows batch processing for this indicator for all countries.\n",
        "\n",
        "* Output is a combined excel file on your Google Drive.\n",
        "\n",
        "* Runs on the cloud using [Google Colab](https://research.google.com/colaboratory/faq.html)\n",
        "\n",
        "* Requires: [Google Earth Engine](https://earthengine.google.com/) (GEE) account and project and access to Google Drive\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YdoCOI_1yWY0"
      },
      "source": [
        "### 1) Install required packages"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 208
        },
        "id": "Kg3K1EPJu_f5",
        "outputId": "460f7ba2-8668-4c4d-824b-6d5068884129"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m6.1/6.1 MB\u001b[0m \u001b[31m34.4 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m2.7/2.7 MB\u001b[0m \u001b[31m54.9 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.6/1.6 MB\u001b[0m \u001b[31m38.1 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hipyvuetify has been installed.\n",
            "ee is already installed.\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m235.5/235.5 kB\u001b[0m \u001b[31m4.3 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hunidecode has been installed.\n",
            "google-api-python-client has been installed.\n",
            "google-auth-httplib2 has been installed.\n",
            "google-auth-oauthlib has been installed.\n",
            "geemap is already installed.\n"
          ]
        }
      ],
      "source": [
        "# to automatically reload modules.\n",
        "%load_ext autoreload\n",
        "\n",
        "# Set to reload all modules before executing code.\n",
        "%autoreload 2\n",
        "\n",
        "# Function to install a package if it's not already installed\n",
        "def install_if_not_exists(package_name):\n",
        "    try:\n",
        "        __import__(package_name)\n",
        "        print(f\"{package_name} is already installed.\")\n",
        "    except ImportError:\n",
        "        !pip install -q {package_name}\n",
        "        print(f\"{package_name} has been installed.\")\n",
        "\n",
        "# List of packages to install if not already installed\n",
        "packages_to_install = ['ipyvuetify','ee', 'unidecode', 'google-api-python-client',\n",
        "                      'google-auth-httplib2', 'google-auth-oauthlib','geemap'] #admin_boundaries\n",
        "\n",
        "# Install necessary packages\n",
        "for package in packages_to_install:\n",
        "    install_if_not_exists(package)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qGfLFLEkwS1n"
      },
      "source": [
        "### 2) Access GitHub repository\n",
        "Clones repository for SDG 15.4.2 into colab.\n",
        "Provides functions and lookup tables etc."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 173
        },
        "id": "TNohZrOTvNqT",
        "outputId": "1ad0a56a-2b6e-4966-ab95-a55a1a612aae"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "            <style>\n",
              "                .geemap-dark {\n",
              "                    --jp-widgets-color: white;\n",
              "                    --jp-widgets-label-color: white;\n",
              "                    --jp-ui-font-color1: white;\n",
              "                    --jp-layout-color2: #454545;\n",
              "                    background-color: #383838;\n",
              "                }\n",
              "\n",
              "                .geemap-dark .jupyter-button {\n",
              "                    --jp-layout-color3: #383838;\n",
              "                }\n",
              "\n",
              "                .geemap-colab {\n",
              "                    background-color: var(--colab-primary-surface-color, white);\n",
              "                }\n",
              "\n",
              "                .geemap-colab .jupyter-button {\n",
              "                    --jp-layout-color3: var(--colab-primary-surface-color, white);\n",
              "                }\n",
              "            </style>\n",
              "            "
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "/content\n",
            "Cloning into 'sepal_mgci'...\n",
            "remote: Enumerating objects: 3071, done.\u001b[K\n",
            "remote: Counting objects: 100% (1009/1009), done.\u001b[K\n",
            "remote: Compressing objects: 100% (426/426), done.\u001b[K\n",
            "remote: Total 3071 (delta 663), reused 807 (delta 582), pack-reused 2062\u001b[K\n",
            "Receiving objects: 100% (3071/3071), 5.02 MiB | 17.37 MiB/s, done.\n",
            "Resolving deltas: 100% (1939/1939), done.\n",
            "/content/sepal_mgci\n"
          ]
        }
      ],
      "source": [
        "# Change the current working directory to \"/content\" for cloning the repo into.\n",
        "%cd \"/content\"\n",
        "\n",
        "# Clone the GitHub repository \"sepalz_mgci\" into the current directory.\n",
        "# NB 'fatal' error on reruns are typically just saying it already exists\n",
        "!git clone https://github.com/sepal-contrib/sepal_mgci\n",
        "!cd sepal_mgci && git pull\n",
        "\n",
        "# Change working directory to the cloned sepal_mgci github repository\n",
        "%cd \"/content/sepal_mgci\""
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Lbg9P7frZRy1"
      },
      "source": [
        "### 3) Setup Google Earth Engine\n",
        "Launches access request pop up window"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "SLLcPONnZRy2"
      },
      "outputs": [],
      "source": [
        "# Google Earth Engine project\n",
        "gee_project_name = \"ee-andyarnellgee\" # \"insert cloud project here\"  # a registered cloud project (if unsure of name see pic here: https://developers.google.com/earth-engine/cloud/assets)\n",
        "\n",
        "import ee # google earth engine\n",
        "\n",
        "ee.Authenticate()\n",
        "\n",
        "ee.Initialize(project=gee_project_name) # NB gee project name is defined in parameters section"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cxXsgaXEZRy3"
      },
      "source": [
        "### 4) Setup Google Drive\n",
        "Launches access request pop up window"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "e2NxIhcgZRy3"
      },
      "outputs": [],
      "source": [
        "# for accessing google drive\n",
        "from google.colab import auth, drive\n",
        "from googleapiclient.discovery import build\n",
        "\n",
        "drive.mount('/content/drive')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MsQu9VfqZRy3"
      },
      "source": [
        "### 5) Import remaining packages\n",
        "- imports required packages and functions listed in 'colab_imports.py' script (found here: sepal_mgci/component/scripts)\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "NQ9flKZGZRy4"
      },
      "outputs": [],
      "source": [
        "from component.scripts.colab_imports import *"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Bi0XpRRDo6V1"
      },
      "source": [
        "### 6) Set parameters\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5aIr_OxkG-Ky"
      },
      "source": [
        "Input parameters"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "t9C1qT5bGoqt"
      },
      "outputs": [],
      "source": [
        "# Google Earth Engine project\n",
        "gee_project_name = \"ee-andyarnellgee\" # \"insert cloud project here\"  # a registered cloud project (if unsure of name see pic here: https://developers.google.com/earth-engine/cloud/assets)\n",
        "\n",
        "\n",
        "# Admin boundaries asset\n",
        "admin_asset_id = \"FAO/GAUL/2015/level0\" # administrative units feature collection\n",
        "\n",
        "admin_asset_property_name = \"ADM0_NAME\" # property/column name for selecting admin boundaries (e.g. ISO3 code or country name)\n",
        "\n",
        "\n",
        "# Land cover assets\n",
        "\n",
        "# For Sub-indicator A (sub_a), we need to set the following structure.\n",
        "a_years = {\n",
        "    1: {\"asset\": \"users/amitghosh/sdg_module/esa/cci_landcover/2000\", \"year\": 2000}, # baseline\n",
        "    2: {\"year\": 2003, \"asset\": \"users/amitghosh/sdg_module/esa/cci_landcover/2003\"}, # subsequent reporting years...\n",
        "    3: {\"year\": 2007, \"asset\": \"users/amitghosh/sdg_module/esa/cci_landcover/2007\"},\n",
        "    4: {\"year\": 2010, \"asset\": \"users/amitghosh/sdg_module/esa/cci_landcover/2010\"},\n",
        "}\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "naz3Qe4JHFER"
      },
      "source": [
        "Output parameters\n",
        "\n",
        "---\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "mSvTM29TnndR"
      },
      "outputs": [],
      "source": [
        "final_report_folder = \"sdg_15_4_2_A_combined_report\" # folder name in Google Drive for final output (if doesnt exist creates one)\n",
        "\n",
        "final_report_name = \"sdg_15_4_2_A_default_global.xlsx\" # file name for final excel output\n",
        "\n",
        "# export GEE tasks or not\n",
        "export = False # default: True. Set to False if debugging or limiting accidental re-exporting of tasks\n",
        "\n",
        "# prints more messages\n",
        "debug = False # default: False. Set to True if debugging code"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "iV8-wbddOJuT"
      },
      "source": [
        "Temporary output parameters\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "TCBNkALuOL1N"
      },
      "outputs": [],
      "source": [
        "stats_csv_folder = \"sdg_15_4_2_A_csvs\" # for storing stats tables exported from GEE for each admin boundary/AOI\n",
        "\n",
        "excel_reports_folder = \"sdg_15_4_2_A_reports\" # for storing formatted excel tables for each admin boundary/AOI\n",
        "\n",
        "drive_home =\"/content/drive/MyDrive/\" # Google Drive location. Don't change unless you know this is incorrect\n",
        "\n",
        "error_log_file_path = drive_home + excel_reports_folder + \"/\"+\"1_error_log\" +\".csv\" # for storing errors\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "c_8UzlUnu_f6"
      },
      "source": [
        "### 7) Setup inputs for processing"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qi5zNGTWN3df"
      },
      "source": [
        "Create list of boundaries to process"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "BiuBEJwPue2v"
      },
      "outputs": [],
      "source": [
        "# admin boundary feature collection\n",
        "admin_boundaries = ee.FeatureCollection(admin_asset_id)\n",
        "\n",
        "# list to process\n",
        "list_of_countries = admin_boundaries.aggregate_array(admin_asset_property_name).getInfo()\n",
        "\n",
        "print (\"Length of admin boundaries to process\", len(list_of_countries))\n",
        "\n",
        "list_of_countries = list(set(list_of_countries)) # remove dupicates\n",
        "\n",
        "print (\"Length of distinct admin boundaries to process\", (len(set(list_of_countries))))\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2G1q9TSiUsc1"
      },
      "source": [
        "Read the default land cover remapping table and convert it to a dictionary"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "PRSEqq5bu_f7"
      },
      "outputs": [],
      "source": [
        "default_map_matrix = map_matrix_to_dict(LC_MAP_MATRIX)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4-Ut_-S35Yg8"
      },
      "source": [
        "Select years of land cover to process"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 17
        },
        "id": "PwqJFWR4u_f7",
        "outputId": "09d3ab98-6b20-4212-b5ed-f3386c3a32bf"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "            <style>\n",
              "                .geemap-dark {\n",
              "                    --jp-widgets-color: white;\n",
              "                    --jp-widgets-label-color: white;\n",
              "                    --jp-ui-font-color1: white;\n",
              "                    --jp-layout-color2: #454545;\n",
              "                    background-color: #383838;\n",
              "                }\n",
              "\n",
              "                .geemap-dark .jupyter-button {\n",
              "                    --jp-layout-color3: #383838;\n",
              "                }\n",
              "\n",
              "                .geemap-colab {\n",
              "                    background-color: var(--colab-primary-surface-color, white);\n",
              "                }\n",
              "\n",
              "                .geemap-colab .jupyter-button {\n",
              "                    --jp-layout-color3: var(--colab-primary-surface-color, white);\n",
              "                }\n",
              "            </style>\n",
              "            "
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        }
      ],
      "source": [
        "# extracts the years from the a_years dictionary (as defined in parameters)\n",
        "single_years = [y[\"year\"] for  y in a_years.values()]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "iNxHtR984cNk"
      },
      "source": [
        "### 8) Calculate area statistics by country\n",
        "* Runs for each country and each mountain biobelt\n",
        "* Gets area of land cover reclassified into the 10 SEAM classes\n",
        "* Repeat for each year specified\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "VftKLuY4u_f7"
      },
      "outputs": [],
      "source": [
        "# you can monitor your GEE tasks here : https://code.earthengine.google.com/tasks\n",
        "create_folder_if_not_exists(stats_csv_folder) # to store outputs in google drive\n",
        "\n",
        "counter=0 # starting place of counter used to keep track of number of tasks that are being run\n",
        "\n",
        "for aoi_name in list_of_countries:\n",
        "\n",
        "    aoi = admin_boundaries.filter(ee.Filter.eq(admin_asset_property_name,aoi_name))#.first()\n",
        "\n",
        "    # gets areas of landcover in each mountain belt in each country\n",
        "    # uses reduce_regions function imported from the cloned sepal_mgci git hub repository (see Imports section)\n",
        "    # pixels counted at native resolution (scale) of input land cover (or DEM if RSA implementation)\n",
        "    process = ee.FeatureCollection([\n",
        "        ee.Feature(\n",
        "            None,\n",
        "            reduce_regions(\n",
        "                aoi,\n",
        "                remap_matrix=default_map_matrix,\n",
        "                rsa=False,\n",
        "                # dem=param.DEM_DEFAULT,\n",
        "                dem=DEM_DEFAULT, #default digital elevation model (DEM). Relevant for the real surface area (RSA) implementation.\n",
        "                lc_years= year,\n",
        "                transition_matrix=False\n",
        "            )\n",
        "        ).set(\"process_id\", year[0][\"year\"])\n",
        "        for year in get_a_years(a_years) # creates GEE images and runs stats on each. Images to run are in the 'a_years\" dictionary (above)\n",
        "    ])\n",
        "\n",
        "    #make name acceptable for running tasks (i.e., removes special characters)\n",
        "    task_name = str(sanitize_description(unidecode(aoi_name)))\n",
        "\n",
        "\n",
        "    task = ee.batch.Export.table.toDrive(\n",
        "        **{  #asterisks unpack dictionary into keyword arguments format\n",
        "            \"collection\": process,\n",
        "            \"description\": task_name,\n",
        "            \"fileFormat\": \"CSV\",\n",
        "            \"folder\":stats_csv_folder,\n",
        "            \"selectors\": [\n",
        "                \"process_id\",\n",
        "                \"sub_a\",\n",
        "            ],\n",
        "        }\n",
        "    )\n",
        "\n",
        "    counter+=1\n",
        "\n",
        "    print (f\"\\r process {counter}/{len(list_of_countries)} {aoi_name} \", end=\"\") #print in place (remove \\r and end=\"\" for verbose version)\n",
        "\n",
        "    if export:\n",
        "      task.start()\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vM0SIvJtu_f8"
      },
      "source": [
        "### 9) Read and translate results into report tables"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3x7jwkZJWwE-"
      },
      "source": [
        "#####NOTE: you will need to wait until results files for each country have been created in your google drive (from previous step).\n",
        "- see here to monitor the tasks https://code.earthengine.google.com/tasks\n",
        "- once tasks are complete, you can run the cell below\n",
        "\n",
        "This cell formats individual excel reports for each country.\n",
        "See Error_log.csv for missing files/errors"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "qkpDfHqQu_f9"
      },
      "outputs": [],
      "source": [
        "# Initialize the counter\n",
        "counter = 0\n",
        "\n",
        "# to store outputs in google drive\n",
        "create_folder_if_not_exists(excel_reports_folder)\n",
        "\n",
        "# Loop over each AOI name in the list of countries\n",
        "for aoi_name in list_of_countries:\n",
        "    counter += 1\n",
        "\n",
        "    # Clean the AOI name\n",
        "    aoi_name_clean = str(sanitize_description(unidecode(aoi_name)))\n",
        "\n",
        "    # Construct the file path for the stats CSV file\n",
        "    stats_csv_file = aoi_name_clean + \".csv\"\n",
        "    stats_csv_file_path = os.path.join(drive_home, stats_csv_folder, stats_csv_file)\n",
        "\n",
        "    message = f\"Process {counter}, {stats_csv_file}\"\n",
        "\n",
        "    try:\n",
        "        # Read the results from the CSV file and parse it to a dictionary\n",
        "        dict_results = read_from_csv(stats_csv_file_path)\n",
        "\n",
        "        details = {\n",
        "            \"geo_area_name\": aoi_name,\n",
        "            \"ref_area\": \" \",\n",
        "            \"source_detail\": \" \",\n",
        "        }\n",
        "\n",
        "        # Generate reports for the sub_a and mtn indicators\n",
        "        sub_a_reports = [sub_a.get_reports(parse_result(dict_results[year][\"sub_a\"], single=True), year, **details) for year in single_years]\n",
        "        mtn_reports = [mntn.get_report(parse_result(dict_results[year][\"sub_a\"], single=True), year, **details) for year in single_years]\n",
        "\n",
        "        # Concatenate the mtn reports\n",
        "        mtn_reports_df = pd.concat(mtn_reports)\n",
        "\n",
        "        # Concatenate the sub a reports\n",
        "        er_mtn_grnvi_df = pd.concat([report[0] for report in sub_a_reports])\n",
        "        er_mtn_grncov_df = pd.concat([report[1] for report in sub_a_reports])\n",
        "\n",
        "        # Define the output report file path\n",
        "        report_file_path = os.path.join(drive_home, excel_reports_folder, aoi_name_clean + \".xlsx\")\n",
        "\n",
        "        # Create the Excel file with the reports\n",
        "        with pd.ExcelWriter(report_file_path) as writer:\n",
        "            mtn_reports_df.to_excel(writer, sheet_name=\"Table1_ER_MTN_TOTL\", index=False)\n",
        "            er_mtn_grncov_df.to_excel(writer, sheet_name=\"Table2_ER_MTN_GRNCOV\", index=False)\n",
        "            er_mtn_grnvi_df.to_excel(writer, sheet_name=\"Table3_ER_MTN_GRNCVI\", index=False)\n",
        "\n",
        "            # Adjust column widths and alignment for each sheet\n",
        "            for sheetname in writer.sheets:\n",
        "                worksheet = writer.sheets[sheetname]\n",
        "                for col in worksheet.columns:\n",
        "                    max_length = max(len(str(cell.value)) for cell in col)\n",
        "                    column = col[0]\n",
        "                    adjusted_width = max(max_length, len(str(column.value))) + 4\n",
        "                    worksheet.column_dimensions[get_column_letter(column.column)].width = adjusted_width\n",
        "\n",
        "                    # Align \"obs_value\" column to the right\n",
        "                    if \"OBS\" in column.value:\n",
        "                        for cell in col:\n",
        "                            cell.alignment = Alignment(horizontal=\"right\")\n",
        "\n",
        "    except Exception as e:\n",
        "        # If an error occurs, catch the exception and handle it\n",
        "        message = f\"process {counter}, {stats_csv_file}, Error: {e}\"\n",
        "\n",
        "        # Get the current time\n",
        "        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')\n",
        "\n",
        "        # Write the error message and file name to the error log file\n",
        "        error_info = pd.DataFrame([[stats_csv_file, str(e), current_time]], columns=['File Name', 'Error Message', 'Time'])\n",
        "\n",
        "        mode = 'w' if not os.path.exists(error_log_file_path) else 'a'\n",
        "        header = False if os.path.exists(error_log_file_path) else True\n",
        "\n",
        "        # Append or write to the error log file\n",
        "        error_info.to_csv(error_log_file_path, mode=mode, header=header, index=False)\n",
        "\n",
        "    print(message)\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qTqI3Ag08k5b"
      },
      "source": [
        "### 10) Combine excel report files into one"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sVpLtG4Z7Jbe"
      },
      "source": [
        "Make a list of files to combine"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "sTyhwne7rr9D"
      },
      "outputs": [],
      "source": [
        "# Directory path where Excel reports are stored\n",
        "directory_path = os.path.join(drive_home, excel_reports_folder)\n",
        "\n",
        "# List files in the directory with '.xlsx' extension\n",
        "files = [file for file in os.listdir(directory_path) if file.endswith('.xlsx')]\n",
        "\n",
        "# Create a list of full file paths\n",
        "full_file_paths = [os.path.join(directory_path, file) for file in files]\n",
        "\n",
        "# Print the number of Excel files found in the folder\n",
        "print(f\"Number of Excel files in folder: {len(full_file_paths)}\")\n",
        "\n",
        "# folder to store outputs in google drive\n",
        "create_folder_if_not_exists(final_report_folder)\n",
        "\n",
        "# File path for the combined final report\n",
        "reports_combined_file_path = os.path.join(drive_home, final_report_folder, final_report_name)\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "C77flAQu7xWX"
      },
      "source": [
        "##### Run function to combine into a single report"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "FkS1ErA9AYj6"
      },
      "outputs": [],
      "source": [
        "append_excel_files(file_paths=full_file_paths,num_sheets=3,output_file_path=reports_combined_file_path)\n",
        "\n",
        "print (f\"\\n Complete! Output file for SDG 15.4.2 Sub-indicator A here: {reports_combined_file_path}\")"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "include_colab_link": true,
      "provenance": []
    },
    "kernelspec": {
      "display_name": "(test) test-sepal_mgci",
      "language": "python",
      "name": "test-sepal_mgci"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.10.12"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}