{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Calculate NERC level results\n",
    "Use the NERC extra generation and fuel consumption (things not yet reported in EIA-923 final data) from the *Calculate national and NERC gen and emissions* notebook, along with facility EIA and EPA data to calculate generation and emissions in each NERC region.\n",
    "\n",
    "## Instructions\n",
    "Make sure the `file_date` parameter below is set to whatever value you would like appended to file names.\n",
    "\n",
    "The entire notebook can be run at once using *Run All Cells*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:27:29.210505Z",
     "start_time": "2017-11-06T17:27:27.751371Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "from os.path import join\n",
    "import sys\n",
    "import json\n",
    "idx = pd.IndexSlice"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Date string for filenames\n",
    "This will be inserted into all filenames (reading and writing)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_date = '2018-03-06'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:27:29.234050Z",
     "start_time": "2017-11-06T17:27:29.212202Z"
    },
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The watermark extension is already loaded. To reload it, use:\n",
      "  %reload_ext watermark\n"
     ]
    }
   ],
   "source": [
    "%load_ext watermark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:27:29.257707Z",
     "start_time": "2017-11-06T17:27:29.241283Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "seaborn     0.8.1\n",
      "pandas      0.22.0\n",
      "numpy       1.14.1\n",
      "json        2.0.9\n",
      "CPython 3.6.4\n",
      "IPython 6.2.1\n"
     ]
    }
   ],
   "source": [
    "%watermark -v -iv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:27:29.305341Z",
     "start_time": "2017-11-06T17:27:29.260163Z"
    },
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The autoreload extension is already loaded. To reload it, use:\n",
      "  %reload_ext autoreload\n"
     ]
    }
   ],
   "source": [
    "# Load the \"autoreload\" extension\n",
    "%load_ext autoreload\n",
    "\n",
    "# always reload modules marked with \"%aimport\"\n",
    "%autoreload 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:27:29.314381Z",
     "start_time": "2017-11-06T17:27:29.307927Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# add the 'src' directory as one where we can import modules\n",
    "src_dir = join(os.getcwd(), os.pardir, 'src')\n",
    "sys.path.append(src_dir)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:27:29.351484Z",
     "start_time": "2017-11-06T17:27:29.318977Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%aimport Data.make_data\n",
    "from Data.make_data import states_in_nerc\n",
    "%aimport Analysis.index\n",
    "from Analysis.index import facility_emission_gen, group_facility_data\n",
    "%aimport Analysis.index\n",
    "from Analysis.index import facility_co2, adjust_epa_emissions, group_fuel_cats\n",
    "from Analysis.index import reduce_emission_factors, add_datetime\n",
    "%aimport util.utils\n",
    "from util.utils import rename_cols, add_facility_location"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extra NERC emissions and generation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load extra NERC generation and fuel consumption"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:27:29.374809Z",
     "start_time": "2017-11-06T17:27:29.354370Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cwd = os.getcwd()\n",
    "path = join(cwd, '..', 'Data storage', 'Derived data',\n",
    "            'NERC extra gen fuels {}.csv'.format(file_date))\n",
    "extra_nerc = pd.read_csv(path, index_col=[0, 1, 2, 3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load emission factors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:27:29.414840Z",
     "start_time": "2017-11-06T17:27:29.406038Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "path = join(cwd, '..', 'Data storage', 'Final emission factors.csv')\n",
    "ef = pd.read_csv(path, index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:27:29.448755Z",
     "start_time": "2017-11-06T17:27:29.441287Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ef_type = reduce_emission_factors(ef)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:27:29.460547Z",
     "start_time": "2017-11-06T17:27:29.451228Z"
    }
   },
   "outputs": [],
   "source": [
    "ef_type = pd.Series(ef_type, name='type')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calculate CO₂ emissions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:27:29.483194Z",
     "start_time": "2017-11-06T17:27:29.463567Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "extra_nerc.loc[:, 'total co2 (kg)'] = (extra_nerc\n",
    "                                       .loc[:, 'total fuel (mmbtu)']\n",
    "                                       .multiply(ef_type, 'type'))\n",
    "extra_nerc.loc[:, 'elec co2 (kg)'] = (extra_nerc\n",
    "                                       .loc[:, 'elec fuel (mmbtu)']\n",
    "                                       .multiply(ef_type, 'type'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:27:29.515226Z",
     "start_time": "2017-11-06T17:27:29.486122Z"
    }
   },
   "outputs": [],
   "source": [
    "extra_nerc.sort_index(inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Facilities by NERC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:27:51.521345Z",
     "start_time": "2017-11-06T17:27:51.510547Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%aimport Analysis.state2nerc\n",
    "from Analysis.state2nerc import fraction_state2nerc, add_region"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:27:51.550854Z",
     "start_time": "2017-11-06T17:27:51.526120Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "fuel_cat_folder = join(cwd, '..', 'Data storage', 'Fuel categories')\n",
    "state_cats_path = join(fuel_cat_folder, 'State_facility.json')\n",
    "\n",
    "with open(state_cats_path, 'r') as f:\n",
    "    state_fuel_cat = json.load(f)\n",
    "    \n",
    "custom_cats_path = join(fuel_cat_folder, 'Custom_results.json')\n",
    "with open(custom_cats_path, 'r') as f:\n",
    "    custom_fuel_cat = json.load(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:27:52.031911Z",
     "start_time": "2017-11-06T17:27:51.555342Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "path = join(cwd, '..', 'Data storage', 'Derived data',\n",
    "            'Monthly EPA emissions {}.csv'.format(file_date))\n",
    "epa = pd.read_csv(path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:27:52.056983Z",
     "start_time": "2017-11-06T17:27:52.034133Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "path = join(cwd, '..', 'Data storage', 'Facility labels',\n",
    "            'Facility locations_RF.csv')\n",
    "facility_labels = pd.read_csv(path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:27:59.157939Z",
     "start_time": "2017-11-06T17:27:52.060055Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "path = join(cwd, '..', 'Data storage', 'Derived data',\n",
    "            'Facility gen fuels and CO2 {}.csv'.format(file_date))\n",
    "eia_fac = pd.read_csv(path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:28:07.326534Z",
     "start_time": "2017-11-06T17:27:59.213972Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Renaming columns\n",
      "Grouping facilities\n",
      "Adjusting EPA emissions\n",
      "Caculating CO2\n",
      "Gen/fuels to state categories\n"
     ]
    }
   ],
   "source": [
    "co2, gen_fuels_state = facility_emission_gen(eia_facility=eia_fac, epa=epa,\n",
    "                                              state_fuel_cat=state_fuel_cat,\n",
    "                                              custom_fuel_cat=custom_fuel_cat,\n",
    "                                              export_state_cats=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Monthly CO₂ emissions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:28:07.609118Z",
     "start_time": "2017-11-06T17:28:07.331549Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "co2 = add_facility_location(co2, facility_labels,\n",
    "                            labels=['lat', 'lon', 'state', 'nerc', 'year'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:28:07.818065Z",
     "start_time": "2017-11-06T17:28:07.612075Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "co2_nerc = co2.groupby(['year', 'nerc', 'month'])['final co2 (kg)'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Monthly generation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:28:18.796370Z",
     "start_time": "2017-11-06T17:28:17.459668Z"
    }
   },
   "outputs": [],
   "source": [
    "gen_fuels_nerc = add_facility_location(gen_fuels_state,\n",
    "                                       facility_labels, labels=['nerc', 'year'])\n",
    "gen_fuels_nerc = (gen_fuels_nerc\n",
    "                  .groupby(['year', 'nerc', 'month', 'type'])\n",
    "                  ['generation (mwh)'].sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combine generation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:28:51.837438Z",
     "start_time": "2017-11-06T17:28:51.832133Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "total_gen = gen_fuels_nerc.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:28:52.069486Z",
     "start_time": "2017-11-06T17:28:51.840287Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "total_gen.loc[idx[2016:, :, :, :]] = (total_gen.loc[2016:] \n",
    "                                     .add(extra_nerc.loc[:, 'generation (mwh)']\n",
    "                                          , fill_value=0))\n",
    "total_gen = total_gen.reset_index()\n",
    "add_datetime(total_gen)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:28:52.204252Z",
     "start_time": "2017-11-06T17:28:52.102359Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "final = group_fuel_cats(total_gen,\n",
    "                        custom_fuel_cat, 'type',\n",
    "                        'fuel category', extra_group_cols=['nerc', 'datetime'])\n",
    "final.set_index(['nerc', 'fuel category', 'datetime'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:28:52.300816Z",
     "start_time": "2017-11-06T17:28:52.280851Z"
    }
   },
   "outputs": [],
   "source": [
    "total = final.groupby(['nerc', 'datetime']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:28:52.307679Z",
     "start_time": "2017-11-06T17:28:52.303033Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "nercs = total.index.get_level_values('nerc').unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Percent generation by fuel\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:28:52.406279Z",
     "start_time": "2017-11-06T17:28:52.310398Z"
    }
   },
   "outputs": [],
   "source": [
    "df_list = []\n",
    "for nerc in nercs:\n",
    "    percent_gen = final.loc[nerc].divide(total.loc[nerc], level='datetime')\n",
    "    percent_gen['nerc'] = nerc\n",
    "    percent_gen.set_index('nerc', append=True, inplace=True)\n",
    "    df_list.append(percent_gen)\n",
    "    \n",
    "percent_gen = pd.concat(df_list)\n",
    "percent_gen.drop(['year', 'month'], axis=1, inplace=True)\n",
    "percent_gen.columns = ['% generation']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:28:52.580429Z",
     "start_time": "2017-11-06T17:28:52.516168Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "path = join(cwd, '..', 'Data storage', 'Final NERC data',\n",
    "            'NERC percent gen {}.csv'.format(file_date))\n",
    "percent_gen.to_csv(path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:29:00.802358Z",
     "start_time": "2017-11-06T17:29:00.752682Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "total_monthly_gen = final.groupby(['fuel category', 'year', 'nerc', 'month']).sum()\n",
    "total_monthly_gen.sort_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:30:33.838463Z",
     "start_time": "2017-11-06T17:30:33.685143Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "path = join(cwd, '..', 'Data storage', 'Final NERC data',\n",
    "            'NERC generation {}.csv'.format(file_date))\n",
    "total_monthly_gen.to_csv(path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CO₂ emissions intensity by NERC region"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:30:48.134154Z",
     "start_time": "2017-11-06T17:30:48.124056Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "total_monthly_gen = total_monthly_gen.groupby(['year', 'nerc', 'month']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:30:48.173027Z",
     "start_time": "2017-11-06T17:30:48.137030Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "nerc_index = pd.concat([co2_nerc.sort_index(), total_monthly_gen.sort_index()], axis=1)\n",
    "nerc_index['index'] = nerc_index['final co2 (kg)'] / nerc_index['generation (mwh)']\n",
    "nerc_index = nerc_index.reset_index()\n",
    "add_datetime(nerc_index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Write data to file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-06T17:30:53.479109Z",
     "start_time": "2017-11-06T17:30:53.439239Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "path = join(cwd, '..', 'Data storage', 'Final NERC data',\n",
    "            'NERC gen emissions and index {}.csv'.format(file_date))\n",
    "nerc_index.to_csv(path, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py36",
   "language": "python",
   "name": "py36"
  },
  "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"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}