Embedded Files
Project Homepage | Key Findings and Recommendations | Process and Documentation | SQL, Python, Google Sheets, Tableau Syntax | References, Data Sources, and Notes
SQL, PYTHON, GOOGLE SHEETS, AND TABLEAU SYNTAX — CORRECTED DOCUMENTATION
SQL, PYTHON, GOOGLE SHEETS, AND TABLEAU SYNTAX — CORRECTED DOCUMENTATION
Python — Wide-to-Long Transformation
Python — Wide-to-Long Transformation
import bigframes.pandas as bpd
bpd.options.bigquery.project = "physical-therapy-attrition"
bpd.options.display.max_columns = 50
DATASET_ID = "physical_therapy_attrition"
YEARS_LIST = [str(year) for year in range(2000, 2025)]
def reshape_wide_to_long(
source_table,
target_table,
metric_column_name,
data_type="Float64"
):
source_path = (
f"physical-therapy-attrition.{DATASET_ID}.{source_table}"
)
target_path = (
f"physical-therapy-attrition.{DATASET_ID}.{target_table}"
)
df_wide = bpd.read_gbq(source_path)
expected_columns = ["County"] + YEARS_LIST
# Validate the imported schema before positional renaming.
if len(df_wide.columns) != len(expected_columns):
raise ValueError(
f"{source_table}: expected {len(expected_columns)} "
f"columns but found {len(df_wide.columns)}."
)
rename_mapping = dict(
zip(df_wide.columns, expected_columns)
)
df_wide = df_wide.rename(columns=rename_mapping)
# Remove known non-county records before reshaping.
df_wide = df_wide[
~df_wide["County"].isin(["County", "North Carolina"])
]
df_long = df_wide.melt(
id_vars=["County"],
value_vars=YEARS_LIST,
var_name="Year",
value_name=metric_column_name
)
df_long["Year"] = df_long["Year"].astype("Int64")
df_long[metric_column_name] = (
df_long[metric_column_name].astype(data_type)
)
df_long = (
df_long
.sort_values(by=["County", "Year"])
.reset_index(drop=True)
)
df_long.to_gbq(
target_path,
if_exists="replace"
)
The four function calls for PT rate, PT count, PTA rate, and PTA count can remain as currently written.
SQL — Workforce Master Table
CREATE OR REPLACE TABLE
`physical-therapy-attrition.physical_therapy_attrition.nc_workforce_master`
AS
SELECT
pt_rate.County,
pt_rate.Year,
pt_rate.Rate_per_10k,
pt_total.Total_Count,
pta_rate.PTA_Rate_per_10k,
pta_total.PTA_Total_Count
FROM
`physical-therapy-attrition.physical_therapy_attrition.nc_pt_rate_long`
AS pt_rate
INNER JOIN
`physical-therapy-attrition.physical_therapy_attrition.nc_pt_total_long`
AS pt_total
ON
pt_rate.County = pt_total.County
AND pt_rate.Year = pt_total.Year
INNER JOIN
`physical-therapy-attrition.physical_therapy_attrition.nc_pta_rate_long`
AS pta_rate
ON
pt_rate.County = pta_rate.County
AND pt_rate.Year = pta_rate.Year
INNER JOIN
`physical-therapy-attrition.physical_therapy_attrition.nc_pta_total_long`
AS pta_total
ON
pt_rate.County = pta_total.County
AND pt_rate.Year = pta_total.Year
WHERE
TRIM(pt_rate.County) NOT IN ('County', 'North Carolina')
ORDER BY
County,
Year;
Validation Query
SELECT
COUNT(*) AS total_rows,
COUNT(DISTINCT County) AS unique_counties,
COUNT(DISTINCT Year) AS unique_years
FROM
`physical-therapy-attrition.physical_therapy_attrition.nc_workforce_master`;
total_rows: 2500
unique_counties: 100
unique_years: 25
SQL — County Health Table
CREATE OR REPLACE TABLE
`physical-therapy-attrition.physical_therapy_attrition.nc_county_health`
AS
SELECT
CAST(string_field_2 AS INT64) AS FIPS_Code,
TRIM(
REGEXP_REPLACE(
string_field_4,
r'(?i)\s+County$',
''
)
) AS County,
CAST(string_field_5 AS INT64)
AS Health_Snapshot_Year,
CAST(string_field_791 AS INT64)
AS Total_Population, -- v051 Population
CAST(string_field_731 AS FLOAT64)
AS Percent_65_Plus, -- v053 % 65 and Older
CAST(string_field_786 AS FLOAT64)
AS Percent_Rural, -- v058 % Rural
CAST(string_field_771 AS FLOAT64)
AS Percent_Disability, -- v180 Functional Limitations
CAST(string_field_612 AS FLOAT64)
AS Median_Household_Income, -- v063 Median Household Income
CAST(string_field_138 AS FLOAT64)
AS Percent_Uninsured -- v085 Uninsured
FROM
`physical-therapy-attrition.physical_therapy_attrition.raw_county_health`
WHERE
string_field_3 = 'NC'
AND LOWER(string_field_4) != 'north carolina'
AND string_field_2 IS NOT NULL
ORDER BY
County;
SQL — Final Join
CREATE OR REPLACE TABLE
`physical-therapy-attrition.physical_therapy_attrition.nc_county_health_and_workforce_trends`
AS
SELECT
workforce.County,
workforce.Year AS Workforce_Year,
health.Health_Snapshot_Year,
health.FIPS_Code,
workforce.Total_Count
AS PT_Total_Count,
workforce.Rate_per_10k
AS PT_Rate_per_10k,
workforce.PTA_Total_Count,
workforce.PTA_Rate_per_10k,
health.Total_Population,
health.Percent_65_Plus,
health.Percent_Rural,
health.Percent_Disability,
health.Median_Household_Income,
health.Percent_Uninsured
FROM
`physical-therapy-attrition.physical_therapy_attrition.nc_workforce_master`
AS workforce
LEFT JOIN
`physical-therapy-attrition.physical_therapy_attrition.nc_county_health`
AS health
ON
workforce.County = health.County
ORDER BY
workforce.County,
workforce.Year;
This creates a longitudinal workforce table with a repeated current county-context snapshot, not longitudinal demographic data.
The joined data table from BigQuery (nc.workforce.master) was imported into Google sheets and the sheet was renamed Raw Joined Results. This sheet was duplicated into a sheet called ‘Joined Results (Cleaning Sheets and Checks)’. The data was then cleaned here, checking for duplicates, null values, ensured logical consistency of data, and ensured proper formatting of percentage values.
From here, additional columns were created to identify PT/PTA provider rate change per year (per 10k population) within each county each year. From there, growth/attrition outliers were identified based on quartiles and additional columns were created to identify PT and PTA county status each year. Additional columns were created to identify PT vs. PTA provider proportions in each county per year, and counties were lagged as rural, suburban, or urban based on their rural percentage (in 2025) within another column.
This data was then copied and pasted (special - values only) to ensure seamless saving and import into Tableau.
The following spreadsheet showcases the organization process and includes the final table used for the analysis in Tableau: nc_county_health_and_workforce_trends_cleaned
A detailed breakdown of the cleaning strategies used in this document are located further down on this page in the Cleaning Log spreadsheet.
This spreadsheet details cleaning and preparation strategies to be used in the final table for the analysis in Tableau.
Google Sheets — Annual Rate Change
Google Sheets — Annual Rate Change
PT
PT
=IF(
AND(A3=A2, B3=B2+1),
F3-F2,
""
)
PTA
=IF(
AND(A3=A2, B3=B2+1),
J3-J2,
""
)
Google Sheets — Correct PT Outlier Classification
=IF(
G3="",
"",
IF(
G3 < (
QUARTILE.INC(G$2:G$2501,1)
- 1.5 * (
QUARTILE.INC(G$2:G$2501,3)
- QUARTILE.INC(G$2:G$2501,1)
)
),
"Rapid Attrition",
IF(
G3 > (
QUARTILE.INC(G$2:G$2501,3)
+ 1.5 * (
QUARTILE.INC(G$2:G$2501,3)
- QUARTILE.INC(G$2:G$2501,1)
)
),
"Rapid Growth",
"Standard"
)
)
)
Google Sheets — Corrected PTA Outlier Classification
Google Sheets — Corrected PTA Outlier Classification
=IF(
K3="",
"",
IF(
K3 < (
QUARTILE.INC(K$2:K$2501,1)
- 1.5 * (
QUARTILE.INC(K$2:K$2501,3)
- QUARTILE.INC(K$2:K$2501,1)
)
),
"Rapid Attrition",
IF(
K3 > (
QUARTILE.INC(K$2:K$2501,3)
+ 1.5 * (
QUARTILE.INC(K$2:K$2501,3)
- QUARTILE.INC(K$2:K$2501,1)
)
),
"Rapid Growth",
"Standard"
)
)
)
Google Sheets — Provider Proportion
Percent PT
=IFERROR(
E2/(E2+I2),
""
)
Percent PTA
=IFERROR(
I2/(E2+I2),
""
)
Google Sheets — County Type
Google Sheets — County Type
=ARRAYFORMULA(
IF(
O2:O2501="",
"",
IF(
O2:O2501<0.25,
"Urban",
IF(
O2:O2501<=0.75,
"Suburban",
"Rural"
)
)
)
)
County type is a project-defined analytical category based on the percent of each county's population classified as rural in the 2025 CHR&R release. It is not intended to reproduce an official federal rurality classification.
Tableau Calculated Fields
Dynamic Provider Rate per 10K
CASE [Select Profession]
WHEN "PT" THEN
[PT Rate per 10k]
WHEN "PTA" THEN
[PTA Rate per 10k]
WHEN "Both" THEN
ZN([PT Rate per 10k])
+ ZN([PTA Rate per 10k])
END
This returns actual provider supply.
Dynamic Provider Rate Change
CASE [Select Profession]
WHEN "PT" THEN
[PT Rate Change]
WHEN "PTA" THEN
[PTA Rate Change]
WHEN "Both" THEN
ZN([PT Rate Change])
+ ZN([PTA Rate Change])
END
The existing notes average the PT and PTA changes for "Both". Since the combined provider rate is PT rate + PTA rate and both use the same population denominator, the combined annual change is also the sum of the two changes.
Dynamic Growth Events
CASE [Select Profession]
WHEN "PT" THEN
[PT - Rapid Growth Count]
WHEN "PTA" THEN
[PTA - Rapid Growth Count]
WHEN "Both" THEN
ZN([PT - Rapid Growth Count])
+ ZN([PTA - Rapid Growth Count])
END
Dynamic Attrition Events
CASE [Select Profession]
WHEN "PT" THEN
[PT - Rapid Attrition Count]
WHEN "PTA" THEN
[PTA - Rapid Attrition Count]
WHEN "Both" THEN
ZN([PT - Rapid Attrition Count])
+ ZN([PTA - Rapid Attrition Count])
END
Provider Rate Relative to the Annual Average
[Dynamic Provider Rate per 10K]
-
{ FIXED : AVG([Dynamic Provider Rate per 10K]) }
Positive values indicate a provider rate above the average county rate for that year; negative values indicate a rate below that year's average.
Page updated
Google Sites
Report abuse