preprocess.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4bed447",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import re\n",
    "\n",
    "#read in category tables data \n",
    "dftemp_A = pd.read_csv(\"C:/datasources/CategoryArticle.csv\", sep='|')\n",
    "dftemp_B = pd.read_csv(\"C:/datasources/Category.csv\", sep='|', usecols=['cid','url'])\n",
    "\n",
    "#cast to str for merge\n",
    "dftemp_A['cid'] = dftemp_A['cid'].astype(str)\n",
    "dftemp_B['cid'] = dftemp_B['cid'].astype(str)\n",
    "\n",
    "# Merge the two dataframes on the 'cid' column\n",
    "merged_df = pd.merge(dftemp_A, dftemp_B, on='cid')\n",
    "\n",
    "# Group the merged dataframe by 'ArticleId' and aggregate the 'url' values into a list\n",
    "dftemp_C = merged_df.groupby('ArticleId')['url'].apply(list).reset_index()\n",
    "dftemp_C.rename(columns={\"url\": \"tags\"}, inplace=True)\n",
    "\n",
    "# Save the grouped dataframe to the alltags.csv file\n",
    "dftemp_C.to_csv('C:/datasources/alltags.csv', index=False, encoding='utf-8')\n",
    "\n",
    "#transform dataframe to dictionary\n",
    "v = dftemp_C.groupby('ArticleId')['tags'].apply(lambda t: list(t)).to_dict()\n",
    "\n",
    "#read in raw articles table data\n",
    "dftemp_DB = pd.read_csv(\"C:\\\\datasources\\\\articlesScrubbed.csv\", sep='|', usecols=['ArticleId','Title','PublicationDate','Publication','Links','Description','Priority','url','url2'])\n",
    "\n",
    "#make some unique wtk urls \n",
    "makeurl = dftemp_DB['url'].astype(str)\n",
    "dftemp_DB['wtkURL'] = \"https://www.wanttoknow.info/a-\" + makeurl\n",
    "  \n",
    "#make tags column and populate with dictionary v\n",
    "dftemp_DB['tags'] = dftemp_DB['ArticleId'].map(v)  \n",
    "\n",
    "#check it with something like dftemp_DB.head(12)\n",
    "\n",
    "#remove rows without valid Title or tags values\n",
    "dftemp_DB = dftemp_DB[dftemp_DB['Title'].notna()]\n",
    "dftemp_DB = dftemp_DB[dftemp_DB['tags'].notna()]\n",
    "\n",
    "#check it with something like dftemp_DB.tail(12)\n",
    "\n",
    "#send to file\n",
    "dftemp_DB.to_csv(\"C:\\\\datasources\\\\WTKpreprocessed.csv\", sep='|', index=False, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16e837a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "dftemp_DB.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "751fbf5c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#'mediacondense' list of dictionaries is built for when there are more than 2 variations of a media source\n",
    "\n",
    "mediacondense = []\n",
    "\n",
    "abc = ['ABC News Australia', 'ABC News blog', 'ABC6', 'abcnews.com', 'WCPO - Cincinnatis ABC Affiliate', 'ABC News', 'ABC News blogs', 'ABC News Blog', 'ABC News Good Morning America', 'ABC New', 'ABC News Nightline', 'ABC15', 'ABC News 20', 'abc4.com', 'ABC Action News', 'ABCs Arizona Affiliate','WXYZ - Detroits ABC News Affiliate']\n",
    "mcat1 = \"ABC\"\n",
    "m1 = dict.fromkeys(abc, mcat1)\n",
    "mediacondense.append(m1)\n",
    "\n",
    "ode = ['Ode Magazine, June 2005 Issue', 'Ode Magazine, July 2005 Issue', 'Ode magazine']\n",
    "mcat2 = \"Ode Magazine\"\n",
    "m2 = dict.fromkeys(ode, mcat2)\n",
    "mediacondense.append(m2)\n",
    "\n",
    "nbc = ['NBC Milwaukee Affiliate', 'NBC Chicago', 'NBC Miami', 'NBC Washington', 'NBC Los Angeles', 'NBC Oklahoma City', 'NBC News']\n",
    "mcat3 = \"NBC\"\n",
    "m3 = dict.fromkeys(nbc, mcat3)\n",
    "mediacondense.append(m3)\n",
    "\n",
    "vfr = ['Vanity Fair August 2006 Issue', 'Vanity Fair September 2005 Issue', 'Vanity Fair magazine']\n",
    "mcat4 = \"Vanity Fair\"\n",
    "m4 = dict.fromkeys(vfr, mcat4)\n",
    "mediacondense.append(m4)\n",
    "\n",
    "nyt = ['The New York Times', 'New York Times Blog', 'New York Times blog']\n",
    "mcat5 = \"New York Times\"\n",
    "m5 = dict.fromkeys(nyt, mcat5)\n",
    "mediacondense.append(m5)\n",
    "\n",
    "unw = ['U.S. News and World Report', 'U.S. News & World Report', 'U.S. News & World Report blog', 'US News & World Report magazine', 'US News & World Report']\n",
    "mcat6 = \"US News and World Report\"\n",
    "m6 = dict.fromkeys(unw, mcat6)\n",
    "mediacondense.append(m6)\n",
    "\n",
    "nwk = ['Newsweek magazine', 'Newsweek blog', 'Newsweek Magazine', 'Newsweek magazine blog']\n",
    "mcat7 = \"Newsweek\"\n",
    "m7 = dict.fromkeys(nwk, mcat7)\n",
    "mediacondense.append(m7)\n",
    "\n",
    "bbc = ['BBC Radio', 'BBC News blog', 'BBC Blogs', 'BBC News']\n",
    "mcat8 = \"BBC\"\n",
    "m8 = dict.fromkeys(bbc, mcat8)\n",
    "mediacondense.append(m8)\n",
    "\n",
    "fop = ['Foreign Policy Magazine May', 'Foreign Policy Journal']\n",
    "mcat9 = \"Foreign Policy\"\n",
    "m9 = dict.fromkeys(fop, mcat9)\n",
    "mediacondense.append(m9)\n",
    "\n",
    "wap = ['Washington Post blog', 'washingtonpost.com', 'Washingon Post', 'Washginton Post', 'The Washington Post']\n",
    "mcat10 = \"Washington Post\"\n",
    "m10 = dict.fromkeys(wap, mcat10)\n",
    "mediacondense.append(m10)\n",
    "\n",
    "tlg = ['The Telegraph blogs', 'Daily Telegraph', 'Telegraph']\n",
    "mcat11 = \"The Telegraph\"\n",
    "m11 = dict.fromkeys(tlg, mcat11)\n",
    "mediacondense.append(m11)\n",
    "\n",
    "nsa = ['U.S. National Security Agency Website', 'National Security Agency  Website', 'NSA Technical Journal, Vol. XI', 'National Security Agency  Website, NSA Technical Journal, Vol. XI']\n",
    "mcat12 = \"NSA Website\"\n",
    "m12 = dict.fromkeys(nsa, mcat12)\n",
    "mediacondense.append(m12)\n",
    "\n",
    "msn = ['MSN Money', 'MSN of Australia', 'MSN Canada', 'MSN']\n",
    "mcat13 = \"MSN News\"\n",
    "m13 = dict.fromkeys(msn, mcat13)\n",
    "mediacondense.append(m13)\n",
    "\n",
    "tim = ['Time magazine', 'Time Magazine', 'Time Magazine blog']\n",
    "mcat14 = \"Time\"\n",
    "m14 = dict.fromkeys(tim, mcat14)\n",
    "mediacondense.append(m14)\n",
    "\n",
    "psc = ['Popular Science - March 2007 Issue', 'Popular Science Magazine', 'Popular Science magazine']\n",
    "mcat15 = \"Popular Science\"\n",
    "m15 = dict.fromkeys(psc, mcat15)\n",
    "mediacondense.append(m15)\n",
    "\n",
    "cnn = ['CNN blog', 'CNN Money', 'CNN International', 'CNN The Situation Room', 'CNN Lou Dobbs Tonight', 'CNN Video Clip', 'CNN Larry King Live', 'CNN News']\n",
    "mcat16 = \"CNN\"\n",
    "m16 = dict.fromkeys(cnn, mcat16)\n",
    "mediacondense.append(m16)\n",
    "\n",
    "cbs = ['CBS Las Vegas Affiliate', 'CBS Philly', 'CBS News', 'KCBS', 'CBS Atlanta', 'CBS Affiliate KUTV', 'CBS News Chicago, Associated Press', 'CBS News, Sacramento Affiliate', 'WCBS News - New York CBS Affiliate', 'CBS News 60 Minutes', 'CBS News 60 Minutes Overtime', 'CBS Los Angeles', 'CBS 60 Minutes', 'CBS News blog', 'CBS News, Stockton Affiliate']\n",
    "mcat17 = \"CBS\"\n",
    "m17 = dict.fromkeys(cbs, mcat17)\n",
    "mediacondense.append(m17)\n",
    "\n",
    "yho = ['Yahoo! News', 'Yahoo!', 'Yahoo! Finance', 'Yahoo! News Australia', 'Yahoo News', 'Yahoo Finance']\n",
    "mcat18 = \"Yahoo\"\n",
    "m18 = dict.fromkeys(yho, mcat18)\n",
    "mediacondense.append(m18)\n",
    "\n",
    "wsj = ['The Wall Street Journal', 'Wall Street Journal blog', 'Full Page Ad in Wall Street Journal', 'Wall Street Journal Article by Former FBI Director Louis Freeh', 'Wall Street Journal Blog']\n",
    "mcat19 = \"Wall Street Journal\"\n",
    "m19 = dict.fromkeys(wsj, mcat19)\n",
    "mediacondense.append(m19)\n",
    "\n",
    "fox = ['Fox News Chicago', 'Fox News video clip', 'FOX News', 'Fox 19', 'Fox News Affiliate']\n",
    "mcat20 = \"FOX\"\n",
    "m20 = dict.fromkeys(fox, mcat20)\n",
    "mediacondense.append(m20)\n",
    "\n",
    "icp = ['The Intercept With Glenn Greenwald', 'The Intercept with Glenn Greenwald']\n",
    "mcat21 = \"The Intercept\"\n",
    "m21 = dict.fromkeys(icp, mcat21)\n",
    "mediacondense.append(m21)\n",
    "\n",
    "lat = ['Los Angeles Times blog', 'The Los Angeles Times', 'LA Times']\n",
    "mcat22 = \"Los Angeles Times\"\n",
    "m22 = dict.fromkeys(lat, mcat22)\n",
    "mediacondense.append(m22)\n",
    "\n",
    "pbs = ['PBS Nova Program', 'PBS Frontline', 'PBS, CBS, Fox compilation', 'PBS News', 'PBS Bill Moyers Journal', 'PBS Newshour', 'PBS Blog']\n",
    "mcat23 = \"PBS\"\n",
    "m23 = dict.fromkeys(pbs, mcat23)\n",
    "mediacondense.append(m23)\n",
    "\n",
    "ecn = ['The Economist blog', 'The Economist Magazine', 'The Economist magazine']\n",
    "mcat24 = \"The Economist\"\n",
    "m24 = dict.fromkeys(ecn, mcat24)\n",
    "mediacondense.append(m24)\n",
    "\n",
    "npr = ['NPR All Things Considered', 'National Public Radio', 'NPR News', 'NPR blog', 'NPR Blog']\n",
    "mcat25 = \"NPR\"\n",
    "m25 = dict.fromkeys(npr, mcat25)\n",
    "mediacondense.append(m25)\n",
    "\n",
    "sfc = ['The San Francisco Chronicle', 'San Francisco Chronicle SFs leading newspaper)']\n",
    "mcat26 = \"San Francisco Chronicle\"\n",
    "m26 = dict.fromkeys(sfc, mcat26)\n",
    "mediacondense.append(m26)\n",
    "\n",
    "cbc = ['Canadian Broadcasting Corporation', 'CBC News', 'CBC News [Canadas Public Broadcasting System]']\n",
    "mcat27 = \"CBC\"\n",
    "m27 = dict.fromkeys(cbc, mcat27)\n",
    "mediacondense.append(m27)\n",
    "    \n",
    "frb = ['Forbes Magazine', 'Forbes blog', 'Forbes magazine', 'Forbes.com', 'Forbes.com blog', 'Forbes India Magazine']\n",
    "mcat28 = \"Forbes\"\n",
    "m28 = dict.fromkeys(frb, mcat28)\n",
    "mediacondense.append(m28)\n",
    "    \n",
    "rst = ['Rolling Stone blog', 'Rolling Stone magazine']\n",
    "mcat29 = \"Rolling Stone\"\n",
    "m29 = dict.fromkeys(rst, mcat29)\n",
    "mediacondense.append(m29)\n",
    "    \n",
    "grd = ['A Guardian blog', 'The Guardian blog', 'Guardian']\n",
    "mcat30 = \"The Guardian\"\n",
    "m30 = dict.fromkeys(grd, mcat30)\n",
    "mediacondense.append(m30)\n",
    "    \n",
    "ngc = ['NationalGeographic.com', 'National Geographic October 2004 Issue', 'National Geographic News', 'NationalGeographic.com blog']\n",
    "mcat31 = \"National Geographic\"\n",
    "m31 = dict.fromkeys(ngc, mcat31)\n",
    "mediacondense.append(m31)\n",
    "    \n",
    "mbc = ['MSNBC News', 'MSNBC Today', 'MSNBC: Keith Olbermann blog', 'MSNBC The Rachel Maddow Show']\n",
    "mcat32 = \"MSNBC\"\n",
    "m32 = dict.fromkeys(mbc, mcat32)\n",
    "mediacondense.append(m32)\n",
    "    \n",
    "rut = ['Reuters News Agency', 'Reuters News', 'Reuters Health', 'Reuters blog']\n",
    "mcat33 = \"Reuters\"\n",
    "m33 = dict.fromkeys(rut, mcat33)\n",
    "mediacondense.append(m33)\n",
    "\n",
    "blb = ['Bloomberg News Service', 'Businessweek', 'BusinessWeek', 'Bloomberg Businessweek', 'Bloomberg News']\n",
    "mcat34 = \"Bloomberg\"\n",
    "m34 = dict.fromkeys(blb, mcat34)\n",
    "mediacondense.append(m34)\n",
    "\n",
    "#the list of dictionaries is then turned into a new little dataframe\n",
    "\n",
    "key = []\n",
    "val = []\n",
    "for i in mediacondense:\n",
    "    for k,v in i.items():\n",
    "        key.append(k)\n",
    "        val.append(v)\n",
    "\n",
    "mediakeys = pd.DataFrame({'asis': key,\n",
    "                  'clean': val})\n",
    "\n",
    "#replacement values are mapped from new dataframe to complete main dataframe \n",
    "mediakeys.set_index('asis', inplace=True)\n",
    "\n",
    "mediakeys.to_csv(\"C:\\\\datasources\\\\mediakeys.csv\", sep='|', index=True, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "32de54fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "from bs4 import BeautifulSoup as bs\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import re\n",
    "\n",
    "#read in preprocessed file\n",
    "dfP = pd.read_csv(\"C:\\\\datasources\\\\WTKpreprocessed.csv\", sep='|', encoding='utf-8')\n",
    "\n",
    "#split raw story into summary and note. \n",
    "dfP['Summary'], dfP['Note'] = dfP['Description'].str.split('Note:', 1).str \n",
    "\n",
    "#create unique tag columns to make values quickly mappable for graph generation\n",
    "dfP['tags'] = dfP['tags'].astype(str).apply(lambda v: v.replace('\\'', ''))\n",
    "dfP['tags'] = dfP['tags'].astype(str).apply(lambda v: v.replace('[[', '['))\n",
    "dfP['tags'] = dfP['tags'].astype(str).apply(lambda v: v.replace(']]', ']'))\n",
    "dfP.tags = dfP.tags.str[1:-1].str.split(',').tolist()\n",
    "dfT = pd.DataFrame(dfP.tags.values.tolist(), dfP.index).add_prefix('tag_')\n",
    "\n",
    "form = lambda f: 'tag_{}'.format(f + 1)\n",
    "pd.DataFrame(\n",
    "    dfP.tags.values.tolist(),\n",
    "    dfP.index, dtype=object\n",
    ").fillna('').rename(columns=form)\n",
    "\n",
    "df = pd.concat([dfP, dfT], axis=1)\n",
    "\n",
    "#add tagcount column for convenient advanced indexing\n",
    "df['tagcount'] = df.tags.apply(lambda l: len(l))\n",
    "\n",
    "#initialize some lists for data extraction and cleaning\n",
    "Quotes = []\n",
    "Summaries = []\n",
    "Note_text = []\n",
    "Adtl_ref_links = []\n",
    "Note_ref_links = []\n",
    "Media_sources = []\n",
    "\n",
    "#set variables up for parsing markup\n",
    "stories = df['Summary'].astype(str).values.tolist()\n",
    "notes = df['Note'].astype(str).values.tolist()\n",
    "medias = df['Publication'].astype(str).values.tolist()\n",
    "\n",
    "#some loops to extract desired text and links\n",
    "for i in stories:\n",
    "    c = bs(i,'lxml')\n",
    "    try:\n",
    "        quote = c.strong.text\n",
    "    except (AttributeError):\n",
    "        quote = c.text.split('. ')[0]\n",
    "    summary = c.text\n",
    "    ref_links = []\n",
    "    for n in c.find_all('a'):\n",
    "        xlinks = n.get('href')\n",
    "        ref_links.append(xlinks)\n",
    "    Quotes.append(quote)\n",
    "    Summaries.append(summary)\n",
    "    Adtl_ref_links.append(ref_links)\n",
    "\n",
    "#switch parser from lxml for notes parsing because different parsers are good for different things    \n",
    "for i in notes:\n",
    "    c = bs(i,'html.parser')\n",
    "    note = c.text    \n",
    "    note_links = []\n",
    "    for n in c.find_all('a'):\n",
    "        nlinks = n.get('href')\n",
    "        note_links.append(nlinks)\n",
    "    Note_text.append(note)\n",
    "    Note_ref_links.append(note_links)\n",
    "\n",
    "#initial cleanup of pub. bs4 282 User Warning may show up because some media sources are urls. no biggie    \n",
    "for i in medias:\n",
    "    c = bs(i,'html.parser')\n",
    "    media = c.text\n",
    "    Media_sources.append(media)\n",
    "    \n",
    "#make new dataframe from extracted data    \n",
    "df3 = pd.DataFrame({'quote': Quotes,\n",
    "                  'description': Summaries,\n",
    "                  'note': Note_text,\n",
    "                  'pub': Media_sources,   \n",
    "                  'Summary_ref_links': Adtl_ref_links,\n",
    "                  'Note_links': Note_ref_links})    \n",
    "\n",
    "#join new dataframe to big raw dataframe\n",
    "dfR = pd.concat([df, df3], axis=1)\n",
    "\n",
    "dfR.drop(['Publication', 'Summary', 'url', 'url2'], axis=1, inplace=True)\n",
    "\n",
    "#begin cleanup of media sources\n",
    "dfR['pub'], dfR['pub2'] = dfR['pub'].str.split('/', 1).str\n",
    "dfR['pub'], dfR['pubdetail'] = dfR['pub'].str.split('(', 1).str\n",
    "dfR['pubdetail'] = dfR['pubdetail'].astype(str).apply(lambda u: u.strip(')'))\n",
    "dfR['pub'] = dfR['pub'].str.strip()\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda u: u.strip('\"'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('\\'',''))\n",
    "\n",
    "#use dfR.pub.unique() with pd.set_option('display.max_colwidth', 1000) to view raw media sources list\n",
    "#dfR['pub'].value_counts()\n",
    "\n",
    "#combine variations of media source into single item\n",
    "\n",
    "#one-off translations are easy, but should eventually be moved to mediacondense list\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('Seattle times', 'Seattle Times'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('Scientific American Blog', 'Scientific American'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('The Sacramento Bee', 'Sacramento Bee'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('Mother Jones Magazine', 'Mother Jones'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('Sydney Mountain Herald', 'Sydney Morning Herald'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('The Nation magazine', 'The Nation'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('Chicago Sun-Times News Group', 'Chicago Sun-Times'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('Wired magazine', 'Wired'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('The New Yorker magazine', 'The New Yorker'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('Fortune magazine', 'Fortune'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('The Atlantic Monthly', 'The Atlantic'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('Tikkun Magazine - March', 'Tikkun Magazine'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('The Daily Mail', 'Daily Mail'))\n",
    "\n",
    "#dfR.pub.head(3)\n",
    "\n",
    "#read in preprocessed mediacondense file\n",
    "mkeys = pd.read_csv(\"C:\\\\datasources\\\\mediakeys.csv\", sep='|', usecols=['asis','clean'], encoding='utf-8')\n",
    "\n",
    "mkeys.set_index('asis', inplace=True)\n",
    "\n",
    "dx = pd.Series(mkeys.clean.values,index=mkeys.index).to_dict()\n",
    "\n",
    "dfR['cpub'] = dfR['pub'].map(dx).fillna(dfR['pub'])\n",
    "\n",
    "dfR.head(3)\n",
    "\n",
    "#save full raw dataframe\n",
    "dfR.to_csv(\"C:\\\\datasources\\\\WTKfullraw.csv\", sep='|', index=False, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a22e489c",
   "metadata": {
    "scrolled": true
   },
   "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>ArticleId</th>\n",
       "      <th>Title</th>\n",
       "      <th>PublicationDate</th>\n",
       "      <th>Links</th>\n",
       "      <th>Description</th>\n",
       "      <th>Priority</th>\n",
       "      <th>wtkURL</th>\n",
       "      <th>tags</th>\n",
       "      <th>Note</th>\n",
       "      <th>cpub</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12317</th>\n",
       "      <td>12521</td>\n",
       "      <td>Health care providers were 'bribed' into sugge...</td>\n",
       "      <td>2023-04-14</td>\n",
       "      <td>https://abcnews4.com/news/nation-world/health-...</td>\n",
       "      <td>&lt;p&gt;Rep. Thomas Massie, R-Ky., shared a docume...</td>\n",
       "      <td>925.0</td>\n",
       "      <td>https://www.wanttoknow.info/a-health-care-prov...</td>\n",
       "      <td>['coronavirus']</td>\n",
       "      <td>&lt;/strong&gt; Read more on the concerns regarding ...</td>\n",
       "      <td>ABC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12321</th>\n",
       "      <td>12525</td>\n",
       "      <td>Rendlesham Forest UFO sighting 'new evidence' ...</td>\n",
       "      <td>2015-07-13</td>\n",
       "      <td>https://www.bbc.com/news/uk-england-suffolk-33...</td>\n",
       "      <td>&lt;p&gt;New evidence has been gathered to back up ...</td>\n",
       "      <td>920.0</td>\n",
       "      <td>https://www.wanttoknow.info/a-rendlesham-fores...</td>\n",
       "      <td>['ufos']</td>\n",
       "      <td>&lt;/strong&gt; Read the official report of this min...</td>\n",
       "      <td>BBC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12322</th>\n",
       "      <td>12526</td>\n",
       "      <td>Confessions of a CIA Terrorist</td>\n",
       "      <td>2023-05-20</td>\n",
       "      <td>https://www.laprogressive.com/foreign-policy/c...</td>\n",
       "      <td>&lt;p&gt;In 1979 the Sandinista revolution overthrew...</td>\n",
       "      <td>915.0</td>\n",
       "      <td>https://www.wanttoknow.info/a-confessions-a-ci...</td>\n",
       "      <td>['massmedia', ' governmentcorruption', ' intel...</td>\n",
       "      <td>&lt;/strong&gt; For more along these lines, see conc...</td>\n",
       "      <td>LA Progressive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12326</th>\n",
       "      <td>12530</td>\n",
       "      <td>When a prison sentence becomes a death sentence</td>\n",
       "      <td>2023-04-27</td>\n",
       "      <td>https://www.npr.org/sections/health-shots/2023...</td>\n",
       "      <td>&lt;p&gt;&lt;strong&gt;At least 6,182 people died in stat...</td>\n",
       "      <td>910.0</td>\n",
       "      <td>https://www.wanttoknow.info/a-when-prison-sent...</td>\n",
       "      <td>['governmentcorruption', ' prisonscorruption']</td>\n",
       "      <td>&lt;/strong&gt; For more along these lines, see conc...</td>\n",
       "      <td>NPR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12327</th>\n",
       "      <td>12531</td>\n",
       "      <td>˜The forever prisoner': Abu Zubaydah's drawing...</td>\n",
       "      <td>2023-05-11</td>\n",
       "      <td>https://www.theguardian.com/law/2023/may/11/ab...</td>\n",
       "      <td>&lt;p&gt;A detainee held in the US prison camp at G...</td>\n",
       "      <td>918.0</td>\n",
       "      <td>https://www.wanttoknow.info/a--forever-prisone...</td>\n",
       "      <td>['governmentcorruption', ' terrorism', ' priso...</td>\n",
       "      <td>&lt;/strong&gt; Read the \"&lt;a href=\"https://www.wantt...</td>\n",
       "      <td>The Guardian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12329</th>\n",
       "      <td>12533</td>\n",
       "      <td>Six whistleblowers who claim they worked on mi...</td>\n",
       "      <td>2023-04-26</td>\n",
       "      <td>https://www.dailymail.co.uk/news/article-11996...</td>\n",
       "      <td>&lt;p&gt;Senior members of Congress have spoken to ...</td>\n",
       "      <td>915.0</td>\n",
       "      <td>https://www.wanttoknow.info/a-six-whistleblowe...</td>\n",
       "      <td>['ufos']</td>\n",
       "      <td>&lt;/strong&gt; Watch the &lt;a href=\"https://www.youtu...</td>\n",
       "      <td>Daily Mail</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ArticleId                                              Title  \\\n",
       "12317      12521  Health care providers were 'bribed' into sugge...   \n",
       "12321      12525  Rendlesham Forest UFO sighting 'new evidence' ...   \n",
       "12322      12526                     Confessions of a CIA Terrorist   \n",
       "12326      12530    When a prison sentence becomes a death sentence   \n",
       "12327      12531  ˜The forever prisoner': Abu Zubaydah's drawing...   \n",
       "12329      12533  Six whistleblowers who claim they worked on mi...   \n",
       "\n",
       "      PublicationDate                                              Links  \\\n",
       "12317      2023-04-14  https://abcnews4.com/news/nation-world/health-...   \n",
       "12321      2015-07-13  https://www.bbc.com/news/uk-england-suffolk-33...   \n",
       "12322      2023-05-20  https://www.laprogressive.com/foreign-policy/c...   \n",
       "12326      2023-04-27  https://www.npr.org/sections/health-shots/2023...   \n",
       "12327      2023-05-11  https://www.theguardian.com/law/2023/may/11/ab...   \n",
       "12329      2023-04-26  https://www.dailymail.co.uk/news/article-11996...   \n",
       "\n",
       "                                             Description  Priority  \\\n",
       "12317   <p>Rep. Thomas Massie, R-Ky., shared a docume...     925.0   \n",
       "12321   <p>New evidence has been gathered to back up ...     920.0   \n",
       "12322  <p>In 1979 the Sandinista revolution overthrew...     915.0   \n",
       "12326   <p><strong>At least 6,182 people died in stat...     910.0   \n",
       "12327   <p>A detainee held in the US prison camp at G...     918.0   \n",
       "12329   <p>Senior members of Congress have spoken to ...     915.0   \n",
       "\n",
       "                                                  wtkURL  \\\n",
       "12317  https://www.wanttoknow.info/a-health-care-prov...   \n",
       "12321  https://www.wanttoknow.info/a-rendlesham-fores...   \n",
       "12322  https://www.wanttoknow.info/a-confessions-a-ci...   \n",
       "12326  https://www.wanttoknow.info/a-when-prison-sent...   \n",
       "12327  https://www.wanttoknow.info/a--forever-prisone...   \n",
       "12329  https://www.wanttoknow.info/a-six-whistleblowe...   \n",
       "\n",
       "                                                    tags  \\\n",
       "12317                                    ['coronavirus']   \n",
       "12321                                           ['ufos']   \n",
       "12322  ['massmedia', ' governmentcorruption', ' intel...   \n",
       "12326     ['governmentcorruption', ' prisonscorruption']   \n",
       "12327  ['governmentcorruption', ' terrorism', ' priso...   \n",
       "12329                                           ['ufos']   \n",
       "\n",
       "                                                    Note            cpub  \n",
       "12317  </strong> Read more on the concerns regarding ...             ABC  \n",
       "12321  </strong> Read the official report of this min...             BBC  \n",
       "12322  </strong> For more along these lines, see conc...  LA Progressive  \n",
       "12326  </strong> For more along these lines, see conc...             NPR  \n",
       "12327  </strong> Read the \"<a href=\"https://www.wantt...    The Guardian  \n",
       "12329  </strong> Watch the <a href=\"https://www.youtu...      Daily Mail  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "from sklearn.feature_extraction.text import TfidfVectorizer\n",
    "from sklearn.metrics.pairwise import cosine_similarity\n",
    "\n",
    "#read in selected columns of preprocessed file\n",
    "df = pd.read_csv(\"C:\\\\datasources\\\\WTKfullraw.csv\", sep='|', usecols=['ArticleId','Title','PublicationDate','cpub','Links','wtkURL','Description','Note','tags','Priority'])\n",
    "#sort by \n",
    "# df = pd.DataFrame(df.sort_values(by='PublicationDate', ascending=False))\n",
    "\n",
    "#deduplication and NaN cleanup\n",
    "df.drop_duplicates('Title')\n",
    "df = df[df['tags'].notna()]\n",
    "df = df[df['Priority'].notna()]\n",
    "df = df[df['wtkURL'].notna()]\n",
    "\n",
    "#substituting multiple spaces with single space\n",
    "df['Description']= df['Description'].apply(lambda x: re.sub(r'\\s+',' ', str(x)))\n",
    "#remove double quotes\n",
    "df['Description']= df['Description'].apply(lambda r: r.replace('\\\"\\\"', '\\\"'))\n",
    "#remove paragraph styling\n",
    "df['Description']= df['Description'].apply(lambda r: r.replace('<p style=\\\"text-align: justify;font-size: 11pt; font-family: Arial;margin: 0 0 11pt 0\\\">', '<p>'))\n",
    "\n",
    "df.tail(6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4941d88c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.tail(12)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "778e92d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Compute the TF-IDF vectors for the preprocessed article text\n",
    "vectorizer = TfidfVectorizer()\n",
    "tfidf_matrix = vectorizer.fit_transform(df['Description'])\n",
    "\n",
    "#Compute the cosine similarity matrix\n",
    "cosine_sim = cosine_similarity(tfidf_matrix, tfidf_matrix)\n",
    "\n",
    "#Iterate through each article and find the most similar articles based on cosine similarity\n",
    "related_articles = []\n",
    "for i in range(len(df)):\n",
    "    similar_articles = []\n",
    "    \n",
    "    # Get the cosine similarity scores for the current article\n",
    "    scores = list(enumerate(cosine_sim[i]))\n",
    "    \n",
    "    # Sort the scores in descending order\n",
    "    scores = sorted(scores, key=lambda x: x[1], reverse=True)\n",
    "    \n",
    "    # Get the top 10 most similar articles (excluding itself)\n",
    "    top_similar = scores[1:11]\n",
    "    for j, _ in top_similar:\n",
    "        similar_articles.append(df.iloc[j]['ArticleId'])\n",
    "    related_articles.append(similar_articles)\n",
    "\n",
    "# Add the column of related articles to the DataFrame\n",
    "df['Related'] = related_articles\n",
    "\n",
    "df.to_csv('C:\\\\datasources\\\\WTKrelated.csv', sep='|', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "570b58bb",
   "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>ArticleId</th>\n",
       "      <th>Title</th>\n",
       "      <th>PublicationDate</th>\n",
       "      <th>Links</th>\n",
       "      <th>Description</th>\n",
       "      <th>Priority</th>\n",
       "      <th>wtkURL</th>\n",
       "      <th>tags</th>\n",
       "      <th>Note</th>\n",
       "      <th>cpub</th>\n",
       "      <th>Related</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12308</th>\n",
       "      <td>12512</td>\n",
       "      <td>How a Human Rights Lawyer Went From Hero to Ho...</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>https://www.thenation.com/article/activism/ste...</td>\n",
       "      <td>&lt;p&gt;More than a quarter-century ago, Steven Do...</td>\n",
       "      <td>910.0</td>\n",
       "      <td>https://www.wanttoknow.info/a-how-human-rights...</td>\n",
       "      <td>['governmentcorruption', ' corporatecorruption...</td>\n",
       "      <td>&lt;/strong&gt; Read more about &lt;a href=\"https://www...</td>\n",
       "      <td>The Nation</td>\n",
       "      <td>[10547, 4692, 4388, 5555, 9587, 3189, 2598, 49...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12312</th>\n",
       "      <td>12516</td>\n",
       "      <td>Does Direct-to-Consumer Advertising Directly H...</td>\n",
       "      <td>2023-03-29</td>\n",
       "      <td>https://time.com/6266695/direct-to-consumer-ad...</td>\n",
       "      <td>&lt;p&gt;Researchers from Johns Hopkins University r...</td>\n",
       "      <td>932.0</td>\n",
       "      <td>https://www.wanttoknow.info/a-does-directtocon...</td>\n",
       "      <td>['corporatecorruption', ' pharmaceuticalcorrup...</td>\n",
       "      <td>&lt;/strong&gt; &lt;a href=\"https://www.bitchute.com/vi...</td>\n",
       "      <td>Time</td>\n",
       "      <td>[12518, 7491, 9197, 6600, 10817, 1629, 11684, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12314</th>\n",
       "      <td>12518</td>\n",
       "      <td>Less Than a Third of Heavily Advertised Drugs ...</td>\n",
       "      <td>2023-01-18</td>\n",
       "      <td>https://www.usnews.com/news/health-news/articl...</td>\n",
       "      <td>&lt;p&gt;&lt;strong&gt;Television ads for drugs are filled...</td>\n",
       "      <td>926.0</td>\n",
       "      <td>https://www.wanttoknow.info/a-less-than-third-...</td>\n",
       "      <td>['corporatecorruption', ' pharmaceuticalcorrup...</td>\n",
       "      <td>&lt;/strong&gt; &lt;a href=\"https://www.bitchute.com/vi...</td>\n",
       "      <td>US News and World Report</td>\n",
       "      <td>[12516, 11684, 9197, 9795, 10817, 7491, 11069,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12315</th>\n",
       "      <td>12519</td>\n",
       "      <td>EPA accused of failing to regulate use of toxi...</td>\n",
       "      <td>2023-04-24</td>\n",
       "      <td>https://www.theguardian.com/environment/2023/a...</td>\n",
       "      <td>&lt;p&gt;The US Environmental Protection Agency has ...</td>\n",
       "      <td>915.0</td>\n",
       "      <td>https://www.wanttoknow.info/a-epa-accused-fail...</td>\n",
       "      <td>['governmentcorruption', ' corporatecorruption...</td>\n",
       "      <td>&lt;/strong&gt; For more along these lines, see conc...</td>\n",
       "      <td>The Guardian</td>\n",
       "      <td>[10607, 9074, 9236, 8469, 8568, 10137, 10009, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12317</th>\n",
       "      <td>12521</td>\n",
       "      <td>Health care providers were 'bribed' into sugge...</td>\n",
       "      <td>2023-04-14</td>\n",
       "      <td>https://abcnews4.com/news/nation-world/health-...</td>\n",
       "      <td>&lt;p&gt;Rep. Thomas Massie, R-Ky., shared a docume...</td>\n",
       "      <td>925.0</td>\n",
       "      <td>https://www.wanttoknow.info/a-health-care-prov...</td>\n",
       "      <td>['coronavirus']</td>\n",
       "      <td>&lt;/strong&gt; Read more on the concerns regarding ...</td>\n",
       "      <td>ABC</td>\n",
       "      <td>[11573, 11907, 11416, 11560, 6963, 11663, 1175...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12321</th>\n",
       "      <td>12525</td>\n",
       "      <td>Rendlesham Forest UFO sighting 'new evidence' ...</td>\n",
       "      <td>2015-07-13</td>\n",
       "      <td>https://www.bbc.com/news/uk-england-suffolk-33...</td>\n",
       "      <td>&lt;p&gt;New evidence has been gathered to back up ...</td>\n",
       "      <td>920.0</td>\n",
       "      <td>https://www.wanttoknow.info/a-rendlesham-fores...</td>\n",
       "      <td>['ufos']</td>\n",
       "      <td>&lt;/strong&gt; Read the official report of this min...</td>\n",
       "      <td>BBC</td>\n",
       "      <td>[2536, 3402, 1552, 2576, 3750, 10875, 3182, 91...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12322</th>\n",
       "      <td>12526</td>\n",
       "      <td>Confessions of a CIA Terrorist</td>\n",
       "      <td>2023-05-20</td>\n",
       "      <td>https://www.laprogressive.com/foreign-policy/c...</td>\n",
       "      <td>&lt;p&gt;In 1979 the Sandinista revolution overthrew...</td>\n",
       "      <td>915.0</td>\n",
       "      <td>https://www.wanttoknow.info/a-confessions-a-ci...</td>\n",
       "      <td>['massmedia', ' governmentcorruption', ' intel...</td>\n",
       "      <td>&lt;/strong&gt; For more along these lines, see conc...</td>\n",
       "      <td>LA Progressive</td>\n",
       "      <td>[8588, 6582, 638, 8589, 10142, 8777, 12204, 42...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12326</th>\n",
       "      <td>12530</td>\n",
       "      <td>When a prison sentence becomes a death sentence</td>\n",
       "      <td>2023-04-27</td>\n",
       "      <td>https://www.npr.org/sections/health-shots/2023...</td>\n",
       "      <td>&lt;p&gt;&lt;strong&gt;At least 6,182 people died in stat...</td>\n",
       "      <td>910.0</td>\n",
       "      <td>https://www.wanttoknow.info/a-when-prison-sent...</td>\n",
       "      <td>['governmentcorruption', ' prisonscorruption']</td>\n",
       "      <td>&lt;/strong&gt; For more along these lines, see conc...</td>\n",
       "      <td>NPR</td>\n",
       "      <td>[10111, 8056, 5129, 10168, 8094, 7599, 12139, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12327</th>\n",
       "      <td>12531</td>\n",
       "      <td>˜The forever prisoner': Abu Zubaydah's drawing...</td>\n",
       "      <td>2023-05-11</td>\n",
       "      <td>https://www.theguardian.com/law/2023/may/11/ab...</td>\n",
       "      <td>&lt;p&gt;A detainee held in the US prison camp at G...</td>\n",
       "      <td>918.0</td>\n",
       "      <td>https://www.wanttoknow.info/a--forever-prisone...</td>\n",
       "      <td>['governmentcorruption', ' terrorism', ' priso...</td>\n",
       "      <td>&lt;/strong&gt; Read the \"&lt;a href=\"https://www.wantt...</td>\n",
       "      <td>The Guardian</td>\n",
       "      <td>[8224, 10466, 11808, 8183, 10433, 7797, 10432,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12329</th>\n",
       "      <td>12533</td>\n",
       "      <td>Six whistleblowers who claim they worked on mi...</td>\n",
       "      <td>2023-04-26</td>\n",
       "      <td>https://www.dailymail.co.uk/news/article-11996...</td>\n",
       "      <td>&lt;p&gt;Senior members of Congress have spoken to ...</td>\n",
       "      <td>915.0</td>\n",
       "      <td>https://www.wanttoknow.info/a-six-whistleblowe...</td>\n",
       "      <td>['ufos']</td>\n",
       "      <td>&lt;/strong&gt; Watch the &lt;a href=\"https://www.youtu...</td>\n",
       "      <td>Daily Mail</td>\n",
       "      <td>[10875, 7019, 9164, 9893, 9165, 9863, 9025, 77...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ArticleId                                              Title  \\\n",
       "12308      12512  How a Human Rights Lawyer Went From Hero to Ho...   \n",
       "12312      12516  Does Direct-to-Consumer Advertising Directly H...   \n",
       "12314      12518  Less Than a Third of Heavily Advertised Drugs ...   \n",
       "12315      12519  EPA accused of failing to regulate use of toxi...   \n",
       "12317      12521  Health care providers were 'bribed' into sugge...   \n",
       "12321      12525  Rendlesham Forest UFO sighting 'new evidence' ...   \n",
       "12322      12526                     Confessions of a CIA Terrorist   \n",
       "12326      12530    When a prison sentence becomes a death sentence   \n",
       "12327      12531  ˜The forever prisoner': Abu Zubaydah's drawing...   \n",
       "12329      12533  Six whistleblowers who claim they worked on mi...   \n",
       "\n",
       "      PublicationDate                                              Links  \\\n",
       "12308      2020-03-31  https://www.thenation.com/article/activism/ste...   \n",
       "12312      2023-03-29  https://time.com/6266695/direct-to-consumer-ad...   \n",
       "12314      2023-01-18  https://www.usnews.com/news/health-news/articl...   \n",
       "12315      2023-04-24  https://www.theguardian.com/environment/2023/a...   \n",
       "12317      2023-04-14  https://abcnews4.com/news/nation-world/health-...   \n",
       "12321      2015-07-13  https://www.bbc.com/news/uk-england-suffolk-33...   \n",
       "12322      2023-05-20  https://www.laprogressive.com/foreign-policy/c...   \n",
       "12326      2023-04-27  https://www.npr.org/sections/health-shots/2023...   \n",
       "12327      2023-05-11  https://www.theguardian.com/law/2023/may/11/ab...   \n",
       "12329      2023-04-26  https://www.dailymail.co.uk/news/article-11996...   \n",
       "\n",
       "                                             Description  Priority  \\\n",
       "12308   <p>More than a quarter-century ago, Steven Do...     910.0   \n",
       "12312  <p>Researchers from Johns Hopkins University r...     932.0   \n",
       "12314  <p><strong>Television ads for drugs are filled...     926.0   \n",
       "12315  <p>The US Environmental Protection Agency has ...     915.0   \n",
       "12317   <p>Rep. Thomas Massie, R-Ky., shared a docume...     925.0   \n",
       "12321   <p>New evidence has been gathered to back up ...     920.0   \n",
       "12322  <p>In 1979 the Sandinista revolution overthrew...     915.0   \n",
       "12326   <p><strong>At least 6,182 people died in stat...     910.0   \n",
       "12327   <p>A detainee held in the US prison camp at G...     918.0   \n",
       "12329   <p>Senior members of Congress have spoken to ...     915.0   \n",
       "\n",
       "                                                  wtkURL  \\\n",
       "12308  https://www.wanttoknow.info/a-how-human-rights...   \n",
       "12312  https://www.wanttoknow.info/a-does-directtocon...   \n",
       "12314  https://www.wanttoknow.info/a-less-than-third-...   \n",
       "12315  https://www.wanttoknow.info/a-epa-accused-fail...   \n",
       "12317  https://www.wanttoknow.info/a-health-care-prov...   \n",
       "12321  https://www.wanttoknow.info/a-rendlesham-fores...   \n",
       "12322  https://www.wanttoknow.info/a-confessions-a-ci...   \n",
       "12326  https://www.wanttoknow.info/a-when-prison-sent...   \n",
       "12327  https://www.wanttoknow.info/a--forever-prisone...   \n",
       "12329  https://www.wanttoknow.info/a-six-whistleblowe...   \n",
       "\n",
       "                                                    tags  \\\n",
       "12308  ['governmentcorruption', ' corporatecorruption...   \n",
       "12312  ['corporatecorruption', ' pharmaceuticalcorrup...   \n",
       "12314  ['corporatecorruption', ' pharmaceuticalcorrup...   \n",
       "12315  ['governmentcorruption', ' corporatecorruption...   \n",
       "12317                                    ['coronavirus']   \n",
       "12321                                           ['ufos']   \n",
       "12322  ['massmedia', ' governmentcorruption', ' intel...   \n",
       "12326     ['governmentcorruption', ' prisonscorruption']   \n",
       "12327  ['governmentcorruption', ' terrorism', ' priso...   \n",
       "12329                                           ['ufos']   \n",
       "\n",
       "                                                    Note  \\\n",
       "12308  </strong> Read more about <a href=\"https://www...   \n",
       "12312  </strong> <a href=\"https://www.bitchute.com/vi...   \n",
       "12314  </strong> <a href=\"https://www.bitchute.com/vi...   \n",
       "12315  </strong> For more along these lines, see conc...   \n",
       "12317  </strong> Read more on the concerns regarding ...   \n",
       "12321  </strong> Read the official report of this min...   \n",
       "12322  </strong> For more along these lines, see conc...   \n",
       "12326  </strong> For more along these lines, see conc...   \n",
       "12327  </strong> Read the \"<a href=\"https://www.wantt...   \n",
       "12329  </strong> Watch the <a href=\"https://www.youtu...   \n",
       "\n",
       "                           cpub  \\\n",
       "12308                The Nation   \n",
       "12312                      Time   \n",
       "12314  US News and World Report   \n",
       "12315              The Guardian   \n",
       "12317                       ABC   \n",
       "12321                       BBC   \n",
       "12322            LA Progressive   \n",
       "12326                       NPR   \n",
       "12327              The Guardian   \n",
       "12329                Daily Mail   \n",
       "\n",
       "                                                 Related  \n",
       "12308  [10547, 4692, 4388, 5555, 9587, 3189, 2598, 49...  \n",
       "12312  [12518, 7491, 9197, 6600, 10817, 1629, 11684, ...  \n",
       "12314  [12516, 11684, 9197, 9795, 10817, 7491, 11069,...  \n",
       "12315  [10607, 9074, 9236, 8469, 8568, 10137, 10009, ...  \n",
       "12317  [11573, 11907, 11416, 11560, 6963, 11663, 1175...  \n",
       "12321  [2536, 3402, 1552, 2576, 3750, 10875, 3182, 91...  \n",
       "12322  [8588, 6582, 638, 8589, 10142, 8777, 12204, 42...  \n",
       "12326  [10111, 8056, 5129, 10168, 8094, 7599, 12139, ...  \n",
       "12327  [8224, 10466, 11808, 8183, 10433, 7797, 10432,...  \n",
       "12329  [10875, 7019, 9164, 9893, 9165, 9863, 9025, 77...  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1bb64e2",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}