business_idea_stats_from_wh.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Business idea\n",
    "\n",
    "This file contains all the plots we made related to our business idea. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Business idea statistics \n",
    "\n",
    "- [The timeframe](#The-timeframe)\n",
    "- [The normal topics dataframe](#Dataframe-for-all-the-tweets-in-a-conversation)\n",
    "- [The dataframes per airline](#Dataframes-per-Airline)\n",
    "- [Number of tweets per topic (in presentation)](#Number-of-tweets-per-topic)\n",
    "- [Number of tweets per airline](#Number-of-tweets-per-airline-that-have-a-topic)\n",
    "- [Number of tweets per topic for all airlines separately](#Number-of-tweets-per-topic-per-Airline)\n",
    "- [Percentage of tweets per airline for all topics separately](#Percentage-of-tweets-per-topic-for-all-airlines)\n",
    "- [Stacked bar chart with airlines and topics (in presentation)](#Stacked-bar-chart-topics-and-airlines)\n",
    "- [Coocurring topics](#Number-of_coocurring-topics)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Imports and setting up MySQL\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",
    "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": "markdown",
   "metadata": {},
   "source": [
    "### The timeframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Input for now is year-month-day\n",
    "timeframe = {'start': ['2019', '06', '10'], 'end':['2020', '01', '12']}\n",
    "timeframe_start = f'{timeframe['start'][2]}/{timeframe['start'][1]}/{timeframe['start'][0]}'\n",
    "timeframe_end = f'{timeframe['end'][2]}/{timeframe['end'][1]}/{timeframe['end'][0]}'\n",
    "\n",
    "start_date = pd.to_datetime(timeframe_start)\n",
    "end_date = pd.to_datetime(timeframe_end)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dataframe for all the tweets in a conversation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conv_topics1 = \"\"\"\n",
    "    SELECT text, id, staff, baggage, delay_and_cancellation, money, timestamp_ms\n",
    "    FROM tweets \n",
    "    WHERE TRIM(language) = 'en' AND (mentioned_airlines LIKE '%Lufthansa%' OR mentioned_airlines LIKE '%British_Airways%' OR mentioned_airlines LIKE '%KLM%' OR mentioned_airlines LIKE '%AirFrance%')\n",
    "\"\"\"\n",
    "conv_topics2 = \"\"\"\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 TRIM(language) = 'en' AND conversations.conversation_id IN (\n",
    "        SELECT conv.conversation_id\n",
    "        FROM conversations AS conv\n",
    "        WHERE conv.airline LIKE '%Lufthansa%' OR conv.airline LIKE '%British_Airways%' OR conv.airline LIKE '%KLM%' OR conv.airline LIKE '%AirFrance%'\n",
    "    )\n",
    "\"\"\"\n",
    "df_topics1 = pd.read_sql(conv_topics1, connection)\n",
    "df_topics2 = pd.read_sql(conv_topics2, connection)\n",
    "\n",
    "df_topics = pd.concat([df_topics1, df_topics2], ignore_index=True, axis=0)\n",
    "df_topics = df_topics.drop_duplicates()\n",
    "\n",
    "df_topics['time'] = pd.to_datetime(df_topics['timestamp_ms'], unit='ms')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Creates a new dataframe with all the tweets in a certain time period"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mask = (df_topics['time'] >= start_date) & (df_topics['time'] <= end_date)\n",
    "df_topics_time = df_topics.loc[mask].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drops all the columns that we do not need anymore\n",
    "\n",
    "columns_drop = ['text', 'id', 'timestamp_ms', 'time']\n",
    "df_topics_time = df_topics_time.drop(columns=columns_drop)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dataframes per Airline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creates the Dataframe for British Airways\n",
    "\n",
    "conv_ba1 = \"\"\"\n",
    "    SELECT text, id, staff, baggage, delay_and_cancellation, money, timestamp_ms\n",
    "    FROM tweets \n",
    "    WHERE TRIM(language) = 'en' AND mentioned_airlines LIKE '%British_Airways%'\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 TRIM(language) = 'en' AND conversations.conversation_id IN (\n",
    "        SELECT conv.conversation_id\n",
    "        FROM conversations AS conv\n",
    "        WHERE conv.airline LIKE '%British_Airways%'\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['time'] = pd.to_datetime(df_ba['timestamp_ms'], unit='ms')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creates the dataframe for AirFrance\n",
    "\n",
    "conv_af1 = \"\"\"\n",
    "    SELECT text, id, staff, baggage, delay_and_cancellation, money, timestamp_ms\n",
    "    FROM tweets \n",
    "    WHERE TRIM(language) = 'en' AND mentioned_airlines LIKE '%AirFrance%'\n",
    "\"\"\"\n",
    "conv_af2 = \"\"\"\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 TRIM(language) = 'en' AND conversations.conversation_id IN (\n",
    "        SELECT conv.conversation_id\n",
    "        FROM conversations AS conv\n",
    "        WHERE conv.airline LIKE '%AirFrance%'\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['time'] = pd.to_datetime(df_af['timestamp_ms'], unit='ms')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creates the dataframe for KLM\n",
    "\n",
    "conv_klm1 = \"\"\"\n",
    "    SELECT text, id, staff, baggage, delay_and_cancellation, money, timestamp_ms\n",
    "    FROM tweets \n",
    "    WHERE TRIM(language) = 'en' AND mentioned_airlines LIKE '%KLM%'\n",
    "\"\"\"\n",
    "conv_klm2 = \"\"\"\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 TRIM(language) = 'en' AND conversations.conversation_id IN (\n",
    "        SELECT conv.conversation_id\n",
    "        FROM conversations AS conv\n",
    "        WHERE conv.airline LIKE '%KLM%'\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['time'] = pd.to_datetime(df_klm['timestamp_ms'], unit='ms')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creates the dataframe for Lufthansa\n",
    "\n",
    "conv_lh1 = \"\"\"\n",
    "    SELECT text, id, staff, baggage, delay_and_cancellation, money, timestamp_ms\n",
    "    FROM tweets \n",
    "    WHERE TRIM(language) = 'en' AND mentioned_airlines LIKE '%Lufthansa%'\n",
    "\"\"\"\n",
    "conv_lh2 = \"\"\"\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 TRIM(language) = 'en' AND conversations.conversation_id IN (\n",
    "        SELECT conv.conversation_id\n",
    "        FROM conversations AS conv\n",
    "        WHERE conv.airline LIKE '%Lufthansa%'\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['time'] = pd.to_datetime(df_lh['timestamp_ms'], unit='ms')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the masks for the right timeframe\n",
    "\n",
    "mask_ba = (df_ba['time'] >= start_date) & (df_ba['time'] <= end_date)\n",
    "mask_af = (df_af['time'] >= start_date) & (df_af['time'] <= end_date)\n",
    "mask_klm = (df_klm['time'] >= start_date) & (df_klm['time'] <= end_date)\n",
    "mask_lh = (df_lh['time'] >= start_date) & (df_lh['time'] <= end_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Gives dataframes with entries that are in the right timeframe\n",
    "\n",
    "df_ba_time = df_ba.loc[mask_ba]\n",
    "df_af_time = df_af.loc[mask_af]\n",
    "df_klm_time = df_klm.loc[mask_klm]\n",
    "df_lh_time = df_lh.loc[mask_lh]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drops all the columns we do not need anymore\n",
    "\n",
    "df_topics_ba = df_ba_time.drop(columns=columns_drop)\n",
    "df_topics_af = df_af_time.drop(columns=columns_drop)\n",
    "df_topics_klm = df_klm_time.drop(columns=columns_drop)\n",
    "df_topics_lh = df_lh_time.drop(columns=columns_drop)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Plots"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Number of tweets per topic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "counts_topics = df_topics_time.apply(lambda col: col.value_counts().get(1, 0))\n",
    "print(counts_topics)\n",
    "\n",
    "plt.figure(figsize=(10,6))\n",
    "counts_topics.plot(kind='bar', color=sns.color_palette('colorblind'))\n",
    "plt.xlabel('topics')\n",
    "plt.ylabel('number of tweets')\n",
    "plt.title('Number of tweets per topic', weight='bold')\n",
    "plt.xticks(rotation=0)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Number of tweets per airline that have a topic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the number of tweets per airline that have a topic\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()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "combined_counts = pd.DataFrame({\n",
    "    'Airline': ['British_Airways', 'AirFrance', 'KLM', 'Lufthansa'],\n",
    "    'Count': [counts_ba_sum, counts_af_sum, counts_klm_sum, counts_lh_sum]\n",
    "})\n",
    "\n",
    "colors = ['skyblue', 'red', 'orange', 'pink']\n",
    "\n",
    "plt.figure(figsize=(10, 6))\n",
    "combined_counts.plot(kind='bar', x='Airline', y='Count', color=colors, legend=False)\n",
    "plt.xlabel('Airline')\n",
    "plt.ylabel('Number of tweets')\n",
    "plt.title('Number of tweets per airline belonging to a topic', weight = 'bold')\n",
    "plt.xticks(rotation=0)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Number of tweets per topic per Airline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### British Airways"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "colors = ['blue', 'green', 'purple', 'orange']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(10,6))\n",
    "counts_ba.plot(kind='bar', color=colors)\n",
    "plt.xlabel('topics')\n",
    "plt.ylabel('number of tweets')\n",
    "plt.title('Number of tweets per topic for British Airways', weight='bold')\n",
    "plt.xticks(rotation=0)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### AirFrance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(10,6))\n",
    "counts_af.plot(kind='bar', color=colors)\n",
    "plt.xlabel('topics')\n",
    "plt.ylabel('number of tweets')\n",
    "plt.title('Number of tweets per topic for AirFrance', weight='bold')\n",
    "plt.xticks(rotation=0)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### KLM"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(10,6))\n",
    "counts_klm.plot(kind='bar', color=colors)\n",
    "plt.xlabel('topics')\n",
    "plt.ylabel('number of tweets')\n",
    "plt.title('Number of tweets per topic for KLM', weight='bold')\n",
    "plt.xticks(rotation=0)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Lufthansa"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(10,6))\n",
    "counts_lh.plot(kind='bar', color=colors)\n",
    "plt.xlabel('topics')\n",
    "plt.ylabel('number of tweets')\n",
    "plt.title('Number of tweets per topic for Lufthansa', weight='bold')\n",
    "plt.xticks(rotation=0)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Percentage of tweets per topic for all airlines"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get a series object with the percentages for each topic for each airline\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)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Staff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "per_staff_ba = percentages_ba[0]\n",
    "per_staff_af = percentages_af[0]\n",
    "per_staff_klm = percentages_klm[0]\n",
    "per_staff_lh = percentages_lh[0]\n",
    "\n",
    "percentages_staff = pd.DataFrame({\n",
    "    'Airline': ['British Airways', 'AirFrance', 'KLM', 'Lufthansa'],\n",
    "    'Percent_staff': [per_staff_ba, per_staff_af, per_staff_klm, per_staff_lh]\n",
    "})\n",
    "\n",
    "colors_airlines = ['skyblue', 'red', 'orange', 'pink']\n",
    "\n",
    "plt.figure(figsize=(10, 6))\n",
    "percentages_staff.plot(kind='bar', x='Airline', y='Percent_staff', color=colors_airlines, legend=False)\n",
    "plt.xlabel('Airline')\n",
    "plt.ylabel('Percentage of staff tweets (%)')\n",
    "plt.title('Percentage of tweets about staff per airline', weight = 'bold')\n",
    "plt.xticks(rotation=0)\n",
    "plt.show()\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Baggage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "per_bagg_ba = percentages_ba[1]\n",
    "per_bagg_af = percentages_af[1]\n",
    "per_bagg_klm = percentages_klm[1]\n",
    "per_bagg_lh = percentages_lh[1]\n",
    "\n",
    "percentages_bagg = pd.DataFrame({\n",
    "    'Airline': ['British Airways', 'AirFrance', 'KLM', 'Lufthansa'],\n",
    "    'Percent_bagg': [per_bagg_ba, per_bagg_af, per_bagg_klm, per_bagg_lh]\n",
    "})\n",
    "\n",
    "colors_airlines = ['skyblue', 'red', 'orange', 'pink']\n",
    "\n",
    "plt.figure(figsize=(10, 6))\n",
    "percentages_bagg.plot(kind='bar', x='Airline', y='Percent_bagg', color=colors_airlines, legend=False)\n",
    "plt.xlabel('Airline')\n",
    "plt.ylabel('Percentage of baggage tweets (%)')\n",
    "plt.title('Percentage of tweets about baggage per airline', weight = 'bold')\n",
    "plt.xticks(rotation=0)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Delay and Cancellation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "per_delay_ba = percentages_ba[2]\n",
    "per_delay_af = percentages_af[2]\n",
    "per_delay_klm = percentages_klm[2]\n",
    "per_delay_lh = percentages_lh[2]\n",
    "\n",
    "percentages_delay = pd.DataFrame({\n",
    "    'Airline': ['British Airways', 'AirFrance', 'KLM', 'Lufthansa'],\n",
    "    'Percent_delay': [per_delay_ba, per_delay_af, per_delay_klm, per_delay_lh]\n",
    "})\n",
    "\n",
    "colors_airlines = ['skyblue', 'red', 'orange', 'pink']\n",
    "\n",
    "plt.figure(figsize=(10, 6))\n",
    "percentages_delay.plot(kind='bar', x='Airline', y='Percent_delay', color=colors_airlines, legend=False)\n",
    "plt.xlabel('Airline')\n",
    "plt.ylabel('Percentage of delay and cancellation tweets (%)')\n",
    "plt.title('Percentage of tweets about delay and cancellations per airline', weight = 'bold')\n",
    "plt.xticks(rotation=0)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Money"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "per_money_ba = percentages_ba[3]\n",
    "per_money_af = percentages_af[3]\n",
    "per_money_klm = percentages_klm[3]\n",
    "per_money_lh = percentages_lh[3]\n",
    "\n",
    "percentages_money = pd.DataFrame({\n",
    "    'Airline': ['British Airways', 'AirFrance', 'KLM', 'Lufthansa'],\n",
    "    'Percent_money': [per_money_ba, per_money_af, per_money_klm, per_money_lh]\n",
    "})\n",
    "\n",
    "colors_airlines = ['skyblue', 'red', 'orange', 'pink']\n",
    "\n",
    "plt.figure(figsize=(10, 6))\n",
    "percentages_money.plot(kind='bar', x='Airline', y='Percent_money', color=colors_airlines, legend=False)\n",
    "plt.xlabel('Airline')\n",
    "plt.ylabel('Percentage of money tweets (%)')\n",
    "plt.title('Percentage of tweets about money per airline', weight = 'bold')\n",
    "plt.xticks(rotation=0)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Stacked bar chart topics and airlines"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "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",
    "\n",
    "percentages.set_index('Airline').plot(kind='bar', stacked=True, figsize=(10, 6), color=sns.color_palette('colorblind'))\n",
    "\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": "markdown",
   "metadata": {},
   "source": [
    "# Number of cooccuring topics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Number of tweets that contain two topics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "count_bdc = df_topics_time[(df_topics_time['baggage'] == 1) & (df_topics_time['delay_and_cancellation'] == 1) & (df_topics_time['money'] == 0) & (df_topics_time['staff'] == 0)].count()[0]\n",
    "count_bm = df_topics_time[(df_topics_time['baggage'] == 1) & (df_topics_time['money'] == 1) & (df_topics_time['staff'] == 0) & (df_topics_time['delay_and_cancellation'] == 0)].count()[0]\n",
    "count_bs = df_topics_time[(df_topics_time['baggage'] == 1) & (df_topics_time['staff'] == 1) & (df_topics_time['money'] == 0) & (df_topics_time['delay_and_cancellation'] == 0)].count()[0]\n",
    "count_mdc = df_topics_time[(df_topics_time['money'] == 1) & (df_topics_time['delay_and_cancellation'] == 1) & (df_topics_time['staff'] == 0) & (df_topics_time['baggage'] == 0)].count()[0]\n",
    "count_ms = df_topics_time[(df_topics_time['money'] == 1) & (df_topics_time['staff'] == 1) & (df_topics_time['delay_and_cancellation'] == 0) & (df_topics_time['baggage'] == 0)].count()[0]\n",
    "count_sdc = df_topics_time[(df_topics_time['delay_and_cancellation'] == 1) & (df_topics_time['staff'] == 1) & (df_topics_time['money'] == 0) & (df_topics_time['baggage'] == 0)].count()[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "counts_two_topics = pd.DataFrame({\n",
    "    'Topics': ['baggage - delay and cancellation','baggage - money', 'baggage - staff', 'money - delay and cancellation', 'money - staff', 'staff - delay and cancellation'],\n",
    "    'Counts': [count_bdc, count_bm, count_bs, count_mdc, count_ms, count_sdc]\n",
    "})\n",
    "\n",
    "plt.figure(figsize=(20, 6))\n",
    "counts_two_topics.plot(kind='bar', x='Topics', y='Counts', color=sns.color_palette('colorblind'), legend=False)\n",
    "plt.xlabel('Combination of topics')\n",
    "plt.ylabel('Number of tweets')\n",
    "plt.title('Number of tweets per pair of topics', weight = 'bold')\n",
    "#plt.xticks(rotation=0)\n",
    "plt.show()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Number of tweets about three topics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "count_bdcs = df_topics_time[(df_topics_time['baggage'] == 1) & (df_topics_time['delay_and_cancellation'] == 1) & (df_topics_time['staff'] == 1) & (df_topics_time['money'] == 0)].count()[0]\n",
    "count_bdcm = df_topics_time[(df_topics_time['baggage'] == 1) & (df_topics_time['delay_and_cancellation'] == 1)& (df_topics_time['money'] == 1) & (df_topics_time['staff'] == 0)].count()[0]\n",
    "count_bsm = df_topics_time[(df_topics_time['baggage'] == 1) & (df_topics_time['staff'] == 1) & (df_topics_time['money'] == 1) & (df_topics_time['delay_and_cancellation'] == 0)].count()[0]\n",
    "count_dcsm = df_topics_time[(df_topics_time['baggage'] == 0) & (df_topics_time['delay_and_cancellation'] == 1) & (df_topics_time['staff'] == 1) & (df_topics_time['money'] == 1)].count()[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "counts_three_topics = pd.DataFrame({\n",
    "    'Topics': ['baggage - delay and cancellation - money','baggage - money - staff', 'baggage - staff - delay and cancellation', 'money - delay and cancellation - staff'],\n",
    "    'Counts': [count_bdcm, count_bsm, count_bdcs, count_dcsm]\n",
    "})\n",
    "\n",
    "plt.figure(figsize=(20, 6))\n",
    "counts_three_topics.plot(kind='bar', x='Topics', y='Counts', color=sns.color_palette('colorblind'), legend=False)\n",
    "plt.xlabel('Combination of topics')\n",
    "plt.ylabel('Number of tweets')\n",
    "plt.title('Number of tweets per triplet of topics', weight = 'bold')\n",
    "#plt.xticks(rotation=0)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Number of tweets about all four topics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "count_all = df_topics_time[(df_topics_time['baggage'] == 1) & (df_topics_time['delay_and_cancellation'] == 1) & (df_topics_time['money'] == 1) & (df_topics_time['staff'] == 1)].count()[0]\n",
    "print(count_all)"
   ]
  }
 ],
 "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
}