How I Brought Five Public Datasets Into One Hospital Quality Analysis
From a Question to Five Public Datasets
My capstone connects a public data pipeline with an analysis of hospital infection measures. I built the workflow from ingestion through preparation to regression because the analytical result depends on the decisions made before a model is fitted. Getting data into one table is an engineering achievement. Understanding what that table and its model can support is a separate responsibility.
The project uses five public hospital quality domains from the Centers for Medicare & Medicaid Services (CMS): healthcare-associated infections (HAI), hospital general information, patient experience, patient safety and timely and effective care. The project overview identifies those sources and the capstone’s engineering and analytical scope.
These are published hospital measures. My analysis does not contain individual patient records or establish a patient’s infection risk. Its target is a hospital index constructed from selected source measures in the infection transformation.
That scope defines the analytical question. I can examine how the prepared hospital characteristics and quality measures relate to the constructed index. I cannot turn that relationship into a causal claim about how changing a hospital practice would change an outcome.
Coordinating Ingestion and Preparation
I use Apache Airflow to coordinate the workflow and Amazon Web Services (AWS) for storage and preparation. The Airflow definition builds the public download uniform resource locators (URLs), retrieves the records and then executes the Glue jobs in sequence. Its schedule is unset, so the implemented workflow runs on a manual trigger.
The ingestion task calls the CMS application programming interface (API) for each dataset. It checks the response status and requires a list of results. It then serializes the records as JavaScript Object Notation (JSON), with one record per line in the JSON Lines format. It writes that output to Amazon Simple Storage Service (S3). The ingestion source shows these checks and the storage operation.
The object key includes the run date. The landing convention has a clear limit: a rerun on the same date can replace that object. The task parses the response and writes its own representation of the records. It does not retain the publisher’s original response bytes. Both behaviors are explicit in the workflow code.
I therefore describe this stage as landing retrieved records. I do not describe the date-keyed objects as an immutable archive of every download. The choice of a storage key affects what a later reader can recover, just as the choice of a transformation affects what a later analyst can interpret.
After ingestion, five AWS Glue jobs curate the domains. A sixth joins those curated outputs into the regression dataset. The dependency chain runs the jobs sequentially and the join job writes the analytical output as JSON in S3.
Manual initiation and automated execution are different parts of this design. Once triggered, Airflow supplies the dependency order rather than requiring each job to be started separately. The workflow definition supports that narrower automation claim. It does not establish an unattended production schedule. I can describe the orchestration precisely while keeping its operating boundary visible.
Each service has a specific responsibility. Airflow establishes the task order. Glue prepares and combines the data. S3 stores the outputs. The regression belongs to the notebook, not to the orchestration layer. Explaining those boundaries makes the pipeline easier to inspect without treating the service list as evidence that every analytical requirement has been met.
Defining the Hospital Dataset
The join job connects the five curated domains through facility_id. Consider a hypothetical hospital represented in those sources. Its identifier supplies the join relationship as its prepared measures enter the combined dataset. This is an illustration of the implemented joins, not a newly observed record or a claim about a particular facility.
The target needs a more precise explanation than “infection rate.” The HAI preparation job selects six standardized infection ratio measures, filters unavailable scores and averages the available values by hospital. It names the result infection_index.
The transformation defines the modeled value. The index is an average of the selected available scores. It is not an infection count or an individual probability. Because it uses available scores, its interpretation also depends on which selected measures contribute to each hospital’s value. I keep the construction beside the analytical result so the reader can see the connection.
For the hypothetical hospital, the average would depend on its available selected scores after filtering. The aggregation code permits different subsets of measures to contribute across hospitals. I am describing what the transformation allows, not reporting how often that happens in the data. A single output column can hide that difference. Its definition belongs beside the dataset.
The project includes an Athena external-table definition for the JSON output. That definition describes the columns and storage location for queries. The Glue job performs the joins; the table definition does not reproduce the transformation or fit the regression. Keeping those roles separate prevents the catalog from being mistaken for the analytical method.
The default Glue quality rule is also limited. In the inspected join job, it checks that the column count is greater than zero. A successful result from that rule does not establish unique hospital keys, complete source coverage or valid clinical interpretation. I can describe the check that exists without presenting it as comprehensive validation.
Reading the Regression Within Its Limits
The regression notebook prepares numerical and categorical inputs, splits the data and fits an ordinary least squares (OLS) model with statsmodels. OLS is the linear regression method used here. The notebook then applies backward elimination, removing the predictor with the highest p-value until its stopping condition is met.
The saved final summary reports an R-squared of 0.102 and an adjusted R-squared of 0.096. These values describe the fitted model in the saved training summary; they are not measures of clinical validity or a claim that the model predicts new hospitals well. The same notebook records a test-set root mean square error, an error measure comparing the predictions with the index values.
These results come from the analysis I ran for my capstone. The test error measures how far the predictions are from the observed infection index values. To assess whether the fitted model improves on a simpler reference prediction, that error needs to be compared with a baseline.
The preprocessing order is a concrete limitation. The notebook fits its median imputer before the train/test split. An imputer replaces missing values; in this case, the replacement medians are learned from the data before the test rows are separated. The later held-out rows therefore influence preprocessing. The notebook cells show that sequence.
The scikit-learn guidance on data leakage explains the relevant boundary: learned preprocessing should be fitted using training data, with the resulting transformation applied to the test data. That is the methodological lesson, not a claim that this capstone has already been corrected and re-evaluated.
Feature selection also needs an explicit account. The notebook’s p-value elimination takes place on the training data. I cannot use the selected coefficients or their significance as a substitute for evidence that the approach generalizes. The inspected record supports a fitted association analysis with qualifications, not a validated hospital prediction system.
What the Connected Workflow Taught Me
The result depends on the full path. The download representation determines what is retained. The transformations determine the hospital measures. The join establishes the combined analytical input. Preprocessing and model selection determine how the regression is fitted and how its evaluation should be read.
The pipeline and notebook make those choices inspectable. I can connect the analytical statement to the code that constructed its inputs and show where the evidence stops. I do not need to turn a working workflow into a stronger modeling claim to explain what I built.
Three lessons carry through the project:
- Define the measure before interpreting the model. The infection index comes from a specific transformation of available source scores.
- Describe the preparation and quality controls that exist, including their limits. A landed object or a completed job is one part of the evidence.
- Keep evaluation separate from implementation. A fitted model and saved test output need an account of preprocessing, selection and comparison before I can judge predictive value.
I built the capstone from source to analysis. Explaining it follows the same route, with each stage contributing both an output and a boundary on what the result can support.