One in five genetics papers contains Excel errors (2016) | Hacker News Reader

One in five genetics papers contains Excel errors (2016)

sciencemag.org

115 pointspjc5059 comments

> The researchers, who published their analysis in Genome Biology, say the issue can be fixed by formatting Excel columns as text and remaining vigilant—or switching to Google Sheets, where gene names are stored exactly as they’re entered

I doubt that. I fight with Google Sheets every day because it thinks some of my text values are dates.

I wonder if the world couls benefit from a SQLite-backed spreadsheet program. All the data type BS would be basically solved.

There has been an amazing project like this on HN this year.

I will do a quick search to see if I can find it.

edit: not so easy, but here it is https://news.ycombinator.com/item?id=16772276

https://www.querystorm.com/

The fundamental issue I have with tools like this is they all require plugins, which makes sharing these spreadsheets outside of your organization nearly impossible.

I'd love to find a plugin that let's me do all this awesome stuff and then export a copy of the spreadsheet with static data and no dependencies/plugins/macros.

Does that exist?

I'd be surprised if you couldn't export the output from that as CSV or "plain" XLSX.

CSV would also defeat the entire point as the goal is the presentation and familiarity of Excel.

But if it's possible to export to a plain XLSX (looks and functions identically but all the data values are hardcoded) that'd be great.

The "backing" isn't the problem, it's the interface to it.

Yes, imposing a schema on arbitrary spreadsheet data absolutely fixes typing errors like this.

It also produces a tool that has the semantics of a formal database and not a "spreadsheet", so none of the biologists in question are going to use it, being biologists and not programmers. We can't fix this with tools. Like the article said, proper scientific rigor is really the only solution.

In some fields (e.g. physics), computation is so fundamental to the research that practitioners are basically computer scientists. We aren't there yet in genetics, nor do we really want to be.

> Like the article said, proper scientific rigor is really the only solution. In some fields (e.g. physics), computation is so fundamental to the research that practitioners are basically computer scientists. We aren't there yet in genetics, nor do we really want to be.

Why not?

> We aren't there yet in genetics, nor do we really want to be.

Why not? Interdisciplinary, quantitative biology is the future of biology, and computers make this kind of work so much easier for the same reason that they make the work of physicists easier. More and more, it seems my colleagues without at least rudimentary programming skills are being left in the dust.

I don't know if the world should give up on making something better than a spreadsheet but easier to use than a full blown database. It's not clear to me at all that spreadsheets have to work exactly as they do today. More type-safety would definitely be an upgrade.

In all fairness, Excel is also notorious for being overaggresive when it comes to deciding whether a given cell should be converted to a date.

This headline is quite unfair. If Excel didn’t try to normalize data that was being input, how many more errors would there be in these and other datasets? Anyway, no automated tool is going to make the right choices in every case. The researchers and journals have a responsibility to take care to check and clean their own datasets. They blame this class of errors on Excel. But if they aren’t catching such obvious issues (like a gene name field that has a date datatype and doesn’t match any gene names) what about all the errors that aren’t Excel’s “fault” but are much more subtle?

Did the researchers license Excel for genetic work? This seems like a major error to treat your gene data as dates.

Excel doesn't treat gene data as dates, it treats text input in certain formats as dates unless you tell it not to by explicitly specifying the data type. Scientists have a whole constellation of tools to choose from--various spreadsheets, Jupyter notebooks, R programs, custom apps written by colleagues, etc.--and each one will have a certain feature set that will have to be used properly to avoid errors. Excel is not "buggy" in this respect.

I will say, though, that I have been repeatedly surprised when creating large, complex models for others in Excel at how easy it is to have a bug in your "code" that isn't obvious. In this respect, it's a bit like JavaScript's philosophy of "don't crash or sound an alarm, just make your best guess and keep going." For simple things, this works fine, but for anything complex, I really prefer more "bondage and discipline" from the platform, so my potential errors are pointed out rather than forgiven.

I didn't expect Excel to understand gene data. I expected that scientists would have used an appropriate tool for gene data.

>complex models for others in Excel

It's best not to do that except in cases of emergency, I think. If you are going to get complex, SQL may be a much better solution, or something else.

I think it's clear that this is the researchers' fault.

>Septin-2 is commonly shortened to SEPT2

Why not short it to "7-2"? Or even just "5"?!

The whole thing smells like good old Microsoft bashing and/or poorly disguised advert for Google Sheets.

I think we should avoid making accusations of a conspiracy to advertise a product in a published paper without good evidence for that conspiracy.

Spreadsheets are not databases... but everyone insists on using them as such, so this happens.

> Spreadsheets are not databases... but everyone insists on using them as such, so this happens.

They are, by Wikipedia’s definition: "A database is an organized collection of data, stored and accessed electronically".

So is a bunch of files in a folder then.

That's right. Many databases are literally just folders of files once you look under the hood - for example, here is PostgreSQL's layout:

https://www.postgresql.org/docs/9.4/static/storage-file-layo...

Note in particular the comment "Each table and index is stored in a separate file" which shows that the file layout is not trivial.

SQLite is another database, but it also is "just files in a folder:" the main difference is that there is a single file for all the tables and indexes but it uses several other files in the same directory as well:

https://www.sqlite.org/tempfiles.html

Note that MS SQL Server uses much the same strategy as SQLite, although you have the option of splitting it across multiple OS files if you need more control over physical storage:

https://docs.microsoft.com/en-us/sql/relational-databases/da...

But if SQLite is a database, how is .xlsx or .ini or .json fundamentally any different? I don't think there's anywhere you can draw a bright line that will include all well known databases but exclude things that "merely" file formats. If we draw the line at "transactions," for example, we'll find that MongoDB and ElasticSearch no longer count. If we draw the line at "user access control" then SQLite and redis are not databases.

Older RDBMSes like Oracle may eschew OS files in an attempt to gain more control but they can't escape the fact that they too are are fundamentally just "organized collection of data, stored and accessed electronically." And then there are in-memory databases - which do not use the disk at all, but are plainly still considered "databases" - it's right there in the name! These varied examples show that the word "database" is used very broadly and any definition narrower than "A database is an organized collection of data, stored and accessed electronically" will certainly fail to include many instances of things which are commonly referred to as databases.

> "A spreadsheet is an interactive computer application for organization, analysis and storage of data in tabular form."

By wikipedia's definition, the application Excel is the spreadsheet. I wouldn't call Excel.exe an organised collection of data.

On the other hand, the relevant articles also state " A spreadsheet may also refer to one such electronic document" and "the term "database" is often used casually to refer to both a database and the DBMS used to manipulate it."

I think we both know the parent's point -- Spreadsheet applications aren't general purpose database management systems; and most databases can't be cleanly implemented as spreadsheet documents.

If everyone uses something as an "x", doesn't that make it an "x"?

The meaning of a word is determined by its usage.

Perhaps we should ask why people use it that way, and what can be done to improve the situation, instead of arguing that the whole world is doing it wrong.

Still better than a colleague of mine who insists on using word to display tables because she knows where the sort button is.

Because it takes 6 months to get approval to have a new database created, two weeks to actually get access to it after it is created, and then every single database query has to go through code review and change control. Oh, and everything must be a stored procedure, no exceptions.

And it’ll be billed out at some ridiculous fee because there are 9 layers of ticket clickers, each of which filter any request made such that the actually contractor gets told a story very different from what was intended. Academia is a strange land.

Or they could use sqlite or even access, both of which are single files and should take about the same admin overhead of a new text file. Speaking of text files, they might be a better option here too.

If it's being handled by excel already then sqlite would be overkill.

I think that it is a result of training or lack of it. Spreadsheets don't scale well and have validation issues but are accessible. That is its own skillset unfortunately to do properly. I have seen scientific code that is frankly painful but their flaws are understandable as I would probably make a hash out of their subject.

I think you really hit on the crux of the problem here. The training needed to even put together a genetics paper, much less published and read by others in the field, is /easily/ 7 years of graduate work—NONE of which is spent learning to code! There ARE better tools, but the incentives aren’t aligned for genomics researchers to learn them.

Which is why bioinformatics is such a great field! Biologists are aware of the problem, and better late than never...if you’re interested in being part of the solution to this problem, there has never been a better time to get started!

It's not that incentives aren't aligned for people to start using better tools, it's that Excel is largely good enough. So we all deal with stupid Excel hashing of specific genes (because we all know which genes are this issue by this point).

This isn't exactly a new phenomenon. When I was in grad school (early 2000s), using Excel for gene names was already a well known issue. Some researchers are never going to use anything other than Excel for this even when people like me harp on them to stop treating Excel as a database. It's "good-enough", so it gets used more often than it should.

A lot of things contain errors because of Excel "helpfully" changing data. Speaking as someone who's spent a lot of time dealing with databases and spreadsheets provided by non-technical people to input.

Same here. I frequently had to demand data in tab delimited format. Comma and quote delimited didn't make the nut sometimes, when values included quotes. Sometimes | delimited was the only workable solution.

