Exploratory Data Analysis in Python: Pandas, PostgreSQL Ingestion, and Clinical Data Profiling

Course Logistics, Grading Structure, and Institutional Reorganization

  • Semester Course Schedules and Milestones:

    • Doctoral seminar coursework is concluded, leaving grading tasks pending.
    • Economics undergraduate coursework concludes with a Tuesday examination, meaning no subsequent lecture session will be held.
    • Course 64706470 includes two additional weeks of instruction due to a scheduled class meeting occurring on the Monday immediately preceding Reading Day.
    • Course 41604160 is currently in its final week of instruction.
    • Course 60606060 has only one single lecture remaining on Wednesday.
    • Six distinct courses are running concurrently.
  • Grading Criteria and Curve Distribution:

    • Project milestones are graded on an accelerated timeline, with individual homework assignments evaluated subsequently.
    • Final grades are subjected to a significant grading curve.
    • Grade distributions historically form two discrete, non-contiguous clusters (designated Cluster A and Cluster B) rather than a continuous spectrum of student marks.
    • Letter grades (AA and BB) are mapped directly to these clusters.
    • Students missing two to three assignments (22 to 33 assignments) forfeit any implied guarantee of an assigned grade of BB.
  • Departmental Reorganization Models and Implications:

    • Under pending administrative reorganization models, the current academic department will cease to exist as an independent entity.
    • Degree programs, program accreditations, and program directorships will remain fully intact without adversely affecting enrolled students.
    • Health and Human Sciences (HHS) Model:
      • The department would be subsumed under Health Education and Promotion (Public Health), transitioning under the administration of the former College of Health and Human Performance (HHP).
      • Academic leadership would fall under Dr. Michelle Walton.
      • The absorbing faculty size is four times (4×4\times) the departmental baseline.
      • This model risks the addition of non-teaching, non-research administrative overhead, costing approximately "$1,000,000""\$1{,}000{,}000" in new institutional cost centers.
    • College of Business Model:
      • The department would be incorporated into the College of Business, allowing the preservation of departmental autonomy.
      • Requires physical relocation to Main Campus, vacating the current building.
      • The program would acquire the 4th-floor spaces vacated by the Department of Foreign Languages.
      • Physical facilities include advanced computer laboratories sponsored by dedicated corporate hardware partners, including IBM.
      • Strategic priority is focused on position preservation and avoiding institutional salary reductions ranging from 20%20\% to 40%40\%.

JupyterLab Environment and Operating System Configuration

  • File Paths and Environment Startup:

    • Windows deployment path: C:\Users\Public\6430\JupyterLab files\exercises.
    • macOS deployment path: /Shared/6430/JupyterLab files/exercises.
    • The environment is launched through execution of the directory .bat script, which initializes JupyterLab.
  • Display Resolution Management Trick:

    • Operating across dual monitors of discordant resolution (e.g., a high-resolution laptop integrated display paired with a lower-resolution secondary external monitor) causes simultaneous zoom distortion if multiple tabs share an identical browser process.
    • To maintain separate UI scaling factors, run isolated browser applications concurrently: execute Google Chrome on the primary monitor for detailed scaling control while running Mozilla Firefox on the secondary display at a reduced zoom level.
  • Cell Execution and Memory Optimization in JupyterLab:

    • Nested cell groupings must be manually revealed by clicking disclosure arrows.
    • Large outputs can be collapsed into a scrollable sub-pane by clicking the margin adjacent to the output cell, preventing excessive DOM node generation.
    • Unrestricted console streaming into browser memory can accumulate up to 15 GB15\,\text{GB} of RAM consumption in Chrome, inducing browser failure.
    • Jupyter Notebook automatically caps large text streaming outputs beyond a fixed size threshold, replacing lines with an expandable banner indicating additional content.
  • Python Package Installation (pip):

    • Students concurrently or previously enrolled in Course 60606060 share identical package requirements and must bypass execution of the pip install cell.
    • First-time users execute the cell via Shift + Enter or Ctrl + Enter until execution status resolves from an asterisk (*) to an integer.
    • Once requirements are verified, comment out the installation code to prevent subsequent accidental runs.
  • Filesystem Paths and Operating System Interfaces:

    • Directory configuration leverages the Python os module: import os.
    • Current directory detection: os.getcwd().
    • Output folder concatenation: python output_dir = os.getcwd() + '/output'         
    • Path separator conventions: Always utilize forward slashes (/) regardless of underlying operating platform (macOS, Linux, or Windows). Backslashes (\) operate as escape characters in Python string literals, necessitating escape sequences (\\) to produce a single literal slash. Python automatically standardizes forward slashes across all supported operating systems.
    • Directory verification relies on os.makedirs() if target storage paths are absent.
    • Dynamic path variables prevent manual updates across multiple code cells and allow clean transitions to external properties files (as utilized in Course 65906590).
  • IPython Magic Commands:

    • %load_ext autoreload: Loads the extension module for dynamic reload tracking.
    • %autoreload 2: Forces the IPython kernel to reload all modules automatically prior to executing any new code line. This bypasses the default Python behavior where imported custom modules remain cached in memory unless the kernel is completely restarted.
    • %matplotlib inline: Configures the Matplotlib graphics engine to embed generated static plot figures directly within the output pane of the notebook rather than routing to external display windows.

PostgreSQL Database Setup and Administrative Grants

  • Database Infrastructure and Connection Parameters:

    • Target database: covid6430.
    • Database Engine: PostgreSQL 1717.
    • Default network port: 54325432 (verified via the Properties interface of pgAdmin).
    • Connection driver: Integrated via an automated SQLAlchemy connection engine object.
    • Engine workflow: Pandas functions receive the engine handle as an argument to negotiate query submission and data streaming.
  • User Provisioning and Schema Permissions in pgAdmin:

    • Users imported from Course 60606060 exist at the server level, but lack object-level access permissions to the database schemas utilized in Course 64306430.
    • Role creation script executed in the pgAdmin Query Tool: sql CREATE ROLE <username> WITH LOGIN PASSWORD '<password>'; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;         If the database role has already been established, PostgreSQL returns a non-fatal notification stating that the role already exists.
  • Permission Granting Statements: sql GRANT USAGE ON SCHEMA hw4 TO <username>; GRANT SELECT ON ALL TABLES IN SCHEMA hw4 TO <username>; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

  • Granting permissions in PostgreSQL is idempotent: re-executing grant commands over existing privileges does not throw an error and will execute cleanly.

  • If students completed prior Tableau exercises within the same database environment, user roles and privileges are already active.

Data Ingestion via Pandas: SQL Queries vs. Database Tables

  • Core Import Standards:

    • Data manipulation standard library import: import pandas as pd.
    • Visualization interface import: import matplotlib.pyplot as plt.
    • Target data schema: hw4 (representing the ARMC patient dataset comprising 100,000100{,}000 patient entries).
  • Executing SQL Queries with pd.read_sql_query:

    • Reads the outcome of an arbitrary SQL query into a tabular pandas.DataFrame: python schema = 'hw4' query = f'SELECT * FROM {schema}.patients LIMIT 100' patients = pd.read_sql_query(query, con=engine) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    • Interpolation approaches: Standard .format(schema) notation is superseded by formatted string literals (f-strings), embedding {schema} directly into the query template.
    • Display characteristics:
      • Calling print(patients) flattens the DataFrame into monospace raw terminal text, displaying ugly newline wraps and line overflow indicators.
      • Evaluating the bare reference patients as the terminating line of a notebook cell utilizes Jupyter's rich HTML rendering engine, outputting a structured table display with truncated row indicators (...) showing the first 55 and last 55 rows.
      • A reddish or orange prompt identifier (e.g., [6]) on an output cell indicates that Jupyter inferred a display operation without an explicit print() call.
  • Type Inference Limitations of read_sql_query:

    • When utilizing pd.read_sql_query(), Pandas does not read the underlying column catalogs from the PostgreSQL data dictionary; it infers storage types from the incoming raw data stream.
    • This causes inconsistent typing: birth_date is routinely classified as a generic string object rather than a native datetime64[ns] timestamp, whereas equivalent columns in the encounters table (admit_date, discharge_date) correctly resolve to timestamps.
    • Manual resolution requires explicit conversion: patients['birth_date'] = pd.to_datetime(patients['birth_date']).
  • Memory Management: Database Buffers vs. Python Memory Allocation:

    • Database management systems (DBMS) efficiently process massive queries by swapping operations between buffer pools and disk-based temporary structures.
    • Pandas DataFrames reside entirely in system volatile memory (RAM).
    • Ingesting large unaggregated queries (such as 20,000,00020{,}000{,}000 rows) can exhaust physical memory and trigger kernel crashes.
    • Best practice: Execute heavy filtering, aggregations, and joins directly within PostgreSQL, returning only the refined dataset to Python.
  • Direct Ingestion with pd.read_sql_table:

    • Bypasses manual SQL text and ingests a full table structure while querying table metadata directly: python patients = pd.read_sql_table('patients', con=engine, schema=schema) encounters = pd.read_sql_table('encounters', con=engine, schema=schema) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  • Direct table reads ensure accurate type mapping, correctly mapping PostgreSQL date fields into datetime64[ns] without manual casting.

