BERT_GPT_merge_compare_clean.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1e792cb0",
   "metadata": {},
   "source": [
    "# File operations for comparing BERT and GPT results (initial stages)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "144edc73",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Read the CSV files\n",
    "twitter_df = pd.read_csv(\"Ingeborg-Bachmann-Preis_Twitter_Decryption.csv\", sep=\";\")\n",
    "outputs_df = pd.read_csv(\"twitter_lore2023/Ingeborg-Bachmann-Preis_Twitter_2017.csv\")\n",
    "\n",
    "# Merge the dataframes on the Filename column\n",
    "merged_df = pd.merge(twitter_df, outputs_df, on=\"Filename\")\n",
    "\n",
    "# Group by Filename and aggregate values into a dictionary\n",
    "grouped_df = merged_df.groupby(\"Filename\").agg({\n",
    "    \"Aspect\": list,\n",
    "    \"Polarity\": list,\n",
    "    \"Category_Coarse\": list,\n",
    "    \"Category_Fine\": list\n",
    "}).reset_index()\n",
    "\n",
    "# Combine the aggregated values into a single JSON column\n",
    "grouped_df[\"results\"] = grouped_df.apply(lambda x: {\n",
    "    \"Aspect\": x[\"Aspect\"],\n",
    "    \"Polarity\": x[\"Polarity\"],\n",
    "    \"Category_Coarse\": x[\"Category_Coarse\"],\n",
    "    \"Category_Fine\": x[\"Category_Fine\"]\n",
    "}, axis=1)\n",
    "\n",
    "# Drop the original columns\n",
    "grouped_df.drop(columns=[\"Aspect\", \"Polarity\", \"Category_Coarse\", \"Category_Fine\"], inplace=True)\n",
    "\n",
    "# Print the resulting dataframe\n",
    "print(grouped_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6352a8bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set the display option to show the full text in columns\n",
    "pd.set_option(\"display.max_colwidth\", None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "fda683c5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Filename</th>\n",
       "      <th>Caption</th>\n",
       "      <th>TweetID</th>\n",
       "      <th>results</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TDDL_Twitter_2017_1</td>\n",
       "      <td>I liked a @YouTube video http://youtu.be/2AAbk...</td>\n",
       "      <td>8.164146e+17</td>\n",
       "      <td>[{'aspect category': 'META', 'aspect term': 'M...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Filename                                            Caption  \\\n",
       "0  TDDL_Twitter_2017_1  I liked a @YouTube video http://youtu.be/2AAbk...   \n",
       "\n",
       "        TweetID                                            results  \n",
       "0  8.164146e+17  [{'aspect category': 'META', 'aspect term': 'M...  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#definitiefwerkt13:26 op 23/5/2023\n",
    "import pandas as pd\n",
    "import json\n",
    "\n",
    "# Read the CSV files with semicolon separator\n",
    "twitter_df = pd.read_csv(\"Ingeborg-Bachmann-Preis_Twitter_Decryption.csv\", sep=\";\")\n",
    "outputs_df = pd.read_csv(\"twitter_lore2023/Ingeborg-Bachmann-Preis_Twitter_2017.csv\")\n",
    "\n",
    "# Merge the dataframes on the Filename column\n",
    "merged_df = pd.merge(twitter_df, outputs_df, on=\"Filename\")\n",
    "\n",
    "# Group by Filename and aggregate values into a dictionary\n",
    "grouped_df = merged_df.groupby(\"Filename\").agg({\n",
    "    \"Caption\": lambda x: x.iloc[0],  # Retrieve the first Caption for each Filename\n",
    "    \"Aspect\": list,\n",
    "    \"Polarity\": list,\n",
    "    \"Category_Coarse\": list,\n",
    "    \"Category_Fine\": list,\n",
    "    \"TweetID\": lambda x: x.iloc[0]  # Retrieve the first TweetID for each Filename\n",
    "}).reset_index()\n",
    "\n",
    "# Read the CSV file\n",
    "df = pd.read_csv(\"twitter_lore2023/Ingeborg-Bachmann-Preis_Twitter_2017.csv\")\n",
    "\n",
    "# Group by Filename, Caption, and aggregate values into a dictionary\n",
    "grouped_df_new = df.groupby([\"Filename\", \"Caption\"]).agg({\n",
    "    \"Aspect\": list,\n",
    "    \"Polarity\": list,\n",
    "    \"Category_Coarse\": list,\n",
    "    \"Category_Fine\": list\n",
    "}).reset_index()\n",
    "\n",
    "# Create a list to store the JSON objects\n",
    "json_list = []\n",
    "\n",
    "# Iterate over the rows of grouped_df_new and create JSON objects for matching Filenames\n",
    "for _, row in grouped_df_new.iterrows():\n",
    "    filename = row[\"Filename\"]\n",
    "    caption = row[\"Caption\"]\n",
    "    if filename in grouped_df[\"Filename\"].values:\n",
    "        aspects = row[\"Aspect\"]\n",
    "        polarities = row[\"Polarity\"]\n",
    "        categories_coarse = row[\"Category_Coarse\"]\n",
    "        categories_fine = row[\"Category_Fine\"]\n",
    "        tweet_id = grouped_df.loc[grouped_df[\"Filename\"] == filename, \"TweetID\"].iloc[0]  # Get the TweetID for the matching Filename\n",
    "\n",
    "        # Iterate over the lists and create JSON objects\n",
    "        for aspect, polarity, category_coarse, category_fine in zip(aspects, polarities, categories_coarse, categories_fine):\n",
    "            json_obj = {\n",
    "                \"aspect category\": category_coarse,\n",
    "                \"aspect term\": aspect,\n",
    "                \"opinion target\": caption,\n",
    "                \"opinion expression\": polarity,\n",
    "                \"sentiment\": polarity.lower(),\n",
    "                \"confidence score\": \"-1\"\n",
    "            }\n",
    "            json_list.append(json_obj)\n",
    "\n",
    "# Convert the list of JSON objects to JSON string\n",
    "json_str = json.dumps(json_list)\n",
    "\n",
    "# Print the JSON string\n",
    "#print(json_str)\n",
    "\n",
    "grouped_df[\"results\"] = grouped_df.apply(lambda x: [\n",
    "    {\n",
    "        \"aspect category\": x[\"Category_Coarse\"][i],\n",
    "        \"aspect term\": x[\"Category_Fine\"][i],\n",
    "        \"aspect content\": x[\"Aspect\"][i],\n",
    "       \"opinion target\": x[\"Caption\"],\n",
    "#        \"opinion expression\": x[\"Polarity\"][i],\n",
    "        \"sentiment\": x[\"Polarity\"][i].lower(),\n",
    "#        \"confidence score\": x[\"Confidence_Score\"][i]  # Extract the confidence score from the corresponding column\n",
    "    }\n",
    "    for i in range(len(x[\"Aspect\"]))\n",
    "], axis=1)\n",
    "\n",
    "# Include the TweetID as a separate column in grouped_df\n",
    "grouped_df.drop(columns=[\"Aspect\", \"Polarity\", \"Category_Coarse\", \"Category_Fine\"], inplace=True)\n",
    "# Include the TweetID as a separate column in grouped_df\n",
    "grouped_df[\"TweetID\"] = grouped_df[\"Filename\"].map(dict(zip(twitter_df[\"Filename\"], twitter_df[\"TweetID\"])))\n",
    "\n",
    "# Display the first row of the grouped_df dataframe\n",
    "display(grouped_df.head(1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ebf923b3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Filename</th>\n",
       "      <th>Caption</th>\n",
       "      <th>TweetID</th>\n",
       "      <th>results</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TDDL_Twitter_2017_1</td>\n",
       "      <td>I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016</td>\n",
       "      <td>8.164146e+17</td>\n",
       "      <td>[{'aspect category': 'META', 'aspect term': 'META_Technology_Social-Media', 'aspect content': 'YouTube', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'META', 'aspect term': 'META_Technology_Social-Media', 'aspect content': 'video', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'META', 'aspect term': 'META_Technology_Social-Media', 'aspect content': 'http', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'CONTENDER', 'aspect term': 'CONTENDER_General', 'aspect content': 'Stefanie', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'CONTENDER', 'aspect term': 'CONTENDER_General', 'aspect content': ' Sargnagel', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'META', 'aspect term': 'CONTENDER_General', 'aspect content': 'Videoporträt', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'META', 'aspect term': 'META_Videoportrait', 'aspect content': ' Bachmannpreis', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Filename  \\\n",
       "0  TDDL_Twitter_2017_1   \n",
       "\n",
       "                                                                                                       Caption  \\\n",
       "0  I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016   \n",
       "\n",
       "        TweetID  \\\n",
       "0  8.164146e+17   \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           results  \n",
       "0  [{'aspect category': 'META', 'aspect term': 'META_Technology_Social-Media', 'aspect content': 'YouTube', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'META', 'aspect term': 'META_Technology_Social-Media', 'aspect content': 'video', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'META', 'aspect term': 'META_Technology_Social-Media', 'aspect content': 'http', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'CONTENDER', 'aspect term': 'CONTENDER_General', 'aspect content': 'Stefanie', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'CONTENDER', 'aspect term': 'CONTENDER_General', 'aspect content': ' Sargnagel', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'META', 'aspect term': 'CONTENDER_General', 'aspect content': 'Videoporträt', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'META', 'aspect term': 'META_Videoportrait', 'aspect content': ' Bachmannpreis', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}]  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(grouped_df.head(1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "1c4d2aa3",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-5-6d6457e89f19>:45: FutureWarning: The default value of regex will change from True to False in a future version. In addition, single character regular expressions will *not* be treated as literal strings when regex=True.\n",
      "  feedbacks_df[\"analysis\"] = feedbacks_df[\"analysis\"].str.replace(\"\\\\\", \"\\\\\\\\\")\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "JSON decode error in row 156: Expecting ',' delimiter: line 1 column 932 (char 931)\n",
      "JSON decode error in row 171: Expecting ',' delimiter: line 1 column 92 (char 91)\n",
      "JSON decode error in row 203: Expecting ',' delimiter: line 1 column 129 (char 128)\n",
      "JSON decode error in row 297: Expecting ',' delimiter: line 1 column 207 (char 206)\n",
      "JSON decode error in row 325: Expecting ',' delimiter: line 1 column 295 (char 294)\n",
      "JSON decode error in row 424: Expecting ',' delimiter: line 1 column 143 (char 142)\n",
      "JSON decode error in row 426: Expecting ',' delimiter: line 1 column 163 (char 162)\n",
      "JSON decode error in row 474: Expecting ',' delimiter: line 1 column 111 (char 110)\n",
      "JSON decode error in row 523: Expecting ',' delimiter: line 1 column 126 (char 125)\n",
      "JSON decode error in row 538: Expecting ',' delimiter: line 1 column 323 (char 322)\n",
      "JSON decode error in row 639: Expecting ',' delimiter: line 1 column 129 (char 128)\n",
      "JSON decode error in row 643: Expecting ',' delimiter: line 1 column 107 (char 106)\n",
      "JSON decode error in row 787: Expecting ',' delimiter: line 1 column 75 (char 74)\n",
      "JSON decode error in row 795: Expecting ',' delimiter: line 1 column 142 (char 141)\n",
      "JSON decode error in row 826: Expecting ',' delimiter: line 1 column 142 (char 141)\n",
      "JSON decode error in row 844: Expecting ',' delimiter: line 1 column 114 (char 113)\n",
      "JSON decode error in row 934: Expecting ',' delimiter: line 1 column 274 (char 273)\n",
      "JSON decode error in row 970: Expecting ',' delimiter: line 1 column 164 (char 163)\n",
      "JSON decode error in row 991: Expecting ',' delimiter: line 1 column 311 (char 310)\n",
      "JSON decode error in row 996: Expecting ',' delimiter: line 1 column 118 (char 117)\n",
      "JSON decode error in row 1065: Expecting ',' delimiter: line 1 column 128 (char 127)\n",
      "JSON decode error in row 1158: Expecting ',' delimiter: line 1 column 124 (char 123)\n",
      "JSON decode error in row 1200: Expecting ',' delimiter: line 1 column 112 (char 111)\n",
      "JSON decode error in row 1254: Expecting ',' delimiter: line 1 column 316 (char 315)\n",
      "JSON decode error in row 1377: Expecting ',' delimiter: line 1 column 282 (char 281)\n",
      "JSON decode error in row 1390: Expecting ',' delimiter: line 1 column 271 (char 270)\n",
      "JSON decode error in row 1412: Expecting ',' delimiter: line 1 column 137 (char 136)\n",
      "JSON decode error in row 1551: Expecting ',' delimiter: line 1 column 145 (char 144)\n",
      "JSON decode error in row 1621: Expecting ',' delimiter: line 1 column 474 (char 473)\n",
      "JSON decode error in row 1707: Expecting ',' delimiter: line 1 column 303 (char 302)\n",
      "JSON decode error in row 1736: Expecting ',' delimiter: line 1 column 320 (char 319)\n",
      "JSON decode error in row 1752: Expecting ',' delimiter: line 1 column 298 (char 297)\n",
      "JSON decode error in row 1759: Expecting ',' delimiter: line 1 column 90 (char 89)\n",
      "JSON decode error in row 1785: Expecting ',' delimiter: line 1 column 148 (char 147)\n",
      "JSON decode error in row 1913: Expecting ',' delimiter: line 1 column 114 (char 113)\n",
      "JSON decode error in row 1915: Expecting ',' delimiter: line 1 column 103 (char 102)\n",
      "JSON decode error in row 1975: Expecting ',' delimiter: line 1 column 101 (char 100)\n",
      "JSON decode error in row 2000: Expecting ',' delimiter: line 1 column 119 (char 118)\n",
      "JSON decode error in row 2021: Expecting ',' delimiter: line 1 column 126 (char 125)\n",
      "JSON decode error in row 2035: Expecting ',' delimiter: line 1 column 113 (char 112)\n",
      "JSON decode error in row 2125: Expecting ',' delimiter: line 1 column 130 (char 129)\n",
      "JSON decode error in row 2173: Expecting ',' delimiter: line 1 column 105 (char 104)\n",
      "JSON decode error in row 2333: Expecting ',' delimiter: line 1 column 80 (char 79)\n",
      "JSON decode error in row 2417: Expecting ',' delimiter: line 1 column 132 (char 131)\n",
      "JSON decode error in row 2678: Expecting ',' delimiter: line 1 column 120 (char 119)\n",
      "JSON decode error in row 2708: Expecting ',' delimiter: line 1 column 145 (char 144)\n",
      "JSON decode error in row 2803: Expecting ',' delimiter: line 1 column 77 (char 76)\n",
      "JSON decode error in row 2894: Expecting ',' delimiter: line 1 column 481 (char 480)\n",
      "JSON decode error in row 2948: Expecting ',' delimiter: line 1 column 152 (char 151)\n",
      "JSON decode error in row 2977: Expecting ',' delimiter: line 1 column 90 (char 89)\n",
      "JSON decode error in row 2980: Expecting ',' delimiter: line 1 column 143 (char 142)\n",
      "JSON decode error in row 3040: Expecting ',' delimiter: line 1 column 353 (char 352)\n",
      "JSON decode error in row 3084: Expecting ',' delimiter: line 1 column 84 (char 83)\n",
      "JSON decode error in row 3118: Expecting ',' delimiter: line 1 column 136 (char 135)\n",
      "JSON decode error in row 3163: Expecting ',' delimiter: line 1 column 80 (char 79)\n",
      "JSON decode error in row 3353: Expecting ',' delimiter: line 1 column 113 (char 112)\n",
      "JSON decode error in row 3415: Expecting ',' delimiter: line 1 column 81 (char 80)\n",
      "JSON decode error in row 3458: Expecting ',' delimiter: line 1 column 120 (char 119)\n",
      "JSON decode error in row 3474: Expecting ',' delimiter: line 1 column 110 (char 109)\n",
      "JSON decode error in row 3504: Expecting ',' delimiter: line 1 column 474 (char 473)\n",
      "JSON decode error in row 3592: Expecting ',' delimiter: line 1 column 139 (char 138)\n",
      "JSON decode error in row 3631: Expecting ',' delimiter: line 1 column 141 (char 140)\n",
      "JSON decode error in row 3645: Expecting ',' delimiter: line 1 column 153 (char 152)\n",
      "JSON decode error in row 3737: Expecting ',' delimiter: line 1 column 81 (char 80)\n",
      "JSON decode error in row 3804: Expecting ',' delimiter: line 1 column 549 (char 548)\n",
      "JSON decode error in row 3882: Expecting ',' delimiter: line 1 column 133 (char 132)\n",
      "JSON decode error in row 4096: Expecting ',' delimiter: line 1 column 132 (char 131)\n",
      "JSON decode error in row 4105: Expecting ',' delimiter: line 1 column 143 (char 142)\n",
      "JSON decode error in row 4115: Expecting ',' delimiter: line 1 column 113 (char 112)\n",
      "JSON decode error in row 4225: Expecting ',' delimiter: line 1 column 108 (char 107)\n",
      "JSON decode error in row 4360: Expecting ',' delimiter: line 1 column 329 (char 328)\n",
      "JSON decode error in row 4416: Expecting ',' delimiter: line 1 column 97 (char 96)\n",
      "JSON decode error in row 4541: Expecting ',' delimiter: line 1 column 110 (char 109)\n",
      "JSON decode error in row 4546: Expecting ',' delimiter: line 1 column 322 (char 321)\n",
      "JSON decode error in row 4658: Expecting ',' delimiter: line 1 column 110 (char 109)\n",
      "JSON decode error in row 4795: Expecting ',' delimiter: line 1 column 345 (char 344)\n",
      "JSON decode error in row 4894: Expecting ',' delimiter: line 1 column 358 (char 357)\n",
      "JSON decode error in row 4906: Expecting ',' delimiter: line 1 column 124 (char 123)\n",
      "JSON decode error in row 4907: Expecting ',' delimiter: line 1 column 364 (char 363)\n",
      "JSON decode error in row 4911: Expecting ',' delimiter: line 1 column 352 (char 351)\n",
      "JSON decode error in row 4912: Expecting ',' delimiter: line 1 column 144 (char 143)\n",
      "JSON decode error in row 4938: Expecting ',' delimiter: line 1 column 323 (char 322)\n",
      "JSON decode error in row 4943: Expecting ',' delimiter: line 1 column 106 (char 105)\n",
      "JSON decode error in row 4989: Expecting ',' delimiter: line 1 column 121 (char 120)\n",
      "JSON decode error in row 5020: Expecting ',' delimiter: line 1 column 528 (char 527)\n",
      "JSON decode error in row 5025: Expecting ',' delimiter: line 1 column 81 (char 80)\n"
     ]
    }
   ],
   "source": [
    "#deze werkt 25/04.2023 9u41\n",
    "import re\n",
    "import pandas as pd\n",
    "import json\n",
    "\n",
    "# Define a function to extract feedback information from the \"analysis\" column\n",
    "def extract_feedback_info(row):\n",
    "    # try to load the JSON string in the analysis column\n",
    "    try:\n",
    "        categories = json.loads(row[\"analysis\"])\n",
    "    except json.JSONDecodeError as e:\n",
    "        # if an error occurs, print a warning message and return an empty dictionary\n",
    "        print(f\"JSON decode error in row {row.name}: {e}\")\n",
    "        return {}\n",
    "\n",
    "    # return the feedback_data dictionary\n",
    "    return categories\n",
    "\n",
    "# Read in the feedbacks and outputs CSV files\n",
    "feedbacks_df = pd.read_csv(\"feedbacks_analysis_2017_all.csv\")\n",
    "outputs_df = grouped_df\n",
    "\n",
    "# Define a function to reformat scientific notation to desired format\n",
    "def reformat_scientific_notation(title):\n",
    "    if isinstance(title, (str, bytes)):\n",
    "        match = re.search(r'(\\d+(\\.\\d+)?)[eE]([-+]?\\d+)', title)\n",
    "        if match:\n",
    "            number = match.group(1)\n",
    "            exponent = match.group(3)\n",
    "            return f\"{number[:16]}E{exponent.zfill(3)}\"\n",
    "        else:\n",
    "            return title\n",
    "    else:\n",
    "        return title\n",
    "\n",
    "# Apply the function to the \"title\" column in the outputs dataframe\n",
    "outputs_df[\"TweetID\"] = outputs_df[\"TweetID\"].astype(str)\n",
    "feedbacks_df[\"title\"] = feedbacks_df[\"title\"].astype(str)\n",
    "#feedbacks_df[\"title\"] = feedbacks_df[\"title\"].apply(reformat_scientific_notation)\n",
    "\n",
    "# Replace single quotes with double quotes in the analysis column\n",
    "feedbacks_df[\"analysis\"] = feedbacks_df[\"analysis\"].str.replace(\"'\", '\"')\n",
    "\n",
    "# Replace backslashes with double backslashes in the analysis column\n",
    "feedbacks_df[\"analysis\"] = feedbacks_df[\"analysis\"].str.replace(\"\\\\\", \"\\\\\\\\\")\n",
    "\n",
    "# Remove control characters from the analysis column\n",
    "feedbacks_df[\"analysis\"] = feedbacks_df[\"analysis\"].str.replace(r'[\\x00-\\x1F]+', '', regex=True)\n",
    "\n",
    "# Apply the function to the feedbacks dataframe to create a new column\n",
    "feedbacks_df[\"categories\"] = feedbacks_df.apply(extract_feedback_info, axis=1)\n",
    "#outputs_df[\"TweetID\"] = outputs_df[\"TweetID\"].astype(str)\n",
    "#make sure that the E in title notations is also capitalized by using the lambda on feedbacks_df[\"title\"] = feedbacks_df[\"title\"].apply(lambda x: '{:.15g}'.format(float(x)))\n",
    "outputs_df[\"TweetID\"] = outputs_df[\"TweetID\"].astype(str).apply(lambda x: x.replace(\"e\", \"E\") if \"e\" in x else x)\n",
    "\n",
    "\n",
    "feedbacks_df[\"title\"] = feedbacks_df[\"title\"].astype(str)\n",
    "# Convert \"title\" column to string data type\n",
    "#feedbacks_df[\"title\"] = feedbacks_df[\"title\"].astype(str)\n",
    "feedbacks_df[\"title\"] = feedbacks_df[\"title\"].apply(lambda x: '{:.15g}'.format(float(x)).replace(\"e\", \"E\"))\n",
    "\n",
    "#I want you to cut down short scientific number in \"title\" like 8.828872388116152e+17 one number before the \"e+\" so that it matches TweetID: 8.82887238811615e+17 via feedbacks_df[\"title\"] = feedbacks_df[\"title\"].astype(str)\n",
    "# Shorten the scientific notation in \"title\" column\n",
    "#feedbacks_df[\"title\"] = feedbacks_df[\"title\"].apply(lambda x: '{:.15g}'.format(float(x)))\n",
    "#The '{:.15g}'.format(float(x)) function formats the number to have 15 significant digits and then shortens the scientific notation to one number before the \"e+\".\n",
    "feedbacks_df[\"title\"] = feedbacks_df[\"title\"].apply(lambda x: '{:.15g}'.format(float(x)).replace(\"e\", \"E\"))\n",
    "# Merge\n",
    "# Merge the outputs dataframe with the feedbacks dataframe on the \"title\" and \"TwitterID\" columns\n",
    "merged_df = pd.merge(outputs_df, feedbacks_df[[\"title\", \"categories\"]], left_on=outputs_df[\"TweetID\"].str[:16], right_on=feedbacks_df[\"title\"].str[:16], how=\"left\")\n",
    "\n",
    "\n",
    "# Merge\n",
    "#Merge the outputs dataframe with the feedbacks dataframe on the \"title\" and \"TwitterID\" columns\n",
    "#merged_df = pd.merge(outputs_df, feedbacks_df[[\"title\", \"categories\"]], left_on=\"TweetID\", right_on=\"title\", how=\"left\")\n",
    "#merged_df = pd.merge(outputs_df, feedbacks_df[[\"title\", \"categories\"]], left_on=outputs_df[\"TweetID\"].str[:16], right_on=feedbacks_df[\"title\"].str[:16], how=\"left\")\n",
    "#merged_df = pd.merge(outputs_df, feedbacks_df[[\"title\", \"categories\"]], left_on=outputs_df[\"TweetID\"].str[:16], right_on=feedbacks_df[\"title\"].str[:16], how=\"left\")\n",
    "\n",
    "#Print out all cases where TweetID does not match title\n",
    "for index, row in merged_df.iterrows():\n",
    "    if pd.notnull(row[\"title\"]) and row[\"title\"] != row[\"TweetID\"]:\n",
    "        print(f\"TweetID: {row['TweetID']} does not match title: {row['title']}\")\n",
    "\n",
    "#Save the merged dataframe to a new CSV file\n",
    "#merged_df.to_csv(\"merged_pranay_gpt_def.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1aebbe65",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key_0</th>\n",
       "      <th>Filename</th>\n",
       "      <th>Caption</th>\n",
       "      <th>TweetID</th>\n",
       "      <th>results</th>\n",
       "      <th>title</th>\n",
       "      <th>categories</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8.16414648199168</td>\n",
       "      <td>TDDL_Twitter_2017_1</td>\n",
       "      <td>I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016</td>\n",
       "      <td>8.16414648199168E+17</td>\n",
       "      <td>[{'aspect category': 'META', 'aspect term': 'META_Technology_Social-Media', 'aspect content': 'YouTube', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'META', 'aspect term': 'META_Technology_Social-Media', 'aspect content': 'video', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'META', 'aspect term': 'META_Technology_Social-Media', 'aspect content': 'http', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'CONTENDER', 'aspect term': 'CONTENDER_General', 'aspect content': 'Stefanie', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'CONTENDER', 'aspect term': 'CONTENDER_General', 'aspect content': ' Sargnagel', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'META', 'aspect term': 'CONTENDER_General', 'aspect content': 'Videoporträt', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'META', 'aspect term': 'META_Videoportrait', 'aspect content': ' Bachmannpreis', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8.56571429512499</td>\n",
       "      <td>TDDL_Twitter_2017_100</td>\n",
       "      <td>tddl trtl</td>\n",
       "      <td>8.56571429512499E+17</td>\n",
       "      <td>[{'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'tddl', 'opinion target': 'tddl trtl', 'sentiment': 'negative'}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'trtl', 'opinion target': 'tddl trtl', 'sentiment': 'negative'}]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8.82882500384752</td>\n",
       "      <td>TDDL_Twitter_2017_1000</td>\n",
       "      <td>Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17</td>\n",
       "      <td>8.82882500384752E+17</td>\n",
       "      <td>[{'aspect category': 'JURY', 'aspect term': 'TEXT_General', 'aspect content': 'Feßmann', 'opinion target': 'Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17', 'sentiment': 'negative'}, {'aspect category': 'TEXT', 'aspect term': 'TEXT_General', 'aspect content': 'Text', 'opinion target': 'Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17', 'sentiment': 'positive'}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'tddl', 'opinion target': 'Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17', 'sentiment': 'negative'}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'tddl17', 'opinion target': 'Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17', 'sentiment': 'negative'}]</td>\n",
       "      <td>8.82882500384752E+17</td>\n",
       "      <td>[{'aspect category': 'JURY', 'aspect term': 'Behaviour', 'opinion target': 'Meike Feßmann', 'opinion expression': 'redet sich den Text schön', 'sentiment': 'positive', 'confidence score': '1'}, {'aspect category': 'JURY', 'aspect term': 'Valuation', 'opinion target': 'die Annahme', 'opinion expression': 'falsch', 'sentiment': 'negative', 'confidence score': '-1'}]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8.82882497335505</td>\n",
       "      <td>TDDL_Twitter_2017_1001</td>\n",
       "      <td>Kindle Hybrid! #keller #peschka #tddl</td>\n",
       "      <td>8.82882497335505E+17</td>\n",
       "      <td>[{'aspect category': 'TEXT', 'aspect term': 'JURY_Discussion_Valuation', 'aspect content': 'Hybrid', 'opinion target': 'Kindle Hybrid! #keller #peschka #tddl', 'sentiment': 'positive'}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'peschka', 'opinion target': 'Kindle Hybrid! #keller #peschka #tddl', 'sentiment': 'positive'}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'tddl', 'opinion target': 'Kindle Hybrid! #keller #peschka #tddl', 'sentiment': 'negative'}]</td>\n",
       "      <td>8.82882497335505E+17</td>\n",
       "      <td>[{'aspect category': 'JURY', 'aspect term': 'Name', 'opinion target': 'Keller', 'opinion expression': '', 'sentiment': 'neutral', 'confidence score': '0'}, {'aspect category': 'ALLO-REFERENCE', 'aspect term': 'Other-Author', 'opinion target': 'Peschka', 'opinion expression': '', 'sentiment': 'neutral', 'confidence score': '0'}]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8.82882518852276</td>\n",
       "      <td>TDDL_Twitter_2017_1002</td>\n",
       "      <td>Google-Bildersuche nach »Kindl« #tddl pic.twitter.com/FxQhSVMAjs</td>\n",
       "      <td>8.82882518852276E+17</td>\n",
       "      <td>[{'aspect category': 'META', 'aspect term': 'META_Technology_Social-Media', 'aspect content': 'Kindl', 'opinion target': 'Google-Bildersuche nach »Kindl« #tddl pic.twitter.com/FxQhSVMAjs', 'sentiment': 'negative'}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'tddl', 'opinion target': 'Google-Bildersuche nach »Kindl« #tddl pic.twitter.com/FxQhSVMAjs', 'sentiment': 'positive'}]</td>\n",
       "      <td>8.82882518852276E+17</td>\n",
       "      <td>[{'aspect category': 'ALLO-REFERENCE', 'aspect term': 'SCREEN_Film_Tv', 'opinion target': 'Google-Bildersuche nach »Kindl«', 'opinion expression': 'pic.twitter.com/FxQhSVMAjs', 'sentiment': 'neutral', 'confidence score': '0'}]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              key_0                Filename  \\\n",
       "0  8.16414648199168     TDDL_Twitter_2017_1   \n",
       "1  8.56571429512499   TDDL_Twitter_2017_100   \n",
       "2  8.82882500384752  TDDL_Twitter_2017_1000   \n",
       "3  8.82882497335505  TDDL_Twitter_2017_1001   \n",
       "4  8.82882518852276  TDDL_Twitter_2017_1002   \n",
       "\n",
       "                                                                                                           Caption  \\\n",
       "0      I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016   \n",
       "1                                                                                                        tddl trtl   \n",
       "2  Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17   \n",
       "3                                                                            Kindle Hybrid! #keller #peschka #tddl   \n",
       "4                                                 Google-Bildersuche nach »Kindl« #tddl pic.twitter.com/FxQhSVMAjs   \n",
       "\n",
       "                TweetID  \\\n",
       "0  8.16414648199168E+17   \n",
       "1  8.56571429512499E+17   \n",
       "2  8.82882500384752E+17   \n",
       "3  8.82882497335505E+17   \n",
       "4  8.82882518852276E+17   \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           results  \\\n",
       "0  [{'aspect category': 'META', 'aspect term': 'META_Technology_Social-Media', 'aspect content': 'YouTube', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'META', 'aspect term': 'META_Technology_Social-Media', 'aspect content': 'video', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'META', 'aspect term': 'META_Technology_Social-Media', 'aspect content': 'http', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'CONTENDER', 'aspect term': 'CONTENDER_General', 'aspect content': 'Stefanie', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'CONTENDER', 'aspect term': 'CONTENDER_General', 'aspect content': ' Sargnagel', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'META', 'aspect term': 'CONTENDER_General', 'aspect content': 'Videoporträt', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}, {'aspect category': 'META', 'aspect term': 'META_Videoportrait', 'aspect content': ' Bachmannpreis', 'opinion target': 'I liked a @YouTube video http://youtu.be/2AAbkbcUjZA?a Stefanie Sargnagel - Videoporträt Bachmannpreis 2016', 'sentiment': 'positive'}]   \n",
       "1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               [{'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'tddl', 'opinion target': 'tddl trtl', 'sentiment': 'negative'}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'trtl', 'opinion target': 'tddl trtl', 'sentiment': 'negative'}]   \n",
       "2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      [{'aspect category': 'JURY', 'aspect term': 'TEXT_General', 'aspect content': 'Feßmann', 'opinion target': 'Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17', 'sentiment': 'negative'}, {'aspect category': 'TEXT', 'aspect term': 'TEXT_General', 'aspect content': 'Text', 'opinion target': 'Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17', 'sentiment': 'positive'}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'tddl', 'opinion target': 'Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17', 'sentiment': 'negative'}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'tddl17', 'opinion target': 'Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17', 'sentiment': 'negative'}]   \n",
       "3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           [{'aspect category': 'TEXT', 'aspect term': 'JURY_Discussion_Valuation', 'aspect content': 'Hybrid', 'opinion target': 'Kindle Hybrid! #keller #peschka #tddl', 'sentiment': 'positive'}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'peschka', 'opinion target': 'Kindle Hybrid! #keller #peschka #tddl', 'sentiment': 'positive'}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'tddl', 'opinion target': 'Kindle Hybrid! #keller #peschka #tddl', 'sentiment': 'negative'}]   \n",
       "4                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   [{'aspect category': 'META', 'aspect term': 'META_Technology_Social-Media', 'aspect content': 'Kindl', 'opinion target': 'Google-Bildersuche nach »Kindl« #tddl pic.twitter.com/FxQhSVMAjs', 'sentiment': 'negative'}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'tddl', 'opinion target': 'Google-Bildersuche nach »Kindl« #tddl pic.twitter.com/FxQhSVMAjs', 'sentiment': 'positive'}]   \n",
       "\n",
       "                  title  \\\n",
       "0                   NaN   \n",
       "1                   NaN   \n",
       "2  8.82882500384752E+17   \n",
       "3  8.82882497335505E+17   \n",
       "4  8.82882518852276E+17   \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                                                                       categories  \n",
       "0                                                                                                                                                                                                                                                                                                                                                                             NaN  \n",
       "1                                                                                                                                                                                                                                                                                                                                                                             NaN  \n",
       "2  [{'aspect category': 'JURY', 'aspect term': 'Behaviour', 'opinion target': 'Meike Feßmann', 'opinion expression': 'redet sich den Text schön', 'sentiment': 'positive', 'confidence score': '1'}, {'aspect category': 'JURY', 'aspect term': 'Valuation', 'opinion target': 'die Annahme', 'opinion expression': 'falsch', 'sentiment': 'negative', 'confidence score': '-1'}]  \n",
       "3                                       [{'aspect category': 'JURY', 'aspect term': 'Name', 'opinion target': 'Keller', 'opinion expression': '', 'sentiment': 'neutral', 'confidence score': '0'}, {'aspect category': 'ALLO-REFERENCE', 'aspect term': 'Other-Author', 'opinion target': 'Peschka', 'opinion expression': '', 'sentiment': 'neutral', 'confidence score': '0'}]  \n",
       "4                                                                                                                                              [{'aspect category': 'ALLO-REFERENCE', 'aspect term': 'SCREEN_Film_Tv', 'opinion target': 'Google-Bildersuche nach »Kindl«', 'opinion expression': 'pic.twitter.com/FxQhSVMAjs', 'sentiment': 'neutral', 'confidence score': '0'}]  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "1390c159",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-7-974e97f4e26d>:108: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  filtered_df[[\"categories_merged\", \"overlapping labels\", \"non-overlapping labels in results\", \"non-overlapping labels in categories\", \"results\"]] = filtered_df.apply(merge_categories, axis=1)\n",
      "<ipython-input-7-974e97f4e26d>:108: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  filtered_df[[\"categories_merged\", \"overlapping labels\", \"non-overlapping labels in results\", \"non-overlapping labels in categories\", \"results\"]] = filtered_df.apply(merge_categories, axis=1)\n",
      "<ipython-input-7-974e97f4e26d>:108: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  filtered_df[[\"categories_merged\", \"overlapping labels\", \"non-overlapping labels in results\", \"non-overlapping labels in categories\", \"results\"]] = filtered_df.apply(merge_categories, axis=1)\n",
      "<ipython-input-7-974e97f4e26d>:108: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  filtered_df[[\"categories_merged\", \"overlapping labels\", \"non-overlapping labels in results\", \"non-overlapping labels in categories\", \"results\"]] = filtered_df.apply(merge_categories, axis=1)\n",
      "<ipython-input-7-974e97f4e26d>:111: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  filtered_df['pranay_matching_cats_terms'] = filtered_df.apply(generate_matching_sequence, axis=1)\n",
      "<ipython-input-7-974e97f4e26d>:114: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  filtered_df['pranay_matching_cats_terms_percentage'] = filtered_df.apply(compute_matching_percentage, axis=1)\n",
      "<ipython-input-7-974e97f4e26d>:117: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  filtered_df['results'] = filtered_df.apply(add_hashtag, axis=1)\n",
      "<ipython-input-7-974e97f4e26d>:120: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  filtered_df[\"equal_labels\"] = filtered_df[\"categories_merged\"].apply(lambda x: len(x)) == filtered_df[\"categories\"].apply(lambda x: len(x) if isinstance(x, list) else 0)\n",
      "<ipython-input-7-974e97f4e26d>:123: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  filtered_df['results_no_hashtags'] = filtered_df.apply(filter_no_hashtags, axis=1)\n"
     ]
    }
   ],
   "source": [
    "def merge_categories(row):\n",
    "    results = row[\"results\"]\n",
    "    categories = row[\"categories\"]\n",
    "\n",
    "    if isinstance(results, list) and all(isinstance(res, dict) for res in results) and \\\n",
    "       isinstance(categories, list) and all(isinstance(cat, dict) for cat in categories):\n",
    "\n",
    "        merged_categories = []\n",
    "        overlapping_labels = 0\n",
    "        non_overlapping_labels_results = len(results)\n",
    "        non_overlapping_labels_categories = len(categories)\n",
    "\n",
    "        for category in categories:\n",
    "            merged_category = category.get(\"aspect category\", \"\") + \"_\" + category.get(\"aspect term\", \"\")\n",
    "            merged_categories.append(merged_category)\n",
    "\n",
    "            if any(result.get(\"aspect category\", \"\") + \"_\" + result.get(\"aspect term\", \"\") == merged_category for result in results):\n",
    "                overlapping_labels += 1\n",
    "            else:\n",
    "                non_overlapping_labels_categories += 1\n",
    "\n",
    "        for result in results:\n",
    "            if not any(result.get(\"aspect category\", \"\") + \"_\" + result.get(\"aspect term\", \"\") == merged_category for merged_category in merged_categories):\n",
    "                non_overlapping_labels_results += 1\n",
    "\n",
    "            aspect_term_first_word = result.get('aspect term', '').split('_')[0]\n",
    "            result['aspect_term_first_word'] = aspect_term_first_word\n",
    "            result['matches_aspect_category'] = aspect_term_first_word.upper() == result.get('aspect category', '')\n",
    "\n",
    "        return pd.Series({\n",
    "            \"categories_merged\": merged_categories,\n",
    "            \"overlapping labels\": overlapping_labels,\n",
    "            \"non-overlapping labels in results\": non_overlapping_labels_results,\n",
    "            \"non-overlapping labels in categories\": non_overlapping_labels_categories,\n",
    "            \"results\": results\n",
    "        })\n",
    "\n",
    "    else:\n",
    "        return pd.Series({\n",
    "            \"categories_merged\": [],\n",
    "            \"overlapping labels\": 0,\n",
    "            \"non-overlapping labels in results\": 0,\n",
    "            \"non-overlapping labels in categories\": len(categories) if isinstance(categories, list) else 0,\n",
    "            \"results\": results\n",
    "        })\n",
    "\n",
    "def generate_matching_sequence(row):\n",
    "    results = row[\"results\"]\n",
    "\n",
    "    if isinstance(results, list) and all(isinstance(res, dict) for res in results):\n",
    "        matching_sequences = []\n",
    "        for result in results:\n",
    "            aspect_term_first_word = result.get('aspect_term_first_word', '')\n",
    "            aspect_category = result.get('aspect category', '')\n",
    "            matches_aspect_category = result.get('matches_aspect_category', '')\n",
    "            matching_sequence = f\"{aspect_category}, {aspect_term_first_word}, {matches_aspect_category}\"\n",
    "            matching_sequences.append(matching_sequence)\n",
    "\n",
    "        return matching_sequences\n",
    "\n",
    "    return []\n",
    "\n",
    "def compute_matching_percentage(row):\n",
    "    results = row[\"results\"]\n",
    "    matching = row[\"bert_matching_cats_terms\"]\n",
    "\n",
    "    if isinstance(results, list) and all(isinstance(res, dict) for res in results):\n",
    "        total_matches = sum(1 for match in matching if 'True' in match)\n",
    "        total_results = len(results)\n",
    "        \n",
    "        if total_results > 0:\n",
    "            return total_matches / total_results * 100\n",
    "\n",
    "    return 0\n",
    "\n",
    "def add_hashtag(row):\n",
    "    caption = row[\"Caption\"]\n",
    "    results = row[\"results\"]\n",
    "    updated_results = []\n",
    "    \n",
    "    if isinstance(results, list) and all(isinstance(res, dict) for res in results):\n",
    "        for result in results:\n",
    "            updated_result = result.copy()  \n",
    "            aspect_content = result.get('aspect content', '')\n",
    "            if aspect_content and ('#' + aspect_content) in caption:\n",
    "                updated_result[\"hashtag\"] = True\n",
    "            else:\n",
    "                updated_result[\"hashtag\"] = False\n",
    "            updated_results.append(updated_result)\n",
    "            \n",
    "    return updated_results\n",
    "\n",
    "def filter_no_hashtags(row):\n",
    "    results = row[\"results\"]\n",
    "    results_no_hashtags = []\n",
    "\n",
    "    if isinstance(results, list) and all(isinstance(res, dict) for res in results):\n",
    "        for result in results:\n",
    "            if not result.get(\"hashtag\", False):\n",
    "                results_no_hashtags.append(result)\n",
    "            \n",
    "    return results_no_hashtags\n",
    "\n",
    "# Filter the DataFrame to include only rows where categories is not NaN\n",
    "filtered_df = merged_df[merged_df[\"categories\"].notna()]\n",
    "\n",
    "# Apply the merge_categories function to create new columns on the filtered DataFrame\n",
    "filtered_df[[\"categories_merged\", \"overlapping labels\", \"non-overlapping labels in results\", \"non-overlapping labels in categories\", \"results\"]] = filtered_df.apply(merge_categories, axis=1)\n",
    "\n",
    "# Generate matching sequence\n",
    "filtered_df['bert_matching_cats_terms'] = filtered_df.apply(generate_matching_sequence, axis=1)\n",
    "\n",
    "# Compute matching percentage\n",
    "filtered_df['bert_matching_cats_terms_percentage'] = filtered_df.apply(compute_matching_percentage, axis=1)\n",
    "\n",
    "# Add hashtag field\n",
    "filtered_df['results'] = filtered_df.apply(add_hashtag, axis=1)\n",
    "\n",
    "# Compare labels and add an \"equal_labels\" column on the filtered DataFrame\n",
    "filtered_df[\"equal_labels\"] = filtered_df[\"categories_merged\"].apply(lambda x: len(x)) == filtered_df[\"categories\"].apply(lambda x: len(x) if isinstance(x, list) else 0)\n",
    "\n",
    "# Apply the filter_no_hashtags function to create the new column\n",
    "filtered_df['results_no_hashtags'] = filtered_df.apply(filter_no_hashtags, axis=1)\n",
    "\n",
    "# Get a list of the column names\n",
    "cols = list(filtered_df.columns)\n",
    "\n",
    "# Move the new column to right after the \"results\" column\n",
    "cols.insert(cols.index('results') + 1, cols.pop(cols.index('results_no_hashtags')))\n",
    "\n",
    "# Reorder the DataFrame\n",
    "filtered_df = filtered_df[cols]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2de75c79",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key_0</th>\n",
       "      <th>Filename</th>\n",
       "      <th>Caption</th>\n",
       "      <th>TweetID</th>\n",
       "      <th>results</th>\n",
       "      <th>results_no_hashtags</th>\n",
       "      <th>title</th>\n",
       "      <th>categories</th>\n",
       "      <th>categories_merged</th>\n",
       "      <th>overlapping labels</th>\n",
       "      <th>non-overlapping labels in results</th>\n",
       "      <th>non-overlapping labels in categories</th>\n",
       "      <th>pranay_matching_cats_terms</th>\n",
       "      <th>pranay_matching_cats_terms_percentage</th>\n",
       "      <th>equal_labels</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8.82882500384752</td>\n",
       "      <td>TDDL_Twitter_2017_1000</td>\n",
       "      <td>Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17</td>\n",
       "      <td>8.82882500384752E+17</td>\n",
       "      <td>[{'aspect category': 'JURY', 'aspect term': 'TEXT_General', 'aspect content': 'Feßmann', 'opinion target': 'Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17', 'sentiment': 'negative', 'aspect_term_first_word': 'TEXT', 'matches_aspect_category': False, 'hashtag': False}, {'aspect category': 'TEXT', 'aspect term': 'TEXT_General', 'aspect content': 'Text', 'opinion target': 'Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17', 'sentiment': 'positive', 'aspect_term_first_word': 'TEXT', 'matches_aspect_category': True, 'hashtag': False}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'tddl', 'opinion target': 'Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17', 'sentiment': 'negative', 'aspect_term_first_word': 'META', 'matches_aspect_category': True, 'hashtag': True}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'tddl17', 'opinion target': 'Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17', 'sentiment': 'negative', 'aspect_term_first_word': 'META', 'matches_aspect_category': True, 'hashtag': True}]</td>\n",
       "      <td>[{'aspect category': 'JURY', 'aspect term': 'TEXT_General', 'aspect content': 'Feßmann', 'opinion target': 'Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17', 'sentiment': 'negative', 'aspect_term_first_word': 'TEXT', 'matches_aspect_category': False, 'hashtag': False}, {'aspect category': 'TEXT', 'aspect term': 'TEXT_General', 'aspect content': 'Text', 'opinion target': 'Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17', 'sentiment': 'positive', 'aspect_term_first_word': 'TEXT', 'matches_aspect_category': True, 'hashtag': False}]</td>\n",
       "      <td>8.82882500384752E+17</td>\n",
       "      <td>[{'aspect category': 'JURY', 'aspect term': 'Behaviour', 'opinion target': 'Meike Feßmann', 'opinion expression': 'redet sich den Text schön', 'sentiment': 'positive', 'confidence score': '1'}, {'aspect category': 'JURY', 'aspect term': 'Valuation', 'opinion target': 'die Annahme', 'opinion expression': 'falsch', 'sentiment': 'negative', 'confidence score': '-1'}]</td>\n",
       "      <td>[JURY_Behaviour, JURY_Valuation]</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>[JURY, TEXT, False, TEXT, TEXT, True, META, META, True, META, META, True]</td>\n",
       "      <td>75.000000</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8.82882497335505</td>\n",
       "      <td>TDDL_Twitter_2017_1001</td>\n",
       "      <td>Kindle Hybrid! #keller #peschka #tddl</td>\n",
       "      <td>8.82882497335505E+17</td>\n",
       "      <td>[{'aspect category': 'TEXT', 'aspect term': 'JURY_Discussion_Valuation', 'aspect content': 'Hybrid', 'opinion target': 'Kindle Hybrid! #keller #peschka #tddl', 'sentiment': 'positive', 'aspect_term_first_word': 'JURY', 'matches_aspect_category': False, 'hashtag': False}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'peschka', 'opinion target': 'Kindle Hybrid! #keller #peschka #tddl', 'sentiment': 'positive', 'aspect_term_first_word': 'META', 'matches_aspect_category': True, 'hashtag': True}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'tddl', 'opinion target': 'Kindle Hybrid! #keller #peschka #tddl', 'sentiment': 'negative', 'aspect_term_first_word': 'META', 'matches_aspect_category': True, 'hashtag': True}]</td>\n",
       "      <td>[{'aspect category': 'TEXT', 'aspect term': 'JURY_Discussion_Valuation', 'aspect content': 'Hybrid', 'opinion target': 'Kindle Hybrid! #keller #peschka #tddl', 'sentiment': 'positive', 'aspect_term_first_word': 'JURY', 'matches_aspect_category': False, 'hashtag': False}]</td>\n",
       "      <td>8.82882497335505E+17</td>\n",
       "      <td>[{'aspect category': 'JURY', 'aspect term': 'Name', 'opinion target': 'Keller', 'opinion expression': '', 'sentiment': 'neutral', 'confidence score': '0'}, {'aspect category': 'ALLO-REFERENCE', 'aspect term': 'Other-Author', 'opinion target': 'Peschka', 'opinion expression': '', 'sentiment': 'neutral', 'confidence score': '0'}]</td>\n",
       "      <td>[JURY_Name, ALLO-REFERENCE_Other-Author]</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>[TEXT, JURY, False, META, META, True, META, META, True]</td>\n",
       "      <td>66.666667</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8.82882518852276</td>\n",
       "      <td>TDDL_Twitter_2017_1002</td>\n",
       "      <td>Google-Bildersuche nach »Kindl« #tddl pic.twitter.com/FxQhSVMAjs</td>\n",
       "      <td>8.82882518852276E+17</td>\n",
       "      <td>[{'aspect category': 'META', 'aspect term': 'META_Technology_Social-Media', 'aspect content': 'Kindl', 'opinion target': 'Google-Bildersuche nach »Kindl« #tddl pic.twitter.com/FxQhSVMAjs', 'sentiment': 'negative', 'aspect_term_first_word': 'META', 'matches_aspect_category': True, 'hashtag': False}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'tddl', 'opinion target': 'Google-Bildersuche nach »Kindl« #tddl pic.twitter.com/FxQhSVMAjs', 'sentiment': 'positive', 'aspect_term_first_word': 'META', 'matches_aspect_category': True, 'hashtag': True}]</td>\n",
       "      <td>[{'aspect category': 'META', 'aspect term': 'META_Technology_Social-Media', 'aspect content': 'Kindl', 'opinion target': 'Google-Bildersuche nach »Kindl« #tddl pic.twitter.com/FxQhSVMAjs', 'sentiment': 'negative', 'aspect_term_first_word': 'META', 'matches_aspect_category': True, 'hashtag': False}]</td>\n",
       "      <td>8.82882518852276E+17</td>\n",
       "      <td>[{'aspect category': 'ALLO-REFERENCE', 'aspect term': 'SCREEN_Film_Tv', 'opinion target': 'Google-Bildersuche nach »Kindl«', 'opinion expression': 'pic.twitter.com/FxQhSVMAjs', 'sentiment': 'neutral', 'confidence score': '0'}]</td>\n",
       "      <td>[ALLO-REFERENCE_SCREEN_Film_Tv]</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>[META, META, True, META, META, True]</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>8.82882554000536</td>\n",
       "      <td>TDDL_Twitter_2017_1003</td>\n",
       "      <td>Klaus Kastbergers kreisenden Fuß hätte ich gern als Gif. #tddl</td>\n",
       "      <td>8.82882554000536E+17</td>\n",
       "      <td>[{'aspect category': 'META', 'aspect term': 'TEXT_General', 'aspect content': 'Gif', 'opinion target': 'Klaus Kastbergers kreisenden Fuß hätte ich gern als Gif. #tddl', 'sentiment': 'positive', 'aspect_term_first_word': 'TEXT', 'matches_aspect_category': False, 'hashtag': False}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'tddl', 'opinion target': 'Klaus Kastbergers kreisenden Fuß hätte ich gern als Gif. #tddl', 'sentiment': 'positive', 'aspect_term_first_word': 'META', 'matches_aspect_category': True, 'hashtag': True}]</td>\n",
       "      <td>[{'aspect category': 'META', 'aspect term': 'TEXT_General', 'aspect content': 'Gif', 'opinion target': 'Klaus Kastbergers kreisenden Fuß hätte ich gern als Gif. #tddl', 'sentiment': 'positive', 'aspect_term_first_word': 'TEXT', 'matches_aspect_category': False, 'hashtag': False}]</td>\n",
       "      <td>8.82882554000536E+17</td>\n",
       "      <td>[{'aspect category': 'JURY', 'aspect term': 'Appearance_Clothing', 'opinion target': 'Klaus Kastberger', 'opinion expression': 'kreisenden Fuß', 'sentiment': 'neutral', 'confidence score': '0'}, {'aspect category': 'META', 'aspect term': 'Technology_Social-Media', 'opinion target': 'Gif', 'opinion expression': 'gern als Gif', 'sentiment': 'positive', 'confidence score': '1'}]</td>\n",
       "      <td>[JURY_Appearance_Clothing, META_Technology_Social-Media]</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>[META, TEXT, False, META, META, True]</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>8.82882648758264</td>\n",
       "      <td>TDDL_Twitter_2017_1004</td>\n",
       "      <td>Jury eher so halbe-halbe. In sich gespalten. #tddl</td>\n",
       "      <td>8.82882648758264E+17</td>\n",
       "      <td>[{'aspect category': 'JURY', 'aspect term': 'JURY_Discussion_Valuation', 'aspect content': 'tddl', 'opinion target': 'Jury eher so halbe-halbe. In sich gespalten. #tddl', 'sentiment': 'negative', 'aspect_term_first_word': 'JURY', 'matches_aspect_category': True, 'hashtag': True}]</td>\n",
       "      <td>[]</td>\n",
       "      <td>8.82882648758264E+17</td>\n",
       "      <td>[{'aspect category': 'JURY', 'aspect term': 'Discussion_Valuation', 'opinion target': 'die Jury', 'opinion expression': 'eher so halbe-halbe. In sich gespalten', 'sentiment': 'neutral', 'confidence score': '0'}]</td>\n",
       "      <td>[JURY_Discussion_Valuation]</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>[JURY, JURY, True]</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              key_0                Filename  \\\n",
       "2  8.82882500384752  TDDL_Twitter_2017_1000   \n",
       "3  8.82882497335505  TDDL_Twitter_2017_1001   \n",
       "4  8.82882518852276  TDDL_Twitter_2017_1002   \n",
       "5  8.82882554000536  TDDL_Twitter_2017_1003   \n",
       "6  8.82882648758264  TDDL_Twitter_2017_1004   \n",
       "\n",
       "                                                                                                           Caption  \\\n",
       "2  Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17   \n",
       "3                                                                            Kindle Hybrid! #keller #peschka #tddl   \n",
       "4                                                 Google-Bildersuche nach »Kindl« #tddl pic.twitter.com/FxQhSVMAjs   \n",
       "5                                                   Klaus Kastbergers kreisenden Fuß hätte ich gern als Gif. #tddl   \n",
       "6                                                               Jury eher so halbe-halbe. In sich gespalten. #tddl   \n",
       "\n",
       "                TweetID  \\\n",
       "2  8.82882500384752E+17   \n",
       "3  8.82882497335505E+17   \n",
       "4  8.82882518852276E+17   \n",
       "5  8.82882554000536E+17   \n",
       "6  8.82882648758264E+17   \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          results  \\\n",
       "2  [{'aspect category': 'JURY', 'aspect term': 'TEXT_General', 'aspect content': 'Feßmann', 'opinion target': 'Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17', 'sentiment': 'negative', 'aspect_term_first_word': 'TEXT', 'matches_aspect_category': False, 'hashtag': False}, {'aspect category': 'TEXT', 'aspect term': 'TEXT_General', 'aspect content': 'Text', 'opinion target': 'Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17', 'sentiment': 'positive', 'aspect_term_first_word': 'TEXT', 'matches_aspect_category': True, 'hashtag': False}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'tddl', 'opinion target': 'Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17', 'sentiment': 'negative', 'aspect_term_first_word': 'META', 'matches_aspect_category': True, 'hashtag': True}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'tddl17', 'opinion target': 'Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17', 'sentiment': 'negative', 'aspect_term_first_word': 'META', 'matches_aspect_category': True, 'hashtag': True}]   \n",
       "3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            [{'aspect category': 'TEXT', 'aspect term': 'JURY_Discussion_Valuation', 'aspect content': 'Hybrid', 'opinion target': 'Kindle Hybrid! #keller #peschka #tddl', 'sentiment': 'positive', 'aspect_term_first_word': 'JURY', 'matches_aspect_category': False, 'hashtag': False}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'peschka', 'opinion target': 'Kindle Hybrid! #keller #peschka #tddl', 'sentiment': 'positive', 'aspect_term_first_word': 'META', 'matches_aspect_category': True, 'hashtag': True}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'tddl', 'opinion target': 'Kindle Hybrid! #keller #peschka #tddl', 'sentiment': 'negative', 'aspect_term_first_word': 'META', 'matches_aspect_category': True, 'hashtag': True}]   \n",
       "4                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         [{'aspect category': 'META', 'aspect term': 'META_Technology_Social-Media', 'aspect content': 'Kindl', 'opinion target': 'Google-Bildersuche nach »Kindl« #tddl pic.twitter.com/FxQhSVMAjs', 'sentiment': 'negative', 'aspect_term_first_word': 'META', 'matches_aspect_category': True, 'hashtag': False}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'tddl', 'opinion target': 'Google-Bildersuche nach »Kindl« #tddl pic.twitter.com/FxQhSVMAjs', 'sentiment': 'positive', 'aspect_term_first_word': 'META', 'matches_aspect_category': True, 'hashtag': True}]   \n",
       "5                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              [{'aspect category': 'META', 'aspect term': 'TEXT_General', 'aspect content': 'Gif', 'opinion target': 'Klaus Kastbergers kreisenden Fuß hätte ich gern als Gif. #tddl', 'sentiment': 'positive', 'aspect_term_first_word': 'TEXT', 'matches_aspect_category': False, 'hashtag': False}, {'aspect category': 'META', 'aspect term': 'META_Main-Event', 'aspect content': 'tddl', 'opinion target': 'Klaus Kastbergers kreisenden Fuß hätte ich gern als Gif. #tddl', 'sentiment': 'positive', 'aspect_term_first_word': 'META', 'matches_aspect_category': True, 'hashtag': True}]   \n",
       "6                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        [{'aspect category': 'JURY', 'aspect term': 'JURY_Discussion_Valuation', 'aspect content': 'tddl', 'opinion target': 'Jury eher so halbe-halbe. In sich gespalten. #tddl', 'sentiment': 'negative', 'aspect_term_first_word': 'JURY', 'matches_aspect_category': True, 'hashtag': True}]   \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      results_no_hashtags  \\\n",
       "2  [{'aspect category': 'JURY', 'aspect term': 'TEXT_General', 'aspect content': 'Feßmann', 'opinion target': 'Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17', 'sentiment': 'negative', 'aspect_term_first_word': 'TEXT', 'matches_aspect_category': False, 'hashtag': False}, {'aspect category': 'TEXT', 'aspect term': 'TEXT_General', 'aspect content': 'Text', 'opinion target': 'Meike Feßmann redet sich den Text schön mit der (falschen) Annahme, dass es Teil eines Romans ist #tddl #tddl17', 'sentiment': 'positive', 'aspect_term_first_word': 'TEXT', 'matches_aspect_category': True, 'hashtag': False}]   \n",
       "3                                                                                                                                                                                                                                                                                                                                                                                                         [{'aspect category': 'TEXT', 'aspect term': 'JURY_Discussion_Valuation', 'aspect content': 'Hybrid', 'opinion target': 'Kindle Hybrid! #keller #peschka #tddl', 'sentiment': 'positive', 'aspect_term_first_word': 'JURY', 'matches_aspect_category': False, 'hashtag': False}]   \n",
       "4                                                                                                                                                                                                                                                                                                                                                                             [{'aspect category': 'META', 'aspect term': 'META_Technology_Social-Media', 'aspect content': 'Kindl', 'opinion target': 'Google-Bildersuche nach »Kindl« #tddl pic.twitter.com/FxQhSVMAjs', 'sentiment': 'negative', 'aspect_term_first_word': 'META', 'matches_aspect_category': True, 'hashtag': False}]   \n",
       "5                                                                                                                                                                                                                                                                                                                                                                                                [{'aspect category': 'META', 'aspect term': 'TEXT_General', 'aspect content': 'Gif', 'opinion target': 'Klaus Kastbergers kreisenden Fuß hätte ich gern als Gif. #tddl', 'sentiment': 'positive', 'aspect_term_first_word': 'TEXT', 'matches_aspect_category': False, 'hashtag': False}]   \n",
       "6                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      []   \n",
       "\n",
       "                  title  \\\n",
       "2  8.82882500384752E+17   \n",
       "3  8.82882497335505E+17   \n",
       "4  8.82882518852276E+17   \n",
       "5  8.82882554000536E+17   \n",
       "6  8.82882648758264E+17   \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                                                                                   categories  \\\n",
       "2              [{'aspect category': 'JURY', 'aspect term': 'Behaviour', 'opinion target': 'Meike Feßmann', 'opinion expression': 'redet sich den Text schön', 'sentiment': 'positive', 'confidence score': '1'}, {'aspect category': 'JURY', 'aspect term': 'Valuation', 'opinion target': 'die Annahme', 'opinion expression': 'falsch', 'sentiment': 'negative', 'confidence score': '-1'}]   \n",
       "3                                                   [{'aspect category': 'JURY', 'aspect term': 'Name', 'opinion target': 'Keller', 'opinion expression': '', 'sentiment': 'neutral', 'confidence score': '0'}, {'aspect category': 'ALLO-REFERENCE', 'aspect term': 'Other-Author', 'opinion target': 'Peschka', 'opinion expression': '', 'sentiment': 'neutral', 'confidence score': '0'}]   \n",
       "4                                                                                                                                                          [{'aspect category': 'ALLO-REFERENCE', 'aspect term': 'SCREEN_Film_Tv', 'opinion target': 'Google-Bildersuche nach »Kindl«', 'opinion expression': 'pic.twitter.com/FxQhSVMAjs', 'sentiment': 'neutral', 'confidence score': '0'}]   \n",
       "5  [{'aspect category': 'JURY', 'aspect term': 'Appearance_Clothing', 'opinion target': 'Klaus Kastberger', 'opinion expression': 'kreisenden Fuß', 'sentiment': 'neutral', 'confidence score': '0'}, {'aspect category': 'META', 'aspect term': 'Technology_Social-Media', 'opinion target': 'Gif', 'opinion expression': 'gern als Gif', 'sentiment': 'positive', 'confidence score': '1'}]   \n",
       "6                                                                                                                                                                         [{'aspect category': 'JURY', 'aspect term': 'Discussion_Valuation', 'opinion target': 'die Jury', 'opinion expression': 'eher so halbe-halbe. In sich gespalten', 'sentiment': 'neutral', 'confidence score': '0'}]   \n",
       "\n",
       "                                          categories_merged  \\\n",
       "2                          [JURY_Behaviour, JURY_Valuation]   \n",
       "3                  [JURY_Name, ALLO-REFERENCE_Other-Author]   \n",
       "4                           [ALLO-REFERENCE_SCREEN_Film_Tv]   \n",
       "5  [JURY_Appearance_Clothing, META_Technology_Social-Media]   \n",
       "6                               [JURY_Discussion_Valuation]   \n",
       "\n",
       "   overlapping labels  non-overlapping labels in results  \\\n",
       "2                   0                                  8   \n",
       "3                   0                                  6   \n",
       "4                   0                                  4   \n",
       "5                   0                                  4   \n",
       "6                   0                                  2   \n",
       "\n",
       "   non-overlapping labels in categories  \\\n",
       "2                                     4   \n",
       "3                                     4   \n",
       "4                                     2   \n",
       "5                                     4   \n",
       "6                                     2   \n",
       "\n",
       "                                                  pranay_matching_cats_terms  \\\n",
       "2  [JURY, TEXT, False, TEXT, TEXT, True, META, META, True, META, META, True]   \n",
       "3                    [TEXT, JURY, False, META, META, True, META, META, True]   \n",
       "4                                       [META, META, True, META, META, True]   \n",
       "5                                      [META, TEXT, False, META, META, True]   \n",
       "6                                                         [JURY, JURY, True]   \n",
       "\n",
       "   pranay_matching_cats_terms_percentage  equal_labels  \n",
       "2                              75.000000          True  \n",
       "3                              66.666667          True  \n",
       "4                             100.000000          True  \n",
       "5                              50.000000          True  \n",
       "6                             100.000000          True  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8acf9fac",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_df.to_csv('2017_bert_vs_gpt.csv', index=False)"
   ]
  }
 ],
 "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}