<jupyter_start><jupyter_text>Get total & ICU staffed bed counts for every acute hospital facility in USAFollowing methodology from this paper: https://www.ncbi.nlm.nih.gov/pmc/articles/PMC5514420/> Study Design and Data Sources> We performed a repeated-measures time series analysis of US ICU bed supply during the 16-year period between 1996 and 2011. We obtained data on hospital characteristics and intensive care occupancy from the Centers for Medicare and Medicaid Services Hospital Cost Report Information System (HCRIS), a publicly available hospital-level database with detailed information on structural, organizational and cost data for all US hospitals. We excluded skilled nursing facilities, long term acute care hospitals, hospitals located in US territories and stand-alone pediatric hospitals (1, 2). We augmented the HCRIS data with data from the US Census Bureau’s 2010 urban-rural classification file which we used to designate hospitals as urban or rural by ZIP code (5).> Variables> The primary dependent variable was each hospital’s number of ICU beds compared to the previous year. We defined total ICU beds using the summed counts of four HCRIS bed categories that were available throughout the study interval: intensive care beds, surgical intensive care beds, cardiac intensive care beds and burn intensive care beds  Useful References & Links:CMS Healthcare Cost Report Information System (HCRIS):https://www.cms.gov/Research-Statistics-Data-and-Systems/Downloadable-Public-Use-Files/Cost-Reports/Hospital-2010-formHospital Facilities: - http://downloads.cms.gov/files/hcris/hosp10-reports.zipPublic Use File (annual, from 2015): - https://data.cms.gov/api/views/absp-nd3x/files/e0ca9126-8fd6-42ca-82bf-c2fe40bd4c0e?download=true&filename=CostReport_Documentation_2015_Final_Oct2019.xlsx- https://www.cms.gov/files/zip/hospital-cost-report-public-use-file-2015.zip![](https://www.resdac.org/sites/resdac.umn.edu/files/kb-images/Figure%204_2.png)Direct link to 2018 reporting from hospitals: http://downloads.cms.gov/Files/hcris/HOSP10FY2018.zip Specific guidance for what we're doing here:from CMS data research guide: https://www.resdac.org/articles/medicare-cost-report-data-structure![alt text](https://www.resdac.org/sites/resdac.umn.edu/files/kb-images/Figure%205.PNG)> In summary, the number of beds will be located in the numeric file. > To identify the number of beds for every report in numeric file, filter the records where the second column (Worksheet Indicator) is “S300001,” the third column (Line Number) is “01400”, and the fourth column (Column Number) is “00200”. > To identify the number of beds for a specific report submitted by a specific facility, filter the records by the “Record Report Number,” which is reported in Column 1. The Report Record Number for a specific facility can be found in the Report data file. Official Worksheet Definitionsfrom Provider Reimbursement Manual (https://www.cms.gov/Regulations-and-Guidance/Guidance/Manuals/Paper-Based-Manuals-Items/CMS021935):Column 2--Refer to 42 CFR 412.105(b) and 69 FR 49093-49098 (August 11, 2004) to determine the facility bed count. Indicate the number of beds available for use by patients at the end of the cost reporting period.A bed means an adult bed, pediatric bed, portion of inpatient labor/delivery/postpartum (LDP) room (also referred to as birthing room) bed when used for services other than labor and delivery, or newborn ICU bed (excluding newborn bassinets) maintained in a patient care area for lodging patients in acute, long term, or domiciliary areas of the hospital. Beds in post-anesthesia, post- operative recovery rooms, outpatient areas, emergency rooms, ancillary departments (however, see exception for labor and delivery department), nurses' and other staff residences, and other such areas that are regularly maintained and utilized for only a portion of the stay of patients (primarily for special procedures or not for inpatient lodging) are not termed a bed for these purposes. (See CMS Pub. 15-1, chapter 22, §2205.)For cost reporting periods beginning prior to October 1, 2012, beds in distinct ancillary labor and delivery rooms and the proportion of LDP room (birthing room) beds used for labor and delivery services are not a bed for these purposes. (See 68 FR 45420 (August 1, 2003).)For cost reporting periods beginning on or after October 1, 2012, in accordance with 77 FR 53411- 53413 (August 31, 2012), beds in distinct labor and delivery rooms, when occupied by an inpatient receiving IPPS-level acute care hospital services or when unoccupied, are considered to be part of a hospital’s inpatient available bed count in accordance with 42 CFR 412.105(b) and are to be reported on line 32. Furthermore, the proportion of the inpatient LDP room (birthing room) beds used for ancillary labor and delivery services is considered part of the hospital’s available bed count.Column 8--Enter the number of inpatient days for all classes of patients for each component. Include organ acquisition and HMO days in this column. This amount will not equal the sum of columns 5 through 7, when the provider renders services to other than titles V, XVIII, or XIX patients.Line 1--For cost reporting periods beginning before October 1, 2012, exclude from column 2 the portion of LDP room (birthing room) beds used for ancillary labor and delivery services, but include on this line beds used for routine adult and pediatric services (postpartum). In accordance with the instructions in 68 FR 45420 (August 1, 2003), compute this proportion (off the cost report) by multiplying the total number of occupied and unoccupied available beds in the LDP room by the percentage of time these beds were used for ancillary labor and delivery services. An example of how to calculate the “percentage of time” would be for a hospital to determine the number of hours for the cost reporting period during which each LDP room maternity patient received labor and delivery services and divide the sum of those hours for all such patients by the sum of the total hours (for both, ancillary labor and delivery services and for routine postpartum services) that all maternity patients spent in the LDP room during that cost reporting period. Alternatively, a hospital could calculate an average percentage of time maternity patients received ancillary labor and delivery services in an LDP room during a typical month.For cost reporting periods beginning on or after October 1, 2012, include all the available LDP room (birthing room) beds in the available bed count in column 2. (See 77 FR 53411-53413 (August 31, 2012).) The proportion of available LDP room beds related to the ancillary labor and delivery services must not be excluded from column 2 for those cost reporting periods.In columns 5, 6, 7 and 8, enter the number of adult and pediatric hospital days excluding the SNF and NF swing-bed, observation bed, and hospice days. In columns 6 and 7, also exclude HMO days. Do not include in column 6 Medicare Secondary Payer/Lesser of Reasonable Cost (MSP/LCC) days. Include these days only in column 8. However, do not include employee discount days in column 8.Line 7--Enter the sum of lines 1, 5, and 6.Lines 8 through 13--Enter the appropriate statistic applicable to each discipline for all programs.Line 14--Enter the sum of lines 7 through 13 for columns 2 through 8, and for columns 12 through 15, enter the amount from line 1. For columns 9 through 11, enter the total for each from your records. Labor and delivery days (as defined in the instructions for Worksheet S-3, Part I, line 32) must not be included on this line.<jupyter_code># get facility level IDs, names and addresses
!wget http://downloads.cms.gov/files/hcris/hosp10-reports.zip
!unzip hosp10-reports.zip
import pandas as pd
import numpy as np
hosp_df = pd.read_csv('HOSPITAL10_PROVIDER_ID_INFO.CSV')
hosp_df.head()
# provider num should be 6 char so need to zfill
hosp_df['PROVIDER_NUMBER'] = hosp_df['PROVIDER_NUMBER'].apply(lambda x: str(x).zfill(6))
hosp_df[hosp_df['County'] == 'SAN FRANCISCO']
# TODO: tried using FY2019 first but it seemed to be missing a lot of facilities, maybe incomplete because their reporting year didn't end yet? Go back and confirm
# !wget http://downloads.cms.gov/Files/hcris/HOSP10FY2019.zip
# !unzip HOSP10FY2019.zip
# get the HCRIS file for FY2018
!wget http://downloads.cms.gov/Files/hcris/HOSP10FY2018.zip
!unzip HOSP10FY2018.zip
!wget https://www.cms.gov/files/zip/hospital2010-documentation.zip
!unzip hospital2010-documentation.zip

