CVLBow.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [],
   "source": [
    "from tqdm import tqdm_notebook as tqdm\n",
    "\n",
    "from time import time\n",
    "import json\n",
    "from glob import glob\n",
    "from pprint import pprint\n",
    "import io\n",
    "import os\n",
    "from tqdm import tqdm_notebook as tqdm\n",
    "\n",
    "import math\n",
    "import pandas as pd\n",
    "\n",
    "from collections import Counter, defaultdict, OrderedDict\n",
    "\n",
    "from fuzzywuzzy import fuzz\n",
    "\n",
    "import spacy\n",
    "import nltk\n",
    "import string\n",
    "\n",
    "nlp = spacy.load('en')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "414bcbd549bc46b79be1c88d3b3a711c",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=2273), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "2273"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "json_files = glob('data/cvl_formatted/*.json')\n",
    "\n",
    "def load_file(fname):\n",
    "    return json.load(io.open(fname, 'r', encoding='utf-8-sig'))\n",
    "\n",
    "data = {os.path.basename(fname)[:-5]: load_file(fname) for fname in tqdm(json_files)}\n",
    "\n",
    "len(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "master_word_counter = Counter()\n",
    "master_noun_counter = Counter()\n",
    "tf_words = {key:Counter() for key in data.keys()}\n",
    "tf_nouns = {key:Counter() for key in data.keys()}\n",
    "word_idf = Counter()\n",
    "noun_idf = Counter()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'aid': '10809618',\n",
       " 'title': 'Global Methodology And Assumptions: Assessing Pools Of Residential Loans',\n",
       " 'top_domain': 'Structured Finance',\n",
       " 'bottom_domain': 'RMBS',\n",
       " 'pub_date': 'Jan 25, 2019',\n",
       " 'link': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?DocumentId=10809618&From=SNP_CRS',\n",
       " 'content': '  Guidance | Criteria | Structured Finance | RMBS:  Global Methodology And Assumptions: Assessing Pools Of Residential Loans    ( Editor\\'s note: This guidance article was updated to include guidance for the U.K. on May 24, 2019. There were no changes to the guidance for Argentina or Mexico at that time.)  Table of Contents  Overview And Scope              Paragraphs: 1-2 Country-Specific Guidance       Paragraphs: 3-101 Argentina                       Paragraphs: 3-14 Mexico                          Paragraphs: 15-48 The U.K.                        Paragraphs: 49-101  Overview And Scope  1. This document relates to S&P Global Ratings\\' criteria \" ~~** Global Methodology And Assumptions: Assessing Pools Of Residential Loans ||LINK_ID:1|| **~~  ,\" published Jan. 25, 2019, and updated on May 24, 2019 (hereafter referred to as the \"residential mortgage criteria\") and \" ~~** Global Framework For Cash Flow Analysis Of Structured Finance Securities ||LINK_ID:2|| **~~  ,\" published Oct. 9, 2014 (hereafter referred to as the \"cash flow criteria\"). This article was updated to include guidance for the U.K. on May 24, 2019. There were no changes to the guidance for Argentina or Mexico at that time.  2. It is intended to be read in conjunction with these criteria and provides additional information and guidance related to the analytical application of our methodology and assumptions. Guidance documents are not criteria, as they do not establish a methodological framework for determining credit ratings. Guidance documents provide guidance on various matters, including articulating how we may apply specific aspects of criteria; describing variables or considerations related to criteria that may change over time; providing additional information on nonfundamental factors that our analysts may consider in the application of criteria; and providing additional guidance on the exercise of analytical judgment under our criteria. Our analysts consider guidance documents as they apply criteria and exercise analytical judgment in the analysis and determination of credit ratings. However, in applying criteria and the exercise of analytic judgment to a specific issuer or issue, analysts may determine that it is suitable to follow an approach that differs from one described in the guidance document. Where appropriate, the rating rationale will highlight that a different approach was taken.  Key Publication Information Original publication date: Jan. 25, 2019. Updated publication date: May 24, 2019. This article provides guidance to \"Criteria: Global Methodology And Assumptions: Assessing Pools of Residential Loans,\" published Jan. 25, 2019, and revised May 24, 2019, and to \"Global Framework for Cash Flow Analysis Of Structured Finance Securities,\" published Oct. 9, 2014. As the criteria become effective in different markets, this guidance document will correspondingly become applicable in those markets. We exercise analytical judgment in determining multiples within ranges in our criteria.  COUNTRY-SPECIFIC GUIDANCE  Guidance: Argentina  Argentina background  3. The MMA for Argentina is \"very high risk\" (\\'5\\' on a scale of \\'1\\' to \\'6\\'), which is at the higher end of the scale, reflecting the greater perceived risk relative to other jurisdictions. The MMA is based on an adjusted economic risk score of \\'5\\', a mortgage industry risk score of \\'5\\', and our assessment of the full-recourse nature of the market. This reflects our view of:  The risks in the Argentine banking system; The country\\'s historical unemployment sensitivity to changes in economic output, which could negatively affect borrowers\\' ability to pay their mortgages in the absence of a strong social welfare system; and The impact of recourse to other assets on the borrower\\'s willingness to pay in a declining housing market.  Argentina archetypal pool  4. We define the archetypal pool for Argentina as shown in table 1.  Table 1 | Download Table   Argentina \\'AAA\\' and \\'B\\' foreclosure frequency anchors  5. Based on an MMA of \\'5\\', Argentina\\'s \\'AAA\\' foreclosure frequency anchor for the archetypal pool is 28%, and its \\'B\\' foreclosure frequency anchor is 6.5%. The \\'AAA\\' assumption reflects the greater potential for defaults under an extreme stress scenario given Argentina\\'s history of high inflation and interest rates as well as limitations in Argentine mortgage performance. The 6.5% \\'B\\' foreclosure frequency assumption reflects our assessment of available performance metrics and our expectations of future performance over the medium term given anticipated macroeconomic conditions.  6. In our analysis, we considered the performance of outstanding S&P Global Ratings-rated Argentine residential mortgage-backed securities (RMBS) transactions, adjusted for seasoning to reflect expected lifetime defaults, which was relatively strong. However, the level of mortgage penetration in the Argentine market has been extremely low historically. As mortgage credit grows at an increasing pace in the current environment to serve a broader segment of borrowers, we expect the low level of defaults observed historically in bank-originated mortgage transactions is likely to increase. In our view, the 6.5% \\'B\\' foreclosure frequency assumption sufficiently addresses this risk.  Argentina archetypal foreclosure frequency anchors  7. Table 2 shows the archetypal foreclosure frequency anchors.  Table 2 | Download Table   Argentina foreclosure frequency factors  8. Table 3 shows the foreclosure frequency factors.  Table 3 | Download Table   9. Charts 1 and 2 show the Argentina LTV and DTI adjustment factors, respectively.    10. Table 4 shows the province concentration thresholds.  Table 4 | Download Table   Argentina loss severity assumptions  11. Table 5 shows our loss severity assumptions for Argentina.  Table 5 | Download Table   Table 6 | Download Table   Argentina cash flow assumptions  Defaults  12. We assume that mortgages default within the first year of a transaction\\'s life. The defaults occur evenly during this 12-month period.  Other liquidity stresses  13. Delinquencies: To stress liquidity, we also assume that mortgage defaults are accompanied by loan delinquencies (that is, mortgages that are temporarily in default). Like loan defaults, delinquencies create cash flow shortfalls. We assume that an amount of mortgages equal to one-half of the default frequency is delinquent for 12 months, upon which time the borrowers cure themselves. This scenario is then repeated throughout the transaction\\'s life. The final cure occurs on the final maturity date of the debt.  14. Excess spread: When assessing the amount of excess spread in a transaction, we stress the issue by assuming worst-case prepayment and expense scenarios. When appropriate, we assume that any excess spread is taxed so that amounts available to the issuer are reduced. Some structures may require that a defined amount of excess spread be trapped, meaning it is not released by the issuer. This allows excess spread to be available for future needs.  Guidance: Mexico  Mexico background  15. The Mortgage Market Assessment (MMA) for Mexico is \"high risk\" (\\'4\\' on a scale of \\'1\\' to \\'6\\'). This is based on an adjusted economic risk score of \\'3\\', a mortgage industry risk score of \\'3\\', and our assessment of a limited level of recourse available to lenders in the market. This reflects our view of:  The risks in the Mexican banking system; Mexico\\'s historical unemployment sensitivity to changes in economic output, which could negatively affect borrowers\\' ability to pay their mortgages in the absence of a strong social welfare system; and The potential for reduced willingness to pay in negative equity scenarios due to limited-recourse lending relative to full-recourse markets.  Mexico archetypal pool  16. We define the archetypal pool for Mexico as shown in table 7.  Table 7 | Download Table   Mexico \\'AAA\\' and \\'B\\' foreclosure frequency anchors  17. Based on an MMA of \"high risk\" (\\'4\\'), Mexico\\'s \\'AAA\\' foreclosure frequency anchor for the archetypal pool is 20%, and its \\'B\\' foreclosure frequency anchor is 5%. The \\'AAA\\' foreclosure frequency assumption reflects the potential increased volatility associated with idiosyncrasies of the Mexican mortgage market such as the significant portion of the population employed in the informal economy relative to other countries assessed as \"high risk\". In our view, the informal economy is more responsive to changing macroeconomic conditions, and therefore expected to contribute to higher defaults under an extreme stress scenario relative to other markets dominated by formal employment arrangements.  18. The 5% \\'B\\' foreclosure frequency assumption reflects our assessment of historical performance and our expectations of future performance over the medium term given anticipated macroeconomic conditions. In our analysis, we considered loss expectations across bank-originated mortgage portfolios as well as the performance of outstanding S&P Global Ratings-rated Mexican RMBS transactions, in both cases adjusted for seasoning to reflect expected lifetime losses and defaults, respectively.  Mexico archetypal foreclosure frequency anchors  19. Table 8 shows the archetypal foreclosure frequency anchors.  Table 8 | Download Table   Mexico foreclosure frequency factors  20. Table 9 shows the foreclosure frequency factors.  Table 9 | Download Table   Foreclosure frequency factors for Fovissste and Infonavit  21. Table 10 shows the foreclosure frequency factors for Fovissste and Infonavit.  Table 10 | Download Table   22. Charts 3 and 4 show the Mexico loan-to-value (LTV) and debt-to-income (DTI) adjustment factors, respectively.    23. Table 11 shows the state concentration thresholds.  Table 11 | Download Table   Infonavit employer concentration stress  24. Since Infonavit does not have third-party recourse if a private sector company ceases to make required payments, we view the employer-concentration risk in relation to Infonavit transactions as a credit risk. To address this risk, we apply an employer-concentration stress in our default risk analysis of these pools. The assumed default frequency for these pools is the higher of: (i) the foreclosure frequency estimated by applying the relevant adjustment factors described above, and (ii) the sum of the top employer concentrations relevant at each rating (see table 12).  Table 12 | Download Table   Mexico loss severity assumptions  25. Table 13 shows our loss severity assumptions for Mexico.  Table 13 | Download Table   Table 14 | Download Table   Mexico cash flow assumptions  Defaults  26. When analyzing the payment structure and cash flow mechanics of Mexican RMBS, we typically test the transaction\\'s ability to withstand front-loaded and concentrated default timing scenarios as outlined in table 15. In certain cases, we also typically apply a back-loaded curve (also included in table 15) such as for transactions:  With pro rata (nonsequential) pay structures; That rely on excess spread (to cover credit losses, pay fees, expenses or coupons, or liquidity stresses); With any other collateral or structural features that may, in our view, increase the likelihood of back-ended defaults occurring or affecting the rated securities.  27. For highly seasoned transactions where the mortgage portfolio\\'s average remaining term is less than 12 years, we may make adjustments to the default timing curves to stress scenarios that better reflect the pool\\'s composition.  Table 15 | Download Table   Prepayments  28. Our prepayment assumptions are generally modeled according to each loan\\'s characteristics and the historical information provided by the originator on the specific securitized loans. For example, for loans originated by Sofoles, we typically model an 8% constant prepayment rate (CPR) that increases linearly to 16% over the first two years of the transaction. For loans originated by commercial banks, we typically assume a 10%-20% prepayment rate.  29. Prepayment speeds are an important factor in our analysis of structures that rely on excess spread. Certain types of structures benefit from higher prepayments and others benefit from lower prepayments. For example, when excess spread is extremely low or even negative, we assume a low prepayment speed in our cash flow analysis. We look at the historical prepayments provided by the mortgage seller and any historical information we may have on the mortgage as published in the latest S&P Global Ratings\\' Mexican RMBS index. We typically take the lower of these two prepayment rates.  Recoveries  30. We assume that the recovery of proceeds from the foreclosure and sale of repossessed properties occur 48 months (for bank and Sofol originated loans) or 72 months (for Infonavit and Fovissste originated loans) following a payment default. In addition, we assume payments under a primary mortgage insurance policy are made according to the policy\\'s terms (usually 18-24 months).  Liquidity stresses  31. Fovissste employer-concentration stress: To address the risk of a delay in receipt of payments from a given government entity for pools of Fovissste-originated loans, we apply a liquidity stress in our analysis based on the concentration of employers to which the loans are exposed. The stress assumes that the most-concentrated employers (the top employers) in the mortgage pool stop transferring collections to Fovissste during a specified time period according to table 16 below. The stress is applied to the percentage of loans in the concentrated pool (from the number of top employers that stop transferring payments) that exceed the foreclosure frequency estimated by applying the relevant adjustment factors described above at each rating level. If the top five concentrations add up to less than the foreclosure frequency for that rating stress, then no additional liquidity stress is applied.  Table 16 | Download Table   32. Commingling: We typically model this risk for each transaction as a loss of one month\\'s cash flow. However, we may adjust our assumptions depending on the controls specified in the legal documentation.  33. Loan extensions: For pools that contain residential loans with an option to extend payments on the loan up to 12 months beyond the maturity date (extension loans), we include a delay of a proportion of scheduled interest and principal receipts in our cash flow analysis. The proportion is based on historical data on borrowers that have exercised such an option, assuming they use this option for the maximum duration permitted under the contractual terms of their loans.  Other cash flow stresses  34. UDI-minimum wage disparity: With respect to transactions issued by Infonavit and Fovissste, where there is an asset-liability mismatch reflecting the minimum wage adjusted (VSM) MXN-denominated loans and Unidad De Inversion (UDI)-denominated liabilities, we apply additional stresses in our cash flow analysis. To address the potential impact of this mismatch on credit enhancement, we model a loss in the real value of minimum wages according to table 17.  Table 17 | Download Table   35. This loss is applied over the timing curve specified in table 18 below depending on whether the loans were originated by Infonavit or Fovissste.  Table 18 | Download Table   36. Infonavit collateral haircuts: To account for the risk that some loans in REA or EXT may not pay off in full by the final legal maturity of the rated notes (because the borrowers\\' monthly payments are not recast to compensate for missed or partial payments), we haircut the amount of collateral to which credit is given in our cash flow analysis (see table 19). We apply this percentage to the current pool balance to reduce the amount of assets generating principal and interest payments to pay off outstanding liabilities.  Table 19 | Download Table   37. Other: As deemed appropriate to a given transaction based on collateral and/or structural considerations, we may apply additional stresses in our cash flow analysis. For example, we may apply stresses to address the liquidity of invested trust assets or currency mismatches between assets and liabilities.  Reference guide to industry terminology  38. The following definitions are intended to assist readers who may be less familiar with some Mexican mortgage industry terminology referred to in these criteria. These terms may have a more specific meaning in particular contexts, including transaction documents and legal frameworks. These definitions are not intended to redefine or alter those contextual meanings and are simply provided to assist with understanding these criteria and our expectations.  39. Apoyo Infonavit/Cofinanciamiento: A mortgage financing method that involves the merger of two credits: one granted by the relevant government institution, Fovissste or Infonavit, respectively; and the other provided by a bank or Sofom.  40. Extension (EXT) or Prorroga: With respect to loans originated by Infonavit or Fovissste, respectively, EXT or Prorroga generally applies when a borrower is no longer receiving income and is permitted an up to 12-month delay in mortgage payments.  41. Fovissste (Fondo de la Vivienda del Instituto de Seguridad Y Servicios Sociales de los Trabajadores del Estado): Housing Fund of the Social Security and Services Institute for State Workers: A federal agency formed in 1972 to meet the mortgage needs of federal, state, and municipal government workers, as well as public university and local agency employees. Its operations are funded through a mandatory contribution equal to 5% of each public sector employee\\'s salary. Borrowers\\' mortgage payments are automatically deducted from their payrolls in amounts up to 30% of their salary.  42. Investment unit (Unidades de Inversion, or UDI): Inflation unit based on price changes in the Mexican economy that is published daily.  43. Minimum wage (Veces Salario Minimo, or VSM): The daily general minimum wage established by the National Minimum Salaries Commission, which sets fixed wages for a given year.  44. Infonavit (Instituto del Fondo Nacional de la Vivienda para los Trajadores): National Workers\\' Housing Fund: A federal agency formed in 1972 to meet the mortgage needs of private sector employees. Its operations are funded through a mandatory contribution equal to 5% of each private sector employee\\'s salary. All borrowers must maintain an account at Infonavit from which their mortgage payments are automatically deducted in amounts generally up to 30% of their salary.  45. Sofomes (Sociedades Financieras de Objeto Multiple): Non-bank financial institutions: Financial companies that do not have a full banking license or are not supervised by a national or international banking regulatory agency. They provide all classes of credit, including mortgages, car loans, personal loans, bridging loans, department store loans, credit cards, commercial loans, leasing, and microcredit.  46. Ordinary amortization scheme (Régimen Ordinario de Amortización, or ROA): With respect to loans originated by Infonavit or Fovissste, ROA represents the typical terms of mortgage interest and principal payments when the borrower is employed by the private (or public) sector whereby funds are deducted directly from the borrower\\'s salary.  47. Special amortization scheme (Régimen Especial de Amortización, or REA) or out-of-sector: With respect to loans originated by Infonavit or Fovissste, REA or out-of-sector generally applies when automatic deduction of mortgage payments is not possible and the borrower must therefore make payments directly to Infonavit or Fovissste because they: (i)lost their employment and are not under an extension arrangement or the extension has expired; (ii)are an accredited pensioner or retired; or (iii)change from private (or public) sector to public (or private) sector employment.  48. Unit of measurement and update (Unidad de Medida y Actualización, or UMA): The unit of measurement or economic reference in pesos to calculate the payment of the obligations and assumptions foreseen in the federal laws of the states, as well as in the legal dispositions that result from them, such as mortgages denominated in VSM.  Guidance: U.K.  The U.K. background  49. The MMA for The U.K. is \"intermediate risk\" (\\'3\\' on a scale of \\'1\\' to \\'6\\'). This is based on an adjusted economic risk score of \\'3\\', a mortgage industry risk score of \\'3\\', and our assessment of recourse available to lenders in the market. This reflects our view of:  The risks in the U.K. banking system; The U.K.\\'s historical unemployment sensitivity to changes in economic output, which could negatively affect borrowers\\' ability to pay their mortgages in the absence of a strong social welfare system in relation to mortgage payments; and The willingness to pay in low/negative equity scenarios due to full-recourse.  The U.K. archetypal pool  50. We define the archetypal pool for The U.K. as follows:  Table 20 | Download Table   The U.K. archetypal foreclosure frequency anchors  51. Table 21 shows the archetypal foreclosure frequency anchors.  Table 21 | Download Table   The U.K. foreclosure frequency adjustments  52. Table 22 shows the foreclosure frequency adjustments, which are typically applied at a property level.  Table 22 | Download Table   Weighted average frequency of foreclosure (WAFF)   Loan affordability:  Table 23 | Download Table   53. If the factor for combined income results in a greater adjustment than would have been the case using only one of the two incomes, then the lower adjustment is used for that loan.  Borrower occupancy status (buy-to-let):  54. The guidance considers the affordability level associated with each loan, based on the ratio of the initial property rental yield to an S&P Global Rating\\'s estimation of a stressed loan financing cost currently estimated at 5.5%.  55. Typically, borrower related adjustments like income multiple, self-certification, self-employment, and first-time buyer do not apply to BTL loans.  Table 24 | Download Table   56. We do not apply an interest only adjustment to buy-to-let loans as the sale of the property is a viable exit strategy for a borrower given the property is not their residence.  57. For BTL, we do not apply a commercial borrower adjustment for loans advanced through a limited liability company or limited liability partnership in the following circumstances: if the issuer/servicer has a demonstrable track record of effectively using a receiver of rent; or where the lender seeks personal guarantees from the shareholders/directors of the limited liability company or partnership.  Adverse credit history:  58. Adjustments are applied to loans to borrowers who have had prior county court judgements (CCJs). Adjustments are calculated based on the number of CCJs.  Table 25 | Download Table   59. A borrower/borrowers with combined CCJs with a monetary amount of greater than £5,000 (but less than £10,000) in the six years preceding the date of the analysis typically receive the higher of the adjustment for the count of CCJs in table 25 above and 2.5x. A borrower/borrowers with combined CCJs with a monetary amount of greater or equal to £10,000 in the last six years preceding the date of the analysis typically receive the higher of the adjustment for the count of CCJs in table 25 above and 4.0x.  60. The CCJ adjustments outlined in paragraphs 57 and 58 above cease to apply six years after the date they were registered. Instead, a loan where a borrower/borrowers previously had a CCJ receive an adjustmentof 1.075 for the life of the loan.  61. Adjustments detailed in table 25 above are aggregated across both borrowers when it is a joint mortgage.  62. For discharged bankrupt borrowers that used an IVA and borrowers who have had a prior repossession, the guidance establishes a default frequency equal to the greater of 50% and the foreclosure frequency that results from all other applicable loan characteristics, except loan seasoning and arrears status. The adjustments for loan seasoning and arrears, where applicable, are made to the resulting default frequency.  63. Similar to CCJ adjustments, we do not apply adjustments for bankrupt borrowers, borrowers with IVAs, or prior repossessions when they occurred more than six years from the date of our analysis. For borrowers with a prior repossession, bankruptcy order, or IVA more than six years ago, we apply a 1.1x adjustment.  64. While we do not adjust default probabilities for other adverse credit events, for example, defaults on other consumer credit products and debt management plans such as credit cards, at a loan-by-loan level, we may ask for default data (amounts and numbers) for individual borrowers to ascertain whether a mortgage pool has concentration to borrowers with recorded defaults in the six years preceding the date of analysis.  65. In the context of new issue rating analysis, for remortgage borrower(s) who were equal or greater than 90 days in arrears within six months of remortgaging their loan to their current lender, we apply a 4.0x adjustment.  Non-income verified loan products (including self-certified loan products):  66. The adjustment factor is 1.5x for a self-certified mortgage loan and decreases over time if the loan is not in arrears (see table 26 below). The self-certification adjustment factor applies only if the borrower also occupies the property.  Table 26 | Download Table   67. We treat fast-track mortgage loans as income-verified if a review of a lender\\'s underwriting processes and historical data indicates that the performance of these loans has not differed significantly from that of standard income-verified loans, including through a period of economic stress.  Interest only:  Table 27 | Download Table   68. For lending types where we consider interest only to be the market standard for that product (for example, BTL loans or for loans made as part of a high net worth/private banking relationship), interest-only adjustments do not apply.  Geographic concentration:  Table 28 | Download Table   Non-residential use loans and non residential borrowers:  69. Adjustments are applied to assets where we consider the primary use of the property to be non-residential. Examples of non-residential assets use include holiday rental BTL assets, mixed-use properties, small-scale commercial properties, and bridging loans. The adjustments detailed in table 29 below are intended for use where less than or equal to 40% of the pool are considered to be non-residential use.  Table 29 | Download Table   Reperforming loans:  70. We define a reperforming loan as a loan that has been 90 or more days past due in the three years leading up to the analysis date and is current as of that date.  71. We only apply a reperforming loans adjustment to portfolios with a material component of loans that have been in arrears for 90 or more days within three years of the analysis date. A material component would typically arise in one of two scenarios: first, where distressed loans have been acquired in the whole loan market and are being packaged into a securitization; and second, where a transaction has exhibited significant performance issues, and where loan modifications/capitalisation of arrears has been used extensively as the principal tool curing arrears.  72. When a reperforming arrangement is made, it is typical that a full reassessment of the borrower\\'s affordability capacity is made; we consider this akin to a reunderwriting of the loan. Accordingly, for pools classified as reperforming, we calculated potential future seasoning credit based on the date a loan was last 90 or more days in arrears.  Table 30 | Download Table   73. In addition, as part of the analytical process, we will analyse data from the issuer/servicer on re-default rates stratified by forbearance type. This analysis will be used to calibrate the origination adjustment for such transactions.  Second-lien loan:  Table 31 | Download Table   Treatment of specific loan product types  Equity loan help-to-buy mortgages/shared equity:  74. We calculated LTV based on the loan balance taken out with the mortgage lender and not with the combined LTV considering the loan amounts to both the mortgage lender and the government. However, given the high overall LTV, we consider that refinancing opportunities will be lower for such borrowers in the event of a period or economic stress and as such certain servicing strategies which may prevent default may not be open to such borrowers. Consequently, we apply a product specific adjustment of 1.35 to all help-to-buy loans. We do not apply first-time buyer and payment shock adjustments.  75. Adjustments for other shared equity schemes may differ based on their terms.  76. Given the potential exposure to both default risk and mortality/morbidity risk we will devise the analytical approach on a case-by-case basis. Typically where RIO loans are non-material portions of a portfolio no adjustments will apply.  Weighted average loss severity (WALS):  Table 32 | Download Table   77. On a case-by-case basis, we may increase or decrease the forced sales discount where there is evidence to support that it may be either higher or lower than envisaged using the standard calculation.  Cash Flow Assumptions  78. For revolving stand-alone RMBS structures (that is, structures backed by a pool whose assets change or revolve), the modelling approach aims to reflect the structure after the activation of any \"stop-substitution\" (or early amortization) triggers and applies cash flow stresses from this point. A stop-substitution trigger is an event or situation that halts the substitution of assets in a revolving loan pool. We do not apply this approach to pools supporting covered bonds, even though the assets in these pools may be substituted over time. This is because the starting assumption of the collateral analysis under our covered bond criteria is the default of the issuing bank, as a result of which we do not expect the cover pool to be actively managed (that is, it would become a static pool).  79. This guidance does not include the assumptions used to assess refinancing costs in covered bonds structured with an asset-liability mismatch (such as target asset spreads). Those are described in \" ~~** Covered Bonds Criteria ||LINK_ID:1|| **~~  ,\" published Dec. 9, 2014.  80. In our surveillance of existing ratings, cash flow modelling may show that--under the \\'B\\' stress--a particular tranche will miss interest payments or fail to repay the principal by or before the final legal maturity date. If this is the case, then our initial assessment under this guidance, all factors remaining the same, may be to consider lowering the rating on those securities to \\'B-\\' or lower.  81. Depending on our view of a transaction\\'s immediate cash flow position, the rating could move into the \\'CCC\\', \\'CC\\', or \\'C\\' category, consistent with Appendix III of \" ~~** Understanding S&P Global Ratings\\' Rating Definitions ||LINK_ID:1|| **~~  ,\" published June 3, 2009, and in accordance with \" ~~** Criteria For Assigning \\'CCC+\\', \\'CCC\\', \\'CCC-\\', And \\'CC\\' Ratings ||LINK_ID:2|| **~~  ,\" published Oct. 1, 2012.  Defaults  82. Under the guidance, the cumulative amount of defaults for cash flow modelling is equal to the pool\\'s WAFF, whereas the cumulative amount of recoveries is calculated as (1 – WALS).  83. We typically model two default-timing scenarios, referred to as \"front-loaded\" (that is, concentrated toward the earlier stage of a transaction) and \"back-loaded\" (that is, concentrated toward the later stage of a transaction). In both scenarios, the defaults occurring over a six-year period, with each scenario having a peak level of stress, referred to as a recession, where 25% of the expected WAFF is applied annually for three years. For the front-loaded curve, the recession starts at day one; and for the back-loaded curve, the recession starts at the start of the fourth year. We typically run the default timing curves detailed in table 33 below. For certain structure types (for example, master trust transactions), we may run additional analysis to test the sensitivity liability structures to different default timing curves.  Table 33 | Download Table   84. The loss severity estimates used in the cash flow modelling are based on the loan principal and assume no recovery of interest accrued on the mortgage loans during the foreclosure period.  Delinquency  85. We assume a delay of a proportion of scheduled interest and principal receipts equal to one-third of the WAFF in each of the first 18 months of a hypothetical recession, and set full recovery of the arrears to take place 36 months after the delinquency occurs. The cash flow stress for delinquencies is independent of the arrears adjustment to the WAFF.  86. For pools that contain residential loans with an option to temporarily suspend the periodic payments (payment holiday loans), the guidance includes a delay of a proportion of scheduled interest and principal receipts. The proportion is based on historical data on borrowers that have exercised such an option, assuming they use this option for the maximum duration permitted under the contractual terms of their loans.  Interest, prepayment, and reinvestment rates  Interest rate risk:  87. The guidance typically applies two different interest rate scenarios, up and down for modelling purposes. See \" ~~** Credit Rating Model: CIR Model ||LINK_ID:1|| **~~  ,\" published Feb. 3, 2017 (see Related Research). The curves vary by stress scenario. In case a transaction features an interest rate cap, either on the note coupon or a cap agreement with an external counterparty, upward interest rate stress assumptions exceeding the cap level may be unduly beneficial for the transaction\\'s cash flow projection. In such event, we may apply a different interest rate stress to test sensitivity of the ratings to the absence of the cap.  Basis risk:  88. The guidance adjusts the interest spread modelled between assets and liabilities by applying different spreads over the life of a transaction as a haircut to the margin. The size of the spreads depends on the distribution of historical differences among indices, using the rating-specific values corresponding to the percentiles shown table 34 below.  89. The guidance applies the stress corresponding to a rating level in the cash flow analysis for the first 18 months of a hypothetical recession. Both before and after this 18-month period, the \\'B\\' percentile from the table below applies at all rating levels.  Table 34 | Download Table   90. For loans linked to a standard variable rate (SVR), or a similar discretionary rate, in our cash flow analysis, we will assume that the SVR has a margin relative to a variable index. To account for the fact that the lender may change the SVR at any time and for a variety of reasons (for example, changes in the cost of funding or to retain borrowers), we apply rating category-specific haircuts to the assumed margin. The margin after the haircut stress is modelled for the life of the transaction.  91. Factors that would determine which end of the range is used in the analysis will be determined on a case-by-case basis and will include the rate setting procedures and SVR history for specific lender(s) in the transaction.  Table 35 | Download Table   Prepayment scenarios:  92. When analysing the payment structure and cash flow mechanics of U.K RMBS, we typically test the transaction\\'s ability to withstand high and low prepayment scenarios as set out in table 36.  93. We may adjust the prepayment assumptions if a pool\\'s historical prepayment rates were higher than historical country averages or if a transaction were particularly sensitive to prepayment risk (for example, excess spread notes). We may also reduce high prepayment stress in situations where long-term historical data support lower prepayment rate assumptions for a specific loan product.  Table 36 | Download Table   Reinvestment rates:  94. Reinvestment rate assumptions stress the yield from excess cash that becomes available because of, for example, prepayments; as well as the revenues associated with any other cash the issuer holds. The guidance applies different reinvestment rate assumptions to covered bonds with a bullet maturity structure on the one hand, and to RMBS transactions and covered bonds with a pass-through maturity structure on the other (see table 37 below). This differentiation reflects that, for a structure with bullet maturities, we generally expect a longer tenor of reinvestment than for a pass-through structure. The guidance applies a floor to the reinvestment rate of 0%.  Table 37 | Download Table   Modelling Of Senior Fees And Expenses, Liquidity Facilities, And Spread Compression  95. The modelling of all the issuer\\'s foreseeable expenses uses stressed costs to reflect the need to replace the initial service provider.  96. The most significant of these is the cost of servicing mortgage loan receivables. We typically model the follow following servicing fees. If actual fees are higher, we will model the higher amount.  Table 38 | Download Table   97. In situations where there is a contracted back-up servicer in place, we may adjust the assumptions in table 38. In addition, in situations where fees are linked directly to specific activities, such as issuing letters, or where increases are linked to inflation, we may apply different assumptions from those detailed in table 38 above.  98. We include estimates of marginal costs from liquidity facilities in cash flow modelling. Most liquidity facilities are renewable after 364 days, subject to a commitment fee and a drawn fee. We assume that the liquidity facility is fully drawn on day one of the analysis. The drawn fee is typically modelled as being payable from this point on the whole facility balance.  99. We also model the possibility that the spread on the loan pool compresses over time, due to defaults, prepayments, and product switches. To reflect this, we reduce margins with the assumption that a percentage of the higher-yielding loans exit the portfolio. In addition, for RMBS, the transaction may feature contractual minimum yield levels, for example, to allow substitutions to continue in revolving pools. In such a case, the modelling approach aims to capture any breach of the yield levels that triggers the end of the revolving period and produces lower yields.  100. Because of the specific nature of master trust structures, there are additional stresses in the cash flow analysis to reflect further testing of credit, liquidity, and recessionary timing.  Treatment Of Flexible Mortgage Loans  101. Flexible loans enable borrowers to pay down their mortgage balance when they have surplus cash and redraw at a later stage. Not all flexible loan products have the same features. Some are linked to current accounts allowing a borrower to have payment holidays or to offset interest income due on savings against mortgage interest payments, without incurring any adjustment. Others allow a borrower to draw on the loan up to a pre-underwritten LTV ratio, or repay principal subject to certain conditions and thresholds that limit the amount that can be redrawn.  102. If a borrower requests further drawing on a flexible loan that is not part of an RMBS transaction, the lender may use its available funds to grant that request.  103. A securitization vehicle, however, has fewer means to fund redraws and may require a committed redraw facility. For this reason, when analyzing RMBS transactions that contain flexible loan products, we assess whether the risk that the issuer may need to fund redraws is adequately addressed through our review of the legal framework, the structure, and our cash flow stresses.  104. More specifically, when a loan is securitized, we assess whether the new owner of the loan--the issuing securitization vehicle (or issuer)--is legally obliged to advance the redraw funds or if the obligation remains with the originator. If the obligation does not pass to the new owner, as in master trust structures, we estimate an amount that covers a borrower\\'s potential claim if the loan originator is insolvent and unable to fund the redraw. This amount is typically the marginal cost of the borrower finding alternative funding, multiplied by the amount of potential redraw requests.  105. If we conclude that the obligation to fund redraws would rest with the issuer (after an insolvency of the originator), we apply the appropriate cash flow stresses in our rating analysis.  106. Typically, an issuer would seek to fund redraws from repayments of principal; so, provided it has received sufficient principal repayments to cover redraw requests, there is no risk. Therefore, the main stress for modelling redraw risk is that the constant prepayment rate (CPR) is so low that there is insufficient repaid principal available to fund redraw requests, leading to a risk that the borrower may make a claim against the issuer for the cost of obtaining alternate financing.  107. Our redraw stress scenarios center on simulating a period of buoyant economic growth, during which we assume that a proportion of borrowers have paid down their mortgage loans with surplus cash. This period is typically one year and is followed by a rapid economic decline (featuring, among other things, higher interest rates, unemployment, declining house prices, and rental voids), during which a proportion of borrowers who had paid down their loans would seek redraws.  108. In assessing the magnitude of stresses--or the proportion of borrowers that would redraw--we consider, among other things, current redraw exposure, redraw history, seasoning of the mortgage pool, leverage of the pool, and fees payable upon redraw requests.  RELATED CRITERIA  ~~** Global Methodology And Assumptions: Assessing Pools Of Residential Loans ||LINK_ID:1|| **~~  , Jan. 25, 2019 ~~** Global Framework For Cash Flow Analysis Of Structured Finance Securities ||LINK_ID:2|| **~~  , Oct. 9, 2014  This report does not constitute a rating action.  This article is a guidance document for Criteria (Guidance Document). Guidance Documents are not Criteria, as they do not establish a methodological framework for determining Credit Ratings. Guidance Documents provide guidance on various matters, including: articulating how we may apply specific aspects of Criteria; describing variables or considerations related to Criteria that may change over time; providing additional information on non-fundamental factors that our analysts may consider in the application of Criteria; and/or providing additional guidance on the exercise of analytical judgment under our Criteria. Our analysts consider Guidance Documents as they apply Criteria and exercise analytical judgment in the analysis and determination of Credit Ratings. However, in applying Criteria and the exercise of analytic judgment to a specific issuer or issue, analysts may determine that it is suitable to follow an approach that differs from one described in the Guidance Document. Where appropriate, the rating rationale will highlight that a different approach was taken.    No content (including ratings, credit-related analyses and data, valuations, model, software or other application or output therefrom) or any part thereof (Content) may be modified, reverse engineered, reproduced or distributed in any form by any means, or stored in a database or retrieval system, without the prior written permission of S&P Global Market Intelligence or its affiliates (collectively, S&P Global). The Content shall not be used for any unlawful or unauthorized purposes. S&P Global and any third-party providers, as well as their directors, officers, shareholders, employees or agents (collectively S&P Global Parties) do not guarantee the accuracy, completeness, timeliness or availability of the Content. S&P Global Parties are not responsible for any errors or omissions (negligent or otherwise), regardless of the cause, for the results obtained from the use of the Content, or for the security or maintenance of any data input by the user. The Content is provided on an “as is” basis. S&P GLOBAL PARTIES DISCLAIM ANY AND ALL EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, ANY WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE OR USE, FREEDOM FROM BUGS, SOFTWARE ERRORS OR DEFECTS, THAT THE CONTENT’S FUNCTIONING WILL BE UNINTERRUPTED OR THAT THE CONTENT WILL OPERATE WITH ANY SOFTWARE OR HARDWARE CONFIGURATION.  In no event shall S&P Global Parties be liable to any party for any direct, indirect, incidental, exemplary, compensatory, punitive, special or consequential damages, costs, expenses, legal fees, or losses (including, without limitation, lost income or lost profits and opportunity costs or losses caused by negligence) in connection with any use of the Content even if advised of the possibility of such damages. Credit-related and other analyses, including ratings, and statements in the Content are statements of opinion as of the date they are expressed and not statements of fact. S&P Global Market Intelligence’s opinions, analyses and rating acknowledgment decisions (described below) are not recommendations to purchase, hold, or sell any securities or to make any investment decisions, and do not address the suitability of any security. S&P Global Market Intelligence assumes no obligation to update the Content following publication in any form or format. The Content should not be relied on and is not a substitute for the skill, judgment and experience of the user, its management, employees, advisors and/or clients when making investment and other business decisions. S&P Global Market Intelligence does not act as a fiduciary or an investment advisor except where registered as such.  While S&P Global Market Intelligence has obtained information from sources it believes to be reliable, S&P Global Market Intelligence does not perform an audit and undertakes no duty of due diligence or independent verification of any information it receives. Rating-related publications may be published for a variety of reasons that are not necessarily dependent on action by rating committees, including, but not limited to, the publication of a periodic update on a credit rating and related analyses. S&P Global keeps certain activities of its divisions separate from each other in order to preserve the independence and objectivity of their respective activities. As a result, certain divisions of S&P Global may have information that is not available to other S&P Global divisions. S&P Global has established policies and procedures to maintain the confidentiality of certain non-public information received in connection with each analytical process. S&P Global Ratings does not contribute to or participate in the creation of credit scores generated by S&P Global Market Intelligence. Lowercase nomenclature is used to differentiate S&P Global Market Intelligence PD credit model scores from the credit ratings issued by S&P Global Ratings. S&P Global may receive compensation for its ratings and certain analyses, normally from issuers or underwriters of securities or from obligors. S&P Global reserves the right to disseminate its opinions and analyses. S&P Global\\'s public ratings and analyses are made available on its Web sites, www.standardandpoors.com (free of charge), and www.capitaliq.com (subscription), and may be distributed through other means, including via S&P Global publications and third-party redistributors. Additional information about our ratings fees is available at www.standardandpoors.com/usratingsfees .',\n",
       " 'refs': [{'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=10808991&ArtRevId=1&sid=&sind=A&',\n",
       "   'LINK_TEXT': 'Global Methodology And Assumptions: Assessing Pools Of Residential Loans',\n",
       "   'ART_OBJECTID': '10808991'},\n",
       "  {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=8759141&ArtRevId=8&sid=&sind=A&',\n",
       "   'LINK_TEXT': 'Global Framework For Cash Flow Analysis Of Structured Finance Securities',\n",
       "   'ART_OBJECTID': '8759141'},\n",
       "  {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=8923943&ArtRevId=11&sid=&sind=A&',\n",
       "   'LINK_TEXT': 'Covered Bonds Criteria',\n",
       "   'ART_OBJECTID': '8923943'},\n",
       "  {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=5435305&ArtRevId=15&sid=&sind=A&',\n",
       "   'LINK_TEXT': \"Understanding S&P Global Ratings' Rating Definitions\",\n",
       "   'ART_OBJECTID': '5435305'},\n",
       "  {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=7554329&ArtRevId=11&sid=&sind=A&',\n",
       "   'LINK_TEXT': \"Criteria For Assigning 'CCC+', 'CCC', 'CCC-', And 'CC' Ratings\",\n",
       "   'ART_OBJECTID': '7554329'},\n",
       "  {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=9969033&ArtRevId=1&sid=&sind=A&',\n",
       "   'LINK_TEXT': 'Credit Rating Model: CIR Model',\n",
       "   'ART_OBJECTID': '9969033'},\n",
       "  {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=10808991&ArtRevId=1&sid=&sind=A&',\n",
       "   'LINK_TEXT': 'Global Methodology And Assumptions: Assessing Pools Of Residential Loans',\n",
       "   'ART_OBJECTID': '10808991'},\n",
       "  {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=8759141&ArtRevId=7&sid=&sind=A&',\n",
       "   'LINK_TEXT': 'Global Framework For Cash Flow Analysis Of Structured Finance Securities',\n",
       "   'ART_OBJECTID': '8759141'}]}"
      ]
     },
     "execution_count": 233,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['10809618']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "6a3d7a09afac410cbced69d551c45ba3",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=2273), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "\n",
    "for key, content in tqdm(data.items()):\n",
    "    doc = nlp(content['content'])\n",
    "\n",
    "    # all tokens that arent stop words or punctuations\n",
    "    words = [token.lemma_.lower() for token in doc if token.is_stop != True and token.is_punct != True]\n",
    "\n",
    "    # noun tokens that arent stop words or punctuations\n",
    "    nouns = [token.lemma_.lower() for token in doc if token.is_stop != True and token.is_punct != True and token.pos_ == \"NOUN\"]\n",
    "\n",
    "    # five most common tokens\n",
    "    word_freq = Counter(words)\n",
    "    tf_words[key] += word_freq\n",
    "    master_word_counter += word_freq\n",
    "    for w in word_freq:\n",
    "        word_idf[w] += 1\n",
    "#     common_words = word_freq.most_common(5)\n",
    "\n",
    "    # five most common noun tokens\n",
    "    noun_freq = Counter(nouns)\n",
    "    tf_nouns[key] += noun_freq\n",
    "    master_noun_counter += noun_freq\n",
    "    for n in noun_freq:\n",
    "        noun_idf[n] += 1\n",
    "#     common_nouns = noun_freq.most_common(5)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('rating', 66868),\n",
       " ('risk', 61096),\n",
       " ('credit', 44781),\n",
       " ('criterion', 36904),\n",
       " ('loan', 36639),\n",
       " ('asset', 33352),\n",
       " ('company', 32817),\n",
       " ('debt', 29241),\n",
       " ('transaction', 29044),\n",
       " ('market', 28185),\n",
       " ('analysis', 25716),\n",
       " ('rate', 24954),\n",
       " ('table', 23823),\n",
       " ('cash', 22586),\n",
       " ('interest', 21567),\n",
       " ('level', 21439),\n",
       " ('capital', 20960),\n",
       " ('default', 20749),\n",
       " ('security', 20733),\n",
       " ('loss', 19718),\n",
       " ('payment', 19133),\n",
       " ('factor', 19019),\n",
       " ('assessment', 17710),\n",
       " ('issuer', 17409),\n",
       " ('term', 16108),\n",
       " ('flow', 15937),\n",
       " ('business', 14942),\n",
       " ('case', 14548),\n",
       " ('pool', 14325),\n",
       " ('obligation', 13955),\n",
       " ('year', 13695),\n",
       " ('mortgage', 13669),\n",
       " ('value', 13646),\n",
       " ('investment', 13502),\n",
       " ('bank', 13443),\n",
       " ('time', 13361),\n",
       " ('cost', 12676),\n",
       " ('fund', 12479),\n",
       " ('property', 12253),\n",
       " ('management', 12134),\n",
       " ('example', 12096),\n",
       " ('industry', 11940),\n",
       " ('issue', 11892),\n",
       " ('assumption', 11826),\n",
       " ('bond', 11272),\n",
       " ('entity', 11161),\n",
       " ('liquidity', 11152),\n",
       " ('adjustment', 10782),\n",
       " ('period', 10730),\n",
       " ('portfolio', 10695),\n",
       " ('structure', 10298),\n",
       " ('methodology', 10253),\n",
       " ('stress', 10180),\n",
       " ('information', 10134),\n",
       " ('government', 10020),\n",
       " ('change', 9919),\n",
       " ('article', 9904),\n",
       " ('service', 9837),\n",
       " ('recovery', 9723),\n",
       " ('performance', 9491),\n",
       " ('borrower', 9264),\n",
       " ('country', 9181),\n",
       " ('equity', 9032),\n",
       " ('ratio', 8979),\n",
       " ('result', 8783),\n",
       " ('opinion', 8758),\n",
       " ('group', 8523),\n",
       " ('insurance', 8515),\n",
       " ('revenue', 8492),\n",
       " ('party', 8411),\n",
       " ('datum', 8325),\n",
       " ('insurer', 8311),\n",
       " ('model', 8300),\n",
       " ('quality', 8262),\n",
       " ('project', 8109),\n",
       " ('liability', 8108),\n",
       " ('income', 8090),\n",
       " ('category', 7976),\n",
       " ('scenario', 7892),\n",
       " ('exposure', 7820),\n",
       " ('counterparty', 7797),\n",
       " ('event', 7770),\n",
       " ('note', 7724),\n",
       " ('support', 7590),\n",
       " ('policy', 7547),\n",
       " ('condition', 7543),\n",
       " ('price', 7462),\n",
       " ('type', 7372),\n",
       " ('product', 7167),\n",
       " ('date', 7132),\n",
       " ('basis', 6915),\n",
       " ('account', 6911),\n",
       " ('balance', 6882),\n",
       " ('paragraph', 6860),\n",
       " ('sector', 6813),\n",
       " ('score', 6803),\n",
       " ('profile', 6667),\n",
       " ('charge', 6618),\n",
       " ('position', 6586),\n",
       " ('view', 6509)]"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "master_noun_counter.most_common(100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10428"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(master_noun_counter)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "# noun_idf['risk']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "_word_counter = list(master_noun_counter.most_common())\n",
    "_noun_idf = list(noun_idf.most_common())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import pickle\n",
    "# with open('./data/word_counter.pkl', 'wb') as f:\n",
    "#     pickle.dump((_word_counter, tf_nouns, master_noun_counter, tf_words, master_word_counter, word_idf, noun_idf), f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import pickle\n",
    "# with open('./data/cvlbow_lemma.pkl', 'wb') as f:\n",
    "#     pickle.dump((master_noun_counter, master_word_counter, tf_nouns, tf_words, noun_idf, word_idf), f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "any(ch in string.punctuation for ch in _noun_idf[4][0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3270"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(list(filter(lambda x: x[1] >= 10 and not any(ch in string.punctuation+\"0123456789\" for ch in x[0]), _noun_idf)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAX0AAAD4CAYAAAAAczaOAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjEsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy8QZhcZAAAQQklEQVR4nO3df6zddX3H8edroGz+yCija7Cta+e6LXWJQG4Qo1mYTH4uKyaLgSzSGJL6B2S4mCzF/YHTkGHij0niyFA6YXEwpjgabWS1IzH+AfbiCFCQcUUYbQqtQ9HNREXf++N86s7Kvb2/zr23PZ/nIzk53+/7+/me8/nk27y+3/s533OaqkKS1IdfWukOSJKWj6EvSR0x9CWpI4a+JHXE0Jekjpy80h04ltNPP702bNiw0t2QpBPKgw8++N2qWj3dtuM69Dds2MDk5ORKd0OSTihJnplpm9M7ktQRQ1+SOmLoS1JHDH1J6oihL0kdMfQlqSOGviR1xNCXpI4Y+pLUkeP6G7mjtGH7l3+x/PSNl65gTyRp5XilL0kdMfQlqSOGviR1xNCXpI4Y+pLUkVlDP8n6JPcleSzJviTXtvoHkxxI8lB7XDK0z3VJppI8keTCofpFrTaVZPvSDEmSNJO53LL5EvD+qvpmktcCDybZ3bZ9oqo+Otw4yWbgcuCNwOuAryb57bb5U8A7gP3A3iQ7q+qxUQxEkjS7WUO/qg4CB9vyD5M8Dqw9xi5bgDur6sfAd5JMAee0bVNV9RRAkjtbW0NfkpbJvOb0k2wAzgIeaKVrkjycZEeSVa22Fnh2aLf9rTZTXZK0TOYc+kleA3wBeF9V/QC4GXgDcCaDvwQ+NooOJdmWZDLJ5OHDh0fxkpKkZk6hn+QVDAL/c1V1N0BVPV9VP6uqnwOf5v+mcA4A64d2X9dqM9X/n6q6paomqmpi9epp/zN3SdICzeXunQC3Ao9X1ceH6mcMNXsn8Ghb3glcnuSUJBuBTcA3gL3ApiQbk7ySwYe9O0czDEnSXMzl7p23Au8GHknyUKt9ALgiyZlAAU8D7wWoqn1J7mLwAe1LwNVV9TOAJNcA9wInATuqat8IxyJJmsVc7t75OpBpNu06xj43ADdMU991rP0kSUvLb+RKUkcMfUnqiKEvSR0x9CWpI4a+JHXE0Jekjhj6ktQRQ1+SOmLoS1JHDH1J6oihL0kdMfQlqSOGviR1xNCXpI4Y+pLUEUNfkjpi6EtSRwx9SeqIoS9JHTH0Jakjhr4kdcTQl6SOGPqS1BFDX5I6YuhLUkcMfUnqiKEvSR0x9CWpI4a+JHXE0Jekjhj6ktSRWUM/yfok9yV5LMm+JNe2+mlJdid5sj2vavUkuSnJVJKHk5w99FpbW/snk2xdumFJkqYzlyv9l4D3V9Vm4Fzg6iSbge3AnqraBOxp6wAXA5vaYxtwMwxOEsD1wJuBc4Drj5woJEnLY9bQr6qDVfXNtvxD4HFgLbAFuK01uw24rC1vAW6vgfuBU5OcAVwI7K6qF6rqe8Bu4KKRjkaSdEzzmtNPsgE4C3gAWFNVB9um54A1bXkt8OzQbvtbbab60e+xLclkksnDhw/Pp3uSpFnMOfSTvAb4AvC+qvrB8LaqKqBG0aGquqWqJqpqYvXq1aN4SUlSM6fQT/IKBoH/uaq6u5Wfb9M2tOdDrX4AWD+0+7pWm6kuSVomc7l7J8CtwONV9fGhTTuBI3fgbAXuGapf2e7iORd4sU0D3QtckGRV+wD3glaTJC2Tk+fQ5q3Au4FHkjzUah8AbgTuSnIV8AzwrrZtF3AJMAX8CHgPQFW9kOTDwN7W7kNV9cJIRiFJmpNZQ7+qvg5khs3nT9O+gKtneK0dwI75dFCSNDp+I1eSOmLoS1JHDH1J6oihL0kdMfQlqSOGviR1xNCXpI4Y+pLUEUNfkjpi6EtSRwx9SeqIoS9JHTH0Jakjhr4kdcTQl6SOGPqS1BFDX5I6YuhLUkcMfUnqiKEvSR0x9CWpI4a+JHXE0Jekjhj6ktQRQ1+SOmLoS1JHDH1J6oihL0kdMfQlqSOGviR1ZNbQT7IjyaEkjw7VPpjkQJKH2uOSoW3XJZlK8kSSC4fqF7XaVJLtox+KJGk2c7nS/yxw0TT1T1TVme2xCyDJZuBy4I1tn79NclKSk4BPARcDm4ErWltJ0jI6ebYGVfW1JBvm+HpbgDur6sfAd5JMAee0bVNV9RRAkjtb28fm3WNJ0oItZk7/miQPt+mfVa22Fnh2qM3+Vpup/jJJtiWZTDJ5+PDhRXRPknS0hYb+zcAbgDOBg8DHRtWhqrqlqiaqamL16tWjellJEnOY3plOVT1/ZDnJp4EvtdUDwPqhputajWPUJUnLZEFX+knOGFp9J3Dkzp6dwOVJTkmyEdgEfAPYC2xKsjHJKxl82Ltz4d2WJC3ErFf6Se4AzgNOT7IfuB44L8mZQAFPA+8FqKp9Se5i8AHtS8DVVfWz9jrXAPcCJwE7qmrfyEcjSTqmudy9c8U05VuP0f4G4IZp6ruAXfPqnSRppPxGriR1xNCXpI4Y+pLUEUNfkjpi6EtSRwx9SerIgr6Re6LbsP3Lv1h++sZLV7AnkrS8vNKXpI4Y+pLUEUNfkjpi6EtSRwx9SeqIoS9JHTH0Jakjhr4kdcTQl6SOGPqS1BFDX5I6YuhLUkcMfUnqiKEvSR0x9CWpI4a+JHXE0Jekjhj6ktQRQ1+SOmLoS1JHDH1J6oihL0kdMfQlqSOGviR1ZNbQT7IjyaEkjw7VTkuyO8mT7XlVqyfJTUmmkjyc5Oyhfba29k8m2bo0w5EkHctcrvQ/C1x0VG07sKeqNgF72jrAxcCm9tgG3AyDkwRwPfBm4Bzg+iMnCknS8pk19Kvqa8ALR5W3ALe15duAy4bqt9fA/cCpSc4ALgR2V9ULVfU9YDcvP5FIkpbYQuf011TVwbb8HLCmLa8Fnh1qt7/VZqq/TJJtSSaTTB4+fHiB3ZMkTWfRH+RWVQE1gr4ceb1bqmqiqiZWr149qpeVJLHw0H++TdvQng+1+gFg/VC7da02U12StIwWGvo7gSN34GwF7hmqX9nu4jkXeLFNA90LXJBkVfsA94JWkyQto5Nna5DkDuA84PQk+xnchXMjcFeSq4BngHe15ruAS4Ap4EfAewCq6oUkHwb2tnYfqqqjPxyWJC2xWUO/qq6YYdP507Qt4OoZXmcHsGNevZMkjZTfyJWkjhj6ktQRQ1+SOmLoS1JHDH1J6oihL0kdMfQlqSOGviR1xNCXpI4Y+pLUEUNfkjpi6EtSRwx9SeqIoS9JHTH0Jakjhr4kdcTQl6SOGPqS1JFZ/7vEcbdh+5d/sfz0jZeuYE8kael5pS9JHTH0Jakjhr4kdcTQl6SOGPqS1BFDX5I6YuhLUkcMfUnqiKEvSR0x9CWpI4a+JHVkUaGf5OkkjyR5KMlkq52WZHeSJ9vzqlZPkpuSTCV5OMnZoxiAJGnuRnGl/wdVdWZVTbT17cCeqtoE7GnrABcDm9pjG3DzCN5bkjQPSzG9swW4rS3fBlw2VL+9Bu4HTk1yxhK8vyRpBov9aeUC/jVJAX9XVbcAa6rqYNv+HLCmLa8Fnh3ad3+rHRyqkWQbg78EeP3rX7/I7s2PP7MsadwtNvTfVlUHkvw6sDvJt4Y3VlW1E8KctRPHLQATExPz2leSdGyLmt6pqgPt+RDwReAc4Pkj0zbt+VBrfgBYP7T7ulaTJC2TBYd+klcnee2RZeAC4FFgJ7C1NdsK3NOWdwJXtrt4zgVeHJoGkiQtg8VM76wBvpjkyOv8Y1V9Jcle4K4kVwHPAO9q7XcBlwBTwI+A9yzivSVJC7Dg0K+qp4A3TVP/L+D8aeoFXL3Q95MkLZ7fyJWkjhj6ktQRQ1+SOmLoS1JHDH1J6oihL0kdWezPMIwtf4dH0jjySl+SOmLoS1JHDH1J6oihL0kdMfQlqSPevTMH3skjaVx4pS9JHTH0JakjTu/Mk1M9kk5kXulLUkcMfUnqiNM7i+BUj6QTjVf6ktQRQ1+SOmLoS1JHnNMfEef3JZ0IDP0l4AlA0vHK0F9ingAkHU+c05ekjnilv4yGr/qH+ReApOVi6B8HnAKStFwM/ePMTCeAudSP3iZJR0tVrXQfZjQxMVGTk5Mjea2ZplZ6NJeTiaQTV5IHq2pium1e6XfIE6DUr2UP/SQXAZ8ETgI+U1U3LncfNL2lPhkca1pqtvaSRmNZQz/JScCngHcA+4G9SXZW1WPL2Q+tjPmeVEZ5ElrqE858P4uZ72vO1LeF3BE23z45BThelnVOP8lbgA9W1YVt/TqAqvrr6do7py9plOZ78l9JiznBHk9z+muBZ4fW9wNvHm6QZBuwra3+d5InFvF+pwPfXcT+J6LextzbeMExL1g+MoKeLJN8ZFFj/o2ZNhx3H+RW1S3ALaN4rSSTM53txlVvY+5tvOCYe7FUY17un2E4AKwfWl/XapKkZbDcob8X2JRkY5JXApcDO5e5D5LUrWWd3qmql5JcA9zL4JbNHVW1bwnfciTTRCeY3sbc23jBMfdiScZ8XH8jV5I0Wv60siR1xNCXpI6MZegnuSjJE0mmkmxf6f4shSTrk9yX5LEk+5Jc2+qnJdmd5Mn2vGql+zpqSU5K8u9JvtTWNyZ5oB3vf2o3CYyNJKcm+XySbyV5PMlbxv04J/nz9u/60SR3JPnlcTvOSXYkOZTk0aHatMc1Aze1sT+c5OyFvu/Yhf7QTz1cDGwGrkiyeWV7tSReAt5fVZuBc4Gr2zi3A3uqahOwp62Pm2uBx4fWPwJ8oqp+C/gecNWK9GrpfBL4SlX9LvAmBmMf2+OcZC3wZ8BEVf0eg5s+Lmf8jvNngYuOqs10XC8GNrXHNuDmhb7p2IU+cA4wVVVPVdVPgDuBLSvcp5GrqoNV9c22/EMGQbCWwVhva81uAy5bmR4ujSTrgEuBz7T1AG8HPt+ajNWYk/wq8PvArQBV9ZOq+j5jfpwZ3Fn4K0lOBl4FHGTMjnNVfQ144ajyTMd1C3B7DdwPnJrkjIW87ziG/nQ/9bB2hfqyLJJsAM4CHgDWVNXBtuk5YM0KdWup/A3wF8DP2/qvAd+vqpfa+rgd743AYeDv25TWZ5K8mjE+zlV1APgo8J8Mwv5F4EHG+zgfMdNxHVmujWPodyXJa4AvAO+rqh8Mb6vB/bhjc09ukj8CDlXVgyvdl2V0MnA2cHNVnQX8D0dN5YzhcV7F4Mp2I/A64NW8fBpk7C3VcR3H0O/mpx6SvIJB4H+uqu5u5eeP/NnXng+tVP+WwFuBP07yNINpu7czmO8+tU0DwPgd7/3A/qp6oK1/nsFJYJyP8x8C36mqw1X1U+BuBsd+nI/zETMd15Hl2jiGfhc/9dDmsm8FHq+qjw9t2glsbctbgXuWu29Lpaquq6p1VbWBwXH9t6r6U+A+4E9as3Eb83PAs0l+p5XOBx5jjI8zg2mdc5O8qv07PzLmsT3OQ2Y6rjuBK9tdPOcCLw5NA81PVY3dA7gE+A/g28BfrnR/lmiMb2Pwp9/DwEPtcQmDOe49wJPAV4HTVrqvSzT+84AvteXfBL4BTAH/DJyy0v0b8VjPBCbbsf4XYNW4H2fgr4BvAY8C/wCcMm7HGbiDwWcWP2XwF91VMx1XIAzuSvw28AiDO5sW9L7+DIMkdWQcp3ckSTMw9CWpI4a+JHXE0Jekjhj6ktQRQ1+SOmLoS1JH/heyjGwwuZxbnQAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "from matplotlib import pyplot as plt\n",
    "plt.hist([v for (_, v ) in _word_counter], bins=100, range=(0, 100))\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3155"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len([v for (_, v ) in _word_counter if v >= 20])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# list(filter(lambda x: x[1] >= 10, _noun_idf))[-150:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "filterd_nouns = list(filter(lambda x: x[1] >= 10 and not any(ch in string.punctuation+\"0123456789\" for ch in x[0]), _noun_idf))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3270"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(filterd_nouns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "def tfidf(tf, df):\n",
    "    if tf == 0:\n",
    "        return 0.\n",
    "    tf_term = 1 + math.log(tf)\n",
    "    idf_term = math.log(1 + 2273. / df)\n",
    "    return tf_term * idf_term"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2231"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# noun_idf.keys()\n",
    "noun_idf[filterd_nouns[0][0]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d52e7ab2808e4b11a156789b6ab79d1f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=2273), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "pd_data = []\n",
    "\n",
    "for fname in tqdm(tf_nouns):\n",
    "    nf = tf_nouns[fname]\n",
    "    title = data[fname]['title']\n",
    "    jdata = [('ARTICLE_ID', fname), ('TITLE', title)] + [(word[0], tfidf(tf_nouns[fname][word[0]], noun_idf[word[0]])) for word in filterd_nouns] \n",
    "    jdata += [('t_dom',data[fname]['top_domain']), ('b_dom', data[fname]['bottom_domain'])]\n",
    "    pd_data.append(OrderedDict(jdata))\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "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>ARTICLE_ID</th>\n",
       "      <th>TITLE</th>\n",
       "      <th>rating</th>\n",
       "      <th>credit</th>\n",
       "      <th>criterion</th>\n",
       "      <th>risk</th>\n",
       "      <th>result</th>\n",
       "      <th>market</th>\n",
       "      <th>article</th>\n",
       "      <th>issuer</th>\n",
       "      <th>...</th>\n",
       "      <th>epidemic</th>\n",
       "      <th>q</th>\n",
       "      <th>tour</th>\n",
       "      <th>defaulter</th>\n",
       "      <th>severance</th>\n",
       "      <th>gallon</th>\n",
       "      <th>mercy</th>\n",
       "      <th>jump</th>\n",
       "      <th>t_dom</th>\n",
       "      <th>b_dom</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18350465</td>\n",
       "      <td>Framework For Credit Analysis In European CMBS...</td>\n",
       "      <td>2.448203</td>\n",
       "      <td>2.853287</td>\n",
       "      <td>1.503871</td>\n",
       "      <td>2.907029</td>\n",
       "      <td>1.554576</td>\n",
       "      <td>1.776164</td>\n",
       "      <td>1.562573</td>\n",
       "      <td>1.952970</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Structured Finance</td>\n",
       "      <td>CMBS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>27198722</td>\n",
       "      <td>Request For Comment: Key Credit Factors For Po...</td>\n",
       "      <td>1.833172</td>\n",
       "      <td>2.615916</td>\n",
       "      <td>3.444467</td>\n",
       "      <td>4.061472</td>\n",
       "      <td>2.068035</td>\n",
       "      <td>4.101837</td>\n",
       "      <td>0.744574</td>\n",
       "      <td>1.267195</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Corporates</td>\n",
       "      <td>Request for Comment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>35464774</td>\n",
       "      <td>Request For Comment: Foreign Exchange Risk In ...</td>\n",
       "      <td>2.504434</td>\n",
       "      <td>1.683458</td>\n",
       "      <td>2.898316</td>\n",
       "      <td>2.825056</td>\n",
       "      <td>1.254222</td>\n",
       "      <td>1.776164</td>\n",
       "      <td>2.193449</td>\n",
       "      <td>0.748426</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Structured Finance</td>\n",
       "      <td>Request for Comment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18570420</td>\n",
       "      <td>Facing Up to the Rating Challenges of Whole Co...</td>\n",
       "      <td>2.387076</td>\n",
       "      <td>1.840879</td>\n",
       "      <td>1.503871</td>\n",
       "      <td>2.356336</td>\n",
       "      <td>1.554576</td>\n",
       "      <td>2.292087</td>\n",
       "      <td>1.260674</td>\n",
       "      <td>1.785964</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Structured Finance</td>\n",
       "      <td>ABS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18076851</td>\n",
       "      <td>Rating U.S.  Residential Asset-Backed Mortgage...</td>\n",
       "      <td>2.163357</td>\n",
       "      <td>2.782681</td>\n",
       "      <td>2.206736</td>\n",
       "      <td>2.825056</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.045040</td>\n",
       "      <td>0.744574</td>\n",
       "      <td>1.570655</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Structured Finance</td>\n",
       "      <td>ABCP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 3274 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  ARTICLE_ID                                              TITLE    rating  \\\n",
       "0   18350465  Framework For Credit Analysis In European CMBS...  2.448203   \n",
       "1   27198722  Request For Comment: Key Credit Factors For Po...  1.833172   \n",
       "2   35464774  Request For Comment: Foreign Exchange Risk In ...  2.504434   \n",
       "3   18570420  Facing Up to the Rating Challenges of Whole Co...  2.387076   \n",
       "4   18076851  Rating U.S.  Residential Asset-Backed Mortgage...  2.163357   \n",
       "\n",
       "     credit  criterion      risk    result    market   article    issuer  ...  \\\n",
       "0  2.853287   1.503871  2.907029  1.554576  1.776164  1.562573  1.952970  ...   \n",
       "1  2.615916   3.444467  4.061472  2.068035  4.101837  0.744574  1.267195  ...   \n",
       "2  1.683458   2.898316  2.825056  1.254222  1.776164  2.193449  0.748426  ...   \n",
       "3  1.840879   1.503871  2.356336  1.554576  2.292087  1.260674  1.785964  ...   \n",
       "4  2.782681   2.206736  2.825056  0.000000  3.045040  0.744574  1.570655  ...   \n",
       "\n",
       "   epidemic    q  tour  defaulter  severance  gallon  mercy  jump  \\\n",
       "0       0.0  0.0   0.0        0.0        0.0     0.0    0.0   0.0   \n",
       "1       0.0  0.0   0.0        0.0        0.0     0.0    0.0   0.0   \n",
       "2       0.0  0.0   0.0        0.0        0.0     0.0    0.0   0.0   \n",
       "3       0.0  0.0   0.0        0.0        0.0     0.0    0.0   0.0   \n",
       "4       0.0  0.0   0.0        0.0        0.0     0.0    0.0   0.0   \n",
       "\n",
       "                t_dom                b_dom  \n",
       "0  Structured Finance                 CMBS  \n",
       "1          Corporates  Request for Comment  \n",
       "2  Structured Finance  Request for Comment  \n",
       "3  Structured Finance                  ABS  \n",
       "4  Structured Finance                 ABCP  \n",
       "\n",
       "[5 rows x 3274 columns]"
      ]
     },
     "execution_count": 235,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(pd_data)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('data/cvl_tfidf_with_title.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [],
   "source": [
    "# LINKING"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2273"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['18350465', '27198722', '35464774', '18570420', '18076851', '18286174', '27211545', '18542947', '18490280', '28699519', '18380511', '18455660', '18373169', '18147644', '26598919', '18567648', '28993151', '18249571', '42573271', '33039745', '18135740', '18419289', '18455637', '38215323', '18528435', '18467316', '18448140', '18185865', '18206219', '18315757', '18105899', '18296070', '18522612', '38006442', '18479488', '39770454', '29134276', '18490412', '18402129', '18448290', '18403368', '36326831', '18387016', '18070751', '41563244', '18171222', '41553758', '39174267', '27043786', '18300113', '18475278', '18332432', '18530382', '18058915', '18283225', '18522645', '18087917', '41854300', '18514262', '18363256', '26778704', '18545514', '27043694', '18417956', '18482614', '29572556', '28890999', '18237342', '18326774', '29998276', '18242757', '18115323', '18517120', '18350408', '18248589', '18405506', '28056886', '41673378', '41426453', '28993612', '18105909', '38994808', '34059134', '18167270', '18223881', '18463695', '18508113', '18490347', '27936861', '18356134', '18169540', '29531374', '18047320', '18160331', '34521561', '18312786', '18379698', '18332330', '18272836', '18157384', '18063940', '19500863', '24974109', '18328947', '43075546', '18377110', '23444783', '18490310', '33927768', '18412781', '24417714', '18265520', '27250073', '25491382', '18168202', '33931215', '18393604', '18490306', '21922601', '18541994', '18178310', '18490428', '18486456', '18058094', '18460739', '18567664', '18414028', '40243234', '41649506', '18495446', '18435149', '27567700', '18459792', '18490351', '18424308', '18491813', '18324163', '18442977', '40831415', '18325322', '18276588', '29623424', '18087700', '30259358', '18197946', '18307224', '18490326', '18187946', '18181191', '18287856', '24974095', '31506536', '18142919', '26435272', '18103445', '18442795', '18252366', '19054987', '18139387', '18381475', '27652745', '27079398', '18192210', '27043773', '18050257', '18551849', '18145788', '25473466', '18460719', '41853467', '18419285', '18407761', '36271391', '28993448', '18252274', '18415463', '18376776', '23502219', '41605474', '18056053', '18113687', '18216983', '18191216', '18294041', '18318743', '18116893', '24973603', '18316423', '32542562', '18545518', '34862656', '18293415', '18243218', '18205881', '18490275', '18495427', '27802817', '18460758', '18173182', '18058919', '18219288', '18384374', '18448224', '18496964', '18188826', '36594047', '20557364', '18382408', '42761771', '27043749', '30060695', '18350668', '35499666', '26306082', '18403718', '18522632', '18262247', '29564226', '26435835', '19392620', '28424876', '18251209', '28151980', '29895454', '18260807', '18175353', '18227525', '29478219', '27235610', '18496698', '18285914', '18488405', '38327782', '18319129', '18224535', '18270368', '18194829', '18570416', '18273401', '38335896', '34133001', '18277792', '18123460', '18545522', '38499778', '18143762', '18119988', '18423245', '18435391', '18498413', '18428409', '18303532', '18087022', '30301957', '18570050', '43278235', '31565433', '38005872', '18384933', '18460559', '36665713', '36845743', '18134521', '18567687', '41496795', '18364008', '18570007', '18323096', '18507090', '18228412', '36271352', '32103102', '18460735', '18333582', '18419302', '18490425', '18363373', '34587119', '27043861', '18427315', '18262250', '22341832', '34316757', '18522625', '18435451', '18448531', '18260847', '18301735', '20766918', '18273787', '18116328', '18065837', '34160570', '18298665', '18490022', '18393035', '18157409', '40716391', '18327579', '18522672', '18276614', '18262207', '27566327', '18283212', '21243830', '39823101', '18463636', '29246310', '18417961', '39061388', '18213082', '18284241', '18172194', '18350782', '32671503', '18435386', '18383536', '18570417', '18291239', '18522664', '29492338', '18388053', '18171741', '34168947', '18246488', '18121919', '18498687', '18302733', '18454768', '18479915', '19068538', '18141436', '22798735', '27833633', '18282012', '27179887', '18212797', '22172209', '18105904', '18482398', '18287093', '18398907', '18271313', '18137176', '18195151', '18435152', '29058078', '27043748', '18354780', '18449273', '18462573', '18350669', '18145818', '18488582', '33140231', '18357587', '18432253', '18284410', '18356007', '18178262', '18484149', '20649396', '18311975', '18391120', '18161940', '18101654', '18291307', '18223770', '18464258', '18567595', '18495867', '18130670', '18284386', '18366262', '18240462', '18489002', '18190685', '18306160', '29760186', '18295352', '18201617', '30146104', '18435168', '18086833', '18105894', '18350716', '18206214', '18342681', '18481424', '18302565', '24464020', '18238468', '41853431', '18175411', '41073568', '23797406', '18118434', '18380635', '18275151', '18156234', '18194238', '18547477', '18490262', '18306156', '18475243', '18213036', '18382805', '18490350', '18283025', '22893034', '18315845', '40564638', '18327522', '18071747', '18275063', '35279035', '18335661', '24957384', '18277770', '18045166', '18350761', '18358644', '18058611', '18201549', '24564270', '18456061', '18289185', '18172032', '18340274', '18414992', '18444375', '18103258', '18432009', '18308373', '38413042', '18302791', '18304215', '29604994', '29564155', '18494647', '18259264', '29457107', '18340262', '23784836', '18350632', '18247016', '18769018', '18516622', '18101336', '18373294', '18478349', '40488783', '18464690', '18305296', '18247387', '40663278', '18365194', '18244401', '18197926', '18491804', '27043744', '18490346', '18536369', '18522280', '18419318', '18384617', '18497250', '27231170', '18340059', '18395143', '18214566', '18435298', '18471095', '25755536', '18165470', '22786475', '18368732', '18253700', '18490278', '18544611', '26330200', '18152001', '18309364', '18462011', '18317680', '32336818', '18364984', '18145814', '18460755', '18548848', '18406096', '18083406', '18145793', '25491244', '33418724', '18349254', '18470702', '18330727', '18330662', '31577655', '18395883', '18452736', '36582433', '18257702', '18551844', '18129918', '18299054', '41034684', '18105174', '18369162', '26357686', '18242015', '18516970', '18326935', '18301715', '18570026', '18090284', '18145855', '18567649', '39921100', '18280449', '18116888', '18311980', '18448287', '18419322', '18490405', '26384977', '41853201', '18114732', '18490281', '38361706', '18309237', '18455231', '18444420', '18455661', '18157700', '18544607', '18233802', '18159821', '37746909', '21269002', '18324230', '43301813', '25977473', '18282136', '18092573', '18490320', '18499551', '18211978', '18560353', '18219918', '35828942', '18154062', '18287850', '41472510', '18389805', '18567615', '18305073', '18284456', '29550207', '18380632', '18196605', '18308929', '18490377', '18125875', '18345513', '18485030', '37121432', '18567642', '25420452', '37444194', '18321982', '18180047', '36167703', '29026070', '19119374', '33478084', '33604991', '27043763', '33418810', '18536964', '18064348', '18490361', '19158414', '18434768', '18359930', '39680354', '18506594', '40407383', '27005416', '18145798', '18111716', '40406204', '18551789', '38968566', '36588035', '18298988', '41563219', '18432311', '18055854', '18244888', '18249243', '35469623', '18287515', '29598885', '18384372', '37852380', '18349208', '27235006', '19216996', '18500381', '18361261', '29871956', '25105553', '18289120', '29564109', '18288674', '18294207', '18283038', '18075575', '21118467', '18291286', '18216045', '18529097', '18479912', '18419313', '18419256', '22341823', '18331111', '37883758', '18305730', '27568001', '27043718', '29023835', '30223191', '18482327', '18293346', '18439658', '31434826', '36819705', '18283203', '18283096', '18279335', '18500991', '18277794', '30044677', '18146835', '18414833', '30204974', '18308682', '18503981', '18286447', '18570056', '18460764', '18509525', '18176538', '21025651', '18183997', '18284354', '18421045', '27044073', '18299835', '38574884', '18116285', '18276582', '31678897', '25818796', '18522622', '18490137', '18286410', '18324688', '18385980', '18126078', '18522655', '25491202', '18339055', '18179985', '18240214', '18444427', '18443618', '18127055', '29630625', '18479709', '18508482', '18219017', '18490269', '35452093', '18271725', '18246456', '18047873', '18371017', '18288300', '18098934', '18145852', '18460713', '18107322', '18490402', '18551793', '18534369', '29562454', '35606812', '18212749', '18244579', '18358638', '18268553', '18298357', '18490394', '18246952', '18145782', '18451058', '42383079', '27047110', '20204475', '18318319', '20952310', '18356519', '40663316', '18483111', '18365916', '18490382', '18105923', '18309334', '18496578', '18072101', '36259649', '18442789', '21313491', '18467078', '18112474', '18050565', '18485991', '18490290', '37060987', '18188206', '18339690', '38554946', '18432608', '40446787', '18530557', '18226312', '18483214', '18502631', '18480438', '18079389', '18427758', '18243140', '29022510', '18484494', '18541195', '18244406', '22207357', '41005808', '27043743', '18490341', '27180816', '20558799', '27023695', '18178357', '18083138', '41015625', '18332336', '18521413', '18304204', '28475609', '18483185', '18490253', '29623875', '18335670', '18363913', '18350770', '18226514', '37138721', '27854251', '25661658', '18355358', '18245893', '18522679', '18536790', '26874070', '18331625', '36712907', '18376341', '18289881', '18234250', '18365243', '38984377', '18256211', '18132105', '18092292', '26829322', '18537586', '18057672', '18387495', '18414887', '27846954', '18481053', '18341120', '18197937', '18365869', '18490357', '18432370', '18195459', '18324165', '18287826', '20556285', '18463391', '19414207', '18448491', '18297963', '18093314', '18260908', '18460801', '18498336', '18439614', '18147511', '18567663', '18115298', '18329869', '33001029', '36913358', '18515788', '18330035', '18286299', '18345420', '18172034', '19792638', '18278196', '18490301', '18074778', '20553896', '18212699', '34127675', '27217510', '18240393', '18178340', '28797072', '29237465', '18415239', '18301011', '18289615', '18287867', '18123492', '31577501', '18490317', '18287922', '40830612', '18171874', '29612850', '18536787', '18376090', '27249994', '18536800', '18311379', '18354765', '42761795', '27043742', '18490340', '40294617', '30251974', '18288550', '19101773', '18348896', '18522639', '18460728', '27198748', '18156746', '18478748', '26982103', '18152007', '18522642', '18277035', '18490442', '18449982', '18324220', '18412179', '18294508', '18467307', '18279210', '18110366', '18065807', '18054659', '18169615', '18461396', '18538131', '18105888', '32251745', '41853180', '18221252', '18307002', '18301586', '26880437', '34521458', '42231628', '18560489', '18490415', '18551784', '18545552', '18332566', '18065811', '34314873', '27481663', '26014258', '18246545', '18110370', '28440602', '18447318', '18270162', '18404453', '41121424', '18341224', '27891624', '18570020', '41015649', '18340065', '18226581', '18349301', '27023587', '24562224', '25473656', '18189050', '18490287', '18184727', '18175732', '18444563', '18241668', '18558272', '19027372', '24525193', '27198725', '18460745', '18291209', '18567618', '18548858', '42093129', '18094629', '22653867', '18184496', '18490423', '29620829', '18352078', '18327528', '26956813', '18419241', '18254734', '18098942', '32103386', '30061580', '37799215', '18105914', '18350696', '18435512', '18205557', '27043758', '29006392', '18421101', '29023526', '18163853', '18435396', '39675417', '18423242', '18356083', '18552960', '18435729', '18140775', '18175591', '18212800', '27198705', '18349233', '18350291', '18444113', '18531664', '18203084', '18570391', '27250081', '18272702', '18435380', '31544144', '18238842', '18373271', '22711556', '18376566', '18349225', '18522662', '18485725', '18348434', '18570041', '18271968', '18547762', '41241968', '18515792', '21053097', '18430614', '18514029', '18475658', '18113478', '18198732', '18559140', '21045456', '26778723', '18188159', '23081382', '27023765', '39719045', '18302670', '18460724', '18522635', '18317188', '18066837', '25491262', '18290300', '18448521', '18082909', '18292297', '18462060', '18257698', '18076708', '18264951', '18435154', '18311230', '18419191', '18132667', '18459432', '18307059', '18132889', '28475628', '18349209', '18275141', '20553770', '18422310', '18373174', '21341196', '18490337', '33123349', '18474823', '18238751', '28058411', '18151277', '18263353', '18049175', '18240960', '18145849', '18496172', '18357610', '18290005', '29562273', '29289953', '38698408', '18496871', '18360975', '18494909', '18270744', '18490419', '18980803', '41789408', '18273538', '18218076', '18478154', '28414803', '35419961', '21043003', '18176153', '10839428', '18276556', '18454611', '18122962', '28797455', '28920629', '18444304', '18261143', '18278362', '18153437', '18567585', '18187445', '29023859', '18447314', '18490399', '18105938', '36776333', '18206212', '29577460', '33673546', '18522658', '18434817', '18126075', '18311877', '18283054', '18491863', '18490321', '36793627', '18449648', '18160479', '38335852', '18532967', '18098939', '18282700', '18241664', '28038705', '18276844', '18059533', '18432243', '34267622', '18490264', '18141560', '18522620', '36916694', '27974925', '18363233', '18315636', '24991950', '18234209', '18261615', '18490359', '18131818', '18287829', '41042162', '28969956', '18105917', '18087499', '27551051', '18296916', '37269260', '18335054', '18093435', '18111846', '18291553', '18350768', '18444110', '18398410', '18254019', '18464363', '18570412', '40944002', '28993421', '18139356', '18414827', '18168359', '18567292', '38499910', '18435383', '18350791', '18435803', '35686927', '18291953', '18295443', '27050136', '18334983', '18335691', '24974101', '18278160', '18397209', '18310530', '39792834', '38335884', '18518459', '19079547', '18491732', '18570015', '18283606', '18419254', '18276596', '18507201', '18549952', '28460281', '18435442', '18413337', '18432190', '18293692', '18550572', '24352444', '33910653', '22961337', '18275007', '18546625', '18221820', '18484398', '24974087', '23866412', '18490334', '18474820', '24691610', '34097241', '23053122', '18448298', '18327842', '35379364', '18330684', '18295654', '18465509', '18504682', '26849444', '18462736', '18481964', '18300818', '18273242', '18282607', '18412208', '18358620', '18259703', '27043761', '18145775', '18318897', '18490363', '28274467', '18078955', '18288565', '18296594', '18567640', '18153571', '30080943', '27973409', '30171337', '18056738', '18454184', '18264591', '27043777', '37957535', '18093767', '18134270', '28993335', '18065799', '27402268', '18452284', '19284105', '18253089', '18432493', '37073126', '18567617', '18475661', '18252948', '22282963', '18471337', '18187942', '18359070', '18490355', '29579236', '33130911', '18446526', '18153382', '31506545', '29023895', '29575583', '19368067', '18430089', '18508678', '40630936', '25687077', '18484896', '18250543', '18465704', '18549159', '18394080', '18131906', '18345423', '24973631', '29837528', '18383250', '29025445', '18485946', '29658586', '18370151', '22171314', '18386681', '18570408', '25062939', '18304355', '18517470', '30474272', '18202049', '41853551', '29895149', '18120252', '40289358', '18086857', '18131506', '40716418', '18331631', '18350772', '41034253', '18490251', '18536784', '18212819', '18194821', '18264323', '18107208', '18460639', '42555054', '18324171', '41853269', '18490343', '24536801', '18432221', '18452820', '18128770', '18554546', '30173015', '18072528', '18116858', '40010965', '18567363', '18460394', '18340119', '34773499', '27023984', '18153245', '25114337', '18195123', '18287848', '30259203', '18358694', '18063968', '18099327', '18476811', '29204500', '18270435', '40333401', '18490338', '18144216', '18083403', '18472278', '18248630', '18488972', '18236890', '18460750', '18227106', '18268547', '26803122', '34707062', '18431071', '37367604', '18318177', '18203009', '18190726', '29596657', '18145779', '18343430', '18264972', '18551787', '18221744', '18201608', '37105933', '18501176', '32335335', '18196888', '22779085', '18145780', '27043794', '18377895', '41789368', '18285072', '31582169', '18516099', '18490379', '18284399', '18445263', '18467191', '18192131', '27749234', '33229476', '31496474', '18570023', '18108485', '40509634', '18544602', '34101504', '42383313', '18432309', '18408648', '18490284', '26338336', '32839878', '18455664', '32336734', '18426599', '21290567', '25704947', '20952353', '18339057', '18306201', '18376553', '42648598', '18329112', '33130340', '18254254', '18396213', '18339056', '18399109', '38521853', '18254891', '18349568', '29603453', '27613939', '18049881', '26615515', '26107313', '18279253', '40324783', '18288568', '18136539', '18294818', '18132595', '32334860', '18479161', '29485861', '36473613', '20284865', '18490397', '18164176', '18383380', '34988260', '18105936', '18165622', '38074502', '18295423', '41853252', '18254215', '30374075', '18410541', '18070884', '18461951', '18317145', '18344462', '18434858', '29134273', '18105920', '18194764', '18264836', '24480383', '18395307', '18570258', '18145778', '18120228', '23975940', '18528076', '18536592', '18097912', '18415714', '18291358', '27132799', '22491498', '39775254', '18500731', '18311029', '22079187', '18515163', '18145810', '18296188', '18173021', '18081203', '42375084', '18518028', '18127555', '36344033', '27566529', '18360306', '40544623', '25849295', '18570018', '18170226', '18156314', '18484497', '34068953', '18288417', '18450666', '18322732', '18367284', '18191376', '18391041', '18498772', '18276827', '18519642', '18329257', '18478199', '18167330', '28787965', '37852659', '38361411', '36680977', '18060955', '18353299', '18542352', '18109997', '18223979', '29570057', '27804266', '38011879', '35452803', '18339200', '23359330', '32442330', '18084742', '40406289', '18570409', '18308232', '18046467', '18475990', '28613207', '18232807', '10996700', '18373793', '18379664', '18222807', '18363269', '28883308', '18420749', '18443637', '18251353', '18172466', '18340270', '18490303', '18432261', '18350765', '27974640', '18158062', '18297873', '18567661', '18233607', '18358181', '18226055', '41853442', '21619511', '18180988', '22079105', '18473055', '18490354', '18408798', '27043756', '38838756', '18212766', '18462078', '28151531', '18341123', '22773128', '18396970', '18490266', '18195991', '29564167', '18046294', '23324289', '18558293', '18297916', '18490323', '40035962', '18468394', '19167895', '18442790', '18264601', '24420507', '18567616', '18210540', '40324737', '18276542', '18350712', '18105890', '18495463', '20047440', '40212588', '27043776', '18098983', '18388485', '18112713', '18260183', '18142224', '18335887', '18396248', '18994911', '30251106', '18490548', '37292252', '18345506', '29304287', '18051843', '18297812', '18494634', '36998564', '18221933', '39174281', '33603191', '18287957', '18116896', '18153423', '18177486', '18332342', '18471818', '18457880', '18229396', '18046056', '18374488', '18167469', '18406708', '41960308', '18287845', '34404641', '18202642', '18490335', '18158812', '18223959', '28993375', '18199418', '18530364', '18870599', '33033750', '10809618', '18448221', '18477132', '18318910', '27710145', '11005168', '38130908', '18179637', '18345480', '18357844', '18474335', '18397526', '18570014', '18275587', '18449918', '18249057', '18111851', '18510087', '18504494', '19068212', '18310024', '18570385', '21045454', '26667309', '38458777', '28920580', '18570413', '18192917', '35464317', '18155099', '18244632', '18557275', '18331450', '18181287', '18221732', '18248611', '43178766', '18478204', '18507191', '18366071', '18331903', '18248484', '18432282', '18394824', '18288636', '18350786', '28120995', '18544623', '18515786', '28788809', '18256926', '18472189', '18435402', '29604836', '18245520', '18292057', '29837749', '18188819', '18435394', '18344815', '18165086', '41158964', '18518622', '18310065', '18247726', '18364261', '18290314', '18189863', '18411673', '18305908', '18212156', '29697248', '18460730', '28993461', '18058567', '43017148', '18352095', '18224121', '18061528', '27043751', '18051034', '18478188', '18536813', '18232768', '18124313', '18130700', '18474001', '18546212', '18212624', '18434865', '35687042', '18570009', '36669801', '41463688', '40253666', '38826173', '18291627', '36096515', '18118440', '18331949', '18221056', '18254156', '29006266', '18251704', '37381500', '20558363', '18350762', '18058757', '22938411', '18114822', '18382294', '25211330', '18257003', '34862674', '18288781', '18479737', '18054627', '18048604', '18175218', '18251342', '18490257', '26103678', '18197564', '18304579', '18306544', '18409120', '27179975', '28627899', '18289606', '29614981', '18434824', '18170276', '18318389', '18439650', '18339591', '18548021', '18344808', '18435389', '18491807', '18324177', '18490345', '41159000', '18328011', '18249660', '18094587', '18276573', '18536805', '18448528', '18409874', '18434873', '35268261', '18529135', '18264722', '34053242', '18334049', '30080908', '18271737', '18225490', '18203408', '18226316', '18278453', '18359715', '40841451', '18515471', '18525028', '18522647', '29562387', '18121605', '18298191', '21269017', '27043784', '18145790', '18378789', '18474191', '41195317', '18260521', '18058690', '18252145', '18347926', '18490369', '28920636', '18312710', '19404054', '18409337', '34083176', '18238133', '18178328', '18305485', '18448292', '18432067', '18141803', '18125611', '18218090', '25491247', '18454862', '18325249', '18490390', '18290333', '18145786', '27508223', '18544716', '18110375', '18448284', '18510798', '18120055', '18116864', '40392922', '18475145', '18484500', '18116537', '18286064', '21791943', '18356730', '18473615', '24691749', '18098930', '18526868', '18444423', '18455662', '29564183', '41195213', '18288241', '18490328', '40010774', '18278015', '18049886', '18095894', '24464214', '18163175', '20952355', '18310629', '18522651', '18474774', '18393124', '18337487', '18286044', '23795855', '18363235', '18292512', '18567685', '18234126', '27125939', '18098802', '18252173', '18308844', '37665755', '18448532', '18269366', '30374506', '18536798', '18460760', '18276617', '18260239', '18145821', '18557634', '28954146', '22471307', '18467699', '23253157', '27927696', '30081769', '18209526', '18300035', '18244859', '18292396', '29641257', '18305722', '18284307', '41872705', '26330809', '18570414', '18228847', '29575874', '25367334', '18433251', '18248495', '18420754', '18444415', '18276197', '18435805', '18187092', '18082058', '18505797', '18364159', '18202517', '31576122', '18553773', '18460721', '18414933', '18667279', '42071774', '18262300', '18570156', '37865599', '18173397', '18241319', '30699211', '18318917', '18089213', '18448524', '18489187', '18274112', '18110441', '18388210', '18156221', '35477906', '18515168', '18285255', '18105839', '18455228', '28944255', '18490332', '18469717', '18554425', '18169535', '18263497', '31013447', '18280515', '18567650', '18222924', '18070163', '18335480', '31433220', '27223617', '20926235', '18464934', '18435192', '18491827', '18490365', '41960122', '28121028', '18270307', '42669298', '41649524', '18284405', '18056944', '18163712', '38756306', '18240477', '36599909', '39718954', '18367036', '18176140', '18341104', '18110379', '18294393', '19034916', '18490373', '27043771', '40261808', '18365725', '18538600', '18279937', '22220368', '18250420', '18287854', '18490261', '25623233', '18454585', '18542363', '18553572', '19709107', '18336081', '18260097', '28969880', '18328972', '18114679', '18490260', '18882563', '18056787', '18490325', '18502985', '38039089', '18312025', '18261540', '32654225', '34268245', '38984305', '18528314', '18268331', '27043818', '18104550', '42949955', '25927776', '18434813', '18364135', '18264579', '18366276', '18276544', '18350714', '36271404', '18379496', '33930165', '18103554', '37832750', '33753804', '18490372', '18470576', '18469887', '18379245', '27386146', '18510795', '18331968', '27217099', '18546819', '18419269', '18570028', '39110646', '18567647', '27023521', '18504955', '18224721', '27043789', '18432343', '18567597', '18273615', '18467925', '18279998', '29134296', '18243470', '18428467', '18116890', '18309501', '33628551', '18311977', '19078003', '37833291', '18289670', '18478412', '30260535', '18287843', '18340305', '26667289', '18409551', '43075565', '18490333', '18129404', '31600764', '18488580', '18388641', '27223587', '18341778', '18285311', '18252236', '36680951', '34955078', '30013455', '18187396', '28361404', '28787943', '18365175', '18525398', '18262478', '18490348', '18168923', '18438332', '18548647', '18547048', '18536808', '18435445', '18308150', '18422380', '33327557', '19206287', '18302674', '18339877', '18570012', '18282440', '18116853', '18460720', '18180078', '18490827', '18244233', '18288558', '18358522', '18378194', '18317860', '33121188', '26107323', '20178909', '18336055', '18494649', '18249513', '18570045', '18086763', '18570415', '18261595', '18283206', '18339965', '18331456', '18360009', '25474322', '18234166', '30145301', '36405288', '24847904', '34352626', '42928210', '18278171', '18288775', '18319429', '18130189', '18522670', '18339565', '18096260', '18116957', '18106795', '18105910', '18153223', '28823573', '29512636', '18548981', '18522627', '18495330', '18068991', '18335511', '18478306', '18547232', '18539987', '18201639', '37236734', '29007323', '20649336', '28806426', '18178692', '18249933', '18048453', '36588092', '18534784', '18460741', '18277924', '18145800', '18329816', '18549934', '18205461', '18506925', '18483010', '18804851', '18455663', '18089336', '18490329', '18432070', '18178295', '18419320', '18551796', '18550884', '18311982', '25919690', '18527055', '18344472', '18317440', '18288581', '18167475', '18571265', '27212628', '18234691', '18349539', '18084945', '19052671', '22829548', '18184774', '27043793', '18113558', '39644010', '18299056', '18318189', '18150655', '18395881', '37534104', '32933828', '18249071', '18280148', '18550892', '18551780', '18547204', '18107274', '28627699', '19345724', '19231357', '18427321', '38574858', '18056375', '28969967', '18421975', '18110362', '18490368', '18051371', '18490446', '18307544', '38717083', '18305980', '18399119', '18314387', '18283226', '18152003', '27858021', '18328968', '18490295', '18132244', '18256853', '18409048', '18531441', '18174799', '38717017', '18087579', '20463192', '18435275', '30106057', '18175537', '18289715', '18567364', '18124241', '18254546', '41287647', '18171348', '18385792', '18435449', '18176874', '18448529', '32924857', '18370510', '18251969', '39842666', '29454816', '31899256', '18415417', '18455659', '24973620', '18490313', '18154051', '18494645', '18452489', '18480169', '18536795', '18445423', '18415378', '18395946', '18155590', '30286115', '43017115', '18553553', '18423533', '28993345', '18435408', '32714145', '18570376', '43007007', '18104874', '29343045', '35663254', '18567667', '18455531', '28130618', '18290871', '18201635', '26667084', '18277549', '32985525', '18457275', '18110358', '18351963', '35025301', '18490352', '18386517', '18152982', '18543912', '18208228', '18448495', '18242191', '20716252', '29564116', '18055563'])"
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "keys = data.keys()\n",
    "list_of_links = {key: data[key]['refs'] for key in data}\n",
    "\n",
    "keys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=10950501&ArtRevId=1&sid=&sind=A&',\n",
       "  'LINK_TEXT': 'U.S. Structured Finance Asset Isolation And Special-Purpose Entity Criteria',\n",
       "  'ART_OBJECTID': '10950501'},\n",
       " {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=5531630&ArtRevId=3&sid=&sind=A&',\n",
       "  'LINK_TEXT': 'Securitizations By Code Transferors',\n",
       "  'ART_OBJECTID': '5531630'},\n",
       " {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=5532219&ArtRevId=3&sid=&sind=A&',\n",
       "  'LINK_TEXT': 'Securitizations By SPE Transferors And Non-Code Transferors',\n",
       "  'ART_OBJECTID': '5532219'},\n",
       " {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=5536184&ArtRevId=3&sid=&sind=A&',\n",
       "  'LINK_TEXT': 'Legal Criteria For U.S. Structured Finance Transactions: Special-Purpose Entities',\n",
       "  'ART_OBJECTID': '5536184'},\n",
       " {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=9779138&ArtRevId=1&sid=&sind=A&',\n",
       "  'LINK_TEXT': 'Guarantee Criteria',\n",
       "  'ART_OBJECTID': '9779138'},\n",
       " {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=3760316&ArtRevId=6&sid=&sind=A&',\n",
       "  'LINK_TEXT': 'New Option For Addressing Transferor Preference Risk Under The U.S. Bankruptcy Code',\n",
       "  'ART_OBJECTID': '3760316'},\n",
       " {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=5513345&ArtRevId=7&sid=&sind=A&',\n",
       "  'LINK_TEXT': 'Legal Considerations For Student Loan Transactions',\n",
       "  'ART_OBJECTID': '5513345'},\n",
       " {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=9779138&ArtRevId=1&sid=&sind=A&',\n",
       "  'LINK_TEXT': 'Guarantee Criteria',\n",
       "  'ART_OBJECTID': '9779138'},\n",
       " {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=5531630&ArtRevId=3&sid=&sind=A&',\n",
       "  'LINK_TEXT': 'Legal Criteria For U.S. Structured Finance Transactions:  Securitizations By Code Transferors',\n",
       "  'ART_OBJECTID': '5531630'},\n",
       " {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=5532219&ArtRevId=3&sid=&sind=A&',\n",
       "  'LINK_TEXT': 'Legal Criteria For U.S. Structured Finance Transactions:  Securitizations By SPE Transferors And Non-Code Transferors',\n",
       "  'ART_OBJECTID': '5532219'},\n",
       " {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=5536184&ArtRevId=3&sid=&sind=A&',\n",
       "  'LINK_TEXT': 'Legal Criteria For U.S. Structured Finance Transactions:  Special-Purpose Entities',\n",
       "  'ART_OBJECTID': '5536184'},\n",
       " {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=5536276&ArtRevId=3&sid=&sind=A&',\n",
       "  'LINK_TEXT': 'Legal Criteria For U.S. Structured Finance Transactions:  Select Issues Criteria',\n",
       "  'ART_OBJECTID': '5536276'},\n",
       " {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=5536299&ArtRevId=3&sid=&sind=A&',\n",
       "  'LINK_TEXT': 'Legal Criteria For U.S. Structured Finance Transactions:  Criteria Related To Asset-Backed Securities',\n",
       "  'ART_OBJECTID': '5536299'},\n",
       " {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=5538584&ArtRevId=3&sid=&sind=A&',\n",
       "  'LINK_TEXT': 'Legal Criteria For U.S. Structured Finance Transactions:  Criteria Related To Securities Backed By Residential Mortgage, Home Equity, And Manufactured Housing Loans',\n",
       "  'ART_OBJECTID': '5538584'},\n",
       " {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=5539088&ArtRevId=3&sid=&sind=A&',\n",
       "  'LINK_TEXT': 'Legal Criteria For U.S. Structured Finance Transactions:  Criteria Related To Collateralized Debt Obligations',\n",
       "  'ART_OBJECTID': '5539088'},\n",
       " {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=5539760&ArtRevId=3&sid=&sind=A&',\n",
       "  'LINK_TEXT': 'Legal Criteria For U.S. Structured Finance Transactions:  Criteria Related To New Asset-Backed Securities',\n",
       "  'ART_OBJECTID': '5539760'},\n",
       " {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=5539809&ArtRevId=3&sid=&sind=A&',\n",
       "  'LINK_TEXT': 'Legal Criteria For U.S. Structured Finance Transactions:  Criteria Related To Hybrid Asset-Backed Securities',\n",
       "  'ART_OBJECTID': '5539809'},\n",
       " {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=5541786&ArtRevId=3&sid=&sind=A&',\n",
       "  'LINK_TEXT': 'Legal Criteria For U.S. Structured Finance Transactions:  Appendix III:  Revised UCC Article 9 Criteria',\n",
       "  'ART_OBJECTID': '5541786'},\n",
       " {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=5541914&ArtRevId=7&sid=&sind=A&',\n",
       "  'LINK_TEXT': \"Legal Criteria For U.S. Structured Finance Transactions:  Appendix IV:  ABCP Officer's Certificate\",\n",
       "  'ART_OBJECTID': '5541914'},\n",
       " {'LINK': 'www.capitaliq.com/CIQDotNet/CreditResearch/SPResearch.aspx?articleId=&ArtObjectId=5542778&ArtRevId=19&sid=&sind=A&',\n",
       "  'LINK_TEXT': 'Legal Criteria For U.S. Structured Finance Transactions: Glossary',\n",
       "  'ART_OBJECTID': '5542778'}]"
      ]
     },
     "execution_count": 175,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list_of_links['18460739']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {},
   "outputs": [],
   "source": [
    "# data['18460739']['title']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "327c93a772224946a6c3b8e76f04eb1d",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=2273), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# (me_id, link_id)\n",
    "refs4 = []\n",
    "\n",
    "\n",
    "for key in tqdm(list_of_links):\n",
    "    title = data[key]['title']\n",
    "    for link in list_of_links[key]:\n",
    "        link_title = link['LINK_TEXT']\n",
    "        for new_key in data:\n",
    "            if fuzz.ratio(link_title, data[new_key]['title']) == 100:\n",
    "                refs4.append((key, new_key))\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "31431"
      ]
     },
     "execution_count": 183,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(refs) # > 90"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "19210"
      ]
     },
     "execution_count": 228,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(set(refs))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "16977"
      ]
     },
     "execution_count": 229,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(set(refs2)) # > 95"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "15691"
      ]
     },
     "execution_count": 230,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(set(refs3)) # > 98"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14442"
      ]
     },
     "execution_count": 231,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(set(refs4)) # == 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "metadata": {},
   "outputs": [],
   "source": [
    "# len(refs4) / (2273*2273)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "metadata": {},
   "outputs": [],
   "source": [
    "# refs4[:100]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "metadata": {},
   "outputs": [],
   "source": [
    "# refs4[:10]\n",
    "\n",
    "refs_pd = [{'citing':key[0], 'cited':key[1]} for key in set(refs4)]\n",
    "\n",
    "refs_df = pd.DataFrame(refs_pd)\n",
    "\n",
    "refs_df.to_csv('data/cvl_cites_100.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 226,
   "metadata": {},
   "outputs": [],
   "source": [
    "# citation_df ="
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "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>citing</th>\n",
       "      <th>cited</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>31582169</td>\n",
       "      <td>26357686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>33604991</td>\n",
       "      <td>18311982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>27043861</td>\n",
       "      <td>28993375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>26778704</td>\n",
       "      <td>35828942</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>33121188</td>\n",
       "      <td>27043789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14437</th>\n",
       "      <td>18309364</td>\n",
       "      <td>18469887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14438</th>\n",
       "      <td>28460281</td>\n",
       "      <td>41960122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14439</th>\n",
       "      <td>29237465</td>\n",
       "      <td>18291358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14440</th>\n",
       "      <td>31506545</td>\n",
       "      <td>31506536</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14441</th>\n",
       "      <td>35828942</td>\n",
       "      <td>29630625</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14442 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         citing     cited\n",
       "0      31582169  26357686\n",
       "1      33604991  18311982\n",
       "2      27043861  28993375\n",
       "3      26778704  35828942\n",
       "4      33121188  27043789\n",
       "...         ...       ...\n",
       "14437  18309364  18469887\n",
       "14438  28460281  41960122\n",
       "14439  29237465  18291358\n",
       "14440  31506545  31506536\n",
       "14441  35828942  29630625\n",
       "\n",
       "[14442 rows x 2 columns]"
      ]
     },
     "execution_count": 227,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "refs_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}