Exploring_aylien_business_5k_responses.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "bcb95162",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import random\n",
    "import numpy as np\n",
    "\n",
    "import ast\n",
    "# Set the display option to show all rows\n",
    "# pd.set_option('display.max_rows', None)\n",
    "# pd.set_option('display.max_colwidth', None)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c87f5808",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the Excel workbook into a DataFrame\n",
    "df = pd.read_excel(\"./results/PP_reformatted_aylien_business_5k_responses.xlsx\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "8afc0316",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5000\n"
     ]
    },
    {
     "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>Unnamed: 0</th>\n",
       "      <th>article_id</th>\n",
       "      <th>title</th>\n",
       "      <th>published_at</th>\n",
       "      <th>source</th>\n",
       "      <th>body</th>\n",
       "      <th>prompt</th>\n",
       "      <th>token_count</th>\n",
       "      <th>responses</th>\n",
       "      <th>responses_PP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>5709294677</td>\n",
       "      <td>: Nike stock jumps to lead the Dow's premarket...</td>\n",
       "      <td>02-June-2023</td>\n",
       "      <td>Finanzen.ch</td>\n",
       "      <td>Nike  96.41 CHF -1.99%  Charts  News  Analysen...</td>\n",
       "      <td>For the main organisation discussed in this ar...</td>\n",
       "      <td>732</td>\n",
       "      <td>org: Nike Inc.\\ncountry: NA\\nstate: NA\\ncity: ...</td>\n",
       "      <td>{'org': 'Nike Inc.', 'country': '', 'state': '...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>5709292390</td>\n",
       "      <td>Renault Customers Planning Criminal Complaint ...</td>\n",
       "      <td>02-June-2023</td>\n",
       "      <td>MarketScreener</td>\n",
       "      <td>Financials EUR USD GBP Sales 2023 50 468 M 54 ...</td>\n",
       "      <td>For the main organisation discussed in this ar...</td>\n",
       "      <td>833</td>\n",
       "      <td>org: Renault\\ncountry: NA\\nstate: NA\\ncity: NA...</td>\n",
       "      <td>{'org': 'Renault', 'country': '', 'state': '',...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>5709276868</td>\n",
       "      <td>Mersana Therapeutics Inc. (MRSN) last month vo...</td>\n",
       "      <td>02-June-2023</td>\n",
       "      <td>Newsdaemon.com</td>\n",
       "      <td>Search  Search   Search  Search  Sana Meer  Ju...</td>\n",
       "      <td>For the main organisation discussed in this ar...</td>\n",
       "      <td>1720</td>\n",
       "      <td>org: Mersana Therapeutics Inc.\\ncountry: NA\\ns...</td>\n",
       "      <td>{'org': 'Mersana Therapeutics Inc.', 'country'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>5709269010</td>\n",
       "      <td>Mike Novogratz Says Crypto is Lackadaisical Ri...</td>\n",
       "      <td>02-June-2023</td>\n",
       "      <td>Crypto Potato</td>\n",
       "      <td>Michael Novogratz, the founder and CEO of Gala...</td>\n",
       "      <td>For the main organisation discussed in this ar...</td>\n",
       "      <td>944</td>\n",
       "      <td>org: Galaxy Digital\\ncountry: NA\\nstate: NA\\nc...</td>\n",
       "      <td>{'org': 'Galaxy Digital', 'country': '', 'stat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>5709271283</td>\n",
       "      <td>Another Soft Market Is Inevitable</td>\n",
       "      <td>02-June-2023</td>\n",
       "      <td>Carrier Management</td>\n",
       "      <td>New You can now listen to Carrier Management a...</td>\n",
       "      <td>For the main organisation discussed in this ar...</td>\n",
       "      <td>2092</td>\n",
       "      <td>org: RLI Corp.\\ncountry: NA\\nstate: NA\\ncity: ...</td>\n",
       "      <td>{'org': 'RLI Corp.', 'country': '', 'state': '...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  article_id                                              title  \\\n",
       "0           0  5709294677  : Nike stock jumps to lead the Dow's premarket...   \n",
       "1           1  5709292390  Renault Customers Planning Criminal Complaint ...   \n",
       "2           2  5709276868  Mersana Therapeutics Inc. (MRSN) last month vo...   \n",
       "3           3  5709269010  Mike Novogratz Says Crypto is Lackadaisical Ri...   \n",
       "4           4  5709271283                  Another Soft Market Is Inevitable   \n",
       "\n",
       "   published_at              source  \\\n",
       "0  02-June-2023         Finanzen.ch   \n",
       "1  02-June-2023      MarketScreener   \n",
       "2  02-June-2023      Newsdaemon.com   \n",
       "3  02-June-2023       Crypto Potato   \n",
       "4  02-June-2023  Carrier Management   \n",
       "\n",
       "                                                body  \\\n",
       "0  Nike  96.41 CHF -1.99%  Charts  News  Analysen...   \n",
       "1  Financials EUR USD GBP Sales 2023 50 468 M 54 ...   \n",
       "2  Search  Search   Search  Search  Sana Meer  Ju...   \n",
       "3  Michael Novogratz, the founder and CEO of Gala...   \n",
       "4  New You can now listen to Carrier Management a...   \n",
       "\n",
       "                                              prompt  token_count  \\\n",
       "0  For the main organisation discussed in this ar...          732   \n",
       "1  For the main organisation discussed in this ar...          833   \n",
       "2  For the main organisation discussed in this ar...         1720   \n",
       "3  For the main organisation discussed in this ar...          944   \n",
       "4  For the main organisation discussed in this ar...         2092   \n",
       "\n",
       "                                           responses  \\\n",
       "0  org: Nike Inc.\\ncountry: NA\\nstate: NA\\ncity: ...   \n",
       "1  org: Renault\\ncountry: NA\\nstate: NA\\ncity: NA...   \n",
       "2  org: Mersana Therapeutics Inc.\\ncountry: NA\\ns...   \n",
       "3  org: Galaxy Digital\\ncountry: NA\\nstate: NA\\nc...   \n",
       "4  org: RLI Corp.\\ncountry: NA\\nstate: NA\\ncity: ...   \n",
       "\n",
       "                                        responses_PP  \n",
       "0  {'org': 'Nike Inc.', 'country': '', 'state': '...  \n",
       "1  {'org': 'Renault', 'country': '', 'state': '',...  \n",
       "2  {'org': 'Mersana Therapeutics Inc.', 'country'...  \n",
       "3  {'org': 'Galaxy Digital', 'country': '', 'stat...  \n",
       "4  {'org': 'RLI Corp.', 'country': '', 'state': '...  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(len(df))\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51ae3691",
   "metadata": {},
   "source": [
    "## 100 randomly selected rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "7aeeefbe",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "100\n"
     ]
    }
   ],
   "source": [
    "# Randomly select 100 rows from df\n",
    "random_100 = df.sample(n=100, random_state=42)\n",
    "print(len(random_100))\n",
    "\n",
    "# export\n",
    "random_100.to_excel('./results/aylien_responses_summary/random_100.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0d73060",
   "metadata": {},
   "source": [
    "## No responses / no orgs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "9911bd0a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Length: 1730\n"
     ]
    }
   ],
   "source": [
    "# Filter the DataFrame based on the condition\n",
    "no_orgs = df[df[\"responses\"].isna()\n",
    "                     | df[\"responses_PP\"].str.contains(\"org':'',\") \n",
    "                     | df[\"responses_PP\"].str.contains(\"org': '',\") \n",
    "                     |(df[\"responses\"].str.strip().str.lower() == \"na\")]\n",
    "# Print the length and head of the filtered DataFrame\n",
    "print(\"Length:\", len(no_orgs))\n",
    "# no_orgs.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "12f53c87",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Filter the DataFrame based on the condition\n",
    "# no_orgs = df[df[\"responses\"].isna()\n",
    "#                      | df[\"responses\"].str.contains(\"org: NA\\n\") \n",
    "#                      | df[\"responses\"].str.contains(\"org:NA\\n\") \n",
    "#                      | df[\"responses\"].str.contains(\"org: na\\n\") \n",
    "#                      | df[\"responses\"].str.contains(\"org:na\\n\")\n",
    "#                      | df[\"responses\"].str.contains(\"org:\\n\")\n",
    "#                      |(df[\"responses\"].str.strip().str.lower() == \"na\")]\n",
    "# # Print the length and head of the filtered DataFrame\n",
    "# print(\"Length:\", len(no_orgs))\n",
    "# # no_orgs.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "90b3fa28",
   "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>Unnamed: 0</th>\n",
       "      <th>article_id</th>\n",
       "      <th>title</th>\n",
       "      <th>published_at</th>\n",
       "      <th>source</th>\n",
       "      <th>body</th>\n",
       "      <th>prompt</th>\n",
       "      <th>token_count</th>\n",
       "      <th>responses</th>\n",
       "      <th>responses_PP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1999</th>\n",
       "      <td>1999</td>\n",
       "      <td>5693902738</td>\n",
       "      <td>Precision Miniature Ball Screws Market: Enabli...</td>\n",
       "      <td>29-May-2023</td>\n",
       "      <td>OpenPR</td>\n",
       "      <td>Precision Miniature Ball Screws Market: Enabli...</td>\n",
       "      <td>For the main organisation discussed in this ar...</td>\n",
       "      <td>2263</td>\n",
       "      <td>org: NA</td>\n",
       "      <td>{'org': '', 'article_id': 5693902738, 'source'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3504</th>\n",
       "      <td>3504</td>\n",
       "      <td>5679258973</td>\n",
       "      <td>Empty offices a challenge to smaller banks: Fitch</td>\n",
       "      <td>24-May-2023</td>\n",
       "      <td>Advisor.ca (Canada)</td>\n",
       "      <td>The review, carried out in cooperation with se...</td>\n",
       "      <td>For the main organisation discussed in this ar...</td>\n",
       "      <td>746</td>\n",
       "      <td>org: NA\\ncountry: NA\\nstate: NA\\ncity: NA\\nind...</td>\n",
       "      <td>{'org': '', 'country': '', 'state': '', 'city'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2067</th>\n",
       "      <td>2067</td>\n",
       "      <td>5693470098</td>\n",
       "      <td>Key Reason Why Bitcoin Just Touched Highest Le...</td>\n",
       "      <td>29-May-2023</td>\n",
       "      <td>Upworthy</td>\n",
       "      <td>Bitcoin surged to two-week high of $28,452, wh...</td>\n",
       "      <td>For the main organisation discussed in this ar...</td>\n",
       "      <td>529</td>\n",
       "      <td>org: NA\\ncountry: NA\\nstate: NA\\ncity: NA\\nind...</td>\n",
       "      <td>{'org': '', 'country': '', 'state': '', 'city'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4792</th>\n",
       "      <td>4792</td>\n",
       "      <td>5669970254</td>\n",
       "      <td>Risk of Investing in Shiba Inu and Why Tradecu...</td>\n",
       "      <td>22-May-2023</td>\n",
       "      <td>CryptoDaily</td>\n",
       "      <td>Shiba Inu (SHIB) completely blew the crypto ma...</td>\n",
       "      <td>For the main organisation discussed in this ar...</td>\n",
       "      <td>1311</td>\n",
       "      <td>org: NA\\n</td>\n",
       "      <td>{'org': '', 'article_id': 5669970254, 'source'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3715</th>\n",
       "      <td>3715</td>\n",
       "      <td>5677984172</td>\n",
       "      <td>QRL: Leading The Charge Against Quantum Risk I...</td>\n",
       "      <td>24-May-2023</td>\n",
       "      <td>PR Inside</td>\n",
       "      <td>CHEYENNE, WY/ ACCESSWIRE/ May 24, 2023/- Amids...</td>\n",
       "      <td>For the main organisation discussed in this ar...</td>\n",
       "      <td>490</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'article_id': 5677984172, 'source': 'PR Inside'}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2784</th>\n",
       "      <td>2784</td>\n",
       "      <td>5684681831</td>\n",
       "      <td>Australian shares post worst week since mid-Ma...</td>\n",
       "      <td>26-May-2023</td>\n",
       "      <td>Business Times</td>\n",
       "      <td>AUSTRALIAN shares inched higher on Friday (May...</td>\n",
       "      <td>For the main organisation discussed in this ar...</td>\n",
       "      <td>481</td>\n",
       "      <td>org: NA\\ncountry: Australia\\nstate: NA\\ncity: ...</td>\n",
       "      <td>{'org': '', 'country': 'Australia', 'state': '...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>85</td>\n",
       "      <td>5708641643</td>\n",
       "      <td>Sustainable Bioenergy Market 2023 Business Gro...</td>\n",
       "      <td>02-June-2023</td>\n",
       "      <td>Digital Journal</td>\n",
       "      <td>Discover the hidden treasures of the “ Sustain...</td>\n",
       "      <td>For the main organisation discussed in this ar...</td>\n",
       "      <td>2561</td>\n",
       "      <td>org: NA\\n</td>\n",
       "      <td>{'org': '', 'article_id': 5708641643, 'source'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2572</th>\n",
       "      <td>2572</td>\n",
       "      <td>5686719070</td>\n",
       "      <td>GOP conservatives fume over possible debt ceil...</td>\n",
       "      <td>26-May-2023</td>\n",
       "      <td>Ground News</td>\n",
       "      <td>Hard-line conservatives are fuming over the de...</td>\n",
       "      <td>For the main organisation discussed in this ar...</td>\n",
       "      <td>547</td>\n",
       "      <td>org: NA\\ncountry: NA\\nstate: NA\\ncity: NA\\nind...</td>\n",
       "      <td>{'org': '', 'country': '', 'state': '', 'city'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2868</th>\n",
       "      <td>2868</td>\n",
       "      <td>5684004542</td>\n",
       "      <td>Internal Server Error .</td>\n",
       "      <td>26-May-2023</td>\n",
       "      <td>Ground News</td>\n",
       "      <td>NDTV Lean Left Mixed Factuality Independent: I...</td>\n",
       "      <td>For the main organisation discussed in this ar...</td>\n",
       "      <td>543</td>\n",
       "      <td>org: NA\\ncountry: NA\\nstate: NA\\ncity: NA\\nind...</td>\n",
       "      <td>{'org': '', 'country': '', 'state': '', 'city'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2365</th>\n",
       "      <td>2365</td>\n",
       "      <td>5689309760</td>\n",
       "      <td>PCs Recognize Paramedics Service Week</td>\n",
       "      <td>27-May-2023</td>\n",
       "      <td>VOCM</td>\n",
       "      <td>The official opposition is recognizing the wor...</td>\n",
       "      <td>For the main organisation discussed in this ar...</td>\n",
       "      <td>575</td>\n",
       "      <td>org: NA</td>\n",
       "      <td>{'org': '', 'article_id': 5689309760, 'source'...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Unnamed: 0  article_id  \\\n",
       "1999        1999  5693902738   \n",
       "3504        3504  5679258973   \n",
       "2067        2067  5693470098   \n",
       "4792        4792  5669970254   \n",
       "3715        3715  5677984172   \n",
       "2784        2784  5684681831   \n",
       "85            85  5708641643   \n",
       "2572        2572  5686719070   \n",
       "2868        2868  5684004542   \n",
       "2365        2365  5689309760   \n",
       "\n",
       "                                                  title  published_at  \\\n",
       "1999  Precision Miniature Ball Screws Market: Enabli...   29-May-2023   \n",
       "3504  Empty offices a challenge to smaller banks: Fitch   24-May-2023   \n",
       "2067  Key Reason Why Bitcoin Just Touched Highest Le...   29-May-2023   \n",
       "4792  Risk of Investing in Shiba Inu and Why Tradecu...   22-May-2023   \n",
       "3715  QRL: Leading The Charge Against Quantum Risk I...   24-May-2023   \n",
       "2784  Australian shares post worst week since mid-Ma...   26-May-2023   \n",
       "85    Sustainable Bioenergy Market 2023 Business Gro...  02-June-2023   \n",
       "2572  GOP conservatives fume over possible debt ceil...   26-May-2023   \n",
       "2868                            Internal Server Error .   26-May-2023   \n",
       "2365              PCs Recognize Paramedics Service Week   27-May-2023   \n",
       "\n",
       "                   source                                               body  \\\n",
       "1999               OpenPR  Precision Miniature Ball Screws Market: Enabli...   \n",
       "3504  Advisor.ca (Canada)  The review, carried out in cooperation with se...   \n",
       "2067             Upworthy  Bitcoin surged to two-week high of $28,452, wh...   \n",
       "4792          CryptoDaily  Shiba Inu (SHIB) completely blew the crypto ma...   \n",
       "3715            PR Inside  CHEYENNE, WY/ ACCESSWIRE/ May 24, 2023/- Amids...   \n",
       "2784       Business Times  AUSTRALIAN shares inched higher on Friday (May...   \n",
       "85        Digital Journal  Discover the hidden treasures of the “ Sustain...   \n",
       "2572          Ground News  Hard-line conservatives are fuming over the de...   \n",
       "2868          Ground News  NDTV Lean Left Mixed Factuality Independent: I...   \n",
       "2365                 VOCM  The official opposition is recognizing the wor...   \n",
       "\n",
       "                                                 prompt  token_count  \\\n",
       "1999  For the main organisation discussed in this ar...         2263   \n",
       "3504  For the main organisation discussed in this ar...          746   \n",
       "2067  For the main organisation discussed in this ar...          529   \n",
       "4792  For the main organisation discussed in this ar...         1311   \n",
       "3715  For the main organisation discussed in this ar...          490   \n",
       "2784  For the main organisation discussed in this ar...          481   \n",
       "85    For the main organisation discussed in this ar...         2561   \n",
       "2572  For the main organisation discussed in this ar...          547   \n",
       "2868  For the main organisation discussed in this ar...          543   \n",
       "2365  For the main organisation discussed in this ar...          575   \n",
       "\n",
       "                                              responses  \\\n",
       "1999                                            org: NA   \n",
       "3504  org: NA\\ncountry: NA\\nstate: NA\\ncity: NA\\nind...   \n",
       "2067  org: NA\\ncountry: NA\\nstate: NA\\ncity: NA\\nind...   \n",
       "4792                                          org: NA\\n   \n",
       "3715                                                NaN   \n",
       "2784  org: NA\\ncountry: Australia\\nstate: NA\\ncity: ...   \n",
       "85                                            org: NA\\n   \n",
       "2572  org: NA\\ncountry: NA\\nstate: NA\\ncity: NA\\nind...   \n",
       "2868  org: NA\\ncountry: NA\\nstate: NA\\ncity: NA\\nind...   \n",
       "2365                                            org: NA   \n",
       "\n",
       "                                           responses_PP  \n",
       "1999  {'org': '', 'article_id': 5693902738, 'source'...  \n",
       "3504  {'org': '', 'country': '', 'state': '', 'city'...  \n",
       "2067  {'org': '', 'country': '', 'state': '', 'city'...  \n",
       "4792  {'org': '', 'article_id': 5669970254, 'source'...  \n",
       "3715  {'article_id': 5677984172, 'source': 'PR Inside'}  \n",
       "2784  {'org': '', 'country': 'Australia', 'state': '...  \n",
       "85    {'org': '', 'article_id': 5708641643, 'source'...  \n",
       "2572  {'org': '', 'country': '', 'state': '', 'city'...  \n",
       "2868  {'org': '', 'country': '', 'state': '', 'city'...  \n",
       "2365  {'org': '', 'article_id': 5689309760, 'source'...  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Randomly select 10 rows from no_orgs\n",
    "random_rows = no_orgs.sample(n=10, random_state=42)\n",
    "\n",
    "# Print the randomly selected rows\n",
    "random_rows"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f02d7d8",
   "metadata": {},
   "source": [
    "## Contains orgs (opposite of above)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ff1639f5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Length: 3199\n"
     ]
    },
    {
     "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>Unnamed: 0</th>\n",
       "      <th>article_id</th>\n",
       "      <th>title</th>\n",
       "      <th>published_at</th>\n",
       "      <th>source</th>\n",
       "      <th>body</th>\n",
       "      <th>prompt</th>\n",
       "      <th>token_count</th>\n",
       "      <th>responses</th>\n",
       "      <th>responses_PP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>5709294677</td>\n",
       "      <td>: Nike stock jumps to lead the Dow's premarket...</td>\n",
       "      <td>02-June-2023</td>\n",
       "      <td>Finanzen.ch</td>\n",
       "      <td>Nike  96.41 CHF -1.99%  Charts  News  Analysen...</td>\n",
       "      <td>For the main organisation discussed in this ar...</td>\n",
       "      <td>732</td>\n",
       "      <td>org: Nike Inc.\\ncountry: NA\\nstate: NA\\ncity: ...</td>\n",
       "      <td>{'org': 'Nike Inc.', 'country': '', 'state': '...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>5709292390</td>\n",
       "      <td>Renault Customers Planning Criminal Complaint ...</td>\n",
       "      <td>02-June-2023</td>\n",
       "      <td>MarketScreener</td>\n",
       "      <td>Financials EUR USD GBP Sales 2023 50 468 M 54 ...</td>\n",
       "      <td>For the main organisation discussed in this ar...</td>\n",
       "      <td>833</td>\n",
       "      <td>org: Renault\\ncountry: NA\\nstate: NA\\ncity: NA...</td>\n",
       "      <td>{'org': 'Renault', 'country': '', 'state': '',...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>5709276868</td>\n",
       "      <td>Mersana Therapeutics Inc. (MRSN) last month vo...</td>\n",
       "      <td>02-June-2023</td>\n",
       "      <td>Newsdaemon.com</td>\n",
       "      <td>Search  Search   Search  Search  Sana Meer  Ju...</td>\n",
       "      <td>For the main organisation discussed in this ar...</td>\n",
       "      <td>1720</td>\n",
       "      <td>org: Mersana Therapeutics Inc.\\ncountry: NA\\ns...</td>\n",
       "      <td>{'org': 'Mersana Therapeutics Inc.', 'country'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>5709269010</td>\n",
       "      <td>Mike Novogratz Says Crypto is Lackadaisical Ri...</td>\n",
       "      <td>02-June-2023</td>\n",
       "      <td>Crypto Potato</td>\n",
       "      <td>Michael Novogratz, the founder and CEO of Gala...</td>\n",
       "      <td>For the main organisation discussed in this ar...</td>\n",
       "      <td>944</td>\n",
       "      <td>org: Galaxy Digital\\ncountry: NA\\nstate: NA\\nc...</td>\n",
       "      <td>{'org': 'Galaxy Digital', 'country': '', 'stat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>5709271283</td>\n",
       "      <td>Another Soft Market Is Inevitable</td>\n",
       "      <td>02-June-2023</td>\n",
       "      <td>Carrier Management</td>\n",
       "      <td>New You can now listen to Carrier Management a...</td>\n",
       "      <td>For the main organisation discussed in this ar...</td>\n",
       "      <td>2092</td>\n",
       "      <td>org: RLI Corp.\\ncountry: NA\\nstate: NA\\ncity: ...</td>\n",
       "      <td>{'org': 'RLI Corp.', 'country': '', 'state': '...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  article_id                                              title  \\\n",
       "0           0  5709294677  : Nike stock jumps to lead the Dow's premarket...   \n",
       "1           1  5709292390  Renault Customers Planning Criminal Complaint ...   \n",
       "2           2  5709276868  Mersana Therapeutics Inc. (MRSN) last month vo...   \n",
       "3           3  5709269010  Mike Novogratz Says Crypto is Lackadaisical Ri...   \n",
       "4           4  5709271283                  Another Soft Market Is Inevitable   \n",
       "\n",
       "   published_at              source  \\\n",
       "0  02-June-2023         Finanzen.ch   \n",
       "1  02-June-2023      MarketScreener   \n",
       "2  02-June-2023      Newsdaemon.com   \n",
       "3  02-June-2023       Crypto Potato   \n",
       "4  02-June-2023  Carrier Management   \n",
       "\n",
       "                                                body  \\\n",
       "0  Nike  96.41 CHF -1.99%  Charts  News  Analysen...   \n",
       "1  Financials EUR USD GBP Sales 2023 50 468 M 54 ...   \n",
       "2  Search  Search   Search  Search  Sana Meer  Ju...   \n",
       "3  Michael Novogratz, the founder and CEO of Gala...   \n",
       "4  New You can now listen to Carrier Management a...   \n",
       "\n",
       "                                              prompt  token_count  \\\n",
       "0  For the main organisation discussed in this ar...          732   \n",
       "1  For the main organisation discussed in this ar...          833   \n",
       "2  For the main organisation discussed in this ar...         1720   \n",
       "3  For the main organisation discussed in this ar...          944   \n",
       "4  For the main organisation discussed in this ar...         2092   \n",
       "\n",
       "                                           responses  \\\n",
       "0  org: Nike Inc.\\ncountry: NA\\nstate: NA\\ncity: ...   \n",
       "1  org: Renault\\ncountry: NA\\nstate: NA\\ncity: NA...   \n",
       "2  org: Mersana Therapeutics Inc.\\ncountry: NA\\ns...   \n",
       "3  org: Galaxy Digital\\ncountry: NA\\nstate: NA\\nc...   \n",
       "4  org: RLI Corp.\\ncountry: NA\\nstate: NA\\ncity: ...   \n",
       "\n",
       "                                        responses_PP  \n",
       "0  {'org': 'Nike Inc.', 'country': '', 'state': '...  \n",
       "1  {'org': 'Renault', 'country': '', 'state': '',...  \n",
       "2  {'org': 'Mersana Therapeutics Inc.', 'country'...  \n",
       "3  {'org': 'Galaxy Digital', 'country': '', 'stat...  \n",
       "4  {'org': 'RLI Corp.', 'country': '', 'state': '...  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Filter the DataFrame to get the opposite of no_orgs\n",
    "# and check for semicolon in 'responses' or 'article_id' in 'no_orgs'\n",
    "contains_orgs = df[~(df[\"article_id\"].isin(no_orgs[\"article_id\"]))]\n",
    "contains_orgs = contains_orgs[~(contains_orgs[\"responses\"].str.contains(r\"org:[^;\\n]*;\"))]\n",
    "\n",
    "# Print the length and head of the contains_orgs DataFrame\n",
    "print(\"Length:\", len(contains_orgs))\n",
    "contains_orgs.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "0cb2cfb2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Filter the DataFrame to get the opposite of no_orgs\n",
    "# # but also filter out where multiple orgs are detected (signified by ;)\n",
    "# contains_orgs = df[~(df[\"responses\"].isna() \n",
    "#                      | df[\"responses\"].str.contains(\"org: NA\\n\") \n",
    "#                      | df[\"responses\"].str.contains(\"org:NA\\n\") \n",
    "#                      | df[\"responses\"].str.contains(\"org: na\\n\") \n",
    "#                      | df[\"responses\"].str.contains(\"org:na\\n\")\n",
    "#                      |(df[\"responses\"].str.strip().str.lower() == \"na\")\n",
    "#                      | df[\"responses\"].str.contains(r\"org:[^;\\n]*;\"))]\n",
    "\n",
    "# # Print the length and head of the contains_orgs DataFrame\n",
    "# print(\"Length:\", len(contains_orgs))\n",
    "# contains_orgs.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ad00353",
   "metadata": {},
   "source": [
    "### Distribution of unique items\n",
    "#### unique orgs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "d2fd57b8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2097\n"
     ]
    },
    {
     "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>ORG</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ARC Automotive Inc.</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Waste Management, Inc.</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Target</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Boozt AB</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Avid Telecom</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      ORG  count\n",
       "0     ARC Automotive Inc.     40\n",
       "1  Waste Management, Inc.     39\n",
       "2                  Target     32\n",
       "3                Boozt AB     21\n",
       "4            Avid Telecom     20"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert string representations to dictionaries\n",
    "contains_orgs['responses_PP_ast'] = contains_orgs['responses_PP'].apply(ast.literal_eval)\n",
    "\n",
    "# Extract 'org' values and count the unique organizations\n",
    "org_counts = contains_orgs['responses_PP_ast'].apply(lambda x: x.get('org', np.nan)).dropna().value_counts()\n",
    "\n",
    "# Create a DataFrame with 'ORG' and 'count' columns, excluding blank org values\n",
    "org_dist = pd.DataFrame({'ORG': org_counts.index, 'count': org_counts.values})\n",
    "\n",
    "# Remove leading and trailing whitespace from 'ORG' column\n",
    "org_dist['ORG'] = org_dist['ORG'].str.strip()\n",
    "\n",
    "# Filter out rows with empty 'ORG' values\n",
    "org_dist = org_dist[org_dist['ORG'] != '']\n",
    "\n",
    "# Write the DataFrame to an Excel file\n",
    "org_dist.to_excel('./results/aylien_responses_summary/org_dist.xlsx', index=False)\n",
    "\n",
    "print(len(org_dist))\n",
    "org_dist.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4c0daa5",
   "metadata": {},
   "source": [
    "### all unique items"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "ad4fec3d",
   "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>item</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>org</td>\n",
       "      <td>2097</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>country</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>state</td>\n",
       "      <td>107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>city</td>\n",
       "      <td>373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>industry</td>\n",
       "      <td>879</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>risks</td>\n",
       "      <td>1219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>items_sold</td>\n",
       "      <td>1040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>service_provided</td>\n",
       "      <td>2129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>business_relations</td>\n",
       "      <td>1430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>article_id</td>\n",
       "      <td>3199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>source</td>\n",
       "      <td>1151</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  item  count\n",
       "0                  org   2097\n",
       "1              country     88\n",
       "2                state    107\n",
       "3                 city    373\n",
       "4             industry    879\n",
       "5                risks   1219\n",
       "6           items_sold   1040\n",
       "7     service_provided   2129\n",
       "8   business_relations   1430\n",
       "9           article_id   3199\n",
       "10              source   1151"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Function to split values and count unique items separately\n",
    "def count_unique_items(field):\n",
    "    unique_items = contains_orgs['responses_PP_ast'].apply(lambda x: str(x.get(field)))\n",
    "    unique_items = unique_items.dropna().str.split(';').explode().str.strip()\n",
    "    unique_items_count = unique_items.nunique()\n",
    "    return unique_items_count\n",
    "\n",
    "# Fields for which you want to get the count of unique items\n",
    "fields = ['org', 'country', 'state', 'city', 'industry', 'risks', 'items_sold', 'service_provided', 'business_relations', 'article_id', 'source']\n",
    "\n",
    "# List to store item and count pairs\n",
    "summary_list = []\n",
    "\n",
    "# Iterate over the fields and get the count of unique items\n",
    "for field in fields:\n",
    "    unique_item_count = count_unique_items(field)\n",
    "    summary_list.append((field, unique_item_count))\n",
    "\n",
    "# Create a DataFrame from the summary list\n",
    "summary_df = pd.DataFrame(summary_list, columns=['item', 'count'])\n",
    "\n",
    "# Return the summary DataFrame\n",
    "summary_df\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f718a8d5",
   "metadata": {},
   "source": [
    "## Longest responses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "73973760",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10\n"
     ]
    }
   ],
   "source": [
    "# Calculate the length of data in the 'responses' column\n",
    "df['response_length'] = df['responses'].str.len()\n",
    "\n",
    "# Get the top 10 longest responses\n",
    "top_10_longest_responses = df.nlargest(10, 'response_length')\n",
    "\n",
    "# Print the top 10 longest responses\n",
    "print(len(top_10_longest_responses))\n",
    "top_10_longest_responses.head()\n",
    "\n",
    "# export\n",
    "top_10_longest_responses.to_excel('./results/aylien_responses_summary/top_10_longest_responses.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f794b018",
   "metadata": {},
   "source": [
    "### Longset articles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "3b44b063",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10\n"
     ]
    }
   ],
   "source": [
    "# Calculate the length of data in the 'body' column\n",
    "df['body_length'] = df['body'].str.len()\n",
    "\n",
    "# Get the top 10 longest values in the 'body' column\n",
    "top_10_longest_articles = df.nlargest(10, 'body_length')\n",
    "\n",
    "# Print the top 10 longest responses\n",
    "print(len(top_10_longest_articles))\n",
    "top_10_longest_articles\n",
    "\n",
    "# export\n",
    "top_10_longest_articles.to_excel('./results/aylien_responses_summary/top_10_longest_articles.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0282e1f6",
   "metadata": {},
   "source": [
    "### Shortest articles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "67908942",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate the length of data in the 'body' column\n",
    "df['body_length'] = df['body'].str.len()\n",
    "\n",
    "# Get the indices of the top 10 shortest articles in ascending order\n",
    "indices = df.nsmallest(10, 'body_length').index\n",
    "\n",
    "# Retrieve the top 10 shortest articles using the sorted indices\n",
    "top_10_shortest_articles = df.loc[indices]\n",
    "top_10_shortest_articles\n",
    "\n",
    "# export\n",
    "top_10_shortest_articles.to_excel('./results/aylien_responses_summary/top_10_shortest_articles.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "343b20a1",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.16"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}