# 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`