Tangent: I once tried to help a friend (non-coder) with his slow excel sheet. He was using string search in excel to match virus DNA fragments. As you’d expect, it was way more complicated than neccesary, and likely buggy. I searched around for something better. But if you cant code, its a really big step to ditch your excel and learn a Programming language. But there is no such thing. It’s understandable that people stick with what they know, but the results are awful.
Scientists rename human genes to stop MS Excel from misreading them as dates
271–280 of 518 posts
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#272Or perhaps prepending them when typing with either a single quote, apex or double quote (left/center/right align AND consider as Text), like all the rest of Excel users have done for the last 25+ years?
You don't understand the problem. Read the article.
>You don't understand the problem. Read the article.
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".
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#273To 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…
It's also my experience of research in biological sciences that it is a widespread belief/fact that in order to get published in a top journal, the analysis methods must be "fancy", for example involving sophisticated statistical techniques. I worked on computational statistical methods so I'm not against that per se, but the problem is that if you have the training to contribute at the research front of an area of biology you rarely have the training to understand the statistics. Some would say that the collaborative publication model is the solution to that, but in many cases the end result isn't what one would hope for. I do think that less emphasis on "fancy" statistics, and more emphasis on simple data visualizations and common sense analyses would be a good thing.
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#274Earlier 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…
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.
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.
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#275Earlier 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…
This is what happens on explicit import, but, formatting is per-cell, not per-column. "General" is the format for "idk, guess" - it stays General after a transform, it's not converted.
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#276This happened to me! Someone sent me a spreadsheet where OCT4 was changed to "October 4"
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#277Earlier 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…
> 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.
Technically, we call that a "neural network". Or "AI".
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#278Earlier 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?
off-the-record (not written, not cited, but available in personal discussions).
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#279Earlier quoted context omitted.
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.
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?
Re: Scientists rename human genes to stop MS Excel from misreading them as dates
#280Earlier quoted context omitted.
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…
> 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. Technically, we call that a "neural network". Or "AI".
It reminds me more of a genetic algorithm