Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

theverge.com

Post reply on HN
Print view View original

311–320 of 518 posts

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

#311

Post by sneak »

reportgunner wrote: post #220
owl57 wrote: 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-…

I suggest a literal T instead of a space, and don’t forget the timezone offset (or a literal Z).

ISO 8601 is great.

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

#312

Post by njarboe »

rorykoehler wrote: post #225
whatisthiseven wrote: post #206

Earlier quoted context omitted.

I keep seeing this sentiment when it comes to those in the natural sciences, but it makes no sense. 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 p…

Tbh I think its a case for multidisciplinary research. You wouldn’t only hire one skill set to run a company, even a tech one, so why should research be any different? That’s probably where the deep insights are.

People that are just decent programmers can make at least twice (probable 3 or 4 times) as much money working for industry than for science in an academic environment. Most programmers that would work for less money because they are interested in science will be more interested in computer programming problems than basic programming to support a scientist. NSF won't give you $250k to hire a really good programmer to support your gene analysis project. More like 100k if you are lucky.

So what you end up with are that great scientists that are decent programmers are the ones who can do the cutting edge science at the moment.

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

#313

Post by ellis-bell »

ivalm wrote: post #236
ellis-bell wrote: post #174

Earlier quoted context omitted.

LibreOffice Calc is free and doesn't corrupt data. It also has full support of UTF8

LibreOffice also explodes (or at least did a few years ago) if you try to open a few hundred mb csv file. Definitely can’t do fast filtering on columns in large files. Should those things be done in sql? Yes... could this data be sent as SQLite files? Yes... were they endemically sent as csv and nothing could change this? Yes... Excel is by far the best of “spreadsheet” apps I’ve seen at munging through multi-hundred…

makes sense!

fwiw, i have not tried to use libreoffice for any heavy lifting, unfortunately (fortunately?). i tend to use bash utils for that, and i 100% do not think telling people to "just use grep / awk / perl and csvcut" is by any means a solution.

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

#314

Post by c3534l »

I've always stood by the principle that a computer should never "correct" human input without asking and this is a great example of that. There should be a prompt asking if you want to convert the data, and there should always remain a way to undo "corrections," but it should never happen without the user knowing about it or wanting it to happen.

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

#316

Post by recursive »

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.

> we happily use YAML

Don't include me in that group.

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

#317

Post by 3pt14159 »

RcouF1uZ4gsC wrote: post #315
t-writescode wrote: post #310

Earlier quoted context omitted.

Or json!!! Why did we leave json???

No comments in standard json.

And you have to choose between readability and whitespace. Doesn't matter for most usecases, but you can't blindly write it out. You have to choose ahead of time:

1. Potentially shoot yourself in the foot with tons of extra whitespace. 2. Unreadable mess.

TOML is better. You can choose whitespace if you want, but you can always go back to dot notation. Though I (sadly) agree JSON is better than YAML. One of the few times I've changed my mind from A to B and then back to A in tech.

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

#318

Post by njarboe »

"There’s no easy fix, either. 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. 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."

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.

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

#319

Post by mixmastamyk »

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.

https://hitchdev.com/strictyaml/

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

#320

Post by danielecook »

I'd love to see a better alternative to Excel geared more towards scientists. While it is true that R and python should be used for analysis, its often hard to escape a tool like Excel for data entry or browsing datasets. It would be nice if there was something out there that was faster and could handle larger datasets, and had features designed to help with data entry and browsing.

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.

Post reply on HN