Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Scientific datasets are riddled with copy-paste errors

sciencedetective.org

Post reply on HN
Print view View original

61–68 of 68 posts

Re: Scientific datasets are riddled with copy-paste errors

#61

Post by theshrike79 »

tempaccount5050 wrote: post #45
tremon wrote: post #39

Earlier quoted context omitted.

that's a little unfair. It really comes down to parsing text and you'll have similar issues even if you use a database or whatever Feel free to show a real-world example of a database or whatever that takes the input string "IGF1 SEPT2 PRX3 MARCH1" and writes that into storage as ["IGF1", "2026-09-02", "PRX3", "2026-03-01"]. Also with Excel, an inadvertent click+drag can move data between cells, and since the cells a…

You still have to push and pull from the db. Meaning transforms still need to happen in either direction. I get what you're saying but it's just as easy to screw up a regex in either direction. Or making assumptions about how your language of choice will handle dates etc.

Excel is the only "language" that just silently transforms anything that might be a date into a date. In such a frequency that SCIENTISTS HAVE RENAMED GENES to prevent it: https://www.theverge.com/2020/8/6/21355674/human-genes-renam...

Re: Scientific datasets are riddled with copy-paste errors

#62

Post by mcdonje »

thunderfork wrote: post #56
mcdonje wrote: post #55

Earlier quoted context omitted.

That's Excel's type inference causing problems. Not an issue with CSV or any other type of DSV. It is possible to import a CSV into Excel without type conversion. I just tested it two different ways. While possible, it's not Excel's default way of doing things. Not always obvious or easy. Not enough people who use Excel really know how to use it. Regardless, Excel mangling files via type inference is an Excel problem…

The file format being ambiguous and underspecified enough to mangle is, though.

No, it's Excel trying to be too clever. It does the same thing with manual imput if you don't proactively change the field type.

You can import a DSV into Excel without mangling datatypes in a few different ways. Probably the best way is using Power Query.

A DSV generally does have a schema. It's just not in the file format itself. Just because it isn't self-describing doesn't mean it isn't described. It just means the schema is communicated outside of the data interchange.

Re: Scientific datasets are riddled with copy-paste errors

#63

Post by mcdonje »

jcattle wrote: post #59
mcdonje wrote: post #55

Earlier quoted context omitted.

That's Excel's type inference causing problems. Not an issue with CSV or any other type of DSV. It is possible to import a CSV into Excel without type conversion. I just tested it two different ways. While possible, it's not Excel's default way of doing things. Not always obvious or easy. Not enough people who use Excel really know how to use it. Regardless, Excel mangling files via type inference is an Excel problem…

If you get an .xls which doesn't have very esoteric functions, I expect it to open about the same way in any Excel program and any other office suite. With CSV I do not have that expectation. I know that for some random user-submitted CSVs, I will have to fiddle. Even if that means finding the one row in thousand rows which has some null value placeholder, messing up the whole automatic inference.

You're just saying when there's no filetype transfer, you don't have to deal with issues related to filetype transfer.

Re: Scientific datasets are riddled with copy-paste errors

#64

Post by IanCal »

xorcist wrote: post #47
IanCal wrote: post #34

Earlier quoted context omitted.

CSV occupies, even years after moving away from more raw data work, way too much of my brain is still dedicated to "ways of dealing with CSV from random places". I can already hear people who like CSV coming in now, so to get some of my bottled up anger about CSV out and to forestall the responses I've seen before * It's not standardised * Yes I know you found an RFC from long after many generators and parsers were w…

While I appreciate a good rant just as much as the next person, most of these points have nothing to do with CSV. They are a general problem with underspecifying data, which is exactly what happens when you move data between systems. The amount of hours I have wasted on unifying character sets across single database tables is horrifying to even think about. And the months it took before an important national dataset…

I disagree, they are absolutely related to CSV in that these are all problems CSV has. Other formats can have these problems, but CSV is almost uniquely bad because these issues compound and it has a lot of them.

> They are a general problem with underspecifying data,

Which CSV provides essentially no tools to solve, unlike many other formats.

Also, several of these problems are not even about underspecified data but the format itself - you can have totally fine data which gets utterly fucked to the point of not parsing as a csv file by minor changes.

It's not even a fully specified format! Someone adds a comma in a field and then one of the following happens:

* Something generating the csv doesn't add quotes

* Something reading the csv doesn't understand quotes

And the classic

* Something sorted the file

> Sure, you can specify MM/DD/YY in a table, but it people put DD/MM/YY in there, what are you going to do about it?

If you've got something with actual date types you can have interfaces show actual calendars, and for many formats you will at least get an error if it's defined as DD/MM/YY and someone puts in 01/13/26. CSV however gives you no ability to do this - all data is just strings. And string defined dates with no restrictions are why I have had to deal with mixtures of 01/13/26 and 13/01/26, meaning everything goes just fine until you try and parse it. Or, like some of my personal favourites, "Winter 2019".

