Data Analysis Report
Joe Long, Data Analyst
October 2019
Opioid Overdose Crisis in San Diego County
Drug abuse and its related problems are among society’s most pervasive health and social concerns. Causes of drug-induced deaths include dependent and non-dependent use of drugs (both legal and illegal use) and also poisoning from medically prescribed drugs. Addicted persons frequently engage in self-destructive and criminal behavior, which can result in injury or death. In addition, recreational drug-use can lead to unintentional overdose and death.
Objectives :
- Collecting data from various public sources for data relating to drug overdose issues in San Diego county, in California, and in the nation, for trend analysis and for comparison
- Looking into supply side of opioid prescription in San Diego county
#Cautions: Two different coding classification systems were utilized:
Cause-of-death between 1999 to present were coded using the Tenth Revision of the International Classification of Diseases codes (ICD-10). Cause-of-death between 1989 and 1998 were coded using the Ninth Revision of the International Classification of Deseases Codes (ICD-9).
Dada Source : https://www.cdc.gov/drugoverdose/data/statedeaths.html
National Trends
Total number and rate of opioid prescriptions dispensed, United States, 2006–2017
national_trend <- general %>%
group_by(YEAR) %>% na.omit %>%
summarise(Total = sum(VALUE))
ggplot(data=national_trend,aes(x=as.factor(YEAR),y=Total))+
geom_bar(stat = "identity",fill="darkorange")+
labs(title = "Total Opioid Overdose Death, national ",
subtitle = "By year",
caption = "Data Source: CDC",
tag = "Figure 6",
x = "Year",
y = "Count")+
scale_y_continuous(labels = comma)
Opioid Prescription, nationally by year.
prescription <- read.csv("~/Datasets/Opioid_CA/presciption_byyear.csv")
data.table(prescription)## Year Total.Number.of.Prescriptions Prescribing.Rate.Per.100.Persons
## 1: 2006 215917663 72.4
## 2: 2007 228543773 75.9
## 3: 2008 237860213 78.2
## 4: 2009 243738090 79.5
## 5: 2010 251088904 81.2
## 6: 2011 252167963 80.9
## 7: 2012 255207954 81.3
## 8: 2013 247090443 78.1
## 9: 2014 240993021 75.6
## 10: 2015 226819924 70.6
## 11: 2016 214881622 66.5
## 12: 2017 191218272 58.7ggplot(data=prescription,aes(x=as.factor(Year),y=Total.Number.of.Prescriptions))+
geom_bar(stat = "identity",fill="#210000")+
labs(title = "Number of Opioid prescription, national ",
subtitle = "By year",
caption = "Data Source: CDC",
tag = "Figure 1",
x = "Year",
y = "Count")+
scale_y_continuous(labels = comma)+
geom_hline(yintercept = max(prescription$Total.Number.of.Prescriptions), color="red")
Key Highlights
After a steady increase in the overall national opioid prescribing rate starting in 2006, the total number of prescriptions dispensed peaked in 2012 at more than 255 million and a prescribing rate of 81.3 prescriptions per 100 persons.
The overall national opioid prescribing rate declined from 2012 to 2017, and in 2017, the prescribing rate had fallen to the lowest it had been in more than 10 years at 58.7 prescriptions per 100 persons (total of more than 191 million total opioid prescriptions).
However, in 2017, prescribing rates continue to remain very high in certain areas across the country. In 16% of U.S. counties, enough opioid prescriptions were dispensed for every person to have one. While the overall opioid prescribing rate in 2017 was 58.7 prescriptions per 100 people, some counties had rates that were seven times higher than that.
Raw data : Opioid Overdose Death count by state
library(choroplethr)
data("df_pop_state")
state_year <- general %>%
group_by(YEAR,STATE) %>%
na.omit(VALUE) %>%
summarise(Count=sum(VALUE))
#----------2017
state_2017 <- state_year %>%
filter(YEAR==2017)
df_state <- state_2017[,2:3]
df_state$STATE <- tolower(df_state$STATE)
colnames(df_state) <- c("region","value")
df_state$value[is.na(df_state$value)] <- 0
state_choropleth(df_state,
title = "Opioid Overdose Death _ 2017 ",
legend = "Year Count")
#---------------------
top_ten <- top_n(state_2017, n=10,Count)
top_ten <- top_ten[,2:3]
top_ten <- arrange(top_ten,-Count)
kable(top_ten) %>%
kable_styling(bootstrap_options = c("striped", "hover"),full_width = F)| STATE | Count |
|---|---|
| Pennsylvania | 5326 |
| Florida | 5002 |
| Ohio | 4984 |
| California | 4805 |
| New York | 3814 |
| New Jersey | 2685 |
| Texas | 2604 |
| Illinois | 2550 |
| Michigan | 2438 |
| Maryland | 2237 |
ggplot(data=top_ten,aes(x=reorder(STATE,-Count),y=Count))+
geom_bar(stat = "identity",fill="#044400")+
labs(title = "Opioid Overdose Death Count_ 2017 ",
subtitle = "top ten states",
caption = "Data Source: CDC",
tag = "Figure 5",
x = "State",
y = "Count")+
theme(axis.text.x = element_text(angle = 45, hjust = 1))
2010 Mapping _ Historial perspective
Note: one state, NORTH DAKOTA has missing value.
value of zero is imputed.
#----------2010
# Note
state_2010 <- state_year %>%
filter(YEAR==2010)
df_state <- state_2010[,2:3]
df_state$STATE <- tolower(df_state$STATE)
colnames(df_state) <- c("region","value")
north_dakota<-data.frame("north dakota",0)
colnames(north_dakota) <- c("region","value")
df_state <- rbind(df_state, north_dakota)
state_choropleth(df_state,
title = "Opioid Overdose Death _ 2010 ",
legend = "Year Count")
top_ten <- top_n(state_2010, n=10,Count)
top_ten <- top_ten[,2:3]
kable(top_ten) %>%
kable_styling(bootstrap_options = c("striped", "hover"),full_width = F)| STATE | Count |
|---|---|
| Arizona | 1073 |
| California | 4011 |
| Florida | 2978 |
| Illinois | 1135 |
| Michigan | 1192 |
| New York | 1388 |
| North Carolina | 863 |
| Ohio | 1576 |
| Pennsylvania | 1801 |
| Texas | 2043 |
ggplot(data=top_ten,aes(x=reorder(STATE,-Count),y=Count))+
geom_bar(stat = "identity",fill="navy")+
labs(title = "Opioid Overdose Death Count_ 2010 ",
subtitle = "top ten states",
caption = "Data Source: CDC",
tag = "Figure 5",
x = "State",
y = "Count")+
theme(axis.text.x = element_text(angle = 45, hjust = 1))
2014 Mapping _ Historical perspective
Note: one state, NORTH DAKOTA has missing value.
value of zero is imputed.
#----------2014
state_2014 <- state_year %>%
filter(YEAR==2014)
df_state <- state_2014[,2:3]
df_state$STATE <- tolower(df_state$STATE)
colnames(df_state) <- c("region","value")
state_choropleth(df_state,
title = "Opioid Overdose Death _ 2014 ",
legend = "Year Count")
top_ten <- top_n(state_2014, n=10,Count)
top_ten <- top_ten[,2:3]
kable(top_ten) %>%
kable_styling(bootstrap_options = c("striped", "hover"),full_width = F)| STATE | Count |
|---|---|
| California | 4447 |
| Florida | 2547 |
| Illinois | 1504 |
| Massachusetts | 1283 |
| Michigan | 1546 |
| New Jersey | 1253 |
| New York | 2182 |
| Ohio | 2553 |
| Pennsylvania | 2636 |
| Texas | 2226 |
ggplot(data=top_ten,aes(x=reorder(STATE,-Count),y=Count))+
geom_bar(stat = "identity",fill="maroon")+
labs(title = "Opioid Overdose Death Count_ 2014 ",
subtitle = "top ten states",
caption = "Data Source: CDC",
tag = "Figure 5",
x = "State",
y = "Count")+
theme(axis.text.x = element_text(angle = 45, hjust = 1))
California state
| County | Count |
|---|---|
| Trinity | 33.53 |
| Lake | 23.01 |
| Plumas | 18.11 |
| Calaveras | 17.41 |
| Amador | 15.69 |
| San Francisco | 14.96 |
| Mendocino | 14.64 |
| Lassen | 14.53 |
| Inyo | 14.45 |
| Ventura | 10.66 |
| Humboldt | 10.52 |
| Nevada | 10.50 |
| Kern | 10.32 |
| Imperial | 10.23 |
| Sutter | 10.05 |
| Sonoma | 8.72 |
| Santa Cruz | 8.22 |
| Merced | 7.76 |
| Orange | 7.37 |
| San Diego | 7.32 |
| Contra Costa | 6.69 |
| Butte | 6.55 |
| Santa Barbara | 6.48 |
| Stanislaus | 6.14 |
| Shasta | 6.06 |
| Riverside | 5.68 |
| Yuba | 5.36 |
| San Benito | 5.08 |
| San Mateo | 5.02 |
| Tuolumne | 5.00 |
| Glenn | 4.71 |
| San Luis Obispo | 4.63 |
| Los Angeles | 4.53 |
| El Dorado | 4.39 |
| Marin | 4.03 |
| Solano | 4.02 |
| Madera | 3.98 |
| Napa | 3.97 |
| San Bernardino | 3.88 |
| Sacramento | 3.78 |
| Siskiyou | 3.62 |
| Yolo | 3.52 |
| San Joaquin | 3.36 |
| Alameda | 3.10 |
| Fresno | 3.04 |
| Santa Clara | 2.75 |
| Placer | 2.42 |
| Kings | 2.08 |
| Tehama | 1.93 |
| Monterey | 1.92 |
| Tulare | 1.73 |
| Alpine | 0.00 |
| Colusa | 0.00 |
| Del Norte | 0.00 |
| Mariposa | 0.00 |
| Modoc | 0.00 |
| Mono | 0.00 |
| Sierra | 0.00 |
All Causes of Death
Dictionary_Causes of Death
AID AID HTD Diseases of the Heart
CAN Malignant Neoplasms (Cancers)
STK Cerebrovascular Disease (Stroke)
CLD Chronic Lower Respiratory Disease (CLRD)
INJ Unintentional Injuries
PNF Pneumonia and Influenza
DIA Diabetes Mellitus
ALZ Alzheimer’s Disease
LIV Chronic Liver Disease and Cirrhosis
SUI Intentional Self Harm (Suicide)
HYP Essential Hypertension and Hypertensive Renal Disease
HOM Homicide
NEP Nephritis, Nephrotic Syndrome and Nephrosis
CPD Chronic pulmonary disease
OTH All Other Causes of Death
Drug Overdose Causes of Death
Zip codes in San diego county
from 91901 to 92199
## 'data.frame': 176460 obs. of 5 variables:
## $ Year : int 1989 1989 1989 1989 1989 1989 1989 1989 1989 1989 ...
## $ Zip_code: Factor w/ 1811 levels "90001","90002",..: 1 1 1 1 1 1 1 1 1 1 ...
## $ Cause : Factor w/ 12 levels "AID","CAN","CPD",..: 6 2 11 7 10 3 1 8 12 4 ...
## $ Count : int 108 61 21 19 10 12 7 9 5 8 ...
## $ Location: Factor w/ 1698 levels "","(32.562567, -117.042976)",..: 358 358 358 358 358 358 358 358 358 358 ...## Warning: NAs introduced by coercion## # A tibble: 120 x 3
## # Groups: Year [10]
## Year Cause Total
## <int> <fct> <int>
## 1 1989 AID 327
## 2 1989 CAN 3975
## 3 1989 CPD 807
## 4 1989 DIA 205
## 5 1989 HOM 159
## 6 1989 HTD 5030
## 7 1989 INJ 705
## 8 1989 LIV 269
## 9 1989 OTH 2646
## 10 1989 PNF 800
## # ... with 110 more rows## Year Cause Total
## 1: 1998 AID 359
## 2: 1998 CAN 4363
## 3: 1998 CPD 667
## 4: 1998 DIA 98
## 5: 1998 HOM 109
## 6: 1998 HTD 5566
## 7: 1998 INJ 1041
## 8: 1998 LIV 253
## 9: 1998 OTH 3342
## 10: 1998 PNF 1139
## 11: 1998 STK 1341
## 12: 1998 SUI 299
Time Trend Plots
## Selecting by Rate
Drug Suppies
For the first time, a database maintained by the Drug Enforcement Administration that tracks the path of every single pain pill sold in the United States — by manufacturers and distributors to pharmacies in every town and city — has been made public.
The Washington Post sifted through nearly 380 million transactions from 2006 through 2012 that are detailed in the DEA’s database and analyzed shipments of oxycodone and hydrocodone pills, which account for three-quarters of the total opioid pill shipments to pharmacies. The Post is making this data available at the county and state levels in order to help the public understand the impact of years of prescription pill shipments on their communities.
Top pharmacies from 2006 to 2012, in San Diego County, California.
sdpharmacy <- read.delim("C:/Users/Joe/Downloads/arcos-ca-san-diego-06073-pharmacy.tsv")
sdmanufacturer <- read.delim("C:/Users/Joe/Downloads/arcos-ca-san-diego-06073-labeler.tsv")
sddistributor <- read.delim("C:/Users/Joe/Downloads/arcos-ca-san-diego-06073-distributor.tsv")
#----------
top_pharmany <- top_n(sdpharmacy,20,total_dosage_unit)
top_pharmany <- select(top_pharmany,c(4:6))
kable(top_pharmany) %>%
kable_styling(bootstrap_options = c("striped", "hover"),full_width = F)| buyer_name | buyer_city | total_dosage_unit |
|---|---|---|
| OPTUMRX | CARLSBAD | 65887670 |
| KAISER PERMANENTE PHRMCY 217 | SAN DIEGO | 8884400 |
| RX SOLUTIONS, INC | CARLSBAD | 8165420 |
| OMNICARE OF SAN DIEGO | SAN DIEGO | 6523160 |
| KAISER PERMANENTE PHRMCY 230 | LA MESA | 5590820 |
| WALGREEN CO. | EL CAJON | 5021730 |
| KAISER FOUNDATION HEALTH | EL CAJON | 4937380 |
| KAISER FOUND HEALTH PLAN INC | SAN MARCOS | 4663510 |
| KAISER FOUND HLTH PLAN | SAN DIEGO | 4652610 |
| GARFIELD BEACH CVS, L.L.C. | SANTEE | 4642589 |
| MEDICAL CENTER PHARMACY | SAN DIEGO | 4338210 |
| GARFIELD BEACH CVS, L.L.C. | ESCONDIDO | 4235700 |
| GARFIELD BEACH CVS, L.L.C. | EL CAJON | 4042400 |
| GARFIELD BEACH CVS, L.L.C. | CARLSBAD | 3848300 |
| KAISER FDN HEALTH PLAN INC | SAN DIEGO | 3701590 |
| GARFIELD BEACH CVS, L.L.C. | EL CAJON | 3519500 |
| RON’S PHARMACY SERVICES | SAN DIEGO | 3516400 |
| KAISER FDN HEALTH PLAN INC | BONITA | 3508500 |
| GARFIELD BEACH CVS, L.L.C. | OCEANSIDE | 3100720 |
| KAISER PERMANENTE PHRMCY 245 | LA MESA | 3094520 |
Top drug distributors in San Diego County
Data source : Washington Post.
a database maintained by the Drug Enforcement Administration that tracks the path of every single pain pill sold in the United States — by manufacturers and distributors to pharmacies in every town and city — has been made public.
The Washington Post sifted through nearly 380 million transactions from 2006 through 2012 that are detailed in the DEA’s database and analyzed shipments of oxycodone and hydrocodone pills, which account for three-quarters of the total opioid pill shipments to pharmacies. The Post is making this data available at the county and state levels in order to help the public understand the impact of years of prescription pill shipments on their communities.
#----------
top_distributor <- top_n(sddistributor,20,total_dosage_unit)
top_distributor <- select(top_distributor,c(3:4))
print(" Top Distibutors from 2006 to 2012, in San Diego County, California.")## [1] " Top Distibutors from 2006 to 2012, in San Diego County, California."kable(top_distributor) %>%
kable_styling(bootstrap_options = c("striped", "hover"),full_width = F) | Reporter_family | total_dosage_unit |
|---|---|
| McKesson Corporation | 155915480 |
| AmerisourceBergen Drug | 99001045 |
| CVS | 68531400 |
| Cardinal Health | 62698690 |
| Thrifty Payless Inc | 43887470 |
| Walgreen Co | 36921310 |
| Kaiser Permanente | 23526830 |
| Wal-Mart | 18394600 |
| Advantage Logistics | 12546500 |
| American Drug Stores | 11715900 |
| Par Pharmaceutical, Inc. | 10959680 |
| Qualitest Pharmaceuticals | 10401720 |
| Actavis Pharma, Inc. | 7632900 |
| Omnicare Distribution Center LLC | 5694760 |
| Smith’s Food & Drug Ctr’s Inc | 4785010 |
| The Harvard Drug Group | 4638170 |
| H. D. Smith | 4292400 |
| Amneal Pharmaceuticals LLC | 4176000 |
| Valley Wholesale Drug Co | 3679380 |
| Anda, Inc | 2607520 |
City of Pine Valley
A rural community on a hillside area in San Diego County, with population of 2,000 people.
View(sdpharmacy)
pinevalley <- sdpharmacy %>% filter(buyer_city=="PINE VALLEY") %>% select(buyer_name,total_dosage_unit)
kable(pinevalley,format = "html",caption = "City of Pine Valley with population of 2,000",
col.names = c("Pharmacy", "Count of Prescription ")) %>%
kable_styling(bootstrap_options = c("striped", "hover"),full_width = F) | Pharmacy | Count of Prescription |
|---|---|
| GRIZZLE, REX E DDS | 12140 |
| STARKEY, ALMEDA LOUISE DVM | 100 |