hcris_dict = pd.read_csv('/content/HCRIS_DataDictionary.csv')
hcris_dict.head()
data_dict = {c:t for c,t in zip(hcris_dict['Column Code'],hcris_dict['Title'])}
data_dict
# Report Table file columns
rpt_columns = [
               'RPT_REC_NUM',
               'PRVDR_CTRL_TYPE_CD',
               'PRVDR_NUM',
               'NPI',
               'RPT_STUS_CD',
               'FY_BGN_DT',
               'FY_END_DT',
               'PROC_DT',
               'INITL_RPT_SW',
               'LAST_RPT_SW',
               'TRNSMTL_NUM',
               'FI_NUM',
               'ADR_VNDR_CD',
               'FI_CREAT_DT',
               'UTIL_CD',
               'NPR_DT',
               'SPEC_IND',
               'FI_RCPT_DT'
]
[data_dict[col] for col in rpt_columns]
hosp10_2018_rpt_df = pd.read_csv('hosp10_2018_RPT.CSV', names=[data_dict[col] for col in rpt_columns], dtype={'Provider Number':object})
hosp10_2018_rpt_df.head(25)
# Numerical Table file columns
nmrc_columns = [
             'RPT_REC_NUM',
             'WKSHT_CD',
             'LINE_NUM',
             'CLMN_NUM',
             'ITM_VAL_NUM'
]
[data_dict[col] for col in nmrc_columns]
hosp10_2018_nmrc_df = pd.read_csv('hosp10_2018_NMRC.CSV',  names=[data_dict[col] for col in nmrc_columns], dtype={'Line Number':object, 'Column Number':object})
hosp10_2018_nmrc_df.head()
hosp10_2018_nmrc_df.dtypes
provider_num = '050228'
hosp10_2018_rpt_df[hosp10_2018_rpt_df['Provider Number'] == provider_num]['Report Record Number'].values[0]
# dict of bed categories we need and their worksheet line number formatted for lookup
beds_dict = {
    'Hospital Adult and Peds': '00100',
    'Intensive Care Unit': '00800',
    'Coronary Care Unit': '00900',
    'Burn ICU': '01000',
    'Surgical ICU': '01100',
    'Total': '01400'
}
beds_dict_flip = {v:k for k,v in beds_dict.items()}
bedtype_list = list(beds_dict_flip.keys())
bedtype_list
hosp_df.head()
# was working itertively to catch issues and pull new data, ugly code in next few cells. should be refactored if we're thinking to compile more data from these HCRIS worksheets

