Opioid Prescriptions in the United States¶
An Exploration of the Washington Post Opioid Dataset
Table of Contents¶
In [1]:
import os
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import calendar
from ipyleaflet import *
import warnings
warnings.filterwarnings('ignore')
In [2]:
# magic function making plot outputs appear within the notebook
%matplotlib inline
# change the default plot output size
plt.rcParams['figure.figsize'] = [18, 8]
In [3]:
# get current working directory
cwd = os.getcwd()
data_dir = os.path.join(cwd, 'data')
# set input and output Excel files
in_nation = os.path.join(data_dir, 'OpioidsPerYearTotalAndRate.xlsx')
in_state = os.path.join(data_dir, 'OpioidRxRateByState.xlsx')
in_county = os.path.join(data_dir, 'OpioidRxRate_County.xlsx')
in_rate = os.path.join(data_dir, 'US_Opioid_OverdoseDeathsByState.xlsx')
In [4]:
df_nation_total_rx = pd.read_excel(in_nation, sheet_name='TotalRx')
df_nation_total_rx.head()
Out[4]:
In [5]:
# Plot the trend by year
ax = df_nation_total_rx.plot.bar(x=0, y=1, title="Total Prescriptions by Year", rot=0)
ax.set_xlabel("Year")
ax.set_ylabel("Total Prescriptions")
vals = ax.get_yticks().astype(int)
ax.set_yticklabels(['{:,}'.format(x) for x in vals])
Out[5]:
In [6]:
# Plot the trend by year
ax = df_nation_total_rx.plot.bar(x=0, y=2, title="Prescription Rate Per 100 People", rot=0)
ax.set_xlabel("Year")
ax.set_ylabel("Prescription Rate")
vals = ax.get_yticks().astype(int)
# ax.set_yticklabels(['{:,}'.format(x) for x in vals])
County participation¶
In [7]:
df_nation_county_ptcp = pd.read_excel(in_nation, sheet_name='CountyParticipation')
df_nation_county_ptcp.head()
Out[7]:
In [8]:
# Plot the trend by year
ax = df_nation_county_ptcp.plot.bar(x=0, y=2, title="Counties with Data", rot=0)
ax.set_xlabel("Year")
ax.set_ylabel("Total Prescriptions")
vals = ax.get_yticks().astype(int)
# ax.set_yticklabels(['{:,}'.format(x) for x in vals])
In [9]:
# Plot the trend by year
ax = df_nation_county_ptcp.plot.bar(x=0, y=3, title="Percent Counties with Data", rot=0)
ax.set_xlabel("Year")
ax.set_ylabel("Total Prescriptions")
vals = ax.get_yticks().astype(int)
In [10]:
df_state = pd.read_excel(in_state, sheet_name=None, ignore_index=True)
df_template = pd.read_excel(in_state, sheet_name=0)
for index, key in enumerate(df_state.keys()):
df_year = pd.read_excel(in_state, sheet_name=key)
df_template[key] = df_year.iloc[:, 2]
df_state=df_template.drop(columns=['2006 Prescribing Rate'])
df_state.head()
Out[10]:
In [11]:
df_state['mean'] = df_state.mean(axis=1)
df_state.head()
Out[11]:
In [12]:
out_state_file = in_state.replace(".xlsx", "_Merge.xlsx")
df_state.to_excel(out_state_file, index=False)
In [13]:
ax = df_state.plot.bar(x=1, y=range(2, 14), rot=0, title="Prescription Rate by State")
ax.set_xlabel("State")
ax.set_ylabel("Prescription Rate")
Out[13]:
In [14]:
df_state_sort = df_state.sort_values(by='mean', ascending=False)
df_state_sort.head()
Out[14]:
In [15]:
ax = df_state_sort.plot.bar(x=1, y=range(2, 14), rot=0, title="Prescription Rate by State")
ax.set_xlabel("State")
ax.set_ylabel("Prescription Rate")
Out[15]:
In [16]:
df_state_top10 = df_state_sort.head(10)
df_state_top10
Out[16]:
Top 10 States with the highest prescription rate¶
In [17]:
ax = df_state_top10.plot.bar(x=1, y=range(2, 14), rot=0, title="Prescription Rate by State")
ax.set_xlabel("State")
ax.set_ylabel("Prescription Rate")
Out[17]:
In [18]:
ax = df_state_sort.plot.bar(x=1, y=14, rot=0, title="Mean Prescription Rate by State")
ax.set_xlabel("State")
ax.set_ylabel("Prescription Rate")
Out[18]:
Opioid Precriptions by County (2006-2017) ¶
In [19]:
df_county = pd.ExcelFile(in_county)
sheet_names =df_county.sheet_names
print(sheet_names)
num_sheets = len(df_county.sheet_names)
print(f"Total number of sheets: {num_sheets}")
In [20]:
sheet_template = df_county.parse(sheet_name=sheet_names[0])[["County", "State", "FIPS County Code"]]
sheet_template.head()
Out[20]:
Merging each yearly spreadsheet into a single spreadsheet¶
In [21]:
for sheet_name in sheet_names[:-1]:
print(sheet_name)
sheet_year = df_county.parse(sheet_name)
# print(sheet_year.head()).
sheet_template= pd.merge(sheet_template, sheet_year, how='left', on=['FIPS County Code'])
# print(sheet_template.head())
sheet_template.head()
Out[21]:
Filter and select columns:
In [22]:
filter_col = ["County", "State", "FIPS County Code"] + [col for col in sheet_template if col.startswith('20')]
filter_col
Out[22]:
Display selected columns:
In [23]:
df_county_merge = sheet_template[filter_col]
df_county_merge.head()
Out[23]:
In [24]:
col_names = ["County", "State", "FIPS County Code"] + sheet_names[:-1]
print(col_names)
Change column names:
In [25]:
df_county_merge.columns = col_names
df_county_merge['FIPS County Code']= df_county_merge['FIPS County Code'].astype(str)
df_county_merge['FIPS County Code'] = df_county_merge['FIPS County Code'].apply(lambda x: x.zfill(5))
df_county_merge.head()
Out[25]:
Create a mean column:
In [26]:
df_county_merge = df_county_merge.replace(-9999, np.nan)
df_county_merge['mean'] = df_county_merge.mean(axis=1)
df_county_merge.head()
Out[26]:
Save the merged opioid data as a new Excel file:
In [27]:
out_county_file = in_county.replace(".xlsx", "_Merge.xlsx")
df_county_merge.to_excel(out_county_file, index=False)
Sort mean prescription rate by county:
In [28]:
df_county = pd.read_excel(out_county_file, sheet_name=0, ignore_index=True)
df_county_sort = df_county.sort_values(by='mean', ascending=False)
df_county_top10 = df_county_sort.head(10)
df_county_top10
Out[28]:
In [29]:
ax = df_county_top10.plot.bar(x=0, y=range(3, 15), rot=0, title="Top 10 County")
ax.set_xlabel("County")
ax.set_ylabel("Prescription Rate")
Out[29]:
In [30]:
ax = df_county_top10.plot.bar(x=0, y=15, rot=0, title="Mean Prescription Rate by County")
ax.set_xlabel("County")
ax.set_ylabel("Prescription Rate")
Out[30]:
Opioid Prescriptions in Tennessee (2006-2017) ¶
Select Tennessee opioid data by county:
In [31]:
df_tn = df_county[df_county['State']=='TN']
df_tn.head(10)
Out[31]:
In [32]:
ax = df_tn.plot.bar(x=0, y=15, rot=0, title="Mean Prescription Rate by County")
ax.set_xlabel("County")
ax.set_ylabel("Prescription Rate")
Out[32]:
In [33]:
df_tn_sort = df_tn.sort_values(by='mean', ascending=False)
ax = df_tn_sort.plot.bar(x=0, y=15, rot=0, title="Mean Prescription Rate by County")
ax.set_xlabel("County")
ax.set_ylabel("Prescription Rate")
Out[33]:
In [34]:
df_tn_top10 = df_tn_sort.head(10)
df_tn_top10
Out[34]:
In [35]:
ax = df_tn_top10.plot.bar(x=0, y=15, rot=0, title="Top 10 TN County")
ax.set_xlabel("County")
ax.set_ylabel("Prescription Rate")
Out[35]:
In [36]:
ax = df_tn_top10.plot.bar(x=0, y=range(3, 15), rot=0, title="Top 10 County")
ax.set_xlabel("County")
ax.set_ylabel("Prescription Rate")
Out[36]:
In [ ]: