2.0_ibes_capiq_format_names.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Parse analyst names from Capital IQ and IBES datasets and reformat to prepare to merge on analyst name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np, re, string\n",
    "import conversions as cv # Custom function to read and format sas files into pandas dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_names_ciq(s,last=True):\n",
    "    delim = re.compile(r'( of )|(;)')\n",
    "    rmv = re.compile(r'[\\(?\\)-]|III|II|IV|Jr\\.|Sr\\.|,.*|from.*|with.*|–.*| - .*')\n",
    "    rmv2 = re.compile(r'Analyst (for|with)')\n",
    "    dblnms = re.compile(r'(Van Den|Van|[dD][ei]n?)\\s+\\w*')\n",
    "    s = re.sub(r'\\d+', '', str(s)) \n",
    "    if str(s).strip().endswith(\".\"):\n",
    "        s = re.sub(r'\\.',\"\",s)\n",
    "    else:\n",
    "        s = s\n",
    "    nm = re.sub(rmv,\"\",str(s))\n",
    "    nm2 = re.split(delim,nm)[0].split(\" for \")[-1]\n",
    "    try:\n",
    "        if last:\n",
    "            if re.search(dblnms,nm2):\n",
    "                ln = re.search(dblnms,nm2).group()\n",
    "            else:\n",
    "                if re.search(r'\\.',nm2):\n",
    "                    nm2 = nm2.split(\".\")[-1]\n",
    "                else:\n",
    "                    nm2 = nm2.translate(str.maketrans('', '', string.punctuation))\n",
    "                ln = nm2.split()[-1]\n",
    "            ln = re.sub(\"\\W\",\"\",ln)\n",
    "            ln = ln.encode(\"ascii\", errors=\"ignore\").decode()\n",
    "            return ln.upper()\n",
    "        else:\n",
    "            fn = nm2.split()[0]\n",
    "            fn = re.sub(\"\\W\",\"\",fn)\n",
    "            fn = fn.encode(\"ascii\", errors=\"ignore\").decode()\n",
    "            return fn.upper()\n",
    "    except: return \"Error\"\n",
    "    \n",
    "def get_names_ibes(s):\n",
    "    rmv = re.compile(r'(,|III|II|IV|JR|SR|\\s{3,}\\w).*')\n",
    "    if len(s.split(\"/\")) > 1:\n",
    "        strs = s.split(\"/\")\n",
    "        s1 = re.sub(rmv,\"\",strs[0])\n",
    "        s2 = re.sub(rmv,\"\",strs[1])\n",
    "        return s1.upper(),s2.upper()\n",
    "    else:\n",
    "        if len(s.split()) == 1: s = s\n",
    "        else: s = s[:-1].strip()\n",
    "        s = re.sub(rmv,\"\",s)\n",
    "        s = re.sub(\"\\W\",\"\",s)\n",
    "        return s.upper(),\"\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "filePath = r\"G:\\\\My Drive\\\\Research\\\\FLP_Analyst_Soft_Skills\\\\FLP_Analyst_Skill_Shared\\\\Data\\\\capital IQ\\\\Adjusted\"\n",
    "ciqFileName = filePath + r\"\\\\ciqanalystfirmyear_20240330.sas7bdat\"\n",
    "ibesFileName = filePath + r\"\\\\ibesanalystfirmyear_20240330.sas7bdat\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Used latin-1 to decode G:\\\\My Drive\\\\Research\\\\FLP_Analyst_Soft_Skills\\\\FLP_Analyst_Skill_Shared\\\\Data\\\\capital IQ\\\\Adjusted\\\\ciqanalystfirmyear_20240330.sas7bdat\n",
      "Used UTF-8 to decode G:\\\\My Drive\\\\Research\\\\FLP_Analyst_Soft_Skills\\\\FLP_Analyst_Skill_Shared\\\\Data\\\\capital IQ\\\\Adjusted\\\\ibesanalystfirmyear_20240330.sas7bdat\n",
      "# of CIQ Analyst-Gvkey-Year obs: 1065634\n",
      "# of IBES Analyst-Gvkey-Year obs: 972595\n"
     ]
    }
   ],
   "source": [
    "ciq = cv.readsas(ciqFileName)\n",
    "ibes = cv.readsas(ibesFileName)\n",
    "print(\"# of CIQ Analyst-Gvkey-Year obs:\",len(ciq))\n",
    "print(\"# of IBES Analyst-Gvkey-Year obs:\",len(ibes))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>transcriptpersonid</th>\n",
       "      <th>proid</th>\n",
       "      <th>companyid</th>\n",
       "      <th>gvkey</th>\n",
       "      <th>year</th>\n",
       "      <th>transcriptpersonname</th>\n",
       "      <th>companyofperson</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>100035.0</td>\n",
       "      <td>29154009.0</td>\n",
       "      <td>1.681540e+05</td>\n",
       "      <td>001004</td>\n",
       "      <td>2015.0</td>\n",
       "      <td>Robert Spingarn</td>\n",
       "      <td>Crédit Suisse AG, Research Division</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>100035.0</td>\n",
       "      <td>29154009.0</td>\n",
       "      <td>1.681540e+05</td>\n",
       "      <td>001004</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>Robert Spingarn</td>\n",
       "      <td>Crédit Suisse AG, Research Division</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>100035.0</td>\n",
       "      <td>29154009.0</td>\n",
       "      <td>1.681540e+05</td>\n",
       "      <td>001004</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>Robert Spingarn</td>\n",
       "      <td>Crédit Suisse AG, Research Division</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>100035.0</td>\n",
       "      <td>29154009.0</td>\n",
       "      <td>1.681540e+05</td>\n",
       "      <td>001004</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>Robert Spingarn</td>\n",
       "      <td>Crédit Suisse AG, Research Division</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>100035.0</td>\n",
       "      <td>29154009.0</td>\n",
       "      <td>1.681540e+05</td>\n",
       "      <td>001004</td>\n",
       "      <td>2019.0</td>\n",
       "      <td>Robert Spingarn</td>\n",
       "      <td>Crédit Suisse AG, Research Division</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1065044</th>\n",
       "      <td>312280.0</td>\n",
       "      <td>311968276.0</td>\n",
       "      <td>2.921972e+08</td>\n",
       "      <td>352853</td>\n",
       "      <td>2023.0</td>\n",
       "      <td>Varun Ahuja</td>\n",
       "      <td>Crédit Suisse AG, Research Division</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1065148</th>\n",
       "      <td>158823.0</td>\n",
       "      <td>29090300.0</td>\n",
       "      <td>1.774496e+09</td>\n",
       "      <td>353070</td>\n",
       "      <td>2022.0</td>\n",
       "      <td>Larry Gandler</td>\n",
       "      <td>Crédit Suisse AG, Research Division</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1065179</th>\n",
       "      <td>299086.0</td>\n",
       "      <td>278541573.0</td>\n",
       "      <td>2.087483e+08</td>\n",
       "      <td>353138</td>\n",
       "      <td>2022.0</td>\n",
       "      <td>Lokesh Garg</td>\n",
       "      <td>Crédit Suisse AG, Research Division</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1065270</th>\n",
       "      <td>349784.0</td>\n",
       "      <td>533359547.0</td>\n",
       "      <td>1.671691e+09</td>\n",
       "      <td>353444</td>\n",
       "      <td>2022.0</td>\n",
       "      <td>Mirza Faham Baig</td>\n",
       "      <td>Crédit Suisse AG, Research Division</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1065271</th>\n",
       "      <td>349784.0</td>\n",
       "      <td>533359547.0</td>\n",
       "      <td>1.671691e+09</td>\n",
       "      <td>353444</td>\n",
       "      <td>2023.0</td>\n",
       "      <td>Mirza Faham Baig</td>\n",
       "      <td>Crédit Suisse AG, Research Division</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>22956 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         transcriptpersonid        proid     companyid   gvkey    year  \\\n",
       "63                 100035.0   29154009.0  1.681540e+05  001004  2015.0   \n",
       "64                 100035.0   29154009.0  1.681540e+05  001004  2016.0   \n",
       "65                 100035.0   29154009.0  1.681540e+05  001004  2017.0   \n",
       "66                 100035.0   29154009.0  1.681540e+05  001004  2018.0   \n",
       "67                 100035.0   29154009.0  1.681540e+05  001004  2019.0   \n",
       "...                     ...          ...           ...     ...     ...   \n",
       "1065044            312280.0  311968276.0  2.921972e+08  352853  2023.0   \n",
       "1065148            158823.0   29090300.0  1.774496e+09  353070  2022.0   \n",
       "1065179            299086.0  278541573.0  2.087483e+08  353138  2022.0   \n",
       "1065270            349784.0  533359547.0  1.671691e+09  353444  2022.0   \n",
       "1065271            349784.0  533359547.0  1.671691e+09  353444  2023.0   \n",
       "\n",
       "        transcriptpersonname                      companyofperson  \n",
       "63           Robert Spingarn  Crédit Suisse AG, Research Division  \n",
       "64           Robert Spingarn  Crédit Suisse AG, Research Division  \n",
       "65           Robert Spingarn  Crédit Suisse AG, Research Division  \n",
       "66           Robert Spingarn  Crédit Suisse AG, Research Division  \n",
       "67           Robert Spingarn  Crédit Suisse AG, Research Division  \n",
       "...                      ...                                  ...  \n",
       "1065044          Varun Ahuja  Crédit Suisse AG, Research Division  \n",
       "1065148        Larry Gandler  Crédit Suisse AG, Research Division  \n",
       "1065179          Lokesh Garg  Crédit Suisse AG, Research Division  \n",
       "1065270     Mirza Faham Baig  Crédit Suisse AG, Research Division  \n",
       "1065271     Mirza Faham Baig  Crédit Suisse AG, Research Division  \n",
       "\n",
       "[22956 rows x 7 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ciq[ciq.companyofperson == \"Crédit Suisse AG, Research Division\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Validate function to properly format names - extract only the last name and make it all capitalized to match analysts' last name in IBES."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['BRAND', 'POPESCU', 'FRISCH', 'DIJINSON', 'WACHTEL', 'ONEIL', 'JOHN', 'GULLEY', 'JUVEKAR', 'FORKIN', 'AMBRECHT', 'ESSI', 'BAILEY', 'MCALLISTER', 'NADOL', 'GRUBELICH', 'MCCARTHY', 'VANRHEE', 'MAIDEN', 'YURNING', 'DEPAOLI', 'SMITH', 'OCALLAGHAN', 'OSULLIVAN', 'SHELTON', 'CABRERA', 'ELRADJAKOBY', 'PENALA', 'DITULLIO', 'OPERATOR', 'VN', 'TREMBOVOLSKY', 'WANG', 'WIDJAJA', 'LATE', 'VANDENBROEK', 'Error']\n"
     ]
    }
   ],
   "source": [
    "ciq_nms = ['Alex Brand','(Actavian Popescu?)','Adam B. Frisch','Analyst for Bev Dijinson',\n",
    "           'Bonnie K.Wachtel','Brooks O’Neil','John','Mark R. Gulley','P. J. Juvekar',\n",
    "           'Patrick J. Forkin III','K.C. Ambrecht','Vern Essi, Jr.','Judson\\xa0E.\\xa0Bailey',\n",
    "          'E. Russell McAllister from Merriman','Joseph B. Nadol, III','Eric J. Grubelich, CFA',\n",
    "          'Robert McCarthy, Jr.', 'J. Van Rhee','C. Todd Maiden','John Yurning(?)','Kim De Paoli',\n",
    "           'Morton Oâ€™Tool Smith',\"Shannon O'Callaghan\",\"Daniel Oâ€™Sullivan\",\"Allan Gould for Jeff Shelton\",\n",
    "          \"Oscar Cabrera with Goldman Sachs\",\"Irit Elrad-Jakoby\",\"Erika Penala -\",\"William Di Tullio\",\"Operator.\t\",\n",
    "           \"Saravanan V.N.\",\"Dmitry Trembovolsky – Goldman Sachs Group Inc....\t\",\"Spencer Wang – Credit Suisse\t\",\n",
    "          \"[ph] Sutanto Widjaja (58:02)\t\",\"Jason Late - Ares Management Limited; Managing...\t\",\n",
    "           \"Robert Van Den Broek\",\"Jason L.;Loews Corporation;Analyst\"]\n",
    "print([get_names_ciq(s,last=True) for s in ciq_nms])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('PRICE', 'PRICE'), ('GARCIACANTER', ''), ('DEVERTEUIL', ''), ('DEATHERAGE', 'MASSICK'), ('BOSSHARD', ''), ('MCALENNEY', ''), ('WELLS', ''), ('NICKLIN', ''), ('LEISURETOURI', ''), ('HUNTERBYRNE', ''), ('WOLFENBERGER', ''), ('VANDENBROEK', ''), ('YOUNG', 'C. BARN'), ('OBRIEN', ''), ('WARNERWILKE', ''), ('MOUSSATOS', '')]\n"
     ]
    }
   ],
   "source": [
    "ibes_nms = [\"PRICE/PRICE   J\",\"GARCIA-CANTER J\",\"DE VERTEUIL   I\",\"DEATHERAGE/MASSICK\",\"BOSSHARD      E\",\n",
    "      \"MCALENNEY,JR. E\",\"WELLS III     H\",\"NICKLIN\",\"LEISURE-TOURI\",\"HUNTER BYRNE  M\",\"WOLFENBERGER  M\",\n",
    "      \"VAN DEN BROEK R\",\"YOUNG/C. BARN\",\"O'BRIEN       K\",\"WARNER-WILKE  C\",\"MOUSSATOS, PH.D.   L\"]\n",
    "print([get_names_ibes(s) for s in ibes_nms])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reformat analysts' names to prepare for match between Capital IQ and IBES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "ciq['fname'] = ciq.transcriptpersonname.apply(lambda x: get_names_ciq(x,last=False))\n",
    "ciq['lname'] = ciq.transcriptpersonname.apply(get_names_ciq)\n",
    "ibes['lname1'] = ibes.analyst.apply(lambda x: get_names_ibes(x)[0])\n",
    "ibes['lname2'] = ibes.analyst.apply(lambda x: get_names_ibes(x)[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "ciq2 = ciq[['transcriptpersonid','proid','companyofperson','year','gvkey','fname','lname']]\n",
    "ibes2 = ibes[['ticker','estimid','amaskcd','year','permno','gvkey','analyst','lname1','lname2']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>transcriptpersonid</th>\n",
       "      <th>proid</th>\n",
       "      <th>companyofperson</th>\n",
       "      <th>year</th>\n",
       "      <th>gvkey</th>\n",
       "      <th>fname</th>\n",
       "      <th>lname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>100035.0</td>\n",
       "      <td>29154009.0</td>\n",
       "      <td>Crédit Suisse AG, Research Division</td>\n",
       "      <td>2015.0</td>\n",
       "      <td>001004</td>\n",
       "      <td>ROBERT</td>\n",
       "      <td>SPINGARN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>100035.0</td>\n",
       "      <td>29154009.0</td>\n",
       "      <td>Crédit Suisse AG, Research Division</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>001004</td>\n",
       "      <td>ROBERT</td>\n",
       "      <td>SPINGARN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>100035.0</td>\n",
       "      <td>29154009.0</td>\n",
       "      <td>Crédit Suisse AG, Research Division</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>001004</td>\n",
       "      <td>ROBERT</td>\n",
       "      <td>SPINGARN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>100035.0</td>\n",
       "      <td>29154009.0</td>\n",
       "      <td>Crédit Suisse AG, Research Division</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>001004</td>\n",
       "      <td>ROBERT</td>\n",
       "      <td>SPINGARN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>100035.0</td>\n",
       "      <td>29154009.0</td>\n",
       "      <td>Crédit Suisse AG, Research Division</td>\n",
       "      <td>2019.0</td>\n",
       "      <td>001004</td>\n",
       "      <td>ROBERT</td>\n",
       "      <td>SPINGARN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1065044</th>\n",
       "      <td>312280.0</td>\n",
       "      <td>311968276.0</td>\n",
       "      <td>Crédit Suisse AG, Research Division</td>\n",
       "      <td>2023.0</td>\n",
       "      <td>352853</td>\n",
       "      <td>VARUN</td>\n",
       "      <td>AHUJA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1065148</th>\n",
       "      <td>158823.0</td>\n",
       "      <td>29090300.0</td>\n",
       "      <td>Crédit Suisse AG, Research Division</td>\n",
       "      <td>2022.0</td>\n",
       "      <td>353070</td>\n",
       "      <td>LARRY</td>\n",
       "      <td>GANDLER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1065179</th>\n",
       "      <td>299086.0</td>\n",
       "      <td>278541573.0</td>\n",
       "      <td>Crédit Suisse AG, Research Division</td>\n",
       "      <td>2022.0</td>\n",
       "      <td>353138</td>\n",
       "      <td>LOKESH</td>\n",
       "      <td>GARG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1065270</th>\n",
       "      <td>349784.0</td>\n",
       "      <td>533359547.0</td>\n",
       "      <td>Crédit Suisse AG, Research Division</td>\n",
       "      <td>2022.0</td>\n",
       "      <td>353444</td>\n",
       "      <td>MIRZA</td>\n",
       "      <td>BAIG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1065271</th>\n",
       "      <td>349784.0</td>\n",
       "      <td>533359547.0</td>\n",
       "      <td>Crédit Suisse AG, Research Division</td>\n",
       "      <td>2023.0</td>\n",
       "      <td>353444</td>\n",
       "      <td>MIRZA</td>\n",
       "      <td>BAIG</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>22956 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         transcriptpersonid        proid                      companyofperson  \\\n",
       "63                 100035.0   29154009.0  Crédit Suisse AG, Research Division   \n",
       "64                 100035.0   29154009.0  Crédit Suisse AG, Research Division   \n",
       "65                 100035.0   29154009.0  Crédit Suisse AG, Research Division   \n",
       "66                 100035.0   29154009.0  Crédit Suisse AG, Research Division   \n",
       "67                 100035.0   29154009.0  Crédit Suisse AG, Research Division   \n",
       "...                     ...          ...                                  ...   \n",
       "1065044            312280.0  311968276.0  Crédit Suisse AG, Research Division   \n",
       "1065148            158823.0   29090300.0  Crédit Suisse AG, Research Division   \n",
       "1065179            299086.0  278541573.0  Crédit Suisse AG, Research Division   \n",
       "1065270            349784.0  533359547.0  Crédit Suisse AG, Research Division   \n",
       "1065271            349784.0  533359547.0  Crédit Suisse AG, Research Division   \n",
       "\n",
       "           year   gvkey   fname     lname  \n",
       "63       2015.0  001004  ROBERT  SPINGARN  \n",
       "64       2016.0  001004  ROBERT  SPINGARN  \n",
       "65       2017.0  001004  ROBERT  SPINGARN  \n",
       "66       2018.0  001004  ROBERT  SPINGARN  \n",
       "67       2019.0  001004  ROBERT  SPINGARN  \n",
       "...         ...     ...     ...       ...  \n",
       "1065044  2023.0  352853   VARUN     AHUJA  \n",
       "1065148  2022.0  353070   LARRY   GANDLER  \n",
       "1065179  2022.0  353138  LOKESH      GARG  \n",
       "1065270  2022.0  353444   MIRZA      BAIG  \n",
       "1065271  2023.0  353444   MIRZA      BAIG  \n",
       "\n",
       "[22956 rows x 7 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ciq2[ciq2.companyofperson == \"Crédit Suisse AG, Research Division\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>transcriptpersonid</th>\n",
       "      <th>proid</th>\n",
       "      <th>companyofperson</th>\n",
       "      <th>year</th>\n",
       "      <th>gvkey</th>\n",
       "      <th>fname</th>\n",
       "      <th>lname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [transcriptpersonid, proid, companyofperson, year, gvkey, fname, lname]\n",
       "Index: []"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ciq2.loc[ciq2.companyofperson.str.contains('\\u2019')==True,]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Save down files with converted name format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "ciq2.to_csv(filePath+r\"\\\\ciqAFY_FmtdNms_20240330.csv\",index=False,encoding='utf8')\n",
    "ibes2.to_csv(filePath+r\"\\\\ibesAFY_new_vint_FmtdNms_20240330.csv\",index=False,encoding='Latin-1')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Print variable lengths for merge back to SAS."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "measurer = np.vectorize(len)\n",
    "ciqLen = measurer(ciq2.values.astype(str)).max(axis=0)\n",
    "ibesLen = measurer(ibes2.values.astype(str)).max(axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CIQ\n",
      "transcriptpersonid :  8\n",
      "proid :  12\n",
      "companyofperson :  92\n",
      "year :  6\n",
      "gvkey :  6\n",
      "fname :  19\n",
      "lname :  22\n",
      "\n",
      "IBES\n",
      "ticker :  6\n",
      "estimid :  8\n",
      "amaskcd :  8\n",
      "year :  6\n",
      "permno :  7\n",
      "gvkey :  6\n",
      "analyst :  20\n",
      "lname1 :  17\n",
      "lname2 :  12\n"
     ]
    }
   ],
   "source": [
    "print(\"CIQ\")\n",
    "for l,col in zip(ciqLen,ciq2.columns):\n",
    "    print(col,\": \",l)\n",
    "    \n",
    "print(\"\\nIBES\")\n",
    "for l,col in zip(ibesLen,ibes2.columns):\n",
    "    print(col,\": \",l)\n",
    "#print(\"ciqLen: \",)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(ciq2[ciq2.lname==\"Error\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "ec3226320b2044639bc3591e836dcf86397b4107f563fd339c5134a7fa487843"
  },
  "kernelspec": {
   "display_name": "Python 3.7.4 ('base')",
   "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.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}