3 ms·

>based on its glorified bugs If someone types MARCH1, DEC1, OCT4, SEPT1 into a column, why should Excel not treat these as dates? Probably 99.999% of users wou

>based on its glorified bugs 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.

mnw21cam 6y ago
> If someone types MARCH1, DEC1, OCT4, SEPT1 into a column, why should Excel not treat these as dates? It should treat these as dates, unless told otherwise. But Excel goes one step further, and throws away the text you originally typed in. That means, if you type "DEC1" into a spreadsheet, see it looks stupid, and change the data type of that cell to "Text", then it's too late, and Excel will show "37226". It has destroyed the original data and replaced it with the numeric equivalent of the date. The data type of the spreadsheet cells should be a display format only. The text you typed in (or loaded from a CSV file) should be preserved, and the data type of the cell should only dictate how that is treated and displayed, not how it is stored.
- FabHK 6y ago
  Excel does that, largely: cells have a datatype, a value, and a format (among other properties). Changing the format leaves the value unchanged. However, when you input something into a cell, Excel has to parse it and decide what value and datatype to assign to it. That is just inevitable, and the same everywhere: in Python, `x = 3` assigns an integer, `x = 3.` a floating point, and `x='3'` a string. Similarly hints (or explicit specifications of type) are possible in Excel. However, given that, what you typed originally is not recoverable anywhere (Python doesn't record whether you wrote `x=3.` or `x = 3.00`, why should it?). So, to the extent that it is reasonable, Excel does what you request, and to the extent it doesn't do it, it's not reasonable, I'd say: A cell need not maintain the original pre-parsing input.
- ChrisLomont 6y ago
  > The text you typed in (or loaded from a CSV file) should be preserved That’s even worse, since a user assumes the cell holds what it shows. Excel, for example, auto completes entries, which is extremely useful for data entry, and for accuracy. If it only held the few characters entered, now it has to repeat exactly the same autocompletes every time it opens, and has to do so even if the data that originally caused the auto complete changes. All this is nearly algrithmically I’m-Seville without changing the document into a history. And now CSV is useless for data transfer. It’s vastly more sane to change to the data the user meant to enter, show it to the user for confirmation, and save that. That the 99.99% use case bites the remaining 1 in 10,000 is a perfectly reasonable trade off, done throughout society in uncounted ways.
glofish 6y ago
It is shortsighted to call this behavior a "useful feature". It was and is a grave and serious mistake. Automatically converting SEPT1 to a date caused far more damage than the benefit of the fleeting convenience. The damage that this "feature" caused and will keep causing goes far beyond the domain of bioinformatics. Even in bioinformatics things have not been "solved", for the next decade the same gene will have two names when looking it up in previously published data.
- FabHK 6y ago
  > Automatically converting SEPT1 to a date caused far more damage than the benefit of the fleeting convenience. Who are you to judge? For the novice, even for most users it'll be what they want. And for the expert (or even intermediate user) it is trivial to override.
  - glofish 6y ago
    How exactly would you trivially correct the massive number of published results in genomics data where SEPT1 was converted to a date? 20% of publications had errors introduced by Excel in them! The real number is probably much higher (Nature had a rate of 35%). What is the rationale in saying that the benefits must still be worth it? See: Gene name errors are widespread in the scientific literature https://genomebiology.biomedcentral.com/articles/10.1186/s13059-016-1044-7 https://genomebiology.biomedcentral.com/articles/10.1186/s13... The root of the problem is not about how Excel displays information, or how Excel parses the data but that the data it tacitly changed upon saving it. Open a CSV file that has SEPT1, don't take any action, save it right away. Boom, the data has been changed and you cannot recover the original information.
- ChrisLomont 6y ago
  So we should not use computers to automate common tasks? In this case likely 99.99% of the time someone enters this they want it to be a date.
epistasis 6y ago
It's one thing if it were typed in, but this is not typically from typing, it's from opening a CSV file that has a field that's OCT4. (And honestly, if I want a date, I'll hit the date formatting button, Excel.) It is super hard to figure out a way to import data into excel without triggering auto-conversions. I knew a way a few years ago when I was dealing with lots more biologists, but I have forgotten now. If I had to do it now, I would write XLSX directly then manually check the file in Excel, because Excel is fundamentally untrustworthy.