Slicing, Dicing, and DataFrame Structural Subsetting

  • Metadata Inspection via DataFrame.info():

    • patients.info() provides structural properties: entry count, RangeIndex boundaries (00 to 9999 for a 100100-record test slice), column count (1515 columns indexed from 00 to 1414), memory utilization, and non-null counts.
    • Data types are delineated into primitive signed integers (int64), floating points (float64), and text-encoded references (object).
  • Top and Bottom Slicing (head and tail):

    • patients.head(n=5): Extracts the initial nn rows (defaults to 55). Derives its name from the computer science convention designating the top of a linked data structure.

    • patients.tail(n=5): Extracts the terminating nn rows. Useful for inspect-on-append structures such as system access logs.

    • SQL Equivalent: ```sql -- Head equivalent SELECT * FROM hw4.patients LIMIT 5;

      -- Tail equivalent (assuming known count of 100) SELECT * FROM hw4.patients OFFSET 95 LIMIT 5;         ```

    • SQL pagination utilizes OFFSET to bypass preceding rows, though OFFSET is not universally supported across all relational engines.

  • Index Range Slicing:

    • Square-bracket positional slicing uses half-open intervals [start:stop], including the starting index and excluding the stopping index: python patients[5:10] &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;         This command reads indices 5,6,7,8,95, 6, 7, 8, 9, returning exactly 55 records.
  • Negative indexing permits slicing relative to the end of the data frame: python patients[-5:] &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;         This accesses the dataset starting five positions from the end and continues through the final row.

  • Series vs. DataFrame Column Subsetting:

    • Single-Bracket Extraction / Dot Notation:
      • patients['gender'] or patients.gender.
      • Dot-notation fails when column names contain spaces or special characters (e.g., marital status).
      • Returns a 1-dimensional pandas.Series object (a single vector), which renders as unformatted console output.
    • Double-Bracket Extraction (DataFrame Generation):
      • patients[['gender']].
      • Supplying a Python list inside the bracket accessor forces the output to be structured as a 2-dimensional pandas.DataFrame (a matrix), rendering as a styled HTML table.
      • Machine learning models and downstream pipelines often demand a strict distinction between a 1D Series target and a 2D DataFrame feature matrix.
    • Multi-Column Selection:
      • Requires passing an explicit list of column names: patients[['patient_id', 'gender', 'race']].
      • Attempting patients['patient_id', 'gender', 'race'] throws a KeyError because Pandas interprets the comma-separated arguments as a single multi-part key rather than a list of independent columns.

