{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# National generation and fuel consumption\n", "The data in this notebook is generation and consumption by fuel type for the entire US. These values are larger than what would be calculated by summing facility-level data. Note that the fuel types are somewhat aggregated (coal rather than BIT, SUB, LIG, etc). So when we multiply the fuel consumption by an emissions factor there will be some level of error.\n", "\n", "The code assumes that you have already downloaded an `ELEC.txt` file from [EIA's bulk download website](https://www.eia.gov/opendata/bulkfiles.php)." ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:49:04.181106Z", "start_time": "2017-10-31T17:49:03.361179Z" }, "collapsed": true }, "outputs": [], "source": [ "import json\n", "import pandas as pd\n", "import os\n", "from os.path import join\n", "import numpy as np\n", "import sys\n", "\n", "cwd = os.getcwd()\n", "data_path = join(cwd, '..', 'Data storage')\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": null, "metadata": {}, "outputs": [], "source": [ "file_date = '2018-03-06'" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:49:04.207213Z", "start_time": "2017-10-31T17:49:04.183134Z" }, "collapsed": true }, "outputs": [], "source": [ "%load_ext watermark" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:49:04.224531Z", "start_time": "2017-10-31T17:49:04.209308Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "json 2.0.9\n", "pandas 0.21.1\n", "numpy 1.13.3\n", "CPython 3.6.3\n", "IPython 6.2.1\n" ] } ], "source": [ "%watermark -iv -v" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:49:04.262482Z", "start_time": "2017-10-31T17:49:04.227049Z" } }, "outputs": [], "source": [ "# Load the \"autoreload\" extension\n", "%load_ext autoreload\n", "\n", "# always reload modules marked with \"%aimport\"\n", "%autoreload 1\n", "\n", "# 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": 5, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:49:04.273333Z", "start_time": "2017-10-31T17:49:04.264609Z" }, "collapsed": true }, "outputs": [], "source": [ "%aimport Analysis.index\n", "from Analysis.index import add_datetime, add_quarter" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Read ELECT.txt file" ] }, { "cell_type": "code", "execution_count": 55, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:49:06.979494Z", "start_time": "2017-10-31T17:49:04.300072Z" } }, "outputs": [], "source": [ "cwd = os.getcwd()\n", "path = join(data_path, 'Raw EIA bulk', '{} ELEC.txt'.format(file_date))\n", "with open(path, 'r') as f:\n", " raw_txt = f.readlines()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Filter lines to only include total generation and fuel use\n", "Only want monthly US data for all sectors\n", "- US-99.M\n", "- ELEC.GEN, ELEC.CONS_TOT_BTU, ELEC.CONS_EG_BTU\n", "- not ALL" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Fuel codes:\n", "- WWW, wood and wood derived fuels\n", "- WND, wind\n", "- STH, solar thermal\n", "- WAS, other biomass\n", "- TSN, all solar\n", "- SUN, utility-scale solar\n", "- NUC, nuclear\n", "- NG, natural gas\n", "- PEL, petroleum liquids\n", "- SPV, utility-scale solar photovoltaic\n", "- PC, petroluem coke\n", "- OTH, other\n", "- COW, coal,\n", "- DPV, distributed photovoltaic\n", "- OOG, other gases\n", "- HPS, hydro pumped storage\n", "- HYC, conventional hydroelectric\n", "- GEO, geothermal\n", "- AOR, other renewables (total)" ] }, { "cell_type": "code", "execution_count": 56, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:49:08.570386Z", "start_time": "2017-10-31T17:49:06.992870Z" }, "collapsed": true }, "outputs": [], "source": [ "def line_to_df(line):\n", " \"\"\"\n", " Takes in a line (dictionary), returns a dataframe\n", " \"\"\"\n", " for key in ['latlon', 'source', 'copyright', 'description', \n", " 'geoset_id', 'iso3166', 'name', 'state']:\n", " line.pop(key, None)\n", "\n", " # Split the series_id up to extract information\n", " # Example: ELEC.PLANT.GEN.388-WAT-ALL.M\n", " series_id = line['series_id']\n", " series_id_list = series_id.split('.')\n", " # Use the second to last item in list rather than third\n", " plant_fuel_mover = series_id_list[-2].split('-')\n", " line['type'] = plant_fuel_mover[0]\n", "# line['state'] = plant_fuel_mover[1]\n", " line['sector'] = plant_fuel_mover[2]\n", " temp_df = pd.DataFrame(line)\n", "\n", " try:\n", " temp_df['year'] = temp_df.apply(lambda x: x['data'][0][:4], axis=1).astype(int)\n", " temp_df['month'] = temp_df.apply(lambda x: x['data'][0][-2:], axis=1).astype(int)\n", " temp_df['value'] = temp_df.apply(lambda x: x['data'][1], axis=1)\n", " temp_df.drop('data', axis=1, inplace=True)\n", " return temp_df\n", " except:\n", " exception_list.append(line)\n", " pass" ] }, { "cell_type": "code", "execution_count": 57, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:49:08.585889Z", "start_time": "2017-10-31T17:49:08.573127Z" }, "collapsed": true }, "outputs": [], "source": [ "states = [\"AL\", \"AK\", \"AZ\", \"AR\", \"CA\", \"CO\", \"CT\", \"DE\",\n", " \"FL\", \"GA\", \"HI\", \"ID\", \"IL\", \"IN\", \"IA\", \"KS\",\n", " \"KY\", \"LA\", \"ME\", \"MD\", \"MA\", \"MI\", \"MN\", \"MS\",\n", " \"MO\", \"MT\", \"NE\", \"NV\", \"NH\", \"NJ\", \"NM\", \"NY\",\n", " \"NC\", \"ND\", \"OH\", \"OK\", \"OR\", \"PA\", \"RI\", \"SC\",\n", " \"SD\", \"TN\", \"TX\", \"UT\", \"VT\", \"VA\", \"WA\", \"WV\", \"WI\", \"WY\"]" ] }, { "cell_type": "code", "execution_count": 58, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:49:08.592972Z", "start_time": "2017-10-31T17:49:08.588321Z" }, "collapsed": true, "scrolled": true }, "outputs": [], "source": [ "state_geos = ['USA-{}'.format(state) for state in states]" ] }, { "cell_type": "code", "execution_count": 59, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:49:08.637908Z", "start_time": "2017-10-31T17:49:08.596564Z" } }, "outputs": [ { "data": { "text/plain": [ "dict" ] }, "execution_count": 59, "metadata": {}, "output_type": "execute_result" } ], "source": [ "type(json.loads(raw_txt[0]))" ] }, { "cell_type": "code", "execution_count": 60, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:49:08.655737Z", "start_time": "2017-10-31T17:49:08.646593Z" } }, "outputs": [ { "data": { "text/plain": [ "'USA-CA'" ] }, "execution_count": 60, "metadata": {}, "output_type": "execute_result" } ], "source": [ "json.loads(raw_txt[0])['geography']" ] }, { "cell_type": "code", "execution_count": 61, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:49:10.033511Z", "start_time": "2017-10-31T17:49:08.659056Z" } }, "outputs": [], "source": [ "gen_rows = [row for row in raw_txt if 'ELEC.GEN' in row \n", " and 'series_id' in row \n", " and '-99.M' in row \n", " and 'ALL' not in row]\n", "\n", "total_fuel_rows = [row for row in raw_txt if 'ELEC.CONS_TOT_BTU' in row \n", " and 'series_id' in row \n", " and '-99.M' in row \n", " and 'ALL' not in row\n", " and 'US-99.m' not in row]\n", "\n", "eg_fuel_rows = [row for row in raw_txt if 'ELEC.CONS_EG_BTU' in row \n", " and 'series_id' in row \n", " and '-99.M' in row \n", " and 'ALL' not in row\n", " and 'US-99.m' not in row]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## All generation by fuel" ] }, { "cell_type": "code", "execution_count": 62, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:51:28.104940Z", "start_time": "2017-10-31T17:51:27.723553Z" }, "collapsed": true }, "outputs": [], "source": [ "gen_dicts = [json.loads(row) for row in gen_rows]" ] }, { "cell_type": "code", "execution_count": 63, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:51:51.811851Z", "start_time": "2017-10-31T17:51:41.595556Z" } }, "outputs": [], "source": [ "gen_df = pd.concat([line_to_df(x) for x in gen_dicts\n", " if x['geography'] in state_geos])" ] }, { "cell_type": "code", "execution_count": 64, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:52:03.655509Z", "start_time": "2017-10-31T17:52:03.630690Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
endfgeographylast_updatedsectorseries_idstarttypeunitsyearmonthvalue
0201712MUSA-AK2018-02-28T02:03:13-05:0099ELEC.GEN.AOR-AK-99.M200101AORthousand megawatthours20171219.39150
1201712MUSA-AK2018-02-28T02:03:13-05:0099ELEC.GEN.AOR-AK-99.M200101AORthousand megawatthours20171119.76574
2201712MUSA-AK2018-02-28T02:03:13-05:0099ELEC.GEN.AOR-AK-99.M200101AORthousand megawatthours20171020.52333
3201712MUSA-AK2018-02-28T02:03:13-05:0099ELEC.GEN.AOR-AK-99.M200101AORthousand megawatthours2017913.56737
4201712MUSA-AK2018-02-28T02:03:13-05:0099ELEC.GEN.AOR-AK-99.M200101AORthousand megawatthours2017811.61728
\n", "
" ], "text/plain": [ " end f geography last_updated sector \\\n", "0 201712 M USA-AK 2018-02-28T02:03:13-05:00 99 \n", "1 201712 M USA-AK 2018-02-28T02:03:13-05:00 99 \n", "2 201712 M USA-AK 2018-02-28T02:03:13-05:00 99 \n", "3 201712 M USA-AK 2018-02-28T02:03:13-05:00 99 \n", "4 201712 M USA-AK 2018-02-28T02:03:13-05:00 99 \n", "\n", " series_id start type units year month \\\n", "0 ELEC.GEN.AOR-AK-99.M 200101 AOR thousand megawatthours 2017 12 \n", "1 ELEC.GEN.AOR-AK-99.M 200101 AOR thousand megawatthours 2017 11 \n", "2 ELEC.GEN.AOR-AK-99.M 200101 AOR thousand megawatthours 2017 10 \n", "3 ELEC.GEN.AOR-AK-99.M 200101 AOR thousand megawatthours 2017 9 \n", "4 ELEC.GEN.AOR-AK-99.M 200101 AOR thousand megawatthours 2017 8 \n", "\n", " value \n", "0 19.39150 \n", "1 19.76574 \n", "2 20.52333 \n", "3 13.56737 \n", "4 11.61728 " ] }, "execution_count": 64, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#drop\n", "gen_df.head()" ] }, { "cell_type": "code", "execution_count": 65, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:52:12.850899Z", "start_time": "2017-10-31T17:52:12.839775Z" } }, "outputs": [ { "data": { "text/plain": [ "array(['USA-AK', 'USA-AZ', 'USA-CO', 'USA-MI', 'USA-DE', 'USA-MO',\n", " 'USA-WA', 'USA-NY', 'USA-CA', 'USA-PA', 'USA-MN', 'USA-MS',\n", " 'USA-MT', 'USA-WI', 'USA-MD', 'USA-OH', 'USA-ME', 'USA-KS',\n", " 'USA-WY', 'USA-RI', 'USA-NV', 'USA-OK', 'USA-OR', 'USA-NC',\n", " 'USA-AR', 'USA-WV', 'USA-SD', 'USA-HI', 'USA-GA', 'USA-ND',\n", " 'USA-TN', 'USA-CT', 'USA-NE', 'USA-SC', 'USA-LA', 'USA-VA',\n", " 'USA-IL', 'USA-AL', 'USA-IN', 'USA-MA', 'USA-IA', 'USA-KY',\n", " 'USA-FL', 'USA-ID', 'USA-NM', 'USA-NJ', 'USA-UT', 'USA-TX',\n", " 'USA-VT', 'USA-NH'], dtype=object)" ] }, "execution_count": 65, "metadata": {}, "output_type": "execute_result" } ], "source": [ "gen_df['geography'].unique()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Multiply generation values by 1000 and change the units to MWh" ] }, { "cell_type": "code", "execution_count": 66, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:52:22.002882Z", "start_time": "2017-10-31T17:52:21.970781Z" }, "collapsed": true }, "outputs": [], "source": [ "gen_df.loc[:,'value'] *= 1000\n", "gen_df.loc[:,'units'] = 'megawatthours'" ] }, { "cell_type": "code", "execution_count": 67, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:52:32.017315Z", "start_time": "2017-10-31T17:52:31.992727Z" }, "collapsed": true }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\gschivley\\Anaconda2\\envs\\psci\\lib\\site-packages\\ipykernel_launcher.py:1: FutureWarning: Using 'rename_axis' to alter labels is deprecated. Use '.rename' instead\n", " \"\"\"Entry point for launching an IPython kernel.\n" ] } ], "source": [ "gen_df.rename_axis({'value':'generation (MWh)'}, axis=1, inplace=True)" ] }, { "cell_type": "code", "execution_count": 68, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:52:32.171144Z", "start_time": "2017-10-31T17:52:32.020555Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
endfgeographylast_updatedsectorseries_idstarttypeunitsyearmonthgeneration (MWh)
5201712MUSA-AK2018-02-28T02:03:13-05:0099ELEC.GEN.AOR-AK-99.M200101AORmegawatthours20177NaN
10201712MUSA-AK2018-02-28T02:03:13-05:0099ELEC.GEN.AOR-AK-99.M200101AORmegawatthours20172NaN
11201712MUSA-AK2018-02-28T02:03:13-05:0099ELEC.GEN.AOR-AK-99.M200101AORmegawatthours20171NaN
61201712MUSA-AK2018-02-28T02:03:13-05:0099ELEC.GEN.AOR-AK-99.M200101AORmegawatthours201211NaN
71201712MUSA-AK2018-02-28T02:03:13-05:0099ELEC.GEN.AOR-AK-99.M200101AORmegawatthours20121NaN
12201712MUSA-MI2018-02-28T02:03:13-05:0099ELEC.GEN.DPV-MI-99.M201401DPVmegawatthours201612NaN
24201712MUSA-WY2018-02-28T02:03:13-05:0099ELEC.GEN.DPV-WY-99.M201401DPVmegawatthours201512NaN
25201712MUSA-WY2018-02-28T02:03:13-05:0099ELEC.GEN.DPV-WY-99.M201401DPVmegawatthours201511NaN
4201712MUSA-HI2018-02-28T02:03:13-05:0099ELEC.GEN.HYC-HI-99.M200101HYCmegawatthours20178NaN
5201712MUSA-HI2018-02-28T02:03:13-05:0099ELEC.GEN.HYC-HI-99.M200101HYCmegawatthours20177NaN
6201712MUSA-HI2018-02-28T02:03:13-05:0099ELEC.GEN.HYC-HI-99.M200101HYCmegawatthours20176NaN
7201712MUSA-HI2018-02-28T02:03:13-05:0099ELEC.GEN.HYC-HI-99.M200101HYCmegawatthours20175NaN
8201712MUSA-HI2018-02-28T02:03:13-05:0099ELEC.GEN.HYC-HI-99.M200101HYCmegawatthours20174NaN
9201712MUSA-HI2018-02-28T02:03:13-05:0099ELEC.GEN.HYC-HI-99.M200101HYCmegawatthours20173NaN
10201712MUSA-HI2018-02-28T02:03:13-05:0099ELEC.GEN.HYC-HI-99.M200101HYCmegawatthours20172NaN
61201712MUSA-OR2018-02-28T02:03:13-05:0099ELEC.GEN.GEO-OR-99.M201211GEOmegawatthours201211NaN
0201712MUSA-GA2018-02-28T02:03:13-05:0099ELEC.GEN.DPV-GA-99.M201401DPVmegawatthours201712NaN
1201712MUSA-GA2018-02-28T02:03:13-05:0099ELEC.GEN.DPV-GA-99.M201401DPVmegawatthours201711NaN
2201712MUSA-GA2018-02-28T02:03:13-05:0099ELEC.GEN.DPV-GA-99.M201401DPVmegawatthours201710NaN
3201712MUSA-GA2018-02-28T02:03:13-05:0099ELEC.GEN.DPV-GA-99.M201401DPVmegawatthours20179NaN
4201712MUSA-GA2018-02-28T02:03:13-05:0099ELEC.GEN.DPV-GA-99.M201401DPVmegawatthours20178NaN
12201712MUSA-GA2018-02-28T02:03:13-05:0099ELEC.GEN.DPV-GA-99.M201401DPVmegawatthours201612NaN
13201712MUSA-GA2018-02-28T02:03:13-05:0099ELEC.GEN.DPV-GA-99.M201401DPVmegawatthours201611NaN
14201712MUSA-GA2018-02-28T02:03:13-05:0099ELEC.GEN.DPV-GA-99.M201401DPVmegawatthours201610NaN
15201712MUSA-GA2018-02-28T02:03:13-05:0099ELEC.GEN.DPV-GA-99.M201401DPVmegawatthours20169NaN
16201712MUSA-GA2018-02-28T02:03:13-05:0099ELEC.GEN.DPV-GA-99.M201401DPVmegawatthours20168NaN
17201712MUSA-GA2018-02-28T02:03:13-05:0099ELEC.GEN.DPV-GA-99.M201401DPVmegawatthours20167NaN
18201712MUSA-GA2018-02-28T02:03:13-05:0099ELEC.GEN.DPV-GA-99.M201401DPVmegawatthours20166NaN
19201712MUSA-GA2018-02-28T02:03:13-05:0099ELEC.GEN.DPV-GA-99.M201401DPVmegawatthours20165NaN
20201712MUSA-GA2018-02-28T02:03:13-05:0099ELEC.GEN.DPV-GA-99.M201401DPVmegawatthours20164NaN
.......................................
30201702MUSA-WV2017-05-24T14:26:30-04:0099ELEC.GEN.WAS-WV-99.M200101WASmegawatthours20148NaN
31201702MUSA-WV2017-05-24T14:26:30-04:0099ELEC.GEN.WAS-WV-99.M200101WASmegawatthours20147NaN
32201702MUSA-WV2017-05-24T14:26:30-04:0099ELEC.GEN.WAS-WV-99.M200101WASmegawatthours20146NaN
33201702MUSA-WV2017-05-24T14:26:30-04:0099ELEC.GEN.WAS-WV-99.M200101WASmegawatthours20145NaN
34201702MUSA-WV2017-05-24T14:26:30-04:0099ELEC.GEN.WAS-WV-99.M200101WASmegawatthours20144NaN
35201702MUSA-WV2017-05-24T14:26:30-04:0099ELEC.GEN.WAS-WV-99.M200101WASmegawatthours20143NaN
36201702MUSA-WV2017-05-24T14:26:30-04:0099ELEC.GEN.WAS-WV-99.M200101WASmegawatthours20142NaN
37201702MUSA-WV2017-05-24T14:26:30-04:0099ELEC.GEN.WAS-WV-99.M200101WASmegawatthours20141NaN
0201410MUSA-AK2016-12-19T17:19:30-05:0099ELEC.GEN.OOG-AK-99.M201101OOGmegawatthours201410NaN
1201410MUSA-AK2016-12-19T17:19:30-05:0099ELEC.GEN.OOG-AK-99.M201101OOGmegawatthours20149NaN
2201410MUSA-AK2016-12-19T17:19:30-05:0099ELEC.GEN.OOG-AK-99.M201101OOGmegawatthours20148NaN
3201410MUSA-AK2016-12-19T17:19:30-05:0099ELEC.GEN.OOG-AK-99.M201101OOGmegawatthours20147NaN
4201410MUSA-AK2016-12-19T17:19:30-05:0099ELEC.GEN.OOG-AK-99.M201101OOGmegawatthours20146NaN
5201410MUSA-AK2016-12-19T17:19:30-05:0099ELEC.GEN.OOG-AK-99.M201101OOGmegawatthours20145NaN
6201410MUSA-AK2016-12-19T17:19:30-05:0099ELEC.GEN.OOG-AK-99.M201101OOGmegawatthours20144NaN
7201410MUSA-AK2016-12-19T17:19:30-05:0099ELEC.GEN.OOG-AK-99.M201101OOGmegawatthours20143NaN
8201410MUSA-AK2016-12-19T17:19:30-05:0099ELEC.GEN.OOG-AK-99.M201101OOGmegawatthours20142NaN
9201410MUSA-AK2016-12-19T17:19:30-05:0099ELEC.GEN.OOG-AK-99.M201101OOGmegawatthours20141NaN
10201410MUSA-AK2016-12-19T17:19:30-05:0099ELEC.GEN.OOG-AK-99.M201101OOGmegawatthours201312NaN
11201410MUSA-AK2016-12-19T17:19:30-05:0099ELEC.GEN.OOG-AK-99.M201101OOGmegawatthours201311NaN
12201410MUSA-AK2016-12-19T17:19:30-05:0099ELEC.GEN.OOG-AK-99.M201101OOGmegawatthours201310NaN
13201410MUSA-AK2016-12-19T17:19:30-05:0099ELEC.GEN.OOG-AK-99.M201101OOGmegawatthours20139NaN
14201410MUSA-AK2016-12-19T17:19:30-05:0099ELEC.GEN.OOG-AK-99.M201101OOGmegawatthours20138NaN
15201410MUSA-AK2016-12-19T17:19:30-05:0099ELEC.GEN.OOG-AK-99.M201101OOGmegawatthours20137NaN
16201410MUSA-AK2016-12-19T17:19:30-05:0099ELEC.GEN.OOG-AK-99.M201101OOGmegawatthours20136NaN
17201410MUSA-AK2016-12-19T17:19:30-05:0099ELEC.GEN.OOG-AK-99.M201101OOGmegawatthours20135NaN
18201410MUSA-AK2016-12-19T17:19:30-05:0099ELEC.GEN.OOG-AK-99.M201101OOGmegawatthours20134NaN
19201410MUSA-AK2016-12-19T17:19:30-05:0099ELEC.GEN.OOG-AK-99.M201101OOGmegawatthours20133NaN
20201410MUSA-AK2016-12-19T17:19:30-05:0099ELEC.GEN.OOG-AK-99.M201101OOGmegawatthours20132NaN
21201410MUSA-AK2016-12-19T17:19:30-05:0099ELEC.GEN.OOG-AK-99.M201101OOGmegawatthours20131NaN
\n", "

900 rows × 12 columns

\n", "
" ], "text/plain": [ " end f geography last_updated sector \\\n", "5 201712 M USA-AK 2018-02-28T02:03:13-05:00 99 \n", "10 201712 M USA-AK 2018-02-28T02:03:13-05:00 99 \n", "11 201712 M USA-AK 2018-02-28T02:03:13-05:00 99 \n", "61 201712 M USA-AK 2018-02-28T02:03:13-05:00 99 \n", "71 201712 M USA-AK 2018-02-28T02:03:13-05:00 99 \n", "12 201712 M USA-MI 2018-02-28T02:03:13-05:00 99 \n", "24 201712 M USA-WY 2018-02-28T02:03:13-05:00 99 \n", "25 201712 M USA-WY 2018-02-28T02:03:13-05:00 99 \n", "4 201712 M USA-HI 2018-02-28T02:03:13-05:00 99 \n", "5 201712 M USA-HI 2018-02-28T02:03:13-05:00 99 \n", "6 201712 M USA-HI 2018-02-28T02:03:13-05:00 99 \n", "7 201712 M USA-HI 2018-02-28T02:03:13-05:00 99 \n", "8 201712 M USA-HI 2018-02-28T02:03:13-05:00 99 \n", "9 201712 M USA-HI 2018-02-28T02:03:13-05:00 99 \n", "10 201712 M USA-HI 2018-02-28T02:03:13-05:00 99 \n", "61 201712 M USA-OR 2018-02-28T02:03:13-05:00 99 \n", "0 201712 M USA-GA 2018-02-28T02:03:13-05:00 99 \n", "1 201712 M USA-GA 2018-02-28T02:03:13-05:00 99 \n", "2 201712 M USA-GA 2018-02-28T02:03:13-05:00 99 \n", "3 201712 M USA-GA 2018-02-28T02:03:13-05:00 99 \n", "4 201712 M USA-GA 2018-02-28T02:03:13-05:00 99 \n", "12 201712 M USA-GA 2018-02-28T02:03:13-05:00 99 \n", "13 201712 M USA-GA 2018-02-28T02:03:13-05:00 99 \n", "14 201712 M USA-GA 2018-02-28T02:03:13-05:00 99 \n", "15 201712 M USA-GA 2018-02-28T02:03:13-05:00 99 \n", "16 201712 M USA-GA 2018-02-28T02:03:13-05:00 99 \n", "17 201712 M USA-GA 2018-02-28T02:03:13-05:00 99 \n", "18 201712 M USA-GA 2018-02-28T02:03:13-05:00 99 \n", "19 201712 M USA-GA 2018-02-28T02:03:13-05:00 99 \n", "20 201712 M USA-GA 2018-02-28T02:03:13-05:00 99 \n", ".. ... .. ... ... ... \n", "30 201702 M USA-WV 2017-05-24T14:26:30-04:00 99 \n", "31 201702 M USA-WV 2017-05-24T14:26:30-04:00 99 \n", "32 201702 M USA-WV 2017-05-24T14:26:30-04:00 99 \n", "33 201702 M USA-WV 2017-05-24T14:26:30-04:00 99 \n", "34 201702 M USA-WV 2017-05-24T14:26:30-04:00 99 \n", "35 201702 M USA-WV 2017-05-24T14:26:30-04:00 99 \n", "36 201702 M USA-WV 2017-05-24T14:26:30-04:00 99 \n", "37 201702 M USA-WV 2017-05-24T14:26:30-04:00 99 \n", "0 201410 M USA-AK 2016-12-19T17:19:30-05:00 99 \n", "1 201410 M USA-AK 2016-12-19T17:19:30-05:00 99 \n", "2 201410 M USA-AK 2016-12-19T17:19:30-05:00 99 \n", "3 201410 M USA-AK 2016-12-19T17:19:30-05:00 99 \n", "4 201410 M USA-AK 2016-12-19T17:19:30-05:00 99 \n", "5 201410 M USA-AK 2016-12-19T17:19:30-05:00 99 \n", "6 201410 M USA-AK 2016-12-19T17:19:30-05:00 99 \n", "7 201410 M USA-AK 2016-12-19T17:19:30-05:00 99 \n", "8 201410 M USA-AK 2016-12-19T17:19:30-05:00 99 \n", "9 201410 M USA-AK 2016-12-19T17:19:30-05:00 99 \n", "10 201410 M USA-AK 2016-12-19T17:19:30-05:00 99 \n", "11 201410 M USA-AK 2016-12-19T17:19:30-05:00 99 \n", "12 201410 M USA-AK 2016-12-19T17:19:30-05:00 99 \n", "13 201410 M USA-AK 2016-12-19T17:19:30-05:00 99 \n", "14 201410 M USA-AK 2016-12-19T17:19:30-05:00 99 \n", "15 201410 M USA-AK 2016-12-19T17:19:30-05:00 99 \n", "16 201410 M USA-AK 2016-12-19T17:19:30-05:00 99 \n", "17 201410 M USA-AK 2016-12-19T17:19:30-05:00 99 \n", "18 201410 M USA-AK 2016-12-19T17:19:30-05:00 99 \n", "19 201410 M USA-AK 2016-12-19T17:19:30-05:00 99 \n", "20 201410 M USA-AK 2016-12-19T17:19:30-05:00 99 \n", "21 201410 M USA-AK 2016-12-19T17:19:30-05:00 99 \n", "\n", " series_id start type units year month \\\n", "5 ELEC.GEN.AOR-AK-99.M 200101 AOR megawatthours 2017 7 \n", "10 ELEC.GEN.AOR-AK-99.M 200101 AOR megawatthours 2017 2 \n", "11 ELEC.GEN.AOR-AK-99.M 200101 AOR megawatthours 2017 1 \n", "61 ELEC.GEN.AOR-AK-99.M 200101 AOR megawatthours 2012 11 \n", "71 ELEC.GEN.AOR-AK-99.M 200101 AOR megawatthours 2012 1 \n", "12 ELEC.GEN.DPV-MI-99.M 201401 DPV megawatthours 2016 12 \n", "24 ELEC.GEN.DPV-WY-99.M 201401 DPV megawatthours 2015 12 \n", "25 ELEC.GEN.DPV-WY-99.M 201401 DPV megawatthours 2015 11 \n", "4 ELEC.GEN.HYC-HI-99.M 200101 HYC megawatthours 2017 8 \n", "5 ELEC.GEN.HYC-HI-99.M 200101 HYC megawatthours 2017 7 \n", "6 ELEC.GEN.HYC-HI-99.M 200101 HYC megawatthours 2017 6 \n", "7 ELEC.GEN.HYC-HI-99.M 200101 HYC megawatthours 2017 5 \n", "8 ELEC.GEN.HYC-HI-99.M 200101 HYC megawatthours 2017 4 \n", "9 ELEC.GEN.HYC-HI-99.M 200101 HYC megawatthours 2017 3 \n", "10 ELEC.GEN.HYC-HI-99.M 200101 HYC megawatthours 2017 2 \n", "61 ELEC.GEN.GEO-OR-99.M 201211 GEO megawatthours 2012 11 \n", "0 ELEC.GEN.DPV-GA-99.M 201401 DPV megawatthours 2017 12 \n", "1 ELEC.GEN.DPV-GA-99.M 201401 DPV megawatthours 2017 11 \n", "2 ELEC.GEN.DPV-GA-99.M 201401 DPV megawatthours 2017 10 \n", "3 ELEC.GEN.DPV-GA-99.M 201401 DPV megawatthours 2017 9 \n", "4 ELEC.GEN.DPV-GA-99.M 201401 DPV megawatthours 2017 8 \n", "12 ELEC.GEN.DPV-GA-99.M 201401 DPV megawatthours 2016 12 \n", "13 ELEC.GEN.DPV-GA-99.M 201401 DPV megawatthours 2016 11 \n", "14 ELEC.GEN.DPV-GA-99.M 201401 DPV megawatthours 2016 10 \n", "15 ELEC.GEN.DPV-GA-99.M 201401 DPV megawatthours 2016 9 \n", "16 ELEC.GEN.DPV-GA-99.M 201401 DPV megawatthours 2016 8 \n", "17 ELEC.GEN.DPV-GA-99.M 201401 DPV megawatthours 2016 7 \n", "18 ELEC.GEN.DPV-GA-99.M 201401 DPV megawatthours 2016 6 \n", "19 ELEC.GEN.DPV-GA-99.M 201401 DPV megawatthours 2016 5 \n", "20 ELEC.GEN.DPV-GA-99.M 201401 DPV megawatthours 2016 4 \n", ".. ... ... ... ... ... ... \n", "30 ELEC.GEN.WAS-WV-99.M 200101 WAS megawatthours 2014 8 \n", "31 ELEC.GEN.WAS-WV-99.M 200101 WAS megawatthours 2014 7 \n", "32 ELEC.GEN.WAS-WV-99.M 200101 WAS megawatthours 2014 6 \n", "33 ELEC.GEN.WAS-WV-99.M 200101 WAS megawatthours 2014 5 \n", "34 ELEC.GEN.WAS-WV-99.M 200101 WAS megawatthours 2014 4 \n", "35 ELEC.GEN.WAS-WV-99.M 200101 WAS megawatthours 2014 3 \n", "36 ELEC.GEN.WAS-WV-99.M 200101 WAS megawatthours 2014 2 \n", "37 ELEC.GEN.WAS-WV-99.M 200101 WAS megawatthours 2014 1 \n", "0 ELEC.GEN.OOG-AK-99.M 201101 OOG megawatthours 2014 10 \n", "1 ELEC.GEN.OOG-AK-99.M 201101 OOG megawatthours 2014 9 \n", "2 ELEC.GEN.OOG-AK-99.M 201101 OOG megawatthours 2014 8 \n", "3 ELEC.GEN.OOG-AK-99.M 201101 OOG megawatthours 2014 7 \n", "4 ELEC.GEN.OOG-AK-99.M 201101 OOG megawatthours 2014 6 \n", "5 ELEC.GEN.OOG-AK-99.M 201101 OOG megawatthours 2014 5 \n", "6 ELEC.GEN.OOG-AK-99.M 201101 OOG megawatthours 2014 4 \n", "7 ELEC.GEN.OOG-AK-99.M 201101 OOG megawatthours 2014 3 \n", "8 ELEC.GEN.OOG-AK-99.M 201101 OOG megawatthours 2014 2 \n", "9 ELEC.GEN.OOG-AK-99.M 201101 OOG megawatthours 2014 1 \n", "10 ELEC.GEN.OOG-AK-99.M 201101 OOG megawatthours 2013 12 \n", "11 ELEC.GEN.OOG-AK-99.M 201101 OOG megawatthours 2013 11 \n", "12 ELEC.GEN.OOG-AK-99.M 201101 OOG megawatthours 2013 10 \n", "13 ELEC.GEN.OOG-AK-99.M 201101 OOG megawatthours 2013 9 \n", "14 ELEC.GEN.OOG-AK-99.M 201101 OOG megawatthours 2013 8 \n", "15 ELEC.GEN.OOG-AK-99.M 201101 OOG megawatthours 2013 7 \n", "16 ELEC.GEN.OOG-AK-99.M 201101 OOG megawatthours 2013 6 \n", "17 ELEC.GEN.OOG-AK-99.M 201101 OOG megawatthours 2013 5 \n", "18 ELEC.GEN.OOG-AK-99.M 201101 OOG megawatthours 2013 4 \n", "19 ELEC.GEN.OOG-AK-99.M 201101 OOG megawatthours 2013 3 \n", "20 ELEC.GEN.OOG-AK-99.M 201101 OOG megawatthours 2013 2 \n", "21 ELEC.GEN.OOG-AK-99.M 201101 OOG megawatthours 2013 1 \n", "\n", " generation (MWh) \n", "5 NaN \n", "10 NaN \n", "11 NaN \n", "61 NaN \n", "71 NaN \n", "12 NaN \n", "24 NaN \n", "25 NaN \n", "4 NaN \n", "5 NaN \n", "6 NaN \n", "7 NaN \n", "8 NaN \n", "9 NaN \n", "10 NaN \n", "61 NaN \n", "0 NaN \n", "1 NaN \n", "2 NaN \n", "3 NaN \n", "4 NaN \n", "12 NaN \n", "13 NaN \n", "14 NaN \n", "15 NaN \n", "16 NaN \n", "17 NaN \n", "18 NaN \n", "19 NaN \n", "20 NaN \n", ".. ... \n", "30 NaN \n", "31 NaN \n", "32 NaN \n", "33 NaN \n", "34 NaN \n", "35 NaN \n", "36 NaN \n", "37 NaN \n", "0 NaN \n", "1 NaN \n", "2 NaN \n", "3 NaN \n", "4 NaN \n", "5 NaN \n", "6 NaN \n", "7 NaN \n", "8 NaN \n", "9 NaN \n", "10 NaN \n", "11 NaN \n", "12 NaN \n", "13 NaN \n", "14 NaN \n", "15 NaN \n", "16 NaN \n", "17 NaN \n", "18 NaN \n", "19 NaN \n", "20 NaN \n", "21 NaN \n", "\n", "[900 rows x 12 columns]" ] }, "execution_count": 68, "metadata": {}, "output_type": "execute_result" } ], "source": [ "gen_df.loc[gen_df.isnull().any(axis=1)]" ] }, { "cell_type": "code", "execution_count": 69, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:52:32.337577Z", "start_time": "2017-10-31T17:52:32.173250Z" }, "collapsed": true }, "outputs": [], "source": [ "gen_df.dropna(inplace=True)" ] }, { "cell_type": "code", "execution_count": 70, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:52:32.432083Z", "start_time": "2017-10-31T17:52:32.344291Z" }, "collapsed": true }, "outputs": [], "source": [ "gen_df.set_index(['type', 'year', 'month', 'geography'], inplace=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Total fuel consumption" ] }, { "cell_type": "code", "execution_count": 71, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:52:56.725934Z", "start_time": "2017-10-31T17:52:56.689529Z" }, "collapsed": true }, "outputs": [], "source": [ "total_fuel_dict = [json.loads(row) for row in total_fuel_rows]" ] }, { "cell_type": "code", "execution_count": 72, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:52:59.336234Z", "start_time": "2017-10-31T17:52:56.727860Z" }, "collapsed": true }, "outputs": [], "source": [ "total_fuel_df = pd.concat([line_to_df(x) for x in total_fuel_dict\n", " if x['geography'] in state_geos])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Multiply generation values by 1,000,000 and change the units to MMBtu" ] }, { "cell_type": "code", "execution_count": 73, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:53:08.715443Z", "start_time": "2017-10-31T17:53:08.704070Z" }, "collapsed": true }, "outputs": [], "source": [ "total_fuel_df.loc[:,'value'] *= 1E6\n", "total_fuel_df.loc[:,'units'] = 'mmbtu'" ] }, { "cell_type": "code", "execution_count": 74, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:53:26.292463Z", "start_time": "2017-10-31T17:53:26.282484Z" }, "collapsed": true }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\gschivley\\Anaconda2\\envs\\psci\\lib\\site-packages\\ipykernel_launcher.py:1: FutureWarning: Using 'rename_axis' to alter labels is deprecated. Use '.rename' instead\n", " \"\"\"Entry point for launching an IPython kernel.\n" ] } ], "source": [ "total_fuel_df.rename_axis({'value':'total fuel (mmbtu)'}, axis=1, inplace=True)" ] }, { "cell_type": "code", "execution_count": 75, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:53:36.092110Z", "start_time": "2017-10-31T17:53:36.074386Z" }, "collapsed": true }, "outputs": [], "source": [ "total_fuel_df.set_index(['type', 'year', 'month', 'geography'], inplace=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Drop nans" ] }, { "cell_type": "code", "execution_count": 76, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:53:43.878486Z", "start_time": "2017-10-31T17:53:43.802556Z" }, "scrolled": true }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
endflast_updatedsectorseries_idstartunitstotal fuel (mmbtu)
typeyearmonthgeography
COW20176USA-ME201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-ME-99.M200101mmbtuNaN
12USA-AK201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-AK-99.M200101mmbtuNaN
7USA-AK201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-AK-99.M200101mmbtuNaN
6USA-AK201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-AK-99.M200101mmbtuNaN
3USA-AK201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-AK-99.M200101mmbtuNaN
12USA-ID201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-ID-99.M200101mmbtuNaN
11USA-ID201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-ID-99.M200101mmbtuNaN
10USA-ID201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-ID-99.M200101mmbtuNaN
9USA-ID201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-ID-99.M200101mmbtuNaN
8USA-ID201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-ID-99.M200101mmbtuNaN
7USA-ID201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-ID-99.M200101mmbtuNaN
6USA-ID201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-ID-99.M200101mmbtuNaN
5USA-ID201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-ID-99.M200101mmbtuNaN
4USA-ID201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-ID-99.M200101mmbtuNaN
3USA-ID201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-ID-99.M200101mmbtuNaN
2USA-ID201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-ID-99.M200101mmbtuNaN
1USA-ID201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-ID-99.M200101mmbtuNaN
20151USA-HI201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-HI-99.M200101mmbtuNaN
PEL201712USA-MT201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-MT-99.M200101mmbtuNaN
11USA-MT201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-MT-99.M200101mmbtuNaN
8USA-MT201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-MT-99.M200101mmbtuNaN
20152USA-KY201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-KY-99.M200101mmbtuNaN
20178USA-KS201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-KS-99.M200101mmbtuNaN
12USA-LA201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-LA-99.M200101mmbtuNaN
11USA-LA201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-LA-99.M200101mmbtuNaN
10USA-LA201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-LA-99.M200101mmbtuNaN
9USA-LA201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-LA-99.M200101mmbtuNaN
8USA-LA201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-LA-99.M200101mmbtuNaN
7USA-LA201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-LA-99.M200101mmbtuNaN
6USA-LA201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-LA-99.M200101mmbtuNaN
..............................
9USA-NE201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-NE-99.M200101mmbtuNaN
8USA-NE201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-NE-99.M200101mmbtuNaN
7USA-NE201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-NE-99.M200101mmbtuNaN
6USA-NE201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-NE-99.M200101mmbtuNaN
5USA-NE201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-NE-99.M200101mmbtuNaN
4USA-NE201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-NE-99.M200101mmbtuNaN
3USA-NE201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-NE-99.M200101mmbtuNaN
2USA-NE201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-NE-99.M200101mmbtuNaN
1USA-NE201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-NE-99.M200101mmbtuNaN
6USA-NM201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-NM-99.M200101mmbtuNaN
11USA-NJ201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-NJ-99.M200101mmbtuNaN
5USA-NJ201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-NJ-99.M200101mmbtuNaN
3USA-NJ201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-NJ-99.M200101mmbtuNaN
2USA-NJ201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-NJ-99.M200101mmbtuNaN
20152USA-NV201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-NV-99.M200101mmbtuNaN
201312USA-PA201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-PA-99.M200101mmbtuNaN
2USA-PA201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-PA-99.M200101mmbtuNaN
PC201412USA-NJ201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PC-NJ-99.M201101mmbtuNaN
6USA-NJ201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PC-NJ-99.M201101mmbtuNaN
5USA-NJ201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PC-NJ-99.M201101mmbtuNaN
4USA-NJ201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PC-NJ-99.M201101mmbtuNaN
3USA-NJ201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PC-NJ-99.M201101mmbtuNaN
2USA-NJ201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PC-NJ-99.M201101mmbtuNaN
1USA-NJ201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PC-NJ-99.M201101mmbtuNaN
PEL20176USA-CO201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-CO-99.M200101mmbtuNaN
4USA-CO201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-CO-99.M200101mmbtuNaN
1USA-CO201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-CO-99.M200101mmbtuNaN
12USA-IL201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-IL-99.M200101mmbtuNaN
4USA-CT201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-CT-99.M200101mmbtuNaN
NG201710USA-MO201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.NG-MO-99.M200101mmbtuNaN
\n", "