beds_list = []
beddays_list = []
ptdays_list = []

for idx, provider_num in hosp_df['PROVIDER_NUMBER'].items():

  try: 
    rpt_num = hosp10_2018_rpt_df[hosp10_2018_rpt_df['Provider Number'] == provider_num]['Report Record Number'].values[0]
    hosp_rpt = hosp10_2018_nmrc_df[(hosp10_2018_nmrc_df['Report Record Number'] == rpt_num)]

    beds = []
    ptdays = []
    beddays = []

    for bedtype in bedtype_list:
      try: bed_count = int(hosp_rpt[(hosp_rpt['Worksheet Identifier'] == 'S300001') 
                        & (hosp_rpt['Line Number'] == bedtype) 
                        & (hosp_rpt['Column Number'] == '00200') 
                        ]['Item Value Number'].values[0])
      except Exception as exc:
        # print(exc)
        bed_count = 0
      
      beds.append(bed_count)

      try: beddays_count = int(hosp_rpt[(hosp_rpt['Worksheet Identifier'] == 'S300001') 
                        & (hosp_rpt['Line Number'] == bedtype) 
                        & (hosp_rpt['Column Number'] == '00300') 
                        ]['Item Value Number'].values[0])
      except Exception as exc:
        # print(exc)
        beddays_count = 0
      
      beddays.append(beddays_count)
    
      try: ptdays_count = int(hosp_rpt[(hosp_rpt['Worksheet Identifier'] == 'S300001') 
                        & (hosp_rpt['Line Number'] == bedtype) 
                        & (hosp_rpt['Column Number'] == '00800') 
                        ]['Item Value Number'].values[0])
      except Exception as exc:
        # print(exc)
        ptdays_count = 0
      
      ptdays.append(ptdays_count)

  except Exception as exc:
    print('rpt_num does not exist')
    beds = []
    ptdays = []
    beddays = []
  
  beds_list.append(beds)
  ptdays_list.append(ptdays)
  beddays_list.append(beddays)