CSV is not one format, lacks verification of any useful kind, is almost uniquely easy for users to completely fuck up, and the lack of types means that programs do their own type inference which adds to things getting messed up.

Re: Scientific datasets are riddled with copy-paste errors

#65

Post by IanCal »

mcdonje wrote: post #55
jcattle wrote: post #44

Earlier quoted context omitted.

I would kind of disagree. We are talking here in the context of scientific datasets. Of course ETL plays a part here. However here it is really more the interplay of Excel with CSV which is often outputted by scientific instruments or scientific assistants. You get your raw sensor data as a csv, just want to take a look in excel, it understandably mangles the data in attempt to infer column types, because of course i…

That's Excel's type inference causing problems. Not an issue with CSV or any other type of DSV. It is possible to import a CSV into Excel without type conversion. I just tested it two different ways. While possible, it's not Excel's default way of doing things. Not always obvious or easy. Not enough people who use Excel really know how to use it. Regardless, Excel mangling files via type inference is an Excel problem…

It's both of their faults. CSV is not blameless here - Excel is doing something broadly that users expect, have dates as dates and numbers as numbers. Not everything as strings. If CSV had types then Excel would not have to guess what they are.

Re: Scientific datasets are riddled with copy-paste errors

#66

Post by mcdonje »

IanCal wrote: post #65
mcdonje wrote: post #55

Earlier quoted context omitted.

That's Excel's type inference causing problems. Not an issue with CSV or any other type of DSV. It is possible to import a CSV into Excel without type conversion. I just tested it two different ways. While possible, it's not Excel's default way of doing things. Not always obvious or easy. Not enough people who use Excel really know how to use it. Regardless, Excel mangling files via type inference is an Excel problem…

It's both of their faults. CSV is not blameless here - Excel is doing something broadly that users expect, have dates as dates and numbers as numbers. Not everything as strings. If CSV had types then Excel would not have to guess what they are.

It does have types if you define them in the schema. Not every format needs to be self-describing. It's often more efficient to share the schema once outside of the data feed than have the overhead of restating it for every data point.

It's completely Excel's fault for pushing their type-inference and making it difficult for users to define or supply their own.

Power Query does a better job handling it, but you should be able to just supply a schema on import, like you can with Polars or DuckDb.

It's another example of MS babying their userbase too much. Like how VBA is single threaded only because threads are hard. They're making their product less usable and making it harder for their users to learn how stuff works.

Re: Scientific datasets are riddled with copy-paste errors

#67

Post by IanCal »

mcdonje wrote: post #66
IanCal wrote: post #65

Earlier quoted context omitted.

It's both of their faults. CSV is not blameless here - Excel is doing something broadly that users expect, have dates as dates and numbers as numbers. Not everything as strings. If CSV had types then Excel would not have to guess what they are.

It does have types if you define them in the schema. Not every format needs to be self-describing. It's often more efficient to share the schema once outside of the data feed than have the overhead of restating it for every data point. It's completely Excel's fault for pushing their type-inference and making it difficult for users to define or supply their own. Power Query does a better job handling it, but you shoul…

Csv doesn’t have a schema, it has a barely adhered to post-hoc “not a specification” and everything is strings.

That you can solve some of these problems by using something as well as the csv file is not anywhere near as helpful, and it’s a clear problem of csv files. There is no universally followed schema, for a start, so now we’re at unique solutions all over the place.

> It's often more efficient to share the schema once outside of the data feed than have the overhead of restating it for every data point.

You cannot be suggesting that csv files are efficient surely, they’re atrociously inefficient. Having the same format and a tied in schema would solve a lot and add barely anything as overhead. If you want efficiency, do not use csv.

Asking users to manually load in the right schema every time they open a file is asking for trouble. Why wouldn’t you combine them?

> It's completely Excel's fault for pushing their type-inference and making it difficult for users to define or supply their own.

It’s not entirely excels fault that csv doesn’t have types. They didn’t invent and promote a new standard, but then why would you? There’s better formats out there. I’m sure they would argue that the excel files are a better format for a start.

And people did make better formats. That’s why I think csv should be consigned to the bin of history.

Re: Scientific datasets are riddled with copy-paste errors

#68

Post by jcattle »

mcdonje wrote: post #63
jcattle wrote: post #59

Earlier quoted context omitted.

If you get an .xls which doesn't have very esoteric functions, I expect it to open about the same way in any Excel program and any other office suite. With CSV I do not have that expectation. I know that for some random user-submitted CSVs, I will have to fiddle. Even if that means finding the one row in thousand rows which has some null value placeholder, messing up the whole automatic inference.

You're just saying when there's no filetype transfer, you don't have to deal with issues related to filetype transfer.

No. That's not at all what I'm saying. I am saying that a fixed CSV file will open differently depending on the program you open it with.

Don't even need to transfer it. Opening a csv in pandas can be different than opening with polars, can be different to DuckDB, can be different to Excel.

You've got not guarantees. There's no spec, and how edge cases (if you want to call how to serialize and deserialize a float an edge case) are handled is open to the implementation.

Post reply on HN