Live data from Hacker News

Scientists rename human genes to stop MS Excel from misreading them as dates

theverge.com

241–250 of 518 posts

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#241

It'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...)

Honestly though, dates make more sense being month first, that way if you are trying to discover which one comes first, 10th of february or 10th of january, you only have to go to the first parameter in format 01/10 vs 02/10 instead of 10/01, 10/02. Logically, the most broad data item should go first.

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#242
post #119

Earlier quoted context omitted.

I don't even know why anyone still uses this notation in today's connected world. 2020-08-06 is ideal. Russian standard (06.08.2020) is uglier (digits are not sorted by significance) but still unambiguous. But that slash horror? Why would anyone continue inflicting this on themselves and their peers!?

yes, YYYY-MM-DD HH:MM:SS is the only proper format. 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-…

agreed fully

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#243
post #7

To 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…

I'm an ex-computation biologist who did most of his work in python but periodically had to interop with excel.

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.

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#244
post #194

Earlier quoted context omitted.

I work in computational materials science (where ML brings funding) and a funny paper of this kind is here: https://journals.aps.org/prl/abstract/10.1103/PhysRevLett.11... - they are literally trying out 100000s of possible combinations by brute force, to build a "physical model". 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…

> (otr they know it's bs). Well, that's a new acronym for me. I wonder where it came from. Apparently it's "on the real". Sounds like AAVE?

It means off the record.

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#245
post #119

Earlier quoted context omitted.

I don't even know why anyone still uses this notation in today's connected world. 2020-08-06 is ideal. Russian standard (06.08.2020) is uglier (digits are not sorted by significance) but still unambiguous. But that slash horror? Why would anyone continue inflicting this on themselves and their peers!?

>2020-08-06 I've just switched to 20200806. Let it be an int and be done with it.

Human readability sometimes matters.

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#248
when i was undergrad there was an academically priced package for about 500$ you get the entire suite of word excel access powerpoint bells whistles and nice rugged carrier box for all the tomes. i dont groom well i used it until taking an assistants position and we used ...lotus, symphony,and norton utilities and some funky in house coded version of a Dbase, and IC4 [some inventory control utility]

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.

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#249
post #63

Earlier quoted context omitted.

This ignores the reality that one will ultimately have to interact with people who have no understanding of any of these tools. Unless you happen to work in a pure computational biology group, one _will_ have to interact with lab workers, biologists with no training (or understanding of) in R or python, doctors, etc. All these people will know excel.

That's why I'm in favor of this change in nomenclature.

IMO the change is good, but is a case of detected vs undetected.

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.

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#250
Just change the data type of the column in question??

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

Post reply on HN