ML Project Pairs Trading - DBSCAN.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import / install relevant Python packages\n",
    "import numpy as np\n",
    "import pandas as pd  \n",
    "import datetime as dt\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "from sklearn.cluster import (KMeans, MiniBatchKMeans, AgglomerativeClustering, DBSCAN)\n",
    "from sklearn.neighbors import NearestNeighbors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "feature_names_list = ['mom1m','absacc','acc','aeavol','age','agr','baspread',\n",
    "'beta','betasq','bm','bm_ia','cash','cashdebt','cashpr','cfp','cfp_ia',\n",
    "'chatoia','chcsho','chempia','chinv','chmom','chpmia','chtx','cinvest',\n",
    "'convind','currat','depr','divi','divo','dolvol','dy','ear','egr','ep',\n",
    "'gma','herf','hire','idiovol','ill','indmom','invest','lev','lgr','maxret',\n",
    "'ms','mve_ia','mvel1','nincr','operprof','pchcapx_ia','pchcurrat','pchdepr',\n",
    "'pchgm_pchsale','pchquick','pchsale_pchrect','pctacc','pricedelay',\n",
    "'ps','quick','rd','retvol','roaq','roeq','roic','rsup','salecash',\n",
    "'salerec','securedind','sgr','sin','sp','std_dolvol','std_turn',\n",
    "'tang','tb','turn','zerotrade']\n",
    "\n",
    "mom_names_list = [\"mom1m\",\"mom2m\",\"mom3m\",\"mom4m\",\"mom5m\",\"mom6m\",\"mom7m\",\"mom8m\",\"mom9m\",\"mom10m\",\"mom11m\",\"mom12m\",\n",
    "                \"mom13m\",\"mom14m\",\"mom15m\",\"mom16m\",\"mom17m\",\"mom18m\",\"mom19m\",\"mom20m\",\"mom21m\",\"mom22m\",\"mom23m\",\"mom24m\",\n",
    "                \"mom25m\",\"mom26m\",\"mom27m\",\"mom28m\",\"mom29m\",\"mom30m\",\"mom31m\",\"mom32m\",\"mom33m\",\"mom34m\",\"mom35m\",\"mom36m\",\n",
    "                \"mom37m\",\"mom38m\",\"mom39m\",\"mom40m\",\"mom41m\",\"mom42m\",\"mom43m\",\"mom44m\",\"mom45m\",\"mom46m\",\"mom47m\",\"mom48m\"]\n",
    "\n",
    "feature_names_list_full =  feature_names_list + mom_names_list[1:]\n",
    "\n",
    "PCA_components_number = len(feature_names_list_full)\n",
    "PCA_components_column_names = ['PCA '+ str(i+1) for i in range(PCA_components_number)]\n",
    "PCA_components_tresholds_column_names=['n_to_reach_90','n_to_reach_95','n_to_reach_99']\n",
    "Original_df_columns_names = ['DATE','permno','sic2']\n",
    "PCA_table_full_columns_names = Original_df_columns_names+PCA_components_column_names+PCA_components_tresholds_column_names\n",
    "DBSCAN_column_names = ['cluster alpha='+ str(i) for i in range(10,100,10)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_PCA = pd.read_csv('D:/USB Drive/Data/DachengXiu/df_PCA_done.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DATE  19791231\n",
      "DATE  19800131\n",
      "DATE  19800229\n",
      "DATE  19800331\n",
      "DATE  19800430\n",
      "DATE  19800530\n",
      "DATE  19800630\n",
      "DATE  19800731\n",
      "DATE  19800829\n",
      "DATE  19800930\n",
      "DATE  19801031\n",
      "DATE  19801128\n",
      "DATE  19801231\n",
      "DATE  19810130\n",
      "DATE  19810227\n",
      "DATE  19810331\n",
      "DATE  19810430\n",
      "DATE  19810529\n",
      "DATE  19810630\n",
      "DATE  19810731\n",
      "DATE  19810831\n",
      "DATE  19810930\n",
      "DATE  19811030\n",
      "DATE  19811130\n",
      "DATE  19811231\n",
      "DATE  19820129\n",
      "DATE  19820226\n",
      "DATE  19820331\n",
      "DATE  19820430\n",
      "DATE  19820528\n",
      "DATE  19820630\n",
      "DATE  19820730\n",
      "DATE  19820831\n",
      "DATE  19820930\n",
      "DATE  19821029\n",
      "DATE  19821130\n",
      "DATE  19821231\n",
      "DATE  19830131\n",
      "DATE  19830228\n",
      "DATE  19830331\n",
      "DATE  19830429\n",
      "DATE  19830531\n",
      "DATE  19830630\n",
      "DATE  19830729\n",
      "DATE  19830831\n",
      "DATE  19830930\n",
      "DATE  19831031\n",
      "DATE  19831130\n",
      "DATE  19831230\n",
      "DATE  19840131\n",
      "DATE  19840229\n",
      "DATE  19840330\n",
      "DATE  19840430\n",
      "DATE  19840531\n",
      "DATE  19840629\n",
      "DATE  19840731\n",
      "DATE  19840831\n",
      "DATE  19840928\n",
      "DATE  19841031\n",
      "DATE  19841130\n",
      "DATE  19841231\n",
      "DATE  19850131\n",
      "DATE  19850228\n",
      "DATE  19850329\n",
      "DATE  19850430\n",
      "DATE  19850531\n",
      "DATE  19850628\n",
      "DATE  19850731\n",
      "DATE  19850830\n",
      "DATE  19850930\n",
      "DATE  19851031\n",
      "DATE  19851129\n",
      "DATE  19851231\n",
      "DATE  19860131\n",
      "DATE  19860228\n",
      "DATE  19860331\n",
      "DATE  19860430\n",
      "DATE  19860530\n",
      "DATE  19860630\n",
      "DATE  19860731\n",
      "DATE  19860829\n",
      "DATE  19860930\n",
      "DATE  19861031\n",
      "DATE  19861128\n",
      "DATE  19861231\n",
      "DATE  19870130\n",
      "DATE  19870227\n",
      "DATE  19870331\n",
      "DATE  19870430\n",
      "DATE  19870529\n",
      "DATE  19870630\n",
      "DATE  19870731\n",
      "DATE  19870831\n",
      "DATE  19870930\n",
      "DATE  19871030\n",
      "DATE  19871130\n",
      "DATE  19871231\n",
      "DATE  19880129\n",
      "DATE  19880229\n",
      "DATE  19880331\n",
      "DATE  19880429\n",
      "DATE  19880531\n",
      "DATE  19880630\n",
      "DATE  19880729\n",
      "DATE  19880831\n",
      "DATE  19880930\n",
      "DATE  19881031\n",
      "DATE  19881130\n",
      "DATE  19881230\n",
      "DATE  19890131\n",
      "DATE  19890228\n",
      "DATE  19890331\n",
      "DATE  19890428\n",
      "DATE  19890531\n",
      "DATE  19890630\n",
      "DATE  19890731\n",
      "DATE  19890831\n",
      "DATE  19890929\n",
      "DATE  19891031\n",
      "DATE  19891130\n",
      "DATE  19891229\n",
      "DATE  19900131\n",
      "DATE  19900228\n",
      "DATE  19900330\n",
      "DATE  19900430\n",
      "DATE  19900531\n",
      "DATE  19900629\n",
      "DATE  19900731\n",
      "DATE  19900831\n",
      "DATE  19900928\n",
      "DATE  19901031\n",
      "DATE  19901130\n",
      "DATE  19901231\n",
      "DATE  19910131\n",
      "DATE  19910228\n",
      "DATE  19910328\n",
      "DATE  19910430\n",
      "DATE  19910531\n",
      "DATE  19910628\n",
      "DATE  19910731\n",
      "DATE  19910830\n",
      "DATE  19910930\n",
      "DATE  19911031\n",
      "DATE  19911129\n",
      "DATE  19911231\n",
      "DATE  19920131\n",
      "DATE  19920228\n",
      "DATE  19920331\n",
      "DATE  19920430\n",
      "DATE  19920529\n",
      "DATE  19920630\n",
      "DATE  19920731\n",
      "DATE  19920831\n",
      "DATE  19920930\n",
      "DATE  19921030\n",
      "DATE  19921130\n",
      "DATE  19921231\n",
      "DATE  19930129\n",
      "DATE  19930226\n",
      "DATE  19930331\n",
      "DATE  19930430\n",
      "DATE  19930528\n",
      "DATE  19930630\n",
      "DATE  19930730\n",
      "DATE  19930831\n",
      "DATE  19930930\n",
      "DATE  19931029\n",
      "DATE  19931130\n",
      "DATE  19931231\n",
      "DATE  19940131\n",
      "DATE  19940228\n",
      "DATE  19940331\n",
      "DATE  19940429\n",
      "DATE  19940531\n",
      "DATE  19940630\n",
      "DATE  19940729\n",
      "DATE  19940831\n",
      "DATE  19940930\n",
      "DATE  19941031\n",
      "DATE  19941130\n",
      "DATE  19941230\n",
      "DATE  19950131\n",
      "DATE  19950228\n",
      "DATE  19950331\n",
      "DATE  19950428\n",
      "DATE  19950531\n",
      "DATE  19950630\n",
      "DATE  19950731\n",
      "DATE  19950831\n",
      "DATE  19950929\n",
      "DATE  19951031\n",
      "DATE  19951130\n",
      "DATE  19951229\n",
      "DATE  19960131\n",
      "DATE  19960229\n",
      "DATE  19960329\n",
      "DATE  19960430\n",
      "DATE  19960531\n",
      "DATE  19960628\n",
      "DATE  19960731\n",
      "DATE  19960830\n",
      "DATE  19960930\n",
      "DATE  19961031\n",
      "DATE  19961129\n",
      "DATE  19961231\n",
      "DATE  19970131\n",
      "DATE  19970228\n",
      "DATE  19970331\n",
      "DATE  19970430\n",
      "DATE  19970530\n",
      "DATE  19970630\n",
      "DATE  19970731\n",
      "DATE  19970829\n",
      "DATE  19970930\n",
      "DATE  19971031\n",
      "DATE  19971128\n",
      "DATE  19971231\n",
      "DATE  19980130\n",
      "DATE  19980227\n",
      "DATE  19980331\n",
      "DATE  19980430\n",
      "DATE  19980529\n",
      "DATE  19980630\n",
      "DATE  19980731\n",
      "DATE  19980831\n",
      "DATE  19980930\n",
      "DATE  19981030\n",
      "DATE  19981130\n",
      "DATE  19981231\n",
      "DATE  19990129\n",
      "DATE  19990226\n",
      "DATE  19990331\n",
      "DATE  19990430\n",
      "DATE  19990528\n",
      "DATE  19990630\n",
      "DATE  19990730\n",
      "DATE  19990831\n",
      "DATE  19990930\n",
      "DATE  19991029\n",
      "DATE  19991130\n",
      "DATE  19991231\n",
      "DATE  20000131\n",
      "DATE  20000229\n",
      "DATE  20000331\n",
      "DATE  20000428\n",
      "DATE  20000531\n",
      "DATE  20000630\n",
      "DATE  20000731\n",
      "DATE  20000831\n",
      "DATE  20000929\n",
      "DATE  20001031\n",
      "DATE  20001130\n",
      "DATE  20001229\n",
      "DATE  20010131\n",
      "DATE  20010228\n",
      "DATE  20010330\n",
      "DATE  20010430\n",
      "DATE  20010531\n",
      "DATE  20010629\n",
      "DATE  20010731\n",
      "DATE  20010831\n",
      "DATE  20010928\n",
      "DATE  20011031\n",
      "DATE  20011130\n",
      "DATE  20011231\n",
      "DATE  20020131\n",
      "DATE  20020228\n",
      "DATE  20020328\n",
      "DATE  20020430\n",
      "DATE  20020531\n",
      "DATE  20020628\n",
      "DATE  20020731\n",
      "DATE  20020830\n",
      "DATE  20020930\n",
      "DATE  20021031\n",
      "DATE  20021129\n",
      "DATE  20021231\n",
      "DATE  20030131\n",
      "DATE  20030228\n",
      "DATE  20030331\n",
      "DATE  20030430\n",
      "DATE  20030530\n",
      "DATE  20030630\n",
      "DATE  20030731\n",
      "DATE  20030829\n",
      "DATE  20030930\n",
      "DATE  20031031\n",
      "DATE  20031128\n",
      "DATE  20031231\n",
      "DATE  20040130\n",
      "DATE  20040227\n",
      "DATE  20040331\n",
      "DATE  20040430\n",
      "DATE  20040528\n",
      "DATE  20040630\n",
      "DATE  20040730\n",
      "DATE  20040831\n",
      "DATE  20040930\n",
      "DATE  20041029\n",
      "DATE  20041130\n",
      "DATE  20041231\n",
      "DATE  20050131\n",
      "DATE  20050228\n",
      "DATE  20050331\n",
      "DATE  20050429\n",
      "DATE  20050531\n",
      "DATE  20050630\n",
      "DATE  20050729\n",
      "DATE  20050831\n",
      "DATE  20050930\n",
      "DATE  20051031\n",
      "DATE  20051130\n",
      "DATE  20051230\n",
      "DATE  20060131\n",
      "DATE  20060228\n",
      "DATE  20060331\n",
      "DATE  20060428\n",
      "DATE  20060531\n",
      "DATE  20060630\n",
      "DATE  20060731\n",
      "DATE  20060831\n",
      "DATE  20060929\n",
      "DATE  20061031\n",
      "DATE  20061130\n",
      "DATE  20061229\n",
      "DATE  20070131\n",
      "DATE  20070228\n",
      "DATE  20070330\n",
      "DATE  20070430\n",
      "DATE  20070531\n",
      "DATE  20070629\n",
      "DATE  20070731\n",
      "DATE  20070831\n",
      "DATE  20070928\n",
      "DATE  20071031\n",
      "DATE  20071130\n",
      "DATE  20071231\n",
      "DATE  20080131\n",
      "DATE  20080229\n",
      "DATE  20080331\n",
      "DATE  20080430\n",
      "DATE  20080530\n",
      "DATE  20080630\n",
      "DATE  20080731\n",
      "DATE  20080829\n",
      "DATE  20080930\n",
      "DATE  20081031\n",
      "DATE  20081128\n",
      "DATE  20081231\n",
      "DATE  20090130\n",
      "DATE  20090227\n",
      "DATE  20090331\n",
      "DATE  20090430\n",
      "DATE  20090529\n",
      "DATE  20090630\n",
      "DATE  20090731\n",
      "DATE  20090831\n",
      "DATE  20090930\n",
      "DATE  20091030\n",
      "DATE  20091130\n",
      "DATE  20091231\n",
      "DATE  20100129\n",
      "DATE  20100226\n",
      "DATE  20100331\n",
      "DATE  20100430\n",
      "DATE  20100528\n",
      "DATE  20100630\n",
      "DATE  20100730\n",
      "DATE  20100831\n",
      "DATE  20100930\n",
      "DATE  20101029\n",
      "DATE  20101130\n",
      "DATE  20101231\n",
      "DATE  20110131\n",
      "DATE  20110228\n",
      "DATE  20110331\n",
      "DATE  20110429\n",
      "DATE  20110531\n",
      "DATE  20110630\n",
      "DATE  20110729\n",
      "DATE  20110831\n",
      "DATE  20110930\n",
      "DATE  20111031\n",
      "DATE  20111130\n",
      "DATE  20111230\n",
      "DATE  20120131\n",
      "DATE  20120229\n",
      "DATE  20120330\n",
      "DATE  20120430\n",
      "DATE  20120531\n",
      "DATE  20120629\n",
      "DATE  20120731\n",
      "DATE  20120831\n",
      "DATE  20120928\n",
      "DATE  20121031\n",
      "DATE  20121130\n",
      "DATE  20121231\n",
      "DATE  20130131\n",
      "DATE  20130228\n",
      "DATE  20130328\n",
      "DATE  20130430\n",
      "DATE  20130531\n",
      "DATE  20130628\n",
      "DATE  20130731\n",
      "DATE  20130830\n",
      "DATE  20130930\n",
      "DATE  20131031\n",
      "DATE  20131129\n",
      "DATE  20131231\n",
      "DATE  20140131\n",
      "DATE  20140228\n",
      "DATE  20140331\n",
      "DATE  20140430\n",
      "DATE  20140530\n",
      "DATE  20140630\n",
      "DATE  20140731\n",
      "DATE  20140829\n",
      "DATE  20140930\n",
      "DATE  20141031\n",
      "DATE  20141128\n",
      "DATE  20141231\n",
      "DATE  20150130\n",
      "DATE  20150227\n",
      "DATE  20150331\n",
      "DATE  20150430\n",
      "DATE  20150529\n",
      "DATE  20150630\n",
      "DATE  20150731\n",
      "DATE  20150831\n",
      "DATE  20150930\n",
      "DATE  20151030\n",
      "DATE  20151130\n",
      "DATE  20151231\n",
      "DATE  20160129\n",
      "DATE  20160229\n",
      "DATE  20160331\n",
      "DATE  20160429\n",
      "DATE  20160531\n",
      "DATE  20160630\n",
      "DATE  20160729\n",
      "DATE  20160831\n",
      "DATE  20160930\n",
      "DATE  20161031\n",
      "DATE  20161130\n",
      "DATE  20161230\n",
      "DATE  20170131\n",
      "DATE  20170228\n",
      "DATE  20170331\n",
      "DATE  20170428\n",
      "DATE  20170531\n",
      "DATE  20170630\n",
      "DATE  20170731\n",
      "DATE  20170831\n",
      "DATE  20170929\n",
      "DATE  20171031\n",
      "DATE  20171130\n",
      "DATE  20171229\n",
      "DATE  20180131\n",
      "DATE  20180228\n",
      "DATE  20180329\n",
      "DATE  20180430\n",
      "DATE  20180531\n",
      "DATE  20180629\n",
      "DATE  20180731\n",
      "DATE  20180831\n",
      "DATE  20180928\n",
      "DATE  20181031\n",
      "DATE  20181130\n",
      "DATE  20181231\n",
      "DATE  20190131\n",
      "DATE  20190228\n",
      "DATE  20190329\n",
      "DATE  20190430\n",
      "DATE  20190531\n",
      "DATE  20190628\n",
      "DATE  20190731\n",
      "DATE  20190830\n",
      "DATE  20190930\n",
      "DATE  20191031\n",
      "DATE  20191129\n",
      "DATE  20191231\n",
      "DATE  20200131\n",
      "DATE  20200228\n",
      "DATE  20200331\n",
      "DATE  20200430\n",
      "DATE  20200529\n",
      "DATE  20200630\n",
      "DATE  20200731\n",
      "DATE  20200831\n",
      "DATE  20200930\n",
      "DATE  20201030\n",
      "DATE  20201130\n",
      "DATE  20201231\n",
      "DATE  20210129\n",
      "DATE  20210226\n",
      "DATE  20210331\n",
      "DATE  20210430\n",
      "DATE  20210528\n",
      "DATE  20210630\n",
      "DATE  20210730\n",
      "DATE  20210831\n",
      "DATE  20210930\n",
      "DATE  20211029\n",
      "DATE  20211130\n",
      "DATE  20211231\n"
     ]
    }
   ],
   "source": [
    "#inicilize df for DBSCAN - make empty df\n",
    "#initial dataframe\n",
    "df_DBSCAN=pd.DataFrame(columns=Original_df_columns_names + DBSCAN_column_names)\n",
    "dates = sorted(df_PCA['DATE'].unique())\n",
    "for date in dates:\n",
    "    print(\"DATE \",date)\n",
    "    #date = 19791231\n",
    "    df_DBSCAN_window = df_PCA[df_PCA['DATE'] == date].sort_values('permno')\n",
    "\n",
    "    #list of column names of PCA components needed to explain 99% of variance\n",
    "    number_of_PCA_components_for_99_variance_explainability =  df_DBSCAN_window['n_to_reach_99'].min()\n",
    "    PCA_components_for_99_variance_explainability_column_names = ['PCA '+ str(i+1) for i in range(number_of_PCA_components_for_99_variance_explainability)]\n",
    "\n",
    "    # Define the key inputs for the DBSCAN model\n",
    "    min_samples = int(round(np.log(len(df_DBSCAN_window)))) #MinPts is set to be the natural logarithm of the total number of data points N\n",
    "\n",
    "    # Create an instance of the class NearestNeighbors and fit the model\n",
    "    #The minimum distance is set as the α percentile of the average distances to the nearest MinPts neighbour points\n",
    "    #So we have to add 1 since 0 position in distances is 0\n",
    "    n_neighbors = min_samples + 1 \n",
    "    #that l1 norm provides more significant and meaningful clustering results for high-dimensional datasets, we choose l1 norm as the distance metric for DBSCAN.\n",
    "    nn_model    = NearestNeighbors(n_neighbors=n_neighbors, metric='l1')\n",
    "    neighbors   = nn_model.fit(df_DBSCAN_window[PCA_components_for_99_variance_explainability_column_names])\n",
    "\n",
    "    # Calcuate average distances to n_neighbors\n",
    "    distances, indices = neighbors.kneighbors(df_DBSCAN_window[PCA_components_for_99_variance_explainability_column_names])\n",
    "    distances_avg = distances[:, 1:].mean(axis=1)\n",
    "    distances_avg = np.sort(distances_avg, axis=0)\n",
    "\n",
    "    for alpha in range(10,100,10):\n",
    "        #we use alpha from 10 - 90\n",
    "        distance_of_alpha_percentile_to_nearest_MinPts = np.percentile(distances_avg, alpha)\n",
    "\n",
    "        # Create an instance of the class DBSCAN, fit the model with l1 metric\n",
    "        dbscan_model = DBSCAN(eps=distance_of_alpha_percentile_to_nearest_MinPts,metric='l1',\n",
    "                                min_samples=min_samples)\n",
    "        dbscan_model_fited = dbscan_model.fit(df_DBSCAN_window[PCA_components_for_99_variance_explainability_column_names])\n",
    "        \n",
    "        #determine cluster lables and fill data to df\n",
    "        df_DBSCAN_window[f'cluster alpha={alpha}'] = dbscan_model_fited.labels_\n",
    "\n",
    "    df_DBSCAN=pd.concat([df_DBSCAN,df_DBSCAN_window],axis=0)\n",
    "df_DBSCAN = df_DBSCAN[Original_df_columns_names+DBSCAN_column_names]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DATE</th>\n",
       "      <th>permno</th>\n",
       "      <th>sic2</th>\n",
       "      <th>cluster alpha=10</th>\n",
       "      <th>cluster alpha=20</th>\n",
       "      <th>cluster alpha=30</th>\n",
       "      <th>cluster alpha=40</th>\n",
       "      <th>cluster alpha=50</th>\n",
       "      <th>cluster alpha=60</th>\n",
       "      <th>cluster alpha=70</th>\n",
       "      <th>cluster alpha=80</th>\n",
       "      <th>cluster alpha=90</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19791231</td>\n",
       "      <td>10006</td>\n",
       "      <td>37.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>19791231</td>\n",
       "      <td>10057</td>\n",
       "      <td>36.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>19791231</td>\n",
       "      <td>10137</td>\n",
       "      <td>49.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>19791231</td>\n",
       "      <td>10145</td>\n",
       "      <td>99.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>19791231</td>\n",
       "      <td>10153</td>\n",
       "      <td>13.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1502130</th>\n",
       "      <td>20211231</td>\n",
       "      <td>93423</td>\n",
       "      <td>79.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1502131</th>\n",
       "      <td>20211231</td>\n",
       "      <td>93426</td>\n",
       "      <td>36.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1502132</th>\n",
       "      <td>20211231</td>\n",
       "      <td>93427</td>\n",
       "      <td>36.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1502133</th>\n",
       "      <td>20211231</td>\n",
       "      <td>93434</td>\n",
       "      <td>1.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1502134</th>\n",
       "      <td>20211231</td>\n",
       "      <td>93436</td>\n",
       "      <td>37.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1502135 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             DATE permno  sic2 cluster alpha=10 cluster alpha=20  \\\n",
       "0        19791231  10006  37.0                0                0   \n",
       "1        19791231  10057  36.0               -1               -1   \n",
       "2        19791231  10137  49.0                0                0   \n",
       "3        19791231  10145  99.0               -1               -1   \n",
       "4        19791231  10153  13.0               -1               -1   \n",
       "...           ...    ...   ...              ...              ...   \n",
       "1502130  20211231  93423  79.0               -1               -1   \n",
       "1502131  20211231  93426  36.0               -1                0   \n",
       "1502132  20211231  93427  36.0               -1               -1   \n",
       "1502133  20211231  93434   1.0               -1               -1   \n",
       "1502134  20211231  93436  37.0               -1               -1   \n",
       "\n",
       "        cluster alpha=30 cluster alpha=40 cluster alpha=50 cluster alpha=60  \\\n",
       "0                      0                0                0                0   \n",
       "1                     -1               -1                0                0   \n",
       "2                      0                0                0                0   \n",
       "3                     -1                0                0                0   \n",
       "4                     -1                0                0                0   \n",
       "...                  ...              ...              ...              ...   \n",
       "1502130                1                0                0                0   \n",
       "1502131                1                0                0                0   \n",
       "1502132                1                0                0                0   \n",
       "1502133               -1               -1               -1               -1   \n",
       "1502134               -1               -1               -1               -1   \n",
       "\n",
       "        cluster alpha=70 cluster alpha=80 cluster alpha=90  \n",
       "0                      0                0                0  \n",
       "1                      0                0                0  \n",
       "2                      0                0                0  \n",
       "3                      0                0                0  \n",
       "4                      0                0                0  \n",
       "...                  ...              ...              ...  \n",
       "1502130                0                0                0  \n",
       "1502131                0                0                0  \n",
       "1502132                0                0                0  \n",
       "1502133               -1                0                0  \n",
       "1502134               -1               -1               -1  \n",
       "\n",
       "[1502135 rows x 12 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_DBSCAN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_DBSCAN.to_csv('D:/USB Drive/Data/DachengXiu/df_DBSCAN.csv')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Analysis:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_DBSCAN = pd.read_csv('D:/USB Drive/Data/DachengXiu/df_DBSCAN.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1990_2000 = df_DBSCAN[(df_DBSCAN['DATE'] > 19891231 ) & (df_DBSCAN['DATE'] < 20210101)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.5403225806451615"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Number of clusters 2\n",
    "df_1990_2000.groupby(['DATE','cluster alpha=10']).nunique()['permno'].count()/372-1\n",
    "#-1 are outliers\n",
    "#1.54"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3286.5779569892475"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Number of stocks in total 3157\n",
    "df_1990_2000.groupby(['DATE'])['permno'].count().mean()\n",
    "#3286"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "429.22849462365593"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Number of stocks in clusters 376 (12.05)\n",
    "df_1990_2000[df_1990_2000['cluster alpha=10'] > -1].groupby(['DATE'])['permno'].count().mean()\n",
    "#429"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "cluster alpha=10    2847.325269\n",
       "cluster alpha=20    2448.150538\n",
       "cluster alpha=30    2077.954301\n",
       "cluster alpha=40    1727.026882\n",
       "cluster alpha=50    1392.755376\n",
       "cluster alpha=60    1079.911290\n",
       "cluster alpha=70     792.344086\n",
       "cluster alpha=80     517.263441\n",
       "cluster alpha=90     247.180108\n",
       "dtype: float64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Number of outliers 2781 (87.95)\n",
    "-df_1990_2000[DBSCAN_column_names].sum()/372\n",
    "#In paper we use cluster alpha=10    \n",
    "# 2847.325269"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "420.1317204301075"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Number of stocks in the biggest cluster 336 (88.89)\n",
    "df_1990_2000[df_1990_2000['cluster alpha=10'] == 0].groupby(['DATE','cluster alpha=10']).nunique()['permno'].mean()\n",
    "#420.1317204301075"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "18.822085889570552"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Number of stocks in the second biggest cluster 32 (8.74)\n",
    "df_1990_2000[df_1990_2000['cluster alpha=10'] == 1].groupby(['DATE','cluster alpha=10']).nunique()['permno'].mean()\n",
    "#.sort_values(ascending=False).head(5)\n",
    "#18.82"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8.588235294117647"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Number of stocks in the third biggest cluster 6 (1.88) \n",
    "df_1990_2000[df_1990_2000['cluster alpha=10'] == 2].groupby(['DATE','cluster alpha=10']).nunique()['permno'].mean()\n",
    "#.sort_values(ascending=False).head(5)\n",
    "#8.58"
   ]
  }
 ],
 "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.11.1 (tags/v3.11.1:a7a450f, Dec  6 2022, 19:58:39) [MSC v.1934 64 bit (AMD64)]"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "ead398d1f62d3de1ff81f1555316da2088b087e145651ae83a7acff8e232f088"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}