financial_data_prep.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Financial Data Preprocessing\n",
    "\n",
    "In this notebook we process financial data. \n",
    "\n",
    "Then we create feature space, which consists of: 1. ESG features and 2. financial features.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "import numpy as np\n",
    "from pandas_datareader import data as pdr\n",
    "import yfinance as yf\n",
    "import pickle\n",
    "from lxml import html\n",
    "from io import StringIO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = '/Users/ChrisQAQ/Downloads/ICAF_2020/data1/'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Ticker File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get gvkey list txt file\n",
    "gvkey_ref = pd.read_excel(path+'company_list/gvkey_ticker_reference.xlsx') \n",
    "# get through WRDS SAS studio .. from COMPA.FUNDA table\n",
    "\n",
    "gvkey_list = gvkey_ref.gvkey.tolist()\n",
    "gvkey_list = [str(key) for key in gvkey_list]\n",
    "gvkey_prcd = []\n",
    "for key in gvkey_list:\n",
    "    n = len(key)\n",
    "    if n<6:\n",
    "        key = '0'*(6-n)+key\n",
    "    gvkey_prcd.append(key)\n",
    "\n",
    "gvkey_txt = ('\\n').join(gvkey_prcd)\n",
    "with open(path + 'company_list/scholar_data_gvkey_list.txt', 'w') as file:\n",
    "    file.write(gvkey_txt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 218,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# of unique stock tickers: 93\n"
     ]
    }
   ],
   "source": [
    "# get finance features from yahoo finance\n",
    "scholar_data_ticker_list = open(path+'company_list/scholar_data_ticker_list.txt').readlines()\n",
    "scholar_data_ticker_list = [tic.replace('\\n','') for tic in scholar_data_ticker_list]\n",
    "print(f'# of unique stock tickers: {len(scholar_data_ticker_list)}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pull Data from WRDS Database\n",
    "\n",
    "* use WRDS tools to query financial indicators\n",
    "\n",
    "* table1: Financial Ratios Firm Level by WRDS\n",
    "* table2: Compustat Daily Updates - Fundamentals Quarterly\n",
    "\n",
    "\n",
    "* input: scholar_data_ticker_list.txt, scholar_data_gvkey_list.txt\n",
    "* output: Financial Ratios Firm Level by WRDS.csv, Compustat Daily Updates - Fundamentals Quarterly.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "metadata": {},
   "outputs": [],
   "source": [
    "ratio_raw = pd.read_csv(path+'financial/WRDS/Financial Ratios Firm Level by WRDS.csv')\n",
    "funda_raw = pd.read_csv(path+'financial/WRDS/Compustat Daily Updates - Fundamentals Quarterly.csv')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Ratio Data Prep"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>gvkey</th>\n",
       "      <th>adate</th>\n",
       "      <th>qdate</th>\n",
       "      <th>public_date</th>\n",
       "      <th>pe_exi</th>\n",
       "      <th>pe_inc</th>\n",
       "      <th>ps</th>\n",
       "      <th>npm</th>\n",
       "      <th>roa</th>\n",
       "      <th>roe</th>\n",
       "      <th>de_ratio</th>\n",
       "      <th>quick_ratio</th>\n",
       "      <th>curr_ratio</th>\n",
       "      <th>ptb</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1072</td>\n",
       "      <td>19950331.0</td>\n",
       "      <td>19950630</td>\n",
       "      <td>19951031</td>\n",
       "      <td>30.822</td>\n",
       "      <td>30.822</td>\n",
       "      <td>2.585</td>\n",
       "      <td>0.082</td>\n",
       "      <td>0.287</td>\n",
       "      <td>0.056</td>\n",
       "      <td>0.494</td>\n",
       "      <td>1.209</td>\n",
       "      <td>2.256</td>\n",
       "      <td>5.333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1072</td>\n",
       "      <td>19950331.0</td>\n",
       "      <td>19950930</td>\n",
       "      <td>19951130</td>\n",
       "      <td>23.566</td>\n",
       "      <td>23.566</td>\n",
       "      <td>2.263</td>\n",
       "      <td>0.094</td>\n",
       "      <td>0.316</td>\n",
       "      <td>0.129</td>\n",
       "      <td>0.472</td>\n",
       "      <td>1.317</td>\n",
       "      <td>2.382</td>\n",
       "      <td>4.296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1072</td>\n",
       "      <td>19950331.0</td>\n",
       "      <td>19950930</td>\n",
       "      <td>19951231</td>\n",
       "      <td>21.721</td>\n",
       "      <td>21.721</td>\n",
       "      <td>2.086</td>\n",
       "      <td>0.094</td>\n",
       "      <td>0.316</td>\n",
       "      <td>0.129</td>\n",
       "      <td>0.472</td>\n",
       "      <td>1.317</td>\n",
       "      <td>2.382</td>\n",
       "      <td>3.960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1072</td>\n",
       "      <td>19950331.0</td>\n",
       "      <td>19950930</td>\n",
       "      <td>19960131</td>\n",
       "      <td>21.516</td>\n",
       "      <td>21.516</td>\n",
       "      <td>2.067</td>\n",
       "      <td>0.094</td>\n",
       "      <td>0.316</td>\n",
       "      <td>0.129</td>\n",
       "      <td>0.472</td>\n",
       "      <td>1.317</td>\n",
       "      <td>2.382</td>\n",
       "      <td>3.922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1072</td>\n",
       "      <td>19950331.0</td>\n",
       "      <td>19951231</td>\n",
       "      <td>19960229</td>\n",
       "      <td>17.626</td>\n",
       "      <td>17.626</td>\n",
       "      <td>1.829</td>\n",
       "      <td>0.103</td>\n",
       "      <td>0.337</td>\n",
       "      <td>0.189</td>\n",
       "      <td>0.456</td>\n",
       "      <td>1.360</td>\n",
       "      <td>2.456</td>\n",
       "      <td>3.504</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gvkey       adate     qdate  public_date  pe_exi  pe_inc     ps    npm  \\\n",
       "0   1072  19950331.0  19950630     19951031  30.822  30.822  2.585  0.082   \n",
       "1   1072  19950331.0  19950930     19951130  23.566  23.566  2.263  0.094   \n",
       "2   1072  19950331.0  19950930     19951231  21.721  21.721  2.086  0.094   \n",
       "3   1072  19950331.0  19950930     19960131  21.516  21.516  2.067  0.094   \n",
       "4   1072  19950331.0  19951231     19960229  17.626  17.626  1.829  0.103   \n",
       "\n",
       "     roa    roe  de_ratio  quick_ratio  curr_ratio    ptb  \n",
       "0  0.287  0.056     0.494        1.209       2.256  5.333  \n",
       "1  0.316  0.129     0.472        1.317       2.382  4.296  \n",
       "2  0.316  0.129     0.472        1.317       2.382  3.960  \n",
       "3  0.316  0.129     0.472        1.317       2.382  3.922  \n",
       "4  0.337  0.189     0.456        1.360       2.456  3.504  "
      ]
     },
     "execution_count": 229,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratio_raw.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "metadata": {},
   "outputs": [],
   "source": [
    "ratio_raw['month'] = ratio_raw['public_date'].apply(lambda x:str(x)[:4]+'-'+str(x)[4:6])\n",
    "ratio_raw = pd.merge(ratio_raw, gvkey_to_tic[['tic','Global Company Key']], how='left',\n",
    "                     left_on='gvkey', right_on='Global Company Key')\n",
    "ratio_prcd = ratio_raw[['month','tic','pe_inc','ptb','ps','roa','roe','quick_ratio','curr_ratio','npm','de_ratio']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>month</th>\n",
       "      <th>tic</th>\n",
       "      <th>pe_inc</th>\n",
       "      <th>ptb</th>\n",
       "      <th>ps</th>\n",
       "      <th>roa</th>\n",
       "      <th>roe</th>\n",
       "      <th>quick_ratio</th>\n",
       "      <th>curr_ratio</th>\n",
       "      <th>npm</th>\n",
       "      <th>de_ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1995-10</td>\n",
       "      <td>AVX</td>\n",
       "      <td>30.822</td>\n",
       "      <td>5.333</td>\n",
       "      <td>2.585</td>\n",
       "      <td>0.287</td>\n",
       "      <td>0.056</td>\n",
       "      <td>1.209</td>\n",
       "      <td>2.256</td>\n",
       "      <td>0.082</td>\n",
       "      <td>0.494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1995-11</td>\n",
       "      <td>AVX</td>\n",
       "      <td>23.566</td>\n",
       "      <td>4.296</td>\n",
       "      <td>2.263</td>\n",
       "      <td>0.316</td>\n",
       "      <td>0.129</td>\n",
       "      <td>1.317</td>\n",
       "      <td>2.382</td>\n",
       "      <td>0.094</td>\n",
       "      <td>0.472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1995-12</td>\n",
       "      <td>AVX</td>\n",
       "      <td>21.721</td>\n",
       "      <td>3.960</td>\n",
       "      <td>2.086</td>\n",
       "      <td>0.316</td>\n",
       "      <td>0.129</td>\n",
       "      <td>1.317</td>\n",
       "      <td>2.382</td>\n",
       "      <td>0.094</td>\n",
       "      <td>0.472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1996-01</td>\n",
       "      <td>AVX</td>\n",
       "      <td>21.516</td>\n",
       "      <td>3.922</td>\n",
       "      <td>2.067</td>\n",
       "      <td>0.316</td>\n",
       "      <td>0.129</td>\n",
       "      <td>1.317</td>\n",
       "      <td>2.382</td>\n",
       "      <td>0.094</td>\n",
       "      <td>0.472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1996-02</td>\n",
       "      <td>AVX</td>\n",
       "      <td>17.626</td>\n",
       "      <td>3.504</td>\n",
       "      <td>1.829</td>\n",
       "      <td>0.337</td>\n",
       "      <td>0.189</td>\n",
       "      <td>1.360</td>\n",
       "      <td>2.456</td>\n",
       "      <td>0.103</td>\n",
       "      <td>0.456</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     month  tic  pe_inc    ptb     ps    roa    roe  quick_ratio  curr_ratio  \\\n",
       "0  1995-10  AVX  30.822  5.333  2.585  0.287  0.056        1.209       2.256   \n",
       "1  1995-11  AVX  23.566  4.296  2.263  0.316  0.129        1.317       2.382   \n",
       "2  1995-12  AVX  21.721  3.960  2.086  0.316  0.129        1.317       2.382   \n",
       "3  1996-01  AVX  21.516  3.922  2.067  0.316  0.129        1.317       2.382   \n",
       "4  1996-02  AVX  17.626  3.504  1.829  0.337  0.189        1.360       2.456   \n",
       "\n",
       "     npm  de_ratio  \n",
       "0  0.082     0.494  \n",
       "1  0.094     0.472  \n",
       "2  0.094     0.472  \n",
       "3  0.094     0.472  \n",
       "4  0.103     0.456  "
      ]
     },
     "execution_count": 231,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratio_prcd.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1990-01\n",
      "2019-01\n"
     ]
    }
   ],
   "source": [
    "print(ratio_prcd.month.min())\n",
    "print(ratio_prcd.month.max())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fundamental Data Prep"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>gvkey</th>\n",
       "      <th>datadate</th>\n",
       "      <th>fyearq</th>\n",
       "      <th>fqtr</th>\n",
       "      <th>indfmt</th>\n",
       "      <th>consol</th>\n",
       "      <th>popsrc</th>\n",
       "      <th>datafmt</th>\n",
       "      <th>tic</th>\n",
       "      <th>conm</th>\n",
       "      <th>ajexq</th>\n",
       "      <th>curcdq</th>\n",
       "      <th>datacqtr</th>\n",
       "      <th>datafqtr</th>\n",
       "      <th>epspiq</th>\n",
       "      <th>costat</th>\n",
       "      <th>prccq</th>\n",
       "      <th>gsector</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1072</td>\n",
       "      <td>19900331</td>\n",
       "      <td>1990</td>\n",
       "      <td>1</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>AVX</td>\n",
       "      <td>AVX CORP</td>\n",
       "      <td>2.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>1990Q1</td>\n",
       "      <td>1990Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>I</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1072</td>\n",
       "      <td>19900630</td>\n",
       "      <td>1990</td>\n",
       "      <td>2</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>AVX</td>\n",
       "      <td>AVX CORP</td>\n",
       "      <td>2.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>1990Q2</td>\n",
       "      <td>1990Q2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>I</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1072</td>\n",
       "      <td>19900930</td>\n",
       "      <td>1990</td>\n",
       "      <td>3</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>AVX</td>\n",
       "      <td>AVX CORP</td>\n",
       "      <td>2.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>1990Q3</td>\n",
       "      <td>1990Q3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>I</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1072</td>\n",
       "      <td>19901231</td>\n",
       "      <td>1990</td>\n",
       "      <td>4</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>AVX</td>\n",
       "      <td>AVX CORP</td>\n",
       "      <td>2.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>1990Q4</td>\n",
       "      <td>1990Q4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>I</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1072</td>\n",
       "      <td>19910331</td>\n",
       "      <td>1991</td>\n",
       "      <td>1</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>AVX</td>\n",
       "      <td>AVX CORP</td>\n",
       "      <td>2.0</td>\n",
       "      <td>USD</td>\n",
       "      <td>1991Q1</td>\n",
       "      <td>1991Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>I</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gvkey  datadate  fyearq  fqtr indfmt consol popsrc datafmt  tic      conm  \\\n",
       "0   1072  19900331    1990     1   INDL      C      D     STD  AVX  AVX CORP   \n",
       "1   1072  19900630    1990     2   INDL      C      D     STD  AVX  AVX CORP   \n",
       "2   1072  19900930    1990     3   INDL      C      D     STD  AVX  AVX CORP   \n",
       "3   1072  19901231    1990     4   INDL      C      D     STD  AVX  AVX CORP   \n",
       "4   1072  19910331    1991     1   INDL      C      D     STD  AVX  AVX CORP   \n",
       "\n",
       "   ajexq curcdq datacqtr datafqtr  epspiq costat  prccq  gsector  \n",
       "0    2.0    USD   1990Q1   1990Q1     NaN      I    NaN       45  \n",
       "1    2.0    USD   1990Q2   1990Q2     NaN      I    NaN       45  \n",
       "2    2.0    USD   1990Q3   1990Q3     NaN      I    NaN       45  \n",
       "3    2.0    USD   1990Q4   1990Q4     NaN      I    NaN       45  \n",
       "4    2.0    USD   1991Q1   1991Q1     NaN      I    NaN       45  "
      ]
     },
     "execution_count": 233,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "funda_raw.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "metadata": {},
   "outputs": [],
   "source": [
    "funda_raw['yearmonth'] = funda_raw['datadate'].apply(lambda x:str(x)[:4]+'-'+str(x)[4:6])\n",
    "# turn quarter data into month data by filling the other month with the same data\n",
    "funda_raw['year'] = funda_raw['yearmonth'].apply(lambda x:x[:4])\n",
    "funda_raw['month'] = funda_raw['yearmonth'].apply(lambda x:int(x[-2:]))\n",
    "funda_raw['monthset'] = funda_raw['month'].apply(lambda x:[((x-1)//3)*3+1,((x-1)//3)*3+2,((x-1)//3)*3+3])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "metadata": {},
   "outputs": [],
   "source": [
    "def Get_Exd_Data(df, lst_col):\n",
    "    return pd.DataFrame({\n",
    "        col:np.repeat(df[col].values, df[lst_col].str.len())\n",
    "        for col in df.columns.difference([lst_col])\n",
    "    }).assign(**{lst_col:np.concatenate(df[lst_col].values)})[df.columns.tolist()]\n",
    "\n",
    "exd_funda = Get_Exd_Data(funda_raw, 'monthset')\n",
    "def Exd_Month_Str(i):\n",
    "    if len(str(i))==1:\n",
    "        return '0'+str(i)\n",
    "    else:return str(i)\n",
    "exd_funda['monthset'] = exd_funda['monthset'].apply(Exd_Month_Str)\n",
    "exd_funda['month'] = exd_funda['year']+'-'+exd_funda['monthset']\n",
    "funda_prcd = exd_funda[['month','tic','gsector','epspiq','prccq']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>month</th>\n",
       "      <th>tic</th>\n",
       "      <th>gsector</th>\n",
       "      <th>epspiq</th>\n",
       "      <th>prccq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>26359</th>\n",
       "      <td>2019-11</td>\n",
       "      <td>EPAM</td>\n",
       "      <td>45</td>\n",
       "      <td>1.35</td>\n",
       "      <td>212.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26360</th>\n",
       "      <td>2019-12</td>\n",
       "      <td>EPAM</td>\n",
       "      <td>45</td>\n",
       "      <td>1.35</td>\n",
       "      <td>212.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26361</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>EPAM</td>\n",
       "      <td>45</td>\n",
       "      <td>1.55</td>\n",
       "      <td>185.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26362</th>\n",
       "      <td>2020-02</td>\n",
       "      <td>EPAM</td>\n",
       "      <td>45</td>\n",
       "      <td>1.55</td>\n",
       "      <td>185.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26363</th>\n",
       "      <td>2020-03</td>\n",
       "      <td>EPAM</td>\n",
       "      <td>45</td>\n",
       "      <td>1.55</td>\n",
       "      <td>185.66</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         month   tic  gsector  epspiq   prccq\n",
       "26359  2019-11  EPAM       45    1.35  212.16\n",
       "26360  2019-12  EPAM       45    1.35  212.16\n",
       "26361  2020-01  EPAM       45    1.55  185.66\n",
       "26362  2020-02  EPAM       45    1.55  185.66\n",
       "26363  2020-03  EPAM       45    1.55  185.66"
      ]
     },
     "execution_count": 236,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "funda_prcd.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1990-01\n",
      "2020-03\n"
     ]
    }
   ],
   "source": [
    "print(funda_prcd.month.min())\n",
    "print(funda_prcd.month.max())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Price & Monthly Return Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 287,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>tic</th>\n",
       "      <th>adj_closed_price</th>\n",
       "      <th>monthly_return</th>\n",
       "      <th>forward_monthly_return</th>\n",
       "      <th>paper_month</th>\n",
       "      <th>month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2002-03-01</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>1.47</td>\n",
       "      <td>0.088889</td>\n",
       "      <td>-0.117647</td>\n",
       "      <td>2002-02</td>\n",
       "      <td>2002-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2002-04-01</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>1.50</td>\n",
       "      <td>0.020408</td>\n",
       "      <td>0.088889</td>\n",
       "      <td>2002-03</td>\n",
       "      <td>2002-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2002-05-01</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>1.44</td>\n",
       "      <td>-0.040000</td>\n",
       "      <td>0.020408</td>\n",
       "      <td>2002-04</td>\n",
       "      <td>2002-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2002-06-01</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>1.10</td>\n",
       "      <td>-0.236111</td>\n",
       "      <td>-0.040000</td>\n",
       "      <td>2002-05</td>\n",
       "      <td>2002-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2002-07-01</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>0.95</td>\n",
       "      <td>-0.136364</td>\n",
       "      <td>-0.236111</td>\n",
       "      <td>2002-06</td>\n",
       "      <td>2002-07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date   tic  adj_closed_price  monthly_return  forward_monthly_return  \\\n",
       "0  2002-03-01  AAPL              1.47        0.088889               -0.117647   \n",
       "1  2002-04-01  AAPL              1.50        0.020408                0.088889   \n",
       "2  2002-05-01  AAPL              1.44       -0.040000                0.020408   \n",
       "3  2002-06-01  AAPL              1.10       -0.236111               -0.040000   \n",
       "4  2002-07-01  AAPL              0.95       -0.136364               -0.236111   \n",
       "\n",
       "  paper_month    month  \n",
       "0     2002-02  2002-03  \n",
       "1     2002-03  2002-04  \n",
       "2     2002-04  2002-05  \n",
       "3     2002-05  2002-06  \n",
       "4     2002-06  2002-07  "
      ]
     },
     "execution_count": 287,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_prcd = pd.read_csv(path+'financial/close_price_processed_monthly.csv')\n",
    "price_prcd.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "metadata": {},
   "outputs": [],
   "source": [
    "startdate = '1990-01-01'\n",
    "enddate = '2020-03-31'\n",
    "\n",
    "# download price data\n",
    "stockdata = pdr.get_data_yahoo(scholar_data_ticker_list, start=startdate, end=enddate)\n",
    "closeadj = stockdata['Adj Close'].fillna(method='ffill').fillna(method='bfill').reset_index()\n",
    "\n",
    "# calculate monthly return\n",
    "closeadj['month'] = closeadj['Date'].apply(lambda x:str(x)[:7])\n",
    "closeadj_month = closeadj.drop_duplicates(['month'],keep='last').set_index(['Date','month'])\n",
    "monthly_return = (closeadj_month-closeadj_month.shift(1))/closeadj_month.shift(1)*100\n",
    "monthly_return = monthly_return.drop(monthly_return.index[0]).fillna(0).reset_index()\n",
    "\n",
    "# merge price and return data\n",
    "price_prcd = pd.melt(closeadj_month.reset_index(), id_vars=['Date','month'], value_vars=scholar_data_ticker_list)\n",
    "price_prcd.columns = ['date','month','tic','adj_closed_price']\n",
    "return_prcd = pd.melt(monthly_return, id_vars=['Date','month'], value_vars=scholar_data_ticker_list)\n",
    "return_prcd.columns = ['date','month','tic','monthly_return']\n",
    "return_prcd['forward_monthly_return'] = return_prcd['monthly_return'].shift(1)\n",
    "price_prcd = pd.merge(price_prcd,return_prcd,on=['date','month','tic'])\n",
    "price_prcd = price_prcd.drop(price_prcd.index[0]).reset_index(drop=True)\n",
    "\n",
    "# lag 6 months of scholar data \n",
    "paperdate = price_prcd['date'] - pd.DateOffset(months=1)\n",
    "price_prcd['paper_month'] = paperdate.apply(lambda x:str(x)[:7])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 239,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>month</th>\n",
       "      <th>tic</th>\n",
       "      <th>adj_closed_price</th>\n",
       "      <th>monthly_return</th>\n",
       "      <th>forward_monthly_return</th>\n",
       "      <th>paper_month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1990-03-30</td>\n",
       "      <td>1990-03</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>1.166358</td>\n",
       "      <td>18.382361</td>\n",
       "      <td>0.322324</td>\n",
       "      <td>1990-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1990-04-30</td>\n",
       "      <td>1990-04</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>1.141003</td>\n",
       "      <td>-2.173910</td>\n",
       "      <td>18.382361</td>\n",
       "      <td>1990-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1990-05-31</td>\n",
       "      <td>1990-05</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>1.198654</td>\n",
       "      <td>5.052718</td>\n",
       "      <td>-2.173910</td>\n",
       "      <td>1990-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1990-06-29</td>\n",
       "      <td>1990-06</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>1.300358</td>\n",
       "      <td>8.484869</td>\n",
       "      <td>5.052718</td>\n",
       "      <td>1990-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1990-07-31</td>\n",
       "      <td>1990-07</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>1.220448</td>\n",
       "      <td>-6.145232</td>\n",
       "      <td>8.484869</td>\n",
       "      <td>1990-06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date    month   tic  adj_closed_price  monthly_return  \\\n",
       "0 1990-03-30  1990-03  AAPL          1.166358       18.382361   \n",
       "1 1990-04-30  1990-04  AAPL          1.141003       -2.173910   \n",
       "2 1990-05-31  1990-05  AAPL          1.198654        5.052718   \n",
       "3 1990-06-29  1990-06  AAPL          1.300358        8.484869   \n",
       "4 1990-07-31  1990-07  AAPL          1.220448       -6.145232   \n",
       "\n",
       "   forward_monthly_return paper_month  \n",
       "0                0.322324     1990-02  \n",
       "1               18.382361     1990-03  \n",
       "2               -2.173910     1990-04  \n",
       "3                5.052718     1990-05  \n",
       "4                8.484869     1990-06  "
      ]
     },
     "execution_count": 239,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_prcd.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1990-02\n",
      "2020-03\n"
     ]
    }
   ],
   "source": [
    "print(price_prcd.month.min())\n",
    "print(price_prcd.month.max())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge Fundamental, Price & Ratio Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 275,
   "metadata": {},
   "outputs": [],
   "source": [
    "financial_feature = pd.merge(funda_prcd, ratio_prcd, on=['month','tic'])\n",
    "financial_feature = financial_feature.ffill()\n",
    "\n",
    "financial_feature = pd.merge(financial_feature, price_prcd, on=['month','tic']).drop_duplicates(['month','tic'],keep='first')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 276,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1990-02\n",
      "2019-01\n"
     ]
    }
   ],
   "source": [
    "print(financial_feature.month.min())\n",
    "print(financial_feature.month.max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 277,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>month</th>\n",
       "      <th>tic</th>\n",
       "      <th>gsector</th>\n",
       "      <th>epspiq</th>\n",
       "      <th>prccq</th>\n",
       "      <th>pe_inc</th>\n",
       "      <th>ptb</th>\n",
       "      <th>ps</th>\n",
       "      <th>roa</th>\n",
       "      <th>roe</th>\n",
       "      <th>quick_ratio</th>\n",
       "      <th>curr_ratio</th>\n",
       "      <th>npm</th>\n",
       "      <th>de_ratio</th>\n",
       "      <th>date</th>\n",
       "      <th>adj_closed_price</th>\n",
       "      <th>monthly_return</th>\n",
       "      <th>forward_monthly_return</th>\n",
       "      <th>paper_month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1995-10</td>\n",
       "      <td>AVX</td>\n",
       "      <td>45</td>\n",
       "      <td>0.39</td>\n",
       "      <td>26.500</td>\n",
       "      <td>30.822</td>\n",
       "      <td>5.333</td>\n",
       "      <td>2.585</td>\n",
       "      <td>0.287</td>\n",
       "      <td>0.056</td>\n",
       "      <td>1.209</td>\n",
       "      <td>2.256</td>\n",
       "      <td>0.082</td>\n",
       "      <td>0.494</td>\n",
       "      <td>1995-10-31</td>\n",
       "      <td>9.572515</td>\n",
       "      <td>-6.790130</td>\n",
       "      <td>5.511842</td>\n",
       "      <td>1995-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1995-11</td>\n",
       "      <td>AVX</td>\n",
       "      <td>45</td>\n",
       "      <td>0.39</td>\n",
       "      <td>26.500</td>\n",
       "      <td>23.566</td>\n",
       "      <td>4.296</td>\n",
       "      <td>2.263</td>\n",
       "      <td>0.316</td>\n",
       "      <td>0.129</td>\n",
       "      <td>1.317</td>\n",
       "      <td>2.382</td>\n",
       "      <td>0.094</td>\n",
       "      <td>0.472</td>\n",
       "      <td>1995-11-30</td>\n",
       "      <td>8.842086</td>\n",
       "      <td>-7.630479</td>\n",
       "      <td>-6.790130</td>\n",
       "      <td>1995-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1995-12</td>\n",
       "      <td>AVX</td>\n",
       "      <td>45</td>\n",
       "      <td>0.39</td>\n",
       "      <td>26.500</td>\n",
       "      <td>21.721</td>\n",
       "      <td>3.960</td>\n",
       "      <td>2.086</td>\n",
       "      <td>0.316</td>\n",
       "      <td>0.129</td>\n",
       "      <td>1.317</td>\n",
       "      <td>2.382</td>\n",
       "      <td>0.094</td>\n",
       "      <td>0.472</td>\n",
       "      <td>1995-12-29</td>\n",
       "      <td>8.150093</td>\n",
       "      <td>-7.826126</td>\n",
       "      <td>-7.630479</td>\n",
       "      <td>1995-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1996-01</td>\n",
       "      <td>AVX</td>\n",
       "      <td>45</td>\n",
       "      <td>0.42</td>\n",
       "      <td>21.875</td>\n",
       "      <td>21.516</td>\n",
       "      <td>3.922</td>\n",
       "      <td>2.067</td>\n",
       "      <td>0.316</td>\n",
       "      <td>0.129</td>\n",
       "      <td>1.317</td>\n",
       "      <td>2.382</td>\n",
       "      <td>0.094</td>\n",
       "      <td>0.472</td>\n",
       "      <td>1996-01-31</td>\n",
       "      <td>8.138925</td>\n",
       "      <td>-0.137035</td>\n",
       "      <td>-7.826126</td>\n",
       "      <td>1995-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1996-02</td>\n",
       "      <td>AVX</td>\n",
       "      <td>45</td>\n",
       "      <td>0.42</td>\n",
       "      <td>21.875</td>\n",
       "      <td>17.626</td>\n",
       "      <td>3.504</td>\n",
       "      <td>1.829</td>\n",
       "      <td>0.337</td>\n",
       "      <td>0.189</td>\n",
       "      <td>1.360</td>\n",
       "      <td>2.456</td>\n",
       "      <td>0.103</td>\n",
       "      <td>0.456</td>\n",
       "      <td>1996-02-29</td>\n",
       "      <td>7.596328</td>\n",
       "      <td>-6.666683</td>\n",
       "      <td>-0.137035</td>\n",
       "      <td>1996-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     month  tic  gsector  epspiq   prccq  pe_inc    ptb     ps    roa    roe  \\\n",
       "0  1995-10  AVX       45    0.39  26.500  30.822  5.333  2.585  0.287  0.056   \n",
       "1  1995-11  AVX       45    0.39  26.500  23.566  4.296  2.263  0.316  0.129   \n",
       "2  1995-12  AVX       45    0.39  26.500  21.721  3.960  2.086  0.316  0.129   \n",
       "3  1996-01  AVX       45    0.42  21.875  21.516  3.922  2.067  0.316  0.129   \n",
       "4  1996-02  AVX       45    0.42  21.875  17.626  3.504  1.829  0.337  0.189   \n",
       "\n",
       "   quick_ratio  curr_ratio    npm  de_ratio       date  adj_closed_price  \\\n",
       "0        1.209       2.256  0.082     0.494 1995-10-31          9.572515   \n",
       "1        1.317       2.382  0.094     0.472 1995-11-30          8.842086   \n",
       "2        1.317       2.382  0.094     0.472 1995-12-29          8.150093   \n",
       "3        1.317       2.382  0.094     0.472 1996-01-31          8.138925   \n",
       "4        1.360       2.456  0.103     0.456 1996-02-29          7.596328   \n",
       "\n",
       "   monthly_return  forward_monthly_return paper_month  \n",
       "0       -6.790130                5.511842     1995-09  \n",
       "1       -7.630479               -6.790130     1995-10  \n",
       "2       -7.826126               -7.630479     1995-11  \n",
       "3       -0.137035               -7.826126     1995-12  \n",
       "4       -6.666683               -0.137035     1996-01  "
      ]
     },
     "execution_count": 277,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "financial_feature.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 278,
   "metadata": {},
   "outputs": [],
   "source": [
    "#financial_feature.to_csv(path+'financial/financial_features.csv', index=0)\n",
    "financial_feature.to_csv(path+'financial/financial_features.csv', index=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge Financial & Scholar Features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 279,
   "metadata": {},
   "outputs": [],
   "source": [
    "scholar_feature = pd.read_csv(path+'scholar_data/scholar_features.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 280,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(9128, 50)"
      ]
     },
     "execution_count": 280,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scholar_feature.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 281,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(21600, 19)"
      ]
     },
     "execution_count": 281,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "financial_feature.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 282,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge two tables while fill monthly scholar data with 0 and cumulatitive data with previous valid input\n",
    "all_feature = pd.merge(financial_feature, scholar_feature, how='left',\n",
    "                       left_on=['paper_month','tic'], right_on=['paper_month','ticker'])\n",
    "all_feature = all_feature.drop(columns=['ticker'])\n",
    "\n",
    "monthly_col=['total_count','total_citation','journal_count','journal_citation','patent_count','patent_citation',\n",
    "             'article_count','article_citation','conference_count','conference_citation','book_count','book_citation',\n",
    "             'total_max_citation','total_average_citation','patent_max_citation','patent_average_citation',\n",
    "             'journal_max_citation','journal_average_citation','article_max_citation','article_average_citation',\n",
    "             'conference_max_citation','conference_average_citation','book_max_citation','book_average_citation',\n",
    "             'total_count_relative','journal_count_relative','patent_count_relative','article_count_relative',\n",
    "             'conference_count_relative','book_count_relative']\n",
    "cum_col = ['total_count_cumsum','total_citation_cumsum','journal_count_cumsum','journal_citation_cumsum','patent_count_cumsum',\n",
    "           'patent_citation_cumsum','article_count_cumsum','article_citation_cumsum','conference_count_cumsum',\n",
    "           'conference_citation_cumsum','book_count_cumsum','book_citation_cumsum','total_count_relative_cumsum',\n",
    "           'journal_count_relative_cumsum','patent_count_relative_cumsum','article_count_relative_cumsum',\n",
    "           'conference_count_relative_cumsum','book_count_relative_cumsum']\n",
    "\n",
    "for col in monthly_col:\n",
    "    all_feature[col] = all_feature[col].fillna(0)\n",
    "for col in cum_col:\n",
    "    all_feature[col] = all_feature[col].ffill().fillna(0)\n",
    "\n",
    "all_feature = all_feature.drop_duplicates(['month','tic'],keep='first')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Output Feature Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 286,
   "metadata": {},
   "outputs": [],
   "source": [
    "#all_feature.to_csv(path+'result/fin+paper_feature_table.csv',index=0)\n",
    "all_feature.to_csv(path+'result/fin+paper_feature_table.csv',index=0)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 287,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "86\n",
      "348\n",
      "2019-01\n",
      "1990-02\n"
     ]
    }
   ],
   "source": [
    "print(all_feature.tic.nunique())\n",
    "print(all_feature.month.nunique())\n",
    "print(max(all_feature.month))\n",
    "print(min(all_feature.month))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 288,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(21600, 67)"
      ]
     },
     "execution_count": 288,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_feature.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}