# Link Executives to Execucomp¶

From corporate participants identified in transcripts, link to execucomp `execid`.

In [1]:

    import pandas as pd
    import numpy as np
    import os, re, multiprocessing
    from fuzzywuzzy import fuzz
    import dask.dataframe as dd

In [2]:

    %matplotlib inline
    %load_ext autoreload
    %autoreload 2

In [3]:

    pd.set_option('display.memory_usage', 'deep')
    pd.set_option('display.precision', 2)
    pd.set_option('display.width', 200)
    pd.set_option('display.max_rows', 300)
    pd.set_option('display.max_columns', None)
    pd.set_option('display.max_colwidth', 80)
    pd.options.display.float_format = '{:,.4f}'.format

In [4]:

    from filepath_01 import * # change to reflect input and output file path

Load participants data

In [5]:

    parlong = pd.read_pickle(filepath_PAR)

In [6]:

    temp = pd.read_pickle(filepath_trans)[['transcriptid','date','gvkey']]

Only gather corporate participants (cp), add `gvkey` from transcript information

In [7]:

    CP = (parlong
          .query('par_role=="cp"')
          [['transcriptid', 'par_name', 'par_firmtitle', '_firm', 'par_seq','CEO','CFO','IRO','CSuite']]
          .merge(right=temp, how='left', on=['transcriptid']).copy())
    CP = CP.loc[CP.gvkey.notnull()]
    CP['year']=CP.date.dt.year
    CP = CP.drop_duplicates(subset=['transcriptid','par_name'])

In [8]:

    CP = CP.reset_index().rename(columns={'index':'par_id'})

Extract first and last name

In [9]:

    CP['par_lname'] = CP['par_name'].str.split().str[-1]
    CP['par_fname'] = CP['par_name'].str.split().str[0]

Extract and clean firm and title info

In [10]:

    def separate_firmtitle_info(row):
        string = row['par_firmtitle']

        firm, title= "XXXXXXXXX", "XXXXXXXXX"
        check1, check2 = 0, 0

        if len(re.findall(r" \- ", string, flags=re.I))>1:
            if fuzz.token_set_ratio(string, row['_firm'])>=95:
                firm=row['_firm']
                title=string
                for word in row['_firm'].split():
                    title = re.sub(word, "", title,flags=re.I)
            else:
                check1=1

        elif len(re.findall(r" \- ", string, flags=re.I))==1:
            string1 = string.split(" - ")[0]
            string2 = string.split(" - ")[1]

            if fuzz.token_set_ratio(string1, row['_firm'])>=95:
                firm=string1
                title=string2
            elif re.findall(r"\bceo\b|\bcfo\b|\bchairman\b|\bevp\b|\bsvp\b|\bvp\b|\bexecutive\b|\bchief\b|\bsenior\b|\bir\b", string2, flags=re.I):
                firm=string1
                title=string2
            elif fuzz.token_set_ratio(string2, row['_firm'])>=95:
                firm=string2
                title=string1
                check2=1
            elif re.findall(r"\bceo\b|\bcfo\b|\bchairman\b|\bevp\b|\bsvp\b|\bvp\b|\bexecutive\b|\bchief\b|\bsenior\b|\bir\b", string1, flags=re.I):
                firm=string2
                title=string1
                check2=1
        else:
            check1=2
        return firm, title, check1, check2

In [11]:

    CP[['_guessfirm', '_guesstitle','_check1', '_check2']] = CP.apply(lambda x: separate_firmtitle_info(x), axis=1, result_type='expand')

In [12]:

    CP['COO'] = CP.par_firmtitle.str.contains(r"chief operating officer|\bCOO\b", regex=True, flags=re.I)*1
    CP['CPO'] = CP.par_firmtitle.str.contains(r"chief product officer|\bCPO\b", regex=True, flags=re.I)*1
    CP['CRO'] = CP.par_firmtitle.str.contains(r"chief risk officer|\bCRO\b", regex=True, flags=re.I)*1
    CP['OCSuite'] = np.where((CP.CSuite==1) & (CP.COO==0) & (CP.CEO==0) & (CP.CFO==0) & (CP.CPO==0) & (CP.CRO==0), 1,0)
    CP['President'] = CP.par_firmtitle.str.contains(r"president", regex=True, flags=re.I)*1
    CP['Chairman'] = CP.par_firmtitle.str.contains(r"chairman", regex=True, flags=re.I)*1
    CP['GC'] = CP.par_firmtitle.str.contains(r"general counsel", regex=True, flags=re.I)*1

