sp500_historical.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-11-16T14:29:50.731803Z",
     "start_time": "2019-11-16T14:29:50.715887Z"
    }
   },
   "source": [
    "# Lists of S&P 500 companies historical components\n",
    "\n",
    "1. Read 'S&P 500 Historical Components & Changes.csv' which contains historical S&P 500 index membership since 1996.  Each row contains 2 fields: date and tickers; tickers is a comma delimited string of the symbols for that specified date.  This file was provided as open source via download for the book \"Trading Evolved\" by Andreas F. Clenow.  \n",
    "2. Clean up the list by fixing up symbols, removing duplicates in each row, and sorting the symbols  \n",
    "3. Read in 'sp500_changes_since_2019.csv' which contains the changes to the index since 2019  \n",
    "4. Create new rows in the historical membership list for these changes  \n",
    "5. Compare the last row to current list of S&P 500 components on the wikipedia  \n",
    "6. A list of differences is generated.  If this list is NOT empty, then changes have been made to the index that should be included in 'sp500_changes_since_2019.csv'.  \n",
    "7. Write out date stamped 'S&P 500 Historical Components & Changes' csv file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-02-09T09:07:24.975185Z",
     "start_time": "2020-02-09T09:07:24.946519Z"
    }
   },
   "outputs": [],
   "source": [
    "# use future imports for python 3.x forward compatibility\n",
    "from __future__ import print_function\n",
    "from __future__ import division\n",
    "from __future__ import absolute_import"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-02-09T09:07:24.998197Z",
     "start_time": "2020-02-09T09:07:24.977789Z"
    }
   },
   "outputs": [],
   "source": [
    "# other imports\n",
    "from datetime import datetime\n",
    "import pandas as pd\n",
    "import os\n",
    "\n",
    "pd.options.mode.chained_assignment = None  # default='warn'\n",
    "pd.set_option('display.max_rows', 600)\n",
    "\n",
    "# -*- encoding: utf-8 -*-\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-02-09T09:07:25.017148Z",
     "start_time": "2020-02-09T09:07:24.999982Z"
    }
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "IPython.OutputArea.prototype._should_scroll = function(lines) {\n",
       "    return false;\n",
       "}"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%javascript\n",
    "IPython.OutputArea.prototype._should_scroll = function(lines) {\n",
    "    return false;\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-02-09T09:07:25.029241Z",
     "start_time": "2020-02-09T09:07:25.018791Z"
    }
   },
   "outputs": [],
   "source": [
    "def get_table(filename):\n",
    "\n",
    "    if os.path.isfile(filename):\n",
    "        df = pd.read_csv(filename, index_col='date')\n",
    "        return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-02-09T09:07:25.236554Z",
     "start_time": "2020-02-09T09:07:25.034676Z"
    },
    "scrolled": true
   },
   "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>tickers</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1996-01-02</th>\n",
       "      <td>TMC-200006,AAL-199702,AAMRQ-201312,AAPL,ABI-20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-03</th>\n",
       "      <td>AAL-199702,AAMRQ-201312,AAPL,ABI-200811,ABS-20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-04</th>\n",
       "      <td>TMC-200006,AAL-199702,AAMRQ-201312,AAPL,OAT-20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-10</th>\n",
       "      <td>TMC-200006,AAL-199702,AAMRQ-201312,AAPL,ABI-20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-11</th>\n",
       "      <td>FLMIQ-200408,FLTWQ-200907,FMC,FMCC,FNMA,FTL.A-...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                      tickers\n",
       "date                                                         \n",
       "1996-01-02  TMC-200006,AAL-199702,AAMRQ-201312,AAPL,ABI-20...\n",
       "1996-01-03  AAL-199702,AAMRQ-201312,AAPL,ABI-200811,ABS-20...\n",
       "1996-01-04  TMC-200006,AAL-199702,AAMRQ-201312,AAPL,OAT-20...\n",
       "1996-01-10  TMC-200006,AAL-199702,AAMRQ-201312,AAPL,ABI-20...\n",
       "1996-01-11  FLMIQ-200408,FLTWQ-200907,FMC,FMCC,FNMA,FTL.A-..."
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filename = 'S&P 500 Historical Components & Changes.csv'\n",
    "df = get_table(filename)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-02-09T09:07:25.451177Z",
     "start_time": "2020-02-09T09:07:25.246536Z"
    },
    "scrolled": true
   },
   "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>tickers</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-01-03</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-08</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-09</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-10</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-11</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                      tickers\n",
       "date                                                         \n",
       "2019-01-03  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-01-08  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-01-09  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-01-10  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-01-11  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,..."
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert ticker column from csv to list, then sort\n",
    "df['tickers'] = df['tickers'].apply(lambda x: sorted(x.split(',')))\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-02-09T09:07:25.887957Z",
     "start_time": "2020-02-09T09:07:25.455435Z"
    }
   },
   "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>tickers</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1996-01-02</th>\n",
       "      <td>[AAL, AAMRQ, AAPL, ABI, ABS, ABT, ABX, ACKH, A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-03</th>\n",
       "      <td>[AAL, AAMRQ, AAPL, ABI, ABS, ABT, ABX, ACKH, A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-04</th>\n",
       "      <td>[AAL, AAMRQ, AAPL, ABI, ABS, ABT, ABX, ACKH, A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-10</th>\n",
       "      <td>[AAL, AAMRQ, AAPL, ABI, ABS, ABT, ABX, ACKH, A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-11</th>\n",
       "      <td>[AAL, AAMRQ, AAPL, ABI, ABS, ABT, ABX, ACKH, A...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                      tickers\n",
       "date                                                         \n",
       "1996-01-02  [AAL, AAMRQ, AAPL, ABI, ABS, ABT, ABX, ACKH, A...\n",
       "1996-01-03  [AAL, AAMRQ, AAPL, ABI, ABS, ABT, ABX, ACKH, A...\n",
       "1996-01-04  [AAL, AAMRQ, AAPL, ABI, ABS, ABT, ABX, ACKH, A...\n",
       "1996-01-10  [AAL, AAMRQ, AAPL, ABI, ABS, ABT, ABX, ACKH, A...\n",
       "1996-01-11  [AAL, AAMRQ, AAPL, ABI, ABS, ABT, ABX, ACKH, A..."
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# replace SYMBOL-yyyymm with SYMBOL\n",
    "df['tickers'] = [[ticker.split('-')[0] for ticker in tickers] for tickers in df['tickers']]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-02-09T09:07:26.322723Z",
     "start_time": "2020-02-09T09:07:25.898395Z"
    },
    "scrolled": true
   },
   "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>tickers</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-01-03</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-08</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-09</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-10</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-11</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                      tickers\n",
       "date                                                         \n",
       "2019-01-03  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-01-08  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-01-09  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-01-10  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-01-11  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,..."
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# remove duplicates in each row\n",
    "df['tickers'] = [sorted(list(set(tickers))) for tickers in df['tickers']]\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Changes to the list of S&P 500 components\n",
    "https://en.wikipedia.org/wiki/List_of_S%26P_500_companies#Selected_changes_to_the_list_of_S&P_500_components"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-02-09T09:07:26.398185Z",
     "start_time": "2020-02-09T09:07:26.332468Z"
    }
   },
   "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>add</th>\n",
       "      <th>remove</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-10-31</th>\n",
       "      <td>[LIN]</td>\n",
       "      <td>[PX]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-18</th>\n",
       "      <td>[TFX]</td>\n",
       "      <td>[PCG]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-02-15</th>\n",
       "      <td>[ATO]</td>\n",
       "      <td>[NFX]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-02-27</th>\n",
       "      <td>[WAB]</td>\n",
       "      <td>[GT]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-04-02</th>\n",
       "      <td>[DOW]</td>\n",
       "      <td>[BHF]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-01</th>\n",
       "      <td>[LHX]</td>\n",
       "      <td>[HRS]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-03</th>\n",
       "      <td>[CTVA, DD]</td>\n",
       "      <td>[DWDP, FLR]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-07</th>\n",
       "      <td>[AMCR]</td>\n",
       "      <td>[MAT]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-01</th>\n",
       "      <td>[MKTX]</td>\n",
       "      <td>[LLL]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-15</th>\n",
       "      <td>[TMUS]</td>\n",
       "      <td>[RHT]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-08-08</th>\n",
       "      <td>[GL]</td>\n",
       "      <td>[TMK]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-08-09</th>\n",
       "      <td>[IEX, LDOS]</td>\n",
       "      <td>[APC, FL]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-09-23</th>\n",
       "      <td>[CDW]</td>\n",
       "      <td>[TSS]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-09-26</th>\n",
       "      <td>[NVR]</td>\n",
       "      <td>[JEF]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-03</th>\n",
       "      <td>[LVS]</td>\n",
       "      <td>[NKTR]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-18</th>\n",
       "      <td>[BKR]</td>\n",
       "      <td>[BHGE]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-11-05</th>\n",
       "      <td>[NLOK, PEAK]</td>\n",
       "      <td>[HCP, SYMC]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-11-21</th>\n",
       "      <td>[NOW]</td>\n",
       "      <td>[CELG]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-05</th>\n",
       "      <td>[VIAC, WRB]</td>\n",
       "      <td>[CBS, VIAB]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-09</th>\n",
       "      <td>[ODFL, TFC]</td>\n",
       "      <td>[BBT, STI]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-10</th>\n",
       "      <td>[J]</td>\n",
       "      <td>[JEC]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-23</th>\n",
       "      <td>[LYV, STE, ZBRA]</td>\n",
       "      <td>[AMG, MAC, TRIP]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-28</th>\n",
       "      <td>[PAYC]</td>\n",
       "      <td>[WCG]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         add            remove\n",
       "date                                          \n",
       "2018-10-31             [LIN]              [PX]\n",
       "2019-01-18             [TFX]             [PCG]\n",
       "2019-02-15             [ATO]             [NFX]\n",
       "2019-02-27             [WAB]              [GT]\n",
       "2019-04-02             [DOW]             [BHF]\n",
       "2019-06-01             [LHX]             [HRS]\n",
       "2019-06-03        [CTVA, DD]       [DWDP, FLR]\n",
       "2019-06-07            [AMCR]             [MAT]\n",
       "2019-07-01            [MKTX]             [LLL]\n",
       "2019-07-15            [TMUS]             [RHT]\n",
       "2019-08-08              [GL]             [TMK]\n",
       "2019-08-09       [IEX, LDOS]         [APC, FL]\n",
       "2019-09-23             [CDW]             [TSS]\n",
       "2019-09-26             [NVR]             [JEF]\n",
       "2019-10-03             [LVS]            [NKTR]\n",
       "2019-10-18             [BKR]            [BHGE]\n",
       "2019-11-05      [NLOK, PEAK]       [HCP, SYMC]\n",
       "2019-11-21             [NOW]            [CELG]\n",
       "2019-12-05       [VIAC, WRB]       [CBS, VIAB]\n",
       "2019-12-09       [ODFL, TFC]        [BBT, STI]\n",
       "2019-12-10               [J]             [JEC]\n",
       "2019-12-23  [LYV, STE, ZBRA]  [AMG, MAC, TRIP]\n",
       "2020-01-28            [PAYC]             [WCG]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read S&P 500 changes csv file\n",
    "filename = 'sp500_changes_since_2019.csv'\n",
    "changes = get_table(filename)\n",
    "\n",
    "# convert ticker column from csv to list, then sort\n",
    "changes['add'] = changes['add'].apply(lambda x: sorted(x.split(',')))\n",
    "changes['remove'] = changes['remove'].apply(lambda x: sorted(x.split(',')))\n",
    "changes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-02-09T09:07:26.520691Z",
     "start_time": "2020-02-09T09:07:26.406413Z"
    }
   },
   "outputs": [],
   "source": [
    "# copy the last row in dataframe, modify for changes, then append\n",
    "for change in changes.itertuples():\n",
    "\n",
    "    new_row = df.tail(1)\n",
    "    \n",
    "    tickers = list(new_row['tickers'][0])\n",
    "    tickers += change.add\n",
    "    tickers = list(set(tickers) - set(change.remove))\n",
    "    tickers = sorted(tickers)\n",
    "    \n",
    "    d = {'date':change.Index, 'tickers':[tickers]}\n",
    "    new_entry = pd.DataFrame(d)\n",
    "    new_entry.set_index('date', inplace=True)\n",
    "    df = df.append(new_entry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-02-09T09:07:26.565165Z",
     "start_time": "2020-02-09T09:07:26.532515Z"
    },
    "scrolled": true
   },
   "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>tickers</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-12-05</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-09</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-10</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-23</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-28</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                      tickers\n",
       "date                                                         \n",
       "2019-12-05  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-12-09  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-12-10  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-12-23  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2020-01-28  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,..."
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-02-09T09:07:26.605571Z",
     "start_time": "2020-02-09T09:07:26.574325Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# compare last row to current S&P500 list\n",
    "filename = 'sp500.csv'\n",
    "current = pd.read_csv(filename)\n",
    "current = list(current['Symbol'])\n",
    "last_entry = list(df['tickers'][-1])\n",
    "\n",
    "diff = list(set(current) - set(last_entry)) + list(set(last_entry) - set(current))\n",
    "diff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-02-09T09:07:26.739716Z",
     "start_time": "2020-02-09T09:07:26.610241Z"
    },
    "scrolled": true
   },
   "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>tickers</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1996-01-02</th>\n",
       "      <td>AAL,AAMRQ,AAPL,ABI,ABS,ABT,ABX,ACKH,ACV,ADM,AD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-03</th>\n",
       "      <td>AAL,AAMRQ,AAPL,ABI,ABS,ABT,ABX,ACKH,ACV,ADM,AD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-04</th>\n",
       "      <td>AAL,AAMRQ,AAPL,ABI,ABS,ABT,ABX,ACKH,ACV,ADM,AD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-10</th>\n",
       "      <td>AAL,AAMRQ,AAPL,ABI,ABS,ABT,ABX,ACKH,ACV,ADM,AD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-11</th>\n",
       "      <td>AAL,AAMRQ,AAPL,ABI,ABS,ABT,ABX,ACKH,ACV,ADM,AD...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                      tickers\n",
       "date                                                         \n",
       "1996-01-02  AAL,AAMRQ,AAPL,ABI,ABS,ABT,ABX,ACKH,ACV,ADM,AD...\n",
       "1996-01-03  AAL,AAMRQ,AAPL,ABI,ABS,ABT,ABX,ACKH,ACV,ADM,AD...\n",
       "1996-01-04  AAL,AAMRQ,AAPL,ABI,ABS,ABT,ABX,ACKH,ACV,ADM,AD...\n",
       "1996-01-10  AAL,AAMRQ,AAPL,ABI,ABS,ABT,ABX,ACKH,ACV,ADM,AD...\n",
       "1996-01-11  AAL,AAMRQ,AAPL,ABI,ABS,ABT,ABX,ACKH,ACV,ADM,AD..."
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert tickers column back to csv\n",
    "df['tickers'] = df['tickers'].apply(lambda x: \",\".join(x))\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-02-09T09:07:26.898227Z",
     "start_time": "2020-02-09T09:07:26.748001Z"
    }
   },
   "outputs": [],
   "source": [
    "now = datetime.now()\n",
    "dt_string = now.strftime('%m-%d-%Y') # mm-dd-YYYY\n",
    "filename = 'S&P 500 Historical Components & Changes({}).csv'.format(dt_string)\n",
    "df.to_csv(filename)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.15+"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": false,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}