Aggregate conference data¶

Output:

  1. Transcript-participant level data with information on analyst (analys) and executives (execid) attending the conference
  2. Transcript level data with conference information and firm ids
In [1]:
import pandas as pd
import numpy as np
import os
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', 500)
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_02 import * # change to reflect input and output file path

1. Load Inputs¶

In [5]:
# schedules
schedule = pd.read_pickle(filepath_schedule)
In [6]:
# transcripts
trans = pd.read_pickle(filepath_trans)

Require non-missing gvkey

In [7]:
trans = trans.query('gvkey.notnull()')
In [8]:
# corporate participants 
CP = pd.read_pickle(filepath_CP)
In [9]:
# other participants 
OP = pd.read_pickle(filepath_OP)
OP = OP.merge(right=trans[['transcriptid','date','gvkey']])

2. Prepare Transcript-Participant Level Data¶

Gather IBES analysts.

In [10]:
df_par = OP.query('is_coveringanalyst==1 & gvkey.notnull()')[['transcriptid','date','analys','par_name','is_coveringanalyst','gvkey']].copy()
df_par['is_exec']=0
In [11]:
lst_trans = df_par['transcriptid'].unique().tolist()

Gather corporate participants. Restrict to transcripts attended by IBES analysts.

In [12]:
CP2 = CP[['transcriptid','par_name','CEO','CFO','COO','Chairman',"President",'CSuite','execid','gender']].copy()
CP2 = CP2.merge(right=trans[['transcriptid','date','gvkey']], how='left', on='transcriptid', indicator=True)
CP2 = CP2.query("transcriptid.isin(@lst_trans) & gvkey.notnull()").rename(columns={'gender':'exec_gender_ecomp'}).drop(columns=['_merge'])
CP2['is_coveringanalyst'] = 0
CP2['is_exec'] = 1

Duplicates at the transcriptid-execid level exist because variations in names. Drop duplicates.

In [13]:
CP2['toDEL'] = 0
CP2.loc[(CP2.execid.notnull()) & (CP2.duplicated(subset=['transcriptid','execid'])==True),'toDEL']=1
In [14]:
CP2 = CP2.query('toDEL==0').drop(columns=['toDEL'])

Combine analysts and executives. Data is at the transcript-participant name level.

In [15]:
df_par = df_par.merge(right=CP2[['transcriptid']].drop_duplicates(), on=['transcriptid'],how='inner')
In [16]:
df_par = pd.concat([df_par, CP2], ignore_index=True)

Upon manual inspection, multiple transcripts can point to the same conference as some transcripts have exactly the same participant informaiton. This happens if different programs of the same conference are recorded as two transcriptids (i.e., the corporate presentation and the Q&A sessions are recorded separately). Manually remove these duplicates (around 400 cases).

In [17]:
# load back manually inspected results
df_par2 = pd.read_pickle("../data/manual/02_df_par2.pkl")

Some statistics

In [18]:
CP_sum = df_par2.groupby(['transcriptid']).agg({'CEO': 'max', 
                                         'CFO': 'max', 
                                         'COO': 'max', 
                                         'Chairman': 'max', 
                                         'President': 'max', 
                                         'CSuite': 'sum'}).reset_index()
CP_sum= (CP_sum.rename(columns={'CEO': 'has_CEO', 
                       'CFO': 'has_CFO', 
                       'CSuite': 'num_CSuite',
                       'COO': 'has_COO', 
                       'Chairman': 'has_Chairman', 
                       'President': 'has_President'}))
In [19]:
df_par2 = df_par2.merge(right=CP_sum, how='inner', on=['transcriptid'])
In [20]:
df_par2.to_csv(outputpath_PAR, index=False)

3. Prepare Transcript Level Data¶

In [21]:
trans2 = trans.merge(right=OP.query('is_coveringanalyst==1')[['transcriptid']].drop_duplicates(), how='left', indicator=True)
trans2.loc[trans2._merge=='both','inIBES']=1
trans2 = trans2.drop(columns=['_merge'])
In [22]:
selvars = ['event_id']
trans2 = trans2.merge(right=schedule[selvars], how='left', on=['event_id'], indicator=True)
trans2 = trans2.drop(columns=['_merge'])
In [23]:
trans2.to_csv(outputpath_trans, index=False)
In [ ]:
In [ ]: