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 [ ]: