Sector Category.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "funds = pd.read_csv(\"/Users/akumaar/Downloads/13F_filtered.csv\")\n",
    "prices = pd.read_csv(\"/Users/akumaar/Downloads/prices_from_2013.csv\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "funds\n",
    "len(np.unique(funds['iCIK']))\n",
    "stock_pool = np.unique(funds['stock_id'])\n",
    "stock_pool_real = pd.DataFrame(stock_pool, columns = ['stock_id'])\n",
    "len(np.unique(funds['stock_id']))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Fund_stocks = np.unique(funds['stock_id'])\n",
    "len(np.unique(prices['stock_id']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#ETF Time Series\n",
    "prices['date'] = pd.to_datetime(prices.date).dt.tz_localize(None)\n",
    "prices.sort_values(by=['date'], inplace = True, ascending = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "XLF = prices.loc[prices['ticker'] == \"XLF\" ]\n",
    "XLY = prices.loc[prices['ticker'] == \"XLY\" ]\n",
    "XLK = prices.loc[prices['ticker'] == \"XLK\" ]\n",
    "XLB = prices.loc[prices['ticker'] == \"XLB\" ]\n",
    "XLI = prices.loc[prices['ticker'] == \"XLI\" ]\n",
    "XLE = prices.loc[prices['ticker'] == \"XLE\" ]\n",
    "XLU = prices.loc[prices['ticker'] == \"XLU\" ]\n",
    "XLV = prices.loc[prices['ticker'] == \"XLV\" ]\n",
    "XLP = prices.loc[prices['ticker'] == \"XLP\" ]\n",
    "VOX = prices.loc[prices['ticker'] == \"VOX\" ]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Etf_data = {'XLF':XLF['close'][:-1].values,\n",
    "           'XLY':XLY['close'][:-1].values,\n",
    "           'XLK':XLK['close'][:-1].values,\n",
    "           'XLB':XLB['close'][:-1].values,\n",
    "           'XLI':XLI['close'][:-1].values,\n",
    "           'XLE':XLE['close'][:-1].values,\n",
    "           'XLU':XLU['close'][:-1].values,\n",
    "           'XLV':XLV['close'][:-1].values,\n",
    "           'XLP':XLP['close'][:-1].values,\n",
    "           'VOX':VOX['close'].values,\n",
    "           'date':VOX['date'].values}\n",
    "\n",
    "Etf_df = pd.DataFrame(Etf_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "x = prices.loc[prices['stock_id'] == \"032511107\" ]\n",
    "#Etf_df['date'] = pd.to_datetime(Etf_df.date)\n",
    "#len(np.unique(prices['stock_id']))\n",
    "Etf_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "joined = pd.merge(x, Etf_df, on = \"date\")\n",
    "joined\n",
    "joined[joined.columns[8:]].corr()['close'][1:]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "prices = prices[prices['stock_id'].isin(Fund_stocks)]\n",
    "print(len(np.unique(prices['stock_id'])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#group prices data by unique stock id. \n",
    "#for every group, do the merge and correlation shit\n",
    "#calculates each stocks industry through a correlation script\n",
    "stock_group = prices.groupby(prices['stock_id'])\n",
    "stock_group.get_group('000307108')\n",
    "sector_categories = pd.DataFrame(columns = ['stock_id', 'sector'])\n",
    "Headings = [\"Financial\", \"Consumer Discretionary\", \"Technology\", \"Materials\", \"Industrial\",\n",
    "               \"Energy\", \"Utilities\", \"Health Care\", \"Consumer Staples\", \"Communications\"]\n",
    "\n",
    "ETF_List = [XLF, XLY, XLK, XLB, XLI, XLE, XLU, XLV, XLP, VOX]\n",
    "\n",
    "for i in stock_pool:\n",
    "    print(i)\n",
    "    try:\n",
    "        #stock = stock_group.get_group(str(i))['close']\n",
    "        stock = stock_group.get_group(str(i))\n",
    "        print(\"after get group\")\n",
    "        #joined = pd.merge(x, Etf_df, on = \"date\")\n",
    "        #sector = joined[joined.columns[8:]].corr()['close'][1:].idxmax()\n",
    "        correlations = []\n",
    "        for j in ETF_List:\n",
    "            joined = pd.merge(stock, j, on = \"date\")\n",
    "            correlation = np.corrcoef(joined['close_x'].values, joined['close_y'].values)\n",
    "            correlations.append(correlation[1][0])\n",
    "        sector_index = correlations.index(max(correlations))\n",
    "        print(Headings[sector_index])\n",
    "        sector_categories = sector_categories.append({'stock_id': i, 'sector':Headings[sector_index]}, ignore_index = True)\n",
    "    except KeyError:\n",
    "        sector_categories = sector_categories.append({'stock_id': i, 'sector':'NA'}, ignore_index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "x = stock_group.get_group('000307108')\n",
    "joined = pd.merge(x, VOX, on = \"date\")\n",
    "joined\n",
    "correlation = np.corrcoef(joined['close_x'].values, joined['close_y'].values)\n",
    "correlation[0][1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sector_categories['sector'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sector_categories.to_csv('/Users/akumaar/Downloads/sectors.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "jj = pd.merge(funds, sector_categories, on = 'stock_id')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##Generating Fund sectors by calculating stock sector holding proportions.\n",
    "groupp = mergedFINAL.groupby('iCIK')\n",
    "fund_sectors = pd.DataFrame(columns = ['fund', 'sector'])\n",
    "for name,group in groupp:\n",
    "    try:\n",
    "        print(name)\n",
    "        group = group[group.sector_x != 'NA']\n",
    "        sector = group.groupby('sector_x')['iMARKET_VALUE'].sum().idxmax()    \n",
    "        fund_sectors = fund_sectors.append({'fund': name, 'sector_x':sector}, ignore_index = True)\n",
    "    except ValueError as e:\n",
    "        print('value_error')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fund_sectors = fund_sectors.drop(['sector'], axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fund_sectors.to_csv('/Users/akumaar/Downloads/fund_sectors_new.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mergedF = pd.read_csv('/Users/akumaar/Downloads/merged.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mergedFINAL = pd.merge(mergedF, funds, on = 'stock_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mergedFINAL"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}