PRC - Market Data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f4dba3ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import statsmodels.api as sm"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a28ed51a",
   "metadata": {},
   "source": [
    "# Daily - data structure adjustment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "e7a179cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "price_file_path = 'Data/PRC/日度数据-股票价格.xlsx'\n",
    "volume_file_path = 'Data/PRC/日度数据-股票交易量.xlsx'\n",
    "return_file_path = 'Data/PRC/日度数据-股票回报率.xlsx'\n",
    "\n",
    "price_df = pd.read_excel(price_file_path, sheet_name=0)\n",
    "volume_df = pd.read_excel(volume_file_path, sheet_name=0)\n",
    "return_df = pd.read_excel(return_file_path, sheet_name=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "3e2d9c16",
   "metadata": {},
   "outputs": [],
   "source": [
    "def transform_to_panel(df, value_name):\n",
    "    # Extract company names and stock codes\n",
    "    company_names = df.iloc[0, 1:].values\n",
    "    stock_codes = df.iloc[1, 1:].values\n",
    "    date_column = df.iloc[2:, 0].values\n",
    "\n",
    "    # Prepare a list to store the long format data\n",
    "    long_data = []\n",
    "\n",
    "    # Loop over the columns (excluding the first date column)\n",
    "    for i, (name, code) in enumerate(zip(company_names, stock_codes), start=1):\n",
    "        # Extract the values for the company\n",
    "        values = df.iloc[2:, i].values\n",
    "        # Combine date, company name, stock code, and values\n",
    "        company_data = list(zip([name] * len(date_column), [code] * len(date_column), date_column, values))\n",
    "        long_data.extend(company_data)\n",
    "\n",
    "    # Convert the list to a DataFrame\n",
    "    long_df = pd.DataFrame(long_data, columns=['公司名称', '股票代码', '日期', value_name])\n",
    "\n",
    "    # Convert the date column to datetime\n",
    "    long_df['日期'] = pd.to_datetime(long_df['日期'], errors='coerce')\n",
    "\n",
    "    # Drop rows with invalid dates\n",
    "    long_df.dropna(subset=['日期'], inplace=True)\n",
    "\n",
    "    return long_df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "1fecf40a",
   "metadata": {},
   "outputs": [],
   "source": [
    "price_panel = transform_to_panel(price_df, '股票价格')\n",
    "volume_panel = transform_to_panel(volume_df, '股票交易量')\n",
    "return_panel = transform_to_panel(return_df, '股票回报率')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "0f9f303d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              公司名称  股票代码         日期  股票价格\n",
      "0        000776.SZ  股票价格 2004-01-02  4.58\n",
      "1        000776.SZ  股票价格 2004-01-05  4.50\n",
      "2        000776.SZ  股票价格 2004-01-06  4.24\n",
      "3        000776.SZ  股票价格 2004-01-07  4.34\n",
      "4        000776.SZ  股票价格 2004-01-08  4.47\n",
      "...            ...   ...        ...   ...\n",
      "1457695  603993.SH  股票价格 2023-12-25  4.93\n",
      "1457696  603993.SH  股票价格 2023-12-26  4.92\n",
      "1457697  603993.SH  股票价格 2023-12-27  5.08\n",
      "1457698  603993.SH  股票价格 2023-12-28  5.16\n",
      "1457699  603993.SH  股票价格 2023-12-29  5.20\n",
      "\n",
      "[1457700 rows x 4 columns]\n"
     ]
    }
   ],
   "source": [
    "print(price_panel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "bcfce94b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              公司名称   股票代码         日期        股票交易量\n",
      "0        000776.SZ  股票交易量 2004-01-02     257179.0\n",
      "1        000776.SZ  股票交易量 2004-01-05     345250.0\n",
      "2        000776.SZ  股票交易量 2004-01-06     868450.0\n",
      "3        000776.SZ  股票交易量 2004-01-07     528968.0\n",
      "4        000776.SZ  股票交易量 2004-01-08     735885.0\n",
      "...            ...    ...        ...          ...\n",
      "1457695  603993.SH  股票交易量 2023-12-25   76115676.0\n",
      "1457696  603993.SH  股票交易量 2023-12-26   56692336.0\n",
      "1457697  603993.SH  股票交易量 2023-12-27  151966596.0\n",
      "1457698  603993.SH  股票交易量 2023-12-28  163415379.0\n",
      "1457699  603993.SH  股票交易量 2023-12-29   98730274.0\n",
      "\n",
      "[1457700 rows x 4 columns]\n"
     ]
    }
   ],
   "source": [
    "print(volume_panel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "284471c3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              公司名称   股票代码         日期     股票回报率\n",
      "0        000776.SZ  股票回报率 2004-01-02  1.777778\n",
      "1        000776.SZ  股票回报率 2004-01-05 -1.746725\n",
      "2        000776.SZ  股票回报率 2004-01-06 -5.777778\n",
      "3        000776.SZ  股票回报率 2004-01-07  2.358491\n",
      "4        000776.SZ  股票回报率 2004-01-08  2.995392\n",
      "...            ...    ...        ...       ...\n",
      "1457695  603993.SH  股票回报率 2023-12-25  0.612245\n",
      "1457696  603993.SH  股票回报率 2023-12-26 -0.202840\n",
      "1457697  603993.SH  股票回报率 2023-12-27  3.252033\n",
      "1457698  603993.SH  股票回报率 2023-12-28  1.574803\n",
      "1457699  603993.SH  股票回报率 2023-12-29  0.775194\n",
      "\n",
      "[1457700 rows x 4 columns]\n"
     ]
    }
   ],
   "source": [
    "print(return_panel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "704d2358",
   "metadata": {},
   "outputs": [],
   "source": [
    "price_panel.drop(columns=['股票代码'], inplace=True)\n",
    "volume_panel.drop(columns=['股票代码'], inplace=True)\n",
    "return_panel.drop(columns=['股票代码'], inplace=True)\n",
    "\n",
    "price_panel.rename(columns={'公司名称': '股票代码'}, inplace=True)\n",
    "volume_panel.rename(columns={'公司名称': '股票代码'}, inplace=True)\n",
    "return_panel.rename(columns={'公司名称': '股票代码'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "f53825ba",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Price Panel dtypes:\n",
      " 股票代码            object\n",
      "日期      datetime64[ns]\n",
      "股票价格           float64\n",
      "dtype: object\n",
      "Volume Panel dtypes:\n",
      " 股票代码             object\n",
      "日期       datetime64[ns]\n",
      "股票交易量           float64\n",
      "dtype: object\n",
      "Return Panel dtypes:\n",
      " 股票代码             object\n",
      "日期       datetime64[ns]\n",
      "股票回报率           float64\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "print(\"Price Panel dtypes:\\n\", price_panel.dtypes)\n",
    "print(\"Volume Panel dtypes:\\n\", volume_panel.dtypes)\n",
    "print(\"Return Panel dtypes:\\n\", return_panel.dtypes)\n",
    "\n",
    "price_panel['股票代码'] = price_panel['股票代码'].astype(str)\n",
    "volume_panel['股票代码'] = volume_panel['股票代码'].astype(str)\n",
    "return_panel['股票代码'] = return_panel['股票代码'].astype(str)\n",
    "price_panel['日期'] = pd.to_datetime(price_panel['日期'])\n",
    "volume_panel['日期'] = pd.to_datetime(volume_panel['日期'])\n",
    "return_panel['日期'] = pd.to_datetime(return_panel['日期'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "0e58c38b",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_panel = pd.merge(price_panel, volume_panel, on=['股票代码', '日期'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "c4625579",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_panel = pd.merge(merged_panel, return_panel, on=['股票代码', '日期'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "225e9d44",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              股票代码         日期  股票价格        股票交易量     股票回报率\n",
      "0        000776.SZ 2004-01-02  4.58     257179.0  1.777778\n",
      "1        000776.SZ 2004-01-05  4.50     345250.0 -1.746725\n",
      "2        000776.SZ 2004-01-06  4.24     868450.0 -5.777778\n",
      "3        000776.SZ 2004-01-07  4.34     528968.0  2.358491\n",
      "4        000776.SZ 2004-01-08  4.47     735885.0  2.995392\n",
      "...            ...        ...   ...          ...       ...\n",
      "1457695  603993.SH 2023-12-25  4.93   76115676.0  0.612245\n",
      "1457696  603993.SH 2023-12-26  4.92   56692336.0 -0.202840\n",
      "1457697  603993.SH 2023-12-27  5.08  151966596.0  3.252033\n",
      "1457698  603993.SH 2023-12-28  5.16  163415379.0  1.574803\n",
      "1457699  603993.SH 2023-12-29  5.20   98730274.0  0.775194\n",
      "\n",
      "[1457700 rows x 5 columns]\n"
     ]
    }
   ],
   "source": [
    "print(merged_panel)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3381084c",
   "metadata": {},
   "source": [
    "## Daily - Bid & Ask"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "86cdd72f",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path = 'Data/PRC/沪深300 ask price&bid price.xlsx'\n",
    "sheets = pd.read_excel(file_path, sheet_name=['bid price', 'ask price'])\n",
    "\n",
    "bid_df = sheets['bid price']\n",
    "ask_df = sheets['ask price']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "67e554e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "bid_panel = transform_to_panel(bid_df, 'bid price')\n",
    "ask_panel = transform_to_panel(ask_df, 'ask price')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "ab78906d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              股票代码         日期  bid price\n",
      "0        000776.SZ 2004-01-02       8.65\n",
      "1        000776.SZ 2004-01-05       8.98\n",
      "2        000776.SZ 2004-01-06       9.55\n",
      "3        000776.SZ 2004-01-07       9.53\n",
      "4        000776.SZ 2004-01-08       9.47\n",
      "...            ...        ...        ...\n",
      "1458293        NaN        NaT      55.00\n",
      "1458294        NaN        NaT      53.23\n",
      "1458295        NaN        NaT      52.29\n",
      "1458296        NaN        NaT      53.45\n",
      "1458297        NaN        NaT      53.50\n",
      "\n",
      "[1457700 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "print(bid_panel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "1966c264",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              股票代码         日期  ask price\n",
      "0        000776.SZ 2004-01-02       8.41\n",
      "1        000776.SZ 2004-01-05       8.60\n",
      "2        000776.SZ 2004-01-06       9.00\n",
      "3        000776.SZ 2004-01-07       9.25\n",
      "4        000776.SZ 2004-01-08       9.23\n",
      "...            ...        ...        ...\n",
      "1458293        NaN        NaT      52.85\n",
      "1458294        NaN        NaT      51.80\n",
      "1458295        NaN        NaT      51.18\n",
      "1458296        NaN        NaT      51.68\n",
      "1458297        NaN        NaT      52.88\n",
      "\n",
      "[1457700 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "print(ask_panel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "b95848cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "bid_panel.drop(columns=['股票代码'], inplace=True)\n",
    "ask_panel.drop(columns=['股票代码'], inplace=True)\n",
    "\n",
    "bid_panel.rename(columns={'公司名称': '股票代码'}, inplace=True)\n",
    "ask_panel.rename(columns={'公司名称': '股票代码'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "fbc70383",
   "metadata": {},
   "outputs": [],
   "source": [
    "bid_panel['股票代码'] = price_panel['股票代码'].astype(str)\n",
    "ask_panel['股票代码'] = volume_panel['股票代码'].astype(str)\n",
    "bid_panel['日期'] = pd.to_datetime(price_panel['日期'])\n",
    "ask_panel['日期'] = pd.to_datetime(volume_panel['日期'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "bf9dab1c",
   "metadata": {},
   "outputs": [],
   "source": [
    "bid_ask_merged_panel = pd.merge(bid_panel, ask_panel, on=['股票代码', '日期'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "f5a9bc88",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              股票代码         日期  bid price  ask price\n",
      "0        000776.SZ 2004-01-02       8.65       8.41\n",
      "1        000776.SZ 2004-01-05       8.98       8.60\n",
      "2        000776.SZ 2004-01-06       9.55       9.00\n",
      "3        000776.SZ 2004-01-07       9.53       9.25\n",
      "4        000776.SZ 2004-01-08       9.47       9.23\n",
      "...            ...        ...        ...        ...\n",
      "1814701        NaN        NaT      53.50      52.85\n",
      "1814702        NaN        NaT      53.50      51.80\n",
      "1814703        NaN        NaT      53.50      51.18\n",
      "1814704        NaN        NaT      53.50      51.68\n",
      "1814705        NaN        NaT      53.50      52.88\n",
      "\n",
      "[1814706 rows x 4 columns]\n"
     ]
    }
   ],
   "source": [
    "print(bid_ask_merged_panel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "4e5278d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_panel = pd.merge(merged_panel, bid_ask_merged_panel, on=['股票代码', '日期'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "cfa023e4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              股票代码         日期  股票价格        股票交易量     股票回报率  bid price  \\\n",
      "0        000776.SZ 2004-01-02  4.58     257179.0  1.777778       8.65   \n",
      "1        000776.SZ 2004-01-05  4.50     345250.0 -1.746725       8.98   \n",
      "2        000776.SZ 2004-01-06  4.24     868450.0 -5.777778       9.55   \n",
      "3        000776.SZ 2004-01-07  4.34     528968.0  2.358491       9.53   \n",
      "4        000776.SZ 2004-01-08  4.47     735885.0  2.995392       9.47   \n",
      "...            ...        ...   ...          ...       ...        ...   \n",
      "1457097  603993.SH 2023-12-25  4.93   76115676.0  0.612245      58.00   \n",
      "1457098  603993.SH 2023-12-26  4.92   56692336.0 -0.202840      57.36   \n",
      "1457099  603993.SH 2023-12-27  5.08  151966596.0  3.252033      57.74   \n",
      "1457100  603993.SH 2023-12-28  5.16  163415379.0  1.574803      57.25   \n",
      "1457101  603993.SH 2023-12-29  5.20   98730274.0  0.775194      56.15   \n",
      "\n",
      "         ask price  \n",
      "0             8.41  \n",
      "1             8.60  \n",
      "2             9.00  \n",
      "3             9.25  \n",
      "4             9.23  \n",
      "...            ...  \n",
      "1457097      56.69  \n",
      "1457098      55.68  \n",
      "1457099      56.41  \n",
      "1457100      55.95  \n",
      "1457101      55.05  \n",
      "\n",
      "[1457102 rows x 7 columns]\n"
     ]
    }
   ],
   "source": [
    "print(merged_panel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "id": "0305fcbb",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_panel.rename(columns={'股票代码': 'TICKER'}, inplace=True)\n",
    "merged_panel.rename(columns={'日期': 'date'}, inplace=True)\n",
    "merged_panel.rename(columns={'股票价格': 'PRC'}, inplace=True)\n",
    "merged_panel.rename(columns={'股票交易量': 'VOL'}, inplace=True)\n",
    "merged_panel.rename(columns={'股票回报率': 'RET'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "id": "86d32a35",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "TICKER            0\n",
      "date              0\n",
      "PRC          483918\n",
      "VOL          485310\n",
      "RET          485109\n",
      "bid price         0\n",
      "ask price         0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "missing_values_after = merged_panel.isnull().sum()\n",
    "print(missing_values_after)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "1edc4d21",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      TICKER COMNAM CSRCIC-1 所属证监会行业名称\\n[行业级别] 门类行业 CSRCIC-2  \\\n",
      "0  000776.SZ   广发证券        J                    金融业      J67   \n",
      "1  000999.SZ   华润三九        C                    制造业      C27   \n",
      "2  002415.SZ   海康威视        C                    制造业      C39   \n",
      "3  002603.SZ   以岭药业        C                    制造业      C27   \n",
      "4  300308.SZ   中际旭创        C                    制造业      C39   \n",
      "\n",
      "  所属证监会行业代码\\n[行业级别] 大类行业 上市地点  上市板  \n",
      "0                 资本市场服务   深圳   主板  \n",
      "1                  医药制造业   深圳   主板  \n",
      "2       计算机、通信和其他电子设备制造业   深圳   主板  \n",
      "3                  医药制造业   深圳   主板  \n",
      "4       计算机、通信和其他电子设备制造业   深圳  创业板  \n"
     ]
    }
   ],
   "source": [
    "identification_file_path = 'Data/PRC/公司标识数据.xlsx'\n",
    "identification_df = pd.read_excel(identification_file_path, sheet_name=0)\n",
    "\n",
    "print(identification_df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "id": "1a423725",
   "metadata": {},
   "outputs": [],
   "source": [
    "identification_df = identification_df[['TICKER', 'COMNAM', 'CSRCIC-1', 'CSRCIC-2']]\n",
    "merged_panel = pd.merge(merged_panel, identification_df, left_on='TICKER', right_on='TICKER', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "abb9a6ea",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "            TICKER       date   PRC          VOL       RET  bid price  \\\n",
      "0        000776.SZ 2004-01-02  4.58     257179.0  1.777778       8.65   \n",
      "1        000776.SZ 2004-01-05  4.50     345250.0 -1.746725       8.98   \n",
      "2        000776.SZ 2004-01-06  4.24     868450.0 -5.777778       9.55   \n",
      "3        000776.SZ 2004-01-07  4.34     528968.0  2.358491       9.53   \n",
      "4        000776.SZ 2004-01-08  4.47     735885.0  2.995392       9.47   \n",
      "...            ...        ...   ...          ...       ...        ...   \n",
      "1457097  603993.SH 2023-12-25  4.93   76115676.0  0.612245      58.00   \n",
      "1457098  603993.SH 2023-12-26  4.92   56692336.0 -0.202840      57.36   \n",
      "1457099  603993.SH 2023-12-27  5.08  151966596.0  3.252033      57.74   \n",
      "1457100  603993.SH 2023-12-28  5.16  163415379.0  1.574803      57.25   \n",
      "1457101  603993.SH 2023-12-29  5.20   98730274.0  0.775194      56.15   \n",
      "\n",
      "         ask price COMNAM CSRCIC-1 CSRCIC-2  \n",
      "0             8.41   广发证券        J      J67  \n",
      "1             8.60   广发证券        J      J67  \n",
      "2             9.00   广发证券        J      J67  \n",
      "3             9.25   广发证券        J      J67  \n",
      "4             9.23   广发证券        J      J67  \n",
      "...            ...    ...      ...      ...  \n",
      "1457097      56.69   洛阳钼业        B      B09  \n",
      "1457098      55.68   洛阳钼业        B      B09  \n",
      "1457099      56.41   洛阳钼业        B      B09  \n",
      "1457100      55.95   洛阳钼业        B      B09  \n",
      "1457101      55.05   洛阳钼业        B      B09  \n",
      "\n",
      "[1457102 rows x 10 columns]\n"
     ]
    }
   ],
   "source": [
    "print(merged_panel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "b7a45e09",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_panel['date'] = pd.to_datetime(merged_panel['date'])\n",
    "\n",
    "merged_panel['year'] = merged_panel['date'].dt.year\n",
    "merged_panel['month'] = merged_panel['date'].dt.month\n",
    "\n",
    "def fill_missing_values(df, column):\n",
    "    df[column] = df.groupby(['CSRCIC-2', 'year', 'month'])[column].transform(lambda x: x.fillna(x.median()))\n",
    "    return df\n",
    "## TICKER/COMNAM/CSRCIC-1/CSRCIC-2 are all used\n",
    "\n",
    "columns_to_fill = ['PRC', 'VOL', 'RET']\n",
    "\n",
    "for column in columns_to_fill:\n",
    "    merged_panel = fill_missing_values(merged_panel, column)\n",
    "\n",
    "merged_panel.drop(columns=['year', 'month'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "id": "4117e754",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "TICKER           0\n",
      "date             0\n",
      "PRC          15310\n",
      "VOL          15427\n",
      "RET          15310\n",
      "bid price        0\n",
      "ask price        0\n",
      "COMNAM           0\n",
      "CSRCIC-1         0\n",
      "CSRCIC-2         0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "missing_values_after = merged_panel.isnull().sum()\n",
    "print(missing_values_after)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "2ac97dae",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_panel.fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "bc9afed9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "TICKER       0\n",
      "date         0\n",
      "PRC          0\n",
      "VOL          0\n",
      "RET          0\n",
      "bid price    0\n",
      "ask price    0\n",
      "COMNAM       0\n",
      "CSRCIC-1     0\n",
      "CSRCIC-2     0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "missing_values_after = merged_panel.isnull().sum()\n",
    "print(missing_values_after)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "id": "5f6a4fc7",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_panel.rename(columns={'bid price': 'BID'}, inplace=True)\n",
    "merged_panel.rename(columns={'ask price': 'ASK'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "id": "da9c0ecb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        date     TICKER COMNAM CSRCIC-1 CSRCIC-2   PRC       VOL       RET  \\\n",
      "0 2004-01-02  000776.SZ   广发证券        J      J67  4.58  257179.0  1.777778   \n",
      "1 2004-01-05  000776.SZ   广发证券        J      J67  4.50  345250.0 -1.746725   \n",
      "2 2004-01-06  000776.SZ   广发证券        J      J67  4.24  868450.0 -5.777778   \n",
      "3 2004-01-07  000776.SZ   广发证券        J      J67  4.34  528968.0  2.358491   \n",
      "4 2004-01-08  000776.SZ   广发证券        J      J67  4.47  735885.0  2.995392   \n",
      "\n",
      "    BID   ASK  \n",
      "0  8.65  8.41  \n",
      "1  8.98  8.60  \n",
      "2  9.55  9.00  \n",
      "3  9.53  9.25  \n",
      "4  9.47  9.23  \n"
     ]
    }
   ],
   "source": [
    "desired_column_order = ['date', 'TICKER', 'COMNAM', 'CSRCIC-1', 'CSRCIC-2', 'PRC', 'VOL', 'RET', 'BID', 'ASK']\n",
    "merged_panel = merged_panel[desired_column_order]\n",
    "print(merged_panel.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "id": "4595fb0b",
   "metadata": {},
   "outputs": [],
   "source": [
    "output_file_path = 'Data/PRC/Daily1.csv'\n",
    "merged_panel.to_csv(output_file_path, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "577a1cd9",
   "metadata": {},
   "source": [
    "# Daily - Liquidity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "id": "a92d1a59",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_panel['date'] = pd.to_datetime(merged_panel['date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "id": "4cfa9a60",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_panel['DV'] = merged_panel['VOL'] * merged_panel['PRC']\n",
    "merged_panel['BAS'] = merged_panel['ASK'] - merged_panel['BID']\n",
    "merged_panel['year_month'] = merged_panel['date'].dt.to_period('M')\n",
    "monthly_dv = merged_panel.groupby(['TICKER', 'year_month'])['DV'].sum().reset_index()\n",
    "monthly_bas = merged_panel.groupby(['TICKER', 'year_month'])['BAS'].mean().reset_index()\n",
    "monthly_df = pd.merge(monthly_dv, monthly_bas, on=['TICKER', 'year_month'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "id": "5a2c52a5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      TICKER year_month            DV       BAS\n",
      "0  000001.SZ    2004-01  1.528854e+09 -0.030769\n",
      "1  000001.SZ    2004-02  4.379420e+09 -0.031000\n",
      "2  000001.SZ    2004-03  3.903233e+09 -0.022609\n",
      "3  000001.SZ    2004-04  1.848258e+09 -0.027273\n",
      "4  000001.SZ    2004-05  5.555516e+08 -0.029375\n"
     ]
    }
   ],
   "source": [
    "print(monthly_df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "id": "34298607",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "TICKER        0\n",
      "year_month    0\n",
      "DV            0\n",
      "BAS           0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "missing_values = monthly_df.isnull().sum()\n",
    "print(missing_values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "2de0b5e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "monthly_df.to_csv('Data/PRC/Monthly - Liquidity1.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27aa15a9",
   "metadata": {},
   "source": [
    "# Total Return Volatility - Standard Deviation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "id": "4201342d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              date     TICKER COMNAM CSRCIC-1 CSRCIC-2   PRC          VOL  \\\n",
      "0       2004-01-02  000776.SZ   广发证券        J      J67  4.58     257179.0   \n",
      "1       2004-01-05  000776.SZ   广发证券        J      J67  4.50     345250.0   \n",
      "2       2004-01-06  000776.SZ   广发证券        J      J67  4.24     868450.0   \n",
      "3       2004-01-07  000776.SZ   广发证券        J      J67  4.34     528968.0   \n",
      "4       2004-01-08  000776.SZ   广发证券        J      J67  4.47     735885.0   \n",
      "...            ...        ...    ...      ...      ...   ...          ...   \n",
      "1457097 2023-12-25  603993.SH   洛阳钼业        B      B09  4.93   76115676.0   \n",
      "1457098 2023-12-26  603993.SH   洛阳钼业        B      B09  4.92   56692336.0   \n",
      "1457099 2023-12-27  603993.SH   洛阳钼业        B      B09  5.08  151966596.0   \n",
      "1457100 2023-12-28  603993.SH   洛阳钼业        B      B09  5.16  163415379.0   \n",
      "1457101 2023-12-29  603993.SH   洛阳钼业        B      B09  5.20   98730274.0   \n",
      "\n",
      "              RET    BID    ASK            DV   BAS year_month  \n",
      "0        1.777778   8.65   8.41  1.177880e+06 -0.24    2004-01  \n",
      "1       -1.746725   8.98   8.60  1.553625e+06 -0.38    2004-01  \n",
      "2       -5.777778   9.55   9.00  3.682228e+06 -0.55    2004-01  \n",
      "3        2.358491   9.53   9.25  2.295721e+06 -0.28    2004-01  \n",
      "4        2.995392   9.47   9.23  3.289406e+06 -0.24    2004-01  \n",
      "...           ...    ...    ...           ...   ...        ...  \n",
      "1457097  0.612245  58.00  56.69  3.752503e+08 -1.31    2023-12  \n",
      "1457098 -0.202840  57.36  55.68  2.789263e+08 -1.68    2023-12  \n",
      "1457099  3.252033  57.74  56.41  7.719903e+08 -1.33    2023-12  \n",
      "1457100  1.574803  57.25  55.95  8.432234e+08 -1.30    2023-12  \n",
      "1457101  0.775194  56.15  55.05  5.133974e+08 -1.10    2023-12  \n",
      "\n",
      "[1457102 rows x 13 columns]\n"
     ]
    }
   ],
   "source": [
    "print(merged_panel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "id": "259ef4a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_panel = merged_panel[pd.to_numeric(merged_panel['RET'], errors='coerce').notnull()]\n",
    "merged_panel['RET'] = merged_panel['RET'].astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "id": "de1602e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "monthly_volatility = merged_panel.groupby(['TICKER', 'year_month'])['RET'].std().reset_index()\n",
    "monthly_volatility.rename(columns={'RET': 'TRV'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "id": "df4dae01",
   "metadata": {},
   "outputs": [],
   "source": [
    "monthly_volatility['year'] = monthly_volatility['year_month'].dt.year\n",
    "annual_volatility = monthly_volatility.groupby(['TICKER', 'year'])['TRV'].mean().reset_index()\n",
    "annual_volatility.rename(columns={'TRV': 'annual_TRV'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "id": "4e44c8d2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "TICKER        0\n",
      "year          0\n",
      "annual_TRV    0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "missing_values_after = annual_volatility.isnull().sum()\n",
    "print(missing_values_after)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "id": "e297eaa5",
   "metadata": {},
   "outputs": [],
   "source": [
    "annual_volatility = annual_volatility[['TICKER', 'year', 'annual_TRV']]\n",
    "\n",
    "annual_volatility.to_csv('/Users/yuyangdou/RSM/BAM/11Model/Data/PRC/annual_volatility.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "id": "902af2ff",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         TICKER  year  annual_TRV\n",
      "0     000001.SZ  2004    1.803115\n",
      "1     000001.SZ  2005    2.299663\n",
      "2     000001.SZ  2006    2.446014\n",
      "3     000001.SZ  2007    3.476504\n",
      "4     000001.SZ  2008    4.149569\n",
      "...         ...   ...         ...\n",
      "5995  688981.SH  2019    0.000000\n",
      "5996  688981.SH  2020    4.726529\n",
      "5997  688981.SH  2021    2.210705\n",
      "5998  688981.SH  2022    1.650728\n",
      "5999  688981.SH  2023    2.202027\n",
      "\n",
      "[6000 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "print(annual_volatility)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e4c8b3d5",
   "metadata": {},
   "source": [
    "# Regression - Idiosyncratic Volatility & Market Beta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "id": "f62b81c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_panel2 = pd.read_csv('Data/PRC/Daily1.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "id": "5f2bb218",
   "metadata": {},
   "outputs": [],
   "source": [
    "market_return_path = '/Users/yuyangdou/RSM/BAM/11Model/Data/PRC/市场回报率.xlsx'\n",
    "market_return_df = pd.read_excel(market_return_path, sheet_name=0)\n",
    "\n",
    "merged_panel2['date'] = pd.to_datetime(merged_panel2['date'])\n",
    "market_return_df['date'] = pd.to_datetime(market_return_df['date'])\n",
    "\n",
    "merged_panel2 = pd.merge(merged_panel2, market_return_df, on='date', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "id": "ba8b2685",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_panel2['date'] = pd.to_datetime(merged_panel2['date'])\n",
    "merged_panel2['year'] = merged_panel2['date'].dt.year\n",
    "merged_panel2['year_month'] = merged_panel2['date'].dt.to_period('M')\n",
    "\n",
    "merged_panel2['RET'] = pd.to_numeric(merged_panel2['RET'], errors='coerce')\n",
    "merged_panel2['sprtrn'] = pd.to_numeric(merged_panel2['sprtrn'], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "id": "5e16fcb5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              date     TICKER COMNAM CSRCIC-1 CSRCIC-2   PRC          VOL  \\\n",
      "0       2004-01-02  000776.SZ   广发证券        J      J67  4.58     257179.0   \n",
      "1       2004-01-05  000776.SZ   广发证券        J      J67  4.50     345250.0   \n",
      "2       2004-01-06  000776.SZ   广发证券        J      J67  4.24     868450.0   \n",
      "3       2004-01-07  000776.SZ   广发证券        J      J67  4.34     528968.0   \n",
      "4       2004-01-08  000776.SZ   广发证券        J      J67  4.47     735885.0   \n",
      "...            ...        ...    ...      ...      ...   ...          ...   \n",
      "1457097 2023-12-25  603993.SH   洛阳钼业        B      B09  4.93   76115676.0   \n",
      "1457098 2023-12-26  603993.SH   洛阳钼业        B      B09  4.92   56692336.0   \n",
      "1457099 2023-12-27  603993.SH   洛阳钼业        B      B09  5.08  151966596.0   \n",
      "1457100 2023-12-28  603993.SH   洛阳钼业        B      B09  5.16  163415379.0   \n",
      "1457101 2023-12-29  603993.SH   洛阳钼业        B      B09  5.20   98730274.0   \n",
      "\n",
      "              RET    BID    ASK    sprtrn  year year_month  \n",
      "0        1.777778   8.65   8.41  0.826035  2004    2004-01  \n",
      "1       -1.746725   8.98   8.60  2.976392  2004    2004-01  \n",
      "2       -5.777778   9.55   9.00  0.859999  2004    2004-01  \n",
      "3        2.358491   9.53   9.25  1.209542  2004    2004-01  \n",
      "4        2.995392   9.47   9.23  1.427028  2004    2004-01  \n",
      "...           ...    ...    ...       ...   ...        ...  \n",
      "1457097  0.612245  58.00  56.69  0.306323  2023    2023-12  \n",
      "1457098 -0.202840  57.36  55.68 -0.676951  2023    2023-12  \n",
      "1457099  3.252033  57.74  56.41  0.347902  2023    2023-12  \n",
      "1457100  1.574803  57.25  55.95  2.343367  2023    2023-12  \n",
      "1457101  0.775194  56.15  55.05  0.485266  2023    2023-12  \n",
      "\n",
      "[1457102 rows x 13 columns]\n"
     ]
    }
   ],
   "source": [
    "print(merged_panel2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "id": "8fa89795",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_panel2['IV'] = np.nan\n",
    "merged_panel2['MB'] = np.nan\n",
    "\n",
    "companies = merged_panel2['TICKER'].unique()\n",
    "for company in companies:\n",
    "    company_data = merged_panel2[merged_panel2['TICKER'] == company]\n",
    "    \n",
    "    \n",
    "    company_data = company_data.dropna(subset=['RET', 'sprtrn'])\n",
    "    \n",
    "    if len(company_data) > 1:  \n",
    "        X = sm.add_constant(company_data['sprtrn'])\n",
    "        y = company_data['RET']\n",
    "        \n",
    "        model = sm.OLS(y, X).fit()\n",
    "        \n",
    "        merged_panel2.loc[merged_panel2['TICKER'] == company, 'MB'] = model.params['sprtrn']\n",
    "        merged_panel2.loc[merged_panel2['TICKER'] == company, 'IV'] = model.resid.std()\n",
    "\n",
    "annual_iv_mb = merged_panel2.groupby(['TICKER', 'year']).agg({\n",
    "    'IV': 'mean',\n",
    "    'MB': 'mean'\n",
    "}).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "id": "89313a0d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "TICKER    0\n",
      "year      0\n",
      "IV        0\n",
      "MB        0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "missing_values_after = annual_iv_mb.isnull().sum()\n",
    "print(missing_values_after)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "id": "1e6e7e33",
   "metadata": {},
   "outputs": [],
   "source": [
    "annual_iv_mb.to_csv('Data/PRC/annual_iv_mb.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "id": "4bd62b10",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         TICKER  year        IV        MB\n",
      "0     000001.SZ  2004  1.724663  1.047493\n",
      "1     000001.SZ  2005  1.724663  1.047493\n",
      "2     000001.SZ  2006  1.724663  1.047493\n",
      "3     000001.SZ  2007  1.724663  1.047493\n",
      "4     000001.SZ  2008  1.724663  1.047493\n",
      "...         ...   ...       ...       ...\n",
      "5995  688981.SH  2019  3.066400  0.009212\n",
      "5996  688981.SH  2020  3.066400  0.009212\n",
      "5997  688981.SH  2021  3.066400  0.009212\n",
      "5998  688981.SH  2022  3.066400  0.009212\n",
      "5999  688981.SH  2023  3.066400  0.009212\n",
      "\n",
      "[6000 rows x 4 columns]\n"
     ]
    }
   ],
   "source": [
    "print(annual_iv_mb)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd751add",
   "metadata": {},
   "source": [
    "# Monthly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "44d8b1c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "monthly_df = pd.read_excel('Data/PRC/月度数据.xlsx', sheet_name='Monthly')\n",
    "identity_df = pd.read_excel('Data/PRC/公司标识数据.xlsx', sheet_name='万得')\n",
    "\n",
    "merged_df = pd.merge(monthly_df, identity_df, on=['TICKER', 'COMNAM'], how='inner')\n",
    "merged_df.to_csv('Data/PRC/M合并1.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "fddbf1f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "columns_to_drop = ['上市地点', '上市板']\n",
    "merged_df.drop(columns=columns_to_drop, inplace=True)\n",
    "\n",
    "merged_df['日期'] = pd.to_datetime(merged_df['日期']).dt.to_period('M')\n",
    "\n",
    "columns_to_rename = {\n",
    "    '日期': 'date',\n",
    "    '月度回报率': 'RET',\n",
    "    '股票价格': 'PRC',\n",
    "    '股票交易量': 'VOL',\n",
    "    '流通股数量': 'SHROUT'\n",
    "}\n",
    "merged_df.rename(columns=columns_to_rename, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "9eeca44f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      COMNAM     TICKER     date        RET   PRC           VOL        SHROUT  \\\n",
      "0       广发证券  000776.SZ  2004-01  12.222222  5.05  1.197356e+07  9.318396e+07   \n",
      "1       广发证券  000776.SZ  2004-02   9.108911  5.51  2.938734e+07  9.318396e+07   \n",
      "2       广发证券  000776.SZ  2004-03  -7.078040  5.12  4.805419e+07  9.318396e+07   \n",
      "3       广发证券  000776.SZ  2004-04 -14.648438  4.37  2.873926e+07  9.318396e+07   \n",
      "4       广发证券  000776.SZ  2004-05 -15.789474  3.68  1.019008e+07  9.318396e+07   \n",
      "...      ...        ...      ...        ...   ...           ...           ...   \n",
      "71995   洛阳钼业  603993.SH  2023-08  -7.131280  5.73  1.963778e+09  2.159924e+10   \n",
      "71996   洛阳钼业  603993.SH  2023-09   3.141361  5.91  1.771370e+09  2.159924e+10   \n",
      "71997   洛阳钼业  603993.SH  2023-10  -6.260575  5.54  1.438451e+09  2.159924e+10   \n",
      "71998   洛阳钼业  603993.SH  2023-11  -5.956679  5.21  1.826941e+09  2.159924e+10   \n",
      "71999   洛阳钼业  603993.SH  2023-12  -0.191939  5.20  2.445170e+09  2.159924e+10   \n",
      "\n",
      "      CSRCIC-1 所属证监会行业名称\\n[行业级别] 门类行业 CSRCIC-2 所属证监会行业代码\\n[行业级别] 大类行业  \n",
      "0            J                    金融业      J67                 资本市场服务  \n",
      "1            J                    金融业      J67                 资本市场服务  \n",
      "2            J                    金融业      J67                 资本市场服务  \n",
      "3            J                    金融业      J67                 资本市场服务  \n",
      "4            J                    金融业      J67                 资本市场服务  \n",
      "...        ...                    ...      ...                    ...  \n",
      "71995        B                    采矿业      B09               有色金属矿采选业  \n",
      "71996        B                    采矿业      B09               有色金属矿采选业  \n",
      "71997        B                    采矿业      B09               有色金属矿采选业  \n",
      "71998        B                    采矿业      B09               有色金属矿采选业  \n",
      "71999        B                    采矿业      B09               有色金属矿采选业  \n",
      "\n",
      "[72000 rows x 11 columns]\n"
     ]
    }
   ],
   "source": [
    "print(merged_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "798aaffd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "COMNAM                        0\n",
      "TICKER                        0\n",
      "date                          0\n",
      "RET                       23652\n",
      "PRC                       23852\n",
      "VOL                       24520\n",
      "SHROUT                    23744\n",
      "CSRCIC-1                      0\n",
      "所属证监会行业名称\\n[行业级别] 门类行业        0\n",
      "CSRCIC-2                      0\n",
      "所属证监会行业代码\\n[行业级别] 大类行业        0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "missing_values = merged_df.isnull().sum()\n",
    "print(missing_values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "869391c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "num_cols = ['PRC', 'SHROUT', 'VOL', 'RET']\n",
    "for col in num_cols:\n",
    "    merged_df[col] = pd.to_numeric(merged_df[col], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "c2aa4311",
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in num_cols:\n",
    "    median_value = merged_df[col].median()\n",
    "    merged_df[col].fillna(median_value, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "40a5c4f4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "COMNAM                    0\n",
      "TICKER                    0\n",
      "date                      0\n",
      "RET                       0\n",
      "PRC                       0\n",
      "VOL                       0\n",
      "SHROUT                    0\n",
      "CSRCIC-1                  0\n",
      "所属证监会行业名称\\n[行业级别] 门类行业    0\n",
      "CSRCIC-2                  0\n",
      "所属证监会行业代码\\n[行业级别] 大类行业    0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "missing_values = merged_df.isnull().sum()\n",
    "print(missing_values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "c0751235",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df.to_csv('Data/PRC/Monthly_Merged.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d792e04f",
   "metadata": {},
   "source": [
    "# Macro Economics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f19945c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "macro_df = pd.read_excel('Data/PRC/中国国库券利率_3个月.xlsx', sheet_name='Macro')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "08f82397",
   "metadata": {},
   "outputs": [],
   "source": [
    "macro_df['date'] = pd.to_datetime(macro_df['date']).dt.to_period('M')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "3befe987",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged2_df = pd.merge(merged_df, macro_df, on=['date'], how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "0ab95481",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      COMNAM     TICKER     date        RET   PRC           VOL        SHROUT  \\\n",
      "0       广发证券  000776.SZ  2004-01  12.222222  5.05  1.197356e+07  9.318396e+07   \n",
      "1       广发证券  000776.SZ  2004-02   9.108911  5.51  2.938734e+07  9.318396e+07   \n",
      "2       广发证券  000776.SZ  2004-03  -7.078040  5.12  4.805419e+07  9.318396e+07   \n",
      "3       广发证券  000776.SZ  2004-04 -14.648438  4.37  2.873926e+07  9.318396e+07   \n",
      "4       广发证券  000776.SZ  2004-05 -15.789474  3.68  1.019008e+07  9.318396e+07   \n",
      "...      ...        ...      ...        ...   ...           ...           ...   \n",
      "71995   洛阳钼业  603993.SH  2023-08  -7.131280  5.73  1.963778e+09  2.159924e+10   \n",
      "71996   洛阳钼业  603993.SH  2023-09   3.141361  5.91  1.771370e+09  2.159924e+10   \n",
      "71997   洛阳钼业  603993.SH  2023-10  -6.260575  5.54  1.438451e+09  2.159924e+10   \n",
      "71998   洛阳钼业  603993.SH  2023-11  -5.956679  5.21  1.826941e+09  2.159924e+10   \n",
      "71999   洛阳钼业  603993.SH  2023-12  -0.191939  5.20  2.445170e+09  2.159924e+10   \n",
      "\n",
      "      CSRCIC-1 所属证监会行业名称\\n[行业级别] 门类行业 CSRCIC-2 所属证监会行业代码\\n[行业级别] 大类行业  \\\n",
      "0            J                    金融业      J67                 资本市场服务   \n",
      "1            J                    金融业      J67                 资本市场服务   \n",
      "2            J                    金融业      J67                 资本市场服务   \n",
      "3            J                    金融业      J67                 资本市场服务   \n",
      "4            J                    金融业      J67                 资本市场服务   \n",
      "...        ...                    ...      ...                    ...   \n",
      "71995        B                    采矿业      B09               有色金属矿采选业   \n",
      "71996        B                    采矿业      B09               有色金属矿采选业   \n",
      "71997        B                    采矿业      B09               有色金属矿采选业   \n",
      "71998        B                    采矿业      B09               有色金属矿采选业   \n",
      "71999        B                    采矿业      B09               有色金属矿采选业   \n",
      "\n",
      "            10Y       tbl   T10Y3MM      gpce  \n",
      "0      3.785178  2.228400  1.556778  0.000000  \n",
      "1      3.827980  2.252155  1.575825  0.012235  \n",
      "2      3.879409  2.339422  1.539987 -0.022742  \n",
      "3      4.167057  2.497357  1.669700  0.084230  \n",
      "4      4.301639  2.694856  1.606783 -0.037681  \n",
      "...         ...       ...       ...       ...  \n",
      "71995  2.598543  1.586778  1.011765  0.010827  \n",
      "71996  2.645305  1.800570  0.844735 -0.165088  \n",
      "71997  2.694521  2.049947  0.644574 -0.236952  \n",
      "71998  2.668309  2.331673  0.336636 -0.477738  \n",
      "71999  2.617368  2.247300  0.370068  0.099311  \n",
      "\n",
      "[72000 rows x 15 columns]\n"
     ]
    }
   ],
   "source": [
    "print(merged2_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "ae6b7bbd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "COMNAM                    0\n",
      "TICKER                    0\n",
      "date                      0\n",
      "RET                       0\n",
      "PRC                       0\n",
      "VOL                       0\n",
      "SHROUT                    0\n",
      "CSRCIC-1                  0\n",
      "所属证监会行业名称\\n[行业级别] 门类行业    0\n",
      "CSRCIC-2                  0\n",
      "所属证监会行业代码\\n[行业级别] 大类行业    0\n",
      "10Y                       0\n",
      "tbl                       0\n",
      "T10Y3MM                   0\n",
      "gpce                      0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "missing_values = merged2_df.isnull().sum()\n",
    "print(missing_values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "0996c9f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged2_df.to_csv('Data/PRC/macro.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4559d0c5",
   "metadata": {},
   "source": [
    "# Balanced Panel???"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "9adf059e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Companies with less than 240 records:\n",
      "Series([], Name: count, dtype: int64)\n"
     ]
    }
   ],
   "source": [
    "company_counts = merged2_df['COMNAM'].value_counts()\n",
    "unbalanced_companies = company_counts[company_counts < 240]\n",
    "\n",
    "print(\"Companies with less than 240 records:\")\n",
    "print(unbalanced_companies)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "df7d9723",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The number of companies with exactly 240 records is: 300\n",
      "Companies with exactly 240 records are:\n",
      "['广发证券', '中国广核', 'TCL中环', '浪潮信息', '中信特钢', '申万宏源', '中国交建', '国投电力', '大华股份', '恒生电子', '汇川技术', '海天味业', '成都银行', '中海油服', '三六零', '广汽集团', '山西汾酒', '北方华创', '亿纬锂能', '恒力石化', '亿联网络', '泸州老窖', '云南白药', '徐工机械', '潍柴动力', '中联重科', 'TCL科技', '华侨城A', '中兴通讯', '万科A', '平安银行', '今世缘', '京沪高铁', '邮储银行', '恒立液压', '闻泰科技', '通威股份', '中国巨石', '华润三九', '三峡能源', '爱美客', '卓胜微', '迈瑞医疗', '康龙化成', '宁德时代', '晶盛机电', '天赐材料', '天齐锂业', '晶澳科技', '东方盛虹', '传音控股', '中微公司', '澜起科技', '万泰生物', '公牛集团', '金龙鱼', '中国电信', '华能水电', '福莱特', '蓝思科技', '三环集团', '荣盛石化', '三花智控', '北新建材', '中国核电', '国泰君安', '中芯国际', '天合光能', '奇安信-U', '东鹏饮料', '科沃斯', '合盛硅业', '中煤能源', '中国能建', '长安汽车', '格力电器', '中航西飞', '华泰证券', '中国人寿', '中国中冶', '中国太保', '中国铝业', '工商银行', '中国中铁', '交通银行', '中国平安', '农业银行', '中国铁建', '北京银行', '兴业银行', '中国国航', '中国神华', '南京银行', '中国建筑', '中国中车', '五粮液', '中国石油', '白云山', '华东医药', '方正证券', '中国电建', '长城汽车', '新华保险', '歌尔股份', '京东方A', '兴业证券', '中信银行', '中国重工', '中国银行', '建设银行', '紫金矿业', '中国中免', '大秦铁路', '招商证券', '长江电力', '海通证券', '中信证券', '南方航空', '中国石化', '宝钢股份', '民生银行', '华夏银行', '包钢股份', '上海机场', '浦发银行', '洋河股份', '金风科技', '宁波银行', '华兰生物', '双汇发展', '新希望', '三一重工', '招商银行', '保利发展', '山东黄金', '国电电力', '华域汽车', '海尔智家', '福耀玻璃', '用友网络', '海螺水泥', '贵州茅台', '中国联通', '江西铜业', '万华化学', '复星医药', '北方稀土', '上汽集团', '同仁堂', '中泰证券', '宝信软件', '青岛啤酒', '光大证券', '东方证券', '东方财富', '国信证券', '大全能源', '时代电气', '德业股份', '拓普集团', '明阳智能', '新奥股份', '川投能源', '圆通速递', '四川路桥', '纳思达', '振华科技', '华润微', '春秋航空', '中远海控', '沃森生物', '分众传媒', '东方雨虹', '中航光电', '新和成', '长春高新', '晨光股份', '韦尔股份', '中国卫通', '红塔证券', '宝丰能源', '生益科技', '泰格医药', '深南电路', '视源股份', '绿地控股', '国投资本', '华熙生物', '沪硅产业', '金山办公', '福斯特', '信达证券', '中国海油', '航发动力', '伊利股份', '海南机场', '中金黄金', '国电南瑞', '中航机载', '恒瑞医药', '中国东航', '浙能电力', '华大九天', '中际旭创', '以岭药业', '海康威视', '光大银行', '浙商银行', '海光信息', '阳光电源', '璞泰来', '中金公司', '星宇股份', '华鲁恒升', '重庆啤酒', '先导智能', '一汽解放', '寒武纪-U', '美的集团', '招商蛇口', '上港集团', '科大讯飞', '比亚迪', '联影医疗', '海大集团', '中航沈飞', '长电科技', '片仔癀', '顺丰控股', '晶科能源', '招商轮船', '潞安环能', '上海医药', '东方电气', '锦江酒店', '爱旭股份', '山西焦煤', '中油资本', '陕西煤业', '立讯精密', '上海莱士', '正泰电器', '新城控股', '三七互娱', '江苏银行', '中信建投', '杭州银行', '中国船舶', '凯莱英', '恩捷股份', '紫光国微', '欧派家居', '华友钴业', '浙商证券', '隆基绿能', '华能国际', '智飞生物', '爱尔眼科', '龙佰集团', '赣锋锂业', '中国银河', '上海银行', '同花顺', '牧原股份', '紫光股份', '凯赛生物', '国轩高科', '龙源电力', '天山股份', '盐湖股份', '藏格矿业', '中科曙光', '中国人保', '温氏股份', '芒果超媒', '鹏鼎控股', '广联达', '古井贡酒', '兆易创新', '药明康德', '工业富联', '卫星化学', '德赛西威', '北京君正', '特变电工', '斯达半导', '中国化学', '中国移动', '士兰微', '南山铝业', '兖矿能源', '华利集团', '深信服', '贝泰妮', '中伟股份', '锦浪科技', '迈为股份', '圣邦股份', '中科创达', '洛阳钼业']\n"
     ]
    }
   ],
   "source": [
    "company_counts = merged2_df['COMNAM'].value_counts()\n",
    "complete_companies = company_counts[company_counts == 240]\n",
    "\n",
    "print(f\"The number of companies with exactly 240 records is: {complete_companies.shape[0]}\")\n",
    "print(\"Companies with exactly 240 records are:\")\n",
    "print(complete_companies.index.tolist())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03a85372",
   "metadata": {},
   "source": [
    "# Momentum - Monthly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "8a5aaf74",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged2_df = merged2_df.sort_values(by=['TICKER', 'date'])\n",
    "merged2_df['12m_momentum'] = merged2_df.groupby('TICKER')['RET'].rolling(12).apply(lambda x: (x + 1).prod() - 1).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "f7e7295f",
   "metadata": {},
   "outputs": [],
   "source": [
    "industry_groups = merged2_df.groupby(['CSRCIC-2', 'date'])\n",
    "merged2_df['industry_momentum'] = industry_groups['12m_momentum'].transform('mean')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "924c9e09",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged2_df['1m_reversal'] = merged2_df.groupby('TICKER')['RET'].shift(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "b61f1f3b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "COMNAM                       0\n",
      "TICKER                       0\n",
      "date                         0\n",
      "RET                          0\n",
      "PRC                          0\n",
      "VOL                          0\n",
      "SHROUT                       0\n",
      "CSRCIC-1                     0\n",
      "所属证监会行业名称\\n[行业级别] 门类行业       0\n",
      "CSRCIC-2                     0\n",
      "所属证监会行业代码\\n[行业级别] 大类行业       0\n",
      "10Y                          0\n",
      "tbl                          0\n",
      "T10Y3MM                      0\n",
      "gpce                         0\n",
      "12m_momentum              3300\n",
      "industry_momentum         3300\n",
      "1m_reversal                300\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "missing_values = merged2_df.isnull().sum()\n",
    "print(missing_values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "4973bc65",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "COMNAM                    0\n",
      "TICKER                    0\n",
      "date                      0\n",
      "RET                       0\n",
      "PRC                       0\n",
      "VOL                       0\n",
      "SHROUT                    0\n",
      "CSRCIC-1                  0\n",
      "所属证监会行业名称\\n[行业级别] 门类行业    0\n",
      "CSRCIC-2                  0\n",
      "所属证监会行业代码\\n[行业级别] 大类行业    0\n",
      "10Y                       0\n",
      "tbl                       0\n",
      "T10Y3MM                   0\n",
      "gpce                      0\n",
      "12m_momentum              0\n",
      "industry_momentum         0\n",
      "1m_reversal               0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "def fill_missing_with_monthly_median(df, column):\n",
    "    df[column] = df.groupby('TICKER')[column].transform(lambda x: x.fillna(x.median()))\n",
    "    return df\n",
    "\n",
    "merged2_df = fill_missing_with_monthly_median(merged2_df, '12m_momentum')\n",
    "merged2_df = fill_missing_with_monthly_median(merged2_df, 'industry_momentum')\n",
    "merged2_df = fill_missing_with_monthly_median(merged2_df, '1m_reversal')\n",
    "\n",
    "missing_values = merged2_df.isnull().sum()\n",
    "print(missing_values)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c12f4e2f",
   "metadata": {},
   "source": [
    "# Market Value & Excess Return(target variable)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "1a77bb61",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged2_df['EXRET'] = merged2_df['RET'] - merged2_df['tbl']\n",
    "merged2_df['MV'] = merged2_df['PRC'] * merged2_df['SHROUT']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "47cb716c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      COMNAM     TICKER     date        RET    PRC          VOL        SHROUT  \\\n",
      "51360   平安银行  000001.SZ  2004-01   9.048179   9.28  165652539.0  1.409362e+09   \n",
      "51361   平安银行  000001.SZ  2004-02  11.637931  10.36  416409885.0  1.409362e+09   \n",
      "51362   平安银行  000001.SZ  2004-03   2.895753  10.66  359113615.0  1.409362e+09   \n",
      "51363   平安银行  000001.SZ  2004-04 -12.476548   9.33  185640796.0  1.409362e+09   \n",
      "51364   平安银行  000001.SZ  2004-05   3.108253   9.62   59981735.0  1.409362e+09   \n",
      "\n",
      "      CSRCIC-1 所属证监会行业名称\\n[行业级别] 门类行业 CSRCIC-2 所属证监会行业代码\\n[行业级别] 大类行业  \\\n",
      "51360        J                    金融业      J66                 货币金融服务   \n",
      "51361        J                    金融业      J66                 货币金融服务   \n",
      "51362        J                    金融业      J66                 货币金融服务   \n",
      "51363        J                    金融业      J66                 货币金融服务   \n",
      "51364        J                    金融业      J66                 货币金融服务   \n",
      "\n",
      "            10Y       tbl   T10Y3MM      gpce  12m_momentum  \\\n",
      "51360  3.785178  2.228400  1.556778  0.000000      7.403997   \n",
      "51361  3.827980  2.252155  1.575825  0.012235      7.403997   \n",
      "51362  3.879409  2.339422  1.539987 -0.022742      7.403997   \n",
      "51363  4.167057  2.497357  1.669700  0.084230      7.403997   \n",
      "51364  4.301639  2.694856  1.606783 -0.037681      7.403997   \n",
      "\n",
      "       industry_momentum  1m_reversal      EXRET            MV  \n",
      "51360      -2.686160e+09     0.515796   6.819779  1.307888e+10  \n",
      "51361      -2.686160e+09     9.048179   9.385776  1.460099e+10  \n",
      "51362      -2.686160e+09    11.637931   0.556331  1.502380e+10  \n",
      "51363      -2.686160e+09     2.895753 -14.973905  1.314935e+10  \n",
      "51364      -2.686160e+09   -12.476548   0.413397  1.355806e+10  \n"
     ]
    }
   ],
   "source": [
    "merged2_df.to_csv('Data/PRC/merged2_with_EXRET_MV.csv', index=False)\n",
    "print(merged2_df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "514bac3d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "COMNAM                    0\n",
      "TICKER                    0\n",
      "date                      0\n",
      "RET                       0\n",
      "PRC                       0\n",
      "VOL                       0\n",
      "SHROUT                    0\n",
      "CSRCIC-1                  0\n",
      "所属证监会行业名称\\n[行业级别] 门类行业    0\n",
      "CSRCIC-2                  0\n",
      "所属证监会行业代码\\n[行业级别] 大类行业    0\n",
      "10Y                       0\n",
      "tbl                       0\n",
      "T10Y3MM                   0\n",
      "gpce                      0\n",
      "12m_momentum              0\n",
      "industry_momentum         0\n",
      "1m_reversal               0\n",
      "EXRET                     0\n",
      "MV                        0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "missing_values = merged2_df.isnull().sum()\n",
    "print(missing_values)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b68450c1",
   "metadata": {},
   "source": [
    "# Final Merge - excluding Financial Ratio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "a507dc76",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = pd.read_csv('Data/PRC/merged2_with_EXRET_MV.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "37b71c04",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      COMNAM     TICKER     date        RET    PRC          VOL        SHROUT  \\\n",
      "0       平安银行  000001.SZ  2004-01   9.048179   9.28  165652539.0  1.409362e+09   \n",
      "1       平安银行  000001.SZ  2004-02  11.637931  10.36  416409885.0  1.409362e+09   \n",
      "2       平安银行  000001.SZ  2004-03   2.895753  10.66  359113615.0  1.409362e+09   \n",
      "3       平安银行  000001.SZ  2004-04 -12.476548   9.33  185640796.0  1.409362e+09   \n",
      "4       平安银行  000001.SZ  2004-05   3.108253   9.62   59981735.0  1.409362e+09   \n",
      "...      ...        ...      ...        ...    ...          ...           ...   \n",
      "71995   中芯国际  688981.SH  2023-08   5.607477  54.24  504143339.0  7.943158e+09   \n",
      "71996   中芯国际  688981.SH  2023-09  -5.696903  51.15  734782312.0  7.945499e+09   \n",
      "71997   中芯国际  688981.SH  2023-10  11.925709  57.25  695973804.0  7.945886e+09   \n",
      "71998   中芯国际  688981.SH  2023-11  -6.200873  53.70  528386389.0  7.946542e+09   \n",
      "71999   中芯国际  688981.SH  2023-12  -1.266294  53.02  367673738.0  7.946556e+09   \n",
      "\n",
      "      CSRCIC-1 所属证监会行业名称\\n[行业级别] 门类行业 CSRCIC-2 所属证监会行业代码\\n[行业级别] 大类行业  \\\n",
      "0            J                    金融业      J66                 货币金融服务   \n",
      "1            J                    金融业      J66                 货币金融服务   \n",
      "2            J                    金融业      J66                 货币金融服务   \n",
      "3            J                    金融业      J66                 货币金融服务   \n",
      "4            J                    金融业      J66                 货币金融服务   \n",
      "...        ...                    ...      ...                    ...   \n",
      "71995        C                    制造业      C39       计算机、通信和其他电子设备制造业   \n",
      "71996        C                    制造业      C39       计算机、通信和其他电子设备制造业   \n",
      "71997        C                    制造业      C39       计算机、通信和其他电子设备制造业   \n",
      "71998        C                    制造业      C39       计算机、通信和其他电子设备制造业   \n",
      "71999        C                    制造业      C39       计算机、通信和其他电子设备制造业   \n",
      "\n",
      "            10Y       tbl   T10Y3MM      gpce  12m_momentum  \\\n",
      "0      3.785178  2.228400  1.556778  0.000000  7.403997e+00   \n",
      "1      3.827980  2.252155  1.575825  0.012235  7.403997e+00   \n",
      "2      3.879409  2.339422  1.539987 -0.022742  7.403997e+00   \n",
      "3      4.167057  2.497357  1.669700  0.084230  7.403997e+00   \n",
      "4      4.301639  2.694856  1.606783 -0.037681  7.403997e+00   \n",
      "...         ...       ...       ...       ...           ...   \n",
      "71995  2.598543  1.586778  1.011765  0.010827  1.393264e+07   \n",
      "71996  2.645305  1.800570  0.844735 -0.165088  2.987144e+07   \n",
      "71997  2.694521  2.049947  0.644574 -0.236952 -8.488479e+06   \n",
      "71998  2.668309  2.331673  0.336636 -0.477738 -8.138546e+05   \n",
      "71999  2.617368  2.247300  0.370068  0.099311 -9.476550e+05   \n",
      "\n",
      "       industry_momentum  1m_reversal      EXRET            MV  \n",
      "0          -2.686160e+09     0.515796   6.819779  1.307888e+10  \n",
      "1          -2.686160e+09     9.048179   9.385776  1.460099e+10  \n",
      "2          -2.686160e+09    11.637931   0.556331  1.502380e+10  \n",
      "3          -2.686160e+09     2.895753 -14.973905  1.314935e+10  \n",
      "4          -2.686160e+09   -12.476548   0.413397  1.355806e+10  \n",
      "...                  ...          ...        ...           ...  \n",
      "71995      -1.478887e+11     1.662708   4.020698  4.308369e+11  \n",
      "71996       4.637670e+11     5.607477  -7.497473  4.064123e+11  \n",
      "71997      -9.647850e+11    -5.696903   9.875761  4.549020e+11  \n",
      "71998       1.550047e+13    11.925709  -8.532546  4.267293e+11  \n",
      "71999       1.064175e+13    -6.200873  -3.513594  4.213264e+11  \n",
      "\n",
      "[72000 rows x 20 columns]\n"
     ]
    }
   ],
   "source": [
    "print(final_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "1f5a1a74",
   "metadata": {},
   "outputs": [],
   "source": [
    "monthly_liquidity = pd.read_csv('/Users/yuyangdou/RSM/BAM/11Model/Data/PRC/Monthly - Liquidity1.csv')\n",
    "monthly_liquidity.rename(columns={'year_month': 'date'}, inplace=True)\n",
    "\n",
    "annual_volatility = pd.read_csv('/Users/yuyangdou/RSM/BAM/11Model/Data/PRC/annual_volatility.csv')\n",
    "annual_iv_mb = pd.read_csv('/Users/yuyangdou/RSM/BAM/11Model/Data/PRC/annual_iv_mb.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "1a88d89a",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df['date'] = pd.to_datetime(final_df['date']).dt.to_period('M').astype(str)\n",
    "monthly_liquidity['date'] = pd.to_datetime(monthly_liquidity['date']).dt.to_period('M').astype(str)\n",
    "annual_volatility['year'] = annual_volatility['year'].astype(str)\n",
    "annual_iv_mb['year'] = annual_iv_mb['year'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "9ef49f3f",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df['year'] = final_df['date'].str[:4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "d2a8cab8",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = pd.merge(final_df, monthly_liquidity[['TICKER', 'date', 'DV', 'BAS']], on=['TICKER', 'date'], how='left')\n",
    "final_df = pd.merge(final_df, annual_volatility[['TICKER', 'year', 'annual_TRV']], on=['TICKER', 'year'], how='left')\n",
    "final_df = pd.merge(final_df, annual_iv_mb[['TICKER', 'year', 'IV', 'MB']], on=['TICKER', 'year'], how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "7f3ca51e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 72000 entries, 0 to 71999\n",
      "Data columns (total 26 columns):\n",
      " #   Column                 Non-Null Count  Dtype  \n",
      "---  ------                 --------------  -----  \n",
      " 0   COMNAM                 72000 non-null  object \n",
      " 1   TICKER                 72000 non-null  object \n",
      " 2   date                   72000 non-null  object \n",
      " 3   RET                    72000 non-null  float64\n",
      " 4   PRC                    72000 non-null  float64\n",
      " 5   VOL                    72000 non-null  float64\n",
      " 6   SHROUT                 72000 non-null  float64\n",
      " 7   CSRCIC-1               72000 non-null  object \n",
      " 8   所属证监会行业名称\n",
      "[行业级别] 门类行业  72000 non-null  object \n",
      " 9   CSRCIC-2               72000 non-null  object \n",
      " 10  所属证监会行业代码\n",
      "[行业级别] 大类行业  72000 non-null  object \n",
      " 11  10Y                    72000 non-null  float64\n",
      " 12  tbl                    72000 non-null  float64\n",
      " 13  T10Y3MM                72000 non-null  float64\n",
      " 14  gpce                   72000 non-null  float64\n",
      " 15  12m_momentum           72000 non-null  float64\n",
      " 16  industry_momentum      72000 non-null  float64\n",
      " 17  1m_reversal            72000 non-null  float64\n",
      " 18  EXRET                  72000 non-null  float64\n",
      " 19  MV                     72000 non-null  float64\n",
      " 20  year                   72000 non-null  object \n",
      " 21  DV                     72000 non-null  float64\n",
      " 22  BAS                    72000 non-null  float64\n",
      " 23  annual_TRV             72000 non-null  float64\n",
      " 24  IV                     72000 non-null  float64\n",
      " 25  MB                     72000 non-null  float64\n",
      "dtypes: float64(18), object(8)\n",
      "memory usage: 14.3+ MB\n",
      "None\n",
      "  COMNAM     TICKER     date        RET    PRC          VOL        SHROUT  \\\n",
      "0   平安银行  000001.SZ  2004-01   9.048179   9.28  165652539.0  1.409362e+09   \n",
      "1   平安银行  000001.SZ  2004-02  11.637931  10.36  416409885.0  1.409362e+09   \n",
      "2   平安银行  000001.SZ  2004-03   2.895753  10.66  359113615.0  1.409362e+09   \n",
      "3   平安银行  000001.SZ  2004-04 -12.476548   9.33  185640796.0  1.409362e+09   \n",
      "4   平安银行  000001.SZ  2004-05   3.108253   9.62   59981735.0  1.409362e+09   \n",
      "\n",
      "  CSRCIC-1 所属证监会行业名称\\n[行业级别] 门类行业 CSRCIC-2  ... industry_momentum  \\\n",
      "0        J                    金融业      J66  ...     -2.686160e+09   \n",
      "1        J                    金融业      J66  ...     -2.686160e+09   \n",
      "2        J                    金融业      J66  ...     -2.686160e+09   \n",
      "3        J                    金融业      J66  ...     -2.686160e+09   \n",
      "4        J                    金融业      J66  ...     -2.686160e+09   \n",
      "\n",
      "   1m_reversal      EXRET            MV  year            DV       BAS  \\\n",
      "0     0.515796   6.819779  1.307888e+10  2004  1.528854e+09 -0.030769   \n",
      "1     9.048179   9.385776  1.460099e+10  2004  4.379420e+09 -0.031000   \n",
      "2    11.637931   0.556331  1.502380e+10  2004  3.903233e+09 -0.022609   \n",
      "3     2.895753 -14.973905  1.314935e+10  2004  1.848258e+09 -0.027273   \n",
      "4   -12.476548   0.413397  1.355806e+10  2004  5.555516e+08 -0.029375   \n",
      "\n",
      "   annual_TRV        IV        MB  \n",
      "0    1.803115  1.724663  1.047493  \n",
      "1    1.803115  1.724663  1.047493  \n",
      "2    1.803115  1.724663  1.047493  \n",
      "3    1.803115  1.724663  1.047493  \n",
      "4    1.803115  1.724663  1.047493  \n",
      "\n",
      "[5 rows x 26 columns]\n"
     ]
    }
   ],
   "source": [
    "print(final_df.info())\n",
    "print(final_df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "e841df48",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "COMNAM                    0\n",
      "TICKER                    0\n",
      "date                      0\n",
      "RET                       0\n",
      "PRC                       0\n",
      "VOL                       0\n",
      "SHROUT                    0\n",
      "CSRCIC-1                  0\n",
      "所属证监会行业名称\\n[行业级别] 门类行业    0\n",
      "CSRCIC-2                  0\n",
      "所属证监会行业代码\\n[行业级别] 大类行业    0\n",
      "10Y                       0\n",
      "tbl                       0\n",
      "T10Y3MM                   0\n",
      "gpce                      0\n",
      "12m_momentum              0\n",
      "industry_momentum         0\n",
      "1m_reversal               0\n",
      "EXRET                     0\n",
      "MV                        0\n",
      "year                      0\n",
      "DV                        0\n",
      "BAS                       0\n",
      "annual_TRV                0\n",
      "IV                        0\n",
      "MB                        0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "missing_values_after = final_df.isnull().sum()\n",
    "print(missing_values_after)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "b65897f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df.to_csv('Data/PRC/final_merged_dataset1.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "788fe0fb",
   "metadata": {},
   "source": [
    "# Final Final Merge - including Financial Ratio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "b33af9cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "financial_ratio_df = pd.read_excel('/Users/yuyangdou/RSM/BAM/11Model/Data/PRC/年度数据.xlsx', sheet_name='Financial Ratios')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "24036000",
   "metadata": {},
   "outputs": [],
   "source": [
    "financial_ratio_df.rename(columns={\n",
    "    '市盈率': 'Book-to-Market',\n",
    "    '市净率': 'Earnings-Price',\n",
    "    '市销率': 'Sales-to-Price',\n",
    "    '股票代码': 'TICKER',\n",
    "    '股票简称': 'COMNAM'\n",
    "}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "7141362f",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df['year'] = final_df['year'].astype(int)\n",
    "financial_ratio_df['year'] = financial_ratio_df['year'].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "7b8a2824",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df['TICKER'] = final_df['TICKER'].astype(str)\n",
    "financial_ratio_df['TICKER'] = financial_ratio_df['TICKER'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "38226065",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df['COMNAM'] = final_df['COMNAM'].astype(str)\n",
    "financial_ratio_df['COMNAM'] = financial_ratio_df['COMNAM'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "0095b46f",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = pd.merge(final_df, financial_ratio_df[['year', 'TICKER', 'COMNAM', 'Book-to-Market', 'Earnings-Price', 'Sales-to-Price']], on=['year', 'TICKER', 'COMNAM'], how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "2fb1fa82",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "COMNAM                        0\n",
      "TICKER                        0\n",
      "date                          0\n",
      "RET                           0\n",
      "PRC                           0\n",
      "VOL                           0\n",
      "SHROUT                        0\n",
      "CSRCIC-1                      0\n",
      "所属证监会行业名称\\n[行业级别] 门类行业        0\n",
      "CSRCIC-2                      0\n",
      "所属证监会行业代码\\n[行业级别] 大类行业        0\n",
      "10Y                           0\n",
      "tbl                           0\n",
      "T10Y3MM                       0\n",
      "gpce                          0\n",
      "12m_momentum                  0\n",
      "industry_momentum             0\n",
      "1m_reversal                   0\n",
      "EXRET                         0\n",
      "MV                            0\n",
      "year                          0\n",
      "DV                            0\n",
      "BAS                           0\n",
      "annual_TRV                    0\n",
      "IV                            0\n",
      "MB                            0\n",
      "Book-to-Market            22920\n",
      "Earnings-Price            22920\n",
      "Sales-to-Price            22920\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "missing_values_after_merge = final_df.isnull().sum()\n",
    "print(missing_values_after_merge)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "7f5dd825",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df['year'] = final_df['year'].astype(int)\n",
    "columns_to_fill = ['Book-to-Market', 'Earnings-Price', 'Sales-to-Price']\n",
    "\n",
    "for col in columns_to_fill:\n",
    "    final_df[col] = final_df.groupby('TICKER')[col].transform(lambda x: x.fillna(x.median()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "6bcec45b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "COMNAM                      0\n",
      "TICKER                      0\n",
      "date                        0\n",
      "RET                         0\n",
      "PRC                         0\n",
      "VOL                         0\n",
      "SHROUT                      0\n",
      "CSRCIC-1                    0\n",
      "所属证监会行业名称\\n[行业级别] 门类行业      0\n",
      "CSRCIC-2                    0\n",
      "所属证监会行业代码\\n[行业级别] 大类行业      0\n",
      "10Y                         0\n",
      "tbl                         0\n",
      "T10Y3MM                     0\n",
      "gpce                        0\n",
      "12m_momentum                0\n",
      "industry_momentum           0\n",
      "1m_reversal                 0\n",
      "EXRET                       0\n",
      "MV                          0\n",
      "year                        0\n",
      "DV                          0\n",
      "BAS                         0\n",
      "annual_TRV                  0\n",
      "IV                          0\n",
      "MB                          0\n",
      "Book-to-Market            240\n",
      "Earnings-Price            240\n",
      "Sales-to-Price            240\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "missing_values_after_merge = final_df.isnull().sum()\n",
    "print(missing_values_after_merge)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "72f31241",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          TICKER COMNAM\n",
      "62400  601988.SH   中国银行\n",
      "      COMNAM     TICKER     date       RET     PRC           VOL  \\\n",
      "62400   中国银行  601988.SH  2004-01  0.000000  15.615  2.951812e+08   \n",
      "62401   中国银行  601988.SH  2004-02  0.194099  15.615  2.951812e+08   \n",
      "62402   中国银行  601988.SH  2004-03  0.194099  15.615  2.951812e+08   \n",
      "62403   中国银行  601988.SH  2004-04  0.194099  15.615  2.951812e+08   \n",
      "62404   中国银行  601988.SH  2004-05  0.194099  15.615  2.951812e+08   \n",
      "...      ...        ...      ...       ...     ...           ...   \n",
      "62635   中国银行  601988.SH  2023-08 -4.092072   3.750  3.607017e+09   \n",
      "62636   中国银行  601988.SH  2023-09  0.533333   3.770  3.194611e+09   \n",
      "62637   中国银行  601988.SH  2023-10  3.448276   3.900  4.197437e+09   \n",
      "62638   中国银行  601988.SH  2023-11  2.307692   3.990  3.401007e+09   \n",
      "62639   中国银行  601988.SH  2023-12  0.000000   3.990  4.158012e+09   \n",
      "\n",
      "             SHROUT CSRCIC-1 所属证监会行业名称\\n[行业级别] 门类行业 CSRCIC-2  ...  \\\n",
      "62400  1.502802e+09        J                    金融业      J66  ...   \n",
      "62401  1.502802e+09        J                    金融业      J66  ...   \n",
      "62402  1.502802e+09        J                    金融业      J66  ...   \n",
      "62403  1.502802e+09        J                    金融业      J66  ...   \n",
      "62404  1.502802e+09        J                    金融业      J66  ...   \n",
      "...             ...      ...                    ...      ...  ...   \n",
      "62635  2.943878e+11        J                    金融业      J66  ...   \n",
      "62636  2.943878e+11        J                    金融业      J66  ...   \n",
      "62637  2.943878e+11        J                    金融业      J66  ...   \n",
      "62638  2.943878e+11        J                    金融业      J66  ...   \n",
      "62639  2.943878e+11        J                    金融业      J66  ...   \n",
      "\n",
      "                 MV  year            DV       BAS  annual_TRV        IV  \\\n",
      "62400  2.346625e+10  2004  0.000000e+00  0.000000    0.000000  1.218568   \n",
      "62401  2.346625e+10  2004  2.592640e+09  0.000000    0.000000  1.218568   \n",
      "62402  2.346625e+10  2004  1.681611e+09  0.000000    0.000000  1.218568   \n",
      "62403  2.346625e+10  2004  1.146237e+09  0.000000    0.000000  1.218568   \n",
      "62404  2.346625e+10  2004  3.940731e+08  0.000000    0.000000  1.218568   \n",
      "...             ...   ...           ...       ...         ...       ...   \n",
      "62635  1.103954e+12  2023  1.374638e+10 -1.576522    1.182201  1.218568   \n",
      "62636  1.109842e+12  2023  1.193052e+10 -1.943500    1.182201  1.218568   \n",
      "62637  1.148112e+12  2023  1.637273e+10 -3.106471    1.182201  1.218568   \n",
      "62638  1.174607e+12  2023  1.340409e+10 -5.071364    1.182201  1.218568   \n",
      "62639  1.174607e+12  2023  1.655232e+10 -5.823333    1.182201  1.218568   \n",
      "\n",
      "             MB  Book-to-Market  Earnings-Price  Sales-to-Price  \n",
      "62400  0.540132             NaN             NaN             NaN  \n",
      "62401  0.540132             NaN             NaN             NaN  \n",
      "62402  0.540132             NaN             NaN             NaN  \n",
      "62403  0.540132             NaN             NaN             NaN  \n",
      "62404  0.540132             NaN             NaN             NaN  \n",
      "...         ...             ...             ...             ...  \n",
      "62635  0.540132             NaN             NaN             NaN  \n",
      "62636  0.540132             NaN             NaN             NaN  \n",
      "62637  0.540132             NaN             NaN             NaN  \n",
      "62638  0.540132             NaN             NaN             NaN  \n",
      "62639  0.540132             NaN             NaN             NaN  \n",
      "\n",
      "[240 rows x 29 columns]\n"
     ]
    }
   ],
   "source": [
    "missing_values_df = final_df[final_df[['Book-to-Market', 'Earnings-Price', 'Sales-to-Price']].isnull().any(axis=1)]\n",
    "print(missing_values_df[['TICKER', 'COMNAM']].drop_duplicates())\n",
    "print(missing_values_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "6ac9021e",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "data_to_fill = {\n",
    "    2004: {'Book-to-Market': None, 'Earnings-Price': None, 'Sales-to-Price': None},\n",
    "    2005: {'Book-to-Market': None, 'Earnings-Price': None, 'Sales-to-Price': None},\n",
    "    2006: {'Book-to-Market': None, 'Earnings-Price': None, 'Sales-to-Price': None},\n",
    "    2007: {'Book-to-Market': None, 'Earnings-Price': None, 'Sales-to-Price': None},\n",
    "    2008: {'Book-to-Market': None, 'Earnings-Price': None, 'Sales-to-Price': None},\n",
    "    2009: {'Book-to-Market': None, 'Earnings-Price': None, 'Sales-to-Price': None},\n",
    "    2010: {'Book-to-Market': None, 'Earnings-Price': None, 'Sales-to-Price': None},\n",
    "    2011: {'Book-to-Market': None, 'Earnings-Price': None, 'Sales-to-Price': None},\n",
    "    2012: {'Book-to-Market': None, 'Earnings-Price': None, 'Sales-to-Price': None},\n",
    "    2013: {'Book-to-Market': None, 'Earnings-Price': None, 'Sales-to-Price': None},\n",
    "    2014: {'Book-to-Market': None, 'Earnings-Price': None, 'Sales-to-Price': None},\n",
    "    2015: {'Book-to-Market': None, 'Earnings-Price': None, 'Sales-to-Price': None},\n",
    "    2016: {'Book-to-Market': None, 'Earnings-Price': None, 'Sales-to-Price': None},\n",
    "    2017: {'Book-to-Market': 26.7646, 'Earnings-Price': 1.6515, 'Sales-to-Price': 9.3918},\n",
    "    2018: {'Book-to-Market': 23.9463, 'Earnings-Price': 1.0478, 'Sales-to-Price': 6.9656},\n",
    "    2019: {'Book-to-Market': 22.5103, 'Earnings-Price': 1.6601, 'Sales-to-Price': 6.9066},\n",
    "    2020: {'Book-to-Market': 17.5073, 'Earnings-Price': 1.6623, 'Sales-to-Price': 5.3399},\n",
    "    2021: {'Book-to-Market': 10.8757, 'Earnings-Price': 1.3493, 'Sales-to-Price': 3.1524},\n",
    "    2022: {'Book-to-Market': 12.1351, 'Earnings-Price': 1.0868, 'Sales-to-Price': 2.7993},\n",
    "    2023: {'Book-to-Market': 16.7234, 'Earnings-Price': 1.3092, 'Sales-to-Price': 3.9163}\n",
    "}\n",
    "\n",
    "for year, values in data_to_fill.items():\n",
    "    final_df.loc[(final_df['TICKER'] == '601881.SH') & (final_df['year'] == year), 'Book-to-Market'] = values['Book-to-Market']\n",
    "    final_df.loc[(final_df['TICKER'] == '601881.SH') & (final_df['year'] == year), 'Earnings-Price'] = values['Earnings-Price']\n",
    "    final_df.loc[(final_df['TICKER'] == '601881.SH') & (final_df['year'] == year), 'Sales-to-Price'] = values['Sales-to-Price']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "286b92dc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "COMNAM                      0\n",
      "TICKER                      0\n",
      "date                        0\n",
      "RET                         0\n",
      "PRC                         0\n",
      "VOL                         0\n",
      "SHROUT                      0\n",
      "CSRCIC-1                    0\n",
      "所属证监会行业名称\\n[行业级别] 门类行业      0\n",
      "CSRCIC-2                    0\n",
      "所属证监会行业代码\\n[行业级别] 大类行业      0\n",
      "10Y                         0\n",
      "tbl                         0\n",
      "T10Y3MM                     0\n",
      "gpce                        0\n",
      "12m_momentum                0\n",
      "industry_momentum           0\n",
      "1m_reversal                 0\n",
      "EXRET                       0\n",
      "MV                          0\n",
      "year                        0\n",
      "DV                          0\n",
      "BAS                         0\n",
      "annual_TRV                  0\n",
      "IV                          0\n",
      "MB                          0\n",
      "Book-to-Market            396\n",
      "Earnings-Price            396\n",
      "Sales-to-Price            396\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "missing_values_after_fill = final_df.isnull().sum()\n",
    "print(missing_values_after_fill)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "81708527",
   "metadata": {},
   "outputs": [],
   "source": [
    "company_medians = final_df[final_df['TICKER'] == '601881.SH'][['Book-to-Market', 'Earnings-Price', 'Sales-to-Price']].median()\n",
    "\n",
    "final_df['Book-to-Market'].fillna(company_medians['Book-to-Market'], inplace=True)\n",
    "final_df['Earnings-Price'].fillna(company_medians['Earnings-Price'], inplace=True)\n",
    "final_df['Sales-to-Price'].fillna(company_medians['Sales-to-Price'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "3ae923fd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "COMNAM                    0\n",
      "TICKER                    0\n",
      "date                      0\n",
      "RET                       0\n",
      "PRC                       0\n",
      "VOL                       0\n",
      "SHROUT                    0\n",
      "CSRCIC-1                  0\n",
      "所属证监会行业名称\\n[行业级别] 门类行业    0\n",
      "CSRCIC-2                  0\n",
      "所属证监会行业代码\\n[行业级别] 大类行业    0\n",
      "10Y                       0\n",
      "tbl                       0\n",
      "T10Y3MM                   0\n",
      "gpce                      0\n",
      "12m_momentum              0\n",
      "industry_momentum         0\n",
      "1m_reversal               0\n",
      "EXRET                     0\n",
      "MV                        0\n",
      "year                      0\n",
      "DV                        0\n",
      "BAS                       0\n",
      "annual_TRV                0\n",
      "IV                        0\n",
      "MB                        0\n",
      "Book-to-Market            0\n",
      "Earnings-Price            0\n",
      "Sales-to-Price            0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "missing_values_after_fill = final_df.isnull().sum()\n",
    "print(missing_values_after_fill)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "3a435736",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df.to_csv('Data/PRC/FINAL_DF.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ba6ec07",
   "metadata": {},
   "source": [
    "# Balanced Panel Filter: 300 = 300 (no missing value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "a14c72a2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The number of companies with exactly 240 records is: 300\n",
      "Companies with exactly 240 records are:\n",
      "['平安银行', '北京银行', '新城控股', '工业富联', '中国化学', '中国国航', '恒立液压', '中国神华', '中信建投', '信达证券', '春秋航空', '隆基绿能', '南京银行', '大秦铁路', '招商证券', '宝丰能源', '东方证券', '兴业银行', '中国铁建', '中国海油', '国泰君安', '中国太保', '中国铝业', '工商银行', '中国中铁', '兴业证券', '三六零', '新华保险', '交通银行', '中国人保', '中国平安', '农业银行', '广汽集团', '红塔证券', '上海银行', '陕西煤业', '中国移动', '杭州银行', '万科A', '海尔智家', '福耀玻璃', '绿地控股', '青岛啤酒', '用友网络', '海螺水泥', '长电科技', '恒生电子', '山东黄金', '贵州茅台', '海南机场', '中金黄金', '士兰微', '通威股份', '片仔癀', '华鲁恒升', '川投能源', '爱旭股份', '江苏银行', '华域汽车', '中泰证券', '三峡能源', '长江电力', '航发动力', '伊利股份', '国投电力', '东方电气', '宝信软件', '海通证券', '山西汾酒', '新奥股份', '国电电力', '中航沈飞', '锦江酒店', '闻泰科技', '上海医药', '明阳智能', '中国中冶', '德业股份', '兆易创新', '晨光股份', '欧派家居', '福斯特', '华友钴业', '璞泰来', '韦尔股份', '科沃斯', '万泰生物', '今世缘', '斯达半导', '海天味业', '合盛硅业', '药明康德', '公牛集团', '洛阳钼业', '东鹏饮料', '中国人寿', '澜起科技', '天合光能', '奇安信-U', '华润微', '华熙生物', '大全能源', '联影医疗', '寒武纪-U', '晶科能源', '时代电气', '沪硅产业', '金山办公', '凯赛生物', '海光信息', '传音控股', '中微公司', '中科曙光', '中信银行', '中金公司', '中国重工', '京沪高铁', '中海油服', '中国交建', '星宇股份', '光大证券', '中国中车', '中国电信', '潞安环能', '中国卫通', '拓普集团', '华泰证券', '中国电建', '中国建筑', '邮储银行', '长城汽车', '光大银行', '成都银行', '中国石油', '紫金矿业', '中国银行', '中国核电', '建设银行', '中远海控', '浙商银行', '方正证券', '中煤能源', '福莱特', '中国中免', '中国银河', '浙商证券', '正泰电器', '招商轮船', '中国能建', '国电南瑞', '中航机载', '江西铜业', '海大集团', '东方雨虹', '上海莱士', '歌尔股份', '大华股份', '科大讯飞', '金风科技', '纳思达', '中航光电', '宁波银行', 'TCL中环', '国轩高科', '三花智控', '紫光国微', '分众传媒', '华兰生物', '洋河股份', '顺丰控股', '凯莱英', '北方华创', '国信证券', '牧原股份', '天赐材料', '卫星化学', '以岭药业', '龙佰集团', '比亚迪', '三七互娱', '荣盛石化', '立讯精密', '天齐锂业', '赣锋锂业', '晶澳科技', '海康威视', '广联达', '新和成', '招商蛇口', '龙源电力', '华润三九', '长安汽车', '中油资本', '古井贡酒', '泸州老窖', '云南白药', '徐工机械', '藏格矿业', '潍柴动力', '美的集团', '东方盛虹', '申万宏源', '中联重科', 'TCL科技', '华侨城A', '中兴通讯', '格力电器', '长春高新', '中信特钢', '新希望', '山西焦煤', '浪潮信息', '华东医药', '紫光股份', '双汇发展', '天山股份', '五粮液', '京东方A', '一汽解放', '盐湖股份', '北新建材', '广发证券', '中航西飞', '振华科技', '恩捷股份', '视源股份', '恒力石化', '中国联通', '四川路桥', '招商银行', '三一重工', '中信证券', '南方航空', '中国石化', '华能水电', '浙能电力', '宝钢股份', '上港集团', '民生银行', '华夏银行', '华能国际', '包钢股份', '上海机场', '保利发展', '国投资本', '深南电路', '同仁堂', '白云山', '万华化学', '恒瑞医药', '圆通速递', '南山铝业', '复星医药', '兖矿能源', '生益科技', '中国巨石', '中国船舶', '重庆啤酒', '中国东航', '北方稀土', '上汽集团', '特变电工', '浦发银行', '华大九天', '金龙鱼', '华利集团', '泰格医药', '晶盛机电', '中际旭创', '阳光电源', '北京君正', '沃森生物', '汇川技术', '智飞生物', '东方财富', '同花顺', '爱尔眼科', '亿纬锂能', '中国广核', '鹏鼎控股', '德赛西威', '三环集团', '芒果超媒', '蓝思科技', '康龙化成', '贝泰妮', '中伟股份', '爱美客', '卓胜微', '锦浪科技', '迈瑞医疗', '迈为股份', '先导智能', '宁德时代', '圣邦股份', '亿联网络', '温氏股份', '中科创达', '深信服', '中芯国际']\n"
     ]
    }
   ],
   "source": [
    "company_counts = final_df['COMNAM'].value_counts()\n",
    "\n",
    "complete_companies = company_counts[company_counts == 240]\n",
    "\n",
    "print(f\"The number of companies with exactly 240 records is: {complete_companies.shape[0]}\")\n",
    "print(\"Companies with exactly 240 records are:\")\n",
    "print(complete_companies.index.tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f2c8f71",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}