# 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 `transcriptid`s (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 [ ]: