Per capita pharmacies

Here’s our process for calculating pills per person within a county— figures that end up in many of our articles.

First, let’s load up the packages we need.

There’s a lot of new ones listed here so we can make fancy tables.

# Uncomment and run the lines below to see if you have the packages required already installed
# packages <- c("dplyr", "jsonlite", "knitr", "geofacet", "scales")
# if (length(setdiff(packages, rownames(installed.packages()))) > 0) {
#   install.packages(setdiff(packages, rownames(installed.packages())), repos = "http://cran.us.r-project.org") # }

library(arcos)
library(dplyr)
library(lubridate)
library(data.table)
library(formattable)
library(vroom)
library(stringr)
library(scales)
library(knitr)

Let’s look at all pharmacies in West Virginia and their total oxycodone and hydrocodone pill orders.

And use the total_pharmacies_state() function.

west_virginia <- total_pharmacies_state(state="WV", key="WaPo")

kable(head(west_virginia))
buyer_state buyer_county buyer_dea_no buyer_name buyer_city total_dosage_unit total_records
WV MINGO BS7437064 STROSNIDER KERMIT 13168350 7691
WV LOGAN BF0660565 FAMILY DISCOUNT PHARMACY INC MOUNT GAY 12849040 5680
WV MINGO BH6954401 HURLEY DRUG COMPANY INC WILLIAMSON 8890370 11138
WV MINGO FT0251227 TUG VALLEY PHARMACY, LLC WILLIAMSON 8827860 5390
WV GREENBRIER BM8273524 FRITZ’S PHARMACY AND WELLNESS RONCEVERTE 7528100 13997
WV MASON AF6754748 FRUTH PHARMACY INC POINT PLEASANT 7317220 9193

Looks nice. We’ve got total_dosage_unit and total_records. We can ignore total_records for this example.

Each pharmacy is listed, as well as which county it resides in.

To help normalize the number of pills ordered, we need to get each county’s population in West Virginia.

We can use the county_population() function.

population <- county_population(state="WV", key="WaPo")

kable(head(population))
BUYER_COUNTY BUYER_STATE countyfips STATE COUNTY county_name NAME variable year population
BARBOUR WV 54001 54 1 Barbour Barbour County, West Virginia B01003_001 2006 15922
BERKELEY WV 54003 54 3 Berkeley Berkeley County, West Virginia B01003_001 2006 96318
BOONE WV 54005 54 5 Boone Boone County, West Virginia B01003_001 2006 25101
BRAXTON WV 54007 54 7 Braxton Braxton County, West Virginia B01003_001 2006 14585
BROOKE WV 54009 54 9 Brooke Brooke County, West Virginia B01003_001 2006 24399
CABELL WV 54011 54 11 Cabell Cabell County, West Virginia B01003_001 2006 94943

Alright, we have population tables but they’re annual figures.

We have one number for dosage per pharmacy. We don’t need seven years of population data.

So we’ll average it out.

And then join it to the pharmacy list.

population <- population %>% 
  group_by(BUYER_COUNTY, BUYER_STATE, countyfips) %>% 
  # Figure out the average population between available years
  summarize(average_population=mean(population, na.rm=T)) %>% 
  ## Have to quickly rename these columns to make them lower case so they'll join easily to the other data frame
  rename(buyer_county=BUYER_COUNTY, buyer_state=BUYER_STATE)

## Join the data
wv_joined <- left_join(west_virginia, population)
#> Joining, by = c("buyer_state", "buyer_county")

kable(head(wv_joined))
buyer_state buyer_county buyer_dea_no buyer_name buyer_city total_dosage_unit total_records countyfips average_population
WV MINGO BS7437064 STROSNIDER KERMIT 13168350 7691 54059 26752.78
WV LOGAN BF0660565 FAMILY DISCOUNT PHARMACY INC MOUNT GAY 12849040 5680 54045 36359.00
WV MINGO BH6954401 HURLEY DRUG COMPANY INC WILLIAMSON 8890370 11138 54059 26752.78
WV MINGO FT0251227 TUG VALLEY PHARMACY, LLC WILLIAMSON 8827860 5390 54059 26752.78
WV GREENBRIER BM8273524 FRITZ’S PHARMACY AND WELLNESS RONCEVERTE 7528100 13997 54025 35365.22
WV MASON AF6754748 FRUTH PHARMACY INC POINT PLEASANT 7317220 9193 54053 26878.56

Now that we’ve joined the county population data to each pharmacy, we can do some math to figure out the pills per person per year.

That’s pills / average population / 7

wv_joined <- wv_joined %>% 
  mutate(per_person=total_dosage_unit/average_population/7)

