Hospital / Emergency Services Data

Loading Hospital Data: Filtering to NY/NJ/CT

import pandas as pd
import requests

hospitals = pd.read_csv(
    "Hospital_General_Information.csv"
)
ct_towns = pd.read_csv("../../reference/ct_crosswalk/ct_town_crosswalk.csv")

hospitals = hospitals[
    hospitals["State"].isin(["NY", "NJ", "CT"])
]

Getting FIPS Code

fips_df = pd.read_csv("../ny_nj_ct_fips.csv")

hospitals.columns = hospitals.columns.str.lower()
fips_df.columns = fips_df.columns.str.lower()

hospitals["county"] = hospitals["county/parish"].str.upper().str.strip()
hospitals["state"] = hospitals["state"].str.upper().str.strip()

fips_df["county"] = fips_df["county"].str.upper().str.strip()
fips_df["state"] = fips_df["state"].str.upper().str.strip()

fips_df["county"] = (
    fips_df["county"]
    .str.upper()
    .str.replace(" COUNTY", "", regex=False)
    .str.strip()
)

fips_df["fips"] = fips_df["fips"].astype(str).str.zfill(5)

hospitals["state"] = hospitals["state"].str.upper().str.strip()
fips_df["state"] = fips_df["state"].str.upper().str.strip()

Merging Hospitals Dataset with FIPS Dataframe -> Creating FIPS Column

hospitals_geo = hospitals.merge(
    fips_df,
    on=["state", "county"],
    how="left"
)

Getting Hospital Count Number By County

hospital_counts = hospitals_geo.groupby("fips").size().reset_index(name="hospital_count")

hospital_counts

	fips	hospital_count
0	09001	8
1	09003	8
2	09005	2
3	09007	3
4	09009	9
...	...	...
80	36113	1
81	36117	1
82	36119	12
83	36121	1
84	36123	1

85 rows × 2 columns

Finding Connecticut County Population and Calculating Hospitals/100k

ct_towns

	town_name	town_fips_2020	county_fips	county_name	town_fips_2022	region_fips	region_name
0	Andover	901301080	9013	Tolland County	911001080	9110	Capitol Planning Region
1	Ansonia	900901220	9009	New Haven County	914001220	9140	Naugatuck Valley Planning Region
2	Ashford	901501430	9015	Windham County	915001430	9150	Northeastern Connecticut Planning Region
3	Avon	900302060	9003	Hartford County	911002060	9110	Capitol Planning Region
4	Barkhamsted	900502760	9005	Litchfield County	916002760	9160	Northwest Hills Planning Region
...	...	...	...	...	...	...	...
164	Windsor Locks	900387070	9003	Hartford County	911087070	9110	Capitol Planning Region
165	Wolcott	900987560	9009	New Haven County	914087560	9140	Naugatuck Valley Planning Region
166	Woodbridge	900987700	9009	New Haven County	917087700	9170	South Central Connecticut Planning Region
167	Woodbury	900587910	9005	Litchfield County	914087910	9140	Naugatuck Valley Planning Region
168	Woodstock	901588190	9015	Windham County	915088190	9150	Northeastern Connecticut Planning Region

169 rows × 7 columns

# Load the population data
pop_df = pd.read_csv('../ct_towns_pop2023.csv')

# Standardize town names for merging (uppercase to match ct_towns)
pop_df['town_name'] = pop_df['town_name'].str.upper()

# Merge with ct_towns to get county info
merged = ct_towns[['town_name', 'county_name']].merge(
    pop_df,
    on='town_name',
    how='left'
)

# Group by county and sum populations
county_pop = merged.groupby('county_name')['pop_2023'].sum().reset_index()
county_pop.columns = ['county_name', 'total_pop_2023']
county_pop = county_pop.sort_values('total_pop_2023', ascending=False)

print(county_pop)

         county_name  total_pop_2023
