Classification/Cleaning_Original_Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Improving on cleaning the original Datasets\n",
    "Cleaning up the Data from each quarterly report with respect to its stock ticker."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Importing Libraries and Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "pd.set_option('display.max_rows', 200)\n",
    "pd.set_option('display.max_columns', 70)\n",
    "import numpy as np\n",
    "from tqdm import tqdm_notebook as tqdm\n",
    "import _pickle as pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"stockpup.pkl\",'rb') as fp:\n",
    "    stocks_df = pickle.load(fp)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Setting the Index to the Date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "36807fa71c674944af6e8a8e3cdce7fc",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=756), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "def setting_index(df):\n",
    "    \"\"\"\n",
    "    Returns a sorted date time index\n",
    "    \"\"\"\n",
    "    df['Quarter end'] = pd.to_datetime(df['Quarter end'])\n",
    "    df.set_index(\"Quarter end\", inplace=True)\n",
    "    return df.sort_index(ascending=True)\n",
    "\n",
    "for i in tqdm(stocks_df.keys()):\n",
    "    stocks_df[i] = setting_index(stocks_df[i])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Replacing all \"None\" values with 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "ebd571d538e34a84bc2eea857a001963",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=756), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "for i in tqdm(stocks_df.keys()):\n",
    "    stocks_df[i].replace(\"None\", 0, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Converting all values to numeric values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "ec014d26e29a48ecb9ad706702c5f54d",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=756), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "def to_num(stocks_df):  \n",
    "    \"\"\"\n",
    "    Converts each column in the DFs in the dictionary into numerical values\n",
    "    \"\"\"\n",
    "    num_df = {}\n",
    "\n",
    "    for i in tqdm(stocks_df.keys()):\n",
    "        # Creates a new DF\n",
    "        n_df = pd.DataFrame(index=stocks_df[i].index, columns=stocks_df[i].columns)\n",
    "\n",
    "        cols = n_df.columns\n",
    "        \n",
    "        # Iterate through each column\n",
    "        for c in cols:\n",
    "            # Converting each column's values to numerical values\n",
    "            n_df[c] = pd.to_numeric(stocks_df[i][c])\n",
    "            \n",
    "        # Assigning the DF to the dictionary\n",
    "        num_df[i] = n_df\n",
    "    \n",
    "    return num_df\n",
    "\n",
    "\n",
    "num_df = to_num(stocks_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Preprocessing the Data\n",
    "Replacing values with percent difference between each quarter\n",
    "\n",
    "Also:\n",
    " - Replacing infinite values with 1 or -1\n",
    " - Replacing NaNs with 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "ae916cc9f00842e7b8349bfee97645c7",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=756), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "def percent_improvement(num_df):\n",
    "    \"\"\"\n",
    "    Shows the percent change from the previous to the current quarter\n",
    "    \"\"\"\n",
    "    pcnt_df = {}\n",
    "    \n",
    "    for i in tqdm(num_df.keys()):\n",
    "        # Creating a new DF\n",
    "        df = pd.DataFrame(index=num_df[i].index, columns=num_df[i].columns)\n",
    "        \n",
    "        # Iterating through the DF columns\n",
    "        for c in df.columns:\n",
    "            if c == 'Price high':\n",
    "                df[c] = num_df[i][c]\n",
    "                \n",
    "                # Another column to show the quarterly return it will have (for EDA purposes and class assignment only)\n",
    "                df['Future % High'] = (num_df[i][c].pct_change(1)*100).shift(-1)\n",
    "                \n",
    "            elif c == 'Price low':\n",
    "                df[c] = num_df[i][c]\n",
    "                \n",
    "                # Another column to show the quarterly return it will have\n",
    "                df['Future % Low'] = (num_df[i][c].pct_change(1)*100).shift(-1)\n",
    "            else:\n",
    "                # Assigning the percent values to the new DF columns\n",
    "                df[c] = num_df[i][c].pct_change(1)*100\n",
    "            \n",
    "        # Assigning the new DF to the dictionary and replacing infinite values with 1 and -1\n",
    "        # Also handling the NaN values\n",
    "        pcnt_df[i] = df.replace([np.inf, -np.inf], [1,-1]).dropna(1, 'all').fillna(0)\n",
    "        \n",
    "    return pcnt_df\n",
    "\n",
    "\n",
    "pcnt_df = percent_improvement(num_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "ee6a9e8a6ce545c8a18ce4ba6316ca7f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=756), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# def percent_improvement(num_df):\n",
    "#     \"\"\"\n",
    "#     Shows the percent change from the previous to the current quarter\n",
    "#     \"\"\"\n",
    "#     pcnt_df = {}\n",
    "    \n",
    "#     for i in tqdm(num_df.keys()):\n",
    "#         # Creating a new DF\n",
    "#         df = pd.DataFrame(index=num_df[i].index, columns=num_df[i].columns)\n",
    "        \n",
    "#         # Iterating through the DF columns\n",
    "#         for c in df.columns:\n",
    "#             if c == 'Price':\n",
    "#                 df[c] = num_df[i][c]\n",
    "                \n",
    "#                 # Another column to show the quarterly return it will have (for EDA purposes and class assignment only)\n",
    "#                 df['Future % Return'] = (num_df[i][c].pct_change(1)*100).shift(-1)\n",
    "#             else:\n",
    "#                 # Assigning the percent values to the new DF columns\n",
    "#                 df[c] = num_df[i][c].pct_change(1)*100\n",
    "            \n",
    "#         # Assigning the new DF to the dictionary and replacing infinite values with 1 and -1\n",
    "#         # Also handling the NaN values\n",
    "#         pcnt_df[i] = df.replace([np.inf, -np.inf], [1,-1]).dropna(1, 'all').fillna(0)\n",
    "        \n",
    "#     return pcnt_df\n",
    "\n",
    "\n",
    "# pcnt_df = percent_improvement(num_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating the Classes\n",
    "- Buy (because the highest high and lowest low of the quarter will both increase by 5% or more)\n",
    "- Sell (because the lowest low and highest high of the quarter will both decrease by 5% or more)\n",
    "- Hold (because it will not do either)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "c4cafff392a64b2c8ad3f15782ef5bc8",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=756), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "def price_check(df):\n",
    "    \"\"\"\n",
    "    Creates classes of buy(1), hold(2), sell(0)\n",
    "    \"\"\"\n",
    "\n",
    "    # Iterate through the dictionary of DFs\n",
    "    for i in tqdm(df.keys()):\n",
    "        # Create an empty list for classes\n",
    "        dec = []\n",
    "        \n",
    "        # Iterating through the values of the 'Price' column\n",
    "        for n in range(len(df[i].Price)):\n",
    "            if df[i]['Future % High'][n]>=5 and df[i]['Future % Low'][n]>=5:\n",
    "                # BUY\n",
    "                dec.append(1)\n",
    "            elif df[i]['Future % Low'][n]<=-5 and df[i]['Future % High'][n]<=-5:\n",
    "                # SELL\n",
    "                dec.append(0)\n",
    "            else:\n",
    "                # HOLD\n",
    "                dec.append(2)\n",
    "                \n",
    "        # Assigning the list to new column for each DF\n",
    "        df[i]['Decision'] = dec\n",
    "        \n",
    "    return df\n",
    "                \n",
    "         \n",
    "new_df = price_check(pcnt_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Excluding the first and last rows\n",
    "This is done because the last row has no data to compare percent improvements to and the first row does not have any data to show if the price will increase in the future."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "07afef3aac8745fc976ae28a7d45dd47",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=756), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "for i in tqdm(pcnt_df.keys()):\n",
    "    new_df[i] = new_df[i][1:-1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Checking an example stock's DF for correct labeling and returns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>Price high</th>\n",
       "      <th>Price low</th>\n",
       "      <th>Future % High</th>\n",
       "      <th>Future % Low</th>\n",
       "      <th>Decision</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Quarter end</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-01-31</th>\n",
       "      <td>76.31</td>\n",
       "      <td>62.00</td>\n",
       "      <td>7.810248</td>\n",
       "      <td>18.967742</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-31</th>\n",
       "      <td>72.37</td>\n",
       "      <td>61.01</td>\n",
       "      <td>5.444245</td>\n",
       "      <td>1.622685</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-31</th>\n",
       "      <td>70.45</td>\n",
       "      <td>60.42</td>\n",
       "      <td>2.725337</td>\n",
       "      <td>0.976498</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-30</th>\n",
       "      <td>74.99</td>\n",
       "      <td>63.38</td>\n",
       "      <td>-6.054141</td>\n",
       "      <td>-4.670243</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-31</th>\n",
       "      <td>75.00</td>\n",
       "      <td>65.60</td>\n",
       "      <td>-0.013333</td>\n",
       "      <td>-3.384146</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-10-31</th>\n",
       "      <td>68.52</td>\n",
       "      <td>58.22</td>\n",
       "      <td>9.457093</td>\n",
       "      <td>12.676056</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-07-31</th>\n",
       "      <td>61.84</td>\n",
       "      <td>55.36</td>\n",
       "      <td>10.802070</td>\n",
       "      <td>5.166185</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-04-30</th>\n",
       "      <td>55.99</td>\n",
       "      <td>48.47</td>\n",
       "      <td>10.448294</td>\n",
       "      <td>14.214978</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-31</th>\n",
       "      <td>49.48</td>\n",
       "      <td>42.92</td>\n",
       "      <td>13.156831</td>\n",
       "      <td>12.931034</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-10-31</th>\n",
       "      <td>48.63</td>\n",
       "      <td>43.11</td>\n",
       "      <td>1.747892</td>\n",
       "      <td>-0.440733</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Price high  Price low  Future % High  Future % Low  Decision\n",
       "Quarter end                                                              \n",
       "2019-01-31        76.31      62.00       7.810248     18.967742         1\n",
       "2018-10-31        72.37      61.01       5.444245      1.622685         2\n",
       "2018-07-31        70.45      60.42       2.725337      0.976498         2\n",
       "2018-04-30        74.99      63.38      -6.054141     -4.670243         2\n",
       "2018-01-31        75.00      65.60      -0.013333     -3.384146         2\n",
       "2017-10-31        68.52      58.22       9.457093     12.676056         1\n",
       "2017-07-31        61.84      55.36      10.802070      5.166185         1\n",
       "2017-04-30        55.99      48.47      10.448294     14.214978         1\n",
       "2017-01-31        49.48      42.92      13.156831     12.931034         1\n",
       "2016-10-31        48.63      43.11       1.747892     -0.440733         2"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df['A'][['Price high', 'Price low', 'Future % High', 'Future % Low', 'Decision']].sort_index(ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\"Future % \" will only be used for EDA/Labeling and will be removed during the modeling process."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Combining all stock DFs into one"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "34df5ffa866a43e18adf163236a62724",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=756), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "big_df = pd.DataFrame()\n",
    "\n",
    "for i in tqdm(pcnt_df.keys()):\n",
    "    big_df = big_df.append(new_df[i], sort=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Quick check for NaN values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Shares                                            0\n",
       "Shares split adjusted                             0\n",
       "Split factor                                      0\n",
       "Assets                                            0\n",
       "Current Assets                                 9103\n",
       "Liabilities                                       0\n",
       "Current Liabilities                            9103\n",
       "Shareholders equity                               0\n",
       "Non-controlling interest                      13870\n",
       "Goodwill & intangibles                         2693\n",
       "Long-term debt                                 1705\n",
       "Revenue                                           0\n",
       "Earnings                                          0\n",
       "Earnings available for common stockholders        0\n",
       "EPS basic                                         0\n",
       "EPS diluted                                       0\n",
       "Dividend per share                             8469\n",
       "Cash from operating activities                    0\n",
       "Cash from investing activities                    0\n",
       "Cash from financing activities                    0\n",
       "Cash change during period                         0\n",
       "Cash at end of period                             0\n",
       "Capital expenditures                           1202\n",
       "Price                                             0\n",
       "Price high                                        0\n",
       "Price low                                         0\n",
       "ROE                                             342\n",
       "ROA                                               0\n",
       "Book value of equity per share                    0\n",
       "P/B ratio                                        32\n",
       "P/E ratio                                        54\n",
       "Cumulative dividends per share                 8469\n",
       "Dividend payout ratio                          8512\n",
       "Long-term debt to equity ratio                 1802\n",
       "Equity to assets ratio                           32\n",
       "Net margin                                       54\n",
       "Asset turnover                                    0\n",
       "Free cash flow per share                          0\n",
       "Current ratio                                  9103\n",
       "Future % High                                     0\n",
       "Future % Low                                      0\n",
       "Decision                                          0\n",
       "Preferred equity                              42120\n",
       "dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "big_df.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Filling the NaNs with 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "big_df.fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Shares                                        0\n",
       "Shares split adjusted                         0\n",
       "Split factor                                  0\n",
       "Assets                                        0\n",
       "Current Assets                                0\n",
       "Liabilities                                   0\n",
       "Current Liabilities                           0\n",
       "Shareholders equity                           0\n",
       "Non-controlling interest                      0\n",
       "Goodwill & intangibles                        0\n",
       "Long-term debt                                0\n",
       "Revenue                                       0\n",
       "Earnings                                      0\n",
       "Earnings available for common stockholders    0\n",
       "EPS basic                                     0\n",
       "EPS diluted                                   0\n",
       "Dividend per share                            0\n",
       "Cash from operating activities                0\n",
       "Cash from investing activities                0\n",
       "Cash from financing activities                0\n",
       "Cash change during period                     0\n",
       "Cash at end of period                         0\n",
       "Capital expenditures                          0\n",
       "Price                                         0\n",
       "Price high                                    0\n",
       "Price low                                     0\n",
       "ROE                                           0\n",
       "ROA                                           0\n",
       "Book value of equity per share                0\n",
       "P/B ratio                                     0\n",
       "P/E ratio                                     0\n",
       "Cumulative dividends per share                0\n",
       "Dividend payout ratio                         0\n",
       "Long-term debt to equity ratio                0\n",
       "Equity to assets ratio                        0\n",
       "Net margin                                    0\n",
       "Asset turnover                                0\n",
       "Free cash flow per share                      0\n",
       "Current ratio                                 0\n",
       "Future % High                                 0\n",
       "Future % Low                                  0\n",
       "Decision                                      0\n",
       "Preferred equity                              0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Checking the DF again for NaN\n",
    "big_df.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Excluding columns with less than 500 unique values\n",
    "Except our class 'price_will_increase?' column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Shares                                        False\n",
       "Shares split adjusted                         False\n",
       "Split factor                                   True\n",
       "Assets                                        False\n",
       "Current Assets                                False\n",
       "Liabilities                                   False\n",
       "Current Liabilities                           False\n",
       "Shareholders equity                           False\n",
       "Non-controlling interest                      False\n",
       "Goodwill & intangibles                        False\n",
       "Long-term debt                                False\n",
       "Revenue                                       False\n",
       "Earnings                                      False\n",
       "Earnings available for common stockholders    False\n",
       "EPS basic                                     False\n",
       "EPS diluted                                   False\n",
       "Dividend per share                            False\n",
       "Cash from operating activities                False\n",
       "Cash from investing activities                False\n",
       "Cash from financing activities                False\n",
       "Cash change during period                     False\n",
       "Cash at end of period                         False\n",
       "Capital expenditures                          False\n",
       "Price                                         False\n",
       "Price high                                    False\n",
       "Price low                                     False\n",
       "ROE                                           False\n",
       "ROA                                           False\n",
       "Book value of equity per share                False\n",
       "P/B ratio                                     False\n",
       "P/E ratio                                     False\n",
       "Cumulative dividends per share                False\n",
       "Dividend payout ratio                         False\n",
       "Long-term debt to equity ratio                False\n",
       "Equity to assets ratio                        False\n",
       "Net margin                                    False\n",
       "Asset turnover                                False\n",
       "Free cash flow per share                      False\n",
       "Current ratio                                 False\n",
       "Future % High                                 False\n",
       "Future % Low                                  False\n",
       "Decision                                       True\n",
       "Preferred equity                              False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "big_df.nunique()<500"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "df2bb70ca549421598a7bb7f97cb7e7f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=43), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "# Iterating through each column and checking if unique values are less than 500\n",
    "# Then dropping those columns\n",
    "for i in tqdm(big_df.columns):\n",
    "    if big_df[i].nunique() < 500 and i != 'Decision':\n",
    "        big_df.drop(i, axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Resetting the index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>Shares</th>\n",
       "      <th>Shares split adjusted</th>\n",
       "      <th>Assets</th>\n",
       "      <th>Current Assets</th>\n",
       "      <th>Liabilities</th>\n",
       "      <th>Current Liabilities</th>\n",
       "      <th>Shareholders equity</th>\n",
       "      <th>Non-controlling interest</th>\n",
       "      <th>Goodwill &amp; intangibles</th>\n",
       "      <th>Long-term debt</th>\n",
       "      <th>Revenue</th>\n",
       "      <th>Earnings</th>\n",
       "      <th>Earnings available for common stockholders</th>\n",
       "      <th>EPS basic</th>\n",
       "      <th>EPS diluted</th>\n",
       "      <th>Dividend per share</th>\n",
       "      <th>Cash from operating activities</th>\n",
       "      <th>Cash from investing activities</th>\n",
       "      <th>Cash from financing activities</th>\n",
       "      <th>Cash change during period</th>\n",
       "      <th>Cash at end of period</th>\n",
       "      <th>Capital expenditures</th>\n",
       "      <th>Price</th>\n",
       "      <th>Price high</th>\n",
       "      <th>Price low</th>\n",
       "      <th>ROE</th>\n",
       "      <th>ROA</th>\n",
       "      <th>Book value of equity per share</th>\n",
       "      <th>P/B ratio</th>\n",
       "      <th>P/E ratio</th>\n",
       "      <th>Cumulative dividends per share</th>\n",
       "      <th>Dividend payout ratio</th>\n",
       "      <th>Long-term debt to equity ratio</th>\n",
       "      <th>Equity to assets ratio</th>\n",
       "      <th>Net margin</th>\n",
       "      <th>Asset turnover</th>\n",
       "      <th>Free cash flow per share</th>\n",
       "      <th>Current ratio</th>\n",
       "      <th>Future % High</th>\n",
       "      <th>Future % Low</th>\n",
       "      <th>Decision</th>\n",
       "      <th>Preferred equity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.060170</td>\n",
       "      <td>0.060170</td>\n",
       "      <td>3.011116</td>\n",
       "      <td>1.505420</td>\n",
       "      <td>2.212896</td>\n",
       "      <td>5.757136</td>\n",
       "      <td>3.477486</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.641140</td>\n",
       "      <td>26.717557</td>\n",
       "      <td>26.717557</td>\n",
       "      <td>23.333333</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-109.895833</td>\n",
       "      <td>63.942308</td>\n",
       "      <td>-100.922819</td>\n",
       "      <td>-128.508772</td>\n",
       "      <td>-28.508772</td>\n",
       "      <td>216.666667</td>\n",
       "      <td>92.855945</td>\n",
       "      <td>159.00</td>\n",
       "      <td>71.00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.427419</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.459442</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>-137.179487</td>\n",
       "      <td>-4.020246</td>\n",
       "      <td>-36.635220</td>\n",
       "      <td>-42.605634</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.164196</td>\n",
       "      <td>0.164196</td>\n",
       "      <td>6.911624</td>\n",
       "      <td>5.675302</td>\n",
       "      <td>9.182531</td>\n",
       "      <td>14.821592</td>\n",
       "      <td>5.601034</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.444668</td>\n",
       "      <td>-6.626506</td>\n",
       "      <td>-6.626506</td>\n",
       "      <td>-8.108108</td>\n",
       "      <td>-5.555556</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-281.578947</td>\n",
       "      <td>22.287390</td>\n",
       "      <td>-763.636364</td>\n",
       "      <td>-29.487179</td>\n",
       "      <td>-28.118609</td>\n",
       "      <td>133.684211</td>\n",
       "      <td>-38.478261</td>\n",
       "      <td>100.75</td>\n",
       "      <td>40.75</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.458090</td>\n",
       "      <td>-40.465919</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1.230090</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>17.241379</td>\n",
       "      <td>-7.966629</td>\n",
       "      <td>-37.468983</td>\n",
       "      <td>-4.760736</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.739888</td>\n",
       "      <td>0.739888</td>\n",
       "      <td>7.640220</td>\n",
       "      <td>5.819611</td>\n",
       "      <td>8.034188</td>\n",
       "      <td>9.880478</td>\n",
       "      <td>7.405141</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>26.292135</td>\n",
       "      <td>96.774194</td>\n",
       "      <td>96.774194</td>\n",
       "      <td>97.058824</td>\n",
       "      <td>91.176471</td>\n",
       "      <td>0.0</td>\n",
       "      <td>513.043478</td>\n",
       "      <td>-63.788969</td>\n",
       "      <td>-71.232877</td>\n",
       "      <td>-206.545455</td>\n",
       "      <td>41.678521</td>\n",
       "      <td>59.909910</td>\n",
       "      <td>-28.042403</td>\n",
       "      <td>63.00</td>\n",
       "      <td>38.81</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.654344</td>\n",
       "      <td>-31.739130</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-0.223535</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>-144.117647</td>\n",
       "      <td>-3.696398</td>\n",
       "      <td>7.936508</td>\n",
       "      <td>-1.932492</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.088384</td>\n",
       "      <td>0.088384</td>\n",
       "      <td>9.293769</td>\n",
       "      <td>-3.430592</td>\n",
       "      <td>16.044304</td>\n",
       "      <td>11.312545</td>\n",
       "      <td>5.242165</td>\n",
       "      <td>0.0</td>\n",
       "      <td>117.773788</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-15.747331</td>\n",
       "      <td>-49.508197</td>\n",
       "      <td>-49.508197</td>\n",
       "      <td>-49.253731</td>\n",
       "      <td>-49.230769</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-146.572104</td>\n",
       "      <td>476.158940</td>\n",
       "      <td>2300.000000</td>\n",
       "      <td>-292.150171</td>\n",
       "      <td>-56.526104</td>\n",
       "      <td>-67.887324</td>\n",
       "      <td>4.164211</td>\n",
       "      <td>68.00</td>\n",
       "      <td>38.06</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.112652</td>\n",
       "      <td>-2.335456</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-3.696591</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>-553.333333</td>\n",
       "      <td>-13.246196</td>\n",
       "      <td>-19.117647</td>\n",
       "      <td>-34.314241</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.066338</td>\n",
       "      <td>0.066338</td>\n",
       "      <td>-1.390096</td>\n",
       "      <td>-8.478301</td>\n",
       "      <td>-5.535860</td>\n",
       "      <td>0.912052</td>\n",
       "      <td>1.353546</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.061006</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-16.156283</td>\n",
       "      <td>-37.662338</td>\n",
       "      <td>-37.662338</td>\n",
       "      <td>-38.235294</td>\n",
       "      <td>-36.363636</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-100.000000</td>\n",
       "      <td>-100.000000</td>\n",
       "      <td>-100.000000</td>\n",
       "      <td>-166.785080</td>\n",
       "      <td>86.836028</td>\n",
       "      <td>199.122807</td>\n",
       "      <td>-24.570998</td>\n",
       "      <td>55.00</td>\n",
       "      <td>25.00</td>\n",
       "      <td>-13.111546</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.319044</td>\n",
       "      <td>-28.260870</td>\n",
       "      <td>-25.917859</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.775008</td>\n",
       "      <td>-8.163265</td>\n",
       "      <td>-6.47482</td>\n",
       "      <td>-100.000000</td>\n",
       "      <td>-9.304025</td>\n",
       "      <td>-25.127273</td>\n",
       "      <td>4.800000</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Shares  Shares split adjusted    Assets  Current Assets  Liabilities  \\\n",
       "0  0.060170               0.060170  3.011116        1.505420     2.212896   \n",
       "1  0.164196               0.164196  6.911624        5.675302     9.182531   \n",
       "2  0.739888               0.739888  7.640220        5.819611     8.034188   \n",
       "3  0.088384               0.088384  9.293769       -3.430592    16.044304   \n",
       "4  0.066338               0.066338 -1.390096       -8.478301    -5.535860   \n",
       "\n",
       "   Current Liabilities  Shareholders equity  Non-controlling interest  \\\n",
       "0             5.757136             3.477486                       0.0   \n",
       "1            14.821592             5.601034                       0.0   \n",
       "2             9.880478             7.405141                       0.0   \n",
       "3            11.312545             5.242165                       0.0   \n",
       "4             0.912052             1.353546                       0.0   \n",
       "\n",
       "   Goodwill & intangibles  Long-term debt    Revenue   Earnings  \\\n",
       "0                0.000000             0.0  10.641140  26.717557   \n",
       "1                0.000000             0.0   7.444668  -6.626506   \n",
       "2                1.000000             0.0  26.292135  96.774194   \n",
       "3              117.773788             0.0 -15.747331 -49.508197   \n",
       "4                2.061006             0.0 -16.156283 -37.662338   \n",
       "\n",
       "   Earnings available for common stockholders  EPS basic  EPS diluted  \\\n",
       "0                                   26.717557  23.333333    20.000000   \n",
       "1                                   -6.626506  -8.108108    -5.555556   \n",
       "2                                   96.774194  97.058824    91.176471   \n",
       "3                                  -49.508197 -49.253731   -49.230769   \n",
       "4                                  -37.662338 -38.235294   -36.363636   \n",
       "\n",
       "   Dividend per share  Cash from operating activities  \\\n",
       "0                 0.0                     -109.895833   \n",
       "1                 0.0                     -281.578947   \n",
       "2                 0.0                      513.043478   \n",
       "3                 0.0                     -146.572104   \n",
       "4                 0.0                     -100.000000   \n",
       "\n",
       "   Cash from investing activities  Cash from financing activities  \\\n",
       "0                       63.942308                     -100.922819   \n",
       "1                       22.287390                     -763.636364   \n",
       "2                      -63.788969                      -71.232877   \n",
       "3                      476.158940                     2300.000000   \n",
       "4                     -100.000000                     -100.000000   \n",
       "\n",
       "   Cash change during period  Cash at end of period  Capital expenditures  \\\n",
       "0                -128.508772             -28.508772            216.666667   \n",
       "1                 -29.487179             -28.118609            133.684211   \n",
       "2                -206.545455              41.678521             59.909910   \n",
       "3                -292.150171             -56.526104            -67.887324   \n",
       "4                -166.785080              86.836028            199.122807   \n",
       "\n",
       "       Price  Price high  Price low        ROE  ROA  \\\n",
       "0  92.855945      159.00      71.00   0.000000  0.0   \n",
       "1 -38.478261      100.75      40.75   0.000000  0.0   \n",
       "2 -28.042403       63.00      38.81   0.000000  0.0   \n",
       "3   4.164211       68.00      38.06   1.000000  0.0   \n",
       "4 -24.570998       55.00      25.00 -13.111546  0.0   \n",
       "\n",
       "   Book value of equity per share  P/B ratio  P/E ratio  \\\n",
       "0                        3.427419   1.000000   0.000000   \n",
       "1                        5.458090 -40.465919   0.000000   \n",
       "2                        6.654344 -31.739130   0.000000   \n",
       "3                        5.112652  -2.335456   1.000000   \n",
       "4                        1.319044 -28.260870 -25.917859   \n",
       "\n",
       "   Cumulative dividends per share  Dividend payout ratio  \\\n",
       "0                             0.0                    0.0   \n",
       "1                             0.0                    0.0   \n",
       "2                             0.0                    0.0   \n",
       "3                             0.0                    0.0   \n",
       "4                             0.0                    0.0   \n",
       "\n",
       "   Long-term debt to equity ratio  Equity to assets ratio  Net margin  \\\n",
       "0                             0.0                0.459442    0.000000   \n",
       "1                             0.0               -1.230090    0.000000   \n",
       "2                             0.0               -0.223535    0.000000   \n",
       "3                             0.0               -3.696591    1.000000   \n",
       "4                             0.0                2.775008   -8.163265   \n",
       "\n",
       "   Asset turnover  Free cash flow per share  Current ratio  Future % High  \\\n",
       "0         0.00000               -137.179487      -4.020246     -36.635220   \n",
       "1         0.00000                 17.241379      -7.966629     -37.468983   \n",
       "2         0.00000               -144.117647      -3.696398       7.936508   \n",
       "3         1.00000               -553.333333     -13.246196     -19.117647   \n",
       "4        -6.47482               -100.000000      -9.304025     -25.127273   \n",
       "\n",
       "   Future % Low  Decision  Preferred equity  \n",
       "0    -42.605634         0               0.0  \n",
       "1     -4.760736         2               0.0  \n",
       "2     -1.932492         2               0.0  \n",
       "3    -34.314241         0               0.0  \n",
       "4      4.800000         2               0.0  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "big_df.reset_index(drop=True, inplace=True)\n",
    "big_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Counting how many classes there are\n",
    "Must make sure they are equal for future modeling."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2    33629\n",
       "1    17554\n",
       "0     8914\n",
       "Name: Decision, dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "big_df['Decision'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The 'Sell' [0] Class has the least so each class has to match that count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Determining how many rows to remove\n",
    "buy = len(big_df[big_df['Decision']==1])\n",
    "hold = len(big_df[big_df['Decision']==2])\n",
    "sell = len(big_df[big_df['Decision']==0])\n",
    "\n",
    "rem_buy = buy - sell\n",
    "rem_hold = hold - sell"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Removing the excess values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda3/lib/python3.7/site-packages/pandas/core/frame.py:3940: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  errors=errors)\n"
     ]
    }
   ],
   "source": [
    "import random\n",
    "\n",
    "# Selecting only the 'Buy' rows\n",
    "buy_df = big_df[big_df['Decision']==1]\n",
    "\n",
    "# Selecting only the 'Hold' rows\n",
    "hold_df = big_df[big_df['Decision']==2]\n",
    "\n",
    "# Randomly choosing rows\n",
    "drop_buy = random.sample(list(buy_df.index), rem_buy)\n",
    "drop_hold = random.sample(list(hold_df.index), rem_hold)\n",
    "\n",
    "# Dropping rows\n",
    "buy_df.drop(drop_buy, inplace=True)\n",
    "hold_df.drop(drop_hold, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating the DataFrame with classes equal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2    8914\n",
       "1    8914\n",
       "0    8914\n",
       "Name: Decision, dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# First assigning the DF full of false values\n",
    "sell_df = big_df[big_df['Decision']==0]\n",
    "\n",
    "# Concatenating all\n",
    "final_df = pd.concat([buy_df, hold_df, sell_df])\n",
    "\n",
    "# Double checking to see if both values are the same\n",
    "final_df['Decision'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exporting the final dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"main_df.pkl\", 'wb') as fp:\n",
    "    pickle.dump(final_df, fp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}