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

Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

theverge.com

Post reply on HN
Print view View original

81–90 of 143 posts

Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

#81

Post by lofatdairy »

washadjeffmad wrote: post #64

Does anyone here use Excel in any capacity on production data in the biosciences? If so, did you go to college for the biosciences, or are you supplying work using a tool you learned under another discipline? I'm always fascinated to learn how Excel makes its way into unusual places.

Not in biotech, but have worked with people who use a tremendous amount of excel, especially in biology. A lot of the people still using Excel either came before bioinformatics libraries got really really good, had to basically teach themselves how to use a computer to deal with bio data, worked in a lab that had protocols in place for data that used Excel, and/or haven't gotten around to teaching themselves R. You have to keep in mind that Excel is extremely good at being a visual representation of data. R, Python (only really adopted in recent years), and Matlab (which I don't see too often in bio) are all excellent for manipulating the same data as Excel, but if you wanted to quickly scroll through your RNA sequencing counts, Excel provides a fast, (almost) out-of-the box way to get a "big picture" view of your data (I haven't met anyone doing bioinformatics in SAS or STATA, but I know people who have used those software in academia for other fields).

Even if no one is directly manipulating the data in Excel, if you don't import the data correctly, or forget to _not_ save the data, you'll end up fucking it with Excel's auto-formatting. These subtleties lead to things as mentioned in the article, but aren't things that the ordinary person learns except through mistakes. Nobody is born knowing what tools to use or how to use them, but Excel is one of the first pieces of software that deal with data for a lot of people, and as such one of the first things they turn to when faced with a new problem.

Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

#82

Post by teh_klev »

AviationAtom wrote: post #77

Isn't this solved by putting a single quote, or something of the sort, in front of the data?

Yes, but if you're opening a CSV file, some folks don't "import" the CSV which would give you a chance to specify the data types for each column:

https://news.ycombinator.com/item?id=30986868

Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

#84

Post by Aloha »

mcv wrote: post #67
jasode wrote: post #30

Earlier quoted context omitted.

>The real problem is of course using Excel to maintain data. That's not what it's for. That's what databases are for. Excel is for complex calculations over rows and columns. I use MS Excel extensively and create new .xlsx files every week even though I know databases like SQLite, MySQL, and did consulting for Oracle RDBMS. The problem is that databases do not include a GUI for data viewing . And even though I also k…

> The problem is that databases do not include a GUI for data viewing. Exactly. It baffles me that such a tool still doesn't exist (though elsewhere someone claimed that MS Access is like this; I'm not familiar with it). Keeping this in the cloud, with a web-based Excel-like interface, where you can share it with anyone you choose, but keep a single source of truth, I think that would be incredibly useful and solve t…

A modern web-based cloud-hosted access implementation would be a killer app for web-2.0.

Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

#85

Post by chasd00 »

charlieyu1 wrote: post #50
jasode wrote: post #30

Earlier quoted context omitted.

>The real problem is of course using Excel to maintain data. That's not what it's for. That's what databases are for. Excel is for complex calculations over rows and columns. I use MS Excel extensively and create new .xlsx files every week even though I know databases like SQLite, MySQL, and did consulting for Oracle RDBMS. The problem is that databases do not include a GUI for data viewing . And even though I also k…

You are not supposed to have a GUI view when you have 100K+ entries. Excel misguides people to think that database management is easy. The reality is it is not possible to have a GUI view of middle-large databases with millions of entries because it is computationally impossible

100k+ is pretty manageable in the Excel GUI. When you get above 1M then it starts getting dicey. I have a junior dev on my team that is very good at python. I only consider him "jr" in the language/tech he's assigned to work on daily, in python i consider him pretty solid.

When we need data work done on csv's larger than I can ask an Analyst to do in Excel I give it to him to write some python against. Finding and exploiting his python skills has won the guy a couple bumps in base pay.

Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

#86

Post by dotancohen »

magicalhippo wrote: post #69
dotancohen wrote: post #47

Earlier quoted context omitted.

Please, keep adding to the "tons of other GUI features" list. I'm just started on a Python/Qt/Sqlite project for spreadsheet-like functionality backed by a strongly-typed database. The things that you mention are perfect features. I'd love to know more.

