10 Project: Data Pipeline

In this project, you will develop a data pipeline for ingesting messy, unstructured data, producing a nice and structured SQL database, and automatically generating reports.

There are two datasets to choose between. For each, the source data is somewhat messy, so you will work to organize it into a convenient format to facilitate reporting and querying. The project is meant to imitate a common situation you will face in industry: your product or business will be spewing a firehose of messy data, and it’ll be your job to turn it into a sprinkler of insight.

The two datasets:

Data from the US Department of Health and Human Services (HHS) about hospitals throughout the US, with weekly updates on how many patients have been admitted with COVID, how many hospital beds are currently available, how many staff are vaccinated, and related factors. You can find the data and descriptions here on HealthData.gov.
Data from the Department of Education’s College Scorecard about the performance of colleges and universities throughout the US. This data is updated annually. The Department of Education provides detailed data dictionaries.

10.1 Logistics

This project will be completed in assigned groups of 2 or 3 students. Group assignments will be posted on Canvas. There will be several milestones throughout the semester when you will turn in parts of the work; at the end of the semester, you will turn in the completed project.

The groups will be paired: your group is matched with another group. You must use a different dataset from your matched group.

While completing the project, you will also be working on a knowledge transfer activity in 36-611 based on this project. Just as in 36-613, I’ll be grading your technical work, and the knowledge transfer activity will be part of your 36-611 grade. The knowledge transfer will be with your paired group.

10.2 Part 1: Designing the database

This part is due Wednesday, November 6 at 5pm.

In Part 1 of the project, your team must design a table schema for the data. By “table schema” I mean the CREATE TABLE statements necessary to create database tables that fit the data. You should follow the design principles in Section 4.1 to build a normalized structure for the database that minimizes redundant information. Include primary keys, foreign keys, column types, and any appropriate constraints. It is up to you to decide how many tables you need, their names, and their contents.

Write your CREATE TABLE statements in a notebook. Test them out on Azure to ensure they work correctly. You do not need to load any real data into the database yet.

In the notebook, write comments explaining the following: What are the basic entities in your schema? (In Example 4.1, entities were things like songs, record labels, and albums, that each had their own database table.) How did you choose them and what did you do to ensure there is not redundant information in your database?

You may find it useful to make a Git repository to share with your project team, though this is not required. Instead you will turn in your schema and explanation on Gradescope.

The rest of the instructions are split up by the dataset your group is using:

10.2.1 HHS data

The HHS data files have many columns; we won’t be interested in all of them here. The data contains the following information:

A unique ID for each hospital (hospital_pk, a string)
The state the hospital is in (state, as a two-letter abbreviation, like PA)
The hospital’s name (hospital_name), street address (address), city (city), ZIP code (zip), and FIPS code (fips_code, a unique identifier for counties)
The latitude and longitude of the hospital (geocoded_hospital_address), formatted as a string like POINT(-91 30), where the first number is the longitude and the second is the latitude. When you load data, you will need to convert this to a format you can use¹
The week this observation is for (collection_week)
The total number of hospital beds available each week, broken down into adult and pediatric (children) beds (all_adult_hospital_beds_7_day_avg, all_pediatric_inpatient_beds_7_day_avg). This can change weekly depending on staffing and facilities.
The number of hospital beds that are in use each week (all_adult_hospital_inpatient_bed_occupied_7_day_avg, all_pediatric_inpatient_bed_occupied_7_day_avg)
The number of ICU (intensive care unit) beds available and the number in use (total_icu_beds_7_day_avg and icu_beds_used_7_day_avg)
The number of patients hospitalized who have confirmed COVID (inpatient_beds_used_covid_7_day_avg)
The number of adult ICU patients who have confirmed COVID (staffed_icu_adult_patients_confirmed_covid_7_day_avg)

The data is updated weekly. In each weekly file I will provide you, each row will be one hospital, and all of the columns above will be present—so each hospital’s address, location, and so on will appear every week.

There are several thousand hospitals in the United States, and this data has been updated weekly for much of the pandemic, so the data contains about 580,000 rows. In raw form, with dozens of columns, it is 257 MB.

You will also be using a hospital quality dataset from the Centers for Medicare and Medicaid Services (CMS). We are interested in the following information in this data:

A facility ID, which matches the hospital_pk in the HHS data
The facility’s name, address, city, state, ZIP code, and county
The type of hospital and its type of ownership (government, private, non-profit, etc.)
Whether the hospital provides emergency services
The hospital’s overall quality rating. This quality rating is updated several times a year, and we want to be able to track each version of the quality rating. For instance, we might ask “What was the quality rating of this hospital in 2020?” and compare it to the rating in 2022.

10.2.2 College Scorecard

The College Scorecard data files cover dozens of variables per institution; we won’t be interested in all of them here. The Data Dictionary lists all variables, their column names (VARIABLE NAME), a human readable description, and the meaning of each value. We care about the following columns:

