4_calculate_profitability.ipynb

{
 "nbformat": 4,
 "nbformat_minor": 0,
 "metadata": {
  "colab": {
   "provenance": [],
   "collapsed_sections": []
  },
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3"
  },
  "language_info": {
   "name": "python"
  }
 },
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# 4. Calculate Profitability\n",
    "-------------------\n",
    "Group 3 , September 24, 2022\n",
    "1. Gezhi Cheng, \n",
    "2. Haowei Lee, \n",
    "3. Ziyi Liu, \n",
    "4. VS Chaitanya Madduri\n",
    "\n",
    "> <i>Description: The program in this notebook\n",
    "- conducts data cleaning on wrds data\n",
    "- merges company informations with processed wrds data\n",
    "- calculates profitability and percentage change of profitability on consecutive three years\n",
    "</i>\n",
    "\n",
    "\n",
    "<div class=\"alert alert-block alert-info\">\n",
    "    <b>Tip:</b> # Please run this notebook in the colab . \n",
    "</div> \n",
    "\n",
    "### Pre requisites: \n",
    "1. And add the shortcut of the drive link :https://drive.google.com/drive/folders/1X4UdGsQiHVWSr63FRiz8rwOuWW5Ua8uI?usp=sharing to your personal drive.\n",
    "\n",
    "\n",
    "Due to the huge files we have used our personal google drive folders to save the files.\n",
    "\n",
    "Files:\n",
    "- wrds_data.csv - Financial data downloaded from [WRDS](https://wrds-www.wharton.upenn.edu/login/?next=/pages/get-data/center-research-security-prices-crsp/annual-update/crspcompustat-merged/fundamentals-annual/)\n",
    "- Company_Details_analysis.csv - Data storing company general informations\n",
    "\n",
    "### Output files:\n",
    "\n",
    "Files:\n",
    "profitability_data.csv - Data storing profitability and percentage change in profitablilty\n"
   ],
   "metadata": {
    "id": "PJILB-vaBsBN"
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Load dependencies"
   ],
   "metadata": {
    "id": "r2kcrAM53osk"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "# Mount the program to the folder on Google Drive to get access to other files on cloud\n",
    "from google.colab import drive\n",
    "drive.mount('/content/drive')\n",
    "\n",
    "# import python packages\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from typing import List\n",
    "import warnings"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "r0T8SzKNSMWA",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1665599140843,
     "user_tz": -120,
     "elapsed": 2028,
     "user": {
      "displayName": "Haowei Lee",
      "userId": "07102738850345521476"
     }
    },
    "outputId": "0f17ca44-6252-4830-af3e-80cb21942089"
   },
   "execution_count": 1,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Drive already mounted at /content/drive; to attempt to forcibly remount, call drive.mount(\"/content/drive\", force_remount=True).\n"
     ]
    }
   ]
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 1. Preprocess financial data from WRDS\n",
    "\n"
   ],
   "metadata": {
    "id": "lhuUUh9U4RP5"
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 1.1 Define constants"
   ],
   "metadata": {
    "id": "TFiR-32IFxJF"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "# Define financial terms as CONSTANTS\n",
    "GROSS_PROFIT = \"Gross Profit (Loss)\"\n",
    "REVENUE = \"Revenue - Total\"\n",
    "GROSS_PROFIT_MARGIN = \"Gross Profit Margin\"\n",
    "NET_INCOME = \"Net Income (Loss)\"\n",
    "NET_PROFIT_MARGIN = \"Net Profit Margin\"\n",
    "OPERATING_EXPENSES = \"Operating Expenses Total\"\n",
    "OPERATING_PROFIT_MARGIN = \"Operating Profit Margin\"\n",
    "ASSETS = \"Assets - Total\"\n",
    "EQUITY = \"Stockholders Equity - Total\"\n",
    "RETAINED_EARNINGS = \"Retained Earnings\"\n",
    "ROA = \"ROA\"\n",
    "ROE = \"ROE\"\n",
    "\n",
    "# Define tha map for renaming a dataframe\n",
    "RENAMING_MAP = {\n",
    "                \"CONM\": \"Company Name\",\n",
    "                \"CIK\": \"CIK Number\",\n",
    "                \"ACT\": \"Current Assets - Total\",\n",
    "                \"AT\": \"Assets - Total\",\n",
    "                \"CH\": \"Cash\",\n",
    "                \"INVOFS\": \"Inventory/Stock - Other\",\n",
    "                \"LCT\": \"Current Liabilities - Total\",\n",
    "                \"LT\": \"Liabilities - Total\",\n",
    "                \"SEQ\": \"Stockholders' Equity - Total\",\n",
    "                \"COGS\": \"Cost of Goods Sold\",\n",
    "                \"EPSPI\": \"Earnings Per Share (Basic) Including Extraordinary Items\",\n",
    "                \"EPSPX\": \"Earnings Per Share (Basic) Excluding Extraordinary Items\",\n",
    "                \"GP\": \"Gross Profit (Loss)\",\n",
    "                \"NI\": \"Net Income (Loss)\",\n",
    "                \"OPEPS\": \"Earnings Per Share from Operations\",\n",
    "                \"OPITI\": \"Operating Income - Total\",\n",
    "                \"REVT\": \"Revenue - Total\",\n",
    "                \"SALE\": \"Sales/Turnover (Net)\",\n",
    "                \"OANCF\": \"Operating Activities Net Cash Flow\",\n",
    "                \"UNWCC\": \"Working Capital (Use) - Increase (Decrease) (Cash Flow)\",\n",
    "                \"ACQINVT\": \"Acquired Assets - Inventory\",\n",
    "                \"DT\": \"Total Debt Including Current\",\n",
    "                \"FINCH\": \"Finance Division - Cash\",\n",
    "                \"TEQ\": \"Stockholders Equity - Total\",\n",
    "                \"XOPR\": \"Operating Expenses Total\",\n",
    "                \"RE\": \"Retained Earnings\"\n",
    "                }\n",
    "\n",
    "# make sure the key in the renamming map is in lower case\n",
    "RENAMING_MAP_LOWER = {k.lower(): v for k, v in RENAMING_MAP.items()}\n",
    "\n",
    "\n",
    "# define the path of the root folder and of the data\n",
    "DIR_PATH = \"/content/drive/MyDrive/SPM_files/\"  # take the current folder as the root folder\n",
    "WRDS_DATA_PATH = \"wrds_data.csv\"\n",
    "\n",
    "LIMIT = 3  # define the numbers of rows for data exploration"
   ],
   "metadata": {
    "id": "SDvHePDgS_XZ",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1665599140843,
     "user_tz": -120,
     "elapsed": 2,
     "user": {
      "displayName": "Haowei Lee",
      "userId": "07102738850345521476"
     }
    }
   },
   "execution_count": 2,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 1.2 Inspect WRDS data"
   ],
   "metadata": {
    "id": "C1_aS_YpFoP9"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "# read in the WRDS data\n",
    "df_wrds = pd.read_csv(DIR_PATH + WRDS_DATA_PATH)\n",
    "\n",
    "# rename column names with the map defined in Constants part\n",
    "df_wrds = df_wrds.rename(columns=RENAMING_MAP_LOWER)\n",
    "\n",
    "# inspect the dimension and the first few records of the wrds data\n",
    "print(df_wrds.shape)\n",
    "df_wrds.head(LIMIT)"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 515
    },
    "id": "xXlFv3w6UXQ-",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1665599141286,
     "user_tz": -120,
     "elapsed": 444,
     "user": {
      "displayName": "Haowei Lee",
      "userId": "07102738850345521476"
     }
    },
    "outputId": "32f02fc6-73dc-42d0-dd1b-206615150da6"
   },
   "execution_count": 3,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "(733, 37)\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "   GVKEY  datadate  fyear indfmt consol popsrc datafmt  \\\n",
       "0   1075  20111231   2011   INDL      C      D     STD   \n",
       "1   1075  20121231   2012   INDL      C      D     STD   \n",
       "2   1075  20131231   2013   INDL      C      D     STD   \n",
       "\n",
       "                 Company Name curcd  Acquired Assets - Inventory  ...  \\\n",
       "0  PINNACLE WEST CAPITAL CORP   USD                          NaN  ...   \n",
       "1  PINNACLE WEST CAPITAL CORP   USD                          NaN  ...   \n",
       "2  PINNACLE WEST CAPITAL CORP   USD                          NaN  ...   \n",
       "\n",
       "   Operating Income - Total  Retained Earnings  Revenue - Total  \\\n",
       "0                       NaN           1382.320         3241.379   \n",
       "1                       NaN           1510.094         3301.804   \n",
       "2                       NaN           1707.220         3454.628   \n",
       "\n",
       "   Sales/Turnover (Net)  Stockholders' Equity - Total  \\\n",
       "0              3241.379                      3821.850   \n",
       "1              3301.804                      3972.806   \n",
       "2              3454.628                      4194.470   \n",
       "\n",
       "   Stockholders Equity - Total  \\\n",
       "0                     3930.586   \n",
       "1                     4102.289   \n",
       "2                     4340.460   \n",
       "\n",
       "   Working Capital (Use) - Increase (Decrease) (Cash Flow)  \\\n",
       "0                                                NaN         \n",
       "1                                                NaN         \n",
       "2                                                NaN         \n",
       "\n",
       "   Operating Expenses Total  CIK Number  costat  \n",
       "0                  2067.817      764622       A  \n",
       "1                  2045.713      764622       A  \n",
       "2                  2192.597      764622       A  \n",
       "\n",
       "[3 rows x 37 columns]"
      ],
      "text/html": [
       "\n",
       "  <div id=\"df-9630d5aa-35ad-40d6-bd0c-68acdfad6355\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <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>GVKEY</th>\n",
       "      <th>datadate</th>\n",
       "      <th>fyear</th>\n",
       "      <th>indfmt</th>\n",
       "      <th>consol</th>\n",
       "      <th>popsrc</th>\n",
       "      <th>datafmt</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>curcd</th>\n",
       "      <th>Acquired Assets - Inventory</th>\n",
       "      <th>...</th>\n",
       "      <th>Operating Income - Total</th>\n",
       "      <th>Retained Earnings</th>\n",
       "      <th>Revenue - Total</th>\n",
       "      <th>Sales/Turnover (Net)</th>\n",
       "      <th>Stockholders' Equity - Total</th>\n",
       "      <th>Stockholders Equity - Total</th>\n",
       "      <th>Working Capital (Use) - Increase (Decrease) (Cash Flow)</th>\n",
       "      <th>Operating Expenses Total</th>\n",
       "      <th>CIK Number</th>\n",
       "      <th>costat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1075</td>\n",
       "      <td>20111231</td>\n",
       "      <td>2011</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>PINNACLE WEST CAPITAL CORP</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1382.320</td>\n",
       "      <td>3241.379</td>\n",
       "      <td>3241.379</td>\n",
       "      <td>3821.850</td>\n",
       "      <td>3930.586</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2067.817</td>\n",
       "      <td>764622</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1075</td>\n",
       "      <td>20121231</td>\n",
       "      <td>2012</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>PINNACLE WEST CAPITAL CORP</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1510.094</td>\n",
       "      <td>3301.804</td>\n",
       "      <td>3301.804</td>\n",
       "      <td>3972.806</td>\n",
       "      <td>4102.289</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2045.713</td>\n",
       "      <td>764622</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1075</td>\n",
       "      <td>20131231</td>\n",
       "      <td>2013</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>PINNACLE WEST CAPITAL CORP</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1707.220</td>\n",
       "      <td>3454.628</td>\n",
       "      <td>3454.628</td>\n",
       "      <td>4194.470</td>\n",
       "      <td>4340.460</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2192.597</td>\n",
       "      <td>764622</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 37 columns</p>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-9630d5aa-35ad-40d6-bd0c-68acdfad6355')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-9630d5aa-35ad-40d6-bd0c-68acdfad6355 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-9630d5aa-35ad-40d6-bd0c-68acdfad6355');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ]
     },
     "metadata": {},
     "execution_count": 3
    }
   ]
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 1.3 Calculate finantial indices"
   ],
   "metadata": {
    "id": "19lFGtepF27J"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "# Define functions to calculate financial indices\n",
    "def get_gross_profit_margin(df):\n",
    "    df[GROSS_PROFIT_MARGIN] = df[GROSS_PROFIT] / df[REVENUE]\n",
    "    return df\n",
    "\n",
    "def get_net_profit_margin(df):\n",
    "    df[NET_PROFIT_MARGIN] = df[NET_INCOME] / df[REVENUE]\n",
    "    return df\n",
    "\n",
    "def get_operating_profit_margin(df):\n",
    "    df[OPERATING_PROFIT_MARGIN] = (df[GROSS_PROFIT] - df[OPERATING_EXPENSES]) / df[REVENUE]\n",
    "    return df\n",
    "\n",
    "def get_roa(df):\n",
    "    df[ROA] = df[NET_INCOME] / df[ASSETS]\n",
    "    return df\n",
    "\n",
    "def get_roe(df):\n",
    "    df[ROE] = df[NET_INCOME] / (df[EQUITY] + df[RETAINED_EARNINGS])\n",
    "    return df"
   ],
   "metadata": {
    "id": "4INgGLbGwo27",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1665599141286,
     "user_tz": -120,
     "elapsed": 8,
     "user": {
      "displayName": "Haowei Lee",
      "userId": "07102738850345521476"
     }
    }
   },
   "execution_count": 4,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# apply the functions define above to get finantial indices\n",
    "df_wrds.pipe(get_gross_profit_margin)\\\n",
    "       .pipe(get_net_profit_margin)\\\n",
    "       .pipe(get_operating_profit_margin)\\\n",
    "       .pipe(get_roa)\\\n",
    "       .pipe(get_roe)\n",
    "\n",
    "# inspect the data\n",
    "df_wrds.head(LIMIT)"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 497
    },
    "id": "uV5hydB7woua",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1665599141286,
     "user_tz": -120,
     "elapsed": 7,
     "user": {
      "displayName": "Haowei Lee",
      "userId": "07102738850345521476"
     }
    },
    "outputId": "9c6c8a14-5001-4622-d926-500f49904636"
   },
   "execution_count": 5,
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "   GVKEY  datadate  fyear indfmt consol popsrc datafmt  \\\n",
       "0   1075  20111231   2011   INDL      C      D     STD   \n",
       "1   1075  20121231   2012   INDL      C      D     STD   \n",
       "2   1075  20131231   2013   INDL      C      D     STD   \n",
       "\n",
       "                 Company Name curcd  Acquired Assets - Inventory  ...  \\\n",
       "0  PINNACLE WEST CAPITAL CORP   USD                          NaN  ...   \n",
       "1  PINNACLE WEST CAPITAL CORP   USD                          NaN  ...   \n",
       "2  PINNACLE WEST CAPITAL CORP   USD                          NaN  ...   \n",
       "\n",
       "   Stockholders Equity - Total  \\\n",
       "0                     3930.586   \n",
       "1                     4102.289   \n",
       "2                     4340.460   \n",
       "\n",
       "   Working Capital (Use) - Increase (Decrease) (Cash Flow)  \\\n",
       "0                                                NaN         \n",
       "1                                                NaN         \n",
       "2                                                NaN         \n",
       "\n",
       "   Operating Expenses Total  CIK Number  costat  Gross Profit Margin  \\\n",
       "0                  2067.817      764622       A             0.362056   \n",
       "1                  2045.713      764622       A             0.380426   \n",
       "2                  2192.597      764622       A             0.365316   \n",
       "\n",
       "   Net Profit Margin  Operating Profit Margin       ROA       ROE  \n",
       "0           0.104731                -0.275887  0.025892  0.063896  \n",
       "1           0.115556                -0.239149  0.028517  0.067982  \n",
       "2           0.117545                -0.269368  0.030060  0.067145  \n",
       "\n",
       "[3 rows x 42 columns]"
      ],
      "text/html": [
       "\n",
       "  <div id=\"df-5c2fd9e9-16a6-4240-8c40-60b237b4896d\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <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>GVKEY</th>\n",
       "      <th>datadate</th>\n",
       "      <th>fyear</th>\n",
       "      <th>indfmt</th>\n",
       "      <th>consol</th>\n",
       "      <th>popsrc</th>\n",
       "      <th>datafmt</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>curcd</th>\n",
       "      <th>Acquired Assets - Inventory</th>\n",
       "      <th>...</th>\n",
       "      <th>Stockholders Equity - Total</th>\n",
       "      <th>Working Capital (Use) - Increase (Decrease) (Cash Flow)</th>\n",
       "      <th>Operating Expenses Total</th>\n",
       "      <th>CIK Number</th>\n",
       "      <th>costat</th>\n",
       "      <th>Gross Profit Margin</th>\n",
       "      <th>Net Profit Margin</th>\n",
       "      <th>Operating Profit Margin</th>\n",
       "      <th>ROA</th>\n",
       "      <th>ROE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1075</td>\n",
       "      <td>20111231</td>\n",
       "      <td>2011</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>PINNACLE WEST CAPITAL CORP</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>3930.586</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2067.817</td>\n",
       "      <td>764622</td>\n",
       "      <td>A</td>\n",
       "      <td>0.362056</td>\n",
       "      <td>0.104731</td>\n",
       "      <td>-0.275887</td>\n",
       "      <td>0.025892</td>\n",
       "      <td>0.063896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1075</td>\n",
       "      <td>20121231</td>\n",
       "      <td>2012</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>PINNACLE WEST CAPITAL CORP</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>4102.289</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2045.713</td>\n",
       "      <td>764622</td>\n",
       "      <td>A</td>\n",
       "      <td>0.380426</td>\n",
       "      <td>0.115556</td>\n",
       "      <td>-0.239149</td>\n",
       "      <td>0.028517</td>\n",
       "      <td>0.067982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1075</td>\n",
       "      <td>20131231</td>\n",
       "      <td>2013</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>PINNACLE WEST CAPITAL CORP</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>4340.460</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2192.597</td>\n",
       "      <td>764622</td>\n",
       "      <td>A</td>\n",
       "      <td>0.365316</td>\n",
       "      <td>0.117545</td>\n",
       "      <td>-0.269368</td>\n",
       "      <td>0.030060</td>\n",
       "      <td>0.067145</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 42 columns</p>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-5c2fd9e9-16a6-4240-8c40-60b237b4896d')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-5c2fd9e9-16a6-4240-8c40-60b237b4896d button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-5c2fd9e9-16a6-4240-8c40-60b237b4896d');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ]
     },
     "metadata": {},
     "execution_count": 5
    }
   ]
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 2. Preprocess the data of companyies' details "
   ],
   "metadata": {
    "id": "RqdpKzEL9Y4Y"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "# Define CONSTANTS\n",
    "COMPANY_DATA_PATH = \"Company_Details_analysis.csv\"\n",
    "TARGET_COLUMNS = [\"Index_Key\", \"Class_number\",\"Std_Indust_Class\", 'Company_Name']\n",
    "\n",
    "# read in data of companies details\n",
    "df_company = pd.read_csv(DIR_PATH + COMPANY_DATA_PATH)\n",
    "\n",
    "# keep only useful columns for further processing\n",
    "df_company = df_company[TARGET_COLUMNS]\n",
    "df_company.head(LIMIT)"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 143
    },
    "id": "GG8Bu7JWcz3-",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1665599141287,
     "user_tz": -120,
     "elapsed": 8,
     "user": {
      "displayName": "Haowei Lee",
      "userId": "07102738850345521476"
     }
    },
    "outputId": "1819158d-10de-46a2-8851-641d959da310"
   },
   "execution_count": 6,
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "   Index_Key  Class_number                                Std_Indust_Class  \\\n",
       "0     755001          4931       ELECTRIC & OTHER SERVICES COMBINED [4931]   \n",
       "1    1069533          4923  NATURAL GAS TRANSMISSION & DISTRIBUTION [4923]   \n",
       "2    1126956          4924                 NATURAL GAS DISTRIBUTION [4924]   \n",
       "\n",
       "        Company_Name  \n",
       "0        UNITIL CORP  \n",
       "1  RGC RESOURCES INC  \n",
       "2          SPIRE INC  "
      ],
      "text/html": [
       "\n",
       "  <div id=\"df-157f3239-c911-4238-b559-9607cda793b8\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <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>Index_Key</th>\n",
       "      <th>Class_number</th>\n",
       "      <th>Std_Indust_Class</th>\n",
       "      <th>Company_Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>755001</td>\n",
       "      <td>4931</td>\n",
       "      <td>ELECTRIC &amp; OTHER SERVICES COMBINED [4931]</td>\n",
       "      <td>UNITIL CORP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1069533</td>\n",
       "      <td>4923</td>\n",
       "      <td>NATURAL GAS TRANSMISSION &amp; DISTRIBUTION [4923]</td>\n",
       "      <td>RGC RESOURCES INC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1126956</td>\n",
       "      <td>4924</td>\n",
       "      <td>NATURAL GAS DISTRIBUTION [4924]</td>\n",
       "      <td>SPIRE INC</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-157f3239-c911-4238-b559-9607cda793b8')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-157f3239-c911-4238-b559-9607cda793b8 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-157f3239-c911-4238-b559-9607cda793b8');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ]
     },
     "metadata": {},
     "execution_count": 6
    }
   ]
  },
  {
   "cell_type": "markdown",
   "source": [
    "##3. Merge data from WRDS and data of companies' details into one single dataframe"
   ],
   "metadata": {
    "id": "X0lbcl7n-FAH"
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 3.1 Merge and inspect the merged data"
   ],
   "metadata": {
    "id": "oKi58FzxJqCR"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "# to avoid irrelevant warning messages\n",
    "warnings.filterwarnings(\"ignore\")  \n",
    "\n",
    "# define the column at where to merge two dataframes\n",
    "MERGE_COLUMN = \"CIK Number\"\n",
    "\n",
    "# rename the column of company index for the convenience when merging two data\n",
    "df_company.rename(columns={\"Index_Key\": MERGE_COLUMN}, inplace=True)\n",
    "df_merged = df_wrds.merge(df_company, on =MERGE_COLUMN)\n",
    "\n",
    "# inspect the data and its dimensions\n",
    "print(df_merged.shape)\n",
    "df_merged.head(LIMIT)"
   ],
   "metadata": {
    "id": "BJsp8ttqdU2i",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1665599141287,
     "user_tz": -120,
     "elapsed": 8,
     "user": {
      "displayName": "Haowei Lee",
      "userId": "07102738850345521476"
     }
    },
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 446
    },
    "outputId": "24a9c854-19df-4b4a-8e0b-1d80276823ee"
   },
   "execution_count": 7,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "(733, 45)\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "   GVKEY  datadate  fyear indfmt consol popsrc datafmt  \\\n",
       "0   1075  20111231   2011   INDL      C      D     STD   \n",
       "1   1075  20121231   2012   INDL      C      D     STD   \n",
       "2   1075  20131231   2013   INDL      C      D     STD   \n",
       "\n",
       "                 Company Name curcd  Acquired Assets - Inventory  ...  \\\n",
       "0  PINNACLE WEST CAPITAL CORP   USD                          NaN  ...   \n",
       "1  PINNACLE WEST CAPITAL CORP   USD                          NaN  ...   \n",
       "2  PINNACLE WEST CAPITAL CORP   USD                          NaN  ...   \n",
       "\n",
       "   CIK Number  costat  Gross Profit Margin  Net Profit Margin  \\\n",
       "0      764622       A             0.362056           0.104731   \n",
       "1      764622       A             0.380426           0.115556   \n",
       "2      764622       A             0.365316           0.117545   \n",
       "\n",
       "   Operating Profit Margin       ROA       ROE  Class_number  \\\n",
       "0                -0.275887  0.025892  0.063896          4911   \n",
       "1                -0.239149  0.028517  0.067982          4911   \n",
       "2                -0.269368  0.030060  0.067145          4911   \n",
       "\n",
       "           Std_Indust_Class                Company_Name  \n",
       "0  ELECTRIC SERVICES [4911]  PINNACLE WEST CAPITAL CORP  \n",
       "1  ELECTRIC SERVICES [4911]  PINNACLE WEST CAPITAL CORP  \n",
       "2  ELECTRIC SERVICES [4911]  PINNACLE WEST CAPITAL CORP  \n",
       "\n",
       "[3 rows x 45 columns]"
      ],
      "text/html": [
       "\n",
       "  <div id=\"df-39c778d8-3372-4067-9c1b-20cddfb0e3a7\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <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>GVKEY</th>\n",
       "      <th>datadate</th>\n",
       "      <th>fyear</th>\n",
       "      <th>indfmt</th>\n",
       "      <th>consol</th>\n",
       "      <th>popsrc</th>\n",
       "      <th>datafmt</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>curcd</th>\n",
       "      <th>Acquired Assets - Inventory</th>\n",
       "      <th>...</th>\n",
       "      <th>CIK Number</th>\n",
       "      <th>costat</th>\n",
       "      <th>Gross Profit Margin</th>\n",
       "      <th>Net Profit Margin</th>\n",
       "      <th>Operating Profit Margin</th>\n",
       "      <th>ROA</th>\n",
       "      <th>ROE</th>\n",
       "      <th>Class_number</th>\n",
       "      <th>Std_Indust_Class</th>\n",
       "      <th>Company_Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1075</td>\n",
       "      <td>20111231</td>\n",
       "      <td>2011</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>PINNACLE WEST CAPITAL CORP</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>764622</td>\n",
       "      <td>A</td>\n",
       "      <td>0.362056</td>\n",
       "      <td>0.104731</td>\n",
       "      <td>-0.275887</td>\n",
       "      <td>0.025892</td>\n",
       "      <td>0.063896</td>\n",
       "      <td>4911</td>\n",
       "      <td>ELECTRIC SERVICES [4911]</td>\n",
       "      <td>PINNACLE WEST CAPITAL CORP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1075</td>\n",
       "      <td>20121231</td>\n",
       "      <td>2012</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>PINNACLE WEST CAPITAL CORP</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>764622</td>\n",
       "      <td>A</td>\n",
       "      <td>0.380426</td>\n",
       "      <td>0.115556</td>\n",
       "      <td>-0.239149</td>\n",
       "      <td>0.028517</td>\n",
       "      <td>0.067982</td>\n",
       "      <td>4911</td>\n",
       "      <td>ELECTRIC SERVICES [4911]</td>\n",
       "      <td>PINNACLE WEST CAPITAL CORP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1075</td>\n",
       "      <td>20131231</td>\n",
       "      <td>2013</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>PINNACLE WEST CAPITAL CORP</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>764622</td>\n",
       "      <td>A</td>\n",
       "      <td>0.365316</td>\n",
       "      <td>0.117545</td>\n",
       "      <td>-0.269368</td>\n",
       "      <td>0.030060</td>\n",
       "      <td>0.067145</td>\n",
       "      <td>4911</td>\n",
       "      <td>ELECTRIC SERVICES [4911]</td>\n",
       "      <td>PINNACLE WEST CAPITAL CORP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 45 columns</p>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-39c778d8-3372-4067-9c1b-20cddfb0e3a7')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-39c778d8-3372-4067-9c1b-20cddfb0e3a7 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-39c778d8-3372-4067-9c1b-20cddfb0e3a7');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ]
     },
     "metadata": {},
     "execution_count": 7
    }
   ]
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 3.2 Define functions and constants"
   ],
   "metadata": {
    "id": "fg47NXC4JxFC"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "# CONSTANTS\n",
    "PROFITABILITY = \"profitability\"\n",
    "PROFITABILITY_NEXT_1 = \"profitability_next_1\"\n",
    "PROFITABILITY_NEXT_2 = \"profitability_next_2\"\n",
    "PROFITABILITY_NEXT_3 = \"profitability_next_3\"\n",
    "PERCENTAGE_CHANGE_PROFITABILITY_NEXT_1 = \"change_next_1\"\n",
    "PERCENTAGE_CHANGE_PROFITABILITY_NEXT_2 = \"change_next_2\"\n",
    "PERCENTAGE_CHANGE_PROFITABILITY_NEXT_3 = \"change_next_3\"\n",
    "\n",
    "COMPANY = \"Company_Key\"\n",
    "COMP_NEXT_1 = \"company_next_1\"\n",
    "COMP_NEXT_2 = \"company_next_2\"\n",
    "COMP_NEXT_3 = \"company_next_3\"\n",
    "\n",
    "\n",
    "RENAMING_MAP_MERGED = {\n",
    "    \"CIK Number\": \"Company_Key\",\n",
    "    \"Company Name\": \"company\",\n",
    "    \"Std_Indust_Class\": \"industry\",\n",
    "    \"fyear\": \"year\",\n",
    "    \"Profitability\": \"profitability\"\n",
    "    }\n",
    "\n",
    "financial_columns = [GROSS_PROFIT, REVENUE, GROSS_PROFIT_MARGIN, NET_INCOME, NET_PROFIT_MARGIN, OPERATING_EXPENSES, ASSETS, EQUITY, RETAINED_EARNINGS, ROA, ROE]\n",
    "\n",
    "KEY_COLUMNS = [\"Company_Key\", \"year\", \"profitability\", \"Company_Name\"] + financial_columns"
   ],
   "metadata": {
    "id": "d-hjfpDCON9i",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1665599141287,
     "user_tz": -120,
     "elapsed": 7,
     "user": {
      "displayName": "Haowei Lee",
      "userId": "07102738850345521476"
     }
    }
   },
   "execution_count": 8,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# define functions to calculate profitability and percentage changes\n",
    "def get_profitability(df, ratio_columns:List = [OPERATING_PROFIT_MARGIN, NET_PROFIT_MARGIN, ROA, ROE, GROSS_PROFIT_MARGIN]):\n",
    "    \"\"\"\n",
    "    This function calculates the proftability by taking average on given columns of financial ratios\n",
    "    input: \n",
    "        df: pd.DataFrame\n",
    "        ratio_columns: the specified columns of financial ratio\n",
    "    return:\n",
    "        original dataframe with a new profitability column\n",
    "    \"\"\"\n",
    "    df_ratios = df.copy()[ratio_columns]\n",
    "    df_ratios[PROFITABILITY] = df_ratios.sum(axis=1) / len(ratio_columns)\n",
    "    df[PROFITABILITY] = df_ratios[PROFITABILITY]\n",
    "\n",
    "    return df\n",
    "\n",
    "def get_key_columns(df, renaming_map: dict, key_col: List):\n",
    "    \"\"\"\n",
    "    This function renames columns of given dataframe and keep only relevant columns\n",
    "    input: \n",
    "        df: pd.DataFrame\n",
    "        renaming_map: the mapper for renaming\n",
    "        key_col: the specified columns to keep\n",
    "    return:\n",
    "        dataframe with renamed and relevant columns\n",
    "    \"\"\"\n",
    "    df = df.rename(columns=renaming_map)\n",
    "    df_key_col = df.copy()[key_col]\n",
    "\n",
    "    return df_key_col\n",
    "\n",
    "def reorder_df(df, columns = ['Company_Key', 'year']):\n",
    "    \"\"\"\n",
    "    This function reorders the records by given columns. By default, the function\n",
    "    reorders by columns 'Company_Key' and 'year'\n",
    "    input: \n",
    "        df: pd.DataFrame\n",
    "        columns: the specified columns to reorder\n",
    "    return:\n",
    "        dataframe with reordered recoreds\n",
    "    \"\"\"\n",
    "    \n",
    "    return df.sort_values(columns)\n",
    "\n",
    "def get_percentage_change(df, n_next_1:int = -1, n_next_2:int = -2, n_next_3:int = -3):\n",
    "    \"\"\"\n",
    "    n: int - shift by number n of rows\n",
    "    \"\"\"\n",
    "\n",
    "    df[PROFITABILITY_NEXT_1] = df[PROFITABILITY].shift(n_next_1)  # shift by n years\n",
    "    df[PROFITABILITY_NEXT_2] = df[PROFITABILITY].shift(n_next_2)  # shift by n years\n",
    "    df[PROFITABILITY_NEXT_3] = df[PROFITABILITY].shift(n_next_3)  # shift by n years\n",
    "\n",
    "    df[COMP_NEXT_1] = df[COMPANY].shift(n_next_1)  # shift by n years\n",
    "    df[COMP_NEXT_2] = df[COMPANY].shift(n_next_2)  # shift by n years\n",
    "    df[COMP_NEXT_3] = df[COMPANY].shift(n_next_3)  # shift by n years\n",
    "\n",
    "    # df = df.dropna()  # drop NaN after shifting\n",
    "\n",
    "    df[PERCENTAGE_CHANGE_PROFITABILITY_NEXT_1] = (((df[PROFITABILITY_NEXT_1] - df[PROFITABILITY]) / df[PROFITABILITY] ) * 100).round(2)\n",
    "    df[PERCENTAGE_CHANGE_PROFITABILITY_NEXT_2] = (((df[PROFITABILITY_NEXT_2] - df[PROFITABILITY]) / df[PROFITABILITY] ) * 100).round(2)\n",
    "    df[PERCENTAGE_CHANGE_PROFITABILITY_NEXT_3] = (((df[PROFITABILITY_NEXT_3] - df[PROFITABILITY]) / df[PROFITABILITY] ) * 100).round(2)\n",
    "\n",
    "    df[PROFITABILITY] = df[PROFITABILITY].round(4)\n",
    "    df[PROFITABILITY_NEXT_1] = df[PROFITABILITY_NEXT_1].round(4)\n",
    "    df[PROFITABILITY_NEXT_2] = df[PROFITABILITY_NEXT_2].round(4)\n",
    "    df[PROFITABILITY_NEXT_3] = df[PROFITABILITY_NEXT_3].round(4)\n",
    "\n",
    "    return df"
   ],
   "metadata": {
    "id": "I9uvkDYTOVez",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1665599141287,
     "user_tz": -120,
     "elapsed": 6,
     "user": {
      "displayName": "Haowei Lee",
      "userId": "07102738850345521476"
     }
    }
   },
   "execution_count": 9,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### 3.3 Inspect the result of profatibility"
   ],
   "metadata": {
    "id": "JyMl7H4rJ1yY"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "# apply the functions to calculate profitability\n",
    "df_profitability = df_merged.pipe(get_profitability)\\\n",
    "                            .pipe(get_key_columns, RENAMING_MAP_MERGED, KEY_COLUMNS)\\\n",
    "                            .pipe(reorder_df)\\\n",
    "                            .pipe(get_percentage_change)"
   ],
   "metadata": {
    "id": "u9SfH_PmO0xH",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1665599141287,
     "user_tz": -120,
     "elapsed": 6,
     "user": {
      "displayName": "Haowei Lee",
      "userId": "07102738850345521476"
     }
    }
   },
   "execution_count": 10,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# specift the order of the columns\n",
    "COLUMNS_ORDER = [\"year\", \"Company_Key\", \n",
    "                 \"Company_Name\",  \"company_next_1\",\"company_next_2\", \"company_next_3\",\n",
    "                 \"profitability\", \"profitability_next_1\", \"profitability_next_2\", \"profitability_next_3\",\n",
    "                 \"change_next_1\", \"change_next_2\", \"change_next_3\"] + financial_columns\n",
    "\n",
    "# reorder the dataframe        \n",
    "df_profitability = df_profitability[COLUMNS_ORDER]\n",
    "df_profitability.head(LIMIT)"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 428
    },
    "id": "gnFp2H751Os_",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1665599141287,
     "user_tz": -120,
     "elapsed": 6,
     "user": {
      "displayName": "Haowei Lee",
      "userId": "07102738850345521476"
     }
    },
    "outputId": "67489829-a074-4258-f96b-5423396356a8"
   },
   "execution_count": 11,
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "    year  Company_Key                    Company_Name  company_next_1  \\\n",
       "22  2011         4904  AMERICAN ELECTRIC POWER CO INC          4904.0   \n",
       "23  2012         4904  AMERICAN ELECTRIC POWER CO INC          4904.0   \n",
       "24  2013         4904  AMERICAN ELECTRIC POWER CO INC          4904.0   \n",
       "\n",
       "    company_next_2  company_next_3  profitability  profitability_next_1  \\\n",
       "22          4904.0          4904.0         0.0322                0.0255   \n",
       "23          4904.0          4904.0         0.0255                0.0272   \n",
       "24          4904.0          4904.0         0.0272                0.0221   \n",
       "\n",
       "    profitability_next_2  profitability_next_3  ...  Revenue - Total  \\\n",
       "22                0.0272                0.0221  ...          15116.0   \n",
       "23                0.0221                0.0420  ...          14945.0   \n",
       "24                0.0420                0.0134  ...          15357.0   \n",
       "\n",
       "    Gross Profit Margin  Net Income (Loss)  Net Profit Margin  \\\n",
       "22             0.299352             1946.0           0.128738   \n",
       "23             0.320107             1259.0           0.084242   \n",
       "24             0.316077             1480.0           0.096373   \n",
       "\n",
       "    Operating Expenses Total  Assets - Total  Stockholders Equity - Total  \\\n",
       "22                   10591.0         52223.0                      14665.0   \n",
       "23                   10161.0         54367.0                      15237.0   \n",
       "24                   10503.0         56414.0                      16086.0   \n",
       "\n",
       "    Retained Earnings       ROA       ROE  \n",
       "22             5420.0  0.037263  0.096888  \n",
       "23             5899.0  0.023157  0.059567  \n",
       "24             6651.0  0.026235  0.065092  \n",
       "\n",
       "[3 rows x 24 columns]"
      ],
      "text/html": [
       "\n",
       "  <div id=\"df-8aca983e-d641-4d3a-913d-3229bc988668\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <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>year</th>\n",
       "      <th>Company_Key</th>\n",
       "      <th>Company_Name</th>\n",
       "      <th>company_next_1</th>\n",
       "      <th>company_next_2</th>\n",
       "      <th>company_next_3</th>\n",
       "      <th>profitability</th>\n",
       "      <th>profitability_next_1</th>\n",
       "      <th>profitability_next_2</th>\n",
       "      <th>profitability_next_3</th>\n",
       "      <th>...</th>\n",
       "      <th>Revenue - Total</th>\n",
       "      <th>Gross Profit Margin</th>\n",
       "      <th>Net Income (Loss)</th>\n",
       "      <th>Net Profit Margin</th>\n",
       "      <th>Operating Expenses Total</th>\n",
       "      <th>Assets - Total</th>\n",
       "      <th>Stockholders Equity - Total</th>\n",
       "      <th>Retained Earnings</th>\n",
       "      <th>ROA</th>\n",
       "      <th>ROE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2011</td>\n",
       "      <td>4904</td>\n",
       "      <td>AMERICAN ELECTRIC POWER CO INC</td>\n",
       "      <td>4904.0</td>\n",
       "      <td>4904.0</td>\n",
       "      <td>4904.0</td>\n",
       "      <td>0.0322</td>\n",
       "      <td>0.0255</td>\n",
       "      <td>0.0272</td>\n",
       "      <td>0.0221</td>\n",
       "      <td>...</td>\n",
       "      <td>15116.0</td>\n",
       "      <td>0.299352</td>\n",
       "      <td>1946.0</td>\n",
       "      <td>0.128738</td>\n",
       "      <td>10591.0</td>\n",
       "      <td>52223.0</td>\n",
       "      <td>14665.0</td>\n",
       "      <td>5420.0</td>\n",
       "      <td>0.037263</td>\n",
       "      <td>0.096888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2012</td>\n",
       "      <td>4904</td>\n",
       "      <td>AMERICAN ELECTRIC POWER CO INC</td>\n",
       "      <td>4904.0</td>\n",
       "      <td>4904.0</td>\n",
       "      <td>4904.0</td>\n",
       "      <td>0.0255</td>\n",
       "      <td>0.0272</td>\n",
       "      <td>0.0221</td>\n",
       "      <td>0.0420</td>\n",
       "      <td>...</td>\n",
       "      <td>14945.0</td>\n",
       "      <td>0.320107</td>\n",
       "      <td>1259.0</td>\n",
       "      <td>0.084242</td>\n",
       "      <td>10161.0</td>\n",
       "      <td>54367.0</td>\n",
       "      <td>15237.0</td>\n",
       "      <td>5899.0</td>\n",
       "      <td>0.023157</td>\n",
       "      <td>0.059567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2013</td>\n",
       "      <td>4904</td>\n",
       "      <td>AMERICAN ELECTRIC POWER CO INC</td>\n",
       "      <td>4904.0</td>\n",
       "      <td>4904.0</td>\n",
       "      <td>4904.0</td>\n",
       "      <td>0.0272</td>\n",
       "      <td>0.0221</td>\n",
       "      <td>0.0420</td>\n",
       "      <td>0.0134</td>\n",
       "      <td>...</td>\n",
       "      <td>15357.0</td>\n",
       "      <td>0.316077</td>\n",
       "      <td>1480.0</td>\n",
       "      <td>0.096373</td>\n",
       "      <td>10503.0</td>\n",
       "      <td>56414.0</td>\n",
       "      <td>16086.0</td>\n",
       "      <td>6651.0</td>\n",
       "      <td>0.026235</td>\n",
       "      <td>0.065092</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 24 columns</p>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-8aca983e-d641-4d3a-913d-3229bc988668')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-8aca983e-d641-4d3a-913d-3229bc988668 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-8aca983e-d641-4d3a-913d-3229bc988668');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ]
     },
     "metadata": {},
     "execution_count": 11
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "def remove_missing_records(df):\n",
    "    # define the filters to clear the records that mix up companies after shifing the columns\n",
    "    filter_diff_company = (df[COMPANY] != df[COMP_NEXT_1]) | \\\n",
    "                          (df[COMPANY] != df[COMP_NEXT_2]) | \\\n",
    "                          (df[COMPANY] != df[COMP_NEXT_3])\n",
    "\n",
    "    # df_profitability[filter_diff_company]\n",
    "    df[PROFITABILITY][filter_diff_company] = np.NaN\n",
    "    df.dropna(inplace=True)\n",
    "    df = df.drop(columns=[COMP_NEXT_1, COMP_NEXT_2, COMP_NEXT_3])\n",
    "\n",
    "    return df"
   ],
   "metadata": {
    "id": "NinVSA72K5iT",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1665599141618,
     "user_tz": -120,
     "elapsed": 337,
     "user": {
      "displayName": "Haowei Lee",
      "userId": "07102738850345521476"
     }
    }
   },
   "execution_count": 12,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# apply the function and inspect the result\n",
    "df_profitability = df_profitability.pipe(remove_missing_records)"
   ],
   "metadata": {
    "id": "ow5OEHsoSXVT",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1665599141619,
     "user_tz": -120,
     "elapsed": 4,
     "user": {
      "displayName": "Haowei Lee",
      "userId": "07102738850345521476"
     }
    }
   },
   "execution_count": 13,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Export data as a CSV file"
   ],
   "metadata": {
    "id": "KOckT7A8FF9d"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "EXPORTED_NAME = \"profitability_data.csv\"\n",
    "df_profitability.to_csv(DIR_PATH + EXPORTED_NAME, index=False)"
   ],
   "metadata": {
    "id": "bnPgWeYx7_V9",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1665599141619,
     "user_tz": -120,
     "elapsed": 4,
     "user": {
      "displayName": "Haowei Lee",
      "userId": "07102738850345521476"
     }
    }
   },
   "execution_count": 14,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# test if the exported data work by reading in the data\n",
    "# only uncomment if needed\n",
    "# df_readin = pd.read_csv(DIR_PATH + EXPORTED_NAME)\n",
    "# print({\"rows\": df_readin.shape[0], \"columns\": df_readin.shape[1]})\n",
    "# df_readin"
   ],
   "metadata": {
    "id": "Oo4dE5WC9OVj",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1665599141619,
     "user_tz": -120,
     "elapsed": 4,
     "user": {
      "displayName": "Haowei Lee",
      "userId": "07102738850345521476"
     }
    },
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 887
    },
    "outputId": "95894134-5240-4994-e852-188c726555d6"
   },
   "execution_count": 15,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "{'rows': 457, 'columns': 21}\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "     year  Company_Key                    Company_Name  profitability  \\\n",
       "0    2011         4904  AMERICAN ELECTRIC POWER CO INC         0.0322   \n",
       "1    2012         4904  AMERICAN ELECTRIC POWER CO INC         0.0255   \n",
       "2    2013         4904  AMERICAN ELECTRIC POWER CO INC         0.0272   \n",
       "3    2014         4904  AMERICAN ELECTRIC POWER CO INC         0.0221   \n",
       "4    2015         4904  AMERICAN ELECTRIC POWER CO INC         0.0420   \n",
       "..    ...          ...                             ...            ...   \n",
       "452  2014      1783400                US ECOLOGY, INC.         0.0610   \n",
       "453  2015      1783400                US ECOLOGY, INC.         0.0285   \n",
       "454  2016      1783400                US ECOLOGY, INC.         0.0438   \n",
       "455  2017      1783400                US ECOLOGY, INC.         0.0507   \n",
       "456  2018      1783400                US ECOLOGY, INC.         0.0394   \n",
       "\n",
       "     profitability_next_1  profitability_next_2  profitability_next_3  \\\n",
       "0                  0.0255                0.0272                0.0221   \n",
       "1                  0.0272                0.0221                0.0420   \n",
       "2                  0.0221                0.0420                0.0134   \n",
       "3                  0.0420                0.0134                0.0561   \n",
       "4                  0.0134                0.0561                0.0291   \n",
       "..                    ...                   ...                   ...   \n",
       "452                0.0285                0.0438                0.0507   \n",
       "453                0.0438                0.0507                0.0394   \n",
       "454                0.0507                0.0394                0.0118   \n",
       "455                0.0394                0.0118               -0.3364   \n",
       "456                0.0118               -0.3364               -0.0268   \n",
       "\n",
       "     change_next_1  change_next_2  change_next_3  ...  Revenue - Total  \\\n",
       "0           -20.91         -15.54         -31.33  ...        15116.000   \n",
       "1             6.79         -13.17          65.03  ...        14945.000   \n",
       "2           -18.69          54.54         -50.69  ...        15357.000   \n",
       "3            90.07         -39.36         153.76  ...        17020.000   \n",
       "4           -68.09          33.51         -30.70  ...        16453.200   \n",
       "..             ...            ...            ...  ...              ...   \n",
       "452         -53.30         -28.14         -16.83  ...          447.411   \n",
       "453          53.87          78.09          38.25  ...          563.070   \n",
       "454          15.74         -10.15         -73.15  ...          477.665   \n",
       "455         -22.37         -76.80        -763.40  ...          504.042   \n",
       "456         -70.12        -954.59        -168.03  ...          565.928   \n",
       "\n",
       "     Gross Profit Margin  Net Income (Loss)  Net Profit Margin  \\\n",
       "0               0.299352           1946.000           0.128738   \n",
       "1               0.320107           1259.000           0.084242   \n",
       "2               0.316077           1480.000           0.096373   \n",
       "3               0.306463           1634.000           0.096005   \n",
       "4               0.324751           2047.100           0.124420   \n",
       "..                   ...                ...                ...   \n",
       "452             0.404688             38.236           0.085461   \n",
       "453             0.384023             25.611           0.045485   \n",
       "454             0.392382             34.252           0.071707   \n",
       "455             0.385569             49.365           0.097938   \n",
       "456             0.375760             49.595           0.087635   \n",
       "\n",
       "     Operating Expenses Total  Assets - Total  Stockholders Equity - Total  \\\n",
       "0                   10591.000       52223.000                    14665.000   \n",
       "1                   10161.000       54367.000                    15237.000   \n",
       "2                   10503.000       56414.000                    16086.000   \n",
       "3                   11804.000       59633.000                    16824.000   \n",
       "4                   11110.000       61683.100                    17904.900   \n",
       "..                        ...             ...                          ...   \n",
       "452                   333.339         919.855                      251.337   \n",
       "453                   438.717         771.987                      256.135   \n",
       "454                   367.804         776.400                      280.024   \n",
       "455                   394.165         802.076                      324.077   \n",
       "456                   445.615         947.898                      359.217   \n",
       "\n",
       "     Retained Earnings       ROA       ROE  \n",
       "0             5420.000  0.037263  0.096888  \n",
       "1             5899.000  0.023157  0.059567  \n",
       "2             6651.000  0.026235  0.065092  \n",
       "3             7303.000  0.027401  0.067725  \n",
       "4             8271.200  0.033187  0.078205  \n",
       "..                 ...       ...       ...  \n",
       "452             85.615  0.041567  0.113476  \n",
       "453             86.234  0.033175  0.074805  \n",
       "454            107.154  0.044116  0.088466  \n",
       "455            146.429  0.061547  0.104919  \n",
       "456            175.533  0.052321  0.092744  \n",
       "\n",
       "[457 rows x 21 columns]"
      ],
      "text/html": [
       "\n",
       "  <div id=\"df-dc79bff2-afa8-475a-842a-79eddda9980d\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <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>year</th>\n",
       "      <th>Company_Key</th>\n",
       "      <th>Company_Name</th>\n",
       "      <th>profitability</th>\n",
       "      <th>profitability_next_1</th>\n",
       "      <th>profitability_next_2</th>\n",
       "      <th>profitability_next_3</th>\n",
       "      <th>change_next_1</th>\n",
       "      <th>change_next_2</th>\n",
       "      <th>change_next_3</th>\n",
       "      <th>...</th>\n",
       "      <th>Revenue - Total</th>\n",
       "      <th>Gross Profit Margin</th>\n",
       "      <th>Net Income (Loss)</th>\n",
       "      <th>Net Profit Margin</th>\n",
       "      <th>Operating Expenses Total</th>\n",
       "      <th>Assets - Total</th>\n",
       "      <th>Stockholders Equity - Total</th>\n",
       "      <th>Retained Earnings</th>\n",
       "      <th>ROA</th>\n",
       "      <th>ROE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011</td>\n",
       "      <td>4904</td>\n",
       "      <td>AMERICAN ELECTRIC POWER CO INC</td>\n",
       "      <td>0.0322</td>\n",
       "      <td>0.0255</td>\n",
       "      <td>0.0272</td>\n",
       "      <td>0.0221</td>\n",
       "      <td>-20.91</td>\n",
       "      <td>-15.54</td>\n",
       "      <td>-31.33</td>\n",
       "      <td>...</td>\n",
       "      <td>15116.000</td>\n",
       "      <td>0.299352</td>\n",
       "      <td>1946.000</td>\n",
       "      <td>0.128738</td>\n",
       "      <td>10591.000</td>\n",
       "      <td>52223.000</td>\n",
       "      <td>14665.000</td>\n",
       "      <td>5420.000</td>\n",
       "      <td>0.037263</td>\n",
       "      <td>0.096888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2012</td>\n",
       "      <td>4904</td>\n",
       "      <td>AMERICAN ELECTRIC POWER CO INC</td>\n",
       "      <td>0.0255</td>\n",
       "      <td>0.0272</td>\n",
       "      <td>0.0221</td>\n",
       "      <td>0.0420</td>\n",
       "      <td>6.79</td>\n",
       "      <td>-13.17</td>\n",
       "      <td>65.03</td>\n",
       "      <td>...</td>\n",
       "      <td>14945.000</td>\n",
       "      <td>0.320107</td>\n",
       "      <td>1259.000</td>\n",
       "      <td>0.084242</td>\n",
       "      <td>10161.000</td>\n",
       "      <td>54367.000</td>\n",
       "      <td>15237.000</td>\n",
       "      <td>5899.000</td>\n",
       "      <td>0.023157</td>\n",
       "      <td>0.059567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013</td>\n",
       "      <td>4904</td>\n",
       "      <td>AMERICAN ELECTRIC POWER CO INC</td>\n",
       "      <td>0.0272</td>\n",
       "      <td>0.0221</td>\n",
       "      <td>0.0420</td>\n",
       "      <td>0.0134</td>\n",
       "      <td>-18.69</td>\n",
       "      <td>54.54</td>\n",
       "      <td>-50.69</td>\n",
       "      <td>...</td>\n",
       "      <td>15357.000</td>\n",
       "      <td>0.316077</td>\n",
       "      <td>1480.000</td>\n",
       "      <td>0.096373</td>\n",
       "      <td>10503.000</td>\n",
       "      <td>56414.000</td>\n",
       "      <td>16086.000</td>\n",
       "      <td>6651.000</td>\n",
       "      <td>0.026235</td>\n",
       "      <td>0.065092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2014</td>\n",
       "      <td>4904</td>\n",
       "      <td>AMERICAN ELECTRIC POWER CO INC</td>\n",
       "      <td>0.0221</td>\n",
       "      <td>0.0420</td>\n",
       "      <td>0.0134</td>\n",
       "      <td>0.0561</td>\n",
       "      <td>90.07</td>\n",
       "      <td>-39.36</td>\n",
       "      <td>153.76</td>\n",
       "      <td>...</td>\n",
       "      <td>17020.000</td>\n",
       "      <td>0.306463</td>\n",
       "      <td>1634.000</td>\n",
       "      <td>0.096005</td>\n",
       "      <td>11804.000</td>\n",
       "      <td>59633.000</td>\n",
       "      <td>16824.000</td>\n",
       "      <td>7303.000</td>\n",
       "      <td>0.027401</td>\n",
       "      <td>0.067725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015</td>\n",
       "      <td>4904</td>\n",
       "      <td>AMERICAN ELECTRIC POWER CO INC</td>\n",
       "      <td>0.0420</td>\n",
       "      <td>0.0134</td>\n",
       "      <td>0.0561</td>\n",
       "      <td>0.0291</td>\n",
       "      <td>-68.09</td>\n",
       "      <td>33.51</td>\n",
       "      <td>-30.70</td>\n",
       "      <td>...</td>\n",
       "      <td>16453.200</td>\n",
       "      <td>0.324751</td>\n",
       "      <td>2047.100</td>\n",
       "      <td>0.124420</td>\n",
       "      <td>11110.000</td>\n",
       "      <td>61683.100</td>\n",
       "      <td>17904.900</td>\n",
       "      <td>8271.200</td>\n",
       "      <td>0.033187</td>\n",
       "      <td>0.078205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>452</th>\n",
       "      <td>2014</td>\n",
       "      <td>1783400</td>\n",
       "      <td>US ECOLOGY, INC.</td>\n",
       "      <td>0.0610</td>\n",
       "      <td>0.0285</td>\n",
       "      <td>0.0438</td>\n",
       "      <td>0.0507</td>\n",
       "      <td>-53.30</td>\n",
       "      <td>-28.14</td>\n",
       "      <td>-16.83</td>\n",
       "      <td>...</td>\n",
       "      <td>447.411</td>\n",
       "      <td>0.404688</td>\n",
       "      <td>38.236</td>\n",
       "      <td>0.085461</td>\n",
       "      <td>333.339</td>\n",
       "      <td>919.855</td>\n",
       "      <td>251.337</td>\n",
       "      <td>85.615</td>\n",
       "      <td>0.041567</td>\n",
       "      <td>0.113476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>453</th>\n",
       "      <td>2015</td>\n",
       "      <td>1783400</td>\n",
       "      <td>US ECOLOGY, INC.</td>\n",
       "      <td>0.0285</td>\n",
       "      <td>0.0438</td>\n",
       "      <td>0.0507</td>\n",
       "      <td>0.0394</td>\n",
       "      <td>53.87</td>\n",
       "      <td>78.09</td>\n",
       "      <td>38.25</td>\n",
       "      <td>...</td>\n",
       "      <td>563.070</td>\n",
       "      <td>0.384023</td>\n",
       "      <td>25.611</td>\n",
       "      <td>0.045485</td>\n",
       "      <td>438.717</td>\n",
       "      <td>771.987</td>\n",
       "      <td>256.135</td>\n",
       "      <td>86.234</td>\n",
       "      <td>0.033175</td>\n",
       "      <td>0.074805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454</th>\n",
       "      <td>2016</td>\n",
       "      <td>1783400</td>\n",
       "      <td>US ECOLOGY, INC.</td>\n",
       "      <td>0.0438</td>\n",
       "      <td>0.0507</td>\n",
       "      <td>0.0394</td>\n",
       "      <td>0.0118</td>\n",
       "      <td>15.74</td>\n",
       "      <td>-10.15</td>\n",
       "      <td>-73.15</td>\n",
       "      <td>...</td>\n",
       "      <td>477.665</td>\n",
       "      <td>0.392382</td>\n",
       "      <td>34.252</td>\n",
       "      <td>0.071707</td>\n",
       "      <td>367.804</td>\n",
       "      <td>776.400</td>\n",
       "      <td>280.024</td>\n",
       "      <td>107.154</td>\n",
       "      <td>0.044116</td>\n",
       "      <td>0.088466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>455</th>\n",
       "      <td>2017</td>\n",
       "      <td>1783400</td>\n",
       "      <td>US ECOLOGY, INC.</td>\n",
       "      <td>0.0507</td>\n",
       "      <td>0.0394</td>\n",
       "      <td>0.0118</td>\n",
       "      <td>-0.3364</td>\n",
       "      <td>-22.37</td>\n",
       "      <td>-76.80</td>\n",
       "      <td>-763.40</td>\n",
       "      <td>...</td>\n",
       "      <td>504.042</td>\n",
       "      <td>0.385569</td>\n",
       "      <td>49.365</td>\n",
       "      <td>0.097938</td>\n",
       "      <td>394.165</td>\n",
       "      <td>802.076</td>\n",
       "      <td>324.077</td>\n",
       "      <td>146.429</td>\n",
       "      <td>0.061547</td>\n",
       "      <td>0.104919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>456</th>\n",
       "      <td>2018</td>\n",
       "      <td>1783400</td>\n",
       "      <td>US ECOLOGY, INC.</td>\n",
       "      <td>0.0394</td>\n",
       "      <td>0.0118</td>\n",
       "      <td>-0.3364</td>\n",
       "      <td>-0.0268</td>\n",
       "      <td>-70.12</td>\n",
       "      <td>-954.59</td>\n",
       "      <td>-168.03</td>\n",
       "      <td>...</td>\n",
       "      <td>565.928</td>\n",
       "      <td>0.375760</td>\n",
       "      <td>49.595</td>\n",
       "      <td>0.087635</td>\n",
       "      <td>445.615</td>\n",
       "      <td>947.898</td>\n",
       "      <td>359.217</td>\n",
       "      <td>175.533</td>\n",
       "      <td>0.052321</td>\n",
       "      <td>0.092744</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>457 rows × 21 columns</p>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-dc79bff2-afa8-475a-842a-79eddda9980d')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-dc79bff2-afa8-475a-842a-79eddda9980d button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-dc79bff2-afa8-475a-842a-79eddda9980d');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ]
     },
     "metadata": {},
     "execution_count": 15
    }
   ]
  },
  {
   "cell_type": "markdown",
   "source": [
    "## End of this notebook"
   ],
   "metadata": {
    "id": "sfxPOZF6FCYG"
   }
  }
 ]
}