Web Scraping.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from gazpacho import get, Soup\n",
    "URL =\"https://en.wikipedia.org/wiki/Taoiseach\"\n",
    "html = get(URL)\n",
    "soup = Soup(html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = soup.find(\"table\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for n, t in enumerate(tables):\n",
    "    if t.attrs['class'] == \"wikitable\":\n",
    "        nrows = t.find(\"tr\")\n",
    "        if isinstance(nrows, list):\n",
    "            print(f\"Table {n} has {len(nrows)} rows.\")\n",
    "        else:\n",
    "            print(f\"Table {n} is of type: {type(nrows)}.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "president_data = tables[2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rows = president_data.find(\"tr\")\n",
    "\n",
    "del(rows[11])\n",
    "del(rows[10])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# backup\n",
    "pot_rowspan = 1\n",
    "pot = \"\"\n",
    "name_rowspan = 1\n",
    "p_name = \"\"\n",
    "ofc_st_rowspan = 1\n",
    "p_ofc_st = \"\"\n",
    "ofc_end_rowspan = 1\n",
    "p_ofc_end = \"\"\n",
    "party_rowspan = 1\n",
    "p_party = \"\"\n",
    "ex_num_rowspan = 1\n",
    "p_ex_num = \"\"\n",
    "ex_type_rowspan = 1\n",
    "p_ex_type = \"\"\n",
    "vp_rowspan = 1\n",
    "p_vp = \"\"\n",
    "dail_rowspan = 1\n",
    "p_dail = \"\"\n",
    "longbar_rowspan = 1\n",
    "p_longbar = \"\"\n",
    "\n",
    "taoiseach_details = []\n",
    "\n",
    "for row in rows[2:]:\n",
    "    r = row.find('td')\n",
    "    if pot_rowspan == 1 and name_rowspan == 1 and ofc_st_rowspan == 1 and ofc_end_rowspan == 1 and party_rowspan == 1 and ex_num_rowspan == 1 and ex_type_rowspan == 1 and vp_rowspan == 1 and dail_rowspan == 1 and longbar_rowspan == 1:\n",
    " \n",
    "        #print(\"tt\")\n",
    "        pot_img = r[0].text\n",
    "        if \"rowspan\" in r[0].attrs:\n",
    "            pot_rowspan = int(r[0].attrs[\"rowspan\"])\n",
    "            pot = pot_img\n",
    "            \n",
    "        presi_name = r[1].text\n",
    "        if \"rowspan\" in r[1].attrs:\n",
    "            name_rowspan = int(r[1].attrs[\"rowspan\"])\n",
    "            p_name = presi_name\n",
    "            \n",
    "        ofc_start = r[2].text\n",
    "        if \"rowspan\" in r[2].attrs:\n",
    "            ofc_st_rowspan = int(r[2].attrs[\"rowspan\"])\n",
    "            p_ofc_st = ofc_start\n",
    "            \n",
    "        ofc_end = r[3].text\n",
    "        if \"rowspan\" in r[3].attrs:\n",
    "            ofc_end_rowspan = int(r[3].attrs[\"rowspan\"])\n",
    "            p_ofc_end = ofc_end\n",
    "            \n",
    "        party = r[4].text\n",
    "        if \"rowspan\" in r[4].attrs:\n",
    "            party_rowspan = int(r[4].attrs[\"rowspan\"])\n",
    "            p_party = party\n",
    "            \n",
    "        ex_num = r[5].text\n",
    "        if \"rowspan\" in r[5].attrs:\n",
    "            ex_num_rowspan = int(r[5].attrs[\"rowspan\"])\n",
    "            p_ex_num = ex_num\n",
    "            \n",
    "        ex_type = r[6].text\n",
    "        if \"rowspan\" in r[6].attrs:\n",
    "            ex_type_rowspan = int(r[6].attrs[\"rowspan\"])\n",
    "            p_ex_type = ex_type\n",
    "        \n",
    "        longbar = r[7].text\n",
    "        if 'rowspan' in r[7].attrs:\n",
    "            longbar_rowspan = int(r[7].attrs[\"rowspan\"])\n",
    "            p_longbar = longbar\n",
    "            \n",
    "        vp = r[8].text\n",
    "        if 'rowspan' in r[8].attrs:\n",
    "            vp_rowspan = int(r[8].attrs[\"rowspan\"])\n",
    "            p_vp = vp\n",
    "            \n",
    "        dail = r[9].text\n",
    "        if \"rowspan\" in r[9].attrs:\n",
    "            dail_rowspan = int(r[9].attrs[\"rowspan\"])\n",
    "            p_dail = dail\n",
    "\n",
    "    elif pot_rowspan > 1 and name_rowspan > 1 and ofc_st_rowspan > 1 and ofc_end_rowspan > 1 and party_rowspan == 1 and ex_num_rowspan == 1 and ex_type_rowspan == 1 and vp_rowspan > 1 and dail_rowspan == 1 and longbar_rowspan > 1:\n",
    "        pot_rowspan -= 1\n",
    "        name_rowspan -= 1\n",
    "        ofc_st_rowspan-= 1\n",
    "        ofc_end_rowspan -= 1\n",
    "        vp_rowspan -= 1\n",
    "        longbar_rowspan -= 1\n",
    "        \n",
    "        #print(\"tt2\")\n",
    "        party = r[0].text\n",
    "        if \"rowspan\" in r[0].attrs:\n",
    "            party_rowspan = int(r[0].attrs[\"rowspan\"])\n",
    "            p_party = party\n",
    "        \n",
    "        ex_num = r[1].text\n",
    "        if \"rowspan\" in r[1].attrs:\n",
    "            ex_num_rowspan = int(r[1].attrs[\"rowspan\"])\n",
    "            p_ex_num = ex_num\n",
    "            \n",
    "        ex_type = r[2].text\n",
    "        if \"rowspan\" in r[2].attrs:\n",
    "            ex_type_rowspan = int(r[2].attrs[\"rowspan\"])\n",
    "            p_ex_type = ex_type\n",
    "        \n",
    "        #longbar = r[7].text\n",
    "            \n",
    "        dail = r[3].text\n",
    "        if \"rowspan\" in r[3].attrs:\n",
    "            dail_rowspan = int(r[3].attrs[\"rowspan\"])\n",
    "            p_dail = dail\n",
    "            \n",
    "    elif pot_rowspan > 1 and name_rowspan > 1 and ofc_st_rowspan > 1 and ofc_end_rowspan > 1 and party_rowspan > 1 and ex_num_rowspan == 1 and ex_type_rowspan > 1 and vp_rowspan == 1 and dail_rowspan == 1 and longbar_rowspan > 1:\n",
    "        pot_rowspan -= 1\n",
    "        name_rowspan -= 1\n",
    "        ofc_st_rowspan-= 1\n",
    "        ofc_end_rowspan -= 1\n",
    "        party_rowspan -= 1\n",
    "        ex_type_rowspan -= 1\n",
    "        longbar_rowspan -= 1\n",
    "        \n",
    "        #print(\"tt3\")\n",
    "        ex_num = r[0].text\n",
    "        if \"rowspan\" in r[0].attrs:\n",
    "            ex_num_rowspan = int(r[0].attrs[\"rowspan\"])\n",
    "            p_ex_num = ex_num\n",
    "        \n",
    "        vp = r[1].text\n",
    "        if 'rowspan' in r[1].attrs:\n",
    "            vp_rowspan = int(r[1].attrs[\"rowspan\"])\n",
    "            p_vp = vp\n",
    "            \n",
    "        dail = r[2].text\n",
    "        if \"rowspan\" in r[2].attrs:\n",
    "            dail_rowspan = int(r[2].attrs[\"rowspan\"])\n",
    "            p_dail = dail\n",
    "    \n",
    "    elif pot_rowspan > 1 and name_rowspan > 1 and ofc_st_rowspan > 1 and ofc_end_rowspan > 1 and party_rowspan > 1 and ex_num_rowspan == 1 and ex_type_rowspan > 1 and vp_rowspan > 1 and dail_rowspan == 1 and longbar_rowspan > 1:\n",
    "        pot_rowspan -= 1\n",
    "        name_rowspan -= 1\n",
    "        ofc_st_rowspan-= 1\n",
    "        ofc_end_rowspan -= 1\n",
    "        party_rowspan -= 1\n",
    "        ex_type_rowspan -= 1\n",
    "        vp_rowspan -= 1\n",
    "        longbar_rowspan -= 1\n",
    "\n",
    "        ex_num = r[0].text\n",
    "        if \"rowspan\" in r[0].attrs:\n",
    "            ex_num_rowspan = int(r[0].attrs[\"rowspan\"])\n",
    "            p_ex_num = ex_num\n",
    "        \n",
    "        dail = r[1].text\n",
    "        if \"rowspan\" in r[1].attrs:\n",
    "            dail_rowspan = int(r[1].attrs[\"rowspan\"])\n",
    "            p_dail = dail\n",
    "            \n",
    "    elif pot_rowspan > 1 and name_rowspan > 1 and ofc_st_rowspan > 1 and ofc_end_rowspan > 1 and party_rowspan > 1 and ex_num_rowspan == 1 and ex_type_rowspan > 1 and vp_rowspan > 1 and dail_rowspan > 1 and longbar_rowspan > 1:\n",
    "        pot_rowspan -= 1\n",
    "        name_rowspan -= 1\n",
    "        ofc_st_rowspan-= 1\n",
    "        ofc_end_rowspan -= 1\n",
    "        party_rowspan -= 1\n",
    "        ex_type_rowspan -= 1\n",
    "        vp_rowspan -= 1\n",
    "        dail_rowspan -= 1\n",
    "        longbar_rowspan -= 1\n",
    "\n",
    "        try:\n",
    "            ex_num = r[0].text\n",
    "            if \"rowspan\" in r[0].attrs:\n",
    "                ex_num_rowspan = int(r[0].attrs[\"rowspan\"])\n",
    "                p_ex_num = ex_num\n",
    "        except:\n",
    "            ex_num = r.text\n",
    "    \n",
    "    elif pot_rowspan > 1 and name_rowspan > 1 and ofc_st_rowspan > 1 and ofc_end_rowspan > 1 and party_rowspan > 1 and ex_num_rowspan == 1 and ex_type_rowspan == 1 and vp_rowspan > 1 and dail_rowspan == 1 and longbar_rowspan >1:\n",
    "        pot_rowspan -= 1\n",
    "        name_rowspan -= 1\n",
    "        ofc_st_rowspan-= 1\n",
    "        ofc_end_rowspan -= 1\n",
    "        party_rowspan -= 1\n",
    "        vp_rowspan -= 1\n",
    "        longbar_rowspan -= 1\n",
    "        \n",
    "        ex_num = r[0].text\n",
    "        if \"rowspan\" in r[0].attrs:\n",
    "            ex_num_rowspan = int(r[0].attrs[\"rowspan\"])\n",
    "            p_ex_num = ex_num\n",
    "            \n",
    "        ex_type = r[1].text\n",
    "        if \"rowspan\" in r[1].attrs:\n",
    "            ex_type_rowspan = int(r[1].attrs[\"rowspan\"])\n",
    "            p_ex_type = ex_type\n",
    "        \n",
    "        dail = r[2].text\n",
    "        if \"rowspan\" in r[2].attrs:\n",
    "            dail_rowspan = int(r[2].attrs[\"rowspan\"])\n",
    "            p_dail = dail\n",
    "            \n",
    "    elif pot_rowspan > 1 and name_rowspan > 1 and ofc_st_rowspan > 1 and ofc_end_rowspan > 1 and party_rowspan > 1 and ex_num_rowspan == 1 and ex_type_rowspan == 1 and vp_rowspan == 1 and dail_rowspan == 1 and longbar_rowspan == 1:\n",
    "        pot_rowspan -= 1\n",
    "        name_rowspan -= 1\n",
    "        ofc_st_rowspan-= 1\n",
    "        ofc_end_rowspan -= 1\n",
    "        party_rowspan -= 1\n",
    "    \n",
    "        ex_num = r[0].text\n",
    "        if \"rowspan\" in r[0].attrs:\n",
    "            ex_num_rowspan = int(r[0].attrs[\"rowspan\"])\n",
    "            p_ex_num = ex_num\n",
    "\n",
    "        ex_type = r[1].text\n",
    "        if \"rowspan\" in r[1].attrs:\n",
    "            ex_type_rowspan = int(r[1].attrs[\"rowspan\"])\n",
    "            p_ex_type = ex_type\n",
    "        \n",
    "        longbar = r[2].text\n",
    "        if \"rowspan\" in r[2].attrs:\n",
    "            longbar_rowspan = int(r[2].attrs[\"rowspan\"])\n",
    "            p_longbar = longbar\n",
    "            \n",
    "        vp = r[3].text\n",
    "        if 'rowspan' in r[3].attrs:\n",
    "            vp_rowspan = int(r[3].attrs[\"rowspan\"])\n",
    "            p_vp = vp\n",
    "            \n",
    "        dail = r[4].text\n",
    "        if \"rowspan\" in r[4].attrs:\n",
    "            dail_rowspan = int(r[4].attrs[\"rowspan\"])\n",
    "            p_dail = dail\n",
    "    \n",
    "    elif pot_rowspan == 1 and name_rowspan == 1 and ofc_st_rowspan == 1 and ofc_end_rowspan == 1 and party_rowspan == 1 and ex_num_rowspan == 1 and ex_type_rowspan == 1 and vp_rowspan == 1 and dail_rowspan > 1 and longbar_rowspan == 1:\n",
    "        dail_rowspan -= 1\n",
    "        \n",
    "        pot_img = r[0]\n",
    "        if \"rowspan\" in r[0].attrs:\n",
    "            pot_rowspan = int(r[0].attrs[\"rowspan\"])\n",
    "            pot = pot_img\n",
    "            \n",
    "        presi_name = r[1].text\n",
    "        if \"rowspan\" in r[1].attrs:\n",
    "            name_rowspan = int(r[1].attrs[\"rowspan\"])\n",
    "            p_name = presi_name\n",
    "            \n",
    "        ofc_start = r[2].text\n",
    "        if \"rowspan\" in r[2].attrs:\n",
    "            ofc_st_rowspan = int(r[2].attrs[\"rowspan\"])\n",
    "            p_ofc_st = ofc_start\n",
    "            \n",
    "        ofc_end = r[3].text\n",
    "        if \"rowspan\" in r[3].attrs:\n",
    "            ofc_end_rowspan = int(r[3].attrs[\"rowspan\"])\n",
    "            p_ofc_end = ofc_end\n",
    "            \n",
    "        party = r[4].text\n",
    "        if \"rowspan\" in r[4].attrs:\n",
    "            party_rowspan = int(r[4].attrs[\"rowspan\"])\n",
    "            p_party = party\n",
    "            \n",
    "        ex_num = r[5].text\n",
    "        if \"rowspan\" in r[5].attrs:\n",
    "            ex_num_rowspan = int(r[5].attrs[\"rowspan\"])\n",
    "            p_ex_num = ex_num\n",
    "            \n",
    "        ex_type = r[6].text\n",
    "        if \"rowspan\" in r[6].attrs:\n",
    "            ex_type_rowspan = int(r[6].attrs[\"rowspan\"])\n",
    "            p_ex_type = ex_type\n",
    "        \n",
    "        longbar = r[7].text\n",
    "        if 'rowspan' in r[7].attrs:\n",
    "            longbar_rowspan = int(r[7].attrs[\"rowspan\"])\n",
    "            p_longbar = longbar\n",
    "        \n",
    "        vp = r[8].text\n",
    "        if 'rowspan' in r[8].attrs:\n",
    "            vp_rowspan = int(r[8].attrs[\"rowspan\"])\n",
    "            p_vp = vp\n",
    "        \n",
    "    elif pot_rowspan > 1 and name_rowspan > 1 and ofc_st_rowspan > 1 and ofc_end_rowspan > 1 and party_rowspan > 1 and ex_num_rowspan == 1 and ex_type_rowspan == 1 and vp_rowspan == 1 and dail_rowspan == 1 and longbar_rowspan > 1:\n",
    "        pot_rowspan -= 1\n",
    "        name_rowspan -= 1\n",
    "        ofc_st_rowspan-= 1\n",
    "        ofc_end_rowspan -= 1\n",
    "        party_rowspan -= 1\n",
    "        longbar_rowspan -= 1\n",
    "    \n",
    "        ex_num = r[0].text\n",
    "        if \"rowspan\" in r[0].attrs:\n",
    "            ex_num_rowspan = int(r[0].attrs[\"rowspan\"])\n",
    "            p_ex_num = ex_num\n",
    "\n",
    "        ex_type = r[1].text\n",
    "        if \"rowspan\" in r[1].attrs:\n",
    "            ex_type_rowspan = int(r[1].attrs[\"rowspan\"])\n",
    "            p_ex_type = ex_type\n",
    "            \n",
    "        vp = r[2].text\n",
    "        if 'rowspan' in r[2].attrs:\n",
    "            vp_rowspan = int(r[2].attrs[\"rowspan\"])\n",
    "            p_vp = vp\n",
    "            \n",
    "        dail = r[3].text\n",
    "        if \"rowspan\" in r[3].attrs:\n",
    "            dail_rowspan = int(r[3].attrs[\"rowspan\"])\n",
    "            p_dail = dail\n",
    "            \n",
    "    elif pot_rowspan == 1 and name_rowspan == 1 and ofc_st_rowspan == 1 and ofc_end_rowspan == 1 and party_rowspan == 1 and ex_num_rowspan == 1 and ex_type_rowspan == 1 and vp_rowspan > 1 and dail_rowspan > 1 and longbar_rowspan > 1:\n",
    "        dail_rowspan -= 1\n",
    "        vp_rowspan -= 1\n",
    "        longbar_rowspan -= 1\n",
    "        \n",
    "        pot_img = r[0]\n",
    "        if \"rowspan\" in r[0].attrs:\n",
    "            pot_rowspan = int(r[0].attrs[\"rowspan\"])\n",
    "            pot = pot_img\n",
    "            \n",
    "        presi_name = r[1].text\n",
    "        if \"rowspan\" in r[1].attrs:\n",
    "            name_rowspan = int(r[1].attrs[\"rowspan\"])\n",
    "            p_name = presi_name\n",
    "            \n",
    "        ofc_start = r[2].text\n",
    "        if \"rowspan\" in r[2].attrs:\n",
    "            ofc_st_rowspan = int(r[2].attrs[\"rowspan\"])\n",
    "            p_ofc_st = ofc_start\n",
    "            \n",
    "        ofc_end = r[3].text\n",
    "        if \"rowspan\" in r[3].attrs:\n",
    "            ofc_end_rowspan = int(r[3].attrs[\"rowspan\"])\n",
    "            p_ofc_end = ofc_end\n",
    "            \n",
    "        party = r[4].text\n",
    "        if \"rowspan\" in r[4].attrs:\n",
    "            party_rowspan = int(r[4].attrs[\"rowspan\"])\n",
    "            p_party = party\n",
    "            \n",
    "        ex_num = r[5].text\n",
    "        if \"rowspan\" in r[5].attrs:\n",
    "            ex_num_rowspan = int(r[5].attrs[\"rowspan\"])\n",
    "            p_ex_num = ex_num\n",
    "            \n",
    "        ex_type = r[6].text\n",
    "        if \"rowspan\" in r[6].attrs:\n",
    "            ex_type_rowspan = int(r[6].attrs[\"rowspan\"])\n",
    "            p_ex_type = ex_type\n",
    "        \n",
    "    elif pot_rowspan > 1 and name_rowspan > 1 and ofc_st_rowspan > 1 and ofc_end_rowspan > 1 and party_rowspan > 1 and ex_num_rowspan > 1 and ex_type_rowspan > 1 and vp_rowspan == 1 and dail_rowspan > 1 and longbar_rowspan == 1:\n",
    "        pot_rowspan -= 1\n",
    "        name_rowspan -= 1\n",
    "        ofc_st_rowspan-= 1\n",
    "        ofc_end_rowspan -= 1\n",
    "        party_rowspan -= 1\n",
    "        ex_type_rowspan -= 1\n",
    "        ex_num_rowspan -= 1\n",
    "        dail_rowspan -= 1\n",
    "        \n",
    "        longbar = r[0].text\n",
    "        if 'rowspan' in r[0].attrs:\n",
    "            longbar_rowspan = int(r[0].attrs[\"rowspan\"])\n",
    "            p_longbar = longbar\n",
    "            \n",
    "        vp = r[1].text\n",
    "        if 'rowspan' in r[1].attrs:\n",
    "            vp_rowspan = int(r[1].attrs[\"rowspan\"])\n",
    "            p_vp = vp\n",
    "    \n",
    "    elif pot_rowspan > 1 and name_rowspan > 1 and ofc_st_rowspan > 1 and ofc_end_rowspan > 1 and party_rowspan > 1 and ex_num_rowspan > 1 and ex_type_rowspan > 1 and vp_rowspan == 1 and dail_rowspan > 1 and longbar_rowspan > 1:\n",
    "        pot_rowspan -= 1\n",
    "        name_rowspan -= 1\n",
    "        ofc_st_rowspan-= 1\n",
    "        ofc_end_rowspan -= 1\n",
    "        party_rowspan -= 1\n",
    "        ex_type_rowspan -= 1\n",
    "        dail_rowspan -= 1\n",
    "        longbar_rowspan -= 1\n",
    "        ex_num_rowspan -= 1\n",
    "\n",
    "        #print(\"tt12\")\n",
    "        try:\n",
    "            vp = r[0].text\n",
    "            if \"rowspan\" in r[0].attrs:\n",
    "                vp_rowspan = int(r[0].attrs[\"rowspan\"])\n",
    "                p_vp = vp\n",
    "        except:\n",
    "            vp = r.text\n",
    "        \n",
    "    taoiseach_details.append(\n",
    "        (\n",
    "            presi_name.strip(),\n",
    "            ofc_start.strip(),\n",
    "            ofc_end.strip(),\n",
    "            party.strip(),\n",
    "            ex_num.strip(),\n",
    "            ex_type.strip(), \n",
    "            vp.strip(),\n",
    "            int(dail.strip()),\n",
    "        )\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### I got data from wiki table but for all the columns after '*br*' tag ,data is not getting retrieved when using 'gazpacho'. After a lot of struggle I gave up and found another method using pandas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "taoiseach_details"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Started again with new method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "URL = 'https://en.wikipedia.org/wiki/Taoiseach'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_data = pd.read_html(URL)[3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop 1st and 2nd row(row index 0 and 1)\n",
    "ts_data = ts_data.drop([0,1,10,11])\n",
    "\n",
    "#drop 1st and 2nd columns(column index 0 and 1)\n",
    "ts_data = ts_data.drop([0,1,8], axis = 'columns')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "headings = {2:'Name',3:'Term Start',4:'Term End',5:'party',6:'Ex_Num',7:'Government',9:'Vice President',10:'Dail'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "headings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_data = ts_data.rename(columns = headings)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#slice their birth and death then add to our df\n",
    "ts_data[\"Birth and Death\"] = ts_data[\"Name\"].str.split(\"(\",expand = True)[1].str.split(\")\",expand = True)[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#slice their constituency and add to our df\n",
    "ts_data[\"Constituency\"] = ts_data[\"Name\"].str.split(\"(\",expand = True)[1].str.split(\")\",expand = True)[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#fixing their names\n",
    "ts_data[\"Name\"] = ts_data[\"Name\"].str.split(\"(\",expand = True)[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# fix Constituency data\n",
    "ts_data[\"Constituency\"] = ts_data[\"Constituency\"].str.replace(\"TD for\",\"\").str.replace(\"until\",\"\").str.replace(\"from 1927\",\"\").str.strip().str.replace(\"from 1969\",\"\").str.strip().str.replace(\" 1927\",\",\").str.replace(\" 1969\",\",\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# fix term date and start date\n",
    "ts_data[\"Term Start\"] = ts_data[\"Term Start\"].str.split(\"[\",expand = True)[0]\n",
    "ts_data[\"Term End\"] = ts_data[\"Term End\"].str.split(\"[\",expand = True)[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# formatting term date and start date\n",
    "ts_data[\"Term Start\"] = pd.to_datetime(ts_data[\"Term Start\"], errors=\"coerce\")\n",
    "ts_data[\"Term End\"] = pd.to_datetime(ts_data[\"Term End\"], errors=\"coerce\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# fixing Dail\n",
    "ts_data[\"Dail\"] = ts_data[\"Dail\"].str.split(\"(\",expand = True)[0]\n",
    "ts_data[\"Dail\"] = pd.to_numeric(ts_data[\"Dail\"], errors=\"coerce\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Data_list = [tuple(x) for x in ts_data.values]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Data_list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Pre-requisites:__\n",
    "\n",
    "Create database taoiseachDB; <br>\n",
    "grant all on taoiseachDB.* to 'usergod' identified by 'man';<br>\n",
    "flush privileges;<br>\n",
    "quit;<br>\n",
    "<br>\n",
    "__login:__ sudo mysql -u usergod -p taoiseachDB <br>\n",
    "__password:__ man<br>\n",
    "<br>\n",
    "create table ts_data(<br>\n",
    "Sno int auto_increment primary key,<br>\n",
    "Name varchar(100),<br>\n",
    "term_start date,<br>\n",
    "term_end date,<br>\n",
    "party varchar(100),<br>\n",
    "ex_num varchar(5),<br>\n",
    "government varchar(500),<br>\n",
    "vice_president varchar(100),<br>\n",
    "dail int,<br>\n",
    "birth_death varchar(100),<br>\n",
    "Constituency varchar(100));<br>\n",
    "<br>\n",
    "<br>\n",
    "create table ts_births(<br>\n",
    "president_name varchar(100),<br>\n",
    "birth_date date);<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import DBcm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "config = {\n",
    "    'user': 'usergod',\n",
    "    'password': 'man',\n",
    "    'host': 'localhost',\n",
    "    'database': 'taoiseachDB',\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with DBcm.UseDatabase(config) as cursor:\n",
    "    SQL = \"insert into ts_data (Name, term_start, term_end, party, ex_num, government, vice_president, dail, birth_death, Constituency) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)\"\n",
    "    cursor.executemany(SQL, Data_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (a) Provide a list of constituencies in Ireland which have produced a Taoiseach."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_data[\"Constituency\"].unique().tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (b) Which political party has produced the most individual Taoiseachs?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with DBcm.UseDatabase(config) as cursor:\n",
    "    SQL = \"select a.party ,count(a.party) val from (select distinct name,party from ts_data) a group by a.party order by val desc limit 1\"\n",
    "    cursor.execute(SQL)\n",
    "    output_max_con = cursor.fetchall()\n",
    "output_max_con"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (c) Provide the list of names for those politicians who previously held the position of “Vice President” or “Tánaiste” prior to becoming Taoiseach."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with DBcm.UseDatabase(config) as cursor:\n",
    "    SQL = \"select distinct name from (select  name, term_start from ts_data) president,(select  vice_president, term_start from ts_data) vp where president.name = vp.vice_president and president.term_start > vp.term_start;\"\n",
    "    cursor.execute(SQL)\n",
    "    output_ts_vp_serve = cursor.fetchall()\n",
    "output_ts_vp_serve"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# funtion to convert number of days to years,months and days.\n",
    "\n",
    "def days_to_str(entry):\n",
    "    daysLeft = entry[1]# deadlineDate - currentDate\n",
    "    #print(daysLeft)\n",
    "    name = entry[0]\n",
    "    years = ((daysLeft)/(365.25))\n",
    "    yearsInt=int(years)\n",
    "\n",
    "    months=(years-yearsInt)*12\n",
    "    monthsInt=int(months)\n",
    "\n",
    "    days=(months-monthsInt)*(365.25/12)\n",
    "    daysInt=int(days)\n",
    "\n",
    "    #print(str(yearsInt)+\" \"+str(monthsInt)+\" \"+str(daysInt))\n",
    "    return name+\": \"+str(yearsInt)+\" years \"+str(monthsInt)+\" months \"+str(daysInt)+\" Days\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (d) Which political party held the office of Taoiseach for the longest amount of overall time, and for how long (in years, months, and days)?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with DBcm.UseDatabase(config) as cursor:\n",
    "    SQL = \"select a.party,cast(sum(a.day) as int) days from (select distinct party, term_start,ifnull(term_end,curdate()),TIMESTAMPDIFF(DAY,term_start,ifnull(term_end,curdate())) day from ts_data) a group by a.party order by days desc limit 1;\"\n",
    "    cursor.execute(SQL)\n",
    "    output_longtime_ts = cursor.fetchall()\n",
    "days_to_str(output_longtime_ts[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (e) Which individual politician held the office of Taoiseach for the longest amount of uninterrupted time, and for how long (in years, months, and days)?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with DBcm.UseDatabase(config) as cursor:\n",
    "    SQL = \"select a.name,TIMESTAMPDIFF(day,b.term_start,a.term_end) days from (select distinct name,term_start,ifnull(term_end,curdate()) term_end from ts_data) a,(select distinct name,term_start,ifnull(term_end,curdate()) term_end from ts_data) b where  a.term_start = b.term_end and a.name = b.name\"\n",
    "    cursor.execute(SQL)\n",
    "    output_uninterupted_ts = cursor.fetchall()\n",
    "days_to_str(output_uninterupted_ts[0])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Hi Paul, you remember that i showed you that my query was returning -ve values in Maria DB and in MySql(i created csv using pandas and manually loaded in my local machine) it was working fine.<br>\n",
    "I found that it was because for Leo Varadkar term_end is *'Incumbent'* hence it is converted to __Nat__ and this Nat value while inserting into DB it is getting inserted as __'0001-01-01'__, because of this Leo was getting -ve years.<br>\n",
    "I have fixed this in sql while retrieving data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (f) Which individual politician held the office of Taoiseach for the shortest amount of time, and for how long (in years, months, and days)?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with DBcm.UseDatabase(config) as cursor:\n",
    "    SQL = \"select a.name,cast(sum(a.day)as int) days from (select distinct name, term_start,timestampDIFF(day,term_start,ifnull(if(term_end = '0001-01-01',null,term_end),curdate())) day from ts_data) a group by a.name order by days limit 1\"\n",
    "    cursor.execute(SQL)\n",
    "    output_short_ts = cursor.fetchall()\n",
    "days_to_str(output_short_ts[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (g) Which political party has held the office of Taoiseach for the largest number of Dáils?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with DBcm.UseDatabase(config) as cursor:\n",
    "    SQL = \"select party,count(a.party) from (select distinct party, dail from ts_data) a group by 1 order by 2 desc limit 1\"\n",
    "    cursor.execute(SQL)\n",
    "    output_more_party_dail = cursor.fetchall()\n",
    "output_more_party_dail"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (h) Which individual politician has held the office of Taoiseach for the most number of Dáils?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with DBcm.UseDatabase(config) as cursor:\n",
    "    SQL = \"select name,count(a.name) from (select distinct name, dail from ts_data) a group by 1 order by 2 desc limit 1\"\n",
    "    cursor.execute(SQL)\n",
    "    output_more_ts_dail = cursor.fetchall()\n",
    "output_more_ts_dail"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_names = list(ts_data[\"Name\"].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Code to Scrape date of birth of each Taoiseach"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from bs4 import BeautifulSoup\n",
    "import urllib.request as ur\n",
    "page = ur.urlopen(URL)\n",
    "soup = BeautifulSoup(page)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = soup.find_all('table')\n",
    "president_data = tables[3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "links = president_data.findAll('a',title = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "links"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "links_with_text = [a['href'] for a in president_data.findAll('a', href=True) if a.text in ts_names]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "links_with = [a.text for a in president_data.findAll('a', href=True) if a.text in ts_names]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "links_with"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "president_data.findAll('a', href=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "links_with_text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x = 0\n",
    "ts_births = []\n",
    "for i in links_with_text:\n",
    "    surl = 'https://en.wikipedia.org'+i\n",
    "    print(surl)\n",
    "    page = ur.urlopen(surl)\n",
    "    print(page)\n",
    "    ssoup = BeautifulSoup(page,'lxml')\n",
    "    table = ssoup.find('table',attrs={'class':'infobox vcard'})\n",
    "    for item in table('span',attrs = {'class':'bday'}):\n",
    "        Born_date = item.text\n",
    "        nam = links_with[x]\n",
    "        x+=1\n",
    "       # print(nam)\n",
    "       # print(Born_date)\n",
    "        ts_births.append([\n",
    "            nam.strip(),\n",
    "            Born_date\n",
    "        ]\n",
    "        )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_births"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_ts_births = [list(x) for x in set(tuple(x) for x in ts_births)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_ts_births"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with DBcm.UseDatabase(config) as cursor:\n",
    "    SQL2 = \"insert into ts_births(president_name, birth_date) values (%s, %s)\"\n",
    "    cursor.executemany(SQL2, unique_ts_births)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### What age was each Taoiseach on the day they assumed office?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with DBcm.UseDatabase(config) as cursor:\n",
    "    SQL = \"select distinct tsd.name,timestampdiff(year,tsb.birth_date,min(tsd.term_start)) years from ts_data tsd,ts_births tsb where tsd.name = tsb.president_name group by tsd.name\"\n",
    "    cursor.execute(SQL)\n",
    "    output_ts_age_ofc = cursor.fetchall()\n",
    "output_ts_age_ofc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (j) On the last day of their term of office, which politician was the oldest?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Assumption: I am considering current date as Leo Varadkar's Term end for my calculation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with DBcm.UseDatabase(config) as cursor:\n",
    "    SQL = \"select distinct tsd.name,timestampdiff(year,tsb.birth_date,max(ifnull(if(tsd.term_end = '0001-01-01',null,term_end),curdate()))) years from ts_data tsd,ts_births tsb where tsd.name = tsb.president_name group by tsd.name order by 2 desc limit 1\"\n",
    "    cursor.execute(SQL)\n",
    "    output_ts_age_old = cursor.fetchall()\n",
    "output_ts_age_old"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (k) In your view, and based on statistics calculated from your scraped data, which Irish political party is the most successful? Show and describe your calculations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas_profiling as pp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tt = ts_data[[\"Name\",\"party\",\"Vice President\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pp.ProfileReport(tt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with DBcm.UseDatabase(config) as cursor:\n",
    "    SQL = \"select a.party,count( distinct a.name),count( distinct a.vice_president) from(select party,name,vice_president from ts_data) a group by a.party order by 2 desc\"\n",
    "    cursor.execute(SQL)\n",
    "    output_taoiseach_vp = cursor.fetchall()\n",
    "output_taoiseach_vp\n",
    "# we can see Fianna Fail produced 7 Taoiseachs and 15 Vice presidents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with DBcm.UseDatabase(config) as cursor:\n",
    "    SQL = \"select count(a.name) times , a.party from (select distinct name,term_start,party from ts_data ) a group by 2 order by 1 desc\"\n",
    "    cursor.execute(SQL)\n",
    "    output_taoiseach_vp = cursor.fetchall()\n",
    "output_taoiseach_vp\n",
    "# we can see Fianna Fail came into power 13 times which is higher than other parties combined."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Based on all the above retrieved data from (b,d,e,g and k) and profiling we can say _Fianna Fail_ is most successful."
   ]
  }
 ],
 "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}