Scientists rename human genes to stop MS Excel from misreading them as dates
theverge.com
theverge.com
Of course Microsoft has thought about this, it's silly to think they never have and we're smarter. Yes, this behavior is annoying and US-centric, they know that. But they also know that breaking compatibility with all these scripts and macros would be the worse problem in the larger picture. That picture is huge, it would be on the order of the scope of the Y2K effort to modify every code everywhere that's ever touched Excel dates.
Would you want Excel to introduce a "quirks mode" to handle this sort of thing?
that's a fairly sophisticated solution, but it's just one of many potential approaches to fixing the problem.
They could easily make it an optional behaviour.
It is sorted from the greatest unit (year) to the smallest unit (second). If you treat them as text and sort alphabetically they still get sorted from oldest to newest.
Other formats don't sort properly.
writing dd.mm.yyyy is like writing time ss:mm:hh
writing mm/dd/yyyy is like writing time mm/ss/hh
If I really have to put YYYY at the end of the date I use the 'dd-mmm-yyyy' format which excel translates based on client locale:
- 13-mar-2020 in enUS
- 13-bře-2020 in czech
https://en.wikipedia.org/wiki/ISO_8601
* YYYYMMDD for dates
* YYYY-MM for months
* YYYYMMDDTHHMM for time where T is the capital letter T. Two additional digits can be added for seconds and then as many additional digits as needed for precision.
------------------
[Edited for format]
- use the mouse to select precisely the part of the word/URL/string you want for Ctrl-C purposes
- it autocorrects your selection to the whole word including a CrLf if it is nearby.
Aaargh!
That said, I completely agree with the tangential issue of U.S. dates being misleadingly different in format compared to non-U.S. Always an issue when teaching data/spreadsheets to a class with at least one non-American – but also a good reason to teach them the value of ISO8601 :)
If you want to know what date and time it will be 476 days and 12 hours from today you can just do =TODAY()+476.5
This is very useful when the requirement is to 'happen every 10 days' or you're looking for '30 days in the past'
Excel has a very low barrier of entry compared to pandas while boasting an immense amount of power and features. I think it was not an easy challenge to keep it going over the decades.
> US dates I'm a non-US person and I fixed this simply by changing my locale to enUS everywhere. It has an added benefit of not having weird translations of everything in random tools and excel functions not being localized to their cringy versions in my native tongue.
Also, data being interpreted different based on region/language settings is a sure way to end up with bugs, so I think its a terrible thing that Excel does this.
I'm surprised there hasn't been a dotfile option added yet
However, Excel remains a nice tool for "I'll just look at this CSV with the final results from this analysis, sort it by correlation, and see if any of the usual suspects are up top". And if the next step is "yeah, that looks fine - I'll just copy the top 100 genes into this convenient GUI pathway analysis tool", you're suddenly exposed to whatever Excel did to your data.
And as for "why not libreoffice", most researchers I personally run into are strong molecular biologists who've learned a subset of R for their uses; they're not really likely to go out and find libreoffice on their own. Besides, the writing process for papers includes sending drafts and spreadsheets to doctors and pathologists and editors, who are probably on hospital computers with a short whitelist of programs ... and I don't really want to debug subtle compatibility issues in the sort of garbage fire those documents can turn into.
Journals don't care, referees/reviewers might care, but they are unpaid and they usually don't want to rock the boat that much.
Now imagine you load a file with 1000s of values in the form AB/CD, many are trashed. If you save the file, you've lost the original data.
All because it might save some data entry drone 5 seconds to expressly make something a date.
There are then also issues about whether "01/02", assuming it actually is a date, should be the second of Jan (US) or the first of Feb (EU, UK, North America ex US, Africa and Pacific regions). In many places, based on some arbitrary and we'll hidden settings, you will get the wrong result.
I honestly think the only reason excel does this is to force you to use excel formats and make it harder to work with non-MS products...
It's people using a tool without knowing said tool. You can disable auto-formatting (or even better yet - set the column data type) with a simple click.
Back in the day I actually wrote a function that would undo this for some sheets that people kept breaking...
The result is a rich deep program that users can grow into, rather than a shallow trivial program that optimizes for the noob experience and leaves power users out in the cold.
[1] https://genomebiology.biomedcentral.com/articles/10.1186/s13...
Recently I was working with some colleagues, being I the computer savvy and them the lab people. I send them some data in CSV, that when opened in Excel turned 123.456 into 123456 (it was a problem with locales, some people using "," as decimal and some using "."). We noticed because the values should be between 0 and 1000. But what if the column could be between 0 and 1000000? A small quantity of numbers bumped up by a factor of 3 could fly under the radar, and distort further measurements. And the error is undetectable forever once published.
I like it better the programming language approach: look, this is how you write a string, this is a char, this is a float and this an integer. "2020-08-04" is a string until you ask me to turn it into a date. "SEPT1" is a string, and you are going to do quite the gymnastics to make me understand it as "date(2020, 9, 1)". Do you like "," or "." as thousands? Then we first turn the number into a string and then format, but the original number is kept.
I use python almost exclusively for data analysis but still open files in excel to view them for lots of reasons.
Nothing in excel is reproducible, but it’s still on all my computers.
Did an analysis a few years ago of the programs run At my organization by job series and scientists run a lot of excel.
Back down? Or pick a better tool. If Excel proves to be an unreliable tool for your job, use a better one. Alternatives exist, ranging from Google Docs, and LibreOffice, to simpler light-weight spreadsheets. Or possibly more specialist tools.
Why does everything always have to be put in Excel if Excel is such a poor tool for so many things?
Excel is a “pretty darn good” tool for 95% of tasks. If your work has highly varied workflows, then that flexibility more than makes up for its failures on the last 5%.
If you have very specific workflows on the other hand, you may find value in replacing Excel with a specialist tool. But let’s not pretend that specialist tools don’t also have their own shortcomings; at best they’ll achieve 99.9% coverage of tasks.
Unilaterally declaring you will not use names that confuse Excel will fix the problem (until we get new month names, anyway), and not require anyone outside their sphere of influence to comply.
I had the same situation at work, as an aquired company we had servers named devX, the new parent had servers named devX and pushed ssh config files to our laptops that made it impossible to connect to our servers; we asked them to fix it the first couple times, but eventually renamed our servers to vedX to avoid the issue. It wasn't the right solution, but it was a stable solution and let us get on with our lives instead of fighting with IT.
* A well known tendency to mangle date and gene data under the guide of being 'helpful'. * Easy to making mistakes when cutting and pasting cells. * Difficult to see what is going on in a spreadsheet. * Poor handling of CSV files.
Some of these shortcoming are inherent to spreadsheets. Others are specific to Excel, but hard to overcome due to the weight of backward compatibility.
I have written a product for transforming and analysing tabular data (https://www.easydatatransform.com) that tries to overcome these issues:
* Doesn't change your input data file. * Doesn't re-interpret your data, unless you ask it to. * See changes as a visual data flow. * Operations happen on a whole table or column. * Good handling of CSV files.
Also it doesn't try to do everything Excel does.
It is a fairly new tool. Would appreciate some feedback.
Edit: great call out to 7 days of non consecutive use
Excel is no longer motivated by the original intention of a spreadsheet, and now caters to the lowest common denominator, a piece of graph paper. As such MS has shifted focus from doing calculation to text and graphics layout tool. white text copied from a terminal : white text white background you got it! comma separated numbers : default a long string with commas in it want a plot : it is in the insert menu for some reason, since plots and numbers are no longer excels raison d'être
Your comment about graph paper echoes a comment from a former Excel PM: "The gridlines are the most important feature of Excel, not recalc."
https://www.joelonsoftware.com/2012/01/06/how-trello-is-diff...
Project is fundamentally a critical path scheduling tool, or at least was. Task A must finish before Task B, which must complete before Task C. If A is delayed, then that delay pushes B and C out, too.
This is what it's FOR, more or less.
Manually scheduled tasks don't move. They're set with whatever dates you give them, and do not move in response to delays or whatnot from predecessor tasks.
People wanted this because some (dumb) people insisted that "well, that task CANT move because it has to be done by then!"
This is akin to asking for the arithmetic engine to be turned off in Excel, because by golly you really need 2 and 2 to sum to 17.5.
So? There are tasks like this that simply CANT move, why should a project management tool not have the ability to model that? If you don't like the feature you don't have to use it.
I get that you wouldn't want these fixed dates and if you can just plan and execute your project by yourself, but that is not always the case.
As an example: I had a project where it was really important for us to inspect the Aircraft Type (A321) into which we wanted to install our hardware and software. We where given an opportunity to do so by an airline during the maintenance of one of their A321. This was a fixed date. If we didn't finish our preparations before that maintenance date then we simply would not get this opportunity (or at the very least would have to wait a very very long time for the next opportunity). The Aircraft would simply not wait for us.
Not every task can move and just because you think they should does not make it so. Maybe there is more to Microsoft Project than just YOUR use-case?
I've been working with scheduling and project management tools for 20 years -- not just MS Project (widely regarded as the idiot cousin of the market, honestly) but also things like Primavera.
There's no reason to have a "manual" task in a scheduling tool.
First, if you have a task that can't move, you set it with a deadline and watch your deadline (as well as watching to see if the task moves PAST the deadline). Cementing the task in place doesn't help you; in fact, it actively HURTS you because it hides the fact that your forecast path isn't valid anymore.
Second, these tools ALSO include the idea of constraints. Scheduling tools include the idea of constraints which limit the critical path motion according to specific rules (based on the type of constraint in play).
Using ANY constraint, though, is frowned upon in serious scheduling circles precisely BECAUSE they distort the predictive ability of a critical path schedule. If Task C has a hard deadline of 1 Sept, then you watch you critical path to see if that remains possible. As tasks slip, you stay on top of the chain of tasks to seek opportunities to streamline or reduce scope ahead of critical task so that the deadline can be met.
(Guess what? It's not always possible.)
And you do this because you SEE that the schedule shows Task C moving to the right.
If you lock the task in place, odds are you won't notice that your critical path is collapsing.
The tl;dr is that cementing a task in place in a critical path schedule is not a good way to model deadlines. This is something any competent scheduler will tell you. It's part of the PMBOK, it's built into DoD scheduling guidelines, etc.
>Maybe there is more to Microsoft Project than just YOUR use-case?
Hilarious. I will say it's clear one of us doesn't quite understand the problem domain as well as they might represent, but given my background I know it's not me.
Generally speaking, you can schedule just fine with partial resources as long as your project doesn't need more of a given resource at any given time than can be allocated to it. Really big construction projects and really big government (defense) contracts work like this.
As in, the history of genetics research is now irreversibly linked with the shortcomings of this one software product, which just happens to be incapable of describing the genetics of the organisms that created it.
The clickbait headline is fun, but the real headline is more like "Scientists find it easier to rename things than learn the basics of data entry in the tools they use".
Astounding, I've literally never heard of this in the 20 years or so I've been using spreadsheets. I'll be using it from now on!
It's a terrible app but Microsoft managed to shove it everywhere in education. Same for Words.
Unfortunately Libre Office and co tried to reproduce the terrible UX of both software, while coming up with inferior functionalities because they had to try to maximise compatibility with these MS tools, time not spent on providing actual useful stuff.
Access is a bit better and I wish more non programmers learned basic SQL instead of Excel.
My anecdote is the exact opposite.
I was recently introduced to using Excel and actually writing some VBA inside of it when I helped a non-programmer with a complicated data processing task on a sheet they were populating from a database.
This was the first time I really used Excel, even though I've had continuous access to it since Windows 3.0.
Aside from some small annoyances, it was a generally pleasant experience. I also can't think of any tool that comes remotely close to the power and flexibility of Excel, particularly for people who are not programmers.
I kind of got hooked on it, and now I frequently use it for little data processing tasks that I used to do in a REPL - and the bonus is I can share those with people who are not programmers.
Worst excuse ever. Excel had to maximize compatibility with Lotus 1-2-3 (in the 80s/early 90s, the dominant spreadsheet application by far) from Day 1. It wasn't just that the files were compatible, Excel included keystroke compatibility so 1-2-3 experts could continue navigating the UI with their old commands.
Unlike the LibreOffice team, Microsoft made a superior program in virtually every way, despite the burden of backwards compatibility with another program.
the paper announcing the new guidelines of renaming genes, a work of fundamental importance to all scientists in the world, cannot be read without an expensive subscription to the journal.
https://www.nature.com/articles/s41588-020-0669-3
Thanks science (sarcasm!)
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 against fundamental forces of nature.
This is just an example of that fundamental force winning.
In grad school I had a subletting roommate for a while who was writing code to match some experimental data with a model. He showed me his model. It was quite literally making random combinations of various trigonometric functions, absolute value, logarithms, polynomials, exponents, etc. into equations that were like a whole page long and just wiggling them around. He was convinced that he was on a path to a revolution in understanding the functional form of his (biological) data, and I believe his research PI was onboard.
I guess "overfitted" never made it into the curriculum.
Technically, we call that a "neural network". Or "AI".
Then they go on conferences and brag about it, because they have to (otr they know it's bs). Datasets are soso (you can have a look at QM9...) and for more specialized things, people generally don't bother trying to benchmark or compare their results on a common reference. It's just something new...
And with all that: even without doing fancy statistical methods without knowing too much about it, your theoretical computations might not make so much sense (at least in the sheer number which is pumped out and published)...
People have figured that out long ago [1] (I know the author of that paper lately turned somewhat controversial, but that doesn't change his findings). It's not very widely known in the general public. But if you understand some basic issues like p-hacking and publication bias and combine that with the knowledge that most scientific fields don't do anything about these issues, there can hardly be any doubt that a lot of research is rubbish.
[1] https://journals.plos.org/plosmedicine/article?id=10.1371/jo...
After the 11th nested 'if' statement, I upped the request to a case of beer. I'm not certain he ever got the code working.
To the larger point, scientists are not programmers. They got into their programs to do research. What keeps them going is not the joy of programming, but the thrill of discovery. Programming is nothing but a means to an end. One they will do the bare minimum to get working. Asking hyper stressed out grad students to also become expert coders isn't reasonable.
And yes, that means that the code is suspect at best. If you load the code on to another computer, make sure you can defenestrate that computer with ease, do not use your home device.
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 program just as they know about the chemical composition of smells, or the particulars of differential equations, etc.
And I know your argument isn't "they shouldn't learn programming", but honestly since I keep seeing this same line of reasoning, I can't help but feel that is ultimately the real reasoning being espoused.
Science is getting harder, and its requirements to competently "find the exciting things" raises the bar each time. I don't see this as a bad thing. To the contrary, it means we are getting to more and more interesting and in-depth discoveries that require more than one discipline and specialty, which ultimately means more cross-functional science that has larger and deeper impacts.
I would say most research, to an ever growing degree, is so heavily dependent on software that it's tough to make that claim anymore. It makes no sense to me. It's like saying Zillow doesn't need software engineers because they are in the Real Estate business, not the software business.
I mean, sort of. Some research is essentially just programming; other research can get by with nothing but excel. Regardless, it's unreasonable to ask most scientists to be expert programmers -- most aren't building libraries that need to be maintained for years. If they do code, they're usually just writing one-shot programs to solve a single problem, and nobody else is likely to look at that code anyway.
What if you want to share data with a wetlab biologist who want to explore their favorite list of genes on their own?
Not that I'm saying using excel is bad either. I use excel plenty to look at data. But scientists need to know how to use the tools that they have.
THe basic assumption I have is that when I input data into a system, it will not translate things, expecially according to ad-hoc rules from another domain, unless I explicitly ask it to do so.
It's not clear what data input sanitization would mean in this case; date support like this in Excel is deeply embedded in the product and nobody reads the documentation of Excel to learn how it works.
If you're deciding who gets a large-scale computational biology grant, and you're choosing between a senior researcher with 5000 publications with a broad scope, and a more junior researcher with 500 publications and a more compuationally focused scope, most committees choose the senior researcher. However, the senior researcher might not know anything about computers, or they may have been trained in the 70's or 80's where the problems of computing were fundamentally different.
So you get someone leading a multi-million dollar project who fundamentally knows nothing about the methods of that project. They don't know how to scope things, how to get past roadblocks, who to hire, etc.
I respectfully disagree with this. Excel is fundamentally not suited to analysing *omics data. It's often the default program affiliated with a .csv filetype on people's computers, but trying to get an entire field of scientific research to rewrite itself based on its glorified bugs is...wrong, in my opinion.
If you see wrong things in the world, do you accept them as they are, or try -- however ineffectually -- to force change for the better? I for one bang the drum into the wind and try to get biochemists off it. I teach people to be very sceptical of excel in my stats courses, for example (aside from some showstopping bugs and downright dangerous defaults, its RNG is particularly crap).
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.
Totally relate to this.
I studied a degree because I wanted to rid the world of Excel.
Just like I wanted to rid the world of pie charts.
Turns out the rest of the world is quite happy using both of them, no matter whether I think it's right or not.
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 or bad data processing.
We do allow web-based spreadsheets, that has the benefit of limiting what you can do and drives people to either put a request in with the correct team or take internal courses on how to do it right.
Some people hate it, but then again, some people hate Excel. The difference is that the people that don't hate it now have an empowered skillset that they didn't have before. In theory you could do the same with Excel-oriented internal courses, but we didn't want to do Excel because a lot of us think it's stupid (often simply for emotional reasons but the push was based on hard data like process metrics and user input).
To add to this: it's probably because I find myself attracted to data-driven organisations where using Excel didn't help the workforce much anyway, and the positions I'm in allow for a certain degree of influence on the tools that we support and how much explicit allow/deny we apply on them.
123456789876, 987654321234
After opening in Excel it will convert these to:
1.23E+11, 9.87E+11
Then resaving the file (as a csv) will store the values in scientific notation and lose all of the digits.
you are 100% correct. and yet it will continue to happen.
If someone types MARCH1, DEC1, OCT4, SEPT1 into a column, why should Excel not treat these as dates? Probably 99.999% of users would prefer this auto-understanding. To make users have to then select cell formatting and select date is a terrible UI decision.
It's odd to call such a useful feature a bug.
>Excel is fundamentally not suited to analysing *omics data
But it's excellent for a massive range of basic analysis and especially for a unified platform to share results, which is why everyone uses it. It's good for publication or proposal steps of creating graphs from data output from other more specialized systems, that usually lack the flexibility and widespread use that Excel has.
If my group uses some specialized, often in house, analysis software, my results cannot be spread to others unless I put them into a more common and accessible format. And nothing in the genomics and wider scientific community has anywhere near the widespread availability as Excel.
You might as well complain they also use word docs instead of Latex or troff. Not everyone wants to spend days fighting software instead of doing work.
I received dozens of comments “Just use Open Office / Libre Office”. I am but a man, I cannot change the world. I forgive that which I cannot control. We work with tens of thousands of schools. Getting all of their administrators to install Open Office, and thousands of administrative assistants to remember to use it rather than Excel is simply an impossibility.
The world speaks English. It's an illogical and absurdly spelled language. Esperanto is miles ahead on all logical measures. Yet we will never switch, both because the cost of switching is enormous, and because there is no way to coordinate/organize such a move.
Man, the extent the aviation world has to bend itself to avoid the pitfalls of English....
"to" and "two" sound alike, and so does "for" and "four".
"Turn left t(w)o four zero" or "Climb t(w)o eight thousand"
To avoid that, the lingo is:
"Turn left heading two four zero", "Climb and maintain two eight thousand" (or, "Climb flight level two eight zero").
At any rate, switching to metric would be much better (If I'm 2000 metre high, and I have a glide ratio of 1:10, I can glide 20,000 metre or 20 km. If you're 5000 feet high, and you have a glide ratio of 1:10, how many nautical miles can you glide?), but for the reasons you mentioned, it won't happen anytime soon.
It makes no difference what men think of war, said the judge.
War endures. As well ask men what they think of stone. War
was always here. Before man was, war waited for him. The
ultimate trade awaiting its ultimate practitioner. That is
the way it was and will be. That way and not some other way.
When I worked in clinical trial design/analysis, no one did their analyses in excel, but for initial data formatting and clean-up? /everything/ went through excel, even if the final cleanup was a python script.
You’re not going to have the same ease of eyeballing your data in SPSS or SAS or R.
I would say R is a lot more useful for a quick check on whether all columns have one data structure, contain NaN/NA's, are of equal length, etc.
I'd like to add that we also had an issue with our clients using only excel and there were issues with calculations not working, not so often but with enough people making noise our management decided to get the subscription. I've been using sheets for years even as a student when i couldn't afford an office license.
I often read comments on HN starting with “to people asking” or “to the people wondering”... and introducing a new discussion, usually on another similar topic, basically hijacking the whole discussion.
Those aren't very good for cleaning and wrangling data, even if the output for analysis ends up being a spreadsheet.
The english comparison made in a peer comment is more apt, but wow.
The tech industry is so self-centered we think that Scientists should change their fields because of our bugs.
As for this being an Excel bug: many, perhaps most people don't think it is. That's why it's so permanent: almost everyone likes it.
And it would be really hard to explain why it is sometimes useful to have constraints to someone who normally sees Excel's flexibility as its main strength.
I don't think I've ever seen a non coder use Excel in a sensible way: maintainable, easy to change, consistent meanings of entities, simple to understand. It's always a ball of spaghetti, even for pretty small projects. Loads of VLOOKUPs, external DLLs, buttons everywhere, vba files galore. Plus they lay out the cells haphazardly.
On the other hand a ton of people that don't write software for a living and have no interest in code manage to produce things that help them do their job and automate a ton of tedious stuff.
I think this might be a type of bias:
The Excel sheets I usually look at as a programmer are from customers who want me to write an application for them. Typically because the conventional means (Excel sheets, directory structures, emails etc.) become unmaintainable.
So we end up seeing the stuff that is bad for w/e reason. Often a combination of complexity, inconsistency, usability and sheer mass.
It's roughly the equivalent of saying "whenever my C program gets too big the 'core dumps,' whatever that means - a totally useless language"
Because if it weren't for Excel, most Excel users would have to hire programmers. (And Gnumeric is very obscure, how many non-programmers have heard of it?)
After a few months of working there my boss handed me 3 or 4 Excel spreadsheets to compare to ensure a recent change I made hadn't affected our data (we don't have much in the way of automated tests either). As a software developer, this was a deeply troubling request.
One option was to load them in to database tables so that I could perform SQL queries against the data (Postgres has COPY that works with CSVs), which isn't hard and probably the path most people should take, but I didn't want to write table definitions.
I ended up using https://github.com/BurntSushi/xsv (I am not affiliated with the project in any way). It's a command-line tool written in Rust that performs queries/joins/manipulation/basic analysis against CSV/TSV files. While not as analytically powerful as Excel or Postgres, I was able to verify the data was good and pipe out results into another file without writing any custom code, and without opening a single file.
Perhaps not directly relevant, but the lesser known GNU Recutils looks neat. Perhaps some day I'll find an opportunity to try it out.
https://www.gnu.org/software/recutils/manual/recutils.html#I...
But then again, I use Excel rarely, usually only at the very end of some analysis (even then I prefer R/Python libraries for visuals). So I do have sympathy for wet-lab researchers who rely heavily on Excel.
If you use software in American English but dislike American date and time formats, you might see if your OS respects the en_DK locale setting. It works on most recent linux systems I've tried.
$ LC_TIME=en_US.UTF-8 date '+%x %X'
08/06/2020 01:00:00 PM
$ LC_TIME=en_DK.UTF-8 date '+%x %X'
2020-08-06 13:00:00
Anyone know why? It makes little sense to me.
You can add a single quote before any value to prevent Excel from autoformatting
Or just turn off autoformatting entirely
To prevent this, set your whole sheet to Text before any other steps.
I have not check myself the full list of special characters that cannot be loaded CSV style from Explorer but one should expect consistency with DATA-IMPORT functionality
In a CSV with zip codes, Excel drops the leading zero: Boston, 02114.
A CSV with text ranges: 1-10, 11-20, 21-30... becomes 10-Jan and 20-Nov!
Why don't people use the import options available to them? You can select the precise data type of each column if you know the format anyway.
While the default choices that Excel makes are questionable at times, they're both known and can easily be overridden.
These are easy fixes. I have this problem using excel. Just format your data as text. If you do that and give the excel file to someone else the errors will not be introduced again. The quote above is at best misleading and I would say just wrong. The problem only happens when you import data in a non-excel format like comma delimited. In that case you have to click on a button when importing to excel to import it as "text" instead of automatic excel formatting. It would be great if you could set this as the default (maybe you can, but I don't know how), but it is something you get used to very quickly.
Think of all of the financial people that use excel that would have this same problem. I'm sure there are some mess ups, but I don't think that industry has the same endemic problem with this excel file formatting issue. There are definitely problems with using excel, but I don't think this would have to be one of them.
Edit: Of course it is not an easy fix to get everyone to use Excel correctly, but these types of bad data errors come from all over the place if the person using the data is not careful.
As others have mentioned, the "fix" of turning off auto-formatting is already available. But many here are wondering my Microsoft hasn't fixed the default.
I assume it's for the same reason Microsoft Excel purposely claims the date 1900-02-29 is valid, to be compatible with Lotus-1-2-3. I assume many legacy spreadsheets would break unpredictably if interpreting values as dates by default changed.
these are not meaningless lables https://en.wikipedia.org/wiki/Sonic_hedgehog
mentioned eslewhere in this thread is a nomenclature that allows one to easily find notes referring to the gene from your research library. It was a new cadre of young upcoming scientists that decided to break with tradition and use something familiar to lable genes according to game characters or pop icons.
You go to someone who's never hears of the hedgehog signaling pathway and ask them what SHH is, they're not gonna be able to give you much.
Is there, perhaps, a market for niche spreadsheet applications that serve one particular market?
Edit: or maybe Sublime Text is more what I mean. In any case I’m hoping someone will pop in and say, “well why aren’t you Snappets!?” and then I’ll go buy a copy of Snappets.
I personally use Table Tool for the most basic editing/collection.
[0] https://apple.stackexchange.com/questions/15946/free-csv-tex...
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.
What are you taking about lol
I think he means "remember"
That said: every datetime function I've ever written is also garbage so... glass houses, I guess?
I am envisioning something with double-entry data checking, enforcement of data types, and rules to check validity. Equally important would be the features it would lack - no tools to color or format text, no variable font sizes, no embedded images, etc.
"There was a shooting today on The 800 block of MLK Doctor"
I see as glorious that I'm able to get something close to real database tables out of non-technical people as long as they're in Excel.
I once populated a pretty sophisticated database by giving a bunch of computer semi-literate people Excel tables with only headers and simple instructions. They respected foreign key constraints, etc. that I could never have directly explained.
*in the software engineering way
In the article it even mentions this and links to how to change it:
>Excel doesn’t offer the option to turn off this auto-formatting, and the only way to avoid it is to change the data type for individual columns.
https://www.youtube.com/watch?v=SppKiKIdCkI&feature=youtu.be
It's managers all the way down.
I tend to use "less -Sx 20" to preview data files but try explaining that to a non-CLI user on Windows.
Seriously, I hardly trust the program anymore. I always need to get the truth from Python and Pandas.
Not worth it for the Microsoft power brokers. Very much worth it for a lot of people.
And so the world is sometimes shaped by the narrow money interests of a very small group.
I consider this a failing not in software or Excel, but in education and professional standards. Scientists can be expected to understand and use their tools correctly, since not doing so will taint their data.
Maybe I don't understand the problem, but I did read the article.
Care to explain what I don't understand?
> so when a user inputs a gene’s alphanumeric symbol into a spreadsheet, like MARCH1 — short for “Membrane Associated Ring-CH-Type Finger 1” — Excel converts that into a date: 1-Mar.
I can assure you that if you prepend what you type with a single quote, or an apex or a double quote, what you type will be interpreted a text in Excel, respectively left, center and right aligned.
Even a number, i.e. "45 will result in 45(right aligned), and a small green triangle in top left corner and if you hover on it with the mouse it will pop-up something to the effect of "number formatted as text".
Excel was nowhere in sight, and that was thirty years ago.
If someone wants a data set i give them a .csv it is then thier fault if they plug it into excel.
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.
> Even then, a scientist might fix their own data, but as soon as someone else opens the same spreadsheet in Excel without thinking, errors will be introduced all over again.
I see a few autoformatting options, but nothing corresponding to dates. The only options I see are similar to this:
https://www.journalofaccountancy.com/issues/2016/dec/how-to-...
Create a simple CSV file like
Gene,value
SEPT1,-7
Exce is truly worse than most people can imagine.
[0] https://www.nature.com/articles/s41588-020-0669-3
Please do. The article has a paragraph that addresses this.
I had a Manager that spent an entire week compiling a report on how much times people spent on tasks.
He was very proud as he was showing me this report. I asked him why he didn’t use a pivot table.
While he was asking what that was, I spent two minutes on the same data that he had and reproduced his report. I had a friend and patron for life after that. I was involved in many major decisions for a fortune 50. All because I could work excel.
Ok, seriously, it would be nice to te able to recommend a alternative. I heard LibreOffice Calc is not really equivalent?
The question I have is not about using spreadsheets, but why Excel? It takes way too long to load and the UI gets worse with every iteration. Most people don’t even use 99% of the features in Excel (Find and Replace amazes some people). It is headed on the same trajectory as the abomination of diagramming software known as Visio.
Also, the main problem is that excel changes your input instead of just misinterpreting it. The difference being, the original will be gone.
That's a feature though. Coincidentally it only happens when you paste the data or if you messed up the generation of the file (didn't escape the column you want to leave untouched).
To me it seems like a case of you're holding it wrong. (which I don't wholeheartedly support)
In Apache POI HSSF/XSSF (Java Excel API), the HSSF actually stands for "Horrible Spreadsheet Format".
As a fun example, note that we need isSheetHidden() and isSheetVeryHidden() functions. (http://poi.apache.org/apidocs/4.1/org/apache/poi/ss/usermode...)
Don't include me in that group.
You can also say pretty much many of Microsoft security issues over the years boil down to their programs needlessly overthinking and parsing perfectly valid input, for obscure reasons.
> Sometimes it’s easier to rewrite genetics than update Excel
rather than y'know, scientists cant properly use a tool they're using.
bad journalist.
edit: it even seems some of the commenters here stopped at the byline. quelle surprise
Ugh... everything is politics now.
You can get frustrated about that fact, or you can use it as an opportunity for learning about how and why that is so, and gain a deeper understanding of the tightly linked systems of power relationships all around you.