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)
#> `summarise()` has grouped output by 'BUYER_COUNTY', 'BUYER_STATE'. You can
#> override using the `.groups` argument.
## Join the data
wv_joined <- left_join(west_virginia, population)
#> Joining with `by = join_by(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)
# 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)
))#> Joining with `by = join_by(buyer_dea_no)`| 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 |