0   Fairfield County             0.0
1    Hartford County             0.0
2  Litchfield County             0.0
3   Middlesex County             0.0
4   New Haven County             0.0
5  New London County             0.0
6     Tolland County             0.0
7     Windham County             0.0

# Check for any towns that didn't match
unmatched = merged[merged['pop_2023'].isna()]['town_name'].tolist()
print("Unmatched towns:", unmatched)

Unmatched towns: ['Andover', 'Ansonia', 'Ashford', 'Avon', 'Barkhamsted', 'Beacon Falls', 'Berlin', 'Bethany', 'Bethel', 'Bethlehem', 'Bloomfield', 'Bolton', 'Bozrah', 'Branford', 'Bridgeport', 'Bridgewater', 'Bristol', 'Brookfield', 'Brooklyn', 'Burlington', 'Canaan', 'Canterbury', 'Canton', 'Chaplin', 'Cheshire', 'Chester', 'Clinton', 'Colchester', 'Colebrook', 'Columbia', 'Cornwall', 'Coventry', 'Cromwell', 'Danbury', 'Darien', 'Deep River', 'Derby', 'Durham', 'East Granby', 'East Haddam', 'East Hampton', 'East Hartford', 'East Haven', 'East Lyme', 'East Windsor', 'Eastford', 'Easton', 'Ellington', 'Enfield', 'Essex', 'Fairfield', 'Farmington', 'Franklin', 'Glastonbury', 'Goshen', 'Granby', 'Greenwich', 'Griswold', 'Groton', 'Guilford', 'Haddam', 'Hamden', 'Hampton', 'Hartford', 'Hartland', 'Harwinton', 'Hebron', 'Kent', 'Killingly', 'Killingworth', 'Lebanon', 'Ledyard', 'Lisbon', 'Litchfield', 'Lyme', 'Madison', 'Manchester', 'Mansfield', 'Marlborough', 'Meriden', 'Middlebury', 'Middlefield', 'Middletown', 'Milford', 'Monroe', 'Montville', 'Morris', 'Naugatuck', 'New Britain', 'New Canaan', 'New Fairfield', 'New Hartford', 'New Haven', 'New London', 'New Milford', 'Newington', 'Newtown', 'Norfolk', 'North Branford', 'North Canaan', 'North Haven', 'North Stonington', 'Norwalk', 'Norwich', 'Old Lyme', 'Old Saybrook', 'Orange', 'Oxford', 'Plainfield', 'Plainville', 'Plymouth', 'Pomfret', 'Portland', 'Preston', 'Prospect', 'Putnam', 'Redding', 'Ridgefield', 'Rocky Hill', 'Roxbury', 'Salem', 'Salisbury', 'Scotland', 'Seymour', 'Sharon', 'Shelton', 'Sherman', 'Simsbury', 'Somers', 'South Windsor', 'Southbury', 'Southington', 'Sprague', 'Stafford', 'Stamford', 'Sterling', 'Stonington', 'Stratford', 'Suffield', 'Thomaston', 'Thompson', 'Tolland', 'Torrington', 'Trumbull', 'Union', 'Vernon', 'Voluntown', 'Wallingford', 'Warren', 'Washington', 'Waterbury', 'Waterford', 'Watertown', 'West Hartford', 'West Haven', 'Westbrook', 'Weston', 'Westport', 'Wethersfield', 'Willington', 'Wilton', 'Winchester', 'Windham', 'Windsor', 'Windsor Locks', 'Wolcott', 'Woodbridge', 'Woodbury', 'Woodstock']

# Normalize both to uppercase for matching
ct_towns_copy = ct_towns.copy()
ct_towns_copy['town_name_upper'] = ct_towns_copy['town_name'].str.upper()
pop_df['town_name_upper'] = pop_df['town_name'].str.upper()

# Merge on the normalized column
merged = ct_towns_copy[['town_name', 'town_name_upper', 'county_name']].merge(
    pop_df[['town_name_upper', 'pop_2023']],
    on='town_name_upper',
    how='left'
)