kable(head(wv_joined))
buyer_state buyer_county buyer_dea_no buyer_name buyer_city total_dosage_unit total_records countyfips average_population per_person
WV MINGO BS7437064 STROSNIDER KERMIT 13168350 7691 54059 26752.78 70.31766
WV LOGAN BF0660565 FAMILY DISCOUNT PHARMACY INC MOUNT GAY 12849040 5680 54045 36359.00 50.48481
WV MINGO BH6954401 HURLEY DRUG COMPANY INC WILLIAMSON 8890370 11138 54059 26752.78 47.47368
WV MINGO FT0251227 TUG VALLEY PHARMACY, LLC WILLIAMSON 8827860 5390 54059 26752.78 47.13988
WV GREENBRIER BM8273524 FRITZ’S PHARMACY AND WELLNESS RONCEVERTE 7528100 13997 54025 35365.22 30.40962
WV MASON AF6754748 FRUTH PHARMACY INC POINT PLEASANT 7317220 9193 54053 26878.56 38.89038

Let’s make sure we’re only dealing with chain and retail pharmacies.

This requires some supplemental data from buyer_addresses() and not_pharmacies()


## Get a list of addresses because it includes BUYER_BUS_ACT information
pharmacy_list <- buyer_addresses(state="WV", key="WaPo")

# We just want the BUYER_BUS_ACT to tell if these are practitioners are retail pharmacies
# This will help us filter out the appropriate pharmacies

pharmacy_list <- pharmacy_list %>% 
  select(buyer_dea_no=BUYER_DEA_NO, BUYER_BUS_ACT)

# Join to the original data set
wv_joined <- left_join(wv_joined, pharmacy_list)
#> Joining, by = "buyer_dea_no"

# Filter the data so we only have retail and chain pharmacies
wv_joined <- wv_joined %>% 
  filter(BUYER_BUS_ACT=="RETAIL PHARMACY" | BUYER_BUS_ACT=="CHAIN PHARMACY")

# Just in case, let's get the BUYER_DEA_NO of pharmacies that aren't really pharmacies
not_pharms <- not_pharmacies(key="WaPo") %>% pull(BUYER_DEA_NO)

# Filter those out, too, if they're in there
wv_joined <- wv_joined %>% 
  filter(!buyer_dea_no %in% not_pharms)

# clean up column names so we can make a pretty table
wv_joined <- wv_joined %>% 
  select(Pharmacy=buyer_name, City=buyer_city, County=buyer_county, `County population`=average_population,
         Pills=total_dosage_unit, `Pills per person`=per_person) %>% 
  mutate(`County population`=round(`County population`),
         `Pills per person`=round(`Pills per person`, 1)) %>% 
  arrange(desc(`Pills per person`)) %>% 
  slice(1:100)

# Create some custome colors
customGreen0 = "#DeF7E9"
customGreen = "#71CA97"
customRed = "#ff7f7f"

# produce a table
wv_joined %>% 
  formattable(align=c("l", "l", "l", "r", "r", "r"),
              list(Pharmacy = formatter("span", style = ~ style(color="grey", font.weight = "bold")),
                   Pills=color_tile(customGreen0, customGreen),
                   `Pills per person` = normalize_bar(customRed)
                   ))
