# Clean Data¶

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

Output is at transcript-person level

In [1]:

    %matplotlib inline
    %load_ext autoreload
    %autoreload 2

In [2]:

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

In [3]:

    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

In [4]:

    from filepath import *

### Clean transcripts¶

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

In [5]:

    %%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

In [6]:

    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)

In [7]:

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

In [8]:

    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'])

In [9]:

    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')

In [10]:

    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¶

In [11]:

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

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

Compute statistics

In [12]:

    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')

In [15]:

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

Merge with transcript information

In [16]:

    %%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

In [17]:

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

In [18]:

    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`

In [20]:

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

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

In [21]:

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

In [22]:

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

In [23]:

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

In [24]:

    linktable = pd.read_csv(filepath_linktable)

In [25]:

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

### Merge and Save¶

In [28]:

    #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')

In [31]:

    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']))

In [34]:

    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

In [35]:

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

In [38]:

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

In [ ]:

In [ ]: