Scientists rename human genes to stop MS Excel from misreading them as dates • Hacker News Board

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

#341

Post by Balgair » Thu, Aug 06, 2020, 7:12 PM UTC

meow1032 wrote: post #256

Balgair wrote: post #178

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

> To the larger point, scientists are not programmers. They got into their programs to do research. 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 maybe misspoke. I meant that scientists do not go into science to program, they go into it to discover and do research (among many many other things). Sure, some do find joy in good programming, but that's not why they are there to begin with. Becoming a better programmer isn't their passion, and those skills remain underdeveloped as a result.

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

#342

Post by erAck » Thu, Aug 06, 2020, 7:16 PM UTC

otherme123 wrote: post #229

mcv wrote: post #162

> "Why, exactly, in a fight between Microsoft and the entire genetics community, was it the scientists who had to back down?" 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…

LibreOffice AND Google Docs fall in the same exact spot than Excel here. Try it. Type "SEPT-2" or "SEPT2" in any of them and see what happens.

LibreOffice Calc: "SEPT-2" is a date, "SEPT2" is not.

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

#343

Post by yardstick » Thu, Aug 06, 2020, 7:17 PM UTC

FabHK wrote: post #164

yardstick wrote: post #123

Earlier quoted context omitted.

Same here, but with IMEI numbers. Excel silently truncates them, it’s frustrating. Why can’t the auto format detect and just store them in text format?

Because most people want to import numbers as numbers most of the time. Excel detects numbers and treats them as numbers. You do have the option to import them as text. So, Excel (as usual) does what's the best for most people.

Why does excel truncate a very long number silently? That is what happens with IMEI numbers. It cuts off the last 4-6 digits. It’s destroying numbers.

Excel should know any number that is longer than it can internally support should be stored as text. Or they need to introduce a data type that can store arbitrary precision numbers.

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

#344

Post by ryukafalz » Thu, Aug 06, 2020, 7:18 PM UTC

zozbot234 wrote: post #279

jaywalk wrote: post #274

Earlier quoted context omitted.

You're simply incorrect. If you set the column format to Text it will never convert data to dates, including when you open the file.

Great, how do you set a custom column format in a CSV file?

I'm only familiar with LibreOffice and not Excel myself, but: if you want to be sure a column is treated as text in a CSV file, you have LO quote text fields on save, and have it treat quoted fields as text on import. I assume Excel must have similar options.

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

#345

Post by [deleted] » Thu, Aug 06, 2020, 7:19 PM UTC

takluyver wrote: post #78

Programmers love to complain about Excel, but we happily use YAML, which has essentially the same footgun: certain strings (like 'on') need quoting if you don't want it to interpret them as something else.

[deleted]

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

#346

Post by rini17 » Thu, Aug 06, 2020, 7:19 PM UTC

zozbot234 wrote: post #279

jaywalk wrote: post #274

Earlier quoted context omitted.

You're simply incorrect. If you set the column format to Text it will never convert data to dates, including when you open the file.

Great, how do you set a custom column format in a CSV file?

LibreOffice, on opening of CSV file, always pops an import dialog where you can do this. To keep the column format permanently, save as ods.

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

#347

Post by yardstick » Thu, Aug 06, 2020, 7:20 PM UTC

pbhjpbhj wrote: post #252

Cactus2018 wrote: post #155

Earlier quoted context omitted.

Why can't there be a flag (defaulted to off) to control auto formatting

I was thinking that perhaps the autoformat options could be explicitly shown in the column headers number date currency A B C and you could click on the format to change it. Italics could show assumed formats, bold could show user set formats. I'm newly returned to (very minor) Excel use and assumed formats with no feedback seems wrong. My situation is dates _not_ being picked up as dates and it not being obvious tha…

I would be happy if Excel supported some way to specify the column type in the CSV itself. Ie first row headers, second row data type/hint, subsequent rows the actual data.

At the moment users need to remember every time they import the CSV to specify A data type for each incorrectly handled column. Suffice to say they don’t always remember... and with large CSVs with hundreds of columns it’s just a pain.

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

#348

Post by hannob » Thu, Aug 06, 2020, 7:20 PM UTC

acidburnNSA wrote: post #76

meow1032 wrote: post #47

Earlier quoted context omitted.

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 have a strong feeling that, if people really put an effort into reading and replicating more papers, we would find that a lot of what's being published is simply meaningless. 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 va…

> I have a strong feeling that, if people really put an effort into reading and replicating more papers, we would find that a lot of what's being published is simply meaningless.

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...

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

#349

Post by FrojoS » Thu, Aug 06, 2020, 7:21 PM UTC

pintxo wrote: post #34

LatteLazy wrote: post #26

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

For Germany this converts to the first of Feb, and this way of entering dates is just so convenient, as you can type the whole date just using the keys from the num block. We do actually support this format in our app for date entry, simply because it's so convenient.

The usual way to write the date Feb 1st in Germany (and I think anywhere but the US) is 01.02 or 1.2. but not 01/02 And the . is part of the num block as well.

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.

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

#350

Post by importantbrian » Thu, Aug 06, 2020, 7:22 PM UTC

jordigh wrote: 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…

Whenever people ask me about getting into the field and if they should learn Python or R or Scala or whatever else, I always respond with you need to get really good with excel/Google Sheets and SQL. Then worry about everything else. I can't tell you how many times I've done an analysis I'm really proud of in something like R Markdown only to have the end user say that's great now can I get it in an excel? I just deliver everything in excel as a default now.