Python 3.10.12 | packaged by Anaconda, Inc. | (main, Jul 5 2023, 19:09:20) [MSC v.1916 64 bit (AMD64)]
Type "copyright", "credits" or "license" for more information.
IPython 8.12.0 -- An enhanced Interactive Python.
Restarting kernel...
In [1]:
...: """
...: Objective: calculating guidance cessation
...: """
...:
...: import pandas as pd
...: from itertools import product
...: import numpy as np
...:
...:
...: guidance_cessation1=pd.read_csv(r"D:\OneDrive - Indiana University\JAR Registered Report\Final_version_submisison\data\cleaned\guidance_cessation_input_for_python.csv",low_memory=False,parse_dates=['FPEDATS','ANNDATS_ACT'])
...:
...: guidance_cessation1.columns = guidance_cessation1.columns.str.lower()
...:
...: guidance_cessation1=guidance_cessation1[['gvkey','fyearq','fqtr','datadate','eps_guide']]
...:
...:
...: guidance_cessation1['gvkey'] = guidance_cessation1['gvkey'].astype(str).str.zfill(6)
...:
...: guidance_cessation1 = guidance_cessation1[guidance_cessation1['eps_guide'].notna()]
...:
...: guidance_cessation1 = guidance_cessation1[guidance_cessation1['gvkey'].notna()]
...:
...: guidance_cessation1.drop_duplicates(subset=['gvkey', 'datadate'],inplace=True)
...:
...: #making all periods to appear in the panel data:
...: guidance_cessation1['period']=guidance_cessation1['fyearq'].astype(str)+'Q'+guidance_cessation1['fqtr'].astype(str)
...:
...:
...: all_periods = pd.DataFrame(list(product(guidance_cessation1['gvkey'].unique(), pd.date_range(start='2007Q1', end='2022Q4', freq='Q'))), columns=['gvkey', 'period'])
...:
...:
...: all_periods['period'] = all_periods['period'].dt.to_period('Q').astype(str)
...:
...:
...:
...: guidance_cessation2 = pd.merge(all_periods, guidance_cessation1, on=['gvkey', 'period'], how='left')
...:
...: guidance_cessation2['eps_guide'].fillna(0, inplace=True)
...:
...:
...:
...: guidance_cessation2.sort_values(by=['gvkey', 'period'], inplace=True)
...:
...:
...: guidance_cessation2.reset_index(drop=True,inplace=True)
...:
...:
...:
...: guidance_cessation3 = guidance_cessation2.groupby('gvkey', sort=False)['eps_guide'].rolling(window=4, min_periods=4).sum().round(0).reset_index()
...:
...: for index,row in guidance_cessation3.iterrows():
...: if pd.isna(row['eps_guide']):
...: guidance_cessation3.loc[index,'eps_guide_cessation']=np.nan
...: elif row['eps_guide']>0:
...: guidance_cessation3.loc[index,'eps_guide_cessation']=0
...: else:
...: guidance_cessation3.loc[index,'eps_guide_cessation']=1
...:
...: guidance_cessation3.reset_index(drop=True, inplace=True)
...:
...: guidance_cessation4 = pd.concat([guidance_cessation3[['eps_guide_cessation']], guidance_cessation2], axis=1)
...:
...:
...: guidance_cessation4[['fyearq', 'fqtr']] = guidance_cessation4['period'].str.split('Q', expand=True)
...:
...: guidance_cessation4['fyearq'] = guidance_cessation4['fyearq'].astype(int)
...: guidance_cessation4['fqtr'] = guidance_cessation4['fqtr'].astype(int)
...:
...: guidance_cessation4.drop(columns=['datadate'],inplace=True)
...: guidance_cessation4.reset_index(drop=True, inplace=True)
...:
...:
...: freq_table = guidance_cessation4['eps_guide_cessation'].value_counts().reset_index()
...: freq_table.columns = ['eps_guide_cessation', 'Frequency']
...:
...:
...: freq_table = freq_table.sort_values(by='eps_guide_cessation')
...:
...:
...: guidance_cessation4.to_csv(r"D:\OneDrive - Indiana University\JAR Registered Report\Final_version_submisison\data\cleaned\guidance_cessation.csv", index=False)
In [2]: