So all that noise for people that don't know to change the data type of a column?
Scientists rename human genes to stop MS Excel from misreading them as dates
261–270 of 518 posts
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#262Earlier quoted context omitted.
Excel is not fundamentally suited, but it's fundamentally used. I wish you luck, but I long ago declared defeat against Excel. I can't prevent everyone from using it. I have come to accept that whatever Excel does is what I have to live with, because someone, somewhere out there hasn't heard the anti-Excel gospel yet.
I'm actually having a lot of scenarios where Excel is banned except for some managers, C-level and board type stuff. If you produce, touch or directly act on data, Excel is no longer allowed. On the other levels the people that still use it (because they think they have the skill - which they often don't) can't do meaningful direct-action on those files anyway so that solves part of the problem of acting on bad data…
The difference is that the people that don't hate it now have an empowered skillset that they didn't have before.
Not knowing how to use the primary tool in your field because you only know how to use a web-based spreadsheet with 1/100th the functionality is not an "empowered skillset." It's a cruel trick to play on employees, who will have difficulty finding further jobs in that field because they don't have a basic understanding of the tool everyone else uses.
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#263Earlier quoted context omitted.
In the case menctioned in the article, LibreOffice is on par with Excel. Don't believe it? Open up LibreOffice, type "MAR-1" or "SEP-2" in any cell, and look how easy is your data corrupted. I have meet some other glitches: identifiers in the form of "1E123" get turned into scientific numbers. The column was something like "1A123", "1B123", etc. Those things are sneaky: you can have thousands of rows, and Excel/LO do…
> This shit could be avoided if they had a "scientific mode" to not be smart with your data, and be the default mode for any file in CSV/TSV mode. There's a whole lot of business use of CSV/TSV with Excel that benefits from the existing defaults. Heck, I've seen federal government websites distributing code lists that are actually in CSV format with .XLS extensions and that are expected to use the default “smart” con…
Then we are doomed to the lowest denominator. The bad actor here is the one that expects some software to magically change your data, and he should expect an error or at least a warning or asking for confirmation.
To good actor is people who carefully care and curate their data, just to be corrupted silently by the program.
Lets say then, keep the default but add at least some sort of "respect my data as it is, because this is a CSV with CSV extension" mode.
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#264To people asking, "why do they use Excel?" that's like asking "why must we be subjected to gravity?" The whole world's data ultimately comes from or ends up in an Excel spreadsheet. Sure, we might use other intermediate data storage methods, but in the end it's going to go into some scientist's or some politician's computer, and by golly it's gonna be in Excel. Trying to rally against Excel is like trying to rally ag…
I don't disagree completely with this, but just want to point out that it's kind of a bad smell to have computational biologists who are - as someone in the article puts it - computationally illiterate. I have met lots of these types over the years, and usually their methods are kind of a gong show. If you can't properly sanitize your data inputs on your column headers, why should I trust that you've treated the rest…
Sorry for asking but are you familiar with how MS Excel aggressively converts data to dates? There's no way to "sanitize" it (without resorting to hacky solutions like including extra characters) and even if you fix the data, it will re-change them to dates the next time you open the file.
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#265Quote: "There’s no easy fix, either. Excel doesn’t offer the option to turn off this auto-formatting..." What kind of loopy Excel variant the article's author is using? It's right there in settings, you can easy stop auto-formatting. Yes, default installation has it on, but it can be turned off.
Also you can single quote ‘MARCH1 to keep it as string. This is safer as when you share the file you don’t know if the next person has the same settings change you described. Either way, it’s only 27 genes they had to rename. Seems like a good choice on their part to just rename them. Hopefully they’re doing this check during the naming stage for new genes.
Now save that file as CSV (maybe because you want to process it with some non-Excel tool) and re-open it. Mar-1.
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#266Most of the reactions seem fall into three categories:
1. "You (the individual) should stop using Excel". Good advice. As the article mentions, though, if you ever send a tabular data file to someone else to edit, there's a fair chance they will open it in Excel and corrupt the data. Excel's CSV import/export cycle has even more traps than using xlsx consistently, so in some sense individual avoidance of Excel makes the problem worse. You could send SQLite files or somesuch so your colleagues can't work with your data, I suppose.
2. "You (all scientists / whatever non-programmer group) should stop using Excel for data analysis". True, but getting everyone to change is hard. Both top-down (thou shalt not use Excel!) and market-driven (Airtable! LibreOffice!) haven't worked so far. Hopefully there will be more progress on this front.
3. "Scientists should learn to use their tools". Even if a user knows all the scenarios in which Excel corrupts data, it cannot in general be stopped from doing so. If you set column types up front, type every value manually (no copy-paste) & save and check on each edit, and then never re-save the file again or let anyone else re-save it, you're safe. Probably. I sort of expect a bunch of replies saying this isn't safe at all. But if the file is edited, you need to check the whole thing. Eventually an error will slip through.
The only way I've found to safely work with Excel files, or files that might be edited in Excel, is to put the data files under version control and always check the diffs. Often someone else's import/export cycle will change the quoting style on the whole file. In this case, the work needs to be repeated so the diffs are clean and the changes can be verified. This is a necessary but very obnoxious part of this process. Version control targeted at non-programmers and non-text files would help a lot.
There are also tools to to flag potential data corruption errors [1], but error detection in the absence of the original data won't be perfect. And if it is manually run, it won't be run consistently. Most people don't have continuous integration pipelines for their data.
Excel is very powerful and has a lot of potential to improve non-programmer productivity. It is unfortunate that avoiding data corruption is such a minefield. The situation could be significantly improved by anyone with a decent product and a great marketing strategy.
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#267It's honestly amazing that Excel hasn't fixed this issue. It's pisses off an enormous number of users especially in basically any non-US country (even if 01/02 is a date, it isn't the second of January in most of the world...)
Why hasn’t the field standardized around another tool? Is the list of excel plugins that something libreoffice couldn’t replace it, where they could just make add the options they need? My guess is the field is mostly lacking people interested in working on tooling like this?
Because actual quality (usability of research, open access, reproducibility, data availability, sane exposition/introduction, proper methods, documentation and code) is not as important as producing a fancy manuscript.
Journals don't care, referees/reviewers might care, but they are unpaid and they usually don't want to rock the boat that much.
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#268Earlier quoted context omitted.
In grad school I had a friend that was doing olfactory (smell) research on rats with tetrode drives (wires in their brain). He was looking at the neuronal response to smells that they gave the rats and had a few signals to match up. There was the signal from the arduino running the scent gates, the amps that reported the weak neuronal currents, the nose lasers that gated the ardunio, etc. He was having a hard time ge…
I keep seeing this sentiment when it comes to those in the natural sciences, but it makes no sense. I could replace "programming" in your above little bit with "mathematics" and it would be just as weird. Our modern world runs on computers and programs, just as our modern world and modern science built itself on mathematics and required many to use it. So too the new world of science may require everyone to know to p…
Most scientists are not great at math either.
Again: these are tools that are means to an end. They only need to work well enough to get the researcher to that end.
A lot of what are considered essential practices by expert programmers are conventions centered around long-term productivity in programming. You can get a right answer out of a computer without following those conventions. Lots of people did back in the day before these conventions were created.
That's not to say that everybody with horrible code is getting the right answers out of it. I'm sure many people are screwing up! My point is just that ugly code does not automatically produce wrong answers just because it is ugly.
By analogy, I'm sure any carpenter would be horrified at how I built my kayak rack. But it's been holding up kayaks for 10 years and really, that's all it needs to do.
I will add that in general, statistical analysis of data is not by itself adequate for scientific theory--no matter how sophisticated the software is. You need explanatory causal mechanisms as well, which are discovered by humans through experimentation and analysis.
And you can do science very well with just the latter. Every grand scientific theory we have available to us today was created without good programming ability, or really the use of computers at all. Many were created using minimal math, for example evolution by natural selection, or plate tectonics. Even in physics, Einstein came up with relativity first, and only then went and learned the math to describe it.
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#269To people asking, "why do they use Excel?" that's like asking "why must we be subjected to gravity?" The whole world's data ultimately comes from or ends up in an Excel spreadsheet. Sure, we might use other intermediate data storage methods, but in the end it's going to go into some scientist's or some politician's computer, and by golly it's gonna be in Excel. Trying to rally against Excel is like trying to rally ag…
I don't disagree completely with this, but just want to point out that it's kind of a bad smell to have computational biologists who are - as someone in the article puts it - computationally illiterate. I have met lots of these types over the years, and usually their methods are kind of a gong show. If you can't properly sanitize your data inputs on your column headers, why should I trust that you've treated the rest…
For the most part we aren't talking about computational biologists but experimentalists using Excel. People at the bench need to collect their data somehow, and using Excel for tabular data and Word for text data is just what they know. Typically they then pass these files over to computational biologists for analysis. Yes, it would be nice if they would use more appropriate tools, but I know from experience that the typical result of trying to teach them better tools is the experimentalists just rolling their eyes and saying that they don't have time to learn some nerdy program because they have experiments to run.
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#270No one knows how to use excel.