Investigations
How to download and use the DEA pain pills database
By Steven Rich ,
María Sánchez Díez and
Kanyakrit Vongkiatkajorn July 18
This page will be updated as we address your questions about the data.
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.
How to download this data
Go to the DEA pain pills database page.
Enter the state and county (if applicable) for which you want data.
You can download graphics and/or analyzed data sets for distributors, manufacturers and pharmacies in the area by clicking “Get chart as image.” If you would like to do your own analysis, you can download the raw data file by clicking the bold and underlined link below each chart.
Click here to download the national data from The Washington Post. Note: This is a very large file, so download times may be long.
If you want to download the full data set released by the Ohio federal court, click here. This includes information on shipments of other drugs, shipments between distributors, drugs distributed to mail order pharmacies, refunded shipments, and more.
Guidelines for using this data
Fill out the form below to establish a connection with our team and report any issues downloading the data. This will also allow us to update you with any additional information as it comes out and answer questions you may have. Because of the volume of requests, we ask you use this channel rather than emailing our reporters individually.
If you publish an online story, graphic, map or other piece of journalism based on this data set, please credit The Washington Post, link to the original source, and send us an email when you’ve hit publish. We want to learn what you discover and will attempt to link to your work as part of cataloguing the impact of this project.
Post reporting and graphics can be used on-air. We ask for oral or on-screen credit to The Washington Post. For specific requests, including interview with Post journalists, please email postpr@washpost.com.
About this data set
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.
There are Veterans Affairs Department distribution pharmacies in Charleston, S.C., and Leavenworth, Kan., that serve the region. The DEA considers these to be retail pharmacies. However, the pills are much more likely to be spread in the region than to those counties. This is why the per capita rate of pills in those counties is far higher.
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 downloaded files are formatted as .tsv with fields separated with tabs. You will need software, such as 7Zip, to unzip the file. Once the file is unzipped, you should open the document using SQL, Python or R. We do not recommend converting the file into a .csv because there are commas within the data fields. We also do not recommend using Excel; the program can only open files in which there are less than a million rows, so the full data set will be inaccessible.
If you are using a slow or old computer, we recommend that you download the summary files on the database page. This will give you a smaller data set geared to whichever fields you are interested in, such as county or state.
The best way to understand the definitions of the columns and variables in this data set is to refer to the DEA’s ARCOS handbook, which provides detailed descriptions of the records.
Steven Rich
Steven Rich is the database editor for investigations at The Washington Post. While at The Post, he has worked on investigations involving the National Security Agency, police shootings, tax liens and civil forfeiture. He was a reporter on two teams to win Pulitzer Prizes, for public service in 2014 and national reporting in 2016. Follow
María Sánchez Díez
María Sánchez Díez is an operations editor in Local. Before joining The Washington Post, she worked at ProPublica's Electionland project and at Univision, where she was a general assignment editor focused on digital storytelling and audience engagement projects aimed to serve the Spanish-speaking immigrant communities in the US. Follow
Kanyakrit Vongkiatkajorn
Kanyakrit Vongkiatkajorn is the community editor at The Washington Post, with a focus on comments, live chats and reader submissions. She comes to The Post from Mother Jones, where she was the assistant editor for audience and breaking news. Follow