Merge with Execucomp

In [13]:

    selvars=['EXECID','GVKEY','YEAR', 'CONAME',
             'GENDER', 'EXEC_FULLNAME', 'EXEC_LNAME', 'EXEC_FNAME', 'EXEC_MNAME', 'TITLE', 'TITLEANN',
             'CEOANN', 'CFOANN']
    anncomp = pd.read_sas(filepath_anncomp,format = 'sas7bdat', encoding="latin")[selvars]
    anncomp.columns =anncomp.columns.str.lower()
    anncomp = anncomp.drop_duplicates(subset=['gvkey', 'execid', 'year'])

Clean last name

In [14]:

    temp = anncomp.exec_lname.str.split(",", expand=True)
    anncomp['exec_lnamecln'] = temp[0]

Clean title

In [15]:

    anncomp['CEO_ec1'] = (anncomp['ceoann']=='CEO')*1
    anncomp['CFO_ec1'] = (anncomp['cfoann']=='CFO')*1
    anncomp['titleall'] = anncomp['title'].fillna("") + " " + anncomp['titleann'].fillna("")
    anncomp = anncomp.drop(columns=['ceoann','cfoann'])

In [16]:

    anncomp['CEO_ec2'] = anncomp['titleall'].str.contains(r"chief executive officer|\bCEO\b", regex=True, flags=re.I)*1

In [17]:

    anncomp['CEO_ec'] = anncomp.filter(regex=r'CEO_ec\d').max(axis=1)

In [18]:

    anncomp['CFO_ec2'] = anncomp['titleall'].str.contains(r"chief financial officer|\bCFO\b", regex=True, flags=re.I)*1

In [19]:

    anncomp['CFO_ec'] = anncomp.filter(regex=r'CFO_ec\d').max(axis=1)

In [20]:

    anncomp['COO_ec'] = anncomp.titleall.str.contains(r"chief operating officer|\bCOO\b", regex=True, flags=re.I)*1
    anncomp['CPO_ec'] = anncomp.titleall.str.contains(r"chief product officer|\bCPO\b", regex=True, flags=re.I)*1
    anncomp['CRO_ec'] = anncomp.titleall.str.contains(r"chief risk officer|\bCRO\b", regex=True, flags=re.I)*1
    anncomp['CSuite_ec'] = anncomp['titleall'].str.contains(r"chief \w{4,15} officer|\bC[A-Z]O\b", regex=True, flags=re.I)*1
    anncomp['OCSuite_ec'] = np.where((anncomp.CSuite_ec==1) & (anncomp.COO_ec==0) & (anncomp.CEO_ec==0)
                                     & (anncomp.CFO_ec==0) & (anncomp.CPO_ec==0) & (anncomp.CRO_ec==0), 1,0)
    anncomp['President_ec'] = anncomp.titleall.str.contains(r"president", regex=True, flags=re.I)*1
    anncomp['Chairman_ec'] = anncomp.titleall.str.contains(r"chairman", regex=True, flags=re.I)*1
    anncomp['GC_ec'] = anncomp.titleall.str.contains(r"general counsel", regex=True, flags=re.I)*1

Match based on firm, year, name, and title.

In [21]:

    lst_title = ['CEO','CFO','COO','CRO','CPO','OCSuite','President','Chairman','GC']

In [22]:

    selvars=['transcriptid','par_id','gvkey','year','par_name','par_lname','par_fname','_guesstitle']+lst_title
    selvars2=['exec_lnamecln', 'exec_fname', 'exec_fullname','execid','gvkey','year','title','titleann']+[title+'_ec' for title in lst_title]
    match_0=(CP[selvars].copy()
            .query('gvkey.notnull()')
            .merge(right=anncomp[selvars2], how='inner',on=['gvkey','year']))
    match_l1=(CP[selvars].copy()
            .query('gvkey.notnull()')
            .merge(right=anncomp[selvars2].assign(year=lambda x: x.year-1),how='inner',on=['gvkey','year']))
    match_n1=(CP[selvars].copy()
            .query('gvkey.notnull()')
            .merge(right=anncomp[selvars2].assign(year=lambda x: x.year+1),how='inner',on=['gvkey','year']))
    match_end=(CP[selvars].copy()
            .query('gvkey.notnull() & year>=2021')
            .merge(right=anncomp[selvars2].assign(year=lambda x: x.year+2),how='inner',on=['gvkey','year']))
    match1 = (pd.concat([match_0,match_l1, match_n1, match_end], ignore_index=True)
              .drop_duplicates(subset=['transcriptid','par_name','execid']))

