News for hackers. Stuff that matters.
Re:Health insurers just published close to a trillion hospit... by RhodesianHunter on Tuesday September 06, 2022 @01:25PM (#32740210)
Anyone else get the feeling this is malicious compliance on behalf of the insurance companies?

"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"

Re:Health insurers just published close to a trillion hospit... by carabiner on Tuesday September 06, 2022 @04:39PM (#32742904)
lol, have you ever worked with data from a non-tech company? This is probably the best they have, even inside the company.
Re:Health insurers just published close to a trillion hospit... by watwut on Tuesday September 06, 2022 @05:44PM (#32743615)
Can confirm. Also, it is not better in tech companies, they just have the same data in higher variety of formats and storage systems.
Re:Health insurers just published close to a trillion hospit... by nerdponx on Tuesday September 06, 2022 @09:38PM (#32745416)
Not only was this probably a best effort, but I would bet that at least a few people were busting their asses trying to pull this data together and clean it up.
Re:Health insurers just published close to a trillion hospit... by axus on Tuesday September 06, 2022 @01:31PM (#32740301)
The article mentioned CSV files, it seems more like a reflection of what a huge bureaucracy the US healthcare system is. I liked their suggestion that the government should have created the database as part of the law, done the processing on the raw data, and made it more accessible.
Re:Health insurers just published close to a trillion hospit... by lumost on Tuesday September 06, 2022 @02:00PM (#32740694)
meh, I'd prefer the raw data. We can always create DBs out of the raw data, we can always link data. Handling this after the fact would be impossible.

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...

Re:Health insurers just published close to a trillion hospit... by orangepurple on Tuesday September 06, 2022 @04:43PM (#32742952)
import a CSV into Postgres

    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;")
this variant will ingest whatever trash is in your CSV fields as-is (cast & cleanup later)

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)

Re:Health insurers just published close to a trillion hospit... by nardi on Tuesday September 06, 2022 @11:31PM (#32746047)
Do NOT read a CSV file by splitting on commas. Python has a perfect CSV library built right in: https://docs.python.org/3/library/csv.html.

If you split on commas, your code will fail for quoted fields with commas in them.

Re:Health insurers just published close to a trillion hospit... by orangepurple on Wednesday September 07, 2022 @01:47AM (#32746819)
This is obviously a quick and dirty hack. It only splits the header by commas. Postgres has its own read in logic you can adjust by passing arguments to \copy or copy. If you have anything more complex you need to handle it by parsing the header more intelligently than splitting on commas and adding appropriate arguments to copy or \copy
Re:Health insurers just published close to a trillion hospit... by RhodesianHunter on Tuesday September 06, 2022 @11:01PM (#32745894)
Loading up 100TB of csv data into a Postgres db is not a realistic proposition.
Re:Health insurers just published close to a trillion hospit... by A4ET8a8uTh0 on Tuesday September 06, 2022 @04:33PM (#32742833)
In their defense, if it was anything but CSV files, they would be accused of making it too complicated/locking into proprietary formats and so on. I can't say CSV would be my first choice, but I don't really want to think what the alternative would be.
Re:Health insurers just published close to a trillion hospit... by easrng on Tuesday September 06, 2022 @05:12PM (#32743268)
NDJSON? Sqlite?
Re:Health insurers just published close to a trillion hospit... by ironick09 on Tuesday September 06, 2022 @08:03PM (#32744742)
csv is many times over again a simpler format than sqlite and easier to understand by anyone across the world.

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).

Re:Health insurers just published close to a trillion hospit... by easrng on Tuesday September 06, 2022 @08:18PM (#32744840)
CSV only seems simple. Lots of parsing edge cases. Sqlite isn't readable by hand but it's basically bulletproof. NDJSON is literally just newline seperated JSON, it's just easier to process as a stream without a special parser.
Re:Health insurers just published close to a trillion hospit... by irrelative on Tuesday September 06, 2022 @08:45PM (#32745055)
Sure, could be simpler, but there's a spec and multiple implementations: https://www.rfc-editor.org/rfc/rfc4180.html And the spec is ~5 pages.

Not sure why NDJSON is considered simpler, as json objects can be arbitrarily nested. Breaking into records is easier, but parsing is harder.

Re:Health insurers just published close to a trillion hospit... by easrng on Tuesday September 06, 2022 @09:25PM (#32745335)
(ND)JSON is simpler because people actually follow the spec, unlike with CSV.
Re:Health insurers just published close to a trillion hospit... by Dylan16807 on Wednesday September 07, 2022 @03:18AM (#32747500)
People violate the JSON spec all the time.

And following the CSV spec is much easier than following the JSON spec. And there's only like three edge cases.

Re:Health insurers just published close to a trillion hospit... by mschuster91 on Wednesday September 07, 2022 @04:52AM (#32748211)
> And there's only like three edge cases.

Unless you involve MS Excel or, worse, MS Excel on macOS. OTOH, pitfalls are: UTF-8 BOM, comma vs semicolon, single vs double quote, multiline cell content and escaping, escaping in general...

Re:Health insurers just published close to a trillion hospit... by mattewong on Thursday September 08, 2022 @01:05AM (#32761394)
All this banter arguing over CSV, JSON, sqlite seems unnecessary when you can just push format X through a pipe and get whichever format Y you want back out: https://github.com/liquidaty/zsv/blob/main/docs/csv_json_sql...

(disclaimer: I'm one of the zsv authors)

Re:Health insurers just published close to a trillion hospit... by mr_toad on Tuesday September 06, 2022 @09:36PM (#32745407)
Then you’d have to get your hands on a parser. Who wants to write up the business case and spend months going through change control (they’d probably just say no).

JSON and NDJSON can also be much larger than CSV files if the wrong structure is used.

Re:Health insurers just published close to a trillion hospit... by Victerius on Tuesday September 06, 2022 @05:05PM (#32743202)
Millions of .xlsx files
[dead]
Re:Health insurers just published close to a trillion hospit... by jjulius on Tuesday September 06, 2022 @05:39PM (#32743563)
... what in the blue hell...?
Re:Health insurers just published close to a trillion hospit... by daniel-cussen on Wednesday September 07, 2022 @07:20AM (#32749134)
I got a little excited, that's all I can say about the flow.

But there is an undercurrent of betrayal, there absolutely is, undermining everything, only visible if you get trapped in it, if not it looks like a meaningless whoopsie.

Re:Health insurers just published close to a trillion hospit... by jjulius on Saturday September 10, 2022 @11:47PM (#32796663)
Flow? Forget about flow, I have no earthly idea what it is that you could possibly be trying to say.
Re:Health insurers just published close to a trillion hospit... by vxNsr on Tuesday September 06, 2022 @06:08PM (#32743868)
Most likely a poor ai attempt.
Re:Health insurers just published close to a trillion hospit... by 1MachineElf on Tuesday September 06, 2022 @06:38PM (#32744101)
From the user's "about" section:

Perhaps you'll think my comments are unthinkable. My only response to that is that they were legibly written, not by a machine, but by a writer with a soul.

Re:Health insurers just published close to a trillion hospit... by jjulius on Tuesday September 06, 2022 @06:48PM (#32744188)
Yeah, it's unclear to me. A lot of the more personal things that are mentioned throughout the account's posts seem to match up with some of the quickly-googleable details that can be found just via their username. I suppose that it could be baked into the AI, but... /shrug
Re:Health insurers just published close to a trillion hospit... by secondcoming on Tuesday September 06, 2022 @05:27PM (#32743420)
So I should never visit a doctor in Chile?
Re:Health insurers just published close to a trillion hospit... by daniel-cussen on Wednesday September 07, 2022 @07:21AM (#32749145)
I was talking about American doctors, Chilean doctors do wash their hands.
Re:Health insurers just published close to a trillion hospit... by teeray on Tuesday September 06, 2022 @05:39PM (#32743559)
Just be glad the lawyers didn’t make the prices exclusively available via the traditional UHaul full of Banker’s Boxes.
Re:Health insurers just published close to a trillion hospit... by danjc on Wednesday September 07, 2022 @03:22AM (#32747536)
Came here to say the same thing. I would expect their internal systems to calculate certain pricing components on the fly so it feels like they’ve deliberately built all possible permutations and data dumped that.

Basically a document dump - https://en.m.wikipedia.org/wiki/Document_dump

Re:Health insurers just published close to a trillion hospit... by ecommerceguy on Tuesday September 06, 2022 @07:21PM (#32744434)
Keep in mind these files are called "Machine Readable" for a reason. Yes, they dumped everything. Machines and 3rd parties are supposed to sort it out. Best be assured each carrier now has full visibility of each other leading to "transparency".

It might be a little exciting to be an underwriter right now :D

Re:Health insurers just published close to a trillion hospit... by CogitoCogito on Wednesday September 07, 2022 @02:30AM (#32747148)
They should be forced to provide the costs of procedures up front. As in the whole procedures not just provide prices for a million sub-procedures that the _might_ bill you for. This is how it works in basically every industry. Healthcare tries to argue that it's impossible for them to do this, but is just a load of bullshit.
Re:Health insurers just published close to a trillion hospit... by ilaksh on Tuesday September 06, 2022 @09:02PM (#32745173)
Of course it is.

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.

Re:Health insurers just published close to a trillion hospit... by smsm42 on Tuesday September 06, 2022 @10:22PM (#32745667)
More like "how we do it in the cheapest way with minimum possible effort". It doesn't earn them any money, so they don't want to spend money on it.
Re:Health insurers just published close to a trillion hospit... by outside1234 on Tuesday September 06, 2022 @02:18PM (#32740948)
Oh totally. It also probably is the formats they already had -- so they just dumped them into a file -- versus making something more orthogonal and ergonomic.
Re:Health insurers just published close to a trillion hospit... by sl-dolt on Tuesday September 06, 2022 @02:25PM (#32741070)
I'm not sure what format they store their records in, but I have a hunch it's a lot more structured than what we see in the CSV files. The data dumps have to comply with some CMS guidelines set out here: https://github.com/CMSgov/price-transparency-guide
Re:Health insurers just published close to a trillion hospit... by kube-system on Tuesday September 06, 2022 @04:40PM (#32742914)
They use relational databases. Then a zillion ETLs to massage that data into every format they need it in, of which this is one of them.
Re:Health insurers just published close to a trillion hospit... by geraldwhen on Tuesday September 06, 2022 @05:58PM (#32743751)
There are a lot of billing codes. It’s not as simple as you hope. A giant csv export is easy enough to process and synthesize for normies.
Re:Health insurers just published close to a trillion hospit... by spaetzleesser on Tuesday September 06, 2022 @06:48PM (#32744193)
They may have that thought but crunching large amounts of data is not exactly hard these days. Better too much than too little data.