{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Compustat Data: Net Income Yield Calculations" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Import stuff" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "data": { "text/html": [ "" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "#display options\n", "from IPython.core.display import display, HTML\n", "display(HTML(\"\"))\n", "\n", "import numpy as np\n", "import pandas as pd\n", "import time\n", "\n", "%matplotlib inline\n", "import matplotlib as mpl\n", "mpl.rc(\"savefig\",dpi=100)\n", "from matplotlib import pyplot as plt\n", "\n", "#pandas option\n", "pd.options.display.max_columns = 50\n", "idx = pd.IndexSlice\n", "\n", "def wins_one(df_var, p = 0.01):\n", " return df_var.groupby(level = 1).apply(lambda x: x.clip(lower = x.quantile(p), upper = x.quantile(1. - p)))\n", "\n", "percentile = [0.1, 0.25, 0.50, 0.75, 0.9][::-1]\n", "scf_years=[1988,1991,1994,1997,2000,2003,2006,2009,2012,2015]\n", "all_years=range(1980,2017)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Load compustat data\n", "Load compustat data from WRDS. We use annual data from 1980-2016. The following variables (named as per WRDS conventions) are used in our analysis. \n", "* GVKEY -- Global Company Key\t\n", "* FYEAR -- Data Year - Fiscal\t\n", "* FIC -- Foreign Incorporation Code\t\n", "* CSHO -- Common Shares Outstanding\t\n", "* PRCC_F -- Price Close - Annual - Fiscal\t\n", "* PI -- Pretax Income\n" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "#load compustat data\n", "df = pd.read_stata('./compustat_mergenfo_plus_sale_at.dta')\n", "df['fyear'] = df['fyear'].astype(int)" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "#set index after sorting\n", "df = df.drop_duplicates(subset = ['gvkey', 'fyear']) #this is not ok.\n", "df = df.sort_values(['gvkey', 'fyear'])\n", "df = df.set_index(['gvkey', 'fyear'], verify_integrity = True, drop = False)\n", "df = df[df['fic'] == 'USA']" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Measurement Concepts\n", "We now construct some user-defined variables:\n", "* mkvalt: market value at the close of fiscal year, MARKET VALUE = CSHO x PRCC_F\n", "* wgt_mkvalt: weights proportional to mkvalt\n", "* pi_mval_ratio: Net income yield, PI/mkvalt\n", "* X_wins: winsorised version of X" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "#calculate self-defined market value variable\n", "df['mkvalt'] = df['csho'] * df['prcc_f']\n", "df = df[pd.notnull(df['pi']) & pd.notnull(df['dvt']) & (pd.notnull(df['mkvalt']))]\n", "#construct value weight\n", "df['wgt_mval'] = df['mkvalt'] / df['mkvalt'].groupby(level = 1).sum()\n", "#create income yield vars\n", "df['pi_mval_ratio'] = (df['pi']/df['mkvalt']).replace([np.inf, -np.inf], np.nan)\n", "#winsorize\n", "df['pi_mval_ratio_wins'] = wins_one(df['pi_mval_ratio'])" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "sel=df['fyear'].isin(scf_years)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Moments \n", "\n", "To change to scf_years replace the variable \"all_years\" by \"scf_years.\" Means are computed after winsorizing. \n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Pre-tax income/ Market Value for All Firms for SCF survey years" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
numEW_meanVW_meanp10p25p50p75p90
fyear
19885268.0-0.0647540.124336-0.346054-0.0634100.0782310.1405170.193712
19915161.0-0.1781240.061864-0.489336-0.0711730.0492830.1031160.156330
19946788.0-0.0122270.098175-0.247581-0.0175750.0738970.1257530.173712
19977291.0-0.0470930.062075-0.263591-0.0432510.0518590.0902760.123436
20006396.0-0.2757890.046411-0.752837-0.1186060.0481910.1244770.197480
20035057.0-0.0145580.061809-0.195183-0.0322970.0481140.0877090.119153
20064813.00.0033270.079718-0.159975-0.0060030.0544700.0883860.121192
20094210.0-0.1623570.056758-0.502311-0.1168130.0311540.0895540.135849
20123897.0-0.0232850.079632-0.243603-0.0179920.0598790.1045240.151912
20154062.0-0.1485650.054432-0.369993-0.0662050.0412770.0842780.120208
\n", "
" ], "text/plain": [ " num EW_mean VW_mean p10 p25 p50 p75 \\\n", "fyear \n", "1988 5268.0 -0.064754 0.124336 -0.346054 -0.063410 0.078231 0.140517 \n", "1991 5161.0 -0.178124 0.061864 -0.489336 -0.071173 0.049283 0.103116 \n", "1994 6788.0 -0.012227 0.098175 -0.247581 -0.017575 0.073897 0.125753 \n", "1997 7291.0 -0.047093 0.062075 -0.263591 -0.043251 0.051859 0.090276 \n", "2000 6396.0 -0.275789 0.046411 -0.752837 -0.118606 0.048191 0.124477 \n", "2003 5057.0 -0.014558 0.061809 -0.195183 -0.032297 0.048114 0.087709 \n", "2006 4813.0 0.003327 0.079718 -0.159975 -0.006003 0.054470 0.088386 \n", "2009 4210.0 -0.162357 0.056758 -0.502311 -0.116813 0.031154 0.089554 \n", "2012 3897.0 -0.023285 0.079632 -0.243603 -0.017992 0.059879 0.104524 \n", "2015 4062.0 -0.148565 0.054432 -0.369993 -0.066205 0.041277 0.084278 \n", "\n", " p90 \n", "fyear \n", "1988 0.193712 \n", "1991 0.156330 \n", "1994 0.173712 \n", "1997 0.123436 \n", "2000 0.197480 \n", "2003 0.119153 \n", "2006 0.121192 \n", "2009 0.135849 \n", "2012 0.151912 \n", "2015 0.120208 " ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "EW_pi_mval=(df[sel]['pi_mval_ratio_wins']).groupby(level = 1).mean()\n", "VW_pi_mval=(df[sel]['pi_mval_ratio_wins']*df[sel]['wgt_mval']).groupby(level = 1).sum()\n", "\n", "num=(df[sel]['pi_mval_ratio_wins']).groupby(level = 1).count()\n", "\n", "pi_mval_mom=pd.DataFrame([num,EW_pi_mval,VW_pi_mval])\n", "pi_mval_mom=pi_mval_mom.T\n", "pi_mval_mom.columns=['num','EW_mean','VW_mean']\n", "\n", "per_pi_mval=[df[sel]['pi_mval_ratio'].groupby(level = 1).quantile(p) for p in percentile]\n", "per_head=['p90','p75','p50','p25','p10']\n", "for p in range(len(percentile)):\n", " pi_mval_mom[per_head[p]]=per_pi_mval[p]\n", "\n", " \n", "pi_mval_mom=pi_mval_mom[['num','EW_mean','VW_mean','p10','p25','p50','p75','p90']]\n", "pi_mval_mom.to_csv('compustat_pi_mval_all_firms.csv')\n", "pi_mval_mom\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Pre-tax income/ Market Value for All Firms for SCF all years" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
numEW_meanVW_meanp10p25p50p75p90
fyear
19885268.0-0.0647540.124336-0.346054-0.0634100.0782310.1405170.193712
19895112.0-0.0832870.098167-0.394644-0.0754550.0664300.1294290.180700
19905057.0-0.2535530.100187-0.680184-0.1070050.0703010.1417390.209138
19915161.0-0.1781240.061864-0.489336-0.0711730.0492830.1031160.156330
19925321.0-0.0544140.067641-0.289328-0.0450080.0576540.1023870.149803
19936416.0-0.0140240.072135-0.217731-0.0286650.0593170.1078330.154211
19946788.0-0.0122270.098175-0.247581-0.0175750.0738970.1257530.173712
19956931.0-0.0103220.081934-0.219226-0.0213530.0621890.1115510.152348
19967344.0-0.0184390.076935-0.220585-0.0293290.0561150.1054300.144862
19977291.0-0.0470930.062075-0.263591-0.0432510.0518590.0902760.123436
19986894.0-0.0676930.049626-0.355223-0.0643570.0550000.1040750.154980
19996670.0-0.0119150.048989-0.240048-0.0367180.0551050.1242910.184916
20006396.0-0.2757890.046411-0.752837-0.1186060.0481910.1244770.197480
20015741.0-0.2871460.023116-0.778434-0.1440480.0316440.0955960.144780
20025349.0-0.2441450.048600-0.762450-0.1325520.0535050.1059570.154910
20035057.0-0.0145580.061809-0.195183-0.0322970.0481140.0877090.119153
20045006.00.0038010.066693-0.153550-0.0103700.0518210.0847210.114044
20054906.00.0014570.077524-0.155835-0.0090430.0537850.0897080.119634
20064813.00.0033270.079718-0.159975-0.0060030.0544700.0883860.121192
20074692.0-0.0391590.069316-0.245041-0.0282400.0514490.0950400.132419
20084437.0-0.5338310.038887-1.429045-0.2861590.0372560.1129950.183122
20094210.0-0.1623570.056758-0.502311-0.1168130.0311540.0895540.135849
20104082.0-0.0294810.082566-0.214753-0.0265240.0523370.0960340.145340
20113966.0-0.0368480.093309-0.276506-0.0180060.0625690.1083870.159660
20123897.0-0.0232850.079632-0.243603-0.0179920.0598790.1045240.151912
20133963.0-0.0093170.071341-0.162604-0.0199960.0466450.0817380.116978
20144106.0-0.0330930.066426-0.205406-0.0302640.0440360.0823230.116619
20154062.0-0.1485650.054432-0.369993-0.0662050.0412770.0842780.120208
\n", "
" ], "text/plain": [ " num EW_mean VW_mean p10 p25 p50 p75 \\\n", "fyear \n", "1988 5268.0 -0.064754 0.124336 -0.346054 -0.063410 0.078231 0.140517 \n", "1989 5112.0 -0.083287 0.098167 -0.394644 -0.075455 0.066430 0.129429 \n", "1990 5057.0 -0.253553 0.100187 -0.680184 -0.107005 0.070301 0.141739 \n", "1991 5161.0 -0.178124 0.061864 -0.489336 -0.071173 0.049283 0.103116 \n", "1992 5321.0 -0.054414 0.067641 -0.289328 -0.045008 0.057654 0.102387 \n", "1993 6416.0 -0.014024 0.072135 -0.217731 -0.028665 0.059317 0.107833 \n", "1994 6788.0 -0.012227 0.098175 -0.247581 -0.017575 0.073897 0.125753 \n", "1995 6931.0 -0.010322 0.081934 -0.219226 -0.021353 0.062189 0.111551 \n", "1996 7344.0 -0.018439 0.076935 -0.220585 -0.029329 0.056115 0.105430 \n", "1997 7291.0 -0.047093 0.062075 -0.263591 -0.043251 0.051859 0.090276 \n", "1998 6894.0 -0.067693 0.049626 -0.355223 -0.064357 0.055000 0.104075 \n", "1999 6670.0 -0.011915 0.048989 -0.240048 -0.036718 0.055105 0.124291 \n", "2000 6396.0 -0.275789 0.046411 -0.752837 -0.118606 0.048191 0.124477 \n", "2001 5741.0 -0.287146 0.023116 -0.778434 -0.144048 0.031644 0.095596 \n", "2002 5349.0 -0.244145 0.048600 -0.762450 -0.132552 0.053505 0.105957 \n", "2003 5057.0 -0.014558 0.061809 -0.195183 -0.032297 0.048114 0.087709 \n", "2004 5006.0 0.003801 0.066693 -0.153550 -0.010370 0.051821 0.084721 \n", "2005 4906.0 0.001457 0.077524 -0.155835 -0.009043 0.053785 0.089708 \n", "2006 4813.0 0.003327 0.079718 -0.159975 -0.006003 0.054470 0.088386 \n", "2007 4692.0 -0.039159 0.069316 -0.245041 -0.028240 0.051449 0.095040 \n", "2008 4437.0 -0.533831 0.038887 -1.429045 -0.286159 0.037256 0.112995 \n", "2009 4210.0 -0.162357 0.056758 -0.502311 -0.116813 0.031154 0.089554 \n", "2010 4082.0 -0.029481 0.082566 -0.214753 -0.026524 0.052337 0.096034 \n", "2011 3966.0 -0.036848 0.093309 -0.276506 -0.018006 0.062569 0.108387 \n", "2012 3897.0 -0.023285 0.079632 -0.243603 -0.017992 0.059879 0.104524 \n", "2013 3963.0 -0.009317 0.071341 -0.162604 -0.019996 0.046645 0.081738 \n", "2014 4106.0 -0.033093 0.066426 -0.205406 -0.030264 0.044036 0.082323 \n", "2015 4062.0 -0.148565 0.054432 -0.369993 -0.066205 0.041277 0.084278 \n", "\n", " p90 \n", "fyear \n", "1988 0.193712 \n", "1989 0.180700 \n", "1990 0.209138 \n", "1991 0.156330 \n", "1992 0.149803 \n", "1993 0.154211 \n", "1994 0.173712 \n", "1995 0.152348 \n", "1996 0.144862 \n", "1997 0.123436 \n", "1998 0.154980 \n", "1999 0.184916 \n", "2000 0.197480 \n", "2001 0.144780 \n", "2002 0.154910 \n", "2003 0.119153 \n", "2004 0.114044 \n", "2005 0.119634 \n", "2006 0.121192 \n", "2007 0.132419 \n", "2008 0.183122 \n", "2009 0.135849 \n", "2010 0.145340 \n", "2011 0.159660 \n", "2012 0.151912 \n", "2013 0.116978 \n", "2014 0.116619 \n", "2015 0.120208 " ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "all_years=np.arange(1988,2016)\n", "sel=df['fyear'].isin(all_years)\n", "EW_pi_mval=(df[sel]['pi_mval_ratio_wins']).groupby(level = 1).mean()\n", "VW_pi_mval=(df[sel]['pi_mval_ratio_wins']*df[sel]['wgt_mval']).groupby(level = 1).sum()\n", "num=(df[sel]['pi_mval_ratio_wins']).groupby(level = 1).count()\n", "pi_mval_mom=pd.DataFrame([num,EW_pi_mval,VW_pi_mval])\n", "pi_mval_mom=pi_mval_mom.T\n", "pi_mval_mom.columns=['num','EW_mean','VW_mean']\n", "\n", "per_pi_mval=[df[sel]['pi_mval_ratio'].groupby(level = 1).quantile(p) for p in percentile]\n", "per_head=['p90','p75','p50','p25','p10']\n", "for p in range(len(percentile)):\n", " pi_mval_mom[per_head[p]]=per_pi_mval[p]\n", "\n", "pi_mval_mom=pi_mval_mom[['num','EW_mean','VW_mean','p10','p25','p50','p75','p90']] \n", "pi_mval_mom.to_csv('compustat_pi_mval_all_firms_allyears.csv')\n", "pi_mval_mom" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Pre-tax income/ Market Value for \"Small\" Firms\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Small by market cap, cutoff bottom 20%" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\admin\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:6: SettingWithCopyWarning: \n", "A value is trying to be set on a copy of a slice from a DataFrame.\n", "Try using .loc[row_indexer,col_indexer] = value instead\n", "\n", "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n", " \n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
numEW_meanVW_meanp10p25p50p75p90
fyear
19881053.0-0.435957-0.269967-1.565283-0.523336-0.1428240.0608630.178730
19911032.0-0.728548-0.489911-2.370858-0.723946-0.1592460.0507760.173996
19941357.0-0.233424-0.142300-0.981368-0.340904-0.0410990.0929340.176146
19971458.0-0.298704-0.192368-1.054894-0.432452-0.0845810.0714040.136607
20001279.0-1.041485-0.718157-3.452889-1.034324-0.1644020.1040180.226318
20031012.0-0.142035-0.091790-0.597383-0.210495-0.0091380.0778800.132970
2006963.0-0.120773-0.080836-0.536028-0.207689-0.0016370.0761560.115198
2009842.0-0.650399-0.472915-2.124260-0.724000-0.2250100.0473510.135825
2012780.0-0.226935-0.126461-0.952222-0.355635-0.0376160.1039650.180540
2015813.0-0.595741-0.355661-1.721846-0.554069-0.1152820.0630140.126491
\n", "
" ], "text/plain": [ " num EW_mean VW_mean p10 p25 p50 p75 \\\n", "fyear \n", "1988 1053.0 -0.435957 -0.269967 -1.565283 -0.523336 -0.142824 0.060863 \n", "1991 1032.0 -0.728548 -0.489911 -2.370858 -0.723946 -0.159246 0.050776 \n", "1994 1357.0 -0.233424 -0.142300 -0.981368 -0.340904 -0.041099 0.092934 \n", "1997 1458.0 -0.298704 -0.192368 -1.054894 -0.432452 -0.084581 0.071404 \n", "2000 1279.0 -1.041485 -0.718157 -3.452889 -1.034324 -0.164402 0.104018 \n", "2003 1012.0 -0.142035 -0.091790 -0.597383 -0.210495 -0.009138 0.077880 \n", "2006 963.0 -0.120773 -0.080836 -0.536028 -0.207689 -0.001637 0.076156 \n", "2009 842.0 -0.650399 -0.472915 -2.124260 -0.724000 -0.225010 0.047351 \n", "2012 780.0 -0.226935 -0.126461 -0.952222 -0.355635 -0.037616 0.103965 \n", "2015 813.0 -0.595741 -0.355661 -1.721846 -0.554069 -0.115282 0.063014 \n", "\n", " p90 \n", "fyear \n", "1988 0.178730 \n", "1991 0.173996 \n", "1994 0.176146 \n", "1997 0.136607 \n", "2000 0.226318 \n", "2003 0.132970 \n", "2006 0.115198 \n", "2009 0.135825 \n", "2012 0.180540 \n", "2015 0.126491 " ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "x=0.2\n", "var='mkvalt'\n", "var_10=var+'_10'\n", "tmp = df.merge(how = 'left', right = df[var].groupby(level = 1).quantile(x).to_frame(var_10), left_index = True, right_index = True)\n", "df_p10 = df[df[var] < tmp[var_10]]\n", "df_p10['wgt_mval'] = df_p10['mkvalt'] / df_p10['mkvalt'].groupby(level = 1).sum()\n", "\n", "sel_10=df_p10['fyear'].isin(scf_years)\n", "EW_pi_mval=(df_p10[sel_10]['pi_mval_ratio_wins']).groupby(level = 1).mean()\n", "num=(df_p10[sel_10]['pi_mval_ratio_wins']).groupby(level = 1).count()\n", "\n", "VW_pi_mval=(df_p10[sel_10]['pi_mval_ratio_wins']*df_p10[sel_10]['wgt_mval']).groupby(level = 1).sum()\n", "pi_mval_mom=pd.DataFrame([num,EW_pi_mval,VW_pi_mval])\n", "pi_mval_mom=pi_mval_mom.T\n", "pi_mval_mom.columns=['num','EW_mean','VW_mean']\n", "per_pi_mval=[df_p10[sel_10]['pi_mval_ratio'].groupby(level = 1).quantile(p) for p in percentile]\n", "per_head=['p90','p75','p50','p25','p10']\n", "for p in range(len(percentile)):\n", " pi_mval_mom[per_head[p]]=per_pi_mval[p]\n", " \n", "pi_mval_mom=pi_mval_mom[['num','EW_mean','VW_mean','p10','p25','p50','p75','p90']]\n", "pi_mval_mom.to_csv('compustat_pi_mval_small_firms_mc.csv')\n", "pi_mval_mom" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Small by book value of assets, cutoff bottom 20%" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\admin\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:6: SettingWithCopyWarning: \n", "A value is trying to be set on a copy of a slice from a DataFrame.\n", "Try using .loc[row_indexer,col_indexer] = value instead\n", "\n", "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n", " \n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
numEW_meanVW_meanp10p25p50p75p90
count10.00000010.00000010.00000010.00000010.00000010.00000010.00000010.000000
mean1059.100000-0.266135-0.085223-0.788456-0.290212-0.0774060.0398440.117146
std234.2783010.1380120.0310140.3433850.0984330.0309280.0125680.019075
min780.000000-0.571285-0.147961-1.575370-0.491529-0.1267860.0134690.087592
25%872.250000-0.292368-0.093957-0.793171-0.328824-0.1005410.0337340.105164
50%1022.500000-0.252606-0.078663-0.748656-0.270087-0.0753950.0428330.119906
75%1222.750000-0.183799-0.063437-0.619515-0.236093-0.0523650.0468840.124984
max1458.000000-0.115641-0.051541-0.412865-0.163783-0.0338110.0589210.154929
\n", "
" ], "text/plain": [ " num EW_mean VW_mean p10 p25 p50 \\\n", "count 10.000000 10.000000 10.000000 10.000000 10.000000 10.000000 \n", "mean 1059.100000 -0.266135 -0.085223 -0.788456 -0.290212 -0.077406 \n", "std 234.278301 0.138012 0.031014 0.343385 0.098433 0.030928 \n", "min 780.000000 -0.571285 -0.147961 -1.575370 -0.491529 -0.126786 \n", "25% 872.250000 -0.292368 -0.093957 -0.793171 -0.328824 -0.100541 \n", "50% 1022.500000 -0.252606 -0.078663 -0.748656 -0.270087 -0.075395 \n", "75% 1222.750000 -0.183799 -0.063437 -0.619515 -0.236093 -0.052365 \n", "max 1458.000000 -0.115641 -0.051541 -0.412865 -0.163783 -0.033811 \n", "\n", " p75 p90 \n", "count 10.000000 10.000000 \n", "mean 0.039844 0.117146 \n", "std 0.012568 0.019075 \n", "min 0.013469 0.087592 \n", "25% 0.033734 0.105164 \n", "50% 0.042833 0.119906 \n", "75% 0.046884 0.124984 \n", "max 0.058921 0.154929 " ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "x=0.2\n", "var='at'\n", "var_10=var+'_10'\n", "tmp = df.merge(how = 'left', right = df[var].groupby(level = 1).quantile(x).to_frame(var_10), left_index = True, right_index = True)\n", "df_p10 = df[df[var] < tmp[var_10]]\n", "df_p10['wgt_mval'] = df_p10['mkvalt'] / df_p10['mkvalt'].groupby(level = 1).sum()\n", "\n", "sel_10=df_p10['fyear'].isin(scf_years)\n", "EW_pi_mval=(df_p10[sel_10]['pi_mval_ratio_wins']).groupby(level = 1).mean()\n", "num=(df_p10[sel_10]['pi_mval_ratio_wins']).groupby(level = 1).count()\n", "\n", "VW_pi_mval=(df_p10[sel_10]['pi_mval_ratio_wins']*df_p10[sel_10]['wgt_mval']).groupby(level = 1).sum()\n", "pi_mval_mom=pd.DataFrame([num,EW_pi_mval,VW_pi_mval])\n", "pi_mval_mom=pi_mval_mom.T\n", "pi_mval_mom.columns=['num','EW_mean','VW_mean']\n", "per_pi_mval=[df_p10[sel_10]['pi_mval_ratio'].groupby(level = 1).quantile(p) for p in percentile]\n", "per_head=['p90','p75','p50','p25','p10']\n", "for p in range(len(percentile)):\n", " pi_mval_mom[per_head[p]]=per_pi_mval[p]\n", " \n", "pi_mval_mom=pi_mval_mom[['num','EW_mean','VW_mean','p10','p25','p50','p75','p90']]\n", "pi_mval_mom.to_csv('compustat_pi_mval_small_firms_at.csv')\n", "pi_mval_mom.describe()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Small by revenues, cutoff bottom 20%" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\admin\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:6: SettingWithCopyWarning: \n", "A value is trying to be set on a copy of a slice from a DataFrame.\n", "Try using .loc[row_indexer,col_indexer] = value instead\n", "\n", "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n", " \n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
numEW_meanVW_meanp10p25p50p75p90
fyear
19881053.0-0.271516-0.087859-0.783450-0.263373-0.0861950.0112760.091830
19911033.0-0.316878-0.060428-0.783600-0.233217-0.0559100.0153240.085937
19941358.0-0.180845-0.092426-0.582123-0.248436-0.0662510.0399880.112247
19971458.0-0.210752-0.084573-0.602096-0.254129-0.0799400.0265200.091064
20001279.0-0.528326-0.123624-1.340882-0.421905-0.1065200.0216480.130146
20031012.0-0.095050-0.072096-0.353032-0.152140-0.0329320.0554730.095537
2006963.0-0.118586-0.086290-0.405136-0.185503-0.0507080.0467320.091290
2009842.0-0.325955-0.109754-0.920999-0.345912-0.1084360.0297350.107952
2012780.0-0.170875-0.057265-0.550771-0.226889-0.0541130.0672970.134526
2015813.0-0.375906-0.114877-1.032843-0.357929-0.1192950.0192830.087611
\n", "
" ], "text/plain": [ " num EW_mean VW_mean p10 p25 p50 p75 \\\n", "fyear \n", "1988 1053.0 -0.271516 -0.087859 -0.783450 -0.263373 -0.086195 0.011276 \n", "1991 1033.0 -0.316878 -0.060428 -0.783600 -0.233217 -0.055910 0.015324 \n", "1994 1358.0 -0.180845 -0.092426 -0.582123 -0.248436 -0.066251 0.039988 \n", "1997 1458.0 -0.210752 -0.084573 -0.602096 -0.254129 -0.079940 0.026520 \n", "2000 1279.0 -0.528326 -0.123624 -1.340882 -0.421905 -0.106520 0.021648 \n", "2003 1012.0 -0.095050 -0.072096 -0.353032 -0.152140 -0.032932 0.055473 \n", "2006 963.0 -0.118586 -0.086290 -0.405136 -0.185503 -0.050708 0.046732 \n", "2009 842.0 -0.325955 -0.109754 -0.920999 -0.345912 -0.108436 0.029735 \n", "2012 780.0 -0.170875 -0.057265 -0.550771 -0.226889 -0.054113 0.067297 \n", "2015 813.0 -0.375906 -0.114877 -1.032843 -0.357929 -0.119295 0.019283 \n", "\n", " p90 \n", "fyear \n", "1988 0.091830 \n", "1991 0.085937 \n", "1994 0.112247 \n", "1997 0.091064 \n", "2000 0.130146 \n", "2003 0.095537 \n", "2006 0.091290 \n", "2009 0.107952 \n", "2012 0.134526 \n", "2015 0.087611 " ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "x=0.2\n", "var='sale'\n", "var_10=var+'_10'\n", "tmp = df.merge(how = 'left', right = df[var].groupby(level = 1).quantile(x).to_frame(var_10), left_index = True, right_index = True)\n", "df_p10 = df[df[var] < tmp[var_10]]\n", "df_p10['wgt_mval'] = df_p10['mkvalt'] / df_p10['mkvalt'].groupby(level = 1).sum()\n", "\n", "sel_10=df_p10['fyear'].isin(scf_years)\n", "EW_pi_mval=(df_p10[sel_10]['pi_mval_ratio_wins']).groupby(level = 1).mean()\n", "num=(df_p10[sel_10]['pi_mval_ratio_wins']).groupby(level = 1).count()\n", "\n", "VW_pi_mval=(df_p10[sel_10]['pi_mval_ratio_wins']*df_p10[sel_10]['wgt_mval']).groupby(level = 1).sum()\n", "pi_mval_mom=pd.DataFrame([num,EW_pi_mval,VW_pi_mval])\n", "pi_mval_mom=pi_mval_mom.T\n", "pi_mval_mom.columns=['num','EW_mean','VW_mean']\n", "per_pi_mval=[df_p10[sel_10]['pi_mval_ratio'].groupby(level = 1).quantile(p) for p in percentile]\n", "per_head=['p90','p75','p50','p25','p10']\n", "for p in range(len(percentile)):\n", " pi_mval_mom[per_head[p]]=per_pi_mval[p]\n", " \n", "pi_mval_mom=pi_mval_mom[['num','EW_mean','VW_mean','p10','p25','p50','p75','p90']]\n", "pi_mval_mom.to_csv('compustat_pi_mval_small_firms_sale.csv')\n", "pi_mval_mom\n" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.7.3" } }, "nbformat": 4, "nbformat_minor": 2 }