In [23]:

    %%time
    scoreNl = (dd.from_pandas(match1, npartitions = 4*multiprocessing.cpu_count())
            .map_partitions(lambda row: row.apply((lambda row: fuzz.token_set_ratio(row.exec_lnamecln, row.par_name)), axis=1))
            .compute(scheduler='processes'))
    scoreNlcln = (dd.from_pandas(match1, npartitions = 4*multiprocessing.cpu_count())
            .map_partitions(lambda row: row.apply((lambda row: fuzz.token_set_ratio(row.exec_lnamecln, row.par_lname)), axis=1))
            .compute(scheduler='processes'))
    scoreNf = (dd.from_pandas(match1, npartitions = 4*multiprocessing.cpu_count())
            .map_partitions(lambda row: row.apply((lambda row: fuzz.token_set_ratio(row.exec_fname, row.par_fname)), axis=1))
            .compute(scheduler='processes'))
    scoreNfull = (dd.from_pandas(match1, npartitions = 4*multiprocessing.cpu_count())
            .map_partitions(lambda row: row.apply((lambda row: fuzz.token_set_ratio(row.exec_fullname, row.par_name)), axis=1))
            .compute(scheduler='processes'))
    scoreT1 = (dd.from_pandas(match1, npartitions = 4*multiprocessing.cpu_count())
            .map_partitions(lambda row: row.apply((lambda row: fuzz.token_set_ratio(row.title, row._guesstitle)), axis=1))
            .compute(scheduler='processes'))
    scoreT2 = (dd.from_pandas(match1, npartitions = 4*multiprocessing.cpu_count())
            .map_partitions(lambda row: row.apply((lambda row: fuzz.token_set_ratio(row.titleann, row._guesstitle)), axis=1))
            .compute(scheduler='processes'))
    match1['scoreN_lnamecln'] = scoreNlcln
    match1['scoreN_fname'] = scoreNf
    match1['scoreN_full'] = scoreNfull
    match1['scoreN_lname'] = scoreNl
    match1['scoreT1'] = scoreT1
    match1['scoreT2'] = scoreT2

    CPU times: total: 30.3 s
    Wall time: 1min 16s

In [24]:

    %%time
    for var in lst_title:
        match1[var] = (match1[var]==1) * (match1[var+'_ec']==1)*1
    match1 = match1.drop(columns=[title+'_ec' for title in lst_title])
    match1['scoreNavg'] = match1.filter(regex='scoreN_').mean(axis=1)
    match1['scoreNmax'] = match1.filter(regex='scoreN_').max(axis=1)
    match1['scoreTavg'] = match1.filter(regex='scoreT').mean(axis=1)
    match1['scoreTmax'] = match1.filter(regex='scoreT').max(axis=1)

    CPU times: total: 406 ms
    Wall time: 426 ms

After imposing initial cutoff scores, export for manual inspection and verification

In [25]:

    match1 = match1.query('scoreNmax>=50')
    match1 = match1.drop_duplicates(subset=['par_id','execid'])

Load back manually inspected matched records

In [26]:

    result2 = pd.read_pickle("../data/manual/01_3B_result2.pkl")

In [27]:

    CP2 = CP.merge(right=result2[['gvkey','par_name','execid']], how='left', on=['gvkey','par_name'])

In [28]:

    CP2 = CP2.merge(right=anncomp[['gvkey','execid','gender']].drop_duplicates(), how='left', on=['gvkey','execid'])

In [29]:

    selvars = ['transcriptid','par_seq','par_name','execid','gender',
               'CEO','CFO','CSuite', 'COO', 'CPO', 'CRO', 'President', 'Chairman']

In [30]:

    CP2[selvars].to_pickle(outputpath_cp)

In [ ]: