Study2_3.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",
    "from tqdm.auto import tqdm\n",
    "import gc\n",
    "from sklearn.neighbors import NearestNeighbors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load BoardEX data\n",
    "compo = pd.read_csv(\"Data/Board-Composition.csv\", parse_dates=['AnnualReportDate']).drop(columns='Ticker').drop_duplicates()\n",
    "\n",
    "committees = pd.read_csv(\n",
    "    \"Data/BoardEx_Committees.csv\", parse_dates=['AnnualReportDate']\n",
    ").drop_duplicates().reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# If director is in a risk committee\n",
    "committees['RiskCommittee'] = committees['CommitteeName'].str.contains(r\"risk\", case=False).astype(int)\n",
    "\n",
    "# Only directors that are in risk committee (some directors are in multiple committees - to remove duplicates)\n",
    "Risk_committee = committees.loc[\n",
    "    committees['RiskCommittee']==1,\n",
    "    ['AnnualReportDate', 'RiskCommittee', 'BoardID', 'DirectorID']\n",
    "].drop_duplicates().reset_index(drop=True)\n",
    "\n",
    "compo = pd.merge(\n",
    "    left=compo,\n",
    "    right=Risk_committee,\n",
    "    on=['AnnualReportDate', 'BoardID', 'DirectorID'],\n",
    "    how='left'\n",
    ").fillna({'RiskCommittee': 0})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "compo['Year'] = pd.to_datetime(compo['AnnualReportDate']).dt.year\n",
    "\n",
    "compo['ED'] = (compo['NED']=='No').astype(int)\n",
    "\n",
    "# Calculate number of DirectorID shared with other BoardID per year\n",
    "link_df = pd.merge(\n",
    "    left=compo[['BoardID', 'DirectorID', 'Year', 'CIKCode', 'ED', 'RiskCommittee']],\n",
    "    right=compo[['DirectorID', 'Year', 'BoardID', 'CIKCode', 'ED', 'RiskCommittee']],\n",
    "    on=['DirectorID', 'Year'],\n",
    "    how='outer',\n",
    "    suffixes=[\"\", \"_lnkd\"]\n",
    ")\n",
    "\n",
    "# if the shared Dir is ED or RiskCommittee in either one of linked firms\n",
    "link_df['ED'] = link_df[['ED', 'ED_lnkd']].max(axis=1)\n",
    "link_df['RiskCommittee'] = link_df[['RiskCommittee', 'RiskCommittee_lnkd']].max(axis=1)\n",
    "\n",
    "link_df.drop(columns=['ED_lnkd', 'RiskCommittee_lnkd'], inplace=True)\n",
    "\n",
    "link_df = link_df[link_df['BoardID']!=link_df['BoardID_lnkd']]\n",
    "\n",
    "link_df = link_df.dropna(subset=['CIKCode', 'CIKCode_lnkd']).drop_duplicates().reset_index(drop=True)\n",
    "\n",
    "# Link age\n",
    "link_df = link_df.sort_values(['BoardID', 'BoardID_lnkd', 'Year']).reset_index(drop=True)\n",
    "link_df['LinkTime'] = link_df.drop_duplicates(subset=['BoardID', 'BoardID_lnkd', 'Year']).groupby(['BoardID', 'BoardID_lnkd'])['Year'].cumcount()+1\n",
    "link_df['LinkTime'] = link_df['LinkTime'].ffill()\n",
    "\n",
    "# List of linked firms\n",
    "lnkdCIKs = link_df.groupby(['CIKCode'])['CIKCode_lnkd'].agg(lambda x: list(set(x)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Matching"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['CIK', 'report_dt', 'filing_dt', 'FF', 'rf_length', 'SIC3',\n",
       "       'Specificity', 'Pa', 'Pr', 'Fu', 'Sentiment', 'FOG', 'Delta_length',\n",
       "       'reported', 'repeated', 'added', 'removed', 'rfGap', 'fyear', 'ryear',\n",
       "       'added+1', 'removed+1', 'COUNT_WEAK', 'Big4', 'TimeInCo', 'NoQuals',\n",
       "       'GenderRatio', 'NationalityMix', 'NumberDirectors', 'NetworkSize',\n",
       "       'TotCurrBrd', 'Age', 'ShrdDir', 'LnkdFirm', 'ShrdED', 'ShrdRiskDir',\n",
       "       'FinLink', 'Degree', 'LinkTime', 'D_NumberDirectors', 'D_ShrdDir',\n",
       "       'D_LnkdFirm', 'D_ShrdED', 'D_ShrdRiskDir', 'D_Degree', 'Volatility+30',\n",
       "       'Volatility_30', 'Volatility+60', 'Volatility_120', 'SHRTURN',\n",
       "       'Beta_126', 'NUMBEROFANALYSTS', 'rmonth', 'cik', 'DtA', 'ROE', 'NPM',\n",
       "       'mkvalt', 'logMC', 'at', 'logTA', 'INTtA', 'Current', 'TobinQ', 'BtM',\n",
       "       'RDxopr', 'ProprietaryCost', 'IndVol_', 'IndVol+', 'InstOwnership'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "firm_data = pd.read_csv('Data/Study2_data1_V2.csv', parse_dates=['report_dt', 'filing_dt'])\n",
    "firm_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "firm_data.dropna(subset=[\n",
    "    'added', 'removed', 'Delta_length', 'Beta_126', 'Volatility_120', 'logTA', 'ROE', 'DtA', 'Current', 'BtM',\n",
    "    'rfGap', 'GenderRatio', 'NUMBEROFANALYSTS', 'NumberDirectors', 'RDxopr'\n",
    "], inplace=True)\n",
    "\n",
    "firm_data = firm_data[firm_data.groupby('CIK')['ryear'].transform('nunique')>1].reset_index(drop=True)\n",
    "\n",
    "match_cols = [\n",
    "    'Volatility_120', 'Beta_126', 'logTA', 'ROE', 'DtA', 'Current', 'RDxopr', 'BtM', 'rfGap', 'COUNT_WEAK', 'Big4',\n",
    "    'GenderRatio', 'NUMBEROFANALYSTS', 'NumberDirectors', 'IndVol_'\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(45400, 70)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "firm_data.fillna(dict((c,0) for c in match_cols), inplace=True)\n",
    "\n",
    "# firm_data.fillna({'NPM': firm_data.groupby('CIK')['NPM'].transform('mean')}, inplace=True)\n",
    "\n",
    "firm_data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Normalize variable for matching\n",
    "firm_data[match_cols] = (firm_data[match_cols] - firm_data[match_cols].mean())/firm_data[match_cols].std()\n",
    "\n",
    "# Winsorize outliers\n",
    "firm_data[match_cols] = firm_data[match_cols].map(lambda x: (3 if x>3 else x) if x>-3 else -3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "39b0078299be4a559f4d15829002b94e",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "  0%|          | 0/18 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# For each year and CIK find the best match\n",
    "knn = NearestNeighbors(n_neighbors=1)\n",
    "\n",
    "for yr in tqdm(firm_data['ryear'].unique()):\n",
    "    # Firms in fiscal year yr\n",
    "    df1 = firm_data[firm_data['ryear']==yr]\n",
    "    \n",
    "    # Find a match for each cik in year yr\n",
    "    for cik in df1['CIK'].unique():\n",
    "        try:\n",
    "            # all firms except cik and those it is connected with in the whole sample\n",
    "            knn_data = df1.loc[~df1['CIK'].isin([cik, *lnkdCIKs[cik]]), match_cols]\n",
    "            knn.fit(knn_data)\n",
    "            \n",
    "            # Find KNN for cik in the pool of knn_data\n",
    "            neighs = knn.kneighbors(df1.loc[df1['CIK']==cik, match_cols], return_distance=False)\n",
    "            matched_cik = df1.loc[knn_data.index[neighs.reshape(1,-1)[0]]]['CIK']\n",
    "\n",
    "            firm_data.loc[(firm_data[\"CIK\"]==cik)&(firm_data['ryear']==yr), 'matched_cik'] = matched_cik.values\n",
    "\n",
    "        except:\n",
    "            continue"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Shared RFs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "H: Firm A discloses (new) RF in year t, it connects with B in t+1 that has disclosed RF in year t"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(3245397, 24)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Index(['Topic', 'Score', 'Topic_H', 'Score_H', 'CIK', 'report_dt', 'filing_dt',\n",
       "       'rf_seq', 'ticker', 'filerCIK', 'rf_length', 'NERs', 'Pa', 'Pr', 'Fu',\n",
       "       'Sentiment', 'FOG', 'SIC', 'FF', 'ryear', 'fyear', 'rf_seq_count',\n",
       "       'Specificity', 'SIC3'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load RF data \n",
    "topics_df = pd.read_csv(\"Data/RDdf_T2V5.csv\", parse_dates=['report_dt', 'filing_dt'])\n",
    "\n",
    "topics_df['NERs'] = topics_df['NERs'].str.replace(pat=\" \", repl=\"\").str.findall(pat=r\"'(.*?)'\")\n",
    "\n",
    "NE_labels = ['PERSON', 'NORP' 'ORG', 'GPE', 'LOC', 'PRODUCT', 'EVENT', 'LAW', 'DATE', 'TIME', 'PERCENT', 'MONEY', 'QUANTITY']\n",
    "topics_df['Specificity'] = topics_df['NERs'].apply(lambda NERs: len([ne for ne in NERs if ne in NE_labels]))\n",
    "\n",
    "topics_df['SIC3'] = topics_df['SIC'].map(lambda x: f\"{int(x):04d}\"[:3])\n",
    "\n",
    "print(topics_df.shape)\n",
    "topics_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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\", \"FF\"], \n",
    "    columns='Topic_H', values='Score'\n",
    ").notna().astype(int).reset_index()\n",
    "\n",
    "disc_df.sort_values(['CIK', 'filing_dt', 'report_dt'], inplace=True)\n",
    "\n",
    "disc_df['ryear'] = disc_df[\"report_dt\"].dt.year\n",
    "\n",
    "# Drop firm-year observations with more than 1 report in one fiscal year\n",
    "disc_df.drop_duplicates(subset=disc_df.columns.difference([\"filing_dt\", \"report_dt\"]), keep=\"first\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Firms with at least 4 years of observation for DID [-2, 2]\n",
    "disc_df = disc_df[disc_df.groupby('CIK')['ryear'].transform('nunique')>3]\n",
    "\n",
    "# Drop disclosure data with missing control variables\n",
    "disc_df = disc_df[disc_df['CIK'].isin(firm_data['CIK'].unique())].reset_index(drop=True)\n",
    "\n",
    "# # Firms that have links with other firms\n",
    "# disc_df = disc_df[disc_df['CIK'].isin(lnkdCIKs.index)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# duplicated ryears with report at the begining of the year\n",
    "disc_df['ryear_dupd'] = disc_df.duplicated(subset=['CIK', 'ryear'], keep='last')\n",
    "\n",
    "disc_df[\"ryear-1\"] = disc_df.groupby('CIK')['ryear'].shift(1)\n",
    "\n",
    "# change ryear if duplicated and there is a gap between two report years \n",
    "disc_df['ryear'] = disc_df[['ryear_dupd', 'ryear', 'ryear-1']].apply(\n",
    "    lambda x: x['ryear']-1 if x['ryear_dupd'] and x['ryear']-1>x['ryear-1'] else x['ryear'],\n",
    "    axis=1\n",
    ")\n",
    "\n",
    "disc_df = disc_df\\\n",
    "    .drop_duplicates(subset=['CIK', 'ryear'], keep='first')\\\n",
    "        .reset_index(drop=True).drop(columns=['ryear_dupd', 'ryear-1'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "260a15017a614552a18128b5dbeac646",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "  0%|          | 0/18 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Number of shared RFs\n",
    "Years = disc_df['ryear'].unique()\n",
    "\n",
    "shared_disc_list = []\n",
    "\n",
    "for yr in tqdm(Years):\n",
    "    df1 = disc_df[disc_df['ryear']==yr].copy()\n",
    "\n",
    "    # Caculate the number of shared RFs as the matrix multiplication of disclosure matrix by itself\n",
    "    # Only keep the upper triangle of product\n",
    "    # Add 1 to product values so only elements below diagonal is zero\n",
    "    a = df1.loc[:, range(105)].to_numpy()\n",
    "    df2 = pd.DataFrame(\n",
    "        np.triu(np.matmul(a, a.T)+1, k=1), index=df1['CIK'], columns=df1[['CIK', \"report_dt\"]]\n",
    "    ).reset_index()\n",
    "\n",
    "    df2[\"report_dt\"] = df1[\"report_dt\"].values\n",
    "\n",
    "    df3 = pd.melt(df2, id_vars=['CIK', \"report_dt\"], value_name='SharedRF')\n",
    "\n",
    "    df3 = df3[df3['SharedRF']>0]\n",
    "\n",
    "    df3['ryear'] = yr\n",
    "\n",
    "    shared_disc_list.append(df3)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "shared_disc_df = pd.concat(shared_disc_list)\n",
    "\n",
    "# Subtract the 1 added to np.matmul\n",
    "shared_disc_df['SharedRF'] = shared_disc_df['SharedRF'] - 1\n",
    "\n",
    "shared_disc_df['CIK_pair'] = shared_disc_df['variable'].apply(lambda x: x[0])\n",
    "shared_disc_df['report_dt_pair'] = shared_disc_df['variable'].apply(lambda x: x[1])\n",
    "\n",
    "shared_disc_df.drop(columns='variable', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del df1\n",
    "del df2\n",
    "del df3\n",
    "del shared_disc_list\n",
    "\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Number of shared Dir between 2 individual firms in a specific year\n",
    "shared_disc_df['NoSharedDir'] = pd.merge(\n",
    "    left=shared_disc_df,\n",
    "    right=link_df.groupby(['CIKCode', 'Year', 'CIKCode_lnkd'])['DirectorID'].nunique(),\n",
    "    left_on=['CIK', 'ryear', 'CIK_pair'],\n",
    "    right_index=True,\n",
    "    how='left'\n",
    ")['DirectorID']\n",
    "\n",
    "# Check if CIK pairs are linked\n",
    "shared_disc_df['Linked'] = shared_disc_df['NoSharedDir'].notna().astype(int)\n",
    "\n",
    "shared_disc_df = shared_disc_df[shared_disc_df['CIK']!=shared_disc_df['CIK_pair']]\n",
    "\n",
    "shared_disc_df['Treatment'] = (shared_disc_df.groupby(['CIK', 'CIK_pair'])['Linked'].transform('max')>0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# For each pair of CIKs in the treatment group, find the mathched CIK\n",
    "df1 = shared_disc_df.loc[shared_disc_df['Treatment'], ['CIK', 'CIK_pair', 'ryear']].copy()\n",
    "df2 = firm_data[['CIK', 'ryear', 'matched_cik']].dropna().copy()\n",
    "\n",
    "# matched CIK for CIK\n",
    "df3 = pd.merge(\n",
    "    left=df1,\n",
    "    right=df2,\n",
    "    left_on=['CIK', 'ryear'],\n",
    "    right_on=['CIK', 'ryear'],\n",
    "    how='left'\n",
    ")\n",
    "\n",
    "# matched CIK for CIK_pair\n",
    "df3 = pd.merge(\n",
    "    left=df3,\n",
    "    right=df2,\n",
    "    left_on=['CIK_pair', 'ryear'],\n",
    "    right_on=['CIK', 'ryear'],\n",
    "    how='left',\n",
    "    suffixes=['', '_2']\n",
    ").drop(columns='CIK_2')\n",
    "\n",
    "\n",
    "# Check if the pair of matched CIKs are actually linked in the sample\n",
    "df3['match_linked'] = pd.merge(\n",
    "    left=df3,\n",
    "    right=link_df.groupby(['CIKCode', 'Year', 'CIKCode_lnkd'])['DirectorID'].nunique(),\n",
    "    left_on=['matched_cik', 'ryear', 'matched_cik_2'],\n",
    "    right_on=['CIKCode', 'Year', 'CIKCode_lnkd'],\n",
    "    how='left'\n",
    ")['DirectorID'].notna()\n",
    "\n",
    "# Keep matched CIK pairs that are not linked in the sample\n",
    "df3 = df3[~df3['match_linked']].dropna()\n",
    "\n",
    "# Drop pairs that cannot be matched (to reduce size of shared_disc_df)\n",
    "shared_disc_df = shared_disc_df[\n",
    "    (shared_disc_df['Treatment'])\n",
    "    |((shared_disc_df['CIK'].isin(df3['matched_cik']))&(shared_disc_df['CIK_pair'].isin(df3['matched_cik_2'])))\n",
    "    |((shared_disc_df['CIK'].isin(df3['matched_cik_2']))&(shared_disc_df['CIK_pair'].isin(df3['matched_cik'])))]\n",
    "\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# From shared_disc_df, only keep obs that are either in treatment or matched pairs\n",
    "# If pair of CIK and CIK_pair is a matched CIK pair \n",
    "# df3['match_linked'] is all False. notna()=True means matched but not linked\n",
    "shared_disc_df['match_linked'] = pd.merge(\n",
    "    left=shared_disc_df,\n",
    "    right=df3[['ryear', 'matched_cik', 'matched_cik_2', 'match_linked']].astype(int),\n",
    "    left_on=['CIK', 'CIK_pair', 'ryear'],\n",
    "    right_on=['matched_cik', 'matched_cik_2', 'ryear'],\n",
    "    how='left'\n",
    ")['match_linked'].notna()\n",
    "\n",
    "shared_disc_df['match_linked_2'] = pd.merge(\n",
    "    left=shared_disc_df,\n",
    "    right=df3[['ryear', 'matched_cik', 'matched_cik_2', 'match_linked']],\n",
    "    left_on=['CIK', 'CIK_pair', 'ryear'],\n",
    "    right_on=['matched_cik_2', 'matched_cik', 'ryear'],\n",
    "    how='left',\n",
    "    suffixes=['', '_2']\n",
    ")['match_linked_2'].notna()\n",
    "\n",
    "refined_shared_disc_df = shared_disc_df[\n",
    "    (shared_disc_df['Treatment'])\n",
    "    |(shared_disc_df['match_linked'])\n",
    "    |(shared_disc_df['match_linked_2'])\n",
    "].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# If the shared director is ED or Risk Committee in any of the linked firms\n",
    "refined_shared_disc_df = pd.merge(\n",
    "    left=refined_shared_disc_df,\n",
    "    right=link_df.groupby(['CIKCode', 'CIKCode_lnkd', 'Year'])[['ED', 'RiskCommittee', 'LinkTime']].max(),\n",
    "    left_on=['CIK', 'CIK_pair', 'ryear'],\n",
    "    right_index=True,\n",
    "    how='left'\n",
    ")\n",
    "\n",
    "refined_shared_disc_df.drop(columns=['match_linked', 'match_linked_2'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(336081, 12)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "refined_shared_disc_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Linked\n",
       "0    258555\n",
       "1     77526\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "refined_shared_disc_df['Linked'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Treatment\n",
       "True     201118\n",
       "False    134963\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "refined_shared_disc_df['Treatment'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del shared_disc_df\n",
    "del firm_data\n",
    "\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "refined_shared_disc_df.sort_values(['CIK', 'CIK_pair', 'ryear'], inplace=True)\n",
    "\n",
    "# Year when the event happens for treated pairs\n",
    "refined_shared_disc_df['eventX'] = refined_shared_disc_df[refined_shared_disc_df['LinkTime']==1]['ryear']\n",
    "refined_shared_disc_df['eventX'] = refined_shared_disc_df.groupby(['CIK', 'CIK_pair'])['eventX'].bfill()\n",
    "\n",
    "# If firms are disconected after a couple of years, treat as control group\n",
    "refined_shared_disc_df['eventX'] = refined_shared_disc_df['eventX'].fillna(refined_shared_disc_df[refined_shared_disc_df['Linked']==1].groupby(['CIK', 'CIK_pair'])['eventX'].ffill())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merge data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(45400, 60)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "firm_data = pd.read_csv('Data/Study2_data1_V2.csv', parse_dates=['report_dt', 'filing_dt'])\n",
    "firm_data.dropna(subset=[\n",
    "    'added', 'removed', 'Delta_length', 'Beta_126', 'Volatility_120', 'logTA', 'ROE', 'DtA', 'Current', 'BtM',\n",
    "    'rfGap', 'GenderRatio', 'NUMBEROFANALYSTS', 'NumberDirectors', 'RDxopr'\n",
    "], inplace=True)\n",
    "\n",
    "firm_data = firm_data[firm_data.groupby('CIK')['ryear'].transform('nunique')>1].reset_index(drop=True)\n",
    "\n",
    "firm_data.drop(\n",
    "    columns=['ryear', 'LinkTime', 'Degree', 'D_Degree', 'rmonth', 'cik', 'mkvalt', 'logMC', 'ProprietaryCost', 'IndVol+'],\n",
    "    inplace=True\n",
    ")\n",
    "\n",
    "firm_data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "Study2_data3 = pd.merge(\n",
    "    left=refined_shared_disc_df,\n",
    "    right=firm_data,\n",
    "    on=[\"CIK\", \"report_dt\"],\n",
    "    how=\"left\"\n",
    ")\n",
    "\n",
    "Study2_data3 = pd.merge(\n",
    "    left=Study2_data3,\n",
    "    right=firm_data,\n",
    "    left_on=[\"CIK_pair\", \"report_dt_pair\"],\n",
    "    right_on=[\"CIK\", \"report_dt\"],\n",
    "    how=\"left\",\n",
    "    suffixes=['', '_2']\n",
    ").drop(columns=['CIK_2', 'report_dt_2'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['added', 'removed', 'Delta_length', 'Beta_126', 'Volatility_120', 'logTA', 'ROE', 'DtA', 'Current', 'BtM',\n",
    "        'rfGap', 'GenderRatio', 'NUMBEROFANALYSTS', 'NumberDirectors', 'RDxopr']\n",
    "\n",
    "Study2_data3.dropna(subset=cols, inplace=True)\n",
    "Study2_data3.dropna(subset=[f\"{c}_2\" for c in cols], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['CIK', 'report_dt', 'SharedRF', 'ryear', 'CIK_pair', 'report_dt_pair',\n",
       "       'NoSharedDir', 'Linked', 'Treatment', 'ED',\n",
       "       ...\n",
       "       'at_2', 'logTA_2', 'INTtA_2', 'Current_2', 'TobinQ_2', 'BtM_2',\n",
       "       'RDxopr_2', 'IndVol__2', 'InstOwnership_2', 'CIKpair_ID'],\n",
       "      dtype='object', length=130)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Study2_data3['CIKpair_ID'] = Study2_data3[['CIK', 'CIK_pair']].astype(str).apply(lambda x: '-'.join(x), axis=1)\n",
    "Study2_data3.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(267235, 130)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Study2_data3.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "Study2_data3.to_csv('Data/Study2_data3_V2.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Study2_data3 = pd.merge(\n",
    "    left=shared_disc_df,\n",
    "    right=agg_tops,\n",
    "    on=[\"CIK\", \"report_dt\"],\n",
    "    how=\"left\"\n",
    ")\n",
    "\n",
    "Study2_data3 = pd.merge(\n",
    "    left=Study2_data3,\n",
    "    right=agg_tops,\n",
    "    left_on=[\"CIK_pair\", \"report_dt_pair\"],\n",
    "    right_on=[\"CIK\", \"report_dt\"],\n",
    "    how=\"left\",\n",
    "    suffixes=['', '_2']\n",
    ").drop(columns=['CIK_2', 'report_dt_2'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Study2_data3 = pd.merge(\n",
    "    left=shared_disc_df,\n",
    "    right=agg_tops,\n",
    "    on=[\"CIK\", \"report_dt\"],\n",
    "    how=\"left\"\n",
    ")\n",
    "\n",
    "Study2_data3 = pd.merge(\n",
    "    left=Study2_data3,\n",
    "    right=agg_tops,\n",
    "    left_on=[\"CIK_pair\", \"report_dt_pair\"],\n",
    "    right_on=[\"CIK\", \"report_dt\"],\n",
    "    how=\"left\",\n",
    "    suffixes=['', '_2']\n",
    ").drop(columns=['CIK_2', 'report_dt_2'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['Age', 'GenderRatio', 'NationalityMix']\n",
    "\n",
    "# Fill missing values with previous year (if any)\n",
    "compo_sum[cols] = compo_sum.groupby('CIKCode')[cols].ffill(limit=1)\n",
    "\n",
    "# Fill missing values with next year (if any)\n",
    "compo_sum[cols] = compo_sum.groupby('CIKCode')[cols].bfill(limit=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Study2_data3 = pd.merge(\n",
    "    left=Study2_data3,\n",
    "    right=compo_sum,\n",
    "    left_on=['CIK', 'ryear'],\n",
    "    right_on=['CIKCode', 'Year'],\n",
    "    how='left'\n",
    ").drop(columns=['CIKCode', 'Year'])\n",
    "\n",
    "Study2_data3 = pd.merge(\n",
    "    left=Study2_data3,\n",
    "    right=compo_sum,\n",
    "    left_on=['CIK', 'ryear'],\n",
    "    right_on=['CIKCode', 'Year'],\n",
    "    how='left',\n",
    "    suffixes=['', '_2']\n",
    ").drop(columns=['CIKCode', 'Year'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Study2_data3 = pd.merge(\n",
    "    left=Study2_data3,\n",
    "    right=std_returns['Volatility_120'],\n",
    "    left_on=[\"CIK\", \"filing_dt\"],\n",
    "    right_index=True,\n",
    "    how=\"left\"\n",
    ")\n",
    "\n",
    "Study2_data3 = pd.merge(\n",
    "    left=Study2_data3,\n",
    "    right=MA_BA['Spread_120'],\n",
    "    left_on=[\"CIK\", \"filing_dt\"],\n",
    "    right_index=True,\n",
    "    how=\"left\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Study2_data3 = pd.merge(\n",
    "    left=Study2_data3,\n",
    "    right=std_returns['Volatility_120'],\n",
    "    left_on=[\"CIK_pair\", \"filing_dt_2\"],\n",
    "    right_index=True,\n",
    "    how=\"left\",\n",
    "    suffixes=['', '_2']\n",
    ")\n",
    "\n",
    "Study2_data3 = pd.merge(\n",
    "    left=Study2_data3,\n",
    "    right=MA_BA['Spread_120'],\n",
    "    left_on=[\"CIK_pair\", \"filing_dt_2\"],\n",
    "    right_index=True,\n",
    "    how=\"left\",\n",
    "    suffixes=['', '_2']\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Study2_data3 = pd.merge(\n",
    "    left=Study2_data3,\n",
    "    right=Beta['Beta_120'],\n",
    "    left_on=[\"CIK\", \"filing_dt\"],\n",
    "    right_index=True,\n",
    "    how=\"left\"\n",
    ")\n",
    "\n",
    "Study2_data3 = pd.merge(\n",
    "    left=Study2_data3,\n",
    "    right=Beta['Beta_120'],\n",
    "    left_on=[\"CIK_pair\", \"filing_dt_2\"],\n",
    "    right_index=True,\n",
    "    how=\"left\",\n",
    "    suffixes=['', '_2']\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Study2_data3[\"ryear\"] = Study2_data3['report_dt'].dt.year\n",
    "Study2_data3[\"rmonth\"] = Study2_data3['report_dt'].dt.month\n",
    "Study2_data3[\"rmonth_pair\"] = Study2_data3['report_dt_pair'].dt.month\n",
    "\n",
    "financials = financials[[\n",
    "    'cik', 'ryear', 'rmonth', 'DtA', 'ROE', 'NPM', 'mkvalt',\n",
    "    'at', 'INTtA', 'Current', 'BtM', 'RDxopr'\n",
    "]]\n",
    "\n",
    "Study2_data3 = pd.merge(\n",
    "    left=Study2_data3,\n",
    "    right=financials,\n",
    "    left_on=[\"CIK\", \"ryear\", \"rmonth\"],\n",
    "    right_on=[\"cik\", \"ryear\", \"rmonth\"],\n",
    "    how=\"left\"\n",
    ").drop(columns=['cik'])\n",
    "\n",
    "Study2_data3 = pd.merge(\n",
    "    left=Study2_data3,\n",
    "    right=financials,\n",
    "    left_on=[\"CIK_pair\", \"ryear\", \"rmonth_pair\"],\n",
    "    right_on=[\"cik\", \"ryear\", \"rmonth\"],\n",
    "    how=\"left\",\n",
    "    suffixes=['', '_2']\n",
    ").drop(columns=['cik'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Owner['ryear'] = Owner['Date'].dt.year\n",
    "Owner['rmonth'] = Owner['Date'].dt.month\n",
    "\n",
    "Study2_data3 = pd.merge(\n",
    "    left=Study2_data3,\n",
    "    right=Owner,\n",
    "    left_on=[\"CIK\", \"ryear\", \"rmonth\"],\n",
    "    right_on=[\"Instrument\", \"ryear\", \"rmonth\"],\n",
    "    how=\"left\"\n",
    ").drop(columns=['Instrument', 'Date'])\n",
    "\n",
    "Study2_data3 = pd.merge(\n",
    "    left=Study2_data3,\n",
    "    right=Owner,\n",
    "    left_on=[\"CIK\", \"ryear\", \"rmonth_pair\"],\n",
    "    right_on=[\"Instrument\", \"ryear\", \"rmonth\"],\n",
    "    how=\"left\",\n",
    "    suffixes=['', '_2']\n",
    ").drop(columns=['Instrument', 'Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Study2_data3 = pd.merge(\n",
    "    left=Study2_data3,\n",
    "    right=Analyst_df[[\"CIK\", \"filing_dt\", 'NUMBEROFANALYSTS']],\n",
    "    on=[\"CIK\", \"filing_dt\"],\n",
    "    how=\"left\"\n",
    ")\n",
    "\n",
    "Study2_data3 = pd.merge(\n",
    "    left=Study2_data3,\n",
    "    right=Analyst_df[[\"CIK\", \"filing_dt\", 'NUMBEROFANALYSTS']],\n",
    "    left_on=[\"CIK_pair\", \"filing_dt_2\"],\n",
    "    right_on=[\"CIK\", \"filing_dt\"],\n",
    "    how=\"left\",\n",
    "    suffixes=['', '_2']\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Study2_data3.drop(columns=['rmonth_2', 'rmonth', 'rmonth_pair', 'CIK_2',\n",
    "                           'report_dt', 'report_dt_pair', \n",
    "                           'filing_dt', 'filing_dt_2'], inplace=True)\n",
    "\n",
    "# Study2_data3 = Study2_data3.loc[:, ~Study2_data3.columns.duplicated()]\n",
    "Study2_data3 = Study2_data3.sort_values(['CIK', 'CIK_pair', 'ryear']).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "shared_disc_df[shared_disc_df.groupby(['CIK', 'CIK_pair'])['NoSharedDir'].transform('sum')>2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Study2_data3[Study2_data3.groupby(['CIK', 'CIK_pair'])['Linked'].transform('sum')>1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Study2_data3.groupby('CIKpair_ID')['Linked'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Determine the risk topics disclosed by every CIK pairs in avery year\n",
    "Years = disc_df['ryear'].unique()\n",
    "\n",
    "# Every element belongs to a year\n",
    "shrd_disc_list = []\n",
    "\n",
    "for yr in tqdm(Years):\n",
    "    df1 = disc_df[disc_df['ryear']==yr]\n",
    "\n",
    "    a = df1.loc[:, range(0,95)].to_numpy()\n",
    "\n",
    "    idx = df1[\"CIK\"].nunique()\n",
    "\n",
    "    df2 = pd.DataFrame(\n",
    "        [[np.where((a[i,:]==1)&(a[j,:]==1))[0] for j in range(idx)[:i+1]] for i in range(idx)], \n",
    "        columns=df1[\"CIK\"], index=df1[\"CIK\"]\n",
    "    )\n",
    "    \n",
    "    df2.columns.name = 'CIK_pair'\n",
    "    df2[\"report_dt\"] = df1[\"report_dt\"].values\n",
    "\n",
    "    shrd_disc_list.append(df2)\n",
    "    \n",
    "shared_disc_df = pd.concat([pd.melt(df.reset_index(), id_vars=[\"CIK\", \"report_dt\"], value_name='SharedRF').dropna() for df in shrd_disc_list])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "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.10.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}