Boolean Indexing and Multi-Condition Filtering

  • Underlying Mechanics of Filtering:

    • Evaluating a conditional statement such as patients['gender'] == 'M' yields a Boolean Series populated with True and False values across the index.
    • When placed within DataFrame selection brackets patients[...], Pandas parses the vector, returning rows associated with True and dropping rows containing False.
  • Boolean Logic Operators and Parentheses Rules:

    • Standard Python logical keywords (and, or, not) cannot evaluate across array vectors; bitwise operators must be used instead:
      • Use & for logical AND.
      • Use | for logical OR.
      • Use ~ for logical NOT.
    • Operator precedence rules will evaluate bitwise operations before equality tests. Omitting parentheses causes Pandas to combine the inner terms incorrectly, producing ambiguous evaluation errors or array type mismatches.
    • Clauses must be encapsulated within parentheses: python patients[(patients['gender'] == 'M') & (patients['race'] == 'unknown')] &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  • Boolean Truth Table for Conjunction (∧\land):

    • True∧True=True\text{True} \land \text{True} = \text{True}
    • True∧False=False\text{True} \land \text{False} = \text{False}
    • False∧True=False\text{False} \land \text{True} = \text{False}
    • False∧False=False\text{False} \land \text{False} = \text{False}
  • Alternative Filtering Syntaxes:

    • DataFrame.query() supports string-based query expressions: python patients.query("gender == 'M' and race == 'unknown'") &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  • External variables can be referenced inside .query() using the @ symbol: python target_race = 'unknown' patients.query("gender == 'M' and race == @target_race") &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

  • Combined Row Filtering and Column Selection:

    • Filtering logic can be chained directly with column indexers: python patients[(patients['gender'] == 'M') & (patients['race'] == 'unknown')][['patient_id', 'gender', 'race']] &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

Descriptive Statistics, Frequency Distribution, and Plotting

  • Standard and Full Summary Statistics (describe):

    • patients.describe() processes only numerical attributes (patient_id, zip).
    • Calculating the arithmetic mean or standard deviation of nominal or categorical identifiers stored as numbers (such as a patient identification key or a ZIP code) is statistically invalid.
    • patients.describe(include='all') expands statistical evaluation across categorical and string columns, adding three non-numeric metrics:
      • unique: Cardinality of distinct non-null categories.
      • top: The most frequently occurring categorical value.
      • freq: The raw count of the most frequent categorical value.
    • Incompatible calculations output NaN (Not a Number).
  • Empirical Values Observed in 100-Patient Sample:

    • first_name: 100100 entries, 9595 unique; top value is Jonathan with a frequency of 22.
    • middle_initial: 2121 unique single-character values; modal character is J with a frequency of 1616.
    • last_name: 9797 unique names; modal name is Johnson with a frequency of 22.
    • gender: 22 unique values; Female frequency is 6060, implying Male frequency is 4040.
    • race: 44 unique categories; Caucasian accounts for 6969 of the 100100 records.
    • religion: 1313 unique categories; None constitutes the highest frequency with 2121 records.
  • Matrix Transposition (transpose or .T):

    • Wide descriptive tables cause horizontal scrolling and awkward document orientation.
    • Applying .transpose() or .T swaps the axes, aligning attributes along the y-axis and statistical metrics across the x-axis, fitting output to standard page widths.
  • Value Frequency Calculations:

    • patients['race'].value_counts() aggregates distinct categories and returns total row instances: text Caucasian 69 unknown 29 Other 1 Hispanic 1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  • By default, value_counts() orders the output descending by frequency.

  • SQL Equivalent: sql SELECT race, COUNT(*) FROM hw4.patients GROUP BY race ORDER BY COUNT(*) DESC; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

  • Plotting Categorical Distributions:

    • Directly invoking .plot() on a value_counts() Series defaults to a line chart, which is methodologically incorrect for discrete nominal data.
    • Bar plot syntax: patients['race'].value_counts().plot.bar().
    • Syntax warning: Calling plot().bar() with parentheses attached to plot triggers an AttributeError. Calling .plot() executes the rendering method and returns a Matplotlib AxesSubplot instance, which lacks a subsequent .bar() child method.

