final_demo.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2s\n",
    "\n",
    "import mysql.connector\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import numpy as np\n",
    "import datetime\n",
    "import plotly.graph_objects as go\n",
    "from config import config\n",
    "\n",
    "connection = mysql.connector.connect(\n",
    "    host=config.get('HOST'),\n",
    "    user=config.get('USERNAME'),\n",
    "    password=config.get('PASSWORD'),\n",
    "    database=config.get('DATABASE')\n",
    ")\n",
    "\n",
    "cursor = connection.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 0s\n",
    "\n",
    "# Function to validate date format\n",
    "def validate_date(date_str):\n",
    "    try:\n",
    "        datetime.datetime.strptime(date_str, '%Y-%m-%d %H:%M:%S')\n",
    "        return True\n",
    "    except ValueError:\n",
    "        return False\n",
    "\n",
    "while True:\n",
    "    start_date = input(\"Please enter the start date (YYYY-MM-DD HH:MM:SS): \")\n",
    "    if validate_date(start_date):\n",
    "        break\n",
    "    else:\n",
    "        print(\"Invalid format. Please enter the date in the format YYYY-MM-DD HH:MM:SS\")\n",
    "\n",
    "while True:\n",
    "    end_date = input(\"Please enter the end date (YYYY-MM-DD HH:MM:SS): \")\n",
    "    if validate_date(end_date):\n",
    "        break\n",
    "    else:\n",
    "        print(\"Invalid format. Please enter the date in the format YYYY-MM-DD HH:MM:SS\")\n",
    "\n",
    "print(f\"Start date: {start_date}\")\n",
    "print(f\"End date: {end_date}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 0s\n",
    "\n",
    "def get_count(query):\n",
    "    cursor = connection.cursor()\n",
    "    try:\n",
    "        for result in cursor.execute(query, multi=True):\n",
    "            if result.with_rows:\n",
    "                first_result = result.fetchone()\n",
    "                cursor.fetchall()  # Ensure all remaining rows are fetched\n",
    "        return first_result[0] if first_result else 0\n",
    "    finally:\n",
    "        cursor.close()\n",
    "\n",
    "airlines = ['British Aiways', 'KLM', 'Lufthansa', 'Air France']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Queries for number of conversations where an airline was mentioned per airline- 0s\n",
    "conv_mentioned_ba = f\"\"\"\n",
    "SELECT COUNT(DISTINCT conversations.conversation_id)\n",
    "FROM conversations\n",
    "JOIN hasher ON hasher.conversation_id = conversations.conversation_id\n",
    "JOIN tweets ON hasher.id = tweets.id\n",
    "WHERE (tweets.user_mentions LIKE '%18332190%' OR tweets.mentioned_airlines LIKE '%British_Airways%')\n",
    "    AND conversations.start >= UNIX_TIMESTAMP('{start_date}') * 1000 \n",
    "    AND conversations.start <= UNIX_TIMESTAMP('{end_date}') * 1000; \n",
    "\"\"\"\n",
    "\n",
    "conv_mentioned_klm = f\"\"\" \n",
    "SELECT COUNT(DISTINCT conversations.conversation_id)\n",
    "FROM conversations\n",
    "JOIN hasher ON hasher.conversation_id = conversations.conversation_id\n",
    "JOIN tweets ON hasher.id = tweets.id\n",
    "WHERE (tweets.user_mentions LIKE '%56377143%' OR tweets.mentioned_airlines LIKE '%KLM%')\n",
    "    AND conversations.start >= UNIX_TIMESTAMP('{start_date}') * 1000 \n",
    "    AND conversations.start <= UNIX_TIMESTAMP('{end_date}') * 1000; \n",
    "\"\"\"\n",
    "\n",
    "conv_mentioned_lufthansa = f\"\"\" \n",
    "SELECT COUNT(DISTINCT conversations.conversation_id)\n",
    "FROM conversations\n",
    "JOIN hasher ON hasher.conversation_id = conversations.conversation_id\n",
    "JOIN tweets ON hasher.id = tweets.id\n",
    "WHERE (tweets.user_mentions LIKE '%124476322%' OR tweets.mentioned_airlines LIKE '%Lufthansa%')\n",
    "    AND conversations.start >= UNIX_TIMESTAMP('{start_date}') * 1000 \n",
    "    AND conversations.start <= UNIX_TIMESTAMP('{end_date}') * 1000; \n",
    "\"\"\"\n",
    "\n",
    "conv_mentioned_af = f\"\"\" \n",
    "SELECT COUNT(DISTINCT conversations.conversation_id)\n",
    "FROM conversations\n",
    "JOIN hasher ON hasher.conversation_id = conversations.conversation_id\n",
    "JOIN tweets ON hasher.id = tweets.id\n",
    "WHERE (tweets.user_mentions LIKE '%106062176%' OR tweets.mentioned_airlines LIKE '%AirFrance%')\n",
    "    AND conversations.start >= UNIX_TIMESTAMP('{start_date}') * 1000 \n",
    "    AND conversations.start <= UNIX_TIMESTAMP('{end_date}') * 1000;\n",
    "\"\"\"\n",
    "\n",
    "# Queries for number of conversations where an airline was mentioned and the airline replied\n",
    "\n",
    "conv_replies_mentioned_ba = f\"\"\"\n",
    "SELECT COUNT(DISTINCT conversations.conversation_id)\n",
    "FROM conversations\n",
    "JOIN hasher ON hasher.conversation_id = conversations.conversation_id\n",
    "JOIN tweets ON hasher.id = tweets.id\n",
    "WHERE (tweets.user_mentions LIKE '%18332190%' OR tweets.mentioned_airlines LIKE '%British_Airways%')\n",
    "    AND conversations.airline LIKE '%British_Airways%'\n",
    "    AND conversations.start >= UNIX_TIMESTAMP('{start_date}') * 1000 \n",
    "    AND conversations.start <= UNIX_TIMESTAMP('{end_date}') * 1000;\n",
    "\"\"\"\n",
    "\n",
    "conv_replies_mentioned_klm = f\"\"\" \n",
    "SELECT COUNT(DISTINCT conversations.conversation_id)\n",
    "FROM conversations\n",
    "JOIN hasher ON hasher.conversation_id = conversations.conversation_id\n",
    "JOIN tweets ON hasher.id = tweets.id\n",
    "WHERE (tweets.user_mentions LIKE '%56377143%' OR tweets.mentioned_airlines LIKE '%KLM%') \n",
    "    AND conversations.airline LIKE '%KLM%'\n",
    "    AND conversations.start >= UNIX_TIMESTAMP('{start_date}') * 1000 \n",
    "    AND conversations.start <= UNIX_TIMESTAMP('{end_date}') * 1000;\n",
    "\"\"\"\n",
    "\n",
    "conv_replies_mentioned_lufthansa = f\"\"\" \n",
    "SELECT COUNT(DISTINCT conversations.conversation_id)\n",
    "FROM conversations\n",
    "JOIN hasher ON hasher.conversation_id = conversations.conversation_id\n",
    "JOIN tweets ON hasher.id = tweets.id\n",
    "WHERE (tweets.user_mentions LIKE '%124476322%' OR tweets.mentioned_airlines LIKE '%Lufthansa%') \n",
    "    AND conversations.airline LIKE '%Lufthansa%'\n",
    "    AND conversations.start >= UNIX_TIMESTAMP('{start_date}') * 1000 \n",
    "    AND conversations.start <= UNIX_TIMESTAMP('{end_date}') * 1000;\n",
    "\"\"\"\n",
    "\n",
    "conv_replies_mentioned_af = f\"\"\" \n",
    "SELECT COUNT(DISTINCT conversations.conversation_id)\n",
    "FROM conversations\n",
    "JOIN hasher ON hasher.conversation_id = conversations.conversation_id\n",
    "JOIN tweets ON hasher.id = tweets.id\n",
    "WHERE (tweets.user_mentions LIKE '%106062176%' OR tweets.mentioned_airlines LIKE '%AirFrance%')\n",
    "    AND conversations.airline LIKE '%AirFrance%'\n",
    "    AND conversations.start >= UNIX_TIMESTAMP('{start_date}') * 1000 \n",
    "    AND conversations.start <= UNIX_TIMESTAMP('{end_date}') * 1000;\n",
    "\"\"\"\n",
    "\n",
    "# Queries for number of conversations where an airline was mentioned and the airline did not reply\n",
    "\n",
    "conv_non_replies_where_mentioned_ba = f\"\"\"\n",
    "SELECT COUNT(DISTINCT conversations.conversation_id)\n",
    "FROM conversations\n",
    "JOIN hasher ON hasher.conversation_id = conversations.conversation_id\n",
    "JOIN tweets ON hasher.id = tweets.id\n",
    "WHERE (tweets.user_mentions LIKE '%18332190%' OR tweets.mentioned_airlines LIKE '%British_Airways%')\n",
    "  AND conversations.airline NOT LIKE '%British_Airways%'\n",
    "  AND conversations.start >= UNIX_TIMESTAMP('{start_date}') * 1000 \n",
    "  AND conversations.start <= UNIX_TIMESTAMP('{end_date}') * 1000;\n",
    "\"\"\"\n",
    "\n",
    "conv_non_replies_where_mentioned_klm = f\"\"\"\n",
    "SELECT COUNT(DISTINCT conversations.conversation_id)\n",
    "FROM conversations\n",
    "JOIN hasher ON hasher.conversation_id = conversations.conversation_id\n",
    "JOIN tweets ON hasher.id = tweets.id\n",
    "WHERE (tweets.user_mentions LIKE '%56377143%' OR tweets.mentioned_airlines LIKE '%KLM%')\n",
    "  AND conversations.airline NOT LIKE '%KLM%'\n",
    "  AND conversations.start >= UNIX_TIMESTAMP('{start_date}') * 1000 \n",
    "  AND conversations.start <= UNIX_TIMESTAMP('{end_date}') * 1000; \n",
    "\"\"\"\n",
    "\n",
    "conv_non_replies_where_mentioned_lufthansa = f\"\"\"\n",
    "SELECT COUNT(DISTINCT conversations.conversation_id)\n",
    "FROM conversations\n",
    "JOIN hasher ON hasher.conversation_id = conversations.conversation_id\n",
    "JOIN tweets ON hasher.id = tweets.id\n",
    "WHERE (tweets.user_mentions LIKE '%124476322%' OR tweets.mentioned_airlines LIKE '%Lufthansa%')\n",
    "  AND conversations.airline NOT LIKE '%Lufthansa%'\n",
    "  AND conversations.start >= UNIX_TIMESTAMP('{start_date}') * 1000 \n",
    "  AND conversations.start <= UNIX_TIMESTAMP('{end_date}') * 1000; \n",
    "\"\"\"\n",
    "\n",
    "conv_non_replies_where_mentioned_af = f\"\"\"\n",
    "SELECT COUNT(DISTINCT conversations.conversation_id)\n",
    "FROM conversations\n",
    "JOIN hasher ON hasher.conversation_id = conversations.conversation_id\n",
    "JOIN tweets ON hasher.id = tweets.id\n",
    "WHERE (tweets.user_mentions LIKE '%106062176%' OR tweets.mentioned_airlines LIKE '%AirFrance%')\n",
    "  AND conversations.airline NOT LIKE '%AirFrance%'\n",
    "  AND conversations.start >= UNIX_TIMESTAMP('{start_date}') * 1000 \n",
    "  AND conversations.start <= UNIX_TIMESTAMP('{end_date}') * 1000;\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2m 30s\n",
    "\n",
    "mentioned_ba_count = get_count(conv_mentioned_ba)\n",
    "replies_mentioned_ba_count = get_count(conv_replies_mentioned_ba)\n",
    "non_replies_where_mentioned_ba_count = get_count(conv_non_replies_where_mentioned_ba)\n",
    "\n",
    "mentioned_klm_count = get_count(conv_mentioned_klm)\n",
    "replies_mentioned_klm_count = get_count(conv_replies_mentioned_klm)\n",
    "non_replies_where_mentioned_klm_count = get_count(conv_non_replies_where_mentioned_klm)\n",
    "\n",
    "mentioned_lufthansa_count = get_count(conv_mentioned_lufthansa)\n",
    "replies_mentioned_lufthansa_count = get_count(conv_replies_mentioned_lufthansa)\n",
    "non_replies_where_mentioned_lufthansa_count = get_count(conv_non_replies_where_mentioned_lufthansa)\n",
    "\n",
    "mentioned_af_count = get_count(conv_mentioned_af)\n",
    "replies_mentioned_af_count = get_count(conv_replies_mentioned_af)\n",
    "non_replies_where_mentioned_af_count = get_count(conv_non_replies_where_mentioned_af)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 0s\n",
    "\n",
    "replied_percentage_ba = replies_mentioned_ba_count / mentioned_ba_count * 100\n",
    "non_replied_percentage_ba = non_replies_where_mentioned_ba_count / mentioned_ba_count * 100\n",
    "\n",
    "replied_percentage_klm = replies_mentioned_klm_count / mentioned_klm_count * 100\n",
    "non_replied_percentage_klm = non_replies_where_mentioned_klm_count / mentioned_klm_count * 100\n",
    "\n",
    "replied_percentage_lufthansa = replies_mentioned_lufthansa_count / mentioned_lufthansa_count * 100\n",
    "non_replied_percentage_lufthansa = non_replies_where_mentioned_lufthansa_count / mentioned_lufthansa_count * 100\n",
    "\n",
    "replied_percentage_af = replies_mentioned_af_count / mentioned_af_count * 100\n",
    "non_replied_percentage_af = non_replies_where_mentioned_af_count / mentioned_af_count * 100\n",
    "\n",
    "mentioned_convs = [mentioned_ba_count, mentioned_klm_count, mentioned_lufthansa_count, mentioned_af_count]\n",
    "replied_percentages = [replied_percentage_ba, replied_percentage_klm, replied_percentage_lufthansa, replied_percentage_af]\n",
    "non_replied_percentages = [non_replied_percentage_ba, non_replied_percentage_klm, non_replied_percentage_lufthansa, non_replied_percentage_af]\n",
    "\n",
    "replied_counts = [replies_mentioned_ba_count, replies_mentioned_klm_count, replies_mentioned_lufthansa_count, replies_mentioned_af_count]\n",
    "non_replied_counts = [non_replies_where_mentioned_ba_count, non_replies_where_mentioned_klm_count, non_replies_where_mentioned_lufthansa_count, non_replies_where_mentioned_af_count]\n",
    "\n",
    "bar_width = 0.5\n",
    "ind = range(len(airlines))\n",
    "\n",
    "fig, ax = plt.subplots(figsize = (9, 6))\n",
    "\n",
    "p1 = ax.bar(ind, replied_counts, bar_width, label='Replied')\n",
    "p2 = ax.bar(ind, non_replied_counts, bar_width, bottom=replied_counts, label='Non Replied')\n",
    "\n",
    "ax.set_ylabel('Number of Conversations, where mentioned', fontsize= 11)\n",
    "ax.set_title('Number of Mentioned Conversations and Reply Percentages per Airline', fontsize= 12, weight= 'bold')\n",
    "ax.set_xticks(ind)\n",
    "ax.set_xticklabels(airlines)\n",
    "ax.legend(fontsize=10)\n",
    "\n",
    "for i in range(len(airlines)):\n",
    "    ax.text(i, replied_counts[i] / 2, f\"{replied_percentages[i]:.1f}%\", ha='center', va='center', color='white', weight='bold', fontsize=10)\n",
    "    ax.text(i, replied_counts[i] + non_replied_counts[i] / 2, f\"{non_replied_percentages[i]:.1f}%\", ha='center', va='center', color='white', weight='bold', fontsize=11)\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 14s\n",
    "\n",
    "all_tweets_query = f\"\"\"SELECT text, user_id, mentioned_airlines, user_mentions, label, timestamp_ms, baggage, money, staff, delay_and_cancellation\n",
    "            FROM tweets\n",
    "            WHERE timestamp_ms >= UNIX_TIMESTAMP('{start_date}') * 1000 \n",
    "                AND timestamp_ms <= UNIX_TIMESTAMP('{end_date}') * 1000 \n",
    "                AND ((\n",
    "                tweets.mentioned_airlines LIKE '%AirFrance%'\n",
    "                OR tweets.mentioned_airlines LIKE '%Lufthansa%'\n",
    "                OR tweets.mentioned_airlines LIKE '%KLM%'\n",
    "                OR tweets.mentioned_airlines LIKE '%British Airways%'\n",
    "            )\n",
    "            OR (\n",
    "                tweets.user_mentions LIKE '%106062176%'\n",
    "                OR tweets.user_mentions LIKE '%124476322%'\n",
    "                OR tweets.user_mentions LIKE '%56377143%'\n",
    "                OR tweets.user_mentions LIKE '%18332190%'\n",
    "            ))\"\"\"\n",
    "\n",
    "cursor.execute(all_tweets_query)\n",
    "texts = cursor.fetchall()\n",
    "print(\"Text fetched.\")\n",
    "number_tweets = cursor.rowcount\n",
    "print(\"Total number of rows in table: \", number_tweets)\n",
    "\n",
    "df = pd.DataFrame(texts, columns=['text', 'user_id', 'mentioned_airlines', 'user_mentions', 'label', 'timestamp', 'baggage', 'money', 'staff', 'delay_and_cancellation'])\n",
    "\n",
    "df['datetime'] = pd.to_datetime(df['timestamp'], unit='ms') \n",
    "\n",
    "df['datetime'] = df['datetime'].dt.tz_localize('UTC').dt.tz_convert('Europe/Amsterdam')\n",
    "\n",
    "df['month'] = df['datetime'].dt.month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Percentage of Positive, Neutral, and Negative Sentiments per Airline- 2s\n",
    "\n",
    "df['AirFrance'] = df['mentioned_airlines'].apply(lambda x: 'AirFrance' in x) | df['user_mentions'].apply(lambda x: '106062176' in x)\n",
    "df['Lufthansa'] = df['mentioned_airlines'].apply(lambda x: 'Lufthansa' in x) | df['user_mentions'].apply(lambda x: '124476322' in x)\n",
    "df['KLM'] = df['mentioned_airlines'].apply(lambda x: 'KLM' in x) | df['user_mentions'].apply(lambda x: '56377143' in x)\n",
    "df['British_Airways'] = df['mentioned_airlines'].apply(lambda x: 'British_Airways' in x) | df['user_mentions'].apply(lambda x: '18332190' in x)\n",
    "\n",
    "airlines_2 = ['AirFrance', 'Lufthansa', 'KLM', 'British_Airways']\n",
    "sentiments = ['positive', 'neutral', 'negative']\n",
    "\n",
    "data = []\n",
    "for airline in airlines_2:\n",
    "    sentiment_counts = []\n",
    "    for sentiment in sentiments:\n",
    "        count = df[df[airline] & (df['label'] == sentiment)].shape[0]\n",
    "        sentiment_counts.append(count)\n",
    "    total = sum(sentiment_counts)\n",
    "    percentages = [(count / total) * 100 if total > 0 else 0 for count in sentiment_counts]\n",
    "    data.append(percentages)\n",
    "\n",
    "plot_df = pd.DataFrame(data, columns=sentiments, index=airlines_2)\n",
    "\n",
    "fig, ax = plt.subplots(figsize=(12, 8))\n",
    "\n",
    "# Create the stacked bar plot\n",
    "custom_colors = ['#01295C', '#EB2226', '#A7A9AC', '#B9CFED']\n",
    "\n",
    "plot_df.plot(kind='bar', stacked=True, color=custom_colors, ax=ax, edgecolor='white')\n",
    "\n",
    "for i, airline in enumerate(airlines_2):\n",
    "    pos_base = 0\n",
    "    neu_base = plot_df.loc[airline, 'positive']\n",
    "    neg_base = plot_df.loc[airline, 'positive'] + plot_df.loc[airline, 'neutral']\n",
    "    for sentiment in sentiments:\n",
    "        count = plot_df.loc[airline, sentiment]\n",
    "        percentage = f\"{count:.1f}%\"\n",
    "        if sentiment == 'positive':\n",
    "            ax.text(i, pos_base + count / 2, percentage, ha='center', va='center', fontsize=11, weight='bold', color='white')\n",
    "            pos_base += count\n",
    "        elif sentiment == 'neutral':\n",
    "            ax.text(i, neu_base + count / 2, percentage, ha='center', va='center', fontsize=11, weight='bold', color='white')\n",
    "            neu_base += count\n",
    "        else:\n",
    "            ax.text(i, neg_base + count / 2, percentage, ha='center', va='center', fontsize=11, weight='bold', color='white')\n",
    "            neg_base += count\n",
    "\n",
    "            \n",
    "ax.set_ylabel('Percentage', fontsize=14)\n",
    "ax.set_xlabel('Airline', fontsize=14)\n",
    "ax.set_title('Percentage of Positive, Neutral, and Negative Sentiments per Airline', fontsize=16, weight='bold', pad=20)\n",
    "ax.tick_params(axis='both', which='major', labelsize=12)\n",
    "ax.legend(title='Sentiment', fontsize=12, title_fontsize='13', loc='center left', bbox_to_anchor=(1, 0.5))\n",
    "\n",
    "plt.tight_layout(rect=[0, 0, 0.85, 1])\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a DataFrame with sentiment percentages for each airline\n",
    "asdf = pd.DataFrame(data, columns=sentiments, index=airlines_2)\n",
    "\n",
    "# Display the DataFrame as a table\n",
    "asdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.metrics import classification_report\n",
    "\n",
    "# Assuming y_true and y_pred are your true and predicted labels respectively\n",
    "y_true = ['negative', 'positive', 'neutral', 'negative', 'negative', 'negative', 'negative', 'negative', 'neutral', 'negative', 'negative', 'neutral', 'neutral', 'neutral', 'neutral', 'positive', 'negative', 'negative', 'neutral', 'positive', 'neutral', 'neutral', 'neutral', 'positive', 'neutral', 'negative', 'neutral', 'negative', 'negative', 'neutral', 'neutral', 'neutral', 'neutral', 'neutral', 'neutral', 'neutral', 'neutral', 'positive', 'negative', 'neutral', 'positive', 'positive', 'positive', 'positive', 'positive', 'neutral', 'positive', 'negative', 'negative', 'neutral', 'neutral', 'neutral', 'negative', 'positive', 'negative', 'neutral', 'negative', 'negative', 'neutral', 'neutral', 'positive', 'positive', 'negative', 'negative', 'positive', 'neutral', 'neutral', 'negative', 'negative', 'neutral', 'neutral', 'negative', 'negative', 'neutral', 'neutral', 'negative', 'neutral', 'positive', 'neutral', 'positive', 'positive', 'negative', 'positive', 'positive', 'neutral', 'negative', 'positive', 'negative', 'neutral', 'neutral', 'neutral', 'neutral', 'neutral', 'neutral', 'negative', 'negative', 'positive', 'neutral', 'neutral', 'neutral']\n",
    "y_pred = ['neutral', 'positive', 'neutral', 'negative', 'positive', 'negative', 'negative', 'negative', 'neutral', 'negative', 'negative', 'positive', 'positive', 'neutral', 'neutral', 'positive', 'negative', 'negative', 'neutral', 'positive', 'neutral', 'positive', 'positive', 'positive', 'neutral', 'negative', 'neutral', 'negative', 'negative', 'neutral', 'negative', 'negative', 'positive', 'neutral', 'neutral', 'neutral', 'neutral', 'neutral', 'negative', 'positive', 'positive', 'positive', 'neutral', 'positive', 'positive', 'neutral', 'positive', 'negative', 'neutral', 'neutral', 'positive', 'neutral', 'negative', 'positive', 'negative', 'neutral', 'neutral', 'negative', 'neutral', 'neutral', 'positive', 'positive', 'negative', 'negative', 'positive', 'neutral', 'neutral', 'neutral', 'negative', 'neutral', 'neutral', 'negative', 'negative', 'neutral', 'neutral', 'negative', 'neutral', 'positive', 'negative', 'positive', 'neutral', 'negative', 'positive', 'positive', 'neutral', 'neutral', 'positive', 'negative', 'neutral', 'negative', 'neutral', 'neutral', 'positive', 'negative', 'neutral', 'neutral', 'neutral', 'neutral', 'neutral', 'neutral']\n",
    "\n",
    "\n",
    "# Generate classification report\n",
    "report = classification_report(y_true, y_pred, target_names=[\"negative\", \"positive\", \"neutral\"])\n",
    "\n",
    "print(report)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import scipy.stats as stats\n",
    "\n",
    "# Convert percentages to counts (assuming 1000 tweets per airline)\n",
    "counts = np.array([\n",
    "    [162, 447, 391],\n",
    "    [144, 446, 411],\n",
    "    [170, 452, 377],\n",
    "    [174, 346, 479]\n",
    "])\n",
    "\n",
    "# Perform Chi-Square Test for Independence\n",
    "chi2, p, dof, expected = stats.chi2_contingency(counts)\n",
    "\n",
    "chi2, p"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import scipy.stats as stats\n",
    "\n",
    "# Function to calculate confidence interval\n",
    "def confidence_interval(proportion, n, confidence=0.95):\n",
    "    z = stats.norm.ppf(1 - (1 - confidence) / 2)\n",
    "    se = np.sqrt(proportion * (1 - proportion) / n)\n",
    "    return proportion - z * se, proportion + z * se\n",
    "\n",
    "# Calculate confidence intervals for British Airways\n",
    "n = 191530  # Assuming 1000 tweets\n",
    "positive_ba = 0.1743\n",
    "neutral_ba = 0.3463\n",
    "negative_ba = 0.4794\n",
    "\n",
    "ci_positive_ba = confidence_interval(positive_ba, n)\n",
    "ci_neutral_ba = confidence_interval(neutral_ba, n)\n",
    "ci_negative_ba = confidence_interval(negative_ba, n)\n",
    "\n",
    "ci_positive_ba, ci_neutral_ba, ci_negative_ba"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creates the Dataframe for British Airways- 17 s\n",
    "\n",
    "conv_ba1 = f\"\"\"\n",
    "    SELECT text, id, staff, baggage, delay_and_cancellation, money, timestamp_ms\n",
    "    FROM tweets \n",
    "    WHERE mentioned_airlines LIKE '%British_Airways%'\n",
    "        AND timestamp_ms >= UNIX_TIMESTAMP('{start_date}') * 1000\n",
    "        AND timestamp_ms <= UNIX_TIMESTAMP('{end_date}') * 1000;   \n",
    "\"\"\"\n",
    "conv_ba2 = \"\"\"\n",
    "    SELECT tweets.text, tweets.id, tweets.staff, tweets.baggage, tweets.delay_and_cancellation, tweets.money, tweets.timestamp_ms\n",
    "    FROM tweets \n",
    "    JOIN hasher ON tweets.id = hasher.id\n",
    "    JOIN conversations ON hasher.conversation_id = conversations.conversation_id\n",
    "    WHERE conversations.conversation_id IN (\n",
    "        SELECT conversation_id\n",
    "        FROM conversations\n",
    "        WHERE airline LIKE '%British_Airways%'\n",
    "            AND timestamp_ms >= UNIX_TIMESTAMP('{start_date}') * 1000\n",
    "            AND timestamp_ms <= UNIX_TIMESTAMP('{end_date}') * 1000\n",
    "    ); \n",
    "\"\"\"\n",
    "\n",
    "df_ba1 = pd.read_sql(conv_ba1, connection)\n",
    "df_ba2 = pd.read_sql(conv_ba2, connection)\n",
    "\n",
    "df_ba = pd.concat([df_ba1, df_ba2], ignore_index=True, axis=0)\n",
    "df_ba = df_ba.drop_duplicates()\n",
    "\n",
    "df_ba['datetime'] = pd.to_datetime(df_ba['timestamp_ms'], unit='ms')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creates the dataframe for AirFrance- 6s\n",
    "\n",
    "conv_af1 = f\"\"\"\n",
    "    SELECT text, id, staff, baggage, delay_and_cancellation, money, timestamp_ms\n",
    "    FROM tweets \n",
    "    WHERE mentioned_airlines LIKE '%AirFrance%'\n",
    "        AND timestamp_ms >= UNIX_TIMESTAMP('{start_date}') * 1000\n",
    "        AND timestamp_ms <= UNIX_TIMESTAMP('{end_date}') * 1000;\n",
    "\"\"\"\n",
    "conv_af2 = f\"\"\"\n",
    "    SELECT tweets.text, tweets.id, tweets.staff, tweets.baggage, tweets.delay_and_cancellation, tweets.money, tweets.timestamp_ms\n",
    "    FROM tweets \n",
    "    JOIN hasher ON tweets.id = hasher.id\n",
    "    JOIN conversations ON hasher.conversation_id = conversations.conversation_id\n",
    "    WHERE conversations.conversation_id IN (\n",
    "        SELECT conversation_id\n",
    "        FROM conversations\n",
    "        WHERE airline LIKE '%AirFrance%'\n",
    "            AND timestamp_ms >= UNIX_TIMESTAMP('{start_date}') * 1000\n",
    "            AND timestamp_ms <= UNIX_TIMESTAMP('{end_date}') * 1000   \n",
    "    );\n",
    "\"\"\"\n",
    "\n",
    "df_af1 = pd.read_sql(conv_af1, connection)\n",
    "df_af2 = pd.read_sql(conv_af2, connection)\n",
    "\n",
    "df_af = pd.concat([df_af1, df_af2], ignore_index=True, axis=0)\n",
    "df_af = df_af.drop_duplicates()\n",
    "\n",
    "df_af['datetime'] = pd.to_datetime(df_af['timestamp_ms'], unit='ms')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creates the dataframe for KLM- 7s\n",
    "\n",
    "conv_klm1 = f\"\"\"\n",
    "    SELECT text, id, staff, baggage, delay_and_cancellation, money, timestamp_ms\n",
    "    FROM tweets \n",
    "    WHERE mentioned_airlines LIKE '%KLM%'\n",
    "        AND timestamp_ms >= UNIX_TIMESTAMP('{start_date}') * 1000\n",
    "        AND timestamp_ms <= UNIX_TIMESTAMP('{end_date}') * 1000; \n",
    "\"\"\"\n",
    "conv_klm2 = f\"\"\"\n",
    "    SELECT tweets.text, tweets.id, tweets.staff, tweets.baggage, tweets.delay_and_cancellation, tweets.money, tweets.timestamp_ms\n",
    "    FROM tweets \n",
    "    JOIN hasher ON tweets.id = hasher.id\n",
    "    JOIN conversations ON hasher.conversation_id = conversations.conversation_id\n",
    "    WHERE conversations.conversation_id IN (\n",
    "        SELECT conversation_id\n",
    "        FROM conversations\n",
    "        WHERE airline LIKE '%KLM%'\n",
    "            AND timestamp_ms >= UNIX_TIMESTAMP('{start_date}') * 1000\n",
    "            AND timestamp_ms <= UNIX_TIMESTAMP('{end_date}') * 1000\n",
    "    );\n",
    "\"\"\"\n",
    "\n",
    "df_klm1 = pd.read_sql(conv_klm1, connection)\n",
    "df_klm2 = pd.read_sql(conv_klm2, connection)\n",
    "\n",
    "df_klm = pd.concat([df_klm1, df_klm2], ignore_index=True, axis=0)\n",
    "df_klm = df_klm.drop_duplicates()\n",
    "\n",
    "df_klm['datetime'] = pd.to_datetime(df_klm['timestamp_ms'], unit='ms')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creates the dataframe for Lufthansa- 7s\n",
    "\n",
    "conv_lh1 = f\"\"\"\n",
    "    SELECT text, id, staff, baggage, delay_and_cancellation, money, timestamp_ms\n",
    "    FROM tweets \n",
    "    WHERE mentioned_airlines LIKE '%Lufthansa%'\n",
    "       AND timestamp_ms >= UNIX_TIMESTAMP('{start_date}') * 1000\n",
    "       AND timestamp_ms <= UNIX_TIMESTAMP('{end_date}') * 1000; \n",
    "\"\"\"\n",
    "conv_lh2 = f\"\"\"\n",
    "    SELECT tweets.text, tweets.id, tweets.staff, tweets.baggage, tweets.delay_and_cancellation, tweets.money, tweets.timestamp_ms\n",
    "    FROM tweets \n",
    "    JOIN hasher ON tweets.id = hasher.id\n",
    "    JOIN conversations ON hasher.conversation_id = conversations.conversation_id\n",
    "    WHERE conversations.conversation_id IN (\n",
    "        SELECT conversation_id\n",
    "        FROM conversations\n",
    "        WHERE airline LIKE '%Lufthansa%'\n",
    "            AND timestamp_ms >= UNIX_TIMESTAMP('{start_date}') * 1000\n",
    "            AND timestamp_ms <= UNIX_TIMESTAMP('{end_date}') * 1000\n",
    "    );\n",
    "\"\"\"\n",
    "\n",
    "df_lh1 = pd.read_sql(conv_lh1, connection)\n",
    "df_lh2 = pd.read_sql(conv_lh2, connection)\n",
    "\n",
    "df_lh = pd.concat([df_lh1, df_lh2], ignore_index=True, axis=0)\n",
    "df_lh = df_lh.drop_duplicates()\n",
    "\n",
    "df_lh['datetime'] = pd.to_datetime(df_lh['timestamp_ms'], unit='ms')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 0s\n",
    "\n",
    "columns_drop = ['text', 'id', 'timestamp_ms', 'datetime']\n",
    "\n",
    "df_topics_ba = df_ba.drop(columns=columns_drop)\n",
    "df_topics_af = df_af.drop(columns=columns_drop)\n",
    "df_topics_klm = df_klm.drop(columns=columns_drop)\n",
    "df_topics_lh = df_lh.drop(columns=columns_drop)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 0s\n",
    "\n",
    "counts_ba = df_topics_ba.apply(lambda col: col.value_counts().get(1, 0))\n",
    "counts_af = df_topics_af.apply(lambda col: col.value_counts().get(1, 0))\n",
    "counts_klm = df_topics_klm.apply(lambda col: col.value_counts().get(1, 0))\n",
    "counts_lh = df_topics_lh.apply(lambda col: col.value_counts().get(1, 0))\n",
    "\n",
    "counts_ba_sum = counts_ba.sum()\n",
    "counts_af_sum = counts_af.sum()\n",
    "counts_klm_sum = counts_klm.sum()\n",
    "counts_lh_sum = counts_lh.sum()\n",
    "\n",
    "percentages_ba = round((counts_ba / counts_ba_sum) * 100, 2)\n",
    "percentages_af = round((counts_af / counts_af_sum) * 100, 2)\n",
    "percentages_klm = round((counts_klm / counts_klm_sum) * 100, 2)\n",
    "percentages_lh = round((counts_lh / counts_lh_sum) * 100, 2)\n",
    "\n",
    "percentages = pd.DataFrame({\n",
    "    'Airline': ['British Airways', 'AirFrance', 'KLM', 'Lufthansa'],\n",
    "    'Staff': [percentages_ba[0], percentages_af[0], percentages_klm[0], percentages_lh[0]],\n",
    "    'Baggage': [percentages_ba[1], percentages_af[1], percentages_klm[1], percentages_lh[1]],\n",
    "    'Delay and Cancellation': [percentages_ba[2], percentages_af[2], percentages_klm[2], percentages_lh[2]],\n",
    "    'Money': [percentages_ba[3], percentages_af[3], percentages_klm[3], percentages_lh[3]]\n",
    "})\n",
    "\n",
    "ax =percentages.set_index('Airline').plot(kind='bar', stacked=True, figsize=(10, 6), color=custom_colors)\n",
    "\n",
    "for p in ax.patches:\n",
    "    width, height = p.get_width(), p.get_height()\n",
    "    x, y = p.get_xy()\n",
    "    if height > 0:\n",
    "        ax.annotate(f'{height:.2f}%', (x + width/2, y + height/2), ha='center', va='center', fontsize=10, color ='white', weight='bold')\n",
    "\n",
    "plt.xlabel('Airline')\n",
    "plt.ylabel('Percentage of Tweets')\n",
    "plt.title('Percentage of Tweets per Topic by Airline', weight='bold')\n",
    "plt.xticks(rotation=0)\n",
    "plt.legend(title='Topics', bbox_to_anchor=(1.05, 1), loc='upper left')\n",
    "plt.tight_layout()\n",
    "\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ba = df[df['mentioned_airlines'].apply(lambda x: 'British_Airways' in x) | df['user_mentions'].apply(lambda x: '18332190' in x)]\n",
    "\n",
    "categories = ['baggage', 'money', 'staff', 'delay_and_cancellation']\n",
    "sentiments = ['positive', 'neutral', 'negative']\n",
    "sentiment_colors = sns.color_palette(\"muted\", 3)\n",
    "\n",
    "# Calculate sentiment percentages for each category\n",
    "sentiment_percentages = {category: [] for category in categories}\n",
    "total_counts = {category: 0 for category in categories}\n",
    "\n",
    "for category in categories:\n",
    "    category_data = df_ba[df_ba[category] == 1]\n",
    "    total_count = category_data.shape[0]\n",
    "    total_counts[category] = total_count\n",
    "    for sentiment in sentiments:\n",
    "        sentiment_count = category_data[category_data['label'] == sentiment].shape[0]\n",
    "        sentiment_percentage = (sentiment_count / total_count) * 100 if total_count > 0 else 0\n",
    "        sentiment_percentages[category].append(sentiment_percentage)\n",
    "\n",
    "# Custom color palette\n",
    "custom_colors = ['#0D2859', '#D83B33', '#A7A9AC']\n",
    "\n",
    "# Prepare the DataFrame for plotting\n",
    "plot_data = pd.DataFrame(sentiment_percentages, index=sentiments).T\n",
    "\n",
    "# Plot the horizontal stacked bar chart\n",
    "fig, ax = plt.subplots(figsize=(20, 10))\n",
    "bottoms = [0] * len(categories)\n",
    "\n",
    "for sentiment, color in zip(sentiments, custom_colors):\n",
    "    ax.barh(categories, plot_data[sentiment], left=bottoms, label=sentiment.capitalize(), color=color)\n",
    "    bottoms = [i + j for i, j in zip(bottoms, plot_data[sentiment])]\n",
    "\n",
    "    # Annotate percentages on the bars\n",
    "    for i, (percent, total) in enumerate(zip(plot_data[sentiment], total_counts.values())):\n",
    "        if total > 0:\n",
    "            ax.text(bottoms[i] - percent / 2, i, f'{percent:.1f}%', va='center', ha='center', fontsize=12, color='white', weight='bold')\n",
    "\n",
    "ax.set_xlabel('Percentage', fontsize=14)\n",
    "ax.set_title('Sentiment Analysis for Different Categories in British Airways', fontsize=20, weight='bold')\n",
    "ax.legend(title='Sentiment', bbox_to_anchor=(1.05, 1), loc='upper left', prop={'size': 15})\n",
    "ax.set_yticklabels([category.capitalize().replace('_', ' ') for category in categories])\n",
    "ax.tick_params(axis='both', which='major', labelsize=13)\n",
    "plt.tight_layout(rect=[0, 0, 0.85, 1])\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define the airline user ID\n",
    "airline_user_id = 18332190\n",
    "\n",
    "# Define the query to get all tweets in the conversations involving British Airways\n",
    "query_sankey_delay = f\"\"\"\n",
    "SELECT tweets.text, tweets.label, tweets.user_id, tweets.timestamp_ms, hasher.conversation_id, hasher.conversation_rank\n",
    "FROM tweets\n",
    "JOIN hasher ON hasher.id = tweets.id\n",
    "JOIN conversations ON hasher.conversation_id = conversations.conversation_id\n",
    "WHERE conversations.airline LIKE '%British_Airways%' \n",
    "    AND tweets.delay_and_cancellation = 1\n",
    "    AND tweets.timestamp_ms >= UNIX_TIMESTAMP('{start_date}') * 1000\n",
    "    AND tweets.timestamp_ms <= UNIX_TIMESTAMP('{end_date}') * 1000\n",
    "ORDER BY tweets.timestamp_ms;\n",
    "\"\"\"\n",
    "\n",
    "# Load the result of the query into a DataFrame\n",
    "df_tweets_british_airways_sankey_delay = pd.read_sql(query_sankey_delay, connection)\n",
    "\n",
    "# Convert the datetime column to pandas datetime\n",
    "df_tweets_british_airways_sankey_delay['datetime'] = pd.to_datetime(df_tweets_british_airways_sankey_delay['timestamp_ms'], unit='ms')\n",
    "\n",
    "df_tweets_british_airways_sankey_delay['datetime'] = df_tweets_british_airways_sankey_delay['datetime'].dt.tz_localize('UTC').dt.tz_convert('Europe/Amsterdam')\n",
    "\n",
    "# Initialize empty lists to store sentiments\n",
    "sentiments_initial_tweet = []\n",
    "sentiments_post_reply = []\n",
    "\n",
    "# Group tweets by conversation_id\n",
    "grouped_sankey_delay= df_tweets_british_airways_sankey_delay.groupby('conversation_id')\n",
    "\n",
    "# Process each conversation\n",
    "for conversation_id, group in grouped_sankey_delay:\n",
    "    # Identify the initial user's tweet (rank 1)\n",
    "    user_initial_tweet = group[(group['user_id'] != airline_user_id) & (group['conversation_rank'] == 1)]\n",
    "    \n",
    "    if not user_initial_tweet.empty:\n",
    "        user_id = user_initial_tweet['user_id'].iloc[0]\n",
    "        initial_tweet_time = user_initial_tweet['datetime'].iloc[0]\n",
    "\n",
    "        # Identify the airline's reply to this initial tweet\n",
    "        airline_reply = group[(group['user_id'] == airline_user_id) & (group['datetime'] > initial_tweet_time)]\n",
    "        if not airline_reply.empty:\n",
    "            airline_reply_time = airline_reply['datetime'].iloc[0]\n",
    "\n",
    "            # Identify the user's reply to the airline's response\n",
    "            user_followup_tweet = group[(group['user_id'] == user_id) & (group['datetime'] > airline_reply_time)]\n",
    "            if not user_followup_tweet.empty:\n",
    "                # Append sentiments for analysis\n",
    "                sentiments_initial_tweet.append(user_initial_tweet['label'].iloc[0])\n",
    "                sentiments_post_reply.append(user_followup_tweet['label'].iloc[0])\n",
    "\n",
    "# Step 1: Prepare data for Sankey diagram\n",
    "transitions = pd.DataFrame({\n",
    "    'initial': sentiments_initial_tweet,\n",
    "    'post_reply': sentiments_post_reply\n",
    "})\n",
    "\n",
    "# Count transitions\n",
    "transition_counts = transitions.value_counts().reset_index(name='count')\n",
    "\n",
    "# Add percentage to transition_counts\n",
    "total_counts = transition_counts['count'].sum()\n",
    "transition_counts['percentage'] = (transition_counts['count'] / total_counts * 100).round(2)\n",
    "\n",
    "# Define nodes (initial sentiments and final grouped sentiments)\n",
    "nodes = ['positive', 'negative', 'neutral', 'positive_end', 'negative_end', 'neutral_end']\n",
    "node_indices = {label: i for i, label in enumerate(nodes)}\n",
    "\n",
    "# Prepare data for Sankey diagram\n",
    "source = [node_indices[row['initial']] for _, row in transition_counts.iterrows()]\n",
    "target = [node_indices[f\"{row['post_reply']}_end\"] for _, row in transition_counts.iterrows()]\n",
    "value = transition_counts['count'].tolist()\n",
    "label = [f\"{row['initial']} to {row['post_reply']}: {row['count']} ({row['percentage']}%)\" for _, row in transition_counts.iterrows()]\n",
    "\n",
    "# Define colors for nodes and links\n",
    "node_colors = {\n",
    "    'positive': 'rgb(44, 160, 44)',  # green\n",
    "    'negative': 'rgb(214, 39, 40)',  # red\n",
    "    'neutral': 'rgb(255, 165, 0)',   # orange\n",
    "    'positive_end': 'rgb(44, 160, 44)',  # green for final positive\n",
    "    'negative_end': 'rgb(214, 39, 40)',  # red for final negative\n",
    "    'neutral_end': 'rgb(255, 165, 0)'    # orange for final neutral\n",
    "}\n",
    "\n",
    "transition_colors = {\n",
    "    'positive': 'rgba(44, 160, 44, 0.4)',  # light green\n",
    "    'negative': 'rgba(214, 39, 40, 0.4)',  # light red\n",
    "    'neutral': 'rgba(255, 165, 0, 0.4)'    # light orange\n",
    "}\n",
    "\n",
    "# Create color list for nodes\n",
    "colors = [node_colors[node] for node in nodes]\n",
    "\n",
    "# Create the Sankey diagram\n",
    "fig = go.Figure(data=[go.Sankey(\n",
    "    node=dict(\n",
    "        pad=15,\n",
    "        thickness=30,\n",
    "        line=dict(color=\"black\", width=0.5),\n",
    "        label=['Positive', 'Negative', 'Neutral', 'Positive End', 'Negative End', 'Neutral End'],\n",
    "        color=colors\n",
    "    ),\n",
    "    link=dict(\n",
    "        source=source,\n",
    "        target=target,\n",
    "        value=value,\n",
    "        color=[transition_colors[row['initial']] for _, row in transition_counts.iterrows()],  # Use bland colors for transitions\n",
    "        customdata=label,\n",
    "        hovertemplate='%{customdata}<extra></extra>',\n",
    "        label=label  # Add labels directly to the links\n",
    "    )\n",
    ")])\n",
    "\n",
    "fig.update_layout(\n",
    "    title_text=\"Sentiment Transition (British Airways, delay and cancellation)\", \n",
    "    font_size=12,\n",
    "    width=1200,  # increased width to accommodate the legend\n",
    "    height=700,\n",
    "    margin=dict(l=50, r=50, t=50, b=50)  # Adjusted margins\n",
    ")\n",
    "\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define the airline user ID\n",
    "airline_user_id = 18332190\n",
    "\n",
    "# Define the query to get all tweets in the conversations involving British Airways\n",
    "query_sankey_ba_general = f\"\"\"\n",
    "SELECT tweets.text, tweets.label, tweets.user_id, tweets.timestamp_ms, hasher.conversation_id, hasher.conversation_rank\n",
    "FROM tweets\n",
    "JOIN hasher ON hasher.id = tweets.id\n",
    "JOIN conversations ON hasher.conversation_id = conversations.conversation_id\n",
    "WHERE conversations.airline LIKE '%British_Airways%' \n",
    "    AND tweets.timestamp_ms >= UNIX_TIMESTAMP('{start_date}') * 1000\n",
    "    AND tweets.timestamp_ms <= UNIX_TIMESTAMP('{end_date}') * 1000\n",
    "ORDER BY tweets.timestamp_ms;\n",
    "\"\"\"\n",
    "\n",
    "# Load the result of the query into a DataFrame\n",
    "df_tweets_british_airways_sankey = pd.read_sql(query_sankey_ba_general, connection)\n",
    "\n",
    "# Convert the datetime column to pandas datetime\n",
    "df_tweets_british_airways_sankey['datetime'] = pd.to_datetime(df_tweets_british_airways_sankey['timestamp_ms'], unit='ms')\n",
    "\n",
    "df_tweets_british_airways_sankey['datetime'] = df_tweets_british_airways_sankey['datetime'].dt.tz_localize('UTC').dt.tz_convert('Europe/Amsterdam')\n",
    "\n",
    "# Initialize empty lists to store sentiments\n",
    "sentiments_initial_tweet = []\n",
    "sentiments_post_reply = []\n",
    "\n",
    "# Group tweets by conversation_id\n",
    "grouped_sankey_ba = df_tweets_british_airways_sankey.groupby('conversation_id')\n",
    "\n",
    "# Process each conversation\n",
    "for conversation_id, group in grouped_sankey_ba:\n",
    "    # Identify the initial user's tweet (rank 1)\n",
    "    user_initial_tweet = group[(group['user_id'] != airline_user_id) & (group['conversation_rank'] == 1)]\n",
    "    \n",
    "    if not user_initial_tweet.empty:\n",
    "        user_id = user_initial_tweet['user_id'].iloc[0]\n",
    "        initial_tweet_time = user_initial_tweet['datetime'].iloc[0]\n",
    "\n",
    "        # Identify the airline's reply to this initial tweet\n",
    "        airline_reply = group[(group['user_id'] == airline_user_id) & (group['datetime'] > initial_tweet_time)]\n",
    "        if not airline_reply.empty:\n",
    "            airline_reply_time = airline_reply['datetime'].iloc[0]\n",
    "\n",
    "            # Identify the user's reply to the airline's response\n",
    "            user_followup_tweet = group[(group['user_id'] == user_id) & (group['datetime'] > airline_reply_time)]\n",
    "            if not user_followup_tweet.empty:\n",
    "                # Append sentiments for analysis\n",
    "                sentiments_initial_tweet.append(user_initial_tweet['label'].iloc[0])\n",
    "                sentiments_post_reply.append(user_followup_tweet['label'].iloc[0])\n",
    "\n",
    "# Step 1: Prepare data for Sankey diagram\n",
    "transitions = pd.DataFrame({\n",
    "    'initial': sentiments_initial_tweet,\n",
    "    'post_reply': sentiments_post_reply\n",
    "})\n",
    "\n",
    "# Count transitions\n",
    "transition_counts = transitions.value_counts().reset_index(name='count')\n",
    "\n",
    "# Add percentage to transition_counts\n",
    "total_counts = transition_counts['count'].sum()\n",
    "transition_counts['percentage'] = (transition_counts['count'] / total_counts * 100).round(2)\n",
    "\n",
    "# Define nodes (initial sentiments and final grouped sentiments)\n",
    "nodes = ['positive', 'negative', 'neutral', 'positive_end', 'negative_end', 'neutral_end']\n",
    "node_indices = {label: i for i, label in enumerate(nodes)}\n",
    "\n",
    "# Prepare data for Sankey diagram\n",
    "source = [node_indices[row['initial']] for _, row in transition_counts.iterrows()]\n",
    "target = [node_indices[f\"{row['post_reply']}_end\"] for _, row in transition_counts.iterrows()]\n",
    "value = transition_counts['count'].tolist()\n",
    "label = [f\"{row['initial']} to {row['post_reply']}: {row['count']} ({row['percentage']}%)\" for _, row in transition_counts.iterrows()]\n",
    "\n",
    "# Define colors for nodes and links\n",
    "node_colors = {\n",
    "    'positive': 'rgb(44, 160, 44)',  # green\n",
    "    'negative': 'rgb(214, 39, 40)',  # red\n",
    "    'neutral': 'rgb(255, 165, 0)',   # orange\n",
    "    'positive_end': 'rgb(44, 160, 44)',  # green for final positive\n",
    "    'negative_end': 'rgb(214, 39, 40)',  # red for final negative\n",
    "    'neutral_end': 'rgb(255, 165, 0)'    # orange for final neutral\n",
    "}\n",
    "\n",
    "transition_colors = {\n",
    "    'positive': 'rgba(44, 160, 44, 0.4)',  # light green\n",
    "    'negative': 'rgba(214, 39, 40, 0.4)',  # light red\n",
    "    'neutral': 'rgba(255, 165, 0, 0.4)'    # light orange\n",
    "}\n",
    "\n",
    "# Create color list for nodes\n",
    "colors = [node_colors[node] for node in nodes]\n",
    "\n",
    "# Create the Sankey diagram\n",
    "fig = go.Figure(data=[go.Sankey(\n",
    "    node=dict(\n",
    "        pad=15,\n",
    "        thickness=30,\n",
    "        line=dict(color=\"black\", width=0.5),\n",
    "        label=['Positive', 'Negative', 'Neutral', 'Positive End', 'Negative End', 'Neutral End'],\n",
    "        color=colors\n",
    "    ),\n",
    "    link=dict(\n",
    "        source=source,\n",
    "        target=target,\n",
    "        value=value,\n",
    "        color=[transition_colors[row['initial']] for _, row in transition_counts.iterrows()],  # Use bland colors for transitions\n",
    "        customdata=label,\n",
    "        hovertemplate='%{customdata}<extra></extra>',\n",
    "        label=label  # Add labels directly to the links\n",
    "    )\n",
    ")])\n",
    "\n",
    "fig.update_layout(\n",
    "    title_text=\"Sentiment Transition (British Airways)\", \n",
    "    font_size=18,\n",
    "    width=1200,  # increased width to accommodate the legend\n",
    "    height=700,\n",
    "    margin=dict(l=50, r=50, t=50, b=50)  # Adjusted margins\n",
    ")\n",
    "\n",
    "fig.show()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "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.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}