Clean Data

Clean conference call transcripts and compute variables of analysts' participation in earnings conference calls (Table 5).

Output is at transcript-person level

%matplotlib inline 
%load_ext autoreload
%autoreload 2

import glob, os, re
import pandas as pd
# from functools import reduce
# from functionsgen_1 import *

pd.set_option('display.memory_usage', 'deep')
pd.set_option('display.precision', 2) 
pd.set_option('display.width', 240)
pd.set_option('display.max_rows', 4000)
pd.options.display.max_columns = None
pd.options.display.float_format = '{:,.4f}'.format

from filepath import *

Clean transcripts

Clean transcripts, keep only earnings call transcript and the most editted transcript per event

%%time
trans = pd.read_pickle(filepath_transdetail, compression="zip")
trans = trans.drop_duplicates(subset=['transcriptid']).query('companyid.notnull()')
trans = trans.query('keydeveventtypename=="Earnings Calls"')

CPU times: total: 23.4 s
Wall time: 24.6 s

wrds_gvkey = pd.read_pickle(filepath_gvkey)
wrds_gvkey = wrds_gvkey.query('companyid.notnull() & gvkey.notnull()').drop_duplicates(subset=['gvkey','companyid'])
wrds_gvkey = wrds_gvkey.groupby('companyid').filter(lambda x: len(x)==1)

trans = trans.merge(right=wrds_gvkey[['companyid','gvkey']], on=['companyid'], how='inner')

trans2 = trans[['keydevid','transcriptid','mostimportantdateutc','companyid','gvkey','headline'
                ,'transcriptcollectiontypename', 'transcriptcollectiontypeid'
                , 'transcriptcreationtime_utc', 'audiolengthsec'
                ,'transcriptcreationdate_utc']].query('mostimportantdateutc.notnull()')
# when multiple version of event transcripts exist, keep the lastest
trans2 = trans2.sort_values(by=['keydevid','transcriptid'], ascending=[True, False]).drop_duplicates(subset=['keydevid'])
trans2 = (trans2.sort_values(by=['gvkey','mostimportantdateutc','transcriptid'], ascending=[True, True, False])
          .drop_duplicates(subset=['gvkey','mostimportantdateutc']))
trans2 = trans2.sort_values(by=['gvkey','mostimportantdateutc','transcriptid'])

trans2['M'] = trans2.mostimportantdateutc.dt.month
trans2['Y'] = trans2.mostimportantdateutc.dt.year
trans2['n_peryear'] = trans2.groupby(['gvkey','Y'])['transcriptid'].transform('count')
trans2['n_perym'] = trans2.groupby(['gvkey','Y','M'])['transcriptid'].transform('count')

trans2 = (trans2.sort_values(by=['gvkey','Y','M','transcriptid'], ascending=[True, True, True, False])
          .drop_duplicates(subset=['gvkey','Y','M']))
trans2['n_peryear'] = trans2.groupby(['gvkey','Y'])['transcriptid'].transform('count')
trans2['n_perym'] = trans2.groupby(['gvkey','Y','M'])['transcriptid'].transform('count')

Clean component-level data

%%time
transanalyst = pd.read_pickle(filepath_transana, compression='zip')

CPU times: total: 3h 18min 33s
Wall time: 3h 32min 20s

Compute statistics

transanalyst = (transanalyst.query('speakertypename=="Analysts"')
                [['transcriptid','transcriptcomponentid','transcriptpersonid','proid','transcriptpersonname','word_count']]
                .drop_duplicates().copy())
transanalyst['proid']=transanalyst['proid'].fillna('EMPTY')
transanalyst['n_component'] = transanalyst.groupby(['transcriptid','transcriptpersonname','proid'])['transcriptcomponentid'].transform('count')
transanalyst['n_words'] = transanalyst.groupby(['transcriptid','transcriptpersonname','proid'])['word_count'].transform('sum')
transanalyst['n_op_per_call'] = transanalyst.groupby(['transcriptid'])['transcriptpersonname'].transform('nunique')

transanalyst2 =transanalyst.drop_duplicates(subset=['transcriptid','transcriptpersonname','proid'])
transanalyst2 = transanalyst2.drop(columns=['word_count','transcriptcomponentid'])

Merge with transcript information

%%time
transanalyst3 = transanalyst2.merge(right=trans[['keydevid','transcriptid','mostimportantdateutc','companyid','gvkey']], 
                                    on=['transcriptid'], how='inner')
transanalyst3['year'] = transanalyst3.mostimportantdateutc.dt.year

CPU times: total: 10.8 s
Wall time: 12.1 s

#proid prior to 2011 is sparsely populated, keep obs on or after 2011
transanalyst3['has_proid'] = (transanalyst3['proid']!="EMPTY")*1

transanalyst4 = transanalyst3.query('year>=2011 & proid!="EMPTY"').copy().drop(columns=['has_proid'])
lst_id = trans2['transcriptid'].unique().tolist()
transanalyst4 =transanalyst4.query('transcriptid.isin(@lst_id)')

Add analys

%%time
wrds_prof = pd.read_pickle(filepath_prof, compression='zip')

CPU times: total: 7min 26s
Wall time: 7min 46s

wrds_prof2 =wrds_prof[['proid','personid','companyname']].copy().drop_duplicates(subset=['proid','personid'])

transanalyst4['proid'] = transanalyst4['proid'].astype(float)

transanalyst5 = transanalyst4.merge(right=wrds_prof2[['proid','personid']], on=['proid'], how='inner')
transanalyst5 = transanalyst5.drop_duplicates(subset=['transcriptid','personid'])

linktable = pd.read_csv(filepath_linktable)

transanalyst5 = transanalyst5.merge(right=linktable, on=['personid'], how='inner')
transanalyst5 = transanalyst5.drop_duplicates(subset=['transcriptid','analys'])

Merge and Save

#only keep transcripts with at least one non-corporate participant 
remove_id = transanalyst.query('n_op_per_call==0')['transcriptid'].unique().tolist()
trans3 = trans2.query('~transcriptid.isin(@remove_id)').copy()
trans3 = trans3.query('mostimportantdateutc.dt.year>=2011')

trans3 = (trans3[['transcriptid','gvkey','mostimportantdateutc','companyid']]
         .merge(right=transanalyst[['transcriptid','n_op_per_call']].drop_duplicates(), on=['transcriptid'], how='left')
         .sort_values(by=['gvkey','mostimportantdateutc']))

transanalyst6 = (trans3
                 .merge(right=transanalyst5.drop(columns=['year','mostimportantdateutc','gvkey','companyid','n_op_per_call']), 
                        on=['transcriptid'], how='left'))
transanalyst6['year'] = transanalyst6.mostimportantdateutc.dt.year

transanalyst6['n_analys_per_call'] = transanalyst6.groupby(['transcriptid'])['analys'].transform('nunique')

transanalyst6.to_pickle(outputpath_pkl)
transanalyst6.to_csv(outputpath_csv, index=False)