Link personid to IBES analys¶

For analysts who attended an earnings conference, matching is based on 1) the covered firm, 2) the brokerage, and 3) the analyst's name. For analysts without information on the covered firm, matching is based on 1) the brokerage and 2) the analyst's name. This code computes fuzzy name scores before exporting for manual inspection and matching. The final output is a linktable between personid and analys.

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]:
filepath_raw1=os.path.abspath("../data/analystgvkeydate.csv")
filepath_raw2=os.path.abspath("../data/analystrole.csv")

Step 1: Match transcript analysts¶

In [5]:
raw1 = pd.read_csv(filepath_raw1, encoding='latin-1',low_memory=False,parse_dates=['date'])
raw1.columns = raw1.columns.str.lower()
raw1 = raw1.reset_index().rename(columns={'index':'tempid'})
In [6]:
out=raw1['personname'].str.split(', ',expand=True)
raw1['person_lname']=out[0]
raw1['person_fname']=out[1]

Load permno-analys-anndats info from IBES

In [7]:
ibesdates=pd.read_stata("../data/ibes_permno_analys_anndats.dta").rename(columns={'broker_id_1':'broker_id'})
ibesdates['analys_name']=ibesdates['analys_name'].str.lower()
ibesdates['fname']=ibesdates['fname'].str.lower()
ibesdates['lname']=ibesdates['lname'].str.lower()

Use permno and broker_id if available. Compute fuzzy name scores.

In [8]:
match1 = raw1.query('broker_id.notnull()')
In [9]:
%%time
match1=(match1.merge(right=ibesdates[['permno','analys','lname','fname','analys_name','broker_id','afemale','anndats']],
                     how='inner',on=['permno','broker_id']))
A = [d.date() for d in match1['date']]
B = [d.date() for d in match1['anndats']]
match1['gap'] = np.busday_count(A, B)

CPU times: total: 1min 49s
Wall time: 1min 55s
In [10]:
%%time
score1 = (dd.from_pandas(match1, npartitions = 4*multiprocessing.cpu_count())
        .map_partitions(lambda row: row.apply((lambda row: fuzz.token_set_ratio(row.lname, row.person_lname)), axis=1))
        .compute(scheduler='processes'))
score2 = (dd.from_pandas(match1, npartitions = 4*multiprocessing.cpu_count())
        .map_partitions(lambda row: row.apply((lambda row: fuzz.token_set_ratio(row.fname, row.person_fname)), axis=1))
        .compute(scheduler='processes'))
score3 = (dd.from_pandas(match1, npartitions = 4*multiprocessing.cpu_count())
        .map_partitions(lambda row: row.apply((lambda row: fuzz.token_set_ratio(row.analys_name, row.personname)), axis=1))
        .compute(scheduler='processes'))
match1['score1'] = score1
match1['score2'] = score2
match1['score3'] = score3

CPU times: total: 5min 34s
Wall time: 15min 15s
In [11]:
match1['avgscore'] = match1.filter(regex="score").mean(axis=1)
match1['_gap_abs'] = abs(match1['gap'])
match1['_withinoneyear'] = (match1['_gap_abs']<=126)*1

Export for manual inspection and matching.

Next, for records without a broker ID, inspect and manually match based on broker names and analyst names.

Step 2: Match remaining analysts¶

For the remaining unmatched analysts in CapitalIQ (without information on covered firm through earnings call participation), match based on broker names and analyst names.

In [12]:
# import previous matched results
resultA = pd.read_pickle("../data/manual/resultA.pkl")
In [13]:
raw2 = pd.read_csv(filepath_raw2, encoding='latin-1',low_memory=False).drop_duplicates(subset=['personname','companyname'])
raw2 = raw2.query('isEquityAnalyst==1 & isocountry2 =="US"').drop(columns=['isTranscriptAnalyst','isEquityAnalyst', 'isResearchFirm',
                                                                           'profunctionname', 'title', 'profunctionid', 'companyid',
                                                                           'startyear', 'endyear',
                                                                           'isocountry2','isocountry3','region','country'])
temp_PERSON = resultA['personid'].unique().tolist()
raw2 = raw2.query('personid not in(@temp_PERSON)')
raw2 = raw2.reset_index().drop(columns='index').reset_index().rename(columns={'index':'tempid'})
In [14]:
out=raw2['personname'].str.split(', ',expand=True)
raw2['person_lname']=out[0]
raw2['person_fname']=out[1]

Gather unmatched analys from IBES

In [15]:
temp_ID = resultA['analys'].unique().tolist()
ibesdates = ibesdates.query('analys not in(@temp_ID)')[['analys', 'analys_name','fname','lname',
                                                        'IBES_id','estimate_names','broker_id']].drop_duplicates()

Similarly, first use broker ID information if available, otherwise, fuzzy match on broker names. Compute fuzzy scores on analyst names.

In [16]:
%%time
Bmatch1 = raw2.query('broker_id.notnull()')
Bmatch1=(Bmatch1.merge(right=ibesdates,how='inner',on=['broker_id']))

CPU times: total: 1.48 s
Wall time: 2.83 s
In [17]:
%%time
score1 = (dd.from_pandas(Bmatch1, npartitions = 4*multiprocessing.cpu_count())
        .map_partitions(lambda row: row.apply((lambda row: fuzz.token_set_ratio(row.lname, row.person_lname)), axis=1))
        .compute(scheduler='processes'))
score2 = (dd.from_pandas(Bmatch1, npartitions = 4*multiprocessing.cpu_count())
        .map_partitions(lambda row: row.apply((lambda row: fuzz.token_set_ratio(row.fname, row.person_fname)), axis=1))
        .compute(scheduler='processes'))
score3 = (dd.from_pandas(Bmatch1, npartitions = 4*multiprocessing.cpu_count())
        .map_partitions(lambda row: row.apply((lambda row: fuzz.token_set_ratio(row.analys_name, row.personname)), axis=1))
        .compute(scheduler='processes'))
Bmatch1['score1'] = score1
Bmatch1['score2'] = score2
Bmatch1['score3'] = score3

CPU times: total: 49.2 s
Wall time: 3min 1s
In [18]:
cuttoff1 = 80
Bmatch1 = Bmatch1.query('score1>=@cuttoff1')
cuttoff2 = 90
Bmatch1 = Bmatch1.query('score1>=@cuttoff2 | score2>=@cuttoff2 | score3>=@cuttoff2')
Bmatch1['avgscore'] = Bmatch1.filter(regex="score").mean(axis=1)

Export for manual inspection and matching.

Next, for records without a broker ID, inspect and manually match based on broker names and analyst names.

Matches from step 1 and 2 are collected and inspected again. The final output is a linktable between personid and analys