data-wrangler.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Take Home Project: Wrangling coal mine data\n",
    "\n",
    "### 1. Do the work!\n",
    "The task is to **write code to extract and clean** some data about coal mines, and **write notes on how to integrate that work into an existing system**.\n",
    "\n",
    "**Setup instructions are in the repo `README.md`.**\n",
    "\n",
    "* This task is an example of the kind of work we do to make public energy data usable for analysis. We want to get at a few different angles:\n",
    "  * How do you approach building code from scratch?\n",
    "  * How do you think about messy datasets?\n",
    "  * How do you think about testing software that deals with said messy datasets?\n",
    "  * How do you think about integrating code into an existing system?\n",
    "* **Spend up to two hours working on this.** If you don't finish, don't worry! **Do** try to make sure you have a little bit of both sections (coding and writing) done, though. The point is to have something concrete and technical to talk about at all.\n",
    "* Feel free to use whatever documentation or online resources you would normally consult while working on a data wrangling problem.\n",
    "* Feel free to use additional 3rd party libraries if you want to.  You should be able to install them from within the notebook using `!pip install packagename` or `!conda install packagename`\n",
    "\n",
    "### 2. Email us your notebook!\n",
    "* Send it to [hello@catalyst.coop](mailto:hello@catalyst.coop).\n",
    "* We'll review your notebook and if it looks good, we'll reach out to schedule a longer conversation about it.\n",
    "\n",
    "### 3. Follow-up conversation\n",
    "\n",
    "If we think your notebook looks good, we'll schedule a 60 minute conversation about it.\n",
    "\n",
    "You will need this code running on your computer and we will expect you to share your screen. Some of the questions will also be best answered by quickly writing out some pseudo-code.\n",
    "\n",
    "During this conversation, we'll ask you to walk us through your Python code for the extraction piece and your English words for the integration piece.\n",
    "\n",
    "We'll also ask some of the following questions:\n",
    "\n",
    "* What assumptions are you making about the input data?\n",
    "  * How will you test whether / when those assumptions are valid?\n",
    "  * How would you / did you deal with the data that don’t conform to those assumptions?\n",
    "* What expectations do you have about the output data?\n",
    "  * How will you evaluate the completeness of the data that you’ve been able to extract?\n",
    "  * What kind of queries are you trying to make easy with the structure of the output data?\n",
    "  * What kind of data validation checks would you design to make sure that the output meets your expectations? These could be either integrated into the table transformation process, or run on the final output.\n",
    "* Did you try anything that didn't work? What was it?\n",
    "* If there are records which can’t be reasonably cleaned automatically, but were high value in an advocacy context, how would you integrate manual cleaning into the automated process so that the manual effort is captured, and can be incrementally improved over time?\n",
    "* How do you decide when data isn’t recoverable?\n",
    "* What parts of this process might make sense to generalize / abstract for re-use in extracting, cleaning, and reorganizing data from other tables?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Background on the MSHA Coal Mine Data\n",
    "\n",
    "* The Mine Health and Safety Administration (MSHA) collects a variety of information about mines, incuding who owns them, what and how much they produce, mining methods used, environmental and safety violations, number of employees, ownership, and location.\n",
    "* This information can be helpful for understanding the economic and environmental consequences of shutting down coal fired power plants. It's especially relevant right now, since the Inflation Reduction Act (IRA) provides tax benefits for clean energy projects in former coal communities. (You can read more about \"energy and coal communitites\" [here](https://www.resources.org/common-resources/what-is-an-energy-community/), but that's not required to answer this interview question.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extract and Clean the MSHA Mine Data Set\n",
    "\n",
    "Please write code in this notebook to address the following requirements.\n",
    "\n",
    "### Extract\n",
    "\n",
    "* Design and implement a function or class that can be used to extract the [MSHA Mines Data Set (ZIP)](https://arlweb.msha.gov/OpenGovernmentData/DataSets/Mines.zip).\n",
    "    * The input to this function can be a URL or local path to the published zipfile. You don't have to worry about handling both.\n",
    "    * The output should be a `pandas` dataframe.\n",
    "\n",
    "\n",
    "### Transform/Clean\n",
    "Take the extracted MSHA Mines data frame and impose some order on it, in preparation for loading it into a well-normalized relational database.\n",
    "\n",
    "* There are many columns - clean 5-10 that seem the most important to you. We'd like to be able to answer questions like:\n",
    "  * Which companies control/operate the most mines?\n",
    "  * How many people are employed directly at coal mines in your state?\n",
    "  * How many mines have been built but aren't yet operational?\n",
    "* Any columns you've cleaned should end up with well-defined data types.\n",
    "* Write down any properties you'd like to assert about the input or output data. If you have time left after everything, we'd love it if you could automate some of these assertions.\n",
    "* Write down any questions you still have about the data by the end of your time window.\n",
    "\n",
    "\n",
    "### Some hints\n",
    "* The MSHA dataset has a [Definitions File](https://arlweb.msha.gov/OpenGovernmentData/DataSets/Mines_Definition_File.txt) with column type and description information. \n",
    "* You'll need to use the `latin_1` character encoding when extracting the .txt files."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Write about integrating this with the rest of our codebase\n",
    "\n",
    "Take a look at the [main Public Utility Data Liberation (PUDL) codebase](https://github.com/catalyst-cooperative/pudl).\n",
    "\n",
    "It's certainly a little overwhelming to jump into. But we're curious how you'd approach it.\n",
    "\n",
    "We'd like you to answer the following questions in a notebook cell below. Please show your thought process - we're particularly interested in how you deal with any *wrong* turns during your investigation. If any questions come up in this process, please note them down also.\n",
    "\n",
    "* Where would you put this MSHA extraction code you just wrote? How do you expect the input and output to be different?\n",
    "\n",
    "As a starting point:\n",
    "* the main body of our source code lives in [`/src/pudl`](https://github.com/catalyst-cooperative/pudl/tree/main/src/pudl)\n",
    "* we use [Dagster](https://docs.dagster.io/getting-started) as an ETL orchestration tool, so that might help demystify things as well."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "pudl-dev",
   "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.12.7"
  },
  "toc-autonumbering": false,
  "toc-showmarkdowntxt": false
 },
 "nbformat": 4,
 "nbformat_minor": 4
}