data_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# S&P 2019 Data Wrangling\n",
    "\n",
    "**by Marc Angelo Acebedo**\n",
    "\n",
    "## Table of Contents\n",
    "- [Introduction](#intro)\n",
    "- [Data Wrangling](#wrangling)\n",
    "    - [Gather](#gather)\n",
    "    - [Assess](#assess)\n",
    "    - [Clean](#clean)\n",
    "- [Store](#store)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## I) Introduction <a id = \"intro\"></a>\n",
    "\n",
    "**Broad question:** How do price forecasts for each firm in the S&P 2019 Index compare to their corresponding actual prices?\n",
    "\n",
    "**Approach:** Analyze difference in means between average forecast EPS and average actual EPS for each firm.\n",
    "\n",
    "I will be analyzing quarterly price returns within the past 20 years for the firms present in the S&P 500 2019 Index.\n",
    "\n",
    "> At first, I wanted to analyze the forecasted vs. actual price earnings of the S&P in its entirety for the past 20 years. However, considering that firms continuously enter and leave stock indices every year, there would be varying levels of inconsistencies and marginal errors when comparing annual S&P returns alone. To combat this problem, I have isolated these two approaches:\n",
    "- Analyze the historical earnings of *only* the firms present in the S&P 2019 Index\n",
    "- Keep track of all firms that were present in the S&P for the past 20 years. Keep track of how many times each firm appeared in the Index and for those with the least count, analyze them individually on how they differ from the firms that stayed for longer.\n",
    "\n",
    "Here is a breakdown of my final clean CSVs' features, `features.csv` and `avgs.csv`.\n",
    "\n",
    "### `features.csv`\n",
    "\n",
    "- **firm_id**\n",
    "- **feature**\n",
    "- **date**\n",
    "- **term**\n",
    "- **value**\n",
    "\n",
    "### `avgs.csv`\n",
    "\n",
    "- **firm_id**\n",
    "- **average**\n",
    "- **average_type**\n",
    "- **time_period**\n",
    "- **feature**\n",
    "\n",
    "> The **firm_id** values refer to their corresponding foreign keys in `firms.csv`. Further explanation of each column can be found in the `data_exploratory.ipynb` file.\n",
    "\n",
    "## II) Data Wrangling <a id=\"wrangling\"></a>\n",
    "\n",
    "To gather the data depicted under the `./data` folder, I used Bloomberg Excel functions.\n",
    "\n",
    "### A) Gather <a id = \"gather\"></a>\n",
    "> **APPROACH 1:** Focus on the firms that appear in the 2019 S&P Index and analyze their forecasted vs. actual price earnings for the last 20 years.\n",
    "\n",
    "To ensure consistency in analysis among multiple firms, I divide both the forecasted and actual EPS terms by *fiscal period* instead of calendar period. On the other hand, EOD Price and Forecasted EPS (3 months prior) have both been recorded by *calendar period*, so this is a discrepancy we must be aware of in our analysis. \n",
    "\n",
    "#### Through the Bloomberg Excel functions, I gathered four datasets with different purposes:\n",
    "\n",
    "- historical forecasted EPS\n",
    "- historical actual EPS\n",
    "- historical actual EOD price\n",
    "- historical forecasted EPS relying on terms\n",
    "\n",
    "---\n",
    "Before delving into the data, let's define what above terms:\n",
    "\n",
    "**EPS**\n",
    "\n",
    "> EPS stands for ***Earnings per Share.*** The formal definition of EPS given by Investopedia is this:\n",
    "\n",
    "Earnings per share is the portion of a company's profit that is allocated to each outstanding share of a common stock, serving as an indicator of the company's financial health.\n",
    "\n",
    "In other words, the EPS is a portion of the company's **net income** after all of their dividends are paid off. Dividends are profits that are paid out to shareholders of the company. EPS is one of the most useful and valuable financial measurements because they ***determine a stock's worth.*** The higher the stock, the more the company can pay out dividends to its shareholders, and the more net profit they are determined to generate.\n",
    "\n",
    "$$ EPS = \\frac{Net Income - Preferred Dividends}{Weighted Average Common Shares Outstanding}\\$$\n",
    "\n",
    "**EOD**\n",
    "\n",
    "> EOD stands for the ***End of Day*** price. For any given day, the EOD marks the ***price at which the stock was valued*** at the end of the day's trading period."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "PATH = './data/'\n",
    "PATH_CLEAN = './data/clean/'\n",
    "PATH_CLEAN_AVGS = './data/clean/averages/'\n",
    "PATH_CLEAN_AVGS_SUB = './data/clean/averages/components/'\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sb\n",
    "\n",
    "from functools import reduce"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import Data <a id = \"import\"></a>\n",
    "\n",
    "### Let's summarize the contents of the following DataFrames as we import them.\n",
    "> All DataFrames consist of the 505 firms found in the 2019 S&P Index with EPS and EOD data encompassing 20 years: from January 1999 until December 2019.\n",
    "\n",
    "**Historic forecasted EPS**\n",
    "> According to Investopedia, consensus estimates is normally an average or median of all the forecasts from individual analysts tracking a particular stock. In this case, the consensus estimate is for ***EPS for each firm present in the index as of 2019.*** Forecasted EPS is calculated by ***quarterly earnings,*** usually by each firm's fiscal period. Estimates of quarterly earnings are published at the beginning of each quarterly period."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#historic forecasted EPS \n",
    "eps_fc = pd.read_csv(PATH + 'sp-eps-fc.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>Term Forecasted</th>\n",
       "      <th>A UN Equity</th>\n",
       "      <th>AAL UW Equity</th>\n",
       "      <th>AAP UN Equity</th>\n",
       "      <th>AAPL UW Equity</th>\n",
       "      <th>ABBV UN Equity</th>\n",
       "      <th>ABC UN Equity</th>\n",
       "      <th>ABMD UW Equity</th>\n",
       "      <th>ABT UN Equity</th>\n",
       "      <th>ACN UN Equity</th>\n",
       "      <th>...</th>\n",
       "      <th>XEL UW Equity</th>\n",
       "      <th>XLNX UW Equity</th>\n",
       "      <th>XOM UN Equity</th>\n",
       "      <th>XRAY UW Equity</th>\n",
       "      <th>XRX UN Equity</th>\n",
       "      <th>XYL UN Equity</th>\n",
       "      <th>YUM UN Equity</th>\n",
       "      <th>ZBH UN Equity</th>\n",
       "      <th>ZION UW Equity</th>\n",
       "      <th>ZTS UN Equity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1999Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.025</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.10</td>\n",
       "      <td>0.423</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.456</td>\n",
       "      <td>0.090</td>\n",
       "      <td>0.239</td>\n",
       "      <td>0.123</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.123</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.622</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1999Q2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.13</td>\n",
       "      <td>0.420</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.213</td>\n",
       "      <td>0.090</td>\n",
       "      <td>0.264</td>\n",
       "      <td>0.135</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.150</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.661</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1999Q3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.11</td>\n",
       "      <td>0.381</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.767</td>\n",
       "      <td>0.099</td>\n",
       "      <td>0.300</td>\n",
       "      <td>0.129</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.171</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.693</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1999Q4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.432</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.432</td>\n",
       "      <td>0.124</td>\n",
       "      <td>0.386</td>\n",
       "      <td>0.178</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.164</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.667</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.441</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.283</td>\n",
       "      <td>0.143</td>\n",
       "      <td>0.436</td>\n",
       "      <td>0.138</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.138</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.668</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 506 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Term Forecasted  A UN Equity  AAL UW Equity  AAP UN Equity  AAPL UW Equity  \\\n",
       "0          1999Q1          NaN            NaN            NaN           0.025   \n",
       "1          1999Q2          NaN            NaN            NaN           0.020   \n",
       "2          1999Q3          NaN            NaN            NaN           0.023   \n",
       "3          1999Q4          NaN            NaN            NaN           0.016   \n",
       "4            00Q1          NaN            NaN            NaN           0.032   \n",
       "\n",
       "   ABBV UN Equity  ABC UN Equity  ABMD UW Equity  ABT UN Equity  \\\n",
       "0             NaN            NaN           -0.10          0.423   \n",
       "1             NaN            NaN           -0.13          0.420   \n",
       "2             NaN            NaN           -0.11          0.381   \n",
       "3             NaN            NaN             NaN          0.432   \n",
       "4             NaN            NaN             NaN          0.441   \n",
       "\n",
       "   ACN UN Equity  ...  XEL UW Equity  XLNX UW Equity  XOM UN Equity  \\\n",
       "0            NaN  ...          0.456           0.090          0.239   \n",
       "1            NaN  ...          0.213           0.090          0.264   \n",
       "2            NaN  ...          0.767           0.099          0.300   \n",
       "3            NaN  ...          0.432           0.124          0.386   \n",
       "4            NaN  ...          0.283           0.143          0.436   \n",
       "\n",
       "   XRAY UW Equity  XRX UN Equity  XYL UN Equity  YUM UN Equity  ZBH UN Equity  \\\n",
       "0           0.123            NaN            NaN          0.123            NaN   \n",
       "1           0.135            NaN            NaN          0.150            NaN   \n",
       "2           0.129            NaN            NaN          0.171            NaN   \n",
       "3           0.178            NaN            NaN          0.164            NaN   \n",
       "4           0.138            NaN            NaN          0.138            NaN   \n",
       "\n",
       "   ZION UW Equity  ZTS UN Equity  \n",
       "0           0.622            NaN  \n",
       "1           0.661            NaN  \n",
       "2           0.693            NaN  \n",
       "3           0.667            NaN  \n",
       "4           0.668            NaN  \n",
       "\n",
       "[5 rows x 506 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eps_fc.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Historic actual EPS**\n",
    "> Unlike forecasted EPS, actual EPS are the real numbers denoting Earnings-per-Share for a singular firm. Historic actual EPS will be compared to forecasted EPS to draw correlations and comparisons."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#historic actual EPS\n",
    "eps_act = pd.read_csv(PATH + 'sp-eps-act.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>Quarter</th>\n",
       "      <th>Year</th>\n",
       "      <th>A UN Equity</th>\n",
       "      <th>AAL UW Equity</th>\n",
       "      <th>AAP UN Equity</th>\n",
       "      <th>AAPL UW Equity</th>\n",
       "      <th>ABBV UN Equity</th>\n",
       "      <th>ABC UN Equity</th>\n",
       "      <th>ABMD UW Equity</th>\n",
       "      <th>ABT UN Equity</th>\n",
       "      <th>...</th>\n",
       "      <th>XEL UW Equity</th>\n",
       "      <th>XLNX UW Equity</th>\n",
       "      <th>XOM UN Equity</th>\n",
       "      <th>XRAY UW Equity</th>\n",
       "      <th>XRX UN Equity</th>\n",
       "      <th>XYL UN Equity</th>\n",
       "      <th>YUM UN Equity</th>\n",
       "      <th>ZBH UN Equity</th>\n",
       "      <th>ZION UW Equity</th>\n",
       "      <th>ZTS UN Equity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Q1</td>\n",
       "      <td>1999</td>\n",
       "      <td>0.16</td>\n",
       "      <td>0.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.040000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0925</td>\n",
       "      <td>-0.030</td>\n",
       "      <td>0.44</td>\n",
       "      <td>...</td>\n",
       "      <td>0.34</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.210</td>\n",
       "      <td>0.123333</td>\n",
       "      <td>1.64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.1725</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.56</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Q2</td>\n",
       "      <td>1999</td>\n",
       "      <td>0.35</td>\n",
       "      <td>1.76</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.035357</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.1000</td>\n",
       "      <td>-0.145</td>\n",
       "      <td>0.42</td>\n",
       "      <td>...</td>\n",
       "      <td>0.06</td>\n",
       "      <td>0.0975</td>\n",
       "      <td>0.285</td>\n",
       "      <td>0.133333</td>\n",
       "      <td>2.28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.2900</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.60</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Q3</td>\n",
       "      <td>1999</td>\n",
       "      <td>0.30</td>\n",
       "      <td>1.86</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.050357</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.1075</td>\n",
       "      <td>-0.135</td>\n",
       "      <td>0.30</td>\n",
       "      <td>...</td>\n",
       "      <td>0.63</td>\n",
       "      <td>0.0325</td>\n",
       "      <td>0.315</td>\n",
       "      <td>0.130000</td>\n",
       "      <td>1.96</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.3200</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.64</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Q4</td>\n",
       "      <td>1999</td>\n",
       "      <td>0.32</td>\n",
       "      <td>1.89</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.024643</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0375</td>\n",
       "      <td>-0.080</td>\n",
       "      <td>0.43</td>\n",
       "      <td>...</td>\n",
       "      <td>0.43</td>\n",
       "      <td>0.1300</td>\n",
       "      <td>0.600</td>\n",
       "      <td>0.180000</td>\n",
       "      <td>1.72</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.2375</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.49</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Q1</td>\n",
       "      <td>2000</td>\n",
       "      <td>0.29</td>\n",
       "      <td>0.89</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.040585</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.1050</td>\n",
       "      <td>-0.045</td>\n",
       "      <td>0.45</td>\n",
       "      <td>...</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0.1650</td>\n",
       "      <td>0.500</td>\n",
       "      <td>0.140000</td>\n",
       "      <td>-1.48</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.2025</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.33</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 507 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Quarter  Year  A UN Equity  AAL UW Equity  AAP UN Equity  AAPL UW Equity  \\\n",
       "0      Q1  1999         0.16           0.99            NaN        0.040000   \n",
       "1      Q2  1999         0.35           1.76            NaN        0.035357   \n",
       "2      Q3  1999         0.30           1.86            NaN        0.050357   \n",
       "3      Q4  1999         0.32           1.89            NaN        0.024643   \n",
       "4      Q1  2000         0.29           0.89            NaN        0.040585   \n",
       "\n",
       "   ABBV UN Equity  ABC UN Equity  ABMD UW Equity  ABT UN Equity  ...  \\\n",
       "0             NaN         0.0925          -0.030           0.44  ...   \n",
       "1             NaN         0.1000          -0.145           0.42  ...   \n",
       "2             NaN         0.1075          -0.135           0.30  ...   \n",
       "3             NaN         0.0375          -0.080           0.43  ...   \n",
       "4             NaN         0.1050          -0.045           0.45  ...   \n",
       "\n",
       "   XEL UW Equity  XLNX UW Equity  XOM UN Equity  XRAY UW Equity  \\\n",
       "0           0.34          0.0000          0.210        0.123333   \n",
       "1           0.06          0.0975          0.285        0.133333   \n",
       "2           0.63          0.0325          0.315        0.130000   \n",
       "3           0.43          0.1300          0.600        0.180000   \n",
       "4           0.45          0.1650          0.500        0.140000   \n",
       "\n",
       "   XRX UN Equity  XYL UN Equity  YUM UN Equity  ZBH UN Equity  ZION UW Equity  \\\n",
       "0           1.64            NaN         0.1725            NaN            0.56   \n",
       "1           2.28            NaN         0.2900            NaN            0.60   \n",
       "2           1.96            NaN         0.3200            NaN            0.64   \n",
       "3           1.72            NaN         0.2375            NaN            0.49   \n",
       "4          -1.48            NaN         0.2025            NaN           -0.33   \n",
       "\n",
       "   ZTS UN Equity  \n",
       "0            NaN  \n",
       "1            NaN  \n",
       "2            NaN  \n",
       "3            NaN  \n",
       "4            NaN  \n",
       "\n",
       "[5 rows x 507 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eps_act.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Historic actual EOD**\n",
    "> Though this is not directly related to EPS data, EOD would be an interesting measure to use when generating intriguing visualizations and analyses. Who knows what visuals and conclusions I would arrive to with this measure. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "#historic actual EOD\n",
    "eod_act = pd.read_csv(PATH + 'sp-eod-act.csv')"
   ]
  },
  {
   "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>date</th>\n",
       "      <th>A UN Equity</th>\n",
       "      <th>AAL UW Equity</th>\n",
       "      <th>AAP UN Equity</th>\n",
       "      <th>AAPL UW Equity</th>\n",
       "      <th>ABBV UN Equity</th>\n",
       "      <th>ABC UN Equity</th>\n",
       "      <th>ABMD UW Equity</th>\n",
       "      <th>ABT UN Equity</th>\n",
       "      <th>ACN UN Equity</th>\n",
       "      <th>...</th>\n",
       "      <th>XEL UW Equity</th>\n",
       "      <th>XLNX UW Equity</th>\n",
       "      <th>XOM UN Equity</th>\n",
       "      <th>XRAY UW Equity</th>\n",
       "      <th>XRX UN Equity</th>\n",
       "      <th>XYL UN Equity</th>\n",
       "      <th>YUM UN Equity</th>\n",
       "      <th>ZBH UN Equity</th>\n",
       "      <th>ZION UW Equity</th>\n",
       "      <th>ZTS UN Equity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3/31/1999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.2835</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.2934</td>\n",
       "      <td>6.250</td>\n",
       "      <td>20.9504</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.2813</td>\n",
       "      <td>35.2813</td>\n",
       "      <td>7.7500</td>\n",
       "      <td>140.6214</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.6284</td>\n",
       "      <td>NaN</td>\n",
       "      <td>66.5000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6/30/1999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.6540</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.1859</td>\n",
       "      <td>6.875</td>\n",
       "      <td>20.3630</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.6250</td>\n",
       "      <td>38.5625</td>\n",
       "      <td>9.6250</td>\n",
       "      <td>155.6057</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.7297</td>\n",
       "      <td>NaN</td>\n",
       "      <td>63.5000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9/30/1999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.2612</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.7462</td>\n",
       "      <td>7.750</td>\n",
       "      <td>16.4471</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32.7656</td>\n",
       "      <td>37.9688</td>\n",
       "      <td>7.5833</td>\n",
       "      <td>110.4883</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.3591</td>\n",
       "      <td>NaN</td>\n",
       "      <td>55.1250</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12/31/1999</td>\n",
       "      <td>52.0909</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.6719</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.6843</td>\n",
       "      <td>18.375</td>\n",
       "      <td>16.2513</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45.4688</td>\n",
       "      <td>40.2813</td>\n",
       "      <td>7.8750</td>\n",
       "      <td>59.7723</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.9434</td>\n",
       "      <td>NaN</td>\n",
       "      <td>59.1875</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3/31/2000</td>\n",
       "      <td>70.0721</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.8504</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.6388</td>\n",
       "      <td>20.250</td>\n",
       "      <td>15.7478</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>82.8125</td>\n",
       "      <td>38.9063</td>\n",
       "      <td>9.4583</td>\n",
       "      <td>68.4994</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.5839</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41.6250</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 506 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  A UN Equity  AAL UW Equity  AAP UN Equity  AAPL UW Equity  \\\n",
       "0   3/31/1999          NaN            NaN            NaN          1.2835   \n",
       "1   6/30/1999          NaN            NaN            NaN          1.6540   \n",
       "2   9/30/1999          NaN            NaN            NaN          2.2612   \n",
       "3  12/31/1999      52.0909            NaN            NaN          3.6719   \n",
       "4   3/31/2000      70.0721            NaN            NaN          4.8504   \n",
       "\n",
       "   ABBV UN Equity  ABC UN Equity  ABMD UW Equity  ABT UN Equity  \\\n",
       "0             NaN         8.2934           6.250        20.9504   \n",
       "1             NaN         6.1859           6.875        20.3630   \n",
       "2             NaN         5.7462           7.750        16.4471   \n",
       "3             NaN         3.6843          18.375        16.2513   \n",
       "4             NaN         3.6388          20.250        15.7478   \n",
       "\n",
       "   ACN UN Equity  ...  XEL UW Equity  XLNX UW Equity  XOM UN Equity  \\\n",
       "0            NaN  ...            NaN         20.2813        35.2813   \n",
       "1            NaN  ...            NaN         28.6250        38.5625   \n",
       "2            NaN  ...            NaN         32.7656        37.9688   \n",
       "3            NaN  ...            NaN         45.4688        40.2813   \n",
       "4            NaN  ...            NaN         82.8125        38.9063   \n",
       "\n",
       "   XRAY UW Equity  XRX UN Equity  XYL UN Equity  YUM UN Equity  ZBH UN Equity  \\\n",
       "0          7.7500       140.6214            NaN        12.6284            NaN   \n",
       "1          9.6250       155.6057            NaN         9.7297            NaN   \n",
       "2          7.5833       110.4883            NaN         7.3591            NaN   \n",
       "3          7.8750        59.7723            NaN         6.9434            NaN   \n",
       "4          9.4583        68.4994            NaN         5.5839            NaN   \n",
       "\n",
       "   ZION UW Equity  ZTS UN Equity  \n",
       "0         66.5000            NaN  \n",
       "1         63.5000            NaN  \n",
       "2         55.1250            NaN  \n",
       "3         59.1875            NaN  \n",
       "4         41.6250            NaN  \n",
       "\n",
       "[5 rows x 506 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eod_act.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Historic forecasted EPS 3 months prior**\n",
    "> Instead of using forecast data collected at the beginning of the fiscal period, this feature contains EPS data projected 3 months before the current fiscal period. This is an interesting metric to see how differently forecasters make their predictions at different times."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#historic forecasted EPS 3-months prior\n",
    "eps_fc_terms = pd.read_csv(PATH + 'sp-eps-fc-terms.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>Forecast Made</th>\n",
       "      <th>Term Forecasted</th>\n",
       "      <th>A UN Equity</th>\n",
       "      <th>AAL UW Equity</th>\n",
       "      <th>AAP UN Equity</th>\n",
       "      <th>AAPL UW Equity</th>\n",
       "      <th>ABBV UN Equity</th>\n",
       "      <th>ABC UN Equity</th>\n",
       "      <th>ABMD UW Equity</th>\n",
       "      <th>ABT UN Equity</th>\n",
       "      <th>...</th>\n",
       "      <th>XEL UW Equity</th>\n",
       "      <th>XLNX UW Equity</th>\n",
       "      <th>XOM UN Equity</th>\n",
       "      <th>XRAY UW Equity</th>\n",
       "      <th>XRX UN Equity</th>\n",
       "      <th>XYL UN Equity</th>\n",
       "      <th>YUM UN Equity</th>\n",
       "      <th>ZBH UN Equity</th>\n",
       "      <th>ZION UW Equity</th>\n",
       "      <th>ZTS UN Equity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10/1/1999</td>\n",
       "      <td>00Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.030</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.466</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.143</td>\n",
       "      <td>0.398</td>\n",
       "      <td>0.140</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.144</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.777</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1/1/2000</td>\n",
       "      <td>00Q2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.026</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.437</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.168</td>\n",
       "      <td>0.378</td>\n",
       "      <td>0.153</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.184</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.790</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4/1/2000</td>\n",
       "      <td>00Q3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.029</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.414</td>\n",
       "      <td>...</td>\n",
       "      <td>0.84</td>\n",
       "      <td>0.189</td>\n",
       "      <td>0.425</td>\n",
       "      <td>0.149</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.218</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.803</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7/1/2000</td>\n",
       "      <td>00Q4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.18</td>\n",
       "      <td>0.477</td>\n",
       "      <td>...</td>\n",
       "      <td>0.53</td>\n",
       "      <td>0.211</td>\n",
       "      <td>0.487</td>\n",
       "      <td>0.200</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.244</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.777</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9/1/2000</td>\n",
       "      <td>01Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.041</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.09</td>\n",
       "      <td>0.495</td>\n",
       "      <td>...</td>\n",
       "      <td>0.40</td>\n",
       "      <td>0.259</td>\n",
       "      <td>0.475</td>\n",
       "      <td>0.158</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.178</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.750</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 507 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Forecast Made Term Forecasted  A UN Equity  AAL UW Equity  AAP UN Equity  \\\n",
       "0     10/1/1999            00Q1          NaN            NaN            NaN   \n",
       "1      1/1/2000            00Q2          NaN            NaN            NaN   \n",
       "2      4/1/2000            00Q3          NaN            NaN            NaN   \n",
       "3      7/1/2000            00Q4          NaN            NaN            NaN   \n",
       "4      9/1/2000            01Q1          NaN            NaN            NaN   \n",
       "\n",
       "   AAPL UW Equity  ABBV UN Equity  ABC UN Equity  ABMD UW Equity  \\\n",
       "0           0.030             NaN            NaN             NaN   \n",
       "1           0.026             NaN            NaN             NaN   \n",
       "2           0.029             NaN            NaN             NaN   \n",
       "3           0.032             NaN            NaN           -0.18   \n",
       "4           0.041             NaN            NaN           -0.09   \n",
       "\n",
       "   ABT UN Equity  ...  XEL UW Equity  XLNX UW Equity  XOM UN Equity  \\\n",
       "0          0.466  ...            NaN           0.143          0.398   \n",
       "1          0.437  ...            NaN           0.168          0.378   \n",
       "2          0.414  ...           0.84           0.189          0.425   \n",
       "3          0.477  ...           0.53           0.211          0.487   \n",
       "4          0.495  ...           0.40           0.259          0.475   \n",
       "\n",
       "   XRAY UW Equity  XRX UN Equity  XYL UN Equity  YUM UN Equity  ZBH UN Equity  \\\n",
       "0           0.140            NaN            NaN          0.144            NaN   \n",
       "1           0.153            NaN            NaN          0.184            NaN   \n",
       "2           0.149            NaN            NaN          0.218            NaN   \n",
       "3           0.200            NaN            NaN          0.244            NaN   \n",
       "4           0.158            NaN            NaN          0.178            NaN   \n",
       "\n",
       "   ZION UW Equity  ZTS UN Equity  \n",
       "0           0.777            NaN  \n",
       "1           0.790            NaN  \n",
       "2           0.803            NaN  \n",
       "3           0.777            NaN  \n",
       "4           0.750            NaN  \n",
       "\n",
       "[5 rows x 507 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eps_fc_terms.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(80, 507)"
      ]
     },
     "execution_count": 216,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eps_fc_terms.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### B) Assess <a id = \"assess\"></a>\n",
    "\n",
    "> The following DataFrames contain data for each firm across various dates. To account for all firm averages, my goal is to generate a CSV file where each row contains the firm average, firm type, and divide them into yearly, quarterly, and twenty-year averages."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "dict_dfs = {'eps_fc' : eps_fc,\n",
    "           'eps_actual' : eps_act,\n",
    "           'eod_actual' : eod_act,\n",
    "           'eps_fc_terms' : eps_fc_terms}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check Missing Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "eps_fc (84, 506)\n",
      "eps_actual (84, 507)\n",
      "eod_actual (84, 506)\n",
      "eps_fc_terms (80, 507)\n"
     ]
    }
   ],
   "source": [
    "for key, df in dict_dfs.items():\n",
    "    print(key, df.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***We need to make sure the number of firms in each DataFrame is consistent.***\n",
    "\n",
    "---\n",
    "\n",
    "**Observation 1:** for `eps_fcast`, there are 505 firms encompassing 84 quarterly fiscal periods since 1999.\n",
    "\n",
    "> There are 506 columns: 1 column being `Term Forecast`, the rest firm names.\n",
    "\n",
    "**Observation 2:** for `eps_actual`, there are 505 firms encompassing 84 quarterly fiscal periods since 1999.\n",
    "\n",
    "> There are 507 columns: 2 columns being `Quarter` and `Year`, the rest firm names.\n",
    "\n",
    "**Observation 3:** for `eod_actual`, there are 505 firms encompassing 84 quarterly calendar periods since 1999.\n",
    "\n",
    "> There are 506 columns: 1 column being `date`, the rest firm names.\n",
    "\n",
    "**Observation 4:** for `eps_fcast_terms`, there are 505 firms encompassing 80 quarterly calendar periods since 1999.\n",
    "\n",
    "> There are 507 columns: 2 columns being `Forecast Made` and `Term Forecasted`, the rest firm names.\n",
    " \n",
    "Since there are only 80 quarterly calendar periods, that ***implies an entire year is missing.***\n",
    "\n",
    "**Observation 5:** For `eps_fcast`, `eps_actual`, and `eod_actual`, since there are 4 quarters in a year, 84 quarterly forecast periods equate to 21 years. This is correct since we are analyzing the years from 1999 until the end of 2019.\n",
    "\n",
    "### Most importantly, the number of firms across all DataFrames is consistent.\n",
    "\n",
    "----\n",
    "\n",
    "***Check which year is missing from `eps_fcast_terms`***.\n",
    "\n",
    "> Each **Term Forecasted** entry under `eps_fcast_terms` records the year with 2 digits, so Quarter 1 of the year 2000 becomes 00Q1.\n",
    "- isolate the first 2 characters to get the year\n",
    "- join a '20' in front of the string so 00 becomes 2000\n",
    "- list the number of unique values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2000',\n",
       " '2001',\n",
       " '2002',\n",
       " '2003',\n",
       " '2004',\n",
       " '2005',\n",
       " '2006',\n",
       " '2007',\n",
       " '2008',\n",
       " '2009',\n",
       " '2010',\n",
       " '2011',\n",
       " '2012',\n",
       " '2013',\n",
       " '2014',\n",
       " '2015',\n",
       " '2016',\n",
       " '2017',\n",
       " '2018',\n",
       " '2019']"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#iterate over all years in eps_fcast_terms, append '20' in front of string\n",
    "list(map(lambda x: '20' + x, eps_fc_terms['Term Forecasted'].str[:2].unique()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation**: The year 1999 is missing from `eps_fc_terms`. This makes sense because the start of the forecasting period would be in the last quarter of 1999, which is October. \n",
    "\n",
    "---\n",
    "\n",
    "### Check Nulls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# NaN in eps_fc: 7055\n",
      "# NaN in eps_actual: 5021\n",
      "# NaN in eod_actual: 6921\n",
      "# NaN in eps_fc_terms: 7080\n"
     ]
    }
   ],
   "source": [
    "for key, df in dict_dfs.items():\n",
    "    #display (row, column) per DataFrame\n",
    "    print('# NaN in {}: {}'.format(key, df.isna().sum().sum()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation:** All four DataSets contain null values.\n",
    "> In order to combat this, we'll have to look at both the **number of rows** and **number of columns** with missing data, separately. This way, we can isolate which firms and/or time periods contain complete or incomplete data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "eps_fc has 82 time periods containing missing data, out of 84 total rows.\n",
      "eps_actual has 84 time periods containing missing data, out of 84 total rows.\n",
      "eod_actual has 83 time periods containing missing data, out of 84 total rows.\n",
      "eps_fc_terms has 80 time periods containing missing data, out of 80 total rows.\n"
     ]
    }
   ],
   "source": [
    "#check rows for missing data\n",
    "for key, df in dict_dfs.items():\n",
    "    num_rows_missing = df.isna().sum().max()\n",
    "    print('{} has {} time periods containing missing data, out of {} total rows.'.format(key, num_rows_missing, df.shape[0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "254"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(eps_fc.columns[eps_fc.isnull().any()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "eps_fc has 254 firms containing missing data, out of 506 total columns.\n",
      "eps_actual has 432 firms containing missing data, out of 507 total columns.\n",
      "eod_actual has 505 firms containing missing data, out of 506 total columns.\n",
      "eps_fc_terms has 340 firms containing missing data, out of 507 total columns.\n"
     ]
    }
   ],
   "source": [
    "#check columns for missing data\n",
    "for key, df in dict_dfs.items():\n",
    "    cols_missing = df.columns[df.isnull().any()]\n",
    "    num_cols_missing = len(cols_missing)\n",
    "    print('{} has {} firms containing missing data, out of {} total columns.'\n",
    "         .format(key, num_cols_missing, df.shape[1]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation 1:** The only two datasets with incomplete data for all time periods are **actual EPS** and **forecasted EPS 3 months prior.**\n",
    "\n",
    "> To address this problem, it'd be helpful to isolate the time period ranges for the datasets with incomplete data by row, **forecasted EPS** and **actual EOD price.**\n",
    "\n",
    "**Observation 2:** For all datasets, all firms contain incomplete data across all time periods.\n",
    "> This is expected, as analyzing financial history spanning over 20 years will naturally be rife with missing and inaccurate data. The ***good news is that `eps_fcast`, `eps_actual`, and `eps_fcast_terms` are the most complete, while `eod_actual` contains the most amount of missing data.***\n",
    "\n",
    "\n",
    "**Moving forward, we need to make sure that these inconsistencies won't clash with our analysis.**\n",
    "> **My approach:** instead of looking at rows and columns ***with*** missing data, we'll be looking at rows and columns that ***are all missing data.***\n",
    "\n",
    "I figured that if there is some missing data here and there scattered throughout the matrix, then that should not skew our analysis too much.\n",
    "\n",
    "However, if there a significant amount of rows/columns that are entirely empty, then we ***might have to get ready to drop some dates and firms from our data overall.***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "eps_fc \n",
      " False    84\n",
      "dtype: int64 \n",
      "----\n",
      "eps_actual \n",
      " False    84\n",
      "dtype: int64 \n",
      "----\n",
      "eod_actual \n",
      " False    84\n",
      "dtype: int64 \n",
      "----\n",
      "eps_fc_terms \n",
      " False    80\n",
      "dtype: int64 \n",
      "----\n"
     ]
    }
   ],
   "source": [
    "#check for empty rows, return False if row contains at least one non-null value, True if all are null\n",
    "for key, df in dict_dfs.items():\n",
    "    cols_check = df.columns\n",
    "    num_empty_rows = (df[cols_check].isnull().apply(lambda x: all(x), axis = 1)).value_counts()\n",
    "    print(key, '\\n', num_empty_rows, '\\n----')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation:** All datasets do not contain empty rows.\n",
    "> This is good news, since we can rely on the firms' averages per row instead of having to drop or limit time periods."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 226,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "eps_fc \n",
      " False    506\n",
      "dtype: int64\n",
      "eps_actual \n",
      " False    506\n",
      "True       1\n",
      "dtype: int64\n",
      "eod_actual \n",
      " False    506\n",
      "dtype: int64\n",
      "eps_fc_terms \n",
      " False    506\n",
      "True       1\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "#check for empty columns, return False if column contains at least one non-null value, True if all are null\n",
    "for key, df in dict_dfs.items():\n",
    "    cols_check = df.columns\n",
    "    num_empty_cols = df[cols_check].isnull().apply(lambda x: all(x), axis = 0).value_counts()\n",
    "    print(key, '\\n', num_empty_cols)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation:** `eps_actual` and `eps_fcast_terms` are the only datasets that have an empty column.\n",
    "> Let's isolate and look at the singular empty column for both DataFrames."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "#helper function to return an array of column names containing empty data\n",
    "def comb_cols(df):\n",
    "    empty_cols = []\n",
    "    for column in df:\n",
    "        if df[column].isnull().all():\n",
    "            empty_cols.append(column)\n",
    "            \n",
    "    return empty_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "In eps_act, the firm ['AMCR UN Equity'] has no data.\n",
      "In eps_fc_terms, the firm ['AMCR UN Equity'] has no data.\n"
     ]
    }
   ],
   "source": [
    "#comb datasets for empty column names\n",
    "print('In eps_act, the firm {} has no data.'.format(comb_cols(eps_act)))\n",
    "print('In eps_fc_terms, the firm {} has no data.'.format(comb_cols(eps_fc_terms)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation:** The same firm in both datasets is empty of data.\n",
    "> Though this is an annoying error to deal with, it still is to our advantage that both datasets ***share one firm*** in common for missing data. This way, we don't have to worry about dropping two entire firms.\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check Duplicate Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "eps_fc 0\n",
      "eps_actual 0\n",
      "eod_actual 0\n",
      "eps_fc_terms 0\n"
     ]
    }
   ],
   "source": [
    "for key, df in dict_dfs.items():\n",
    "    print(key, df.duplicated().sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation:** For all datasets, there are ***no duplicate data.*** This is good news!\n",
    "\n",
    "**Next, I will check for duplicated firm names.** Although the presence of duplicated firm names will inherently imply duplicated data, sometimes data gets dispersed in weird, unexpected ways, especially when dealing with large datasets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "eps_fc 0\n",
      "eps_actual 0\n",
      "eod_actual 0\n",
      "eps_fc_terms 0\n"
     ]
    }
   ],
   "source": [
    "#check for duplicated firm names\n",
    "for key, df in dict_dfs.items():\n",
    "    print(key, df.columns.duplicated().sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation:** For all datasets, there are ***no duplicate firm names.*** This is also good news.\n",
    "\n",
    "### There is no need to dedupe our data during the cleaning stage.\n",
    "\n",
    "---\n",
    "\n",
    "### Check Data Types\n",
    "\n",
    "> Make sure all numerical data types are consistent.\n",
    "- EPS and EOD values must all be **floats**\n",
    "- Dates should all be **objects** (for now)\n",
    "\n",
    "- `eps_fc` contains ***1 date field,***, so we should expect ***1 object type.***\n",
    "- `eps_act` contains ***2 date fields,*** so we should expect ***2 object types.***\n",
    "- `eod_act` contains ***1 date field,*** so we should expect ***1 object type.***\n",
    "- `eps_fc_terms` contains ***2 date fields,*** so we should expect ***2 object types.***\n",
    "\n",
    "> And everything else should be a ***float*** type.\n",
    "\n",
    "**Examine each dataset's overall data types per column.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "eps_fc \n",
      " float64    505\n",
      "object       1\n",
      "dtype: int64 \n",
      "--------\n",
      "eps_actual \n",
      " float64    499\n",
      "object       7\n",
      "int64        1\n",
      "dtype: int64 \n",
      "--------\n",
      "eod_actual \n",
      " float64    505\n",
      "object       1\n",
      "dtype: int64 \n",
      "--------\n",
      "eps_fc_terms \n",
      " float64    505\n",
      "object       2\n",
      "dtype: int64 \n",
      "--------\n"
     ]
    }
   ],
   "source": [
    "for key, df in dict_dfs.items():\n",
    "    print(key,'\\n', df.dtypes.value_counts(), '\\n--------')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation 1:** `eps_fc`, `eod_act`, and `eps_fc_terms` are all consistent with the expected number of object types.\n",
    "\n",
    "> But we'll still have to double check these facts later.\n",
    "\n",
    "**Observation 2:** `eps_act` contains 5 more object columns than expected.\n",
    "\n",
    "---\n",
    "\n",
    "***Isolate 'Object' Columns under `eps_act`***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>Quarter</th>\n",
       "      <th>BRK/B UN Equity</th>\n",
       "      <th>FOX UW Equity</th>\n",
       "      <th>GOOG UW Equity</th>\n",
       "      <th>HCP UN Equity</th>\n",
       "      <th>SYMC UW Equity</th>\n",
       "      <th>UA UN Equity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Q1</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Q2</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Q3</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Q4</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Q1</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "      <td>#N/A Field Not Applicable</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Quarter            BRK/B UN Equity              FOX UW Equity  \\\n",
       "0      Q1  #N/A Field Not Applicable  #N/A Field Not Applicable   \n",
       "1      Q2  #N/A Field Not Applicable  #N/A Field Not Applicable   \n",
       "2      Q3  #N/A Field Not Applicable  #N/A Field Not Applicable   \n",
       "3      Q4  #N/A Field Not Applicable  #N/A Field Not Applicable   \n",
       "4      Q1  #N/A Field Not Applicable  #N/A Field Not Applicable   \n",
       "\n",
       "              GOOG UW Equity              HCP UN Equity  \\\n",
       "0  #N/A Field Not Applicable  #N/A Field Not Applicable   \n",
       "1  #N/A Field Not Applicable  #N/A Field Not Applicable   \n",
       "2  #N/A Field Not Applicable  #N/A Field Not Applicable   \n",
       "3  #N/A Field Not Applicable  #N/A Field Not Applicable   \n",
       "4  #N/A Field Not Applicable  #N/A Field Not Applicable   \n",
       "\n",
       "              SYMC UW Equity               UA UN Equity  \n",
       "0  #N/A Field Not Applicable  #N/A Field Not Applicable  \n",
       "1  #N/A Field Not Applicable  #N/A Field Not Applicable  \n",
       "2  #N/A Field Not Applicable  #N/A Field Not Applicable  \n",
       "3  #N/A Field Not Applicable  #N/A Field Not Applicable  \n",
       "4  #N/A Field Not Applicable  #N/A Field Not Applicable  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eps_act.select_dtypes(include = 'object').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation:** These firms are all 'Object' types because there is no recorded data under them.\n",
    "> This is actually missing data. From `eps_act`, there are 6 firms that are empty of data.\n",
    "\n",
    "### This was a tricky situation to spot, but I caught it in the end. There actually is missing data after all, but we missed those 5 entire firms during the \"Check Missing Data\" stage, because they were recorded as objects. This means that Pandas wrongly recognized these 5 firms as \"complete.\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Quality\n",
    "\n",
    "**Missing Data**\n",
    "> **In the end, I decided not to drop any rows or columns with missing data. For the sake of data preservation, I kept all missing data recorded as NaN.**\n",
    "\n",
    "-  `eps_fc_terms` is missing the year 1999.\n",
    "- `eps_act` and `eps_fc_act` have one firm with empty data: 'AMCR UN Equity'\n",
    "- `eps_act` contains 7 empty firms: BRK/B UN Equity, FOX UW Equity, GOOG UW Equity, HCP UN Equity, SYMC UW Equity, UA UN Equity\n",
    "--- \n",
    "- Unnormalized date formats among all DataFrames.\n",
    "- Firm names across all DataFrames are capitalized and contain white space.\n",
    "- Erroneous data type for `eps_act` Object columns.\n",
    "- Erroneous data type for **date** under `eod_act`\n",
    "- Erroneous data type for **forecast_made** under `eps_fc_terms`\n",
    "- Erroneous data types for **term** under `eps_act`, `eps_fc`, and `eps_fc_terms` to DateTime quarter index\n",
    "- No recorded quarterly data for `eod_act`\n",
    "---\n",
    "- Firm names not referenced by **firm_id**\n",
    "- No recorded 20-year for each dataset.\n",
    "- No recorded yearly averages for each dataset\n",
    "- No recorded quarterly averages for each dataset.\n",
    "---\n",
    "- Features datasets \n",
    "\n",
    "### Tidiness\n",
    "- Unnormalized data among `df_twenty_year_avgs`, `df_yearly_avgs`, and `df_quarter_avgs`\n",
    "- 20-year, yearly, and quarterly averages contained in different DataFrames.\n",
    "- Unnormalized data among `eps_act`, `eps_fc`, `eod_act`, and `eps_fc_terms`\n",
    "\n",
    "\n",
    "### C) CLEAN <a id=\"clean\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "eps_fc_clean = eps_fc.copy()\n",
    "eps_act_clean = eps_act.copy()\n",
    "eod_act_clean = eod_act.copy()\n",
    "eps_fc_terms_clean = eps_fc_terms.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create dictionary of all clean dfs for iteration\n",
    "dict_clean = {'eps_fc_clean' : eps_fc_clean,\n",
    "             'eps_act_clean' : eps_act_clean,\n",
    "             'eod_act_clean' : eod_act_clean,\n",
    "             'eps_fc_terms_clean' : eps_fc_terms_clean}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Code\n",
    "> **ISSUE 1:**  Unnormalized date formats among all DataFrames.\n",
    "\n",
    "**Define:** \n",
    "- Conjoin `eps_act_clean` dates from 2 columns into 1 to match `eps_fc_clean` format.\n",
    "> The format we want is YYYYQN. For example, Quarter 1 in 2005 will be 2005Q1.\n",
    "\n",
    "- Rename both fiscal periods under `eps_act_clean` and `eps_fc_clean` to **term.**\n",
    "\n",
    "- For both fiscal periods under `eps_fc_terms_clean`, add an underscore.\n",
    "- Under `eps_fc_terms_clean`, format **term** to YYYYQN."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create new column, combine year and quarter into 1 string\n",
    "eps_act_clean['term'] = eps_act_clean['Year'].map(str) + eps_act_clean['Quarter']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop 'Year' and 'Quarter'\n",
    "eps_act_clean.drop(['Year', 'Quarter'], axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "#designate new column as first column\n",
    "reorder_cols = eps_act_clean.columns.tolist()\n",
    "reorder_cols.insert(0, reorder_cols.pop(reorder_cols.index('term')))\n",
    "\n",
    "eps_act_clean = eps_act_clean.reindex(columns = reorder_cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "#rename eps_fc_clean fiscal period to term\n",
    "eps_fc_clean.rename(index = str, columns = {'Term Forecasted' : 'term'}, inplace = True)\n",
    "\n",
    "#reassign to dictionary\n",
    "dict_clean['eps_act_clean'] = eps_act_clean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\Anaconda3\\lib\\site-packages\\pandas\\core\\generic.py:5096: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self[name] = value\n"
     ]
    }
   ],
   "source": [
    "eps_fc_clean[4:].term = '20' + eps_fc_clean[4:].term.astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "#add an underscore for both date periods under eps_fc_terms_clean\n",
    "eps_fc_terms_clean.rename(index = str, columns = {'Forecast Made' : 'forecast_made',\n",
    "                                                 'Term Forecasted' : 'term'},\n",
    "                         inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "#under term, add a '20' before each string\n",
    "eps_fc_terms_clean.term = '20' + eps_fc_terms_clean.term.astype(str)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>term</th>\n",
       "      <th>A UN Equity</th>\n",
       "      <th>AAL UW Equity</th>\n",
       "      <th>AAP UN Equity</th>\n",
       "      <th>AAPL UW Equity</th>\n",
       "      <th>ABBV UN Equity</th>\n",
       "      <th>ABC UN Equity</th>\n",
       "      <th>ABMD UW Equity</th>\n",
       "      <th>ABT UN Equity</th>\n",
       "      <th>ACN UN Equity</th>\n",
       "      <th>...</th>\n",
       "      <th>XEL UW Equity</th>\n",
       "      <th>XLNX UW Equity</th>\n",
       "      <th>XOM UN Equity</th>\n",
       "      <th>XRAY UW Equity</th>\n",
       "      <th>XRX UN Equity</th>\n",
       "      <th>XYL UN Equity</th>\n",
       "      <th>YUM UN Equity</th>\n",
       "      <th>ZBH UN Equity</th>\n",
       "      <th>ZION UW Equity</th>\n",
       "      <th>ZTS UN Equity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>2008Q1</td>\n",
       "      <td>0.393</td>\n",
       "      <td>-1.307</td>\n",
       "      <td>0.786</td>\n",
       "      <td>0.232</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.313</td>\n",
       "      <td>-0.205</td>\n",
       "      <td>0.623</td>\n",
       "      <td>0.557</td>\n",
       "      <td>...</td>\n",
       "      <td>0.316</td>\n",
       "      <td>0.301</td>\n",
       "      <td>2.148</td>\n",
       "      <td>0.429</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.398</td>\n",
       "      <td>1.035</td>\n",
       "      <td>1.01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>2016Q2</td>\n",
       "      <td>0.389</td>\n",
       "      <td>1.678</td>\n",
       "      <td>2.111</td>\n",
       "      <td>1.998</td>\n",
       "      <td>1.200</td>\n",
       "      <td>1.585</td>\n",
       "      <td>0.155</td>\n",
       "      <td>0.533</td>\n",
       "      <td>1.184</td>\n",
       "      <td>...</td>\n",
       "      <td>0.404</td>\n",
       "      <td>0.469</td>\n",
       "      <td>0.637</td>\n",
       "      <td>0.700</td>\n",
       "      <td>0.981</td>\n",
       "      <td>0.474</td>\n",
       "      <td>0.739</td>\n",
       "      <td>1.968</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>2014Q3</td>\n",
       "      <td>0.741</td>\n",
       "      <td>1.634</td>\n",
       "      <td>1.880</td>\n",
       "      <td>1.232</td>\n",
       "      <td>0.773</td>\n",
       "      <td>0.922</td>\n",
       "      <td>0.140</td>\n",
       "      <td>0.598</td>\n",
       "      <td>1.210</td>\n",
       "      <td>...</td>\n",
       "      <td>0.755</td>\n",
       "      <td>0.545</td>\n",
       "      <td>1.708</td>\n",
       "      <td>0.595</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.520</td>\n",
       "      <td>0.830</td>\n",
       "      <td>1.301</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.370</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 506 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      term  A UN Equity  AAL UW Equity  AAP UN Equity  AAPL UW Equity  \\\n",
       "36  2008Q1        0.393         -1.307          0.786           0.232   \n",
       "69  2016Q2        0.389          1.678          2.111           1.998   \n",
       "62  2014Q3        0.741          1.634          1.880           1.232   \n",
       "\n",
       "    ABBV UN Equity  ABC UN Equity  ABMD UW Equity  ABT UN Equity  \\\n",
       "36             NaN          0.313          -0.205          0.623   \n",
       "69           1.200          1.585           0.155          0.533   \n",
       "62           0.773          0.922           0.140          0.598   \n",
       "\n",
       "    ACN UN Equity  ...  XEL UW Equity  XLNX UW Equity  XOM UN Equity  \\\n",
       "36          0.557  ...          0.316           0.301          2.148   \n",
       "69          1.184  ...          0.404           0.469          0.637   \n",
       "62          1.210  ...          0.755           0.545          1.708   \n",
       "\n",
       "    XRAY UW Equity  XRX UN Equity  XYL UN Equity  YUM UN Equity  \\\n",
       "36           0.429            NaN            NaN          0.398   \n",
       "69           0.700          0.981          0.474          0.739   \n",
       "62           0.595            NaN          0.520          0.830   \n",
       "\n",
       "    ZBH UN Equity  ZION UW Equity  ZTS UN Equity  \n",
       "36          1.035            1.01            NaN  \n",
       "69          1.968             NaN          0.444  \n",
       "62          1.301             NaN          0.370  \n",
       "\n",
       "[3 rows x 506 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#give overview of both DataFrames\n",
    "eps_fc_clean.sample(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>term</th>\n",
       "      <th>A UN Equity</th>\n",
       "      <th>AAL UW Equity</th>\n",
       "      <th>AAP UN Equity</th>\n",
       "      <th>AAPL UW Equity</th>\n",
       "      <th>ABBV UN Equity</th>\n",
       "      <th>ABC UN Equity</th>\n",
       "      <th>ABMD UW Equity</th>\n",
       "      <th>ABT UN Equity</th>\n",
       "      <th>ACN UN Equity</th>\n",
       "      <th>...</th>\n",
       "      <th>XEL UW Equity</th>\n",
       "      <th>XLNX UW Equity</th>\n",
       "      <th>XOM UN Equity</th>\n",
       "      <th>XRAY UW Equity</th>\n",
       "      <th>XRX UN Equity</th>\n",
       "      <th>XYL UN Equity</th>\n",
       "      <th>YUM UN Equity</th>\n",
       "      <th>ZBH UN Equity</th>\n",
       "      <th>ZION UW Equity</th>\n",
       "      <th>ZTS UN Equity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>2016Q1</td>\n",
       "      <td>0.37</td>\n",
       "      <td>1.15</td>\n",
       "      <td>2.16</td>\n",
       "      <td>3.300000</td>\n",
       "      <td>0.83</td>\n",
       "      <td>1.60</td>\n",
       "      <td>0.21</td>\n",
       "      <td>0.21</td>\n",
       "      <td>1.31</td>\n",
       "      <td>...</td>\n",
       "      <td>0.47</td>\n",
       "      <td>0.57</td>\n",
       "      <td>0.43</td>\n",
       "      <td>0.72</td>\n",
       "      <td>0.12</td>\n",
       "      <td>0.370000</td>\n",
       "      <td>0.88</td>\n",
       "      <td>0.54</td>\n",
       "      <td>0.38</td>\n",
       "      <td>0.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>2018Q1</td>\n",
       "      <td>-0.99</td>\n",
       "      <td>0.34</td>\n",
       "      <td>1.85</td>\n",
       "      <td>3.920000</td>\n",
       "      <td>1.74</td>\n",
       "      <td>3.95</td>\n",
       "      <td>0.85</td>\n",
       "      <td>0.24</td>\n",
       "      <td>1.82</td>\n",
       "      <td>...</td>\n",
       "      <td>0.57</td>\n",
       "      <td>0.63</td>\n",
       "      <td>1.09</td>\n",
       "      <td>0.36</td>\n",
       "      <td>0.08</td>\n",
       "      <td>0.440000</td>\n",
       "      <td>1.30</td>\n",
       "      <td>0.86</td>\n",
       "      <td>1.16</td>\n",
       "      <td>0.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>2013Q4</td>\n",
       "      <td>0.64</td>\n",
       "      <td>-5.38</td>\n",
       "      <td>0.68</td>\n",
       "      <td>1.187143</td>\n",
       "      <td>0.70</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.37</td>\n",
       "      <td>1.04</td>\n",
       "      <td>...</td>\n",
       "      <td>0.30</td>\n",
       "      <td>0.50</td>\n",
       "      <td>1.91</td>\n",
       "      <td>0.52</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.368964</td>\n",
       "      <td>0.72</td>\n",
       "      <td>1.38</td>\n",
       "      <td>-0.32</td>\n",
       "      <td>0.21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 506 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      term  A UN Equity  AAL UW Equity  AAP UN Equity  AAPL UW Equity  \\\n",
       "68  2016Q1         0.37           1.15           2.16        3.300000   \n",
       "76  2018Q1        -0.99           0.34           1.85        3.920000   \n",
       "59  2013Q4         0.64          -5.38           0.68        1.187143   \n",
       "\n",
       "    ABBV UN Equity  ABC UN Equity  ABMD UW Equity  ABT UN Equity  \\\n",
       "68            0.83           1.60            0.21           0.21   \n",
       "76            1.74           3.95            0.85           0.24   \n",
       "59            0.70           0.22            0.10           0.37   \n",
       "\n",
       "    ACN UN Equity  ...  XEL UW Equity  XLNX UW Equity  XOM UN Equity  \\\n",
       "68           1.31  ...           0.47            0.57           0.43   \n",
       "76           1.82  ...           0.57            0.63           1.09   \n",
       "59           1.04  ...           0.30            0.50           1.91   \n",
       "\n",
       "    XRAY UW Equity  XRX UN Equity  XYL UN Equity  YUM UN Equity  \\\n",
       "68            0.72           0.12       0.370000           0.88   \n",
       "76            0.36           0.08       0.440000           1.30   \n",
       "59            0.52           1.00       0.368964           0.72   \n",
       "\n",
       "    ZBH UN Equity  ZION UW Equity  ZTS UN Equity  \n",
       "68           0.54            0.38           0.41  \n",
       "76           0.86            1.16           0.72  \n",
       "59           1.38           -0.32           0.21  \n",
       "\n",
       "[3 rows x 506 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eps_act_clean.sample(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True    84\n",
       "Name: term, dtype: int64"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check consistent format between both `term` fields\n",
    "forecasts = pd.concat([eps_fc_clean.term, eps_act_clean.term])\n",
    "\n",
    "#count number of instances where unique term values are the same\n",
    "(forecasts.value_counts().sort_index() == 2).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['term', 'A UN Equity'], dtype='object')\n",
      "Index(['term', 'A UN Equity'], dtype='object')\n",
      "Index(['date', 'A UN Equity'], dtype='object')\n",
      "Index(['forecast_made', 'term'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "#check that dates are the first columns\n",
    "for key, df in dict_clean.items():\n",
    "    print(df.columns[:2])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The **term** column under `eps_fc_clean`, `eps_act_clean`, and `eps_fc_terms_clean` are all normalized to YYYQN format. This means `eod_act_clean` is the only one with a different date formatting rule.\n",
    "\n",
    "### Code\n",
    "> **ISSUE 2:** Firm names across all DataFrames are capitalized and contain white space.\n",
    "\n",
    "**Define:** Iterate across all DataFrames. Get rid of everything from the first whitespace character onward. Lowercase column names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "#lowercase names\n",
    "for key, df in dict_clean.items():\n",
    "    lower_cols = [x.lower() for x in df.columns]\n",
    "    df.columns = lower_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "#get rid of white space\n",
    "for key, df in dict_clean.items():\n",
    "    stock_symbols = [x.split(' ', 1)[0] for x in df.columns]\n",
    "    df.columns = stock_symbols"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "#check that all firm names are consistent, should return 505 firm results\n",
    "all_ticks = []\n",
    "for key, df in dict_clean.items():\n",
    "    firm_ticks = np.array(df.columns.values)\n",
    "    all_ticks.append(firm_ticks)\n",
    "    \n",
    "#flatten array\n",
    "all_ticks = np.hstack(all_ticks)\n",
    "\n",
    "#assign tick names to Pandas Series\n",
    "all_ticks = pd.Series(all_ticks)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date             1\n",
       "forecast_made    1\n",
       "term             3\n",
       "ntrs             4\n",
       "nvr              4\n",
       "dtype: int64"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#look at lowest 3 value counts, SHOULD BE DATE PERIODS\n",
    "all_ticks.value_counts().sort_values()[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "alxn    4\n",
       "are     4\n",
       "ben     4\n",
       "syy     4\n",
       "snps    4\n",
       "dtype: int64"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#look at highest value counts, should be FOUR (because there are 4 dataframes all with firm names)\n",
    "all_ticks.value_counts().sort_values(ascending = False)[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check for any upper case instance among columns\n",
    "all_ticks.str.isupper().any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check for any white space\n",
    "all_ticks.str.contains(' ').any()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Code\n",
    "> **ISSUE 3:** Erroneous data type for `eps_act` Object columns.\n",
    "\n",
    "**Define:**\n",
    "\n",
    "- Convert '#N/A Field Not Applicable' strings into 'NaN'\n",
    "- Convert the 6 `eps_act_clean` Object columns into NaN type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "#isolate 6 firm names recorded as 'object'\n",
    "obj_ticks = eps_act_clean.select_dtypes(include = 'object').columns.values\n",
    "\n",
    "#exclude 'term'\n",
    "obj_ticks = obj_ticks[1:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "#convert to NaN\n",
    "str_replace = '#N/A Field Not Applicable'\n",
    "eps_act_clean[obj_ticks] = eps_act_clean[obj_ticks].replace(str_replace, np.nan)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 84 entries, 0 to 83\n",
      "Columns: 506 entries, term to zts\n",
      "dtypes: float64(505), object(1)\n",
      "memory usage: 332.1+ KB\n"
     ]
    }
   ],
   "source": [
    "eps_act_clean.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['term']"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#make sure 'term' is the only object-type column\n",
    "eps_act_clean.select_dtypes(include = 'object').columns.values.tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Code\n",
    "\n",
    "> **ISSUE 4:**  Erroneous data type for **date** under `eod_act`\n",
    "\n",
    "**Define:** Convert column `date` to DateTime object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "eod_act_clean.date = pd.to_datetime(eod_act_clean.date, infer_datetime_format=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "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>date</th>\n",
       "      <th>a</th>\n",
       "      <th>aal</th>\n",
       "      <th>aap</th>\n",
       "      <th>aapl</th>\n",
       "      <th>abbv</th>\n",
       "      <th>abc</th>\n",
       "      <th>abmd</th>\n",
       "      <th>abt</th>\n",
       "      <th>acn</th>\n",
       "      <th>...</th>\n",
       "      <th>xel</th>\n",
       "      <th>xlnx</th>\n",
       "      <th>xom</th>\n",
       "      <th>xray</th>\n",
       "      <th>xrx</th>\n",
       "      <th>xyl</th>\n",
       "      <th>yum</th>\n",
       "      <th>zbh</th>\n",
       "      <th>zion</th>\n",
       "      <th>zts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2002-09-30</td>\n",
       "      <td>8.7994</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.58</td>\n",
       "      <td>1.0357</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.3254</td>\n",
       "      <td>3.350</td>\n",
       "      <td>18.0806</td>\n",
       "      <td>14.28</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.838</td>\n",
       "      <td>31.90</td>\n",
       "      <td>20.085</td>\n",
       "      <td>13.0412</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.9625</td>\n",
       "      <td>38.34</td>\n",
       "      <td>43.53</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2002-06-28</td>\n",
       "      <td>15.9347</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.17</td>\n",
       "      <td>1.2657</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.4364</td>\n",
       "      <td>8.479</td>\n",
       "      <td>16.8498</td>\n",
       "      <td>19.00</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22.430</td>\n",
       "      <td>40.92</td>\n",
       "      <td>18.455</td>\n",
       "      <td>18.3631</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.5162</td>\n",
       "      <td>35.66</td>\n",
       "      <td>52.10</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 506 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         date        a  aal    aap    aapl  abbv      abc   abmd      abt  \\\n",
       "14 2002-09-30   8.7994  NaN  17.58  1.0357   NaN  17.3254  3.350  18.0806   \n",
       "13 2002-06-28  15.9347  NaN  18.17  1.2657   NaN  18.4364  8.479  16.8498   \n",
       "\n",
       "      acn  ...  xel    xlnx    xom    xray      xrx  xyl      yum    zbh  \\\n",
       "14  14.28  ...  NaN  15.838  31.90  20.085  13.0412  NaN   9.9625  38.34   \n",
       "13  19.00  ...  NaN  22.430  40.92  18.455  18.3631  NaN  10.5162  35.66   \n",
       "\n",
       "     zion  zts  \n",
       "14  43.53  NaN  \n",
       "13  52.10  NaN  \n",
       "\n",
       "[2 rows x 506 columns]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eod_act_clean.sample(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 84 entries, 0 to 83\n",
      "Columns: 506 entries, date to zts\n",
      "dtypes: datetime64[ns](1), float64(505)\n",
      "memory usage: 332.1 KB\n"
     ]
    }
   ],
   "source": [
    "#confirm datetime64 present\n",
    "eod_act_clean.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Code\n",
    "> **ISSUE 5:** Erroneous data type for **forecast_made** under `eps_fc_terms`.\n",
    "\n",
    "**Define:**\n",
    "\n",
    "- Convert column **forecast_made** to DateTime object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "eps_fc_terms_clean.forecast_made = pd.to_datetime(eps_fc_terms_clean.forecast_made, infer_datetime_format=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 80 entries, 0 to 79\n",
      "Columns: 507 entries, forecast_made to zts\n",
      "dtypes: datetime64[ns](1), float64(505), object(1)\n",
      "memory usage: 317.5+ KB\n"
     ]
    }
   ],
   "source": [
    "#confirm presence of datetime64 object\n",
    "eps_fc_terms_clean.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "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>forecast_made</th>\n",
       "      <th>term</th>\n",
       "      <th>a</th>\n",
       "      <th>aal</th>\n",
       "      <th>aap</th>\n",
       "      <th>aapl</th>\n",
       "      <th>abbv</th>\n",
       "      <th>abc</th>\n",
       "      <th>abmd</th>\n",
       "      <th>abt</th>\n",
       "      <th>...</th>\n",
       "      <th>xel</th>\n",
       "      <th>xlnx</th>\n",
       "      <th>xom</th>\n",
       "      <th>xray</th>\n",
       "      <th>xrx</th>\n",
       "      <th>xyl</th>\n",
       "      <th>yum</th>\n",
       "      <th>zbh</th>\n",
       "      <th>zion</th>\n",
       "      <th>zts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>2014-07-01</td>\n",
       "      <td>2014Q4</td>\n",
       "      <td>0.937</td>\n",
       "      <td>1.178</td>\n",
       "      <td>1.447</td>\n",
       "      <td>1.334</td>\n",
       "      <td>0.892</td>\n",
       "      <td>0.964</td>\n",
       "      <td>0.063</td>\n",
       "      <td>0.697</td>\n",
       "      <td>...</td>\n",
       "      <td>0.305</td>\n",
       "      <td>0.555</td>\n",
       "      <td>1.906</td>\n",
       "      <td>0.649</td>\n",
       "      <td>1.244</td>\n",
       "      <td>0.629</td>\n",
       "      <td>0.999</td>\n",
       "      <td>1.752</td>\n",
       "      <td>0.454</td>\n",
       "      <td>0.391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>2011-10-01</td>\n",
       "      <td>2012Q1</td>\n",
       "      <td>0.697</td>\n",
       "      <td>-1.200</td>\n",
       "      <td>1.510</td>\n",
       "      <td>1.259</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.612</td>\n",
       "      <td>-0.057</td>\n",
       "      <td>1.038</td>\n",
       "      <td>...</td>\n",
       "      <td>0.490</td>\n",
       "      <td>0.530</td>\n",
       "      <td>2.245</td>\n",
       "      <td>0.533</td>\n",
       "      <td>1.001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.713</td>\n",
       "      <td>1.298</td>\n",
       "      <td>0.367</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 507 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   forecast_made    term      a    aal    aap   aapl   abbv    abc   abmd  \\\n",
       "59    2014-07-01  2014Q4  0.937  1.178  1.447  1.334  0.892  0.964  0.063   \n",
       "48    2011-10-01  2012Q1  0.697 -1.200  1.510  1.259    NaN  0.612 -0.057   \n",
       "\n",
       "      abt  ...    xel   xlnx    xom   xray    xrx    xyl    yum    zbh   zion  \\\n",
       "59  0.697  ...  0.305  0.555  1.906  0.649  1.244  0.629  0.999  1.752  0.454   \n",
       "48  1.038  ...  0.490  0.530  2.245  0.533  1.001    NaN  0.713  1.298  0.367   \n",
       "\n",
       "      zts  \n",
       "59  0.391  \n",
       "48    NaN  \n",
       "\n",
       "[2 rows x 507 columns]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eps_fc_terms_clean.sample(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Code\n",
    "> **ISSUE 6:** Erroneous data types for **term** under `eps_act`, `eps_fc`, and `eps_fc_terms` to DateTime quarter index\n",
    "\n",
    "**Define:** Convert YYYYQQ formats into DateTime quarter index type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "#helper function to turn a column into Quarter Period type\n",
    "def convert_period(df, col):\n",
    "    return pd.to_datetime(df[col]).dt.to_period('Q')\n",
    "# eps_fc_terms_clean.term = pd.PeriodIndex(eps_fc_terms_clean.term, freq='Q')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "#convert term fields to Period type\n",
    "eps_fc_clean.term = convert_period(eps_fc_clean, 'term')\n",
    "eps_act_clean.term = convert_period(eps_act_clean, 'term')\n",
    "eps_fc_terms_clean.term = convert_period(eps_fc_terms_clean, 'term')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "period[Q-DEC]\n",
      "period[Q-DEC]\n",
      "period[Q-DEC]\n"
     ]
    }
   ],
   "source": [
    "#verify Period Object types\n",
    "print(eps_fc_clean.dtypes['term'])\n",
    "print(eps_act_clean.dtypes['term'])\n",
    "print(eps_fc_terms_clean.dtypes['term'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Code\n",
    "> **ISSUE 7:** No recorded quarterly data for `eod_act`\n",
    "\n",
    "**Define:** Add a new column **term**, which extracts the *year* and *quarter* from the **date** column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "eod_act_clean['term'] = eod_act_clean.date.dt.to_period('Q')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "#reorder columns\n",
    "eod_cols = ['date', 'term'] + [col for col in eod_act_clean.iloc[:, 1:] if col!='term']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "eod_act_clean = eod_act_clean.reindex(columns = eod_cols)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "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>date</th>\n",
       "      <th>term</th>\n",
       "      <th>a</th>\n",
       "      <th>aal</th>\n",
       "      <th>aap</th>\n",
       "      <th>aapl</th>\n",
       "      <th>abbv</th>\n",
       "      <th>abc</th>\n",
       "      <th>abmd</th>\n",
       "      <th>abt</th>\n",
       "      <th>...</th>\n",
       "      <th>xel</th>\n",
       "      <th>xlnx</th>\n",
       "      <th>xom</th>\n",
       "      <th>xray</th>\n",
       "      <th>xrx</th>\n",
       "      <th>xyl</th>\n",
       "      <th>yum</th>\n",
       "      <th>zbh</th>\n",
       "      <th>zion</th>\n",
       "      <th>zts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1999-03-31</td>\n",
       "      <td>1999Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.2835</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.2934</td>\n",
       "      <td>6.250</td>\n",
       "      <td>20.9504</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.2813</td>\n",
       "      <td>35.2813</td>\n",
       "      <td>7.7500</td>\n",
       "      <td>140.6214</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.6284</td>\n",
       "      <td>NaN</td>\n",
       "      <td>66.5000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1999-06-30</td>\n",
       "      <td>1999Q2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.6540</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.1859</td>\n",
       "      <td>6.875</td>\n",
       "      <td>20.3630</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.6250</td>\n",
       "      <td>38.5625</td>\n",
       "      <td>9.6250</td>\n",
       "      <td>155.6057</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.7297</td>\n",
       "      <td>NaN</td>\n",
       "      <td>63.5000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1999-09-30</td>\n",
       "      <td>1999Q3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.2612</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.7462</td>\n",
       "      <td>7.750</td>\n",
       "      <td>16.4471</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32.7656</td>\n",
       "      <td>37.9688</td>\n",
       "      <td>7.5833</td>\n",
       "      <td>110.4883</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.3591</td>\n",
       "      <td>NaN</td>\n",
       "      <td>55.1250</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1999-12-31</td>\n",
       "      <td>1999Q4</td>\n",
       "      <td>52.0909</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.6719</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.6843</td>\n",
       "      <td>18.375</td>\n",
       "      <td>16.2513</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45.4688</td>\n",
       "      <td>40.2813</td>\n",
       "      <td>7.8750</td>\n",
       "      <td>59.7723</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.9434</td>\n",
       "      <td>NaN</td>\n",
       "      <td>59.1875</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2000-03-31</td>\n",
       "      <td>2000Q1</td>\n",
       "      <td>70.0721</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.8504</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.6388</td>\n",
       "      <td>20.250</td>\n",
       "      <td>15.7478</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>82.8125</td>\n",
       "      <td>38.9063</td>\n",
       "      <td>9.4583</td>\n",
       "      <td>68.4994</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.5839</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41.6250</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 507 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        date    term        a  aal  aap    aapl  abbv     abc    abmd  \\\n",
       "0 1999-03-31  1999Q1      NaN  NaN  NaN  1.2835   NaN  8.2934   6.250   \n",
       "1 1999-06-30  1999Q2      NaN  NaN  NaN  1.6540   NaN  6.1859   6.875   \n",
       "2 1999-09-30  1999Q3      NaN  NaN  NaN  2.2612   NaN  5.7462   7.750   \n",
       "3 1999-12-31  1999Q4  52.0909  NaN  NaN  3.6719   NaN  3.6843  18.375   \n",
       "4 2000-03-31  2000Q1  70.0721  NaN  NaN  4.8504   NaN  3.6388  20.250   \n",
       "\n",
       "       abt  ...  xel     xlnx      xom    xray       xrx  xyl      yum  zbh  \\\n",
       "0  20.9504  ...  NaN  20.2813  35.2813  7.7500  140.6214  NaN  12.6284  NaN   \n",
       "1  20.3630  ...  NaN  28.6250  38.5625  9.6250  155.6057  NaN   9.7297  NaN   \n",
       "2  16.4471  ...  NaN  32.7656  37.9688  7.5833  110.4883  NaN   7.3591  NaN   \n",
       "3  16.2513  ...  NaN  45.4688  40.2813  7.8750   59.7723  NaN   6.9434  NaN   \n",
       "4  15.7478  ...  NaN  82.8125  38.9063  9.4583   68.4994  NaN   5.5839  NaN   \n",
       "\n",
       "      zion  zts  \n",
       "0  66.5000  NaN  \n",
       "1  63.5000  NaN  \n",
       "2  55.1250  NaN  \n",
       "3  59.1875  NaN  \n",
       "4  41.6250  NaN  \n",
       "\n",
       "[5 rows x 507 columns]"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify column order\n",
    "eod_act_clean.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "84"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify 84 columns\n",
    "eod_act_clean.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 84 entries, 0 to 83\n",
      "Columns: 507 entries, date to zts\n",
      "dtypes: datetime64[ns](1), float64(505), period[Q-DEC](1)\n",
      "memory usage: 332.8 KB\n"
     ]
    }
   ],
   "source": [
    "eod_act_clean.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## All full years are normalized to YYYY-MM-DD, and all quarterly records to YYYY-QQ.\n",
    "\n",
    "## This is to enable for more efficient handling, cleaning, and classifying of data later on."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Code\n",
    "\n",
    "**ISSUE 8:** Firm names not referenced by **firm_id**\n",
    "\n",
    "**Define:**\n",
    "- Assign a **firm_id** to each firm for future normalization.\n",
    "\n",
    "- Generate new CSV named `firms.csv`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "#get firm names\n",
    "firm_names = eps_act_clean.columns[1:].to_numpy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "#assign ids to firm names\n",
    "firm_names_ids = {}\n",
    "counter = 0\n",
    "\n",
    "for firm in firm_names:\n",
    "    firm_names_ids.update({firm: counter})\n",
    "    counter += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "#generate new DF\n",
    "firm_ids = pd.DataFrame(list(firm_names_ids.items())).rename(columns = {0: 'firm', 1: 'firm_id'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "#switch firm and firm_id columns\n",
    "firm_ids = firm_ids.reindex(columns = ['firm_id', 'firm'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "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>firm_id</th>\n",
       "      <th>firm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>191</td>\n",
       "      <td>fls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>395</th>\n",
       "      <td>395</td>\n",
       "      <td>regn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330</th>\n",
       "      <td>330</td>\n",
       "      <td>mtb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>385</th>\n",
       "      <td>385</td>\n",
       "      <td>psx</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>90</td>\n",
       "      <td>cdw</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>350</th>\n",
       "      <td>350</td>\n",
       "      <td>nue</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>452</th>\n",
       "      <td>452</td>\n",
       "      <td>ua</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325</th>\n",
       "      <td>325</td>\n",
       "      <td>mro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173</th>\n",
       "      <td>173</td>\n",
       "      <td>ew</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     firm_id  firm\n",
       "191      191   fls\n",
       "395      395  regn\n",
       "330      330   mtb\n",
       "385      385   psx\n",
       "90        90   cdw\n",
       "0          0     a\n",
       "350      350   nue\n",
       "452      452    ua\n",
       "325      325   mro\n",
       "173      173    ew"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "firm_ids.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 505 entries, 0 to 504\n",
      "Data columns (total 2 columns):\n",
      "firm_id    505 non-null int64\n",
      "firm       505 non-null object\n",
      "dtypes: int64(1), object(1)\n",
      "memory usage: 8.0+ KB\n"
     ]
    }
   ],
   "source": [
    "firm_ids.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Code\n",
    "> **ISSUE 9:** No recorded 20-year averages for each dataset.\n",
    "\n",
    "**Define:**\n",
    "\n",
    "\n",
    "- Isolate 20-year averages for each firm into its own DataFrame\n",
    "\n",
    "- Create new DataFrame `twenty_avgs` depicting all 20-year averages for each firm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "#convert foreign keys to str temporarily\n",
    "firm_ids_dict = {str(k):v for k, v in firm_names_ids.items()}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_twenty_yr_avgs(df, index_names, col_name, firm_ids_dict):\n",
    "    df = pd.DataFrame({col_name: df[index_names].mean()}).rename_axis('firm_id').reset_index()\n",
    "    \n",
    "    #set 'firm_id' type int64\n",
    "    df['firm_id'] = df['firm_id'].map(lambda s: firm_ids_dict.get(s) if s in firm_ids_dict else s)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "#get 20-year averages\n",
    "eps_fc_twenty_avg = get_twenty_yr_avgs(eps_fc_clean, firm_names, 'eps_fc', firm_ids_dict)\n",
    "eps_act_twenty_avg = get_twenty_yr_avgs(eps_act_clean, firm_names, 'eps_act', firm_ids_dict)\n",
    "eod_act_twenty_avg = get_twenty_yr_avgs(eod_act_clean, firm_names, 'eod_act', firm_ids_dict)\n",
    "eps_fc_terms_twenty_avg = get_twenty_yr_avgs(eps_fc_terms_clean, firm_names, 'eps_fc_terms', firm_ids_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "#put averages into a list\n",
    "twenty_avg_list = [eps_fc_twenty_avg,\n",
    "                  eps_act_twenty_avg,\n",
    "                  eod_act_twenty_avg,\n",
    "                  eps_fc_terms_twenty_avg]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "#merge all Series\n",
    "df_twenty_year_avgs = reduce(lambda x, y: pd.merge(x, y, on = 'firm_id', how = 'outer'), twenty_avg_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "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>firm_id</th>\n",
       "      <th>eps_fc</th>\n",
       "      <th>eps_act</th>\n",
       "      <th>eod_act</th>\n",
       "      <th>eps_fc_terms</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>27</td>\n",
       "      <td>0.786224</td>\n",
       "      <td>0.516265</td>\n",
       "      <td>26.256899</td>\n",
       "      <td>0.886074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>354</th>\n",
       "      <td>354</td>\n",
       "      <td>0.083833</td>\n",
       "      <td>-0.065484</td>\n",
       "      <td>14.560577</td>\n",
       "      <td>0.095760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>139</td>\n",
       "      <td>0.586952</td>\n",
       "      <td>0.584727</td>\n",
       "      <td>41.484419</td>\n",
       "      <td>0.631437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14</td>\n",
       "      <td>0.202857</td>\n",
       "      <td>0.067798</td>\n",
       "      <td>43.845867</td>\n",
       "      <td>0.206675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>488</th>\n",
       "      <td>488</td>\n",
       "      <td>0.233048</td>\n",
       "      <td>0.113171</td>\n",
       "      <td>24.628083</td>\n",
       "      <td>0.288237</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     firm_id    eps_fc   eps_act    eod_act  eps_fc_terms\n",
       "27        27  0.786224  0.516265  26.256899      0.886074\n",
       "354      354  0.083833 -0.065484  14.560577      0.095760\n",
       "139      139  0.586952  0.584727  41.484419      0.631437\n",
       "14        14  0.202857  0.067798  43.845867      0.206675\n",
       "488      488  0.233048  0.113171  24.628083      0.288237"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_twenty_year_avgs.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 505 entries, 0 to 504\n",
      "Data columns (total 5 columns):\n",
      "firm_id         505 non-null int64\n",
      "eps_fc          505 non-null float64\n",
      "eps_act         498 non-null float64\n",
      "eod_act         505 non-null float64\n",
      "eps_fc_terms    504 non-null float64\n",
      "dtypes: float64(4), int64(1)\n",
      "memory usage: 23.7 KB\n"
     ]
    }
   ],
   "source": [
    "df_twenty_year_avgs.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "505"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check there are 505 firms\n",
    "df_twenty_year_avgs.firm_id.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "firm_id         False\n",
       "eps_fc          False\n",
       "eps_act          True\n",
       "eod_act         False\n",
       "eps_fc_terms     True\n",
       "dtype: bool"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check missing data\n",
    "df_twenty_year_avgs.isna().any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check duplicate data\n",
    "df_twenty_year_avgs.duplicated().any()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Code\n",
    "> **ISSUE 10:** No recorded yearly averages for each dataset.\n",
    "\n",
    "**Define:**\n",
    "- Create 4 separate DataFrames for all attributes\n",
    "- Rename columns to \"Feature_Year\" (e.g. eps_fc_1999, eod_act_2000, etc.)\n",
    "- Outer merge all DataFrames to create new DataFrame, `df_yearly_avgs`, on **firms** attribute"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "#helper function to calculate yearly average of each DataFrame\n",
    "def calculate_df_yearly_avgs(df, col, firm_ids_dict):\n",
    "    #extract year from dates, reset index, and remove index name\n",
    "    df = df.groupby(df[col].dt.year).mean().transpose().rename_axis('firm_id').reset_index()\n",
    "    \n",
    "    df['firm_id'] = df['firm_id'].map(lambda s: firm_ids_dict.get(s) if s in firm_ids_dict else s)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "#helper function to melt columns\n",
    "def melt_avgs(df, firm_id, var_name):\n",
    "    return df.melt(id_vars = [firm_id],\n",
    "                value_vars = df.columns.difference([firm_id]),\n",
    "                var_name = var_name,\n",
    "                value_name = 'average')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "#calculate and melt\n",
    "def create_df_yearly_avgs(df, col, firm_ids_dict, firm_id):\n",
    "    df = calculate_df_yearly_avgs(df, 'term', firm_ids_dict)\n",
    "    df = melt_avgs(df, firm_id, 'year')\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "#generate DataFrames of all yearly averages\n",
    "df_eps_fc_yearly_avgs = create_df_yearly_avgs(eps_fc_clean, 'term', firm_ids_dict, 'firm_id')\n",
    "df_eps_act_yearly_avgs = create_df_yearly_avgs(eps_act_clean, 'term', firm_ids_dict, 'firm_id')\n",
    "\n",
    "#for eod_act, doesn't matter if we use date or term field\n",
    "df_eod_act_yearly_avgs = create_df_yearly_avgs(eod_act_clean, 'date', firm_ids_dict, 'firm_id')\n",
    "df_eps_fc_terms_yearly_avgs = create_df_yearly_avgs(eps_fc_terms_clean, 'term', firm_ids_dict, 'firm_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "def store_df(path, df, csv_name):\n",
    "    df.to_csv(path + csv_name, encoding = 'utf-8', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create copies\n",
    "eps_fc_yearly_avgs = df_eps_fc_yearly_avgs.copy()\n",
    "eps_act_yearly_avgs = df_eps_act_yearly_avgs.copy()\n",
    "eod_act_yearly_avgs = df_eod_act_yearly_avgs.copy()\n",
    "eps_fc_terms_yearly_avgs = df_eps_fc_terms_yearly_avgs.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "#add new column entailing \"type\" of feature before concat\n",
    "eps_fc_yearly_avgs = eps_fc_yearly_avgs.assign(feature = 'eps_fc')\n",
    "eps_act_yearly_avgs = eps_act_yearly_avgs.assign(feature = 'eps_act')\n",
    "eod_act_yearly_avgs = eod_act_yearly_avgs.assign(feature = 'eod_act')\n",
    "eps_fc_terms_yearly_avgs = eps_fc_terms_yearly_avgs.assign(feature = 'eps_fc_terms')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "yearly_avgs_arr = [eps_fc_yearly_avgs, \n",
    "                  eps_act_yearly_avgs,\n",
    "                  eod_act_yearly_avgs,\n",
    "                  eps_fc_terms_yearly_avgs]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "#merge all yearly average DataFrames\n",
    "df_yearly_avgs = pd.concat(yearly_avgs_arr)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "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>firm_id</th>\n",
       "      <th>year</th>\n",
       "      <th>average</th>\n",
       "      <th>feature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5326</th>\n",
       "      <td>276</td>\n",
       "      <td>2009</td>\n",
       "      <td>0.08300</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1649</th>\n",
       "      <td>134</td>\n",
       "      <td>2002</td>\n",
       "      <td>0.09350</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5385</th>\n",
       "      <td>335</td>\n",
       "      <td>2009</td>\n",
       "      <td>0.35725</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5654</th>\n",
       "      <td>99</td>\n",
       "      <td>2010</td>\n",
       "      <td>0.98875</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9019</th>\n",
       "      <td>434</td>\n",
       "      <td>2016</td>\n",
       "      <td>2.73625</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6371</th>\n",
       "      <td>311</td>\n",
       "      <td>2011</td>\n",
       "      <td>0.84500</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>211</th>\n",
       "      <td>211</td>\n",
       "      <td>1999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4420</th>\n",
       "      <td>380</td>\n",
       "      <td>2007</td>\n",
       "      <td>0.64150</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8070</th>\n",
       "      <td>495</td>\n",
       "      <td>2014</td>\n",
       "      <td>0.50175</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      firm_id  year  average feature\n",
       "5326      276  2009  0.08300  eps_fc\n",
       "1649      134  2002  0.09350  eps_fc\n",
       "5385      335  2009  0.35725  eps_fc\n",
       "5654       99  2010  0.98875  eps_fc\n",
       "0           0  1999      NaN  eps_fc\n",
       "9019      434  2016  2.73625  eps_fc\n",
       "6371      311  2011  0.84500  eps_fc\n",
       "211       211  1999      NaN  eps_fc\n",
       "4420      380  2007  0.64150  eps_fc\n",
       "8070      495  2014  0.50175  eps_fc"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#each average, individually\n",
    "\n",
    "#eps_fc_yearly\n",
    "eps_fc_yearly_avgs.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "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>firm_id</th>\n",
       "      <th>year</th>\n",
       "      <th>average</th>\n",
       "      <th>feature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7986</th>\n",
       "      <td>411</td>\n",
       "      <td>2014</td>\n",
       "      <td>2.245000</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2074</th>\n",
       "      <td>54</td>\n",
       "      <td>2003</td>\n",
       "      <td>NaN</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4043</th>\n",
       "      <td>3</td>\n",
       "      <td>2007</td>\n",
       "      <td>0.144643</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3177</th>\n",
       "      <td>147</td>\n",
       "      <td>2005</td>\n",
       "      <td>NaN</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2406</th>\n",
       "      <td>386</td>\n",
       "      <td>2003</td>\n",
       "      <td>0.275000</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3623</th>\n",
       "      <td>88</td>\n",
       "      <td>2006</td>\n",
       "      <td>0.715000</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5415</th>\n",
       "      <td>365</td>\n",
       "      <td>2009</td>\n",
       "      <td>0.784935</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2392</th>\n",
       "      <td>372</td>\n",
       "      <td>2003</td>\n",
       "      <td>0.314375</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4002</th>\n",
       "      <td>467</td>\n",
       "      <td>2006</td>\n",
       "      <td>0.300625</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4939</th>\n",
       "      <td>394</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.415000</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      firm_id  year   average  feature\n",
       "7986      411  2014  2.245000  eps_act\n",
       "2074       54  2003       NaN  eps_act\n",
       "4043        3  2007  0.144643  eps_act\n",
       "3177      147  2005       NaN  eps_act\n",
       "2406      386  2003  0.275000  eps_act\n",
       "3623       88  2006  0.715000  eps_act\n",
       "5415      365  2009  0.784935  eps_act\n",
       "2392      372  2003  0.314375  eps_act\n",
       "4002      467  2006  0.300625  eps_act\n",
       "4939      394  2008  0.415000  eps_act"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#eps_fc_yearly\n",
    "eps_act_yearly_avgs.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "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>firm_id</th>\n",
       "      <th>year</th>\n",
       "      <th>average</th>\n",
       "      <th>feature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4704</th>\n",
       "      <td>159</td>\n",
       "      <td>2008</td>\n",
       "      <td>32.26750</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4225</th>\n",
       "      <td>185</td>\n",
       "      <td>2007</td>\n",
       "      <td>66.66250</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9821</th>\n",
       "      <td>226</td>\n",
       "      <td>2018</td>\n",
       "      <td>230.23500</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6317</th>\n",
       "      <td>257</td>\n",
       "      <td>2011</td>\n",
       "      <td>37.90000</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6307</th>\n",
       "      <td>247</td>\n",
       "      <td>2011</td>\n",
       "      <td>NaN</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1296</th>\n",
       "      <td>286</td>\n",
       "      <td>2001</td>\n",
       "      <td>37.34075</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225</th>\n",
       "      <td>225</td>\n",
       "      <td>1999</td>\n",
       "      <td>50.84375</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1211</th>\n",
       "      <td>201</td>\n",
       "      <td>2001</td>\n",
       "      <td>38.56375</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8414</th>\n",
       "      <td>334</td>\n",
       "      <td>2015</td>\n",
       "      <td>55.38500</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1349</th>\n",
       "      <td>339</td>\n",
       "      <td>2001</td>\n",
       "      <td>19.36000</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      firm_id  year    average  feature\n",
       "4704      159  2008   32.26750  eod_act\n",
       "4225      185  2007   66.66250  eod_act\n",
       "9821      226  2018  230.23500  eod_act\n",
       "6317      257  2011   37.90000  eod_act\n",
       "6307      247  2011        NaN  eod_act\n",
       "1296      286  2001   37.34075  eod_act\n",
       "225       225  1999   50.84375  eod_act\n",
       "1211      201  2001   38.56375  eod_act\n",
       "8414      334  2015   55.38500  eod_act\n",
       "1349      339  2001   19.36000  eod_act"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#eod_act_yearly\n",
    "eod_act_yearly_avgs.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "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>firm_id</th>\n",
       "      <th>year</th>\n",
       "      <th>average</th>\n",
       "      <th>feature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7990</th>\n",
       "      <td>415</td>\n",
       "      <td>2015</td>\n",
       "      <td>0.42625</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7514</th>\n",
       "      <td>444</td>\n",
       "      <td>2014</td>\n",
       "      <td>1.08350</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4632</th>\n",
       "      <td>87</td>\n",
       "      <td>2009</td>\n",
       "      <td>-0.02850</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2261</th>\n",
       "      <td>241</td>\n",
       "      <td>2004</td>\n",
       "      <td>NaN</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2758</th>\n",
       "      <td>233</td>\n",
       "      <td>2005</td>\n",
       "      <td>0.37150</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3583</th>\n",
       "      <td>48</td>\n",
       "      <td>2007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8415</th>\n",
       "      <td>335</td>\n",
       "      <td>2016</td>\n",
       "      <td>-0.14125</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7375</th>\n",
       "      <td>305</td>\n",
       "      <td>2014</td>\n",
       "      <td>0.27825</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3388</th>\n",
       "      <td>358</td>\n",
       "      <td>2006</td>\n",
       "      <td>0.60750</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5745</th>\n",
       "      <td>190</td>\n",
       "      <td>2011</td>\n",
       "      <td>0.45325</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      firm_id  year  average       feature\n",
       "7990      415  2015  0.42625  eps_fc_terms\n",
       "7514      444  2014  1.08350  eps_fc_terms\n",
       "4632       87  2009 -0.02850  eps_fc_terms\n",
       "2261      241  2004      NaN  eps_fc_terms\n",
       "2758      233  2005  0.37150  eps_fc_terms\n",
       "3583       48  2007      NaN  eps_fc_terms\n",
       "8415      335  2016 -0.14125  eps_fc_terms\n",
       "7375      305  2014  0.27825  eps_fc_terms\n",
       "3388      358  2006  0.60750  eps_fc_terms\n",
       "5745      190  2011  0.45325  eps_fc_terms"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#eps_fc_terms_yearly\n",
    "eps_fc_terms_yearly_avgs.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['firm_id', 'year', 'average', 'feature'], dtype='object') \n",
      " Index(['firm_id', 'year', 'average', 'feature'], dtype='object') \n",
      " Index(['firm_id', 'year', 'average', 'feature'], dtype='object') \n",
      " Index(['firm_id', 'year', 'average', 'feature'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "#verify all columns are consistent\n",
    "print(eps_fc_yearly_avgs.columns, '\\n',\n",
    "      eps_act_yearly_avgs.columns, '\\n',\n",
    "      eod_act_yearly_avgs.columns, '\\n',\n",
    "      eps_fc_terms_yearly_avgs.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "505 \n",
      " 505 \n",
      " 505 \n",
      " 505\n"
     ]
    }
   ],
   "source": [
    "#verify there are 505 firms in each dataframe\n",
    "print(eps_fc_yearly_avgs.firm_id.nunique(), '\\n',\n",
    "      eps_act_yearly_avgs.firm_id.nunique(), '\\n',\n",
    "      eod_act_yearly_avgs.firm_id.nunique(), '\\n',\n",
    "      eps_fc_terms_yearly_avgs.firm_id.nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "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>firm_id</th>\n",
       "      <th>year</th>\n",
       "      <th>average</th>\n",
       "      <th>feature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2782</th>\n",
       "      <td>257</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.032500</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>595</th>\n",
       "      <td>90</td>\n",
       "      <td>2001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1166</th>\n",
       "      <td>156</td>\n",
       "      <td>2001</td>\n",
       "      <td>0.020625</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9226</th>\n",
       "      <td>136</td>\n",
       "      <td>2017</td>\n",
       "      <td>1.112500</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9568</th>\n",
       "      <td>478</td>\n",
       "      <td>2017</td>\n",
       "      <td>0.060000</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5015</th>\n",
       "      <td>470</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.706000</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>457</th>\n",
       "      <td>457</td>\n",
       "      <td>1999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7984</th>\n",
       "      <td>409</td>\n",
       "      <td>2014</td>\n",
       "      <td>0.240000</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3546</th>\n",
       "      <td>11</td>\n",
       "      <td>2007</td>\n",
       "      <td>0.584500</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>47</td>\n",
       "      <td>1999</td>\n",
       "      <td>3.033225</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      firm_id  year   average       feature\n",
       "2782      257  2004  0.032500       eps_act\n",
       "595        90  2001       NaN  eps_fc_terms\n",
       "1166      156  2001  0.020625       eps_act\n",
       "9226      136  2017  1.112500       eps_act\n",
       "9568      478  2017  0.060000       eps_act\n",
       "5015      470  2008  0.706000        eps_fc\n",
       "457       457  1999       NaN       eps_act\n",
       "7984      409  2014  0.240000       eps_act\n",
       "3546       11  2007  0.584500  eps_fc_terms\n",
       "47         47  1999  3.033225       eod_act"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#all yearly averages\n",
    "df_yearly_avgs.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "505"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify 505 firms in all yearly averages df\n",
    "df_yearly_avgs.firm_id.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 41915 entries, 0 to 10099\n",
      "Data columns (total 4 columns):\n",
      "firm_id    41915 non-null int64\n",
      "year       41915 non-null object\n",
      "average    35971 non-null float64\n",
      "feature    41915 non-null object\n",
      "dtypes: float64(1), int64(1), object(2)\n",
      "memory usage: 1.6+ MB\n"
     ]
    }
   ],
   "source": [
    "df_yearly_avgs.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Code\n",
    "\n",
    "> **ISSUE 11:** No recorded quarterly averages for each dataset.\n",
    "\n",
    "**Define:** \n",
    "\n",
    "- Parse `eod_act_clean` dates  by **calendar quarter average** with quarterly data in a new DataFrame.\n",
    "- Create separate DataFrames containing quarterly averages for `eps_fc_clean`, `eps_act_clean`, and `eps_fc_terms_clean` \n",
    "- Rename columns to \"Quarter_Year_Feature\" (e.g. **q1_eps_fc,** etc.)\n",
    "- Outer merge all DataFrames into a new df `df_quarterly_avgs` on **firms**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "#helper function to calculate quarterly average of each DataFrame\n",
    "def calculate_df_quarterly_avgs(df, col, firm_ids_dict):\n",
    "    #extract quarter from Period objects\n",
    "    df =  df.groupby(df[col].dt.strftime('Q%q')).mean().transpose().rename_axis('firm_id').reset_index()\n",
    "    df['firm_id'] = df['firm_id'].map(lambda s: firm_ids_dict.get(s) if s in firm_ids_dict else s)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "#calculate and melt\n",
    "def create_df_quarterly_avgs(df, col, firm_ids_dict, firm_id):\n",
    "    df = calculate_df_quarterly_avgs(df, 'term', firm_ids_dict)\n",
    "    df = melt_avgs(df, firm_id, 'quarter')\n",
    "    \n",
    "    #lowercase quarter column\n",
    "    df.quarter = df.quarter.str.lower()\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "#generate all quarterly average DataFrames\n",
    "df_eps_fc_quarter_avgs = create_df_quarterly_avgs(eps_fc_clean, 'term', firm_ids_dict, 'firm_id')\n",
    "df_eps_act_quarter_avgs = create_df_quarterly_avgs(eps_act_clean, 'term', firm_ids_dict, 'firm_id')\n",
    "df_eod_act_quarter_avgs = create_df_quarterly_avgs(eod_act_clean, 'term', firm_ids_dict, 'firm_id')\n",
    "df_eps_fc_terms_quarter_avgs = create_df_quarterly_avgs(eps_fc_terms_clean, 'term', firm_ids_dict, 'firm_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create copies\n",
    "eps_fc_quarter_avgs = df_eps_fc_quarter_avgs.copy()\n",
    "eps_act_quarter_avgs = df_eps_act_quarter_avgs.copy()\n",
    "eod_act_quarter_avgs = df_eod_act_quarter_avgs.copy()\n",
    "eps_fc_terms_quarter_avgs = df_eps_fc_terms_quarter_avgs.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "#add new column entailing \"type\" of feature before concat\n",
    "eps_fc_quarter_avgs = eps_fc_quarter_avgs.assign(feature = 'eps_fc')\n",
    "eps_act_quarter_avgs = eps_act_quarter_avgs.assign(feature = 'eps_act')\n",
    "eod_act_quarter_avgs = eod_act_quarter_avgs.assign(feature = 'eod_act')\n",
    "eps_fc_terms_quarter_avgs = eps_fc_terms_quarter_avgs.assign(feature = 'eps_fc_terms')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "#put DataFrames in array\n",
    "quarterly_avgs_arr = [eps_fc_quarter_avgs,\n",
    "                  eps_act_quarter_avgs,\n",
    "                  eod_act_quarter_avgs,\n",
    "                  eps_fc_terms_quarter_avgs]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [],
   "source": [
    "#merge all quarterly average DataFrames\n",
    "df_quarter_avgs = pd.concat(quarterly_avgs_arr)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "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>firm_id</th>\n",
       "      <th>quarter</th>\n",
       "      <th>average</th>\n",
       "      <th>feature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>481</th>\n",
       "      <td>481</td>\n",
       "      <td>q1</td>\n",
       "      <td>1.440429</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1801</th>\n",
       "      <td>286</td>\n",
       "      <td>q4</td>\n",
       "      <td>1.244619</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1701</th>\n",
       "      <td>186</td>\n",
       "      <td>q4</td>\n",
       "      <td>0.873571</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1285</th>\n",
       "      <td>275</td>\n",
       "      <td>q3</td>\n",
       "      <td>0.234111</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1933</th>\n",
       "      <td>418</td>\n",
       "      <td>q4</td>\n",
       "      <td>0.277286</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>919</th>\n",
       "      <td>414</td>\n",
       "      <td>q2</td>\n",
       "      <td>0.559238</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>608</th>\n",
       "      <td>103</td>\n",
       "      <td>q2</td>\n",
       "      <td>0.902190</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>742</th>\n",
       "      <td>237</td>\n",
       "      <td>q2</td>\n",
       "      <td>0.290286</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>108</td>\n",
       "      <td>q1</td>\n",
       "      <td>0.531214</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>989</th>\n",
       "      <td>484</td>\n",
       "      <td>q2</td>\n",
       "      <td>0.678524</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      firm_id quarter   average feature\n",
       "481       481      q1  1.440429  eps_fc\n",
       "1801      286      q4  1.244619  eps_fc\n",
       "1701      186      q4  0.873571  eps_fc\n",
       "1285      275      q3  0.234111  eps_fc\n",
       "1933      418      q4  0.277286  eps_fc\n",
       "919       414      q2  0.559238  eps_fc\n",
       "608       103      q2  0.902190  eps_fc\n",
       "742       237      q2  0.290286  eps_fc\n",
       "108       108      q1  0.531214  eps_fc\n",
       "989       484      q2  0.678524  eps_fc"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#each quarter individually\n",
    "\n",
    "#eps_fc_quarter\n",
    "eps_fc_quarter_avgs.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "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>firm_id</th>\n",
       "      <th>quarter</th>\n",
       "      <th>average</th>\n",
       "      <th>feature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>653</th>\n",
       "      <td>148</td>\n",
       "      <td>q2</td>\n",
       "      <td>0.403449</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1695</th>\n",
       "      <td>180</td>\n",
       "      <td>q4</td>\n",
       "      <td>0.117725</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1484</th>\n",
       "      <td>474</td>\n",
       "      <td>q3</td>\n",
       "      <td>-0.401429</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>520</th>\n",
       "      <td>15</td>\n",
       "      <td>q2</td>\n",
       "      <td>0.716667</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1588</th>\n",
       "      <td>73</td>\n",
       "      <td>q4</td>\n",
       "      <td>0.436000</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343</th>\n",
       "      <td>343</td>\n",
       "      <td>q1</td>\n",
       "      <td>0.079934</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1730</th>\n",
       "      <td>215</td>\n",
       "      <td>q4</td>\n",
       "      <td>1.523000</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1579</th>\n",
       "      <td>64</td>\n",
       "      <td>q4</td>\n",
       "      <td>0.762233</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>753</th>\n",
       "      <td>248</td>\n",
       "      <td>q2</td>\n",
       "      <td>0.391667</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>158</td>\n",
       "      <td>q1</td>\n",
       "      <td>0.942857</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      firm_id quarter   average  feature\n",
       "653       148      q2  0.403449  eps_act\n",
       "1695      180      q4  0.117725  eps_act\n",
       "1484      474      q3 -0.401429  eps_act\n",
       "520        15      q2  0.716667  eps_act\n",
       "1588       73      q4  0.436000  eps_act\n",
       "343       343      q1  0.079934  eps_act\n",
       "1730      215      q4  1.523000  eps_act\n",
       "1579       64      q4  0.762233  eps_act\n",
       "753       248      q2  0.391667  eps_act\n",
       "158       158      q1  0.942857  eps_act"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#eps_act_quarter\n",
    "eps_act_quarter_avgs.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "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>firm_id</th>\n",
       "      <th>quarter</th>\n",
       "      <th>average</th>\n",
       "      <th>feature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>981</th>\n",
       "      <td>476</td>\n",
       "      <td>q2</td>\n",
       "      <td>41.839700</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>272</th>\n",
       "      <td>272</td>\n",
       "      <td>q1</td>\n",
       "      <td>22.370714</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1280</th>\n",
       "      <td>270</td>\n",
       "      <td>q3</td>\n",
       "      <td>53.544000</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1363</th>\n",
       "      <td>353</td>\n",
       "      <td>q3</td>\n",
       "      <td>18.720000</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1428</th>\n",
       "      <td>418</td>\n",
       "      <td>q3</td>\n",
       "      <td>37.352290</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1368</th>\n",
       "      <td>358</td>\n",
       "      <td>q3</td>\n",
       "      <td>50.118219</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1755</th>\n",
       "      <td>240</td>\n",
       "      <td>q4</td>\n",
       "      <td>126.306500</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1267</th>\n",
       "      <td>257</td>\n",
       "      <td>q3</td>\n",
       "      <td>47.822048</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>252</td>\n",
       "      <td>q1</td>\n",
       "      <td>76.920769</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1702</th>\n",
       "      <td>187</td>\n",
       "      <td>q4</td>\n",
       "      <td>39.786333</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      firm_id quarter     average  feature\n",
       "981       476      q2   41.839700  eod_act\n",
       "272       272      q1   22.370714  eod_act\n",
       "1280      270      q3   53.544000  eod_act\n",
       "1363      353      q3   18.720000  eod_act\n",
       "1428      418      q3   37.352290  eod_act\n",
       "1368      358      q3   50.118219  eod_act\n",
       "1755      240      q4  126.306500  eod_act\n",
       "1267      257      q3   47.822048  eod_act\n",
       "252       252      q1   76.920769  eod_act\n",
       "1702      187      q4   39.786333  eod_act"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#eod_act_quarter\n",
    "eod_act_quarter_avgs.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "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>firm_id</th>\n",
       "      <th>quarter</th>\n",
       "      <th>average</th>\n",
       "      <th>feature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1614</th>\n",
       "      <td>99</td>\n",
       "      <td>q4</td>\n",
       "      <td>1.615133</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>817</th>\n",
       "      <td>312</td>\n",
       "      <td>q2</td>\n",
       "      <td>1.126158</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1716</th>\n",
       "      <td>201</td>\n",
       "      <td>q4</td>\n",
       "      <td>1.685650</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>68</td>\n",
       "      <td>q1</td>\n",
       "      <td>0.642684</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>895</th>\n",
       "      <td>390</td>\n",
       "      <td>q2</td>\n",
       "      <td>0.619400</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1166</th>\n",
       "      <td>156</td>\n",
       "      <td>q3</td>\n",
       "      <td>0.373050</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1520</th>\n",
       "      <td>5</td>\n",
       "      <td>q4</td>\n",
       "      <td>0.794200</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1181</th>\n",
       "      <td>171</td>\n",
       "      <td>q3</td>\n",
       "      <td>2.018200</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1498</th>\n",
       "      <td>488</td>\n",
       "      <td>q3</td>\n",
       "      <td>0.256600</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>66</td>\n",
       "      <td>q1</td>\n",
       "      <td>0.186850</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      firm_id quarter   average       feature\n",
       "1614       99      q4  1.615133  eps_fc_terms\n",
       "817       312      q2  1.126158  eps_fc_terms\n",
       "1716      201      q4  1.685650  eps_fc_terms\n",
       "68         68      q1  0.642684  eps_fc_terms\n",
       "895       390      q2  0.619400  eps_fc_terms\n",
       "1166      156      q3  0.373050  eps_fc_terms\n",
       "1520        5      q4  0.794200  eps_fc_terms\n",
       "1181      171      q3  2.018200  eps_fc_terms\n",
       "1498      488      q3  0.256600  eps_fc_terms\n",
       "66         66      q1  0.186850  eps_fc_terms"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#eps_fc_terms_quarter\n",
    "eps_fc_terms_quarter_avgs.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['firm_id', 'quarter', 'average', 'feature'], dtype='object') \n",
      " Index(['firm_id', 'quarter', 'average', 'feature'], dtype='object') \n",
      " Index(['firm_id', 'quarter', 'average', 'feature'], dtype='object') \n",
      " Index(['firm_id', 'quarter', 'average', 'feature'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "#verify that all columns are consistent\n",
    "print(eps_fc_quarter_avgs.columns, '\\n',\n",
    "      eps_act_quarter_avgs.columns, '\\n',\n",
    "      eod_act_quarter_avgs.columns, '\\n',\n",
    "      eps_fc_terms_quarter_avgs.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "505 \n",
      " 505 \n",
      " 505 \n",
      " 505\n"
     ]
    }
   ],
   "source": [
    "#verify there are 505 firms in each dataframe\n",
    "print(eps_fc_quarter_avgs.firm_id.nunique(), '\\n',\n",
    "      eps_act_quarter_avgs.firm_id.nunique(), '\\n',\n",
    "      eod_act_quarter_avgs.firm_id.nunique(), '\\n',\n",
    "      eps_fc_terms_quarter_avgs.firm_id.nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "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>firm_id</th>\n",
       "      <th>quarter</th>\n",
       "      <th>average</th>\n",
       "      <th>feature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>828</th>\n",
       "      <td>323</td>\n",
       "      <td>q2</td>\n",
       "      <td>1.323500</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1410</th>\n",
       "      <td>400</td>\n",
       "      <td>q3</td>\n",
       "      <td>0.346810</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>385</th>\n",
       "      <td>385</td>\n",
       "      <td>q1</td>\n",
       "      <td>0.982875</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1030</th>\n",
       "      <td>20</td>\n",
       "      <td>q3</td>\n",
       "      <td>512.112467</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1784</th>\n",
       "      <td>269</td>\n",
       "      <td>q4</td>\n",
       "      <td>0.398450</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303</th>\n",
       "      <td>303</td>\n",
       "      <td>q1</td>\n",
       "      <td>0.131545</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1747</th>\n",
       "      <td>232</td>\n",
       "      <td>q4</td>\n",
       "      <td>0.231667</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>928</th>\n",
       "      <td>423</td>\n",
       "      <td>q2</td>\n",
       "      <td>56.671371</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>755</th>\n",
       "      <td>250</td>\n",
       "      <td>q2</td>\n",
       "      <td>0.334762</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>491</th>\n",
       "      <td>491</td>\n",
       "      <td>q1</td>\n",
       "      <td>0.368923</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      firm_id quarter     average       feature\n",
       "828       323      q2    1.323500       eps_act\n",
       "1410      400      q3    0.346810        eps_fc\n",
       "385       385      q1    0.982875        eps_fc\n",
       "1030       20      q3  512.112467       eod_act\n",
       "1784      269      q4    0.398450  eps_fc_terms\n",
       "303       303      q1    0.131545  eps_fc_terms\n",
       "1747      232      q4    0.231667       eps_act\n",
       "928       423      q2   56.671371       eod_act\n",
       "755       250      q2    0.334762       eps_act\n",
       "491       491      q1    0.368923  eps_fc_terms"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#all quarterly averages\n",
    "df_quarter_avgs.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "505"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify 505 firms in df_quarter_avgs\n",
    "df_quarter_avgs.firm_id.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 8080 entries, 0 to 2019\n",
      "Data columns (total 4 columns):\n",
      "firm_id    8080 non-null int64\n",
      "quarter    8080 non-null object\n",
      "average    8021 non-null float64\n",
      "feature    8080 non-null object\n",
      "dtypes: float64(1), int64(1), object(2)\n",
      "memory usage: 315.6+ KB\n"
     ]
    }
   ],
   "source": [
    "df_quarter_avgs.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Code\n",
    ">**ISSUE 1:** Unnormalized data among `df_twenty_year_avgs`, `df_yearly_avgs`, and `df_quarter_avgs`\n",
    "\n",
    "**Define:** \n",
    "- Normalize features across all 3 DataFrames. \n",
    "- For `df_twenty_year_avgs`, melt **feature column names** (eps_fc, eps_act, eod_act, eps_fc_terms) into a single column called **feature**\n",
    "\n",
    "Desired output for each DataFrame:\n",
    "\n",
    "\n",
    "| firm_id     | average  | average_type | time_period   | feature|\n",
    "|:------------|:-------------:|:-----------:|:---------:|-------:|\n",
    "|501           | 0.21875         | yearly   | 2002        | eps_fc_terms |\n",
    "| 378          |   0.059000     |   quarterly | q4        | eps_act  |\n",
    "| 147           | NaN     | yearly   |   2016      | eod_act  |\n",
    "| 33           |  0.005067       | quarterly  | q4      | eps_fc |\n",
    "\n",
    "> ***Apply the above table format to all three DFs, then concatenate to create `all_avgs` .***\n",
    "\n",
    "**Note:** For `df_twenty_year_avgs`, it would be redundant to put *twenty_year* as the value under both **average_type** and **time_period** columns.\n",
    "\n",
    "To curb this, I will assign all **time_period** values as NaN for `df_twenty_year_avgs`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [],
   "source": [
    "#assign column order\n",
    "avgs_col_order = ['firm_id', 'average', 'average_type', 'time_period', 'feature']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create clean copies of dfs\n",
    "twenty_year_clean = df_twenty_year_avgs.copy()\n",
    "yearly_clean = df_yearly_avgs.copy()\n",
    "quarterly_clean = df_quarter_avgs.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- ### `twenty_year_clean`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [],
   "source": [
    "#isolate feature names\n",
    "feats_col_names = twenty_year_clean.columns.difference(['firm_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [],
   "source": [
    "#move eps_fc . . . eps_fc_terms under \"average_type\"\n",
    "twenty_year_clean = pd.melt(twenty_year_clean,\n",
    "        id_vars = ['firm_id'],\n",
    "        value_vars = feats_col_names,\n",
    "        var_name = 'feature',\n",
    "        value_name = 'average')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "#add 'feature' and 'time_period' columns\n",
    "twenty_year_clean = twenty_year_clean.assign(average_type = 'twenty_year', time_period = np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [],
   "source": [
    "#reorder columns\n",
    "twenty_year_clean = twenty_year_clean[avgs_col_order]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "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>firm_id</th>\n",
       "      <th>average</th>\n",
       "      <th>average_type</th>\n",
       "      <th>time_period</th>\n",
       "      <th>feature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1276</th>\n",
       "      <td>266</td>\n",
       "      <td>1.198424</td>\n",
       "      <td>twenty_year</td>\n",
       "      <td>NaN</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>468</th>\n",
       "      <td>468</td>\n",
       "      <td>49.542500</td>\n",
       "      <td>twenty_year</td>\n",
       "      <td>NaN</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1636</th>\n",
       "      <td>121</td>\n",
       "      <td>0.366400</td>\n",
       "      <td>twenty_year</td>\n",
       "      <td>NaN</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1297</th>\n",
       "      <td>287</td>\n",
       "      <td>0.815134</td>\n",
       "      <td>twenty_year</td>\n",
       "      <td>NaN</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1525</th>\n",
       "      <td>10</td>\n",
       "      <td>0.672000</td>\n",
       "      <td>twenty_year</td>\n",
       "      <td>NaN</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      firm_id    average average_type  time_period       feature\n",
       "1276      266   1.198424  twenty_year          NaN        eps_fc\n",
       "468       468  49.542500  twenty_year          NaN       eod_act\n",
       "1636      121   0.366400  twenty_year          NaN  eps_fc_terms\n",
       "1297      287   0.815134  twenty_year          NaN        eps_fc\n",
       "1525       10   0.672000  twenty_year          NaN  eps_fc_terms"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#twenty_year\n",
    "twenty_year_clean.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2020 entries, 0 to 2019\n",
      "Data columns (total 5 columns):\n",
      "firm_id         2020 non-null int64\n",
      "average         2012 non-null float64\n",
      "average_type    2020 non-null object\n",
      "time_period     0 non-null float64\n",
      "feature         2020 non-null object\n",
      "dtypes: float64(2), int64(1), object(2)\n",
      "memory usage: 79.0+ KB\n"
     ]
    }
   ],
   "source": [
    "twenty_year_clean.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "505"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify 505 firm names\n",
    "twenty_year_clean.firm_id.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([nan])"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify all time_period values are NaN\n",
    "twenty_year_clean.time_period.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['twenty_year'], dtype=object)"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify average_type contains only twenty_year\n",
    "twenty_year_clean.average_type.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- ### `yearly_clean`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [],
   "source": [
    "#assign 'average_type' column\n",
    "yearly_clean = yearly_clean.assign(average_type = 'yearly')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [],
   "source": [
    "#rename 'year' column to 'time_period'\n",
    "yearly_clean.rename(columns = {'year' : 'time_period'}, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [],
   "source": [
    "#reorder columns\n",
    "yearly_clean = yearly_clean[avgs_col_order]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "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>firm_id</th>\n",
       "      <th>average</th>\n",
       "      <th>average_type</th>\n",
       "      <th>time_period</th>\n",
       "      <th>feature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>373</th>\n",
       "      <td>373</td>\n",
       "      <td>NaN</td>\n",
       "      <td>yearly</td>\n",
       "      <td>1999</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6202</th>\n",
       "      <td>142</td>\n",
       "      <td>0.352750</td>\n",
       "      <td>yearly</td>\n",
       "      <td>2012</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8252</th>\n",
       "      <td>172</td>\n",
       "      <td>NaN</td>\n",
       "      <td>yearly</td>\n",
       "      <td>2016</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8533</th>\n",
       "      <td>453</td>\n",
       "      <td>0.135593</td>\n",
       "      <td>yearly</td>\n",
       "      <td>2015</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8245</th>\n",
       "      <td>165</td>\n",
       "      <td>1.292750</td>\n",
       "      <td>yearly</td>\n",
       "      <td>2016</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2164</th>\n",
       "      <td>144</td>\n",
       "      <td>NaN</td>\n",
       "      <td>yearly</td>\n",
       "      <td>2003</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6772</th>\n",
       "      <td>207</td>\n",
       "      <td>0.982500</td>\n",
       "      <td>yearly</td>\n",
       "      <td>2013</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3775</th>\n",
       "      <td>240</td>\n",
       "      <td>84.595000</td>\n",
       "      <td>yearly</td>\n",
       "      <td>2006</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4062</th>\n",
       "      <td>22</td>\n",
       "      <td>1.256500</td>\n",
       "      <td>yearly</td>\n",
       "      <td>2007</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651</th>\n",
       "      <td>146</td>\n",
       "      <td>29.451150</td>\n",
       "      <td>yearly</td>\n",
       "      <td>2000</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      firm_id    average average_type time_period       feature\n",
       "373       373        NaN       yearly        1999       eod_act\n",
       "6202      142   0.352750       yearly        2012  eps_fc_terms\n",
       "8252      172        NaN       yearly        2016  eps_fc_terms\n",
       "8533      453   0.135593       yearly        2015       eps_act\n",
       "8245      165   1.292750       yearly        2016  eps_fc_terms\n",
       "2164      144        NaN       yearly        2003       eod_act\n",
       "6772      207   0.982500       yearly        2013  eps_fc_terms\n",
       "3775      240  84.595000       yearly        2006       eod_act\n",
       "4062       22   1.256500       yearly        2007        eps_fc\n",
       "651       146  29.451150       yearly        2000       eod_act"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "yearly_clean.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 41915 entries, 0 to 10099\n",
      "Data columns (total 5 columns):\n",
      "firm_id         41915 non-null int64\n",
      "average         35971 non-null float64\n",
      "average_type    41915 non-null object\n",
      "time_period     41915 non-null object\n",
      "feature         41915 non-null object\n",
      "dtypes: float64(1), int64(1), object(3)\n",
      "memory usage: 1.9+ MB\n"
     ]
    }
   ],
   "source": [
    "yearly_clean.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "505"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify 505 firms\n",
    "yearly_clean.firm_id.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['yearly'], dtype=object)"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify 'average_type' contains only 'yearly'\n",
    "yearly_clean.average_type.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify 'feature' contains all features\n",
    "all(feat in feats_col_names for feat in yearly_clean.feature.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21"
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify there are 21 years recorded (1999 - 2019)\n",
    "len(yearly_clean.time_period.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1999, 2019)"
      ]
     },
     "execution_count": 144,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#examine lowest and highest year\n",
    "yearly_clean.time_period.min(), yearly_clean.time_period.max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- ### `quarterly_clean`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [],
   "source": [
    "#assign 'average_type' column\n",
    "quarterly_clean = quarterly_clean.assign(average_type = 'quarterly')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [],
   "source": [
    "#rename 'quarter' column to 'time_period'\n",
    "quarterly_clean.rename(columns = {'quarter' : 'time_period'}, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [],
   "source": [
    "#reorder columns\n",
    "quarterly_clean = quarterly_clean[avgs_col_order]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "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>firm_id</th>\n",
       "      <th>average</th>\n",
       "      <th>average_type</th>\n",
       "      <th>time_period</th>\n",
       "      <th>feature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1633</th>\n",
       "      <td>118</td>\n",
       "      <td>52.310000</td>\n",
       "      <td>quarterly</td>\n",
       "      <td>q4</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1726</th>\n",
       "      <td>211</td>\n",
       "      <td>0.463263</td>\n",
       "      <td>quarterly</td>\n",
       "      <td>q4</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>681</th>\n",
       "      <td>176</td>\n",
       "      <td>0.782667</td>\n",
       "      <td>quarterly</td>\n",
       "      <td>q2</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>692</th>\n",
       "      <td>187</td>\n",
       "      <td>43.366721</td>\n",
       "      <td>quarterly</td>\n",
       "      <td>q2</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1696</th>\n",
       "      <td>181</td>\n",
       "      <td>1.308125</td>\n",
       "      <td>quarterly</td>\n",
       "      <td>q4</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>0.565619</td>\n",
       "      <td>quarterly</td>\n",
       "      <td>q1</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>126</td>\n",
       "      <td>0.440000</td>\n",
       "      <td>quarterly</td>\n",
       "      <td>q1</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1792</th>\n",
       "      <td>277</td>\n",
       "      <td>32.133440</td>\n",
       "      <td>quarterly</td>\n",
       "      <td>q4</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1856</th>\n",
       "      <td>341</td>\n",
       "      <td>0.495800</td>\n",
       "      <td>quarterly</td>\n",
       "      <td>q4</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1681</th>\n",
       "      <td>166</td>\n",
       "      <td>0.525500</td>\n",
       "      <td>quarterly</td>\n",
       "      <td>q4</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      firm_id    average average_type time_period       feature\n",
       "1633      118  52.310000    quarterly          q4       eod_act\n",
       "1726      211   0.463263    quarterly          q4        eps_fc\n",
       "681       176   0.782667    quarterly          q2       eps_act\n",
       "692       187  43.366721    quarterly          q2       eod_act\n",
       "1696      181   1.308125    quarterly          q4        eps_fc\n",
       "7           7   0.565619    quarterly          q1        eps_fc\n",
       "126       126   0.440000    quarterly          q1        eps_fc\n",
       "1792      277  32.133440    quarterly          q4       eod_act\n",
       "1856      341   0.495800    quarterly          q4  eps_fc_terms\n",
       "1681      166   0.525500    quarterly          q4       eps_act"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "quarterly_clean.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 8080 entries, 0 to 2019\n",
      "Data columns (total 5 columns):\n",
      "firm_id         8080 non-null int64\n",
      "average         8021 non-null float64\n",
      "average_type    8080 non-null object\n",
      "time_period     8080 non-null object\n",
      "feature         8080 non-null object\n",
      "dtypes: float64(1), int64(1), object(3)\n",
      "memory usage: 378.8+ KB\n"
     ]
    }
   ],
   "source": [
    "quarterly_clean.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "505"
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify 505 firms\n",
    "quarterly_clean.firm_id.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['quarterly'], dtype=object)"
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify 'average_type' contains only 'quarterly'\n",
    "quarterly_clean.average_type.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['q1', 'q2', 'q3', 'q4'], dtype=object)"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify 'time_period' contains only 'q1' till 'q4'\n",
    "quarterly_clean.time_period.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify 'feature' contains all features\n",
    "all(feat in feats_col_names for feat in quarterly_clean.feature.unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Code\n",
    ">**ISSUE 2:** 20-year, yearly, and quarterly averages contained in different DataFrames.\n",
    "\n",
    "**Define:** Concatenate the normalized `twenty_year_clean`, `yearly_clean`, and `quarterly_clean` into new CSV **all_avgs.csv**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [],
   "source": [
    "#put all average DFs in an array\n",
    "avgs_array = [twenty_year_clean, yearly_clean, quarterly_clean]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_avgs = pd.concat(avgs_array)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "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>firm_id</th>\n",
       "      <th>average</th>\n",
       "      <th>average_type</th>\n",
       "      <th>time_period</th>\n",
       "      <th>feature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8222</th>\n",
       "      <td>142</td>\n",
       "      <td>0.511000</td>\n",
       "      <td>yearly</td>\n",
       "      <td>2016</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8826</th>\n",
       "      <td>241</td>\n",
       "      <td>0.686000</td>\n",
       "      <td>yearly</td>\n",
       "      <td>2016</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6807</th>\n",
       "      <td>242</td>\n",
       "      <td>46.966250</td>\n",
       "      <td>yearly</td>\n",
       "      <td>2012</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8662</th>\n",
       "      <td>77</td>\n",
       "      <td>0.801500</td>\n",
       "      <td>yearly</td>\n",
       "      <td>2016</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1077</th>\n",
       "      <td>67</td>\n",
       "      <td>129.920095</td>\n",
       "      <td>quarterly</td>\n",
       "      <td>q3</td>\n",
       "      <td>eod_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5006</th>\n",
       "      <td>461</td>\n",
       "      <td>0.788250</td>\n",
       "      <td>yearly</td>\n",
       "      <td>2009</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7516</th>\n",
       "      <td>446</td>\n",
       "      <td>0.478750</td>\n",
       "      <td>yearly</td>\n",
       "      <td>2013</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1362</th>\n",
       "      <td>352</td>\n",
       "      <td>6.986250</td>\n",
       "      <td>yearly</td>\n",
       "      <td>2002</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7322</th>\n",
       "      <td>252</td>\n",
       "      <td>0.755000</td>\n",
       "      <td>yearly</td>\n",
       "      <td>2013</td>\n",
       "      <td>eps_act</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9979</th>\n",
       "      <td>384</td>\n",
       "      <td>1.863000</td>\n",
       "      <td>yearly</td>\n",
       "      <td>2018</td>\n",
       "      <td>eps_fc</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      firm_id     average average_type time_period       feature\n",
       "8222      142    0.511000       yearly        2016  eps_fc_terms\n",
       "8826      241    0.686000       yearly        2016        eps_fc\n",
       "6807      242   46.966250       yearly        2012       eod_act\n",
       "8662       77    0.801500       yearly        2016        eps_fc\n",
       "1077       67  129.920095    quarterly          q3       eod_act\n",
       "5006      461    0.788250       yearly        2009  eps_fc_terms\n",
       "7516      446    0.478750       yearly        2013       eps_act\n",
       "1362      352    6.986250       yearly        2002  eps_fc_terms\n",
       "7322      252    0.755000       yearly        2013       eps_act\n",
       "9979      384    1.863000       yearly        2018        eps_fc"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_avgs.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 52015 entries, 0 to 2019\n",
      "Data columns (total 5 columns):\n",
      "firm_id         52015 non-null int64\n",
      "average         46004 non-null float64\n",
      "average_type    52015 non-null object\n",
      "time_period     49995 non-null object\n",
      "feature         52015 non-null object\n",
      "dtypes: float64(1), int64(1), object(3)\n",
      "memory usage: 2.4+ MB\n"
     ]
    }
   ],
   "source": [
    "all_avgs.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(52015, 5)"
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_avgs.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "505"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify 505 firms, no duplicates\n",
    "all_avgs.firm_id.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['twenty_year', 'yearly', 'quarterly'], dtype=object)"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify only 3 average_types\n",
    "all_avgs.average_type.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['eod_act', 'eps_act', 'eps_fc', 'eps_fc_terms'], dtype=object)"
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify 4 features\n",
    "all_avgs.feature.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Code\n",
    ">**ISSUE 3:** Unnormalized data among `eps_act`, `eps_fc`, `eod_act`, and `eps_fc_terms`\n",
    "\n",
    "**Define:**\n",
    "\n",
    "- Achieve desired `features.csv` output:\n",
    "\n",
    "| firm_id  | feature     | date      | term         |    value    |\n",
    "|:---------| :-------: | :-----------------:| :-------------: |----------: |\n",
    "| 485       |     eps_fc  |  NaT           |        2004A1      |  1.40900      |\n",
    "| 104       | eps_act     |  NaT       |  2001Q4       | -0.113333 |\n",
    "| 337     | eod_act      | 2019-03-29      |   2019Q1         |   87.490    |\n",
    "| 276       | eps_fc_terms | 2009-04-01   | 2009Q3   | 0.009 |\n",
    "\n",
    "> **date** feature contains the **date column in `eod_act`** and **forecast_made in `eps_fc_terms`**. They are assigned to the same column because both columns are consistent: the same DateTime Object.\n",
    "\n",
    "> **date** feature will be NaN for `eps_fc` and `eps_act`.\n",
    "\n",
    "- ### `eps_fc` and `eps_act`\n",
    "\n",
    "> For both DataFrames, we don't have to worry about handling the **date** column. That will be resolved later when merging all the DataFrames with `eod_act` and `eps_fc_terms`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {},
   "outputs": [],
   "source": [
    "fc_act_order = ['firm_id', 'feature', 'term', 'value']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [],
   "source": [
    "#melt firm names\n",
    "eps_fc_clean = eps_fc_clean.melt (id_vars = ['term'],\n",
    "                  var_name = 'firm_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "outputs": [],
   "source": [
    "eps_act_clean = eps_act_clean.melt(id_vars = ['term'],\n",
    "                                  var_name = 'firm_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [],
   "source": [
    "#reassign all firms to firm_id\n",
    "eps_fc_clean['firm_id'] = eps_fc_clean['firm_id'].map(firm_ids_dict)\n",
    "eps_act_clean['firm_id'] = eps_act_clean['firm_id'].map(firm_ids_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [],
   "source": [
    "#add feature column\n",
    "eps_fc_clean = eps_fc_clean.assign(feature = 'eps_fc')\n",
    "eps_act_clean = eps_act_clean.assign(feature = 'eps_act')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "outputs": [],
   "source": [
    "#reorder columns\n",
    "eps_fc_clean = eps_fc_clean[fc_act_order]\n",
    "eps_act_clean = eps_act_clean[fc_act_order]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "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>firm_id</th>\n",
       "      <th>feature</th>\n",
       "      <th>term</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>41191</th>\n",
       "      <td>490</td>\n",
       "      <td>eps_fc</td>\n",
       "      <td>2006Q4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10572</th>\n",
       "      <td>125</td>\n",
       "      <td>eps_fc</td>\n",
       "      <td>2017Q1</td>\n",
       "      <td>0.837</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23574</th>\n",
       "      <td>280</td>\n",
       "      <td>eps_fc</td>\n",
       "      <td>2012Q3</td>\n",
       "      <td>0.839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37262</th>\n",
       "      <td>443</td>\n",
       "      <td>eps_fc</td>\n",
       "      <td>2011Q3</td>\n",
       "      <td>0.386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39954</th>\n",
       "      <td>475</td>\n",
       "      <td>eps_fc</td>\n",
       "      <td>2012Q3</td>\n",
       "      <td>0.280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28909</th>\n",
       "      <td>344</td>\n",
       "      <td>eps_fc</td>\n",
       "      <td>2002Q2</td>\n",
       "      <td>0.773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38937</th>\n",
       "      <td>463</td>\n",
       "      <td>eps_fc</td>\n",
       "      <td>2010Q2</td>\n",
       "      <td>0.387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12143</th>\n",
       "      <td>144</td>\n",
       "      <td>eps_fc</td>\n",
       "      <td>2010Q4</td>\n",
       "      <td>0.208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30291</th>\n",
       "      <td>360</td>\n",
       "      <td>eps_fc</td>\n",
       "      <td>2011Q4</td>\n",
       "      <td>0.857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29262</th>\n",
       "      <td>348</td>\n",
       "      <td>eps_fc</td>\n",
       "      <td>2006Q3</td>\n",
       "      <td>0.206</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       firm_id feature    term  value\n",
       "41191      490  eps_fc  2006Q4    NaN\n",
       "10572      125  eps_fc  2017Q1  0.837\n",
       "23574      280  eps_fc  2012Q3  0.839\n",
       "37262      443  eps_fc  2011Q3  0.386\n",
       "39954      475  eps_fc  2012Q3  0.280\n",
       "28909      344  eps_fc  2002Q2  0.773\n",
       "38937      463  eps_fc  2010Q2  0.387\n",
       "12143      144  eps_fc  2010Q4  0.208\n",
       "30291      360  eps_fc  2011Q4  0.857\n",
       "29262      348  eps_fc  2006Q3  0.206"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eps_fc_clean.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 42420 entries, 0 to 42419\n",
      "Data columns (total 4 columns):\n",
      "firm_id    42420 non-null int64\n",
      "feature    42420 non-null object\n",
      "term       42420 non-null period[Q-DEC]\n",
      "value      35365 non-null float64\n",
      "dtypes: float64(1), int64(1), object(1), period[Q-DEC](1)\n",
      "memory usage: 1.3+ MB\n"
     ]
    }
   ],
   "source": [
    "eps_fc_clean.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify 505 firms in both\n",
    "eps_fc_clean.firm_id.nunique() == 505 & eps_act_clean.firm_id.nunique() == 505"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#examine 'feature' in both\n",
    "len(eps_fc_clean.feature.unique()) == 1 & len(eps_act_clean.feature.unique()) == 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(84, 84)"
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify 84 unique term values: 21 years x 4 quarters\n",
    "len(eps_fc_clean.term.unique()), len(eps_act_clean.term.unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- ### `eod_act`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {},
   "outputs": [],
   "source": [
    "#define column order\n",
    "feats_col_order = ['firm_id', 'feature', 'date', 'term', 'value']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {},
   "outputs": [],
   "source": [
    "#melt firm ids\n",
    "eod_act_clean = eod_act_clean.melt(id_vars = ['date', 'term'],\n",
    "                  var_name = 'firm_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [],
   "source": [
    "#reassign firm_ids\n",
    "eod_act_clean['firm_id'] = eod_act_clean['firm_id'].map(firm_ids_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [],
   "source": [
    "#add feature column\n",
    "eod_act_clean = eod_act_clean.assign(feature = 'eod_act')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [],
   "source": [
    "#reorder columns \n",
    "eod_act_clean = eod_act_clean[feats_col_order]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "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>firm_id</th>\n",
       "      <th>feature</th>\n",
       "      <th>date</th>\n",
       "      <th>term</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7277</th>\n",
       "      <td>86</td>\n",
       "      <td>eod_act</td>\n",
       "      <td>2012-06-29</td>\n",
       "      <td>2012Q2</td>\n",
       "      <td>32.7800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39502</th>\n",
       "      <td>470</td>\n",
       "      <td>eod_act</td>\n",
       "      <td>2004-09-30</td>\n",
       "      <td>2004Q3</td>\n",
       "      <td>50.9500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>701</th>\n",
       "      <td>8</td>\n",
       "      <td>eod_act</td>\n",
       "      <td>2006-06-30</td>\n",
       "      <td>2006Q2</td>\n",
       "      <td>28.3200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14196</th>\n",
       "      <td>169</td>\n",
       "      <td>eod_act</td>\n",
       "      <td>1999-03-31</td>\n",
       "      <td>1999Q1</td>\n",
       "      <td>291.5625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18284</th>\n",
       "      <td>217</td>\n",
       "      <td>eod_act</td>\n",
       "      <td>2013-03-28</td>\n",
       "      <td>2013Q1</td>\n",
       "      <td>43.9400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       firm_id  feature       date    term     value\n",
       "7277        86  eod_act 2012-06-29  2012Q2   32.7800\n",
       "39502      470  eod_act 2004-09-30  2004Q3   50.9500\n",
       "701          8  eod_act 2006-06-30  2006Q2   28.3200\n",
       "14196      169  eod_act 1999-03-31  1999Q1  291.5625\n",
       "18284      217  eod_act 2013-03-28  2013Q1   43.9400"
      ]
     },
     "execution_count": 178,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eod_act_clean.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 42420 entries, 0 to 42419\n",
      "Data columns (total 5 columns):\n",
      "firm_id    42420 non-null int64\n",
      "feature    42420 non-null object\n",
      "date       42420 non-null datetime64[ns]\n",
      "term       42420 non-null period[Q-DEC]\n",
      "value      35499 non-null float64\n",
      "dtypes: datetime64[ns](1), float64(1), int64(1), object(1), period[Q-DEC](1)\n",
      "memory usage: 1.6+ MB\n"
     ]
    }
   ],
   "source": [
    "eod_act_clean.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 180,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify 505 firms \n",
    "eod_act_clean.firm_id.nunique() == 505 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['eod_act'], dtype=object)"
      ]
     },
     "execution_count": 181,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#examine 'feature' column\n",
    "eod_act_clean.feature.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(True, True)"
      ]
     },
     "execution_count": 182,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify 84 unique dates and terms\n",
    "len(eod_act_clean.term.unique()) == 84, len(eod_act_clean.date.unique()) == 84"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- ### `eps_fc_terms`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [],
   "source": [
    "#melt firm ids\n",
    "eps_fc_terms_clean = eps_fc_terms_clean.melt(id_vars = ['forecast_made', 'term'],\n",
    "                       var_name = 'firm_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {},
   "outputs": [],
   "source": [
    "#reassign firm ids\n",
    "eps_fc_terms_clean['firm_id'] = eps_fc_terms_clean['firm_id'].map(firm_ids_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "metadata": {},
   "outputs": [],
   "source": [
    "#rename forecast_made to date\n",
    "eps_fc_terms_clean = eps_fc_terms_clean.rename(columns = {'forecast_made' : 'date'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {},
   "outputs": [],
   "source": [
    "#add feature column\n",
    "eps_fc_terms_clean = eps_fc_terms_clean.assign(feature = 'eps_fc_terms')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "metadata": {},
   "outputs": [],
   "source": [
    "#reorder columns\n",
    "eps_fc_terms_clean = eps_fc_terms_clean[feats_col_order]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "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>firm_id</th>\n",
       "      <th>feature</th>\n",
       "      <th>date</th>\n",
       "      <th>term</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12566</th>\n",
       "      <td>157</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "      <td>2001-04-01</td>\n",
       "      <td>2001Q3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6242</th>\n",
       "      <td>78</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "      <td>2000-04-01</td>\n",
       "      <td>2000Q3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6893</th>\n",
       "      <td>86</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "      <td>2003-01-01</td>\n",
       "      <td>2003Q2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8928</th>\n",
       "      <td>111</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "      <td>2011-10-01</td>\n",
       "      <td>2012Q1</td>\n",
       "      <td>1.531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38726</th>\n",
       "      <td>484</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "      <td>2001-04-01</td>\n",
       "      <td>2001Q3</td>\n",
       "      <td>0.360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14605</th>\n",
       "      <td>182</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "      <td>2011-01-01</td>\n",
       "      <td>2011Q2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36864</th>\n",
       "      <td>460</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "      <td>2015-10-01</td>\n",
       "      <td>2016Q1</td>\n",
       "      <td>1.442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16887</th>\n",
       "      <td>211</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "      <td>2001-07-01</td>\n",
       "      <td>2001Q4</td>\n",
       "      <td>0.050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11579</th>\n",
       "      <td>144</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "      <td>2014-07-01</td>\n",
       "      <td>2014Q4</td>\n",
       "      <td>0.218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27860</th>\n",
       "      <td>348</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "      <td>2004-10-01</td>\n",
       "      <td>2005Q1</td>\n",
       "      <td>0.126</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       firm_id       feature       date    term  value\n",
       "12566      157  eps_fc_terms 2001-04-01  2001Q3    NaN\n",
       "6242        78  eps_fc_terms 2000-04-01  2000Q3    NaN\n",
       "6893        86  eps_fc_terms 2003-01-01  2003Q2    NaN\n",
       "8928       111  eps_fc_terms 2011-10-01  2012Q1  1.531\n",
       "38726      484  eps_fc_terms 2001-04-01  2001Q3  0.360\n",
       "14605      182  eps_fc_terms 2011-01-01  2011Q2    NaN\n",
       "36864      460  eps_fc_terms 2015-10-01  2016Q1  1.442\n",
       "16887      211  eps_fc_terms 2001-07-01  2001Q4  0.050\n",
       "11579      144  eps_fc_terms 2014-07-01  2014Q4  0.218\n",
       "27860      348  eps_fc_terms 2004-10-01  2005Q1  0.126"
      ]
     },
     "execution_count": 188,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eps_fc_terms_clean.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "505"
      ]
     },
     "execution_count": 189,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify 505 firms\n",
    "eps_fc_terms_clean.firm_id.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['eps_fc_terms'], dtype=object)"
      ]
     },
     "execution_count": 190,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#examine 'feature' column\n",
    "eps_fc_terms_clean.feature.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 191,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify 80 unique terms (1999 not recorded)\n",
    "eps_fc_terms_clean.term.nunique() == 80"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- ### `features.csv`\n",
    "\n",
    "Now's the time to bring everything together!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "metadata": {},
   "outputs": [],
   "source": [
    "feat_array = [eps_fc_clean, eps_act_clean, eod_act_clean, eps_fc_terms_clean]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "metadata": {},
   "outputs": [],
   "source": [
    "#concat to prepare for outer left merge\n",
    "eod_act_eps_terms = pd.concat([eod_act_clean, eps_fc_terms_clean], join = 'outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "metadata": {},
   "outputs": [],
   "source": [
    "#outer join\n",
    "features = pd.concat([eod_act_eps_terms, eps_fc_clean], join = 'outer', sort = False)\n",
    "features = pd.concat([features, eps_act_clean], join = 'outer', sort = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "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>firm_id</th>\n",
       "      <th>feature</th>\n",
       "      <th>date</th>\n",
       "      <th>term</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20114</th>\n",
       "      <td>239</td>\n",
       "      <td>eod_act</td>\n",
       "      <td>2008-09-30</td>\n",
       "      <td>2008Q3</td>\n",
       "      <td>41.2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6039</th>\n",
       "      <td>75</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "      <td>2009-07-01</td>\n",
       "      <td>2009Q4</td>\n",
       "      <td>0.8170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16686</th>\n",
       "      <td>208</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "      <td>2011-04-01</td>\n",
       "      <td>2011Q3</td>\n",
       "      <td>4.2750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34534</th>\n",
       "      <td>431</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "      <td>2013-04-01</td>\n",
       "      <td>2013Q3</td>\n",
       "      <td>0.4350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2766</th>\n",
       "      <td>32</td>\n",
       "      <td>eod_act</td>\n",
       "      <td>2018-09-28</td>\n",
       "      <td>2018Q3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11616</th>\n",
       "      <td>138</td>\n",
       "      <td>eps_act</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2005Q1</td>\n",
       "      <td>0.5775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6896</th>\n",
       "      <td>82</td>\n",
       "      <td>eps_fc</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2001Q1</td>\n",
       "      <td>0.2540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12549</th>\n",
       "      <td>149</td>\n",
       "      <td>eps_act</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2007Q2</td>\n",
       "      <td>0.4200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35031</th>\n",
       "      <td>437</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "      <td>2017-07-01</td>\n",
       "      <td>2017Q4</td>\n",
       "      <td>1.5080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33247</th>\n",
       "      <td>415</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "      <td>2011-07-01</td>\n",
       "      <td>2011Q4</td>\n",
       "      <td>0.1330</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       firm_id       feature       date    term    value\n",
       "20114      239       eod_act 2008-09-30  2008Q3  41.2000\n",
       "6039        75  eps_fc_terms 2009-07-01  2009Q4   0.8170\n",
       "16686      208  eps_fc_terms 2011-04-01  2011Q3   4.2750\n",
       "34534      431  eps_fc_terms 2013-04-01  2013Q3   0.4350\n",
       "2766        32       eod_act 2018-09-28  2018Q3      NaN\n",
       "11616      138       eps_act        NaT  2005Q1   0.5775\n",
       "6896        82        eps_fc        NaT  2001Q1   0.2540\n",
       "12549      149       eps_act        NaT  2007Q2   0.4200\n",
       "35031      437  eps_fc_terms 2017-07-01  2017Q4   1.5080\n",
       "33247      415  eps_fc_terms 2011-07-01  2011Q4   0.1330"
      ]
     },
     "execution_count": 195,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "features.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 196,
   "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>firm_id</th>\n",
       "      <th>feature</th>\n",
       "      <th>date</th>\n",
       "      <th>term</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>30813</th>\n",
       "      <td>385</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "      <td>2003-01-01</td>\n",
       "      <td>2003Q2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40536</th>\n",
       "      <td>482</td>\n",
       "      <td>eps_act</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2011Q1</td>\n",
       "      <td>0.730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5098</th>\n",
       "      <td>60</td>\n",
       "      <td>eod_act</td>\n",
       "      <td>2013-09-30</td>\n",
       "      <td>2013Q3</td>\n",
       "      <td>13.800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23801</th>\n",
       "      <td>297</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>2010Q2</td>\n",
       "      <td>0.074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5203</th>\n",
       "      <td>65</td>\n",
       "      <td>eps_fc_terms</td>\n",
       "      <td>2000-07-01</td>\n",
       "      <td>2000Q4</td>\n",
       "      <td>0.192</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       firm_id       feature       date    term   value\n",
       "30813      385  eps_fc_terms 2003-01-01  2003Q2     NaN\n",
       "40536      482       eps_act        NaT  2011Q1   0.730\n",
       "5098        60       eod_act 2013-09-30  2013Q3  13.800\n",
       "23801      297  eps_fc_terms 2010-01-01  2010Q2   0.074\n",
       "5203        65  eps_fc_terms 2000-07-01  2000Q4   0.192"
      ]
     },
     "execution_count": 196,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "features.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 167660 entries, 0 to 42419\n",
      "Data columns (total 5 columns):\n",
      "firm_id    167660 non-null int64\n",
      "feature    167660 non-null object\n",
      "date       82820 non-null datetime64[ns]\n",
      "term       167660 non-null period[Q-DEC]\n",
      "value      141079 non-null float64\n",
      "dtypes: datetime64[ns](1), float64(1), int64(1), object(1), period[Q-DEC](1)\n",
      "memory usage: 7.7+ MB\n"
     ]
    }
   ],
   "source": [
    "features.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 198,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify 505 terms\n",
    "features.firm_id.nunique() == 505"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['eod_act', 'eps_fc_terms', 'eps_fc', 'eps_act'], dtype=object)"
      ]
     },
     "execution_count": 199,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#examine 'feature' column\n",
    "features.feature.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(True,)"
      ]
     },
     "execution_count": 200,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify 84 term ranges\n",
    "features.term.nunique() == 84, "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "164"
      ]
     },
     "execution_count": 201,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify 164 date values: 84 ones from eod_act, and 80 from eps_fc_terms\n",
    "features.date.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# III) Store Data <a id = \"store\"></a>\n",
    "\n",
    "**Disclaimer:** I decided to put the following CSVs under the ***./data/clean/averages/components*** directory because they form *part* of the main CSVs under the ***./data/clean/averages*** directory:\n",
    "- `quarter-avgs-eod-act.csv`\n",
    "- `quarter-avgs-eps-act.csv`\n",
    "- `quarter-avgs-eps-fc.csv`\n",
    "- `quarter-avgs-eps-fc-terms.csv`\n",
    "\n",
    "> The above are all components of the `quarter-avgs.csv` under **/averages/**\n",
    "\n",
    "- `yearly-avgs-eod-act.csv`\n",
    "- `yearly-avgs-eps-act.csv`\n",
    "- `yearly-avgs-eps-fc.csv`\n",
    "- `yearly-avgs-eps-fc-terms.csv`\n",
    "\n",
    "> The above are all components of the `yearly-avgs.csv` under **/averages/**\n",
    "\n",
    "**Additionally, I decided to keep `avgs.csv` under the *./data/clean/averages/* directory.** \n",
    "\n",
    "## Firms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "metadata": {},
   "outputs": [],
   "source": [
    "firm_ids.to_csv(PATH_CLEAN + 'firms.csv', encoding = 'utf-8', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Averages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "metadata": {},
   "outputs": [],
   "source": [
    "#twenty-year-averages\n",
    "df_twenty_year_avgs.to_csv(PATH_CLEAN_AVGS + 'twenty-year-avgs.csv', encoding = 'utf-8', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "metadata": {},
   "outputs": [],
   "source": [
    "#yearly averagas\n",
    "df_yearly_avgs.to_csv(PATH_CLEAN_AVGS + 'yearly-avgs.csv', encoding = 'utf-8', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "metadata": {},
   "outputs": [],
   "source": [
    "#quarterly averages\n",
    "df_quarter_avgs.to_csv(PATH_CLEAN_AVGS + 'quarter-avgs.csv', encoding = 'utf-8', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "metadata": {},
   "outputs": [],
   "source": [
    "#all averages\n",
    "all_avgs.to_csv(PATH_CLEAN_AVGS + 'avgs.csv', encoding = 'utf-8', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**NOTE:** For the twenty year, yearly, and quarterly datasets, I stored their old `df_` version instead of their new clean versions. \n",
    "> This is because their old versions are stored more efficiently as ***standalone*** CSVs, and were only modified to fit the larger `all_avgs` DataFrame.\n",
    "\n",
    "### Components"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "metadata": {},
   "outputs": [],
   "source": [
    "#quarterly averages\n",
    "store_df(PATH_CLEAN_AVGS_SUB, df_eps_fc_quarter_avgs, 'quarter-avgs-eps-fc.csv')\n",
    "store_df(PATH_CLEAN_AVGS_SUB, df_eps_act_quarter_avgs, 'quarter-avgs-eps-act.csv')\n",
    "store_df(PATH_CLEAN_AVGS_SUB, df_eod_act_quarter_avgs, 'quarter-avgs-eod-act.csv')\n",
    "store_df(PATH_CLEAN_AVGS_SUB, df_eps_fc_terms_quarter_avgs, 'quarter-avgs-eps-fc-terms.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "metadata": {},
   "outputs": [],
   "source": [
    "#yearly averages\n",
    "store_df(PATH_CLEAN_AVGS_SUB, df_eps_fc_yearly_avgs, 'yearly-avgs-eps-fc.csv')\n",
    "store_df(PATH_CLEAN_AVGS_SUB, df_eps_act_yearly_avgs, 'yearly-avgs-eps-act.csv')\n",
    "store_df(PATH_CLEAN_AVGS_SUB, df_eod_act_yearly_avgs, 'yearly-avgs-eod-act.csv')\n",
    "store_df(PATH_CLEAN_AVGS_SUB, df_eps_fc_terms_yearly_avgs, 'yearly-avgs-eps-fc-terms.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 209,
   "metadata": {},
   "outputs": [],
   "source": [
    "eps_fc_clean.to_csv(PATH_CLEAN + 'eps-fc.csv')\n",
    "eps_act_clean.to_csv(PATH_CLEAN + 'eps-act.csv')\n",
    "eod_act_clean.to_csv(PATH_CLEAN + 'eod-act.csv')\n",
    "eps_fc_terms_clean.to_csv(PATH_CLEAN + 'eps-fc-terms.csv')\n",
    "\n",
    "features.to_csv(PATH_CLEAN + 'features.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# IV) Notes\n",
    "\n",
    "\n",
    "- When dealing with the CSVs depicting averages, we need to keep in mind **missing data** in our interpretations.    \n",
    "- Keep in mind that `eod_act` and `eps_fc_terms` is based on **calendar years** not fiscal years."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 210,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 210,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#convert notebook to HTML\n",
    "from subprocess import call\n",
    "call(['python', '-m', 'nbconvert', 'data_cleaning.ipynb'])"
   ]
  }
 ],
 "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
}