combinedata.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "done importing\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import yfinance as yf\n",
    "from os.path import exists\n",
    "print(\"done importing\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "# some helpful definitions\n",
    "stocks = { # excludes SPY\n",
    "    'BHP', 'NPCPF', 'VITFF', \n",
    "    'GOOG', 'ROKU', 'OPRA', \n",
    "    'LVMUY', 'EXPE', 'TUP', \n",
    "    'JPM', 'MET', 'ARES', \n",
    "    'PLD', 'DITTF', 'INN', \n",
    "    'PG', 'AJINY', 'DFIHY', \n",
    "    'UNH', 'LH', 'NVTA', \n",
    "    'NEE', 'TKGSY', 'AZRE', \n",
    "    'XOM', 'VVV', 'AROC', \n",
    "    'UPS', 'CMI', 'ROCK', \n",
    "    'AAPL', 'CAJ', 'APPN'}\n",
    "\n",
    "directories = {\n",
    "    'risk_data',\n",
    "    'sector_data',\n",
    "    'Mean Reversion'\n",
    "    'stocks_equityreturn_operatingmodel'\n",
    "}\n",
    "\n",
    "risk_dict = {} # maps stock ticker string to dataframe of its factors\n",
    "equity_operatingmodel_dict = {}\n",
    "mean_reversion_dict = {}\n",
    "size_dict = {}\n",
    "merged_dict = {}\n",
    "\n",
    "def get_risk_file(stock):\n",
    "    return 'risk_data\\\\' + stock + '_risk_factors.csv'\n",
    "\n",
    "def get_equity_operatingmodel_file(stock):\n",
    "    return 'stocks_equityreturn_operatingmodel\\\\' + stock + '_CSV.csv'\n",
    "\n",
    "def get_mean_reversion_file(stock):\n",
    "    return 'Mean Reversion\\\\' + stock + '_CSV'\n",
    "\n",
    "def get_size_file(stock):\n",
    "    return 'Mean Reversion\\\\' + stock + '_Size'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mean Reversion\\DFIHY_Size not loaded\n",
      "Mean Reversion\\VVV_Size not loaded\n",
      "Mean Reversion\\BHP_Size not loaded\n",
      "Mean Reversion\\LVMUY_Size not loaded\n",
      "Mean Reversion\\AJINY_CSV not loaded\n",
      "Mean Reversion\\AJINY_Size not loaded\n",
      "Mean Reversion\\VITFF_Size not loaded\n",
      "Mean Reversion\\CAJ_CSV not loaded\n",
      "Mean Reversion\\CAJ_Size not loaded\n",
      "Mean Reversion\\PG_CSV not loaded\n",
      "Mean Reversion\\PG_Size not loaded\n",
      "Mean Reversion\\APPN_Size not loaded\n",
      "Mean Reversion\\ROKU_Size not loaded\n",
      "Mean Reversion\\UPS_CSV not loaded\n",
      "Mean Reversion\\UPS_Size not loaded\n"
     ]
    }
   ],
   "source": [
    "# importing files\n",
    "success = True # set to false if file is missing or doesn't load\n",
    "\n",
    "for stock in stocks:\n",
    "    risk_file = get_risk_file(stock)\n",
    "    equity_operatingmodel_file = get_equity_operatingmodel_file(stock)\n",
    "    mean_reversion_file = get_mean_reversion_file(stock)\n",
    "    size_file = get_size_file(stock)\n",
    "\n",
    "    if(exists(risk_file) == False):\n",
    "        success = False\n",
    "        print (risk_file + ' not loaded')\n",
    "    else:\n",
    "        risk_dict[stock] = pd.read_csv(risk_file, index_col='Date')\n",
    "\n",
    "    if(exists(equity_operatingmodel_file) == False):\n",
    "        success = False\n",
    "        print (equity_operatingmodel_file + ' not loaded')\n",
    "    else:\n",
    "        equity_operatingmodel_dict[stock] = pd.read_csv(equity_operatingmodel_file, index_col='Date')\n",
    "\n",
    "    if(exists(mean_reversion_file) == False):\n",
    "        success = False\n",
    "        print (mean_reversion_file + ' not loaded')\n",
    "    else:\n",
    "        mean_reversion_dict[stock] = pd.read_csv(mean_reversion_file, index_col='Date')\n",
    "\n",
    "    if(exists(size_file) == False):\n",
    "        success = False\n",
    "        print (size_file + ' not loaded')\n",
    "    else:\n",
    "        size_dict[stock] = pd.read_csv(size_file, index_col='Date')\n",
    "        \n",
    "\n",
    "sector_file = 'sector_data\\\\sector_classification.csv'\n",
    "if(exists(sector_file) == False):\n",
    "    success = False\n",
    "    print (sector_file + ' not loaded')\n",
    "else:\n",
    "    sector_data = pd.read_csv(sector_file)\n",
    "\n",
    "if success == True:\n",
    "    print('all files loaded')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "# aligning dataset formatting\n",
    "\n",
    "drop_columns = ['Unnamed: 0']\n",
    "\n",
    "# changing all data to be datetime-indexed, and dropping unnecessary columns\n",
    "for stock in stocks:\n",
    "    risk_dict[stock].index = pd.to_datetime(risk_dict[stock].index) # converting Date column to DateTime format\n",
    "    equity_operatingmodel_dict[stock].index = pd.to_datetime(equity_operatingmodel_dict[stock].index) \n",
    "    if(exists(get_mean_reversion_file(stock)) == True):\n",
    "        mean_reversion_dict[stock].index = pd.to_datetime(mean_reversion_dict[stock].index) \n",
    "        mean_reversion_dict[stock].drop(drop_columns, inplace=True, axis=1)\n",
    "    if(exists(get_size_file(stock)) == True):\n",
    "        size_dict[stock].index = pd.to_datetime(size_dict[stock].index) \n",
    "        size_dict[stock].drop(drop_columns, inplace=True, axis=1)\n",
    "\n",
    "# changing mean reversion and size to be within 2015-01-01 and 2021-01-01\n",
    "start = '2015-01-01'\n",
    "end = '2021-01-01' # last trading day in 2020\n",
    "for stock in stocks:\n",
    "    if(exists(get_mean_reversion_file(stock)) == True):\n",
    "        mean_reversion_dict[stock] = mean_reversion_dict[stock].truncate(before=start, after=end)\n",
    "    if(exists(get_size_file(stock)) == True):\n",
    "        size_dict[stock] = size_dict[stock].truncate(before=start, after=end)\n",
    "\n",
    "    \n",
    "# changing risk data and equity/operating model data to have intersection of dates\n",
    "common_index = risk_dict['AAPL'].index.intersection(equity_operatingmodel_dict['AAPL'].index) # should work with any ticker\n",
    "for stock in stocks:\n",
    "    equity_operatingmodel_dict[stock] = equity_operatingmodel_dict[stock].loc[common_index]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# joining the dataframes\n",
    "for stock in stocks:\n",
    "    datasets = [risk_dict[stock], equity_operatingmodel_dict[stock]]\n",
    "    if(exists(get_mean_reversion_file(stock)) == True):\n",
    "        datasets.append(mean_reversion_dict[stock])\n",
    "    if(exists(get_size_file(stock)) == True):\n",
    "        datasets.append(size_dict[stock])\n",
    "    merged_dict[stock] = pd.concat(datasets, axis=1, ignore_index = False)\n",
    "\n",
    "# adding null columns if there is no size or no csv file\n",
    "mean_reversion_columns = ['STMR', 'momentum', 'ltmr']\n",
    "size_columns = ['Size', 'P/B Ratio']\n",
    "for stock in stocks:\n",
    "    if(exists(get_mean_reversion_file(stock)) == False):\n",
    "        for col in mean_reversion_columns:\n",
    "            merged_dict[stock][col] = np.nan\n",
    "    if(exists(get_size_file(stock)) == False):\n",
    "        for col in size_columns:\n",
    "            merged_dict[stock][col] = np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# adding sector classification\n",
    "sector_columns = {'Unnamed: 0':'Ticker', 'Sector':'Sector', 'Sector Index':'Sector Index'}\n",
    "sector_data = sector_data.rename(sector_columns, axis=1)\n",
    "sector_data.index = sector_data['Ticker']\n",
    "\n",
    "for stock in stocks:\n",
    "    merged_dict[stock]['Sector Index'] = sector_data['Sector Index'].loc[stock]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "csvs generated\n"
     ]
    }
   ],
   "source": [
    "# generating csv and storing them in \\merged_data\n",
    "for stock in stocks:\n",
    "    merged_dict[stock].to_csv('merged_data\\\\' + stock + 'data.csv')\n",
    "print('csvs generated')"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "1c5c1c3f122e29ccde6698060fcfd1b780606b8b96d1437ab2ea9396e20b7aef"
  },
  "kernelspec": {
   "display_name": "Python 3.9.8 64-bit",
   "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.9.8"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}