ND_v0613_GitHub_UPDATED UNDP GPT 3 5 Prompt Testing Biodiversity Policy Similarity Analysis 3Jun24.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 2,
  "metadata": {
    "language_info": {
      "name": "python"
    },
    "kernelspec": {
      "name": "synapse_pyspark",
      "display_name": "python"
    },
    "description": null,
    "save_output": true,
    "synapse_widget": {
      "version": "0.1",
      "state": {}
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "outputs": [],
      "metadata": {},
      "source": [
        "!pip install openai==0.28.1"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "outputs": [],
      "metadata": {
        "jupyter": {
          "source_hidden": false,
          "outputs_hidden": false
        },
        "nteract": {
          "transient": {
            "deleting": false
          }
        }
      },
      "source": [
        "get_ipython().system('pip install azure-storage-blob azure-identity')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "outputs": [],
      "metadata": {
        "jupyter": {
          "source_hidden": false,
          "outputs_hidden": false
        },
        "nteract": {
          "transient": {
            "deleting": false
          }
        }
      },
      "source": [
        "#CNN concatenate example\n",
        "import os\n",
        "import pandas as pd\n",
        "from pickle import load\n",
        "import numpy as np\n",
        "import matplotlib.pyplot as plt\n",
        "import openai\n",
        "import requests\n",
        "#from openai import AzureOpenAI"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "outputs": [],
      "metadata": {
        "jupyter": {
          "source_hidden": false,
          "outputs_hidden": false
        },
        "nteract": {
          "transient": {
            "deleting": false
          }
        }
      },
      "source": [
        "import uuid\n",
        "from azure.identity import DefaultAzureCredential\n",
        "from azure.storage.blob import BlobServiceClient, ContentSettings"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "outputs": [],
      "metadata": {
        "jupyter": {
          "source_hidden": false,
          "outputs_hidden": false
        },
        "nteract": {
          "transient": {
            "deleting": false
          }
        }
      },
      "source": [
        "import csv\n",
        "import time"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "outputs": [],
      "metadata": {
        "jupyter": {
          "source_hidden": false,
          "outputs_hidden": false
        },
        "nteract": {
          "transient": {
            "deleting": false
          }
        }
      },
      "source": [
        "# Create the BlobServiceClient object \n",
        "conn_str = <Connection string of Azure BLOB Storage Account>\n",
        "blob_service_client = BlobServiceClient.from_connection_string(conn_str)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "outputs": [],
      "metadata": {
        "jupyter": {
          "source_hidden": false,
          "outputs_hidden": false
        },
        "nteract": {
          "transient": {
            "deleting": false
          }
        }
      },
      "source": [
        "# Create container client \n",
        "container = <Name of the Azure BLOB Storage Container>\n",
        "container_client = blob_service_client.get_container_client(container=container)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "outputs": [],
      "metadata": {
        "jupyter": {
          "source_hidden": false,
          "outputs_hidden": false
        },
        "nteract": {
          "transient": {
            "deleting": false
          }
        }
      },
      "source": [
        "#spreadsheet with all NBTs and GBF targets to run for all countries\n",
        "global_targets_df = pd.read_excel(<abfss path to the Excel file>, sheet_name=<Name of the sheet in Excel workbook>)\n",
        "national_targets_df = pd.read_excel(<abfss path to the Excel file>, sheet_name=<Name of the sheet in Excel workbook>)\n",
        ""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "outputs": [],
      "metadata": {
        "jupyter": {
          "source_hidden": false,
          "outputs_hidden": false
        },
        "nteract": {
          "transient": {
            "deleting": false
          }
        }
      },
      "source": [
        "# Azure OpenAI setup for openai==0.28\n",
        "openai.api_type = \"azure\";\n",
        "openai.api_version = \"<AZURE_OPENAI_VERSION>\",\n",
        "openai.api_base = os.getenv(\"AZURE_OPENAI_ENDPOINT\"),\n",
        "openai.api_key = os.getenv(\"AZURE_OPENAI_API_KEY\"),\n",
        "deployment_id = \"<Name of the Azure OpenAI Model Deployment ID>\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "outputs": [],
      "metadata": {
        "jupyter": {
          "source_hidden": false,
          "outputs_hidden": false
        },
        "nteract": {
          "transient": {
            "deleting": false
          }
        }
      },
      "source": [
        "#refined prompt\n",
        "prompt = \"\"\"Assess the similarity between each National Biodiversity Target (NBT) and the corresponding Global Biodiversity Framework (GBF) Goal or Target by providing a numerical score from 0.000 to 1.000. A score of 1.000 signals a high degree of similarity with nearly identical language and objectives, while 0.000 indicates no similarity. Some similarity scores will be above 0.000 and below 1.000: 0.250 would be lower similarity, 0.500 would be medium similarity and 0.750 would be a higher level of similarity.\n",
        "Reminder: Some NBTs will have a score of 0.000 and have no similarity to a GBF goal or target. In contrast, a perfect score of 1.000 is highly unlikely. Reserve a score of 1.000 for instances where the NBT and GBF Target are identical in both wording and intent. Such exact matches are highly unlikely due to the specific context of each NBT. \n",
        "\n",
        "Guidelines for scoring:\n",
        "- Focus on identifying explicit textual similarities between the NBT and GBF Target, considering shared goals, strategies, and approaches. \n",
        "- **Guided Nuanced Evaluation**: Also recognize implicit similarities where the NBT and GBF Target share overarching goals or principles that are clearly articulated, even if not directly mirrored. Ensure that these implicit similarities are grounded in clear statements within the text.\n",
        "- For a score of 0.000, simply state: \"#0.000 #No identified similarity.\"\n",
        "- For scores above 0.000, provide the score (such as 0.250, 0.500, or 0.750) followed by a summary including:\n",
        "  1. #Similarities: Highlight explicitly stated objectives and strategies shared between the NBT and GBF Target and acknowledge well-supported implicit similarities that reflect shared overarching goals.\n",
        "  2. #Recommendations: Suggest specific ways to further align the NBT with the GBF Target, focusing on areas of strategic and linguistic improvement.\n",
        "\n",
        "Example of Expected Output: #0.500 #1. Similarities: The NBT and GBF Goal C both emphasize the equitable sharing of benefits from genetic resources utilization. 2. Recommendations: To increase alignment with GBF Goal C, the NBT could incorporate explicit mentions of protecting traditional knowledge, supporting biodiversity conservation and sustainable use, and outlining the sharing mechanisms for digital sequence information on genetic resources.\"\"\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "outputs": [],
      "metadata": {
        "jupyter": {
          "source_hidden": false,
          "outputs_hidden": false
        },
        "nteract": {
          "transient": {
            "deleting": false
          }
        }
      },
      "source": [
        "#refined system prompt\n",
        "#background for the system - what the systems identity is\n",
        "system_prompt = \"\"\"Your task is to assess the alignment between National Biodiversity Targets (NBTs) and Global Biodiversity Framework (GBF) Goals or Targets, assigning similarity scores from 0.000 (indicating no similarity) to 1.000 (indicating the highest level of similarity).\n",
        "Please note, a perfect score of 1.000 is highly unlikely. Reserve a score of 1.000 for instances where the NBT and GBF Target are identical in both wording and intent. Such exact matches are highly unlikely due to the specific context of each NBT\n",
        "\n",
        "Scoring Guidelines:\n",
        "- Prioritize explicit textual similarities in language and objectives between the NBT and GBF Target. Shared goals and strategies should be clearly stated in both documents.\n",
        "- **Guided Nuanced Evaluation**: Also recognize implicit similarities where the NBT and GBF Target share overarching goals or principles that are clearly articulated, even if not directly mirrored. Ensure that these implicit similarities are grounded in clear statements within the text.\n",
        "- Provide a brief notation for scores at 0.000: '#0.000 #No identified similarity.'\n",
        "- For scores above 0.000, include the similarity score as well as a summary with two parts:\n",
        "  1. #Similarities: Emphasize direct parallels and carefully supported implicit similarities that demonstrate a connection in overarching goals, ensuring these observations are traceable to the text.\n",
        "  2. #Recommendations: Develop targeted recommendations to increase the NBT's alignment with GBF Targets, emphasizing concrete adjustments in strategy and language.\"\"\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "outputs": [],
      "metadata": {
        "jupyter": {
          "source_hidden": false,
          "outputs_hidden": false
        },
        "nteract": {
          "transient": {
            "deleting": false
          }
        }
      },
      "source": [
        "#run for a subset of countries\n",
        "list_of_countries = ['COUNTRY']\n",
        "country_subset = national_targets_df[national_targets_df['Country'].isin(list_of_countries)]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "outputs": [],
      "metadata": {
        "jupyter": {
          "source_hidden": false,
          "outputs_hidden": false
        },
        "nteract": {
          "transient": {
            "deleting": false
          }
        }
      },
      "source": [
        "# from transformers.models.bridgetower.modeling_bridgetower import BridgeTowerForMaskedLM\n",
        "\n",
        "# The maximum number of retries\n",
        "MAX_RETRIES = 3\n",
        "\n",
        "def call_api(row,national_row):\n",
        "\n",
        "    #test NBT\n",
        "    #print('Country: '+str(national_row['Country'].values[0]))\n",
        "    #  print('Country NBT text: '+str(national_row['NBT Text - English '].values[0]))\n",
        "\n",
        "    #test GBF\n",
        "    #print('GBF text: '+'GBF Target Text: '+str(row['GBF Target Text']))\n",
        "\n",
        "    messages = [\n",
        "    # {\"role\": \"user\", \"content\": 'Here is the national biodiversity target for '+str(national_row['Country'].values[0])+': \\n' + str(national_row['NBT Text - English '].values[0])},\n",
        "    {\"role\": \"system\", \"content\":  system_prompt},\n",
        "    {\"role\": \"user\", \"content\": 'Here is the national biodiversity target for '+str(national_row['Country'].values[0])+': \\n' + str(national_row['NBT Text - English'].values[0]) + '\\n'\n",
        "                'Here are the global biodiversity targets: \\n' +\n",
        "                'GBF Target Text: '+str(row['GBF Target Text']) +\n",
        "                 prompt},\n",
        "        #  {\"role\": \"user\", \"content\":  prompt},\n",
        "      # {\"role\": \"user\", \"content\": row[\"all_text\"].values[0]},\n",
        "    # {\"role\": \"assistant\", \"content\": \"Text: \"},\n",
        "    ]\n",
        "    print(messages)\n",
        "\n",
        "    try:\n",
        "      #Make your OpenAI API request here\n",
        "      # Call the API - openai==0.28\n",
        "\n",
        "      response = openai.ChatCompletion.create(\n",
        "      \n",
        "        messages=messages,\n",
        "        deployment_id=deployment_id, \n",
        "        #message=messages,\n",
        "        max_tokens=500,\n",
        "        temperature=0.0,\n",
        "        top_p=0.3,\n",
        "        frequency_penalty=0.0,\n",
        "        presence_penalty=0.0,\n",
        "        stop=None\n",
        "      )\n",
        "\n",
        "      \n",
        "      row['generated_sample'] = response.choices[0].message.content #response['choices'][0].message.content\n",
        "      print(\"TESTING GPT\")\n",
        "      print(row['generated_sample'])\n",
        "      errorPresent = False\n",
        "    except openai.error.APIError as e:\n",
        "      #Handle API error here, e.g. retry or log\n",
        "      print(f\"OpenAI API returned an API Error: {e}\")\n",
        "      pass\n",
        "      row['generated_sample'] = 'NAN'\n",
        "      print(row['generated_sample'])\n",
        "      errorPresent = False\n",
        "    except openai.error.APIConnectionError as e:\n",
        "      #Handle connection error here\n",
        "      print(f\"Failed to connect to OpenAI API: {e}\")\n",
        "      pass\n",
        "      row['generated_sample'] = 'NAN'\n",
        "      print(row['generated_sample'])\n",
        "      errorPresent = False\n",
        "    except openai.error.RateLimitError as e:\n",
        "      #Handle rate limit error (we recommend using exponential backoff)\n",
        "      print(f\"OpenAI API request exceeded rate limit: {e}\")\n",
        "      pass\n",
        "      row['generated_sample'] = 'NAN'\n",
        "      print(row['generated_sample'])\n",
        "      errorPresent = False\n",
        "    except openai.error.ServiceUnavailableError as e:\n",
        "      #ServiceUnavailableError (we recommend using exponential backoff)\n",
        "      print(f\"OpenAI API request exceeded rate limit: {e}\")\n",
        "      pass\n",
        "      row['generated_sample'] = 'NAN'\n",
        "      print(row['generated_sample'])\n",
        "      errorPresent = True\n",
        "    except Exception as e:\n",
        "      #ServiceUnavailableError (we recommend using exponential backoff)\n",
        "      print(f\"Catch all error: {e}\")\n",
        "      pass\n",
        "      row['generated_sample'] = 'NAN'\n",
        "      print(row['generated_sample'])\n",
        "      errorPresent = True\n",
        "\n",
        "    # Save the result to a CSV file immediately after it's called\n",
        "\n",
        "    return row,errorPresent\n",
        "\n",
        "\n",
        "# Define the loop count and delay between calls\n",
        "# LOOP_COUNT = 1\n",
        "DELAY_SECONDS = 1\n",
        "\n",
        "#ii represents the global targets \n",
        "#jj represents the national targets \n",
        "\n",
        "local_file_path = '<Path to the directory in your local machine>'\n",
        "\n",
        "#!!!create an empty CSV file in downloads with below filename to save results to Azure data lake\n",
        "file_name = 'UNDP_LLM_Assessment_GPT3.5_0613_UNDP_enterprise_Global_Analysis_v18_<COUNTRY_DATE>.csv'\n",
        "\n",
        "# #main block of code to run for loops across all global and national targets\n",
        "with open(file=os.path.join(local_file_path+file_name), mode=\"w\") as csvfile:\n",
        "\n",
        "  for cc in range(len(list_of_countries)):\n",
        "    print('cc: ' + str(cc))\n",
        "    print('Country: ' + str(list_of_countries[cc]))\n",
        "    country_of_interest = country_subset[country_subset['Country'] == list_of_countries[cc]]\n",
        "    for ii in range(len(global_targets_df)):\n",
        "      print('ii: ' + str(ii))\n",
        "      for jj in range(len(country_of_interest)):\n",
        "        print('jj: ' + str(jj))\n",
        "        # Call the API\n",
        "        temp = pd.DataFrame(country_of_interest.iloc[jj,:]).T\n",
        "        print('temp')\n",
        "        print(temp.shape)\n",
        "        result,errorPresent = call_api(global_targets_df.iloc[ii,:],temp)\n",
        "        print('result')\n",
        "        # print(type(result))\n",
        "        result = pd.DataFrame(result).T\n",
        "        print('result shape: ' + str(result.shape))\n",
        "        result.reset_index(inplace=True)\n",
        "        temp.reset_index(inplace=True)\n",
        "        concatenated_df = pd.concat([temp, result], axis=1)\n",
        "        print('concatenated_df shape: ' + str(concatenated_df.shape))\n",
        "        if ii==0 and jj==0:\n",
        "          print('first one!')\n",
        "          concatenated_df.to_csv(csvfile, index=False, header=True)\n",
        "        else:\n",
        "          print('the rest')\n",
        "          concatenated_df.to_csv(csvfile, index=False, header=False)\n",
        "        # Wait for a few seconds before making the next API call\n",
        "        if errorPresent==True:\n",
        "          time.sleep(2)\n",
        "        else:\n",
        "          time.sleep(DELAY_SECONDS)\n",
        "\n",
        "# Create Blob client\n",
        "# Folder_name\\\\File_name.ext in Azure Data Lake\n",
        "#update filename to match empty file created in downloads folder\n",
        "blob = \"Global_GBF_LLM_Results\\\\UNDP_LLM_Assessment_GPT3.5_0613_UNDP_enterprise_Global_Analysis_v18_<COUNTRY_DATE>.csv\"\n",
        "blob_client = blob_service_client.get_blob_client(container=container, blob=blob)\n",
        "\n",
        "# Upload file to Azure Data Lake Storage Account\n",
        "if(os.path.isfile(os.path.join(local_file_path+file_name))):\n",
        "    try:\n",
        "        with open(file=os.path.join(local_file_path+file_name), mode=\"rb\") as data:\n",
        "            blob_client = container_client.upload_blob(name=blob, data=data, overwrite=True)\n",
        "    except Exception as e:\n",
        "        print('error ',e)\n",
        "else:\n",
        "    print(\"Local file does not exist\")"
      ]
    }
  ]
}