# Check unmatched
unmatched = merged[merged['pop_2023'].isna()]['town_name'].tolist()
print("Unmatched towns:", unmatched)

# Group by county
county_pop = merged.groupby('county_name')['pop_2023'].sum().reset_index()
county_pop.columns = ['county_name', 'total_pop_2023']
county_pop = county_pop.sort_values('total_pop_2023', ascending=False)
print(county_pop)

Unmatched towns: []
         county_name  total_pop_2023
0   Fairfield County          963780
1    Hartford County          898478
4   New Haven County          865717
5  New London County          268518
2  Litchfield County          186551
3   Middlesex County          166110
6     Tolland County          150906
7     Windham County          117116

# Get unique county_fips + county_name from ct_towns
county_fips_map = ct_towns[['county_fips', 'county_name']].drop_duplicates()

# Merge fips into county population df
ct_county_pop = county_pop.merge(
    county_fips_map,
    on='county_name',
    how='left'
)
ct_county_pop.set_index('county_fips', inplace = True)
print(ct_county_pop)

                   county_name  total_pop_2023
county_fips                                   
9001          Fairfield County          963780
9003           Hartford County          898478
9009          New Haven County          865717
9011         New London County          268518
9005         Litchfield County          186551
9007          Middlesex County          166110
9013            Tolland County          150906
9015            Windham County          117116

hospitals_per_100k_all = pd.read_csv('hospitals_per_100k_nj_ny.csv')

hospitals_per_100k_all = hospitals_per_100k_all.merge(
    ct_county_pop[['county_name', 'total_pop_2023']],
    left_on='fips',
    right_index=True,
    how='left'
)

hospitals_per_100k_all['county'] = hospitals_per_100k_all['county'].fillna(hospitals_per_100k_all['county_name'])
hospitals_per_100k_all['population'] = hospitals_per_100k_all['population'].fillna(hospitals_per_100k_all['total_pop_2023'])
hospitals_per_100k_all['hospitals_per_100k'] = hospitals_per_100k_all['hospital_count'] / hospitals_per_100k_all['population'] * 100000

hospitals_per_100k_all.drop(columns=['county_name', 'total_pop_2023'], inplace=True)
print(hospitals_per_100k_all[hospitals_per_100k_all['fips'] < 10000])

    fips      county state  hospital_count  population  hospitals_per_100k
83  9001   FAIRFIELD    CT             8.0    963780.0            0.830065
84  9003    HARTFORD    CT             8.0    898478.0            0.890395
85  9005  LITCHFIELD    CT             2.0    186551.0            1.072093
86  9007   MIDDLESEX    CT             3.0    166110.0            1.806032
87  9009   NEW HAVEN    CT             9.0    865717.0            1.039601
88  9011  NEW LONDON    CT             2.0    268518.0            0.744829
89  9013     TOLLAND    CT             2.0    150906.0            1.325328
90  9015     WINDHAM    CT             2.0    117116.0            1.707709

# Fill NaN state with Connecticut
hospitals_per_100k_all['state'] = hospitals_per_100k_all['state'].fillna('Connecticut')

# Remove ' County' from CT rows only
hospitals_per_100k_all['county'] = hospitals_per_100k_all['county'].str.replace(' County', '', regex=False)

print(hospitals_per_100k_all.tail())

    fips      county state  hospital_count  population  hospitals_per_100k
86  9007   MIDDLESEX    CT             3.0    166110.0            1.806032
87  9009   NEW HAVEN    CT             9.0    865717.0            1.039601
88  9011  NEW LONDON    CT             2.0    268518.0            0.744829
89  9013     TOLLAND    CT             2.0    150906.0            1.325328
90  9015     WINDHAM    CT             2.0    117116.0            1.707709

hospitals_per_100k_all.to_csv('hospitals_per_100k_all.csv', index=False)

