How to download and use the DEA pain pills database | Hacker News Reader

How to download and use the DEA pain pills database

washingtonpost.com

61 pointsdanso36 comments

As a chronic pain sufferer, opiates are a lifeline. Problem has been indescriminate use pushed by docs before they became fully aware of the risks. Now I jump through hoops to get a fairly low dose of hydrocodone 7.5. I have both rheumatoid and osteoarthritis, fibromyalgia and a host of other diagnosis. Without effective pain management, life isnt worth living. I'm only 58!

Sad to say, many doctors in our area have stopped prescribing and shunt patients over to pain specialists because they do not want to lose their license.

The number of pain specialists seems to be dwindling due to DEA harassment/regulation.

In the denser urban areas, it is almost impossible to get opioids due to heightened DEA scrutiny. You have to travel outside of those areas. A friend got a surgery downtown and was only able to get a token dose at the hospital pharmacy. She was told she could likely only get the script filled where she lived (an hour outside of town).

This is what happens when a law enforcement agency is put in charge of who gets what medicine.

You must be putting up with a lot, sorry to hear that.

For as much as opioids are demonized, there are a lot of people out there who can’t function without them and use them responsibly. Of course they are getting caught up in the moral panic and either being forced to stop taking them or significantly reducing their dose, resulting in a much lower quality of life.

What sort of hoops do you have to jump through?

I cant speak for Whiskers, but my father, who is almost seventy and takes a similar dose of the same medication, is routinely forced to jump through an unreasonable amount of hoops just to get a recently-lowered prescription that barely lasts throughout the month. I say "barely lasts" because his new prescription always seems to arrive a few days late.

And the doctors could care less. Not only that, but if he complains about his medicine arriving late or about his dose being constantly lowered or about his treatment, then he is treated even worse.

The "hoops" I am referring to are: frequent drug tests, frequent classes/counseling an hours drive away, frequent alternative and experimental treatments an hours drive away, all the while being blatantly accused of being a drug-seeking addict.

My father is the most upstanding man I know. He is a Vietnam Veteran with related injuries who receives his treatment at the VA (which is the most inefficiently-run place I have ever witnessed. Seriously its a complete embarrassment. The arrogant doctors who know absolutely nothing about pain management, and who make no effort to hide that they care less doesnt even begin to cover whats wrong with that place..but I digress..). My father only agreed to accept opiates as a last resort. He has never had issues with alcohol/cigarettes/marijuana or any other drugs. He is an honest, church-going, hardworking gentleman who doesnt deserve to be humiliated multiple times per month to just live a nearly pain-free life.

This recent trend to eradicate opiates, while somewhat understandable, is leaving many good people with a life of constant severe pain.

Sorry for the rant. I got started, and I couldnt stop.

> Not only that, but if he complains about his medicine arriving late or about his dose being constantly lowered or about his treatment, then he is treated even worse.

This is totally my experience. If you want to know why people die trying to stretch out their supply of insulin, that's why. They know they'll be punished for being too much of a pain in the ass. My friend with epilepsy carefully hoards his drugs. He knows he needs to be able to skate at least two weeks on his own.

Does being an upstanding, church-going citizen prevent becoming addicted to the most addictive drug known?

Good point, and not at all. This is a charged subject and I guess I was throwing in some facts to support my statement that he isn't a drug seeker. My point is that he doesnt deserve to be humiliated every month just to live a somewhat pain-free life. Something I feel very strongly about, but you are totally right.

No. The whole paragraph shows what most people would deem a life of sacrifice, good decision making, and positive societal influence which juxtaposed against the treatment and risk of pain is a strong example of a system that is broken.

http://archive.is/1MA0P

I'm not sure if WaPo just made their paywall more aggressive, but I can't seem to read this URL now w/o logging in. They do provide a direct URL from Cloudflare to the raw data, which is about 7GB compressed and uncompresses to a single tab-delimited text file with 180M rows at 80GB:

    https://d2ty8gaf6rmowa.cloudfront.net/dea-pain-pill-database/bulk/arcos_all_washpost.tsv.gz

There is also individual state level data with the following format (replace `il` – i.e. IL for Illinois – with the state postal code of your choice):

    https://www.washingtonpost.com/wp-stat/dea-pain-pill-database/summary/arcos-il-statewide-itemized.tsv.gz

That said, the data is fairly hard to understand without reading the article so it's likely much more efficient to login to read it and download. Here's a tweet thread with context from the WaPo's data editor:

https://twitter.com/dataeditor/status/1151904218095194112

And here's a 10K row random sample to preview in Google Sheets:

https://docs.google.com/spreadsheets/d/19NZtjOEPxNdNfZS9l-yZ...

I was able to read it by disabling Javascript.

I am able to read the site without trouble. I have all cookies from washingtonpost.com blocked though, so I don't know if that's what lets me get through.

I really wish the WaPo app opened urls from their site. I use Firefox focus so I'm never logged in, but I am logged in the app.

For 10 years I had Hydrocodone as a treatment for pain. Truth be told it also helped with my energy levels. I was functioning well. At 60 diagnosed with RA and a number of other immune disorders. That was at the time of Feds starting to crack down. I could probably find doctors to prescribe the pills , but after a pain chest & back sent me to ER , a GI scope showed changes to the ducts that run to the pancreas. The Anesthetist said pain pills do that ( said with no judgement & said if you need them then take them ) . But I thought , i’d try to get off them. My doctor weaned me down. But my quality day to day is gone. No energy , pain without relief . Illinois medical cannabis is not my answer , been trying. Some strains actually increase body awareness therefore increasing pain. So I’ll probably have to change doctors if mine won’t put me back on them.

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

