Comprehensive Study Notes on Python Data Engineering, Relational Normalization, and Health Surveillance Analytics
Course Context and Technical Environment Setup
- Curriculum Progression and Software Tooling:
- Week 12 of the curriculum transitions from core Python programming into hands-on data manipulation exercises.
- Subsequent curriculum stages transition away from programming into drag-and-drop business intelligence and data visualization using Tableau.
- Cross-course alignment:
- Course 6060: Covers initial workstation environment setup, SQL basics, and data pipeline fundamentals.
- Course 6430: Covers database design, query construction, and administrative management.
- Course 6590: Focuses on advanced healthcare analytics, large-scale data cleansing, and data imputation.
- Course 4160 (Professor Rouse): Healthcare Information Management (HIM) module focusing on SQL Data Definition Language (DDL), logical and physical database design, data engineering, online data acquisition, and Tableau visualization.
- JupyterLab Workspace Navigation:
- Directory navigation paths:
- Windows operating systems: Access files within the
public directory. - macOS operating systems: Access files within the
shared directory.
- Execution starts by running a dedicated local batch script to launch the local JupyterLab server instance.
- If the default web browser fails to load automatically, the console outputs specific local web addresses that can be manually copied and pasted into a browser.
- File management protocol:
- Locate exercise workbook
02_exercises. - Right-click and select Duplicate (keyboard shortcut:
Ctrl+D) to preserve the untouched master exercise file while performing live operations on the duplicate.
- Python Package Installation and Environment Management:
- Package installations are performed directly inside a Jupyter notebook cell using the shell execution escape operator (
!). - A leading exclamation point (
!pip install ...) instructs the IPython kernel to execute the command within the underlying operating system terminal rather than through the Python interpreter. - Running the package installer cell is required only once per machine or environment setup. Re-running it across duplicated workbooks is unnecessary.
- To prevent redundant re-execution during global notebook runs ("Run All Cells"), comment out the command using a hash symbol (
#) or convert the cell from Code to Markdown. - Network and environment troubleshooting:
- Corporate or institutional networks frequently flag Python package downloads as potential security threats or viruses, blocking external connections.
- SSL and certificate validation errors may occur; troubleshooting requires capturing the complete terminal trace output.
- Python Dependency Versioning and Virtual Environments:
- Python package ecosystems frequently lack reverse compatibility (backward compatibility). Changes in core libraries like
pandas or matplotlib introduce breaking syntax changes and library conflicts. - Upgrading database adapters such as
psycopg3 often breaks interoperability with specific versions of pandas, SQLAlchemy, and matplotlib. - Freezing dependencies: The terminal command
pip freeze dumps exact installed package names and version numbers, creating a standard requirements.txt file. - Modern virtual environment management tools:
venv: Standard built-in Python module creating isolated local virtual environments.uv: A high-performance package and environment manager designed as a fast alternative to pip.- Conda / Anaconda: Automatically provisions a base virtual environment labeled
base. Course operations execute directly inside Conda's default base environment.
PostgreSQL Database Configuration and Process Security
- Principle of Least Privilege and Process Credentials:
- Administrative accounts (such as the default
postgres superuser) possess elevated permissions allowing arbitrary data modification, table deletion, and schema destruction. - Production security principles dictate that automated software processes must have restricted, task-specific credentials distinct from human operator credentials.
- Hardcoding plain-text passwords directly in source code represents poor credential hygiene. Production systems isolate credentials within external environment configuration files (such as
.env files) loaded through secure secret managers.
- PostgreSQL Role Creation and Privilege Grants:
- PostgreSQL user roles are server-level objects rather than database-level objects. A role defined within an active database server is accessible across all database instances hosted on that server.
- Create a read-only role named
coexternal:CREATE ROLE coexternal WITH LOGIN PASSWORD 'coexternal_password';
- Grant structural navigation and query access within the target database (e.g.,
armc):GRANT USAGE ON SCHEMA public TO coexternal;GRANT SELECT ON ALL TABLES IN SCHEMA public TO coexternal;
- Operational semantics:
USAGE ON SCHEMA public: Grants permission to navigate into the public schema namespace without granting rights to alter or create structural objects.SELECT ON ALL TABLES: Restricts database interaction to read-only queries. Explicitly blocks data manipulation (INSERT, UPDATE, DELETE) and administrative operations (DROP, VACUUM, ANALYZE).
- Execution caveat: Execute the
CREATE ROLE statement independently from the permission grants. Because PostgreSQL treats query batches as transactional units, if the role already exists, the creation statement errors out, terminating the entire batch before the GRANT statements can execute.
- Port Identification and SQLAlchemy Connection Engine:
- PostgreSQL operates by default on network port
5432. - When multiple server versions coexist on a host, successive installations increment the port allocation (e.g.,
5433, 5434, 5435). Verify the active port via the Properties tab in pgAdmin 4. - Database connection engines in Python are instantiated via SQLAlchemy. The engine specifies the dialect, user credentials, host, port, and database name:
- Connection parameters:
postgresql://coexternal:coexternal_password@localhost:5432/armc
- Restarting the IPython kernel (
Kernel -> Restart Kernel and Clear Outputs of All Cells) is mandatory following new library installations to ensure binaries are fully loaded into memory.
Pandas Fundamentals and Data Slicing Techniques
- Namespace Conventions and Jupyter Magic Commands:
- Standard library import conventions:
import pandas as pdimport matplotlib.pyplot as pltimport numpy as np
- Jupyter magic functions operate with a leading percent sign (
% for line magics, %% for cell magics). %matplotlib inline: Instructs the notebook rendering engine to embed graphical figures directly beneath code cells rather than in separate application windows.
- Extracting SQL Queries via Pandas:
- Function syntax:
pd.read_sql_query(sql, con) - Implementation:
patients = pd.read_sql_query('SELECT * FROM patients LIMIT 100;', engine)
- Displaying objects in notebooks:
- Calling
print(patients) prints unformatted raw text console output. - Referencing
patients as the final expression in a cell invokes the notebook's rich display system, rendering a structured HTML table with row/column truncation.
- Inspecting DataFrame Metadata:
- Execute
patients.info() to inspect structural schema details:- Class type:
pandas.core.frame.DataFrame - Total entries:
100 rows indexed from 0 to 99 - Total columns:
15 features indexed 0 through 14 - Non-null count: Evaluates data density; columns containing
100 non-null values are completely dense. - Data types (
Dtype): Evaluates columns into machine representations, such as int64 (integers) and object (representing text strings or mixed arbitrary data).
- Row-Based Slicing Operations:
- Head extraction:
patients.head(n=5) returns the top 5 records (indices 0 to 4). Accepts integer arguments specifying the row count (e.g., patients.head(10)). - Tail extraction:
patients.tail(n=5) returns the bottom 5 records. Crucial for log files and temporal append-only stores. - Positional slicing syntax:
patients[start:stop] uses mathematical interval notation [start,stop), where the start index is inclusive and the stop index is exclusive.patients[5:10] retrieves 5 records corresponding to indices 5,6,7,8,9 (the 6th through 10th rows).- Formula for row count:
Count=stop−start=10−5=5. - Negative indexing:
patients[-5:] retrieves the final 5 records by counting backward from the end of the DataFrame.
Column Selection, Data Types, and Filtering Syntax
- Column Access Modes: Series vs. DataFrame:
- Direct attribute dot notation (
patients.gender):- Returns a 1-dimensional
pandas.core.series.Series object. - Fails if the column identifier contains spaces, hyphens, or special non-alphanumeric characters.
- Single bracket notation (
patients['gender']):- Universal lookup method returning a 1-dimensional
Series. - Accommodates spaces, punctuation, case variations, and dynamic string variables.
- Double bracket notation (
patients[['gender']]):- Forces Pandas to evaluate the inner brackets as a Python
list. - Returns a 2-dimensional
pandas.core.frame.DataFrame formatted as an HTML table. - Mandatory when piping data to machine learning models or relational merge functions requiring 2D table structures.
- Multi-Column Projection:
- Syntax:
patients[['patient_id', 'gender', 'race']] - SQL Equivalent:
SELECT patient_id, gender, race FROM patients; - Executing
patients['patient_id', 'gender'] without an inner list causes a KeyError because the indexing operator interprets the comma-separated sequence as a single tuple lookup rather than distinct column names.
- Relational Filtering (WHERE Constraints):
- Filtering requires passing a Boolean expression into the DataFrame indexing brackets.
- Equality testing uses the double equal operator (
==):patients[patients.gender == 'M']- Internally generates a Boolean
Series of True and False values across all rows, projecting only records evaluating to True.
- Compound Boolean logic constraints:
- Standard Python logical keywords (
and, or) do not apply to vectorized Series evaluations. - Pandas requires bitwise operators:
& for logical AND, | for logical OR. - Parenthetical grouping is strictly mandatory for every individual condition to override Python's bitwise operator precedence:
patients[(patients.gender == 'M') & (patients.race == 'UNKNOWN')]- Omitting parentheses results in an unparseable binary typing exception.
- Chained filtering and column projection:
patients[(patients.gender == 'M') & (patients.race == 'UNKNOWN')][['patient_id', 'gender', 'race']]- Evaluates conditional row filters first, followed by column subset projection.
Descriptive Statistics, Data Profiling, and In-line Plotting
- Statistical Profiling with
.describe():- Default behavior (
patients.describe()):- Restricts analysis exclusively to numerical columns.
- Computes:
count, mean, std (sample standard deviation), min, 25% (first quartile), 50% (median), 75% (third quartile), and max. - Categorical integers: Calculating standard deviations or means on nominal or ordinal identifiers (such as
patient_id or zip_code) produces mathematically meaningless output.
- Full descriptive profiling (
patients.describe(include='all')):- Profiles every attribute in the DataFrame.
- Categorical/Text (
object) metrics:unique: Total count of distinct categorical values.top: The modal value occurring with the highest frequency.freq: Absolute occurrence count of the modal value.
- Continuous numeric metrics evaluate to
NaN (null) for categorical columns, while categorical metrics evaluate to NaN for continuous numeric columns.
- Data quality inspection:
- Evaluating English alphabet constraints: The
middle_initial field should logically possess at most 26 unique values. Observing >26 unique values indicates dirty data (e.g., whitespace variations, digits, punctuation, or non-ASCII/Unicode foreign characters).
- Frequency Tables and Visualizations:
- Computing categorical frequencies:
patients['gender'].value_counts()- SQL Equivalent:
SELECT gender, count(*) FROM patients GROUP BY gender ORDER BY count(*) DESC; - Outputs descending frequency totals (e.g.,
60 females, 40 males in a sample of 100).
- Direct bar chart generation:
patients['gender'].value_counts().plot.bar()plt.show()- Explicitly invoking
plt.show() flushes the Matplotlib buffer, rendering the graphic and preventing visual duplication across cell runs.
Large-Scale Data Ingestion and Clinical Anomaly Investigation
- Query vs. Table Ingestion Patterns:
pd.read_sql_query(sql, con): Executes arbitrary SQL text. Pandas often fails to infer date data types correctly, importing temporal fields like birth_date as raw text objects (object).pd.read_sql_table(table_name, con, schema='public'): Reads database table schemas directly via SQLAlchemy metadata, mapping PostgreSQL date/time types to Pandas datetime64[ns].- Memory footprint and scaling:
- Reading
patients table: 100,000 rows, 15 columns, memory consumption ≈11.4MB. - Reading
encounters table: 1,718,315 rows, 13 columns, memory consumption ≈170MB.
- Automated Information Thresholds:
- Pandas disables automated non-null profiling computations on DataFrames exceeding a hardcoded threshold of
1,690,785 rows to prevent memory and processing latency. - To force full density calculations on large tables, pass the explicit parameter:
encounters.info(show_counts=True). - Clinical missingness profiling across
1,718,315 encounters:- Primary keys (
encounter_id, patient_id) and admit_date: 100% complete density (1,718,315 non-null). discharge_date: ≈300,000 missing values (ongoing stays or missing entries).- Clinical vital signs (
bp_systolic, bp_diastolic, temperature, pulse, height, weight):- Contain only
≈135,191 non-null records. - Over
90% of vital data is missing in non-dense operational tables.
- Clinical Vital Sign Filtering and Shallow vs. Deep Copies:
- Filtering non-null blood pressure records:
enc_bp = encounters[encounters.bp_systolic.notnull() & encounters.bp_diastolic.notnull()]- Result set yields exactly
135,191 records where both systolic and diastolic blood pressure are present.
- Memory mechanics:
- A standard variable assignment (
enc_bp = encounters[...]) produces a shallow copy (a view or pointer referencing the original DataFrame memory block). - Modifying an object derived through a shallow copy triggers a
SettingWithCopyWarning. - To mutate, add, or alter derived tables independently, generate an explicit deep copy using
.copy():enc_bp = encounters[encounters.bp_systolic.notnull() & encounters.bp_diastolic.notnull()].copy()
- Bivariate Scatter Plotting and Data Anomaly Auditing:
- Generating a clinical scatter plot:
enc_bp.plot.scatter(x='bp_diastolic', y='bp_systolic')plt.show()
- Clinical data interpretation and outlier identification:
- Expected baseline: Physiological correlation showing a positive relationship between diastolic and systolic metrics.
- Dead record markers: Observations at
(0,0) represent physiological telemetry monitors recording post-mortem flatlines. - Erroneous physiological entries: Records indicating systolic BP
≈100 paired with diastolic ≈1, or systolic ≈5−10 paired with diastolic ≈150. - Analytical bias: Extreme physiological outliers exert high leverage, pulling linear regression trendlines away from true distributions.
- Auditing questions for data engineering: Determine if anomalies cluster around specific clinical hardware, facilities, software interfaces, or historical operational periods.
CSV File Ingestion, Data Merging, and Temporal Manipulation
- CSV Ingestion and Dimensional Verification:
- Read comma-separated files using
pd.read_csv('COVID.csv'). - COVID surveillance dataset dimensions:
- Total row count:
92,700 entries. - North Carolina geographic distribution: Exactly
100 counties. - Temporal uniformity verification:
100counties92,700rows=927days of data per county.
- Date Parsing and Time-Series Indexing:
- Pandas imports raw CSV date columns as string data types (
object). - Explicit conversion:
covid['date'] = pd.to_datetime(covid['date']). - Setting temporal index:
- DataFrames default to an auto-incrementing integer
RangeIndex (0,1,2,…). - Set datetime as the DataFrame index:
covid.index = covid['date']. - Once set as a
DatetimeIndex, temporal line plots correctly align sequential time series along the x-axis, scaling interval ticks and leap dates automatically.
- Statewide Aggregations via GroupBy:
- Compute total daily cumulative state cases by aggregating across all
100 counties for each date:state_cases = covid.groupby(covid.index)[['cases']].sum()
- SQL Equivalent:
SELECT date, SUM(cases) FROM covid GROUP BY date ORDER BY date;
- Generate time-series line plot:
state_cases.plot()plt.show()
- Relational Merging with Census Data:
- Load demographic populations:
census = pd.read_csv('census.csv') containing county FIPS codes (fips) and population estimates (pop_estimate). - Example population benchmarks: Pitt County FIPS
37147 (≈178,000), Alamance County FIPS 37001 (169,509), Alexander County FIPS 37003 (37,497). - Relational join operation: Merge tables on matching FIPS codes:
covid_census = covid.merge(census, on='fips')
- Sort and structure the merged dataset:
covid_census = covid_census.sort_values(by=['date', 'county'], ascending=[True, True])- Re-assign temporal index:
covid_census.index = covid_census['date'] - Remove redundant column:
del covid_census['date']
Population Normalization and Comparative Epidemiological Rate Calculations
- Epidemiological Pitfalls of Raw Case Counts:
- Comparing raw positive infection counts between heterogeneous regions creates structural bias:
- Wake County / Mecklenburg County population:
≈1,000,000 residents. - Pitt County population:
≈180,000 residents. - North Carolina overall population:
≈10,800,000 residents.
- Raw counts are essential for assessing local healthcare operational resource demand (e.g., staffing, beds, medications for
5,000 patients). - Raw counts cannot measure regional population risk, disease incidence, or epidemiological burden because the denominators are unequal.
- Direct cross-regional plotting failure: Overlaying raw Pitt County cases (
≈60,000) against total North Carolina cases (≈3,000,000) flattens the county line at the bottom of the chart due to the order-of-magnitude scaling difference (106 scale).
- Formulas for Normalizing Population Rates:
- Normalize raw counts into rates per
1,000 residents:
Rate per 1,000=Population EstimateCumulative Cases×1000 - Alternative population normalizations:
Rate per 10,000=Population EstimateCumulative Cases×10,000Rate per 100,000=Population EstimateCumulative Cases×100,000
- Step-by-Step Implementation in Pandas:
- Step 1: Extract and deep-copy Pitt County observations:
covid_census_pitt = covid_census[covid_census.county == 'Pitt'].copy()
- Step 2: Calculate Pitt County rate per
1,000:covid_census_pitt['rate_per_1k'] = (covid_census_pitt['cases'] * 1000) / covid_census_pitt['pop_estimate']- End-of-period rate: Reaches
342.9 per 1,000 (meaning ≈343 out of every 1,000 residents were cumulatively infected).
- Step 3: Aggregate statewide cases and total population estimates by date:
covid_census_nc = covid_census.groupby(covid_census.index)[['cases', 'pop_estimate']].sum()
- Step 4: Calculate statewide rate per
1,000:covid_census_nc['rate_per_1k'] = (covid_census_nc['cases'] * 1000) / covid_census_nc['pop_estimate']
- Dual-Layer Time-Series Visualization:
- Plot both rates on a shared axis (
ax):ax = covid_census_nc['rate_per_1k'].plot()covid_census_pitt['rate_per_1k'].plot(ax=ax)plt.legend(['North Carolina', 'Pitt'])plt.show()
- Visual epidemiological analysis:
- Normalizing both series to rates per
1,000 eliminates scale distortion. - Except for a brief period in late 2020 where statewide rates matched Pitt County, Pitt County consistently experienced higher cumulative case rates per capita than the North Carolina state baseline.
- Catchment bias in regional hospital metrics:
- Pitt County houses a major regional Level 1 trauma center (ECU Health Medical Center) serving a 29-county catchment area across Eastern North Carolina.
- If transferred patients testing positive are logged in the facility's home county rather than their residence of origin, the county's rate numerator becomes artificially inflated relative to its residential population denominator.
Healthcare Analytics Implementation, Dashboard Design, and Metric Interpretation
- Reporting Period Artifacts in Surveillance Data:
- Sawtooth/Stair-step patterns: During the pandemic, public health reporting shifted from daily cadences to consolidated weekly batches. Six days of zero reporting followed by a single 7-day lumped total produced false visual spikes that news media often misinterpreted as single-day outbreaks.
- Seven-day moving averages:
MA7=71∑i=06xt−i
Overlaid onto raw step-wise counts to smooth artificial batch reporting artifacts.
- Double counting: Antigen rapid tests and molecular PCR tests were frequently aggregated into a single positive count without de-duplicating patients who took both tests.
- Mortality classification rules: Guidelines mandated classifying deceased patients testing positive for SARS-CoV-2 as COVID-related deaths, even when primary mortality was caused by unrelated trauma (e.g., motor vehicle accidents, cerebral aneurysms).
- Interactive Dashboard Design (ECU Health / Grant Operations):
- Target vs. baseline comparative architecture: Compares user-selected counties against the North Carolina state average, Eastern North Carolina (41 counties), or regional healthcare administrative divisions (ECU Health SWARM regions).
- Small-population rate volatility:
- In sparsely populated counties (e.g., Hyde County, population
≈5,000), small changes in absolute events cause massive swings in mortality and case rates. - Densely populated counties (e.g., Wake County, population
≈1,100,000) dilute individual events, maintaining stable longitudinal rates.
- Community Health Needs Assessment (CHNA) Case Study:
- Assessing community survey penetration:
- Cumberland County generated
1,545 raw survey responses (the highest absolute count). - Hyde County generated
213 raw survey responses.
- Normalized survey coverage per
10,000 population:- Hyde County achieved
431.4 responses per 10,000 residents (representing ≈1.3% population coverage). - Cumberland County's rate per
10,000 was among the lowest in the state due to its large population base.
- Operational trade-offs: Raw counts establish statistical sampling thresholds (e.g., reaching a statistical target of
n≥300), whereas normalized rates identify true population survey representation to guide community outreach.
- User Experience and Usability Constraints:
- Color theory and accessibility: Visual dashboards should use diverging, color-blind-friendly palettes (such as contrasting blue and orange).
- Institutional branding conflicts:
- Purple: Strongly associated with East Carolina University (ECU).
- Dark Blue: Strongly associated with Duke University.
- Light Blue: Strongly associated with UNC Chapel Hill.
- Red: Strongly associated with North Carolina State University.
- Inter-agency negotiations (such as those with AHEC) often mandate non-conflicting colors (e.g., neutral greens), sometimes overriding optimal accessible visual design.
In-Class Dialogue and Technical Q&A
- Question: Why does the script throw an error stating the role
coexternal already exists, and does this halt execution?- Response: PostgreSQL roles exist at the database server level and persist across all individual databases. If a role was created in an earlier semester or course (such as 6430 or 6590), it already exists globally. In PostgreSQL, executing an existing
CREATE ROLE within a transactional block aborts the entire batch. The error can be ignored, but the command must be isolated so subsequent GRANT permission statements can run independently.
- Question: Why did the scatter plot trendline appear pulled downward away from the main cluster of blood pressure points?
- Response: Scatter plots of clinical vital signs often include extreme anomalies, such as non-physiological blood pressure readings (e.g., systolic
5 with diastolic 150, or post-mortem entries at 0/0). In ordinary least squares or trendline fittings, these distant outliers exert strong leverage, tilting the regression slope downward away from the central data distribution.
- Question: When plotting multiple time-series lines onto the same figure, why does a previous broken run cause lines to appear with incorrect colors or overlay artifacts?
- Response: Newer versions of Matplotlib buffer active figure objects in memory until they are explicitly rendered. If a cell plots data and encounters an execution error before reaching
plt.show(), the unrendered graphic stays in the active buffer. Re-running the cell overlays the new plot directly on top of the buffered graphic. Explicitly calling plt.show() flushes the drawing buffer, ensuring subsequent plots render cleanly.
- Question: What is the difference between rural and urban response tracking in the CHNA dashboard when comparing raw totals to normalized rates?
- Response: Looking only at raw counts suggests that urban counties (like Cumberland with
1,545 surveys) have sufficient data while rural counties (like Hyde with 213 surveys) require more outreach. However, calculating rates per 10,000 residents reveals that Hyde's 213 surveys cover 1.3% of its population (431.4 per 10,000), whereas Cumberland's high response count actually represents poor population coverage. Effective healthcare resource allocation requires evaluating both absolute sample sizes and population-adjusted rates.