auditor_year.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "613e813f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import fitz\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "97b340e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import fitz\n",
    "import re\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "042c7dad",
   "metadata": {},
   "outputs": [],
   "source": [
    "path= r'D:\\C Drive Backup 2021\\Desktop\\company_contracts\\cgt_data\\final_data'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "058314f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "def hasNumbers(inputString):\n",
    "                return bool(re.search(r'\\d', inputString))\n",
    "\n",
    "def tender_data_v1(path, file):\n",
    "    page_l=[]\n",
    "    text_l=[]\n",
    "    file_l=[]\n",
    "    doc = fitz.open(os.path.join(path, file))\n",
    "    for j in range(doc.pageCount):\n",
    "      if j>30:\n",
    "        page = doc.loadPage(j)\n",
    "        text = ' '.join(page.getText().splitlines()).strip()\n",
    "        text = text.splitlines()\n",
    "        text1 = ' '.join([i.strip() for i in text if i != ''])\n",
    "        text1= text1.replace('\\s+', '')\n",
    "        if ('audit' in text1.lower().strip()) and ('tender' in text1.lower().strip()) and ('plan'  in text1.lower().strip()):\n",
    "            print(j)\n",
    "            ind1 = [a.start() for a in list(re.finditer('audit', text1.lower()))]\n",
    "            if ind1:\n",
    "                for i in range(len(ind1)):\n",
    "                    if ('tender' in text1[ind1[i]-200:ind1[i]+300].lower()) and hasNumbers(text1[ind1[i]-200:ind1[i]+300].lower()):\n",
    "                        text_l.append(text1[ind1[i]-200:ind1[i]+300])\n",
    "                        file_l.append(file)\n",
    "                        page_l.append(j)\n",
    "                \n",
    "            \n",
    "    df_c= pd.DataFrame()\n",
    "    df_c['file'] = file_l\n",
    "    df_c['page']  = page_l\n",
    "    df_c['text'] = text_l\n",
    "    \n",
    "    df_c = df_c.drop_duplicates()\n",
    "    return(df_c)\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "a396a625",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3i infrastructure ar 2021.pdf\n",
      "101\n",
      "4imprint Group AR 2020.pdf\n",
      "57\n",
      "888 Holdings Annual Report.pdf\n",
      "99\n",
      "91-ninety-one-integrated-annual-report-2021.pdf\n",
      "75\n",
      "76\n",
      "79\n",
      "Admiral Group Plc Annual Report 2020.pdf\n",
      "120\n",
      "121\n",
      "Aggreko PLC annual report 2020.pdf\n",
      "67\n",
      "Airtel Africa Annual Report 2021.pdf\n",
      "108\n",
      "109\n",
      "AJ_Bell PLC Annual Report 2020.pdf\n",
      "33\n",
      "34\n",
      "39\n",
      "Anglo American PLC AR 2020.pdf\n",
      "121\n",
      "122\n",
      "antofagasta_2020_annual_report.pdf\n",
      "127\n",
      "Ascential PLC Annual Report 2020.pdf\n",
      "87\n",
      "Ashmore Group PLC Annual Report 2020.pdf\n",
      "Associated British Foods PLC AR 2020.pdf\n",
      "112\n",
      "124\n",
      "Assura AnnualReport2021final.pdf\n",
      "88\n",
      "89\n",
      "110\n",
      "Aston-Martin-Lagonda-2020-Annual-Report-v2.pdf\n",
      "60\n",
      "AstraZeneca_AR_2020.pdf\n",
      "131\n",
      "Auto Trader  plc ar2021-web.pdf\n",
      "90\n",
      "114\n",
      "Avast PLC Annual report 2020.pdf\n",
      "Aveva AR 2021.pdf\n",
      "87\n",
      "88\n",
      "89\n",
      "aviva-plc-annual-report-and-accounts-2020.pdf\n",
      "83\n",
      "84\n",
      "86\n",
      "105\n",
      "avon-rubber-annual-report-2020.pdf\n",
      "70\n",
      "B&MFull_Annual_Report_and_Accounts_2021.pdf\n",
      "63\n",
      "BAE Systems PLC AR 2020.pdf\n",
      "Balfour Beatty PLC Annual Report 2020.pdf\n",
      "60\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "mupdf: invalid page object\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "132\n",
      "133\n",
      "134\n",
      "Barclays PLC AR 2020.pdf\n",
      "80\n",
      "81\n",
      "Barratt Developments P L C AR 2020.pdf\n",
      "120\n",
      "Beazley PLC Annual Report-2020.pdf\n",
      "bellway_ar20_web.pdf\n",
      "96\n",
      "BHP Group PLC AR 2020.pdf\n",
      "133\n",
      "Biffa Annual Report and Accounts FY21 Final.pdf\n",
      "74\n",
      "105\n",
      "Big Yellow_AR21_Annual_report_and_accounts_2021.pdf\n",
      "84\n",
      "115\n",
      "Bodycote PLC Annual-report-2020.pdf\n",
      "63\n",
      "BP PLC AR 2020.pdf\n",
      "98\n",
      "124\n",
      "230\n",
      "Brewin Dolphin Holdings annual-report-2020.pdf\n",
      "British American Tobacco P.L.C. AR 2020.pdf\n",
      "113\n",
      "115\n",
      "116\n",
      "British Land Company plc AR 2021.pdf\n",
      "120\n",
      "Britvic PLC Annual Report 2020.pdf\n",
      "83\n",
      "85\n",
      "bt-annual-report 2021.pdf\n",
      "88\n",
      "89\n",
      "103\n",
      "Bunzl PLC annual report 2020.pdf\n",
      "115\n",
      "Burberry_2020-21_Annual_Report.pdf\n",
      "178\n",
      "207\n",
      "Bytes Technology Group plc AR 2021.pdf\n",
      "81\n",
      "C&C Group plc annual report 2021.pdf\n",
      "73\n",
      "91\n",
      "cairn-ar2020.pdf\n",
      "56\n",
      "57\n",
      "61\n",
      "90\n",
      "91\n",
      "CandC Annual Report 2021.pdf\n",
      "73\n",
      "91\n",
      "Capita PLC annual report 2020.pdf\n",
      "88\n",
      "Capital & Counties Properties PLC Annual Report 2020.pdf\n",
      "87\n",
      "107\n",
      "Carnival PLC Annual Report 2020.pdf\n",
      "Centamin PLC Annual Report 2020.pdf\n",
      "56\n",
      "58\n",
      "76\n",
      "centrica-ar2020.pdf\n",
      "56\n",
      "58\n",
      "76\n",
      "chemring-plc-annual-report-2020.pdf\n",
      "80\n",
      "Cineworld Group PLC Annual Report 2020.pdf\n",
      "56\n",
      "81\n",
      "Clarkson annual_report_2020.pdf\n",
      "106\n",
      "107\n",
      "Close Brothers Group PLC Annual report 2020.pdf\n",
      "84\n",
      "CLS Annual Report - 31 Dec 2020.pdf\n",
      "88\n",
      "89\n",
      "90\n",
      "93\n",
      "CMC AR 2021.pdf\n",
      "56\n",
      "57\n",
      "Coats Group AR 2020.pdf\n",
      "67\n",
      "68\n",
      "71\n",
      "Coca-Cola HBC AG AR 2020.pdf\n",
      "106\n",
      "146\n",
      "CompassGroupPLC_AnnualReport2020_FINAL.pdf\n",
      "100\n",
      "Computacenter PLC annual report 2020.pdf\n",
      "93\n",
      "97\n",
      "98\n",
      "108\n",
      "Contourglobal 2020_annual_report.pdf\n",
      "108\n",
      "130\n",
      "ConvaTec Group PLC Annual Report 2020.pdf\n",
      "114\n",
      "117\n",
      "Countryside_Properties_PLC_Annual_report_2020.pdf\n",
      "86\n",
      "Crainswick 35913 AR2021 Spreads WEB.pdf\n",
      "44\n",
      "crest-nicholson-annual-integrated-report-2020.pdf\n",
      "94\n",
      "crh-annual-report-2020.pdf\n",
      "62\n",
      "63\n",
      "64\n",
      "154\n",
      "Croda International PLC Annual Report  2020.pdf\n",
      "67\n",
      "74\n",
      "78\n",
      "DCC Annual Report 2021.pdf\n",
      "Dechra Pharmaceuticals PLC Annual report 2020.pdf\n",
      "111\n",
      "Derwent London PLC Annual report 2020.pdf\n",
      "69\n",
      "88\n",
      "Diageo PLC AR 2020.pdf\n",
      "75\n",
      "83\n",
      "132\n",
      "Diploma PLC Annual Report 2020.pdf\n",
      "Direct Line PLC Annual report 2020.pdf\n",
      "101\n",
      "102\n",
      "121\n",
      "Diversified Energy plc_2021_Annual_Report.pdf\n",
      "107\n",
      "Dominos pizza 2020-annual-report.pdf\n",
      "61\n",
      "Dr._Martens_plc_Annual_Report_2021.pdf\n",
      "91\n",
      "103\n",
      "108\n",
      "Dr._Martens_plc_FY21_results_statement.pdf\n",
      "Drax_AR2020.pdf\n",
      "106\n",
      "108\n",
      "Dunelm Group Plc annual-report-2020.pdf\n",
      "37\n",
      "57\n",
      "60\n",
      "Easyjet PLC Annual Report 2020.pdf\n",
      "107\n",
      "electrocomponents aR 2021.pdf\n",
      "47\n",
      "Elementis 2020 Annual Report and Accounts.pdf\n",
      "85\n",
      "energean-2020-annual-report.pdf\n",
      "35\n",
      "114\n",
      "120\n",
      "Equiniti annual-report-2020.pdf\n",
      "70\n",
      "84\n",
      "113\n",
      "essentra-ar-2020-.pdf\n",
      "Euromoney Institutional Investor annual-report-2020.pdf\n",
      "71\n",
      "72\n",
      "Evraz PLC Annual report 2020.pdf\n",
      "98\n",
      "124\n",
      "142\n",
      "experian AR 2021.pdf\n",
      "108\n",
      "109\n",
      "FDM-Annual-Report-2020.pdf\n",
      "84\n",
      "Ferguson-plc-Annual-Report-2020.pdf\n",
      "77\n",
      "80\n",
      "ferrexpo-plc-ara-2020.pdf\n",
      "Flutter Entertainment PLC Annual Report 2020.pdf\n",
      "95\n",
      "100\n",
      "101\n",
      "102\n",
      "Fresnillo PLC Annual Report 2020.pdf\n",
      "163\n",
      "164\n",
      "Future plc ar 2020.pdf\n",
      "73\n",
      "77\n",
      "G4S PLC Annual report 2020.pdf\n",
      "39\n",
      "gamesys-annual-report-and-accounts-2020.pdf\n",
      "Genuit Polypile AR 2020 2021.pdf\n",
      "63\n",
      "67\n",
      "Genus PLC  Annual report 2020.pdf\n",
      "68\n",
      "Glaxosmithkline PLC AR 2020.pdf\n",
      "97\n",
      "Glencore_AR20_Interactive.pdf\n",
      "101\n",
      "Grafton Group PLC Annual Report 2020.pdf\n",
      "Grainger PLC Annual report 2020.pdf\n",
      "73\n",
      "Great Portland Estates-ar2021_bookmarked.pdf\n",
      "131\n",
      "132\n",
      "133\n",
      "134\n",
      "Greencore-Group-plc-Annual-Report-and-Financial-Statements-2020.pdf\n",
      "97\n",
      "106\n",
      "113\n",
      "Greggs PLC Annual Report 2020.pdf\n",
      "77\n",
      "79\n",
      "halma-ara-2021.pdf\n",
      "96\n",
      "111\n",
      "112\n",
      "113\n",
      "Hammerson PLC Annual Report 2020.pdf\n",
      "58\n",
      "102\n",
      "Hargreaves Lansdown PLC AR 2020.pdf\n",
      "70\n",
      "Hays PLC Annual Report 2020.pdf\n",
      "83\n",
      "helios towers-annual-report-2020.pdf\n",
      "66\n",
      "85\n",
      "91\n",
      "92\n",
      "109\n",
      "HG Capital annual-report-and-accounts-2020 2021.pdf\n",
      "128\n",
      "Hikma Pharmaceuticals PLC Annual report 2020.pdf\n",
      "37\n",
      "40\n",
      "46\n",
      "Hill & Smith annual-report-2020.pdf\n",
      "52\n",
      "75\n",
      "Hilton Food Group annual-report-2020.pdf\n",
      "75\n",
      "Hiscox Ltd Annual Report 2020.pdf\n",
      "Hochschild-AR2020-Single-Pages.pdf\n",
      "129\n",
      "homeserve plc AR 2021.pdf\n",
      "90\n",
      "91\n",
      "Howden Joinery Group PLC Annual Report 2020.pdf\n",
      "HSBC Holdings PLC AR 2020.pdf\n",
      "219\n",
      "Ibstock-annual-report-2020.pdf\n",
      "84\n",
      "IMI PLC Annual Report 2020.pdf\n",
      "70\n",
      "74\n",
      "77\n",
      "78\n",
      "80\n",
      "Imperial Brands PLC AR 2020.pdf\n",
      "84\n",
      "Inchape PLC Annual Report 2020.pdf\n",
      "INDIVIOR -2020-Annual-Report_Web.pdf\n",
      "67\n",
      "72\n",
      "informa-annual-report--financial-statements-2020.pdf\n",
      "58\n",
      "IntegraFin Holding PLC Annual Report 2020.pdf\n",
      "Intercontinental Hotels Group PLC AR 2020.pdf\n",
      "90\n",
      "Intermediate Capital Group plc annual report 2021.pdf\n",
      "73\n",
      "77\n",
      "International Consolidated Airlines Group AR 2020.pdf\n",
      "116\n",
      "117\n",
      "124\n",
      "Intertek_Group_plc_Annual_Report_2020.pdf\n",
      "102\n",
      "103\n",
      "Investec-Volume-1-Annual-Report-2021-Online.pdf\n",
      "110\n",
      "Investec-Volume-2-Annual-Report-2021-Online.pdf\n",
      "Investec-Volume-3-Annual-Report-2021-Online.pdf\n",
      "IP Group AR 2020.pdf\n",
      "132\n",
      "ITV PLC annual-report-2020.pdf\n",
      "116\n",
      "123\n",
      "124\n",
      "126\n",
      "IWG PLC Annual Report 2020.pdf\n",
      "JD Sports annual-report-2021.pdf\n",
      "90\n",
      "JD Sports Fashion plc annual report 2021.pdf\n",
      "90\n",
      "John Laing Group PLC Annual Report 2020.pdf\n",
      "104\n",
      "John Wood Group PLC Annual Report 2020.pdf\n",
      "61\n",
      "63\n",
      "96\n",
      "Johnson Matthey plc annual report 2021.pdf\n",
      "135\n",
      "JTC Full_Annual_Report_2020-21.pdf\n",
      "75\n",
      "Jupiter Fund Management Annual Report 2020.pdf\n",
      "76\n",
      "78\n",
      "80\n",
      "171\n",
      "Just Eat PLC Annual Report 2020.pdf\n",
      "Just Group AR 2020.pdf\n",
      "34\n",
      "56\n",
      "63\n",
      "75\n",
      "87\n",
      "94\n",
      "151\n",
      "KAZ Minerals PLC annual report 2020.pdf\n",
      "100\n",
      "101\n",
      "103\n",
      "Kingfisher plc 2020-21 AR.pdf\n",
      "82\n",
      "Lancashire_2020_Annual_Report_210301.pdf\n",
      "70\n",
      "Land Securities Group plc ar 2021.pdf\n",
      "112\n",
      "Legal & General Group PLC AR 2020.pdf\n",
      "75\n",
      "86\n",
      "Lloyds Banking Group PLC AR 2020.pdf\n",
      "105\n",
      "215\n",
      "Londonmetric Property plc AR 2021.pdf\n",
      "110\n",
      "114\n",
      "LSEG Annual Report 2020.pdf\n",
      "93\n",
      "94\n",
      "120\n",
      "M&G Plc Annual Report 2020.pdf\n",
      "80\n",
      "91\n",
      "92\n",
      "101\n",
      "102\n",
      "112\n",
      "137\n",
      "Man Group PLC Annual Report 2020.pdf\n",
      "86\n",
      "Marks and Spencer Group plc ar 2021.pdf\n",
      "55\n",
      "Marshalls-plc-Annual-Report-and-Accounts-2020.pdf\n",
      "67\n",
      "68\n",
      "Mediclinic International plc 2021 Annual Report.pdf\n",
      "152\n",
      "Meggitt-AR-2020-Web-PDF.pdf\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "mupdf: invalid page object\n",
      "mupdf: invalid page object\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "110\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "mupdf: invalid page object\n",
      "mupdf: invalid page object\n",
      "mupdf: invalid page object\n",
      "mupdf: invalid page object\n",
      "mupdf: invalid page object\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "melrose-annual-report-and-accounts-2020.pdf\n",
      "54\n",
      "Micro Focus International PLC Annual Report 2020.pdf\n",
      "98\n",
      "Mitchells & Butlers PLC Annual report 2020.pdf\n",
      "74\n",
      "75\n",
      "76\n",
      "mondi_integrated-report-2020.pdf\n",
      "122\n",
      "150\n",
      "Moneysupermarket com Group Annual Report-2020.pdf\n",
      "45\n",
      "morgan-ar2020.pdf\n",
      "59\n",
      "63\n",
      "Morgan_Sindall_Interactive-AR2020-21.pdf\n",
      "36\n",
      "37\n",
      "38\n",
      "39\n",
      "41\n",
      "Morrisons (WM) Supermarkets plc AR 20 to 21.pdf\n",
      "51\n",
      "52\n",
      "National Express Group PLC Annual Report 2020.pdf\n",
      "79\n",
      "82\n",
      "86\n",
      "87\n",
      "93\n",
      "143\n",
      "NATIONAL GRID Full Report_Interactive_v2 2020-21.pdf\n",
      "73\n",
      "85\n",
      "88\n",
      "natwest-group-annual-report-accounts-2020-21.pdf\n",
      "110\n",
      "279\n",
      "Network International Holding PLC Annual Report 2020.pdf\n",
      "Next AR 2021.pdf\n",
      "114\n",
      "115\n",
      "Next PLC AR 2020.pdf\n",
      "95\n",
      "96\n",
      "Ocado Group-Annual Report 2020.pdf\n",
      "139\n",
      "140\n",
      "Onesavings Bank PLC Annual Report 2020.pdf\n",
      "112\n",
      "140\n",
      "157\n",
      "OXFORD BIOMEDICA AR_2020-21.pdf\n",
      "92\n",
      "Page Group PLC Annual Report 2020.pdf\n",
      "31\n",
      "40\n",
      "41\n",
      "42\n",
      "ParagonBankingGroup_AR_2020.pdf\n",
      "145\n",
      "294\n",
      "Pearson PLC Annual Report 2020.pdf\n",
      "91\n",
      "93\n",
      "95\n",
      "Pennon Group plc AR 2021.pdf\n",
      "102\n",
      "104\n",
      "Pershing-Square-Holdings-Ltd.-2020-Annual-Report-1.pdf\n",
      "52\n",
      "persimmon_ar2020_web_bmarks-final.pdf\n",
      "64\n",
      "104\n",
      "105\n",
      "Petropavoslak _Report_and_Accounts_2020-1.pdf\n",
      "106\n",
      "109\n",
      "112\n",
      "153\n",
      "Pets at Home Group plc AR 2021.pdf\n",
      "108\n",
      "114\n",
      "Phoenix Group Holdings Annual report 2020.pdf\n",
      "120\n",
      "Playtech annual-report-2020.pdf\n",
      "100\n",
      "Plus500_Annual_Report_20.pdf\n",
      "56\n",
      "Polymetal_Annual_Report_2020.pdf\n",
      "33\n",
      "35\n",
      "61\n",
      "Polypipe_-AR-2020_-Interactive-19Apr21.pdf\n",
      "63\n",
      "67\n",
      "Premier_Foods_AR2021.pdf\n",
      "66\n",
      "Primary-Health-Properties-PLC-Annual-Report-2020.pdf\n",
      "67\n",
      "71\n",
      "Provident_Financial_plc_Annual_Report_and_Financial_Statements_2020_002.pdf\n",
      "97\n",
      "109\n",
      "135\n",
      "136\n",
      "137\n",
      "148\n",
      "167\n",
      "prudential-plc-ar-2020.pdf\n",
      "131\n",
      "152\n",
      "153\n",
      "158\n",
      "181\n",
      "PureTech Health AR 2020.pdf\n",
      "107\n",
      "Qinetiq Group plc AR 2021.pdf\n",
      "91\n",
      "Quilter PLC Annual Report 2020.pdf\n",
      "Rank Group Annual Report and Financial Statements Dec 2020.pdf\n",
      "rathbone_brothers_plc_report_and_accounts_2020.pdf\n",
      "99\n",
      "Reckitt Benckiser Group PLC AR 2020.pdf\n",
      "123\n",
      "126\n",
      "Redrow PLC annual report 2020.pdf\n",
      "34\n",
      "36\n",
      "52\n",
      "56\n",
      "Relx PLC AR 2020.pdf\n",
      "118\n",
      "Renishaw PLC Annual Report 2020.pdf\n",
      "69\n",
      "rentokil-initial-annual-report-2020.pdf\n",
      "88\n",
      "100\n",
      "104\n",
      "105\n",
      "106\n",
      "RHI Magnesita NV annual-report-2020.pdf\n",
      "102\n",
      "Rightmove PLC Annual_Report 2020.pdf\n",
      "61\n",
      "Rio Tinto PLC AR 2020.pdf\n",
      "135\n",
      "194\n",
      "196\n",
      "316\n",
      "Rolls-Royce Holdings PLC AR 2020.pdf\n",
      "81\n",
      "Rotork PLC Annual Report 2020.pdf\n",
      "107\n",
      "Royal Dutch Shell PLC AR 2020.pdf\n",
      "154\n",
      "Royal Mail Group_ar2020-21.pdf\n",
      "105\n",
      "RSA Insurance Group PLC Annual Report 2020.pdf\n",
      "52\n",
      "53\n",
      "Sabre Insurance 2020-annual-report-and-accounts.pdf\n",
      "57\n",
      "65\n",
      "79\n",
      "92\n",
      "Safestore Holdings PLC Annual report 2020.pdf\n",
      "67\n",
      "72\n",
      "sainsburys ar2021.pdf\n",
      "61\n",
      "67\n",
      "Saint James Place-annual-report-and-accounts-2020_April2021.pdf\n",
      "106\n",
      "109\n",
      "111\n",
      "sanne-group-plc-annual-report-2020.pdf\n",
      "76\n",
      "81\n",
      "Savills PLC Annual Report 2020.pdf\n",
      "schroders-annual-report-and-accounts-2020.pdf\n",
      "73\n",
      "Segro Public Limited Company AR 2020.pdf\n",
      "122\n",
      "123\n",
      "Serco Group PLC Annual report 2020.pdf\n",
      "Severn Trent-annual-report-2021.pdf\n",
      "110\n",
      "Shaftesbury Annual Report 2020.pdf\n",
      "100\n",
      "Signature Aviation PLC Annual Report 2020.pdf\n",
      "smith nephew_annual report 2020_interactive.pdf\n",
      "92\n",
      "110\n",
      "Smiths Group PLC annual report 2020.pdf\n",
      "108\n",
      "Smurfit_Kappa_Annual_Report_2020.pdf\n",
      "76\n",
      "Softcat PLC Annual Report 2020.pdf\n",
      "61\n",
      "Spectris PLC annual report 2020.pdf\n",
      "Spirax-Sarco S2020-annual-report.pdf\n",
      "113\n",
      "Spirent-Communications-plc-Annual-Report-2020.pdf\n",
      "70\n",
      "76\n",
      "77\n",
      "81\n",
      "SSE AR 2021.pdf\n",
      "129\n",
      "SSP Group PLC annual report 2020.pdf\n",
      "62\n",
      "97\n",
      "St Modwen Properties annual-report2020.pdf\n",
      "70\n",
      "92\n",
      "Standard Life Aberdeen PLC AR 2020.pdf\n",
      "52\n",
      "65\n",
      "66\n",
      "98\n",
      "standard-chartered-plc-full-year-2020-report.pdf\n",
      "113\n",
      "Synthomer_AR2020.pdf\n",
      "94\n",
      "99\n",
      "100\n",
      "Tate & Lyle plc ar 2021.pdf\n",
      "109\n",
      "115\n",
      "116\n",
      "Taylor Wimpey PLC AR 2020.pdf\n",
      "46\n",
      "47\n",
      "48\n",
      "49\n",
      "72\n",
      "tbc bank AR 2020.pdf\n",
      "218\n",
      "220\n",
      "319\n",
      "Telecoms pluc plc-annual-report-2021-rgb-2-.pdf\n",
      "52\n",
      "Tesco AR 2021.pdf\n",
      "71\n",
      "76\n",
      "The Sage Group Plc. AR 2020.pdf\n",
      "119\n",
      "TI Fluid Systems 2020-annual-report.pdf\n",
      "122\n",
      "TP ICAP PLC Annual Report 2020.pdf\n",
      "90\n",
      "Trainline_AR2021.pdf\n",
      "63\n",
      "Travis Perkins PLC annual-report-2020.pdf\n",
      "93\n",
      "Tritax Big Box Reit PLC Annual Report 2020.pdf\n",
      "TUI AG Annual Report 2020.pdf\n",
      "Tullow_Oil_plc_2020-21_Annual_Report_and_Accounts.pdf\n",
      "53\n",
      "udg-annual-report-2020-web.pdf\n",
      "84\n",
      "UK Commercial Property Trust AR 2020.pdf\n",
      "Ultra Electronic Holding PLC Annual Report 2020.pdf\n",
      "Unilever PLC AR 2020.pdf\n",
      "73\n",
      "Unite Group PLC Annual Report 2020.pdf\n",
      "115\n",
      "United Utilities Group plc ar 2021.pdf\n",
      "123\n",
      "136\n",
      "149\n",
      "155\n",
      "156\n",
      "Vectura-Group-plc-Annual-Report-and-Accounts-2020-21.pdf\n",
      "70\n",
      "Vesuvius_AR20.pdf\n",
      "111\n",
      "113\n",
      "Victrex PLC Annual-Report 2020.pdf\n",
      "82\n",
      "Virgin Money UK plc Full_Annual_Report_2020.pdf\n",
      "78\n",
      "Vistry Group PLC Annual report 2020.pdf\n",
      "127\n",
      "vivo-energy-annual-report-and-accounts-2020.pdf\n",
      "94\n",
      "vodafone AR 2021.pdf\n",
      "51\n",
      "52\n",
      "81\n",
      "82\n",
      "126\n",
      "136\n",
      "238\n",
      "Weir Group PLC Annual-report 2020.pdf\n",
      "97\n",
      "Whitbread_AR2020-1_web-1.pdf\n",
      "84\n",
      "117\n",
      "WHSmith PLC Annual Report 2020.pdf\n",
      "52\n",
      "William Hill PLC Annual Report 2020.pdf\n",
      "76\n",
      "77\n",
      "78\n",
      "Wizz Air Holdings plc ar 2021.pdf\n",
      "77\n",
      "165\n",
      "Workspace Group_AR21.pdf\n",
      "70\n",
      "157\n",
      "158\n",
      "wppannualreport2020.pdf\n",
      "132\n",
      "XP Power AR 2020-21.pdf\n",
      "95\n",
      "111\n",
      "116\n"
     ]
    }
   ],
   "source": [
    "df_tender = pd.DataFrame()\n",
    "\n",
    "for i in os.listdir(path):\n",
    "    print(i)\n",
    "    a =tender_data_v1(path, i)\n",
    "    df_tender = pd.concat([df_tender, a], axis = 0)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "ae42562c",
   "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>file</th>\n",
       "      <th>page</th>\n",
       "      <th>text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3i infrastructure ar 2021.pdf</td>\n",
       "      <td>101.0</td>\n",
       "      <td>with Deloitte LLP (‘Deloitte’), the External  ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3i infrastructure ar 2021.pdf</td>\n",
       "      <td>101.0</td>\n",
       "      <td>ctiveness and independence. Shareholders appro...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3i infrastructure ar 2021.pdf</td>\n",
       "      <td>101.0</td>\n",
       "      <td>or for the  year ended 31 March 2021 at the Co...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3i infrastructure ar 2021.pdf</td>\n",
       "      <td>101.0</td>\n",
       "      <td>Holden has been the audit partner for Deloitt...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3i infrastructure ar 2021.pdf</td>\n",
       "      <td>101.0</td>\n",
       "      <td>tender. The Committee reviewed and monitored ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>XP Power AR 2020-21.pdf</td>\n",
       "      <td>111.0</td>\n",
       "      <td>d internal controls  throughout the year, and ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>XP Power AR 2020-21.pdf</td>\n",
       "      <td>111.0</td>\n",
       "      <td>021. In addition, the promotion  of the CFO to...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>XP Power AR 2020-21.pdf</td>\n",
       "      <td>111.0</td>\n",
       "      <td>motion  of the CFO to CEO and hence the  need ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>XP Power AR 2020-21.pdf</td>\n",
       "      <td>116.0</td>\n",
       "      <td>allenge. The  Committee has therefore reported...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>XP Power AR 2020-21.pdf</td>\n",
       "      <td>116.0</td>\n",
       "      <td>nual  General Meeting. The current external au...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2448 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             file   page  \\\n",
       "0   3i infrastructure ar 2021.pdf  101.0   \n",
       "1   3i infrastructure ar 2021.pdf  101.0   \n",
       "2   3i infrastructure ar 2021.pdf  101.0   \n",
       "3   3i infrastructure ar 2021.pdf  101.0   \n",
       "4   3i infrastructure ar 2021.pdf  101.0   \n",
       "..                            ...    ...   \n",
       "2         XP Power AR 2020-21.pdf  111.0   \n",
       "3         XP Power AR 2020-21.pdf  111.0   \n",
       "4         XP Power AR 2020-21.pdf  111.0   \n",
       "5         XP Power AR 2020-21.pdf  116.0   \n",
       "6         XP Power AR 2020-21.pdf  116.0   \n",
       "\n",
       "                                                 text  \n",
       "0   with Deloitte LLP (‘Deloitte’), the External  ...  \n",
       "1   ctiveness and independence. Shareholders appro...  \n",
       "2   or for the  year ended 31 March 2021 at the Co...  \n",
       "3    Holden has been the audit partner for Deloitt...  \n",
       "4    tender. The Committee reviewed and monitored ...  \n",
       "..                                                ...  \n",
       "2   d internal controls  throughout the year, and ...  \n",
       "3   021. In addition, the promotion  of the CFO to...  \n",
       "4   motion  of the CFO to CEO and hence the  need ...  \n",
       "5   allenge. The  Committee has therefore reported...  \n",
       "6   nual  General Meeting. The current external au...  \n",
       "\n",
       "[2448 rows x 3 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tender"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f24ab76",
   "metadata": {},
   "source": [
    "# refer to last_year_planned_tender for data extraction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "e7e5a3fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(r'D:\\C Drive Backup 2021\\Desktop\\company_contracts\\answers\\tender_data\\last_auditor\\spacy_labels_v1.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "df2b2fda",
   "metadata": {},
   "outputs": [],
   "source": [
    "def spacy_form(a):\n",
    "    c={}\n",
    "    b=[]\n",
    "    a = eval(str(a))\n",
    "    for i in a:\n",
    "        start= i['start']\n",
    "        end = i['end']\n",
    "        \n",
    "        label=''.join(i['labels'])\n",
    "        # if label == 'PER':\n",
    "        #   label = 'PERSON'\n",
    "        # elif label == 'AREA':\n",
    "        #   label= 'WORK_OF_ART'\n",
    "       \n",
    "        # elif label == 'TITLE':\n",
    "        #   label = 'NORP'\n",
    "        b.append((start, end, label))\n",
    "    \n",
    "    c['entities']=b\n",
    "    return(c)\n",
    "\n",
    "df= df.dropna()\n",
    "df['spacy_format'] = df.label.apply(lambda x:spacy_form(x) )\n",
    "df['train_v1'] =df[['text', 'spacy_format']].apply(lambda x: (x['text'], x['spacy_format']), axis = 1)\n",
    "spacy_train = [i for i in df.train_v1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "0cb179ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "import spacy\n",
    "nlp=spacy.blank(\"en\")\n",
    "\n",
    "nlp.add_pipe(nlp.create_pipe('ner'))\n",
    "ner = nlp.get_pipe('ner')\n",
    "for _, annotations in spacy_train:\n",
    "    for ent in annotations.get('entities'):\n",
    "        ner.add_label(ent[2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "bace1a63",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 {'ner': 1090.9248351078265}\n",
      "1 {'ner': 354.838973129234}\n",
      "2 {'ner': 351.54123419863754}\n",
      "3 {'ner': 278.2108136581537}\n",
      "4 {'ner': 226.29363648169763}\n",
      "5 {'ner': 230.72419506444504}\n",
      "6 {'ner': 196.45718602140988}\n",
      "7 {'ner': 167.92435824806373}\n",
      "8 {'ner': 157.52763758890478}\n",
      "9 {'ner': 169.004686710142}\n",
      "10 {'ner': 115.38863303131905}\n",
      "11 {'ner': 134.3838623375067}\n",
      "12 {'ner': 127.53413322763409}\n",
      "13 {'ner': 120.0140419559108}\n",
      "14 {'ner': 126.95124085492768}\n",
      "15 {'ner': 101.68972877974497}\n",
      "16 {'ner': 105.05097877645616}\n",
      "17 {'ner': 103.18811616985528}\n",
      "18 {'ner': 121.30299005783975}\n",
      "19 {'ner': 124.77751619901082}\n",
      "20 {'ner': 78.7509233623744}\n",
      "21 {'ner': 81.83797987699242}\n",
      "22 {'ner': 105.1426200742588}\n",
      "23 {'ner': 84.35531351974132}\n",
      "24 {'ner': 91.21098850005076}\n",
      "25 {'ner': 87.813436806777}\n",
      "26 {'ner': 80.59130790922761}\n",
      "27 {'ner': 83.4053290483288}\n",
      "28 {'ner': 76.91895853374851}\n",
      "29 {'ner': 88.52675751257254}\n",
      "30 {'ner': 70.26031887325937}\n",
      "31 {'ner': 84.98740163132146}\n",
      "32 {'ner': 73.77372627459833}\n",
      "33 {'ner': 83.93698713444051}\n",
      "34 {'ner': 74.57591983470414}\n",
      "35 {'ner': 77.08955675858022}\n",
      "36 {'ner': 72.93474752067844}\n",
      "37 {'ner': 79.10798501439395}\n",
      "38 {'ner': 66.1051541899661}\n",
      "39 {'ner': 71.7174379999331}\n",
      "40 {'ner': 69.37616529352451}\n",
      "41 {'ner': 56.4157063570614}\n",
      "42 {'ner': 59.877804940486584}\n",
      "43 {'ner': 45.721757156187316}\n",
      "44 {'ner': 67.00210697292991}\n",
      "45 {'ner': 53.447705602582}\n",
      "46 {'ner': 70.91270182471023}\n",
      "47 {'ner': 54.21676301438156}\n",
      "48 {'ner': 48.69920353077868}\n",
      "49 {'ner': 57.50279268344314}\n",
      "50 {'ner': 61.617996413359755}\n",
      "51 {'ner': 55.98203879219982}\n",
      "52 {'ner': 69.37857134997397}\n",
      "53 {'ner': 52.65411782564227}\n",
      "54 {'ner': 55.519268252160096}\n",
      "55 {'ner': 65.49482385213474}\n",
      "56 {'ner': 61.92503139578899}\n",
      "57 {'ner': 55.48607804299311}\n",
      "58 {'ner': 54.8033272990987}\n",
      "59 {'ner': 69.74709227620497}\n",
      "60 {'ner': 55.8024419261242}\n",
      "61 {'ner': 45.576687016997354}\n",
      "62 {'ner': 37.4060332093439}\n",
      "63 {'ner': 46.90043413124602}\n",
      "64 {'ner': 59.25955631117221}\n",
      "65 {'ner': 51.984832602695306}\n",
      "66 {'ner': 43.22195390263655}\n",
      "67 {'ner': 42.33719916249298}\n",
      "68 {'ner': 46.9019399885722}\n",
      "69 {'ner': 55.4731192534988}\n",
      "70 {'ner': 45.9242773018284}\n",
      "71 {'ner': 53.0795907914987}\n",
      "72 {'ner': 58.747664374043104}\n",
      "73 {'ner': 61.622876685322154}\n",
      "74 {'ner': 44.549214123362134}\n",
      "75 {'ner': 48.6107684721648}\n",
      "76 {'ner': 53.97637343629874}\n",
      "77 {'ner': 50.11218244438598}\n",
      "78 {'ner': 42.20001749261638}\n",
      "79 {'ner': 58.45631005757251}\n"
     ]
    }
   ],
   "source": [
    "# spacy2.2.4\n",
    "n_iter =80\n",
    "import random\n",
    "other_pipes = [pipe for pipe in nlp.pipe_names if pipe != 'ner']\n",
    "with nlp.disable_pipes(*other_pipes):\n",
    "    optimizer = nlp.begin_training()\n",
    "    for i in range(n_iter):\n",
    "        random.shuffle(spacy_train)\n",
    "        losses = {}\n",
    "        \n",
    "        for texts,annotations in spacy_train:\n",
    "            \n",
    "            nlp.update([texts], [annotations],  sgd = optimizer, losses= losses, drop=0.2)\n",
    "        print(i, losses)\n",
    "            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "2ebca2f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import pickle as pkl\n",
    "# import pickle as pkl\n",
    "# pkl.dump(nlp, open(r'D:\\C Drive Backup 2021\\Desktop\\company_contracts\\answers\\tender_data\\last_auditor\\auditor_year_nlp.pkl', 'wb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "844f2eaf",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle as pkl\n",
    "nlp = pkl.load(open(r'D:\\C Drive Backup 2021\\Desktop\\company_contracts\\answers\\tender_data\\last_auditor\\auditor_year_nlp.pkl', 'rb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "48ac7c7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "def entities1(x):\n",
    "    val_l=[]\n",
    "    doc = nlp(x)\n",
    "    for ent in doc.ents:\n",
    "        if ent.label_ != 'FALSE_DATE':\n",
    "            val_l.append(ent.text)\n",
    "    return(val_l)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "e445a9a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "val_entities=[]\n",
    "\n",
    "for i in df_tender.text:\n",
    "    val_entities.append(entities1(i))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "80406653",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_tender['entities'] = val_entities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "8789537d",
   "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>file</th>\n",
       "      <th>page</th>\n",
       "      <th>text</th>\n",
       "      <th>entities</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3i infrastructure ar 2021.pdf</td>\n",
       "      <td>101.0</td>\n",
       "      <td>with Deloitte LLP (‘Deloitte’), the External  ...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3i infrastructure ar 2021.pdf</td>\n",
       "      <td>101.0</td>\n",
       "      <td>ctiveness and independence. Shareholders appro...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3i infrastructure ar 2021.pdf</td>\n",
       "      <td>101.0</td>\n",
       "      <td>or for the  year ended 31 March 2021 at the Co...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3i infrastructure ar 2021.pdf</td>\n",
       "      <td>101.0</td>\n",
       "      <td>Holden has been the audit partner for Deloitt...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3i infrastructure ar 2021.pdf</td>\n",
       "      <td>101.0</td>\n",
       "      <td>tender. The Committee reviewed and monitored ...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4imprint Group AR 2020.pdf</td>\n",
       "      <td>57.0</td>\n",
       "      <td>are recoverable. External audit The Audit Comm...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4imprint Group AR 2020.pdf</td>\n",
       "      <td>57.0</td>\n",
       "      <td>ation and monitoring of the Group’s policy on ...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4imprint Group AR 2020.pdf</td>\n",
       "      <td>57.0</td>\n",
       "      <td>ernal  audit, overseeing relations with the ex...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4imprint Group AR 2020.pdf</td>\n",
       "      <td>57.0</td>\n",
       "      <td>ointment or reappointment  of the external aud...</td>\n",
       "      <td>[2019]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4imprint Group AR 2020.pdf</td>\n",
       "      <td>57.0</td>\n",
       "      <td>ies) Order 2014  and undertook a competitive t...</td>\n",
       "      <td>[2019]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            file   page  \\\n",
       "0  3i infrastructure ar 2021.pdf  101.0   \n",
       "1  3i infrastructure ar 2021.pdf  101.0   \n",
       "2  3i infrastructure ar 2021.pdf  101.0   \n",
       "3  3i infrastructure ar 2021.pdf  101.0   \n",
       "4  3i infrastructure ar 2021.pdf  101.0   \n",
       "0     4imprint Group AR 2020.pdf   57.0   \n",
       "1     4imprint Group AR 2020.pdf   57.0   \n",
       "2     4imprint Group AR 2020.pdf   57.0   \n",
       "3     4imprint Group AR 2020.pdf   57.0   \n",
       "4     4imprint Group AR 2020.pdf   57.0   \n",
       "\n",
       "                                                text entities  \n",
       "0  with Deloitte LLP (‘Deloitte’), the External  ...       []  \n",
       "1  ctiveness and independence. Shareholders appro...       []  \n",
       "2  or for the  year ended 31 March 2021 at the Co...       []  \n",
       "3   Holden has been the audit partner for Deloitt...       []  \n",
       "4   tender. The Committee reviewed and monitored ...       []  \n",
       "0  are recoverable. External audit The Audit Comm...       []  \n",
       "1  ation and monitoring of the Group’s policy on ...       []  \n",
       "2  ernal  audit, overseeing relations with the ex...       []  \n",
       "3  ointment or reappointment  of the external aud...   [2019]  \n",
       "4  ies) Order 2014  and undertook a competitive t...   [2019]  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tender.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "012cac5b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def combine_entities_auditor(data_group):\n",
    "    \n",
    "    dt = data_group.groupby(['file'])['entities'].apply(lambda x: sum(x, [])).reset_index(drop = False)\n",
    "    \n",
    "    return(dt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "851d1f73",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_tender = pd.DataFrame()\n",
    "\n",
    "uniq_page = list(df_tender.file.unique())\n",
    "for i in uniq_page:\n",
    "    a = combine_entities_auditor(df_tender[df_tender.file==i])\n",
    "    data_tender = pd.concat([data_tender, a], axis = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "e1a9efc9",
   "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>file</th>\n",
       "      <th>entities</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3i infrastructure ar 2021.pdf</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4imprint Group AR 2020.pdf</td>\n",
       "      <td>[2019, 2019, 2019]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>888 Holdings Annual Report.pdf</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>91-ninety-one-integrated-annual-report-2021.pdf</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Admiral Group Plc Annual Report 2020.pdf</td>\n",
       "      <td>[2016, 2015, 2016, 2016, 2016, 2021, 2021]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aggreko PLC annual report 2020.pdf</td>\n",
       "      <td>[2016, 2016, 2016]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Airtel Africa Annual Report 2021.pdf</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AJ_Bell PLC Annual Report 2020.pdf</td>\n",
       "      <td>[2020, 2020, 2020, 2020, 2020]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Anglo American PLC AR 2020.pdf</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>antofagasta_2020_annual_report.pdf</td>\n",
       "      <td>[2015, 2015, 2015, 2015]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              file  \\\n",
       "0                    3i infrastructure ar 2021.pdf   \n",
       "0                       4imprint Group AR 2020.pdf   \n",
       "0                   888 Holdings Annual Report.pdf   \n",
       "0  91-ninety-one-integrated-annual-report-2021.pdf   \n",
       "0         Admiral Group Plc Annual Report 2020.pdf   \n",
       "0               Aggreko PLC annual report 2020.pdf   \n",
       "0             Airtel Africa Annual Report 2021.pdf   \n",
       "0               AJ_Bell PLC Annual Report 2020.pdf   \n",
       "0                   Anglo American PLC AR 2020.pdf   \n",
       "0               antofagasta_2020_annual_report.pdf   \n",
       "\n",
       "                                     entities  \n",
       "0                                          []  \n",
       "0                          [2019, 2019, 2019]  \n",
       "0                                          []  \n",
       "0                                          []  \n",
       "0  [2016, 2015, 2016, 2016, 2016, 2021, 2021]  \n",
       "0                          [2016, 2016, 2016]  \n",
       "0                                          []  \n",
       "0              [2020, 2020, 2020, 2020, 2020]  \n",
       "0                                          []  \n",
       "0                    [2015, 2015, 2015, 2015]  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_tender.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "573f3f9a",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_tender['final_value']  = data_tender.entities.apply(lambda x: min(x) if x else None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "a6959235",
   "metadata": {},
   "outputs": [],
   "source": [
    "# data_tender.to_csv(r'D:\\C Drive Backup 2021\\Desktop\\company_contracts\\answers\\tender_data\\last_auditor\\audior_date_change_2021.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "cbe4294b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('2004', 'DATE'), ('2016', 'FALSE_DATE')]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "entities1('''Group risk register from which the Group’s principal risks are identified and materiality calculated. Further information on the Group’s principal risks is available on pages 71 to 77. External auditor Deloitte LLP (Deloitte) continues as the Group’s external auditor. Deloitte was first appointed in 2004 and reappointed in 2016 following an audit tender. The Committee reviews the appointment of the external auditor annually and, in accordance with legislation and its own terms of re''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "00b64fd9",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}