dhomer: DHOME Data

## code to prepare `pharmacies` dataset goes here

# https://www.selecthealthofsc.com/pdf/provider/pharmacy/pharmacy-network.pdf
# South Carolina Pharmacies
# Downloaded 2021-06-09

## Matching Pharmacy Locations to NPIs

library(httr)
library(jsonlite)
library(tibble)
library(dplyr)
library(npi)
library(readr)
library(tidygeocoder)
library(janitor)
library(fs)
library(here)
library(sf)
library(tigris)
options(tigris_use_cache = TRUE)
library(dplyr)
library(data.table)

explorer_fname <- here(path('data-raw'), "raw_sc_pharmacy_network.csv")
pharmacy_network <- readr::read_csv(explorer_fname)

key = GET('https://npiregistry.cms.hhs.gov/api/?version=2.1')

searches = tibble()

# NPI values copied and concatenated from raw_sc_pharmacy_network.csv

npi = c(1003051129,1003152810,1003233925,1003262809,1003287632,1003371386,1003819673,1003821190,1003833260,1003856444,1003876244,1003919598,1003919796,1013004217,1013010552,1013010875,1013010883,1013011410,1013164177,1013264670,1013428176,1013468404,1013922962,1013934272,1013972108,1023058468,1023104890,1023111887,1023111895,1023176658,1033118757,1033122635,1033136205,1033212527,1033267695,1033291513,1033349345,1033633730,1033635933,1033641121,1033739024,1043279789,1043313711,1043313810,1043365703,1043394299,1043394315,1043509045,1043517329,1043579824,1043641020,1043858996,1053322628,1053345785,1053356360,1053393710,1053402891,1053414722,1053475566,1053594812,1053595074,1053782615,1053788638,1063434702,1063434710,1063515740,1063581304,1063651941,1063695823,1063790947,1063876084,1063905552,1063914372,1063915643,1073038691,1073146502,1073530226,1073616413,1073616751,1073789111,1073917043,1083139745,1083171185,1083619464,1083631238,1083632822,1083632889,1083675391,1083711584,1083717763,1083727630,1083801286,1083964829,1093018228,1093064172,1093137564,1093141970,1093720161,1093720187,1093732257,1093755431,1093812596,1093942906,1093988214,1104069707,1104114388,1104275403,1104343623,1104458173,1104831056,1104843366,1104883107,1104902147,1104913300,1104924141,1104929975,1104994938,1114020971,1114020989,1114108735,1114114501,1114152204,1114203973,1114251337,1114306735,1114325800,1114497435,1114545936,1114967551,1124045398,1124069612,1124121611,1124121991,1124122932,1124382478,1124469473,1124672662,1134131352,1134185838,1134222805,1134222904,1134384506,1134577331,1134668395,1134680762,1134696230,1134770753,1144231713,1144254871,1144298282,1144306713,1144323817,1144323916,1144362229,1144572850,1144743170,1144744848,1144840026,1154330926,1154343804,1154343895,1154365468,1154400752,1154424828,1154424836,1154586063,1154588101,1154803831,1154859494,1164449310,1164508941,1164525507,1164525606,1164747929,1164796215,1164987525,1174175038,1174540322,1174541973,1174626857,1174639041,1174779219,1174847586,1174939078,1174985246,1184639254,1184639270,1184649055,1184728982,1194057927,1194069880,1194195925,1194730044,1194730283,1194742353,1194765537,1194828889,1205080363,1205175932,1205226495)