hospitals_per_100k_all

	fips	county	state	hospital_count	population	hospitals_per_100k
0	36001	ALBANY	NY	4.0	317590.0	1.259486
1	36003	ALLEGANY	NY	2.0	46694.0	4.283206
2	36005	BRONX	NY	7.0	1380478.0	0.507071
3	36007	BROOME	NY	3.0	195934.0	1.531128
4	36009	CATTARAUGUS	NY	1.0	75671.0	1.321510
...	...	...	...	...	...	...
86	9007	MIDDLESEX	CT	3.0	166110.0	1.806032
87	9009	NEW HAVEN	CT	9.0	865717.0	1.039601
88	9011	NEW LONDON	CT	2.0	268518.0	0.744829
89	9013	TOLLAND	CT	2.0	150906.0	1.325328
90	9015	WINDHAM	CT	2.0	117116.0	1.707709

91 rows × 6 columns

PCP per 100k

#Get Primary Care Physican data from County Health Rankings
df_raw = pd.read_excel('County_Health_Rankings_NY.xlsx', 
                        sheet_name='Ranked Measure Data', header=None)

cols = [0, 1, 2, 114]
pcp_df = df_raw.iloc[2:, cols].copy()

pcp_df.columns = ['FIPS', 'State', 'County', 
                   '# Primary Care Physicians']

pcp_df = pcp_df.iloc[1:]
pcp_df = pcp_df.reset_index(drop=True)

---------------------------------------------------------------------------
FileNotFoundError                         Traceback (most recent call last)
Cell In[29], line 2
      1 #Get Primary Care Physican data from County Health Rankings
----> 2 df_raw = pd.read_excel('County_Health_Rankings_NY.xlsx', 
      3                         sheet_name='Ranked Measure Data', header=None)
      5 cols = [0, 1, 2, 114]
      6 pcp_df = df_raw.iloc[2:, cols].copy()

File ~\anaconda3\Lib\site-packages\pandas\io\excel\_base.py:495, in read_excel(io, sheet_name, header, names, index_col, usecols, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, date_format, thousands, decimal, comment, skipfooter, storage_options, dtype_backend, engine_kwargs)
    493 if not isinstance(io, ExcelFile):
    494     should_close = True
--> 495     io = ExcelFile(
    496         io,
    497         storage_options=storage_options,
    498         engine=engine,
    499         engine_kwargs=engine_kwargs,
    500     )
    501 elif engine and engine != io.engine:
    502     raise ValueError(
    503         "Engine should not be specified when passing "
    504         "an ExcelFile - ExcelFile already has the engine set"
    505     )

File ~\anaconda3\Lib\site-packages\pandas\io\excel\_base.py:1550, in ExcelFile.__init__(self, path_or_buffer, engine, storage_options, engine_kwargs)
   1548     ext = "xls"
   1549 else:
-> 1550     ext = inspect_excel_format(
   1551         content_or_path=path_or_buffer, storage_options=storage_options
   1552     )
   1553     if ext is None:
   1554         raise ValueError(
   1555             "Excel file format cannot be determined, you must specify "
   1556             "an engine manually."
   1557         )

File ~\anaconda3\Lib\site-packages\pandas\io\excel\_base.py:1402, in inspect_excel_format(content_or_path, storage_options)
   1399 if isinstance(content_or_path, bytes):
   1400     content_or_path = BytesIO(content_or_path)
-> 1402 with get_handle(
   1403     content_or_path, "rb", storage_options=storage_options, is_text=False
   1404 ) as handle:
   1405     stream = handle.handle
   1406     stream.seek(0)

File ~\anaconda3\Lib\site-packages\pandas\io\common.py:882, in get_handle(path_or_buf, mode, encoding, compression, memory_map, is_text, errors, storage_options)
    873         handle = open(
    874             handle,
    875             ioargs.mode,
   (...)
    878             newline="",
    879         )
    880     else:
    881         # Binary mode
