lectures/electricity.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Elictricity\n",
    "\n",
    "Data source: http://www.eia.gov/electricity/data/eia861/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set up the environment and load libraries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import re\n",
    "import requests\n",
    "import zipfile\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "pd.set_option('float_format', '{:6.2f}'.format)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Download the webpage with data links."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "url = 'http://www.eia.gov/electricity/data/eia861/'\n",
    "response = requests.get(url)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find all zip files mentioned on the webpage with the help of regular expressions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['f8612015.zip', 'f8612014.zip', 'f8612013.zip', 'f8612012.zip', 'f86111.zip', 'f86110.zip', 'f86109.zip', 'f86108.zip', 'f86107.zip', 'f86106.zip', 'f86105.zip', 'f86104.zip', 'f86103.zip', 'f86102.zip', 'f86101.zip', 'f86100.zip', 'f86199.zip', 'f86198.zip', 'f86197.zip', 'f86196.zip', 'f86195.zip', 'f86194.zip', 'f86193.zip', 'f86192.zip', 'f86191.zip', 'f86190.zip']\n"
     ]
    }
   ],
   "source": [
    "fnames = re.findall('zip\\/(.*?\\.zip)', str(response.content))\n",
    "print(fnames)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find all years from zip file names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[2015, 2014, 2013, 2012, 2011, 2010, 2009, 2008, 2007, 2006, 2005, 2004, 2003, 2002, 2001, 2000, 1999, 1998, 1997, 1996, 1995, 1994, 1993, 1992, 1991, 1990]\n"
     ]
    }
   ],
   "source": [
    "years = [re.search('\\d+', x).group()[-2:] for x in fnames]\n",
    "years = ['20' + x if int(x) < 50 else '19' + x for x in years]\n",
    "years = [int(x) for x in years]\n",
    "print(years)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{1990: 'f86190.zip', 1991: 'f86191.zip', 1992: 'f86192.zip', 1993: 'f86193.zip', 1994: 'f86194.zip', 1995: 'f86195.zip', 1996: 'f86196.zip', 1997: 'f86197.zip', 1998: 'f86198.zip', 1999: 'f86199.zip', 2000: 'f86100.zip', 2001: 'f86101.zip', 2002: 'f86102.zip', 2003: 'f86103.zip', 2004: 'f86104.zip', 2005: 'f86105.zip', 2006: 'f86106.zip', 2007: 'f86107.zip', 2008: 'f86108.zip', 2009: 'f86109.zip', 2010: 'f86110.zip', 2011: 'f86111.zip', 2012: 'f8612012.zip', 2013: 'f8612013.zip', 2014: 'f8612014.zip', 2015: 'f8612015.zip'}\n"
     ]
    }
   ],
   "source": [
    "files = dict(zip(years, fnames))\n",
    "print(files)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Download all zip files to the disk."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "path = '../data/Electricity/'\n",
    "\n",
    "for year, fname in files.items():\n",
    "    response = requests.get(url + 'zip/' + fname)\n",
    "    with open(path + str(year) + '.zip', \"wb\") as file:\n",
    "        file.write(response.content)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load year only 'Distributed_Generation_2013.xls' into Pandas DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of Generators            int64\n",
      "Total Capacity in MWs         float64\n",
      "Capacity Back-up Only         float64\n",
      "Capacity Utility Owned        float64\n",
      "Estimated or Actual Data       object\n",
      "Internal Combustion Engine    float64\n",
      "Combusiton Turbine            float64\n",
      "Steam Turbine                 float64\n",
      "HydroElectric                 float64\n",
      "Wind                          float64\n",
      "Photovoltaic                  float64\n",
      "Storage                       float64\n",
      "Other                         float64\n",
      "Total                         float64\n",
      "Estimated or Actual Data.1     object\n",
      "dtype: object\n",
      "                                                                Number of Generators  \\\n",
      "Data Year Utility Number Utility Name                    State                         \n",
      "2013      276            Alcorn County Elec Power Assn   MS                        9   \n",
      "          392            Alpena Power Co                 MI                       29   \n",
      "          3046           Duke Energy Progress - (NC)     NC                      110   \n",
      "                                                         SC                        3   \n",
      "          3989           City of Colorado Springs - (CO) CO                        3   \n",
      "\n",
      "                                                                Total Capacity in MWs  \n",
      "Data Year Utility Number Utility Name                    State                         \n",
      "2013      276            Alcorn County Elec Power Assn   MS                      8.00  \n",
      "          392            Alpena Power Co                 MI                      6.90  \n",
      "          3046           Duke Energy Progress - (NC)     NC                     14.70  \n",
      "                                                         SC                      0.10  \n",
      "          3989           City of Colorado Springs - (CO) CO                      1.40  \n"
     ]
    }
   ],
   "source": [
    "generation = {2013: 'Distributed_Generation_2013.xls',\n",
    "              2012: 'distributed_generation_2012.xls'}\n",
    "\n",
    "year = 2013\n",
    "zf = zipfile.ZipFile(path + str(year) + '.zip')\n",
    "raw = pd.read_excel(zf.open(generation[year]),\n",
    "                    index_col=list(range(4)), na_values=['.'],\n",
    "                    sheetname=0, header=1).dropna()\n",
    "\n",
    "print(raw.dtypes)\n",
    "print(raw[['Number of Generators', 'Total Capacity in MWs']].head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read two years of data and concatenate them into one dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(487, 15)\n",
      "Number of Generators            int64\n",
      "Total Capacity in MWs         float64\n",
      "Capacity Back-up Only         float64\n",
      "Capacity Utility Owned        float64\n",
      "Estimated or Actual Data       object\n",
      "Internal Combustion Engine    float64\n",
      "Combusiton Turbine            float64\n",
      "Steam Turbine                 float64\n",
      "HydroElectric                 float64\n",
      "Wind                          float64\n",
      "Photovoltaic                  float64\n",
      "Storage                       float64\n",
      "Other                         float64\n",
      "Total                         float64\n",
      "Estimated or Actual Data.1     object\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "raw_frames = []\n",
    "for year, name in generation.items():\n",
    "    zf = zipfile.ZipFile(path + str(year) + '.zip')\n",
    "    raw_frames.append(pd.read_excel(zf.open(generation[year]),\n",
    "                                    index_col=list(range(4)), na_values=['.'],\n",
    "                                    sheetname=0, header=1))\n",
    "\n",
    "distributed_generation = pd.concat(raw_frames)\n",
    "\n",
    "print(distributed_generation.shape)\n",
    "print(distributed_generation.dtypes)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many unique years, utility companies, and states do we have in the dataset?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data Year 2\n",
      "Utility Number 282\n",
      "Utility Name 295\n",
      "State 50\n"
     ]
    }
   ],
   "source": [
    "for level in distributed_generation.index.names:\n",
    "    print(level, distributed_generation.index.get_level_values(level).nunique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Aggregate capacity by year and state."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                 Capacity  Companies\n",
      "Data Year State                     \n",
      "2013      TN         0.00       1.00\n",
      "          PA         0.10       2.00\n",
      "2012      MT         0.20       2.00\n",
      "          TN         0.20       1.00\n",
      "          WV         0.40       2.00\n",
      "                 Capacity  Companies\n",
      "Data Year State                     \n",
      "2013      NY       150.38       3.00\n",
      "          CA       179.91      11.00\n",
      "2012      FL       254.00      10.00\n",
      "2013      FL       275.30       7.00\n",
      "2012      CA       483.20      16.00\n"
     ]
    }
   ],
   "source": [
    "fun = {'Capacity': np.sum, 'Companies': pd.Series.count}\n",
    "capacity = distributed_generation.groupby(level=['Data Year', 'State'])['Total'].agg(fun)\n",
    "capacity.sort_values(by='Capacity', inplace=True)\n",
    "\n",
    "print(capacity.head())\n",
    "print(capacity.tail())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compute means for each state."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Capacity  Companies  Mean generation per company\n",
      "State                                                  \n",
      "TN         0.10       1.00                         0.10\n",
      "WV         0.40       2.00                         0.20\n",
      "SD         1.45       6.00                         0.24\n",
      "KS         8.50      10.50                         0.81\n",
      "NV         1.00       1.00                         1.00\n",
      "       Capacity  Companies  Mean generation per company\n",
      "State                                                  \n",
      "CT        29.75       1.00                        29.75\n",
      "MI       135.90       4.50                        30.20\n",
      "FL       264.65       8.50                        31.14\n",
      "NY       106.29       3.00                        35.43\n",
      "NJ       119.15       2.00                        59.58\n",
      "       Capacity  Companies  Mean generation per company\n",
      "count     50.00      50.00                        50.00\n",
      "mean      41.39       4.86                         9.49\n",
      "std       64.71       4.35                        11.98\n",
      "min        0.10       1.00                         0.10\n",
      "25%        6.35       2.00                         1.47\n",
      "50%       17.00       3.50                         4.64\n",
      "75%       38.23       6.00                        12.36\n",
      "max      331.56      21.00                        59.58\n"
     ]
    }
   ],
   "source": [
    "capacity_state = capacity.groupby(level='State').mean()\n",
    "capacity_state['Mean generation per company'] = capacity_state.eval('Capacity / Companies')\n",
    "capacity_state.sort_values(by='Mean generation per company', inplace=True)\n",
    "\n",
    "print(capacity_state.head())\n",
    "print(capacity_state.tail())\n",
    "print(capacity_state.describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compute Herfindahl index (https://en.wikipedia.org/wiki/Herfindahl_index) for each state. For that we first need to compute market share of each company."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data Year  Utility Number  Utility Name              State\n",
      "2012       97              Adams Electric Coop       IL        0.05\n",
      "           122             Village of Arcade         NY        0.01\n",
      "           123             City of Adel- (GA)        GA        0.00\n",
      "           232             City of Albemarle - (NC)  NC        0.01\n",
      "           392             Alpena Power Co           MI        0.05\n",
      "Name: Total, dtype: float64\n",
      "count   482.00\n",
      "mean      0.20\n",
      "std       0.30\n",
      "min       0.00\n",
      "25%       0.01\n",
      "50%       0.05\n",
      "75%       0.24\n",
      "max       1.00\n",
      "Name: Total, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "fun = {'Capacity': np.sum, 'Companies': pd.Series.count}\n",
    "share = distributed_generation.groupby(level=['Data Year', 'State'])['Total'].apply(lambda x: x / x.sum())\n",
    "\n",
    "print(share.head())\n",
    "print(share.describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And now compute the index itself."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "count    50.00\n",
      "mean      0.57\n",
      "std       0.31\n",
      "min       0.05\n",
      "25%       0.34\n",
      "50%       0.50\n",
      "75%       0.82\n",
      "max       1.00\n",
      "Name: Total, dtype: float64\n",
      "State\n",
      "KS     0.05\n",
      "UT     0.07\n",
      "NC     0.15\n",
      "KY     0.15\n",
      "SC     0.18\n",
      "Name: Total, dtype: float64\n",
      "State\n",
      "TN     1.00\n",
      "DC     1.00\n",
      "CT     1.00\n",
      "AR     1.00\n",
      "NV     1.00\n",
      "Name: Total, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "def compute_hf(shares):\n",
    "    if len(shares) == 1:\n",
    "        return 1.\n",
    "    else:\n",
    "        return ((shares**2).sum() - 1/len(shares)) / (1 - 1/len(shares))\n",
    "\n",
    "hfindex = share.groupby(level=['Data Year', 'State']).apply(compute_hf)\n",
    "hfindex = hfindex.groupby(level='State').mean().sort_values()\n",
    "\n",
    "print(hfindex.describe())\n",
    "print(hfindex.head())\n",
    "print(hfindex.tail())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compute capacity for each year and state by technology."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Storage                        0.17\n",
      "Steam Turbine                  0.93\n",
      "Combusiton Turbine             2.17\n",
      "HydroElectric                  2.28\n",
      "Wind                           3.38\n",
      "Other                          4.22\n",
      "Photovoltaic                  11.22\n",
      "Internal Combustion Engine    17.73\n",
      "Name: mean, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "techs = raw.columns[5:-2]\n",
    "technology = distributed_generation.groupby(level=['Data Year', 'State'])[techs].sum().fillna(0)\n",
    "\n",
    "print(technology.describe().ix['mean'].sort_values())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert capacity to shares."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Storage                        0.40\n",
      "Combusiton Turbine             3.10\n",
      "Steam Turbine                  4.68\n",
      "Other                          6.03\n",
      "Wind                           6.94\n",
      "HydroElectric                 12.63\n",
      "Photovoltaic                  27.61\n",
      "Internal Combustion Engine    38.61\n",
      "Name: mean, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "technology = technology.apply(lambda x: x / x.sum(), axis=1)\n",
    "\n",
    "print(technology.describe().ix['mean'].sort_values()*100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compute Herfindahl index over technology types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "State\n",
      "MI     0.14\n",
      "IL     0.18\n",
      "CA     0.21\n",
      "LA     0.25\n",
      "CT     0.29\n",
      "dtype: float64\n",
      "State\n",
      "NJ     0.98\n",
      "MD     0.98\n",
      "NV     1.00\n",
      "AL     1.00\n",
      "WV     1.00\n",
      "dtype: float64\n",
      "count    50.00\n",
      "mean      0.62\n",
      "std       0.25\n",
      "min       0.14\n",
      "25%       0.43\n",
      "50%       0.60\n",
      "75%       0.86\n",
      "max       1.00\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "tech_hfindex = technology.apply(compute_hf, axis=1)\n",
    "tech_hfindex = tech_hfindex.groupby(level='State').mean().sort_values()\n",
    "\n",
    "print(tech_hfindex.head())\n",
    "print(tech_hfindex.tail())\n",
    "print(tech_hfindex.describe())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data Year                    2012   2013\n",
      "Internal Combustion Engine   0.10   0.09\n",
      "Combusiton Turbine           0.31   0.28\n",
      "Steam Turbine                0.08   0.10\n",
      "HydroElectric                0.05   0.05\n",
      "Wind                         0.35   0.37\n",
      "Photovoltaic                 0.10   0.10\n",
      "Storage                      0.00   0.00\n",
      "Other                        0.01   0.01\n"
     ]
    }
   ],
   "source": [
    "df = technology.xs(tech_hfindex.index[0], level='State')\n",
    "print(df.T)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load efficiency data for 2013. Note that it does not have 'Ownership Type' column. We can load another dataset that has this column and merge them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data Year  Utility Number  Utility Name                   State  Ownership Type\n",
      "2013       122             Village of Arcade - (NY)       NY     Municipal          324.00\n",
      "           189             PowerSouth Energy Cooperative  AL     Cooperative       4428.00\n",
      "           195             Alabama Power Co               AL     Investor Owned   14657.00\n",
      "           207             City of Alameda                CA     Municipal         3076.00\n",
      "           295             City of Alexandria - (MN)      MN     Municipal         1867.00\n",
      "Name: Total, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "efficiency = {2013: 'Energy_Efficiency_2013.xls',\n",
    "              2012: 'dsm_2012.xls'}\n",
    "\n",
    "year = 2013\n",
    "zf = zipfile.ZipFile(path + str(year) + '.zip')\n",
    "eff2013 = pd.read_excel(zf.open(efficiency[year]),\n",
    "                        index_col=list(range(4)), na_values=['.'],\n",
    "                        skipfooter=1, sheetname=0, header=2)\n",
    "\n",
    "zf = zipfile.ZipFile(path + str(year) + '.zip')\n",
    "utility_data2013 = pd.read_excel(zf.open('Utility_Data_2013.xls'),\n",
    "                               index_col=list(range(5)), na_values=['.'],\n",
    "                               sheetname=0, header=1)\n",
    "\n",
    "eff2013 = pd.merge(eff2013, utility_data2013.reset_index('Ownership Type'),\n",
    "                  left_index=True, right_index=True)\n",
    "\n",
    "eff2013.set_index('Ownership Type', append=True, inplace=True)\n",
    "\n",
    "eff2013 = eff2013.loc[:, 'Total']\n",
    "\n",
    "print(eff2013.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load efficiency data for 2012."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data Year  Utility Number  Utility Name                    State  Ownership  \n",
      "2012       84              A & N Electric Coop             VA     Cooperative   100.00\n",
      "           97              Adams Electric Coop             IL     Cooperative     0.00\n",
      "           108             Adams-Columbia Electric Coop    WI     Cooperative     0.00\n",
      "           118             Adams Rural Electric Coop, Inc  OH     Cooperative     0.00\n",
      "           122             Village of Arcade               NY     Municipal       0.00\n",
      "Name: Total, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "year = 2012\n",
    "zf = zipfile.ZipFile(path + str(year) + '.zip')\n",
    "eff2012 = pd.read_excel(zf.open(efficiency[year]),\n",
    "                        index_col=list(range(5)), na_values=['.'],\n",
    "                        sheetname=0, header=2)\n",
    "\n",
    "eff2012 = eff2012.loc[:, 'Total']\n",
    "\n",
    "print(eff2012.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Concatenate two datasets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Data Year', 'Utility Number', 'Utility Name', 'State', 'Ownership']\n",
      "Data Year  Utility Number  Utility Name                    State  Ownership  \n",
      "2012       84              A & N Electric Coop             VA     Cooperative   100.00\n",
      "           97              Adams Electric Coop             IL     Cooperative     0.00\n",
      "           108             Adams-Columbia Electric Coop    WI     Cooperative     0.00\n",
      "           118             Adams Rural Electric Coop, Inc  OH     Cooperative     0.00\n",
      "           122             Village of Arcade               NY     Municipal       0.00\n",
      "Name: Savings, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "efficiency = pd.concat([eff2012, eff2013])\n",
    "efficiency.name = 'Savings'\n",
    "\n",
    "print(efficiency.index.names)\n",
    "print(efficiency.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compute total savings for each year and state."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "State  Ownership            \n",
      "AK     Cooperative             3655.00\n",
      "       Investor Owned             0.00\n",
      "       Municipal                   nan\n",
      "       Political Subdivision       nan\n",
      "AL     Cooperative             9907.00\n",
      "Name: Savings, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "efficiency_agg = efficiency.groupby(level=['State', 'Ownership']).sum()\n",
    "\n",
    "print(efficiency_agg.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compute total capacity for each year and state. Convert MWs to MWh."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "State\n",
      "AK    182520.00\n",
      "AL    182520.00\n",
      "AR     16920.00\n",
      "AZ    232920.00\n",
      "CA   2387214.00\n",
      "Name: Capacity, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "distr_generation = distributed_generation.groupby(level='State').sum()\n",
    "distr_generation = distr_generation.loc[:, 'Total Capacity in MWs'] * 60**2\n",
    "distr_generation.name = 'Capacity'\n",
    "\n",
    "print(distr_generation.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge two datasets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                             Savings  Capacity\n",
      "State Ownership                               \n",
      "AK    Cooperative            3655.00 182520.00\n",
      "      Investor Owned            0.00 182520.00\n",
      "      Municipal                  nan 182520.00\n",
      "      Political Subdivision      nan 182520.00\n",
      "AL    Cooperative            9907.00 182520.00\n"
     ]
    }
   ],
   "source": [
    "norm_eff = pd.merge(pd.DataFrame(efficiency_agg).reset_index('Ownership'),\n",
    "                    pd.DataFrame(distr_generation), left_index=True, right_index=True)\n",
    "\n",
    "norm_eff.set_index('Ownership', append=True, inplace=True)\n",
    "\n",
    "print(norm_eff.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compute savings relative to capacity."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                             Savings  Capacity  Relative Savings\n",
      "State Ownership                                                 \n",
      "AK    Cooperative            3655.00 182520.00              0.02\n",
      "      Investor Owned            0.00 182520.00              0.00\n",
      "      Municipal                  nan 182520.00               nan\n",
      "      Political Subdivision      nan 182520.00               nan\n",
      "AL    Cooperative            9907.00 182520.00              0.05\n"
     ]
    }
   ],
   "source": [
    "norm_eff['Relative Savings'] = norm_eff.eval('Savings / Capacity')\n",
    "\n",
    "print(norm_eff.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compute average savings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Ownership\n",
      "Retail Power Marketer        0.00\n",
      "Municipal Mktg Authority     0.17\n",
      "Cooperative                  0.25\n",
      "State                        0.38\n",
      "Municipal                    0.54\n",
      "Political Subdivision        2.43\n",
      "DSM Administrator            3.68\n",
      "Investor Owned               6.20\n",
      "Federal                    120.54\n",
      "Name: Relative Savings, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "savings = norm_eff.groupby(level='Ownership')['Relative Savings'].mean().sort_values()\n",
    "\n",
    "print(savings)"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [default]",
   "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}