01_analyze_comments.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Analyze classified comments\n",
    "\n",
    "After the first part of the project, to find suitable categories and classify all comments according to the found cateogries, we can start analyzing the classified comments.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle\n",
    "\n",
    "import data\n",
    "import prompt\n",
    "from dotenv import load_dotenv\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import openai\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "load_dotenv()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = data.load_data(\"Umfrage DE submissions 2024-07-31 00_25.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df.Stadtteil != \"SPAM\"]\n",
    "df = df[(df[\"Positive\"].str.len() > 7) | (df[\"Negative\"].str.len() > 7)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "client = openai.Client()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Process categorized comments\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Actually, before we can analyze the comments, we need to process them and merge them with the original dataset so that each survey answer has the detected categories.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"artifacts/pos_results.pkl\", \"rb\") as f:\n",
    "    pos_comments = pickle.load(f)\n",
    "\n",
    "with open(\"artifacts/neg_results.pkl\", \"rb\") as f:\n",
    "    neg_comments = pickle.load(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "pos_comments = [c if c.startswith(\"[\") else \"[\" + c + \"]\" for _, c in pos_comments]\n",
    "neg_comments = [c if c.startswith(\"[\") else \"[\" + c + \"]\" for _, c in neg_comments]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "pos_comments = [c if c != \"[]\" else \"['UNCLEAR']\" for c in pos_comments]\n",
    "neg_comments = [c if c != \"[]\" else \"['UNCLEAR']\" for c in neg_comments]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pos_comments[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "pos_comments_list = []\n",
    "for comment in pos_comments:\n",
    "    cats = []\n",
    "    if \"UNCLEAR\" in comment:\n",
    "        cats.append(\"UNCLEAR\")\n",
    "    else:\n",
    "        for cat in data.final_categories:\n",
    "            if cat in comment:\n",
    "                cats.append(cat)\n",
    "    pos_comments_list.append(cats)\n",
    "\n",
    "neg_comments_list = []\n",
    "for comment in neg_comments:\n",
    "    cats = []\n",
    "    if \"UNCLEAR\" in comment:\n",
    "        cats.append(\"UNCLEAR\")\n",
    "    else:\n",
    "        for cat in data.final_categories:\n",
    "            if cat in comment:\n",
    "                cats.append(cat)\n",
    "    neg_comments_list.append(cats)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(pos_comments_list), len(neg_comments_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for comment, cat in zip(df.Positive.head(20), pos_comments_list[:20]):\n",
    "    print(\"Comment:\", comment)\n",
    "    print(\"Categories:\", \",\".join(cat))\n",
    "    print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_positive = df[[\"Date\", \"Stadtteil\", \"Region\", \"Positive\"]]\n",
    "df_negative = df[[\"Date\", \"Stadtteil\", \"Region\", \"Negative\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_positive[\"category\"] = [\n",
    "    list(\n",
    "        map(\n",
    "            lambda x: data.category_mapping.get(x) if x in data.category_mapping else x,\n",
    "            l,\n",
    "        )\n",
    "    )\n",
    "    for l in pos_comments_list\n",
    "]\n",
    "df_positive[\"no_categories\"] = df_positive.category.apply(len)\n",
    "# df_positive[\"spam\"] = df_positive[\"category\"].apply(lambda x: \"SPAM\" in x)\n",
    "df_positive[\"unclear\"] = df_positive[\"category\"].apply(lambda x: \"UNCLEAR\" in x)\n",
    "\n",
    "df_negative[\"category\"] = [\n",
    "    list(\n",
    "        map(\n",
    "            lambda x: data.category_mapping.get(x) if x in data.category_mapping else x,\n",
    "            l,\n",
    "        )\n",
    "    )\n",
    "    for l in neg_comments_list\n",
    "]\n",
    "df_negative[\"no_categories\"] = df_negative.category.apply(len)\n",
    "# df_negative[\"spam\"] = df_negative[\"category\"].apply(lambda x: \"SPAM\" in x)\n",
    "df_negative[\"unclear\"] = df_negative[\"category\"].apply(lambda x: \"UNCLEAR\" in x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_positive.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_negative.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## First look at comments\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_positive.spam.sum() / len(df_positive),\n",
    "df_positive.unclear.sum() / len(df_positive)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_negative.spam.sum() / len(df_positive),\n",
    "df_negative.unclear.sum() / len(df_negative)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for x in df_positive[df_positive.unclear][[\"Positive\", \"category\"]].itertuples(\n",
    "    index=False\n",
    "):\n",
    "    print(x.Positive)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for x in df_negative[df_negative.unclear][[\"Negative\", \"category\"]].itertuples(\n",
    "    index=False\n",
    "):\n",
    "    print(x.Negative)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_positive.no_categories.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_negative.no_categories.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for x in df_positive[df_positive.no_categories >= 4].Positive:\n",
    "    print(x)\n",
    "    print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for x in df_negative[df_negative.no_categories >= 4].Negative:\n",
    "    print(x)\n",
    "    print()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Visualizations\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pure_cats = df_positive.category.explode()\n",
    "pure_cats = pure_cats.loc[lambda x: x != \"UNCLEAR\"]\n",
    "pure_cats.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pure_cats = df_positive.category.explode()\n",
    "pure_cats = pure_cats.loc[lambda x: x != \"UNCLEAR\"]\n",
    "pure_cats.value_counts()\n",
    "\n",
    "ax = pure_cats.value_counts(ascending=True).plot(\n",
    "    kind=\"barh\", width=0.8, color=\"green\", legend=False, alpha=0.8, edgecolor=\"k\"\n",
    ")\n",
    "plt.title(\"Positive Aspekte über das Familienleben mit Baby in Frankfurt\")\n",
    "plt.ylabel(\"\")\n",
    "plt.xlim((0, 450))\n",
    "ax.bar_label(ax.containers[0], padding=3)\n",
    "plt.savefig(\"output/Positive_Aspekte_gesamt.png\", bbox_inches=\"tight\")\n",
    "plt.savefig(\"output/Positive_Aspekte_gesamt.pdf\", bbox_inches=\"tight\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pure_cats = df_negative.category.explode()\n",
    "pure_cats = pure_cats.loc[lambda x: x != \"UNCLEAR\"]\n",
    "pure_cats.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pure_cats = df_negative.category.explode()\n",
    "pure_cats = pure_cats.loc[lambda x: x != \"UNCLEAR\"]\n",
    "pure_cats.value_counts()\n",
    "\n",
    "ax = pure_cats.value_counts(ascending=True).plot(\n",
    "    kind=\"barh\", width=0.8, color=\"darkred\", legend=False, alpha=0.8, edgecolor=\"k\"\n",
    ")\n",
    "\n",
    "plt.title(\"Negative Aspekte über das Familienleben mit Baby in Frankfurt\")\n",
    "plt.ylabel(\"\")\n",
    "plt.xlim((0, 320))\n",
    "ax.bar_label(ax.containers[0], padding=3)\n",
    "plt.savefig(\"output/Negative_Aspekte_gesamt.png\", bbox_inches=\"tight\")\n",
    "plt.savefig(\"output/Negative_Aspekte_gesamt.pdf\", bbox_inches=\"tight\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Regional analysis\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for region in df.Region.dropna().unique():\n",
    "    for cat, df_reg in [\n",
    "        (\"positive\", df_positive.query(\"Region == @region\")),\n",
    "        (\"negative\", df_negative.query(\"Region == @region\")),\n",
    "    ]:\n",
    "        plt.figure(figsize=(10, 6))\n",
    "        pure_cats = df_reg.category.explode()\n",
    "        pure_cats = pure_cats.loc[lambda x: x != \"UNCLEAR\"]\n",
    "        ax = pure_cats.value_counts(ascending=True).plot(\n",
    "            kind=\"barh\",\n",
    "            width=0.8,\n",
    "            color=\"darkred\" if cat == \"negative\" else \"green\",\n",
    "            legend=False,\n",
    "        )\n",
    "        # plt.title(f\"Categories of {cat} comments about living in Frankfurt {region} with a baby\")\n",
    "        plt.title(\n",
    "            f\"{cat.title()} Aspekte über das Familienleben mit Baby in Frankfurt {region}\"\n",
    "        )\n",
    "        plt.ylabel(\"\")\n",
    "        plt.xlim((0, pure_cats.value_counts().values[0] + 10))\n",
    "        ax.bar_label(ax.containers[0], padding=3)\n",
    "        plt.savefig(f\"output/{region}_{cat}.png\", bbox_inches=\"tight\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_positive"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_positive.explode(\"category\").query(\"category != 'UNCLEAR'\").groupby(\n",
    "    [\"Region\", \"category\"]\n",
    ").count().groupby(level=0, group_keys=False).apply(\n",
    "    lambda x: x.sort_values(\"Positive\", ascending=False).head(3)\n",
    ")[[\"Positive\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_positive.explode(\"category\").query(\"category != 'UNCLEAR'\").groupby(\n",
    "    [\"Region\", \"category\"]\n",
    ").count().groupby(level=0, group_keys=False).apply(\n",
    "    lambda x: x.sort_values(\"Positive\", ascending=False).head(3)\n",
    ")[[\"Positive\"]].plot(kind=\"bar\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_negative.explode(\"category\").query(\"category != 'UNCLEAR'\").groupby(\n",
    "    [\"Region\", \"category\"]\n",
    ").count().groupby(level=0, group_keys=False).apply(\n",
    "    lambda x: x.sort_values(\"Negative\", ascending=False).head(3)\n",
    ")[[\"Negative\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analyze categories\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_negative[\n",
    "    df_negative.category.apply(\n",
    "        lambda x: data.category_mapping[\"Childcare and Early Education Resources\"] in x\n",
    "    )\n",
    "].Negative"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Warning! This cell will cost money when using OpenAI's API\n",
    "model = \"gpt-4o-mini\"\n",
    "temperature = 0\n",
    "\n",
    "pure_cats = df_negative.category.explode()\n",
    "pure_cats = pure_cats.loc[lambda x: x != \"UNCLEAR\"]\n",
    "pure_cats = pure_cats.value_counts()\n",
    "for cat in pure_cats.index:\n",
    "    comments = df_negative[\n",
    "        (df_negative.category.apply(lambda x: cat in x))\n",
    "        & (df_negative.no_categories == 1)\n",
    "    ].Negative\n",
    "\n",
    "    response = client.chat.completions.create(\n",
    "        model=model,\n",
    "        # response_format={\"type\": \"json_object\"},\n",
    "        messages=[\n",
    "            {\n",
    "                \"role\": \"system\",\n",
    "                \"content\": prompt.SYSTEM_PROMPT_SUMMARIZE_TOPICS_PER_CAT,\n",
    "            },\n",
    "            {\n",
    "                \"role\": \"user\",\n",
    "                \"content\": prompt.USER_PROMPT_SUMMARIZE_TOPICS_PER_CAT.format(\n",
    "                    comments=\"\\n\".join(\n",
    "                        f\"{i}. {comment}\" for i, comment in enumerate(comments)\n",
    "                    ),\n",
    "                ),\n",
    "            },\n",
    "        ],\n",
    "        temperature=temperature,\n",
    "    )\n",
    "\n",
    "    print(response.choices[0].finish_reason)\n",
    "    print(\"Category: \", cat)\n",
    "    print(response.choices[0].message.content)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Antworten nach Kategorien\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel(\"output/Antworten_mit_Regionen.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# store your dataframes in a dict, where the key is the sheet name you want\n",
    "frames = {\"Positive\": df_positive, \"Negative\": df_negative}\n",
    "\n",
    "with pd.ExcelWriter(\n",
    "    \"output/Antworten_mit_Kategorien.xlsx\", engine=\"xlsxwriter\"\n",
    ") as writer:\n",
    "    # now loop thru and put each on a specific sheet\n",
    "    for sheet, frame in frames.items():  # .use .items for python 3.X\n",
    "        frame.to_excel(writer, sheet_name=sheet)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Stadtteil.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "city_parts = []\n",
    "categories = []\n",
    "sentiments = []\n",
    "values = []\n",
    "\n",
    "\n",
    "for city_part in df.Stadtteil.unique():\n",
    "    for cat in data.final_categories_de:\n",
    "        for sentiment in [\"positive\", \"negative\"]:\n",
    "            city_parts.append(city_part)\n",
    "            categories.append(cat)\n",
    "            sentiments.append(sentiment)\n",
    "            if sentiment == \"positive\":\n",
    "                df_ = df_positive\n",
    "            else:\n",
    "                df_ = df_negative\n",
    "                \n",
    "            values.append(\n",
    "                df_[\n",
    "                    df_positive.Stadtteil == city_part\n",
    "                ].category.explode().value_counts().get(cat, 0)\n",
    "            )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pos_neg_cat_per_city_part = pd.DataFrame({'Stadtteil': city_parts, 'Kategorie': categories, 'Sentiment': sentiments, 'Anzahl': values})\n",
    "df_pos_neg_cat_per_city_part"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pos_neg_cat_per_city_part.pivot(index='Stadtteil', columns=['Kategorie', 'Sentiment'], values='Anzahl').fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pos_neg_cat_per_city_part.pivot(index='Stadtteil', columns=['Kategorie', 'Sentiment'], values='Anzahl').fillna(0).to_excel(\"output/Pos_Neg_Kategorien_pro_Stadtteil_wide.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pos_neg_cat_per_city_part.to_excel(\n",
    "    \"output/Pos_Neg_Kategorien_pro_Stadtteil_tidy.xlsx\"\n",
    ")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}