study_1_more.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import liberaries and functions\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pickle\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "plt.rc('figure', autolayout=True)\n",
    "\n",
    "import seaborn as sns\n",
    "sns.set_style('white')\n",
    "sns.set_context(\"paper\", font_scale = 1.5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load data sample \n",
    "RF_df = pd.read_csv(\"Data\\clean_docs_3.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load Top2Vec topics df\n",
    "t2v_df = (\n",
    "    pd.read_csv(\"Top2Vec\\T2V_df_H95.csv\")\n",
    "    .set_index(\"index\").drop(columns=['Docs'])\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "topics_df = pd.concat([RF_df, t2v_df[['Topic', 'Score', 'Topic_H', 'Score_H']]], axis=1)\n",
    "\n",
    "topics_df[\"filing_dt\"] = pd.to_datetime(topics_df[\"filing_dt\"])\n",
    "topics_df[\"report_dt\"] = pd.to_datetime(topics_df[\"report_dt\"])\n",
    "\n",
    "topics_df['rprt_length'] = topics_df['cleaned_txt'].map(lambda x: len(x.split()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use historical SIC data for industry analysis\n",
    "sich = pd.read_csv(\n",
    "    filepath_or_buffer=\"Data\\Financials.csv\",\n",
    "    decimal=\".\", \n",
    "    thousands=',',\n",
    "    usecols=[\n",
    "        'cik', 'datadate', 'sich'\n",
    "    ]\n",
    ").drop_duplicates()\n",
    "\n",
    "sich.sort_values(['cik', 'datadate'], inplace=True)\n",
    "sich[\"datadate\"] = pd.to_datetime(sich[\"datadate\"], format=\"%d/%m/%Y\")\n",
    "\n",
    "topics_df = pd.merge(\n",
    "    left=topics_df,\n",
    "    right=sich[[\n",
    "        'cik', 'datadate', 'sich']],\n",
    "    left_on=[\"CIK\", \"report_dt\"],\n",
    "    right_on=[\"cik\", \"datadate\"],\n",
    "    how=\"left\"\n",
    ").drop(columns=['datadate', 'cik'])\n",
    "\n",
    "topics_df['sich'] = topics_df['sich'].fillna(topics_df['SIC']).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"Top2Vec\\\\topic_hierarchy.txt\", 'rb') as f:\n",
    "    TH = pickle.load(f)\n",
    "\n",
    "df = pd.DataFrame(TH).reset_index()\n",
    "\n",
    "TH = (\n",
    "    pd.melt(df, id_vars=['index'])\n",
    "    .dropna()\n",
    "    .astype(int)\n",
    "    .sort_values(['index', 'value'])\n",
    "    .drop(columns='variable')\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "topics_df['Topic_H'] = pd.merge(\n",
    "    left=topics_df,\n",
    "    right=TH[['index', 'value']],\n",
    "    left_on=['Topic'],\n",
    "    right_on=['value'],\n",
    "    how='left'\n",
    ")['index']"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Firm level"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "group = 'CIK'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Risk topics disclosed and not disclosed per report \n",
    "disc_df = pd.pivot_table(\n",
    "    topics_df, index = [\"CIK\", \"filing_dt\", \"report_dt\", \"sich\"], \n",
    "    columns='Topic', values='Score'\n",
    ").notna().astype(int).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "disc_long = pd.melt(disc_df, id_vars=[\"CIK\", \"filing_dt\", \"report_dt\", \"sich\"])\n",
    "disc_long.sort_values([group, 'Topic', 'filing_dt'], inplace=True)\n",
    "\n",
    "disc_long['Occurence'] = disc_long.groupby([group, 'Topic'])['value'].cumsum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "disc_long['min_occ'] = disc_long.groupby([group, 'Topic'])['Occurence'].transform('min')\n",
    "disc_long['max_occ'] = disc_long.groupby([group, 'Topic'])['Occurence'].transform('max')\n",
    "\n",
    "# Keep risk topics that are not always present in a firm and are disclosed at list once\n",
    "# Keep observations that the RF is disclosed\n",
    "disc_long = disc_long[\n",
    "    (disc_long['min_occ']==0)&\n",
    "    (disc_long['max_occ']>0)&\n",
    "    (disc_long['value']>0)\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "count_rpt = disc_long.groupby(group)['filing_dt'].transform('nunique')\n",
    "disc_long = disc_long[count_rpt>1]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Industry level"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "group = 'sich'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Counting the actual number of firms per industry\n",
    "filings_df = pd.read_csv(\"Data\\\\filings_df.csv\")\n",
    "\n",
    "filings_df.dropna(subset=[\"filingDate\", \"reportDate\", \"sic\"], inplace=True)\n",
    "\n",
    "filings_df[\"filingDate\"] = pd.to_datetime(filings_df[\"filingDate\"])\n",
    "filings_df[\"reportDate\"] = pd.to_datetime(filings_df[\"reportDate\"])\n",
    "\n",
    "filings_df = filings_df.sort_values(['cik', 'reportDate', 'filingDate']).drop_duplicates(keep='last')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Historical SIC\n",
    "filings_df = pd.merge(\n",
    "    left=filings_df,\n",
    "    right=sich[[\n",
    "        'cik', 'datadate', 'sich']],\n",
    "    left_on=[\"cik\", \"reportDate\"],\n",
    "    right_on=[\"cik\", \"datadate\"],\n",
    "    how=\"left\"\n",
    ").drop(columns=['datadate'])\n",
    "\n",
    "filings_df['sich'] = topics_df['sich'].fillna(filings_df['sic']).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Risk topics disclosed and not disclosed per report \n",
    "disc_df = pd.pivot_table(\n",
    "    topics_df, index = [\"CIK\", \"filing_dt\", \"report_dt\", group], \n",
    "    columns='Topic_H', values='Score_H'\n",
    ").notna().astype(int).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "filings_df = filings_df[\n",
    "    (filings_df[\"filingDate\"] >= disc_df['filing_dt'].min())\n",
    "    &(filings_df[\"filingDate\"] <= disc_df['filing_dt'].max())\n",
    "]\n",
    "filings_df['fyear'] = filings_df[\"filingDate\"].dt.year\n",
    "filings_df['sic3'] = filings_df[\"sich\"].map(lambda x: f\"{int(x):04d}\"[:3])\n",
    "\n",
    "# Number of reporting firms per filing year\n",
    "filings_df['#firms'] = filings_df.groupby('sic3')['filingDate'].transform('nunique')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "disc_long = pd.melt(disc_df, id_vars=[\"CIK\", \"filing_dt\", \"report_dt\", group])\n",
    "\n",
    "disc_long[f\"{group}3\"] = disc_long[group].map(lambda x: f\"{int(x):04d}\"[:3])\n",
    "\n",
    "disc_long.sort_values([f\"{group}3\", 'Topic_H', 'filing_dt'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "disc_long = pd.merge(\n",
    "    left=disc_long,\n",
    "    right=filings_df[['filingDate', 'reportDate', 'cik', '#firms']],\n",
    "    left_on=['CIK', 'report_dt'],\n",
    "    right_on=['cik', 'reportDate'],\n",
    "    how='left'\n",
    ").dropna(subset=['cik', 'reportDate']).drop(columns=['filingDate', 'reportDate', 'cik'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "disc_long['Occurence'] = disc_long.groupby([f\"{group}3\", 'Topic_H'])['value'].cumsum()\n",
    "\n",
    "# disc_long['#firms'] = disc_long.groupby(f\"{group}3\")['filing_dt'].transform('nunique')\n",
    "\n",
    "disc_long['frac'] = disc_long['Occurence'] / disc_long['#firms']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "disc_long['min_frac'] = disc_long.groupby([group, 'Topic_H'])['frac'].transform('min')\n",
    "disc_long['max_frac'] = disc_long.groupby([group, 'Topic_H'])['frac'].transform('max')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "disc_long = disc_long[\n",
    "    (disc_long['min_frac']<=0.1)\n",
    "    &(disc_long['max_frac']>=0.2)\n",
    "    &(disc_long['value']>0)\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Start from one year after the earliest filing date\n",
    "disc_df[\"filing_dt-1\"] = disc_df[\"filing_dt\"] - pd.Timedelta(weeks=52)\n",
    "disc_df = disc_df[disc_df[\"filing_dt\"]>'2007-02-01']\n",
    "disc_df['sich'] = disc_df['sich'].map(lambda x: f\"{int(x):04d}\"[:2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [],
   "source": [
    "count_rpt = disc_df.groupby(group)['filing_dt'].transform('nunique')\n",
    "disc_df = disc_df[count_rpt>2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [],
   "source": [
    "def count_func(x):\n",
    "    df_slice = disc_df[\n",
    "        (disc_df[\"filing_dt\"]>x[\"filing_dt-1\"])&\n",
    "        (disc_df[\"filing_dt\"]<=x[\"filing_dt\"])&\n",
    "        (disc_df['sich']==x['sich'])\n",
    "    ]\n",
    "    output = (\n",
    "        df_slice.filter(range(0,100)).sum() / \n",
    "        df_slice[\"CIK\"].count()\n",
    "    )\n",
    "    \n",
    "    return output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [],
   "source": [
    "disc_df.loc[:, range(0,95)] = disc_df.apply(count_func, axis=1)\n",
    "disc_df.drop(columns=['filing_dt-1'], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Daily data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Instrument', 'CLOSEPRICE', 'VOLUME', 'COMPANYMARKETCAP',\n",
       "       'TTLCMNSHARESOUT'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prices_df = pd.read_csv(\"Data\\Prices.csv\")\n",
    "prices_df[\"Date\"] = pd.to_datetime(prices_df[\"Date\"])\n",
    "prices_df.sort_values([\"Instrument\", \"Date\"], inplace=True)\n",
    "prices_df = prices_df[prices_df[\"Date\"]>'2005-01-01'].set_index('Date')\n",
    "prices_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Daily shares turnover\n",
    "prices_df[\"SHRTURN\"] = prices_df[\"VOLUME\"] / prices_df[\"TTLCMNSHARESOUT\"]\n",
    "\n",
    "# Returns\n",
    "prices_df[\"Return\"] = prices_df.groupby(\"Instrument\")[\"CLOSEPRICE\"].pct_change(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Time window\n",
    "N = 20\n",
    "\n",
    "# Average of N-day std of daily returns\n",
    "std_returns = (\n",
    "    prices_df.groupby(\"Instrument\")[\"Return\"]\n",
    "    .rolling(N, min_periods=N//2).std().to_frame()\n",
    ")\n",
    "std_returns[f\"stdReturn+{N}\"] = std_returns.groupby(\"Instrument\")[\"Return\"].shift(-N-2)\n",
    "std_returns[f\"stdReturn_{N}\"] = std_returns.groupby(\"Instrument\")[\"Return\"].shift(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Average of N-day trade volumes\n",
    "N=5\n",
    "MA_vol = prices_df.groupby(\"Instrument\")[\"VOLUME\"].rolling(N, min_periods=3).mean().to_frame()\n",
    "MA_vol[f\"VOLUME\"] = MA_vol.groupby(\"Instrument\")[\"VOLUME\"].shift(N//2)\n",
    "MA_vol[\"SHRTURN\"] = prices_df.groupby(\"Instrument\")[\"SHRTURN\"].rolling(N, min_periods=3).mean()\n",
    "MA_vol[f\"SHRTURN\"] = MA_vol.groupby(\"Instrument\")[\"SHRTURN\"].shift(N//2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Time window\n",
    "N = 30\n",
    "\n",
    "# Average of 60-day std of daily returns\n",
    "std_returns[f\"stdReturn+{N}\"] = prices_df.groupby(\"Instrument\")[\"Return\"].rolling(N, min_periods=N//2).std().groupby(\"Instrument\").shift(-N-2)\n",
    "std_returns[f\"stdReturn_{N}\"] = prices_df.groupby(\"Instrument\")[\"Return\"].rolling(N, min_periods=N//2).std().groupby(\"Instrument\").shift(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "for N in [20, 30]:\n",
    "    std_returns[f\"D_std{N}\"] = (std_returns[f\"stdReturn+{N}\"] - std_returns[f\"stdReturn_{N}\"])/std_returns[f\"stdReturn_{N}\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "DatetimeIndex: 8687523 entries, 2006-01-17 to 2022-06-14\n",
      "Data columns (total 5 columns):\n",
      " #   Column      Dtype  \n",
      "---  ------      -----  \n",
      " 0   Instrument  int64  \n",
      " 1   HIGHPRICE   float64\n",
      " 2   LOWPRICE    float64\n",
      " 3   BIDPRICE    float64\n",
      " 4   ASKPRICE    float64\n",
      "dtypes: float64(4), int64(1)\n",
      "memory usage: 397.7 MB\n"
     ]
    }
   ],
   "source": [
    "BidAsk_df = pd.read_csv(\"Data\\BidAsk.csv\").drop_duplicates()\n",
    "BidAsk_df[\"Date\"] = pd.to_datetime(BidAsk_df[\"Date\"])\n",
    "BidAsk_df.set_index('Date', inplace=True)\n",
    "BidAsk_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "BidAsk_df[\"BAspread\"] = (BidAsk_df[\"ASKPRICE\"] - BidAsk_df['BIDPRICE'])/BidAsk_df[\"ASKPRICE\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "N = 20\n",
    "\n",
    "# N-day moving average (trading days only)\n",
    "MA_BA = BidAsk_df.groupby('Instrument')['BAspread'].rolling(N, min_periods=N//2).mean().to_frame()\n",
    "MA_BA[f\"avgBA+{N}\"] = MA_BA.groupby(\"Instrument\")[\"BAspread\"].shift(-N-2)\n",
    "MA_BA[f\"avgBA_{N}\"] = MA_BA.groupby(\"Instrument\")[\"BAspread\"].shift(2)\n",
    "\n",
    "MA_BA.drop(columns='BAspread', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "N = 30\n",
    "# N-day moving average (trading days only)\n",
    "MA_BA[f\"avgBA+{N}\"] = BidAsk_df.groupby('Instrument')['BAspread'].rolling(N, min_periods=N//2).mean().groupby(\"Instrument\").shift(-N-2)\n",
    "MA_BA[f\"avgBA_{N}\"] = BidAsk_df.groupby('Instrument')['BAspread'].rolling(N, min_periods=N//2).mean().groupby(\"Instrument\").shift(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "for N in [20, 30]:\n",
    "    MA_BA[f\"D_ba{N}\"] = (MA_BA[f\"avgBA+{N}\"] - MA_BA[f\"avgBA_{N}\"])/MA_BA[f\"avgBA_{N}\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Date', 'Instrument', 'Beta_30', 'Beta_90', 'Beta_250', 'AR', 'CAR_10',\n",
       "       'CAR_20', 'CAR_30'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Beta = pd.read_csv(\"Data\\Beta_AR.csv\")\n",
    "Beta.drop_duplicates(inplace=True)\n",
    "Beta.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "Beta['Date'] = pd.to_datetime(Beta['Date'])\n",
    "Beta.set_index('Date', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Daily Beta\n",
    "Beta[\"BETA+90\"] = Beta.groupby('Instrument')[\"Beta_90\"].shift(-90-2)\n",
    "Beta[\"BETA_90\"] = Beta.groupby('Instrument')[\"Beta_90\"].shift(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "Analysts = pd.read_csv(\"Data\\Analysts.csv\")\n",
    "Analysts.dropna(inplace=True)\n",
    "\n",
    "Analysts.columns = ['Instrument', 'NUMBEROFANALYSTS', 'Date']\n",
    "Analysts['Date'] = pd.to_datetime(Analysts['Date'], errors='coerce').dt.tz_localize(None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "Analyst_df = pd.merge(\n",
    "    left=prices_df[\"Instrument\"], \n",
    "    right=Analysts, \n",
    "    on=[\"Date\", \"Instrument\"],\n",
    "    how=\"outer\"\n",
    ")\n",
    "\n",
    "Analyst_df = Analyst_df[(Analyst_df[\"Date\"]>'2005-01-01')&(Analyst_df[\"Date\"]<'2023-01-01')]\n",
    "\n",
    "Analyst_df.sort_values(['Instrument', 'Date', 'NUMBEROFANALYSTS'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "Analyst_df[\"NUMBEROFANALYSTS\"] = Analyst_df.groupby(\"Instrument\")[\"NUMBEROFANALYSTS\"].fillna(method='ffill')\n",
    "Analyst_df.drop_duplicates(subset=['Instrument', 'Date'], keep='last', inplace=True)\n",
    "Analyst_df.fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load annual financial data\n",
    "# Fill NAs with last (previouse) observation and drop duplicates\n",
    "financials = pd.read_csv(\n",
    "    filepath_or_buffer=\"Data\\Financials.csv\",\n",
    "    decimal=\".\", \n",
    "    thousands=',',\n",
    ").drop_duplicates()\n",
    "\n",
    "financials.sort_values(['cik', 'datadate'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "financials[\"datadate\"] = pd.to_datetime(financials[\"datadate\"], format=\"%d/%m/%Y\")\n",
    "financials[\"seq\"].fillna(financials[\"teq\"], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Leverage\n",
    "financials[\"DtA\"] = financials[\"dt\"] / financials[\"at\"].replace(0, np.nan)\n",
    "financials[\"DtEBITDA\"] = financials[\"dt\"] / financials[\"ebitda\"].replace(0, np.nan)\n",
    "\n",
    "# Profitability\n",
    "financials[\"ROE\"] = financials[\"ni\"] / financials[\"seq\"].replace(0, np.nan)\n",
    "financials[\"NPM\"] = financials[\"ni\"] / financials[\"revt\"].replace(0, np.nan) # net profit margin\n",
    "\n",
    "# Firm size\n",
    "financials[\"logMC\"] = np.log(financials[\"mkvalt\"].replace(0, np.nan))\n",
    "financials[\"logTA\"] = np.log(financials[\"at\"].replace(0, np.nan))\n",
    "\n",
    "# Intangible assets\n",
    "financials[\"RtINT\"] = financials[\"revt\"] / financials[\"intan\"].replace(0, np.nan)\n",
    "financials[\"INTtA\"] = financials[\"intan\"] / financials[\"at\"].replace(0, np.nan) \n",
    "\n",
    "# Liquidity\n",
    "financials[\"current\"] = financials[\"act\"] / financials[\"lct\"].replace(0, np.nan)\n",
    "\n",
    "# Other\n",
    "financials[\"TobinQ\"] = financials[\"mkvalt\"] / financials[\"at\"].replace(0, np.nan)\n",
    "financials[\"BtM\"] = financials[\"seq\"] / financials[\"mkvalt\"].replace(0, np.nan)\n",
    "\n",
    "# R&D intensity\n",
    "financials[\"RDxopr\"] = financials[\"xrd\"].fillna(0) / financials[\"xopr\"].replace(0, np.nan)\n",
    "financials[\"ProprietaryCost\"] = financials[\"xrd\"].fillna(0) / financials.groupby('cik')[\"at\"].shift(1).replace(0, np.nan)\n",
    "\n",
    "# %change in earnings\n",
    "financials[\"DEarnings\"] = financials.groupby(\"cik\")[\"ni\"].pct_change(1).replace(np.Inf, np.nan)\n",
    "financials[\"DEarnings\"].replace(-np.Inf, np.nan, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['gvkey', 'datadate', 'fyear', 'indfmt', 'consol', 'popsrc', 'datafmt',\n",
       "       'curcd', 'act', 'at', 'dt', 'ebit', 'ebitda', 'intan', 'lct', 'lt',\n",
       "       'ni', 'revt', 'seq', 'teq', 'xopr', 'xrd', 'xt', 'cik', 'costat',\n",
       "       'naicsh', 'sich', 'mkvalt', 'naics', 'sic', 'DtA', 'DtEBITDA', 'ROE',\n",
       "       'NPM', 'logMC', 'logTA', 'RtINT', 'INTtA', 'current', 'TobinQ', 'BtM',\n",
       "       'RDxopr', 'ProprietaryCost', 'DEarnings'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "financials.columns"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "rprt_length = (\n",
    "    topics_df.groupby([\"CIK\", \"report_dt\", \"filing_dt\"])['rprt_length'].sum()\n",
    ").reset_index().drop_duplicates(subset=[\"CIK\", \"filing_dt\", \"report_dt\"])\n",
    "\n",
    "disc_long = pd.merge(\n",
    "    left=disc_long, \n",
    "    right=rprt_length,\n",
    "    on=[\"CIK\", \"filing_dt\", \"report_dt\"],\n",
    "    how='left'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "disc_long = pd.merge(\n",
    "    left=disc_long,\n",
    "    right=std_returns,\n",
    "    left_on=[\"CIK\", \"filing_dt\"],\n",
    "    right_index=True,\n",
    "    how=\"left\"\n",
    ")\n",
    "\n",
    "disc_long = pd.merge(\n",
    "    left=disc_long,\n",
    "    right=MA_BA,\n",
    "    left_on=[\"CIK\", \"filing_dt\"],\n",
    "    right_index=True,\n",
    "    how=\"left\"\n",
    ")\n",
    "\n",
    "disc_long = pd.merge(\n",
    "    left=disc_long,\n",
    "    right=MA_vol,\n",
    "    left_on=[\"CIK\", \"filing_dt\"],\n",
    "    right_index=True,\n",
    "    how=\"left\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "disc_long = pd.merge(\n",
    "    left=disc_long,\n",
    "    right=Beta.reset_index()[[\n",
    "        'Instrument', 'Date', 'BETA_90',\n",
    "        'CAR_10', 'CAR_20', 'CAR_30'\n",
    "    ]],\n",
    "    left_on=[\"CIK\", \"filing_dt\"],\n",
    "    right_on=['Instrument', 'Date'],\n",
    "    how=\"left\"\n",
    ").drop(columns=['Instrument', 'Date'])\n",
    "\n",
    "disc_long = pd.merge(\n",
    "    left=disc_long,\n",
    "    right=Analyst_df[['Date', 'Instrument', 'NUMBEROFANALYSTS']],\n",
    "    left_on=[\"CIK\", \"filing_dt\"],\n",
    "    right_on=['Instrument', 'Date'],\n",
    "    how=\"left\"\n",
    ").drop(columns=['Instrument', 'Date'])\n",
    "\n",
    "disc_long = pd.merge(\n",
    "    left=disc_long,\n",
    "    right=financials[[\n",
    "        'cik', 'datadate', 'DtA', 'DtEBITDA', 'ROE', 'NPM', 'logMC', 'logTA', 'RtINT', \n",
    "        'INTtA', 'current', 'TobinQ', 'BtM', 'RDxopr', 'ProprietaryCost', 'DEarnings'\n",
    "    ]],\n",
    "    left_on=[\"CIK\", \"report_dt\"],\n",
    "    right_on=[\"cik\", \"datadate\"],\n",
    "    how=\"left\"\n",
    ").drop(columns=['datadate', 'cik'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# disc_long.dropna(inplace=True)\n",
    "disc_long['rfGap'] = (disc_long['filing_dt'] - disc_long['report_dt']).dt.days\n",
    "disc_long['fyear'] = disc_long['filing_dt'].dt.year\n",
    "disc_long['ryear'] = disc_long['report_dt'].dt.year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "disc_long.to_csv('Data\\study1_more(3).csv', index=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Table of mean per OCC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "firm = pd.read_csv('Data\\study1_more(3).csv')\n",
    "# industry = pd.read_csv('Data\\study1_more(2)(2).csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "firm[\"filing_dt\"] = pd.to_datetime(firm[\"filing_dt\"])\n",
    "firm[\"report_dt\"] = pd.to_datetime(firm[\"report_dt\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "firm = pd.merge(\n",
    "    left=firm,\n",
    "    right=TH[['index', 'value']],\n",
    "    left_on=['Topic'],\n",
    "    right_on=['value'],\n",
    "    how='left'\n",
    ").rename(columns={'index': 'Topic_H'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.merge(\n",
    "    left=firm,\n",
    "    right=disc_long[['CIK', 'filing_dt', 'report_dt', 'Topic_H', 'frac', '#firms']],\n",
    "    on = ['CIK', 'filing_dt', 'report_dt', 'Topic_H'],\n",
    "    how='left'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('Data\\study1_more.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [],
   "source": [
    "firm.dropna(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "firm.dropna(subset=['stdReturn+20', 'D_std20', 'CAR_20', 'avgBA+20', 'D_ba20'], inplace=True)\n",
    "industry.dropna(subset=['stdReturn+20', 'D_std20', 'CAR_20', 'avgBA+20', 'D_ba20'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [],
   "source": [
    "winsor = ['stdReturn+20', 'D_std20', 'CAR_20', 'avgBA+20', 'D_ba20']\n",
    "df1 = firm[(firm['Occurence']<=10)].copy()\n",
    "df1[winsor] = df1[winsor].clip(\n",
    "    lower=df1[winsor].quantile(0.01), \n",
    "    upper=df1[winsor].quantile(0.99), \n",
    "    axis=1\n",
    ")*100\n",
    "\n",
    "df2 = industry.copy()\n",
    "df2[winsor] = df2[winsor].clip(\n",
    "    lower=df2[winsor].quantile(0.01), \n",
    "    upper=df2[winsor].quantile(0.99), \n",
    "    axis=1\n",
    ")*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "Occ_df1 = df1.groupby('Occurence')[winsor].mean()\n",
    "Occ_df1['Obs'] = df1.groupby(\"Occurence\")['Topic'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [],
   "source": [
    "bins = np.arange(0, 1.1, 0.1)\n",
    "labels = range(1, 11)\n",
    "df2['frac_bin'] = pd.cut(df2['frac'], bins=bins, labels=labels)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [],
   "source": [
    "Occ_df2 = df2.groupby('frac_bin')[winsor].mean()\n",
    "Occ_df2['Obs'] = df2.groupby(\"frac_bin\")['Topic_H'].count()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create OCC graphs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "# graph = disc_long[(disc_long['filing_dt']>'2011-01-01')&(disc_long['filing_dt']<'2016-01-01')].copy()\n",
    "graph_1 = industry[industry['Occurence']<=5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "graph_2 = firm[firm['Occurence']<=5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAr8AAAGfCAYAAABWXbpFAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjUuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8qNh9FAAAACXBIWXMAAAsTAAALEwEAmpwYAABIEElEQVR4nO3de1yUZf7/8fcIDAiDBzZXzdiUMRUtBSSwcNdWO1gG9M3NtQyXTTbFyiWrr+aaaNvBUrOi1EDbLc0SoxKobXfLVWu3SBPLQ6Jfx29b5LoVHWRgGA7z+8Of83UWS8ZmGJj79Xw8fOjc98XN51K5eHPNdV+3yeVyuQQAAAAYQJdAFwAAAAC0F8IvAAAADIPwCwAAAMMg/AIAAMAwCL8AAAAwDMIvAAAADIPwC0Pbvn27Bg8erAULFnj1cRUVFRo8ePApf82dO1eSlJWVpYceesgfZQOA3/znmDZy5EjNmDFDn3zySZuv8emnn2rw4ME6cODAGddx7bXXqqCg4Iw//gSXy6Xi4mI1NDT84GshOIQGugAgkEpLS9W/f3+99tprmjdvniIiIrz6+DfeeKPVx5x4XVBQoNBQvsQAdD4PP/ywLr74YrW0tOirr77SY489ppkzZ6q0tFQmkynQ5Xll+/btuueeezRhwgSFh4cHuhx0AMz8wrCcTqf+/Oc/a8aMGWpoaNCf//xnr68RExOjXr16efyKjo6WJPXo0UMWi8XXZQOA33Xr1k29evVS7969NWTIEM2dO1cHDhxQVVVVoEvzGs/ywn8i/MKwtmzZomPHjmnMmDEaNWqUSkpKfHr9k5c9zJ07V3feeaeuu+46paSk6B//+IeysrJUWFioGTNmaPjw4bryyiu1e/duFRUV6aKLLlJqaqoef/xxn9YEAGeia9euP+jjCwoKdOutt2rx4sVKSUlRcnKyfv/736u5udnd5umnn9bPfvYzJSYmatmyZR4fP3fuXM2aNcvj2NixY7Vu3TpJ0tGjRzVjxgyNHDlSycnJmjVrlr788kt9+umnmjp1qiQpKSlJL730kgoKCpSTk6Np06Zp5MiReuKJJzR48GD985//dF+7rq5OCQkJeuedd35Qv9ExEX5hWKWlpUpKSlJMTIwuv/xyvffee16tafNWeXm5pkyZomeeeUaJiYmSpBUrVmj8+PEqLS1Vz549NW3aNO3du1fr1q1Tbm6unnzySe3fv99vNQHA6dTX12vVqlWKj4/X4MGDz/g6W7Zskd1u14YNGzR//nytX79emzdvliR3KJ07d642btyo6upq7d27t83XXrRokRobG1VcXKx169apurpaixcvVt++fd3rht944w1dddVVkqS33npLKSkpKi4u1g033OBe/nbCm2++qe7duys1NfWM+4uOi/ALQ/r222+1detWXXbZZZKkcePGKSQkRC+//LJX1xk9erQSExPdvyZMmPCdbQcMGKBrrrlG8fHx7lmUUaNG6ZprrlH//v119dVX65tvvtG9994rq9Wq7OxsRUZG6n/+53/OvKMAcAZ++9vfKjExUQkJCUpMTNQLL7ygvLy8H7TeNyIiQvfcc497LBwyZIh2794tSXr++ec1efJkXXXVVRo4cKDuv/9+9ejRo83X/vTTT2WxWHTOOedoyJAheuSRR/TrX/9aISEh6t69u6Tjy9RO3JPRtWtX3XzzzbJarYqJiVF6erpeffVV9/XKy8s1YcIEdelCTApG3I0DQ/rTn/4kp9Opyy+/XNLxQfHCCy/UK6+8oltvvbXNA97zzz/v8Xbg993gFhsb+73HIiIi1L17d3Xr1s19zGw2y+l0tqkWAPCV+fPnu2c9a2trtXXrVt12221avXr1Gc+G9u3bV2az2f3aYrGosbFRknTw4EH96le/cp/r2rWrBg4c2OZrz5gxQ3PmzFFqaqpGjRqlSy+9VBkZGd/Zvl+/fh5BPjMzUwUFBTp06JB+9KMf6e9//7tuv/12b7qHToTwC0MqLS2VJF166aXuYy0tLXK5XHrnnXeUlpbWpuvExsYqKiqqTW1PtZPEf4blznYXNYDg1KtXL5177rnu18OGDdP777+v55577ozDb1hY2HeeM5lMrW5MO7n9qcbGpqYm95+vuuoqjRo1Sn/729/01ltv6f7771dZWZmeeeaZU36+/9z1ITY2VomJifrTn/6kH//4xxowYICGDBnSpn6h8yH8wnCqq6v1/vvv67bbbnPP/ErHB9Ibb7xRJSUlbQ6/AGAULpdLLS0tfrn2oEGD9MEHHyg9PV3S8d14Dh48qJEjR0o6HoTtdru7vd1uV01Njfv1o48+qrFjx2rixImaOHGi/v73v+umm27SF1980eZJhYyMDG3cuFG9e/d214HgRPiF4ZSVlSk8PFxTp071WGIgSf/1X/+ljRs36quvvlJTU5Oio6O93vsXADq7b7/9Vp9//rkkqbGxUX/961/1zjvv6JFHHpF0PJx+88036t69u8dShjOVnZ2tOXPmaNiwYRoxYoTWrFnjEW4vuOACbdq0SVu3blVsbKwKCgo8lqfZbDa9/fbbys/PV7du3VReXq5+/fopJiZG//rXvyRJe/fu1bBhw76zhquuukoPPPCAbDab1w8+QufCSm4YTllZma666qpWwVeSpkyZIqfTqddee02jR4/2uPsXAIziv//7vzV69GiNHj1aV1xxhV588UXdc889uvLKKyVJlZWVGj16tCorK33y+a688kr97ne/U0FBga699lp16dJFo0aNcp/PzMzU1Vdfrdtvv11TpkxRfHy8kpKS3OcXLVqkc889Vzk5OcrIyNBnn32mp556Sl26dNGgQYP085//XDfddJOKi4u/s4YePXpo9OjRuuCCC3T22Wf7pF/omEwudn8GAADQxIkTNXnyZF133XWBLgV+xLIHAABgaNu2bdPOnTv1z3/+0z27jeBF+AUAAIa2du1affjhh7rvvvt4LL0BsOwBAAAAhsENbwAAADAMwi8AAAAMgzW//9/gwYMDXQIAqKqqKtAl/GCMpwA6gu8aTwm/JwmGbzoAOq9gCo2MpwAC6fvGU5Y9AAAAwDAIvwAAADAMwi8AAAAMg/ALAAAAwyD8AgAAwDAIvwAAADAMwi8AAAAMg/ALAAAAwyD8AgAAwDAIvwAAADAMwi8AAAAMg/ALAAAAwyD8AgAAwDBCA10AgMA6duyYGhoa2ty+qalJoaFtHzrCw8MVHR19JqUBCBKMM+hICL+AgTU1NWnSpEmy2+1++xxRUVEqKyvz6hsZgODBOIOOhv8lgIGFhoaquLi4zTMyNTU1ysnJ0erVqxUTE9OmjwkPD+cbEmBgjDPoaPifAhhcdHS0128XxsTE6KyzzvJTRQCCDeMMOhJueAMAAIBhMPMLAOhw/HmDVEe6Ocoo/QQ6EsIvAKBD8fcNUh3l5iij9BPoaPiKAAB0KP6+Qaqj3BxllH4CHQ1fFQCADscoN0gZpZ9AR8INbwAAADAMwi8AAAAMg/ALAAAAwyD8AgAAwDAIvwAAADAMwi8AAAAMg/ALAAAAwyD8AgAAwDAIvwAAADAMwi8AAAAMg/ALAAAAwyD8AgAAwDAIvwAQRKqqqjR58mQlJCRo/Pjx2rp16ynbHTlyRNOmTVNiYqLGjh2rkpIS97kJEyYoMTHR/Wv48OEaPHiwdu7c2V7dAAC/CQ10AQAA33A6ncrNzVVWVpbWrl2rbdu2KS8vT+Xl5erXr59H27y8PI0YMUIrV67Unj17NH36dFmtViUkJOjVV1/1aDt79mx16dJFSUlJ7dkdAPALZn4BIEhUVFTI4XAoOztbYWFhGjdunFJSUlRWVubRzmazaffu3Zo1a5bMZrOSkpKUnp7uMft7QllZmSorK7Vo0aL26gYA+BXhFwCCxKFDh2S1WmUymdzH4uLidPDgQY92NptNffr0kcVi+d52DQ0Neuihh3T33XcrKirKv8UDQDsh/AJAkKirq1NERITHsYiICNXX13scs9vtbWr34osv6qyzztJll13mn4IBIAAIvwAQJCIjI+VwODyOORwORUZGnlG7F198Uddff73HTDIAdHYdIvy29e7kEz755BNdeOGFqqmpcR9zuVx67LHHdPHFF2vkyJGaM2eO6urq/F06AHQYVqtVhw8f9jhms9k0cODAVu2OHj0qu93+ne2OHDmi/fv3a/z48f4tGgDaWcDD74m7k6+44gpt375dd911l/Ly8lRdXX3K9u+8846mTJmib7/91uP4hg0b9Prrr6ukpESbN2/WF198oYceeqg9ugAAHUJqaqpCQkJUWFgop9OpzZs3q6KiQhMmTPBoFxcXp/j4eC1dulQNDQ2qrKxUWVmZMjMz3W127dqluLg4de/evb27AQB+FfDw29a7kyVp48aNys/P16233trq3Msvv6wbb7xRffv2Vffu3ZWXl6dNmzapsbGxPboBAAFnNptVVFSkLVu2aNSoUVqyZImWL1+u2NhYlZaWKjEx0d22oKBA1dXVSktL0x133KE5c+YoOTnZfb66ulq9evUKRDcAwK8Cvs9vW+9OlqRLLrlE1157rY4cOXLK65z8ll1cXJzq6+tVXV2t/v37+6V2AOhoBg0apPXr17c6npGRoYyMDPfrvn37qrCw8Duvk5OTo5ycHL/UCACBFPDw29a7kyV97yxEXV2dunbt6n594s+nug4AAACMKeDLHtp61/HpdO3a1eM6J0Kvt9cBAABA8Ap4+G3r3cmnM3DgQNlsNo9rdO3atdUjPQEAAGBcAV/2cPLdydnZ2Xr77bdVUVGh+fPne3WdjIwM/eEPf1BaWpq6deumRx99VFdffbVCQwPeRQAAYADHjh1TQ0NDm9s3NTV5lVPCw8MVHR19JqXhJAFPhifuTl64cKFWrVql3r17e9ydnJ+fr8rKytNe5/rrr1dNTY2mTJmi+vp6XXLJJZo3b1479AAAABhdU1OTJk2a5LF/tq9FRUWprKyMib0fqEP87bX17uQTzjnnHFVVVXkc69Kli2677TbddtttfqsTAADgVEJDQ1VcXNzmmd+amhrl5ORo9erViomJadPHhIeHE3x9gL9BAAAAH4iOjvZ6WUJMTIzOOussP1WEUwn4DW8AAABAeyH8AgAAwDAIvwAAADAMwi8AAAAMgxveAAAA0GadfT9jwi8AAADaJBj2Myb8AgAAoE2CYT9jwi8AAADarLPvZ8wNbwAAADAMwi8AAAAMg/ALAAAAwyD8AgAAwDAIvwAAADAMwi8AAAAMg/ALAAAAwyD8AgAAwDAIvwAAADAMwi8AAAAMg/ALAAAAwyD8AgAAwDAIvwAAADAMwi8AAAAMg/ALAAAAwyD8AgAAwDAIvwAAADAMwi8ABJGqqipNnjxZCQkJGj9+vLZu3XrKdkeOHNG0adOUmJiosWPHqqSkxOP8xo0bNW7cOCUmJmry5Mmqqqpqj/IBwO8IvwAQJJxOp3Jzc3XFFVdo+/btuuuuu5SXl6fq6upWbfPy8mS1WlVRUaGlS5dq8eLF2rVrlyRpy5YtWrZsmR577DHt2LFDaWlpmjVrVjv3BgD8g/ALAEGioqJCDodD2dnZCgsL07hx45SSkqKysjKPdjabTbt379asWbNkNpuVlJSk9PR09+zvunXrlJOTo/PPP18hISGaPn26HnnkEbW0tASiWwDgU4RfAAgShw4dktVqlclkch+Li4vTwYMHPdrZbDb16dNHFovllO327t2rLl26aPLkyUpNTVVubq4sFou6dOFbBoDOj5EMAIJEXV2dIiIiPI5FRESovr7e45jdbv/edt988402bNig++67T9u2bZPValVubq6ampr82wEAaAeEXwAIEpGRkXI4HB7HHA6HIiMjvWpnNps1efJkDRw4UOHh4Zo9e7YOHz4sm83m3w4AQDsg/AJAkLBarTp8+LDHMZvNpoEDB7Zqd/ToUdnt9lO2i4uLk9PpdJ9rbm6Wy+XyY+UA0H4IvwAQJFJTUxUSEqLCwkI5nU5t3rxZFRUVmjBhgke7uLg4xcfHa+nSpWpoaFBlZaXKysqUmZkpSZo4caLWrVunAwcOyOl0atmyZTrvvPN03nnnBaJbAOBThF8ACBJms1lFRUXasmWLRo0apSVLlmj58uWKjY1VaWmpEhMT3W0LCgpUXV2ttLQ03XHHHZozZ46Sk5MlSTfccIN+85vf6LbbblNqaqoOHTqkJ5980uNGOgDorEIDXQAAwHcGDRqk9evXtzqekZGhjIwM9+u+ffuqsLDwlNcwmUyaOnWqpk6d6rc6ASBQmPkFAACAYRB+AQAAYBiEXwAAABgG4RcAAACGQfgFAACAYRB+AQAAYBiEXwAAABgG4RcAAACGQfgFAACAYRB+AQAAYBiEXwAAABhGhwi/VVVVmjx5shISEjR+/Hht3br1lO2OHDmiadOmKTExUWPHjlVJSYn7XHNzsx566CGNHj1aKSkp+s1vfqNPP/20vboAAACATiA00AU4nU7l5uYqKytLa9eu1bZt25SXl6fy8nL169fPo21eXp5GjBihlStXas+ePZo+fbqsVqsSEhL0wgsv6L333lNZWZmioqK0cOFC/e53v9MzzzwToJ4B6EiOHTumhoaGNrdvampSaGjbh8jw8HBFR0efSWkAgHYU8PBbUVEhh8Oh7OxsmUwmjRs3TikpKSorK9OMGTPc7Ww2m3bv3q01a9bIbDYrKSlJ6enpKikpUUJCgmw2m1wul/tXSEiIwsPDA9gzAB1FU1OTJk2aJLvd7rfPERUVpbKyMq8CMwCg/QV8lD506JCsVqtMJpP7WFxcnA4ePOjRzmazqU+fPrJYLB7tysvLJUmTJk3SX/7yF1100UUKCQlRnz59tH79+vbpBIAOLTQ0VMXFxW2e+a2pqVFOTo5Wr16tmJiYNn1MeHg4wRcAOoGAj9R1dXWKiIjwOBYREaH6+nqPY3a7/XvbNTc362c/+5luvfVWWSwWLVq0SHl5eXr++ec9gjUAY4qOjvZ6WUJMTIzOOussP1UEAAiEgN/wFhkZKYfD4XHM4XAoMjLSq3Zz5szR5Zdfrr59+yo6Olr5+fnatWuXDhw44N8OAAAAoNMIePi1Wq06fPiwxzGbzaaBAwe2anf06FGPNXsnt6uurpbT6XSfCwkJkclk4m1IAAAAuAU8/KampiokJESFhYVyOp3avHmzKioqNGHCBI92cXFxio+P19KlS9XQ0KDKykqVlZUpMzNTkvTzn/9cTzzxhD7//HM5HA4tXrxY8fHxGjBgQCC6BQAAgA4o4OHXbDarqKhIW7Zs0ahRo7RkyRItX75csbGxKi0tVWJiorttQUGBqqurlZaWpjvuuENz5sxRcnKyJGnhwoUaMWKE/uu//ktjxoxRTU2NVqxYoS5dAt5FAAAAdBAdYk3AoEGDTrkzQ0ZGhjIyMtyv+/btq8LCwlNeIzo6Wvfee6/uvfdev9UJAACAzo1pUQAAABgG4RcAAACGQfgFAACAYRB+AQAAYBiEXwAAABgG4RcAAACGQfgFAACAYRB+AQAAYBiEXwAAABgG4RcAAACGQfgFgCBSVVWlyZMnKyEhQePHj9fWrVtP2e7IkSOaNm2aEhMTNXbsWJWUlHic/+lPf6qEhAQlJiYqMTFRV1xxRXuUDwB+FxroAgAAvuF0OpWbm6usrCytXbtW27ZtU15ensrLy9WvXz+Ptnl5eRoxYoRWrlypPXv2aPr06bJarUpISNDnn3+umpoa7dy5U+Hh4QHqDQD4BzO/ABAkKioq5HA4lJ2drbCwMI0bN04pKSkqKyvzaGez2bR7927NmjVLZrNZSUlJSk9Pd8/+7t27VwMGDCD4AghKhF8ACBKHDh2S1WqVyWRyH4uLi9PBgwc92tlsNvXp00cWi+WU7fbu3avGxkZNnDhRo0aN0rRp03To0KH26QQA+BnhFwCCRF1dnSIiIjyORUREqL6+3uOY3W7/3nahoaEaMWKEVqxYoc2bN2vIkCHKyclRXV2dfzsAAO2ANb8AECQiIyPlcDg8jjkcDkVGRnrVbvr06R7n7rzzTj3//PPavXu3UlNT/VA5ALQfZn4BIEhYrVYdPnzY45jNZtPAgQNbtTt69Kjsdvsp2z399NPauXOn+1xTU5Oam5tZAwwgKBB+ASBIpKamKiQkRIWFhXI6ndq8ebMqKio0YcIEj3ZxcXGKj4/X0qVL1dDQoMrKSpWVlSkzM1OS9PHHH+uBBx7Q559/rvr6ej3wwAP6yU9+ogsuuCAQ3QIAnyL8AkCQMJvNKioq0pYtWzRq1CgtWbJEy5cvV2xsrEpLS5WYmOhuW1BQoOrqaqWlpemOO+7QnDlzlJycLEmaM2eOhg4dqszMTF188cX67LPP9NRTTykkJCRQXQMAn2HNLwAEkUGDBmn9+vWtjmdkZCgjI8P9um/fviosLDzlNSIjI3Xvvffq3nvv9VudABAozPwCAADAMAi/AAAAMAzCLwAAAAyD8AsAAADDIPwCAADAMAi/AAAAMAzCLwAAAAyD8AsAAADDIPwCAADAMLwKvxs2bPBXHQAAAIDfeRV+X3jhBX/VAQAAAPgdyx4AAABgGKGna5CVlSWTySSXy6WPP/5YU6dOlcvlkslk0rPPPtseNQIAAAA+cdrwu3jxYkmSy+XS9OnT9eCDD/q9KAAAAMAfTht++/Xr5/6z2Wz2eA0AAAB0Jl6t+e3Vq5e/6gAAAAD8zqvwW1hY6K86AAAAAL9jtwcAAAAYBuEXAAAAhuF1+H3llVf8UAYAAADgf16H34cffliTJ0/W3r17/VEPAAAA4Ddeh9/XX39dw4YN0y9/+Uvdc889qqmp8UddAAAAgM95HX67deume+65Ry+++KL+93//V+PHj9ezzz6rlpYWf9QHAAAA+MxpH3LxXYYMGaK1a9eqvLxcS5Ys0caNG/W73/1Oo0aN8mV9ncqxY8fU0NDQ5vZNTU0KDW3bP0F4eLiio6PPtDQAAADoB4RfSfr444/lcrmUlpaml19+Wb/+9a91+eWXa968eerdu7evauwUmpqaNGnSJNntdr9cPyoqSmVlZW0OywAAAGjN6yT1+OOP68MPP9Tu3bv17bffymKxaMSIEbr11ls1bNgwvfTSS0pPT9fjjz9uqFng0NBQFRcXt3nmt6amRjk5OVq9erViYmJO2z48PJzgCwAA8AN5naY2bdqkCy+8ULNnz1ZSUpLOO+88j/OXXHKJnnnmGS1cuFCvv/56m65ZVVWl/Px87d+/X3369NHdd9+tMWPGtGp35MgRzZ8/Xzt37lTPnj11yy23aOLEie7zGzdu1KpVq1RTU6PBgwdr0aJFGjx4sLddPGPR0dFeL02IiYnRWWed5aeKAAAAcDKvb3j797//rTlz5uiXv/xlq+B7Qnp6uj7++OM2Xc/pdCo3N1dXXHGFtm/frrvuukt5eXmqrq5u1TYvL09Wq1UVFRVaunSpFi9erF27dkmStmzZomXLlumxxx7Tjh07lJaWplmzZnnbPQAAAAQxr8NvY2PjKXd2+PrrrzVjxgxJUo8ePfSHP/yhTderqKiQw+FQdna2wsLCNG7cOKWkpKisrMyjnc1m0+7duzVr1iyZzWYlJSUpPT1dJSUlkqR169YpJydH559/vkJCQjR9+nQ98sgj7EIBAAAAtzaH32uuuUYLFiyQyWTS3r175XA4PM43NDRo27Ztxy/apUub1/seOnRIVqtVJpPJfSwuLk4HDx70aGez2dSnTx9ZLJZTttu7d6+6dOmiyZMnKzU1Vbm5ubJYLOrShSc4AwAA4Lg2r/m96qqr9MEHH8jlcunmm29WSEiIBgwYoKFDhyo+Pl6HDx9W3759vS6grq5OERERHsciIiJUX1/vccxut39vu2+++UYbNmzQk08+qdjYWC1btky5ubkqLS3lRjEAAABI8iL83nzzzZKkxMRElZSU6OjRo9q/f7/27dunl156SU1NTZo7d67XBURGRraaRXY4HIqMjPSqndls1uTJkzVw4EBJ0uzZs7V27VrZbDYNGjTI67oAAAAQfLyeEt25c6dMJpPi4uJ00UUX/eACrFarVq9e7XHMZrMpMTGxVbujR4/KbrcrKirK3e5E2I2Li5PT6XS3b25ulsvl+sH1wbh4aAk6I1/tnnPCxo0b9cADD6iysrI9ygcAv2vTd+rJkydrzZo1ioqK0q5duzRkyBB17drVJwWkpqYqJCREhYWFys7O1ttvv62KigrNnz/fo11cXJzi4+O1dOlSzZ07V/v27VNZWZlWrlwpSZo4caJWrVqln//85+rfv7+WLVum88477zt3pAC+Dw8tQWd0YvecrKwsrV27Vtu2bVNeXp7Ky8vVr18/j7Z5eXkaMWKEVq5cqT179mj69OmyWq1KSEhwt7HZbHrwwQfbuRcA4F9t+s578cUXu2dRb7jhBplMJp177rnu9b4nfu/Zs6fXBZjNZhUVFWnhwoVatWqVevfureXLlys2NlalpaXKz893zzgUFBQoPz9faWlp6tatm+bMmaPk5GR3Xc3Nzbrtttv073//W8OHD9eTTz7pcSMdfMMIM6I8tASd0cm755hMJo/dc07sxiP93+45a9asabV7zonw63Q6deedd2rKlClat25dgHoEAL7Xpu++J++Xu337dn300Ufat2+fPvroI23atEmPPvqompub1bt3b23ZssXrIgYNGqT169e3Op6RkaGMjAz36759+6qwsPCU1zCZTJo6daqmTp3q9edH2xlpRpSHlqCz+aG755SXl7tfL1u2TElJSRo9ejThF0BQ8TphWCwWXXjhhbrwwgvdx5xOp/bv36+PPvrIp8Wh42FGFOi4fLV7zltvvaV//OMfevHFF90PEgKAYNGmlJGenq7zzz/fY4nDiZvOpONLF4YPH67hw4f7rVB0HMyIAh2TL3bP+fLLL5Wfn68VK1YoPDzc7zWj86qtrW31/8gXampqPH73tYiICI93PWA8bQq/mZmZ+uijj7R+/Xp9/PHHMplMio2N1dChQz1+ncmaXwCAb/hi95y3335bX375pW688UZJx3fOqaurU3JyskpLS3X22We3T2fQodXW1uq6SdfJXuufJXCSlJOT45frRlmitLF4IwHYwNoUfk/+D1hXV+de73tix4XHHnvsB635BQD8cL7YPSc5OVmZmZnuthUVFZoxY4Z27NjR3t3ptIwwI+pwOGSvtav50mYp4vTtvdYiL55B6wWHZH/DLofDQfg1MK8XV0ZGRio5Odm9y4LEml8A6Ah8tXsOzlxtba0mT/qFvq2t89vn8NeMaDdLpF4oftG7UBghyTc7nwLtxut9fisrK1vt88uaXwDoGHyxe87JUlNTffaAC6PMiH5bW6ffp3yrHuEtPq+l2SWF+GEHz68buuie98SMKAwh4Pv8AgCC3/E1or+UvfaY3z6H/9aIRmtj8QavQmGP8Bb1DO9MTxn1fVAHOqoOsc8vACC4HV8jekz1518jV5gf3id3tUgm3y8SNTXWS3teYUYUCCJntOaXfX4BAGfCFdZVLnPU6RsCgJ94HX4TEhK0detWjyUOrPkFOhYjrK0EAOBMeB1+nU6nWlparw36+uuvNXfuXK1atconhQE4M7W1tfrlddfpmJ8eQS35b21ldFSUNmxk/00AgP+0Ofxec801Gj58uEwmk/bu3auUlBSPx2M2NDRo27ZtfikSQNs5HA4ds9uVK8kfEdJf22/WSlppZ/9NAIB/tTn8XnXVVfrggw/kcrl08803KyQkRAMGDHDv9HD48GH17dvXn7UC8IJFUjf5YU8kv+lMd8YDADqrNoffm2++WZKUmJiokpISHT16VPv379e+ffv00ksvqampSXPnzvVboQAAAMAP5fWa3507d8pkMikuLk4XXXSRP2oCAAAA/MLrpXsmU2d6GxUAAAD4P17P/B49elQPPvigDh06pN69e2vYsGEaOnSohg4dqtjYWH/UCAAAAPiE1+E3Pz9f//73vzVp0iQ9/PDD+te//qWnnnpKJpNJ0dHReu+99/xRJwAAQLtj3/Tg43X4ff/997V69WqNGDFCjzzyiFauXKnPP/9cc+fO1ZQpU/xRIwAAQLtj3/Tg5HX4bWlpUa9evSRJ4eHhqq+vV1JSkmbOnKmKigqfFwgAABAI7JsenLwOv+eee64+++wznX322erdu7eOHDmiQYMGaeTIkXrwwQf9USMAAEDAGGHfdCMt7/A6/Kanp+u9995TcnKyLr74Yj3//PMaPXq0Kisr5XKxST0AAEBnUltbq0mTfqna2mN++xz+Wt5hsUSruHiDVwHY6/D761//2v3nm266Sb/4xS+UmJioxsZGZWdne3s5AAAABJDD4VBt7TFdNnSqIsKifH79FleLuph8v8DD0WjXX/c96/XyDq/D78l69eql8vJybd26VT179lRaWtoPuRwAAAACJCIsSl3Nwb9GuE3hNz09Xeeff77i4+M1dOhQxcfHKyrq+E8G0dHRuvrqq/1aJAAAAOALbQq/mZmZ+uijj7R+/Xp9/PHHMplMio2NdT/c4sSvnj17+rteAAAA4Iy1KfyevEi5rq5O+/bt00cffaR9+/aprKxMjz32mJqbm9W7d29t2bLFX7UCAAAAP4jXa34jIyOVnJys5ORk9zGn06n9+/fro48+8mlxAAAAgC+d8Q1vtbW1cjqdiomJkdls1vDhwzV8+HBf1gYAAAD4lNfh9/PPP9fs2bO1Y8cOSdJZZ52la665RjNmzHDfBAcAAAB0RF6H39///vdqbGzUqlWrZDabdeDAAa1du1Z///vf9eyzz/IYPQAAAHRYXu84/O6772rRokUaM2aMLrroIv3qV7/Sa6+9ph49eujxxx/3R40AAACAT3gdfk0mk0JDPSeMzWaz8vLy9Prrr/usMAAAAMDXvA6/F198sdauXdvqePfu3XXsmP+eCQ0AAAD8UF6v+Z09e7YmTZqkr776StOmTdN5550nh8Ohp556SvHx8f6oEQAAAPCJNs/8btq0SZIUGxur5557TjU1NZo0aZKSkpJ08cUXa9u2bZo3b57fCgUAAAB+qDbP/M6bN08lJSXKz8+X1WrV2rVr9emnn+rAgQOKiorS+eefz1ZnAAAA6NDaPPNbUlKi5uZmZWZmasmSJaqvr9c555yjsWPHKjU1leALAACADq/N4XfIkCF67rnndP/996u0tFRXXnml/vznP/uzNgCAl6qqqjR58mQlJCRo/Pjx2rp16ynbHTlyRNOmTVNiYqLGjh2rkpIS9zm73a67775bqampGjlypGbOnKl//etf7dUFAPArr3d7yMzM1Ouvv64rr7xSd9xxh3JycvTPf/7TH7UBALzgdDqVm5urK664Qtu3b9ddd92lvLw8VVdXt2qbl5cnq9WqiooKLV26VIsXL9auXbskSQ899JC+/PJLvfHGG3rnnXfUvXt3LViwoJ17AwD+4XX4laSoqCjNmTNHmzZtUktLi66++mo9/vjjcjqdvq4PANBGFRUVcjgcys7OVlhYmMaNG6eUlBSVlZV5tLPZbNq9e7dmzZols9mspKQkpaenu2d/FyxYoIKCAkVHR8tut8tutysmJiYQXQIAnzuj8HuC1WrV008/rWXLlumVV17RhAkTfFUXAMBLhw4dktVqlclkch+Li4vTwYMHPdrZbDb16dPH43H0J7cLDQ1VeHi4HnroIV100UXatWuXbrnllvbpBAD4mdf7/EpSdXW1Dhw4oAMHDqiqqkoHDhzQ0aNH1dLS4uv6AABtVFdXp4iICI9jERERqq+v9zhmt9vb1C4vL0+zZs3Sgw8+qGnTpunVV19VWFiYf4oHgHbS5vC7YMECHThwQAcPHlRdXZ2k43v+DhkyRFdeeaWGDBmiIUOG+K1QAPhPtbW1cjgcPr9uTU2Nx+++FhER4THr6iuRkZGt/j4cDociIyPPqF14eLgkae7cuRo5cqQOHDigYcOG+bxuAGhPbQ6/VVVVGjx4sDIyMjRkyBANHjyY7c0ABExtba0mTfqlamv991j1nJwcv1zXYolWcfEGnwdgq9Wq1atXexyz2WxKTExs1e7o0aOy2+3ucdxms2ngwIGSpBkzZig9Pd29lK2pqUktLS3q1q2bT+sFgEBoc/jdsGGDP+sAAK84HA7V1h7TZUOnKiLM9z+It7ha1MX0g26LOCVHo11/3fesHA6Hz8NvamqqQkJCVFhYqOzsbL399tuqqKjQ/PnzPdrFxcUpPj5eS5cu1dy5c7Vv3z6VlZVp5cqVkqQLLrhAK1asUHJysiwWi+677z6NGjVKsbGxPq0XAALhjNb8AkBHEREWpa5m3y8h6IzMZrOKioq0cOFCrVq1Sr1799by5csVGxur0tJS5efnq7KyUpJUUFCg/Px8paWlqVu3bpozZ46Sk5MlSdOnT1d9fb0mTpyo5uZm/fSnP9Wjjz4awJ4BgO90iPBbVVWl/Px87d+/X3369NHdd9+tMWPGtGp35MgRzZ8/Xzt37lTPnj11yy23aOLEia3abdy4UQ888IB7kAcAoxg0aJDWr1/f6nhGRoYyMjLcr/v27avCwsJTXiM0NFR33nmn7rzzTr/VCQCB4vv39Lzkq03ZT7DZbHrwwQfbqXoAAAB0JgEPv77alF06HqTvvPNOTZkypb27AQAAgE4g4OHXV5uyS9KyZcuUlJSk0aNH+79wAAAAdDoBD7++2pT9rbfe0j/+8Q/ddddd/i0YAAAAnVbAb3jzxabsX375pfLz87VixQr3puwAAADAfwr4zK/VatXhw4c9jp282frJ7U5syv6f7d5++219+eWXuvHGG5WcnKwZM2aorq5OycnJ+uyzz9qlHwAAAOj4Ah5+T96U3el0avPmzaqoqHA/WeiEkzdlb2hoUGVlpcrKypSZmanMzEx98MEH2rFjh3bs2KFVq1YpMjJSO3bs0Nlnnx2gngEAAKCjCXj4PbEp+5YtWzRq1CgtWbLEY1P2kx/LWVBQoOrqaqWlpemOO+7w2JQdAAAAOJ2Ar/mVfLMp+8lSU1N5wAUAAABaCfjMLwAAANBeCL8AAAAwDMIvAAAADIPwCwAAAMMg/AIAAMAwCL8AAAAwDMIvAAAADIPwCwAAAMMg/AIAAMAwCL8AAAAwjA7xeOOOqra2Vg6Hwy/Xrqmp8fjd1yIiImSxWPxybQAAgM6K8Psdamtrdd2kX8pee8yvnycnJ8cv142yRGtj8QYCMAAAwEkIv9/B4XDIXntM9edfI1dYV/98EleLZPL9yhNTY7205xU5HA7CLwDAf/zz5qj/dLZ64ReE39NwhXWVyxwV6DIAAOgwmpubJUkhb4QEuJIzc6J+GBPhFwAAH/umwRToErzibb0hIcdDb/OlzVKEPyryE8fxwH6ifhgT4ReS/HdzX0e7sc8o/QQ6KlNjXaBL8Iq39Z6YUZz/Xnd/lON3Xs+IRkjy08pAwF8Iv1Btba0mT/qFvq313zclf93Y180SqReKX2xTMDx+E+N1stfa/VKL5M8bGKO0sXgjARid1olQ1XXPpgBXcmbaGgpPzCjel/KNuoe7/FmST33TYNL897ozIwpDIPxCDodD39bW6fcp36pHeIvPr9/skkL88A7g1w1ddM97avONfcdvYrT77226Fvln52yHZH/Dzg2M6NROhKr68zPlCosMcDVtZ2qsU9c9m7wOhd3DXerZicIvYCSEX7j1CG/pZIP1GQZ13qYDAsYVFslNxEAH5Wj03zuj/nCm9RJ+AQAAvketJKnzTA7Vetn+xLKev+571vfFtANv16oTfgEAAE7hRKhaGeA6zpS3a9UvGzpVEWGd550ZR6Ndf933rNfLkgi/AAAAp3AiVOVK6kx3XNTqeGD3NhRGhEWpq7kz9fTMEH4BAAC+h0VSN3WmvZs7zxKNQPDHvekAgACpqqrS5MmTlZCQoPHjx2vr1q2nbHfkyBFNmzZNiYmJGjt2rEpKStznmpubtWzZMv3sZz9TcnKysrKytH///vbqAgD4FeEXAIKE0+lUbm6urrjiCm3fvl133XWX8vLyVF1d3aptXl6erFarKioqtHTpUi1evFi7du2SJD399NPaunWrXnjhBb377rtKTU3VzTffrMbGxnbuEQD4HuEXAIJERUWFHA6HsrOzFRYWpnHjxiklJUVlZWUe7Ww2m3bv3q1Zs2bJbDYrKSlJ6enp7tnf2tpazZw5U2effbZCQ0OVnZ2to0eP6tNPPw1EtwDAp1jzCwBB4tChQ7JarTKZ/m9tYlxcnA4ePOjRzmazqU+fPh4PTYmLi1N5ebkk6fbbb/do/8Ybbyg6OlrnnHOOH6sHgPbBzC8ABIm6ujpFRHg+vjAiIkL19fUex+x2e5vaSdK7776re++9V/fcc4/CwsJ8XzQAtDPCLwAEicjISDkcDo9jDodDkZGRZ9SuuLhYubm5WrBggTIzM/1TNAC0M8IvAAQJq9Wqw4cPexyz2WwaOHBgq3ZHjx6V3W4/ZTuXy6UHHnhAy5cvV1FRka655hq/1w4A7YXwCwBBIjU1VSEhISosLJTT6dTmzZtVUVGhCRMmeLSLi4tTfHy8li5dqoaGBlVWVqqsrMw9u1tYWKhXX31VxcXFSk5ODkRXAMBvuOENCFLB/ix6tGY2m1VUVKSFCxdq1apV6t27t5YvX67Y2FiVlpYqPz9flZWVkqSCggLl5+crLS1N3bp105w5c5ScnCyXy6WioiLV19crIyPD4/rPPfechg4dGoiuAYDPEH6BIGOUZ9Gf4Gi0n75RB+LvegcNGqT169e3Op6RkeERZvv27avCwsJW7Uwmk3bs2OHXGgEgkAi/QJAxyrPoT4Tkv+571n9F+ZG3IR8A4BuEXyBIBfuz6E+E5MuGTlVEWJQ/CvILR6Ndf933bJtDPgDAtwi/ADq1iLAodTV3pjluAEAgsdsDAAAADIPwCwAAAMMg/AIAAMAwCL8AAAAwDMIvAAAADIPwCwAAAMMg/AIAAMAw2Of3NEyNdYEuwWudsWYAAID2QPj9DicePdp1z6YAV3LmeHwqAMCvHH66bov88960v+pFp0L4/Q4nHj1af36mXGGRAa7GO6bGOnXds4nHpwIA/CIiIkJRlijZ37AHuhSvRVmiFBEREegyEECE39NwhUXKZY4KdBnt4psGU6BL8MoZ19vZfvLvbPUC0NcNXXR8+tK3ml1SiB+G6uP1tp3FYtHG4o1yOHw/QNXU1CgnJ0erV69WTEyMz68fEREhi4VHohtZhwi/VVVVys/P1/79+9WnTx/dfffdGjNmTKt2R44c0fz587Vz50717NlTt9xyiyZOnCjp+Fv8jz76qDZt2qS6ujrFx8frd7/7nYYMGdLe3el0TiyPmP9e9wBXcmbaurzjRLuQNzrnjDjLWICOLyIiQt0skbrnvUBX4r1ulkivZkQtFotfQ2RMTIzOOussv10fxhXw8Ot0OpWbm6usrCytXbtW27ZtU15ensrLy9WvXz+Ptnl5eRoxYoRWrlypPXv2aPr06bJarUpISNDTTz+trVu36oUXXtCPf/xjrVq1SjfffLPefPNNhYWFBah3ncOJ5RH3pXyj7uGuAFfTdt80mDT/ve5tXt5xol3zpc1SZ3rHy3E8sLOMBcHA1Fjvnwu7WiST7xeJeluvxWLRC8UvMiOKTsnR6J9lLC2uFnXxw9fnmdYb8PBbUVEhh8Oh7OxsmUwmjRs3TikpKSorK9OMGTPc7Ww2m3bv3q01a9bIbDYrKSlJ6enpKikpUUJCgmprazVz5kydffbZkqTs7GwVFBTo008/1YABAwLVvU6le7hLPTtR+D1jEZK6BroIwFiOrxGNlva8EuhSvBZliWZG1OBqJUm+//7or/v6ar1sf/wHn2j9dd+zfqjGvyxefn1KHSD8Hjp0SFarVSbT/y1iiouL08GDBz3a2Ww29enTx2NAiYuLU3l5uSTp9ttv92j/xhtvKDo6Wuecc44fqwcAtMXxNaIbmBFFpxIREaHoqCittHe+G/uio9p+Y5/FYlGxgb4+Ax5+6+rqWv3jREREqL7e860mu93epnaS9O677+ree+9Vfn4+Sx4AoINgRhSdjcVi0YaNxrixz0hfnwEPv5GRka3+UzkcDkVGRp5Ru+LiYj344IPKz89XZmamf4oGAACGYKRQaBQBf7yx1WrV4cOHPY7ZbDYNHDiwVbujR4/KftJbDye3c7lceuCBB7R8+XIVFRXpmmuu8XvtAAAA6FwCHn5TU1MVEhKiwsJCOZ1Obd68WRUVFZowYYJHu7i4OMXHx2vp0qVqaGhQZWWlysrK3LO7hYWFevXVV1VcXKzk5ORAdAUAAAAdXMDDr9lsVlFRkbZs2aJRo0ZpyZIlWr58uWJjY1VaWqrExER324KCAlVXVystLU133HGH5syZo+TkZLlcLhUVFenrr79WRkaGEhMT3b/27dsXwN4BAACgIwn4ml9JGjRokNavX9/qeEZGhjIyMtyv+/btq8LCwlbtTCaTduzY4dcaAQAA0PkFfOYXAAAAaC8dYuYXAM6UUZ5IBADwDcIvgE7JaE8kAgD4BuEXCFLB/jhOoz2RCADgG4RfIMgY5XGcEpvPAwC8R/gFgoyRHscJAIC3CL9AEGJGFACAU2OrMwAAABgGM7+nYWqs99/FXS2SH7ZSOtOav27oouO3M/lWs0sKMfn8sv+/XgAnq6qqUn5+vvbv368+ffro7rvv1pgxY1q1O3LkiObPn6+dO3eqZ8+euuWWWzRx4kSPNi0tLZo1a5YSExM1bdq09uoCAPgV4fc7REREKMoSLe15JdClnJEoL7ZSioiIUDdLpO55z89F+UE3S6T3W0b5finscf7aBsFf9SLoOJ1O5ebmKisrS2vXrtW2bduUl5en8vJy9evXz6NtXl6eRowYoZUrV2rPnj2aPn26rFarEhISJElffPGFFixYoDfffNPjMfMA0NkRfr+DxWLRRj9toyR1rBuHLBaLXih+MehvkDr+A02U7G90vl0Qoize7YIAY6qoqJDD4VB2drZMJpPGjRunlJQUlZWVacaMGe52NptNu3fv1po1a2Q2m5WUlKT09HSVlJQoISFBx44d09VXX61f/vKXqqmpCWCPAMD3CL/fw983DUkd58YhI9wgdfwHGnZBQPA6dOiQrFarTKb/W2cUFxengwcPerSz2Wzq06ePx/+puLg4lZeXSzr+/+21115TTEyMsrKy2qd4AGgnhF8YihFCPoyrrq6u1TsEERERqq/3vA/Abrd/b7uwsDC//BAHAB0BdwwBQJCIjIxs9c6Gw+FQZGTkGbUDgGBE+AWAIGG1WnX48GGPYzabTQMHDmzV7ujRo7Kf9BTAU7UDgGBE+AWAIJGamqqQkBAVFhbK6XRq8+bNqqio0IQJEzzaxcXFKT4+XkuXLlVDQ4MqKytVVlamzMzMAFUOAO2H8AsAQcJsNquoqEhbtmzRqFGjtGTJEi1fvlyxsbEqLS312LKsoKBA1dXVSktL0x133KE5c+YoOTk5gNUDQPvghjcACCKDBg3S+vXrWx3PyMhQRkaG+3Xfvn1VWFh42uutXbvWp/UBQKAx8wsAAADDIPwCAADAMAi/AAAAMAzCLwAAAAyD8AsAAADDIPwCAADAMAi/AAAAMAzCLwAAAAyD8AsAAADDIPwCAADAMAi/AAAAMAzCLwAAAAyD8AsAAADDIPwCAADAMAi/AAAAMAzCLwAAAAyD8AsAAADDIPwCAADAMAi/AAAAMAzCLwAAAAyD8AsAAADDCA10AQAC69ixY2poaGhT25qaGo/f2yI8PFzR0dFnVBsAAL5G+AUMrKmpSZMmTZLdbvfq43JyctrcNioqSmVlZQoNZbgBAAQe340AAwsNDVVxcXGbZ36l44HZmyAbHh5O8AUAdBh8RwIMLjo6mmUJAADDIPwC38Gfa2FZBwsAQGAQfoFT8PdaWNbBAgAQGHznBU7B32thWQcLAEBg8N3Xh3ibPLiwFhYAgODTIcJvVVWV8vPztX//fvXp00d33323xowZ06rdkSNHNH/+fO3cuVM9e/bULbfcookTJ0qSXC6XHn/8cW3YsEENDQ269NJLlZ+fr8jIyHbpA2+TAx0b+xkDAKQOEH6dTqdyc3OVlZWltWvXatu2bcrLy1N5ebn69evn0TYvL08jRozQypUrtWfPHk2fPl1Wq1UJCQnasGGDXn/9dZWUlCgyMlKzZ8/WQw89pEWLFrVLP3ibHOi42M8YHRXvGALtL+CjdEVFhRwOh7Kzs2UymTRu3DilpKSorKxMM2bMcLez2WzavXu31qxZI7PZrKSkJKWnp6ukpEQJCQl6+eWXdeONN6pv376SjgflrKwszZ8/X2FhYe3SF94mBzom9jNGR8Q7hkBgBPwr4tChQ7JarTKZTO5jcXFxOnjwoEc7m82mPn36yGKxeLQrLy93X2fgwIEe5+rr61VdXa3+/fv7txMGw0wFOiOj/HAaDMvIJGOMM0Z6x9Aoy47oZ2sdsZ8B/6qoq6tTRESEx7GIiAjV19d7HLPb7d/brq6uTl27dnWfO/Hn/7wOfhhmKoCOK1iWkRlpnDHCD2VGWXZEP79fR+pnwL/yIyMj5XA4PI45HI5WMwyna9e1a1eP8ydCb3vOVBiBkWYqgM4mWJaRMc4EF6MsO6Kf362j9TPgX/1Wq1WrV6/2OGaz2ZSYmNiq3dGjR2W32xUVFeVud2Kpw8CBA2Wz2ZSSkuI+17Vr11azHfjhjDBTAXRGwbSMjHEmuBjl35N+dg5dAl1AamqqQkJCVFhYKKfTqc2bN6uiokITJkzwaBcXF6f4+HgtXbpUDQ0NqqysVFlZmTIzMyVJGRkZ+sMf/qBPPvlE33zzjR599FFdffXVAf8JCQDaC8vIAOD0Ah5+zWazioqKtGXLFo0aNUpLlizR8uXLFRsbq9LSUo8Z4IKCAlVXVystLU133HGH5syZo+TkZEnS9ddfr6uvvlpTpkzRpZdeqpiYGM2bNy9Q3QKAdscyMgA4vQ4xLTpo0CCtX7++1fGMjAxlZGS4X/ft21eFhYWnvEaXLl1022236bbbbvNbnQDQkbGMDABOL+AzvwAA32AZGQCcHuEXAIIEy8gA4PRMLpfLFegiOoLBgwerqqoq0GUAMLBgGYeCpR8AOq/vG4eY+QUAAIBhEH4BAABgGIRfAAAAGAbhFwAAAIZB+AUAAIBhEH4BAABgGIRfAAAAGAaP6znJ4MGDA10CAAQFxlMAHRUPuQAAAIBhsOwBAAAAhkH4BQAAgGEQfgEAAGAYhF8AAAAYBuEXAAAAhkH4BQAAgGEQfgPkww8/VEpKSqDL8KsPP/xQN9xwg5KTk3XJJZeooKBAwbiz3t/+9jelp6crMTFRl112mV544YVAl+Q3dXV1Gj9+vNasWRPoUvymuLhYw4YNU2JiovvXyy+/HOiycBrBPqYyngYfxtPA4SEXAfDaa69pwYIFam5uDnQpfmO32zV9+nTdcsstWrt2rT755BNNmzZNMTExmjJlSqDL85nPPvtMs2bN0sqVKzV69Gjt379fkyZN0tChQzV8+PBAl+dz9913nz7++ONAl+FXe/bsUU5Ojm6//fZAl4I2CvYxlfGU8bSz6qjjKTO/7Wz58uVavXq1Zs6cGehS/OrIkSNKSkrSjTfeqJCQEPXv31+XXXaZdu7cGejSfOrss8/WO++8o9GjR6ulpUVfffWVQkJCZLFYAl2az/3pT3/S//7v/yopKSnQpfjV3r17FR8fH+gy0EZGGFMZTxlPO6uOOp4SftvZDTfcoJdeeknDhg0LdCl+NXDgQD355JPu106nU9u2bdOQIUMCWJV/WCwW1dbW6vzzz1d2draysrIUFxcX6LJ86rPPPtOSJUv08MMPq0uX4B02GhsbdeDAAb300ksaPXq0LrvsMhUWFgbl28vBwghjKuMp42ln1JHHU5Y9tLPevXsHuoR253Q6NXv2bJnN5qB6i+5kXbt21a5du7R//3795je/Uf/+/XXttdcGuiyfaG5u1l133aXf/va3OueccwJdjl/V1NRo+PDhuvbaa/XEE0/of/7nfzRz5kx17dpVWVlZgS4Pp2C0MZXxtHNjPO0Y42nw/siBDuHzzz/X1KlT9cUXX+gPf/iDIiMjA12SX4SEhMhsNmv48OH6xS9+ob/85S+BLslnVq5cqd69eyszMzPQpfhd79699dxzz2n8+PEym80aOnSosrKygurfE50X42nnx3jaMcZTwi/85sCBA5o4caL69++vZ599Vj179gx0ST73zjvvaNKkSR7HGhsb1a1btwBV5Hvl5eXasmWLkpOTlZycrPfff1+PPfaYFi5cGOjSfK6qqkpPPPGEx7GGhgaZzeYAVQQcx3gaHBhPO8h46kJAvPvuu66EhIRAl+E3NTU1rrS0NNdDDz0U6FL86quvvnKlpqa6nnnmGVdTU5Nr+/btruTkZFdFRUWgS/ObG2+80bV69epAl+EXn3zyieuCCy5wvfjii67m5mbXBx984Lroootcr732WqBLw2kE85jKeMp42hl15PGUmV/4xSuvvKLPP/9c69ev99jfb9asWYEuzad69OihoqIivfbaa0pJSdGiRYt0//33B/V+o8HsnHPO0YoVK/Tcc89p5MiRysvL0y233KIrr7wy0KXBwBhPGU87o448nppcrg5w2x0AAADQDpj5BQAAgGEQfgEAAGAYhF8AAAAYBuEXAAAAhkH4BQAAgGEQfgEAAGAYoYEuAPCV+vp6rVmzRuXl5aqurlaPHj2UlpamGTNmqH///oEuDwA6DcZTBDP2+UVQqK+v19SpU2W325WXl6dhw4bp888/17PPPqtt27apqKhIiYmJgS4TADo8xlMEO8IvgsLDDz+sv/zlL3r55ZcVHR3tcW7evHnavn27XnvtNYWFhQWoQgDoHBhPEexY84tOr6WlRRs3btSvfvWrVgO1JP32t7/VJ598or///e9qaGjQAw88oLS0NCUlJWnmzJk6evSo+zpPPvmkLrnkEiUmJmrq1Kk6dOiQJGnu3LmtHiU6duxYrVu3zn3+zjvv1HXXXaeUlBT94x//UGNjo5YuXaq0tDSNHDlS06ZNk81mc398VlaWHn/8ceXm5mr48OEaM2aMNm7c6D7/fbVK0po1a/Tzn/9ciYmJuv7667Vr1y6f/Z0CMCbGU8ZTIyD8otM7fPiwvv32W40YMeKU53v37q3+/fursrJSCxYs0JtvvqklS5Zo48aNqqur0+zZsyVJTzzxhNauXat58+bp5ZdfVq9evXTzzTerubm5TXWUl5drypQpeuaZZ5SYmKjHH39c27Zt06OPPqri4mINGDBAWVlZOnbsmPtjioqK9NOf/lSvvvqqLrvsMi1atEhffPGFJH1vrS+88ILWrVunhQsX6uWXX9aYMWP0q1/9Sp9++ukP+asEYHCMp4ynRkD4Raf3zTffSJJ69OjxnW169Oihf//73yovL9e8efN08cUXy2q16t5771ViYqKcTqeef/55zZw5U5dffrn69++v/Px8XXbZZe7rn86AAQN0zTXXKD4+XiaTSX/84x+1cOFCXXjhhbJarZo/f74sFotKS0vdH5OamqobbrhBsbGxysvLU2Njo/bv369jx459b61PPfWUZs+erTFjxqh///6aMWOGRo4cqeeee+4H/V0CMDbGU8ZTI2C3B3R63bt3lyTV1tZ+Z5tjx47pyy+/VFNTky644AL38Z/85Ce68847VVNTo5qaGo9z3bp109y5c9tcR2xsrPvP//znP+V0OnXTTTfJZDK5jzc0NHi8VXfyXdMWi0WS1NTUpMOHD39nrXa7XZ999pnmz5+vBQsWuM87nU6ZzeY21wsA/4nx9DjG0+BG+EWn179/f8XExOj999/X0KFDW52vqanR4cOHNWnSJG3dulWnusfzxI0b33X/58kD7glNTU0eryMiItx/PvHW3tNPP60f/ehHHu1ODMonf96TuVwu96B7qnpOXHvx4sWt+ntyDQDgLcbTU9eA4MKyB3R6ISEhmjx5sp5++ml9/fXXrc4/+uij6tu3ryZOnKiQkBDt27fPfe6TTz7RqFGjVFdXpx/96Ece5xwOh9LS0vThhx8qLCxMdrvdfc5ut6umpuY7a/rJT36i0NBQ1dTU6Nxzz9W5556r2NhYPfbYY/rggw9O26dzzjnnO2utr69Xr169dPToUfe1zz33XD3zzDN66623TnttAPgujKeMp0bAzC+CQm5urrZv367rr79es2fP1rBhw/TFF19o3bp1evPNN1VUVCSLxaJf/OIXWrx4sSwWi3r06KHFixfrvPPOU+/evZWdna2VK1fqnHPO0bnnnqsVK1bIYrFoyJAhuuCCC7Rp0yZt3bpVsbGxKigoUJcu3/2zY1RUlK6//nrdf//9CgsLU//+/bVmzRr97W9/0+23337a/pyu1pycHK1YsUI//vGPdf7556u0tFQbNmzQ2rVrffnXCsCAGE8ZT4Md4RdBwWw26+mnn9Yf//hHPfroo/r000/VrVs3paWlqaSkxL0WbO7cuVq8eLFuvfVWNTc3Ky0tTffcc48k6aabbpLdbtf8+fNlt9uVlJSkp556SmazWZmZmdq1a5duv/12hYeH69e//rW++uqr763pv//7vxUaGqp58+aptrZW8fHxWr16tcdatu/zfbVOnTpVDodDS5Ys0RdffKEBAwbo8ccfV1JS0pn/JQKAGE8ZT4MfD7kAAACAYbDmFwAAAIZB+AUAAIBhEH4BAABgGIRfAAAAGAbhFwAAAIZB+AUAAIBhEH4BAABgGIRfAAAAGAbhFwAAAIbx/wDruLh078xWXwAAAABJRU5ErkJggg==",
      "text/plain": [
       "<Figure size 720x432 with 2 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "fig, ax = plt.subplots(1,2, figsize=(10,6))\n",
    "fig.tight_layout()\n",
    "\n",
    "sns.boxplot(\n",
    "    data=firm, x = 'Occurence', y = 'stdReturn+20', showfliers=False, ax=ax[0]\n",
    ")\n",
    "sns.boxplot(\n",
    "    data=graph_1, x = 'Occurence', y = 'stdReturn+20', showfliers=False, ax=ax[1]\n",
    ")\n",
    "\n",
    "ax[0].set_xlabel('Occurence')\n",
    "ax[1].set_xlabel('Occurence')\n",
    "ax[0].set_ylabel(\"$Volatility^+$\")\n",
    "ax[1].set_ylabel(\"\")\n",
    "ax[0].set_title('A. Firm')\n",
    "ax[1].set_title('B. Industry')\n",
    "plt.savefig('occurence.png', dpi=300)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "covid = disc_long[(disc_long['Topic_H']==94)&(disc_long['filing_dt']>'2019-06-01')&(disc_long['Occurence']<=3)&((disc_long['value']>=1))]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Summary tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "stat_data = pd.read_csv('Data\\stats_data_T2V_H_V3.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "stat_data.dropna(\n",
    "    subset=['added', 'stdReturn+20', 'stdReturn_20', 'SHRTURN', 'NUMBEROFANALYSTS', 'DtA', \n",
    "            'NPM', 'INTtA', 'current', 'BtM', 'DEarnings', 'BETA_90'],\n",
    "    inplace=True\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "year = stat_data.groupby('fyear')[[\"reported_crnt\", \"added\", \"repeated\"]].mean()#.agg(['mean', 'std'])\n",
    "ind = stat_data.groupby('Industry')[[\"reported_crnt\", \"added\", \"repeated\"]].agg(['mean', 'std'])\n",
    "\n",
    "year['obs'] = stat_data.groupby('fyear')['CIK'].count()\n",
    "# year['firms'] = stat_data.groupby('fyear')['CIK'].nunique()\n",
    "\n",
    "year.loc['Total', [('reported_crnt', 'mean'), ('added', 'mean'), ('repeated', 'mean')]] = (\n",
    "    stat_data[[\"reported_crnt\", \"added\", \"repeated\"]].mean().values\n",
    ")\n",
    "year.loc['Total', [('reported_crnt', 'std'), ('added', 'std'), ('repeated', 'std')]] = (\n",
    "    stat_data[[\"reported_crnt\", \"added\", \"repeated\"]].std().values\n",
    ")\n",
    "year.loc['Total', 'obs'] = year['obs'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "stat_data['D_std'] = (stat_data['stdReturn+20'] - stat_data['stdReturn_20'])*100 / stat_data['stdReturn_20']\n",
    "stat_data['D_ba'] = (stat_data['avgBA+20'] - stat_data['avgBA_20'])*100 / stat_data['avgBA_20']\n",
    "stat_data[['stdReturn+20', 'CAR_20', 'avgBA+20']] = stat_data[['stdReturn+20', 'CAR_20', 'avgBA+20']] * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "year = stat_data.groupby('fyear')[[\"reported_crnt\", \"added\", \"repeated\", 'stdReturn+20', 'D_std', 'CAR_20', 'avgBA+20', 'D_ba', 'BETA_90']].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "year[[(          'obs',     ''),\n",
    "    ('reported_crnt', 'mean'),\n",
    "    ('reported_crnt',  'std'),\n",
    "    (        'added', 'mean'),\n",
    "    (        'added',  'std'),\n",
    "    (     'repeated', 'mean'),\n",
    "    (     'repeated',  'std'),\n",
    "]].round(0).astype(int).to_latex('disc_year.tex')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = topics_df[topics_df['CIK'].isin(stat_data['CIK'])].copy()\n",
    "df['ryear'] = df['report_dt'].dt.year\n",
    "\n",
    "sum_year = pd.DataFrame()\n",
    "sum_year['#CIK'] = df.groupby('ryear')['CIK'].nunique()\n",
    "sum_year['#RF'] = df.groupby(['ryear', 'CIK'])['rf_seq'].count().reset_index().groupby('ryear')['rf_seq'].mean().round()\n",
    "sum_year['#IT'] = df.groupby(['ryear', 'CIK'])['Topic'].nunique().reset_index().groupby('ryear')['Topic'].mean().round()\n",
    "sum_year['#TC'] = df.groupby(['ryear', 'CIK'])['Topic_H'].nunique().reset_index().groupby('ryear')['Topic_H'].mean().round()\n",
    "\n",
    "sum_ind = pd.DataFrame()\n",
    "sum_ind['#CIK'] = df.groupby('Industry')['CIK'].nunique()\n",
    "sum_ind['#RF'] = df.groupby(['Industry', 'ryear', 'CIK'])['rf_seq'].count().reset_index().groupby('Industry')['rf_seq'].mean().round()\n",
    "sum_ind['#IT'] = df.groupby(['Industry', 'ryear', 'CIK'])['Topic'].nunique().reset_index().groupby('Industry')['Topic'].mean().round()\n",
    "sum_ind['#TC'] = df.groupby(['Industry', 'ryear', 'CIK'])['Topic_H'].nunique().reset_index().groupby('Industry')['Topic_H'].mean().round()\n",
    "\n",
    "sum_ind.astype(int).to_latex('ind_tbl.tex')\n",
    "sum_year.astype(int).to_latex('yr_tbl.tex')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "topics_df['ryear'] = topics_df['report_dt'].dt.year\n",
    "\n",
    "df = topics_df.groupby(\"ryear\")[['Topic', 'Topic_H']].agg({'Topic': lambda l: set(l), 'Topic_H': lambda l: set(l)}).reset_index()\n",
    "df[\"shifted\"] = df['Topic'].shift(1)\n",
    "\n",
    "df.dropna(inplace=True)\n",
    "\n",
    "df[\"repeated\"] = df.apply(lambda r: r['Topic'].intersection(r['shifted']), axis=1)\n",
    "df[\"added\"] = df.apply(lambda r: r['Topic'].difference(r['shifted']), axis=1)\n",
    "df[\"removed\"] = df.apply(lambda r: r['shifted'].difference(r['Topic']), axis=1)\n",
    "df[\"reported_crnt\"] = df['Topic'].map(len)\n",
    "df[\"repeated\"] = df[\"repeated\"].map(len)\n",
    "df[\"added\"] = df[\"added\"].map(len)\n",
    "df[\"removed\"] = df[\"removed\"].map(len)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "# plt.style.use('ggplot')\n",
    "plt.rc('figure', autolayout=True)\n",
    "\n",
    "# Set the global font to be DejaVu Sans, size 10 (or any other sans-serif font of your choice!)\n",
    "plt.rc('font',**{'family':'sans-serif','sans-serif':['DejaVu Sans'],'size':12})\n",
    "# Set the font used for MathJax - more on this later\n",
    "plt.rc('mathtext',**{'default':'regular'})\n",
    "%config InlineBackend.figure_format = 'retina'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(3, 1, figsize=(12,10))\n",
    "ax[0].plot(year[('reported_crnt', 'mean')])\n",
    "ax[0].set_xlabel('Year')\n",
    "ax[0].set_ylabel('Reported risk topics')\n",
    "\n",
    "# plt.savefig(\"HReduction.png\", dpi=600)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.7.13 ('ETM_env')",
   "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.13"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "2e814d5f901f2e2f1812217256e7a2386c6b3dac449e52472436b21a420d1617"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}