npi2 = c(1225394760,1225484074,1225551211,1225552995,1235232000,1235232703,1235232901,1235240706,1235289562,1235290149,1235386012,1235445222,1235586884,1235743295,1245252998,1245321363,1245333913,1245333921,1245390707,1245755909,1255358404,1255358412,1255390993,1255416616,1255434791,1255434932,1255472627,1255490314,1255634069,1255731097,1255857108,1255860243,1255872107,1265045322,1265417927,1265459416,1265462659,1265535942,1265819205,1265953251,1265957047,1275025942,1275030645,1275150815,1275548349,1275548364,1275596249,1275596876,1275671851,1275686628,1275929218,1275937104,1285027391,1285162305,1285171728,1285181891,1285649350,1285649368,1285649376,1285651448,1285674622,1285731786,1285746602,1285826974,1285912170,1295038438,1295258358,1295289742,1295359818,1295740140,1295752459,1295838050,1295838068,1295840148,1306002167,1306004478,1306149950,1306164413,1306187653,1306369335,1306467766,1306829668,1306863576,1306864111,1306916481,1306940119,1306949177,1306950381,1306953096,1316020589,1316040181,1316121262,1316135999,1316155823,1316188105,1316326887,1316339906,1316374507,1316415755,1316918436,1316987738,1326013517,1326141094,1326250952,1326366493,1336161082,1336215581,1336217132,1336242007,1336242015,1336409846,1336424167,1336504695,1336506427,1336512623,1336745561,1346267598,1346343027,1346343787,1346343886,1346412939,1346571460,1346723004,1346760592,1356368500,1356377402,1356393771,1356444038,1356445159,1356487607,1356490148,1356510820,1356674378,1356726798,1356767461,1356812432,1356906903,1366065468,1366457434,1366457459,1366459323,1366469520,1366540916,1366546160,1366597270,1366640955,1366642209,1366774408,1366830408,1376057570,1376550327,1376558445,1376558452,1376558460,1376560532,1376583716,1376614594,1376646067,1376669184,1376979229,1386066132,1386277846,1386603025,1386659233,1386659985,1386661544,1386747079,1386855377,1386872604,1396166633,1396701447,1396796769,1396842233,1396848156,1396848164)

npi3 = c(1396848883,1396890638,1396962395,1396988291,1407189921,1407204613,1407959174,1407959273,1417184334,1417356320,1417385899,1417952425,1427069095,1427070176,1427146059,1427151109,1427151190,1427572106,1437176682,1437252111,1437252871,1437252970,1437562642,1437565843,1437586294,1437769494,1447277694,1447284161,1447353123,1447354246,1447398375,1447408331,1447756762,1447793161,1457366528,1457366544,1457378614,1457436628,1457450389,1457455255,1457560328,1457625428,1467000588,1467002816,1467467530,1467467548,1467467555,1467469411,1467479626,1467492801,1467551390,1467581819,1467617258,1467818922,1467851501,1467934810,1477065704,1477072874,1477110047,1477562262,1477568327,1477570638,1477593812,1477656163,1477656239,1477663474,1477691962,1477709756,1477787562,1477892644,1477950632,1477957397,1477969053,1487108031,1487142956,1487682878,1487757241,1487781217,1487790812,1487849949,1487854204,1487931341,1487935474,1497159305,1497278501,1497772669,1497809479,1497848105,1497858260,1497859201,1497863062,1508008202,1508109497,1508127739,1508223645,1508303330,1508347931,1508382839,1508816802,1508850157,1508903394,1508938879,1508938879,1508969270,1508994872,1518060284,1518060292,1518114339,1518233535,1518313329,1518417351,1518596873,1518989268,1528007465,1528041373,1528079175,1528085776,1528085784,1528104767,1528161205,1528229390,1528299252,1528319548,1528365046,1528388279,1528451556,1528512357,1538108444,1538174628,1538186788,1538195029,1538252671,1538263330,1538347893,1538449707,1548275142,1548275613,1548275639,1548287709,1548328743,1548364342,1548374242,1548414543,1548769219,1558301994,1558309534,1558366930,1558376640,1558378505,1558388710,1558688226,1558756072,1558884593,1568402428,1568402907,1568477412,1568489722,1568527752,1568544591,1568565257,1568697621,1568745081,1568934677,1578607248,1578607289,1578666335,1578666343,1578798658,1578911772,1578994786,1588002497,1588016687,1588709828,1588761423,1588771927,1588927057,1598774366,1598858987,1598868267,1598868358,1598868366,1609821875,1609898352,1609899988,1609943901,1609979376,1609979384,1609982636,1609989219,1619070398,1619074119,1619371606,1619375839,1619485992,1619490646,1619916558,1619929684,1619994860,1619994878,1629017538)