beddays_df = pd.DataFrame(beddays_list, columns=[beds_dict_flip[b]+ ' Bed Days Available' for b in bedtype_list])#.astype('uint8')
beddays_df['PROVIDER_NUMBER'] = hosp_df['PROVIDER_NUMBER'].values
beddays_df.head(25)
ptdays_df = pd.DataFrame(ptdays_list, columns=[beds_dict_flip[b]+ ' Inpt Days' for b in bedtype_list])#.astype('uint8')
ptdays_df['PROVIDER_NUMBER'] = hosp_df['PROVIDER_NUMBER'].values
ptdays_df.head(25)
beds_df = pd.DataFrame(beds_list, columns=[beds_dict_flip[b]+' Beds' for b in bedtype_list])#.astype('uint8')
beds_df['PROVIDER_NUMBER'] = hosp_df['PROVIDER_NUMBER'].values
beds_df.head(25)
hosp_df = pd.merge(hosp_df, beds_df)
hosp_df = pd.merge(hosp_df, beddays_df)
hosp_df = pd.merge(hosp_df, ptdays_df)
icu_cols = [beds_dict_flip[b] for b in bedtype_list][1:-1]
icu_cols
hosp_df.head()
hosp_df[[c+' Beds' for c in icu_cols]].sum(axis=1)
hosp_df['ICU Total Beds'] = hosp_df[[c+' Beds' for c in icu_cols]].sum(axis=1)
hosp_df['ICU Total Bed Days Available'] = hosp_df[[c+' Bed Days Available' for c in icu_cols]].sum(axis=1)
hosp_df['ICU Total Inpt Days'] = hosp_df[[c+' Inpt Days' for c in icu_cols]].sum(axis=1)
hosp_df['ICU Occupancy Rate'] = hosp_df['ICU Total Inpt Days']/hosp_df['ICU Total Bed Days Available']
hosp_df['Total Bed Occupancy Rate'] = hosp_df['Total Inpt Days']/hosp_df['Total Bed Days Available']
hosp_df.head()
# some rows have all NaN for bed counts, suspect because they didn't submit report in 2018 by looking at FYB (fiscal year begin), FYE (fiscal year end), Status
# TODO: need to confirm or clarify why NaN for all bed counts, dropping those would remove ~1k facilities
hosp_df.drop(hosp_df[hosp_df['Total Beds'].isna()].index).reset_index().drop('index', axis=1)<jupyter_output><empty_output><jupyter_text>Geocoding facilities<jupyter_code>!pip install geocoder
!pip install geopandas
import geocoder
from shapely.geometry import Point
from pprint import pprint
import geopandas as gpd
from tqdm import tqdm
import requests
#add tokens here, don't forget to delete if git committing nb
MAPBOX_TOKEN = '' 
GMAP_TOKEN = ''
hosp_df.head()
hosp_df.to_csv('usa_hospital_beds_hcris2018.csv')
hosp_df.head()
# save geocoder results as Point(lon,lat) in dict with df idx as key
geoms = {}
# geocoding first with google maps which gave best results, fall back to mapbox geocode and then search_str without street address
# TODO: figure out why geocoder with gmaps stopped sending requests halfway through, temp switch to directly using gmaps api

for i, row in tqdm(hosp_df.iloc[:].iterrows()):
  search_str = str(', ').join(row[['HOSP10_Name','Street_Addr','City','State','Zip_Code']].astype(str).values).replace('-','').replace('#','').replace('/',' ')
  new_str = str(', ').join(row[['HOSP10_Name','City','State','Zip_Code']].astype(str).values).replace('-','').replace('#','').replace('/',' ').replace(';',',')
  
  try: 
    # g = geocoder.google(search_str, key=GMAP_TOKEN)
    # lon, lat = g.json['lng'], g.json['lat']
    
    res = requests.post(f'https://maps.googleapis.com/maps/api/geocode/json?address={search_str}&components=country:US&key={GMAP_TOKEN}')
    g1 = res.json()['results'][0]['geometry']['location']
    lon, lat = g1['lng'], g1['lat']

  except Exception as exc: 
    print(exc)
    try: 
      print(i, search_str, new_str)
      g = geocoder.mapbox(search_str, key=MAPBOX_TOKEN)
      pprint(g.json)
      lon, lat = g.json['lng'], g.json['lat']

    except Exception as exc:
      print('trying mapbox ',i, new_str)
      g = geocoder.mapbox(new_str, key=TOKEN)
      pprint(g.json)
      lon, lat = g.json['lng'], g.json['lat']  

  geoms[i] = Point(lon,lat)
geoms
hosp_df.shape
len(geoms)
geoms_list = [geoms[i] for i in range(len(geoms))]
hosp_df['Total Beds'].sort_values(ascending=False)
# make gdf out of hosp_df and geocoder results, drop row with the huge # of reported beds, save to geojson
gpd.GeoDataFrame(hosp_df.iloc[:], geometry=geoms_list).drop(index=854).to_file('usa_hospital_beds_hcris2018_cleaned3.geojson', encoding='utf-8', driver='GeoJSON')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09655756549095529, 'fraction_numerical': 0.03127835924738339, 'mean_word_length': 4.232668566001899, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 16, 'https://': 10, 'lorem ipsum': 0, 'www.': 8, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 7, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '294044', 'license': 'MIT', 'n_tokens_mistral': 5525, 'n_tokens_neox': 4962, 'n_words': 1795, 'path': 'notebooks/old/usa_hcris2018_facilitybedcounts_20200313_v1.ipynb', 'repo_name': 'sharma-apoorv/covid19-healthsystemcapacity', 'size': '16529'}