d_center <- d2 %>%
# Select relevant variables
select(
FILE = type_of_center, BHCMISID, GrantNumber, HealthCenterName, HealthCenterStreetAddress, HealthCenterState, HealthCenterZIPCode, year, starts_with("T4_L8")
) %>%
# Select year 2021 and filter out US territories
filter(
year %in% 2021 &
!(HealthCenterState %in% c("AS", "GU", "MP", "PR", "VI", "FM", "MH", "PW"))
) %>%
# If value is missing, assume it is 0
mutate(across(9:14,
~ifelse(is.na(.), 0, .))) %>%
# Create measures
mutate(
SUBTOT_MCAID = `T4_L8a_Ca` + `T4_L8a_Cb`,
SUBTOT_CHIP = `T4_L8b_Ca` + `T4_L8b_Cb`,
TOTAL_MCAIDCHIP = `T4_L8_Ca` + `T4_L8_Cb`
)n_sites <- d_site %>%
# Filter for year 2021
filter(year == 2021) %>%
# Count the number of sites linked to each FQHC
group_by(BHCMISID) %>%
summarize(N_SITES = n())
# Merge the number of sites back onto the FQHC level data
d_center <- left_join(d_center, n_sites, by = "BHCMISID")# Using the FQHC level file: For H80s and LALs, count the number of Medicaid enrollees by state
counts_of_enrollees <- d_center %>%
# Group by center type (H80/LAL) and state
group_by(HealthCenterState, FILE) %>%
# Sum up the total number of Medicaid enrollees
summarize(n = sum(TOTAL_MCAIDCHIP)) %>%
# Clean and format data
pivot_wider(names_from = FILE, values_from = n) %>%
# If a state/center type has no enrollees, it will automatically get coded as missing. I recode these values to 0.
mutate(across(everything(), ~replace_na(., 0))) %>%
# Create total number of enrollees across both types of centers
mutate(TOTAL = `UDS H80` + `UDS LAL`)# Using the FQHC level file: For H80s and LALs, count the number of Health Centers by state
count_of_HCs <- d_center %>%
# Group by state and type of center
group_by(HealthCenterState, FILE) %>%
# Count the number of centers
summarize(n = n()) %>%
# Clean and format as before
pivot_wider(names_from = FILE, values_from = n) %>%
mutate(across(everything(), ~replace_na(., 0))) %>%
# Sum up the total number of centers
mutate(TOTAL = `UDS H80` + `UDS LAL`)# Using the FQHC level file: For H80s and LALs, count the number of Health Centers Sites by state
count_of_sites <- d_center %>%
# Group by state and type of center
group_by(HealthCenterState, FILE) %>%
# Count the number of centers
summarize(n = sum(N_SITES)) %>%
# Clean and format as before
pivot_wider(names_from = FILE, values_from = n) %>%
mutate(across(everything(), ~replace_na(., 0))) %>%
# Sum up the total number of centers
mutate(TOTAL = `UDS H80` + `UDS LAL`)# Filter out US territories and select relevant variables
hrsa_public <- hrsa_public2 %>%
filter(
!(`Site State Abbreviation` %in% c("AS", "GU", "MP", "PR", "VI", "FM", "MH", "PW", "XX"))
) %>%
select("GrantNumber" = "Health Center Number", "BHCMISID" = "BHCMIS Organization Identification Number", "NPI" = "FQHC Site NPI Number")
# Create a separate dataframe from Center level file with only relevant variables
health_center <- d_center %>%
select(BHCMISID, HealthCenterState, TOTAL_MCAIDCHIP)
# Merge the HRSA public data onto health_center data by ID
m <- left_join(health_center, hrsa_public, by = "BHCMISID") %>%
# Drop missing NPIs
drop_na(NPI) %>%
# Remove duplicate NPIs
distinct(NPI, .keep_all = T) %>%
# Turn NPI variable into characer and re-order variables
mutate(NPI = as.character(NPI)) %>%
select(BHCMISID, GrantNumber, everything())# Select relevant variables from Site level data and merge onto health center level data
d_site <- d_site %>%
select(BHCMISID, GrantNumber, SiteState, MedicaidNumber, MedicaidPharmNumber)
merge <- left_join(d_site, health_center, by = "BHCMISID")
# Clean MedicaidNumbers
m <- merge %>%
# If a center has multiple MedicaidNumbers (separated by "," or "/"), create new row for each number
separate_rows(MedicaidNumber, sep = ",") %>%
separate_rows(MedicaidNumber, sep = "/") %>%
# Filter out missing MedicaidNumbers
filter(!(MedicaidNumber %in% c(NA, "NA", 0, "TBD", "pending", "not applicable", "n/a", "N/A", "none", "na", "None", "tbd", "*", "unk"))) %>%
# Filter out MedicaidNumbers that are only 0s, Xs, or -s (e.g., "-------")
filter(!str_detect(MedicaidNumber, "^[0]+$")) %>%
filter(!str_detect(MedicaidNumber, "^[x]+$")) %>%
filter(!str_detect(MedicaidNumber, "^[-]+$")) %>%
# Remove "NPI" from MedicaidNumbers
mutate(MedicaidNumber = str_replace(MedicaidNumber, "(?i)NPI", "")) %>%
# Keep unique list of MedicaidNumbers
distinct(MedicaidNumber, .keep_all = T) %>%
# Remove rows that have no numbers
filter(str_detect(MedicaidNumber, "\\d")) %>%
# Remove rows that have 4 or more consecutive characters
filter(!str_detect(MedicaidNumber, "[[:alpha:]]{4,}")) %>%
# Turn MedicaidNumber into character
mutate(MedicaidNumber = as.character(MedicaidNumber)) %>%
# Re-order variables
select(BHCMISID, GrantNumber, HealthCenterState, TOTAL_MCAIDCHIP, MedicaidNumber)# Clean MedicaidPharmNumbers the same way as with MedicaidNumbers
m <- merge %>%
separate_rows(MedicaidPharmNumber, sep = ",") %>%
separate_rows(MedicaidPharmNumber, sep = "/") %>%
filter(!(MedicaidPharmNumber %in% c(NA, "NA", 0, "TBD", "pending", "not applicable", "n/a", "N/A", "none", "na", "None", "tbd", "*", "unk"))) %>%
filter(!str_detect(MedicaidPharmNumber, "^[0]+$")) %>%
filter(!str_detect(MedicaidPharmNumber, "^[x]+$")) %>%
filter(!str_detect(MedicaidPharmNumber, "^[-]+$")) %>%
mutate(MedicaidPharmNumber = str_replace(MedicaidPharmNumber, "(?i)NPI", "")) %>%
distinct(MedicaidPharmNumber, .keep_all = T) %>%
filter(str_detect(MedicaidPharmNumber, "\\d")) %>%
filter(!str_detect(MedicaidPharmNumber, "[[:alpha:]]{4,}")) %>%
mutate(MedicaidPharmNumber = as.character(MedicaidPharmNumber)) %>%
select(BHCMISID, GrantNumber, HealthCenterState, TOTAL_MCAIDCHIP, MedicaidPharmNumber)