npi4 = c(1629083712,1629095872,1629123807,1629172424,1629411384,1629434071,1629449624,1629472758,1629526777,1639184708,1639184724,1639196892,1639204738,1639267230,1639273436,1639332976,1639390941,1639562945,1639577166,1639593304,1639737349,1639796022,1649210089,1649227133,1649250333,1649285735,1649297805,1649319039,1649359928,1649373135,1649373408,1649467374,1649518036,1649650375,1649670969,1649797168,1659311090,1659346542,1659351427,1659386506,1659398816,1659465151,1659470433,1659474146,1659474344,1659474419,1659474583,1659475614,1659511061,1659550341,1659735785,1659739118,1659739118,1659808996,1669021770,1669546057,1669575429,1669575437,1669615860,1669742144,1669876587,1669926739,1669997441,1669997458,1679116453,1679648448,1679666044,1679670517,1679676449,1679732069,1679941108,1679950158,1689032898,1689048381,1689691859,1689730913,1689756777,1689777450,1689778490,1699214718,1699298604,1699709329,1699797340,1699878363,1699900720,1700069275,1700309804,1700324100,1700803954,1700803962,1700879681,1700881943,1700923117,1700945854,1700949328,1700983202,1710069596,1710081518,1710086194,1710102553,1710200290,1710204490,1710310792,1710318274,1710327705,1710590278,1710904966,1710909544,1710992805,1720005986,1720006554,1720093818,1720093891,1720111305,1720182520,1720192610,1720390982,1720452345,1720536048,1720571003,1720624927,1730106998,1730129172,1730194820,1730282229,1730282427,1730311432,1730329467,1730331182,1730432964,1730516808,1730554650,1730558024,1730564733,1730604323,1730614587,1730669789,1740207901,1740220185,1740295690,1740370725,1740383504,1740438647,1740598051,1740807288,1750436754,1750444311,1750475067,1750484523,1750527834,1750740460,1750901674,1760585392,1760585459,1760585533,1760586572,1760718761,1760886808,1760933196,1770078602,1770112849,1770509218,1770517401,1770563231,1770588097,1770588154,1770628653,1770686446,1770686545,1770746026,1770940801,1770960247,1770987695,1780127159,1780135764,1780618405,1780750646,1780787457,1780787556,1780788430,1780819839,1780958744,1790210839,1790306363,1790707446,1790708071,1790772598,1790888469,1790888477,1790894061,1801037775,1801087556,1801131719,1801801998,1801813050,1801990601,1811045677,1811060650,1811091614,1811194194)

npi5 = c(1811204696,1811248412,1811476005,1811902901,1811902984,1811914070,1811915614,1811937253,1821003914,1821015082,1821038266,1821109901,1821117375,1821186461,1821191511,1821191792,1821238551,1821307356,1821355611,1821493693,1821513235,1821531062,1821635301,1831104785,1831110899,1831116094,1831139278,1831167873,1831266592,1831289818,1831292374,1831292697,1831423326,1831533199,1831615715,1841237245,1841271525,1841280625,1841374097,1841393600,1841393618,1841437266,1841716784,1841717360,1841801669,1851318026,1851337778,1851463087,1851494629,1851508477,1851716062,1861418360,1861426595,1861545113,1861585234,1861586075,1861595639,1861793911,1861847295,1861918740,1861971749,1871560631,1871696542,1871696641,1871700922,1871759167,1871767566,1871775841,1871785592,1881021582,1881088656,1881117695,1881118511,1881158053,1881616522,1881616530,1881758464,1881797363,1881797553,1881837664,1881858462,1881925501,1891017984,1891227922,1891704672,1891712048,1891712055,1891770574,1891814000,1891822664,1891830899,1891854949,1891879854,1891892394,1891898573,1902136260,1902139538,1902182769,1902325715,1902430267,1902444508,1902802531,1902811078,1902811094,1902846348,1902855984,1902900707,1902903404,1902925118,1912000407,1912000605,1912013889,1912016593,1912131871,1912198623,1912239096,1912304197,1912342221,1912558313,1912912007,1912914938,1912924176,1912947359,1912963745,1922013879,1922025188,1922029644,1922048362,1922049816,1922101468,1922101781,1922183987,1922195122,1922198902,1922267707,1922499912,1922521020,1922566017,1932202702,1932202793,1932273463,1942227111,1942241880,1942270582,1942303433,1942303714,1942314927,1942388970,1942486386,1942527452,1942633086,1952494320,1952632234,1952763898,1952792798,1952824146,1952826695,1952882128,1962486696,1962505636,1962505735,1962574764,1962803775,1962861922,1962975888,1972028512,1972163350,1972525616,1972525624,1972606648,1972690857,1972900462,1972907236,1972991164,1972996908,1972998128,1982085767,1982087318,1982621132,1982707329,1982707659,1982707667,1982710554,1982763603,1982839551,1982933263,1982944195,1982974390,1992008650,1992228902,1992228936,1992293476,1992710081,1992722144,1992741888,1992755011,1992809792,1992965263)

