07-01_dataset_control_variables.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dataset Controls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "NAME = '07-01_dataset_control_variables'\r\n",
    "PROJECT = 'conference-calls-sentiment'\r\n",
    "PYTHON_VERSION = '3.7.0'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Imports  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import re\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Settings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "workdir = re.sub(\"(?<={})[\\w\\W]*\".format(PROJECT), \"\", os.getcwd())\r\n",
    "os.chdir(workdir)\r\n",
    "\r\n",
    "pipeline = os.path.join('2_pipeline', NAME)\r\n",
    "if not os.path.exists(pipeline):\r\n",
    "    os.makedirs(pipeline)\r\n",
    "    for folder in ['out', 'store', 'tmp']:\r\n",
    "        os.makedirs(os.path.join(pipeline, folder))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "# Main Code "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Compustat\r\n",
    "*I construct the following control variable from Compustat data:*\r\n",
    "- *Size*: `ATQ`\r\n",
    "- *Eanings*: `NIQ`\r\n",
    "- *Loss*: `NIQ` < 0 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "compustat = pd.read_csv(os.path.join('0_data', 'compustat', 'compustat_quarterly_2000-2020.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "def lag_controls(data):\r\n",
    "    lagged_controls = (data\r\n",
    "                       .sort_values(['gvkey', 'ticker', 'quarter'])\r\n",
    "                       .assign(quarter=data.groupby(['gvkey', 'ticker'])['quarter'].shift(-1))\r\n",
    "                       .dropna(subset=['quarter']))\r\n",
    "    return lagged_controls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "compustat_clean = (compustat\r\n",
    "                   .copy()\r\n",
    "                   .assign(quarter=pd.PeriodIndex(compustat['datacqtr'], freq='Q'))\r\n",
    "                   .filter(['gvkey', 'tic', 'quarter', 'atq', 'ibq',\r\n",
    "                            'cshoq', 'prccq', 'capxy', 'dlttq'])\r\n",
    "                   .rename(columns={'tic': 'ticker'})\r\n",
    "                   .drop_duplicates(['gvkey', 'quarter'], keep='last')\r\n",
    "                   .pipe(lag_controls))\r\n",
    "compustat_clean"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Earnings Surprise"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "surprise = pd.read_csv(os.path.join('0_data', 'ibes', 'ibes_earnings-surprise_2000-2020.csv'))\r\n",
    "surprise"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "surprise['PMON'].value_counts().to_frame().sort_index().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_quarter(row):\r\n",
    "    date = pd.to_datetime(surprise['anndats'], format='%Y%m%d')\r\n",
    "    row['quarter'] = date.dt.to_period('Q')\r\n",
    "    return row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "surprise_clean = (surprise.copy()\r\n",
    "                          .pipe(add_quarter)\r\n",
    "                          .filter(['OFTIC', 'quarter', 'surpmean'])\r\n",
    "                          .rename(columns={'OFTIC': 'ticker'})\r\n",
    "                          .drop_duplicates(['ticker', 'quarter']))\r\n",
    "surprise_clean"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Construct controls dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "controls = (pd.merge(compustat_clean, surprise_clean, on=['ticker', 'quarter'], how='left')\r\n",
    "            .assign(surprise=lambda x: x['surpmean'] / x['prccq'] * 100,\r\n",
    "                    size=lambda x: np.log(x['cshoq'] * x['prccq']),\r\n",
    "                    roa=lambda x: x['ibq'] / x['atq'] * 100,\r\n",
    "                    leverage=lambda x: x['dlttq'] / x['atq'],\r\n",
    "                    loss=lambda x: (x['ibq'] < 0).astype('int'),\r\n",
    "                    capex=lambda x: x['capxy'] / x['atq'] * 100)\r\n",
    "            .filter(['gvkey', 'quarter', 'surprise', 'size', 'roa',\r\n",
    "                     'leverage', 'loss', 'capex'])\r\n",
    "            .sort_values(['gvkey', 'quarter'])\r\n",
    "            .reset_index(drop=True))\r\n",
    "\r\n",
    "controls.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "controls.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save\r\n",
    "controls.to_feather(os.path.join(pipeline, 'out', 'control_variables.feather'))"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "4ad8bff83ca7a203c8f5c1f2f3a00576dbcaaca98e752b3367803050219b2dc1"
  },
  "kernelspec": {
   "display_name": "Python 3.7.0 64-bit ('ccs': conda)",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": ""
  },
  "orig_nbformat": 2
 },
 "nbformat": 4,
 "nbformat_minor": 2
}