--> 882         handle = open(handle, ioargs.mode)
    883     handles.append(handle)
    885 # Convert BytesIO or file objects passed with an encoding

FileNotFoundError: [Errno 2] No such file or directory: 'County_Health_Rankings_NY.xlsx'

pcp_df.head()

df_raw = pd.read_excel('County_Health_Rankings_CT.xlsx', 
                        sheet_name='Ranked Measure Data', header=None)

cols = [0, 1, 2, 114]
pcp_df2= df_raw.iloc[2:, cols].copy()

pcp_df2.columns = ['FIPS', 'State', 'County', 
                   '# Primary Care Physicians']

pcp_df2 = pcp_df2.iloc[1:]
pcp_df2 = pcp_df2.reset_index(drop=True)

pcp_df2.head()

df_raw = pd.read_excel('County_Health_Rankings_NJ.xlsx', 
                        sheet_name='Ranked Measure Data', header=None)

cols = [0, 1, 2, 114]
pcp_df3= df_raw.iloc[2:, cols].copy()

pcp_df3.columns = ['FIPS', 'State', 'County', 
                   '# Primary Care Physicians']
pcp_df3 = pcp_df3.iloc[1:]
pcp_df3 = pcp_df3.reset_index(drop=True)

pcp_df3.head()

pcp_all = pd.concat([pcp_df2, pcp_df3, pcp_df], join = 'inner')
pcp_all = pcp_all.rename(columns={'FIPS' :'fips'})
pcp_all.set_index('fips', inplace = True)
pcp_all.head(20)

import requests

url = "https://api.census.gov/data/2023/acs/acs1"

API_KEY = "3d1a8efb010ab94526aed9bb9b1b8ba58c722e3d"

states = ["09", "34", "36"]  # CT, NJ, NY

all_data = []

for s in states:
    params = {
        "get": "NAME,B01003_001E",
        "for": "county:*",
        "in": f"state:{s}",
        "key": API_KEY
    }

    r = requests.get(url, params=params)

    print("\nSTATE:", s)
    print("STATUS:", r.status_code)
    print("CONTENT (first 300 chars):")
    print(r.text[:300])
    print("-" * 50)

pop = pd.read_csv("co-est2025-alldata.csv", encoding = "latin1")

print(pop.columns.tolist())

# Filtering for NY-NJ-CT
pop = pop[
    pop["STATE"].isin([9, 34, 36])
]

pop = pop[
    pop["COUNTY"] > 0
]

pop["fips"] = (
    pop["STATE"].astype(str).str.zfill(2)
    + pop["COUNTY"].astype(str).str.zfill(3)
)

pop = pop[[
    "fips",
    "STNAME",
    "CTYNAME",
    "POPESTIMATE2023"
]]

# Renaming columns
pop = pop.rename(columns={
    "STNAME": "state",
    "CTYNAME": "county",
    "POPESTIMATE2023": "population"
})

# Check data
print(pop.shape)
print(pop.head())

pop = pop[pop['state'] != 'Connecticut']

pop.head()

ct_county_pop.head()

ct_county_pop = ct_county_pop.rename(columns={'county_name': 'county', 'total_pop_2023':'population'})
ct_county_pop.index.name = 'fips'
ct_county_pop.head()

ct_county_pop['state'] = 'Connecticut'
pop['fips'] = pop['fips'].astype(int)
pop.set_index('fips', inplace = True)

pop.head()

pop_new = pd.concat([ct_county_pop, pop])

pop_new.head(20)

pop_new.index = pop_new.index.astype(int)
pcp_all.index =pcp_all.index.astype(int)

merged = pcp_all.merge(pop_new[['population']], left_on='fips', right_index=True, how='left')

merged['pcp_per_100k'] = merged['# Primary Care Physicians'] / merged['population'] * 100000

merged.head(20)

merged[merged['pcp_per_100k'] < 30]

