2_Data cleansing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Treating missing values, winsorizing outliers and feature normalization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import math\n",
    "import sklearn\n",
    "import sklearn.preprocessing\n",
    "import datetime\n",
    "import os\n",
    "import matplotlib.pyplot as plt\n",
    "import tensorflow as tf\n",
    "\n",
    "\n",
    "# loading packages\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "\n",
    "# data visualization and missing values\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns # advanced vizs\n",
    "sns.set()\n",
    "import missingno as msno # missing values\n",
    "%matplotlib inline\n",
    "\n",
    "# stats\n",
    "from statsmodels.distributions.empirical_distribution import ECDF\n",
    "from sklearn.metrics import mean_squared_error, r2_score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>id</th>\n",
       "      <th>date</th>\n",
       "      <th>eom</th>\n",
       "      <th>size_grp</th>\n",
       "      <th>primary_sec</th>\n",
       "      <th>curcd</th>\n",
       "      <th>fx</th>\n",
       "      <th>common</th>\n",
       "      <th>comp_tpci</th>\n",
       "      <th>comp_exchg</th>\n",
       "      <th>...</th>\n",
       "      <th>rmax5_rvol_21d</th>\n",
       "      <th>ni_be</th>\n",
       "      <th>ocf_at</th>\n",
       "      <th>ocf_at_chg1</th>\n",
       "      <th>mispricing_perf</th>\n",
       "      <th>mispricing_mgmt</th>\n",
       "      <th>qmj</th>\n",
       "      <th>qmj_prof</th>\n",
       "      <th>qmj_growth</th>\n",
       "      <th>qmj_safety</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>comp_001166_02W</td>\n",
       "      <td>1999-07-30</td>\n",
       "      <td>1999-07-31</td>\n",
       "      <td>small</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.070296</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.661029</td>\n",
       "      <td>-0.047722</td>\n",
       "      <td>0.140976</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>comp_001166_02W</td>\n",
       "      <td>1999-08-31</td>\n",
       "      <td>1999-08-31</td>\n",
       "      <td>small</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.056227</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.661029</td>\n",
       "      <td>-0.047722</td>\n",
       "      <td>0.140976</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>comp_001166_02W</td>\n",
       "      <td>1999-09-30</td>\n",
       "      <td>1999-09-30</td>\n",
       "      <td>small</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.064990</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.661029</td>\n",
       "      <td>-0.047722</td>\n",
       "      <td>0.140976</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>comp_001166_02W</td>\n",
       "      <td>1999-10-29</td>\n",
       "      <td>1999-10-31</td>\n",
       "      <td>small</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.051050</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.398598</td>\n",
       "      <td>0.093191</td>\n",
       "      <td>0.045857</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>comp_001166_02W</td>\n",
       "      <td>1999-11-30</td>\n",
       "      <td>1999-11-30</td>\n",
       "      <td>small</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.006891</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.398598</td>\n",
       "      <td>0.093191</td>\n",
       "      <td>0.045857</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 182 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                id       date        eom size_grp  primary_sec curcd  \\\n",
       "0  comp_001166_02W 1999-07-30 1999-07-31    small            0   EUR   \n",
       "1  comp_001166_02W 1999-08-31 1999-08-31    small            0   EUR   \n",
       "2  comp_001166_02W 1999-09-30 1999-09-30    small            0   EUR   \n",
       "3  comp_001166_02W 1999-10-29 1999-10-31    small            0   EUR   \n",
       "4  comp_001166_02W 1999-11-30 1999-11-30    small            0   EUR   \n",
       "\n",
       "         fx  common comp_tpci  comp_exchg  ...  rmax5_rvol_21d     ni_be  \\\n",
       "0  1.070296       1         0         154  ...             NaN -0.661029   \n",
       "1  1.056227       1         0         154  ...             NaN -0.661029   \n",
       "2  1.064990       1         0         154  ...             NaN -0.661029   \n",
       "3  1.051050       1         0         154  ...             NaN -0.398598   \n",
       "4  1.006891       1         0         154  ...             NaN -0.398598   \n",
       "\n",
       "     ocf_at  ocf_at_chg1  mispricing_perf  mispricing_mgmt  qmj  qmj_prof  \\\n",
       "0 -0.047722     0.140976              NaN              NaN  NaN       NaN   \n",
       "1 -0.047722     0.140976              NaN              NaN  NaN       NaN   \n",
       "2 -0.047722     0.140976              NaN              NaN  NaN       NaN   \n",
       "3  0.093191     0.045857              NaN              NaN  NaN       NaN   \n",
       "4  0.093191     0.045857              NaN              NaN  NaN       NaN   \n",
       "\n",
       "   qmj_growth  qmj_safety  \n",
       "0         NaN         NaN  \n",
       "1         NaN         NaN  \n",
       "2         NaN         NaN  \n",
       "3         NaN         NaN  \n",
       "4         NaN         NaN  \n",
       "\n",
       "[5 rows x 182 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(r\"C:\\Users\\Besitzer\\Documents\\Data\\ger_factor_data_short.csv\", dtype ={\"comp_tpci\": str})\n",
    "df[\"date\"] = pd.to_datetime(df[\"date\"])  #convert to date format\n",
    "df[\"eom\"] = pd.to_datetime(df[\"eom\"])    #convert to date format\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Missing values\n",
    "\n",
    "- Inspect the amount of missing values per variable:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                           0\n",
       "date                         0\n",
       "eom                          0\n",
       "size_grp                  6256\n",
       "primary_sec                  0\n",
       "curcd                        0\n",
       "fx                           0\n",
       "common                       0\n",
       "comp_tpci                    0\n",
       "comp_exchg                   0\n",
       "sic                      32512\n",
       "shares                    6256\n",
       "me_company                6256\n",
       "prc_local                    0\n",
       "dolvol                   68758\n",
       "ret                          0\n",
       "ret_local                    0\n",
       "ret_exc                      0\n",
       "ret_lag_dif                  0\n",
       "ret_exc_lead1m            4750\n",
       "enterprise_value         41189\n",
       "book_equity               9412\n",
       "assets                       0\n",
       "sales                     1930\n",
       "net_income                  11\n",
       "bidask                       0\n",
       "prc_high                     0\n",
       "prc_low                      0\n",
       "tvol                     68758\n",
       "niq_su                  159582\n",
       "ret_6_1                  40467\n",
       "ret_12_1                 53911\n",
       "saleq_su                200920\n",
       "tax_gr1a                 18475\n",
       "ni_inc8q                175937\n",
       "prc_highprc_252d         50615\n",
       "resff3_6_1               76474\n",
       "resff3_12_1              75564\n",
       "be_me                    37701\n",
       "debt_me                  35240\n",
       "at_me                    28792\n",
       "ret_60_12               148577\n",
       "ni_me                    28803\n",
       "fcf_me                  121285\n",
       "div12m_me               185673\n",
       "eqpo_me                 252359\n",
       "eqnpo_me                231017\n",
       "sale_gr3                 56161\n",
       "sale_gr1                 21317\n",
       "ebitda_mev               45592\n",
       "sale_me                  30685\n",
       "ocf_me                   31651\n",
       "intrinsic_value         165178\n",
       "bev_mev                  50372\n",
       "netdebt_me               35240\n",
       "eq_dur                   75684\n",
       "capex_abn               154164\n",
       "at_gr1                   11646\n",
       "ppeinv_gr1a              64287\n",
       "noa_at                   69827\n",
       "noa_gr1a                 70601\n",
       "lnoa_gr1a                49224\n",
       "capx_gr1                117532\n",
       "capx_gr2                132584\n",
       "capx_gr3                148031\n",
       "chcsho_12m               35493\n",
       "eqnpo_12m                35493\n",
       "debt_gr3                 87657\n",
       "inv_gr1                  88517\n",
       "inv_gr1a                 32529\n",
       "oaccruals_at             66821\n",
       "taccruals_at             68941\n",
       "cowc_gr1a                69676\n",
       "coa_gr1a                 69453\n",
       "col_gr1a                 69544\n",
       "nncoa_gr1a               70574\n",
       "ncoa_gr1a                69411\n",
       "ncol_gr1a                70478\n",
       "nfna_gr1a                20241\n",
       "sti_gr1a                 49517\n",
       "lti_gr1a                 70749\n",
       "fnl_gr1a                 20241\n",
       "be_gr1a                  24159\n",
       "oaccruals_ni             68020\n",
       "taccruals_ni             70424\n",
       "netis_at                182790\n",
       "eqnetis_at              182324\n",
       "dbnetis_at               15661\n",
       "niq_be                  122435\n",
       "niq_be_chg1             141348\n",
       "niq_at                  120118\n",
       "niq_at_chg1             139552\n",
       "ebit_bev                 13221\n",
       "ebit_sale                 8924\n",
       "sale_bev                 13855\n",
       "at_turnover              10396\n",
       "gp_at                    60892\n",
       "gp_atl1                  70731\n",
       "ope_be                   24452\n",
       "ope_bel1                 35475\n",
       "op_at                     3636\n",
       "op_atl1                  15914\n",
       "cop_at                   67191\n",
       "cop_atl1                 69354\n",
       "f_score                  93640\n",
       "o_score                  76563\n",
       "z_score                  95322\n",
       "pi_nix                   96373\n",
       "at_be                     9424\n",
       "saleq_gr1               171142\n",
       "rd_me                   253417\n",
       "rd_sale                 243383\n",
       "opex_at                  58882\n",
       "emp_gr1                 131424\n",
       "rd5_at                  284647\n",
       "age                          0\n",
       "dsale_dinv              101387\n",
       "dsale_drec               89161\n",
       "dgp_dsale                96772\n",
       "dsale_dsga              164365\n",
       "sale_emp_gr1            127491\n",
       "tangibility              71732\n",
       "kz_index                180901\n",
       "ocfq_saleq_std          189277\n",
       "cash_at                   9484\n",
       "ni_ar1                   94538\n",
       "ni_ivol                  94538\n",
       "earnings_variability     97885\n",
       "aliq_at                  68980\n",
       "aliq_mat                110044\n",
       "seas_1_1an               40728\n",
       "seas_1_1na              108566\n",
       "seas_2_5an              158891\n",
       "seas_2_5na              247441\n",
       "seas_6_10an             250161\n",
       "seas_6_10na             305362\n",
       "seas_11_15an            306059\n",
       "seas_11_15na            335760\n",
       "seas_16_20an            336809\n",
       "seas_16_20na            350359\n",
       "market_equity             6256\n",
       "ivol_ff3_21d             75410\n",
       "ivol_capm_252d           50615\n",
       "ivol_capm_21d            65703\n",
       "ivol_hxz4_21d           122611\n",
       "rvol_21d                 65703\n",
       "beta_60m                 95612\n",
       "betabab_1260d           148176\n",
       "beta_dimson_21d          65703\n",
       "turnover_126d           157363\n",
       "turnover_var_126d       157363\n",
       "dolvol_126d             154685\n",
       "dolvol_var_126d         154685\n",
       "prc                          0\n",
       "ami_126d                157866\n",
       "zero_trades_21d         178657\n",
       "zero_trades_126d        157301\n",
       "zero_trades_252d        161368\n",
       "rmax1_21d                65703\n",
       "rskew_21d                65703\n",
       "iskew_capm_21d           65703\n",
       "iskew_ff3_21d            75410\n",
       "iskew_hxz4_21d          122611\n",
       "coskew_21d               65703\n",
       "ret_1_0                  25961\n",
       "betadown_252d            56155\n",
       "bidaskhl_21d             31616\n",
       "ret_3_1                  32971\n",
       "ret_9_1                  47400\n",
       "ret_12_7                 53809\n",
       "corr_1260d              146391\n",
       "rmax5_21d                65703\n",
       "rmax5_rvol_21d           79564\n",
       "ni_be                     9720\n",
       "ocf_at                    3148\n",
       "ocf_at_chg1              17034\n",
       "mispricing_perf         150849\n",
       "mispricing_mgmt         157746\n",
       "qmj                     211863\n",
       "qmj_prof                147129\n",
       "qmj_growth              211492\n",
       "qmj_safety              148385\n",
       "dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option('display.max_rows',None)\n",
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Drop observations without SIC-codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.dropna( how='any',subset=[\"sic\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Replace the missing valuues with the cross-sectional median at each month\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.fillna(df.groupby('eom').transform('median'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Drop variable characteristics the more than 10,000 missing values:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "seas_16_20na            98269\n",
       "seas_16_20an            98098\n",
       "ivol_hxz4_21d           59487\n",
       "iskew_hxz4_21d          59487\n",
       "seas_11_15na            44474\n",
       "seas_11_15an            44474\n",
       "seas_6_10na             14543\n",
       "seas_6_10an             14543\n",
       "capex_abn               11828\n",
       "zero_trades_252d        10056\n",
       "turnover_126d            9341\n",
       "dolvol_126d              9341\n",
       "dolvol_var_126d          9341\n",
       "ami_126d                 9341\n",
       "zero_trades_126d         9341\n",
       "turnover_var_126d        9341\n",
       "qmj_growth               9341\n",
       "qmj                      9341\n",
       "tvol                     8869\n",
       "dolvol                   8869\n",
       "zero_trades_21d          8869\n",
       "ni_inc8q                 7929\n",
       "capx_gr2                 7630\n",
       "capx_gr1                 6955\n",
       "size_grp                 6198\n",
       "niq_be_chg1              6171\n",
       "niq_at_chg1              6171\n",
       "niq_su                   6083\n",
       "capx_gr3                 5932\n",
       "resff3_12_1              4959\n",
       "resff3_6_1               4959\n",
       "niq_be                   3955\n",
       "niq_at                   3445\n",
       "ocfq_saleq_std           2791\n",
       "fcf_me                   1783\n",
       "ret_60_12                1762\n",
       "seas_2_5na               1590\n",
       "seas_2_5an               1590\n",
       "eqnpo_me                 1564\n",
       "eqpo_me                  1564\n",
       "eqnetis_at               1564\n",
       "netis_at                 1564\n",
       "ivol_ff3_21d              918\n",
       "iskew_ff3_21d             918\n",
       "bidaskhl_21d              631\n",
       "beta_60m                  623\n",
       "mispricing_mgmt           320\n",
       "ni_ar1                     95\n",
       "earnings_variability       95\n",
       "ni_ivol                    95\n",
       "qmj_prof                   95\n",
       "be_gr1a                    77\n",
       "ncol_gr1a                  77\n",
       "nfna_gr1a                  77\n",
       "sti_gr1a                   77\n",
       "lti_gr1a                   77\n",
       "fnl_gr1a                   77\n",
       "dbnetis_at                 77\n",
       "oaccruals_ni               77\n",
       "taccruals_ni               77\n",
       "tax_gr1a                   77\n",
       "saleq_su                   77\n",
       "at_turnover                77\n",
       "nncoa_gr1a                 77\n",
       "ncoa_gr1a                  77\n",
       "eq_dur                     77\n",
       "col_gr1a                   77\n",
       "coa_gr1a                   77\n",
       "ppeinv_gr1a                77\n",
       "intrinsic_value            77\n",
       "noa_at                     77\n",
       "noa_gr1a                   77\n",
       "lnoa_gr1a                  77\n",
       "gp_atl1                    77\n",
       "sale_gr1                   77\n",
       "sale_gr3                   77\n",
       "debt_gr3                   77\n",
       "inv_gr1                    77\n",
       "at_gr1                     77\n",
       "inv_gr1a                   77\n",
       "oaccruals_at               77\n",
       "taccruals_at               77\n",
       "cowc_gr1a                  77\n",
       "gp_at                      77\n",
       "qmj_safety                 77\n",
       "dsale_dinv                 77\n",
       "cash_at                    77\n",
       "dgp_dsale                  77\n",
       "dsale_drec                 77\n",
       "tangibility                77\n",
       "kz_index                   77\n",
       "rd5_at                     77\n",
       "emp_gr1                    77\n",
       "opex_at                    77\n",
       "rd_sale                    77\n",
       "rd_me                      77\n",
       "saleq_gr1                  77\n",
       "aliq_at                    77\n",
       "sale_emp_gr1               77\n",
       "z_score                    77\n",
       "o_score                    77\n",
       "f_score                    77\n",
       "cop_atl1                   77\n",
       "cop_at                     77\n",
       "op_atl1                    77\n",
       "aliq_mat                   77\n",
       "ope_bel1                   77\n",
       "ocf_at_chg1                77\n",
       "dsale_dsga                 77\n",
       "betabab_1260d              69\n",
       "corr_1260d                 69\n",
       "ret_lag_dif                 0\n",
       "prc_low                     0\n",
       "ocf_at                      0\n",
       "ni_be                       0\n",
       "prc_high                    0\n",
       "bidask                      0\n",
       "ret_6_1                     0\n",
       "net_income                  0\n",
       "ret_12_1                    0\n",
       "sales                       0\n",
       "assets                      0\n",
       "rmax5_rvol_21d              0\n",
       "book_equity                 0\n",
       "rmax5_21d                   0\n",
       "enterprise_value            0\n",
       "ret_exc_lead1m              0\n",
       "ope_be                      0\n",
       "ret_exc                     0\n",
       "common                      0\n",
       "date                        0\n",
       "eom                         0\n",
       "mispricing_perf             0\n",
       "primary_sec                 0\n",
       "curcd                       0\n",
       "fx                          0\n",
       "prc_highprc_252d            0\n",
       "ret_local                   0\n",
       "comp_exchg                  0\n",
       "sic                         0\n",
       "shares                      0\n",
       "me_company                  0\n",
       "prc_local                   0\n",
       "ret                         0\n",
       "comp_tpci                   0\n",
       "rskew_21d                   0\n",
       "ret_12_7                    0\n",
       "ret_9_1                     0\n",
       "rvol_21d                    0\n",
       "ivol_capm_21d               0\n",
       "ivol_capm_252d              0\n",
       "market_equity               0\n",
       "seas_1_1na                  0\n",
       "seas_1_1an                  0\n",
       "chcsho_12m                  0\n",
       "eqnpo_12m                   0\n",
       "age                         0\n",
       "at_be                       0\n",
       "pi_nix                      0\n",
       "op_at                       0\n",
       "ebit_bev                    0\n",
       "ebit_sale                   0\n",
       "sale_bev                    0\n",
       "beta_dimson_21d             0\n",
       "prc                         0\n",
       "netdebt_me                  0\n",
       "div12m_me                   0\n",
       "be_me                       0\n",
       "debt_me                     0\n",
       "at_me                       0\n",
       "ret_3_1                     0\n",
       "ni_me                       0\n",
       "betadown_252d               0\n",
       "ret_1_0                     0\n",
       "bev_mev                     0\n",
       "coskew_21d                  0\n",
       "iskew_capm_21d              0\n",
       "ebitda_mev                  0\n",
       "sale_me                     0\n",
       "ocf_me                      0\n",
       "rmax1_21d                   0\n",
       "id                          0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option('display.max_rows',None)\n",
    "df.isna().sum().sort_values(ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop([\"seas_16_20na\", \"seas_16_20an\",\"ivol_hxz4_21d\", \"iskew_hxz4_21d\",\"seas_11_15na\",\"seas_11_15an\",\"seas_6_10na\",\"seas_6_10an\"], axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-  Drop all observations for which there are still missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- There are 306411 observations left"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "306411\n"
     ]
    }
   ],
   "source": [
    "print(len(df))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "qmj_safety              0\n",
       "netdebt_me              0\n",
       "capx_gr1                0\n",
       "lnoa_gr1a               0\n",
       "noa_gr1a                0\n",
       "noa_at                  0\n",
       "ppeinv_gr1a             0\n",
       "at_gr1                  0\n",
       "capex_abn               0\n",
       "eq_dur                  0\n",
       "bev_mev                 0\n",
       "capx_gr3                0\n",
       "intrinsic_value         0\n",
       "ocf_me                  0\n",
       "sale_me                 0\n",
       "ebitda_mev              0\n",
       "sale_gr1                0\n",
       "sale_gr3                0\n",
       "eqnpo_me                0\n",
       "eqpo_me                 0\n",
       "capx_gr2                0\n",
       "chcsho_12m              0\n",
       "fcf_me                  0\n",
       "ncoa_gr1a               0\n",
       "taccruals_ni            0\n",
       "oaccruals_ni            0\n",
       "be_gr1a                 0\n",
       "fnl_gr1a                0\n",
       "lti_gr1a                0\n",
       "sti_gr1a                0\n",
       "nfna_gr1a               0\n",
       "ncol_gr1a               0\n",
       "nncoa_gr1a              0\n",
       "eqnpo_12m               0\n",
       "col_gr1a                0\n",
       "coa_gr1a                0\n",
       "cowc_gr1a               0\n",
       "taccruals_at            0\n",
       "oaccruals_at            0\n",
       "inv_gr1a                0\n",
       "inv_gr1                 0\n",
       "debt_gr3                0\n",
       "div12m_me               0\n",
       "ni_me                   0\n",
       "qmj_growth              0\n",
       "sic                     0\n",
       "ret_lag_dif             0\n",
       "ret_exc                 0\n",
       "ret_local               0\n",
       "ret                     0\n",
       "dolvol                  0\n",
       "prc_local               0\n",
       "me_company              0\n",
       "shares                  0\n",
       "comp_exchg              0\n",
       "enterprise_value        0\n",
       "comp_tpci               0\n",
       "common                  0\n",
       "fx                      0\n",
       "curcd                   0\n",
       "primary_sec             0\n",
       "size_grp                0\n",
       "eom                     0\n",
       "date                    0\n",
       "ret_exc_lead1m          0\n",
       "book_equity             0\n",
       "ret_60_12               0\n",
       "saleq_su                0\n",
       "at_me                   0\n",
       "debt_me                 0\n",
       "be_me                   0\n",
       "resff3_12_1             0\n",
       "resff3_6_1              0\n",
       "prc_highprc_252d        0\n",
       "ni_inc8q                0\n",
       "tax_gr1a                0\n",
       "ret_12_1                0\n",
       "assets                  0\n",
       "ret_6_1                 0\n",
       "niq_su                  0\n",
       "tvol                    0\n",
       "prc_low                 0\n",
       "prc_high                0\n",
       "bidask                  0\n",
       "net_income              0\n",
       "sales                   0\n",
       "netis_at                0\n",
       "eqnetis_at              0\n",
       "dbnetis_at              0\n",
       "beta_dimson_21d         0\n",
       "zero_trades_126d        0\n",
       "zero_trades_21d         0\n",
       "ami_126d                0\n",
       "prc                     0\n",
       "dolvol_var_126d         0\n",
       "dolvol_126d             0\n",
       "turnover_var_126d       0\n",
       "turnover_126d           0\n",
       "betabab_1260d           0\n",
       "rmax1_21d               0\n",
       "beta_60m                0\n",
       "rvol_21d                0\n",
       "ivol_capm_21d           0\n",
       "ivol_capm_252d          0\n",
       "ivol_ff3_21d            0\n",
       "market_equity           0\n",
       "seas_2_5na              0\n",
       "seas_2_5an              0\n",
       "zero_trades_252d        0\n",
       "rskew_21d               0\n",
       "niq_be                  0\n",
       "rmax5_21d               0\n",
       "qmj_prof                0\n",
       "qmj                     0\n",
       "mispricing_mgmt         0\n",
       "mispricing_perf         0\n",
       "ocf_at_chg1             0\n",
       "ocf_at                  0\n",
       "ni_be                   0\n",
       "rmax5_rvol_21d          0\n",
       "corr_1260d              0\n",
       "iskew_capm_21d          0\n",
       "ret_12_7                0\n",
       "ret_9_1                 0\n",
       "ret_3_1                 0\n",
       "bidaskhl_21d            0\n",
       "betadown_252d           0\n",
       "ret_1_0                 0\n",
       "coskew_21d              0\n",
       "iskew_ff3_21d           0\n",
       "seas_1_1na              0\n",
       "seas_1_1an              0\n",
       "aliq_mat                0\n",
       "ope_be                  0\n",
       "z_score                 0\n",
       "o_score                 0\n",
       "f_score                 0\n",
       "cop_atl1                0\n",
       "cop_at                  0\n",
       "op_atl1                 0\n",
       "op_at                   0\n",
       "ope_bel1                0\n",
       "gp_atl1                 0\n",
       "aliq_at                 0\n",
       "gp_at                   0\n",
       "at_turnover             0\n",
       "sale_bev                0\n",
       "ebit_sale               0\n",
       "ebit_bev                0\n",
       "niq_at_chg1             0\n",
       "niq_at                  0\n",
       "niq_be_chg1             0\n",
       "pi_nix                  0\n",
       "at_be                   0\n",
       "saleq_gr1               0\n",
       "rd_me                   0\n",
       "earnings_variability    0\n",
       "ni_ivol                 0\n",
       "ni_ar1                  0\n",
       "cash_at                 0\n",
       "ocfq_saleq_std          0\n",
       "kz_index                0\n",
       "tangibility             0\n",
       "sale_emp_gr1            0\n",
       "dsale_dsga              0\n",
       "dgp_dsale               0\n",
       "dsale_drec              0\n",
       "dsale_dinv              0\n",
       "age                     0\n",
       "rd5_at                  0\n",
       "emp_gr1                 0\n",
       "opex_at                 0\n",
       "rd_sale                 0\n",
       "id                      0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option('display.max_rows',None)\n",
    "df.isna().sum().sort_values(ascending = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Save cleaned data set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(r'C:\\Users\\Besitzer\\Documents\\Data\\ger_factor_data_without_NAN.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>id</th>\n",
       "      <th>date</th>\n",
       "      <th>eom</th>\n",
       "      <th>size_grp</th>\n",
       "      <th>primary_sec</th>\n",
       "      <th>curcd</th>\n",
       "      <th>fx</th>\n",
       "      <th>common</th>\n",
       "      <th>comp_tpci</th>\n",
       "      <th>comp_exchg</th>\n",
       "      <th>...</th>\n",
       "      <th>rmax5_rvol_21d</th>\n",
       "      <th>ni_be</th>\n",
       "      <th>ocf_at</th>\n",
       "      <th>ocf_at_chg1</th>\n",
       "      <th>mispricing_perf</th>\n",
       "      <th>mispricing_mgmt</th>\n",
       "      <th>qmj</th>\n",
       "      <th>qmj_prof</th>\n",
       "      <th>qmj_growth</th>\n",
       "      <th>qmj_safety</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>comp_001166_02W</td>\n",
       "      <td>1999-07-30</td>\n",
       "      <td>1999-07-31</td>\n",
       "      <td>small</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.070296</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>0.964529</td>\n",
       "      <td>-0.661029</td>\n",
       "      <td>-0.047722</td>\n",
       "      <td>0.140976</td>\n",
       "      <td>0.503758</td>\n",
       "      <td>0.514521</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.019932</td>\n",
       "      <td>0.052354</td>\n",
       "      <td>-0.130143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>comp_001166_02W</td>\n",
       "      <td>1999-08-31</td>\n",
       "      <td>1999-08-31</td>\n",
       "      <td>small</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.056227</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>0.894882</td>\n",
       "      <td>-0.661029</td>\n",
       "      <td>-0.047722</td>\n",
       "      <td>0.140976</td>\n",
       "      <td>0.511799</td>\n",
       "      <td>0.506162</td>\n",
       "      <td>-0.008770</td>\n",
       "      <td>0.019840</td>\n",
       "      <td>0.043850</td>\n",
       "      <td>-0.118398</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>comp_001166_02W</td>\n",
       "      <td>1999-09-30</td>\n",
       "      <td>1999-09-30</td>\n",
       "      <td>small</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.064990</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>0.880642</td>\n",
       "      <td>-0.661029</td>\n",
       "      <td>-0.047722</td>\n",
       "      <td>0.140976</td>\n",
       "      <td>0.502374</td>\n",
       "      <td>0.505931</td>\n",
       "      <td>-0.008770</td>\n",
       "      <td>0.011904</td>\n",
       "      <td>0.043850</td>\n",
       "      <td>-0.125760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>comp_001166_02W</td>\n",
       "      <td>1999-10-29</td>\n",
       "      <td>1999-10-31</td>\n",
       "      <td>small</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.051050</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>0.952324</td>\n",
       "      <td>-0.398598</td>\n",
       "      <td>0.093191</td>\n",
       "      <td>0.045857</td>\n",
       "      <td>0.512833</td>\n",
       "      <td>0.509351</td>\n",
       "      <td>-0.008998</td>\n",
       "      <td>0.015945</td>\n",
       "      <td>0.044988</td>\n",
       "      <td>-0.111005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>comp_001166_02W</td>\n",
       "      <td>1999-11-30</td>\n",
       "      <td>1999-11-30</td>\n",
       "      <td>small</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.006891</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>1.027418</td>\n",
       "      <td>-0.398598</td>\n",
       "      <td>0.093191</td>\n",
       "      <td>0.045857</td>\n",
       "      <td>0.506444</td>\n",
       "      <td>0.517646</td>\n",
       "      <td>0.026716</td>\n",
       "      <td>0.033999</td>\n",
       "      <td>0.080147</td>\n",
       "      <td>-0.065429</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 174 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                id       date        eom size_grp  primary_sec curcd  \\\n",
       "0  comp_001166_02W 1999-07-30 1999-07-31    small            0   EUR   \n",
       "1  comp_001166_02W 1999-08-31 1999-08-31    small            0   EUR   \n",
       "2  comp_001166_02W 1999-09-30 1999-09-30    small            0   EUR   \n",
       "3  comp_001166_02W 1999-10-29 1999-10-31    small            0   EUR   \n",
       "4  comp_001166_02W 1999-11-30 1999-11-30    small            0   EUR   \n",
       "\n",
       "         fx  common comp_tpci  comp_exchg  ...  rmax5_rvol_21d     ni_be  \\\n",
       "0  1.070296       1         0         154  ...        0.964529 -0.661029   \n",
       "1  1.056227       1         0         154  ...        0.894882 -0.661029   \n",
       "2  1.064990       1         0         154  ...        0.880642 -0.661029   \n",
       "3  1.051050       1         0         154  ...        0.952324 -0.398598   \n",
       "4  1.006891       1         0         154  ...        1.027418 -0.398598   \n",
       "\n",
       "     ocf_at  ocf_at_chg1  mispricing_perf  mispricing_mgmt       qmj  \\\n",
       "0 -0.047722     0.140976         0.503758         0.514521  0.000000   \n",
       "1 -0.047722     0.140976         0.511799         0.506162 -0.008770   \n",
       "2 -0.047722     0.140976         0.502374         0.505931 -0.008770   \n",
       "3  0.093191     0.045857         0.512833         0.509351 -0.008998   \n",
       "4  0.093191     0.045857         0.506444         0.517646  0.026716   \n",
       "\n",
       "   qmj_prof  qmj_growth  qmj_safety  \n",
       "0  0.019932    0.052354   -0.130143  \n",
       "1  0.019840    0.043850   -0.118398  \n",
       "2  0.011904    0.043850   -0.125760  \n",
       "3  0.015945    0.044988   -0.111005  \n",
       "4  0.033999    0.080147   -0.065429  \n",
       "\n",
       "[5 rows x 174 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(r\"C:\\Users\\Besitzer\\Documents\\Data\\ger_factor_data_without_NAN.csv\", dtype ={\"comp_tpci\": str})\n",
    "df[\"date\"] = pd.to_datetime(df[\"date\"])  #convert to date format\n",
    "df[\"eom\"] = pd.to_datetime(df[\"eom\"])    #convert to date format\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Explore the data before treating \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.sort_values(by=['eom', \"id\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>id</th>\n",
       "      <th>date</th>\n",
       "      <th>eom</th>\n",
       "      <th>size_grp</th>\n",
       "      <th>primary_sec</th>\n",
       "      <th>curcd</th>\n",
       "      <th>fx</th>\n",
       "      <th>common</th>\n",
       "      <th>comp_tpci</th>\n",
       "      <th>comp_exchg</th>\n",
       "      <th>...</th>\n",
       "      <th>rmax5_rvol_21d</th>\n",
       "      <th>ni_be</th>\n",
       "      <th>ocf_at</th>\n",
       "      <th>ocf_at_chg1</th>\n",
       "      <th>mispricing_perf</th>\n",
       "      <th>mispricing_mgmt</th>\n",
       "      <th>qmj</th>\n",
       "      <th>qmj_prof</th>\n",
       "      <th>qmj_growth</th>\n",
       "      <th>qmj_safety</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3557</th>\n",
       "      <td>comp_011217_05W</td>\n",
       "      <td>1995-04-28</td>\n",
       "      <td>1995-04-30</td>\n",
       "      <td>mega</td>\n",
       "      <td>0</td>\n",
       "      <td>DEM</td>\n",
       "      <td>0.723476</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>163</td>\n",
       "      <td>...</td>\n",
       "      <td>1.502913</td>\n",
       "      <td>0.278979</td>\n",
       "      <td>0.088404</td>\n",
       "      <td>0.037669</td>\n",
       "      <td>0.500384</td>\n",
       "      <td>0.499886</td>\n",
       "      <td>0.018878</td>\n",
       "      <td>-0.024001</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.029671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7008</th>\n",
       "      <td>comp_015334_02W</td>\n",
       "      <td>1995-04-28</td>\n",
       "      <td>1995-04-30</td>\n",
       "      <td>mega</td>\n",
       "      <td>0</td>\n",
       "      <td>DEM</td>\n",
       "      <td>0.723476</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>1.271648</td>\n",
       "      <td>0.187998</td>\n",
       "      <td>0.151067</td>\n",
       "      <td>0.129397</td>\n",
       "      <td>0.500384</td>\n",
       "      <td>0.499886</td>\n",
       "      <td>0.018878</td>\n",
       "      <td>-0.024001</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.029671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7411</th>\n",
       "      <td>comp_015496_01W</td>\n",
       "      <td>1995-04-28</td>\n",
       "      <td>1995-04-30</td>\n",
       "      <td>small</td>\n",
       "      <td>0</td>\n",
       "      <td>DEM</td>\n",
       "      <td>0.723476</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>1.061040</td>\n",
       "      <td>0.087750</td>\n",
       "      <td>0.006923</td>\n",
       "      <td>-0.001141</td>\n",
       "      <td>0.500384</td>\n",
       "      <td>0.499886</td>\n",
       "      <td>0.018878</td>\n",
       "      <td>-0.024001</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.029671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7627</th>\n",
       "      <td>comp_015496_02W</td>\n",
       "      <td>1995-04-28</td>\n",
       "      <td>1995-04-30</td>\n",
       "      <td>large</td>\n",
       "      <td>1</td>\n",
       "      <td>DEM</td>\n",
       "      <td>0.723476</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>1.266525</td>\n",
       "      <td>0.087750</td>\n",
       "      <td>0.006923</td>\n",
       "      <td>-0.001141</td>\n",
       "      <td>0.232975</td>\n",
       "      <td>0.538805</td>\n",
       "      <td>0.773998</td>\n",
       "      <td>-0.424014</td>\n",
       "      <td>0.906144</td>\n",
       "      <td>0.830793</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7668</th>\n",
       "      <td>comp_015518_01W</td>\n",
       "      <td>1995-04-28</td>\n",
       "      <td>1995-04-30</td>\n",
       "      <td>large</td>\n",
       "      <td>1</td>\n",
       "      <td>DEM</td>\n",
       "      <td>0.723476</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>1.066115</td>\n",
       "      <td>0.054419</td>\n",
       "      <td>0.013456</td>\n",
       "      <td>0.000189</td>\n",
       "      <td>0.623656</td>\n",
       "      <td>0.446684</td>\n",
       "      <td>0.509706</td>\n",
       "      <td>-0.728023</td>\n",
       "      <td>0.868388</td>\n",
       "      <td>0.815957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306352</th>\n",
       "      <td>comp_333391_05W</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>nano</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.228850</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>0.577215</td>\n",
       "      <td>-0.267562</td>\n",
       "      <td>-0.146800</td>\n",
       "      <td>0.642355</td>\n",
       "      <td>0.504174</td>\n",
       "      <td>0.509169</td>\n",
       "      <td>0.029274</td>\n",
       "      <td>0.030414</td>\n",
       "      <td>0.048699</td>\n",
       "      <td>0.062550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306370</th>\n",
       "      <td>comp_333885_01W</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>large</td>\n",
       "      <td>1</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.228850</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>171</td>\n",
       "      <td>...</td>\n",
       "      <td>0.961956</td>\n",
       "      <td>0.639671</td>\n",
       "      <td>0.193740</td>\n",
       "      <td>0.074673</td>\n",
       "      <td>0.664996</td>\n",
       "      <td>0.382121</td>\n",
       "      <td>0.029274</td>\n",
       "      <td>1.618001</td>\n",
       "      <td>0.048699</td>\n",
       "      <td>-0.446789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306384</th>\n",
       "      <td>comp_334036_02W</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>nano</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.228850</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>1.713296</td>\n",
       "      <td>-1.335185</td>\n",
       "      <td>-0.366940</td>\n",
       "      <td>0.061961</td>\n",
       "      <td>0.504174</td>\n",
       "      <td>0.509169</td>\n",
       "      <td>0.029274</td>\n",
       "      <td>0.030414</td>\n",
       "      <td>0.048699</td>\n",
       "      <td>0.062550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306389</th>\n",
       "      <td>comp_334302_02W</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>micro</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.228850</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>257</td>\n",
       "      <td>...</td>\n",
       "      <td>1.024812</td>\n",
       "      <td>0.251130</td>\n",
       "      <td>0.075040</td>\n",
       "      <td>-0.115848</td>\n",
       "      <td>0.504174</td>\n",
       "      <td>0.509169</td>\n",
       "      <td>0.029274</td>\n",
       "      <td>0.030414</td>\n",
       "      <td>0.048699</td>\n",
       "      <td>0.062550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306410</th>\n",
       "      <td>comp_336873_02W</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>nano</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.228850</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>1.696445</td>\n",
       "      <td>0.056421</td>\n",
       "      <td>-0.379762</td>\n",
       "      <td>-0.467364</td>\n",
       "      <td>0.504174</td>\n",
       "      <td>0.509169</td>\n",
       "      <td>0.029274</td>\n",
       "      <td>0.030414</td>\n",
       "      <td>0.048699</td>\n",
       "      <td>0.062550</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 174 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     id       date        eom size_grp  primary_sec curcd  \\\n",
       "3557    comp_011217_05W 1995-04-28 1995-04-30     mega            0   DEM   \n",
       "7008    comp_015334_02W 1995-04-28 1995-04-30     mega            0   DEM   \n",
       "7411    comp_015496_01W 1995-04-28 1995-04-30    small            0   DEM   \n",
       "7627    comp_015496_02W 1995-04-28 1995-04-30    large            1   DEM   \n",
       "7668    comp_015518_01W 1995-04-28 1995-04-30    large            1   DEM   \n",
       "306352  comp_333391_05W 2020-12-30 2020-12-31     nano            0   EUR   \n",
       "306370  comp_333885_01W 2020-12-30 2020-12-31    large            1   EUR   \n",
       "306384  comp_334036_02W 2020-12-30 2020-12-31     nano            0   EUR   \n",
       "306389  comp_334302_02W 2020-12-30 2020-12-31    micro            0   EUR   \n",
       "306410  comp_336873_02W 2020-12-30 2020-12-31     nano            0   EUR   \n",
       "\n",
       "              fx  common comp_tpci  comp_exchg  ...  rmax5_rvol_21d     ni_be  \\\n",
       "3557    0.723476       1         0         163  ...        1.502913  0.278979   \n",
       "7008    0.723476       1         0         154  ...        1.271648  0.187998   \n",
       "7411    0.723476       1         0         154  ...        1.061040  0.087750   \n",
       "7627    0.723476       1         0         154  ...        1.266525  0.087750   \n",
       "7668    0.723476       1         0         154  ...        1.066115  0.054419   \n",
       "306352  1.228850       1         0         154  ...        0.577215 -0.267562   \n",
       "306370  1.228850       1         0         171  ...        0.961956  0.639671   \n",
       "306384  1.228850       1         0         154  ...        1.713296 -1.335185   \n",
       "306389  1.228850       1         0         257  ...        1.024812  0.251130   \n",
       "306410  1.228850       1         0         154  ...        1.696445  0.056421   \n",
       "\n",
       "          ocf_at  ocf_at_chg1  mispricing_perf  mispricing_mgmt       qmj  \\\n",
       "3557    0.088404     0.037669         0.500384         0.499886  0.018878   \n",
       "7008    0.151067     0.129397         0.500384         0.499886  0.018878   \n",
       "7411    0.006923    -0.001141         0.500384         0.499886  0.018878   \n",
       "7627    0.006923    -0.001141         0.232975         0.538805  0.773998   \n",
       "7668    0.013456     0.000189         0.623656         0.446684  0.509706   \n",
       "306352 -0.146800     0.642355         0.504174         0.509169  0.029274   \n",
       "306370  0.193740     0.074673         0.664996         0.382121  0.029274   \n",
       "306384 -0.366940     0.061961         0.504174         0.509169  0.029274   \n",
       "306389  0.075040    -0.115848         0.504174         0.509169  0.029274   \n",
       "306410 -0.379762    -0.467364         0.504174         0.509169  0.029274   \n",
       "\n",
       "        qmj_prof  qmj_growth  qmj_safety  \n",
       "3557   -0.024001    0.000000    0.029671  \n",
       "7008   -0.024001    0.000000    0.029671  \n",
       "7411   -0.024001    0.000000    0.029671  \n",
       "7627   -0.424014    0.906144    0.830793  \n",
       "7668   -0.728023    0.868388    0.815957  \n",
       "306352  0.030414    0.048699    0.062550  \n",
       "306370  1.618001    0.048699   -0.446789  \n",
       "306384  0.030414    0.048699    0.062550  \n",
       "306389  0.030414    0.048699    0.062550  \n",
       "306410  0.030414    0.048699    0.062550  \n",
       "\n",
       "[10 rows x 174 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The observation period contains 300 months in the period from 04.1995 to 12.2020'\n",
    "df.head(5).append(df.tail(5))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['1995-04-30T00:00:00.000000000', '1995-05-31T00:00:00.000000000',\n",
       "       '1995-06-30T00:00:00.000000000', '1996-04-30T00:00:00.000000000',\n",
       "       '1996-05-31T00:00:00.000000000', '1996-06-30T00:00:00.000000000',\n",
       "       '1996-07-31T00:00:00.000000000', '1996-08-31T00:00:00.000000000',\n",
       "       '1996-09-30T00:00:00.000000000', '1996-10-31T00:00:00.000000000',\n",
       "       '1996-11-30T00:00:00.000000000', '1996-12-31T00:00:00.000000000',\n",
       "       '1997-01-31T00:00:00.000000000', '1997-02-28T00:00:00.000000000',\n",
       "       '1997-03-31T00:00:00.000000000', '1997-04-30T00:00:00.000000000',\n",
       "       '1997-05-31T00:00:00.000000000', '1997-06-30T00:00:00.000000000',\n",
       "       '1997-07-31T00:00:00.000000000', '1997-08-31T00:00:00.000000000',\n",
       "       '1997-09-30T00:00:00.000000000', '1997-10-31T00:00:00.000000000',\n",
       "       '1997-11-30T00:00:00.000000000', '1997-12-31T00:00:00.000000000',\n",
       "       '1998-01-31T00:00:00.000000000', '1998-02-28T00:00:00.000000000',\n",
       "       '1998-03-31T00:00:00.000000000', '1998-04-30T00:00:00.000000000',\n",
       "       '1998-05-31T00:00:00.000000000', '1998-06-30T00:00:00.000000000',\n",
       "       '1998-07-31T00:00:00.000000000', '1998-08-31T00:00:00.000000000',\n",
       "       '1998-09-30T00:00:00.000000000', '1998-10-31T00:00:00.000000000',\n",
       "       '1998-11-30T00:00:00.000000000', '1998-12-31T00:00:00.000000000',\n",
       "       '1999-01-31T00:00:00.000000000', '1999-02-28T00:00:00.000000000',\n",
       "       '1999-03-31T00:00:00.000000000', '1999-04-30T00:00:00.000000000',\n",
       "       '1999-05-31T00:00:00.000000000', '1999-06-30T00:00:00.000000000',\n",
       "       '1999-07-31T00:00:00.000000000', '1999-08-31T00:00:00.000000000',\n",
       "       '1999-09-30T00:00:00.000000000', '1999-10-31T00:00:00.000000000',\n",
       "       '1999-11-30T00:00:00.000000000', '1999-12-31T00:00:00.000000000',\n",
       "       '2000-01-31T00:00:00.000000000', '2000-02-29T00:00:00.000000000',\n",
       "       '2000-03-31T00:00:00.000000000', '2000-04-30T00:00:00.000000000',\n",
       "       '2000-05-31T00:00:00.000000000', '2000-06-30T00:00:00.000000000',\n",
       "       '2000-07-31T00:00:00.000000000', '2000-08-31T00:00:00.000000000',\n",
       "       '2000-09-30T00:00:00.000000000', '2000-10-31T00:00:00.000000000',\n",
       "       '2000-11-30T00:00:00.000000000', '2000-12-31T00:00:00.000000000',\n",
       "       '2001-01-31T00:00:00.000000000', '2001-02-28T00:00:00.000000000',\n",
       "       '2001-03-31T00:00:00.000000000', '2001-04-30T00:00:00.000000000',\n",
       "       '2001-05-31T00:00:00.000000000', '2001-06-30T00:00:00.000000000',\n",
       "       '2001-07-31T00:00:00.000000000', '2001-08-31T00:00:00.000000000',\n",
       "       '2001-09-30T00:00:00.000000000', '2001-10-31T00:00:00.000000000',\n",
       "       '2001-11-30T00:00:00.000000000', '2001-12-31T00:00:00.000000000',\n",
       "       '2002-01-31T00:00:00.000000000', '2002-02-28T00:00:00.000000000',\n",
       "       '2002-03-31T00:00:00.000000000', '2002-04-30T00:00:00.000000000',\n",
       "       '2002-05-31T00:00:00.000000000', '2002-06-30T00:00:00.000000000',\n",
       "       '2002-07-31T00:00:00.000000000', '2002-08-31T00:00:00.000000000',\n",
       "       '2002-09-30T00:00:00.000000000', '2002-10-31T00:00:00.000000000',\n",
       "       '2002-11-30T00:00:00.000000000', '2002-12-31T00:00:00.000000000',\n",
       "       '2003-01-31T00:00:00.000000000', '2003-02-28T00:00:00.000000000',\n",
       "       '2003-03-31T00:00:00.000000000', '2003-04-30T00:00:00.000000000',\n",
       "       '2003-05-31T00:00:00.000000000', '2003-06-30T00:00:00.000000000',\n",
       "       '2003-07-31T00:00:00.000000000', '2003-08-31T00:00:00.000000000',\n",
       "       '2003-09-30T00:00:00.000000000', '2003-10-31T00:00:00.000000000',\n",
       "       '2003-11-30T00:00:00.000000000', '2003-12-31T00:00:00.000000000',\n",
       "       '2004-01-31T00:00:00.000000000', '2004-02-29T00:00:00.000000000',\n",
       "       '2004-03-31T00:00:00.000000000', '2004-04-30T00:00:00.000000000',\n",
       "       '2004-05-31T00:00:00.000000000', '2004-06-30T00:00:00.000000000',\n",
       "       '2004-07-31T00:00:00.000000000', '2004-08-31T00:00:00.000000000',\n",
       "       '2004-09-30T00:00:00.000000000', '2004-10-31T00:00:00.000000000',\n",
       "       '2004-11-30T00:00:00.000000000', '2004-12-31T00:00:00.000000000',\n",
       "       '2005-01-31T00:00:00.000000000', '2005-02-28T00:00:00.000000000',\n",
       "       '2005-03-31T00:00:00.000000000', '2005-04-30T00:00:00.000000000',\n",
       "       '2005-05-31T00:00:00.000000000', '2005-06-30T00:00:00.000000000',\n",
       "       '2005-07-31T00:00:00.000000000', '2005-08-31T00:00:00.000000000',\n",
       "       '2005-09-30T00:00:00.000000000', '2005-10-31T00:00:00.000000000',\n",
       "       '2005-11-30T00:00:00.000000000', '2005-12-31T00:00:00.000000000',\n",
       "       '2006-01-31T00:00:00.000000000', '2006-02-28T00:00:00.000000000',\n",
       "       '2006-03-31T00:00:00.000000000', '2006-04-30T00:00:00.000000000',\n",
       "       '2006-05-31T00:00:00.000000000', '2006-06-30T00:00:00.000000000',\n",
       "       '2006-07-31T00:00:00.000000000', '2006-08-31T00:00:00.000000000',\n",
       "       '2006-09-30T00:00:00.000000000', '2006-10-31T00:00:00.000000000',\n",
       "       '2006-11-30T00:00:00.000000000', '2006-12-31T00:00:00.000000000',\n",
       "       '2007-01-31T00:00:00.000000000', '2007-02-28T00:00:00.000000000',\n",
       "       '2007-03-31T00:00:00.000000000', '2007-04-30T00:00:00.000000000',\n",
       "       '2007-05-31T00:00:00.000000000', '2007-06-30T00:00:00.000000000',\n",
       "       '2007-07-31T00:00:00.000000000', '2007-08-31T00:00:00.000000000',\n",
       "       '2007-09-30T00:00:00.000000000', '2007-10-31T00:00:00.000000000',\n",
       "       '2007-11-30T00:00:00.000000000', '2007-12-31T00:00:00.000000000',\n",
       "       '2008-01-31T00:00:00.000000000', '2008-02-29T00:00:00.000000000',\n",
       "       '2008-03-31T00:00:00.000000000', '2008-04-30T00:00:00.000000000',\n",
       "       '2008-05-31T00:00:00.000000000', '2008-06-30T00:00:00.000000000',\n",
       "       '2008-07-31T00:00:00.000000000', '2008-08-31T00:00:00.000000000',\n",
       "       '2008-09-30T00:00:00.000000000', '2008-10-31T00:00:00.000000000',\n",
       "       '2008-11-30T00:00:00.000000000', '2008-12-31T00:00:00.000000000',\n",
       "       '2009-01-31T00:00:00.000000000', '2009-02-28T00:00:00.000000000',\n",
       "       '2009-03-31T00:00:00.000000000', '2009-04-30T00:00:00.000000000',\n",
       "       '2009-05-31T00:00:00.000000000', '2009-06-30T00:00:00.000000000',\n",
       "       '2009-07-31T00:00:00.000000000', '2009-08-31T00:00:00.000000000',\n",
       "       '2009-09-30T00:00:00.000000000', '2009-10-31T00:00:00.000000000',\n",
       "       '2009-11-30T00:00:00.000000000', '2009-12-31T00:00:00.000000000',\n",
       "       '2010-01-31T00:00:00.000000000', '2010-02-28T00:00:00.000000000',\n",
       "       '2010-03-31T00:00:00.000000000', '2010-04-30T00:00:00.000000000',\n",
       "       '2010-05-31T00:00:00.000000000', '2010-06-30T00:00:00.000000000',\n",
       "       '2010-07-31T00:00:00.000000000', '2010-08-31T00:00:00.000000000',\n",
       "       '2010-09-30T00:00:00.000000000', '2010-10-31T00:00:00.000000000',\n",
       "       '2010-11-30T00:00:00.000000000', '2010-12-31T00:00:00.000000000',\n",
       "       '2011-01-31T00:00:00.000000000', '2011-02-28T00:00:00.000000000',\n",
       "       '2011-03-31T00:00:00.000000000', '2011-04-30T00:00:00.000000000',\n",
       "       '2011-05-31T00:00:00.000000000', '2011-06-30T00:00:00.000000000',\n",
       "       '2011-07-31T00:00:00.000000000', '2011-08-31T00:00:00.000000000',\n",
       "       '2011-09-30T00:00:00.000000000', '2011-10-31T00:00:00.000000000',\n",
       "       '2011-11-30T00:00:00.000000000', '2011-12-31T00:00:00.000000000',\n",
       "       '2012-01-31T00:00:00.000000000', '2012-02-29T00:00:00.000000000',\n",
       "       '2012-03-31T00:00:00.000000000', '2012-04-30T00:00:00.000000000',\n",
       "       '2012-05-31T00:00:00.000000000', '2012-06-30T00:00:00.000000000',\n",
       "       '2012-07-31T00:00:00.000000000', '2012-08-31T00:00:00.000000000',\n",
       "       '2012-09-30T00:00:00.000000000', '2012-10-31T00:00:00.000000000',\n",
       "       '2012-11-30T00:00:00.000000000', '2012-12-31T00:00:00.000000000',\n",
       "       '2013-01-31T00:00:00.000000000', '2013-02-28T00:00:00.000000000',\n",
       "       '2013-03-31T00:00:00.000000000', '2013-04-30T00:00:00.000000000',\n",
       "       '2013-05-31T00:00:00.000000000', '2013-06-30T00:00:00.000000000',\n",
       "       '2013-07-31T00:00:00.000000000', '2013-08-31T00:00:00.000000000',\n",
       "       '2013-09-30T00:00:00.000000000', '2013-10-31T00:00:00.000000000',\n",
       "       '2013-11-30T00:00:00.000000000', '2013-12-31T00:00:00.000000000',\n",
       "       '2014-01-31T00:00:00.000000000', '2014-02-28T00:00:00.000000000',\n",
       "       '2014-03-31T00:00:00.000000000', '2014-04-30T00:00:00.000000000',\n",
       "       '2014-05-31T00:00:00.000000000', '2014-06-30T00:00:00.000000000',\n",
       "       '2014-07-31T00:00:00.000000000', '2014-08-31T00:00:00.000000000',\n",
       "       '2014-09-30T00:00:00.000000000', '2014-10-31T00:00:00.000000000',\n",
       "       '2014-11-30T00:00:00.000000000', '2014-12-31T00:00:00.000000000',\n",
       "       '2015-01-31T00:00:00.000000000', '2015-02-28T00:00:00.000000000',\n",
       "       '2015-03-31T00:00:00.000000000', '2015-04-30T00:00:00.000000000',\n",
       "       '2015-05-31T00:00:00.000000000', '2015-06-30T00:00:00.000000000',\n",
       "       '2015-07-31T00:00:00.000000000', '2015-08-31T00:00:00.000000000',\n",
       "       '2015-09-30T00:00:00.000000000', '2015-10-31T00:00:00.000000000',\n",
       "       '2015-11-30T00:00:00.000000000', '2015-12-31T00:00:00.000000000',\n",
       "       '2016-01-31T00:00:00.000000000', '2016-02-29T00:00:00.000000000',\n",
       "       '2016-03-31T00:00:00.000000000', '2016-04-30T00:00:00.000000000',\n",
       "       '2016-05-31T00:00:00.000000000', '2016-06-30T00:00:00.000000000',\n",
       "       '2016-07-31T00:00:00.000000000', '2016-08-31T00:00:00.000000000',\n",
       "       '2016-09-30T00:00:00.000000000', '2016-10-31T00:00:00.000000000',\n",
       "       '2016-11-30T00:00:00.000000000', '2016-12-31T00:00:00.000000000',\n",
       "       '2017-01-31T00:00:00.000000000', '2017-02-28T00:00:00.000000000',\n",
       "       '2017-03-31T00:00:00.000000000', '2017-04-30T00:00:00.000000000',\n",
       "       '2017-05-31T00:00:00.000000000', '2017-06-30T00:00:00.000000000',\n",
       "       '2017-07-31T00:00:00.000000000', '2017-08-31T00:00:00.000000000',\n",
       "       '2017-09-30T00:00:00.000000000', '2017-10-31T00:00:00.000000000',\n",
       "       '2017-11-30T00:00:00.000000000', '2017-12-31T00:00:00.000000000',\n",
       "       '2018-01-31T00:00:00.000000000', '2018-02-28T00:00:00.000000000',\n",
       "       '2018-03-31T00:00:00.000000000', '2018-04-30T00:00:00.000000000',\n",
       "       '2018-05-31T00:00:00.000000000', '2018-06-30T00:00:00.000000000',\n",
       "       '2018-07-31T00:00:00.000000000', '2018-08-31T00:00:00.000000000',\n",
       "       '2018-09-30T00:00:00.000000000', '2018-10-31T00:00:00.000000000',\n",
       "       '2018-11-30T00:00:00.000000000', '2018-12-31T00:00:00.000000000',\n",
       "       '2019-01-31T00:00:00.000000000', '2019-02-28T00:00:00.000000000',\n",
       "       '2019-03-31T00:00:00.000000000', '2019-04-30T00:00:00.000000000',\n",
       "       '2019-05-31T00:00:00.000000000', '2019-06-30T00:00:00.000000000',\n",
       "       '2019-07-31T00:00:00.000000000', '2019-08-31T00:00:00.000000000',\n",
       "       '2019-09-30T00:00:00.000000000', '2019-10-31T00:00:00.000000000',\n",
       "       '2019-11-30T00:00:00.000000000', '2019-12-31T00:00:00.000000000',\n",
       "       '2020-01-31T00:00:00.000000000', '2020-02-29T00:00:00.000000000',\n",
       "       '2020-03-31T00:00:00.000000000', '2020-04-30T00:00:00.000000000',\n",
       "       '2020-05-31T00:00:00.000000000', '2020-06-30T00:00:00.000000000',\n",
       "       '2020-07-31T00:00:00.000000000', '2020-08-31T00:00:00.000000000',\n",
       "       '2020-09-30T00:00:00.000000000', '2020-10-31T00:00:00.000000000',\n",
       "       '2020-11-30T00:00:00.000000000', '2020-12-31T00:00:00.000000000'],\n",
       "      dtype='datetime64[ns]')"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"eom\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#start from 1997--> first year with complete data for all months"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[~(df['eom'] < '1997-01-31')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "288\n"
     ]
    }
   ],
   "source": [
    "#the new observation period contains 24 years (288 Months) form 01.1997 to 12.2020 \n",
    "print(df['eom'].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>id</th>\n",
       "      <th>date</th>\n",
       "      <th>eom</th>\n",
       "      <th>size_grp</th>\n",
       "      <th>primary_sec</th>\n",
       "      <th>curcd</th>\n",
       "      <th>fx</th>\n",
       "      <th>common</th>\n",
       "      <th>comp_tpci</th>\n",
       "      <th>comp_exchg</th>\n",
       "      <th>...</th>\n",
       "      <th>rmax5_rvol_21d</th>\n",
       "      <th>ni_be</th>\n",
       "      <th>ocf_at</th>\n",
       "      <th>ocf_at_chg1</th>\n",
       "      <th>mispricing_perf</th>\n",
       "      <th>mispricing_mgmt</th>\n",
       "      <th>qmj</th>\n",
       "      <th>qmj_prof</th>\n",
       "      <th>qmj_growth</th>\n",
       "      <th>qmj_safety</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3149</th>\n",
       "      <td>comp_010787_02W</td>\n",
       "      <td>1997-01-31</td>\n",
       "      <td>1997-01-31</td>\n",
       "      <td>mega</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.196431</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>1.419911</td>\n",
       "      <td>0.151126</td>\n",
       "      <td>0.091640</td>\n",
       "      <td>-0.019869</td>\n",
       "      <td>0.531032</td>\n",
       "      <td>0.492101</td>\n",
       "      <td>-0.025660</td>\n",
       "      <td>0.068057</td>\n",
       "      <td>-0.042767</td>\n",
       "      <td>-0.056583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3569</th>\n",
       "      <td>comp_011217_05W</td>\n",
       "      <td>1997-01-31</td>\n",
       "      <td>1997-01-31</td>\n",
       "      <td>mega</td>\n",
       "      <td>0</td>\n",
       "      <td>DEM</td>\n",
       "      <td>0.610616</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>163</td>\n",
       "      <td>...</td>\n",
       "      <td>1.544091</td>\n",
       "      <td>0.229582</td>\n",
       "      <td>0.047085</td>\n",
       "      <td>-0.045487</td>\n",
       "      <td>0.531032</td>\n",
       "      <td>0.492101</td>\n",
       "      <td>-0.025660</td>\n",
       "      <td>0.068057</td>\n",
       "      <td>-0.042767</td>\n",
       "      <td>-0.056583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7020</th>\n",
       "      <td>comp_015334_02W</td>\n",
       "      <td>1997-01-31</td>\n",
       "      <td>1997-01-31</td>\n",
       "      <td>mega</td>\n",
       "      <td>0</td>\n",
       "      <td>DEM</td>\n",
       "      <td>0.610616</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>1.682550</td>\n",
       "      <td>0.185862</td>\n",
       "      <td>0.114406</td>\n",
       "      <td>-0.036662</td>\n",
       "      <td>0.531032</td>\n",
       "      <td>0.492101</td>\n",
       "      <td>-0.025660</td>\n",
       "      <td>0.068057</td>\n",
       "      <td>-0.042767</td>\n",
       "      <td>-0.056583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7423</th>\n",
       "      <td>comp_015496_01W</td>\n",
       "      <td>1997-01-31</td>\n",
       "      <td>1997-01-31</td>\n",
       "      <td>small</td>\n",
       "      <td>0</td>\n",
       "      <td>DEM</td>\n",
       "      <td>0.610616</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>1.465363</td>\n",
       "      <td>0.096171</td>\n",
       "      <td>0.004535</td>\n",
       "      <td>-0.002389</td>\n",
       "      <td>0.531032</td>\n",
       "      <td>0.492101</td>\n",
       "      <td>-0.025660</td>\n",
       "      <td>0.068057</td>\n",
       "      <td>-0.042767</td>\n",
       "      <td>-0.056583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7639</th>\n",
       "      <td>comp_015496_02W</td>\n",
       "      <td>1997-01-31</td>\n",
       "      <td>1997-01-31</td>\n",
       "      <td>large</td>\n",
       "      <td>1</td>\n",
       "      <td>DEM</td>\n",
       "      <td>0.610616</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>1.253663</td>\n",
       "      <td>0.096171</td>\n",
       "      <td>0.004535</td>\n",
       "      <td>-0.002389</td>\n",
       "      <td>0.531032</td>\n",
       "      <td>0.513601</td>\n",
       "      <td>-0.111194</td>\n",
       "      <td>-0.469594</td>\n",
       "      <td>-0.145407</td>\n",
       "      <td>0.603547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306352</th>\n",
       "      <td>comp_333391_05W</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>nano</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.228850</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>0.577215</td>\n",
       "      <td>-0.267562</td>\n",
       "      <td>-0.146800</td>\n",
       "      <td>0.642355</td>\n",
       "      <td>0.504174</td>\n",
       "      <td>0.509169</td>\n",
       "      <td>0.029274</td>\n",
       "      <td>0.030414</td>\n",
       "      <td>0.048699</td>\n",
       "      <td>0.062550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306370</th>\n",
       "      <td>comp_333885_01W</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>large</td>\n",
       "      <td>1</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.228850</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>171</td>\n",
       "      <td>...</td>\n",
       "      <td>0.961956</td>\n",
       "      <td>0.639671</td>\n",
       "      <td>0.193740</td>\n",
       "      <td>0.074673</td>\n",
       "      <td>0.664996</td>\n",
       "      <td>0.382121</td>\n",
       "      <td>0.029274</td>\n",
       "      <td>1.618001</td>\n",
       "      <td>0.048699</td>\n",
       "      <td>-0.446789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306384</th>\n",
       "      <td>comp_334036_02W</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>nano</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.228850</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>1.713296</td>\n",
       "      <td>-1.335185</td>\n",
       "      <td>-0.366940</td>\n",
       "      <td>0.061961</td>\n",
       "      <td>0.504174</td>\n",
       "      <td>0.509169</td>\n",
       "      <td>0.029274</td>\n",
       "      <td>0.030414</td>\n",
       "      <td>0.048699</td>\n",
       "      <td>0.062550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306389</th>\n",
       "      <td>comp_334302_02W</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>micro</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.228850</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>257</td>\n",
       "      <td>...</td>\n",
       "      <td>1.024812</td>\n",
       "      <td>0.251130</td>\n",
       "      <td>0.075040</td>\n",
       "      <td>-0.115848</td>\n",
       "      <td>0.504174</td>\n",
       "      <td>0.509169</td>\n",
       "      <td>0.029274</td>\n",
       "      <td>0.030414</td>\n",
       "      <td>0.048699</td>\n",
       "      <td>0.062550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306410</th>\n",
       "      <td>comp_336873_02W</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>nano</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.228850</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>1.696445</td>\n",
       "      <td>0.056421</td>\n",
       "      <td>-0.379762</td>\n",
       "      <td>-0.467364</td>\n",
       "      <td>0.504174</td>\n",
       "      <td>0.509169</td>\n",
       "      <td>0.029274</td>\n",
       "      <td>0.030414</td>\n",
       "      <td>0.048699</td>\n",
       "      <td>0.062550</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 174 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     id       date        eom size_grp  primary_sec curcd  \\\n",
       "3149    comp_010787_02W 1997-01-31 1997-01-31     mega            0   EUR   \n",
       "3569    comp_011217_05W 1997-01-31 1997-01-31     mega            0   DEM   \n",
       "7020    comp_015334_02W 1997-01-31 1997-01-31     mega            0   DEM   \n",
       "7423    comp_015496_01W 1997-01-31 1997-01-31    small            0   DEM   \n",
       "7639    comp_015496_02W 1997-01-31 1997-01-31    large            1   DEM   \n",
       "306352  comp_333391_05W 2020-12-30 2020-12-31     nano            0   EUR   \n",
       "306370  comp_333885_01W 2020-12-30 2020-12-31    large            1   EUR   \n",
       "306384  comp_334036_02W 2020-12-30 2020-12-31     nano            0   EUR   \n",
       "306389  comp_334302_02W 2020-12-30 2020-12-31    micro            0   EUR   \n",
       "306410  comp_336873_02W 2020-12-30 2020-12-31     nano            0   EUR   \n",
       "\n",
       "              fx  common comp_tpci  comp_exchg  ...  rmax5_rvol_21d     ni_be  \\\n",
       "3149    1.196431       1         0         154  ...        1.419911  0.151126   \n",
       "3569    0.610616       1         0         163  ...        1.544091  0.229582   \n",
       "7020    0.610616       1         0         154  ...        1.682550  0.185862   \n",
       "7423    0.610616       1         0         154  ...        1.465363  0.096171   \n",
       "7639    0.610616       1         0         154  ...        1.253663  0.096171   \n",
       "306352  1.228850       1         0         154  ...        0.577215 -0.267562   \n",
       "306370  1.228850       1         0         171  ...        0.961956  0.639671   \n",
       "306384  1.228850       1         0         154  ...        1.713296 -1.335185   \n",
       "306389  1.228850       1         0         257  ...        1.024812  0.251130   \n",
       "306410  1.228850       1         0         154  ...        1.696445  0.056421   \n",
       "\n",
       "          ocf_at  ocf_at_chg1  mispricing_perf  mispricing_mgmt       qmj  \\\n",
       "3149    0.091640    -0.019869         0.531032         0.492101 -0.025660   \n",
       "3569    0.047085    -0.045487         0.531032         0.492101 -0.025660   \n",
       "7020    0.114406    -0.036662         0.531032         0.492101 -0.025660   \n",
       "7423    0.004535    -0.002389         0.531032         0.492101 -0.025660   \n",
       "7639    0.004535    -0.002389         0.531032         0.513601 -0.111194   \n",
       "306352 -0.146800     0.642355         0.504174         0.509169  0.029274   \n",
       "306370  0.193740     0.074673         0.664996         0.382121  0.029274   \n",
       "306384 -0.366940     0.061961         0.504174         0.509169  0.029274   \n",
       "306389  0.075040    -0.115848         0.504174         0.509169  0.029274   \n",
       "306410 -0.379762    -0.467364         0.504174         0.509169  0.029274   \n",
       "\n",
       "        qmj_prof  qmj_growth  qmj_safety  \n",
       "3149    0.068057   -0.042767   -0.056583  \n",
       "3569    0.068057   -0.042767   -0.056583  \n",
       "7020    0.068057   -0.042767   -0.056583  \n",
       "7423    0.068057   -0.042767   -0.056583  \n",
       "7639   -0.469594   -0.145407    0.603547  \n",
       "306352  0.030414    0.048699    0.062550  \n",
       "306370  1.618001    0.048699   -0.446789  \n",
       "306384  0.030414    0.048699    0.062550  \n",
       "306389  0.030414    0.048699    0.062550  \n",
       "306410  0.030414    0.048699    0.062550  \n",
       "\n",
       "[10 rows x 174 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(5).append(df.tail(5))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2660\n"
     ]
    }
   ],
   "source": [
    "#There are 2660 unique german stocks\n",
    "print(df['id'].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "370"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#There are 370 unique SIC-codes\n",
    "df['sic'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 306411 entries, 3557 to 306410\n",
      "Data columns (total 174 columns):\n",
      " #   Column                Dtype         \n",
      "---  ------                -----         \n",
      " 0   id                    object        \n",
      " 1   date                  datetime64[ns]\n",
      " 2   eom                   datetime64[ns]\n",
      " 3   size_grp              object        \n",
      " 4   primary_sec           int64         \n",
      " 5   curcd                 object        \n",
      " 6   fx                    float64       \n",
      " 7   common                int64         \n",
      " 8   comp_tpci             object        \n",
      " 9   comp_exchg            int64         \n",
      " 10  sic                   float64       \n",
      " 11  shares                float64       \n",
      " 12  me_company            float64       \n",
      " 13  prc_local             float64       \n",
      " 14  dolvol                float64       \n",
      " 15  ret                   float64       \n",
      " 16  ret_local             float64       \n",
      " 17  ret_exc               float64       \n",
      " 18  ret_lag_dif           int64         \n",
      " 19  ret_exc_lead1m        float64       \n",
      " 20  enterprise_value      float64       \n",
      " 21  book_equity           float64       \n",
      " 22  assets                float64       \n",
      " 23  sales                 float64       \n",
      " 24  net_income            float64       \n",
      " 25  bidask                int64         \n",
      " 26  prc_high              float64       \n",
      " 27  prc_low               float64       \n",
      " 28  tvol                  float64       \n",
      " 29  niq_su                float64       \n",
      " 30  ret_6_1               float64       \n",
      " 31  ret_12_1              float64       \n",
      " 32  saleq_su              float64       \n",
      " 33  tax_gr1a              float64       \n",
      " 34  ni_inc8q              float64       \n",
      " 35  prc_highprc_252d      float64       \n",
      " 36  resff3_6_1            float64       \n",
      " 37  resff3_12_1           float64       \n",
      " 38  be_me                 float64       \n",
      " 39  debt_me               float64       \n",
      " 40  at_me                 float64       \n",
      " 41  ret_60_12             float64       \n",
      " 42  ni_me                 float64       \n",
      " 43  fcf_me                float64       \n",
      " 44  div12m_me             float64       \n",
      " 45  eqpo_me               float64       \n",
      " 46  eqnpo_me              float64       \n",
      " 47  sale_gr3              float64       \n",
      " 48  sale_gr1              float64       \n",
      " 49  ebitda_mev            float64       \n",
      " 50  sale_me               float64       \n",
      " 51  ocf_me                float64       \n",
      " 52  intrinsic_value       float64       \n",
      " 53  bev_mev               float64       \n",
      " 54  netdebt_me            float64       \n",
      " 55  eq_dur                float64       \n",
      " 56  capex_abn             float64       \n",
      " 57  at_gr1                float64       \n",
      " 58  ppeinv_gr1a           float64       \n",
      " 59  noa_at                float64       \n",
      " 60  noa_gr1a              float64       \n",
      " 61  lnoa_gr1a             float64       \n",
      " 62  capx_gr1              float64       \n",
      " 63  capx_gr2              float64       \n",
      " 64  capx_gr3              float64       \n",
      " 65  chcsho_12m            float64       \n",
      " 66  eqnpo_12m             float64       \n",
      " 67  debt_gr3              float64       \n",
      " 68  inv_gr1               float64       \n",
      " 69  inv_gr1a              float64       \n",
      " 70  oaccruals_at          float64       \n",
      " 71  taccruals_at          float64       \n",
      " 72  cowc_gr1a             float64       \n",
      " 73  coa_gr1a              float64       \n",
      " 74  col_gr1a              float64       \n",
      " 75  nncoa_gr1a            float64       \n",
      " 76  ncoa_gr1a             float64       \n",
      " 77  ncol_gr1a             float64       \n",
      " 78  nfna_gr1a             float64       \n",
      " 79  sti_gr1a              float64       \n",
      " 80  lti_gr1a              float64       \n",
      " 81  fnl_gr1a              float64       \n",
      " 82  be_gr1a               float64       \n",
      " 83  oaccruals_ni          float64       \n",
      " 84  taccruals_ni          float64       \n",
      " 85  netis_at              float64       \n",
      " 86  eqnetis_at            float64       \n",
      " 87  dbnetis_at            float64       \n",
      " 88  niq_be                float64       \n",
      " 89  niq_be_chg1           float64       \n",
      " 90  niq_at                float64       \n",
      " 91  niq_at_chg1           float64       \n",
      " 92  ebit_bev              float64       \n",
      " 93  ebit_sale             float64       \n",
      " 94  sale_bev              float64       \n",
      " 95  at_turnover           float64       \n",
      " 96  gp_at                 float64       \n",
      " 97  gp_atl1               float64       \n",
      " 98  ope_be                float64       \n",
      " 99  ope_bel1              float64       \n",
      " 100 op_at                 float64       \n",
      " 101 op_atl1               float64       \n",
      " 102 cop_at                float64       \n",
      " 103 cop_atl1              float64       \n",
      " 104 f_score               float64       \n",
      " 105 o_score               float64       \n",
      " 106 z_score               float64       \n",
      " 107 pi_nix                float64       \n",
      " 108 at_be                 float64       \n",
      " 109 saleq_gr1             float64       \n",
      " 110 rd_me                 float64       \n",
      " 111 rd_sale               float64       \n",
      " 112 opex_at               float64       \n",
      " 113 emp_gr1               float64       \n",
      " 114 rd5_at                float64       \n",
      " 115 age                   int64         \n",
      " 116 dsale_dinv            float64       \n",
      " 117 dsale_drec            float64       \n",
      " 118 dgp_dsale             float64       \n",
      " 119 dsale_dsga            float64       \n",
      " 120 sale_emp_gr1          float64       \n",
      " 121 tangibility           float64       \n",
      " 122 kz_index              float64       \n",
      " 123 ocfq_saleq_std        float64       \n",
      " 124 cash_at               float64       \n",
      " 125 ni_ar1                float64       \n",
      " 126 ni_ivol               float64       \n",
      " 127 earnings_variability  float64       \n",
      " 128 aliq_at               float64       \n",
      " 129 aliq_mat              float64       \n",
      " 130 seas_1_1an            float64       \n",
      " 131 seas_1_1na            float64       \n",
      " 132 seas_2_5an            float64       \n",
      " 133 seas_2_5na            float64       \n",
      " 134 market_equity         float64       \n",
      " 135 ivol_ff3_21d          float64       \n",
      " 136 ivol_capm_252d        float64       \n",
      " 137 ivol_capm_21d         float64       \n",
      " 138 rvol_21d              float64       \n",
      " 139 beta_60m              float64       \n",
      " 140 betabab_1260d         float64       \n",
      " 141 beta_dimson_21d       float64       \n",
      " 142 turnover_126d         float64       \n",
      " 143 turnover_var_126d     float64       \n",
      " 144 dolvol_126d           float64       \n",
      " 145 dolvol_var_126d       float64       \n",
      " 146 prc                   float64       \n",
      " 147 ami_126d              float64       \n",
      " 148 zero_trades_21d       float64       \n",
      " 149 zero_trades_126d      float64       \n",
      " 150 zero_trades_252d      float64       \n",
      " 151 rmax1_21d             float64       \n",
      " 152 rskew_21d             float64       \n",
      " 153 iskew_capm_21d        float64       \n",
      " 154 iskew_ff3_21d         float64       \n",
      " 155 coskew_21d            float64       \n",
      " 156 ret_1_0               float64       \n",
      " 157 betadown_252d         float64       \n",
      " 158 bidaskhl_21d          float64       \n",
      " 159 ret_3_1               float64       \n",
      " 160 ret_9_1               float64       \n",
      " 161 ret_12_7              float64       \n",
      " 162 corr_1260d            float64       \n",
      " 163 rmax5_21d             float64       \n",
      " 164 rmax5_rvol_21d        float64       \n",
      " 165 ni_be                 float64       \n",
      " 166 ocf_at                float64       \n",
      " 167 ocf_at_chg1           float64       \n",
      " 168 mispricing_perf       float64       \n",
      " 169 mispricing_mgmt       float64       \n",
      " 170 qmj                   float64       \n",
      " 171 qmj_prof              float64       \n",
      " 172 qmj_growth            float64       \n",
      " 173 qmj_safety            float64       \n",
      "dtypes: datetime64[ns](2), float64(162), int64(6), object(4)\n",
      "memory usage: 409.1+ MB\n"
     ]
    }
   ],
   "source": [
    "#Inspect variable types\n",
    "df.info(verbose=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Treating Outliers\n",
    "\n",
    "All monthly firm characteristics are winsorized at the 1% and 99% levels to correct for outliers. In contrast to Gu, Kelly, and Xiu (2020), the excess returns are also winsorized."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['shares',\n",
       " 'me_company',\n",
       " 'prc_local',\n",
       " 'dolvol',\n",
       " 'ret',\n",
       " 'ret_local',\n",
       " 'ret_exc',\n",
       " 'ret_lag_dif',\n",
       " 'ret_exc_lead1m',\n",
       " 'enterprise_value',\n",
       " 'book_equity',\n",
       " 'assets',\n",
       " 'sales',\n",
       " 'net_income',\n",
       " 'bidask',\n",
       " 'prc_high',\n",
       " 'prc_low',\n",
       " 'tvol',\n",
       " 'niq_su',\n",
       " 'ret_6_1',\n",
       " 'ret_12_1',\n",
       " 'saleq_su',\n",
       " 'tax_gr1a',\n",
       " 'ni_inc8q',\n",
       " 'prc_highprc_252d',\n",
       " 'resff3_6_1',\n",
       " 'resff3_12_1',\n",
       " 'be_me',\n",
       " 'debt_me',\n",
       " 'at_me',\n",
       " 'ret_60_12',\n",
       " 'ni_me',\n",
       " 'fcf_me',\n",
       " 'div12m_me',\n",
       " 'eqpo_me',\n",
       " 'eqnpo_me',\n",
       " 'sale_gr3',\n",
       " 'sale_gr1',\n",
       " 'ebitda_mev',\n",
       " 'sale_me',\n",
       " 'ocf_me',\n",
       " 'intrinsic_value',\n",
       " 'bev_mev',\n",
       " 'netdebt_me',\n",
       " 'eq_dur',\n",
       " 'capex_abn',\n",
       " 'at_gr1',\n",
       " 'ppeinv_gr1a',\n",
       " 'noa_at',\n",
       " 'noa_gr1a',\n",
       " 'lnoa_gr1a',\n",
       " 'capx_gr1',\n",
       " 'capx_gr2',\n",
       " 'capx_gr3',\n",
       " 'chcsho_12m',\n",
       " 'eqnpo_12m',\n",
       " 'debt_gr3',\n",
       " 'inv_gr1',\n",
       " 'inv_gr1a',\n",
       " 'oaccruals_at',\n",
       " 'taccruals_at',\n",
       " 'cowc_gr1a',\n",
       " 'coa_gr1a',\n",
       " 'col_gr1a',\n",
       " 'nncoa_gr1a',\n",
       " 'ncoa_gr1a',\n",
       " 'ncol_gr1a',\n",
       " 'nfna_gr1a',\n",
       " 'sti_gr1a',\n",
       " 'lti_gr1a',\n",
       " 'fnl_gr1a',\n",
       " 'be_gr1a',\n",
       " 'oaccruals_ni',\n",
       " 'taccruals_ni',\n",
       " 'netis_at',\n",
       " 'eqnetis_at',\n",
       " 'dbnetis_at',\n",
       " 'niq_be',\n",
       " 'niq_be_chg1',\n",
       " 'niq_at',\n",
       " 'niq_at_chg1',\n",
       " 'ebit_bev',\n",
       " 'ebit_sale',\n",
       " 'sale_bev',\n",
       " 'at_turnover',\n",
       " 'gp_at',\n",
       " 'gp_atl1',\n",
       " 'ope_be',\n",
       " 'ope_bel1',\n",
       " 'op_at',\n",
       " 'op_atl1',\n",
       " 'cop_at',\n",
       " 'cop_atl1',\n",
       " 'f_score',\n",
       " 'o_score',\n",
       " 'z_score',\n",
       " 'pi_nix',\n",
       " 'at_be',\n",
       " 'saleq_gr1',\n",
       " 'rd_me',\n",
       " 'rd_sale',\n",
       " 'opex_at',\n",
       " 'emp_gr1',\n",
       " 'rd5_at',\n",
       " 'age',\n",
       " 'dsale_dinv',\n",
       " 'dsale_drec',\n",
       " 'dgp_dsale',\n",
       " 'dsale_dsga',\n",
       " 'sale_emp_gr1',\n",
       " 'tangibility',\n",
       " 'kz_index',\n",
       " 'ocfq_saleq_std',\n",
       " 'cash_at',\n",
       " 'ni_ar1',\n",
       " 'ni_ivol',\n",
       " 'earnings_variability',\n",
       " 'aliq_at',\n",
       " 'aliq_mat',\n",
       " 'seas_1_1an',\n",
       " 'seas_1_1na',\n",
       " 'seas_2_5an',\n",
       " 'seas_2_5na',\n",
       " 'market_equity',\n",
       " 'ivol_ff3_21d',\n",
       " 'ivol_capm_252d',\n",
       " 'ivol_capm_21d',\n",
       " 'rvol_21d',\n",
       " 'beta_60m',\n",
       " 'betabab_1260d',\n",
       " 'beta_dimson_21d',\n",
       " 'turnover_126d',\n",
       " 'turnover_var_126d',\n",
       " 'dolvol_126d',\n",
       " 'dolvol_var_126d',\n",
       " 'prc',\n",
       " 'ami_126d',\n",
       " 'zero_trades_21d',\n",
       " 'zero_trades_126d',\n",
       " 'zero_trades_252d',\n",
       " 'rmax1_21d',\n",
       " 'rskew_21d',\n",
       " 'iskew_capm_21d',\n",
       " 'iskew_ff3_21d',\n",
       " 'coskew_21d',\n",
       " 'ret_1_0',\n",
       " 'betadown_252d',\n",
       " 'bidaskhl_21d',\n",
       " 'ret_3_1',\n",
       " 'ret_9_1',\n",
       " 'ret_12_7',\n",
       " 'corr_1260d',\n",
       " 'rmax5_21d',\n",
       " 'rmax5_rvol_21d',\n",
       " 'ni_be',\n",
       " 'ocf_at',\n",
       " 'ocf_at_chg1',\n",
       " 'mispricing_perf',\n",
       " 'mispricing_mgmt',\n",
       " 'qmj',\n",
       " 'qmj_prof',\n",
       " 'qmj_growth',\n",
       " 'qmj_safety']"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "features = df.columns[11:174].tolist()\n",
    "features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[features] = df[features].apply(lambda x: x.clip(*x.quantile([0.01, 0.99])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-64-f07716cb0f4d>:2: FutureWarning: Treating datetime data as categorical rather than numeric in `.describe` is deprecated and will be removed in a future version of pandas. Specify `datetime_is_numeric=True` to silence this warning and adopt the future behavior now.\n",
      "  print(df.describe(include='all'))\n",
      "<ipython-input-64-f07716cb0f4d>:2: FutureWarning: Treating datetime data as categorical rather than numeric in `.describe` is deprecated and will be removed in a future version of pandas. Specify `datetime_is_numeric=True` to silence this warning and adopt the future behavior now.\n",
      "  print(df.describe(include='all'))\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                     id                 date                  eom size_grp  \\\n",
      "count            302640               302640               302640   302640   \n",
      "unique             2660                 4862                  288        5   \n",
      "top     comp_200759_02W  2014-12-30 00:00:00  2014-10-31 00:00:00    micro   \n",
      "freq                288                 1250                 1411   100999   \n",
      "first               NaN  1997-01-02 00:00:00  1997-01-31 00:00:00      NaN   \n",
      "last                NaN  2020-12-30 00:00:00  2020-12-31 00:00:00      NaN   \n",
      "mean                NaN                  NaN                  NaN      NaN   \n",
      "std                 NaN                  NaN                  NaN      NaN   \n",
      "min                 NaN                  NaN                  NaN      NaN   \n",
      "25%                 NaN                  NaN                  NaN      NaN   \n",
      "50%                 NaN                  NaN                  NaN      NaN   \n",
      "75%                 NaN                  NaN                  NaN      NaN   \n",
      "max                 NaN                  NaN                  NaN      NaN   \n",
      "\n",
      "          primary_sec   curcd             fx         common comp_tpci  \\\n",
      "count   302640.000000  302640  302640.000000  302640.000000    302640   \n",
      "unique            NaN       5            NaN            NaN         7   \n",
      "top               NaN     EUR            NaN            NaN         0   \n",
      "freq              NaN  292093            NaN            NaN    266577   \n",
      "first             NaN     NaN            NaN            NaN       NaN   \n",
      "last              NaN     NaN            NaN            NaN       NaN   \n",
      "mean         0.624894     NaN       1.195905       0.880839       NaN   \n",
      "std          0.484151     NaN       0.196060       0.323979       NaN   \n",
      "min          0.000000     NaN       0.078746       0.000000       NaN   \n",
      "25%          0.000000     NaN       1.104040       1.000000       NaN   \n",
      "50%          1.000000     NaN       1.216082       1.000000       NaN   \n",
      "75%          1.000000     NaN       1.328414       1.000000       NaN   \n",
      "max          1.000000     NaN       2.077200       1.000000       NaN   \n",
      "\n",
      "           comp_exchg            sic         shares     me_company  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean       164.740371    4815.858466     530.850179    5181.338233   \n",
      "std         22.425246    2020.192060    1751.164453   15170.204220   \n",
      "min        115.000000     100.000000       0.120000       1.328290   \n",
      "25%        154.000000    3330.000000       5.335000      42.349568   \n",
      "50%        154.000000    4810.000000      21.000000     227.039699   \n",
      "75%        171.000000    6510.000000     195.000000    2079.563390   \n",
      "max        257.000000    9998.000000   13151.244320   99669.950582   \n",
      "\n",
      "            prc_local        dolvol            ret      ret_local  \\\n",
      "count   302640.000000  3.026400e+05  302640.000000  302640.000000   \n",
      "unique            NaN           NaN            NaN            NaN   \n",
      "top               NaN           NaN            NaN            NaN   \n",
      "freq              NaN           NaN            NaN            NaN   \n",
      "first             NaN           NaN            NaN            NaN   \n",
      "last              NaN           NaN            NaN            NaN   \n",
      "mean        44.360808  6.989198e+07       0.006129       0.005513   \n",
      "std        114.790895  3.548110e+08       0.138113       0.133884   \n",
      "min          0.018000  2.151535e+02      -0.386103      -0.379375   \n",
      "25%          2.500000  7.120035e+04      -0.061211      -0.057292   \n",
      "50%          9.920000  3.649790e+05       0.000387       0.000000   \n",
      "75%         31.820000  3.460392e+06       0.063652       0.058824   \n",
      "max        814.000000  2.912537e+09       0.548973       0.538462   \n",
      "\n",
      "              ret_exc  ret_lag_dif  ret_exc_lead1m  enterprise_value  \\\n",
      "count   302640.000000     302640.0   302640.000000     302640.000000   \n",
      "unique            NaN          NaN             NaN               NaN   \n",
      "top               NaN          NaN             NaN               NaN   \n",
      "freq              NaN          NaN             NaN               NaN   \n",
      "first             NaN          NaN             NaN               NaN   \n",
      "last              NaN          NaN             NaN               NaN   \n",
      "mean         0.004853          1.0        0.004882       7896.217387   \n",
      "std          0.138154          0.0        0.136614      23887.448432   \n",
      "min         -0.387614          1.0       -0.382309          1.674310   \n",
      "25%         -0.062610          1.0       -0.061935         71.777856   \n",
      "50%         -0.000917          1.0       -0.000855        336.718051   \n",
      "75%          0.062499          1.0        0.061924       2687.172848   \n",
      "max          0.547134          1.0        0.538720     154387.218518   \n",
      "\n",
      "          book_equity         assets          sales     net_income    bidask  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000  302640.0   \n",
      "unique            NaN            NaN            NaN            NaN       NaN   \n",
      "top               NaN            NaN            NaN            NaN       NaN   \n",
      "freq              NaN            NaN            NaN            NaN       NaN   \n",
      "first             NaN            NaN            NaN            NaN       NaN   \n",
      "last              NaN            NaN            NaN            NaN       NaN   \n",
      "mean      3429.872401   16920.162561    5882.387029     335.487739       0.0   \n",
      "std      10319.548406   61162.869238   16658.180780    1146.886002       0.0   \n",
      "min          0.798406       1.536630       0.000000    -906.759000       0.0   \n",
      "25%         33.795956      69.248611      45.812237      -0.491999       0.0   \n",
      "50%        158.415981     373.365137     286.376658       7.590096       0.0   \n",
      "75%       1284.385531    3869.275554    2423.274425     107.016452       0.0   \n",
      "max      67983.815479  455141.184114  103779.051478    7816.328802       0.0   \n",
      "\n",
      "             prc_high        prc_low          tvol         niq_su  \\\n",
      "count   302640.000000  302640.000000  3.026400e+05  302640.000000   \n",
      "unique            NaN            NaN           NaN            NaN   \n",
      "top               NaN            NaN           NaN            NaN   \n",
      "freq              NaN            NaN           NaN            NaN   \n",
      "first             NaN            NaN           NaN            NaN   \n",
      "last              NaN            NaN           NaN            NaN   \n",
      "mean        49.157508      43.181383  1.827060e+06      -0.039518   \n",
      "std        117.647545     105.233369  7.954125e+06       1.167833   \n",
      "min          0.028158       0.016078  1.500000e+01      -4.770180   \n",
      "25%          3.394753       2.710682  8.400000e+03      -0.366036   \n",
      "50%         12.939310      10.913132  4.926150e+04       0.001613   \n",
      "75%         40.119244      34.464009  2.840682e+05       0.343807   \n",
      "max        824.141652     736.575913  6.388384e+07       3.601906   \n",
      "\n",
      "              ret_6_1       ret_12_1       saleq_su       tax_gr1a  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean         0.030427       0.075176       0.042678       0.000584   \n",
      "std          0.305082       0.484566       1.148085       0.022688   \n",
      "min         -0.678124      -0.824566      -3.662227      -0.095588   \n",
      "25%         -0.128680      -0.198423      -0.506134      -0.004460   \n",
      "50%          0.008501       0.018088       0.088183       0.000115   \n",
      "75%          0.157540       0.251039       0.741639       0.006135   \n",
      "max          1.274001       2.235560       3.256447       0.094363   \n",
      "\n",
      "             ni_inc8q  prc_highprc_252d     resff3_6_1    resff3_12_1  \\\n",
      "count   302640.000000     302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN               NaN            NaN            NaN   \n",
      "top               NaN               NaN            NaN            NaN   \n",
      "freq              NaN               NaN            NaN            NaN   \n",
      "first             NaN               NaN            NaN            NaN   \n",
      "last              NaN               NaN            NaN            NaN   \n",
      "mean         2.272877          0.747733      -0.066346      -0.029151   \n",
      "std          2.442523          0.206802       0.490279       0.252321   \n",
      "min          0.000000          0.124684      -1.780598      -0.798715   \n",
      "25%          0.000000          0.636786      -0.284362      -0.158688   \n",
      "50%          2.000000          0.801158      -0.029992      -0.012453   \n",
      "75%          4.000000          0.907313       0.196285       0.116509   \n",
      "max          8.000000          1.000000       1.230951       0.582372   \n",
      "\n",
      "                be_me        debt_me          at_me      ret_60_12  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean         0.877036       0.934957       3.508111       0.398995   \n",
      "std          0.938014       2.340201       6.909961       1.181512   \n",
      "min          0.034962       0.000000       0.092418      -0.929783   \n",
      "25%          0.369256       0.052171       0.821389      -0.200915   \n",
      "50%          0.617228       0.257516       1.531634       0.081231   \n",
      "75%          1.013951       0.705494       2.936145       0.617245   \n",
      "max          6.150324      17.036663      49.091416       6.947696   \n",
      "\n",
      "                ni_me         fcf_me      div12m_me        eqpo_me  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean        -0.059912      -0.008917       0.033365       0.032944   \n",
      "std          0.468489       0.246252       0.022790       0.024492   \n",
      "min         -3.398466      -1.478866       0.003166       0.000000   \n",
      "25%         -0.006381      -0.018607       0.022742       0.018685   \n",
      "50%          0.038689       0.031398       0.029023       0.034725   \n",
      "75%          0.072483       0.056268       0.035484       0.040904   \n",
      "max          0.525108       0.692331       0.163751       0.155940   \n",
      "\n",
      "             eqnpo_me       sale_gr3       sale_gr1     ebitda_mev  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean         0.012714       0.561307       0.166816       0.089337   \n",
      "std          0.066623       2.003967       0.691673       0.195406   \n",
      "min         -0.459036      -0.960299      -0.849058      -0.958675   \n",
      "25%          0.009897      -0.064705      -0.070341       0.048122   \n",
      "50%          0.023797       0.150611       0.046520       0.095749   \n",
      "75%          0.029807       0.479714       0.201574       0.151121   \n",
      "max          0.144026      16.028093       5.002326       0.770676   \n",
      "\n",
      "              sale_me         ocf_me  intrinsic_value        bev_mev  \\\n",
      "count   302640.000000  302640.000000    302640.000000  302640.000000   \n",
      "unique            NaN            NaN              NaN            NaN   \n",
      "top               NaN            NaN              NaN            NaN   \n",
      "freq              NaN            NaN              NaN            NaN   \n",
      "first             NaN            NaN              NaN            NaN   \n",
      "last              NaN            NaN              NaN            NaN   \n",
      "mean         2.014011       0.070947      3490.671421       0.796170   \n",
      "std          3.182464       0.311400     10446.842592       0.667954   \n",
      "min          0.000000      -1.537848         2.491002       0.018388   \n",
      "25%          0.425476       0.013882       163.776655       0.418438   \n",
      "50%          0.984983       0.074792       725.154538       0.686137   \n",
      "75%          2.122654       0.146634      1321.096408       0.977498   \n",
      "max         20.722951       1.308234     73545.097488       4.629562   \n",
      "\n",
      "           netdebt_me         eq_dur      capex_abn         at_gr1  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean         0.641032      16.327350       0.004315       0.148215   \n",
      "std          2.123302       4.317919       0.625859       0.559328   \n",
      "min         -1.528184       3.881245      -0.956574      -0.582037   \n",
      "25%         -0.052563      14.919482      -0.229782      -0.065575   \n",
      "50%          0.116884      16.398156      -0.061411       0.041766   \n",
      "75%          0.495177      17.477787       0.028505       0.178321   \n",
      "max         15.348466      39.837547       3.741961       3.956889   \n",
      "\n",
      "          ppeinv_gr1a         noa_at       noa_gr1a      lnoa_gr1a  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean         0.041438       0.519186       0.053695       0.014864   \n",
      "std          0.151434       0.302371       0.218905       0.080292   \n",
      "min         -0.410121      -0.378238      -0.438134      -0.254731   \n",
      "25%         -0.009362       0.378309      -0.031889      -0.012824   \n",
      "50%          0.015287       0.532825       0.020550       0.006091   \n",
      "75%          0.075249       0.659525       0.089195       0.034057   \n",
      "max          0.756529       1.695588       1.314765       0.357275   \n",
      "\n",
      "             capx_gr1       capx_gr2       capx_gr3     chcsho_12m  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean         0.292807       0.504996       0.658489       0.044786   \n",
      "std          1.324327       2.018835       2.565908       0.185193   \n",
      "min         -0.932967      -0.965249      -0.971395      -0.094992   \n",
      "25%         -0.164794      -0.173986      -0.150548       0.000000   \n",
      "50%          0.019811       0.069675       0.081623       0.000000   \n",
      "75%          0.267441       0.384383       0.516274       0.000000   \n",
      "max          9.564479      14.873391      19.325442       1.387552   \n",
      "\n",
      "           eqnpo_12m       debt_gr3        inv_gr1       inv_gr1a  \\\n",
      "count   3.026400e+05  302640.000000  302640.000000  302640.000000   \n",
      "unique           NaN            NaN            NaN            NaN   \n",
      "top              NaN            NaN            NaN            NaN   \n",
      "freq             NaN            NaN            NaN            NaN   \n",
      "first            NaN            NaN            NaN            NaN   \n",
      "last             NaN            NaN            NaN            NaN   \n",
      "mean   -1.523396e-02       2.558579       0.143770       0.002494   \n",
      "std     1.342175e-01      14.410995       0.743525       0.042392   \n",
      "min    -8.674392e-01      -1.000000      -1.000000      -0.183769   \n",
      "25%    -1.665335e-16      -0.192038      -0.085117      -0.002707   \n",
      "50%     2.009748e-04       0.082846       0.028436       0.000000   \n",
      "75%     2.784665e-02       0.541428       0.174600       0.009285   \n",
      "max     2.137249e-01     127.034183       5.427644       0.169068   \n",
      "\n",
      "         oaccruals_at   taccruals_at      cowc_gr1a       coa_gr1a  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean        -0.047039      -0.051644       0.001820       0.009271   \n",
      "std          0.119665       0.158401       0.090986       0.101582   \n",
      "min         -0.659574      -0.758501      -0.373954      -0.453094   \n",
      "25%         -0.073584      -0.092923      -0.020853      -0.017198   \n",
      "50%         -0.039763      -0.042046       0.002087       0.008546   \n",
      "75%         -0.009237      -0.000830       0.027805       0.046763   \n",
      "max          0.339613       0.534780       0.352011       0.335063   \n",
      "\n",
      "             col_gr1a     nncoa_gr1a      ncoa_gr1a      ncol_gr1a  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean         0.007472       0.016249       0.018381       0.001672   \n",
      "std          0.080678       0.126861       0.132344       0.043225   \n",
      "min         -0.369281      -0.568350      -0.591016      -0.193162   \n",
      "25%         -0.013200      -0.019518      -0.021683      -0.005491   \n",
      "50%          0.007566       0.012805       0.014554       0.000500   \n",
      "75%          0.032461       0.053259       0.057490       0.009913   \n",
      "max          0.277774       0.438076       0.473208       0.174784   \n",
      "\n",
      "            nfna_gr1a       sti_gr1a       lti_gr1a       fnl_gr1a  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean        -0.005936       0.000222       0.000715       0.007395   \n",
      "std          0.128632       0.044619       0.029575       0.103856   \n",
      "min         -0.469093      -0.219299      -0.143646      -0.428724   \n",
      "25%         -0.045148      -0.000077      -0.000439      -0.021050   \n",
      "50%         -0.000088       0.000000       0.000000       0.000000   \n",
      "75%          0.031385       0.000025       0.000602       0.035489   \n",
      "max          0.533026       0.218021       0.153058       0.376613   \n",
      "\n",
      "              be_gr1a   oaccruals_ni   taccruals_ni       netis_at  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean         0.014383      -1.804524      -2.021820       0.037794   \n",
      "std          0.172676       6.505924       8.592360       0.140863   \n",
      "min         -0.776519     -48.147053     -59.163193      -0.262129   \n",
      "25%         -0.026121      -1.506273      -1.841858       0.000000   \n",
      "50%          0.013997      -0.707934      -0.732540       0.008157   \n",
      "75%          0.066269      -0.156801      -0.051617       0.026889   \n",
      "max          0.627426      13.172846      25.716378       0.808348   \n",
      "\n",
      "           eqnetis_at     dbnetis_at         niq_be    niq_be_chg1  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean         0.030101       0.005159       0.012437      -0.004019   \n",
      "std          0.120358       0.112306       0.077841       0.071833   \n",
      "min         -0.077126      -0.456107      -0.438293      -0.387725   \n",
      "25%          0.000000      -0.024939       0.005939      -0.010477   \n",
      "50%          0.000196       0.000000       0.020952       0.000203   \n",
      "75%          0.002035       0.035032       0.036721       0.007488   \n",
      "max          0.782364       0.411426       0.236374       0.309604   \n",
      "\n",
      "               niq_at    niq_at_chg1       ebit_bev      ebit_sale  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean         0.003434      -0.000562       0.052234      -0.184498   \n",
      "std          0.034627       0.029451       0.526917       1.781604   \n",
      "min         -0.189397      -0.138382      -3.339450     -15.108386   \n",
      "25%          0.001282      -0.004056       0.022440       0.013873   \n",
      "50%          0.006756       0.000113       0.094380       0.068006   \n",
      "75%          0.014169       0.003047       0.183306       0.150510   \n",
      "max          0.091202       0.140454       1.619273       0.848708   \n",
      "\n",
      "             sale_bev    at_turnover          gp_at        gp_atl1  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean         2.528346       0.887968       0.473043       0.524968   \n",
      "std          3.945472       0.709141       0.464925       0.527502   \n",
      "min          0.000000       0.000000      -0.086223      -0.085797   \n",
      "25%          0.668639       0.349039       0.206928       0.225285   \n",
      "50%          1.445489       0.770621       0.310004       0.346266   \n",
      "75%          2.749926       1.245497       0.580835       0.647812   \n",
      "max         29.165289       3.653738       2.471454       2.980170   \n",
      "\n",
      "               ope_be       ope_bel1          op_at        op_atl1  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean         0.166233       0.225590       0.074501       0.091494   \n",
      "std          0.398474       0.444322       0.151355       0.163094   \n",
      "min         -1.969856      -1.586278      -0.676913      -0.651546   \n",
      "25%          0.073322       0.081380       0.032012       0.036542   \n",
      "50%          0.180221       0.201000       0.089343       0.097655   \n",
      "75%          0.306338       0.349377       0.145264       0.159993   \n",
      "max          1.507683       2.364466       0.416548       0.585701   \n",
      "\n",
      "               cop_at       cop_atl1        f_score        o_score  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean         0.135419       0.141679       5.356182      -2.473060   \n",
      "std          0.161165       0.187117       1.471837       2.147059   \n",
      "min         -0.541531      -0.739161       2.000000      -6.923383   \n",
      "25%          0.084840       0.090861       5.000000      -3.561526   \n",
      "50%          0.140842       0.149354       5.000000      -2.816827   \n",
      "75%          0.200225       0.212760       6.000000      -1.793421   \n",
      "max          0.638082       0.687356       9.000000       7.812621   \n",
      "\n",
      "              z_score         pi_nix          at_be      saleq_gr1  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean         3.286077       1.582090       4.205556       0.087915   \n",
      "std          4.066395       0.962440       6.351820       0.306521   \n",
      "min         -8.008044       0.443974       1.023048      -0.675026   \n",
      "25%          1.958964       1.262235       1.607671      -0.037210   \n",
      "50%          2.575365       1.378899       2.289284       0.055403   \n",
      "75%          3.405411       1.603195       3.650255       0.173646   \n",
      "max         28.786523       8.597967      43.392479       1.869478   \n",
      "\n",
      "                rd_me        rd_sale        opex_at        emp_gr1  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean         0.046124       0.055078       0.891524       0.038310   \n",
      "std          0.049741       0.106920       0.633856       0.175914   \n",
      "min          0.000421       0.000342       0.024130      -0.615106   \n",
      "25%          0.024794       0.026599       0.500283      -0.009119   \n",
      "50%          0.033031       0.029688       0.751774       0.025163   \n",
      "75%          0.051671       0.052300       1.126518       0.062130   \n",
      "max          0.349636       0.933168       3.651525       0.857143   \n",
      "\n",
      "               rd5_at            age     dsale_dinv     dsale_drec  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean         0.124200     284.215332      -0.022347      -0.026629   \n",
      "std          0.093471     107.996039       0.616317       0.490059   \n",
      "min          0.003232      33.000000      -3.962400      -2.750440   \n",
      "25%          0.081730     211.000000      -0.073526      -0.091084   \n",
      "50%          0.108725     302.000000       0.006409       0.001652   \n",
      "75%          0.142274     372.000000       0.098092       0.102044   \n",
      "max          0.706305     444.000000       1.761913       1.716045   \n",
      "\n",
      "            dgp_dsale     dsale_dsga   sale_emp_gr1    tangibility  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean        -0.013649      -0.023576       0.047115       0.659995   \n",
      "std          0.284629       0.371372       0.269675       0.220001   \n",
      "min         -1.199056      -1.819992      -0.584990       0.119664   \n",
      "25%         -0.036077      -0.048653      -0.066000       0.557916   \n",
      "50%          0.000000      -0.008374       0.014517       0.639537   \n",
      "75%          0.014983       0.038512       0.118412       0.761780   \n",
      "max          1.405241       1.528640       1.650898       1.438406   \n",
      "\n",
      "             kz_index  ocfq_saleq_std        cash_at         ni_ar1  \\\n",
      "count   302640.000000   302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN             NaN            NaN            NaN   \n",
      "top               NaN             NaN            NaN            NaN   \n",
      "freq              NaN             NaN            NaN            NaN   \n",
      "first             NaN             NaN            NaN            NaN   \n",
      "last              NaN             NaN            NaN            NaN   \n",
      "mean       -11.192612        0.364164       0.156470       0.138450   \n",
      "std         34.004312        1.659250       0.175318       0.499776   \n",
      "min       -257.182114        0.025294       0.000299      -1.232270   \n",
      "25%         -4.462420        0.085150       0.038982      -0.106187   \n",
      "50%         -3.593043        0.109128       0.092739       0.106296   \n",
      "75%         -1.343382        0.124175       0.204254       0.310860   \n",
      "max          2.594932       14.982081       0.839605       2.237173   \n",
      "\n",
      "              ni_ivol  earnings_variability        aliq_at       aliq_mat  \\\n",
      "count   302640.000000         302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN                   NaN            NaN            NaN   \n",
      "top               NaN                   NaN            NaN            NaN   \n",
      "freq              NaN                   NaN            NaN            NaN   \n",
      "first             NaN                   NaN            NaN            NaN   \n",
      "last              NaN                   NaN            NaN            NaN   \n",
      "mean         0.057473              0.933063       0.664111       0.470428   \n",
      "std          0.115197              0.825199       0.322115       0.213184   \n",
      "min          0.000610              0.052594       0.174446       0.073605   \n",
      "25%          0.012330              0.432736       0.525813       0.363935   \n",
      "50%          0.025038              0.844144       0.614141       0.441608   \n",
      "75%          0.046113              1.008185       0.722596       0.553118   \n",
      "max          0.845830              5.481654       2.728571       1.346636   \n",
      "\n",
      "           seas_1_1an     seas_1_1na     seas_2_5an     seas_2_5na  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean         0.004909       0.007251       0.007885       0.010079   \n",
      "std          0.126646       0.038252       0.050453       0.011773   \n",
      "min         -0.356336      -0.109272      -0.139281      -0.024293   \n",
      "25%         -0.056127      -0.011500      -0.016935       0.003721   \n",
      "50%          0.000183       0.007244       0.006610       0.008586   \n",
      "75%          0.057283       0.024827       0.031715       0.015641   \n",
      "max          0.495596       0.139449       0.190283       0.050040   \n",
      "\n",
      "        market_equity   ivol_ff3_21d  ivol_capm_252d  ivol_capm_21d  \\\n",
      "count   302640.000000  302640.000000   302640.000000  302640.000000   \n",
      "unique            NaN            NaN             NaN            NaN   \n",
      "top               NaN            NaN             NaN            NaN   \n",
      "freq              NaN            NaN             NaN            NaN   \n",
      "first             NaN            NaN             NaN            NaN   \n",
      "last              NaN            NaN             NaN            NaN   \n",
      "mean      5181.338233       0.027732        0.032461       0.028356   \n",
      "std      15170.204220       0.020305        0.020598       0.020530   \n",
      "min          1.328290       0.005853        0.008837       0.006230   \n",
      "25%         42.349568       0.015945        0.019889       0.016367   \n",
      "50%        227.039699       0.021685        0.026366       0.022244   \n",
      "75%       2079.563390       0.032220        0.038054       0.032977   \n",
      "max      99669.950582       0.126442        0.126101       0.127576   \n",
      "\n",
      "             rvol_21d       beta_60m  betabab_1260d  beta_dimson_21d  \\\n",
      "count   302640.000000  302640.000000  302640.000000    302640.000000   \n",
      "unique            NaN            NaN            NaN              NaN   \n",
      "top               NaN            NaN            NaN              NaN   \n",
      "freq              NaN            NaN            NaN              NaN   \n",
      "first             NaN            NaN            NaN              NaN   \n",
      "last              NaN            NaN            NaN              NaN   \n",
      "mean         0.030805       0.825692       0.799424         0.732050   \n",
      "std          0.021217       0.463471       0.330610         1.296422   \n",
      "min          0.007622      -0.207438       0.184237        -3.742165   \n",
      "25%          0.018264       0.510788       0.590080         0.200503   \n",
      "50%          0.024603       0.793067       0.759098         0.672884   \n",
      "75%          0.035778       1.035073       0.956007         1.218101   \n",
      "max          0.132390       2.391596       1.974988         5.491912   \n",
      "\n",
      "        turnover_126d  turnover_var_126d   dolvol_126d  dolvol_var_126d  \\\n",
      "count    3.026400e+05      302640.000000  3.026400e+05    302640.000000   \n",
      "unique            NaN                NaN           NaN              NaN   \n",
      "top               NaN                NaN           NaN              NaN   \n",
      "freq              NaN                NaN           NaN              NaN   \n",
      "first             NaN                NaN           NaN              NaN   \n",
      "last              NaN                NaN           NaN              NaN   \n",
      "mean     1.311326e-03           1.183271  3.323269e+06         1.202759   \n",
      "std      1.767538e-03           0.402108  1.658764e+07         0.417315   \n",
      "min      9.636440e-07           0.413701  2.761966e+03         0.405411   \n",
      "25%      5.278265e-04           1.008205  4.170546e+04         1.031966   \n",
      "50%      7.400189e-04           1.151000  8.062081e+04         1.166429   \n",
      "75%      1.328266e-03           1.253839  1.953238e+05         1.283357   \n",
      "max      1.041213e-02           2.883130  1.358304e+08         2.977677   \n",
      "\n",
      "                  prc       ami_126d  zero_trades_21d  zero_trades_126d  \\\n",
      "count   302640.000000  302640.000000    302640.000000     302640.000000   \n",
      "unique            NaN            NaN              NaN               NaN   \n",
      "top               NaN            NaN              NaN               NaN   \n",
      "freq              NaN            NaN              NaN               NaN   \n",
      "first             NaN            NaN              NaN               NaN   \n",
      "last              NaN            NaN              NaN               NaN   \n",
      "mean        46.276097      13.025828         0.006471          0.006741   \n",
      "std        111.804049      54.039573         0.001811          0.001916   \n",
      "min          0.021241       0.000106         0.001180          0.001293   \n",
      "25%          3.030546       0.203602         0.005813          0.005957   \n",
      "50%         11.904227       0.846456         0.006699          0.007004   \n",
      "75%         37.349975       2.950663         0.007227          0.007699   \n",
      "max        783.640098     433.093446         0.009719          0.009825   \n",
      "\n",
      "        zero_trades_252d      rmax1_21d      rskew_21d  iskew_capm_21d  \\\n",
      "count      302640.000000  302640.000000  302640.000000   302640.000000   \n",
      "unique               NaN            NaN            NaN             NaN   \n",
      "top                  NaN            NaN            NaN             NaN   \n",
      "freq                 NaN            NaN            NaN             NaN   \n",
      "first                NaN            NaN            NaN             NaN   \n",
      "last                 NaN            NaN            NaN             NaN   \n",
      "mean            0.006786       0.068763       0.219292        0.221520   \n",
      "std             0.001882       0.058488       0.743663        0.736946   \n",
      "min             0.001383       0.012625      -1.915970       -1.917268   \n",
      "25%             0.006069       0.036225      -0.157686       -0.150731   \n",
      "50%             0.007044       0.050897       0.192285        0.194191   \n",
      "75%             0.007664       0.077599       0.561669        0.565133   \n",
      "max             0.009839       0.358907       2.547376        2.510366   \n",
      "\n",
      "        iskew_ff3_21d     coskew_21d        ret_1_0  betadown_252d  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean         0.183323      -0.015524       0.005546       0.686325   \n",
      "std          0.679032       0.275984       0.132829       0.536974   \n",
      "min         -1.747764      -0.718037      -0.373207      -0.883172   \n",
      "25%         -0.168586      -0.175813      -0.059405       0.377377   \n",
      "50%          0.160899      -0.020796       0.000838       0.656298   \n",
      "75%          0.507743       0.139185       0.061375       0.958520   \n",
      "max          2.264932       0.723313       0.518364       2.350378   \n",
      "\n",
      "         bidaskhl_21d        ret_3_1        ret_9_1       ret_12_7  \\\n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000   \n",
      "unique            NaN            NaN            NaN            NaN   \n",
      "top               NaN            NaN            NaN            NaN   \n",
      "freq              NaN            NaN            NaN            NaN   \n",
      "first             NaN            NaN            NaN            NaN   \n",
      "last              NaN            NaN            NaN            NaN   \n",
      "mean         0.021179       0.011033       0.052753       0.027063   \n",
      "std          0.042373       0.186651       0.403489       0.293492   \n",
      "min          0.000646      -0.494922      -0.771130      -0.665423   \n",
      "25%          0.005252      -0.082351      -0.168790      -0.122333   \n",
      "50%          0.009218       0.003610       0.011823       0.005791   \n",
      "75%          0.017828       0.091071       0.207284       0.149052   \n",
      "max          0.312543       0.739188       1.806747       1.217819   \n",
      "\n",
      "           corr_1260d      rmax5_21d  rmax5_rvol_21d          ni_be  \\\n",
      "count   302640.000000  302640.000000   302640.000000  302640.000000   \n",
      "unique            NaN            NaN             NaN            NaN   \n",
      "top               NaN            NaN             NaN            NaN   \n",
      "freq              NaN            NaN             NaN            NaN   \n",
      "first             NaN            NaN             NaN            NaN   \n",
      "last              NaN            NaN             NaN            NaN   \n",
      "mean         0.353177       0.038436        1.177870      -0.029285   \n",
      "std          0.141707       0.027913        0.450562       0.537578   \n",
      "min          0.071907       0.008071        0.331712      -3.879039   \n",
      "25%          0.264417       0.022089        0.889585      -0.004877   \n",
      "50%          0.328992       0.030368        1.112619       0.075480   \n",
      "75%          0.424469       0.044592        1.390389       0.143403   \n",
      "max          0.762789       0.174939        2.709280       0.700258   \n",
      "\n",
      "               ocf_at    ocf_at_chg1  mispricing_perf  mispricing_mgmt  \\\n",
      "count   302640.000000  302640.000000    302640.000000    302640.000000   \n",
      "unique            NaN            NaN              NaN              NaN   \n",
      "top               NaN            NaN              NaN              NaN   \n",
      "freq              NaN            NaN              NaN              NaN   \n",
      "first             NaN            NaN              NaN              NaN   \n",
      "last              NaN            NaN              NaN              NaN   \n",
      "mean         0.037284       0.001251         0.512927         0.506477   \n",
      "std          0.151483       0.136917         0.153614         0.123465   \n",
      "min         -0.728472      -0.545975         0.065847         0.093451   \n",
      "25%          0.003732      -0.036254         0.483878         0.491745   \n",
      "50%          0.056293       0.000114         0.522080         0.517655   \n",
      "75%          0.108750       0.036680         0.555490         0.538919   \n",
      "max          0.370796       0.581787         0.904289         0.800868   \n",
      "\n",
      "                  qmj       qmj_prof     qmj_growth     qmj_safety  \n",
      "count   302640.000000  302640.000000  302640.000000  302640.000000  \n",
      "unique            NaN            NaN            NaN            NaN  \n",
      "top               NaN            NaN            NaN            NaN  \n",
      "freq              NaN            NaN            NaN            NaN  \n",
      "first             NaN            NaN            NaN            NaN  \n",
      "last              NaN            NaN            NaN            NaN  \n",
      "mean         0.025253       0.026031       0.018625       0.030435  \n",
      "std          0.650632       0.764341       0.652215       0.734396  \n",
      "min         -1.641207      -1.667292      -1.644462      -1.636153  \n",
      "25%         -0.008511      -0.230771      -0.010196      -0.209584  \n",
      "50%          0.025779       0.028933       0.024899       0.037248  \n",
      "75%          0.067236       0.292461       0.053294       0.268386  \n",
      "max          1.652757       1.671761       1.649089       1.645174  \n"
     ]
    }
   ],
   "source": [
    "with pd.option_context('display.max_columns', 174):\n",
    "    print(df.describe(include='all'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Normalization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[features]=sklearn.preprocessing.minmax_scale(df[features], feature_range=(0, 1), axis=0, copy=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "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>id</th>\n",
       "      <th>date</th>\n",
       "      <th>eom</th>\n",
       "      <th>size_grp</th>\n",
       "      <th>primary_sec</th>\n",
       "      <th>curcd</th>\n",
       "      <th>fx</th>\n",
       "      <th>common</th>\n",
       "      <th>comp_tpci</th>\n",
       "      <th>comp_exchg</th>\n",
       "      <th>...</th>\n",
       "      <th>rmax5_rvol_21d</th>\n",
       "      <th>ni_be</th>\n",
       "      <th>ocf_at</th>\n",
       "      <th>ocf_at_chg1</th>\n",
       "      <th>mispricing_perf</th>\n",
       "      <th>mispricing_mgmt</th>\n",
       "      <th>qmj</th>\n",
       "      <th>qmj_prof</th>\n",
       "      <th>qmj_growth</th>\n",
       "      <th>qmj_safety</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>comp_001166_02W</td>\n",
       "      <td>1999-07-30</td>\n",
       "      <td>1999-07-31</td>\n",
       "      <td>small</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.070296</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>0.266162</td>\n",
       "      <td>0.702730</td>\n",
       "      <td>0.619276</td>\n",
       "      <td>0.609128</td>\n",
       "      <td>0.522291</td>\n",
       "      <td>0.595222</td>\n",
       "      <td>0.498247</td>\n",
       "      <td>0.505300</td>\n",
       "      <td>0.515193</td>\n",
       "      <td>0.458964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>comp_001166_02W</td>\n",
       "      <td>1999-08-31</td>\n",
       "      <td>1999-08-31</td>\n",
       "      <td>small</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.056227</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>0.236868</td>\n",
       "      <td>0.702730</td>\n",
       "      <td>0.619276</td>\n",
       "      <td>0.609128</td>\n",
       "      <td>0.531882</td>\n",
       "      <td>0.583406</td>\n",
       "      <td>0.495584</td>\n",
       "      <td>0.505273</td>\n",
       "      <td>0.512611</td>\n",
       "      <td>0.462543</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>comp_001166_02W</td>\n",
       "      <td>1999-09-30</td>\n",
       "      <td>1999-09-30</td>\n",
       "      <td>small</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.064990</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>0.230879</td>\n",
       "      <td>0.702730</td>\n",
       "      <td>0.619276</td>\n",
       "      <td>0.609128</td>\n",
       "      <td>0.520640</td>\n",
       "      <td>0.583079</td>\n",
       "      <td>0.495584</td>\n",
       "      <td>0.502896</td>\n",
       "      <td>0.512611</td>\n",
       "      <td>0.460300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>comp_001166_02W</td>\n",
       "      <td>1999-10-29</td>\n",
       "      <td>1999-10-31</td>\n",
       "      <td>small</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.051050</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>0.261028</td>\n",
       "      <td>0.760038</td>\n",
       "      <td>0.747463</td>\n",
       "      <td>0.524785</td>\n",
       "      <td>0.533115</td>\n",
       "      <td>0.587913</td>\n",
       "      <td>0.495515</td>\n",
       "      <td>0.504106</td>\n",
       "      <td>0.512957</td>\n",
       "      <td>0.464796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>comp_001166_02W</td>\n",
       "      <td>1999-11-30</td>\n",
       "      <td>1999-11-30</td>\n",
       "      <td>small</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.006891</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>0.292613</td>\n",
       "      <td>0.760038</td>\n",
       "      <td>0.747463</td>\n",
       "      <td>0.524785</td>\n",
       "      <td>0.525494</td>\n",
       "      <td>0.599639</td>\n",
       "      <td>0.506357</td>\n",
       "      <td>0.509513</td>\n",
       "      <td>0.523632</td>\n",
       "      <td>0.478686</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 174 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                id       date        eom size_grp  primary_sec curcd  \\\n",
       "0  comp_001166_02W 1999-07-30 1999-07-31    small            0   EUR   \n",
       "1  comp_001166_02W 1999-08-31 1999-08-31    small            0   EUR   \n",
       "2  comp_001166_02W 1999-09-30 1999-09-30    small            0   EUR   \n",
       "3  comp_001166_02W 1999-10-29 1999-10-31    small            0   EUR   \n",
       "4  comp_001166_02W 1999-11-30 1999-11-30    small            0   EUR   \n",
       "\n",
       "         fx  common comp_tpci  comp_exchg  ...  rmax5_rvol_21d     ni_be  \\\n",
       "0  1.070296       1         0         154  ...        0.266162  0.702730   \n",
       "1  1.056227       1         0         154  ...        0.236868  0.702730   \n",
       "2  1.064990       1         0         154  ...        0.230879  0.702730   \n",
       "3  1.051050       1         0         154  ...        0.261028  0.760038   \n",
       "4  1.006891       1         0         154  ...        0.292613  0.760038   \n",
       "\n",
       "     ocf_at  ocf_at_chg1  mispricing_perf  mispricing_mgmt       qmj  \\\n",
       "0  0.619276     0.609128         0.522291         0.595222  0.498247   \n",
       "1  0.619276     0.609128         0.531882         0.583406  0.495584   \n",
       "2  0.619276     0.609128         0.520640         0.583079  0.495584   \n",
       "3  0.747463     0.524785         0.533115         0.587913  0.495515   \n",
       "4  0.747463     0.524785         0.525494         0.599639  0.506357   \n",
       "\n",
       "   qmj_prof  qmj_growth  qmj_safety  \n",
       "0  0.505300    0.515193    0.458964  \n",
       "1  0.505273    0.512611    0.462543  \n",
       "2  0.502896    0.512611    0.460300  \n",
       "3  0.504106    0.512957    0.464796  \n",
       "4  0.509513    0.523632    0.478686  \n",
       "\n",
       "[5 rows x 174 columns]"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.9999999999999999"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"me_company\"].min()\n",
    "df[\"me_company\"].max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Convert float64 to float32 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "for column in df:\n",
    "    if df[column].dtypes == \"float64\": \n",
    "        df[column] = df[column].astype(\"float32\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 302640 entries, 0 to 306410\n",
      "Data columns (total 174 columns):\n",
      " #   Column                Dtype         \n",
      "---  ------                -----         \n",
      " 0   id                    object        \n",
      " 1   date                  datetime64[ns]\n",
      " 2   eom                   datetime64[ns]\n",
      " 3   size_grp              object        \n",
      " 4   primary_sec           int64         \n",
      " 5   curcd                 object        \n",
      " 6   fx                    float32       \n",
      " 7   common                int64         \n",
      " 8   comp_tpci             object        \n",
      " 9   comp_exchg            int64         \n",
      " 10  sic                   float32       \n",
      " 11  shares                float32       \n",
      " 12  me_company            float32       \n",
      " 13  prc_local             float32       \n",
      " 14  dolvol                float32       \n",
      " 15  ret                   float32       \n",
      " 16  ret_local             float32       \n",
      " 17  ret_exc               float32       \n",
      " 18  ret_lag_dif           float32       \n",
      " 19  ret_exc_lead1m        float32       \n",
      " 20  enterprise_value      float32       \n",
      " 21  book_equity           float32       \n",
      " 22  assets                float32       \n",
      " 23  sales                 float32       \n",
      " 24  net_income            float32       \n",
      " 25  bidask                float32       \n",
      " 26  prc_high              float32       \n",
      " 27  prc_low               float32       \n",
      " 28  tvol                  float32       \n",
      " 29  niq_su                float32       \n",
      " 30  ret_6_1               float32       \n",
      " 31  ret_12_1              float32       \n",
      " 32  saleq_su              float32       \n",
      " 33  tax_gr1a              float32       \n",
      " 34  ni_inc8q              float32       \n",
      " 35  prc_highprc_252d      float32       \n",
      " 36  resff3_6_1            float32       \n",
      " 37  resff3_12_1           float32       \n",
      " 38  be_me                 float32       \n",
      " 39  debt_me               float32       \n",
      " 40  at_me                 float32       \n",
      " 41  ret_60_12             float32       \n",
      " 42  ni_me                 float32       \n",
      " 43  fcf_me                float32       \n",
      " 44  div12m_me             float32       \n",
      " 45  eqpo_me               float32       \n",
      " 46  eqnpo_me              float32       \n",
      " 47  sale_gr3              float32       \n",
      " 48  sale_gr1              float32       \n",
      " 49  ebitda_mev            float32       \n",
      " 50  sale_me               float32       \n",
      " 51  ocf_me                float32       \n",
      " 52  intrinsic_value       float32       \n",
      " 53  bev_mev               float32       \n",
      " 54  netdebt_me            float32       \n",
      " 55  eq_dur                float32       \n",
      " 56  capex_abn             float32       \n",
      " 57  at_gr1                float32       \n",
      " 58  ppeinv_gr1a           float32       \n",
      " 59  noa_at                float32       \n",
      " 60  noa_gr1a              float32       \n",
      " 61  lnoa_gr1a             float32       \n",
      " 62  capx_gr1              float32       \n",
      " 63  capx_gr2              float32       \n",
      " 64  capx_gr3              float32       \n",
      " 65  chcsho_12m            float32       \n",
      " 66  eqnpo_12m             float32       \n",
      " 67  debt_gr3              float32       \n",
      " 68  inv_gr1               float32       \n",
      " 69  inv_gr1a              float32       \n",
      " 70  oaccruals_at          float32       \n",
      " 71  taccruals_at          float32       \n",
      " 72  cowc_gr1a             float32       \n",
      " 73  coa_gr1a              float32       \n",
      " 74  col_gr1a              float32       \n",
      " 75  nncoa_gr1a            float32       \n",
      " 76  ncoa_gr1a             float32       \n",
      " 77  ncol_gr1a             float32       \n",
      " 78  nfna_gr1a             float32       \n",
      " 79  sti_gr1a              float32       \n",
      " 80  lti_gr1a              float32       \n",
      " 81  fnl_gr1a              float32       \n",
      " 82  be_gr1a               float32       \n",
      " 83  oaccruals_ni          float32       \n",
      " 84  taccruals_ni          float32       \n",
      " 85  netis_at              float32       \n",
      " 86  eqnetis_at            float32       \n",
      " 87  dbnetis_at            float32       \n",
      " 88  niq_be                float32       \n",
      " 89  niq_be_chg1           float32       \n",
      " 90  niq_at                float32       \n",
      " 91  niq_at_chg1           float32       \n",
      " 92  ebit_bev              float32       \n",
      " 93  ebit_sale             float32       \n",
      " 94  sale_bev              float32       \n",
      " 95  at_turnover           float32       \n",
      " 96  gp_at                 float32       \n",
      " 97  gp_atl1               float32       \n",
      " 98  ope_be                float32       \n",
      " 99  ope_bel1              float32       \n",
      " 100 op_at                 float32       \n",
      " 101 op_atl1               float32       \n",
      " 102 cop_at                float32       \n",
      " 103 cop_atl1              float32       \n",
      " 104 f_score               float32       \n",
      " 105 o_score               float32       \n",
      " 106 z_score               float32       \n",
      " 107 pi_nix                float32       \n",
      " 108 at_be                 float32       \n",
      " 109 saleq_gr1             float32       \n",
      " 110 rd_me                 float32       \n",
      " 111 rd_sale               float32       \n",
      " 112 opex_at               float32       \n",
      " 113 emp_gr1               float32       \n",
      " 114 rd5_at                float32       \n",
      " 115 age                   float32       \n",
      " 116 dsale_dinv            float32       \n",
      " 117 dsale_drec            float32       \n",
      " 118 dgp_dsale             float32       \n",
      " 119 dsale_dsga            float32       \n",
      " 120 sale_emp_gr1          float32       \n",
      " 121 tangibility           float32       \n",
      " 122 kz_index              float32       \n",
      " 123 ocfq_saleq_std        float32       \n",
      " 124 cash_at               float32       \n",
      " 125 ni_ar1                float32       \n",
      " 126 ni_ivol               float32       \n",
      " 127 earnings_variability  float32       \n",
      " 128 aliq_at               float32       \n",
      " 129 aliq_mat              float32       \n",
      " 130 seas_1_1an            float32       \n",
      " 131 seas_1_1na            float32       \n",
      " 132 seas_2_5an            float32       \n",
      " 133 seas_2_5na            float32       \n",
      " 134 market_equity         float32       \n",
      " 135 ivol_ff3_21d          float32       \n",
      " 136 ivol_capm_252d        float32       \n",
      " 137 ivol_capm_21d         float32       \n",
      " 138 rvol_21d              float32       \n",
      " 139 beta_60m              float32       \n",
      " 140 betabab_1260d         float32       \n",
      " 141 beta_dimson_21d       float32       \n",
      " 142 turnover_126d         float32       \n",
      " 143 turnover_var_126d     float32       \n",
      " 144 dolvol_126d           float32       \n",
      " 145 dolvol_var_126d       float32       \n",
      " 146 prc                   float32       \n",
      " 147 ami_126d              float32       \n",
      " 148 zero_trades_21d       float32       \n",
      " 149 zero_trades_126d      float32       \n",
      " 150 zero_trades_252d      float32       \n",
      " 151 rmax1_21d             float32       \n",
      " 152 rskew_21d             float32       \n",
      " 153 iskew_capm_21d        float32       \n",
      " 154 iskew_ff3_21d         float32       \n",
      " 155 coskew_21d            float32       \n",
      " 156 ret_1_0               float32       \n",
      " 157 betadown_252d         float32       \n",
      " 158 bidaskhl_21d          float32       \n",
      " 159 ret_3_1               float32       \n",
      " 160 ret_9_1               float32       \n",
      " 161 ret_12_7              float32       \n",
      " 162 corr_1260d            float32       \n",
      " 163 rmax5_21d             float32       \n",
      " 164 rmax5_rvol_21d        float32       \n",
      " 165 ni_be                 float32       \n",
      " 166 ocf_at                float32       \n",
      " 167 ocf_at_chg1           float32       \n",
      " 168 mispricing_perf       float32       \n",
      " 169 mispricing_mgmt       float32       \n",
      " 170 qmj                   float32       \n",
      " 171 qmj_prof              float32       \n",
      " 172 qmj_growth            float32       \n",
      " 173 qmj_safety            float32       \n",
      "dtypes: datetime64[ns](2), float32(165), int64(3), object(4)\n",
      "memory usage: 213.6+ MB\n"
     ]
    }
   ],
   "source": [
    "#Inspect variable types\n",
    "df.info(verbose=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(r'C:\\Users\\Besitzer\\Documents\\Data\\ger_factor_data_win_norm.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a new lead variable for excess return"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['ret_exc_l1'] = df.groupby(['id'])['ret_exc'].shift(-1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "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>id</th>\n",
       "      <th>date</th>\n",
       "      <th>eom</th>\n",
       "      <th>size_grp</th>\n",
       "      <th>primary_sec</th>\n",
       "      <th>curcd</th>\n",
       "      <th>fx</th>\n",
       "      <th>common</th>\n",
       "      <th>comp_tpci</th>\n",
       "      <th>comp_exchg</th>\n",
       "      <th>...</th>\n",
       "      <th>ni_be</th>\n",
       "      <th>ocf_at</th>\n",
       "      <th>ocf_at_chg1</th>\n",
       "      <th>mispricing_perf</th>\n",
       "      <th>mispricing_mgmt</th>\n",
       "      <th>qmj</th>\n",
       "      <th>qmj_prof</th>\n",
       "      <th>qmj_growth</th>\n",
       "      <th>qmj_safety</th>\n",
       "      <th>ret_exc_l1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>comp_001166_02W</td>\n",
       "      <td>1999-07-30</td>\n",
       "      <td>1999-07-31</td>\n",
       "      <td>small</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.070296</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>0.702730</td>\n",
       "      <td>0.619276</td>\n",
       "      <td>0.609128</td>\n",
       "      <td>0.522291</td>\n",
       "      <td>0.595222</td>\n",
       "      <td>0.498247</td>\n",
       "      <td>0.505300</td>\n",
       "      <td>0.515193</td>\n",
       "      <td>0.458964</td>\n",
       "      <td>0.343650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>comp_001166_02W</td>\n",
       "      <td>1999-08-31</td>\n",
       "      <td>1999-08-31</td>\n",
       "      <td>small</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.056227</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>0.702730</td>\n",
       "      <td>0.619276</td>\n",
       "      <td>0.609128</td>\n",
       "      <td>0.531882</td>\n",
       "      <td>0.583405</td>\n",
       "      <td>0.495584</td>\n",
       "      <td>0.505273</td>\n",
       "      <td>0.512611</td>\n",
       "      <td>0.462543</td>\n",
       "      <td>0.605915</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>comp_001166_02W</td>\n",
       "      <td>1999-09-30</td>\n",
       "      <td>1999-09-30</td>\n",
       "      <td>small</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.064990</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>0.702730</td>\n",
       "      <td>0.619276</td>\n",
       "      <td>0.609128</td>\n",
       "      <td>0.520640</td>\n",
       "      <td>0.583079</td>\n",
       "      <td>0.495584</td>\n",
       "      <td>0.502896</td>\n",
       "      <td>0.512611</td>\n",
       "      <td>0.460300</td>\n",
       "      <td>0.382962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>comp_001166_02W</td>\n",
       "      <td>1999-10-29</td>\n",
       "      <td>1999-10-31</td>\n",
       "      <td>small</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.051050</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>0.760038</td>\n",
       "      <td>0.747463</td>\n",
       "      <td>0.524785</td>\n",
       "      <td>0.533115</td>\n",
       "      <td>0.587913</td>\n",
       "      <td>0.495515</td>\n",
       "      <td>0.504106</td>\n",
       "      <td>0.512957</td>\n",
       "      <td>0.464796</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>comp_001166_02W</td>\n",
       "      <td>1999-11-30</td>\n",
       "      <td>1999-11-30</td>\n",
       "      <td>small</td>\n",
       "      <td>0</td>\n",
       "      <td>EUR</td>\n",
       "      <td>1.006891</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>154</td>\n",
       "      <td>...</td>\n",
       "      <td>0.760038</td>\n",
       "      <td>0.747463</td>\n",
       "      <td>0.524785</td>\n",
       "      <td>0.525494</td>\n",
       "      <td>0.599639</td>\n",
       "      <td>0.506357</td>\n",
       "      <td>0.509513</td>\n",
       "      <td>0.523632</td>\n",
       "      <td>0.478686</td>\n",
       "      <td>0.929363</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 175 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                id       date        eom size_grp  primary_sec curcd  \\\n",
       "0  comp_001166_02W 1999-07-30 1999-07-31    small            0   EUR   \n",
       "1  comp_001166_02W 1999-08-31 1999-08-31    small            0   EUR   \n",
       "2  comp_001166_02W 1999-09-30 1999-09-30    small            0   EUR   \n",
       "3  comp_001166_02W 1999-10-29 1999-10-31    small            0   EUR   \n",
       "4  comp_001166_02W 1999-11-30 1999-11-30    small            0   EUR   \n",
       "\n",
       "         fx  common comp_tpci  comp_exchg  ...     ni_be    ocf_at  \\\n",
       "0  1.070296       1         0         154  ...  0.702730  0.619276   \n",
       "1  1.056227       1         0         154  ...  0.702730  0.619276   \n",
       "2  1.064990       1         0         154  ...  0.702730  0.619276   \n",
       "3  1.051050       1         0         154  ...  0.760038  0.747463   \n",
       "4  1.006891       1         0         154  ...  0.760038  0.747463   \n",
       "\n",
       "   ocf_at_chg1  mispricing_perf  mispricing_mgmt       qmj  qmj_prof  \\\n",
       "0     0.609128         0.522291         0.595222  0.498247  0.505300   \n",
       "1     0.609128         0.531882         0.583405  0.495584  0.505273   \n",
       "2     0.609128         0.520640         0.583079  0.495584  0.502896   \n",
       "3     0.524785         0.533115         0.587913  0.495515  0.504106   \n",
       "4     0.524785         0.525494         0.599639  0.506357  0.509513   \n",
       "\n",
       "   qmj_growth  qmj_safety  ret_exc_l1  \n",
       "0    0.515193    0.458964    0.343650  \n",
       "1    0.512611    0.462543    0.605915  \n",
       "2    0.512611    0.460300    0.382962  \n",
       "3    0.512957    0.464796    1.000000  \n",
       "4    0.523632    0.478686    0.929363  \n",
       "\n",
       "[5 rows x 175 columns]"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2660"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#There are 2660 unique german stocks --> We can see that we have no previous value that we can use to predict the frst value in the sequence. \n",
    "df['ret_exc_l1'].isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(r'C:\\Users\\Besitzer\\Documents\\Data\\ger_factor_data_win_norm.csv', index = False)"
   ]
  },
  {
   "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}