Formulas is a big one for me. As a programmer I often use Excel to generate insert/update SQL statements for example. So I'd need formulas with not just numerical but also string manipulation functions. Also filtering is essential. So nice to be able to see all the values in the filter dropdown, easy to quickly spot weird values.

How does this sound? Formulae are supported, but the cell (not column) must be set as the Formulae datatype. The first formula that I am supporting is SUM, as it is easy to code in SQL. What are the other most common formulae that you use? I'll only implement the integer / float formulae at first, but I'll add string manipulation later.

  > Also filtering is essential.
Filtering, of could. I've already got a hidden column for all rows isDisplay.

  > So nice to be able to see all the values in the filter dropdown, easy to quickly spot weird values.
Perhaps instead of filtering, you'd like to see outliers or the range of values?

Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

#87

Post by Aloha »

chasd00 wrote: post #85
charlieyu1 wrote: post #50

Earlier quoted context omitted.

You are not supposed to have a GUI view when you have 100K+ entries. Excel misguides people to think that database management is easy. The reality is it is not possible to have a GUI view of middle-large databases with millions of entries because it is computationally impossible

100k+ is pretty manageable in the Excel GUI. When you get above 1M then it starts getting dicey. I have a junior dev on my team that is very good at python. I only consider him "jr" in the language/tech he's assigned to work on daily, in python i consider him pretty solid. When we need data work done on csv's larger than I can ask an Analyst to do in Excel I give it to him to write some python against. Finding and ex…

Very much agreed - and the upper limit is a little more fuzzy, and has more to do with file size, record complexity (how many columns), and what you want to do with the data.

At some point the overall pokeyness of excel when dealing with large datasets overcomes the inertia of "everyone already knows it" and "we'd need a environment to spin up something more complex".

Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

#88

Post by Aloha »

screye wrote: post #73
charlieyu1 wrote: post #50

Earlier quoted context omitted.

You are not supposed to have a GUI view when you have 100K+ entries. Excel misguides people to think that database management is easy. The reality is it is not possible to have a GUI view of middle-large databases with millions of entries because it is computationally impossible

One thing I've learnt over the years, is that users do not care about the inherent complexity of your problem. Like water, they will always choose the path of least resistance. It is why people would rather copy-paste documents than learn git, despite versioning being inherently complex. It is why people complain about android, but only use 1st party preinstalled apps or freemium ad-infested crap. Excel works and it…

a few thousand or more is practical, it depends on what the data is, if the data is inherently non-relational (a master parts list, a user list, a bunch of logged data points) then all is well, it breaks down based on how big the data set is.. and what the dataset is.

Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

#89

Post by Aloha »

jasode wrote: post #30
mcv wrote: post #20

The real problem is of course using Excel to maintain data. That's not what it's for. That's what databases are for. Excel is for complex calculations over rows and columns. And of course it should be better at data validation; you should be able to tell it whether a certain column is a date or not, and Excel shouldn't be jumping to stupid conclusions about it, but it does, and that means you need to be careful and n…

>The real problem is of course using Excel to maintain data. That's not what it's for. That's what databases are for. Excel is for complex calculations over rows and columns. I use MS Excel extensively and create new .xlsx files every week even though I know databases like SQLite, MySQL, and did consulting for Oracle RDBMS. The problem is that databases do not include a GUI for data viewing . And even though I also k…

I wish there were modern record oriented databases and processing tools - I've used some of the tools on zOS and that whole mindset of "even the system tools are record aware" so you can use the equivalent of basic UNIX tools to do reporting and data analysis, is pretty powerful.

We could build something that is both as powerful as excel, and easier to use, yet designed for average users to manipulate very large data sets - we just have to chose to do that.

Python has footguns in it, PHP has a confusing standard library, perl is complex, and bash is missing some of the data processing primitives needed.

Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

#90

Post by adhesive_wombat »

> Be conservative in what you send, be liberal in what you accept.

This is why I think that's wrong: if you allow people to be sloppy with how they do things, they'll do it, and then make it part of their workflow, product, religion or whatever, and now everyone is stuck with it.

Be absolutely explicit with what you accept and refuse to deal with crap. Then you will only ever have to maintain a simple validator and the code that deals with good data, rather than having to have an incredibly hairly validator that leaks into your logic at every level, followed by cementing your bugs into everyone's implementations.

Post reply on HN