UNITID, the institution ID
ACCREDAGENCY
PREDDEG
HIGHDEG
CONTROL
REGION
CCBASIC (TODO: this is missing before 2022; prefer IPEDS version?)
ADM_RATE
TUITIONFEE_IN, TUITIONFEE_OUT, and TUITIONFEE_PROG
TUITFTE
AVGFACSAL
CDR2 and CDR3
Scroll through the list (there are nearly 3,500 variables!) and pick some additional variables that could be interesting to analyze.

The data is updated annually. Your database should be able to store each year’s data for each university, so you can quickly look up statistics for a university in any particular year.

You will also be using supplementary data from the Integrated Postsecondary Education Data System (IPEDS); specifically, the directory information files. These provide annual directory information and other statistics based on surveys of institutions. Again, since this is updated annually, you must be able to track each version of the data and the dates it applies to.

From the IPEDS data, obtain:

All information about the institution’s name, location, address, and similar
All Carnegie Classification 2021 variables
The Census identifiers that apply to it: Core Based Statistical Area (CBSA) and its type, the Combined Statistical Area (CSA), and the county FIPS code
Latitude and longitude of the institution.

Most of these values will not change from year to year, but they can, since colleges can change names, move, grow, or change.

The College Scorecard data also includes crosswalk files giving links between OPEIDs (in the College Scorecard data) and UNITIDs (in the IPEDS) data, so you can match between datasets.

10.3 Part 2: Loading data

Note

This part is due Friday, November 15th at 5pm. Plan ahead so you can use the Tuesday and Friday office hours.

Now we need to write Python code to load data each week. This code will handle any conversions, cleaning, and reformatting of the data, and then run the necessary INSERT commands.

The necessary data files will be posted on Canvas. Do not use the original files from the sources; I am providing a few specific subsets to make your task easier.

10.3.1 HHS data

10.3.1.1 Weekly updates

I will provide the HHS data to you in the form of files containing new data for each week. Each CSV file will cover one week of data. Your first script will load one file into your SQL database.

The script should be run from the command line like this:

python load-hhs.py 2022-01-04-hhs-data.csv

where the argument is the name of the file to load from. The script should then:

Load the CSV file.
Do any necessary processing (such as converting -999 to None or NULL, parsing dates into Python date objects, etc.).
Execute a series of INSERT commands to insert the relevant data into the database.

10.3.1.2 CMS quality data

Similarly, you will write a script to load the quality data whenever it may be updated. I’ll provide you several example files you will load. The script should run from the command line like this:

python load-quality.py 2021-07-01 Hospital_General_Information-2021-07.csv

Here the first argument is the date of this quality data (YYYY-mm-dd) and the second argument is the CSV file name. Again, your Python script will do any necessary work and then INSERT the data.

If you chose to have a separate hospitals table, referenced by the tables storing the weekly updates or quality data, it will need to be updated whenever there’s a new hospital. Your scripts should automatically insert new hospitals whenever they appear. If the new quality data changes key hospital metadata (like its location), you should update your hospital data.

10.3.2 College Scorecard

I will provide you College Scorecard and IPEDS data files containing data for each year.

You should create two Python scripts that can be run from the command line, like so:

python load-scorecard.py TODO-name-of-file.csv
python load-ipeds.py TODO-name-of-file.csv

where the arguments are the names of the files to load from. The scripts should then:

Load the CSV file.
Do any necessary processing (such as converting -999 to None or NULL, parsing dates into Python date objects, etc.).
Execute a series of INSERT commands to insert the relevant data into the database.

10.3.3 Data processing requirements

Note

These requirements apply to both project datasets.

Your scripts should have the following common features:

They should print out a summary of how much data has been read from the CSV and how much has been successfully inserted into the database. This should allow the user to determine whether the amount of data loaded matches what they expect.
If the script gets partway through and crashes with an error, either all the data should be inserted or none of it. You should not be able to insert only half the data, then have to manually remove it before you can run the script again to reload the rest of the data.
If a row is invalid and rejected by Postgres (because a constraint failed, for instance), your script should print an error message identifying which row failed and giving information about it (such as the hospital or college affected) that helps the user figure out what’s wrong. Then your script should stop so the user can fix the issue in the data before re-running the script.

10.3.4 Formatting and style requirements

Additionally, this work will be part of your knowledge transfer activity in 611. To facilitate transfer, you should ensure that:

your code is clearly written, separated into small understandable functions, as discussed in 36-650
you follow standard PEP 8 style rules for naming and formatting
you have docstrings for each function explaining what it does, following PEP 257 conventions
your code also includes a README file describing how to use your scripts to load the data

If you choose, you may create additional Python files containing functions and classes used by your data loading scripts.

10.3.5 Submission details

To submit your code, create a private GitHub repository shared with your classmates. We will ask you to add us (capnrefsmmat and aerosengart) as collaborators, and you will submit a link to the repository as your Canvas submission for this part of the project.

10.4 Part 3: Analytics and reporting

Note

This part is due Friday, December 6th at 5pm. Plan ahead so you can use the Tuesday and Thursday office hours.

Your final task for this semester is to develop an automatic reporting system. A user should be able to run the reporting system, specify a date of interest, and obtain a report or dashboard summarizing the data as of the selected date.

10.4.1 Report contents

