A_prediction_using_pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Prediction using Pandas and Co."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %matplotlib inline\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "# import dask.dataframe as dd\n",
    "from bs4 import BeautifulSoup # For Scraping HTML page\n",
    "from bs4.element import Comment\n",
    "import re\n",
    "import tldextract"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sqlite:///avdb.db\n",
      "True\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import create_engine # sqlalchemy = ORM library\n",
    "from sqlalchemy_utils.functions import database_exists, drop_database\n",
    "import os\n",
    "cwd = os.getcwd() # current working  directory\n",
    "\n",
    "# Create an engine that stores data in the local directory's\n",
    "# sqlalchemy_example.db file.\n",
    "db_engine = create_engine('sqlite:///avdb.db', encoding='utf-8')\n",
    "# db_path = os.path.realpath( os.path.dirname(str(engine.url)) )\n",
    "url = db_engine.url\n",
    "print(url)\n",
    "print(database_exists(url))\n",
    "# drop_database(url)\n",
    "# print(database_exists(url))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_data = 'bigdata/train.csv'\n",
    "html_data = 'bigdata/train/html_data.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Domain</th>\n",
       "      <th>Url</th>\n",
       "      <th>Tag</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Webpage_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>www.fiercepharma.com</td>\n",
       "      <td>http://www.fiercepharma.com/marketing/tecfider...</td>\n",
       "      <td>news</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>www.fiercepharma.com</td>\n",
       "      <td>http://www.fiercepharma.com/pharma/novo-equipp...</td>\n",
       "      <td>news</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>www.fiercepharma.com</td>\n",
       "      <td>http://www.fiercepharma.com/pharma/another-exe...</td>\n",
       "      <td>news</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>www.fiercepharma.com</td>\n",
       "      <td>http://www.fiercepharma.com/pharma/teva-buy-bi...</td>\n",
       "      <td>news</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>www.fiercepharma.com</td>\n",
       "      <td>http://www.fiercepharma.com/marketing/actress-...</td>\n",
       "      <td>news</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Domain  \\\n",
       "Webpage_id                         \n",
       "1           www.fiercepharma.com   \n",
       "2           www.fiercepharma.com   \n",
       "3           www.fiercepharma.com   \n",
       "4           www.fiercepharma.com   \n",
       "5           www.fiercepharma.com   \n",
       "\n",
       "                                                          Url   Tag  \n",
       "Webpage_id                                                           \n",
       "1           http://www.fiercepharma.com/marketing/tecfider...  news  \n",
       "2           http://www.fiercepharma.com/pharma/novo-equipp...  news  \n",
       "3           http://www.fiercepharma.com/pharma/another-exe...  news  \n",
       "4           http://www.fiercepharma.com/pharma/teva-buy-bi...  news  \n",
       "5           http://www.fiercepharma.com/marketing/actress-...  news  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(train_data, index_col='Webpage_id')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Domain    0\n",
       "Url       0\n",
       "Tag       0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull().sum() # No null values, for good..Sanity check ok!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 53447 entries, 1 to 79345\n",
      "Data columns (total 3 columns):\n",
      "Domain    53447 non-null object\n",
      "Url       53447 non-null object\n",
      "Tag       53447 non-null object\n",
      "dtypes: object(3)\n",
      "memory usage: 1.6+ MB\n"
     ]
    }
   ],
   "source": [
    "# df.drop(columns=['Url'], inplace=True)\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 53447 entries, 1 to 79345\n",
      "Data columns (total 3 columns):\n",
      "Domain    53447 non-null category\n",
      "Url       53447 non-null object\n",
      "Tag       53447 non-null category\n",
      "dtypes: category(2), object(1)\n",
      "memory usage: 1.2+ MB\n"
     ]
    }
   ],
   "source": [
    "# Convert columns to CATEGORY type to reduce memory footprint\n",
    "df.Domain =  df.Domain.astype('category')\n",
    "df.Tag = df.Tag.astype('category')\n",
    "df.info() # Size reduced from 1.6+ to 1.2+"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['clinicalTrials', 'conferences', 'forum', 'guidelines', 'news', 'others', 'profile', 'publication', 'thesis']\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "others            17417\n",
       "news               7992\n",
       "publication        7705\n",
       "profile            5196\n",
       "conferences        4666\n",
       "forum              4503\n",
       "clinicalTrials     2839\n",
       "thesis             1800\n",
       "guidelines         1329\n",
       "Name: Tag, dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# What are the different Target Classes, we have?\n",
    "\n",
    "print(sorted(df.Tag.unique()))\n",
    "# ['clinicalTrials', 'conferences', 'forum', 'guidelines', 'news', 'others', 'profile', 'publication', 'thesis']\n",
    "\n",
    "df.Tag.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "thesis.library.caltech.edu                301\n",
       "ecommons.cornell.edu                      300\n",
       "dspace.mit.edu                            300\n",
       "curate.nd.edu                             300\n",
       "academiccommons.columbia.edu              300\n",
       "www.dart-europe.eu                        300\n",
       "www.nice.org.uk                           230\n",
       "www.ncbi.nlm.nih.gov                      226\n",
       "www.australiancancertrials.gov.au         209\n",
       "ctri.nic.in                               209\n",
       "rctportal.niph.go.jp                      206\n",
       "slctr.lk                                  203\n",
       "en.search.irct.ir                         201\n",
       "www.anzctr.org.au                         200\n",
       "clinicaltrials.gov                        200\n",
       "www.isrctn.com                            200\n",
       "www.medbox.org                            200\n",
       "upload.umin.ac.jp                         200\n",
       "cris.nih.go.kr                            200\n",
       "www.chictr.org.cn                         200\n",
       "www.clinicalguidelines.gov.au             200\n",
       "www.trialregister.nl                      200\n",
       "www.clinicaltrials.in.th                  200\n",
       "www.clinicaltrials.jp                     200\n",
       "agelab.mit.edu                            199\n",
       "www.mskcc.org                             196\n",
       "guidelines.gov                            189\n",
       "www.ucsfhealth.org                        174\n",
       "www.hopkinsmedicine.org                   172\n",
       "www.openwetware.org                       170\n",
       "                                         ... \n",
       "onlung.ca                                   1\n",
       "online.udel.edu                             1\n",
       "pathology.med.uky.edu                       1\n",
       "online.liebertpub.com                       1\n",
       "online.bryantstratton.edu                   1\n",
       "oncology.nursingconference.com              1\n",
       "oncologists.cancersummit.org                1\n",
       "olt.theygsgroup.com                         1\n",
       "ok.ubc.ca                                   1\n",
       "ojs.aishe.org                               1\n",
       "openaccess.thehealthwell.info               1\n",
       "opennetworking.us8.list-manage.com          1\n",
       "optimusride.com                             1\n",
       "oraltherapy.dentalcongress.com              1\n",
       "patents.cercacor.com                        1\n",
       "partnerships.ifpma.org                      1\n",
       "pancreatic-colorectal.cancersummit.org      1\n",
       "palmbeach.floridaweekly.com                 1\n",
       "pallonc.org                                 1\n",
       "paleocurebook.com                           1\n",
       "pakcyber.com                                1\n",
       "outpatients.tas.gov.au                      1\n",
       "outmatch.org                                1\n",
       "outdatedbrowser.com                         1\n",
       "otm.illinois.edu                            1\n",
       "otd.harvard.edu                             1\n",
       "osteoporosiscalgary.com                     1\n",
       "orsp.kean.edu                               1\n",
       "organisations.rcni.com                      1\n",
       "ncn.gov.pl                                  1\n",
       "Name: Domain, Length: 3974, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.Domain.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "# df.Domain.apply(lambda x: x.replace('www[0-9].','')).value_counts().sort_index()\n",
    "# df.Domain.apply(lambda x: re.sub(r'ww[0-9A-Za-z]*.','',x)) \\\n",
    "#          .apply(lambda x: re.sub(r'201[0-9]{1,}.','',x)) \\\n",
    "#          .value_counts().sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "biomedcentral                  3568\n",
       "bayer                          1675\n",
       "mit                            1663\n",
       "gsk                            1081\n",
       "bmj                             776\n",
       "nih                             645\n",
       "clinicaltrials                  600\n",
       "conferenceseries                546\n",
       "sagepub                         448\n",
       "blogspot                        386\n",
       "amgen                           383\n",
       "aacrjournals                    375\n",
       "abbvie                          352\n",
       "dana-farber                     352\n",
       "emdgroup                        322\n",
       "caltech                         322\n",
       "cornell                         304\n",
       "nd                              300\n",
       "columbia                        300\n",
       "dart-europe                     300\n",
       "qiagen                          297\n",
       "mskcc                           295\n",
       "astrazeneca                     279\n",
       "pharmaceuticalconferences       262\n",
       "physiology                      259\n",
       "boehringer-ingelheim            254\n",
       "biogen                          238\n",
       "alexion                         232\n",
       "nice                            230\n",
       "asm                             210\n",
       "                               ... \n",
       "myast                             1\n",
       "cjcnn                             1\n",
       "ropme                             1\n",
       "dailystrength                     1\n",
       "mortgagecancellationsecrets       1\n",
       "thehealthwell                     1\n",
       "salus                             1\n",
       "3lbgames                          1\n",
       "dailyvitamina                     1\n",
       "nationalroboticsweek              1\n",
       "terminalserviceplus               1\n",
       "paleocurebook                     1\n",
       "umassmemorial                     1\n",
       "safpj                             1\n",
       "haaretz                           1\n",
       "jiaci                             1\n",
       "ota                               1\n",
       "haymarket                         1\n",
       "watches2u                         1\n",
       "tjn                               1\n",
       "coolfoodplanet                    1\n",
       "animalsmart                       1\n",
       "portlandpresspublishing           1\n",
       "nypost                            1\n",
       "open-access                       1\n",
       "pensoft                           1\n",
       "bidmc                             1\n",
       "transfusionnews                   1\n",
       "orange                            1\n",
       "greenphotonicsguide               1\n",
       "Name: Domain, Length: 2279, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get just the domain from URLs\n",
    "import tldextract\n",
    "\n",
    "def extract_domain(url):\n",
    "    return tldextract.extract(url).domain\n",
    "\n",
    "df.Domain = df.Domain.apply(extract_domain)\n",
    "df.Domain.value_counts() #.sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sanity check for null values\n",
    "df.Domain.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['uofmhealth', 'sidneyhealth', 'sovhealth', 'healthcare4ppl',\n",
       "       'healthgrades', 'wkhealth', 'health', 'monashhealth', 'sahealth',\n",
       "       'globalhealthaction', 'longevityandhealthspan',\n",
       "       'microbecolhealthdis', 'besthealthmag', 'emedicinehealth',\n",
       "       'everydayhealth', 'healthable', 'healthcareitnews',\n",
       "       'healthnutnews', 'healthyfoodteam', 'healthywomen',\n",
       "       'healthy-holistic-living', 'naturalhealth365', 'wakehealth',\n",
       "       '1millionhealthworkers', 'bulletinhealthcare',\n",
       "       'animalhealthireland', 'blissfullyhealthy', 'worldlunghealth',\n",
       "       'healthimpactnews', 'thehealthwell', 'samhealth', 'browardhealth',\n",
       "       'dukehealth', 'orlandohealth', 'emoryhealthcare', 'unchealthcare',\n",
       "       'uhealthsystem', 'tophealthnews', 'uchealth', 'myhealthtalent',\n",
       "       'irishhealth', 'ucsfhealth', 'consumerhealthdigest',\n",
       "       'healthaffairs', 'onlymyhealth', 'spine-health',\n",
       "       'thehealthyhomeeconomist', 'created4health', 'ghanahealthservice',\n",
       "       'healthcommcore', 'healthierwealthiersmarter', 'healthiertn',\n",
       "       'healthblogs', 'ariahealth'], dtype=object)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df[df.Domain.str.match('chictr')]\n",
    "df[df.Domain.str.contains(\"health\")]['Domain'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>Webpage_id</th>\n",
       "      <th>Html</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>&lt;!DOCTYPE html&gt;\\n&lt;html lang=\"en\" dir=\"ltr\" xml...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>&lt;!DOCTYPE html&gt;\\n&lt;html lang=\"en\" dir=\"ltr\" xml...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>&lt;!DOCTYPE html&gt;\\n&lt;html lang=\"en\" dir=\"ltr\" xml...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Webpage_id                                               Html\n",
       "0           1  <!DOCTYPE html>\\n<html lang=\"en\" dir=\"ltr\" xml...\n",
       "1           2  <!DOCTYPE html>\\n<html lang=\"en\" dir=\"ltr\" xml...\n",
       "2           3  <!DOCTYPE html>\\n<html lang=\"en\" dir=\"ltr\" xml..."
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sample data from HTML DataFrame\n",
    "hdf = pd.read_csv('bigdata/train/html_data.csv', nrows=3) # Read just 10 rows\n",
    "hdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\" For Testing Purpopse Only\\n# Read html_data.csv using pandas in chunks.\\n# The chunksize parameter refers to the number of rows per chunk.\\nrow_count = 100\\ntxtFileReader = pd.read_csv('bigdata/train/html_data.csv', chunksize=row_count, iterator=True, index_col='Webpage_id')\\nddf = txtFileReader.get_chunk()\\n\""
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "''' For Testing Purpopse Only\n",
    "# Read html_data.csv using pandas in chunks.\n",
    "# The chunksize parameter refers to the number of rows per chunk.\n",
    "row_count = 100\n",
    "txtFileReader = pd.read_csv('bigdata/train/html_data.csv', chunksize=row_count, iterator=True, index_col='Webpage_id')\n",
    "ddf = txtFileReader.get_chunk()\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Domain                                         fiercepharma\n",
       "Url       http://www.fiercepharma.com/pharma/novo-equipp...\n",
       "Tag                                                    news\n",
       "Name: 2, dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.iloc[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Tecfidera, Gilenya and Aubagio's 3-way battle for MS share is about to heat up | FiercePharma\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "# Objective : Extract text from title tag of HTML source of web-page\n",
    "def extract_title(page):\n",
    "    if (page == None): \n",
    "        return None\n",
    "    soup = BeautifulSoup(page, 'html.parser')\n",
    "    title_tag = soup.find('title')\n",
    "    if (title_tag == None):\n",
    "        title = None\n",
    "    else:\n",
    "        title = title_tag.text.strip()\n",
    "    return title\n",
    "\n",
    "# Test method definition\n",
    "page = hdf.head(1)['Html'].values[0]\n",
    "print(extract_title(page))\n",
    "print(extract_title(\"<html></html>\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Skip to main content Twitter LinkedIn Search Top Menu DDF 2017 FierceBiotech Jobs Resources Events Subscribe Main navigation Pharma M&A Regulatory Financials Corporate Legal Manufacturing M&A Outsourcing Regulatory Supply Chain Partnering Drug Safety Marketing Regulatory DTC Advertising Digital and Social Media Data and Analytics Launches Pharma Asia M&A R&D Regulatory Sales and Marketing Financials Manufacturing Animal Health R&D M&A Regulatory Veterinarian Financials Vaccines Drug Delivery R&D Regulatory Partnering Vaccines Deals Infectious Diseases R&D Regulatory Main navigation - Mobile Pharma M&A Regulatory Financials Corporate Legal Manufacturing M&A Outsourcing Regulatory Supply Chain Partnering Drug Safety Marketing Regulatory DTC Advertising Digital and Social Media Data and Analytics Launches Pharma Asia M&A R&D Regulatory Sales and Marketing Financials Manufacturing Animal Health R&D M&A Regulatory Veterinarian Financials Vaccines Drug Delivery R&D Regulatory Partnering Vaccines Deals Infectious Diseases R&D Regulatory Marketing Tecfidera Gilenya and Aubagio\\'s 3-way battle for MS share is about to heat up by Tracy Staton Oct 31 2016 6:54am Multiple sclerosis drugmakers are in a fix: These days the U.S. market isn’t delivering the kind of growth that had been fueling big increases in sales. For high-flying pills from Biogen (Tecfidera) and Novartis (Gilenya) that could mean an unwelcome comedown. It definitely means the coming market-share battle will grow even more competitive than before. If the quickest way to grow sales is to steal share you can bet Tecfidera and Gilenya--and their up-and-coming rival med from Sanofi Aubagio--will be working hard to do just that. Thing is Biogen isn’t on that sort of growth trajectory. It’s the market leader--with 51% share of the oral market and 20% of MS overall as EVP Michel Vounatsos hastened to point out during the company’s earnings call with analysts last week. But Tecfidera’s 10% growth during the quarter largely depended on price increases the company disclosed in a securities filing the next day. Gilenya on the other hand grew by 15% and in its earnings announcement the Swiss drugmaker said that growth wasn’t based on price but volume. That’s not to say Gilenya doesn’t have some troubling signs in its Q3 stats; pharma chief Paul Hudson said script numbers have been “broadly flat but a little bit down”--which suggests the overall market slowdown hasn’t been without its effect on Gilenya too. Neither Biogen nor Novartis plans to stick with the status quo. Biogen is grappling with Tecfidera’s gastrointestinal side effects and various MS specialists are working on ways to manage that; 22% of Tecfidera patients quit because of GI issues and keeping patients on therapy would obviously help sales. The Tecfidera team is pilot-testing a new “engagement model” at top treatment centers with plans to roll it out by January. “The team in the U.S. is all up and running” Vounatsos said during the call. “I’m very pleased to see the perception improvement and now we have to continue to execute.” On the advertising side Biogen recently backed off a TV campaign that had stirred up some controversy in the MS community and the company said at the time that it didn’t plan more television in the immediate future though it is running other DTC promos. Novartis is running with its Hey MS Take This! campaign for Gilenya which launched in 2014 and has since won kudos--and made inroads--particularly with its digital and social media work. Besides print and web video the campaign invites patients to “tell relapsing MS exactly where to go” and many have both on the campaign’s website and via social media. They’ve also shared what they do to manage their MS. Novartis apparently believes that Gilenya DTC and salesforce work coupled with a steady stream of new data will keep the brand’s volume growing enough to avoid using price as a driver. The company has “some sensible plans” for growth without “a big impact ... on price” Hudson said during the Q3 call. “We are ... looking forward to the energy into Q4” he said. Meanwhile the big leaper of the quarter was Aubagio Sanofi’s slow-to-start entry in the class. Sales of that drug grew by almost 50% to 334 million euros during the quarter--and that was driven by growth in the U.S. Its 9-month revenue amounted to 928 million euros a 57% hike. The French drugmaker quickly bragged that Aubagio is the “fastest-growing oral disease modifying therapy” in MS with a U.S. share of 9%--far far below Tecfidera’s but rising. This time last year Sanofi was happy to report it had captured a 6% share. What’s spurring Aubagio on? The company has been running a campaign with Revenge star Madeleine Stowe since late 2014 for one; the actor has been traveling across the U.S. doing awareness events. The Lights! Camera! Take Action! partnership most recently rolled out a series of “webisodes” featuring Stowe in interviews with MS patients--a common theme in awareness campaigns--but also with nurses physical therapists and others with advice for MS patients and their caregivers. The drug\\'s brand website also has a share-your-story channel and Sanofi has put other promo work under a tagline \"Aubagio quiets MS.\" The company has set out a goal of €2 billion in sales by 2020 for its entire franchise; if Aubagio and its fellow med Lemtrada keep delivering as they are the company could actually hit that target sooner. \"In MS we continued to gain share and sales from the franchise are now approaching €2 billion on an annualized basis\" CEO Olivier Brandicourt said during the company\\'s Q3 call. Biogen’s Vounatsos may have summed it up for the entire oral market when he pledged to fight the good fight on Tecfidera last week. “The base of the market is certainly lower than what we have estimated in our assumptions but it\\'s not an excuse for not increasing share” he said. There’s a confounding factor however: The group of oral treatments may get some unwanted rivals in a few years. Novartis could face earlier-than-expected Gilenya generics thanks to some recent patent decisions. As early as 2019 in fact--and that would give these three meds just a few more years to divvy up share just among themselves. Add to that the payer pressure that\\'s increasingly a problem and the price-increase growth option looks even less likely. Related Articles: Biogen’s Tecfidera Amgen’s Enbrel tell a tale of pricing power and weakness Novartis could snag a blockbuster MS market all for itself just in time to fight Gilenya generics Biogen\\'s controversial Tecfidera ad is about to meet its end Biogen sees new Tecfidera buzz as DTC marketing push kicks in Buoyed by rising tide of Aubagio scripts Sanofi aims for €2B in MS sales Early Gilenya generics now spell trouble for rival MS drugs Read more on multiple sclerosis drug launch generics payer Sanofi Aubagio Tecfidera Biogen Novartis Gilenya Popular Content Pfizer puts hemophilia spin on ultrapopular Minecraft to educate young patients Sep 06 2017 Takeda Johnson & Johnson and more join Clara Health\\'s Boston-based patient empowerment push Sep 06 2017 Empathy at work: GlaxoSmithKline extends Excedrin Migraine campaign to include sufferers on the job Sep 04 2017 What\\'s the world\\'s best pharma market? To Allergan\\'s CEO that one\\'s easy Aug 07 2017 Roche nets new Actemra nod to treat deadly CAR-T response Sep 01 2017 About the Author Tracy Staton Editor-in-Chief tracy@fiercemarkets.com https://twitter.com/TracyStaton \\u200b Home Subscribe Manage Newsletter Subscriptions Advertise RSS Privacy About Us Contact © 2017 Questex LLC. All rights reserved. 275 Grove Street Suite 2-130 Newton MA 02466 Reproduction in whole or part is prohibited.'"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Objective : Extract text from HTML source of web-page\n",
    "def extract_body(page):\n",
    "    if (page == None): \n",
    "        return None\n",
    "    soup = BeautifulSoup(page, 'html.parser', from_encoding=\"utf-8\")\n",
    "    body_tag = soup.find('body')\n",
    "    if (body_tag == None):\n",
    "        body = page \n",
    "    else:\n",
    "        body = body_tag # What should be returned here? How to stringify this for further  procecssing?\n",
    "    return body\n",
    "\n",
    "def is_visible_content(element):\n",
    "    if element.parent.name in ['style', 'script', 'head', 'title', 'meta', '[document]']:\n",
    "        return False\n",
    "    if isinstance(element, Comment):\n",
    "        return False\n",
    "    return True\n",
    "\n",
    "def remove_extra_spaces(str):\n",
    "    return u\" \".join(str.split())\n",
    "\n",
    "def extract_text(page):\n",
    "    if (page == None): \n",
    "        return None\n",
    "    soup = BeautifulSoup(page, 'html.parser') #, from_encoding=\"utf-8\"\n",
    "    texts = soup.findAll(text=True) # Extracts text from all HTML Markups, incl nested ones\n",
    "    visible_texts = filter(is_visible_content, texts)\n",
    "    # The u-prefix u\" \".join() indicates Unicode and has been in python since v2.0\n",
    "    # Ref. Read: https://www.joelonsoftware.com/2003/10/08/the-absolute-minimum-every-software-developer-absolutely-positively-must-know-about-unicode-and-character-sets-no-excuses/\n",
    "    text = u\" \".join(remove_extra_spaces(t.strip()) for t in visible_texts)\n",
    "    text = text.replace(',','')\n",
    "    text = text.replace('|','')\n",
    "    text = re.sub(r'\\s\\s+',' ',text).strip()\n",
    "    return text.encode('utf-8',errors='ignore').decode('utf-8').strip()\n",
    "\n",
    "text = extract_text(hdf.head(1)['Html'].values[0])\n",
    "# extract_text( extract_body(hdf.head(1)['Html'].values[0]) )\n",
    "text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(texts[0]) # Be either bs4.element.Doctype or bs4.element.NavigatableString\n",
    "texts[94]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Processing records from 75001 to 76000\n",
      "Processing records from 76001 to 77000\n",
      "Processing records from 77001 to 78000\n",
      "Processing records from 78001 to 79000\n",
      "Processing records from 79001 to 79345\n"
     ]
    }
   ],
   "source": [
    "# OBJECTIVE: Read html_data.csv in chunks, extract title and text from html-page, and add the them to train.csv as new columns\n",
    "\n",
    "if(False): # Set False because the HTML is already loaded,parsed and persisted in DB for convenience\n",
    "    for ddf in pd.read_csv('bigdata/train/html_data.csv', \n",
    "                           index_col='Webpage_id', \n",
    "                           iterator=True , \n",
    "                           chunksize=1000, \n",
    "                           encoding='utf-8'):\n",
    "        start = ddf.head(1).index.values[0]\n",
    "        end = ddf.tail(1).index.values[0]\n",
    "        if(start<75000): continue\n",
    "        print(\"Processing records from {0} to {1}\".format(start, end))\n",
    "        ddf['Title'] = ddf.apply(lambda row: extract_title(row.Html), axis=1)\n",
    "        ddf['Html2Text'] = ddf.apply(lambda row : extract_text(row.Html), axis=1)\n",
    "#         ddf[['Title','Html2Text']].to_csv('bigdata/debugging.csv', encoding='utf-8')\n",
    "        ddf[['Title','Html2Text']].to_sql('webpage_table',db_engine, if_exists='append') #Write to DB to save time/memory\n",
    "#         df.merge(ddf[['Title']], how='inner', left_index=True, right_index=True)\n",
    "#         df['Title'] = ddf.apply(transform_row, axis=1)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "row:  (1, \"Tecfidera, Gilenya and Aubagio's 3-way battle for MS share is about to heat up | FiercePharma\", 'Skip to main content Twitter LinkedIn Search Top Menu DDF 2017 FierceBiotech Jobs Resources Events Subscribe Main navigation Pharma M&A Regulatory Fi ... (7455 characters truncated) ... y About Us Contact © 2017 Questex LLC. All rights reserved. 275 Grove Street Suite 2-130 Newton MA 02466 Reproduction in whole or part is prohibited.')\n",
      "row:  (2, 'Novo equipped to weather the storm in the U.S. diabetes market, CEO says | FiercePharma', 'Skip to main content Twitter LinkedIn Search Top Menu DDF 2017 FierceBiotech Jobs Resources Events Subscribe Main navigation Pharma M&A Regulatory Fi ... (5559 characters truncated) ... y About Us Contact © 2017 Questex LLC. All rights reserved. 275 Grove Street Suite 2-130 Newton MA 02466 Reproduction in whole or part is prohibited.')\n",
      "row:  (3, \"Another exec departs troubled Endo--and this time, it's for another drugmaker | FiercePharma\", 'Skip to main content Twitter LinkedIn Search Top Menu DDF 2017 FierceBiotech Jobs Resources Events Subscribe Main navigation Pharma M&A Regulatory Fi ... (4281 characters truncated) ... y About Us Contact © 2017 Questex LLC. All rights reserved. 275 Grove Street Suite 2-130 Newton MA 02466 Reproduction in whole or part is prohibited.')\n",
      "row:  (4, \"Would Teva buy Korea's Celltrion to beef up in biosimilars? It wouldn't say no | FiercePharma\", \"Skip to main content Twitter LinkedIn Search Top Menu DDF 2017 FierceBiotech Jobs Resources Events Subscribe Main navigation Pharma M&A Regulatory Fi ... (3996 characters truncated) ... y About Us Contact © 2017 Questex LLC. All rights reserved. 275 Grove Street Suite 2-130 Newton MA 02466 Reproduction in whole or part is prohibited.\")\n",
      "row:  (5, 'Restasis-maker Allergan recruits actress Marisa Tomei to drive dry eye awareness | FiercePharma', \"Skip to main content Twitter LinkedIn Search Top Menu DDF 2017 FierceBiotech Jobs Resources Events Subscribe Main navigation Pharma M&A Regulatory Fi ... (4559 characters truncated) ... y About Us Contact © 2017 Questex LLC. All rights reserved. 275 Grove Street Suite 2-130 Newton MA 02466 Reproduction in whole or part is prohibited.\")\n",
      "row:  (79341, 'William J Athans, MD', \"Contact Us Home About Us Find a Location Patient Portal Virtual Tour Foundation Careers Menu ER Online Scheduling Request More Info Back Browse All L ... (7218 characters truncated) ... vember 8 2017 2:32 PM Minor Emergency Reduce Your ER Wait Time. Find an ER location near you to schedule an arrival time online Learn How To Check In\")\n",
      "row:  (79342, 'Christopher E. Ambs, MD', \"Contact Us Home About Us Find a Location Patient Portal Virtual Tour Foundation Careers Menu ER Online Scheduling Request More Info Back Browse All L ... (7159 characters truncated) ... vember 8 2017 2:32 PM Minor Emergency Reduce Your ER Wait Time. Find an ER location near you to schedule an arrival time online Learn How To Check In\")\n",
      "row:  (79343, 'David L Allyn, MD', \"Contact Us Home About Us Find a Location Patient Portal Virtual Tour Foundation Careers Menu ER Online Scheduling Request More Info Back Browse All L ... (7319 characters truncated) ... vember 8 2017 2:32 PM Minor Emergency Reduce Your ER Wait Time. Find an ER location near you to schedule an arrival time online Learn How To Check In\")\n",
      "row:  (79344, 'Illena Antonetti, MD', \"Contact Us Home About Us Find a Location Patient Portal Virtual Tour Foundation Careers Menu ER Online Scheduling Request More Info Back Browse All L ... (9093 characters truncated) ... vember 8 2017 2:32 PM Minor Emergency Reduce Your ER Wait Time. Find an ER location near you to schedule an arrival time online Learn How To Check In\")\n",
      "row:  (79345, '404 Error - South Lake Hospital', \"Contact Us Home About Us Find a Location Patient Portal Virtual Tour Foundation Careers Menu ER Online Scheduling Request More Info Back Browse All L ... (4754 characters truncated) ... vember 8 2017 2:32 PM Minor Emergency Reduce Your ER Wait Time. Find an ER location near you to schedule an arrival time online Learn How To Check In\")\n"
     ]
    }
   ],
   "source": [
    "# OBJECTIVE: Sanity check of the data persistence of the data that was transformed from html_data.csv\n",
    "\n",
    "# ddf[['Title']].to_sql('webpage_table',db_engine, if_exists='append')\n",
    "# db_engine.execute('DROP TABLE IF EXISTS webpage_table')\n",
    "# result = db_engine.execute('select count(*) from webpage_table')\n",
    "result = db_engine.execute('select * from webpage_table where Webpage_id<=5 or Webpage_id>79340')\n",
    "for row in result:\n",
    "    print('row: ', row)\n",
    "result.close()\n",
    "# Q: Data Pre-procecssing : How do we deal with titles in languages other than English??"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape :  (79345, 2)\n",
      "Head :\n",
      "                                                         Title  \\\n",
      "Webpage_id                                                      \n",
      "1           Tecfidera, Gilenya and Aubagio's 3-way battle ...   \n",
      "2           Novo equipped to weather the storm in the U.S....   \n",
      "3           Another exec departs troubled Endo--and this t...   \n",
      "4           Would Teva buy Korea's Celltrion to beef up in...   \n",
      "5           Restasis-maker Allergan recruits actress Maris...   \n",
      "\n",
      "                                                    Html2Text  \n",
      "Webpage_id                                                     \n",
      "1           Skip to main content Twitter LinkedIn Search T...  \n",
      "2           Skip to main content Twitter LinkedIn Search T...  \n",
      "3           Skip to main content Twitter LinkedIn Search T...  \n",
      "4           Skip to main content Twitter LinkedIn Search T...  \n",
      "5           Skip to main content Twitter LinkedIn Search T...  \n"
     ]
    }
   ],
   "source": [
    "# Read Procecssed Data into DataFrame\n",
    "if(False): #You don't want to load a very large file from DB. Do you? Read the proccessed file train1.csv. See further below.\n",
    "    pdf = pd.read_sql_table('webpage_table', db_engine, index_col='Webpage_id')\n",
    "    print('Shape : ', pdf.shape)\n",
    "    print('Head :\\n', pdf.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "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>Domain</th>\n",
       "      <th>Url</th>\n",
       "      <th>Tag</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Webpage_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>fiercepharma</td>\n",
       "      <td>http://www.fiercepharma.com/marketing/tecfider...</td>\n",
       "      <td>news</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>fiercepharma</td>\n",
       "      <td>http://www.fiercepharma.com/pharma/novo-equipp...</td>\n",
       "      <td>news</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>fiercepharma</td>\n",
       "      <td>http://www.fiercepharma.com/pharma/another-exe...</td>\n",
       "      <td>news</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>fiercepharma</td>\n",
       "      <td>http://www.fiercepharma.com/pharma/teva-buy-bi...</td>\n",
       "      <td>news</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>fiercepharma</td>\n",
       "      <td>http://www.fiercepharma.com/marketing/actress-...</td>\n",
       "      <td>news</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Domain                                                Url  \\\n",
       "Webpage_id                                                                    \n",
       "1           fiercepharma  http://www.fiercepharma.com/marketing/tecfider...   \n",
       "2           fiercepharma  http://www.fiercepharma.com/pharma/novo-equipp...   \n",
       "3           fiercepharma  http://www.fiercepharma.com/pharma/another-exe...   \n",
       "4           fiercepharma  http://www.fiercepharma.com/pharma/teva-buy-bi...   \n",
       "5           fiercepharma  http://www.fiercepharma.com/marketing/actress-...   \n",
       "\n",
       "             Tag  \n",
       "Webpage_id        \n",
       "1           news  \n",
       "2           news  \n",
       "3           news  \n",
       "4           news  \n",
       "5           news  "
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape of new DF : (53447, 5)\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>Domain</th>\n",
       "      <th>Url</th>\n",
       "      <th>Tag</th>\n",
       "      <th>Title</th>\n",
       "      <th>Html2Text</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Webpage_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>fiercepharma</td>\n",
       "      <td>http://www.fiercepharma.com/marketing/tecfider...</td>\n",
       "      <td>news</td>\n",
       "      <td>Tecfidera, Gilenya and Aubagio's 3-way battle ...</td>\n",
       "      <td>Skip to main content Twitter LinkedIn Search T...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>fiercepharma</td>\n",
       "      <td>http://www.fiercepharma.com/pharma/novo-equipp...</td>\n",
       "      <td>news</td>\n",
       "      <td>Novo equipped to weather the storm in the U.S....</td>\n",
       "      <td>Skip to main content Twitter LinkedIn Search T...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>fiercepharma</td>\n",
       "      <td>http://www.fiercepharma.com/pharma/another-exe...</td>\n",
       "      <td>news</td>\n",
       "      <td>Another exec departs troubled Endo--and this t...</td>\n",
       "      <td>Skip to main content Twitter LinkedIn Search T...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>fiercepharma</td>\n",
       "      <td>http://www.fiercepharma.com/pharma/teva-buy-bi...</td>\n",
       "      <td>news</td>\n",
       "      <td>Would Teva buy Korea's Celltrion to beef up in...</td>\n",
       "      <td>Skip to main content Twitter LinkedIn Search T...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>fiercepharma</td>\n",
       "      <td>http://www.fiercepharma.com/marketing/actress-...</td>\n",
       "      <td>news</td>\n",
       "      <td>Restasis-maker Allergan recruits actress Maris...</td>\n",
       "      <td>Skip to main content Twitter LinkedIn Search T...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Domain                                                Url  \\\n",
       "Webpage_id                                                                    \n",
       "1           fiercepharma  http://www.fiercepharma.com/marketing/tecfider...   \n",
       "2           fiercepharma  http://www.fiercepharma.com/pharma/novo-equipp...   \n",
       "3           fiercepharma  http://www.fiercepharma.com/pharma/another-exe...   \n",
       "4           fiercepharma  http://www.fiercepharma.com/pharma/teva-buy-bi...   \n",
       "5           fiercepharma  http://www.fiercepharma.com/marketing/actress-...   \n",
       "\n",
       "             Tag                                              Title  \\\n",
       "Webpage_id                                                            \n",
       "1           news  Tecfidera, Gilenya and Aubagio's 3-way battle ...   \n",
       "2           news  Novo equipped to weather the storm in the U.S....   \n",
       "3           news  Another exec departs troubled Endo--and this t...   \n",
       "4           news  Would Teva buy Korea's Celltrion to beef up in...   \n",
       "5           news  Restasis-maker Allergan recruits actress Maris...   \n",
       "\n",
       "                                                    Html2Text  \n",
       "Webpage_id                                                     \n",
       "1           Skip to main content Twitter LinkedIn Search T...  \n",
       "2           Skip to main content Twitter LinkedIn Search T...  \n",
       "3           Skip to main content Twitter LinkedIn Search T...  \n",
       "4           Skip to main content Twitter LinkedIn Search T...  \n",
       "5           Skip to main content Twitter LinkedIn Search T...  "
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# new df\n",
    "ndf = pd.merge(df,pdf,on='Webpage_id')\n",
    "print('Shape of new DF :', ndf.shape)\n",
    "ndf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "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>Domain</th>\n",
       "      <th>Url</th>\n",
       "      <th>Title</th>\n",
       "      <th>Html2Text</th>\n",
       "      <th>Tag</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Webpage_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>fiercepharma</td>\n",
       "      <td>http://www.fiercepharma.com/marketing/tecfider...</td>\n",
       "      <td>Tecfidera, Gilenya and Aubagio's 3-way battle ...</td>\n",
       "      <td>Skip to main content Twitter LinkedIn Search T...</td>\n",
       "      <td>news</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>fiercepharma</td>\n",
       "      <td>http://www.fiercepharma.com/pharma/novo-equipp...</td>\n",
       "      <td>Novo equipped to weather the storm in the U.S....</td>\n",
       "      <td>Skip to main content Twitter LinkedIn Search T...</td>\n",
       "      <td>news</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>fiercepharma</td>\n",
       "      <td>http://www.fiercepharma.com/pharma/another-exe...</td>\n",
       "      <td>Another exec departs troubled Endo--and this t...</td>\n",
       "      <td>Skip to main content Twitter LinkedIn Search T...</td>\n",
       "      <td>news</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>fiercepharma</td>\n",
       "      <td>http://www.fiercepharma.com/pharma/teva-buy-bi...</td>\n",
       "      <td>Would Teva buy Korea's Celltrion to beef up in...</td>\n",
       "      <td>Skip to main content Twitter LinkedIn Search T...</td>\n",
       "      <td>news</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>fiercepharma</td>\n",
       "      <td>http://www.fiercepharma.com/marketing/actress-...</td>\n",
       "      <td>Restasis-maker Allergan recruits actress Maris...</td>\n",
       "      <td>Skip to main content Twitter LinkedIn Search T...</td>\n",
       "      <td>news</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Domain                                                Url  \\\n",
       "Webpage_id                                                                    \n",
       "1           fiercepharma  http://www.fiercepharma.com/marketing/tecfider...   \n",
       "2           fiercepharma  http://www.fiercepharma.com/pharma/novo-equipp...   \n",
       "3           fiercepharma  http://www.fiercepharma.com/pharma/another-exe...   \n",
       "4           fiercepharma  http://www.fiercepharma.com/pharma/teva-buy-bi...   \n",
       "5           fiercepharma  http://www.fiercepharma.com/marketing/actress-...   \n",
       "\n",
       "                                                        Title  \\\n",
       "Webpage_id                                                      \n",
       "1           Tecfidera, Gilenya and Aubagio's 3-way battle ...   \n",
       "2           Novo equipped to weather the storm in the U.S....   \n",
       "3           Another exec departs troubled Endo--and this t...   \n",
       "4           Would Teva buy Korea's Celltrion to beef up in...   \n",
       "5           Restasis-maker Allergan recruits actress Maris...   \n",
       "\n",
       "                                                    Html2Text   Tag  \n",
       "Webpage_id                                                           \n",
       "1           Skip to main content Twitter LinkedIn Search T...  news  \n",
       "2           Skip to main content Twitter LinkedIn Search T...  news  \n",
       "3           Skip to main content Twitter LinkedIn Search T...  news  \n",
       "4           Skip to main content Twitter LinkedIn Search T...  news  \n",
       "5           Skip to main content Twitter LinkedIn Search T...  news  "
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Persist the Merged DataFrame (having additional columns - Title, Html2Text) to CSV\n",
    "ndf = ndf[['Domain', 'Url', 'Title', 'Html2Text', 'Tag']]\n",
    "ndf.to_csv('bigdata/train1.csv',encoding='utf-8')\n",
    "ndf.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Domain        0\n",
       "Url           0\n",
       "Title        99\n",
       "Html2Text     0\n",
       "Tag           0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ndf.isnull().sum() # Sanity check for null existence and we got  99 null value Titles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[10606 10630 10637 10641 10642 10644 10647 10651 10711 10712 10747 10881\n",
      " 10941 13126 13364 17847 22180 23707 28277 28278 28827 28828 28829 28830\n",
      " 28831 28832 28833 28871 28886 28890 29405 30412 30822 31165 31515 31519\n",
      " 40057 40849 52767 52780 52781 54462 54463 56238 56239 57686 57688 57692\n",
      " 57693 57696 57697 57703 65809 66069 66123 66139 66170 67007 70246 70248\n",
      " 70263 70264 70266 70267 70270 70832 73889 73946 73952 73955 73991 74030\n",
      " 74048 74070 74201 74212 74222 74229 74317 74340 74375 74382 74388 74402\n",
      " 74472 74485 74572 74577 74610 74631 74718 74869 74938 74978 77662 77757\n",
      " 77867 77912 77993]\n"
     ]
    }
   ],
   "source": [
    "null_title_ids = ndf[ndf.Title.isnull()].index.values\n",
    "print(null_title_ids)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "scrolled": false
   },
   "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>Url</th>\n",
       "      <th>Title</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Webpage_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10606</th>\n",
       "      <td>http://journals.sagepub.com/doi/pdf/10.1177/20...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10630</th>\n",
       "      <td>http://www.health.govt.nz/system/files/documen...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10637</th>\n",
       "      <td>http://www.health.govt.nz/system/files/documen...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10641</th>\n",
       "      <td>http://www.cieh.org/assets/0/72/1126/1212/1216...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10642</th>\n",
       "      <td>https://www.health.govt.nz/system/files/docume...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10644</th>\n",
       "      <td>http://www.health.govt.nz/system/files/documen...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10647</th>\n",
       "      <td>https://www.karger.com/Article/Pdf/367759</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10651</th>\n",
       "      <td>https://link.springer.com/content/pdf/10.1007%...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10711</th>\n",
       "      <td>http://files.abstractsonline.com/SUPT/101/4292...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10712</th>\n",
       "      <td>http://www.practicalradonc.org/article/S1879-8...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10747</th>\n",
       "      <td>http://www.europsy.net/wp-content/uploads/2016...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10881</th>\n",
       "      <td>https://www.ics.org/Publications/ICI_4/files-b...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10941</th>\n",
       "      <td>http://deutscher-schmerzkongress2014.de/wp-con...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13126</th>\n",
       "      <td>https://www.nice.org.uk/guidance/ng18/resource...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13364</th>\n",
       "      <td>https://derbyhospitals-nhs.archive.knowledgear...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17847</th>\n",
       "      <td>http://static.smallworldlabs.com/molmedcommuni...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22180</th>\n",
       "      <td>http://www.kardiologiapolska.pl/en/darmowy_pdf...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23707</th>\n",
       "      <td>http://www.kardiologiapolska.pl/en/darmowy_pdf...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28277</th>\n",
       "      <td>http://1.eyefortravel.com/LP=17841?extsource=p...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28278</th>\n",
       "      <td>http://1.eyefortravel.com/LP=3387</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28827</th>\n",
       "      <td>http://aim.bmj.com</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28828</th>\n",
       "      <td>http://aim.bmj.com/pages/about/</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28829</th>\n",
       "      <td>http://aim.bmj.com/pages/browse-by-topic/</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28830</th>\n",
       "      <td>http://aim.bmj.com/pages/most-read-articles/</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28831</th>\n",
       "      <td>http://aim.bmj.com/pages/authors/</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28832</th>\n",
       "      <td>http://aim.bmj.com/pages/editorial-board/</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28833</th>\n",
       "      <td>http://aim.bmj.com/pages/contact-us/</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28871</th>\n",
       "      <td>http://aim.bmj.com/pages/rss-feeds/</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28886</th>\n",
       "      <td>http://aim.bmj.com/pages/subscribe/</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28890</th>\n",
       "      <td>http://aim.bmj.com/pages/thank-you-to-our-revi...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73955</th>\n",
       "      <td>https://www.bioscience.org/fbs/access_denied.p...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73991</th>\n",
       "      <td>https://www.bioscience.org/fbs/access_denied.p...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74030</th>\n",
       "      <td>https://www.bioscience.org/fbs/access_denied.p...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74048</th>\n",
       "      <td>https://www.bioscience.org/fbs/access_denied.p...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74070</th>\n",
       "      <td>https://www.bioscience.org/fbs/access_denied.p...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74201</th>\n",
       "      <td>https://www.bioscience.org/fbs/access_denied.p...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74212</th>\n",
       "      <td>https://www.bioscience.org/fbs/access_denied.p...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74222</th>\n",
       "      <td>https://www.bioscience.org/fbs/access_denied.p...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74229</th>\n",
       "      <td>https://www.bioscience.org/fbs/access_denied.p...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74317</th>\n",
       "      <td>https://www.bioscience.org/fbs/access_denied.p...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74340</th>\n",
       "      <td>https://www.bioscience.org/fbs/access_denied.p...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74375</th>\n",
       "      <td>https://www.bioscience.org/fbs/access_denied.p...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74382</th>\n",
       "      <td>https://www.bioscience.org/fbs/access_denied.p...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74388</th>\n",
       "      <td>https://www.bioscience.org/fbs/access_denied.p...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74402</th>\n",
       "      <td>https://www.bioscience.org/fbs/access_denied.p...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74472</th>\n",
       "      <td>https://www.bioscience.org/fbs/access_denied.p...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74485</th>\n",
       "      <td>https://www.bioscience.org/fbs/access_denied.p...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74572</th>\n",
       "      <td>https://www.bioscience.org/fbs/access_denied.p...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74577</th>\n",
       "      <td>http://www.grupoaran.com/mrmUpdate/lecturaPDFf...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74610</th>\n",
       "      <td>http://www.grupoaran.com/mrmUpdate/lecturaPDFf...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74631</th>\n",
       "      <td>http://www.grupoaran.com/mrmUpdate/lecturaPDFf...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74718</th>\n",
       "      <td>https://www.bioscience.org/fbs/access_denied.p...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74869</th>\n",
       "      <td>https://www.bioscience.org/fbs/access_denied.p...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74938</th>\n",
       "      <td>https://www.bioscience.org/fbs/access_denied.p...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74978</th>\n",
       "      <td>http://www.grupoaran.com/mrmUpdate/lecturaPDFf...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77662</th>\n",
       "      <td>http://lakshadweep.nic.in/</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77757</th>\n",
       "      <td>http://manpowergroup.com/DataExplorer/</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77867</th>\n",
       "      <td>http://msb.embopress.org/sites/default/files/a...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77912</th>\n",
       "      <td>http://nymalize.com</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77993</th>\n",
       "      <td>http://feed2js.org//feed2js.php?au=y&amp;chan=y&amp;da...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                          Url Title\n",
       "Webpage_id                                                         \n",
       "10606       http://journals.sagepub.com/doi/pdf/10.1177/20...  None\n",
       "10630       http://www.health.govt.nz/system/files/documen...  None\n",
       "10637       http://www.health.govt.nz/system/files/documen...  None\n",
       "10641       http://www.cieh.org/assets/0/72/1126/1212/1216...  None\n",
       "10642       https://www.health.govt.nz/system/files/docume...  None\n",
       "10644       http://www.health.govt.nz/system/files/documen...  None\n",
       "10647               https://www.karger.com/Article/Pdf/367759  None\n",
       "10651       https://link.springer.com/content/pdf/10.1007%...  None\n",
       "10711       http://files.abstractsonline.com/SUPT/101/4292...  None\n",
       "10712       http://www.practicalradonc.org/article/S1879-8...  None\n",
       "10747       http://www.europsy.net/wp-content/uploads/2016...  None\n",
       "10881       https://www.ics.org/Publications/ICI_4/files-b...  None\n",
       "10941       http://deutscher-schmerzkongress2014.de/wp-con...  None\n",
       "13126       https://www.nice.org.uk/guidance/ng18/resource...  None\n",
       "13364       https://derbyhospitals-nhs.archive.knowledgear...  None\n",
       "17847       http://static.smallworldlabs.com/molmedcommuni...  None\n",
       "22180       http://www.kardiologiapolska.pl/en/darmowy_pdf...  None\n",
       "23707       http://www.kardiologiapolska.pl/en/darmowy_pdf...  None\n",
       "28277       http://1.eyefortravel.com/LP=17841?extsource=p...  None\n",
       "28278                       http://1.eyefortravel.com/LP=3387  None\n",
       "28827                                      http://aim.bmj.com  None\n",
       "28828                         http://aim.bmj.com/pages/about/  None\n",
       "28829               http://aim.bmj.com/pages/browse-by-topic/  None\n",
       "28830            http://aim.bmj.com/pages/most-read-articles/  None\n",
       "28831                       http://aim.bmj.com/pages/authors/  None\n",
       "28832               http://aim.bmj.com/pages/editorial-board/  None\n",
       "28833                    http://aim.bmj.com/pages/contact-us/  None\n",
       "28871                     http://aim.bmj.com/pages/rss-feeds/  None\n",
       "28886                     http://aim.bmj.com/pages/subscribe/  None\n",
       "28890       http://aim.bmj.com/pages/thank-you-to-our-revi...  None\n",
       "...                                                       ...   ...\n",
       "73955       https://www.bioscience.org/fbs/access_denied.p...  None\n",
       "73991       https://www.bioscience.org/fbs/access_denied.p...  None\n",
       "74030       https://www.bioscience.org/fbs/access_denied.p...  None\n",
       "74048       https://www.bioscience.org/fbs/access_denied.p...  None\n",
       "74070       https://www.bioscience.org/fbs/access_denied.p...  None\n",
       "74201       https://www.bioscience.org/fbs/access_denied.p...  None\n",
       "74212       https://www.bioscience.org/fbs/access_denied.p...  None\n",
       "74222       https://www.bioscience.org/fbs/access_denied.p...  None\n",
       "74229       https://www.bioscience.org/fbs/access_denied.p...  None\n",
       "74317       https://www.bioscience.org/fbs/access_denied.p...  None\n",
       "74340       https://www.bioscience.org/fbs/access_denied.p...  None\n",
       "74375       https://www.bioscience.org/fbs/access_denied.p...  None\n",
       "74382       https://www.bioscience.org/fbs/access_denied.p...  None\n",
       "74388       https://www.bioscience.org/fbs/access_denied.p...  None\n",
       "74402       https://www.bioscience.org/fbs/access_denied.p...  None\n",
       "74472       https://www.bioscience.org/fbs/access_denied.p...  None\n",
       "74485       https://www.bioscience.org/fbs/access_denied.p...  None\n",
       "74572       https://www.bioscience.org/fbs/access_denied.p...  None\n",
       "74577       http://www.grupoaran.com/mrmUpdate/lecturaPDFf...  None\n",
       "74610       http://www.grupoaran.com/mrmUpdate/lecturaPDFf...  None\n",
       "74631       http://www.grupoaran.com/mrmUpdate/lecturaPDFf...  None\n",
       "74718       https://www.bioscience.org/fbs/access_denied.p...  None\n",
       "74869       https://www.bioscience.org/fbs/access_denied.p...  None\n",
       "74938       https://www.bioscience.org/fbs/access_denied.p...  None\n",
       "74978       http://www.grupoaran.com/mrmUpdate/lecturaPDFf...  None\n",
       "77662                              http://lakshadweep.nic.in/  None\n",
       "77757                  http://manpowergroup.com/DataExplorer/  None\n",
       "77867       http://msb.embopress.org/sites/default/files/a...  None\n",
       "77912                                     http://nymalize.com  None\n",
       "77993       http://feed2js.org//feed2js.php?au=y&chan=y&da...  None\n",
       "\n",
       "[99 rows x 2 columns]"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_titles = ndf[ndf.Title.isnull()]\n",
    "null_titles[['Url','Title']]\n",
    "# Observation: URLs return 404 or access_dednied or a PDF doc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Domain       0\n",
       "Url          0\n",
       "Title        0\n",
       "Html2Text    0\n",
       "Tag          0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How to cleanse or fillna the Title column with None values???\n",
    "# Replace None in Title column with Empty strings. Later this helps in vectorizing this column\n",
    "# Change approach? I think these rows should have been purged/deleted. Because non-existant page add no business value, right?\n",
    "ndf.Title.fillna('',inplace=True)\n",
    "ndf.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Modeling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.model_selection import train_test_split, ShuffleSplit\n",
    "y = ndf.pop('Tag')\n",
    "x = ndf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "x.pop('Url')\n",
    "x_train, x_test, y_train, y_test = train_test_split(x,y,test_size=0.3,random_state=42,shuffle=True,stratify=y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(37412, 3572)\n",
      "(37412, 33830)\n"
     ]
    }
   ],
   "source": [
    "from sklearn.feature_extraction.text import TfidfVectorizer\n",
    "vectorizer_domain = TfidfVectorizer()\n",
    "vectorizer_title = TfidfVectorizer()\n",
    "\n",
    "vectors_domain = vectorizer_domain.fit_transform(x_train.Domain)\n",
    "print(vectors_domain.shape)\n",
    "\n",
    "vectors_title = vectorizer_title.fit_transform(x_train.Title)\n",
    "print(vectors_title.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>Domain</th>\n",
       "      <th>Title</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Webpage_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>49734</th>\n",
       "      <td>ca.gsk.com</td>\n",
       "      <td>GSK to discontinue manufacture and sale of the...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62253</th>\n",
       "      <td>www.msd.com</td>\n",
       "      <td>The Impact of Cancer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70502</th>\n",
       "      <td>www.detc.uk</td>\n",
       "      <td>News Archive - The Digital Engineering &amp; Test ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75620</th>\n",
       "      <td>www.health.com</td>\n",
       "      <td>Why Does Sugar Make You Thirsty? - Health</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78138</th>\n",
       "      <td>www.urmc.rochester.edu</td>\n",
       "      <td>Jefferson S. Svengsouk, M.D., M.B.A. -  Univer...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            Domain  \\\n",
       "Webpage_id                           \n",
       "49734                   ca.gsk.com   \n",
       "62253                  www.msd.com   \n",
       "70502                  www.detc.uk   \n",
       "75620               www.health.com   \n",
       "78138       www.urmc.rochester.edu   \n",
       "\n",
       "                                                        Title  \n",
       "Webpage_id                                                     \n",
       "49734       GSK to discontinue manufacture and sale of the...  \n",
       "62253                                    The Impact of Cancer  \n",
       "70502       News Archive - The Digital Engineering & Test ...  \n",
       "75620               Why Does Sugar Make You Thirsty? - Health  \n",
       "78138       Jefferson S. Svengsouk, M.D., M.B.A. -  Univer...  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x_train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.naive_bayes import MultinomialNB\n",
    "from sklearn import metrics\n",
    "\n",
    "vectors_domain_test = vectorizer_domain.transform(x_test.Domain)\n",
    "vectors_title_test = vectorizer_title.transform(x_test.Title)\n",
    "\n",
    "clf = MultinomialNB(alpha=.01)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.901020902082288"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clf.fit(vectors_domain, y_train)\n",
    "\n",
    "y_pred = clf.predict(vectors_domain_test)\n",
    "\n",
    "metrics.f1_score(y_test, y_pred, average='macro')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Modeling : Predicting Test Data Classification"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(25787, 2)\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>Domain</th>\n",
       "      <th>Url</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Webpage_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>isrctn.com</td>\n",
       "      <td>http://www.isrctn.com/ISRCTN57801413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>www.clinicaltrialsregister.eu</td>\n",
       "      <td>https://www.clinicaltrialsregister.eu/ctr-sear...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>www.clinicaltrialsregister.eu</td>\n",
       "      <td>https://www.clinicaltrialsregister.eu/ctr-sear...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>www.clinicaltrialsregister.eu</td>\n",
       "      <td>https://www.clinicaltrialsregister.eu/ctr-sear...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>www.clinicaltrialsregister.eu</td>\n",
       "      <td>https://www.clinicaltrialsregister.eu/ctr-sear...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   Domain  \\\n",
       "Webpage_id                                  \n",
       "31                             isrctn.com   \n",
       "32          www.clinicaltrialsregister.eu   \n",
       "33          www.clinicaltrialsregister.eu   \n",
       "34          www.clinicaltrialsregister.eu   \n",
       "35          www.clinicaltrialsregister.eu   \n",
       "\n",
       "                                                          Url  \n",
       "Webpage_id                                                     \n",
       "31                       http://www.isrctn.com/ISRCTN57801413  \n",
       "32          https://www.clinicaltrialsregister.eu/ctr-sear...  \n",
       "33          https://www.clinicaltrialsregister.eu/ctr-sear...  \n",
       "34          https://www.clinicaltrialsregister.eu/ctr-sear...  \n",
       "35          https://www.clinicaltrialsregister.eu/ctr-sear...  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "testdf = pd.read_csv('bigdata/test.csv', index_col='Webpage_id')\n",
    "# testdf.drop(columns=['Url'], inplace=True)\n",
    "print(testdf.shape)\n",
    "testdf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['clinicalTrials', 'thesis', 'thesis', ..., 'forum', 'forum',\n",
       "       'forum'], dtype='<U14')"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vectors_domain_testdf = vectorizer_domain.transform(testdf.Domain)\n",
    "y_pred_testdf = clf.predict(vectors_domain_testdf)\n",
    "y_pred_testdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {\n",
    "    'Webpage_id': testdf.index.values,\n",
    "    'Tag': y_pred_testdf\n",
    "}\n",
    "testdf_pred = pd.DataFrame(data)\n",
    "testdf_pred.set_index('Webpage_id', inplace=True)\n",
    "testdf_pred.to_csv('bigdata/test_pred_1.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ndf = ndf.reset_index()\n",
    "# indices = testdf.index.values\n",
    "# ndf.loc[ndf.Webpage_id.isin(indices)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(25787, 4)\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>Domain</th>\n",
       "      <th>Url</th>\n",
       "      <th>Title</th>\n",
       "      <th>Html</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Webpage_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>isrctn.com</td>\n",
       "      <td>http://www.isrctn.com/ISRCTN57801413</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>www.clinicaltrialsregister.eu</td>\n",
       "      <td>https://www.clinicaltrialsregister.eu/ctr-sear...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>www.clinicaltrialsregister.eu</td>\n",
       "      <td>https://www.clinicaltrialsregister.eu/ctr-sear...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   Domain  \\\n",
       "Webpage_id                                  \n",
       "31                             isrctn.com   \n",
       "32          www.clinicaltrialsregister.eu   \n",
       "33          www.clinicaltrialsregister.eu   \n",
       "\n",
       "                                                          Url Title Html  \n",
       "Webpage_id                                                                \n",
       "31                       http://www.isrctn.com/ISRCTN57801413             \n",
       "32          https://www.clinicaltrialsregister.eu/ctr-sear...             \n",
       "33          https://www.clinicaltrialsregister.eu/ctr-sear...             "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add 'Title' column to testdf, so that its values can be derived from the corresponding urls in the Domain column of each row\n",
    "testdf['Title'] = ''\n",
    "testdf['Html'] = ''\n",
    "# testdf = testdf.reset_index()\n",
    "# testdf = testdf.set_index('Webpage_id')\n",
    "# testdf\n",
    "# testdf.loc[testdf.Webpage_id == 31] #['Title'] = 'test'\n",
    "# testdf.at[31,'Title'] = ''\n",
    "print(testdf.shape)\n",
    "testdf.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "testdf.to_sql('testdf',db_engine, if_exists='replace') #Write to DB to save time/memory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# OBJECTIVE: Parse web-page to extract titles\n",
    "'''\n",
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "\n",
    "# urls = testdf.Url.values\n",
    "# print(urls.size) # 25787 tallies with test dataset\n",
    "# for idx,row in testdf.iterrows():\n",
    "#     print(idx,'\\n',row)\n",
    "#     break\n",
    "\n",
    "for idx,row in testdf.iterrows():\n",
    "#     page = requests.get(row.Url)\n",
    "    title = extract_title(row['Html'])\n",
    "    print(title)\n",
    "    if (title != None): \n",
    "        testdf.at[idx,'Title'] = title\n",
    "print('Done computing titles..')  \n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "testdf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "clf.fit(vectors_title, y_train)\n",
    "\n",
    "vectors_title_testdf = vectorizer_title.transform(testdf.Title)\n",
    "y_pred_testdf = clf.predict(vectors_title_testdf)\n",
    "data = {\n",
    "    'Webpage_id': testdf.index.values,\n",
    "    'Tag': y_pred_testdf\n",
    "}\n",
    "testdf_pred = pd.DataFrame(data)\n",
    "testdf_pred.set_index('Webpage_id', inplace=True)\n",
    "testdf_pred.to_csv('bigdata/test_pred_2.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}