Pharmacy City County County population Pills Pills per person
STROSNIDER KERMIT MINGO 26753 13168350 70.3
FAMILY DISCOUNT PHARMACY INC MOUNT GAY LOGAN 36359 12849040 50.5
HURLEY DRUG COMPANY INC WILLIAMSON MINGO 26753 8890370 47.5
TUG VALLEY PHARMACY, LLC WILLIAMSON MINGO 26753 8827860 47.1
LARRY’S DRIVE-IN PHARMACY INC MADISON BOONE 24780 7227600 41.7
FRUTH PHARMACY INC POINT PLEASANT MASON 26879 7317220 38.9
REED’S BERKELEY SPRINGS MORGAN 17295 3939400 32.5
WESTSIDE PHARMACY OCEANA WYOMING 23793 5353990 32.1
MACE’S PHARMACY INC PHILIPPI BARBOUR 16244 3612930 31.8
PHILLIPS PHARMACY SAINT MARYS PLEASANTS 7631 1686260 31.6
FRITZ’S PHARMACY AND WELLNESS RONCEVERTE GREENBRIER 35365 7528100 30.4
HIGHLANDER PHARMACY WEBSTER SPRINGS WEBSTER 9234 1946520 30.1
BIG FOUR DRUG STORE INC HINTON SUMMERS 13739 2664450 27.7
PHARMACY INC CHAPMANVILLE LOGAN 36359 7032640 27.6
PHARMACY INC CHAPMANVILLE LOGAN 36359 7032640 27.6
RITE AID OF WEST VIRGINIA, INC. NEW MARTINSVILLE WETZEL 16598 3168780 27.3
JUDY’S DRUG STORE INC PETERSBURG GRANT 11904 2207400 26.5
RITE AID OF WEST VIRGINIA, INC. ST MARYS PLEASANTS 7631 1281320 24.0
FOLLANSBEE PHARMACY FOLLANSBEE BROOKE 24069 3899940 23.1
BATJAC OF SOUTH CAROLINA MARLINTON POCAHONTAS 8774 1397320 22.8
GIL-CO FAITH PHCY GLENVILLE GILMER 8283 1320859 22.8
FLAT IRON DRUG STORE INC WELCH MCDOWELL 22347 3431120 21.9
FRUTH PHARMACY #18 SPENCER ROANE 15043 2308890 21.9
WAL-MART PHARMACY 10-2684 NEW MARTINSVILLE WETZEL 16598 2426050 20.9
RITE AID OF WEST VIRGINIA, INC. PT PLEASANT MASON 26879 3912290 20.8
RITE AID OF WEST VIRGINIA, INC. GRAFTON TAYLOR 16715 2349790 20.1
RITE AID OF WEST VIRGINIA, INC. CLAY CLAY 9572 1280300 19.1
BEST CARE PHARMACY INC WESTON LEWIS 16496 2180940 18.9
NICHOLAS PHARMACY, INC SUMMERSVILLE NICHOLAS 26157 3439100 18.8
WEST VIRGINIA CVS PHARMACY, L.L.C. GASSAWAY BRAXTON 14551 1878800 18.4
RITE AID OF WEST VIRGINIA, INC. FRANKLIN PENDLETON 7705 993310 18.4
BLACK DIAMOND PHARMACY NORTHFORK MCDOWELL 22347 2755760 17.6
MICHEL’S PHARMACY WEST UNION DODDRIDGE 7985 961790 17.2
PHILLIPS DRUG LLC SISTERSVILLE TYLER 9249 1105230 17.1
TRI-STATE PHARMACY WEIRTON HANCOCK 30694 3539710 16.5
FOUR SEASONS PHARMACY INC PRINCETON MERCER 61867 7099410 16.4
MOUNDSVILLE PHARMACY MOUNDSVILLE MARSHALL 33215 3808030 16.4
WAL-MART PHARMACY 10-2849 MASON MASON 26879 3010650 16.0
RITE AID OF WEST VIRGINIA, INC. HARRISVILLE RITCHIE 10428 1165930 16.0
CARL WALKER’S DRUG STORE GASSAWAY BRAXTON 14551 1623340 15.9
POCAHONTAS PHARMACY MARLINTON POCAHONTAS 8774 977390 15.9
WEST VIRGINIA CVS PHARMACY, L.L.C. WESTON LEWIS 16496 1793260 15.5
DENNIS’, PHARMACY LLC HAMLIN LINCOLN 21860 2349260 15.4
WEST VIRGINIA CVS PHARMACY, L.L.C. MOUNDSVILLE MARSHALL 33215 3551300 15.3
UNITED PHARMACY GROUP OCEANA WYOMING 23793 2552680 15.3
WAL-MART PHARMACY 10-2810 SPENCER ROANE 15043 1612830 15.3
CLAY FOODLAND PHARMACY CLAY CLAY 9572 1003280 15.0
WEST VIRGINIA CVS PHARMACY, L.L.C. ROMNEY HAMPSHIRE 23459 2419200 14.7
RITE AID OF WEST VIRGINIA, INC. BRANCHLAND LINCOLN 21860 2218910 14.5
MOUNTAIN LAKE PHARMACY SUMMERSVILLE NICHOLAS 26157 2617790 14.3
KROGER PHARMACY MADISON BOONE 24780 2483700 14.3
WEST VIRGINIA CVS PHARMACY, L.L.C. MOOREFIELD HARDY 13801 1331100 13.8
PHARMACY CARE, INC. PINEVILLE WYOMING 23793 2262020 13.6
KROGER PHARMACY LOGAN LOGAN 36359 3441400 13.5
RITE AID OF WEST VIRGINIA, INC. WEBSTER SPRINGS WEBSTER 9234 857680 13.3
PETERSTOWN PHARMACY, LLC PETERSTOWN MONROE 13515 1253120 13.2
RITE AID OF WEST VIRGINIA, INC. GRANTSVILLE CALHOUN 7550 697330 13.2
WEST VIRGINIA CVS PHARMACY, L.L.C. KEYSER MINERAL 27709 2541900 13.1
WAL-MART PHARMACY 10-2610 LOGAN LOGAN 36359 3251160 12.8
WAL-MART PHARMACY 10-1477 SUMMERSVILLE NICHOLAS 26157 2329350 12.7
RITE AID OF WEST VIRGINIA, INC. GLENVILLE GILMER 8283 716400 12.4
TOWN PHARMACY CARE MAN LOGAN 36359 3055510 12.0
WAL-MART PHARMACY 10-1653 WESTON LEWIS 16496 1388800 12.0
RITE AID OF WEST VIRGINIA, INC. SUTTON BRAXTON 14551 1222140 12.0
COLONY DRUG BECKLEY RALEIGH 78606 6550590 11.9
CRAB ORCHARD PHARMACY, INC. CRAB ORCHARD RALEIGH 78606 6477420 11.8
WAL-MART PHARMACY 10-2696 MOOREFIELD HARDY 13801 1125820 11.7
WEST VIRGINIA CVS PHARMACY, L.L.C. SUMMERSVILLE NICHOLAS 26157 2121460 11.6
RITE AID OF WEST VIRGINIA, INC. DANVILLE BOONE 24780 2015250 11.6
WELCH PHARMACY, INC WELCH MCDOWELL 22347 1790640 11.4
HARTS PHARMACY, INC. HARTS LINCOLN 21860 1736300 11.3
WAL-MART PHARMACY 10-1522 ELKINS RANDOLPH 29170 2220600 10.9
STAATS PHCY & HLTH CARE SPENCER ROANE 15043 1145830 10.9
BURNSVILLE DRUG STORE BURNSVILLE BRAXTON 14551 1112100 10.9
T AND J ENTERPRISES INC HUNTINGTON WAYNE 42322 3210690 10.8
FRUTH RIPLEY JACKSON 29013 2192823 10.8
PENN WAY PHARMACY WEIRTON HANCOCK 30694 2296230 10.7
GREENBRIER MEDICAL ARTS PHARMACY INC UNION MONROE 13515 1013090 10.7
RITE AID OF WEST VIRGINIA, INC. CRAIGSVILLE NICHOLAS 26157 1945700 10.6
MEADOW RIVER HEALTH CARE ASSOCIATES, INC. CRAIGSVILLE NICHOLAS 26157 1944600 10.6
RITE AID OF WEST VIRGINIA, INC. HINTON SUMMERS 13739 1011540 10.5
ARACOMA DRUG CO OF CHAPMANVIL CHAPMANVILLE LOGAN 36359 2656020 10.4
FAM DISCNT PHCY OF STOLLINGS LOGAN LOGAN 36359 2639720 10.4
FAM DISCNT PHCY OF STOLLINGS LOGAN LOGAN 36359 2639720 10.4
CHARLIE’S PHARMACY OF MULLENS, LLC MULLENS WYOMING 23793 1737920 10.4
WITSCHEY’SPHARMACY NEW MARTINSVILLE WETZEL 16598 1176480 10.1
CARDINAL PHARMACY ELIZABETH WIRT 5768 409160 10.1
WAL-MART PHARMACY 10-2833 GRAFTON TAYLOR 16715 1164600 10.0
HAYES PROFESSIONAL SERVICE, LLC SISTERSVILLE TYLER 9249 647810 10.0
PENNSBORO FAMILY PHARMACY PENNSBORO RITCHIE 10428 721220 9.9
CARDINAL PHARMACY ELIZABETH WIRT 5768 399420 9.9
RITE AID OF WEST VIRGINIA, INC. WHEELING OHIO 44436 3053760 9.8
THRIFT DRUG, INC. WEIRTON HANCOCK 30694 2107570 9.8
RITE AID OF WEST VIRGINIA, INC. RAVENSWOOD JACKSON 29013 1980740 9.8
WAL-MART PHARMACY 10-1544 CLARKSBURG HARRISON 68581 4659700 9.7
MEDICINE STOP PHARMACY UNEEDA BOONE 24780 1689590 9.7
RITE AID OF WEST VIRGINIA, INC. PETERSBURG GRANT 11904 806970 9.7
RITE AID OF WEST VIRGINIA, INC. BENWOOD MARSHALL 33215 2225690 9.6
RITE AID OF WEST VIRGINIA, INC. BERKELEY SPRINGS MORGAN 17295 1156910 9.6
MAN PHARMACY MAN LOGAN 36359 2420280 9.5