Ideally they should use a formal RDBMS, but that cranks up the cost of the study. A data QA team also helps. The question is whether these measures are "worth it". Are the Excel errors significant enough? Are 10 studies with a conclusion flaw rate of 30% better or worse than 5 studies for the same cost with say a 10% conclusion flaw rate? A cost/benefit analysis on this can get tricky.

In almost any endeavor there is a quantity-versus-quality trade-off per given unit of resources. In the IT field, we often say: A: cost, B: speed of delivery, C: quality. You can only pick 2 of the 3, which are they? It's kind of the same principle, but with delivery time more explicit.

People doesn't realize that what they are doing in Excel is no other than the programming. If the task requires programming, it should better be handled by a programming language which support version controlling and debugging.

True. But isn't it a bit too easy to just tell people to stop using Excel (which they won't do anyway) instead of giving them something to help them solve their problems _within_ the Excel/spreadsheet world?

Departments, journals and other institutions could crowdsource an Excel plugin to solve this. (Or at least to lint the spreadsheets.)

Maybe, but, even leaving aside the knowledge and comfort zones of the people doing the work, many of the people who do signficant Excel work are business-side staff who are prohibited access to programming tools by corporate IT policy, but allowed access to Excel.

As someone who way too many times have had to try to wrangle data sent as excel sheets into useful shape, this doesn’t surprise me even a little bit.

I use occasions like these to point the website operated by the fine people of the European Spreadsheet Risks Interest Group: http://www.eusprig.org/

Does this affect the research itself, or only the presentation of it?

It can affect the research - doing subsequent analysis on the resulting data; searching for all genes with the name "J12" resulting in no hits, because it's been renamed to "January 12th". This is especially true when sifting through thousands of hits, where searches become more powerful than just listing/sorting the top 10 hits.

There are apocrypha in the field about when such results actually impacted results and grants. Not often, but not unheard of either.

Also, we should be using Entrez IDs or ncbi accession numbers instead of the horrible gene symbol system

Corrected headline: "One in five genetics researchers are careless with their data"

See also "A poor workman blames his tools"

I have sometimes had Excel force pasted data into date format, even after I've explicitly set the entire row to text format. I had to go back and manually retype every date-like entry. It doesn't always do this, but it's frustrating as hell when it does. I can see even an attentive researcher being tripped up by that.

It's an irritatingly common requirement for supplementary data submitted to a journal to be in the form of an excel file, so I'd bet a number of those errors were caused by researchers just going through all the hoops required for publishing.

Chiming in with the first big name journal I thought of—Nature, which lists Excel as a more appropriate format for supplementary data than a table: https://www.nature.com/nature/for-authors/supp-info

It doesn’t mean Excel is the right tool, but it does speak to a fundamental problem: it’s a non-starter to try to change the entire scientific community in one move. We need to find ways to make the tools people use do a better job at doing what we want them to, rather than shouting that they’re using the wrong tool...

>A poor workman blames his tools

This phrase is used far too much when defending buggy or user-unfriendly software. A good workman forced to use bad tools won't have a great time.

I think Excel is better than its competitors but I'm not very familiar with academia. If what other users mention about being forced to reenter already complete data into Excel, which then arbitrarily converts entries into dates, is true, this is absolutely an issue with the tool and/or bureaucracy.

[2016] One in five genetics papers contains errors thanks to Microsoft Excel

Thanks; we added the year above.

Thanks to the person whom ran their genetic data through their Excel autoformatter?

Is this what really passes as science around here?

Oh boy... I've got some bad news for you...

What is it?

That most geneticists aren’t as good at excel as you think

I don't know how good most geneticists are at Excel, it just seems to be the wrong tool for the job.

Unfortunately, in academia the only correct answer to the question, ‘what is the right tool for the job?’ is the question, ‘what does your adviser use?’ Excel is not the optimal tool—I agree. But, it’s the tool (many) geneticists use, and it’s important to find a way to make it work /better/ for the purposes it is /being used for/, rather than taking up space complaining that it’s /not the right tool/.

Unfortunately Microsoft Office is pretty much the software bench biologists use. Excel for data files, Word for papers, and Powerpoint for presentations. Bioinformaticans may use R for analysis and LaTeX for papers and presentations, but these are seen as needlessly "nerdy" by bench types.

Is Excel open source? Sure, I could maybe write some VBA scripts to help Excel do something better (and have) or put in some feature requests in their issue tracker (is there a public one?), but I really don't get the attitude that "taking up space complaining" is something to be avoided.

Reply on news.ycombinator.com