mnw21cam 6y ago

> If someone types MARCH1, DEC1, OCT4, SEPT1 into a column, why should Excel not treat these as dates? It should treat these as dates, unless told otherwise. But Excel goes one step further, and throws away the text you originally typed in. That means, if you type "DEC1" into a spreadsheet, see it looks stupid, and change the data type of that cell to "Text", then it's too late, and Excel will show "37226". It has destroyed the original data and replaced it with the numeric equivalent of the date. The data type of the spreadsheet cells should be a display format only. The text you typed in (or loaded from a CSV file) should be preserved, and the data type of the cell should only dictate how that is treated and displayed, not how it is stored.

FabHK 6y ago
Excel does that, largely: cells have a datatype, a value, and a format (among other properties). Changing the format leaves the value unchanged. However, when you input something into a cell, Excel has to parse it and decide what value and datatype to assign to it. That is just inevitable, and the same everywhere: in Python, `x = 3` assigns an integer, `x = 3.` a floating point, and `x='3'` a string. Similarly hints (or explicit specifications of type) are possible in Excel. However, given that, what you typed originally is not recoverable anywhere (Python doesn't record whether you wrote `x=3.` or `x = 3.00`, why should it?). So, to the extent that it is reasonable, Excel does what you request, and to the extent it doesn't do it, it's not reasonable, I'd say: A cell need not maintain the original pre-parsing input.
ChrisLomont 6y ago
> The text you typed in (or loaded from a CSV file) should be preserved That’s even worse, since a user assumes the cell holds what it shows. Excel, for example, auto completes entries, which is extremely useful for data entry, and for accuracy. If it only held the few characters entered, now it has to repeat exactly the same autocompletes every time it opens, and has to do so even if the data that originally caused the auto complete changes. All this is nearly algrithmically I’m-Seville without changing the document into a history. And now CSV is useless for data transfer. It’s vastly more sane to change to the data the user meant to enter, show it to the user for confirmation, and save that. That the 99.99% use case bites the remaining 1 in 10,000 is a perfectly reasonable trade off, done throughout society in uncounted ways.

glofish 6y ago

It is shortsighted to call this behavior a "useful feature". It was and is a grave and serious mistake. Automatically converting SEPT1 to a date caused far more damage than the benefit of the fleeting convenience. The damage that this "feature" caused and will keep causing goes far beyond the domain of bioinformatics. Even in bioinformatics things have not been "solved", for the next decade the same gene will have two names when looking it up in previously published data.

FabHK 6y ago
> Automatically converting SEPT1 to a date caused far more damage than the benefit of the fleeting convenience. Who are you to judge? For the novice, even for most users it'll be what they want. And for the expert (or even intermediate user) it is trivial to override.
- glofish 6y ago
  How exactly would you trivially correct the massive number of published results in genomics data where SEPT1 was converted to a date? 20% of publications had errors introduced by Excel in them! The real number is probably much higher (Nature had a rate of 35%). What is the rationale in saying that the benefits must still be worth it? See: Gene name errors are widespread in the scientific literature https://genomebiology.biomedcentral.com/articles/10.1186/s13059-016-1044-7 https://genomebiology.biomedcentral.com/articles/10.1186/s13... The root of the problem is not about how Excel displays information, or how Excel parses the data but that the data it tacitly changed upon saving it. Open a CSV file that has SEPT1, don't take any action, save it right away. Boom, the data has been changed and you cannot recover the original information.
ChrisLomont 6y ago
So we should not use computers to automate common tasks? In this case likely 99.99% of the time someone enters this they want it to be a date.

epistasis 6y ago

It's one thing if it were typed in, but this is not typically from typing, it's from opening a CSV file that has a field that's OCT4. (And honestly, if I want a date, I'll hit the date formatting button, Excel.) It is super hard to figure out a way to import data into excel without triggering auto-conversions. I knew a way a few years ago when I was dealing with lots more biologists, but I have forgotten now. If I had to do it now, I would write XLSX directly then manually check the file in Excel, because Excel is fundamentally untrustworthy.