Health insurers just published close to a trillion hospital prices
dolthub.com
dolthub.com
Many of the private health providers are for-profit and lobby against rule changes that would reduce complexity and save the system money. It know this may sound glib, but if you are trying to understand the US healthcare system and something seems strange, usually it's because it makes someone money and they'll fight hard to keep it that way.
And it's not just some money but very BIG money they make.
This is almost certainly an anti-competitive move. By keeping many rules and regulations, you need more staff to deal with them - and smaller insurers have fewer patients to amortize those salaries over.
So your claim of public + private being the issue makes no sense when almost every country has that.
For procedures covered by provincial health programs, doctors in private practice (GPs, at least some surgeons) give the service to the patient and then bill the government for that amount. For services that aren't covered by provincial health programs, GPs/surgeons bill the patient.
Provincial health care programs cover anything medically necessary. My general understanding is that you cannot pay to have a service done which is covered by a provincial health program. The intent is to prevent the establishment of a two-tier health care system.
It's probably less complicated than the US system, but it's still complicated.
But, when publishing, they omit the context and just dump every negotiated rate. Because it's technically compliant, but keeps things opaque.
They basically denormalized all the dimensions.
Imagine you have a function which takes 5 arguments and returns one value. You could give me the source code and let me run this function. Or you could give me a mapping of every possible combination of the 5 inputs to the returned value. The former could be quite small, but the latter would be a massive number of rows.
If I understand correctly, in this case, that function's source is highly distributed in wetware. It's about as closed-source as it gets; nobody has anywhere near the full source. Each hospital is its own fiefdom!
But even if you had like 10M rows of pricing and then gave a 2% discount to entity A, 3% discount to entity B, 4% discount to C, etc.
You could publish these discount rules.
Or you could just multiply the 10M rows by the number of different entities giving 10*n M rows.
And then let the consumer of the data try to figure out the rules from the output...?
When it comes to large parties, multi-practice groups, health systems, etc, an overall fee schedule or charge master for an existing institution is typically not renegotitated line by line every year but as incremental changes from the previous. Many/most of the parties involved have been working together for decades, some even longer.
Many plans administered by familiar names like Anthem are actually funded and controlled by the large employers the plan services. In those cases the employer plays a role in defining what will and will not be covered and what will be paid and the insurer is a middleman (acting as a third party administrator).
Hospitals are an entirely different system. They have much more negotiating power and if an insurer has a customer that goes to a hospital emergency room outside of their contract, the insurer has to pay outlandish rates. So it is in the insurer's interest to make a deal. They achieve this by inventing different 'products' with different amounts of 'coverage' for different premiums. Each of these 'products' had their own negotiation and their contracts.
Price transparency is the first good thing that has been mandated. However, this misses the mark. The focus is the patient, not the insurer, the hospital or the physician. Accordingly, patients should be allowed to submit their explanation of benefits and their bills-this is the data that reflects the true cost of healthcare. All of the numbers provided by hospitals, insurers and physicians has been massaged and buried in a forest of minutiae.
The end result is that you might end up with an individual doctor having to work with the insurance company for pricing, so the same procedure can cost vastly different amounts at hospitals down the road from each other providing the same level of care. To make it worse we also have laws preventing healthcare providers from providing prices upfront, out of a fear that people will forego necessary care they can't afford.
Edit: seems like this changed 01-01-2021, now we do have some price transparency laws - https://www.cms.gov/hospital-price-transparency
What are these laws? This seems so backwards - I know personally I have put off medical care in my past because I had high deductible insurance, and no guarantee that the bill I'd get wouldn't wipe me out, and no way to price shop. Paralysis of unknown.
https://www.cms.gov/hospital-price-transparency
Now assuming the hospital is compliant the information should be available. To be fair my understanding of the argument for the old law was that you didn't want a hospital with a big sign out front saying "Broken arm repair: $10k" and having people not go in for it when there might be some financial aid they could get afterwards
The real economy has way many more prices than this one - from each store of anything in the country that negotiates from straws to bread. The difference is that these ones happen in a system that has a paper trail from the doctor, to the insurance, and this admin burden is only (apparently) worth it because the vast majority of money in healthcare goes through tax-advantaged insurance.
Cash based payment should suffice for 50~70% of healthcare expenditues and it would have more prices and not have expensive and abusive billing processes.
Also! What did they do before they could store 100TB of pricing data? How has pricing (and care quality) changed as a result of being able to do this type of thing?
Maybe you have a couple one off negotiations for high volume procedures, but even still the source data could be several orders of magnitude smaller than the dumps.
It's very easy for people to forget the scale of the US "health system", we are talking 1/5, maybe more, of the entire US economy. If US healthcare spending were a country, it would have the third largest GDP in the world. Accidents of history and the massive federal beauracracy created the crazy monster of ICD/CPT codes that results in the very clumsy way of pricing healthcare services that results in this massive matrix of data.
As pointed out elsewhere there is a tremendous amount of cost distribution that goes into the code matrix and this plays a large role in negotiations with health insurers as well. Ground is given in one set of procedures and lost in others.
This is a big step in shining light into areas that need it to improve the system overall.
Also wondering what you think a solution is - single-payer for better and simpler price negotiations, or some other approach?
My main concern is if we're spending 20% of GDP on something other countries accomplish with 10%, then that's a huge waste, especially in a country with a larger total GDP pool.
They're most obvious with "basic" products like energy and comms - in theory what is delivered is mostly undifferentiated kWh or GB but through the magic of "confusing plans", marketers have succeeded in making comparisons very difficult for regular people.
(You can do it, but you need OCD, a year of billing data and a spreadsheet - which greatly exceeds the cognitive effort most people will invest in choosing a mobile or energy provider).
The US healthcare sector seems to be the largest, most intricate and most successful (in terms of gdp extraction) confusopoly in history.
Also a marginal Joule that you can demand at will is different from one that you committed to months in advance.
Similarly for data.
Of course, in practice most plans don't reflect this 'essential' complexity, but are full of accidental complexity to confuse people.
Academically this sounds enlightening, but it only takes one cursory walk around a supermarket in the US to see this is unequivocally false. Healthcare is an externality, not a base of anything. From the average customer to the product in the aisle to the marketing - everything is 100% not a direct cost benefit function in terms of healthcare.
Unfortunately I think your comment is even less enlightening.
I mean, not all healthcare problems are caused by lifestyle. So clearly healthcare is a base layer - there is no situation where it wouldn’t exist.
do you have evidence to support these claims? what makes the US patient population or their expectations or the US taxation system unique in the world?
A part of the latter is based on the actual superiority of the quality of medical care in this country -- due to the high levels of wealth produced by this (mostly market-oriented) economy and advanced medical technology, doctors can in fact perform miracles here that they cannot elsewhere.
This is a big thing. I'm in the UK, where healthcare is very socialised, but I very much appreciate the fact that the US invests in making and productionising the next generation of healthcare, which we can then buy in bulk at a discount.
I've been hospitalized in four different countries. The least sane was America. The sanest was a private hospital in England, but the public hospital in England was fine too. My home country of Canada is sane, reliable, and reliably slow and mediocre bordering on subpar. Cyprus lacked toilet seats, but at least the food was fantastic.
America's healthcare system is bananas. Even trying to come up with a metaphor here is difficult. It's $5k a day stays with Wonderbread, tuna, and bad not-actually-mayo-mayo for lunch. It's well groomed, well respected, monied indentured second and third opinion servants. It's Moloch's own mediation on Moloch[0] sold on the discount rack of the bookstore pharmacy downstairs.
You can think Americans are different. They are not. They move to Canada all the time and we service their bum knees just fine.
You can think Americans do all the medical research in the world. They don't. Plenty comes out of Europe, China, and elsewhere.
You can think your tax code is unique. Ok this one I kinda agree with. It's almost as bananas as your medical system. But it doesn't change the fact that Americans put up with absolute bananaspants insanity for a healthcare system when they're perfectly capable of funding their libraries and roads.
China beats you on scale. And so on.
The basic fact is that Americans have what is essentially a psychopathic medical system at the best of times. One can negotiate with a psychopath, but Kafka returns your offer with a can of stale soup and doesn't even laugh.
[0] https://slatestarcodex.com/2014/07/30/meditations-on-moloch/
It's also hard to explain that US patients pay a multiple of the drug price people in other parts of the world pay for the something.
The problem is that if the US wastes 10% of GDP on health care inefficiencies this creates a huge lobby that will fight tooth and mail to keep that money.
What we all forget is that nobody is actually paying the US healthcare invoices.
Roughly two options... 1. You have insurance and they negotiated a different (much lower) rate or 2. you don't have insurance and can't pay the amount on the invoice.
In option 2 you either declare bankruptcy and they get nothing or they sell the claim for something like 20% of the invoice to a collections agency.
Also: if you make a certain amount of money and they hit you with a 100k bill you can't just declare bankruptcy. The court won't allow you to do it because you make too much money.
I work in the industry as well and it’s one of the few reports that actually breaks down the spending in a logical way.
They basically adjust US spending by GDP (high GDP countries spend more generally) then compare each category to the OECD average (also adjust by GDP), on a price and volume measure.
The answer is - yes, higher price are a factor, but volume is also a major factor. In hospital spending is actually in line with other countries. Drugs costs more but it doesn’t contribute that much to total spending. In terms of durables (equipment) the US spends less.
The biggest driver? Out patient procedures. Not just price, but Americans get way more out patient procedures done compared to other countries and it accounts for like half of the “excess spend” of the US compared to other countries.
We might be able to eke out some minor improvements by tweaking the payment model and eliminating some waste. Those things are worth doing, but they won't fix the fundamental problem. The US won't get healthcare spending down to Japan's levels until Americans start acting like Japanese.
There are some other key factors as well. A large fraction of healthcare spending goes toward treating elderly patients with serious chronic conditions in their last few years of life. Some countries explicitly deny care to such patients because they don't think it's justified on a QALY basis, but Americans seem uncomfortable with rationing on that basis.
And some aspects of the US healthcare system are top notch. For many types of cancers we have the world's best 5-year survival rates. There is a thriving medical tourism business where patients from countries with socialized medicine such as Canada come here to receive rapid treatment instead of waiting for years for something like a hip replacement.
That we spend more per capita for approximately the same level of care as most other first world countries is certainly annoying. But sometimes I think we are too focused on that and not putting enough effort into trying to stop the cost from increasing.
I think increasing costs are a more serious problem because the problem of spending so much more than the others is a US problem. That suggests it is just something we are doing wrong, and by making our system more like some of those others we can fix it.
The problem of rising costs also plagues those other countries, and to about the same extent as it does the US. That suggests it is a much harder problem to solve.
Here are some examples of rising costs per capita.
How much costs per capita went up from 2000 to 2018: US 2.3x, Germany 2.1x, France 1.8x, Canada 2.0x, Italy 1.7x, Japan 2.6x, and UK 2.6x.
Costs per capita in 1980, 1990, 2000, 2010, and 2020 divided by 1970 costs:
1980 1990 2000 2010 2020
US 3.2 8.2 13.9 24.1 36.3
UK 3.1 6.3 15.3 27.8 40.5
FR 3.4 7.6 14.9 21.1 28.5
1980 1990 2000 2010 2020
US 3.2 2.6 1.7 1.7 1.5
UK 3.1 2.0 2.4 1.8 1.5
FR 3.4 2.2 2.0 1.4 1.4
If "latest data available" is checked, uncheck it to unlock the slider that lets you look at historical data back to 1970.
In 1970 US health care spending was $327.0/per capita. France was $192.1, and UK was $124.0. That's 1.7x France and 2.6x UK for the US.
In 2020 it was $11859 for the US, $5468 for France, and $5019 for the UK. That puts 2020 US spending at 2.2x France and 2.4x UK.
From 1970 to 2020 US went up 36x, France 28x, and UK 40x.
It looks like much of the first world has a serious rising health spending problem, with costs rising roughly the same over time everywhere. The US was more expensive long ago, and since the rising costs have been roughly the same the US has stayed more expensive by about the same ratio.
If we could get our spending down to match the rest of the first world, without reducing the level of care, that would be great.
BTW, it is similar if we go by percent of GDP instead of per capita.
US was spending 6.2% of GDP on health in 1970, France 5.2%, and UK 4.0%.
In 2020 that was 18.8% of GDP for the US, 12.2% France, and 12.0% UK.
On-shoring that research also seems to be an advantage -Looking at the astounding amount of research that poured into covid post 2020 would show that we have a huge dormant muscle that can be flexed in unison during an emergency.
https://www.ahip.org/news/articles/new-study-in-the-midst-of...
Generally in business marketing budgets should generate more sales than they cost (in ideal case), so big budgets doesn’t mean that they’re “wasting” that money that could go elsewhere. If the sale wouldn’t happen without an ad, then that’s a necessary ad.
I don't think that money is necessarily a waste if it goes back into the economy one way or another. There are very few things that are actually a waste, one example is probably flying first class or private jet. If you literally burn money then it's clearly a waste. A part of me thinks the huge cost of healthcare is contributing to more R&D by the big pharma and possibly the reason we're seeing RDNA breakthroughs. Yes a lot of that money also ends up in the pocket of people running the show, but they most likely then invest it with a Blackrock which in turn pushes the money back into the economy in form of private equity, VC funds, etc. For the record I don't like the high healthcare prices and wish US was more similar to other countries in this regard.
In comparison to France, Germany, Belgium, the Netherlands, etc I would disagree.
So maybe thats where the extra cost goes? To drive research, and support the infrastructure that creates good to better health outcomes on average vs the rest of the world.
Saying something is a failure just because it costs so much is only looking at one side of the coin.
In particular life expectancy is very low by developed world standards and deaths from preventable causes are very high.
I don’t doubt that the US has world leading hospitals but the population level outcomes delivered are poor by developed world standards.
The US is a net exporter of healthcare services but mostly to developing countries and the numbers involved are tiny (https://www.usitc.gov/publications/332/executive_briefings/c...)
The idea that people who want any sort of complex procedure travel to the US is pure fantasy.
As for the COVID-19 vaccines. The first approved vaccine was the “Pfizer” vaccine developed by BioNTech in Germany (https://en.m.wikipedia.org/wiki/Pfizer–BioNTech_COVID-19_vac...).
This isn't true, the US didn't make the first vaccines, it was created by a German company that partnered with Pfizer for trials/production/distribution[0]. People more often travel outside the US for care, we actually have the second highest amount of people leaving their home country for medical care[1], with the top destinations being in South America [1a].
> creates good to better health outcomes on average vs the rest of the world
This would be nice if true, but we spend more and still have worse outcomes in almost every area[2]. The only things I could actually find that are better here is post-op sepsis and 30-day heart attack survival, but in just about every other area it's more dangerous to get care in the US.
[0]: https://en.wikipedia.org/wiki/History_of_COVID-19_vaccine_de...
[1]: https://www.health-tourism.com/medical-tourism/statistics/
[1a]: https://amjmed.org/medical-tourists-incoming-and-outgoing/
[2]: https://www.healthsystemtracker.org/chart-collection/quality...
Of course the biggest issue of all time is that germs are evolving to survive our antibacterial soap, we may need to develop a large variety of antibiotics.
You're conflating 'capitalism' with 'free market'. You can have either without the other and OP was calling out 'capitalism' specifically.
1) Feverent, almost religious, adherence to hand washing. 2) No neck ties or dangly sleves whatsoever in buildings that house patients. 3) Change from stainless steel hardware for doors and travel touch surfaces back to "brass/copper".
Those are simple, virtually free, things that have a very meaningful impact on outcomes. Some of the most viscous fights I've had with hospital boards were over what amounted to the "uglier look" of copper/brass.
It is an extremely unpopular topic in healthcare but the area that takes a lot of effort to solve but also has a tremendously out-weighted benefit is reducing preventable medical errors. My opinion after being in healthcare ~20 years is that preventable medical error is absolutely in the top 3 causes of death in the US. The easiest subset of it to resolve is prescription related errors, we have all the tools to resolve those but not the will.
Because of the pandemic I started encountering doors that have a shoe pull, where you can use your foot to open the door instead of having to touch the handle. I really hope these catch on, but they are still quite rare.
I have never heard of this. I had to Google it to even understand your meaning. It's eye-opening to learn that different metal surfaces have an effect on the spread of germs.
Am I the only one who finds copper/brass much more aesthetically pleasing than plain and boring stainless steel?
It is hard to compare details of the systems and outcomes across countries, but surely we can find where the money apent ends up? Construction firms? Doctors? Equipment manufacturers? Hospital administration?
This is anecdotal but the number one complaint I've heard from physicians about patient care is facilities being run and and managed by non-clinical MPH/MHA "business types" whose primary focus is almost invariably cutting costs, increasing physician workloads, and fighting salary increases tooth and nail.
Where in the world did you hear this? Don't trust anything else that person told you.
Aside from some low-income clinic hours for certain specialties (which is objectively a societal good, not to mention typically specific to a given specialty, e.g. OBs have an OB clinic not primary care) no doctor is spending 6 years of "excess schooling residencies" learning anything.
Med school is 2 years of classes then 2 years of rotations where the students - who aren't yet doctors - do 4-12 weeks of rotations through various core and elective specialties. After they graduate they're now doctors but have 3-6 years of specialty-based residency training where for 80-100 hours a week, 50+ weeks a year, they do nothing but their specialty. ACGME limits weekly hours to 80 (I think over a 2-3 week average), but 90% of the doctors I know said they regularly broke that and just didn't log the extra time.
Especially in surgical residencies, all you're doing is your specialty-specific stuff during that period of those.
You just said what he said, but with emphasis on 100 hour weeks for years on end being good instead of bad. Why did you disagree with me, then go on to list how much doctors work before the get to practice on their own? His point was they get too much training, with much of it being irrelevant (not all). If you're this angry and reactive, you really shouldn't be a doctor.
People here seem to love the NHS. In the UK, doctors are not forced to study something irrelevant for four years in college, then do med school, then do a 4 year residency (i.e. age 30). They are often done by age 24, and ready to help.
Standard Oil had a lot of different shareholders, John D. Rockefeller was never majority owner, that was an organization AMA is an organization there's now medical families. Common heirs. Medical students from a medical family get little hazing compared to the rest. All the maneuvers they make to avoid the words "monopoly" and "cornered market" are of no help and mitigate nothing. So they know people get fucked off with those words, like bad, they're afraid of those words.
Erm this is incorrect given that ICD are international it's actually the WHO that creates them source:
https://en.wikipedia.org/wiki/International_Classification_o...
It does not seem reasonable that 20% of a countries economy is spent on health care.
Just as it doesn't seem reasonable for the cost of healthcare to be ~12% of the household income for a family (third highest living expense).
I'm akemendo at the google mail service
For context, we bundle the 100's of itemized costs into a single, static bill per surgery type. In doing so, we've built a custom virtual-network with the most efficient surgeons. These surgeons are able to meet the volume and quality requirements to allow for lower margins. We're able to get negotiated rates that are 10-40% cheaper than traditional insurance contracts when we have data that we trust.
Unfortunately, this data alone isn't enough to properly determine prices because organizations will spread costs across procedure and billing codes that often occur in aggregate groups. For example, in a joint replacement surgery, some organizations may dump the cost into the billing for the implant itself, while others may put it under the procedure code. You have to gather billing data en masse to see which charges occur together, then combine this pricing data to determine what costs will actually look like for someone experiencing a procedure.
It's a nightmare!
* Small hospitals in low-density, underserved areas have to make up for underutilized equipment and personnel costs. They raise prices on unrelated, common procedures to break even (This is very common)
* CMS (medicare/medicaid) sets a low price for a procedure that's overly common in a particular facility, now that facility loses money for each occurrence. They choose other procedures to raise the price to try to break even.
* Larger hospitals have higher administrative and operations costs (for things like training and research) that benefit society, but need to be averaged out across all procedure costs. This differs from hospital to hospital.
* Smaller professional facilities or physicians groups (like Ambulatory Surgery Centers) have much lower administrative costs and a smaller staff, so they have lower overhead per procedure. They are designed to be efficient, and can handle lower prices. However if there are any major complications, they won't be able to service the patient, and have to send to a hospital. This then pushes all the highest-cost, ICU-type procedures into hospitals, where there is already a higher overhead, causing hospitals to need separate pricing to cover more complex patients.
A large single payer price set will probably force efficiencies into the healthcare system. It'll be great for folk's costs, but we may see many facilities close, and lines of care will be consolidated into specialty centers. (more travel to get imaging, procedures, or to see a specialist)
This is precisely why most Doctors I speak with are abhorrently against a single payer system.
The net cost of insurance represents 6.4% of all healthcare spending.
https://www.ama-assn.org/delivering-care/patient-support-adv...
Is the data partitioned at all (e.g. by state) so that you can just download the data for California without downloading all the data; loading it into a huge database table; and then querying it (e.g. SELECT * from <table> WHERE state = 'California')?
The data is partitioned for some carriers at the network level, but unless that carrier has networks that are unique to a given state it's difficult to partition by location.
The majority of the data is lumped into very large, single JSON (not newline delimited), so an initial parsing step is required to break out substructures for parallel processing via warehousing technologies. I think Aetna has a 300Gb compressed (single) json file.
After breaking the json to a single array entry per provider/network, parsing is still a bit tricky because there are some very "hot" keys. Some provider array entries may only have 1000 code and cost entries, others may have 100k. We've seen array entries >50Mb for a single provider/network/carrier.
https://drive.google.com/drive/folders/1zmNEPoVCa0kIVBIu2hu7...
{'REPORTING_ENTITY_NAME': 'Humana Inc',
'REPORTING_ENTITY_TYPE': 'Health Insurance Issuer',
'LAST_UPDATED_ON': '2022-08-24',
'VERSION': '1.0.0',
'NPI': '1629053517,1659354272',
'TIN': '593279318',
'TYPE': 'ein',
'NEGOTIATION_ARRANGEMENT': 'ffs',
'NAME': 'Nasal Prosthesis Replacement See Also Code 21087',
'BILLING_CODE_TYPE': 'CDT',
'BILLING_CODE_TYPE_VERSION': '2022',
'BILLING_CODE': 'D5926',
'DESCRIPTION': 'Nasal Prosthesis Replacement See Also Code 21087',
'NEGOTIATED_TYPE': 'negotiated',
'NEGOTIATED_RATE': '906.98',
'EXPIRATION_DATE': '9999-12-31',
'SERVICE_CODE': '',
'BILLING_CLASS': 'professional',
'BILLING_CODE_MODIFIER': '',
'ADDITIONAL_INFO': '',
'BUNDLED_BILLING_CODE_TYPE': '',
'BUNDLED_BILLING_CODE_VERSION': '',
'BUNDLED_BILLING_CODE': '',
'BUNDLED_DESCRIPTION': ''}
"reporting_entity_name": "Excellus BlueCross BlueShield",
"reporting_entity_type": "Health Insurance Issuer",
"last_updated_on": "2022-06-14",
"version": "1.0.0",
"provider_references": [
{
"provider_group_id": 302.1518360704,
"location": "https://mrf.healthsparq.com/exc-egress.nophi.kyruushsq.com/prd/mrf/EXC_I/EXC/providerReference/Providers/S-000000001063.json"
}
],
"in_network": [
{
"negotiation_arrangement": "ffs",
"name": "Brief (20 minutes) care management home visit for an existing patient. for use only in a medicare-approved cmmi model. (services must be furnished within a benefi",
"billing_code_type": "HCPCS",
"billing_code_type_version": "2022",
"billing_code": "G0081",
"description": "Brief (20 minutes) care management home visit for an existing patient. for use only in a medicare-approved cmmi model. (services must be furnished within a benefi",
"negotiated_rates": [
{
"negotiated_prices": [
{
"negotiated_type": "fee schedule",
"negotiated_rate": 51.1,
"expiration_date": "9999-12-31",
"service_code": [
"11"
],
"billing_class": "professional"
}
],
"provider_references": [
302.1518360704
]
}
]
},Negotiation arrangement: for fuck’s sake
"Oh, they're going to force us to publish our prices are they? Well we'll publish so much data it'll take a herculean effort to make it readable to anyone that doesn't work in data engineering"
Linking a few trillion records doesn't seem that difficult. It should be doable with a good data warehouse and a reasonable entity linking model. I suspect that we'll find more than a few instances of fraudulent behavior once the data is linked.
My father was nearly pushed into ~2 Million dollars worth of brain surgery that was unnecessary. Not only was the procedure unnecessary, the price for it was >5X what a top-3 hospital would have charged. I only became privy to this once I pushed him to come to Mass General Hospital (MGH) for a second opinion. The surgeon we saw at MGH also believed the suggested procedure to be dangerous.
I wonder if it's possible to cross-reference mortality/complication rates with prices...
with open(filepath) as fd:
first_line = fd.readline()
cols = []
for col in first_line.strip().split(','):
col2 = f'''"{col.strip('"')}" text'''
cols.append(col2)
cols2 = ','.join(cols)
print(f"create table {table_name} ({cols2});")
print(f"\copy {table_name} from '{filepath}' csv header;")
run the output in a psql instance connected to your db
(important note: \copy is a psql client command and it is critical to use \copy instead of COPY in many cases where the server process may not have the permission to read your CSV file. with \copy you can read any file the user that launched psql client has permission to read. to make things more confusing it is indeed possible to stream stdin through psql but you use the regular COPY for that instead of \copy)
If you split on commas, your code will fail for quoted fields with commas in them.
Never heard of ndjson, can’t see one publishing this data in a format that isn’t nearly as common as something like csv (or regular json which some of the data is published in).
(disclaimer: I'm one of the zsv authors)
JSON and NDJSON can also be much larger than CSV files if the wrong structure is used.
Basically a document dump - https://en.m.wikipedia.org/wiki/Document_dump
It might be a little exciting to be an underwriter right now :D
The problem is that the health care costs situation results in many deaths and very severe economic consequences for much of the country.
Until lying, cheating, and scheming, and screwing over the public have consequences like prison time, you can expect executives to do everything possible to avoid complying with the spirit of laws like this.
There probably was an effort to create a more useful and sanely worded law that would provide a uniform format for rules that could reduce dataset sizes by a factor of 100, but was killed by the healthcare industry because it would require some implementation costs on their end and make the data files actually useful.
[1]https://www.pgpf.org/blog/2022/07/how-does-the-us-healthcare...
The US pays about twice per nurse or doctor in the system, and part of that is because the US pays nearly twice for most skilled work. So, to get prices like most other developed nations, we would be forced to cut nurse and doctor salaries, which would likely lower quality of workers as future workers went to more lucrative fields, which would likely lower outcomes.
The US can have higher cost or lower quality. How would you make this tradeoff?
Why are you ignoring all of the costs that go to people besides nurses and doctors? I know very rich people whose entire careers are built around selling overpriced products to hospitals. These people are leeches that provide no value other than profiting off of dumb compliance laws. If you can buy the same product at any store for 1/10 the price, there is no benefit to requiring it be gatekept by people whose sole incentive is squeezing blood from a stone.
Get rid of graft. The problem is the system and the incentives it creates. US healthcare is dictated primarily by insurance companies who care more about maximizing profit than providing healthcare.
To fix the system you start with increased transparency, then you focus on accountability. Why do we allow such blatant corruption? Let's get rid of all the leeches first, since they provide no actual value while jacking up prices. There are so many areas we can improve results and cut costs before we address the salaries of doctors and nurses.
which is in turn because in the US an average GP comes out of medical school with $200k-300k of student debt that has to have interest serviced and paid off within some 10-20 year timespan. That cost ultimately ends up being borne by the patient and their insurance.
unfortunately the US is very resistant to the idea of education reform in general, very very resistant to student debt relief, and very very very resistant to student debt relief for "high earners" like doctors and lawyers, even when a huge chunk of that earn is going to debt service. But there is a shortage of doctors and we're doing everything in our power to make the path unattractive for new students. And this time the problem isn't even the AMA - the AMA agrees there is a problem and is onboard with expanding the pipeline... it's just not all that attractive a profession anymore when you can make equal/higher compensation (after considering the debt) in software or other fields.
doctors are still extremely well-paid professionals in other countries, but if we tackle the cost of education we can get our numbers down much closer to theirs. conversely if you push salaries too low then servicing $200-300k of student debt won't be realistic and the path becomes even less attractive.
medical care is probably the single most complex political problem in the US because it's basically at the nexus of every single social and political problem we have. doctors are too expensive... because they're trucking around a quarter million of student loan debt from our shitty education system. we spend way too much on end-of-life care and not enough on earlier care... because seniors vote. we have way too much overhead due to the multi-payer insurance system and the market-driven pricing system's overheads... and all those insurance companies are huge lobbyists too. Drug and device costs are out of control... because the US doesn't allow conditioning of regulatory approval on price negotiations, or reimportation from other countries, etc. It's just every single political problem in the US in a single field all at once and every hand is dipping into the till as much as they can get away with, and it's politically infeasible to slap the hands that are necessary to slap to actually get costs reduced.
The US brought this problem upon itself by cutting medical school funding in the 1980s to reduce the number of doctors and keep salaries high. That situation remained until 2005. Now we have too few doctors, too few schools, and a generation that grabbed all the money for themselves and is now retiring.
Which people are those millions?
The system saves millions of lives that would have died in generations past. How do you factor that into your claim?
https://www.oecd-ilibrary.org/sites/3b4fdbf2-en/index.html?i...
The US has a mortality rate due to preventable causes comparable to Poland/Slovakia despite having nearly a 4x higher GDP per capita. Even poorer countries with better systems do far better w.r.t preventable mortality. This amounts to an extra 100,000 deaths per year approx vs countries like Italy, Germany, Switzerland, Sweden, etc.
and there are an awful lot of people, even if it is free or paid for by a great health insurance plan that ALSO don't go to the doctor when they should - thats just human nature
and on the other hand, lots of people running up ten's of thousands of dollars in unnecessary tests because they are hypochondriacs and run to the doctor every time they have a sniffle and demand every treatment and test under the sun.
Aligning the incentives is pretty hard but not impossible. The Dutch system was facing rising cost a few decades ago. It was split in a private insurance and public insurance system. Privately insured people enjoyed all sorts of perks (like private hospital rooms, less waiting time, etc.). The same system still exists in Germany (I live there currently).
To improve financial efficiency the Dutch government decided to get rid of public insurance and empower people to switch insurance. Everybody has to have insurance, all the insurers are private, and they have to compete to keep people as they can choose to jump to another insurer and they don't get to reject people. They all have to offer the same base package of care to everyone but can choose to diversify on top of that. This results in people shopping around and being treated like customers by insurers.
The second thing they then did was empower insurance companies to make deals with care providers. After all, they are paying the bills and if some hospital is being inefficient, they have to pay for it. They can't reject patients. But they can make deals with certain care providers or refuse to do business with others. This incentives care providers to align with insurers.
Likewise, pharmacies that supply medication are incentivized to look for cheaper alternatives. So, pharmaceuticals end up competing with each other for some things and pharmacies will pick what's cost effective rather than what doctors prescribe (in case of compatible alternatives).
It's not a perfect system but it has resulted in hospitals and insurers improving their game and getting rid of inefficiencies or bad service. Bad insurers lose their customers, inefficient hospitals result in insurers taking their business elsewhere and they suffer financially. Smart hospitals and insurers align what they are doing and avoid needless treatment. Patients and employers shop around for the best insurers based on the needs and means and to get the best rate and care or access to their preferred care providers.
I actually live in Germany which has a system that resembles what things used to look like in the Netherlands. It's a bloated, inefficient system. There's stupid bureaucracy left right and center, endless referrals and waiting lists, and you are treated like cattle. I have private insurance so I get to jump the queue but I also get to deal with doctors that are a bit too trigger happy with treatments and needless appointments that they can squeeze the insurer for. The insurance is super expensive for me. And I can't easily switch insurer so they can squeeze me hard and up their rates. The hospitals are pretty bad and miserable compared to Dutch hospitals.
If they're looking for projects that create public value and demonstrate the power of their products at scale, digitizing this and making it searchable may be a good marketing project that's appealing to certain kinds of customers.
Petabytes uncompressed would be tricky if you need to slice those columns. SQLite caps out at ~281 terabytes of storage before it can't track any additional pages.
None of this is to say you couldn't partition the data across a lot of SQLite instances in varying ways. I will probably take a shot at it this weekend. Looking to see just how unlimited my AT&T fiber connection is anyways.
That's cute. :)
There isn't much value in feeding it all into a conventional RDBMS. OLAPs and columnar stores are what is needed here. But first it will need a great deal of grooming and ETL work.
Just wait. It's actually a multi-boss fight, since you have to wrangle the Pharmacy Benefits Management datasets, plus Medispan, plus Medicare, plus all the MedicAid datasets, plus VA.
Are you and all your mightiest boxen bad enough dudes to make sense of the entire U.S. Healthcare industry?
<Actuary Stormrage in the background>
You are not prepared!
edit: As I reflect, I'm amused to recall that this was early enough in my path that I didn't know about DB indexes, so I was very proud that I figured out how to basically roll my own indexes by pre-sorting the columns by lat and lon. I don't remember whether my solution actually prevented a full-table scan, but it felt like a major breakthrough at the time.
I'd be very curious to read more about the data cleaning phase when you get there. Specifically, how hard it is to combine this data and construct good schemas.
It's entirely possible that two surgeons with offices next to each other could be getting reimbursed at wildly different rates for their most common procedures for their most common procedures by the same provider.
If you're that provider, you ABSOLUTELY want to know what the surgeon next door is getting paid the next time your group is negotiating with the insurance provider.
Pricing transparency is only one piece of the puzzle. It is a tremendously antiquated industry. Fax is still state of the art -- welcome to the 1980s!
[1] CMS. https://www.cms.gov/Research-Statistics-Data-and-Systems/Sta...
And there are arguments against all these points, I concur. I just said it for the American folks to know.
Has a lot changed in the last decade? Is the “good” healthcare just in wealthier areas? Was this just an extreme outlier?
Obviously, this is just one (anec)datapoint.
https://www.mhtf.org/2017/06/23/quality-of-routine-labor-and...
By January 1, 2023, plans and issuers must make price comparison information available with respect to an initial list of 500 identified items and services. By January 1, 2024, plans and issuers must make price comparison information available with respect to all covered items and services. This information must be made available through an internet-based self-service tool and in paper form, upon request. Typically, consumers receive an Explanation of Benefits after receiving care, which details the prices charged by the provider, the plan’s contracted or negotiated rates, consumer cost-sharing obligations, and other information. Consumers will have access to this type of information before receiving care and can use it to compare prices and better estimate potential out-of-pocket costs.(2)
(1)https://www.cms.gov/newsroom/fact-sheets/transparency-covera... (2)https://www.cms.gov/healthplan-price-transparency/consumers
How did it take another administration, an executive order, and 8 more years?!
Hell, even the Clinton plan was calling for it in 1993 [2] but that never went anywhere.
[1] https://www.healthleadersmedia.com/finance/new-price-transpa...
[2] https://www.heritage.org/health-care-reform/report/guide-the...
[1]https://www.cms.gov/files/document/hospital-price-transparen...
Of course many hospitals negotiate en bloc as part of a healthcare network, and there probably are more than 100 different organizations that negotiated unique healthcare pricing but the ballpark number seems to make sense.
This blog was a feasibility analysis to see what kind of work it would take to get that data. If we do get it, we plan on making it free to download.
What you are seeing here is essentially part of the claims adjudication process output.
This takes as input the procedure (ie the ICD/CPT code) and the provider, and runs it against the 'plan design' (which essentially is what includes the provider networks and the associated negotiated rates per procedure) and outputs the resultant cost.
Same can be done on the PBM side, just substitute drug for procedure, and 'formulary' for 'plan design'.
The interesting question when you look at cross plan, cross provider, and cross carrier variances, is why :)
Many companies, careers, and lawsuits, are being born from this data drop. Will be fun times the next decade or so to see how this plays out.
Is it fully public or does it require registration to access?
This won't get you all of the insurers, but it'll get you a a few of the major ones.
If you want links to the files of more insurers, here's a project from one of my friends at Postman: https://github.com/postman-open-technologies/us-cms-price-tr...
Noteworthy(?): there seems to be a limit of ~100~ 140 sets of prices, as seen in the filenames:
2022-08-25_NNN_in-network-rates_0000000XXXXX.csv.gz
~Did I miss something? ... or is this some kind of technical limitation for Humana?~ Edit: I missed the alphabetical ordering. Still, only about 140 price sets.
Also, each plan member's JSON file has a small chunk of useful information, then a useless list of all 15k gz parts of a relevant NN_in-network-rates file (you only need the first filename to figure out which NN to reference).
For these files, you can use Range requests to download only the first, say, 50KB, and pipe it to gunzip and jq. (https://github.com/stedolan/jq/issues/31#issuecomment-900184...)
I would also be interested in helping throw such an analytical dataset into BigQuery. It'll be great for sharing an open dataset. No doubt this will still be a gigantic headache, but it is tractable.
If you think the government can bring down the cost of anything please see education and NASA for great examples.
Perhaps a lot of money depending on what you think should be prioritized. personally I think it’s hard to argue that they aren’t delivering something of high value to humanity, especially recently with the success of the JWST. And maybe very especially compared to other agencies.
https://www.washingtonpost.com/business/nasas-artemis-rocket...
SLS is expensive, but it's nowhere close to the bulk of NASA's funding. It's about 10%.
"Over the he past 50 years they have held back any economical progress in access to space."
without NASA there wouldn't be any SpaceX's and others doing space projects ....
You will literally save American lives.
We will sell our benefits investigation services to providers (hospitals, clinics) so that they can give better information to their patients. However our main customers right now are labs that do diagnostic testing.
What we've been able to do is build data-backed algorithms to determine whether a given patient's health insurance will cover a given test. Since patients are (obviously) more willing to get tested if they know that their insurance covers it, labs that work with us can increase their volume. Labs' sticker price for tests can be thousands of dollars, and they don't want to advertise that. They need information about what insurance the patient has and whether that insurance will cover a given test.
I think most of our data is internal data we've collected, but the Price Transparency Act is definitely useful to us in delivering accurate estimates.
(This is an oversimplification of our system. If you work in the space and want details, I'm sure our team would be happy to talk with you.)
- these prices, as negotiated between insurers and providers, were already well known inside the industry. so much so that many procedures could be declined coverage well in advance of a customer ever needing one. this insider knowledge formed the core of many earnings reports for insurers and hospitals alike,
- Disclosure is meaningless if the customer has no alternative. most health services that bankrupt are emergency medicine, and as such youll pay anything to save your own life. thrusting a stack of price sheets at a faceless national healthcare monopoly and demanding a fair price is a laughable if not sad idea. Healthcare is not something capitalism is equipped to competently support.
- hospitals have zero incentive to work with you on any price for any service, and no federal state or local law will compel them to do so by virtue of a combination of bureaucratic deadlock and regulatory capture. is it, for them, more profitable to sell your arbitrary debt to a credit collection agency? shove you into a debt counseling service they get kickbacks from? work a long and grueling payment plan through their own financial services division to bolster quarterly profit long-term in a recession? or just ignore your pleas entirely? what they charge is not up for debate by you.
It´s like if you signed a contract to pay Netflix a monthly fee to eventually watch a movie, and for some reason Netflix profit would be based on you watching as little as possible. They would do all in their power to minimize the amount of content you could really watch. Unaligned objectives. And the problem is that unlike Netflix, Health Insurance (at least in the USA) is inelastic: You MUST pay for it.
A sane approach might be to let more people graduate medical school and increase the number of applicants…
This post is lot more interesting and important than the current short title would suggest.
The USA healthcare price gouges via run away prices. Healthcare corporations corrupting congress is the fuel that forces this on people (and breaks a free market).
That being said, Canada has a huge health care problem right now. Frequently they need to close emergency services in major (& minor) cities at night due to staff shortages. The wait times for basic diagnostics is on the order of months (not days or weeks). For example, a relative of mine has lost 90 pounds since the start of the year, complained of chest pains and couldn’t (still can’t) swallow. They just got a basic scan after 6 months of waiting to confirm a baseball sized tumour in their lungs.
For many years we ran with just the bare minimum of staffing and equipment to keep costs low, and now, due to a number of factors our system can’t keep up.
Waste is not good, but a little bit of extra capacity (and cost) isn’t a bad thing when it comes to emergency services.
We are now reaping what they have sown.
This is a classic "underfund to prove the system doesn't work thus we must privatize it!" move.
The crazy thing to me about this is, most Americans simply skip medical procedures, or tests due to cost.
So American wait times aren't lower because their hospitals are better, they just server a much smaller pool of people due to so many being priced out of being healthy.
And honestly, the longer you go without doctor visits, more expensive, complicated and involved the process is.
(Ridiculousness not being the pandemic itself, but inadequate resources, planning, and political cover from all levels of government.)
Part of this is COVID burnout. It wasn't this bad in 2019. Wife works in healthcare and sees the difference every day. Nurses crying etc.
For many years Canada (and similar countries) have relied on cutting costs by importing health care professionals from other countries. That pipeline has now shrunk considerably, resulting in the exposure of the underlying problem.
This says 10.2% as of 2020 : https://www.aihw.gov.au/reports/health-welfare-expenditure/h...
Any improvement in efficiency would mean those people lose out on jobs. A new use for them must be found, or there will be massive unemployment (at least, short-medium term).
The first economist says to the other “I’ll pay you $100 to eat that pile of shit.” The second economist takes the $100 and eats the pile of shit.
They continue walking until they come across a second pile of shit. The second economist turns to the first and says “I’ll pay you $100 to eat that pile of shit.” The first economist takes the $100 and eats a pile of shit.
Walking a little more, the first economist looks at the second and says, "You know, I gave you $100 to eat shit, then you gave me back the same $100 to eat shit. I can't help but feel like we both just ate shit for nothing."
"That's not true," responded the second economist. "We increased the GDP by $200!"
Another example of political economy is two different efforts to address climate crisis, with very different outcomes.
During the Obama Admin, the push was for carbon taxes. Wonks loved it. Logical, concise, moral. Complete nonstarter. Because carbon taxes has no built-in constituency that's willing to advocate and defend it.
Whereas in 2020, progressives pushed "green new deal" themed industrial policy and investment strategy. Made most everyone a benefactor. Millions of jobs. 100s of billions of dollars. Much better political economy.
This isn't necessarily a great metric, because almost all countries have better outcomes and all countries have lower per capita cost, whether their systems are public, private, or mixed. The US spends more public funds on healthcare than countries with universal socialized health care systems. The fact that we're also personally bankrupted after spending the same tax proportion on healthcare is just a bonus.
It's not specifically private healthcare or insurance that's the problem, it's the specific corruption of the people who own the healthcare industry and their legislators.
The US federal govt spends >8% of GDP on healthcare, with the current system of all of our insurance and payments and everything that we have to pay in addition, totaling nearly 20% of GDP: https://www.crfb.org/papers/american-health-care-health-spen...
The UK spent 9.6% of GDP on healthcare in that same year (2017) for their publicly-funded full healthcare system: https://www.ons.gov.uk/peoplepopulationandcommunity/healthan...
There is quite a bit of variety across Europe - U.K. is 100% government run, France is a public/private mix, Germany is similar to Obamacare in some ways, others are single payer, apparently some are private, also. But I've read that they all have cost controls.
This is not true.
Private healthcare is alive and well in the UK in the supply of both privately and publicly-commissioned healthcare.
I know I'm a broken record on this but people who say this are simply wrong.
1) There is not one NHS for the UK. Health is devolved and there are totally separate systems for NI, Scotland, Wales, and England.
2) Speaking only for England, it's not "run by the government". The NHS in England is run by NHS England and Improvement. The people who work for NHSE/I are not civil servants, they do not work for the government.
Also FWIW France and Switzerland both have universal healthcare, under different systems where France splits payments 3 ways and covers more with the govt[1], and Switzerland seems to have a system like the ACA in the US where it's compulsory, but they also set caps on the deductibles and maximum price.
[1]: https://en.wikipedia.org/wiki/Health_care_in_France
[2]: https://en.wikipedia.org/wiki/Healthcare_in_Switzerland
> Looking for evidence to the contrary
There was no evidence behind your claim either. 90% of US healthcare costs go to managing ( not treating ) obesity/metabolic disease . Yes 90% [1].
Which country with similar metabolic disease rate has better outcomes with less costs?
"90% of the nation's $4.1 trillion in annual health care expenditures go to people with chronic and mental health conditions"
Obesity is $173 billion, diabetes $327 billion.
We could certainly do much better, but blaming health care cost on metabolic diseases is disingenuous. Diabetes cost is roughly the same percentage of total expenditure as it is in other industrialized nations. (I didn't check obesity)
It stands to reason that really, the problem is the size of the pie, not the size of the slice.
Also anybody legally employed must have insurance (of varying quality).
Physicians have a monopoly on treatment and restrict supply through the ACGME.
Hospitals and physicians have spent 1Billion on lobbying.
I could go on, I want a science based healthcare as an alternative to our authority based system.
It would be one thing if our system healed everyone, but the number of mistakes is enough to make me lose faith completely.
In the actual system good lifetime is traded for bad one.
In the case of healthcare, states have a tendency to shift costs elsewhere. As in, send the sick person to New York City.