Large-Scale Data Loading and Memory Optimization

  • Scaling Up Ingestion Volumes:

    • Ingesting the full dataset using pd.read_sql_table() loads 100,000100{,}000 patient records and 1,700,0001{,}700{,}000 clinical encounter records.
    • Full patient table metadata confirms 100,000100{,}000 non-null rows across all 1515 columns, consuming approximately 11.4 MB11.4\,\text{MB} of system memory.
  • Memory Footprint and Minification Strategies:

    • Unchecked database ingestion often defaults small integers and categories into broad 6464-bit types (int64, float64), causing significant memory bloat.
    • Downcasting and minification algorithms convert standard structures into optimized primitives (e.g., int8, int16, or categorical types), shrinking multi-gigabyte DataFrames down to roughly 100 MB100\,\text{MB}.
    • Minification warning: Aggressive type truncation can introduce data types unsupported by downstream machine learning libraries, causing algorithmic execution errors.
  • The Default Non-Null Count Suppression Threshold:

    • Calling encounters.info() on the encounter table suppresses non-null counts by default.
    • Pandas sets an internal performance threshold of exactly 1,069,7851{,}069{,}785 rows. Datasets exceeding this row threshold skip automated null-value scans to prevent high processing latency.
    • To force an explicit null-value scan, override the setting using: encounters.info(show_counts=True).

Clinical Data Profiling and Imputation Methodologies

  • Integrity and Relational Sparsity in Encounters Data:

    • Total recorded encounters: 1,700,0001{,}700{,}000.
    • Key attributes display full data density: encounter_id and patient_id have 100%100\% data completeness with zero missing records, satisfying relational database constraints where every encounter must map directly to a corresponding patient in a 1:11:1 mandatory relationship.
    • Temporal attributes: admit_date is 100%100\% complete (1,700,0001{,}700{,}000 non-null rows), while discharge_date is missing approximately 300,000300{,}000 values, indicating either open hospitalizations, non-inpatient interactions, or recording gaps.
  • Sparsity of Clinical Vital Signs:

    • Severe missingness affects recorded physiological measurements, with over 90%90\% of entries missing (retaining single-digit non-null percentages):
      • Systolic Blood Pressure (bp_systolic)
      • Diastolic Blood Pressure (bp_diastolic)
      • Height
      • Weight (shows slightly higher density than other vitals)
      • Pulse
      • Body Temperature
  • Analytical Challenges with Overlapping Measurements:

    • Blood pressure evaluation requires both systolic and diastolic measurements to occur simultaneously within the same encounter.
    • The maximum possible count of complete blood pressure records is constrained by the smaller non-null total between systolic and diastolic counts (135,192135{,}192 instances).
    • If non-null entries for systolic and diastolic measurements fail to overlap temporally, the true count of complete blood pressure records drops toward zero.
  • Cohort Size vs. Dimensional Completeness:

    • Filtering a dataset with 90%90\% missingness across clinical parameters poses different challenges depending on the analytical goal:
      • Clinical Trial Cohort Identification: Extracting a small cohort (e.g., 2020 candidate patients) from a baseline of 100,000100{,}000 distinct individuals across 1,700,0001{,}700{,}000 encounters remains feasible, as dropping incomplete encounters leaves enough raw observations to identify candidate patients.
      • Machine Learning / Multivariate Models: Constructing predictive models requiring complete vectors across multiple vitals (e.g., computing Body Mass Index (BMI) from joint height and weight alongside blood pressure) shrinks the usable dataset drastically, risking model failure unless missing entries are imputed.
  • Data Imputation Strategies Across Clinical Variables:

    • Adult Height Imputation: Height remains static in adult populations over time. Missing values do not require statistical estimation; they can be populated using forward-filling (ffill) from an individual's nearest historical encounter.
    • Pediatric Height Imputation: Pediatric height changes continuously. Imputation requires mathematical interpolation between surrounding observations or trend-based extrapolation along growth trajectories.
    • Body Weight Imputation: Weight fluctuates dynamically (e.g., documenting a loss of 24 lbs24\,\text{lbs} within a single month). Simple forward-fill methods risk significant error; person-specific median values or rolling moving averages provide better estimates.
    • Highly Volatile Vital Signs: Highly variable physiological metrics (such as pulse rate, body temperature, and isolated blood pressure readings) fluctuate too erratically to permit reliable statistical imputation, and should generally not be filled artificially.