stack-statements-data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_raw = pd.read_csv('statements-data-semi-stacked.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df_raw.copy()\n",
    "for col in ['FY18', 'FY17', 'FY16', 'FY15']:\n",
    "    df[col] = df[col].str.replace(',', '')\n",
    "    df[col] = df[col].str.replace('-', '')\n",
    "    df[col] = pd.to_numeric(df[col])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "stacked = df.set_index(['Company', 'Metric']).stack().reset_index().sort_values('Company')\n",
    "stacked.columns = ['Company', 'Metric', 'FY', 'Value']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "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>Company</th>\n",
       "      <th>Metric</th>\n",
       "      <th>FY</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>221</th>\n",
       "      <td>TELUS</td>\n",
       "      <td>Equity</td>\n",
       "      <td>FY17</td>\n",
       "      <td>9416000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>220</th>\n",
       "      <td>TELUS</td>\n",
       "      <td>Equity</td>\n",
       "      <td>FY18</td>\n",
       "      <td>10259000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>TELUS</td>\n",
       "      <td>EBITDA</td>\n",
       "      <td>FY15</td>\n",
       "      <td>4050000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327</th>\n",
       "      <td>TELUS</td>\n",
       "      <td>FFO</td>\n",
       "      <td>FY17</td>\n",
       "      <td>3947000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td>TELUS</td>\n",
       "      <td>Long Term Debt</td>\n",
       "      <td>FY15</td>\n",
       "      <td>11182000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Company          Metric    FY       Value\n",
       "221   TELUS          Equity  FY17   9416000.0\n",
       "220   TELUS          Equity  FY18  10259000.0\n",
       "39    TELUS          EBITDA  FY15   4050000.0\n",
       "327   TELUS             FFO  FY17   3947000.0\n",
       "219   TELUS  Long Term Debt  FY15  11182000.0"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stacked.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "stacked.to_csv('stacked-statements-data.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "pivoted = stacked.pivot_table(columns='Metric', index=['Company', 'FY'], values = 'Value')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "pivoted['EBIT to Interest Expense'] = pivoted['EBIT'] / pivoted['Interest Expense']\n",
    "pivoted['EBITDA to Interest Expense'] = pivoted['EBITDA'] / pivoted['Interest Expense']\n",
    "pivoted['EBIT Margin'] = pivoted['EBIT'] / pivoted['Total Revenue']\n",
    "pivoted['EBITDA Margin'] = pivoted['EBITDA'] / pivoted['Total Revenue']\n",
    "pivoted['Debt to EBITDA'] = pivoted['Total Debt'] / pivoted['EBITDA']\n",
    "pivoted['Debt to EBIT'] = pivoted['Total Debt'] / pivoted['EBIT']\n",
    "pivoted['Debt to Equity'] = pivoted['Total Debt'] / pivoted['Equity']\n",
    "pivoted['ROE'] = pivoted['Net Income'] / pivoted['Equity']\n",
    "pivoted['FFO + IntExp to IntExp'] =  (pivoted['FFO'] +  pivoted['Interest Expense']) /  pivoted['Interest Expense']\n",
    "pivoted['FFO to Debt'] =  pivoted['FFO'] /  pivoted['Total Debt']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "averages = pivoted.groupby('Company')[pivoted.columns].mean()\n",
    "averages.index = zip(averages.index, ['Average']*len(averages.index))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "pivoted = pd.concat([pivoted, averages]).sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "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>Metric</th>\n",
       "      <th>Assets</th>\n",
       "      <th>EBIT</th>\n",
       "      <th>EBITDA</th>\n",
       "      <th>Equity</th>\n",
       "      <th>FFO</th>\n",
       "      <th>Interest Expense</th>\n",
       "      <th>Long Term Debt</th>\n",
       "      <th>Net Income</th>\n",
       "      <th>Total Debt</th>\n",
       "      <th>Total Revenue</th>\n",
       "      <th>EBIT to Interest Expense</th>\n",
       "      <th>EBITDA to Interest Expense</th>\n",
       "      <th>EBIT Margin</th>\n",
       "      <th>EBITDA Margin</th>\n",
       "      <th>Debt to EBITDA</th>\n",
       "      <th>Debt to EBIT</th>\n",
       "      <th>Debt to Equity</th>\n",
       "      <th>ROE</th>\n",
       "      <th>FFO + IntExp to IntExp</th>\n",
       "      <th>FFO to Debt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Company</th>\n",
       "      <th>FY</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">TELUS</th>\n",
       "      <th>Average</th>\n",
       "      <td>29563250.0</td>\n",
       "      <td>2705500.0</td>\n",
       "      <td>4336000.0</td>\n",
       "      <td>8816000.0</td>\n",
       "      <td>3695000.0</td>\n",
       "      <td>544000.0</td>\n",
       "      <td>12089250.0</td>\n",
       "      <td>1441000.0</td>\n",
       "      <td>20711500.0</td>\n",
       "      <td>13138750.0</td>\n",
       "      <td>5.008332</td>\n",
       "      <td>8.027649</td>\n",
       "      <td>0.205901</td>\n",
       "      <td>0.330009</td>\n",
       "      <td>4.770694</td>\n",
       "      <td>7.645973</td>\n",
       "      <td>2.362599</td>\n",
       "      <td>0.164036</td>\n",
       "      <td>7.822008</td>\n",
       "      <td>0.178450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FY15</th>\n",
       "      <td>26406000.0</td>\n",
       "      <td>2537000.0</td>\n",
       "      <td>4050000.0</td>\n",
       "      <td>7672000.0</td>\n",
       "      <td>3556000.0</td>\n",
       "      <td>470000.0</td>\n",
       "      <td>11182000.0</td>\n",
       "      <td>1382000.0</td>\n",
       "      <td>18734000.0</td>\n",
       "      <td>12430000.0</td>\n",
       "      <td>5.397872</td>\n",
       "      <td>8.617021</td>\n",
       "      <td>0.204103</td>\n",
       "      <td>0.325825</td>\n",
       "      <td>4.625679</td>\n",
       "      <td>7.384312</td>\n",
       "      <td>2.441867</td>\n",
       "      <td>0.180136</td>\n",
       "      <td>8.565957</td>\n",
       "      <td>0.189815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FY16</th>\n",
       "      <td>27729000.0</td>\n",
       "      <td>2617000.0</td>\n",
       "      <td>4228000.0</td>\n",
       "      <td>7917000.0</td>\n",
       "      <td>3219000.0</td>\n",
       "      <td>502000.0</td>\n",
       "      <td>11625000.0</td>\n",
       "      <td>1223000.0</td>\n",
       "      <td>19793000.0</td>\n",
       "      <td>12725000.0</td>\n",
       "      <td>5.213147</td>\n",
       "      <td>8.422311</td>\n",
       "      <td>0.205658</td>\n",
       "      <td>0.332259</td>\n",
       "      <td>4.681410</td>\n",
       "      <td>7.563240</td>\n",
       "      <td>2.500063</td>\n",
       "      <td>0.154478</td>\n",
       "      <td>7.412351</td>\n",
       "      <td>0.162633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FY17</th>\n",
       "      <td>31053000.0</td>\n",
       "      <td>2781000.0</td>\n",
       "      <td>4450000.0</td>\n",
       "      <td>9416000.0</td>\n",
       "      <td>3947000.0</td>\n",
       "      <td>579000.0</td>\n",
       "      <td>12332000.0</td>\n",
       "      <td>1559000.0</td>\n",
       "      <td>21595000.0</td>\n",
       "      <td>13305000.0</td>\n",
       "      <td>4.803109</td>\n",
       "      <td>7.685665</td>\n",
       "      <td>0.209019</td>\n",
       "      <td>0.334461</td>\n",
       "      <td>4.852809</td>\n",
       "      <td>7.765192</td>\n",
       "      <td>2.293437</td>\n",
       "      <td>0.165569</td>\n",
       "      <td>7.816926</td>\n",
       "      <td>0.182774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FY18</th>\n",
       "      <td>33065000.0</td>\n",
       "      <td>2887000.0</td>\n",
       "      <td>4616000.0</td>\n",
       "      <td>10259000.0</td>\n",
       "      <td>4058000.0</td>\n",
       "      <td>625000.0</td>\n",
       "      <td>13218000.0</td>\n",
       "      <td>1600000.0</td>\n",
       "      <td>22724000.0</td>\n",
       "      <td>14095000.0</td>\n",
       "      <td>4.619200</td>\n",
       "      <td>7.385600</td>\n",
       "      <td>0.204824</td>\n",
       "      <td>0.327492</td>\n",
       "      <td>4.922877</td>\n",
       "      <td>7.871147</td>\n",
       "      <td>2.215031</td>\n",
       "      <td>0.155961</td>\n",
       "      <td>7.492800</td>\n",
       "      <td>0.178578</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Metric               Assets       EBIT     EBITDA      Equity        FFO  \\\n",
       "Company FY                                                                 \n",
       "TELUS   Average  29563250.0  2705500.0  4336000.0   8816000.0  3695000.0   \n",
       "        FY15     26406000.0  2537000.0  4050000.0   7672000.0  3556000.0   \n",
       "        FY16     27729000.0  2617000.0  4228000.0   7917000.0  3219000.0   \n",
       "        FY17     31053000.0  2781000.0  4450000.0   9416000.0  3947000.0   \n",
       "        FY18     33065000.0  2887000.0  4616000.0  10259000.0  4058000.0   \n",
       "\n",
       "Metric           Interest Expense  Long Term Debt  Net Income  Total Debt  \\\n",
       "Company FY                                                                  \n",
       "TELUS   Average          544000.0      12089250.0   1441000.0  20711500.0   \n",
       "        FY15             470000.0      11182000.0   1382000.0  18734000.0   \n",
       "        FY16             502000.0      11625000.0   1223000.0  19793000.0   \n",
       "        FY17             579000.0      12332000.0   1559000.0  21595000.0   \n",
       "        FY18             625000.0      13218000.0   1600000.0  22724000.0   \n",
       "\n",
       "Metric           Total Revenue  EBIT to Interest Expense  \\\n",
       "Company FY                                                 \n",
       "TELUS   Average     13138750.0                  5.008332   \n",
       "        FY15        12430000.0                  5.397872   \n",
       "        FY16        12725000.0                  5.213147   \n",
       "        FY17        13305000.0                  4.803109   \n",
       "        FY18        14095000.0                  4.619200   \n",
       "\n",
       "Metric           EBITDA to Interest Expense  EBIT Margin  EBITDA Margin  \\\n",
       "Company FY                                                                \n",
       "TELUS   Average                    8.027649     0.205901       0.330009   \n",
       "        FY15                       8.617021     0.204103       0.325825   \n",
       "        FY16                       8.422311     0.205658       0.332259   \n",
       "        FY17                       7.685665     0.209019       0.334461   \n",
       "        FY18                       7.385600     0.204824       0.327492   \n",
       "\n",
       "Metric           Debt to EBITDA  Debt to EBIT  Debt to Equity       ROE  \\\n",
       "Company FY                                                                \n",
       "TELUS   Average        4.770694      7.645973        2.362599  0.164036   \n",
       "        FY15           4.625679      7.384312        2.441867  0.180136   \n",
       "        FY16           4.681410      7.563240        2.500063  0.154478   \n",
       "        FY17           4.852809      7.765192        2.293437  0.165569   \n",
       "        FY18           4.922877      7.871147        2.215031  0.155961   \n",
       "\n",
       "Metric           FFO + IntExp to IntExp  FFO to Debt  \n",
       "Company FY                                            \n",
       "TELUS   Average                7.822008     0.178450  \n",
       "        FY15                   8.565957     0.189815  \n",
       "        FY16                   7.412351     0.162633  \n",
       "        FY17                   7.816926     0.182774  \n",
       "        FY18                   7.492800     0.178578  "
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivoted.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "pivoted.to_csv('statements-data-pivoted.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}