Load_Data (market_cap).ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import seaborn as sb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.read_csv('BAB_data.csv').drop(['Unnamed: 0','Price'],axis=1).sort_values(['Ticker','date']).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_rows',5000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>CUR_MKT_CAP</th>\n",
       "      <th>Ticker</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>8505.0530</td>\n",
       "      <td>ABB IS Equity</td>\n",
       "      <td>2002-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>8492.6275</td>\n",
       "      <td>ABB IS Equity</td>\n",
       "      <td>2002-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>8505.0530</td>\n",
       "      <td>ABB IS Equity</td>\n",
       "      <td>2002-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>8490.5607</td>\n",
       "      <td>ABB IS Equity</td>\n",
       "      <td>2002-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>8519.5453</td>\n",
       "      <td>ABB IS Equity</td>\n",
       "      <td>2002-01-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>220889</td>\n",
       "      <td>276331.0267</td>\n",
       "      <td>Z IS Equity</td>\n",
       "      <td>2019-12-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>220890</td>\n",
       "      <td>272921.3112</td>\n",
       "      <td>Z IS Equity</td>\n",
       "      <td>2019-12-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>220891</td>\n",
       "      <td>260242.9325</td>\n",
       "      <td>Z IS Equity</td>\n",
       "      <td>2019-12-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>220892</td>\n",
       "      <td>272248.9730</td>\n",
       "      <td>Z IS Equity</td>\n",
       "      <td>2019-12-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>220893</td>\n",
       "      <td>268359.0100</td>\n",
       "      <td>Z IS Equity</td>\n",
       "      <td>2019-12-12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>220894 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        CUR_MKT_CAP         Ticker        date\n",
       "0         8505.0530  ABB IS Equity  2002-01-01\n",
       "1         8492.6275  ABB IS Equity  2002-01-02\n",
       "2         8505.0530  ABB IS Equity  2002-01-03\n",
       "3         8490.5607  ABB IS Equity  2002-01-04\n",
       "4         8519.5453  ABB IS Equity  2002-01-07\n",
       "...             ...            ...         ...\n",
       "220889  276331.0267    Z IS Equity  2019-12-06\n",
       "220890  272921.3112    Z IS Equity  2019-12-09\n",
       "220891  260242.9325    Z IS Equity  2019-12-10\n",
       "220892  272248.9730    Z IS Equity  2019-12-11\n",
       "220893  268359.0100    Z IS Equity  2019-12-12\n",
       "\n",
       "[220894 rows x 3 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>Beta</th>\n",
       "      <th>E(Ri)</th>\n",
       "      <th>Ri</th>\n",
       "      <th>Ticker</th>\n",
       "      <th>date</th>\n",
       "      <th>minus</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.170441</td>\n",
       "      <td>0.146306</td>\n",
       "      <td>ABB IS Equity</td>\n",
       "      <td>2002-01-03</td>\n",
       "      <td>0.881350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.341463</td>\n",
       "      <td>-0.170441</td>\n",
       "      <td>ABB IS Equity</td>\n",
       "      <td>2002-01-04</td>\n",
       "      <td>2.157729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.750122</td>\n",
       "      <td>0.341463</td>\n",
       "      <td>ABB IS Equity</td>\n",
       "      <td>2002-01-07</td>\n",
       "      <td>0.360336</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.668896</td>\n",
       "      <td>1.750122</td>\n",
       "      <td>ABB IS Equity</td>\n",
       "      <td>2002-01-08</td>\n",
       "      <td>1.050654</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.355956</td>\n",
       "      <td>0.668896</td>\n",
       "      <td>ABB IS Equity</td>\n",
       "      <td>2002-01-09</td>\n",
       "      <td>-0.836382</td>\n",
       "    </tr>\n",
       "    <tr>\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",
       "      <td>217411</td>\n",
       "      <td>-0.411990</td>\n",
       "      <td>-4.405738</td>\n",
       "      <td>-0.085310</td>\n",
       "      <td>Z IS Equity</td>\n",
       "      <td>2019-12-02</td>\n",
       "      <td>-0.436630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>217412</td>\n",
       "      <td>-0.450626</td>\n",
       "      <td>0.518042</td>\n",
       "      <td>-4.405738</td>\n",
       "      <td>Z IS Equity</td>\n",
       "      <td>2019-12-03</td>\n",
       "      <td>-0.108903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>217413</td>\n",
       "      <td>-0.448763</td>\n",
       "      <td>6.646526</td>\n",
       "      <td>0.518042</td>\n",
       "      <td>Z IS Equity</td>\n",
       "      <td>2019-12-04</td>\n",
       "      <td>0.408531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>217414</td>\n",
       "      <td>-0.439833</td>\n",
       "      <td>-4.115981</td>\n",
       "      <td>6.646526</td>\n",
       "      <td>Z IS Equity</td>\n",
       "      <td>2019-12-05</td>\n",
       "      <td>-2.465288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>217415</td>\n",
       "      <td>-0.360021</td>\n",
       "      <td>-1.233924</td>\n",
       "      <td>-4.115981</td>\n",
       "      <td>Z IS Equity</td>\n",
       "      <td>2019-12-06</td>\n",
       "      <td>-1.608322</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>217416 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Beta     E(Ri)        Ri         Ticker        date     minus\n",
       "0            NaN -0.170441  0.146306  ABB IS Equity  2002-01-03  0.881350\n",
       "1            NaN  0.341463 -0.170441  ABB IS Equity  2002-01-04  2.157729\n",
       "2            NaN  1.750122  0.341463  ABB IS Equity  2002-01-07  0.360336\n",
       "3            NaN  0.668896  1.750122  ABB IS Equity  2002-01-08  1.050654\n",
       "4            NaN -0.355956  0.668896  ABB IS Equity  2002-01-09 -0.836382\n",
       "...          ...       ...       ...            ...         ...       ...\n",
       "217411 -0.411990 -4.405738 -0.085310    Z IS Equity  2019-12-02 -0.436630\n",
       "217412 -0.450626  0.518042 -4.405738    Z IS Equity  2019-12-03 -0.108903\n",
       "217413 -0.448763  6.646526  0.518042    Z IS Equity  2019-12-04  0.408531\n",
       "217414 -0.439833 -4.115981  6.646526    Z IS Equity  2019-12-05 -2.465288\n",
       "217415 -0.360021 -1.233924 -4.115981    Z IS Equity  2019-12-06 -1.608322\n",
       "\n",
       "[217416 rows x 6 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_beta=pd.read_csv('Beta_done.csv').drop(['Unnamed: 0'],axis=1)\n",
    "df_beta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2=pd.merge(df,df_beta,on=['date','Ticker']).dropna().reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>CUR_MKT_CAP</th>\n",
       "      <th>Ticker</th>\n",
       "      <th>date</th>\n",
       "      <th>Beta</th>\n",
       "      <th>E(Ri)</th>\n",
       "      <th>Ri</th>\n",
       "      <th>minus</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>11775.3566</td>\n",
       "      <td>ABB IS Equity</td>\n",
       "      <td>2002-07-03</td>\n",
       "      <td>-0.039839</td>\n",
       "      <td>0.575850</td>\n",
       "      <td>2.736180</td>\n",
       "      <td>0.344271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>11843.1630</td>\n",
       "      <td>ABB IS Equity</td>\n",
       "      <td>2002-07-04</td>\n",
       "      <td>-0.038681</td>\n",
       "      <td>0.984076</td>\n",
       "      <td>0.575850</td>\n",
       "      <td>-0.111032</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>11959.7085</td>\n",
       "      <td>ABB IS Equity</td>\n",
       "      <td>2002-07-05</td>\n",
       "      <td>-0.039663</td>\n",
       "      <td>-1.009922</td>\n",
       "      <td>0.984076</td>\n",
       "      <td>0.464998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>11838.9249</td>\n",
       "      <td>ABB IS Equity</td>\n",
       "      <td>2002-07-08</td>\n",
       "      <td>-0.042511</td>\n",
       "      <td>-1.342402</td>\n",
       "      <td>-1.009922</td>\n",
       "      <td>1.633052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>11679.9977</td>\n",
       "      <td>ABB IS Equity</td>\n",
       "      <td>2002-07-09</td>\n",
       "      <td>-0.050111</td>\n",
       "      <td>-2.304064</td>\n",
       "      <td>-1.342402</td>\n",
       "      <td>-0.173176</td>\n",
       "    </tr>\n",
       "    <tr>\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",
       "      <td>203229</td>\n",
       "      <td>281229.4912</td>\n",
       "      <td>Z IS Equity</td>\n",
       "      <td>2019-12-02</td>\n",
       "      <td>-0.411990</td>\n",
       "      <td>-4.405738</td>\n",
       "      <td>-0.085310</td>\n",
       "      <td>-0.436630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>203230</td>\n",
       "      <td>268839.2575</td>\n",
       "      <td>Z IS Equity</td>\n",
       "      <td>2019-12-03</td>\n",
       "      <td>-0.450626</td>\n",
       "      <td>0.518042</td>\n",
       "      <td>-4.405738</td>\n",
       "      <td>-0.108903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>203231</td>\n",
       "      <td>270231.9582</td>\n",
       "      <td>Z IS Equity</td>\n",
       "      <td>2019-12-04</td>\n",
       "      <td>-0.448763</td>\n",
       "      <td>6.646526</td>\n",
       "      <td>0.518042</td>\n",
       "      <td>0.408531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>203232</td>\n",
       "      <td>288192.9947</td>\n",
       "      <td>Z IS Equity</td>\n",
       "      <td>2019-12-05</td>\n",
       "      <td>-0.439833</td>\n",
       "      <td>-4.115981</td>\n",
       "      <td>6.646526</td>\n",
       "      <td>-2.465288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>203233</td>\n",
       "      <td>276331.0267</td>\n",
       "      <td>Z IS Equity</td>\n",
       "      <td>2019-12-06</td>\n",
       "      <td>-0.360021</td>\n",
       "      <td>-1.233924</td>\n",
       "      <td>-4.115981</td>\n",
       "      <td>-1.608322</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>203234 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        CUR_MKT_CAP         Ticker        date      Beta     E(Ri)        Ri  \\\n",
       "0        11775.3566  ABB IS Equity  2002-07-03 -0.039839  0.575850  2.736180   \n",
       "1        11843.1630  ABB IS Equity  2002-07-04 -0.038681  0.984076  0.575850   \n",
       "2        11959.7085  ABB IS Equity  2002-07-05 -0.039663 -1.009922  0.984076   \n",
       "3        11838.9249  ABB IS Equity  2002-07-08 -0.042511 -1.342402 -1.009922   \n",
       "4        11679.9977  ABB IS Equity  2002-07-09 -0.050111 -2.304064 -1.342402   \n",
       "...             ...            ...         ...       ...       ...       ...   \n",
       "203229  281229.4912    Z IS Equity  2019-12-02 -0.411990 -4.405738 -0.085310   \n",
       "203230  268839.2575    Z IS Equity  2019-12-03 -0.450626  0.518042 -4.405738   \n",
       "203231  270231.9582    Z IS Equity  2019-12-04 -0.448763  6.646526  0.518042   \n",
       "203232  288192.9947    Z IS Equity  2019-12-05 -0.439833 -4.115981  6.646526   \n",
       "203233  276331.0267    Z IS Equity  2019-12-06 -0.360021 -1.233924 -4.115981   \n",
       "\n",
       "           minus  \n",
       "0       0.344271  \n",
       "1      -0.111032  \n",
       "2       0.464998  \n",
       "3       1.633052  \n",
       "4      -0.173176  \n",
       "...          ...  \n",
       "203229 -0.436630  \n",
       "203230 -0.108903  \n",
       "203231  0.408531  \n",
       "203232 -2.465288  \n",
       "203233 -1.608322  \n",
       "\n",
       "[203234 rows x 7 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.to_csv('Beta_with_cap.csv')"
   ]
  },
  {
   "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}