# Link Financial Analysts with IBES `analys`¶

Link non-corporate participants in transcript to IBES `analys`.

In [1]:

    import pandas as pd
    import numpy as np
    import os, openpyxl, re, glob, nltk, 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)

Add `broker_id`

In [6]:

    temp_sc = pd.read_pickle(filepath_schedule)[['permno','broker_id_1','event_id']]
    temp_sc = temp_sc.rename(columns={'permno':'permno_sc'})

In [7]:

    temp = (pd.read_pickle(filepath_trans)
            [['transcriptid','event_id', 'ric','date','permno']]
            .merge(right=temp_sc, how='left', on=['event_id']))

Obtain non-corporate participants

In [8]:

    OP = (parlong
          .query('par_role=="op"')
          [['transcriptid', 'par_name', 'par_firmtitle', '_broker', 'par_seq']]
          .merge(right=temp, how='left', on=['transcriptid']).copy())
    OP = OP.loc[OP.permno.notnull()].copy()
    OP['cyear']=OP.date.dt.year

Remove unidentified audience member

In [9]:

    OP['todelete'] = (OP.par_name.str.contains("unidentified", regex=True, flags=re.I))*1
    OP = OP.query('todelete==0').drop(columns=['todelete'])

Clean broker information from participant title

In [10]:

    def clean_firmtitle(string):
        '''
        Remove common words
        '''
        string2 = string

        # remove common words
        string2 = re.sub(r"analyst|senior analyst|research division|research dividsion|thomson reuters journalist|reuters journalist|thomson reuters media|video presentation|investment|holdings|research|advisor?|llc|\bmd\b", "", string, flags=re.I).strip()
        return string2

In [11]:

    def remove_special_char(string):
        '''
        Remove special characters
        '''
        string2=string

        string2 = re.sub(r"[^a-zA-Z\d\s]", '', string2).strip()

        string2 = re.sub(u'\xa0', u' ', string2).strip()

        string2 = re.sub(r"  ", " ", string2).strip()

        string2 = string2 if len(string2)>3 else None
        return string2

In [12]:

    OP['_firmtitle'] = OP.apply(lambda x: clean_firmtitle(x['par_firmtitle']), axis=1)
    OP['_firmtitle'] = OP.apply(lambda x: remove_special_char(x['_firmtitle']), axis=1)

Use forecast issuance information extracted from IBES (analyst-broker-firm-forecast date), merge other participants with IBES analysts. Analyst names were manually collected.

In [13]:

    ibesdates=pd.read_stata(filepath_ibesdata)
    ibesdates['analys_name']=ibesdates['analys_name'].str.lower()
    ibesdates['fname']=ibesdates['fname'].str.lower()
    ibesdates['lname']=ibesdates['lname'].str.lower()

Match using firm id, broker id, and analyst names. Manually clean and refine the match.

In [14]:

    match1=(OP[['transcriptid','par_name','permno','broker_id_1','date']]
            .copy()
            .query('broker_id_1.notnull()')
            .merge(right=ibesdates[['permno','analys','lname','fname','analys_name','broker_id_1','afemale','anndats']],
                   how='inner',on=['permno','broker_id_1']))
    A = [d.date() for d in match1['date']]
    B = [d.date() for d in match1['anndats']]
    match1['gap'] = np.busday_count(A, B)

In [15]:

    %%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.par_name)), 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.par_name)), 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.par_name)), axis=1))
            .compute(scheduler='processes'))
    match1['score1'] = score1
    match1['score2'] = score2
    match1['score3'] = score3

    CPU times: total: 14.6 s
    Wall time: 1min 15s

Various cutoff scores are determined via manual inspection

In [16]:

    cuttoff1 = 80
    match1 = match1.query('score1>=@cuttoff1 | score2>=@cuttoff1 | score3>=@cuttoff1')
    match1['avgscore'] = match1.filter(regex="score").mean(axis=1)

In [17]:

    match1['_pre'] = (match1['gap']<0)*1
    match1['_post'] = (match1['gap']>0)*1
    match1['_gap_pre'] = np.where(match1['gap']<0, match1['gap'], np.nan)
    match1['_gap_post'] = np.where(match1['gap']>0, match1['gap'], np.nan)
    match1['_gap_abs'] = abs(match1['gap'])
    match1['_withinoneyear'] = (match1['_gap_abs']<=126)*1

In [18]:

    match1 = match1.sort_values(by=['transcriptid', 'par_name', '_withinoneyear','avgscore', '_gap_abs'],
                                ascending=[True, True, False, False, True])
    match1 = match1.drop_duplicates(subset=['transcriptid', 'par_name'])

In [19]:

    match1.query('_gap_abs>126 & score1<80').sort_values(by=['score1'])
    match1['DEL'] = ((match1._gap_abs>126) & (match1.score1<55))*1
    match1 = match1.query('DEL==0').drop(columns=['DEL'])

For observations without broker ids, manually match based on broker names, firm, and analyst names. Next, manually inspect and clean matched observations from both steps.

In [20]:

    # load results from manual inspection
    result = pd.read_pickle("../data/manual/01_3A_result.pkl")

Merge back

In [21]:

    selvars=['transcriptid','par_name','analys','broker_id_1','afemale','lname']
    OP2 = OP.merge(right=result[selvars], on=['transcriptid','par_name'], how='left', indicator=True)
    OP2['is_coveringanalyst'] = (OP2['_merge']=='both')*1
    OP2 = OP2.drop(columns=['_merge'])

In [22]:

    selvars =['transcriptid','par_name','par_seq',
               'analys','is_coveringanalyst',
               'afemale']

In [23]:

    OP_final = OP2.copy()

In [24]:

    OP_final[selvars].to_pickle(outputpath_op)

In [25]:

    parlong2 = parlong.merge(right=OP_final[['transcriptid','par_name','analys','is_coveringanalyst', 'afemale']],
                            on=['transcriptid','par_name'], how='left')

In [26]:

    parlong2['has_coveringanalyst'] = parlong2.groupby(['transcriptid'])['is_coveringanalyst'].transform('max')
    parlong2 = parlong2.sort_values(by=['transcriptid','par_seq'])

In [27]:

    covering_analys = parlong2.query('analys.notnull()')[['transcriptid','analys']].copy()
    covering_analys['analys'] = covering_analys['analys'].astype(str)
    covering_analys = covering_analys.groupby(['transcriptid'])['analys'].apply("|".join).reset_index().rename(columns={'analys':'covering_analys'})

In [28]:

    parlong2.to_pickle(outputpath_par2)

In [ ]:

In [ ]: