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 create industry median adjusted operating cf volatility over 8 quarters
...: """
...:
...: import pandas as pd
...: from itertools import product
...: import numpy as np
...:
...:
...: compustat_crsp_quarterly1=pd.read_csv(r"D:\OneDrive - Indiana University\JAR Registered Report\Final_version_submisison\data\cleaned\compustat_crsp_merge.csv",
...: dtype={'GVKEY': 'str','FYEARQ':'int','FQTR':'int','operating_cf':'float'},parse_dates=['DATADATE'],low_memory=False)
...:
...:
...: compustat_crsp_quarterly1.columns = compustat_crsp_quarterly1.columns.str.lower()
...:
...: compustat_crsp_quarterly1['gvkey'] = compustat_crsp_quarterly1['gvkey'].str.zfill(6)
...:
...:
...:
...: compustat_crsp_quarterly1=compustat_crsp_quarterly1[['gvkey','datadate','fyearq','fqtr','operating_cf','sic2']]
...:
...: compustat_crsp_quarterly1 = compustat_crsp_quarterly1[compustat_crsp_quarterly1['operating_cf'].notna()]
...:
...: compustat_crsp_quarterly1.drop_duplicates(subset=['gvkey', 'datadate'],inplace=True)
...:
...:
...: compustat_crsp_quarterly1['period']=compustat_crsp_quarterly1['fyearq'].astype(str)+'Q'+compustat_crsp_quarterly1['fqtr'].astype(str)
...:
...:
...:
...: all_periods = pd.DataFrame(list(product(compustat_crsp_quarterly1['gvkey'].unique(), pd.date_range(start='2006Q1', end='2022Q4', freq='Q'))), columns=['gvkey', 'period'])
...:
...:
...: all_periods['period'] = all_periods['period'].dt.to_period('Q').astype(str)
...:
...:
...:
...: compustat_crsp_quarterly2 = pd.merge(all_periods, compustat_crsp_quarterly1, on=['gvkey', 'period'], how='left')
...:
...: compustat_crsp_quarterly2['operating_cf'].fillna(np.nan, inplace=True)
...:
...:
...: compustat_crsp_quarterly2.sort_values(by=['gvkey', 'period'], inplace=True)
...:
...:
...: compustat_crsp_quarterly2.reset_index(drop=True,inplace=True)
...:
...:
...:
...:
...: compustat_crsp_quarterly2['median_operating_cf'] = compustat_crsp_quarterly2.groupby(['sic2','fyearq','fqtr'])['operating_cf'].transform('median')
...:
...:
...: compustat_crsp_quarterly2['adjusted_operating_cf'] = compustat_crsp_quarterly2['operating_cf'] - compustat_crsp_quarterly2['median_operating_cf']
...:
...:
...:
...:
...:
...:
...: compustat_crsp_quarterly2.sort_values(by=['gvkey', 'period'], inplace=True)
...:
...:
...: compustat_crsp_quarterly2.reset_index(drop=True,inplace=True)
...:
...:
...:
...: operating_cf_volatility = compustat_crsp_quarterly2.groupby('gvkey', sort=False)['adjusted_operating_cf'].rolling(window=8, min_periods=4).std().round(3).reset_index()
...:
...:
...: operating_cf_volatility.rename(columns={'adjusted_operating_cf': 'operating_cf_volatility'}, inplace=True)
...:
...: operating_cf_volatility.reset_index(drop=True, inplace=True)
...:
...: operating_cf_volatility2 = pd.concat([operating_cf_volatility[['operating_cf_volatility']], compustat_crsp_quarterly2], axis=1)
...:
...:
...: operating_cf_volatility2[['fyearq', 'fqtr']] = operating_cf_volatility2['period'].str.split('Q', expand=True)
...:
...: operating_cf_volatility2['fyearq'] = operating_cf_volatility2['fyearq'].astype(int)
...: operating_cf_volatility2['fqtr'] = operating_cf_volatility2['fqtr'].astype(int)
...:
...: operating_cf_volatility2.drop(columns=['datadate'],inplace=True)
...:
...: operating_cf_volatility2.reset_index(drop=True, inplace=True)
...:
...:
...: operating_cf_volatility2.drop(columns=['adjusted_operating_cf','operating_cf','sic2','median_operating_cf','period'],inplace=True)
...:
...: operating_cf_volatility2.to_csv(r"D:\OneDrive - Indiana University\JAR Registered Report\Final_version_submisison\data\cleaned\operating_cf_volatility.csv", index=False)
In [2]: