NOTE: The Washington Post requests that those who create online stories, graphics, maps, or other pieces of journalism based on these data please credit The Washington Post, link to the original source article (title and URL above), and send them an email (maria.sanchezdiez@washpost.com) after publication.
The following is excerpted from The Washington Post article titled "Drilling into the DEA's pain pill database":
"About this story"
"The Washington Post analyzed data from the Drug Enforcement Administration's Automation of Reports and Consolidated Orders System, known as ARCOS, from 2006 to 2012."
"Data analyzed includes only shipments from sales of oxycodone and hydrocodone pills to retail pharmacies, chain pharmacies and practitioners. The entire database tracks a dozen different opioids, including oxycodone and hydrocodone, which make up three-quarters of the total pill shipments to pharmacies."
"Originally published July 16, 2019."
"Data analysis by Steven Rich, Aaron Williams and Andrew Ba Tran. Graphics, design and development by Armand Emamdjomeh, Kevin Schaul, Jake Crump and Chris Alcantara. Editing by Danielle Rindler, Tim Meko and Matt Callahan. Additional development by Jason Holt."
OPIOID_DATA_ARCOS_DEA_IN.GDB also contains a polygon feature class named "OPIOID_DISTRIBUTION_INDIANA_COUNTIES," which provides a summary of county-level analysis of the cumulative data for opioid pills distributed across Indiana.
OPIOID_DATA_ARCOS_DEA_IN.GDB also contains a point feature class named "OPIOID_DISTRIBUTION_INDIANA_CITIES-TOWNS," which provides a summary of a local-level anlysis for opioid pill distribution to cities and towns in Indiana.
The following is excerpted from The Washington Post article titled "How to download and use the DEA pain pills database," (<https://www.washingtonpost.com/national/2019/07/18/how-download-use-dea-pain-pills-database/>):
"The Post gained access to the Drug Enforcement Administration's Automation of Reports and Consolidated Orders System, known as ARCOS, as the result of a court order. The Post and HD Media, which publishes the Charleston Gazette-Mail in West Virginia, waged a year-long legal battle for access to the database, which the government and the drug industry had sought to keep secret."
"The download contains raw data on shipments of oxycodone and hydrocodone pills to chain pharmacies, retail pharmacies and practitioners. You can also download summary data on the largest distributors, manufacturers and pharmacies in your county and state. The summary is one analysis of the data. There is much more that can be analyzed and reported."
"We have cleaned the data to include only information on shipments of oxycodone and hydrocodone pills. We did not include data on 10 other opioids because they were shipped in much lower quantities and were diverted at far lower rates over the seven years. Diversion refers to when pills do not go directly to a patient and end up at another source, such as the black market. The Post also removed shipments that did not wind up in the hands of consumers, such as shipments from distributors to themselves. The subset of the data is very similar to how experts working on the federal court case in Ohio analyzed the data."
"It's important to remember that the number of pills in each county does not necessarily mean those pills went to people who live in that county. The data only shows us what pharmacies the pills are shipped to and nothing else."
"The best way to understand the definitions of the columns and variables in this data set is to refer to the DEA's 'ARCOS Registrant Handbook', (<https://www.deadiversion.usdoj.gov/arcos/handbook/full.pdf>) which provides detailed descriptions of the records."
The following is excerpted from The Washington Post article titled "How to download and use the DEA pain pills database," (<https://www.washingtonpost.com/national/2019/07/18/how-download-use-dea-pain-pills-database/>):
"The Washington Post published a significant portion of a database that tracks the path of every opioid pain pill, from manufacturer to pharmacy, in the United States between 2006 and 2012. We have reported a number of stories using this data set, but we believe there are more stories to be told."
"We are making this data accessible to journalists to download and use in their reporting to promote a deeper understanding of the regional and local effects of the opioid crisis. Academics and readers may also download and explore this data for their own use."
CREDIT - It is requested that the Indiana Geological and Water Survey be cited in any products generated from this data. The following source citation should be included:
[OPIOID_DATA_ARCOS_DEA_IN.GDB: Opioid Pill Distribution Information for Indiana from 2006 to 2012, from the U.S. DEA's Automation of Reports and Colsolidated Orders System (ARCOS), (DEA, The Washington Post, File Geodatabase), digital compilation by IGWS, 20190809].
NOTE: The Washington Post also requests that those who use these data to create online stories, graphics, maps, or other pieces of journalism based on these data please credit The Washington Post, link to the original source article (title and URL above), and send them an email (maria.sanchezdiez@washpost.com) after publication.
LIMITATION OF WARRANTIES AND LIABILITY - Except for the expressed warranty above, the product is provided "AS IS", without any other warranties or conditions, expressed or implied, including, but not limited to, warranties for product quality, or suitability to a particular purpose or use. The risk or liability resulting from the use of this product is assumed by the user. Indiana University, Indiana Geological and Water Survey shares no liability with product users indirect, incidental, special, or consequential damages whatsoever, including, but not limite to, loss of revenue or profit, lost or damaged data or other commercial or economic loss. Indiana University, Indiana Geological and Water Survey is not responsible for claims by a third party. The maximum aggregate liability to the original purchaser shall not exceed the amount paid by you for the product.
The Following is excerpted from online documentation provided by the Washington Post for the tabular data release:
"The Post gained access to the Drug Enforcement Administration's Automation of Reports and Consolidated Orders System, known as ARCOS, as the result of a court order. The Post and HD Media, which publishes the Charleston Gazette-Mail in West Virginia, waged a year-long legal battle for access to the database, which the government and the drug industry had sought to keep secret."
"The download contains raw data on shipments of oxycodone and hydrocodone pills to chain pharmacies, retail pharmacies and practitioners. You can also download summary data on the largest distributors, manufacturers and pharmacies in your county and state. The summary is one analysis of the data. There is much more that can be analyzed and reported."
"We have cleaned the data to include only information on shipments of oxycodone and hydrocodone pills. We did not include data on 10 other opioids because they were shipped in much lower quantities and were diverted at far lower rates over the seven years. Diversion refers to when pills do not go directly to a patient and end up at another source, such as the black market. The Post also removed shipments that did not wind up in the hands of consumers, such as shipments from distributors to themselves. The subset of the data is very similar to how experts working on the federal court case in Ohio analyzed the data."
"It's important to remember that the number of pills in each county does not necessarily mean those pills went to people who live in that county. The data only shows us what pharmacies the pills are shipped to and nothing else."
Each file was individually imported into Microsoft Excel, and three new attribute fields named "Month," "Day," and "Year" were added. The values from the attribute named "TRANSACTION_DATE" were then used to populate the values for the three new attributes. Each record of each file was then sorted by these new fields, and then the files were exported out of Microsoft Excel and saved as comma-separated text files (.CSV).
The resulting 92 county-based text files were then added to Esri ArcMap and exported in the "dBase Table" format and then loaded into the Esri file geodatabase OPIOID_DATA_ARCOS_DEA_IN.GDB. The filenames were then standardized for each county in Indiana (the file "MarionCoIN_ARCOS_DEA" is an example of the file naming convention used).
Both data were loaded into Esri ArcMap and the population values and estimates were joined together into the attribute table of the county polygons (joined using attributes containing the FIPS values for each Indiana county). The data were then exported to a new polygon shapefile named "OPIOID_DISTRIBUTION_INDIANA_COUNTIES.SHP" to conform to naming conventions of the IGWS.
A new attibute field named "PillTotal" was then added to OPIOID_DISTRIBUTION_INDIANA_COUNTIES.SHP. Esri ArcMap "field calculator" was used to calculate these values, which provide the "sum total of the number of pills delivered to each county over the seven-year time frame" (2006-2012).
A new attribute field named "PillTotal_PC" was then added to OPIOID_DISTRIBUTION_INDIANA_COUNTIES.SHP. Esri ArcMap "field calculator" was then used to calculate these values, which provide the "average total number of pills delivered per capita (person) to each county per year," over the seven-year time frame (2006-2012).
A series of new attribute fields named "PillTot2006," "PillTot2007," "PillTot2008," "PillTot2009," "PillTot2010," "PillTot2011," and "PillTot2012" were also added. Esri ArcMap "field calculator" was then used to calculate the respective values, providing the "sum total number of pills delivered to each county per year" for each year of the seven-year time frame (2006-2012).
The polygon shapefile was then imported to a polygon feature class named "OPIOID_DISTRIBUTION_INDIANA_COUNTIES" into the Esri file geodatabase named "OPIOID_DATA_ARCOS_DEA_IN.GDB."
Both data were loaded into Esri ArcMap and the population values and estimates from Indiana incorporated places were joined together into the point attribute table of the places (city/town) layer. The joined data were exported to a new point shapefile named "OPIOID_DISTRIBUTION_INDIANA_CITIES_TOWNS.SHP" to conform to naming conventions of the IGWS.
A new attibute field named "PillTotal" was added to OPIOID_DISTRIBUTION_INDIANA_CITIES_TOWNS.SHP. Esri ArcMap "field calculator" was used to calculate these values, which provide the "sum total of the number of pills delivered to each place (city/town) over the seven-year time frame" (2006-2012).
A new attribute field named "PillTotal_PC" was added to OPIOID_DISTRIBUTION_INDIANA_CITIES-TOWNS.SHP. Esri ArcMap "field calculator" was used to calculate these values, which provide the "average number of pills delivered per capita (person) to each place (city/town) per year," over the seven-year time frame (2006-2012).
The polygon shapefile was imported as a polygon feature class named "OPIOID_DISTRIBUTION_INDIANA_CITIES_TOWNS" into the Esri file geodatabase named "OPIOID_DATA_ARCOS_DEA_IN.GDB."
OPIOID_DISTRIBUTION_INDIANA_COUNTIES is a polygon feature class that contains summary statistics for the opioid pill distribution data, as well as population estimates obtained from the Indiana Business Research Center (originally provided by USCB) at the county-level (92 features). A detailed description for each attribute of this polygon feature class attribute table is also included below.
OPIOID_DISTRIBUTION_INDIANA_CITIES_TOWNS is a point feature class that contains summary statistics for the opioid pill distribution data, as well as population estimates obtained from the Indiana Business Research Center (originally provided by USCB) at the city- or town-level (285 features). A detailed description for each attribute of this point feature class attribute table is also included below.
MARIONCOIN_ARCOS_DEA is an example of one of 92 file geodatabase tables that contain the raw opioid pill distribution data for Indiana, for the seven-year period of 2006 to 2012, arranged by county. A detailed description for each attribute of the attribute table for MARIONCOIN_ARCOS_DEA is included below. (The attribute definitions apply to all 92 file geodatabase tables, but value ranges are specific to the data from Marion County, Indiana.)
CREDIT - It is requested that the Indiana Geological and Water Survey be cited in any products generated from this data. The following source citation should be included:
[OPIOID_DATA_ARCOS_DEA_IN.GDB: Opioid Pill Distribution Information for Indiana from 2006 to 2012, from the U.S. DEA's Automation of Reports and Colsolidated Orders System (ARCOS), (DEA, The Washington Post, File Geodatabase), digital compilation by IGWS, 20190809].
NOTE: The Washington Post also requests that those who use these data to create online stories, graphics, maps, or other pieces of journalism based on these data please credit The Washington Post, link to the original source article (title and URL above), and send them an email (maria.sanchezdiez@washpost.com) after publication.
LIMITATION OF WARRANTIES AND LIABILITY - Except for the expressed warranty above, the product is provided "AS IS", without any other warranties or conditions, expressed or implied, including, but not limited to, warranties for product quality, or suitability to a particular purpose or use. The risk or liability resulting from the use of this product is assumed by the user. Indiana University, Indiana Geological and Water Survey shares no liability with product users indirect, incidental, special, or consequential damages whatsoever, including, but not limite to, loss of revenue or profit, lost or damaged data or other commercial or economic loss. Indiana University, Indiana Geological and Water Survey is not responsible for claims by a third party. The maximum aggregate liability to the original purchaser shall not exceed the amount paid by you for the product.