061_EntityExtraction_Spacy.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b23b7367-0f34-46d7-9f11-bde5542085ee",
   "metadata": {},
   "source": [
    "# NB 6.1: Entity Extraction"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3305d02-cd1a-45a2-9056-67a5f7396996",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "## Part 0: Importing Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b94c9b60-d8c7-4605-9e40-7f21063ea690",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import requests\n",
    "import re\n",
    "import sys\n",
    "import regex as re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "0751f4e7-58f4-440d-9231-4c807df3e1d1",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2023-05-23 00:00:09.606605: I tensorflow/core/platform/cpu_feature_guard.cc:193] This TensorFlow binary is optimized with oneAPI Deep Neural Network Library (oneDNN) to use the following CPU instructions in performance-critical operations:  AVX2 FMA\n",
      "To enable them in other operations, rebuild TensorFlow with the appropriate compiler flags.\n",
      "2023-05-23 00:00:10.792332: W tensorflow/compiler/xla/stream_executor/platform/default/dso_loader.cc:64] Could not load dynamic library 'libnvinfer.so.7'; dlerror: libnvinfer.so.7: cannot open shared object file: No such file or directory; LD_LIBRARY_PATH: /usr/local/cuda/lib64:/usr/local/nccl2/lib:/usr/local/cuda/extras/CUPTI/lib64\n",
      "2023-05-23 00:00:10.792497: W tensorflow/compiler/xla/stream_executor/platform/default/dso_loader.cc:64] Could not load dynamic library 'libnvinfer_plugin.so.7'; dlerror: libnvinfer_plugin.so.7: cannot open shared object file: No such file or directory; LD_LIBRARY_PATH: /usr/local/cuda/lib64:/usr/local/nccl2/lib:/usr/local/cuda/extras/CUPTI/lib64\n",
      "2023-05-23 00:00:10.792513: W tensorflow/compiler/tf2tensorrt/utils/py_utils.cc:38] TF-TRT Warning: Cannot dlopen some TensorRT libraries. If you would like to use Nvidia GPU with TensorRT, please make sure the missing libraries mentioned above are installed properly.\n",
      "2023-05-23 00:00:11.941808: W tensorflow/compiler/xla/stream_executor/platform/default/dso_loader.cc:64] Could not load dynamic library 'libcuda.so.1'; dlerror: libcuda.so.1: cannot open shared object file: No such file or directory; LD_LIBRARY_PATH: /usr/local/cuda/lib64:/usr/local/nccl2/lib:/usr/local/cuda/extras/CUPTI/lib64\n",
      "2023-05-23 00:00:11.941862: W tensorflow/compiler/xla/stream_executor/cuda/cuda_driver.cc:265] failed call to cuInit: UNKNOWN ERROR (303)\n",
      "2023-05-23 00:00:11.941893: I tensorflow/compiler/xla/stream_executor/cuda/cuda_diagnostics.cc:156] kernel driver does not appear to be running on this host (nlp-final-project-notebook): /proc/driver/nvidia/version does not exist\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<spacy.lang.en.English at 0x7f277e8b2550>"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import spacy\n",
    "\n",
    "# Using Spacy Large\n",
    "# !python -m spacy download en_core_web_lg\n",
    "nlp = spacy.load(\"en_core_web_lg\", disable=[\"tagger\", \"parser\"])\n",
    "\n",
    "# Enable only NER\n",
    "nlp.enable_pipe(\"ner\")\n",
    "nlp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "01b6d2d0-a258-4a4c-98eb-b7eb56704ed3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import nltk as nltk\n",
    "import nltk.corpus  \n",
    "from nltk.text import Text\n",
    "\n",
    "# nltk.download('punkt')\n",
    "# nltk.download('stopwords')\n",
    "# nltk.download('wordnet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e6aa99c9-07be-4cd2-96a5-aef05889b7e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import sklearn\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "import seaborn as sns\n",
    "# Graphics in SVG format are more sharp and legible\n",
    "%config InlineBackend.figure_format = 'svg'\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4f82a944-2606-4664-ba5f-75b96072413f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Available CPUs: 16\n",
      "INFO: Pandarallel will run on 15 workers.\n",
      "INFO: Pandarallel will use standard multiprocessing data transfer (pipe) to transfer data between the main process and workers.\n"
     ]
    }
   ],
   "source": [
    "#pip install pandarallel\n",
    "import multiprocessing\n",
    "\n",
    "num_processors = multiprocessing.cpu_count()\n",
    "print(f'Available CPUs: {num_processors}')\n",
    "\n",
    "import pandarallel\n",
    "from pandarallel import pandarallel\n",
    "pandarallel.initialize(nb_workers=num_processors-1, use_memory_fs=False, progress_bar=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "0a4777e0-4cb3-4c4d-bffa-97304fc90234",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Bucket nlp_final_project_kshitijm connected.\n"
     ]
    }
   ],
   "source": [
    "# Imports the Google Cloud client library\n",
    "from google.cloud import storage\n",
    "# Instantiates a client\n",
    "storage_client = storage.Client()\n",
    "\n",
    "# The name for the new bucket\n",
    "bucket_name = \"nlp_final_project_kshitijm\"\n",
    "\n",
    "# Creates the new bucket\n",
    "bucket = storage_client.bucket(bucket_name)\n",
    "print(f\"Bucket {bucket.name} connected.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "de71ef34-0253-4fdb-abf1-e400d54c162e",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tqdm import tqdm\n",
    "from collections import Counter\n",
    "from itertools import chain"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e999d4bc-b5f0-4adb-a6ea-b1e016119031",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "## Part 1: Running NER for each year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5bcef9f3-d1a2-4b88-b1b9-60785b08d2b4",
   "metadata": {},
   "outputs": [],
   "source": [
    "def spacy_ner(x):\n",
    "  doc = nlp(x)\n",
    "  entities = []\n",
    "  labels = []\n",
    "  for ent in doc.ents:\n",
    "    entities.append(ent.text)\n",
    "    labels.append(ent.label_)\n",
    "  entities_labels = list(zip(entities, labels)) #we do not want unique entities\n",
    "  return entities_labels"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "94726e22-8d0c-40b3-b16d-d6cadc4bf527",
   "metadata": {},
   "source": [
    "**2020**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "7c6f4478-6b47-4093-96a6-d82b3e5b50f1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 19 s, sys: 2.51 s, total: 21.5 s\n",
      "Wall time: 1min 1s\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>url</th>\n",
       "      <th>date</th>\n",
       "      <th>language</th>\n",
       "      <th>title</th>\n",
       "      <th>text</th>\n",
       "      <th>cleaned_text</th>\n",
       "      <th>article_source</th>\n",
       "      <th>clean_title</th>\n",
       "      <th>title_tokens</th>\n",
       "      <th>...</th>\n",
       "      <th>year_pub</th>\n",
       "      <th>month</th>\n",
       "      <th>month-year</th>\n",
       "      <th>flag_relevant</th>\n",
       "      <th>num_tokens</th>\n",
       "      <th>rake_phrases_articles</th>\n",
       "      <th>rake_phrases_joined</th>\n",
       "      <th>final_topic</th>\n",
       "      <th>sent_probs</th>\n",
       "      <th>sent_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>https://fusionscienceacademy.com/artificial-in...</td>\n",
       "      <td>2020-01-30</td>\n",
       "      <td>en</td>\n",
       "      <td>Artificial Intelligence (AI) in Social Media  ...</td>\n",
       "      <td>\\n\\nArtificial Intelligence (AI) in Social Med...</td>\n",
       "      <td>Artificial Intelligence AI in Social MediaMar...</td>\n",
       "      <td>2026 – Fusion Science Academy</td>\n",
       "      <td>Artificial Intelligence (AI) in Social Media  ...</td>\n",
       "      <td>['artificial', 'intelligence', 'ai', 'social',...</td>\n",
       "      <td>...</td>\n",
       "      <td>2020</td>\n",
       "      <td>1</td>\n",
       "      <td>Jan 2020</td>\n",
       "      <td>1</td>\n",
       "      <td>22283</td>\n",
       "      <td>['burkert fluid control systems emerson electr...</td>\n",
       "      <td>burkert fluid control systems emerson electric...</td>\n",
       "      <td>6</td>\n",
       "      <td>[[2.1574432e-04 9.9863547e-01 1.1488239e-03]]</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>https://health.economictimes.indiatimes.com/ne...</td>\n",
       "      <td>2020-01-10</td>\n",
       "      <td>en</td>\n",
       "      <td>artificial intelligence: Researchers develop A...</td>\n",
       "      <td>\\n\\nartificial intelligence: Researchers devel...</td>\n",
       "      <td>artificial intelligence: Researchers develop ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>artificial intelligence: Researchers develop A...</td>\n",
       "      <td>['artificial', 'intelligence', 'researchers', ...</td>\n",
       "      <td>...</td>\n",
       "      <td>2020</td>\n",
       "      <td>1</td>\n",
       "      <td>Jan 2020</td>\n",
       "      <td>1</td>\n",
       "      <td>8087</td>\n",
       "      <td>['economic times ethealthworldhome news hospit...</td>\n",
       "      <td>economic times ethealthworldhome news hospital...</td>\n",
       "      <td>1</td>\n",
       "      <td>[[1.9508268e-04 8.0524624e-04 9.9899966e-01]]</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>https://heraldpublicist.com/bet-gil-on-ai-fina...</td>\n",
       "      <td>2020-01-15</td>\n",
       "      <td>en</td>\n",
       "      <td>Bet Gil on AI Final Fantasy Tactics Matches in...</td>\n",
       "      <td>\\n\\nBet Gil on AI Final Fantasy Tactics Matche...</td>\n",
       "      <td>Bet Gil on AI Final Fantasy Tactics Matches i...</td>\n",
       "      <td>Herald Publicist</td>\n",
       "      <td>Bet Gil on AI Final Fantasy Tactics Matches in...</td>\n",
       "      <td>['bet', 'gil', 'ai', 'final', 'fantasy', 'tact...</td>\n",
       "      <td>...</td>\n",
       "      <td>2020</td>\n",
       "      <td>1</td>\n",
       "      <td>Jan 2020</td>\n",
       "      <td>1</td>\n",
       "      <td>4458</td>\n",
       "      <td>['hilarious twitch streamnewstechnologycricket...</td>\n",
       "      <td>hilarious twitch streamnewstechnologycricketpo...</td>\n",
       "      <td>1</td>\n",
       "      <td>[[5.5355614e-04 9.9602497e-01 3.4214482e-03]]</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0                                                url        date  \\\n",
       "0           0  https://fusionscienceacademy.com/artificial-in...  2020-01-30   \n",
       "1           1  https://health.economictimes.indiatimes.com/ne...  2020-01-10   \n",
       "2           2  https://heraldpublicist.com/bet-gil-on-ai-fina...  2020-01-15   \n",
       "\n",
       "  language                                              title  \\\n",
       "0       en  Artificial Intelligence (AI) in Social Media  ...   \n",
       "1       en  artificial intelligence: Researchers develop A...   \n",
       "2       en  Bet Gil on AI Final Fantasy Tactics Matches in...   \n",
       "\n",
       "                                                text  \\\n",
       "0  \\n\\nArtificial Intelligence (AI) in Social Med...   \n",
       "1  \\n\\nartificial intelligence: Researchers devel...   \n",
       "2  \\n\\nBet Gil on AI Final Fantasy Tactics Matche...   \n",
       "\n",
       "                                        cleaned_text  \\\n",
       "0   Artificial Intelligence AI in Social MediaMar...   \n",
       "1   artificial intelligence: Researchers develop ...   \n",
       "2   Bet Gil on AI Final Fantasy Tactics Matches i...   \n",
       "\n",
       "                  article_source  \\\n",
       "0  2026 – Fusion Science Academy   \n",
       "1                            NaN   \n",
       "2               Herald Publicist   \n",
       "\n",
       "                                         clean_title  \\\n",
       "0  Artificial Intelligence (AI) in Social Media  ...   \n",
       "1  artificial intelligence: Researchers develop A...   \n",
       "2  Bet Gil on AI Final Fantasy Tactics Matches in...   \n",
       "\n",
       "                                        title_tokens  ... year_pub  month  \\\n",
       "0  ['artificial', 'intelligence', 'ai', 'social',...  ...     2020      1   \n",
       "1  ['artificial', 'intelligence', 'researchers', ...  ...     2020      1   \n",
       "2  ['bet', 'gil', 'ai', 'final', 'fantasy', 'tact...  ...     2020      1   \n",
       "\n",
       "   month-year flag_relevant  num_tokens  \\\n",
       "0    Jan 2020             1       22283   \n",
       "1    Jan 2020             1        8087   \n",
       "2    Jan 2020             1        4458   \n",
       "\n",
       "                               rake_phrases_articles  \\\n",
       "0  ['burkert fluid control systems emerson electr...   \n",
       "1  ['economic times ethealthworldhome news hospit...   \n",
       "2  ['hilarious twitch streamnewstechnologycricket...   \n",
       "\n",
       "                                 rake_phrases_joined final_topic  \\\n",
       "0  burkert fluid control systems emerson electric...           6   \n",
       "1  economic times ethealthworldhome news hospital...           1   \n",
       "2  hilarious twitch streamnewstechnologycricketpo...           1   \n",
       "\n",
       "                                      sent_probs sent_label  \n",
       "0  [[2.1574432e-04 9.9863547e-01 1.1488239e-03]]          1  \n",
       "1  [[1.9508268e-04 8.0524624e-04 9.9899966e-01]]          2  \n",
       "2  [[5.5355614e-04 9.9602497e-01 3.4214482e-03]]          1  \n",
       "\n",
       "[3 rows x 21 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "df_filt_2020=pd.read_csv('gs://nlp_final_project_kshitijm/00_Data/NLP_FP_Data5_2020_Topics_Sentiments.csv',lineterminator='\\n')\n",
    "df_filt_2020.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "a0a17382-1b61-4839-813f-5928eee4dd76",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "33f8f7005ad8407eab5465cc39316ecb",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(children=(IntProgress(value=0, description='0.00%', max=2178), Label(value='0 / 2178'))), …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 32667/32667 [00:00<00:00, 786346.25it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 35.9 s, sys: 4.79 s, total: 40.7 s\n",
      "Wall time: 8min 14s\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "tqdm.pandas()\n",
    "df_filt_2020['entities_spacy'] = df_filt_2020['cleaned_text'].parallel_apply(lambda x: spacy_ner(x)).progress_apply(lambda x: x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8cc1b1c-0006-49b5-b1b6-f03c0d2755e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_filt_2020['ENT_ORG'] = df_filt_2020['entities_spacy'].parallel_apply(lambda x: [tup[0] for tup in x if tup[1] == \"ORG\"])\n",
    "df_filt_2020['ENT_PROD'] = df_filt_2020['entities_spacy'].parallel_apply(lambda x: [tup[0] for tup in x if tup[1] == \"PRODUCT\"])\n",
    "df_filt_2020['ENT_PER'] = df_filt_2020['entities_spacy'].parallel_apply(lambda x: [tup[0] for tup in x if tup[1] == \"PERSON\"])\n",
    "df_filt_2020['ENT_NORP'] = df_filt_2020['entities_spacy'].parallel_apply(lambda x: [tup[0] for tup in x if tup[1] == \"NORP\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "f7b8a135-3b7f-4c73-926b-9f000281f2c7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 35.5 s, sys: 525 ms, total: 36 s\n",
      "Wall time: 1min 4s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "##SAVED AS THE WRONG FILE NAME BY MISTAKE, CORRECT LATER\n",
    "df_filt_2020.to_csv('gs://nlp_final_project_kshitijm/00_Data/NLP_FP_Data5_2021_Topics_Sentiments_NER.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1db41014-2002-4108-b886-b3c2ccf275b3",
   "metadata": {},
   "source": [
    "----\n",
    "**2021**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "d15fdaf8-2251-41a3-aa35-3ee3c69c84c2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a12d78c01ba84423b88d34cd0df8b5b0",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(children=(IntProgress(value=0, description='0.00%', max=2858), Label(value='0 / 2858'))), …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_filt_2021=pd.read_csv('gs://nlp_final_project_kshitijm/00_Data/NLP_FP_Data5_2021_Topics_Sentiments.csv',lineterminator='\\n')\n",
    "df_filt_2021['entities_spacy'] = df_filt_2021['cleaned_text'].parallel_apply(lambda x: spacy_ner(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "990b239e-afb1-4cb7-913c-60d1e492c0df",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_filt_2021['ENT_ORG'] = df_filt_2021['entities_spacy'].parallel_apply(lambda x: [tup[0] for tup in x if tup[1] == \"ORG\"])\n",
    "df_filt_2021['ENT_PROD'] = df_filt_2021['entities_spacy'].parallel_apply(lambda x: [tup[0] for tup in x if tup[1] == \"PRODUCT\"])\n",
    "df_filt_2021['ENT_PER'] = df_filt_2021['entities_spacy'].parallel_apply(lambda x: [tup[0] for tup in x if tup[1] == \"PERSON\"])\n",
    "df_filt_2021['ENT_NORP'] = df_filt_2021['entities_spacy'].parallel_apply(lambda x: [tup[0] for tup in x if tup[1] == \"NORP\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "1b528fc5-bf26-4e29-8f8c-5074cc6f697e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 46.5 s, sys: 808 ms, total: 47.3 s\n",
      "Wall time: 1min 18s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_filt_2021.to_csv('gs://nlp_final_project_kshitijm/00_Data/NLP_FP_Data6_2021v2_Topics_Sentiments_NER.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27ae4f8d-8d76-4192-82bb-e7f44de89f1a",
   "metadata": {},
   "source": [
    "------\n",
    "**2022**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "178492df-3228-434e-a39e-5821695d3958",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "bdc7bc665e6645b3aa3f129a567a5931",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(children=(IntProgress(value=0, description='0.00%', max=3663), Label(value='0 / 3663'))), …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "cca4e6c7cc4b453296e734bc182e7961",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(children=(IntProgress(value=0, description='0.00%', max=3663), Label(value='0 / 3663'))), …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "41497b4965a24cd5b9e14bda551e07f8",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(children=(IntProgress(value=0, description='0.00%', max=3663), Label(value='0 / 3663'))), …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a7d4229b01b4482f83d403f5b3075746",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(children=(IntProgress(value=0, description='0.00%', max=3663), Label(value='0 / 3663'))), …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "e7932ebb09e24ae9b05b6a3cbdfaff9e",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(children=(IntProgress(value=0, description='0.00%', max=3663), Label(value='0 / 3663'))), …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2min 28s, sys: 19.7 s, total: 2min 48s\n",
      "Wall time: 16min 26s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_filt_2022=pd.read_csv('gs://nlp_final_project_kshitijm/00_Data/NLP_FP_Data5_2022_Topics_Sentiments.csv',lineterminator='\\n')\n",
    "df_filt_2022['entities_spacy'] = df_filt_2022['cleaned_text'].parallel_apply(lambda x: spacy_ner(x))\n",
    "\n",
    "df_filt_2022['ENT_ORG'] = df_filt_2022['entities_spacy'].parallel_apply(lambda x: [tup[0] for tup in x if tup[1] == \"ORG\"])\n",
    "df_filt_2022['ENT_PROD'] = df_filt_2022['entities_spacy'].parallel_apply(lambda x: [tup[0] for tup in x if tup[1] == \"PRODUCT\"])\n",
    "df_filt_2022['ENT_PER'] = df_filt_2022['entities_spacy'].parallel_apply(lambda x: [tup[0] for tup in x if tup[1] == \"PERSON\"])\n",
    "df_filt_2022['ENT_NORP'] = df_filt_2022['entities_spacy'].parallel_apply(lambda x: [tup[0] for tup in x if tup[1] == \"NORP\"])\n",
    "\n",
    "df_filt_2022.to_csv('gs://nlp_final_project_kshitijm/00_Data/NLP_FP_Data6_2022_Topics_Sentiments_NER.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90c55991-89c6-43fa-9a1c-39502a345272",
   "metadata": {},
   "source": [
    "-----\n",
    "**2023**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "47f5a569-0cfe-4852-a44a-2c2ae4c6878a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "0192fab87b434a0a884c067109e09715",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(children=(IntProgress(value=0, description='0.00%', max=3756), Label(value='0 / 3756'))), …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a12adda5c7d84c8487cee6096775fb7f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(children=(IntProgress(value=0, description='0.00%', max=3756), Label(value='0 / 3756'))), …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f637f4f2ebe44830af14afe862efae5b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(children=(IntProgress(value=0, description='0.00%', max=3756), Label(value='0 / 3756'))), …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "2aa6142675414ecab65625e614bc98b6",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(children=(IntProgress(value=0, description='0.00%', max=3756), Label(value='0 / 3756'))), …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "55ddaa31610c4377966a47aa1605bf5f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(children=(IntProgress(value=0, description='0.00%', max=3756), Label(value='0 / 3756'))), …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2min 38s, sys: 24.2 s, total: 3min 2s\n",
      "Wall time: 18min 20s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_filt_2023=pd.read_csv('gs://nlp_final_project_kshitijm/00_Data/NLP_FP_Data5_2023_Topics_Sentiments.csv',lineterminator='\\n')\n",
    "df_filt_2023['entities_spacy'] = df_filt_2023['cleaned_text'].parallel_apply(lambda x: spacy_ner(x))\n",
    "\n",
    "df_filt_2023['ENT_ORG'] = df_filt_2023['entities_spacy'].parallel_apply(lambda x: [tup[0] for tup in x if tup[1] == \"ORG\"])\n",
    "df_filt_2023['ENT_PROD'] = df_filt_2023['entities_spacy'].parallel_apply(lambda x: [tup[0] for tup in x if tup[1] == \"PRODUCT\"])\n",
    "df_filt_2023['ENT_PER'] = df_filt_2023['entities_spacy'].parallel_apply(lambda x: [tup[0] for tup in x if tup[1] == \"PERSON\"])\n",
    "df_filt_2023['ENT_NORP'] = df_filt_2023['entities_spacy'].parallel_apply(lambda x: [tup[0] for tup in x if tup[1] == \"NORP\"])\n",
    "\n",
    "df_filt_2023.to_csv('gs://nlp_final_project_kshitijm/00_Data/NLP_FP_Data6_2023_Topics_Sentiments_NER.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf503100-e8b4-4a3c-8c07-37c45565f584",
   "metadata": {},
   "source": [
    "-----\n",
    "**Analysing NERs across the years**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "cf4241a1-28e6-4a35-b99b-1427e7d1815a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 20.9 s, sys: 2.85 s, total: 23.7 s\n",
      "Wall time: 48.2 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_filt_2020=pd.read_csv('gs://nlp_final_project_kshitijm/00_Data/NLP_FP_Data5_2021_Topics_Sentiments_NER.csv',lineterminator='\\n')\n",
    "df_filt_2021=pd.read_csv('gs://nlp_final_project_kshitijm/00_Data/NLP_FP_Data6_2021v2_Topics_Sentiments_NER.csv',lineterminator='\\n')\n",
    "df_filt_2022=pd.read_csv('gs://nlp_final_project_kshitijm/00_Data/NLP_FP_Data6_2022_Topics_Sentiments_NER.csv', lineterminator='\\n')\n",
    "df_filt_2023=pd.read_csv('gs://nlp_final_project_kshitijm/00_Data/NLP_FP_Data6_2023_Topics_Sentiments_NER.csv', lineterminator='\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "fbed33fe-f485-460a-9bff-971cc871aca6",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_top_ents(df):\n",
    "\n",
    "    df['ENT_ORG']=df['ENT_ORG'].parallel_apply(ast.literal_eval)\n",
    "    org_list = list(chain.from_iterable(df['ENT_ORG']))\n",
    "    counter_org = Counter(org_list)\n",
    "    top_20_org = counter_org.most_common(20)\n",
    "    \n",
    "    df['ENT_PROD']=df['ENT_PROD'].parallel_apply(ast.literal_eval)\n",
    "    prod_list = list(chain.from_iterable(df['ENT_PROD']))\n",
    "    counter_prod = Counter(prod_list)\n",
    "    top_20_prod = counter_prod.most_common(20)\n",
    "    \n",
    "    df['ENT_PER']=df['ENT_PER'].parallel_apply(ast.literal_eval)\n",
    "    per_list = list(chain.from_iterable(df['ENT_PER']))\n",
    "    counter_per = Counter(per_list)\n",
    "    top_20_per = counter_per.most_common(20)\n",
    "    \n",
    "    df['ENT_NORP']=df['ENT_NORP'].parallel_apply(ast.literal_eval)\n",
    "    norm_list = list(chain.from_iterable(df['ENT_NORP']))\n",
    "    counter_norm = Counter(norm_list)\n",
    "    top_20_norm = counter_norm.most_common(20)\n",
    "    \n",
    "    return top_20_org, top_20_prod, top_20_per, top_20_norm\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5430da0d-9a52-4441-9e02-e815c169189b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "top_20_org_2020, top_20_prod_2020, top_20_per_2020, top_20_norm_2020 = get_top_ents(df_filt_2020)\n",
    "top_20_org_2021, top_20_prod_2021, top_20_per_2021, top_20_norm_2021 = get_top_ents(df_filt_2021)\n",
    "top_20_org_2022, top_20_prod_2022, top_20_per_2022, top_20_norm_2022 = get_top_ents(df_filt_2022)\n",
    "top_20_org_2023, top_20_prod_2023, top_20_per_2023, top_20_norm_2023 = get_top_ents(df_filt_2023)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0330774-d44e-4ff1-8df8-73a2594a3a88",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "## Part 2: Yearly Results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "c45d9f34-455f-4137-b042-16b652eaf7c2",
   "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>2020</th>\n",
       "      <th>2021</th>\n",
       "      <th>2022</th>\n",
       "      <th>2023</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>(AI, 37756)</td>\n",
       "      <td>(AI, 50705)</td>\n",
       "      <td>(AI, 71025)</td>\n",
       "      <td>(ChatGPT, 155292)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>(V19, 28787)</td>\n",
       "      <td>(V19, 21160)</td>\n",
       "      <td>(Gray Media Group, 54282)</td>\n",
       "      <td>(AI, 84010)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>(Google, 20518)</td>\n",
       "      <td>(Gray Media Group, 19507)</td>\n",
       "      <td>(Station20022022 Gray Television, 23401)</td>\n",
       "      <td>(Microsoft, 53121)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>(Microsoft, 14155)</td>\n",
       "      <td>(Google, 19109)</td>\n",
       "      <td>(Google, 19290)</td>\n",
       "      <td>(Google, 50085)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>(IBM, 13379)</td>\n",
       "      <td>(Microsoft, 14261)</td>\n",
       "      <td>(ML, 12760)</td>\n",
       "      <td>(OpenAI, 29117)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>(Artificial Intelligence, 12495)</td>\n",
       "      <td>(Artificial Intelligence, 13384)</td>\n",
       "      <td>(V19, 9213)</td>\n",
       "      <td>(Gray Media Group, 28657)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>(GR, 11260)</td>\n",
       "      <td>(IBM, 13200)</td>\n",
       "      <td>(Microsoft, 7737)</td>\n",
       "      <td>(Bard, 18375)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>(Facebook, 9600)</td>\n",
       "      <td>(Facebook, 10079)</td>\n",
       "      <td>(ChatGPT, 7456)</td>\n",
       "      <td>(Station20022023 Gray Television, 12870)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>(Artificial Intelligence AI, 7383)</td>\n",
       "      <td>(GR, 9837)</td>\n",
       "      <td>(Facebook, 7436)</td>\n",
       "      <td>(Microsofts, 10510)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>(Amazon, 6893)</td>\n",
       "      <td>(Station20022021 Gray Television, 7721)</td>\n",
       "      <td>(IBM, 7204)</td>\n",
       "      <td>(Googles, 10029)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>(Machine Learning, 6402)</td>\n",
       "      <td>(Machine Learning, 7536)</td>\n",
       "      <td>(CaptioningAudio DescriptionA Gray Media Group, 6811)</td>\n",
       "      <td>(Facebook, 9210)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>(Intel, 6191)</td>\n",
       "      <td>(ML, 7343)</td>\n",
       "      <td>(Artificial Intelligence, 6591)</td>\n",
       "      <td>(GPT, 9033)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>(Apple, 5274)</td>\n",
       "      <td>(Artificial Intelligence AI, 7210)</td>\n",
       "      <td>(Amazon, 6109)</td>\n",
       "      <td>(Apple, 8173)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>(ML, 4460)</td>\n",
       "      <td>(Amazon, 6835)</td>\n",
       "      <td>(Meta, 5833)</td>\n",
       "      <td>(Amazon, 7776)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>(AsiaPacific, 4086)</td>\n",
       "      <td>(Intel, 6033)</td>\n",
       "      <td>(DLE, 4885)</td>\n",
       "      <td>(AIgenerated, 7114)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>(Healthcare, 3892)</td>\n",
       "      <td>(Apple, 4753)</td>\n",
       "      <td>(GR, 4471)</td>\n",
       "      <td>(Instagram, 7030)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>(Samsung, 3010)</td>\n",
       "      <td>(EU, 4701)</td>\n",
       "      <td>(Twitter, 4422)</td>\n",
       "      <td>(Twitter, 6972)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>(Twitter, 2701)</td>\n",
       "      <td>(BrandVoicePaid Program, 3981)</td>\n",
       "      <td>(Instagram, 4418)</td>\n",
       "      <td>(Meta, 6379)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>(Oracle, 2699)</td>\n",
       "      <td>(Tesla, 3610)</td>\n",
       "      <td>(Tesla, 4168)</td>\n",
       "      <td>(Baidu, 5915)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>(IBM Corporation, 2665)</td>\n",
       "      <td>(Healthcare, 3586)</td>\n",
       "      <td>(Machine Learning, 4153)</td>\n",
       "      <td>(AP, 5846)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  2020  \\\n",
       "0                          (AI, 37756)   \n",
       "1                         (V19, 28787)   \n",
       "2                      (Google, 20518)   \n",
       "3                   (Microsoft, 14155)   \n",
       "4                         (IBM, 13379)   \n",
       "5     (Artificial Intelligence, 12495)   \n",
       "6                          (GR, 11260)   \n",
       "7                     (Facebook, 9600)   \n",
       "8   (Artificial Intelligence AI, 7383)   \n",
       "9                       (Amazon, 6893)   \n",
       "10            (Machine Learning, 6402)   \n",
       "11                       (Intel, 6191)   \n",
       "12                       (Apple, 5274)   \n",
       "13                          (ML, 4460)   \n",
       "14                 (AsiaPacific, 4086)   \n",
       "15                  (Healthcare, 3892)   \n",
       "16                     (Samsung, 3010)   \n",
       "17                     (Twitter, 2701)   \n",
       "18                      (Oracle, 2699)   \n",
       "19             (IBM Corporation, 2665)   \n",
       "\n",
       "                                       2021  \\\n",
       "0                               (AI, 50705)   \n",
       "1                              (V19, 21160)   \n",
       "2                 (Gray Media Group, 19507)   \n",
       "3                           (Google, 19109)   \n",
       "4                        (Microsoft, 14261)   \n",
       "5          (Artificial Intelligence, 13384)   \n",
       "6                              (IBM, 13200)   \n",
       "7                         (Facebook, 10079)   \n",
       "8                                (GR, 9837)   \n",
       "9   (Station20022021 Gray Television, 7721)   \n",
       "10                 (Machine Learning, 7536)   \n",
       "11                               (ML, 7343)   \n",
       "12       (Artificial Intelligence AI, 7210)   \n",
       "13                           (Amazon, 6835)   \n",
       "14                            (Intel, 6033)   \n",
       "15                            (Apple, 4753)   \n",
       "16                               (EU, 4701)   \n",
       "17           (BrandVoicePaid Program, 3981)   \n",
       "18                            (Tesla, 3610)   \n",
       "19                       (Healthcare, 3586)   \n",
       "\n",
       "                                                     2022  \\\n",
       "0                                             (AI, 71025)   \n",
       "1                               (Gray Media Group, 54282)   \n",
       "2                (Station20022022 Gray Television, 23401)   \n",
       "3                                         (Google, 19290)   \n",
       "4                                             (ML, 12760)   \n",
       "5                                             (V19, 9213)   \n",
       "6                                       (Microsoft, 7737)   \n",
       "7                                         (ChatGPT, 7456)   \n",
       "8                                        (Facebook, 7436)   \n",
       "9                                             (IBM, 7204)   \n",
       "10  (CaptioningAudio DescriptionA Gray Media Group, 6811)   \n",
       "11                        (Artificial Intelligence, 6591)   \n",
       "12                                         (Amazon, 6109)   \n",
       "13                                           (Meta, 5833)   \n",
       "14                                            (DLE, 4885)   \n",
       "15                                             (GR, 4471)   \n",
       "16                                        (Twitter, 4422)   \n",
       "17                                      (Instagram, 4418)   \n",
       "18                                          (Tesla, 4168)   \n",
       "19                               (Machine Learning, 4153)   \n",
       "\n",
       "                                        2023  \n",
       "0                          (ChatGPT, 155292)  \n",
       "1                                (AI, 84010)  \n",
       "2                         (Microsoft, 53121)  \n",
       "3                            (Google, 50085)  \n",
       "4                            (OpenAI, 29117)  \n",
       "5                  (Gray Media Group, 28657)  \n",
       "6                              (Bard, 18375)  \n",
       "7   (Station20022023 Gray Television, 12870)  \n",
       "8                        (Microsofts, 10510)  \n",
       "9                           (Googles, 10029)  \n",
       "10                          (Facebook, 9210)  \n",
       "11                               (GPT, 9033)  \n",
       "12                             (Apple, 8173)  \n",
       "13                            (Amazon, 7776)  \n",
       "14                       (AIgenerated, 7114)  \n",
       "15                         (Instagram, 7030)  \n",
       "16                           (Twitter, 6972)  \n",
       "17                              (Meta, 6379)  \n",
       "18                             (Baidu, 5915)  \n",
       "19                                (AP, 5846)  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ORG_trend=pd.DataFrame({'2020':top_20_org_2020,'2021':top_20_org_2021,'2022':top_20_org_2022, '2023':top_20_org_2023})\n",
    "ORG_trend"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "60f4c16a-28a9-4c17-9d3f-805a611e68a4",
   "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>2020</th>\n",
       "      <th>2021</th>\n",
       "      <th>2022</th>\n",
       "      <th>2023</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>(AI, 70971)</td>\n",
       "      <td>(AI, 109573)</td>\n",
       "      <td>(AI, 167203)</td>\n",
       "      <td>(AI, 202427)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>(V19, 2601)</td>\n",
       "      <td>(V19, 2151)</td>\n",
       "      <td>(AIdriven, 3785)</td>\n",
       "      <td>(Bing, 8028)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>(AIdriven, 1194)</td>\n",
       "      <td>(AIdriven, 1929)</td>\n",
       "      <td>(UsMeet, 2337)</td>\n",
       "      <td>(Android, 3517)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>(Twitter YouTube, 1113)</td>\n",
       "      <td>(Twitter YouTube, 1467)</td>\n",
       "      <td>(HPC, 1486)</td>\n",
       "      <td>(AIdriven, 3465)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>(JavaScript, 984)</td>\n",
       "      <td>(JavaScript, 1170)</td>\n",
       "      <td>(JavaScript, 1223)</td>\n",
       "      <td>(Windows, 1832)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>(SE, 718)</td>\n",
       "      <td>(SE, 1124)</td>\n",
       "      <td>(SE, 1152)</td>\n",
       "      <td>(YouTube, 1813)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>(Galaxy, 669)</td>\n",
       "      <td>(HPC, 983)</td>\n",
       "      <td>(Cresta, 1054)</td>\n",
       "      <td>(UsMeet, 1646)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>(YouTube, 572)</td>\n",
       "      <td>(Desktop Alert Notifications .Learn, 795)</td>\n",
       "      <td>(YouTube, 939)</td>\n",
       "      <td>(Windows 11, 1499)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>(TensorFlow, 532)</td>\n",
       "      <td>(CRM, 794)</td>\n",
       "      <td>(Android, 850)</td>\n",
       "      <td>(Excel, 1331)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>(Global AI, 459)</td>\n",
       "      <td>(YouTube, 604)</td>\n",
       "      <td>(ADAS, 822)</td>\n",
       "      <td>(Open AI, 1179)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>(HPC, 443)</td>\n",
       "      <td>(Facebook, 602)</td>\n",
       "      <td>(CRM, 772)</td>\n",
       "      <td>(JavaScript, 1117)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>(Discovery, 433)</td>\n",
       "      <td>(Discovery, 598)</td>\n",
       "      <td>(Google Cloud, 766)</td>\n",
       "      <td>(CRM, 927)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>(Javascript Course, 422)</td>\n",
       "      <td>(UsMeet, 518)</td>\n",
       "      <td>(NewscastsPress, 720)</td>\n",
       "      <td>(ChatGPTstyle, 805)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>(MasteryData ScienceData, 422)</td>\n",
       "      <td>(UX, 505)</td>\n",
       "      <td>(Akismet, 692)</td>\n",
       "      <td>(Akismet, 762)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>(Facebook, 390)</td>\n",
       "      <td>(Verbit, 445)</td>\n",
       "      <td>(Cloud, 688)</td>\n",
       "      <td>(Galaxy, 744)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>(CRM, 369)</td>\n",
       "      <td>(TensorFlow, 444)</td>\n",
       "      <td>(Aetina, 674)</td>\n",
       "      <td>(Facebook, 707)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>(A100, 366)</td>\n",
       "      <td>(Xilinx, 442)</td>\n",
       "      <td>(Twitter YouTube, 670)</td>\n",
       "      <td>(Chart PatternRounding BottomRising WedgeFalling, 705)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>(FoodProduct, 352)</td>\n",
       "      <td>(Galaxy, 438)</td>\n",
       "      <td>(TensorFlow, 655)</td>\n",
       "      <td>(FormationHead, 705)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>(Xilinx, 332)</td>\n",
       "      <td>(Android, 425)</td>\n",
       "      <td>(Google Cloud Certification:, 587)</td>\n",
       "      <td>(ToolsPivot Point  , 705)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>(Windows 10, 326)</td>\n",
       "      <td>(Excel, 419)</td>\n",
       "      <td>(Google Cloud Certification: Cloud Data EngineerLaunch, 587)</td>\n",
       "      <td>(SoundHound AI, 693)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              2020                                       2021  \\\n",
       "0                      (AI, 70971)                               (AI, 109573)   \n",
       "1                      (V19, 2601)                                (V19, 2151)   \n",
       "2                 (AIdriven, 1194)                           (AIdriven, 1929)   \n",
       "3          (Twitter YouTube, 1113)                    (Twitter YouTube, 1467)   \n",
       "4                (JavaScript, 984)                         (JavaScript, 1170)   \n",
       "5                        (SE, 718)                                 (SE, 1124)   \n",
       "6                    (Galaxy, 669)                                 (HPC, 983)   \n",
       "7                   (YouTube, 572)  (Desktop Alert Notifications .Learn, 795)   \n",
       "8                (TensorFlow, 532)                                 (CRM, 794)   \n",
       "9                 (Global AI, 459)                             (YouTube, 604)   \n",
       "10                      (HPC, 443)                            (Facebook, 602)   \n",
       "11                (Discovery, 433)                           (Discovery, 598)   \n",
       "12        (Javascript Course, 422)                              (UsMeet, 518)   \n",
       "13  (MasteryData ScienceData, 422)                                  (UX, 505)   \n",
       "14                 (Facebook, 390)                              (Verbit, 445)   \n",
       "15                      (CRM, 369)                          (TensorFlow, 444)   \n",
       "16                     (A100, 366)                              (Xilinx, 442)   \n",
       "17              (FoodProduct, 352)                              (Galaxy, 438)   \n",
       "18                   (Xilinx, 332)                             (Android, 425)   \n",
       "19               (Windows 10, 326)                               (Excel, 419)   \n",
       "\n",
       "                                                            2022  \\\n",
       "0                                                   (AI, 167203)   \n",
       "1                                               (AIdriven, 3785)   \n",
       "2                                                 (UsMeet, 2337)   \n",
       "3                                                    (HPC, 1486)   \n",
       "4                                             (JavaScript, 1223)   \n",
       "5                                                     (SE, 1152)   \n",
       "6                                                 (Cresta, 1054)   \n",
       "7                                                 (YouTube, 939)   \n",
       "8                                                 (Android, 850)   \n",
       "9                                                    (ADAS, 822)   \n",
       "10                                                    (CRM, 772)   \n",
       "11                                           (Google Cloud, 766)   \n",
       "12                                         (NewscastsPress, 720)   \n",
       "13                                                (Akismet, 692)   \n",
       "14                                                  (Cloud, 688)   \n",
       "15                                                 (Aetina, 674)   \n",
       "16                                        (Twitter YouTube, 670)   \n",
       "17                                             (TensorFlow, 655)   \n",
       "18                            (Google Cloud Certification:, 587)   \n",
       "19  (Google Cloud Certification: Cloud Data EngineerLaunch, 587)   \n",
       "\n",
       "                                                      2023  \n",
       "0                                             (AI, 202427)  \n",
       "1                                             (Bing, 8028)  \n",
       "2                                          (Android, 3517)  \n",
       "3                                         (AIdriven, 3465)  \n",
       "4                                          (Windows, 1832)  \n",
       "5                                          (YouTube, 1813)  \n",
       "6                                           (UsMeet, 1646)  \n",
       "7                                       (Windows 11, 1499)  \n",
       "8                                            (Excel, 1331)  \n",
       "9                                          (Open AI, 1179)  \n",
       "10                                      (JavaScript, 1117)  \n",
       "11                                              (CRM, 927)  \n",
       "12                                     (ChatGPTstyle, 805)  \n",
       "13                                          (Akismet, 762)  \n",
       "14                                           (Galaxy, 744)  \n",
       "15                                         (Facebook, 707)  \n",
       "16  (Chart PatternRounding BottomRising WedgeFalling, 705)  \n",
       "17                                    (FormationHead, 705)  \n",
       "18                               (ToolsPivot Point  , 705)  \n",
       "19                                    (SoundHound AI, 693)  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PROD_trend=pd.DataFrame({'2020':top_20_prod_2020,'2021':top_20_prod_2021,'2022':top_20_prod_2022, '2023':top_20_prod_2023})\n",
    "PROD_trend"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "2f4acfda-da79-4af5-b77d-4885f0df5ce0",
   "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>2020</th>\n",
       "      <th>2021</th>\n",
       "      <th>2022</th>\n",
       "      <th>2023</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>(Size, 3979)</td>\n",
       "      <td>(Size, 2994)</td>\n",
       "      <td>(Elon Musk, 2219)</td>\n",
       "      <td>(Bing, 10618)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>(Trump, 3283)</td>\n",
       "      <td>(Biden, 1993)</td>\n",
       "      <td>(Musk, 2192)</td>\n",
       "      <td>(Musk, 8478)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>(Instagram, 1360)</td>\n",
       "      <td>(Closefor, 1908)</td>\n",
       "      <td>(Lemoine, 2044)</td>\n",
       "      <td>(Elon Musk, 7255)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>(Middle EastAfrica, 1277)</td>\n",
       "      <td>(Greta Van, 1880)</td>\n",
       "      <td>(Ai Weiwei, 1766)</td>\n",
       "      <td>(Biden, 4333)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>(Biden, 1046)</td>\n",
       "      <td>(Instagram, 1469)</td>\n",
       "      <td>(Vectorspace AI, 1651)</td>\n",
       "      <td>(Trump, 4095)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>(Richests Richests, 1016)</td>\n",
       "      <td>(Elon Musk, 1220)</td>\n",
       "      <td>(Biden, 1464)</td>\n",
       "      <td>(Sam Altman, 3417)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>(Gebru, 886)</td>\n",
       "      <td>(Jim Thorpe, 1088)</td>\n",
       "      <td>(EnterpriseFor, 1184)</td>\n",
       "      <td>(Bard, 2638)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>(Ai Weiwei, 857)</td>\n",
       "      <td>(Middle EastAfrica, 1087)</td>\n",
       "      <td>(Mark Zuckerberg, 1066)</td>\n",
       "      <td>(Sundar Pichai, 2584)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>(Musk, 808)</td>\n",
       "      <td>(Su Tierra Tiempo, 1085)</td>\n",
       "      <td>(StartedIs Bitcoin, 1006)</td>\n",
       "      <td>(Pichai, 2359)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>(Elon Musk, 779)</td>\n",
       "      <td>(Musk, 1080)</td>\n",
       "      <td>(CoFounder, 961)</td>\n",
       "      <td>(Ernie Bot, 2302)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>(Trumps, 776)</td>\n",
       "      <td>(DIIR F, 1060)</td>\n",
       "      <td>(Greta Van, 951)</td>\n",
       "      <td>(Lessby, 2286)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>(Busco, 774)</td>\n",
       "      <td>(Greta Van     , 1014)</td>\n",
       "      <td>(SitesU.S. TVRadio, 881)</td>\n",
       "      <td>(Donald Trump, 2282)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>(asistencia tcnica para mi solucin de cuidados de salud Busco, 774)</td>\n",
       "      <td>(Trump, 987)</td>\n",
       "      <td>(Standigm, 859)</td>\n",
       "      <td>(Altman, 2020)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>(Donald Trump, 704)</td>\n",
       "      <td>(Richests Richests, 984)</td>\n",
       "      <td>(Laivly, 854)</td>\n",
       "      <td>(Satya Nadella, 1929)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>(Pichai, 647)</td>\n",
       "      <td>(Gebru, 851)</td>\n",
       "      <td>(Watson, 837)</td>\n",
       "      <td>(Tu, 1808)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>(BFSI, 613)</td>\n",
       "      <td>(Mitchell, 817)</td>\n",
       "      <td>(Blake Lemoine, 816)</td>\n",
       "      <td>(Putin, 1790)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>(Kyle Wiggers, 587)</td>\n",
       "      <td>(Greta Van SusterenGray, 788)</td>\n",
       "      <td>(Instagram, 781)</td>\n",
       "      <td>(Joe Biden, 1590)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>(Sundar Pichai, 575)</td>\n",
       "      <td>(Joe Biden, 754)</td>\n",
       "      <td>(Sophy, 768)</td>\n",
       "      <td>(Higgins, 1557)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>(Jim Thorpe, 564)</td>\n",
       "      <td>(Ai Weiwei, 744)</td>\n",
       "      <td>(GShare, 684)</td>\n",
       "      <td>(Bill Gates, 1456)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>(Mark My ServicesAll, 525)</td>\n",
       "      <td>(Greta Van      , 743)</td>\n",
       "      <td>(TVLatest NewscastsPress, 658)</td>\n",
       "      <td>(Bidens, 1243)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                   2020  \\\n",
       "0                                                          (Size, 3979)   \n",
       "1                                                         (Trump, 3283)   \n",
       "2                                                     (Instagram, 1360)   \n",
       "3                                             (Middle EastAfrica, 1277)   \n",
       "4                                                         (Biden, 1046)   \n",
       "5                                             (Richests Richests, 1016)   \n",
       "6                                                          (Gebru, 886)   \n",
       "7                                                      (Ai Weiwei, 857)   \n",
       "8                                                           (Musk, 808)   \n",
       "9                                                      (Elon Musk, 779)   \n",
       "10                                                        (Trumps, 776)   \n",
       "11                                                         (Busco, 774)   \n",
       "12  (asistencia tcnica para mi solucin de cuidados de salud Busco, 774)   \n",
       "13                                                  (Donald Trump, 704)   \n",
       "14                                                        (Pichai, 647)   \n",
       "15                                                          (BFSI, 613)   \n",
       "16                                                  (Kyle Wiggers, 587)   \n",
       "17                                                 (Sundar Pichai, 575)   \n",
       "18                                                    (Jim Thorpe, 564)   \n",
       "19                                           (Mark My ServicesAll, 525)   \n",
       "\n",
       "                             2021                            2022  \\\n",
       "0                    (Size, 2994)               (Elon Musk, 2219)   \n",
       "1                   (Biden, 1993)                    (Musk, 2192)   \n",
       "2                (Closefor, 1908)                 (Lemoine, 2044)   \n",
       "3               (Greta Van, 1880)               (Ai Weiwei, 1766)   \n",
       "4               (Instagram, 1469)          (Vectorspace AI, 1651)   \n",
       "5               (Elon Musk, 1220)                   (Biden, 1464)   \n",
       "6              (Jim Thorpe, 1088)           (EnterpriseFor, 1184)   \n",
       "7       (Middle EastAfrica, 1087)         (Mark Zuckerberg, 1066)   \n",
       "8        (Su Tierra Tiempo, 1085)       (StartedIs Bitcoin, 1006)   \n",
       "9                    (Musk, 1080)                (CoFounder, 961)   \n",
       "10                 (DIIR F, 1060)                (Greta Van, 951)   \n",
       "11         (Greta Van     , 1014)        (SitesU.S. TVRadio, 881)   \n",
       "12                   (Trump, 987)                 (Standigm, 859)   \n",
       "13       (Richests Richests, 984)                   (Laivly, 854)   \n",
       "14                   (Gebru, 851)                   (Watson, 837)   \n",
       "15                (Mitchell, 817)            (Blake Lemoine, 816)   \n",
       "16  (Greta Van SusterenGray, 788)                (Instagram, 781)   \n",
       "17               (Joe Biden, 754)                    (Sophy, 768)   \n",
       "18               (Ai Weiwei, 744)                   (GShare, 684)   \n",
       "19         (Greta Van      , 743)  (TVLatest NewscastsPress, 658)   \n",
       "\n",
       "                     2023  \n",
       "0           (Bing, 10618)  \n",
       "1            (Musk, 8478)  \n",
       "2       (Elon Musk, 7255)  \n",
       "3           (Biden, 4333)  \n",
       "4           (Trump, 4095)  \n",
       "5      (Sam Altman, 3417)  \n",
       "6            (Bard, 2638)  \n",
       "7   (Sundar Pichai, 2584)  \n",
       "8          (Pichai, 2359)  \n",
       "9       (Ernie Bot, 2302)  \n",
       "10         (Lessby, 2286)  \n",
       "11   (Donald Trump, 2282)  \n",
       "12         (Altman, 2020)  \n",
       "13  (Satya Nadella, 1929)  \n",
       "14             (Tu, 1808)  \n",
       "15          (Putin, 1790)  \n",
       "16      (Joe Biden, 1590)  \n",
       "17        (Higgins, 1557)  \n",
       "18     (Bill Gates, 1456)  \n",
       "19         (Bidens, 1243)  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PER_trend=pd.DataFrame({'2020':top_20_per_2020,'2021':top_20_per_2021,'2022':top_20_per_2022, '2023':top_20_per_2023})\n",
    "PER_trend"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "a6b68fcc-20df-4620-a759-39e4899f2461",
   "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>2020</th>\n",
       "      <th>2021</th>\n",
       "      <th>2022</th>\n",
       "      <th>2023</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>(Chinese, 4344)</td>\n",
       "      <td>(Chinese, 5339)</td>\n",
       "      <td>(Chinese, 6519)</td>\n",
       "      <td>(Chinese, 9313)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>(American, 1869)</td>\n",
       "      <td>(European, 2883)</td>\n",
       "      <td>(European, 3094)</td>\n",
       "      <td>(Americans, 5781)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>(European, 1783)</td>\n",
       "      <td>(American, 2178)</td>\n",
       "      <td>(Canadian, 2696)</td>\n",
       "      <td>(American, 5154)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>(British, 1654)</td>\n",
       "      <td>(Canadian, 2097)</td>\n",
       "      <td>(American, 2344)</td>\n",
       "      <td>(Italian, 4704)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>(Canadian, 1486)</td>\n",
       "      <td>(British, 1674)</td>\n",
       "      <td>(British, 1713)</td>\n",
       "      <td>(British, 3858)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>(French, 1077)</td>\n",
       "      <td>(American African American, 1526)</td>\n",
       "      <td>(Americans, 1551)</td>\n",
       "      <td>(Canadian, 3022)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>(Americans, 860)</td>\n",
       "      <td>(French, 1170)</td>\n",
       "      <td>(American African American, 1543)</td>\n",
       "      <td>(European, 2791)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>(German, 847)</td>\n",
       "      <td>(Americans, 1150)</td>\n",
       "      <td>(French, 1500)</td>\n",
       "      <td>(French, 2773)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>(Hindu, 742)</td>\n",
       "      <td>(German, 1082)</td>\n",
       "      <td>(Lunit, 1343)</td>\n",
       "      <td>(German, 2011)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>(Tamil, 541)</td>\n",
       "      <td>(Korean, 1033)</td>\n",
       "      <td>(Ukrainian, 1106)</td>\n",
       "      <td>(Canadians, 1405)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>(Asian, 513)</td>\n",
       "      <td>(Asian, 781)</td>\n",
       "      <td>(German, 1087)</td>\n",
       "      <td>(Swiss, 1310)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>(ian, 446)</td>\n",
       "      <td>(CHANLSAdd, 668)</td>\n",
       "      <td>(Swiss, 903)</td>\n",
       "      <td>(Republican, 1200)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>(RemoteMoneyAll, 444)</td>\n",
       "      <td>(Tamil, 645)</td>\n",
       "      <td>(African, 885)</td>\n",
       "      <td>(Ukrainian, 1038)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>(African, 437)</td>\n",
       "      <td>(EUs, 641)</td>\n",
       "      <td>(Korean, 722)</td>\n",
       "      <td>(Christian, 1010)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>(Canadians, 434)</td>\n",
       "      <td>(English, 636)</td>\n",
       "      <td>(Saudi, 720)</td>\n",
       "      <td>(English, 953)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>(EUs, 433)</td>\n",
       "      <td>(African, 613)</td>\n",
       "      <td>(North American, 717)</td>\n",
       "      <td>(ian, 950)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>(Irish, 423)</td>\n",
       "      <td>(Lunit, 612)</td>\n",
       "      <td>(Italian, 688)</td>\n",
       "      <td>(Democrats, 905)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>(Democratic, 398)</td>\n",
       "      <td>(North American, 570)</td>\n",
       "      <td>(Spanish, 632)</td>\n",
       "      <td>(Republicans, 880)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>(Popular, 393)</td>\n",
       "      <td>(Spanish, 531)</td>\n",
       "      <td>(Tamil, 631)</td>\n",
       "      <td>(Greek, 796)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>(North American, 363)</td>\n",
       "      <td>(Dutch, 526)</td>\n",
       "      <td>(DLE2, 542)</td>\n",
       "      <td>(EUs, 778)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     2020                               2021  \\\n",
       "0         (Chinese, 4344)                    (Chinese, 5339)   \n",
       "1        (American, 1869)                   (European, 2883)   \n",
       "2        (European, 1783)                   (American, 2178)   \n",
       "3         (British, 1654)                   (Canadian, 2097)   \n",
       "4        (Canadian, 1486)                    (British, 1674)   \n",
       "5          (French, 1077)  (American African American, 1526)   \n",
       "6        (Americans, 860)                     (French, 1170)   \n",
       "7           (German, 847)                  (Americans, 1150)   \n",
       "8            (Hindu, 742)                     (German, 1082)   \n",
       "9            (Tamil, 541)                     (Korean, 1033)   \n",
       "10           (Asian, 513)                       (Asian, 781)   \n",
       "11             (ian, 446)                   (CHANLSAdd, 668)   \n",
       "12  (RemoteMoneyAll, 444)                       (Tamil, 645)   \n",
       "13         (African, 437)                         (EUs, 641)   \n",
       "14       (Canadians, 434)                     (English, 636)   \n",
       "15             (EUs, 433)                     (African, 613)   \n",
       "16           (Irish, 423)                       (Lunit, 612)   \n",
       "17      (Democratic, 398)              (North American, 570)   \n",
       "18         (Popular, 393)                     (Spanish, 531)   \n",
       "19  (North American, 363)                       (Dutch, 526)   \n",
       "\n",
       "                                 2022                2023  \n",
       "0                     (Chinese, 6519)     (Chinese, 9313)  \n",
       "1                    (European, 3094)   (Americans, 5781)  \n",
       "2                    (Canadian, 2696)    (American, 5154)  \n",
       "3                    (American, 2344)     (Italian, 4704)  \n",
       "4                     (British, 1713)     (British, 3858)  \n",
       "5                   (Americans, 1551)    (Canadian, 3022)  \n",
       "6   (American African American, 1543)    (European, 2791)  \n",
       "7                      (French, 1500)      (French, 2773)  \n",
       "8                       (Lunit, 1343)      (German, 2011)  \n",
       "9                   (Ukrainian, 1106)   (Canadians, 1405)  \n",
       "10                     (German, 1087)       (Swiss, 1310)  \n",
       "11                       (Swiss, 903)  (Republican, 1200)  \n",
       "12                     (African, 885)   (Ukrainian, 1038)  \n",
       "13                      (Korean, 722)   (Christian, 1010)  \n",
       "14                       (Saudi, 720)      (English, 953)  \n",
       "15              (North American, 717)          (ian, 950)  \n",
       "16                     (Italian, 688)    (Democrats, 905)  \n",
       "17                     (Spanish, 632)  (Republicans, 880)  \n",
       "18                       (Tamil, 631)        (Greek, 796)  \n",
       "19                        (DLE2, 542)          (EUs, 778)  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "NORM_trend=pd.DataFrame({'2020':top_20_norm_2020,'2021':top_20_norm_2021,'2022':top_20_norm_2022, '2023':top_20_norm_2023})\n",
    "NORM_trend"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "fcf88737-673e-40b8-b890-9d6e5185ec62",
   "metadata": {},
   "outputs": [],
   "source": [
    "ORG_trend.to_csv(\"NER_Trend_ORG.csv\",index=False)\n",
    "PROD_trend.to_csv(\"PROD_Trend_ORG.csv\",index=False)\n",
    "PER_trend.to_csv(\"PER_Trend_ORG.csv\",index=False)\n",
    "NORM_trend.to_csv(\"NORM_Trend_ORG.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d7ad3cd-5a8c-4f35-a346-8eb3c0994eb2",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_top_ents_2(df):\n",
    "\n",
    "    # df['ENT_ORG']=df['ENT_ORG'].parallel_apply(ast.literal_eval)\n",
    "    org_list = list(chain.from_iterable(df['ENT_ORG']))\n",
    "    counter_org = Counter(org_list)\n",
    "    top_20_org = counter_org.most_common(20)\n",
    "    \n",
    "    # df['ENT_PROD']=df['ENT_PROD'].parallel_apply(ast.literal_eval)\n",
    "    prod_list = list(chain.from_iterable(df['ENT_PROD']))\n",
    "    counter_prod = Counter(prod_list)\n",
    "    top_20_prod = counter_prod.most_common(20)\n",
    "    \n",
    "    # df['ENT_PER']=df['ENT_PER'].parallel_apply(ast.literal_eval)\n",
    "    per_list = list(chain.from_iterable(df['ENT_PER']))\n",
    "    counter_per = Counter(per_list)\n",
    "    top_20_per = counter_per.most_common(20)\n",
    "    \n",
    "    # df['ENT_NORP']=df['ENT_NORP'].parallel_apply(ast.literal_eval)\n",
    "    norm_list = list(chain.from_iterable(df['ENT_NORP']))\n",
    "    counter_norm = Counter(norm_list)\n",
    "    top_20_norm = counter_norm.most_common(20)\n",
    "    \n",
    "    ent_trend_df=pd.DataFrame({'ORGANIZATION':top_20_org,'PRODUCT':top_20_prod,'PERSON':top_20_per, 'NORM':top_20_norm})\n",
    "    return ent_trend_df\n"
   ]
  }
 ],
 "metadata": {
  "environment": {
   "kernel": "python3",
   "name": "tf2-gpu.2-11.m108",
   "type": "gcloud",
   "uri": "gcr.io/deeplearning-platform-release/tf2-gpu.2-11:m108"
  },
  "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.10.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}