In [1]:
import pandas as pd
Datasets were gathered from CDC PLACES (2025 release), which provides model-based estimates of health behaviors and chronic diseases in 2023.
The target variables: the Crude Prevalence (_CrudePrev) for 8 metrics. This represents the actual, unadjusted percentage of adults in each community living with that specific behavior or condition.
| Target Variables | Description | Variable Codes |
|---|---|---|
| binge_drinking_prevalence | Percentage of adults reporting heavy episodic alcohol consumption | BINGE_CrudePrev |
| smoking_prevalence | Percentage of adults who currently smoke cigarettes | CSMOKING_CrudePrev |
| physical_inactivity | Percentage of adults reporting no physical exercise outside of work | LPA_CrudePrev |
| hypertension_prevalence | Percentage of adults diagnosed with hypertension (the leading stroke risk factor) | BPHIGH_CrudePre |
| high_cholesterol_prevalenc | Percentage of adults diagnosed with high cholesterol | HIGHCHOL_CrudePrev |
| diabetes_prevalence | Percentage of adults diagnosed with diabetes_prevalence | diabetes_prevalence_CrudePrev |
| obesity_prevalence | Percentage of adults with a Body Mass Index (BMI) of 30.0 or higher | obesity_prevalence_CrudePrev |
| stroke_prevalence | Percentage of adults who have already survived a prior stroke (indicating high vulnerability to recurrence) | STROKE_CrudePrev |
Because of geographic differences between states, data collection is split into two separate methods before being merged.
| State | Dataset Level Used | Dataset Link | Handling & Processing |
|---|---|---|---|
| NY & NJ | County data | PLACES: County Data (GIS Friendly Format), 2025 release | Downloaded directly at the county level. No pre-processing or geographic translation is needed. |
| CT | Census tract data | PLACES: Census Tract Data (GIS Friendly Format), 2025 release | Downloaded at the neighborhood (tract) level. A 2022 Tract Crosswalk is applied in Python to map these modern tracts back to Connecticut’s 8 traditional counties before running a population-weighted average. |
Data for NY - NJ¶
In [2]:
# Import data
df1 = pd.read_csv('PLACES__County_Data_(GIS_Friendly_Format),_2025_release_20260622.csv', dtype={'CountyFIPS': str})
df1.head()
Out[2]:
| StateAbbr | CountyName | CountyFIPS | BINGE_CrudePrev | CSMOKING_CrudePrev | LPA_CrudePrev | BPHIGH_CrudePrev | HIGHCHOL_CrudePrev | DIABETES_CrudePrev | OBESITY_CrudePrev | STROKE_CrudePrev | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | NJ | Somerset | 34035 | 13.3 | 8.2 | 20.8 | 30.6 | 39.3 | 10.1 | 26.3 | 2.6 |
| 1 | NJ | Monmouth | 34025 | 16.0 | 9.7 | 19.5 | 32.9 | 39.7 | 9.8 | 30.4 | 2.9 |
| 2 | NJ | Essex | 34013 | 14.8 | 12.4 | 28.9 | 31.7 | 38.4 | 12.0 | 29.6 | 3.6 |
| 3 | NJ | Salem | 34033 | 16.0 | 14.7 | 27.9 | 37.4 | 39.6 | 11.9 | 34.0 | 3.8 |
| 4 | NJ | Burlington | 34005 | 15.9 | 10.9 | 23.6 | 34.0 | 40.0 | 10.5 | 30.2 | 3.1 |
In [3]:
df1.info()
<class 'pandas.DataFrame'> RangeIndex: 83 entries, 0 to 82 Data columns (total 11 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 StateAbbr 83 non-null str 1 CountyName 83 non-null str 2 CountyFIPS 83 non-null str 3 BINGE_CrudePrev 83 non-null float64 4 CSMOKING_CrudePrev 83 non-null float64 5 LPA_CrudePrev 83 non-null float64 6 BPHIGH_CrudePrev 83 non-null float64 7 HIGHCHOL_CrudePrev 83 non-null float64 8 DIABETES_CrudePrev 83 non-null float64 9 OBESITY_CrudePrev 83 non-null float64 10 STROKE_CrudePrev 83 non-null float64 dtypes: float64(8), str(3) memory usage: 7.3 KB
In [4]:
# Rearrange columns
df1.drop(columns=['StateAbbr','CountyName'], inplace=True)
df1.rename(columns={'CountyFIPS':'fips', 'BINGE_CrudePrev':'binge_drinking_prevalence', 'CSMOKING_CrudePrev':'smoking_prevalence',
'LPA_CrudePrev':'physical_inactivity', 'BPHIGH_CrudePrev':'hypertension_prevalence', 'HIGHCHOL_CrudePrev':'high_cholesterol_prevalence',
'DIABETES_CrudePrev':'diabetes_prevalence', 'OBESITY_CrudePrev':'obesity_prevalence', 'STROKE_CrudePrev':'stroke_prevalence'}, inplace=True)
df1.head(3)
Out[4]:
| fips | binge_drinking_prevalence | smoking_prevalence | physical_inactivity | hypertension_prevalence | high_cholesterol_prevalence | diabetes_prevalence | obesity_prevalence | stroke_prevalence | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 34035 | 13.3 | 8.2 | 20.8 | 30.6 | 39.3 | 10.1 | 26.3 | 2.6 |
| 1 | 34025 | 16.0 | 9.7 | 19.5 | 32.9 | 39.7 | 9.8 | 30.4 | 2.9 |
| 2 | 34013 | 14.8 | 12.4 | 28.9 | 31.7 | 38.4 | 12.0 | 29.6 | 3.6 |
In [5]:
df=pd.read_csv('../ny_nj_ct_fips.csv', dtype={'fips':str})
df.info()
<class 'pandas.DataFrame'> RangeIndex: 91 entries, 0 to 90 Data columns (total 3 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 fips 91 non-null str 1 county 91 non-null str 2 state 91 non-null str dtypes: str(3) memory usage: 2.3 KB
In [6]:
ny_nj = df[df['state'].isin(['NY', 'NJ'])]
ny_nj.tail(2)
Out[6]:
| fips | county | state | |
|---|---|---|---|
| 81 | 34039 | Union | NJ |
| 82 | 34041 | Warren | NJ |
In [7]:
merged_nynj = pd.merge(ny_nj, df1, how='left', on=['fips'])
merged_nynj.info()
<class 'pandas.DataFrame'> RangeIndex: 83 entries, 0 to 82 Data columns (total 11 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 fips 83 non-null str 1 county 83 non-null str 2 state 83 non-null str 3 binge_drinking_prevalence 83 non-null float64 4 smoking_prevalence 83 non-null float64 5 physical_inactivity 83 non-null float64 6 hypertension_prevalence 83 non-null float64 7 high_cholesterol_prevalence 83 non-null float64 8 diabetes_prevalence 83 non-null float64 9 obesity_prevalence 83 non-null float64 10 stroke_prevalence 83 non-null float64 dtypes: float64(8), str(3) memory usage: 7.3 KB
Data for CT¶
Aggregating Tract-Level Crude Prevalence Rates to the County Level¶
The crude prevalence columns are each a percentage of that tract adult population, not the county's. So we can't just average the percentages across tracts in a county, because tracts have different population sizes. We need a population-weighted recalculation.
Procedure:
Recover the raw count for each tract: multiplying each tract's prevalence by its adult population. Because CDC PLACES metrics apply strictly to adults, I must use the adult population (
TotalPop18pluscolumn) as the weight baseline, not the total population.$$\text{Estimated Case Count} = \frac{\text{Tract Prevalence} \times \text{Tract Adult Population}}{100}$$
Aggregation: Sum the estimated case counts and the adult populations across all tracts within each respective traditional county.
Recalculate county-level weighted percentages by dividing the aggregated county case count by the county's total adult population.
$$\text{County Prevalence} = \left( \frac{\sum \text{Estimated Case Count}}{\sum \text{County Adult Population}} \right) \times 100$$
Since the process involves multiplying then dividing by 100, to optimize computational efficiency, the steps are streamlined into a direct Weighted Prevalence Population product:
- Tract-Level Metric: $$\text{Weighted Prevalence Population} = \text{Tract Prevalence} \times \text{Tract Adult Population}$$
- County-Level Aggregation: $$\text{County Prevalence} = \frac{\sum \text{Weighted Prevalence Population}}{\sum \text{County Adult Population}}$$
| Derived Column | Description | Calculation |
|---|---|---|
| pop_binge_drinking | Weighted prevalence population for heavy episodic alcohol consumption | BINGE_CrudePrev * TotalPop18plus |
| pop_current_smoking | Weighted prevalence population for current cigarette smoking | CSMOKING_CrudePrev * TotalPop18plus |
| pop_phys_inactivity | Weighted prevalence population for no physical exercise outside of work | LPA_CrudePrev * TotalPop18plus |
| pop_high_bp | Weighted prevalence population for hypertension | BPHIGH_CrudePrev * TotalPop18plus |
| pop_high_chol | Weighted prevalence population for high cholesterol | HIGHCHOL_CrudePrev * TotalPop18plus |
| pop_diabetes | Weighted prevalence population for diabetes | DIABETES_CrudePrev * TotalPop18plus |
| pop_obesity | Weighted prevalence population for a BMI of 30.0 or higher | OBESITY_CrudePrev * TotalPop18plus |
| pop_stroke_hist | Weighted prevalence population for history of prior stroke | STROKE_CrudePrev * TotalPop18plus |
| County-Level Features | Description | Calculation |
|---|---|---|
| binge_drinking_prevalence | Percentage of adults reporting heavy episodic alcohol consumption | sum(pop_binge_drinking) / sum(TotalPop18plus) |
| smoking_prevalence | Percentage of adults who currently smoke cigarettes | sum(pop_current_smoking) / sum(TotalPop18plus) |
| physical_inactivity | Percentage of adults reporting no physical exercise outside of work | sum(pop_phys_inactivity) / sum(TotalPop18plus) |
| hypertension_prevalence | Percentage of adults diagnosed with hypertension (the leading stroke risk factor) | sum(pop_high_bp) / sum(TotalPop18plus) |
| high_cholesterol_prevalence | Percentage of adults diagnosed with high cholesterol | sum(pop_high_chol) / sum(TotalPop18plus) |
| diabetes_prevalence | Percentage of adults diagnosed with diabetes | sum(pop_diabetes) / sum(TotalPop18plus) |
| obesity_prevalence | Percentage of adults with a Body Mass Index (BMI) of 30.0 or higher | sum(pop_obesity) / sum(TotalPop18plus) |
| stroke_prevalence | Percentage of adults who have already survived a prior stroke | sum(pop_stroke_hist) / sum(TotalPop18plus) |
In [8]:
# Import data
df2 = pd.read_csv('PLACES__Census_Tract_Data_(GIS_Friendly_Format),_2025_release_20260622.csv', dtype={'TractFIPS': str})
df2.head()
Out[8]:
| StateAbbr | TractFIPS | TotalPop18plus | BINGE_CrudePrev | CSMOKING_CrudePrev | LPA_CrudePrev | BPHIGH_CrudePrev | HIGHCHOL_CrudePrev | DIABETES_CrudePrev | OBESITY_CrudePrev | STROKE_CrudePrev | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | CT | 09110400101 | 2,483 | 14.3 | 9.9 | 23.9 | 36.4 | 44.0 | 11.4 | 28.7 | 3.7 |
| 1 | CT | 09110400102 | 3,500 | 16.5 | 10.5 | 21.7 | 32.8 | 41.3 | 10.4 | 30.1 | 3.0 |
| 2 | CT | 09110400200 | 4,839 | 16.6 | 9.6 | 20.4 | 32.1 | 41.8 | 9.7 | 28.1 | 2.9 |
| 3 | CT | 09110400300 | 5,645 | 16.3 | 9.8 | 21.2 | 32.8 | 41.7 | 9.9 | 28.6 | 3.1 |
| 4 | CT | 09110415300 | 1,735 | 15.3 | 18.4 | 40.9 | 32.2 | 35.4 | 14.0 | 41.4 | 3.5 |
In [9]:
df2.info()
<class 'pandas.DataFrame'> RangeIndex: 876 entries, 0 to 875 Data columns (total 11 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 StateAbbr 876 non-null str 1 TractFIPS 876 non-null str 2 TotalPop18plus 876 non-null str 3 BINGE_CrudePrev 876 non-null float64 4 CSMOKING_CrudePrev 876 non-null float64 5 LPA_CrudePrev 876 non-null float64 6 BPHIGH_CrudePrev 876 non-null float64 7 HIGHCHOL_CrudePrev 876 non-null float64 8 DIABETES_CrudePrev 876 non-null float64 9 OBESITY_CrudePrev 876 non-null float64 10 STROKE_CrudePrev 876 non-null float64 dtypes: float64(8), str(3) memory usage: 75.4 KB
In [10]:
# Import the tract-county crosswalk file
crosswalk = pd.read_csv('../../reference/ct_crosswalk/2022tractcrosswalk.csv', dtype={'Tract_fips_2022':str, 'county_fips_2020': str})
crosswalk.head(2)
Out[10]:
| tract_fips_2020 | Tract_fips_2022 | tract_name | town_name | town_fips_2020 | town_fips_2022 | county_name | county_fips_2020 | ce_name_2022 | ce_fips_2022 | school_district_code | school_district_name | zip5_zcta2020 | PUMA2020code | PUMA2020name | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 9013528100 | 09110528100 | 5281.0 | Andover | 901301080 | 911001080 | Tolland | 09013 | Capitol Planning Region | 9110 | 208 | Region 8 | 6232 | 20203 | Capitol East |
| 1 | 9009125200 | 09140125200 | 1252.0 | Ansonia | 900901220 | 914001220 | New Haven | 09009 | Naugatuck Valley Planning Region | 9140 | 2 | Ansonia | 6401 | 20703 | Naugatuck Valley South |
In [11]:
# Keep only important columns in the crosswalk
crosswalk = crosswalk[['Tract_fips_2022', 'county_fips_2020', 'county_name']]
crosswalk.info()
<class 'pandas.DataFrame'> RangeIndex: 879 entries, 0 to 878 Data columns (total 3 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 Tract_fips_2022 879 non-null str 1 county_fips_2020 879 non-null str 2 county_name 879 non-null str dtypes: str(3) memory usage: 20.7 KB
In [12]:
ct_df = pd.merge(crosswalk, df2, left_on='Tract_fips_2022', right_on='TractFIPS', how='left')
ct_df.info()
<class 'pandas.DataFrame'> RangeIndex: 879 entries, 0 to 878 Data columns (total 14 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 Tract_fips_2022 879 non-null str 1 county_fips_2020 879 non-null str 2 county_name 879 non-null str 3 StateAbbr 876 non-null str 4 TractFIPS 876 non-null str 5 TotalPop18plus 876 non-null str 6 BINGE_CrudePrev 876 non-null float64 7 CSMOKING_CrudePrev 876 non-null float64 8 LPA_CrudePrev 876 non-null float64 9 BPHIGH_CrudePrev 876 non-null float64 10 HIGHCHOL_CrudePrev 876 non-null float64 11 DIABETES_CrudePrev 876 non-null float64 12 OBESITY_CrudePrev 876 non-null float64 13 STROKE_CrudePrev 876 non-null float64 dtypes: float64(8), str(6) memory usage: 96.3 KB
In [13]:
# Check where data is missing
ct_df[ct_df['TractFIPS'].isna()]
Out[13]:
| Tract_fips_2022 | county_fips_2020 | county_name | StateAbbr | TractFIPS | TotalPop18plus | BINGE_CrudePrev | CSMOKING_CrudePrev | LPA_CrudePrev | BPHIGH_CrudePrev | HIGHCHOL_CrudePrev | DIABETES_CrudePrev | OBESITY_CrudePrev | STROKE_CrudePrev | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 151 | 09110980001 | 09003 | Hartford | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 709 | 09110980003 | 09003 | Hartford | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 866 | 09110980002 | 09003 | Hartford | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
The Census Bureau creates these unique 9800-series boundaries specifically to isolate areas with zero permanent residential populations. Since these tracks have a residential adult population of exactly zero, they would carry a population weight of 0. We can just remove them from aggregation as they contribute nothing to the county-level averages.
In [14]:
# Remove tracts with no data
ct_df = ct_df[~ct_df['TractFIPS'].isna()]
ct_df.info()
<class 'pandas.DataFrame'> Index: 876 entries, 0 to 878 Data columns (total 14 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 Tract_fips_2022 876 non-null str 1 county_fips_2020 876 non-null str 2 county_name 876 non-null str 3 StateAbbr 876 non-null str 4 TractFIPS 876 non-null str 5 TotalPop18plus 876 non-null str 6 BINGE_CrudePrev 876 non-null float64 7 CSMOKING_CrudePrev 876 non-null float64 8 LPA_CrudePrev 876 non-null float64 9 BPHIGH_CrudePrev 876 non-null float64 10 HIGHCHOL_CrudePrev 876 non-null float64 11 DIABETES_CrudePrev 876 non-null float64 12 OBESITY_CrudePrev 876 non-null float64 13 STROKE_CrudePrev 876 non-null float64 dtypes: float64(8), str(6) memory usage: 102.7 KB
In [15]:
# Remove unimportant columns
ct_df.drop(columns=['Tract_fips_2022','StateAbbr'], inplace=True)
In [16]:
# Change datatype of TotalPop18plus before calculating any column
ct_df['TotalPop18plus'] = ct_df['TotalPop18plus'].str.replace(',', '', regex=False).astype('int64')
ct_df['TotalPop18plus'].dtype
Out[16]:
dtype('int64')
In [17]:
# Get weighted prevalence population
ct_df['pop_binge_drinking'] = ct_df['BINGE_CrudePrev'] * ct_df['TotalPop18plus']
ct_df['pop_current_smoking'] = ct_df['CSMOKING_CrudePrev'] * ct_df['TotalPop18plus']
ct_df['pop_phys_inactivity'] = ct_df['LPA_CrudePrev'] * ct_df['TotalPop18plus']
ct_df['pop_high_bp'] = ct_df['BPHIGH_CrudePrev'] * ct_df['TotalPop18plus']
ct_df['pop_high_chol'] = ct_df['HIGHCHOL_CrudePrev'] * ct_df['TotalPop18plus']
ct_df['pop_diabetes'] = ct_df['DIABETES_CrudePrev'] * ct_df['TotalPop18plus']
ct_df['pop_obesity'] = ct_df['OBESITY_CrudePrev'] * ct_df['TotalPop18plus']
ct_df['pop_stroke_hist'] = ct_df['STROKE_CrudePrev'] * ct_df['TotalPop18plus']
In [18]:
ct_df.sort_values(['county_name', 'TractFIPS'])
Out[18]:
| county_fips_2020 | county_name | TractFIPS | TotalPop18plus | BINGE_CrudePrev | CSMOKING_CrudePrev | LPA_CrudePrev | BPHIGH_CrudePrev | HIGHCHOL_CrudePrev | DIABETES_CrudePrev | OBESITY_CrudePrev | STROKE_CrudePrev | pop_binge_drinking | pop_current_smoking | pop_phys_inactivity | pop_high_bp | pop_high_chol | pop_diabetes | pop_obesity | pop_stroke_hist | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 220 | 09001 | Fairfield | 09120060100 | 3897 | 19.5 | 5.4 | 16.3 | 19.9 | 29.9 | 5.1 | 18.2 | 1.9 | 75991.5 | 21043.8 | 63521.1 | 77550.3 | 116520.3 | 19874.7 | 70925.4 | 7404.3 |
| 219 | 09001 | Fairfield | 09120060200 | 3271 | 16.2 | 7.8 | 19.6 | 29.9 | 40.4 | 8.2 | 23.3 | 2.9 | 52990.2 | 25513.8 | 64111.6 | 97802.9 | 132148.4 | 26822.2 | 76214.3 | 9485.9 |
| 221 | 09001 | Fairfield | 09120060300 | 3022 | 16.8 | 5.3 | 15.2 | 27.5 | 39.8 | 7.0 | 21.1 | 2.4 | 50769.6 | 16016.6 | 45934.4 | 83105.0 | 120275.6 | 21154.0 | 63764.2 | 7252.8 |
| 218 | 09001 | Fairfield | 09120060400 | 3315 | 17.2 | 5.3 | 14.7 | 27.2 | 39.6 | 6.8 | 21.3 | 2.3 | 57018.0 | 17569.5 | 48730.5 | 90168.0 | 131274.0 | 22542.0 | 70609.5 | 7624.5 |
| 209 | 09001 | Fairfield | 09120060500 | 2207 | 17.6 | 5.2 | 14.3 | 26.0 | 38.6 | 6.5 | 20.8 | 2.2 | 38843.2 | 11476.4 | 31560.1 | 57382.0 | 85190.2 | 14345.5 | 45905.6 | 4855.4 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 853 | 09015 | Windham | 09180800400 | 3147 | 15.1 | 14.4 | 35.2 | 34.9 | 36.8 | 12.7 | 36.9 | 3.7 | 47519.7 | 45316.8 | 110774.4 | 109830.3 | 115809.6 | 39966.9 | 116124.3 | 11643.9 |
| 856 | 09015 | Windham | 09180800501 | 2952 | 13.1 | 15.3 | 38.3 | 40.2 | 39.3 | 14.9 | 38.6 | 4.8 | 38671.2 | 45165.6 | 113061.6 | 118670.4 | 116013.6 | 43984.8 | 113947.2 | 14169.6 |
| 855 | 09015 | Windham | 09180800502 | 2616 | 15.4 | 12.7 | 27.8 | 37.0 | 39.7 | 11.2 | 33.2 | 3.6 | 40286.4 | 33223.2 | 72724.8 | 96792.0 | 103855.2 | 29299.2 | 86851.2 | 9417.6 |
| 854 | 09015 | Windham | 09180800600 | 3117 | 15.6 | 17.9 | 43.3 | 32.3 | 32.6 | 13.7 | 43.1 | 3.3 | 48625.2 | 55794.3 | 134966.1 | 100679.1 | 101614.2 | 42702.9 | 134342.7 | 10286.1 |
| 857 | 09015 | Windham | 09180800700 | 2758 | 15.6 | 10.6 | 29.3 | 30.7 | 34.0 | 10.5 | 32.9 | 2.9 | 43024.8 | 29234.8 | 80809.4 | 84670.6 | 93772.0 | 28959.0 | 90738.2 | 7998.2 |
876 rows × 20 columns
In [19]:
ct_df.info()
<class 'pandas.DataFrame'> Index: 876 entries, 0 to 878 Data columns (total 20 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 county_fips_2020 876 non-null str 1 county_name 876 non-null str 2 TractFIPS 876 non-null str 3 TotalPop18plus 876 non-null int64 4 BINGE_CrudePrev 876 non-null float64 5 CSMOKING_CrudePrev 876 non-null float64 6 LPA_CrudePrev 876 non-null float64 7 BPHIGH_CrudePrev 876 non-null float64 8 HIGHCHOL_CrudePrev 876 non-null float64 9 DIABETES_CrudePrev 876 non-null float64 10 OBESITY_CrudePrev 876 non-null float64 11 STROKE_CrudePrev 876 non-null float64 12 pop_binge_drinking 876 non-null float64 13 pop_current_smoking 876 non-null float64 14 pop_phys_inactivity 876 non-null float64 15 pop_high_bp 876 non-null float64 16 pop_high_chol 876 non-null float64 17 pop_diabetes 876 non-null float64 18 pop_obesity 876 non-null float64 19 pop_stroke_hist 876 non-null float64 dtypes: float64(16), int64(1), str(3) memory usage: 143.7 KB
In [20]:
# Drop _CrudePrev columns
ct_df.drop(columns=ct_df.columns[4:12], inplace=True)
ct_df.head(2)
Out[20]:
| county_fips_2020 | county_name | TractFIPS | TotalPop18plus | pop_binge_drinking | pop_current_smoking | pop_phys_inactivity | pop_high_bp | pop_high_chol | pop_diabetes | pop_obesity | pop_stroke_hist | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 09013 | Tolland | 09110528100 | 2592 | 41731.2 | 21772.8 | 42768.0 | 78537.6 | 102384.0 | 20736.0 | 76464.0 | 6739.2 |
| 1 | 09009 | New Haven | 09140125200 | 4614 | 71055.6 | 59520.6 | 131499.0 | 146725.2 | 171640.8 | 46601.4 | 161951.4 | 14303.4 |
In [21]:
# Aggregate and group columns by county
agg_ct = ct_df.drop(columns=['TractFIPS']).groupby(['county_fips_2020','county_name']).sum().reset_index()
agg_ct.head()
Out[21]:
| county_fips_2020 | county_name | TotalPop18plus | pop_binge_drinking | pop_current_smoking | pop_phys_inactivity | pop_high_bp | pop_high_chol | pop_diabetes | pop_obesity | pop_stroke_hist | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 09001 | Fairfield | 743170 | 11902702.0 | 6857144.5 | 17835876.1 | 21611616.7 | 28136942.1 | 6739437.2 | 19156922.9 | 2225568.6 |
| 1 | 09003 | Hartford | 713425 | 11056449.8 | 8144623.7 | 18558285.6 | 23559013.6 | 28125748.9 | 8153463.6 | 22904504.0 | 2336703.7 |
| 2 | 09005 | Litchfield | 151879 | 2376994.1 | 1631996.7 | 3507772.7 | 4778871.9 | 6037056.2 | 1462234.4 | 4222163.0 | 504853.5 |
| 3 | 09007 | Middlesex | 135983 | 2190325.6 | 1350938.3 | 2860448.6 | 4530262.3 | 5256108.9 | 1281863.4 | 3955726.9 | 428757.9 |
| 4 | 09009 | New Haven | 690994 | 10029446.0 | 7812038.6 | 18761911.5 | 23015763.9 | 26580954.2 | 7348462.9 | 23382072.0 | 2289579.9 |
the pop_ columns have higher values than the TotalPop18plusbecause they are weighted prevalence population (as explained above).
In [22]:
# Calculate the crude prevalence at county level
agg_ct['binge_drinking_prevalence'] = agg_ct['pop_binge_drinking'] / agg_ct['TotalPop18plus']
agg_ct['smoking_prevalence'] = agg_ct['pop_current_smoking'] / agg_ct['TotalPop18plus']
agg_ct['physical_inactivity'] = agg_ct['pop_phys_inactivity'] / agg_ct['TotalPop18plus']
agg_ct['hypertension_prevalence'] = agg_ct['pop_high_bp'] / agg_ct['TotalPop18plus']
agg_ct['high_cholesterol_prevalence'] = agg_ct['pop_high_chol'] / agg_ct['TotalPop18plus']
agg_ct['diabetes_prevalence'] = agg_ct['pop_diabetes'] / agg_ct['TotalPop18plus']
agg_ct['obesity_prevalence'] = agg_ct['pop_obesity'] / agg_ct['TotalPop18plus']
agg_ct['stroke_prevalence'] = agg_ct['pop_stroke_hist'] / agg_ct['TotalPop18plus']
In [23]:
agg_ct.head(3)
Out[23]:
| county_fips_2020 | county_name | TotalPop18plus | pop_binge_drinking | pop_current_smoking | pop_phys_inactivity | pop_high_bp | pop_high_chol | pop_diabetes | pop_obesity | pop_stroke_hist | binge_drinking_prevalence | smoking_prevalence | physical_inactivity | hypertension_prevalence | high_cholesterol_prevalence | diabetes_prevalence | obesity_prevalence | stroke_prevalence | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 09001 | Fairfield | 743170 | 11902702.0 | 6857144.5 | 17835876.1 | 21611616.7 | 28136942.1 | 6739437.2 | 19156922.9 | 2225568.6 | 16.016123 | 9.226886 | 23.999726 | 29.080314 | 37.860708 | 9.068500 | 25.777309 | 2.994697 |
| 1 | 09003 | Hartford | 713425 | 11056449.8 | 8144623.7 | 18558285.6 | 23559013.6 | 28125748.9 | 8153463.6 | 22904504.0 | 2336703.7 | 15.497704 | 11.416230 | 26.012945 | 33.022411 | 39.423554 | 11.428621 | 32.104992 | 3.275332 |
| 2 | 09005 | Litchfield | 151879 | 2376994.1 | 1631996.7 | 3507772.7 | 4778871.9 | 6037056.2 | 1462234.4 | 4222163.0 | 504853.5 | 15.650578 | 10.745374 | 23.095837 | 31.464995 | 39.749117 | 9.627627 | 27.799518 | 3.324051 |
In [24]:
agg_ct.info()
<class 'pandas.DataFrame'> RangeIndex: 8 entries, 0 to 7 Data columns (total 19 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 county_fips_2020 8 non-null str 1 county_name 8 non-null str 2 TotalPop18plus 8 non-null int64 3 pop_binge_drinking 8 non-null float64 4 pop_current_smoking 8 non-null float64 5 pop_phys_inactivity 8 non-null float64 6 pop_high_bp 8 non-null float64 7 pop_high_chol 8 non-null float64 8 pop_diabetes 8 non-null float64 9 pop_obesity 8 non-null float64 10 pop_stroke_hist 8 non-null float64 11 binge_drinking_prevalence 8 non-null float64 12 smoking_prevalence 8 non-null float64 13 physical_inactivity 8 non-null float64 14 hypertension_prevalence 8 non-null float64 15 high_cholesterol_prevalence 8 non-null float64 16 diabetes_prevalence 8 non-null float64 17 obesity_prevalence 8 non-null float64 18 stroke_prevalence 8 non-null float64 dtypes: float64(16), int64(1), str(2) memory usage: 1.3 KB
In [25]:
# Remove population columns
agg_ct.drop(columns=agg_ct.columns[2:11], inplace=True)
agg_ct.info()
<class 'pandas.DataFrame'> RangeIndex: 8 entries, 0 to 7 Data columns (total 10 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 county_fips_2020 8 non-null str 1 county_name 8 non-null str 2 binge_drinking_prevalence 8 non-null float64 3 smoking_prevalence 8 non-null float64 4 physical_inactivity 8 non-null float64 5 hypertension_prevalence 8 non-null float64 6 high_cholesterol_prevalence 8 non-null float64 7 diabetes_prevalence 8 non-null float64 8 obesity_prevalence 8 non-null float64 9 stroke_prevalence 8 non-null float64 dtypes: float64(8), str(2) memory usage: 772.0 bytes
In [26]:
# Round float columns to 1 decimal place
float_cols = agg_ct.columns[3:]
agg_ct[float_cols] = agg_ct[float_cols].round(1)
agg_ct.head(2)
Out[26]:
| county_fips_2020 | county_name | binge_drinking_prevalence | smoking_prevalence | physical_inactivity | hypertension_prevalence | high_cholesterol_prevalence | diabetes_prevalence | obesity_prevalence | stroke_prevalence | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 09001 | Fairfield | 16.016123 | 9.2 | 24.0 | 29.1 | 37.9 | 9.1 | 25.8 | 3.0 |
| 1 | 09003 | Hartford | 15.497704 | 11.4 | 26.0 | 33.0 | 39.4 | 11.4 | 32.1 | 3.3 |
Make sure the CT data has the same columns as the datafram for NY-NJ (merged_nynj) before appending.
In [27]:
# Rename columns
agg_ct.rename(columns={'county_fips_2020':'fips', 'county_name':'county'}, inplace=True)
agg_ct.head(2)
Out[27]:
| fips | county | binge_drinking_prevalence | smoking_prevalence | physical_inactivity | hypertension_prevalence | high_cholesterol_prevalence | diabetes_prevalence | obesity_prevalence | stroke_prevalence | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 09001 | Fairfield | 16.016123 | 9.2 | 24.0 | 29.1 | 37.9 | 9.1 | 25.8 | 3.0 |
| 1 | 09003 | Hartford | 15.497704 | 11.4 | 26.0 | 33.0 | 39.4 | 11.4 | 32.1 | 3.3 |
In [28]:
# Insert state column between
agg_ct.insert(2, 'state', 'CT')
agg_ct.info()
<class 'pandas.DataFrame'> RangeIndex: 8 entries, 0 to 7 Data columns (total 11 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 fips 8 non-null str 1 county 8 non-null str 2 state 8 non-null str 3 binge_drinking_prevalence 8 non-null float64 4 smoking_prevalence 8 non-null float64 5 physical_inactivity 8 non-null float64 6 hypertension_prevalence 8 non-null float64 7 high_cholesterol_prevalence 8 non-null float64 8 diabetes_prevalence 8 non-null float64 9 obesity_prevalence 8 non-null float64 10 stroke_prevalence 8 non-null float64 dtypes: float64(8), str(3) memory usage: 836.0 bytes
Combine NY, NJ and CT¶
In [29]:
merged_nynj.info()
<class 'pandas.DataFrame'> RangeIndex: 83 entries, 0 to 82 Data columns (total 11 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 fips 83 non-null str 1 county 83 non-null str 2 state 83 non-null str 3 binge_drinking_prevalence 83 non-null float64 4 smoking_prevalence 83 non-null float64 5 physical_inactivity 83 non-null float64 6 hypertension_prevalence 83 non-null float64 7 high_cholesterol_prevalence 83 non-null float64 8 diabetes_prevalence 83 non-null float64 9 obesity_prevalence 83 non-null float64 10 stroke_prevalence 83 non-null float64 dtypes: float64(8), str(3) memory usage: 7.3 KB
In [30]:
appended_df = pd.concat ([merged_nynj, agg_ct])
appended_df.info()
<class 'pandas.DataFrame'> Index: 91 entries, 0 to 7 Data columns (total 11 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 fips 91 non-null str 1 county 91 non-null str 2 state 91 non-null str 3 binge_drinking_prevalence 91 non-null float64 4 smoking_prevalence 91 non-null float64 5 physical_inactivity 91 non-null float64 6 hypertension_prevalence 91 non-null float64 7 high_cholesterol_prevalence 91 non-null float64 8 diabetes_prevalence 91 non-null float64 9 obesity_prevalence 91 non-null float64 10 stroke_prevalence 91 non-null float64 dtypes: float64(8), str(3) memory usage: 8.5 KB
In [31]:
appended_df.head()
Out[31]:
| fips | county | state | binge_drinking_prevalence | smoking_prevalence | physical_inactivity | hypertension_prevalence | high_cholesterol_prevalence | diabetes_prevalence | obesity_prevalence | stroke_prevalence | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 36001 | Albany | NY | 18.4 | 10.5 | 21.2 | 31.7 | 34.9 | 9.2 | 29.8 | 3.0 |
| 1 | 36003 | Allegany | NY | 17.4 | 13.8 | 25.5 | 35.5 | 37.8 | 11.1 | 34.4 | 3.7 |
| 2 | 36005 | Bronx | NY | 14.1 | 13.2 | 38.1 | 36.5 | 37.4 | 16.1 | 33.5 | 4.4 |
| 3 | 36007 | Broome | NY | 15.9 | 13.2 | 24.4 | 35.0 | 34.4 | 10.9 | 33.1 | 3.7 |
| 4 | 36009 | Cattaraugus | NY | 16.7 | 16.5 | 28.4 | 38.1 | 40.0 | 12.1 | 35.7 | 4.1 |
In [32]:
appended_df.tail()
Out[32]:
| fips | county | state | binge_drinking_prevalence | smoking_prevalence | physical_inactivity | hypertension_prevalence | high_cholesterol_prevalence | diabetes_prevalence | obesity_prevalence | stroke_prevalence | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 3 | 09007 | Middlesex | CT | 16.107349 | 9.9 | 21.0 | 33.3 | 38.7 | 9.4 | 29.1 | 3.2 |
| 4 | 09009 | New Haven | CT | 14.514520 | 11.3 | 27.2 | 33.3 | 38.5 | 10.6 | 33.8 | 3.3 |
| 5 | 09011 | New London | CT | 15.270420 | 10.4 | 25.2 | 34.7 | 36.3 | 9.7 | 31.9 | 3.2 |
| 6 | 09013 | Tolland | CT | 15.588377 | 9.7 | 19.8 | 29.3 | 36.5 | 8.2 | 30.4 | 2.8 |
| 7 | 09015 | Windham | CT | 16.177997 | 13.5 | 28.3 | 34.7 | 37.1 | 10.7 | 34.0 | 3.4 |
In [33]:
appended_df.to_csv('../cdcplaces_data.csv', index=False)
In [ ]: