notebooks/pudl-catalog.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Notebook Preamble\n",
    "For this notebook to work you need to have the `pudl_catalog` package installed. To install the most recently released version, you can do:\n",
    "\n",
    "## Installation\n",
    "```\n",
    "pip install catalystcoop.pudl_catalog\n",
    "```\n",
    "or\n",
    "```\n",
    "mamba install -c conda-forge catalystcoop.pudl_catalog\n",
    "```\n",
    "\n",
    "If you want to work with the development version in the repository, you can clone it locally and create a conda environment in the top level directory, where `environment.yml` is, and then activate that environment:\n",
    "\n",
    "```\n",
    "mamba env create\n",
    "mamba activate pudl-catalog\n",
    "```\n",
    "\n",
    "Or you can use `pip`\n",
    "\n",
    "```\n",
    "pip install --editable ./\n",
    "```\n",
    "\n",
    "## Configuration / Environment\n",
    "* You need to have configured a Google Cloud Platform project & billing account. See the [PUDL Catalog documentation](https://catalystcoop-pudl-catalog.readthedocs.io/en/latest/) on using public \"requester pays\" data for more information.\n",
    "* The catalog makes use of two environment variables `PUDL_INTAKE_CACHE` and `PUDL_INTAKE_PATH`.\n",
    "* `PUDL_INTAKE_PATH` is the source location for the catalog data, `gs://intake.catalyst.coop/REF` by default, where `REF` is either the catalog version (e.g. `v2022.06.10`) or `dev` for unreleased versions of the catalog that refer to the nightly PUDL data builds. You should not need to set this environment variable unless you're working with your own copy of the underlying catalog data in some special situation.\n",
    "* `PUDL_INTAKE_CACHE` is the path to the directory where Intake will cache the data locally. This directory needs to exist -- it won't be created if it doesn't. By default it's set to `$HOME/.cache/intake`.\n",
    "* If you need to set either of these environment variables to custom values, it must be done before the `intake` package is imported by Python, since paths within the catalog are set at import.\n",
    "* That means it can be done either in the environment where you're running Jupyter beforehand, or in the notebook itself prior to the `import intake` line."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data is cached locally\n",
    "* The contents of the data catalogs are only updated occasionally, so it doesn't make sense to download the whole dataset again every time you want to access it, or to directly access the version that's stored in the cloud.\n",
    "* Downloading data from cloud storage also incurs a small cost ($0.10-0.20/GB).\n",
    "* By default the PUDL Catalog creates a local copy (cache) of the data the first time you access it.\n",
    "* Where exactly this cached data is stored is determined by the `PUDL_INTAKE_CACHE` environment variable.\n",
    "* Subsequent access will refer to this local copy rather than the remote data.\n",
    "* When a new version of the catalog is released, and you upgrade your installation of the `catalystcoop.pudl_catalog` package, the new data will be downloaded locally again when you attempt to access it for the first time.\n",
    "* Each of the SQLite databases is about 1 GB, and the EPA CEMS dataset is about 5 GB, so this may take a few minutes, depending on the speed of your network connection.\n",
    "* Once the data has been cached, subsequent access should be much faster."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext autoreload\n",
    "%autoreload 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Standard Library Imports\n",
    "import logging\n",
    "import os\n",
    "import sys\n",
    "from pathlib import Path\n",
    "\n",
    "logger = logging.getLogger()\n",
    "logger.setLevel(logging.INFO)\n",
    "handler = logging.StreamHandler(stream=sys.stdout)\n",
    "formatter = logging.Formatter(\"%(message)s\")\n",
    "handler.setFormatter(formatter)\n",
    "logger.handlers = [handler]\n",
    "\n",
    "# Where to cache downloaded data locally. Defaults to ~/.intake/cache\n",
    "# os.environ[\"PUDL_INTAKE_CACHE\"] = str(Path.home() / \".cache/intake\")\n",
    "\n",
    "# You can override the default path to the data in your environment, if you have it.\n",
    "# By default it reads from Google Cloud Storage:\n",
    "# os.environ[\"PUDL_INTAKE_PATH\"] = \"gs://intake.catalyst.coop/dev\"\n",
    "\n",
    "# 3rd Party Imports:\n",
    "import intake\n",
    "import pandas as pd\n",
    "from pudl_catalog.helpers import year_state_filter\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Explore installed Intake catalogs\n",
    "* When you install and import `intake`, it provides a built-in (but empty) catalog at `intake.cat`\n",
    "* If other Intake catalog packages (like `catalystcoop.pudl_catalog`) are installed as well, they register their existence with the top-level Intake catalog.\n",
    "* Listing the built in catalog will show you which (sub-)catalogs are available, in this case including `pudl_cat`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "list(intake.cat)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Catalog Level Metadata\n",
    "* To avoid going through the main Intake catalog every time, we can store a reference to the PUDL Catalog in a variable.\n",
    "* Looking at the text representation of the catalog, you can see high level information about it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pudl_cat = intake.cat.pudl_cat\n",
    "pudl_cat"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The contents of `pudl_cat`:\n",
    "* Listing that catalog will show us the data sources it contains by name.\n",
    "* These sources can also be sub-catalogs nested within it, as is the case for the catalog entries which represent whole SQL databases with multiple tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "list(pudl_cat)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Inspecting an individual data source\n",
    "* Some of the entries in the PUDL Catalog are data sources.\n",
    "* In our case this means they represent a particular tabular dataset.\n",
    "* One example is the EPA CEMS hourly emissions data, which is stored in Apache Parquet files.\n",
    "* Looking at that catalog entry, we can see some metadata related to the source."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pudl_cat.hourly_emissions_epacems"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Inspecting an SQLite sub-catalog\n",
    "* Several of the PUDL Catalog entries are entire databases containing many separate tables.\n",
    "* These databases are each used to populate a whole sub-catalog, with each table in the database being represented by a data source within that catalog.\n",
    "* The top level catalog representation shows some basic metadata.\n",
    "* The first time you access this sub-catalog, it should download the data and cache it locally. It's about 1 GB, so it could take a couple of minutes depending on the speed of your network connection.  Subsequent access will be much faster.\n",
    "* If you don't have GCP / Requester Pays set up correctly, this is the first place that would cause a problem."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pudl_cat.pudl"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Identifying sources within an SQLite sub-catalog\n",
    "* As with the top level PUDL Catalog (or any Intake catalog), looking at the `list()` representation of the catalog will show you all the available sources within it.\n",
    "* In the case of an SQL database derived catalog, each table becomes its own independent data source:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "list(pudl_cat.pudl)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading data from the PUDL Catalog\n",
    "* Every source exists as an attribute of the catalog\n",
    "* You can see what form it will be returned in by looking at the `.container` attribute.\n",
    "* In our case everything is going to be returned as a dataframe.\n",
    "* You can also look at the `.container` attribute to differentiate between data sources and sub-catalogs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pudl_cat.pudl.fuel_ferc1.container"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pudl_cat.hourly_emissions_epacems.container"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pudl_cat.ferc1.container"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pudl_cat.ferc1.f1_steam.container"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading an SQL catalog source into a Pandas dataframe\n",
    "* The SQL table data sources have a `.read()` method that will read the whole table into a dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fuel_ferc1 = pudl_cat.pudl.fuel_ferc1.read()\n",
    "fuel_ferc1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading data directly from SQLite\n",
    "* If you need to query the underlying DB rather than reading an entire table, the `uri` attribute is an SQLAlchemy URI.\n",
    "* However, this isn't the recommended usage pattern, and it will only work if the data has already been cached locally."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pudl_cat.pudl.uri"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy as sa\n",
    "engine = sa.create_engine(pudl_cat.pudl.uri)\n",
    "sql = \"\"\"\n",
    "SELECT utility_id_ferc1, plant_id_ferc1, report_year, plant_name_ferc1, capacity_mw\n",
    "  FROM plants_steam_ferc1\n",
    " LIMIT 10\n",
    "\"\"\"\n",
    "df = pd.read_sql(sql, engine)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading Parquet data into a Dask dataframe\n",
    "* The Parquet datasets are typically too large to fit in memory. EPA CEMS is about a billion rows.\n",
    "* Rather than reading the entire table all at once, we can select subsets using filters.\n",
    "* If we want to operate on the entire dataset, we can also use Dask to serialize or distribute computations, only returning a Pandas dataframe once the data has been consolidated or aggregated to a reasonable scale.\n",
    "* For more on how to work with Dask, you can check out [this self-guided tutorial](https://coiled.io/blog/how-to-learn-dask-in-2021/).\n",
    "* Here we create a Dask dataframe, but we don't compute its contents yet.\n",
    "* However, if this is the first time you're accessing the data, this query will trigger the download and local caching of the entire dataset, so it may still take a couple of minutes to run."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "## Read a couple of years of data for a couple of states into a dataframe\n",
    "TEST_YEARS = [2018, 2020]\n",
    "TEST_STATES = [\"ID\", \"ME\"]\n",
    "TEST_FILTERS = year_state_filter(years=TEST_YEARS, states=TEST_STATES)\n",
    "display(TEST_FILTERS)\n",
    "epacems_dd = (\n",
    "    pudl_cat.hourly_emissions_epacems(filters=TEST_FILTERS)\n",
    "    .to_dask()\n",
    ")\n",
    "epacems_dd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "epacems_df = epacems_dd.compute()\n",
    "epacems_df.sample(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.4"
  },
  "toc-autonumbering": true
 },
 "nbformat": 4,
 "nbformat_minor": 4
}