More people forced into the black market, good job guys. I feel bad for people who get addicted but also feel personal responsibility and choice should be paramount. You should be able to decide for yourself.

IMO they're working to construct a grey market here. As far as I can tell, the "opiate crisis" has become a thing because vested interests wanted to build out a lucrative specialization in "pain management". Florida enacted a law last year severely restricting the ability of ordinary doctors to prescribe opiate medications, legally requiring anyone who needs more than a 72 hour supply to seek it from a separate specialized pain management clinic.

Woah... just woah, so am I mathing this wrong, or from 2006-2012 there were ~250 pain pills per person (man, woman, child) sold in America? Because if so, I don't know how on earth any executive couldn't be fucking liable for knowing the vast majority being manufactured and sold were being used illicitly.

I was reading another discussion among physicians about this and the number isn't that excessive.

50M Americans have chronic pain, assume 10M (20%) are on opioids. 76B pills over 10 years is 7.6B per year, so 760 pills per [chronic pain + opioid] person per year. Take 4 pills per day or 120 per month or 1440 per year. So basically 6 months of therapy for every chronic pain patient in the US.

But that's one of the big lies - or minimally very misleading marketing: long term opiate treatment isn't as remarkably effective for chronic pain as Purdue had us believe and carries a LOT of downside: https://www.ncbi.nlm.nih.gov/pubmed/30313000

I have a relative with 2 artificial hip replacements and many issues with his other organs which were injured durring a war from machine gun fire in Vietnam. About 8 months ago his opiate pain meds were reduced by over half. We now often visit and help him with chorrs as he can barely move. Opiates are a very important treatment and there is a stigma currently hurting needing patients.

> For a number of Key Questions, we identified no studies meeting inclusion criteria. Where studies were available, the strength of evidence was rated no higher than low, due to imprecision and methodological shortcomings, with the exception of buccal or intranasal fentanyl[...]. No study evaluated effects of long-term opioid therapy versus no opioid therapy.

> No study evaluated the effectiveness of risk mitigation strategies on outcomes related to overdose, addiction, abuse, or misuse. Evidence was insufficient to evaluate benefits and harms of long-term opioid therapy in high-risk patients or in other subgroups.

> CONCLUSIONS: Evidence on long-term opioid therapy for chronic pain is very limited but suggests an increased risk of serious harms that appears to be dose-dependent. More research is needed to understand long-term benefits, risk of abuse and related outcomes, and effectiveness of different opioid prescribing methods and risk mitigation strategies.

This doesn't show they aren't effective.

Your source doesn’t say they don’t work for chronic pain, it says there isn’t data and there are risks.

Unfortunately for a lot of patients, opioids are the only thing that works.

Very true. It's also true that Medicare and Medicaid, used by the majority of disabled and elderly does not provide coverage for most alternative therapies. Medicare recently approved 'dry needle' treatment, a less-effective cousin of acupuncture. Massage therapy is a proven pain reducer but not covered at all. The list goes on.

Some of the issues activists are still working on are quality of life aspects of health care: for instance, Most policies will pay for the amputation of a diabetic patient's foot but NOT for a $50 pair of properly fitting shoes.

This is my 2 cents: I was looking for access info on the DEA database referenced here and found your discussion interesting. Thanks for the tips on getting past WAPO's paywall!

1 in 6 Americans are in chronic pain?

I (non-US) am on (up to) 8 per day, or 2920 per year. To get an average of ~42 pills per year (250/6), it only requires 1 in 70 to be on a similar regime (2920/70 ~= 42). Given that 8% of American adults have high-impact chronic pain (frequently limits life or work activities)[1], that doesn't sound that excessive.

Sanity check: 19.6 million people * 4 pills per day * 6 years ~= 172bn pills. That's over _double_ the actual number, suggesting high-impact chronic pain sufferers are, on average, only taking 2 pills per day.

[1] https://www.cdc.gov/mmwr/volumes/67/wr/mm6736a2.htm

Edit: I overlooked that this only covers oxycodone and hydrocodone, and not the other opioids, but as they account for 3/4 of the total opioid pill shipments[2], it doesn't change the picture much (76bn * 4/3 ~= 101bn, which is still well under the number I posited above).

That's not to say there isn't an issue with diversion, but in terms of raw numbers, it doesn't seem excessive.

[2] https://www.washingtonpost.com/graphics/2019/investigations/...

Everyone has plausible deniability that theirs follows a secure supply chain to the pharmacy. Its those other guys that can’t control their inventory.

Nobody has an incentive to audit their supply chains or doctor RXs to verify that of course.

Sorry, how does this relate to the topic?

> I don't know how on earth any executive couldn't be fucking liable for knowing the vast majority being manufactured and sold were being used illicitly.

The answer is plausible deniability. Nobody in the chain has specific knowledge of wrongdoing and nobody seeks it out.

People with chronic pain will usually need multiple pills a day. And some of these pain pills might lose their effectiveness with long term use. What alternatives do we have for these people?

Doctors prescribed them, so who cares?

I personally think we should just be able to buy any drug we want at a convenience store, but what do I know?

Doctors prescribed them unnecessarily under perverse incentives from pharma leading to the addiction and deaths of innumerable people who trusted them to do the right thing, that's what you know.

That would have a negative effect on how useful our modern antibiotics are.

You know, that's one of the few valid points against the legalization of all drugs. I don't care how many die or overdose or whatever (it's a moral issue of owning one's own body, much like abortion) but antibiotics seem to throw a wrench into the whole thing. By misusing antibiotics, you are not just affecting your body, but everyone else's on earth as well.

An interesting problem that I still haven't resolved.

I hope you're joking. You need to go to school many years to understand drug interactions.

Reply on news.ycombinator.com