Slashdot Mirror
20% of Scientific Papers On Genes Contain Conversion Errors Caused By Excel, Says Report (winbeta.org)
An anonymous reader writes from a report via WinBeta: A new report from scientists Mark Ziemann, Yotam Eren, and Assam El-Osta says that 20% of scientific papers on genes contain gene name conversion errors caused by Excel. In the scientific article, titled "Gene name errors are widespread in the scientific literature," article's abstract section, the scientists explain: "The spreadsheet software Microsoft Excel, when used with default settings, is known to convert gene names to dates and floating-point numbers. A programmatic scan of leading genomics journals reveals that approximately one-fifth of papers with supplementary Excel gene lists contain erroneous gene name conversions."
It's easy to see why Excel might have problems with certain gene names when you see the "gene symbols" that the scientists use as examples: "For example, gene symbols such as SEPT2 (Septin 2) and MARCH1 [Membrane-Associated Ring Finger (C3HC4) 1, E3 Ubiquitin Protein Ligase] are converted by default to '2-Sep' and '1-Mar', respectively. Furthermore, RIKEN identifiers were described to be automatically converted to floating point numbers (i.e. from accession '2310009E13' to '2.31E+13'). Since that report, we have uncovered further instances where gene symbols were converted to dates in supplementary data of recently published papers (e.g. 'SEPT2' converted to '2006/09/02'). This suggests that gene name errors continue to be a problem in supplementary files accompanying articles. Inadvertent gene symbol conversion is problematic because these supplementary files are an important resource in the genomics community that are frequently reused. Our aim here is to raise awareness of the problem." You can view the scientific paper in its entirety here.
It's easy to see why Excel might have problems with certain gene names when you see the "gene symbols" that the scientists use as examples: "For example, gene symbols such as SEPT2 (Septin 2) and MARCH1 [Membrane-Associated Ring Finger (C3HC4) 1, E3 Ubiquitin Protein Ligase] are converted by default to '2-Sep' and '1-Mar', respectively. Furthermore, RIKEN identifiers were described to be automatically converted to floating point numbers (i.e. from accession '2310009E13' to '2.31E+13'). Since that report, we have uncovered further instances where gene symbols were converted to dates in supplementary data of recently published papers (e.g. 'SEPT2' converted to '2006/09/02'). This suggests that gene name errors continue to be a problem in supplementary files accompanying articles. Inadvertent gene symbol conversion is problematic because these supplementary files are an important resource in the genomics community that are frequently reused. Our aim here is to raise awareness of the problem." You can view the scientific paper in its entirety here.
I think the thing that really surprises me is that all my professors told me it was hard to get published, when failing to make sure your data was correctly entered into whatever spreadsheet program you used to for number crunching (and creating graphs) was one of the dead basics of working there. Yet 1 in 5 papers has notable failures here? And nobody noticed before publishing them? What kinds of major errands have gotten in, then, if basic spot checks are getting failed?
Why in God's name are you using a Microsoft product for scientific documents?
Those conversions look like cases where the column type during import was left at "General" instead of being set to "Text" as it should have been, telling Excel to try and infer the actual type from the format of the column's contents. It's an awkward situation where the user should be telling Excel what the data type for each column is, but it's not strictly Excel's fault for doing what the user told it to do. IMO Excel should be either changed to not have a default type and to not allow an import until the user's selected a type for each column, or it should throw up an error if it infers different data types for a column for different rows.
That nobody reads all that shit. The only people reading scientific papers are the people writing scientific papers.
Only the State obtains its revenue by coercion. - Murray Rothbard
Many, many people use Excel as a grid oriented editor to capture a mix of text and numeric data. It's massive overkill, and the helpful features like automatic date conversion DO get in the way.
But it's ubiquitously available (either in MS form or various and sundry clones)...so it gets used.
In the year 2016, a disturbing amount of human activity is run through Excel instead of proper databases.
A similar study from 2009 tested for errors in various operational spreadsheets and concluded, "Our results confirm the general belief among those who have studied spreadsheets that errors are commonplace." The Financial Times commented on the prevalence of spreadsheet errors in business, saying it's probably a function of training and organizational culture.
I've heard from a few salespeople in the software industry that their biggest competitor in the SMB space isn't $BigCRMCorp, but Excel spreadsheets that have acreted over the years.
I don't know how much number crunching was actually involved here. I suspect the problem comes from using a spreadsheet as a database. Because databases are, you know, hard.
It was user error, not a spreadsheet problem. Prefacing the names with a ' would have identified them as a string not to be interpreted as numeric or date. If the researchers couldn't manage this do you really think they could have used a database?
That said, yes spreadsheet are overused and abused.
Just do a google scholar search for large hardon collider. None of them will ever live that down, doubly so when it's the title
It could rather be a conversion error. For example, if you have the original in a CSV file (possibly output from one program) where strings have no lead colon, and then load into Excel or LibreOffice, it will (by default) turn everything it can into a numeric format. One needs to be aware of that and ask that the column be converted to text -- which is easy to overlook if you have a column that's mostly non-ambiguous, but somewhere far below is a single date-like name.
I've gotten hit by this many times with CSVs coming out of our school's learning management system, with long numeric student IDs that get turned into scientific notation in the spreadsheet application. In some sense that's easier to catch, because it will hit a whole column of data at once; but even so it's distressing how often I need to backtrack to resolve that.
We know where leadership by an anti-intellectual "strongman" who scapegoats minorities and likes boisterous rallies goes
Type inference in excel has wasted countless hours of my time trying to make sense of corruption caused by third parties using excel. Has gotten to the point where we actively recommend people avoid excel when handling any data they care about. I do fault excel itself because these errors are pervasive. They could have better structured the data imports or made them less creative or asked users for more feedback or have the import do a pass over the entire datasets checking for outliers that may suggest a different type.
When a critical mass is "doing it wrong" becomes pointless and counterproductive in the real world to continue to point fingers at users. Tools are supposed to be useful and if they tend not to be then that's on them.
Without diminishing the other comments, it crossed my mind that the issue here is probably not whether Excel was used in the research. It's one of getting backup (supplementary) data into publishable form. That'd occur after the authors(s) had written their paper using their normal toolset for their work and gotten the paper through review. At this point, they are supposed to package up their data in some format dictated by the journal they are publishing in. Apparently .xls is an acceptable format -- which is not irrational. The format is documented and widely supported.
Anyway, the authors are just cleaning up and getting on with their lives -- cleaning the glassware (if any), paying any bills, archiving their data and scripts, returning borrowed equipment, etc. They are going to convert their data to .xls using whatever quick and dirty tool they can find. I doubt they are going to type tens of thousands of genome codes in manually. They'll use some tool they got from a buddy or write something themselves in Perl or Python or whatever scripting language they know. And they'll check the output to make sure that Excel loads it and that it's about the right length and that the first page or so and the last page look reasonable. And off it goes.
I don't think most folks outside of IT (and probably most in IT) are all that aware of Excel's flaky and sometimes bizarre data conversions. And, assuming that there's an unambiguous one to one translation between gene codes and excel mangled gene codes, this probably isn't a big deal. Anyone using the archived data will scratch their heads, maybe ask around, figure out what's happened, fix the data, and get on with THEIR research.
You can't see ANYTHING from a car, You've got to get out of the goddamned contraption and walk...Edward Abbey
The problem is that the not all tools require a ' to stop this behaviour. And, in fact, adding this may well break these tools.
Ultimately, this is not a new problem. It was first noted about a decade ago in yeast (which uses a lot of very date like gene names). It's a bit depressing it's still happening.
I'm unconvinced that this can be classed as a user error, though. Excel is using a heuristic to determine the data type of a field (probably on a per cell, not per column basis). And that heuristic is failing.
No, this would be a disaster. The acronyms have the advantage that they are, relatively, semantics-free., If we turned them into long hand, then they would describe the gene in some way. Which means that the descriptions in the knowledge would go out of date, or would have to be changed. It's a recipe for instability.
It is very easy to laugh at biologists and think that you know how to manage data better than they do. In some cases, you may be right, but in this case it is not so. Identifiers are there to identify and not describe. This is something we learned with Linneaus, and have stuck with since.
See http://www.forbes.com/sites/sa... for example. It's endemic. They'll probably derail civilisation as we know it eventually, bridges falling, weapons launched. Then, when we're back to the caves/trees and eating nuts and berries (not a bad life outside the cubicles, really), we will curse the evil god Ex-cel and provide blood sacrifices on altars (inscribed with A1, C2 etc.) to keep him away?
On y va, qui mal y pense!
I can see how it helps with people who don't spell very well. But for people who can, it's an outright hinderance. Also, they should grow up and add profanity, The puritan dictionary is a ducking disgrace, utterly shot.
I do not want your cheap brainburning drugs. They are useless for work. And I am a working man today.
Which is too late if the damage has already been done. And if the first 2000 lines look ok, no one is going to do that because, hey, the auto import worked!
bickerdyke
Oh, yeah, the single quote - so obvious that the solution to the problem of Excel's stupid, stupid, stupid default behavior of *silently modifying your data* is to put a freaking Single Quote character in front of every cell data element.
Why can't the default behavior be that the data is just not modified, at all, unless I tell Excel I want it to be.
Once we ran all our British chemical patent info through software to convert it to good old-fashioned American English. All instances of Tungsten Carbide (WC) were converted to "Toilet".
-----
Sorry, I'm only a 1336 h4x0r.
But they used the wrong rounding mode.
---- Den ene knappen er powerknapp, den andre er Bender voice knapp "Bite My Shiny Metal Ass"
No, it is a conversion problem. .csv "wizard" , it won't convert.
True, if you make the column text in the import
But if Excel does convert text to a date or to a number, you lose the original text and have to import again to get it back.
Numbers displayed in scientific notation (or any other format) and dates displayed however Excel is set up to display them, are stored internally as just numbers, and the original text is not saved anywhere.
You are right, I stand corrected. I thought that the converted cell would revert back to the raw data if the column type was changed, or if one exported as CSV. A little testing shows I was wrong.
This posting is provided 'AS IS' without warranty of any kind, implied or otherwise.
Penn Jillette on Donald Trump, Hillary Clinton, And Why He's All in on Gary Johnson — 2 August 2016
I watched this video yesterday. There a fabulous exchange 24:30–30:00 on truth and naievity.
Once upon a time I would have ventured that most Slashdot readers would want to view this. It had me thinking about my own life 1985–1995 where I watched the software industry turning into a train wreck, where every seventh train car is painted bright orange and lettered in an ominous Area 51 black stencil font "patch Tuesday", with sparks flying off wheels seized (and reseized) for so long they resemble lopsided pentagons.
I used to think to myself "surely these are just temporary conditions due to the extreme rate of expansion of the software industry, and it will all settle back down to sanity as we crest the exponential growth phase". But no. Like Jillette, I was a die-hard naievitarian. Lesson learned.