197 rows × 8 columns

\n", "
" ], "text/plain": [ " end f last_updated sector \\\n", "type year month geography \n", "COW 2017 6 USA-ME 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 12 USA-AK 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 7 USA-AK 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 6 USA-AK 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 3 USA-AK 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 12 USA-ID 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 11 USA-ID 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 10 USA-ID 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 9 USA-ID 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 8 USA-ID 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 7 USA-ID 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 6 USA-ID 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 5 USA-ID 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 4 USA-ID 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 3 USA-ID 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 2 USA-ID 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 1 USA-ID 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 2015 1 USA-HI 201712 M 2018-02-28T02:03:13-05:00 99 \n", "PEL 2017 12 USA-MT 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 11 USA-MT 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 8 USA-MT 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 2015 2 USA-KY 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 2017 8 USA-KS 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 12 USA-LA 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 11 USA-LA 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 10 USA-LA 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 9 USA-LA 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 8 USA-LA 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 7 USA-LA 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 6 USA-LA 201712 M 2018-02-28T02:03:13-05:00 99 \n", "... ... .. ... ... \n", " 9 USA-NE 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 8 USA-NE 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 7 USA-NE 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 6 USA-NE 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 5 USA-NE 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 4 USA-NE 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 3 USA-NE 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 2 USA-NE 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 1 USA-NE 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 6 USA-NM 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 11 USA-NJ 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 5 USA-NJ 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 3 USA-NJ 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 2 USA-NJ 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 2015 2 USA-NV 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 2013 12 USA-PA 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 2 USA-PA 201712 M 2018-02-28T02:03:13-05:00 99 \n", "PC 2014 12 USA-NJ 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 6 USA-NJ 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 5 USA-NJ 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 4 USA-NJ 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 3 USA-NJ 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 2 USA-NJ 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 1 USA-NJ 201712 M 2018-02-28T02:03:13-05:00 99 \n", "PEL 2017 6 USA-CO 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 4 USA-CO 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 1 USA-CO 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 12 USA-IL 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 4 USA-CT 201712 M 2018-02-28T02:03:13-05:00 99 \n", "NG 2017 10 USA-MO 201712 M 2018-02-28T02:03:13-05:00 99 \n", "\n", " series_id start units \\\n", "type year month geography \n", "COW 2017 6 USA-ME ELEC.CONS_TOT_BTU.COW-ME-99.M 200101 mmbtu \n", " 12 USA-AK ELEC.CONS_TOT_BTU.COW-AK-99.M 200101 mmbtu \n", " 7 USA-AK ELEC.CONS_TOT_BTU.COW-AK-99.M 200101 mmbtu \n", " 6 USA-AK ELEC.CONS_TOT_BTU.COW-AK-99.M 200101 mmbtu \n", " 3 USA-AK ELEC.CONS_TOT_BTU.COW-AK-99.M 200101 mmbtu \n", " 12 USA-ID ELEC.CONS_TOT_BTU.COW-ID-99.M 200101 mmbtu \n", " 11 USA-ID ELEC.CONS_TOT_BTU.COW-ID-99.M 200101 mmbtu \n", " 10 USA-ID ELEC.CONS_TOT_BTU.COW-ID-99.M 200101 mmbtu \n", " 9 USA-ID ELEC.CONS_TOT_BTU.COW-ID-99.M 200101 mmbtu \n", " 8 USA-ID ELEC.CONS_TOT_BTU.COW-ID-99.M 200101 mmbtu \n", " 7 USA-ID ELEC.CONS_TOT_BTU.COW-ID-99.M 200101 mmbtu \n", " 6 USA-ID ELEC.CONS_TOT_BTU.COW-ID-99.M 200101 mmbtu \n", " 5 USA-ID ELEC.CONS_TOT_BTU.COW-ID-99.M 200101 mmbtu \n", " 4 USA-ID ELEC.CONS_TOT_BTU.COW-ID-99.M 200101 mmbtu \n", " 3 USA-ID ELEC.CONS_TOT_BTU.COW-ID-99.M 200101 mmbtu \n", " 2 USA-ID ELEC.CONS_TOT_BTU.COW-ID-99.M 200101 mmbtu \n", " 1 USA-ID ELEC.CONS_TOT_BTU.COW-ID-99.M 200101 mmbtu \n", " 2015 1 USA-HI ELEC.CONS_TOT_BTU.COW-HI-99.M 200101 mmbtu \n", "PEL 2017 12 USA-MT ELEC.CONS_TOT_BTU.PEL-MT-99.M 200101 mmbtu \n", " 11 USA-MT ELEC.CONS_TOT_BTU.PEL-MT-99.M 200101 mmbtu \n", " 8 USA-MT ELEC.CONS_TOT_BTU.PEL-MT-99.M 200101 mmbtu \n", " 2015 2 USA-KY ELEC.CONS_TOT_BTU.PEL-KY-99.M 200101 mmbtu \n", " 2017 8 USA-KS ELEC.CONS_TOT_BTU.PEL-KS-99.M 200101 mmbtu \n", " 12 USA-LA ELEC.CONS_TOT_BTU.PEL-LA-99.M 200101 mmbtu \n", " 11 USA-LA ELEC.CONS_TOT_BTU.PEL-LA-99.M 200101 mmbtu \n", " 10 USA-LA ELEC.CONS_TOT_BTU.PEL-LA-99.M 200101 mmbtu \n", " 9 USA-LA ELEC.CONS_TOT_BTU.PEL-LA-99.M 200101 mmbtu \n", " 8 USA-LA ELEC.CONS_TOT_BTU.PEL-LA-99.M 200101 mmbtu \n", " 7 USA-LA ELEC.CONS_TOT_BTU.PEL-LA-99.M 200101 mmbtu \n", " 6 USA-LA ELEC.CONS_TOT_BTU.PEL-LA-99.M 200101 mmbtu \n", "... ... ... ... \n", " 9 USA-NE ELEC.CONS_TOT_BTU.PEL-NE-99.M 200101 mmbtu \n", " 8 USA-NE ELEC.CONS_TOT_BTU.PEL-NE-99.M 200101 mmbtu \n", " 7 USA-NE ELEC.CONS_TOT_BTU.PEL-NE-99.M 200101 mmbtu \n", " 6 USA-NE ELEC.CONS_TOT_BTU.PEL-NE-99.M 200101 mmbtu \n", " 5 USA-NE ELEC.CONS_TOT_BTU.PEL-NE-99.M 200101 mmbtu \n", " 4 USA-NE ELEC.CONS_TOT_BTU.PEL-NE-99.M 200101 mmbtu \n", " 3 USA-NE ELEC.CONS_TOT_BTU.PEL-NE-99.M 200101 mmbtu \n", " 2 USA-NE ELEC.CONS_TOT_BTU.PEL-NE-99.M 200101 mmbtu \n", " 1 USA-NE ELEC.CONS_TOT_BTU.PEL-NE-99.M 200101 mmbtu \n", " 6 USA-NM ELEC.CONS_TOT_BTU.PEL-NM-99.M 200101 mmbtu \n", " 11 USA-NJ ELEC.CONS_TOT_BTU.PEL-NJ-99.M 200101 mmbtu \n", " 5 USA-NJ ELEC.CONS_TOT_BTU.PEL-NJ-99.M 200101 mmbtu \n", " 3 USA-NJ ELEC.CONS_TOT_BTU.PEL-NJ-99.M 200101 mmbtu \n", " 2 USA-NJ ELEC.CONS_TOT_BTU.PEL-NJ-99.M 200101 mmbtu \n", " 2015 2 USA-NV ELEC.CONS_TOT_BTU.PEL-NV-99.M 200101 mmbtu \n", " 2013 12 USA-PA ELEC.CONS_TOT_BTU.PEL-PA-99.M 200101 mmbtu \n", " 2 USA-PA ELEC.CONS_TOT_BTU.PEL-PA-99.M 200101 mmbtu \n", "PC 2014 12 USA-NJ ELEC.CONS_TOT_BTU.PC-NJ-99.M 201101 mmbtu \n", " 6 USA-NJ ELEC.CONS_TOT_BTU.PC-NJ-99.M 201101 mmbtu \n", " 5 USA-NJ ELEC.CONS_TOT_BTU.PC-NJ-99.M 201101 mmbtu \n", " 4 USA-NJ ELEC.CONS_TOT_BTU.PC-NJ-99.M 201101 mmbtu \n", " 3 USA-NJ ELEC.CONS_TOT_BTU.PC-NJ-99.M 201101 mmbtu \n", " 2 USA-NJ ELEC.CONS_TOT_BTU.PC-NJ-99.M 201101 mmbtu \n", " 1 USA-NJ ELEC.CONS_TOT_BTU.PC-NJ-99.M 201101 mmbtu \n", "PEL 2017 6 USA-CO ELEC.CONS_TOT_BTU.PEL-CO-99.M 200101 mmbtu \n", " 4 USA-CO ELEC.CONS_TOT_BTU.PEL-CO-99.M 200101 mmbtu \n", " 1 USA-CO ELEC.CONS_TOT_BTU.PEL-CO-99.M 200101 mmbtu \n", " 12 USA-IL ELEC.CONS_TOT_BTU.PEL-IL-99.M 200101 mmbtu \n", " 4 USA-CT ELEC.CONS_TOT_BTU.PEL-CT-99.M 200101 mmbtu \n", "NG 2017 10 USA-MO ELEC.CONS_TOT_BTU.NG-MO-99.M 200101 mmbtu \n", "\n", " total fuel (mmbtu) \n", "type year month geography \n", "COW 2017 6 USA-ME NaN \n", " 12 USA-AK NaN \n", " 7 USA-AK NaN \n", " 6 USA-AK NaN \n", " 3 USA-AK NaN \n", " 12 USA-ID NaN \n", " 11 USA-ID NaN \n", " 10 USA-ID NaN \n", " 9 USA-ID NaN \n", " 8 USA-ID NaN \n", " 7 USA-ID NaN \n", " 6 USA-ID NaN \n", " 5 USA-ID NaN \n", " 4 USA-ID NaN \n", " 3 USA-ID NaN \n", " 2 USA-ID NaN \n", " 1 USA-ID NaN \n", " 2015 1 USA-HI NaN \n", "PEL 2017 12 USA-MT NaN \n", " 11 USA-MT NaN \n", " 8 USA-MT NaN \n", " 2015 2 USA-KY NaN \n", " 2017 8 USA-KS NaN \n", " 12 USA-LA NaN \n", " 11 USA-LA NaN \n", " 10 USA-LA NaN \n", " 9 USA-LA NaN \n", " 8 USA-LA NaN \n", " 7 USA-LA NaN \n", " 6 USA-LA NaN \n", "... ... \n", " 9 USA-NE NaN \n", " 8 USA-NE NaN \n", " 7 USA-NE NaN \n", " 6 USA-NE NaN \n", " 5 USA-NE NaN \n", " 4 USA-NE NaN \n", " 3 USA-NE NaN \n", " 2 USA-NE NaN \n", " 1 USA-NE NaN \n", " 6 USA-NM NaN \n", " 11 USA-NJ NaN \n", " 5 USA-NJ NaN \n", " 3 USA-NJ NaN \n", " 2 USA-NJ NaN \n", " 2015 2 USA-NV NaN \n", " 2013 12 USA-PA NaN \n", " 2 USA-PA NaN \n", "PC 2014 12 USA-NJ NaN \n", " 6 USA-NJ NaN \n", " 5 USA-NJ NaN \n", " 4 USA-NJ NaN \n", " 3 USA-NJ NaN \n", " 2 USA-NJ NaN \n", " 1 USA-NJ NaN \n", "PEL 2017 6 USA-CO NaN \n", " 4 USA-CO NaN \n", " 1 USA-CO NaN \n", " 12 USA-IL NaN \n", " 4 USA-CT NaN \n", "NG 2017 10 USA-MO NaN \n", "\n", "[197 rows x 8 columns]" ] }, "execution_count": 76, "metadata": {}, "output_type": "execute_result" } ], "source": [ "total_fuel_df.loc[total_fuel_df.isnull().any(axis=1)]" ] }, { "cell_type": "code", "execution_count": 77, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:53:53.313268Z", "start_time": "2017-10-31T17:53:53.288287Z" }, "collapsed": true }, "outputs": [], "source": [ "total_fuel_df = total_fuel_df.dropna()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Electric generation fuel consumption" ] }, { "cell_type": "code", "execution_count": 78, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:54:01.866857Z", "start_time": "2017-10-31T17:54:01.739518Z" }, "collapsed": true }, "outputs": [], "source": [ "eg_fuel_dict = [json.loads(row) for row in eg_fuel_rows]" ] }, { "cell_type": "code", "execution_count": 79, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:54:12.574727Z", "start_time": "2017-10-31T17:54:09.940318Z" }, "collapsed": true }, "outputs": [], "source": [ "eg_fuel_df = pd.concat([line_to_df(x) for x in eg_fuel_dict\n", " if x['geography'] in state_geos])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Multiply generation values by 1,000,000 and change the units to MMBtu" ] }, { "cell_type": "code", "execution_count": 80, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:54:25.125560Z", "start_time": "2017-10-31T17:54:25.114673Z" }, "collapsed": true }, "outputs": [], "source": [ "eg_fuel_df.loc[:,'value'] *= 1E6\n", "eg_fuel_df.loc[:,'units'] = 'mmbtu'" ] }, { "cell_type": "code", "execution_count": 81, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:54:32.925591Z", "start_time": "2017-10-31T17:54:32.917528Z" }, "collapsed": true }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\gschivley\\Anaconda2\\envs\\psci\\lib\\site-packages\\ipykernel_launcher.py:1: FutureWarning: Using 'rename_axis' to alter labels is deprecated. Use '.rename' instead\n", " \"\"\"Entry point for launching an IPython kernel.\n" ] } ], "source": [ "eg_fuel_df.rename_axis({'value':'elec fuel (mmbtu)'}, axis=1, inplace=True)" ] }, { "cell_type": "code", "execution_count": 82, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:54:41.279174Z", "start_time": "2017-10-31T17:54:41.259131Z" }, "collapsed": true }, "outputs": [], "source": [ "eg_fuel_df.set_index(['type', 'year', 'month', 'geography'], inplace=True)" ] }, { "cell_type": "code", "execution_count": 83, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:54:50.073626Z", "start_time": "2017-10-31T17:54:50.054411Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
endflast_updatedsectorseries_idstartunitselec fuel (mmbtu)
typeyearmonthgeography
PC201712USA-NJ201712M2018-02-28T02:03:13-05:0099ELEC.CONS_EG_BTU.PC-NJ-99.M201101mmbtu22330.0
11USA-NJ201712M2018-02-28T02:03:13-05:0099ELEC.CONS_EG_BTU.PC-NJ-99.M201101mmbtu21780.0
10USA-NJ201712M2018-02-28T02:03:13-05:0099ELEC.CONS_EG_BTU.PC-NJ-99.M201101mmbtu18450.0
9USA-NJ201712M2018-02-28T02:03:13-05:0099ELEC.CONS_EG_BTU.PC-NJ-99.M201101mmbtu22760.0
8USA-NJ201712M2018-02-28T02:03:13-05:0099ELEC.CONS_EG_BTU.PC-NJ-99.M201101mmbtu20030.0
\n", "
" ], "text/plain": [ " end f last_updated sector \\\n", "type year month geography \n", "PC 2017 12 USA-NJ 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 11 USA-NJ 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 10 USA-NJ 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 9 USA-NJ 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 8 USA-NJ 201712 M 2018-02-28T02:03:13-05:00 99 \n", "\n", " series_id start units \\\n", "type year month geography \n", "PC 2017 12 USA-NJ ELEC.CONS_EG_BTU.PC-NJ-99.M 201101 mmbtu \n", " 11 USA-NJ ELEC.CONS_EG_BTU.PC-NJ-99.M 201101 mmbtu \n", " 10 USA-NJ ELEC.CONS_EG_BTU.PC-NJ-99.M 201101 mmbtu \n", " 9 USA-NJ ELEC.CONS_EG_BTU.PC-NJ-99.M 201101 mmbtu \n", " 8 USA-NJ ELEC.CONS_EG_BTU.PC-NJ-99.M 201101 mmbtu \n", "\n", " elec fuel (mmbtu) \n", "type year month geography \n", "PC 2017 12 USA-NJ 22330.0 \n", " 11 USA-NJ 21780.0 \n", " 10 USA-NJ 18450.0 \n", " 9 USA-NJ 22760.0 \n", " 8 USA-NJ 20030.0 " ] }, "execution_count": 83, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#drop\n", "eg_fuel_df.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "I verified on [EIA's website](https://www.eia.gov/opendata/qb.php?category=400&sdid=ELEC.CONS_EG_BTU.PEL-MN-99.M) that the values below are correct." ] }, { "cell_type": "code", "execution_count": 84, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:54:58.387406Z", "start_time": "2017-10-31T17:54:58.356615Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
endflast_updatedsectorseries_idstartunitselec fuel (mmbtu)
typeyearmonthgeography
PEL200212USA-MN201712M2018-02-28T02:03:13-05:0099ELEC.CONS_EG_BTU.PEL-MN-99.M200101mmbtu-43000.0
11USA-MN201712M2018-02-28T02:03:13-05:0099ELEC.CONS_EG_BTU.PEL-MN-99.M200101mmbtu-32000.0
10USA-MN201712M2018-02-28T02:03:13-05:0099ELEC.CONS_EG_BTU.PEL-MN-99.M200101mmbtu-15000.0
8USA-MN201712M2018-02-28T02:03:13-05:0099ELEC.CONS_EG_BTU.PEL-MN-99.M200101mmbtu-16000.0
7USA-MN201712M2018-02-28T02:03:13-05:0099ELEC.CONS_EG_BTU.PEL-MN-99.M200101mmbtu-1000.0
4USA-MN201712M2018-02-28T02:03:13-05:0099ELEC.CONS_EG_BTU.PEL-MN-99.M200101mmbtu-6000.0
3USA-MN201712M2018-02-28T02:03:13-05:0099ELEC.CONS_EG_BTU.PEL-MN-99.M200101mmbtu-10000.0
2USA-MN201712M2018-02-28T02:03:13-05:0099ELEC.CONS_EG_BTU.PEL-MN-99.M200101mmbtu-30000.0
1USA-MN201712M2018-02-28T02:03:13-05:0099ELEC.CONS_EG_BTU.PEL-MN-99.M200101mmbtu-34000.0
\n", "
" ], "text/plain": [ " end f last_updated sector \\\n", "type year month geography \n", "PEL 2002 12 USA-MN 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 11 USA-MN 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 10 USA-MN 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 8 USA-MN 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 7 USA-MN 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 4 USA-MN 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 3 USA-MN 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 2 USA-MN 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 1 USA-MN 201712 M 2018-02-28T02:03:13-05:00 99 \n", "\n", " series_id start units \\\n", "type year month geography \n", "PEL 2002 12 USA-MN ELEC.CONS_EG_BTU.PEL-MN-99.M 200101 mmbtu \n", " 11 USA-MN ELEC.CONS_EG_BTU.PEL-MN-99.M 200101 mmbtu \n", " 10 USA-MN ELEC.CONS_EG_BTU.PEL-MN-99.M 200101 mmbtu \n", " 8 USA-MN ELEC.CONS_EG_BTU.PEL-MN-99.M 200101 mmbtu \n", " 7 USA-MN ELEC.CONS_EG_BTU.PEL-MN-99.M 200101 mmbtu \n", " 4 USA-MN ELEC.CONS_EG_BTU.PEL-MN-99.M 200101 mmbtu \n", " 3 USA-MN ELEC.CONS_EG_BTU.PEL-MN-99.M 200101 mmbtu \n", " 2 USA-MN ELEC.CONS_EG_BTU.PEL-MN-99.M 200101 mmbtu \n", " 1 USA-MN ELEC.CONS_EG_BTU.PEL-MN-99.M 200101 mmbtu \n", "\n", " elec fuel (mmbtu) \n", "type year month geography \n", "PEL 2002 12 USA-MN -43000.0 \n", " 11 USA-MN -32000.0 \n", " 10 USA-MN -15000.0 \n", " 8 USA-MN -16000.0 \n", " 7 USA-MN -1000.0 \n", " 4 USA-MN -6000.0 \n", " 3 USA-MN -10000.0 \n", " 2 USA-MN -30000.0 \n", " 1 USA-MN -34000.0 " ] }, "execution_count": 84, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#drop\n", "eg_fuel_df.loc[~(eg_fuel_df['elec fuel (mmbtu)'] >= 0) &\n", " ~(eg_fuel_df['elec fuel (mmbtu)'].isnull())]" ] }, { "cell_type": "code", "execution_count": 85, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:55:07.070435Z", "start_time": "2017-10-31T17:55:07.045574Z" }, "collapsed": true }, "outputs": [], "source": [ "eg_fuel_df.dropna(inplace=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Combine three datasets\n", "Need to estimate fuel use for OOG, because EIA doesn't include any (this is only ~2% of OOG fuel for electricity in 2015)." ] }, { "cell_type": "code", "execution_count": 86, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:55:15.779136Z", "start_time": "2017-10-31T17:55:15.473615Z" }, "collapsed": true }, "outputs": [], "source": [ "fuel_df = pd.concat([total_fuel_df, eg_fuel_df['elec fuel (mmbtu)']], axis=1)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Not sure how this happens in EIA's data, but we do see the negative fuel consumption for electricity generation." ] }, { "cell_type": "code", "execution_count": 87, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:55:25.638114Z", "start_time": "2017-10-31T17:55:25.612533Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
endflast_updatedsectorseries_idstartunitstotal fuel (mmbtu)elec fuel (mmbtu)
typeyearmonthgeography
PEL20021USA-MN201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-MN-99.M200101mmbtu51000.0-34000.0
2USA-MN201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-MN-99.M200101mmbtu62000.0-30000.0
3USA-MN201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-MN-99.M200101mmbtu99000.0-10000.0
4USA-MN201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-MN-99.M200101mmbtu84000.0-6000.0
7USA-MN201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-MN-99.M200101mmbtu93000.0-1000.0
8USA-MN201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-MN-99.M200101mmbtu66000.0-16000.0
10USA-MN201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-MN-99.M200101mmbtu64000.0-15000.0
11USA-MN201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-MN-99.M200101mmbtu49000.0-32000.0
12USA-MN201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.PEL-MN-99.M200101mmbtu50000.0-43000.0
\n", "
" ], "text/plain": [ " end f last_updated sector \\\n", "type year month geography \n", "PEL 2002 1 USA-MN 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 2 USA-MN 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 3 USA-MN 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 4 USA-MN 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 7 USA-MN 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 8 USA-MN 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 10 USA-MN 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 11 USA-MN 201712 M 2018-02-28T02:03:13-05:00 99 \n", " 12 USA-MN 201712 M 2018-02-28T02:03:13-05:00 99 \n", "\n", " series_id start units \\\n", "type year month geography \n", "PEL 2002 1 USA-MN ELEC.CONS_TOT_BTU.PEL-MN-99.M 200101 mmbtu \n", " 2 USA-MN ELEC.CONS_TOT_BTU.PEL-MN-99.M 200101 mmbtu \n", " 3 USA-MN ELEC.CONS_TOT_BTU.PEL-MN-99.M 200101 mmbtu \n", " 4 USA-MN ELEC.CONS_TOT_BTU.PEL-MN-99.M 200101 mmbtu \n", " 7 USA-MN ELEC.CONS_TOT_BTU.PEL-MN-99.M 200101 mmbtu \n", " 8 USA-MN ELEC.CONS_TOT_BTU.PEL-MN-99.M 200101 mmbtu \n", " 10 USA-MN ELEC.CONS_TOT_BTU.PEL-MN-99.M 200101 mmbtu \n", " 11 USA-MN ELEC.CONS_TOT_BTU.PEL-MN-99.M 200101 mmbtu \n", " 12 USA-MN ELEC.CONS_TOT_BTU.PEL-MN-99.M 200101 mmbtu \n", "\n", " total fuel (mmbtu) elec fuel (mmbtu) \n", "type year month geography \n", "PEL 2002 1 USA-MN 51000.0 -34000.0 \n", " 2 USA-MN 62000.0 -30000.0 \n", " 3 USA-MN 99000.0 -10000.0 \n", " 4 USA-MN 84000.0 -6000.0 \n", " 7 USA-MN 93000.0 -1000.0 \n", " 8 USA-MN 66000.0 -16000.0 \n", " 10 USA-MN 64000.0 -15000.0 \n", " 11 USA-MN 49000.0 -32000.0 \n", " 12 USA-MN 50000.0 -43000.0 " ] }, "execution_count": 87, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#drop\n", "fuel_df.loc[~(fuel_df['elec fuel (mmbtu)']>=0)]" ] }, { "cell_type": "code", "execution_count": 88, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:55:34.219142Z", "start_time": "2017-10-31T17:55:34.206392Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
endflast_updatedsectorseries_idstartunitstotal fuel (mmbtu)elec fuel (mmbtu)
typeyearmonthgeography
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: [end, f, last_updated, sector, series_id, start, units, total fuel (mmbtu), elec fuel (mmbtu)]\n", "Index: []" ] }, "execution_count": 88, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#drop\n", "fuel_df.loc[~(fuel_df['total fuel (mmbtu)']>=0)]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Add CO2 emissions\n", "\n", "The difficulty here is that EIA combines all types of coal fuel consumption together in the bulk download and API. Fortunately the emission factors for different coal types aren't too far off on an energy basis (BIT is 93.3 kg/mmbtu, SUB is 97.2 kg/mmbtu). I'm going to average the BIT and SUB factors rather than trying to do something more complicated. In 2015 BIT represented 45% of coal energy for electricity and SUB represented 48%.\n", "\n", "Same issue with petroleum liquids. Using the average of DFO and RFO, which were the two largest share of petroleum liquids." ] }, { "cell_type": "code", "execution_count": 89, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:55:42.569867Z", "start_time": "2017-10-31T17:55:42.560218Z" }, "collapsed": true }, "outputs": [], "source": [ "path = join(data_path, 'Final emission factors.csv')\n", "ef = pd.read_csv(path, index_col=0)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Match general types with specific fuel codes" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Fuel codes:\n", "- WWW, wood and wood derived fuels\n", "- WND, wind\n", "- STH, solar thermal\n", "- WAS, other biomass\n", "- TSN, all solar\n", "- SUN, utility-scale solar\n", "- NUC, nuclear\n", "- NG, natural gas\n", "- PEL, petroleum liquids\n", "- SPV, utility-scale solar photovoltaic\n", "- PC, petroluem coke\n", "- OTH, other\n", "- COW, coal,\n", "- DPV, distributed photovoltaic\n", "- OOG, other gases\n", "- HPS, hydro pumped storage\n", "- HYC, conventional hydroelectric\n", "- GEO, geothermal\n", "- AOR, other renewables (total)" ] }, { "cell_type": "code", "execution_count": 90, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:56:04.263644Z", "start_time": "2017-10-31T17:56:04.253963Z" }, "collapsed": true }, "outputs": [], "source": [ "fuel_factors = pd.Series({'NG' : ef.loc['NG', 'Fossil Factor'],\n", " 'PEL': ef.loc[['DFO', 'RFO'], 'Fossil Factor'].mean(),\n", " 'PC' : ef.loc['PC', 'Fossil Factor'], \n", " 'COW' : ef.loc[['BIT', 'SUB'], 'Fossil Factor'].mean(),\n", " 'OOG' : ef.loc['OG', 'Fossil Factor']}, name='type')" ] }, { "cell_type": "code", "execution_count": 91, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:56:12.342458Z", "start_time": "2017-10-31T17:56:12.335537Z" } }, "outputs": [ { "data": { "text/plain": [ "COW 95.250\n", "NG 53.070\n", "OOG 59.000\n", "PC 102.100\n", "PEL 75.975\n", "Name: type, dtype: float64" ] }, "execution_count": 91, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#drop\n", "fuel_factors" ] }, { "cell_type": "code", "execution_count": 92, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:56:21.450888Z", "start_time": "2017-10-31T17:56:21.424111Z" }, "collapsed": true }, "outputs": [], "source": [ "fuel_df['all fuel CO2 (kg)'] = (fuel_df['total fuel (mmbtu)']\n", " .multiply(fuel_factors, level='type',\n", " fill_value=0))\n", "fuel_df['elec fuel CO2 (kg)'] = (fuel_df['elec fuel (mmbtu)']\n", " .multiply(fuel_factors, level='type',\n", " fill_value=0))" ] }, { "cell_type": "code", "execution_count": 93, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:56:29.803955Z", "start_time": "2017-10-31T17:56:29.774735Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
endflast_updatedsectorseries_idstartunitstotal fuel (mmbtu)elec fuel (mmbtu)all fuel CO2 (kg)elec fuel CO2 (kg)
typeyearmonthgeography
COW20011USA-AK201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-AK-99.M200101mmbtu1120000.0872000.01.066800e+088.305800e+07
USA-AL201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-AL-99.M200101mmbtu67999000.066582000.06.476905e+096.341935e+09
USA-AR201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-AR-99.M200101mmbtu23099000.022700000.02.200180e+092.162175e+09
USA-AZ201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-AZ-99.M200101mmbtu35873000.035483000.03.416903e+093.379756e+09
USA-CA201712M2018-02-28T02:03:13-05:0099ELEC.CONS_TOT_BTU.COW-CA-99.M200101mmbtu3652000.02008000.03.478530e+081.912620e+08
\n", "
" ], "text/plain": [ " end f last_updated sector \\\n", "type year month geography \n", "COW 2001 1 USA-AK 201712 M 2018-02-28T02:03:13-05:00 99 \n", " USA-AL 201712 M 2018-02-28T02:03:13-05:00 99 \n", " USA-AR 201712 M 2018-02-28T02:03:13-05:00 99 \n", " USA-AZ 201712 M 2018-02-28T02:03:13-05:00 99 \n", " USA-CA 201712 M 2018-02-28T02:03:13-05:00 99 \n", "\n", " series_id start units \\\n", "type year month geography \n", "COW 2001 1 USA-AK ELEC.CONS_TOT_BTU.COW-AK-99.M 200101 mmbtu \n", " USA-AL ELEC.CONS_TOT_BTU.COW-AL-99.M 200101 mmbtu \n", " USA-AR ELEC.CONS_TOT_BTU.COW-AR-99.M 200101 mmbtu \n", " USA-AZ ELEC.CONS_TOT_BTU.COW-AZ-99.M 200101 mmbtu \n", " USA-CA ELEC.CONS_TOT_BTU.COW-CA-99.M 200101 mmbtu \n", "\n", " total fuel (mmbtu) elec fuel (mmbtu) \\\n", "type year month geography \n", "COW 2001 1 USA-AK 1120000.0 872000.0 \n", " USA-AL 67999000.0 66582000.0 \n", " USA-AR 23099000.0 22700000.0 \n", " USA-AZ 35873000.0 35483000.0 \n", " USA-CA 3652000.0 2008000.0 \n", "\n", " all fuel CO2 (kg) elec fuel CO2 (kg) \n", "type year month geography \n", "COW 2001 1 USA-AK 1.066800e+08 8.305800e+07 \n", " USA-AL 6.476905e+09 6.341935e+09 \n", " USA-AR 2.200180e+09 2.162175e+09 \n", " USA-AZ 3.416903e+09 3.379756e+09 \n", " USA-CA 3.478530e+08 1.912620e+08 " ] }, "execution_count": 93, "metadata": {}, "output_type": "execute_result" } ], "source": [ "fuel_df.head()" ] }, { "cell_type": "code", "execution_count": 94, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:56:39.238148Z", "start_time": "2017-10-31T17:56:38.505831Z" }, "collapsed": true }, "outputs": [], "source": [ "fuel_cols = ['total fuel (mmbtu)', 'elec fuel (mmbtu)',\n", " 'all fuel CO2 (kg)', 'elec fuel CO2 (kg)']\n", "gen_fuel_df = pd.concat([gen_df, fuel_df[fuel_cols]], axis=1)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Add datetime and quarter columns" ] }, { "cell_type": "code", "execution_count": 95, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:56:59.385347Z", "start_time": "2017-10-31T17:56:59.219142Z" }, "collapsed": true }, "outputs": [], "source": [ "add_quarter(gen_fuel_df)" ] }, { "cell_type": "code", "execution_count": 96, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:57:07.206454Z", "start_time": "2017-10-31T17:57:07.135063Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
endflast_updatedsectorseries_idstartunitsgeneration (MWh)total fuel (mmbtu)elec fuel (mmbtu)all fuel CO2 (kg)elec fuel CO2 (kg)datetimequarter
typeyearmonthgeography
AOR20011USA-AK201712M2018-02-28T02:03:13-05:0099ELEC.GEN.AOR-AK-99.M200101megawatthours87.00NaNNaNNaNNaN2001-01-011
USA-AL201712M2018-02-28T02:03:13-05:0099ELEC.GEN.AOR-AL-99.M200101megawatthours401167.59NaNNaNNaNNaN2001-01-011
USA-AR201712M2018-02-28T02:03:13-05:0099ELEC.GEN.AOR-AR-99.M200101megawatthours136530.37NaNNaNNaNNaN2001-01-011
USA-AZ201712M2018-02-28T02:03:13-05:0099ELEC.GEN.AOR-AZ-99.M200101megawatthours453.00NaNNaNNaNNaN2001-01-011
USA-CA201712M2018-02-28T02:03:13-05:0099ELEC.GEN.AOR-CA-99.M200101megawatthours1717398.41NaNNaNNaNNaN2001-01-011
\n", "
" ], "text/plain": [ " end f last_updated sector \\\n", "type year month geography \n", "AOR 2001 1 USA-AK 201712 M 2018-02-28T02:03:13-05:00 99 \n", " USA-AL 201712 M 2018-02-28T02:03:13-05:00 99 \n", " USA-AR 201712 M 2018-02-28T02:03:13-05:00 99 \n", " USA-AZ 201712 M 2018-02-28T02:03:13-05:00 99 \n", " USA-CA 201712 M 2018-02-28T02:03:13-05:00 99 \n", "\n", " series_id start units \\\n", "type year month geography \n", "AOR 2001 1 USA-AK ELEC.GEN.AOR-AK-99.M 200101 megawatthours \n", " USA-AL ELEC.GEN.AOR-AL-99.M 200101 megawatthours \n", " USA-AR ELEC.GEN.AOR-AR-99.M 200101 megawatthours \n", " USA-AZ ELEC.GEN.AOR-AZ-99.M 200101 megawatthours \n", " USA-CA ELEC.GEN.AOR-CA-99.M 200101 megawatthours \n", "\n", " generation (MWh) total fuel (mmbtu) \\\n", "type year month geography \n", "AOR 2001 1 USA-AK 87.00 NaN \n", " USA-AL 401167.59 NaN \n", " USA-AR 136530.37 NaN \n", " USA-AZ 453.00 NaN \n", " USA-CA 1717398.41 NaN \n", "\n", " elec fuel (mmbtu) all fuel CO2 (kg) \\\n", "type year month geography \n", "AOR 2001 1 USA-AK NaN NaN \n", " USA-AL NaN NaN \n", " USA-AR NaN NaN \n", " USA-AZ NaN NaN \n", " USA-CA NaN NaN \n", "\n", " elec fuel CO2 (kg) datetime quarter \n", "type year month geography \n", "AOR 2001 1 USA-AK NaN 2001-01-01 1 \n", " USA-AL NaN 2001-01-01 1 \n", " USA-AR NaN 2001-01-01 1 \n", " USA-AZ NaN 2001-01-01 1 \n", " USA-CA NaN 2001-01-01 1 " ] }, "execution_count": 96, "metadata": {}, "output_type": "execute_result" } ], "source": [ "gen_fuel_df.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "No records with positive fuel use but no generation" ] }, { "cell_type": "code", "execution_count": 97, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:57:35.851758Z", "start_time": "2017-10-31T17:57:35.843395Z" }, "collapsed": true }, "outputs": [], "source": [ "gen_fuel_df['generation (MWh)'].fillna(value=0, inplace=True)" ] }, { "cell_type": "code", "execution_count": 98, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T17:57:46.347755Z", "start_time": "2017-10-31T17:57:46.304898Z" }, "scrolled": true }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
endflast_updatedsectorseries_idstartunitsgeneration (MWh)total fuel (mmbtu)elec fuel (mmbtu)all fuel CO2 (kg)elec fuel CO2 (kg)datetimequarter
yearmonthgeography
20011USA-AK201712M2018-02-28T02:03:13-05:0099ELEC.GEN.COW-AK-99.M200101megawatthours46903.01120000.0872000.01.066800e+088.305800e+072001-01-011
USA-AL201712M2018-02-28T02:03:13-05:0099ELEC.GEN.COW-AL-99.M200101megawatthours6557913.067999000.066582000.06.476905e+096.341935e+092001-01-011
USA-AR201712M2018-02-28T02:03:13-05:0099ELEC.GEN.COW-AR-99.M200101megawatthours2149808.023099000.022700000.02.200180e+092.162175e+092001-01-011
USA-AZ201712M2018-02-28T02:03:13-05:0099ELEC.GEN.COW-AZ-99.M200101megawatthours3418454.035873000.035483000.03.416903e+093.379756e+092001-01-011
USA-CA201712M2018-02-28T02:03:13-05:0099ELEC.GEN.COW-CA-99.M200101megawatthours199857.03652000.02008000.03.478530e+081.912620e+082001-01-011
\n", "
" ], "text/plain": [ " end f last_updated sector \\\n", "year month geography \n", "2001 1 USA-AK 201712 M 2018-02-28T02:03:13-05:00 99 \n", " USA-AL 201712 M 2018-02-28T02:03:13-05:00 99 \n", " USA-AR 201712 M 2018-02-28T02:03:13-05:00 99 \n", " USA-AZ 201712 M 2018-02-28T02:03:13-05:00 99 \n", " USA-CA 201712 M 2018-02-28T02:03:13-05:00 99 \n", "\n", " series_id start units \\\n", "year month geography \n", "2001 1 USA-AK ELEC.GEN.COW-AK-99.M 200101 megawatthours \n", " USA-AL ELEC.GEN.COW-AL-99.M 200101 megawatthours \n", " USA-AR ELEC.GEN.COW-AR-99.M 200101 megawatthours \n", " USA-AZ ELEC.GEN.COW-AZ-99.M 200101 megawatthours \n", " USA-CA ELEC.GEN.COW-CA-99.M 200101 megawatthours \n", "\n", " generation (MWh) total fuel (mmbtu) elec fuel (mmbtu) \\\n", "year month geography \n", "2001 1 USA-AK 46903.0 1120000.0 872000.0 \n", " USA-AL 6557913.0 67999000.0 66582000.0 \n", " USA-AR 2149808.0 23099000.0 22700000.0 \n", " USA-AZ 3418454.0 35873000.0 35483000.0 \n", " USA-CA 199857.0 3652000.0 2008000.0 \n", "\n", " all fuel CO2 (kg) elec fuel CO2 (kg) datetime \\\n", "year month geography \n", "2001 1 USA-AK 1.066800e+08 8.305800e+07 2001-01-01 \n", " USA-AL 6.476905e+09 6.341935e+09 2001-01-01 \n", " USA-AR 2.200180e+09 2.162175e+09 2001-01-01 \n", " USA-AZ 3.416903e+09 3.379756e+09 2001-01-01 \n", " USA-CA 3.478530e+08 1.912620e+08 2001-01-01 \n", "\n", " quarter \n", "year month geography \n", "2001 1 USA-AK 1 \n", " USA-AL 1 \n", " USA-AR 1 \n", " USA-AZ 1 \n", " USA-CA 1 " ] }, "execution_count": 98, "metadata": {}, "output_type": "execute_result" } ], "source": [ "gen_fuel_df.loc['COW',:].head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Export data" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "State-level" ] }, { "cell_type": "code", "execution_count": 99, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T18:05:21.536020Z", "start_time": "2017-10-31T18:05:19.631166Z" }, "collapsed": true }, "outputs": [], "source": [ "path = join(data_path, 'EIA state-level gen fuel CO2 2018-03-06.csv')\n", "gen_fuel_df.to_csv(path)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "National totals" ] }, { "cell_type": "code", "execution_count": 100, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T18:03:49.791963Z", "start_time": "2017-10-31T18:03:49.736372Z" }, "collapsed": true }, "outputs": [], "source": [ "nat_gen_fuel = gen_fuel_df.groupby(['type', 'year', 'month']).sum()\n", "add_quarter(nat_gen_fuel)" ] }, { "cell_type": "code", "execution_count": 101, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T18:04:09.296377Z", "start_time": "2017-10-31T18:04:09.280132Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
generation (MWh)total fuel (mmbtu)elec fuel (mmbtu)all fuel CO2 (kg)elec fuel CO2 (kg)quarterdatetime
typeyearmonth
WWW201783879727.60NaNNaNNaNNaN32017-08-01
93357157.33NaNNaNNaNNaN32017-09-01
103558432.79NaNNaNNaNNaN42017-10-01
113512302.55NaNNaNNaNNaN42017-11-01
123831624.38NaNNaNNaNNaN42017-12-01
\n", "
" ], "text/plain": [ " generation (MWh) total fuel (mmbtu) elec fuel (mmbtu) \\\n", "type year month \n", "WWW 2017 8 3879727.60 NaN NaN \n", " 9 3357157.33 NaN NaN \n", " 10 3558432.79 NaN NaN \n", " 11 3512302.55 NaN NaN \n", " 12 3831624.38 NaN NaN \n", "\n", " all fuel CO2 (kg) elec fuel CO2 (kg) quarter datetime \n", "type year month \n", "WWW 2017 8 NaN NaN 3 2017-08-01 \n", " 9 NaN NaN 3 2017-09-01 \n", " 10 NaN NaN 4 2017-10-01 \n", " 11 NaN NaN 4 2017-11-01 \n", " 12 NaN NaN 4 2017-12-01 " ] }, "execution_count": 101, "metadata": {}, "output_type": "execute_result" } ], "source": [ "nat_gen_fuel.tail()" ] }, { "cell_type": "code", "execution_count": 102, "metadata": { "ExecuteTime": { "end_time": "2017-10-31T18:05:35.398602Z", "start_time": "2017-10-31T18:05:33.172398Z" }, "collapsed": true }, "outputs": [], "source": [ "path = join(data_path, 'Derived data',\n", " 'EIA country-wide gen fuel CO2 {}.csv'.format(file_date))\n", "gen_fuel_df.to_csv(path)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "psci", "language": "python", "name": "psci" }, "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 }