Data_Engineering_Essentials(news sentiment analysis).ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyMRtYIe2hkxa+TBKSPrPrVC",
      "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/SatadruMukherjee/Data-Preprocessing-Models/blob/main/Data_Engineering_Essentials(news%20sentiment%20analysis).ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# **[Fetch Data from an API](https://requests.readthedocs.io/en/latest/)**"
      ],
      "metadata": {
        "id": "iF49wD_Hywhd"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "EpY21caVdUwi"
      },
      "outputs": [],
      "source": [
        "!pip install requests"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import requests"
      ],
      "metadata": {
        "id": "riGdBeLmdYe7"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "payload = {\"dx_hcc\": \"dx\", \"dos_year\": \"v24_2022\", \"drf\": \"CNA\", \"search\": \"I50814\", \"start\": 0}"
      ],
      "metadata": {
        "id": "lhrmYjn_dZvu"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "response = requests.post(url = 'https://www.hccreference.com/api/search',headers={\"content-type\":\"application/json\"},json =payload)"
      ],
      "metadata": {
        "id": "HdchjHWOd2LA"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "response.text"
      ],
      "metadata": {
        "id": "cyGnBIaseCpx"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "response.status_code"
      ],
      "metadata": {
        "id": "VzYuedqKMwSx"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# **News Sentiment Analysis**"
      ],
      "metadata": {
        "id": "uYoESOrLzFdd"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "https://newsapi.org/\n",
        "\n",
        "https://signup.snowflake.com/"
      ],
      "metadata": {
        "id": "rJWvDUaJzkYQ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install snowflake-connector-python"
      ],
      "metadata": {
        "id": "TXacvsnkiLUS"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#data extraction & transformation\n",
        "import json\n",
        "import requests\n",
        "import datetime\n",
        "from datetime import date\n",
        "\n",
        "# Your News API key\n",
        "api_key = ''\n",
        "\n",
        "end_day = date.today()\n",
        "start_day = end_day - datetime.timedelta(days=3)\n",
        "\n",
        "def extract_news_api():\n",
        "    url_extractor = f\"https://newsapi.org/v2/everything?q='USA Election'&from={start_day}&to={end_day}&sortBy=popularity&apiKey={api_key}&language=en\"\n",
        "    print(\"The URL to extract the data is: \", url_extractor)\n",
        "    response = requests.get(url_extractor)\n",
        "    data = response.json()\n",
        "\n",
        "    if data['status'] == 'ok':\n",
        "        news_data = []\n",
        "        for article in data['articles']:\n",
        "            news_title = article['title']\n",
        "            timestamp = article['publishedAt']\n",
        "            url_source = article['url']\n",
        "            partial_content = article['content'] or \"\"\n",
        "\n",
        "            if len(partial_content) >= 200:\n",
        "                partial_content = partial_content[:199]\n",
        "\n",
        "            if '.' in partial_content:\n",
        "                trimmed_part = partial_content[:partial_content.rindex('.')]\n",
        "            else:\n",
        "                trimmed_part = partial_content\n",
        "\n",
        "            news_data.append((news_title, timestamp, url_source, trimmed_part))\n",
        "\n",
        "        return news_data"
      ],
      "metadata": {
        "id": "mFpkkqjDjFyW"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "news_data=extract_news_api()"
      ],
      "metadata": {
        "id": "X47tOykZjRBp"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "news_data"
      ],
      "metadata": {
        "id": "XkCw_Je8jXfI"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#loading data in Snowflake\n",
        "import snowflake.connector\n",
        "\n",
        "# Replace with your actual Snowflake credentials\n",
        "SNOWFLAKE_ACCOUNT = ''\n",
        "SNOWFLAKE_USER = ''\n",
        "SNOWFLAKE_PASSWORD = ''\n",
        "SNOWFLAKE_DATABASE = 'data_engineering'\n",
        "SNOWFLAKE_SCHEMA = 'PUBLIC'\n",
        "SNOWFLAKE_TABLE = 'NEWS_DATA'\n",
        "SNOWFLAKE_WAREHOUSE = 'COMPUTE_WH'\n",
        "\n",
        "def load_data_to_snowflake(data):\n",
        "    conn = snowflake.connector.connect(\n",
        "        user=SNOWFLAKE_USER,\n",
        "        password=SNOWFLAKE_PASSWORD,\n",
        "        account=SNOWFLAKE_ACCOUNT,\n",
        "        warehouse=SNOWFLAKE_WAREHOUSE,\n",
        "        database=SNOWFLAKE_DATABASE,\n",
        "        schema=SNOWFLAKE_SCHEMA\n",
        "    )\n",
        "\n",
        "    cursor = conn.cursor()\n",
        "\n",
        "    # Assuming the table has columns: TITLE, TIMESTAMP, URL, CONTENT\n",
        "    insert_query = f\"\"\"\n",
        "    INSERT INTO {SNOWFLAKE_TABLE} (TITLE, TIMESTAMP, URL, CONTENT)\n",
        "    VALUES (%s, %s, %s, %s)\n",
        "    \"\"\"\n",
        "\n",
        "    try:\n",
        "        cursor.executemany(insert_query, data)\n",
        "        conn.commit()\n",
        "        print(f\"{len(data)} rows inserted successfully.\")\n",
        "    except Exception as e:\n",
        "        print(\"Error inserting data:\", e)\n",
        "    finally:\n",
        "        cursor.close()\n",
        "        conn.close()\n",
        "\n",
        "if __name__ == \"__main__\":\n",
        "    news_data = extract_news_api()\n",
        "    if news_data:\n",
        "        load_data_to_snowflake(news_data)\n"
      ],
      "metadata": {
        "id": "Y4NZ75XAiLRm"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# **SQL Queries**"
      ],
      "metadata": {
        "id": "Pgg8vHom2EuT"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "DROP DATABASE IF EXISTS data_engineering;\n",
        "\n",
        "CREATE DATABASE data_engineering;\n",
        "\n",
        "use data_engineering;\n",
        "\n",
        "CREATE or replace TABLE NEWS_DATA (TITLE TEXT, TIMESTAMP TEXT, URL TEXT, CONTENT TEXT);\n",
        "\n",
        "select * from NEWS_DATA;\n",
        "\n",
        "SELECT\n",
        "    URL,\n",
        "    title,\n",
        "    SNOWFLAKE.CORTEX.COMPLETE(\n",
        "        'mistral-large2',\n",
        "        'Classify the sentiment of the following text as Positive, Negative, or Neutral: ' || title || '. Return only the sentiment.'\n",
        "    ) as content_sentiment,\n",
        "    CONTENT,\n",
        "    SNOWFLAKE.CORTEX.COMPLETE(\n",
        "        'mistral-large2',\n",
        "        'Classify the sentiment of the following text as Positive, Negative, or Neutral: ' || CONTENT || '. Return only the sentiment.'\n",
        "    ) as content_sentiment\n",
        "FROM NEWS_DATA;\n",
        "\n",
        "\n",
        "--summarization\n",
        "select SNOWFLAKE.CORTEX.COMPLETE(\n",
        "        'mistral-large2',\n",
        "        'Human: You are a summarisation assistant. Your task is to summarise product reviews given to you as a list. Within this list, there are individual product reviews in an array.\n",
        "    Create a JSON document with the following fields:\n",
        "    summary - A summary of these reviews in less than 200 words\n",
        "    overall_sentiment - The overall sentiment of the reviews\n",
        "    sentiment_confidence - How confident you are about the sentiment of the reviews\n",
        "    reviews_positive - The percent of positive reviews\n",
        "    reviews_neutral - The percent of neutral reviews\n",
        "    reviews_negative - The percent of negative reviews\n",
        "    action_items - A list of action items to resolve the customer complaints (don''t put something which is already good and there is no customer complaint)\n",
        "    Your output should be raw JSON - do not include any sentences or additional text outside of the JSON object.\n",
        "    Assistant:'||\n",
        "    'I recently had a very disappointing and stressful experience on my flight from Delhi (DEL) to Vancouver (YVR). The whole journey was plagued with issues and it left me with a bitter taste in my mouth.\n",
        "\n",
        "To begin with, the flight schedule kept changing multiple times before my departure date. This caused a lot of inconvenience as I had to keep rearranging my plans and make last-minute changes. The lack of proper communication about these changes only added to my frustration.\n",
        "\n",
        "On the day of my flight, I arrived at the airport to find out that my flight was delayed by four hours. This was not only frustrating but also exhausting as I had a connecting flight to catch in Vancouver. Despite the long delay, there was no proper explanation or updates provided by the airline staff.\n",
        "\n",
        "When I finally boarded the flight, I was shocked to see the condition of the aircraft. The seats were old and uncomfortable, and there was a musty smell in the cabin. Additionally, the in-flight entertainment system was not working, which made the long journey even more unbearable.\n",
        "\n",
        "The worst part of the flight was the food. It was tasteless and poorly prepared. To make matters worse, the flight attendants were rude and unaccommodating when I requested a vegetarian meal, which I had pre-booked. I had to settle for some fruits and crackers for my entire meal.\n",
        "\n",
        "To top it all off, my baggage did not arrive with me at YVR. It was frustrating to have to wait for hours at baggage claim only to find out that my luggage was still in Delhi. I had to file a report and wait for two days before my baggage was finally delivered to my hotel.\n",
        "\n",
        "Overall, my experience with this flight was highly disappointing. The constant delays, poor service, uncomfortable seats, and lost baggage left me stressed and exhausted. I expected much better from this airline, especially for the price I paid for my ticket. I would not recommend this airline to anyone and I will definitely think twice before booking another flight with them.'\n",
        "    ) ;\n",
        "\n",
        "--bit cleanup\n",
        "select parse_json(replace(replace(SNOWFLAKE.CORTEX.COMPLETE(\n",
        "'mistral-large2',\n",
        "'Human: You are a summarisation assistant. Your task is to summarise product reviews given to you as a list. Within this list, there are individual product reviews in an array.\n",
        "Create a JSON document with the following fields:\n",
        "summary - A summary of these reviews in less than 200 words\n",
        "overall_sentiment - The overall sentiment of the reviews\n",
        "sentiment_confidence - How confident you are about the sentiment of the reviews\n",
        "reviews_positive - The percent of positive reviews\n",
        "reviews_neutral - The percent of neutral reviews\n",
        "reviews_negative - The percent of negative reviews\n",
        "action_items - A list of action items to resolve the customer complaints (don''t put something which is already good and there is no customer complaint)\n",
        "Your output should be raw JSON - do not include any sentences or additional text outside of the JSON object.\n",
        "Assistant:'||\n",
        "'I recently had a very disappointing and stressful experience on my flight from Delhi (DEL) to Vancouver (YVR). The whole journey was plagued with issues and it left me with a bitter taste in my mouth.\n",
        "\n",
        "To begin with, the flight schedule kept changing multiple times before my departure date. This caused a lot of inconvenience as I had to keep rearranging my plans and make last-minute changes. The lack of proper communication about these changes only added to my frustration.\n",
        "\n",
        "On the day of my flight, I arrived at the airport to find out that my flight was delayed by four hours. This was not only frustrating but also exhausting as I had a connecting flight to catch in Vancouver. Despite the long delay, there was no proper explanation or updates provided by the airline staff.\n",
        "\n",
        "When I finally boarded the flight, I was shocked to see the condition of the aircraft. The seats were old and uncomfortable, and there was a musty smell in the cabin. Additionally, the in-flight entertainment system was not working, which made the long journey even more unbearable.\n",
        "\n",
        "The worst part of the flight was the food. It was tasteless and poorly prepared. To make matters worse, the flight attendants were rude and unaccommodating when I requested a vegetarian meal, which I had pre-booked. I had to settle for some fruits and crackers for my entire meal.\n",
        "\n",
        "To top it all off, my baggage did not arrive with me at YVR. It was frustrating to have to wait for hours at baggage claim only to find out that my luggage was still in Delhi. I had to file a report and wait for two days before my baggage was finally delivered to my hotel.\n",
        "\n",
        "Overall, my experience with this flight was highly disappointing. The constant delays, poor service, uncomfortable seats, and lost baggage left me stressed and exhausted. I expected much better from this airline, especially for the price I paid for my ticket. I would not recommend this airline to anyone and I will definitely think twice before booking another flight with them.'\n",
        "),'```',''),'json','')) ;\n",
        "\n",
        "--entity extraction example\n",
        "select parse_json(replace(replace(SNOWFLAKE.CORTEX.COMPLETE(\n",
        "'mistral-large2','You are a helpful assistant. Please extract the following details from the extracted_text & format the output as JSON using the keys\n",
        "\n",
        "<details>\n",
        "doctor_name: The doctor or provider''s full name\n",
        "provider_id: The doctor or provider''s ID\n",
        "patient_name: The patient''s full name\n",
        "patient_id: The patient''s ID\n",
        "patient_gender: The patient''s gender\n",
        "patient_age: The patient''s age\n",
        "admitted_date: Date the patient was admitted to the hospital\n",
        "discharge_date: Date the patient was discharged from the hospital\n",
        "discharged_to: where the patient was released or discharged to\n",
        "drug_allergies: The patient''s known drug allergies (NKDA)\n",
        "patient_medical_history: The patient''s medical history (PMHx)\n",
        "family_medical_history: The patient''s family medical history (FHx)\n",
        "social_habits: The patient''s social habits (Social HX)\n",
        "</details>\n",
        "\n",
        "<keys>\n",
        "doctor_name\n",
        "provider_id\n",
        "patient_name\n",
        "patient_id\n",
        "patient_gender\n",
        "patient_age\n",
        "admitted_date\n",
        "discharge_date\n",
        "discharged_to\n",
        "drug_allergies\n",
        "patient_medical_history\n",
        "family_medical_history\n",
        "social_habits\n",
        "</keys>\n",
        "\n",
        "<extracted_text>'||\n",
        "'Not a Memorial Hospital\n",
        "Of Collier\n",
        "Reg: PN/S/11011, Non-Profit\n",
        "Contact: (999)-(888)-(1234)\n",
        "\n",
        "Physician Hospital Discharge Summary\n",
        "\n",
        "Provider: Mateo Jackson, Phd\n",
        "\n",
        "Patient: John Doe\n",
        "\n",
        "Provider''s Pt ID: 00988277891\n",
        "\n",
        "Patient Gender: Male\n",
        "\n",
        "Attachment Control Number: XA/7B/00338763\n",
        "\n",
        "Visit (Encounter)\n",
        "\n",
        "Admitted: 07-Sep-2020\n",
        "\n",
        "Discharged: 08-Sep-2020\n",
        "\n",
        "Discharged to: Home with support services\n",
        "\n",
        "Assessment\n",
        "\n",
        "Reported Symptoms / History\n",
        "of present illness:\n",
        "\n",
        "35 yo M c/o stomach problems since 2 montsh ago. Patient\n",
        "reports epigastric abdominal pain non-radiating. Pain is\n",
        "described as gnawing and burning, intermitent lasting 1-2\n",
        "hours, and gotten progressively worse. Antacids used to\n",
        "alleviate pain but not anymore; nothing exhacerbates pain.\n",
        "Pain unrelated to daytime or to meals. Patient denies\n",
        "constipation or diarrhea. Patient denies blood in stool but\n",
        "have noticed them darker. Patient also reports nausea.\n",
        "Denies recent illness or fever. He also reports fatigue\n",
        "since 2 weeks ago and bloating after eating.\n",
        "Patient ID: NARH-36640\n",
        "ROS: Negative except for above findings\n",
        "Meds: Motrin once/week. Tums previously.\n",
        "PMHX: Back pain and muscle spasms. No HX of surgery. NKDA.\n",
        "FHX: Uncle has a bleeding ulcer.\n",
        "\n",
        "Discharge\n",
        "\n",
        "Discharge Studies Summary:\n",
        "Some activity restrictions suggested, full course of\n",
        "antibiotics, check back with physican in case of relapse,\n",
        "strict diet\n",
        "\n",
        "Social Hx: Smokes since 15 yo, 1/2-1 PPD. No recent EtOH\n",
        "use. Denies illicit drug use. works on high elevation\n",
        "construction. Fast food diet. Exercises 3-4 times/week but\n",
        "stopped 2 weeks ago.\n",
        "\n",
        "VS/3S/Q990-7550/1090001004290'||'</extracted_text>'),'```',''),'json',''));\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "--nlp\n",
        "select SNOWFLAKE.CORTEX.SENTIMENT('I am just so frustrated I don''t know how the story ends!');\n",
        "\n",
        "--nlu\n",
        "select snowflake.cortex.complete('mistral-large','Would the user like next game or next book in the series based on the review, strictly return yes or no without any additional text: ' ||'I am just so frustrated I don’t know how the story ends!');\n"
      ],
      "metadata": {
        "id": "cgjhe3i11yTf"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}