for (i in npi) {
  data <- npi_search(number = i, enumeration_type = NULL, limit = 200)
  searches <- rbind(searches, data)
}

for (i in npi2) {
  data <- npi_search(number = i, enumeration_type = NULL, limit = 200)
  searches <- rbind(searches, data)
}

for (i in npi3) {
  data <- npi_search(number = i, enumeration_type = NULL, limit = 200)
  searches <- rbind(searches, data)
}

for (i in npi4) {
  data <- npi_search(number = i, enumeration_type = NULL, limit = 200)
  searches <- rbind(searches, data)
}

for (i in npi5) {
  data <- npi_search(number = i, enumeration_type = NULL, limit = 200)
  searches <- rbind(searches, data)
}

search_flattened <- npi_flatten(searches)
locations <- npi_flatten(searches, c('basic', 'addresses'))
locations <- subset(locations, addresses_address_purpose == 'LOCATION')

fwrite(locations, file = here(path('data-raw'), "pharmacy_locations.csv"))


## Geocoding

explorer_fname <- here(path('data-raw'), "initial_pharmacies.csv")
pharm <- readr::read_csv(explorer_fname) %>%
  clean_names()

geocodes <- pharm %>% geocode(
  street = 'addresses_address_1', city = 'addresses_city', state = 'addresses_state',
  postalcode = 'addresses_postal_code', method = 'cascade'
)

fwrite(geocodes, file = here(path('data-raw'), "pharmacies_primary_processed.csv"))

explorer_fname <- here(path('data-raw'), "pharmacies_secondary_processed.csv")
pharm <- readr::read_csv(explorer_fname) %>%
  clean_names()

sc_tracts <- tracts(state = 45)

coords <- pharm %>%
  filter(is.na(long) == F & is.na(lat) == F) %>%
  st_as_sf(coords = c('long', 'lat'), crs = st_crs(sc_tracts))

coords

system.time({
  intersected <- st_within(coords, sc_tracts)
})

pharmacies_processed <- coords %>%
  mutate(intersection = as.integer(intersected),
         geoid = if_else(is.na(intersection), "",
                         sc_tracts$GEOID[intersection]))

pharmacies_unchecked <- st_join(pharmacies_processed, sc_tracts, by = c('geoid','GEOID')) %>%
  dplyr::mutate(lat = sf::st_coordinates(.)[,2],
                lon = sf::st_coordinates(.)[,1]) %>%
  select(c('npi','basic_organization_name','hours_of_operation','medicaid_id','addresses_telephone_number',
           'geoid','INTPTLAT','INTPTLON','lat','lon')) %>%
  distinct(npi, .keep_all = TRUE) %>%
  rename(tract_latitude = INTPTLAT, tract_longitude = INTPTLON) %>%
  st_drop_geometry()

pharmacies = data.frame()

for (i in 1:nrow(pharmacies_unchecked) ) {
  if(32.0346 <= pharmacies_unchecked$lat[i] && pharmacies_unchecked$lat[i] <= 35.215402 && -83.35391 <= pharmacies_unchecked$lon[i] && pharmacies_unchecked$lon[i] <= -78.54203) {
    pharmacies <- rbind(pharmacies, pharmacies_unchecked[i,])
  }
}

fwrite(pharmacies, file = here(path('data-raw'), 'sc_pharmacies.csv'))

usethis::use_data(pharmacies, overwrite = TRUE)

## At line 73, pharmacy_locations.csv was exported and concatenated with the downloaded data in raw_sc_pharmacy_network.csv ##
## At line 87, the .csv file was exported for accuracy check of latitudes and longitudes and manual input of missing values via GoogleMaps search ##