Project Homepage | Key Findings and Recommendations | Process and Documentation | SQL, Python, Google Sheets, Tableau Syntax | References, Data Sources, and Notes
PROCESS AND DOCUMENTATION - UPDATED COPY
Data Sources
Source / URL: Cecil G. Sheps Center for Health Services Research (UNC Chapel Hill)
Collection Method: Collected annually in collaboration with the North Carolina State Board of Physical Therapy Examiners. Counts reflect active, licensed clinicians practicing in NC as of October 31 of each data year, mapped by primary practice location.
Data Organization: Originally four independent wide-format datasets tracking 100 North Carolina counties. These datasets provided insight to Physical Therapist total counts and counts per 10K population per county, as well as the same convention for Physical Therapist Assistants.
Reshaped via Python (BigQuery DataFrames) into normalized long-format tables and unified using SQL INNER JOIN logic into a master table containing 6 columns and 2,550 rows (100 counties × 25 years). The table was named nc_workforce_master. The table is organized by county and year, and each column provides values for physical therapist and physical therapy counts/rates in defined counties each year.
Reliable (High): High degree of reliability. Data is sourced from the oldest continuous state health workforce data system in the United States, utilizing census-level licensing board registries rather than voluntary sample surveys.
Original (High): Primary, un-aggregated administrative data directly owned by the licensing boards and maintained under strict state research oversight.
Comprehensive (Medium-High): Highly comprehensive regarding geographic supply, saturation, and longitudinal timelines (25-year trend lines). It lacks individual clinician-level tracking (demographics or direct exit surveys are isolated to the national benchmarks).
Current (High): Spans 2000 to 2024, capturing the baseline pre-pandemic environment, pandemic-era changes, and years following
Cited (High): Consistently cited by health policymakers, academic researchers, and the North Carolina Area Health Education Centers (AHEC) Program.
Privacy: Fully anonymized at the aggregate county level. Contains no Protected Health Information (PHI), individual provider names, National Provider Identifiers (NPIs), or individual license numbers.
Source / URL: University of Wisconsin Population Health Institute / Robert Wood Johnson Foundation
Collection Method: Annual compilation aggregating data from vital statistics and federal registries (including the CDC, US Census Bureau, National Center for Health Statistics, and Behavioral Risk Factor Surveillance System).
Data Organization: A massive, comprehensive national CSV file (analytic_data2025_v3.csv) covering nearly every county in the United States. It contains hundreds of columns dedicated to health outcomes, health factors, demographics, and 5-digit Federal Information Processing Series (FIPS) codes. For the physical therapy attrition project, this will be filtered to North Carolina records (State Abbreviation = 'NC') to join with the master workforce dataset.
Reliable (High): Managed by the University of Wisconsin and funded by the Robert Wood Johnson Foundation. It is a major public health benchmarking data tool used by community planners and epidemiologists across the US.
Original (Medium): This is a secondary, curated compilation. While the data is highly trustworthy, it is aggregated from primary federal sources rather than collected directly by the publishers.
Comprehensive (High): Exceptionally comprehensive. It introduces vital community vulnerability metrics—like premature mortality, economic stress, and overall health behaviors—allowing you to analyze if PT/PTA shortage trends correlate directly with poor regional health outcomes.
Current (High): The 2025 Release Year dataset reflects the most up-to-date community health baselines available for post-pandemic analysis.
Cited (High): Extensively peer-reviewed and cited in thousands of public health studies, white papers, and policy initiatives.
Privacy Check: Zero risk. All records are strictly population-level, macro-aggregated statistics. There is no Protected Health Information (PHI) or individual patient tracking.
The primary workforce data were obtained from the North Carolina Health Professions Data System maintained by the Cecil G. Sheps Center for Health Services Research at the University of North Carolina at Chapel Hill.
The source data provide annual county-level information for actively licensed Physical Therapists and Physical Therapist Assistants practicing in North Carolina. Four source tables were used:
PT total provider count
PT provider rate per 10,000 population
PTA total provider count
PTA provider rate per 10,000 population
Each source table originally stored annual values from 2000–2024 in wide format, with one row per geographic area and separate columns for each year.
Selected county characteristics were obtained from the 2025 County Health Rankings & Roadmaps Annual Data Release.
The variables used in the project were population, age 65 and older, percent rural, functional limitations/disability, median household income, and uninsured percentage.
The official 2025 CHR&R data dictionary confirms that v053 represents age 65 and older, v058 percent rural, v180functional limitations/disability, v063 median household income, v085 uninsured, and v051 population. The earlier project notes incorrectly identify the age 65+ variable as v052; v052 represents the population below age 18.
The four workforce source tables were uploaded to BigQuery in wide format.
Python using Google's bigframes.pandas library was then used to reshape each table into long format. Instead of one column for each year, the transformed data contain one row for each county-year observation.
This produced four standardized tables that could be joined on the shared County + Year key.
The Python code also converted year values to integers and rate/count fields to appropriate numerical formats.
One limitation of the original script is that it renamed imported columns based on their position. Because positional renaming assumes the source columns remain in the same order, future runs should include an explicit schema check before applying the mapping.
The four long-format workforce tables were joined using County and Year.
The original SQL output contained 2,550 records rather than the expected 2,500. Subsequent validation in Google Sheets identified two sets of non-county records:
25 rows where County was "North Carolina"
25 rows where County was "County"
After removing those records, the analytical table contained the expected:
2,500 rows
100 unique counties
25 workforce years
A future version of the SQL workflow should filter these records before creating the workforce master table so the clean 2,500-row structure is established upstream.
The national County Health Rankings dataset was filtered to North Carolina, the state summary was excluded, county names were standardized, and the selected county variables were cast to usable numeric formats.
The source dataset imported into BigQuery with generic string_field_* column names. The relevant fields were therefore identified from the CHR&R data dictionary and explicitly renamed during table creation.
The final SQL step used a LEFT JOIN to attach county-level CHR&R variables to every workforce observation.
The workforce table remains longitudinal from 2000–2024, while the CHR&R variables represent one annual-release snapshot. For this reason, the county variables are used as descriptive geographic context rather than historical predictors.
The joined table was imported into Google Sheets and duplicated so that the original SQL output was preserved separately from the cleaning and calculation workflow.
Data-quality checks included:
validating the expected row count;
identifying and removing state/header rows;
confirming 100 unique counties;
confirming a complete 2000–2024 year range;
checking for duplicate county-year combinations;
checking core fields for missing values;
standardizing percentage formatting;
reviewing logical consistency in provider counts and rates.
After cleaning, the final analytical structure contained 2,500 unique county-year records.
Year-over-year PT and PTA rate changes were calculated only when the current observation and previous row represented consecutive years within the same county.
The first observation for each county therefore has no annual change value.
Rate-change outliers were identified using the standard 1.5 × interquartile-range rule across the complete set of annual county-level rate changes.
For PT:
Q1 = approximately -0.18 providers per 10,000
Q3 = approximately +0.39
Lower outlier threshold = approximately -1.035
Upper outlier threshold = approximately +1.245
For PTA:
Q1 = approximately -0.14
Q3 = approximately +0.29
Lower threshold = approximately -0.785
Upper threshold = approximately +0.935
Values below the lower threshold were labeled Rapid Attrition, while values above the upper threshold were labeled Rapid Growth.
These labels represent extreme annual provider-rate changes, not confirmed clinician departures or hires.
PT and PTA provider counts were used to calculate each profession's share of the combined rehabilitation workforce within each county-year.
A protected formula should be used so counties with zero combined providers do not produce divide-by-zero errors.
Counties were grouped using a project-defined classification based on the percent of the population classified as rural in the CHR&R release:
Urban: less than 25% rural
Suburban: 25%–75% rural
Rural: greater than 75% rural
This produces:
39 Rural counties
50 Suburban counties
11 Urban counties
These categories were created specifically for this analysis and should not be presented as an official federal or state rurality classification.
The cleaned values-only table was imported into Tableau Public.
A four-dashboard structure was developed to allow users to move from broad statewide patterns to more detailed county-level analysis:
Provides a geographic view of PT/PTA supply, profession proportions, and annual provider-rate trends.
Ranks counties according to the number of Rapid Growth and Rapid Attrition events during the selected period.
Compares growth and decline event counts by county type and year and positions counties according to their total increase/decrease event counts.
The quadrant labels are relative descriptive categories determined by reference-line averages rather than validated risk categories.
Compares workforce supply with selected county characteristics, examines the PT/PTA relationship by county type, and identifies counties with no net increase in combined provider rate from 2000 to 2024.
For demographic comparisons, the 2025 CHR&R release should be clearly separated from the historical workforce timeline. A cleaner design would compare those variables specifically with 2024 provider rates.
Calculated field syntax can be seen in the SQL, Python, Google Sheets, and Tableau subpage