code/automation/Count.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "872a60ca-8e79-4533-95ff-790403a4ad5c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import spacy\n",
    "import warnings\n",
    "import itertools\n",
    "import collections\n",
    "import pandas as pd\n",
    "\n",
    "# pd.set_option('max_colwidth', None)\n",
    "warnings.simplefilter(action='ignore', category=FutureWarning)\n",
    "\n",
    "nlp = spacy.load(\"en_core_web_sm\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97a3d9ba-f326-4f06-ad75-fb13d378cf58",
   "metadata": {},
   "source": [
    "## Years"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "331d0df9-41d2-4f98-a200-b5dc061c9651",
   "metadata": {},
   "outputs": [],
   "source": [
    "years = {}\n",
    "for index, row in df.iterrows():\n",
    "    sentences = row['fullText'].split(\"\\n\")\n",
    "    for sentence in sentences:\n",
    "        if \"publication year\" in sentence.lower():\n",
    "            year = sentence.split(\":\")[1].replace(\" \", \"\")\n",
    "            if not year in years:\n",
    "                years[year] = 1\n",
    "            else:\n",
    "                years[year] += 1\n",
    "years = collections.OrderedDict(sorted(years.items()))\n",
    "pd.DataFrame(list(years.items()), columns=['year','count']).to_csv(\"years.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "737a11fe-a3c0-4fc3-b41e-94d483ce42dd",
   "metadata": {},
   "source": [
    "## Dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "caffdbc9-8d8a-472e-9aad-50f10b9280bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "dates = {}\n",
    "for index, row in df.iterrows():\n",
    "    sentences = row['fullText'].split(\"\\n\")\n",
    "    for sentence in sentences:\n",
    "        if \"publication date\" in sentence.lower():\n",
    "            date = sentence.split(\":\")[1].replace(\" \", \"\")\n",
    "            year = date.split(\",\")[1]\n",
    "            if not date in dates:\n",
    "                dates[date] = 1\n",
    "            else:\n",
    "                dates[date] += 1\n",
    "dates = collections.OrderedDict(sorted(dates.items()))\n",
    "\n",
    "dates_df = pd.DataFrame(list(dates.items()), columns=['full_date', 'count'])\n",
    "dates_df[['temp', 'year']] = dates_df['full_date'].str.split(',', expand=True)\n",
    "dates_df['full_date'] = dates_df['full_date'].str[:3] + \" \" + dates_df['full_date'].str[3:]\n",
    "dates_df['month'] = dates_df['temp'].str[:3]\n",
    "dates_df['day'] = dates_df['temp'].str[3:]\n",
    "\n",
    "del dates_df['temp']\n",
    "months = [\"Jan\", \"Feb\", \"Mar\", \"Apr\", \"May\", \"Jun\", \"Jul\", \"Aug\", \"Sep\", \"Oct\", \"Nov\", \"Dec\"]\n",
    "dates_df['month'] = pd.Categorical(dates_df['month'], categories=months, ordered=True)\n",
    "dates_df.sort_values(['year', 'month', 'day'], ascending=[True, True, True]).to_csv(\"dates.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20597f20-85e3-4cf1-92d3-703719b90111",
   "metadata": {},
   "source": [
    "## Count based on ID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "38aec6d9-e9bb-4da1-8c03-84010daba9b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "!rm -r count_id\n",
    "!mkdir count_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "cc44692e-221b-41db-9ffc-6231384c650f",
   "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>id</th>\n",
       "      <th>outputFormat</th>\n",
       "      <th>wordCount</th>\n",
       "      <th>fullText</th>\n",
       "      <th>unigramCount</th>\n",
       "      <th>bigramCount</th>\n",
       "      <th>trigramCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>111665565</td>\n",
       "      <td>[unigram, bigram, trigram, fullText]</td>\n",
       "      <td>285</td>\n",
       "      <td>metals industry speedsconversion average incre...</td>\n",
       "      <td>{'metals': 3, 'industry': 1, 'speedsconversion...</td>\n",
       "      <td>{'metals industry': 1, 'industry speedsconvers...</td>\n",
       "      <td>{'metals industry speedsconversion': 1, 'indus...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                          outputFormat  wordCount  \\\n",
       "0  111665565  [unigram, bigram, trigram, fullText]        285   \n",
       "\n",
       "                                            fullText  \\\n",
       "0  metals industry speedsconversion average incre...   \n",
       "\n",
       "                                        unigramCount  \\\n",
       "0  {'metals': 3, 'industry': 1, 'speedsconversion...   \n",
       "\n",
       "                                         bigramCount  \\\n",
       "0  {'metals industry': 1, 'industry speedsconvers...   \n",
       "\n",
       "                                        trigramCount  \n",
       "0  {'metals industry speedsconversion': 1, 'indus...  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unigram CSV for count_id/1950-1959 has been created\n",
      "Bigram CSV for count_id/1950-1959 has been created\n",
      "Trigram CSV for count_id/1950-1959 has been created.\n",
      "\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>id</th>\n",
       "      <th>outputFormat</th>\n",
       "      <th>wordCount</th>\n",
       "      <th>fullText</th>\n",
       "      <th>unigramCount</th>\n",
       "      <th>bigramCount</th>\n",
       "      <th>trigramCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>114925995</td>\n",
       "      <td>[unigram, bigram, trigram, fullText]</td>\n",
       "      <td>92</td>\n",
       "      <td>unions demanding programs washington upia grou...</td>\n",
       "      <td>{'unions': 3, 'demanding': 1, 'programs': 2, '...</td>\n",
       "      <td>{'unions demanding': 1, 'demanding programs': ...</td>\n",
       "      <td>{'unions demanding programs': 1, 'demanding pr...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                          outputFormat  wordCount  \\\n",
       "0  114925995  [unigram, bigram, trigram, fullText]         92   \n",
       "\n",
       "                                            fullText  \\\n",
       "0  unions demanding programs washington upia grou...   \n",
       "\n",
       "                                        unigramCount  \\\n",
       "0  {'unions': 3, 'demanding': 1, 'programs': 2, '...   \n",
       "\n",
       "                                         bigramCount  \\\n",
       "0  {'unions demanding': 1, 'demanding programs': ...   \n",
       "\n",
       "                                        trigramCount  \n",
       "0  {'unions demanding programs': 1, 'demanding pr...  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unigram CSV for count_id/1960-1969 has been created\n",
      "Bigram CSV for count_id/1960-1969 has been created\n",
      "Trigram CSV for count_id/1960-1969 has been created.\n",
      "\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>id</th>\n",
       "      <th>outputFormat</th>\n",
       "      <th>wordCount</th>\n",
       "      <th>fullText</th>\n",
       "      <th>unigramCount</th>\n",
       "      <th>bigramCount</th>\n",
       "      <th>trigramCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>117783758</td>\n",
       "      <td>[unigram, bigram, trigram, fullText]</td>\n",
       "      <td>450</td>\n",
       "      <td>costs alexander hammer specialize automate ord...</td>\n",
       "      <td>{'costs': 9, 'alexander': 1, 'hammer': 1, 'spe...</td>\n",
       "      <td>{'costs alexander': 1, 'alexander hammer': 1, ...</td>\n",
       "      <td>{'costs alexander hammer': 1, 'alexander hamme...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                          outputFormat  wordCount  \\\n",
       "0  117783758  [unigram, bigram, trigram, fullText]        450   \n",
       "\n",
       "                                            fullText  \\\n",
       "0  costs alexander hammer specialize automate ord...   \n",
       "\n",
       "                                        unigramCount  \\\n",
       "0  {'costs': 9, 'alexander': 1, 'hammer': 1, 'spe...   \n",
       "\n",
       "                                         bigramCount  \\\n",
       "0  {'costs alexander': 1, 'alexander hammer': 1, ...   \n",
       "\n",
       "                                        trigramCount  \n",
       "0  {'costs alexander hammer': 1, 'alexander hamme...  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unigram CSV for count_id/1970-1979 has been created\n",
      "Bigram CSV for count_id/1970-1979 has been created\n",
      "Trigram CSV for count_id/1970-1979 has been created.\n",
      "\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>id</th>\n",
       "      <th>outputFormat</th>\n",
       "      <th>wordCount</th>\n",
       "      <th>fullText</th>\n",
       "      <th>unigramCount</th>\n",
       "      <th>bigramCount</th>\n",
       "      <th>trigramCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>427454541</td>\n",
       "      <td>[unigram, bigram, trigram, fullText]</td>\n",
       "      <td>352</td>\n",
       "      <td>number basic reasons think outcry system tradi...</td>\n",
       "      <td>{'number': 3, 'basic': 2, 'reasons': 2, 'think...</td>\n",
       "      <td>{'number basic': 2, 'basic reasons': 2, 'reaso...</td>\n",
       "      <td>{'number basic reasons': 2, 'basic reasons thi...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                          outputFormat  wordCount  \\\n",
       "0  427454541  [unigram, bigram, trigram, fullText]        352   \n",
       "\n",
       "                                            fullText  \\\n",
       "0  number basic reasons think outcry system tradi...   \n",
       "\n",
       "                                        unigramCount  \\\n",
       "0  {'number': 3, 'basic': 2, 'reasons': 2, 'think...   \n",
       "\n",
       "                                         bigramCount  \\\n",
       "0  {'number basic': 2, 'basic reasons': 2, 'reaso...   \n",
       "\n",
       "                                        trigramCount  \n",
       "0  {'number basic reasons': 2, 'basic reasons thi...  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unigram CSV for count_id/1980-1989 has been created\n",
      "Bigram CSV for count_id/1980-1989 has been created\n",
      "Trigram CSV for count_id/1980-1989 has been created.\n",
      "\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>id</th>\n",
       "      <th>outputFormat</th>\n",
       "      <th>wordCount</th>\n",
       "      <th>fullText</th>\n",
       "      <th>unigramCount</th>\n",
       "      <th>bigramCount</th>\n",
       "      <th>trigramCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2234013923</td>\n",
       "      <td>[unigram, bigram, trigram, fullText]</td>\n",
       "      <td>236</td>\n",
       "      <td>governments coordinator 2000 conversion today ...</td>\n",
       "      <td>{'governments': 1, 'coordinator': 1, '2000': 3...</td>\n",
       "      <td>{'governments coordinator': 1, 'coordinator 20...</td>\n",
       "      <td>{'governments coordinator 2000': 1, 'coordinat...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           id                          outputFormat  wordCount  \\\n",
       "0  2234013923  [unigram, bigram, trigram, fullText]        236   \n",
       "\n",
       "                                            fullText  \\\n",
       "0  governments coordinator 2000 conversion today ...   \n",
       "\n",
       "                                        unigramCount  \\\n",
       "0  {'governments': 1, 'coordinator': 1, '2000': 3...   \n",
       "\n",
       "                                         bigramCount  \\\n",
       "0  {'governments coordinator': 1, 'coordinator 20...   \n",
       "\n",
       "                                        trigramCount  \n",
       "0  {'governments coordinator 2000': 1, 'coordinat...  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unigram CSV for count_id/1990-1999 has been created\n",
      "Bigram CSV for count_id/1990-1999 has been created\n",
      "Trigram CSV for count_id/1990-1999 has been created.\n",
      "\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>id</th>\n",
       "      <th>outputFormat</th>\n",
       "      <th>wordCount</th>\n",
       "      <th>fullText</th>\n",
       "      <th>unigramCount</th>\n",
       "      <th>bigramCount</th>\n",
       "      <th>trigramCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>434257357</td>\n",
       "      <td>[unigram, bigram, trigram, fullText]</td>\n",
       "      <td>377</td>\n",
       "      <td>replication enabled digital coming tactile wor...</td>\n",
       "      <td>{'replication': 1, 'enabled': 2, 'digital': 5,...</td>\n",
       "      <td>{'replication enabled': 1, 'enabled digital': ...</td>\n",
       "      <td>{'replication enabled digital': 1, 'enabled di...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                          outputFormat  wordCount  \\\n",
       "0  434257357  [unigram, bigram, trigram, fullText]        377   \n",
       "\n",
       "                                            fullText  \\\n",
       "0  replication enabled digital coming tactile wor...   \n",
       "\n",
       "                                        unigramCount  \\\n",
       "0  {'replication': 1, 'enabled': 2, 'digital': 5,...   \n",
       "\n",
       "                                         bigramCount  \\\n",
       "0  {'replication enabled': 1, 'enabled digital': ...   \n",
       "\n",
       "                                        trigramCount  \n",
       "0  {'replication enabled digital': 1, 'enabled di...  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unigram CSV for count_id/2000-2009 has been created\n",
      "Bigram CSV for count_id/2000-2009 has been created\n",
      "Trigram CSV for count_id/2000-2009 has been created.\n",
      "\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>id</th>\n",
       "      <th>outputFormat</th>\n",
       "      <th>wordCount</th>\n",
       "      <th>fullText</th>\n",
       "      <th>unigramCount</th>\n",
       "      <th>bigramCount</th>\n",
       "      <th>trigramCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1857295624</td>\n",
       "      <td>[unigram, bigram, trigram, fullText]</td>\n",
       "      <td>1813</td>\n",
       "      <td>president obama delivered farewell address chi...</td>\n",
       "      <td>{'president': 7, 'obama': 1, 'delivered': 1, '...</td>\n",
       "      <td>{'president obama': 1, 'obama delivered': 1, '...</td>\n",
       "      <td>{'president obama delivered': 1, 'obama delive...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           id                          outputFormat  wordCount  \\\n",
       "0  1857295624  [unigram, bigram, trigram, fullText]       1813   \n",
       "\n",
       "                                            fullText  \\\n",
       "0  president obama delivered farewell address chi...   \n",
       "\n",
       "                                        unigramCount  \\\n",
       "0  {'president': 7, 'obama': 1, 'delivered': 1, '...   \n",
       "\n",
       "                                         bigramCount  \\\n",
       "0  {'president obama': 1, 'obama delivered': 1, '...   \n",
       "\n",
       "                                        trigramCount  \n",
       "0  {'president obama delivered': 1, 'obama delive...  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unigram CSV for count_id/2010-2019 has been created\n",
      "Bigram CSV for count_id/2010-2019 has been created\n",
      "Trigram CSV for count_id/2010-2019 has been created.\n",
      "\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>id</th>\n",
       "      <th>outputFormat</th>\n",
       "      <th>wordCount</th>\n",
       "      <th>fullText</th>\n",
       "      <th>unigramCount</th>\n",
       "      <th>bigramCount</th>\n",
       "      <th>trigramCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2472937624</td>\n",
       "      <td>[unigram, bigram, trigram, fullText]</td>\n",
       "      <td>425</td>\n",
       "      <td>sleep procrastinate better money 2021what terr...</td>\n",
       "      <td>{'sleep': 7, 'procrastinate': 4, 'better': 4, ...</td>\n",
       "      <td>{'sleep procrastinate': 1, 'procrastinate bett...</td>\n",
       "      <td>{'sleep procrastinate better': 1, 'procrastina...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           id                          outputFormat  wordCount  \\\n",
       "0  2472937624  [unigram, bigram, trigram, fullText]        425   \n",
       "\n",
       "                                            fullText  \\\n",
       "0  sleep procrastinate better money 2021what terr...   \n",
       "\n",
       "                                        unigramCount  \\\n",
       "0  {'sleep': 7, 'procrastinate': 4, 'better': 4, ...   \n",
       "\n",
       "                                         bigramCount  \\\n",
       "0  {'sleep procrastinate': 1, 'procrastinate bett...   \n",
       "\n",
       "                                        trigramCount  \n",
       "0  {'sleep procrastinate better': 1, 'procrastina...  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unigram CSV for count_id/2020-2029 has been created\n",
      "Bigram CSV for count_id/2020-2029 has been created\n",
      "Trigram CSV for count_id/2020-2029 has been created.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "folders = ['1950-1959', '1960-1969', '1970-1979', '1980-1989', '1990-1999', '2000-2009','2010-2019', '2020-2029']\n",
    "for prefix in folders:\n",
    "    df = pd.read_json(path_or_buf=\"jsonl/\"+prefix+'.jsonl', lines=True)\n",
    "    display(df.head(1))\n",
    "\n",
    "    # unigram\n",
    "    for index, row in df.iterrows():\n",
    "        count = 0\n",
    "        for key, value in row['unigramCount'].items():\n",
    "            if 'automation' in key.lower():\n",
    "                count+=row['unigramCount'][key]\n",
    "        df.loc[index, 'automation_unigram_count'] = count\n",
    "    df[['id', 'automation_unigram_count']].to_csv(\"count_id/\"+prefix+\"_unigram.csv\", index=False)\n",
    "    print(\"Unigram CSV for {} has been created\".format(prefix))\n",
    "    \n",
    "    # bigram\n",
    "    bigram_df = pd.DataFrame()\n",
    "    for index, row in df.iterrows():\n",
    "        for key, value in row['bigramCount'].items():\n",
    "            if 'automation' in key.lower():\n",
    "                new_row = {'id':row['id'], 'word_1':key.split()[0], 'word_2':key.split()[1], 'automation_bigram_count':row['bigramCount'][key]}\n",
    "                bigram_df = bigram_df.append(new_row, ignore_index=True)\n",
    "    bigram_df_grouped = bigram_df.groupby(['id'], as_index=False)['automation_bigram_count'].sum().rename(columns={'automation_bigram_count':'automation_bigram_count_grouped'})\n",
    "    pd.merge(bigram_df, bigram_df_grouped, on='id').to_csv(\"count_id/\"+prefix+\"_bigram.csv\", index=False)\n",
    "    print(\"Bigram CSV for {} has been created\".format(prefix))\n",
    "\n",
    "    # trigram\n",
    "    trigram_df = pd.DataFrame()\n",
    "    for index, row in df.iterrows():\n",
    "        for key, value in row['trigramCount'].items():\n",
    "            if 'automation' in key.lower():\n",
    "                new_row = {'id':row['id'], 'word_1':key.split()[0], 'word_2':key.split()[1], 'word_3':key.split()[2], \n",
    "                           'automation_trigram_count':row['trigramCount'][key]}\n",
    "                trigram_df = trigram_df.append(new_row, ignore_index=True)\n",
    "    trigram_df_grouped = trigram_df.groupby(['id'], as_index=False)['automation_trigram_count'].sum().rename(columns={'automation_trigram_count':'automation_trigram_count_grouped'})\n",
    "    pd.merge(trigram_df, trigram_df_grouped, on='id').to_csv(\"count_id/\"+prefix+\"_trigram.csv\", index=False)\n",
    "    print(\"Trigram CSV for {} has been created.\\n\".format(prefix))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10705c86-f0bf-4014-8488-4ff6752c8f35",
   "metadata": {},
   "source": [
    "# Count based on words"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "178606ef-e0de-49fc-a5ac-1d2552152522",
   "metadata": {},
   "outputs": [],
   "source": [
    "!rm -r count_word\n",
    "!mkdir count_word"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "31751fea-6d99-47e7-b777-035ad5ce959a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "********************FINISHED********************\n"
     ]
    }
   ],
   "source": [
    "files = ['1950-1959', '1960-1969', '1970-1979', '1980-1989', '1990-1999', '2000-2009','2010-2019', '2020-2029']\n",
    "for file in files:\n",
    "    df = pd.read_csv(\"count_id/\"+file+\"_bigram.csv\")\n",
    "    df[\"word\"] = df[\"word_1\"] + \" \" + df[\"word_2\"]\n",
    "    # sort for aggreagation\n",
    "    df['word'] = df['word'].apply(lambda x: \" \".join(sorted(x.split(\" \"))))\n",
    "    df = df.groupby('word')['automation_bigram_count'].agg('sum').reset_index()\n",
    "    df.to_csv(\"count_word/\"+file+\"_bigram.csv\")\n",
    "    \n",
    "    \n",
    "    df = pd.read_csv(\"count_id/\"+file+\"_trigram.csv\")\n",
    "    df[\"word\"] = df[\"word_1\"] + \" \" + df[\"word_2\"] + \" \" + df[\"word_3\"]\n",
    "    # sort for aggreagation\n",
    "    df['word'] = df['word'].apply(lambda x: \" \".join(sorted(x.split(\" \"))))\n",
    "    df = df.groupby('word')['automation_trigram_count'].agg('sum').reset_index()\n",
    "    df.to_csv(\"count_word/\"+file+\"_trigram.csv\")\n",
    "print(\"*\"*20+\"FINISHED\"+\"*\"*20)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec5d9eba-d4e1-4173-ba96-6efa60486a7f",
   "metadata": {},
   "source": [
    "# Lemmatization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "175cdbdd-bca5-44f5-b63d-847ffd77fde3",
   "metadata": {},
   "outputs": [],
   "source": [
    "!rm -r count_word_lemmatized\n",
    "!mkdir count_word_lemmatized"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2076a16f-356c-4288-8063-7bcb6b48fc94",
   "metadata": {},
   "outputs": [],
   "source": [
    "def lemmatize_text(text):\n",
    "    return [lemmatizer.lemmatize(w) for w in w_tokenizer.tokenize(text)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "de51228f-3994-464f-b563-5446e433dffa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "********************FINISHED********************\n"
     ]
    }
   ],
   "source": [
    "files = ['1950-1959', '1960-1969', '1970-1979', '1980-1989', '1990-1999', '2000-2009','2010-2019', '2020-2029']\n",
    "for file in files:\n",
    "    df = pd.read_csv(\"count_id/\"+file+\"_bigram.csv\")\n",
    "    # lemmatization\n",
    "    df[\"word\"] = df[\"word_1\"] + \" \" + df[\"word_2\"]\n",
    "    df[\"word\"] = df[\"word\"].apply(lambda row: \" \".join([w.lemma_ for w in nlp(row)]))\n",
    "    # sort for aggreagation\n",
    "    df['word'] = df['word'].apply(lambda x: \" \".join(sorted(x.split(\" \"))))\n",
    "    df = df.groupby('word')['automation_bigram_count'].agg('sum').reset_index()\n",
    "    df.to_csv(\"count_word_lemmatized/\"+file+\"_bigram.csv\")\n",
    "    \n",
    "    \n",
    "    df = pd.read_csv(\"count_id/\"+file+\"_trigram.csv\")\n",
    "    # lemmatization\n",
    "    df[\"word\"] = df[\"word_1\"] + \" \" + df[\"word_2\"] + \" \" + df[\"word_3\"]\n",
    "    df[\"word\"] = df[\"word\"].apply(lambda row: \" \".join([w.lemma_ for w in nlp(row)]))\n",
    "    # sort for aggreagation\n",
    "    df['word'] = df['word'].apply(lambda x: \" \".join(sorted(x.split(\" \"))))\n",
    "    df = df.groupby('word')['automation_trigram_count'].agg('sum').reset_index()\n",
    "    df.to_csv(\"count_word_lemmatized/\"+file+\"_trigram.csv\")\n",
    "print(\"*\"*20+\"FINISHED\"+\"*\"*20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "bce70434-f73c-4697-b12f-5742a928d2fd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dangerous\n"
     ]
    }
   ],
   "source": [
    "import spacy\n",
    "nlp = spacy.load(\"en_core_web_sm\")\n",
    "doc = nlp(\"dangerous\")\n",
    "for token in doc:\n",
    "    print(token.lemma_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da6fcca1-4c33-4058-a079-40b1e0e5cb50",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8",
   "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}