Your reports should be graphical dashboards summarizing the data and interesting features it contains. You do not need to do any statistical modeling, although you can if you want. Descriptive tables and graphs are sufficient, since the purpose of this project is to give you experience working with real data in SQL.

10.4.1.1 HHS data

The HHS report should summarize the COVID situation suggested by the data on hospital beds: how full are hospitals in each state? What states have the fewest open beds? Where are beds filling up the fastest?

Your report/dashboard should include:

A summary of how many hospital records were loaded in the week selected by the user, and how that compares to previous weeks.
A table summarizing the number of adult and pediatric beds available that week, the number used, and the number used by patients with COVID, compared to the 4 most recent weeks.
A graph or table summarizing the fraction of beds in use by hospital quality rating, so we can compare high-quality and low-quality hospitals.
A plot of the total number of hospital beds used per week, over all time up to the selected week, split into all cases and COVID cases.

Additionally, you should include three additional interesting analyses. At least two of these should involve combining multiple tables from your database. These could be anything that can be expressed as a table or graph and that is potentially interesting. Some examples include:

A map showing the number of COVID cases by state (the first two digits of a hospital FIPS code is its state)
A table of the states in which the number of cases has increased by the most since last week
A table of the hospitals (including names and locations) with the largest changes in COVID cases in the last week
A table of hospitals that did not report any data in the past week, their names, and the date they most recently reported data
Graphs of hospital utilization (the percent of available beds being used) by state, or by type of hospital (private or public), over time

You are free to think of other interesting plots, tables, and maps, as long as you think they could be useful to the Department of Health and Human Services for some health-related purpose.

If it helps, imagine sending this report to the Assistant to the Deputy Director of the Department of Health and Human Services. The Assistant is a doctor, not a statistician or programmer, so make your report accordingly.

10.4.1.2 College Scorecard

The College Scorecard report should summarize the current data, highlighting the best- and worst-performing colleges according to financial results, and also summarize how the data has changed since the previous year. The user should be able to select a year of interest and get the report generated for that year.

Your report/dashboard should include:

Summaries of how many colleges and universities are included in the data for the selected year, by state and type of institution (private, public, for-profit, and so on).
Summaries of current college tuition rates, by state and Carnegie Classification of institution.
A table showing the best- and worst-performing institutions by loan repayment rates.
Graphs showing how tuition rates and loan repayment rates have changed over time, either in aggregate (such as averages for all institutions by type) or for selected institutions (such as the most expensive).

Maps of tuition or loan repayment rates across the country
Graphs comparing tuition, loan repayment rates, and faculty salaries across institution, and demonstrating their correlation
A table of institutions that did not report data in the selected year but did previously, or that are new this year
Graphs of total enrollment or total tuition costs over time

If it helps, imagine sending this report to the Assistant to the Deputy Director of the Department of Education. The Assistant has a degree in educational administration, not in statistics, so make your report accordingly.

10.4.2 Report format

The report should be output in the form of a document with graphs, tables, and text. This could be an HTML file, a PDF, a Jupyter notebook (built automatically with the latest data), or even an interactive dashboard made with something like Streamlit.

10.4.3 Mechanical requirements

Your reporting system must be automated. This means it cannot require the user to manually edit a file or notebook every week. Ideally, it could be run with a single command:

python weekly-report.py 2022-09-30 # for HHS data
python education-report.py 2021 # for College Scorecard

That might produce an output file called report-2022-09-30.pdf or report-2021.html; for an interactive dashboard, it might open up the dashboard for that date.

If your report is in the form of a notebook, you can produce it from the command line using papermill, which lets you pass command-line arguments to Jupyter notebooks. For example, if you had a week parameter in your code, you could run

papermill weekly-report.ipynb 2022-09-30-report.ipynb -p week 2022-09-30
jupyter nbconvert --no-input --to html 2022-09-30-report.ipynb

The first command substitutes week = "2022-09-30" into the parameters of the notebook, and the second executes the notebook and produces the HTML output. The --no-input argument exports only the output, not the code in the notebook. Read the papermill usage guide for details on how to accept parameters in a notebook.

Your reporting system must be built on SQL, using a SQL connection to generate the results. The calculations must be primarily done in SQL, with a minimum of post-processing in Python to format the results for display. You should not, at any point, load the entire database table into Python so you can do your analysis in Python alone.

Your reporting system should assume the user has already run your data loading scripts from Part 2; it does not need to load all the data.

10.4.4 Format requirements

The report must be designed for an end-user: to the extent you can, it should hide the code and technical details, and present useful tables and graphs of results. Format the output nicely! Do not simply print out lists of Python output. Make graphs and tables. If you’re printing a data frame, consider the Pandas data frame formatting options that let you print it as a nice table.

10.4.5 Submission details

Add your code to the GitHub repository you created in previous part. Make sure this code is committed and pushed by the deadline.

Load all the data files provided on Canvas and then generate a report for the final week of data. Convert this report to a PDF and submit it on Gradescope. This serves as an example of what your code produces.

The coolest option would be to use PostGIS, which extends Postgres to understand geographic coordinate systems. But that is overkill for us here, and is not installed on our server, so you can’t use it. A couple simple columns would be fine.↩︎