#merged.to_csv('pcp_per_100k_all.csv', index=False)

Stroke Centers Per 100k

Importing and Cleaning NY

from bs4 import BeautifulSoup


headers = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0.0.0 Safari/537.36"
}

url = "https://www.health.ny.gov/diseases/cardiovascular/stroke/designation/stroke_designated_centers.htm"
response = requests.get(url, headers=headers)
response.raise_for_status()

soup = BeautifulSoup(response.text, "lxml")
table = soup.find("table")

headers_row = [th.get_text(strip=True) for th in table.find_all("th")]

rows = []
for tr in table.find_all("tr")[1:]:  # skip header row
    cells = [td.get_text(strip=True) for td in tr.find_all("td")]
    if len(cells) == len(headers_row):  # skip the "no centers in county" placeholder row
        rows.append(cells)

df = pd.DataFrame(rows, columns=headers_row)
print(df.shape)
print(df.head())

#Cleaning table
df = df.iloc[1:]
df = df.drop(columns=['PFI', 'Address', 'EMS Region'])
df.head(20)

#Finding # stroke centers by county 
stroke_centers_by_county = (
    df.groupby("County")["Hospital Name"]
    .count()
    .reset_index()
    .rename(columns={"Hospital Name": "Total Stroke Centers"})
    .sort_values("Total Stroke Centers", ascending=False)
    .reset_index(drop=True)
)

stroke_centers_by_county.head(20)

#Finding # of each type of stroke center in each county
stroke_centers_by_county_detailed = (
    df.groupby(["County", "NYSDOH Level of Designation"])["Hospital Name"]
    .count()
    .unstack(fill_value=0)
    .reset_index()
)
stroke_centers_by_county_detailed["Total Stroke Centers"] = stroke_centers_by_county_detailed.iloc[:, 1:].sum(axis=1)
stroke_centers_by_county_detailed.columns.name = None

stroke_centers_by_county_detailed.head(20)

Importing and cleaning CT

from io import StringIO

url = "https://portal.ct.gov/dph/emergency-medical-services/ems/certified-stroke-centers"
headers = {"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36"}

response = requests.get(url, headers=headers)
response.raise_for_status()

tables = pd.read_html(StringIO(response.text))
print(f"{len(tables)} tables found")
for i, t in enumerate(tables):
    print(f"Table {i}: {t.shape}")

ct_df = max(tables, key=lambda t: t.shape[0])
print(ct_df.shape)
ct_df.head()

ct_df = tables[2].copy()
ct_df.columns = ct_df.iloc[0]        # set first row as header
ct_df = ct_df.drop(0).reset_index(drop=True)  # drop that row

ct_df.head(20)

ct_df = ct_df.drop(columns=['DATE OF CERTIFICATION', 'CERTIFYING ORGANIZATION'])

ct_df.head()

ct_df.iloc[:, 1] = ct_df.iloc[:, 1].str.split("CONTACT:").str[0].str.strip()

ct_df.head()

ct_latlong = pd.read_csv('ct_stroke_centers_geocoded.csv')
ct_latlong.head()

len(ct_df)

!conda install -c conda-forge geopandas -y

import requests

def get_county(lat, lon, vintage=419):
    url = "https://geocoding.geo.census.gov/geocoder/geographies/coordinates"
    params = {
        "x": lon,
        "y": lat,
        "benchmark": "Public_AR_Current",
        "vintage": vintage,
        "format": "json"
    }
    r = requests.get(url, params=params)
    result = r.json()
    try:
        county_info = result["result"]["geographies"]["Counties"][0]
        return county_info["NAME"], county_info["GEOID"]
    except (KeyError, IndexError):
        return None, None

ct_latlong[["County", "County_FIPS"]] = ct_latlong.apply(
    lambda row: pd.Series(get_county(row["latitude"], row["longitude"])),
    axis=1
)

ct_df_with_county.columns

ct_df_with_county.head()