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