10 ms·
Autocorrect errors in Excel still creating genomics headache
- OptionX 5y agoCan anyone with first hand experience on this tell shed some light on why stick with Excel versus using python, R or what have you to treat the data?
- minikites 5y agoGiven that these are relatively unskilled users, would switching away from Excel increase or decrease errors? I don't think it's a given that errors would decrease by switching away from Excel.
- instagraham 5y agoI think there is a difference between the errors. Excel autocorrect errors are easy to miss as it is the software making an arbritrary decision over your own. Errors made from using a new , presumably neutral software, would be the kind you are supposed to catch as a researcher. There is obviously scope for error in everything but Excel's user-hostile methods are not justified by this.
- an_d_rew 5y agoSwitch to what? Excel is everywhere. People are familiar with it. The same people are often not familiar with, and have never used, and may even be confused by “notepad.exe”.
- ltbarcly3 5y agoThese aren't someone's 85 year old grandparent getting a computer for the first time to look at pictures of their great grandchildren, these are people who have used a computer every day since they were 14 and have advanced STEM degrees. I think they are probably re-trainable.
- Iwan-Zotow 5y agoXXX YYY ZZZ, PhD in genomics, probably re-trainable
- blackbear_ 5y agoAnecdotically, I was talking to an acquaintance who decided to pick up R as a replacement for excel for data analysis and they were positively blown away by the possibilities opened by the new mindset that came with programming. So yes, maybe these "relatively unskilled users" will struggle for a few weeks/months, but it will be a net positive change as many of them will see immense benefit after some tinkering.
- an_d_rew 5y agoBecause an absolutely enormous number of people involved in all ends of genomics and biomedical science have absolutely no inkling whatsoever about programming. Furthermore, Microsoft office is installed everywhere everywhere everywhere… like it leave it love it or hate it, it doesn’t matter… it’s true (at least for huge swaths of the demographic). So the first thing most people learn is excel and the last thing most people use is excel. It’s a database, it’s a spreadsheet, you can even use it as a word processor if you want to. And some have. I’m not saying you could and I’m not saying you should, I’m just answering as to “why“.
- fartcannon 5y agoYeah. At some point though, people who want to do something that requires python or R, but refuse to learn it, and then use Excel (and make mistakes like the article mentions) should reconsider their choices. This is really out of character for me, but in this instance, relying on excel to do everything is a mistake by the user, not Microsoft's ridiculous data destroying import function. Use the right tool.
- qayxc 5y ago> Use the right tool. Step one should be learn your tool, no matter the tool. There's several ways to avoid the issue in Excel. If someone working with Excel isn't able to learn that (heck, a simple template would suffice), I have no hope the same demographic would have any success with R or Python.
- gerdesj 5y ago"That’s something Purdie says she doesn’t have time for. She has adapted to Excel’s quirks, adding apostrophes before commonly affected genes to prevent the conversion, or pre-formatting spreadsheet cells before importing data. “It’s one of those things that I just accept,” she says."
- qayxc 5y agoWait, if she knows how to avoid the issue, how is it still an issue?
- acomjean 5y agoI work in bioinformatics and have seen the issue. You get data from a lot of sources, and people (like me) just sometimes forget to check the spreadsheet. Often a csv file so it’s not clear it’s been generated by excel. It’s also only a problem for gene symbols (not entrez gene IDs or flybase gene numbers). Also the datasets are big, so there are 15 genes in error over the whole dataset it can be hard to spot. Honestly genes are a constantly moving target and are kinda a pain to write software for. They get new names, split and merge over time…
- qayxc 5y agoWhat really interests me is why your field is still using untyped CSV data for exchanging information, instead of say XML. And why hasn't there been a collaboration between professionals to fix this on an organisational level (like agreeing on a spreadsheet template or sane method for data exchange)? Is it really every lab for themselves with MS Excel and CSV of all things being the lowest common denominator?
- acomjean 5y agoFile sizes are an issue and xml won’t help. The file formats are wierd (fasta?) but they are at least kinda standard. There are groups dedicated to organizing/ annotating genes. They’re quite organized and have highly structured databases. http://gmod.org/wiki/Introduction_to_Chado http://gmod.org/wiki/Introduction_to_Chado A lot of the model orgainism species have gotten together to make thinks more uniform. https://www.alliancegenome.org/ https://www.alliancegenome.org/ Also most people in this field are using free software.
- enaaem 5y agoIt might be easier to teach people how to use excel correctly with for example power query.
- happytoexplain 5y agoIs there any reason to suspect that the primary factor is something other than the difference between an extremely featured end user GUI and programming?
- gpapilion 5y agoI took a programming for math majors course in college, and the largest challenge for the class was understanding procedural looping to process data sets. Understanding a for loop or a while loop really was a hard concept for someone to understand. We didn't cover any real data structures, and everything was a 2 dimensional array. Excel, and most spreadsheets for that, avoid some of the basic control structures beginners find challenging. Many functions are basically map and reduce functions so you typically are getting the same number of cells back, or one. Often maps are done visually in the spreadsheet with a formula being applied to every cell in a column.
- delosrogers 5y agoFrom my experience Python/R can be great when you have large scale analysis to do but if you have a task that requires more manual fiddling with the data then it’s much nicer to use Excel.
- cm2187 5y agoWhen you have done programming for a while and it is mostly muscle memory, it is easy to forget how much it is an impenetrable black box to someone who has never done it. Try to tell someone who just needs to get a task done that he needs to spend the next 3 months learning programming from scratch vs using excel.
- breakfastduck 5y agoTough shit, though. Thats what they're being paid for. "A bad workman always blames his tools" has never been truer than this situation.
- jpeloquin 5y agoAs a biomedical researcher who prefers using python & R over Excel: (1) Graduate students (who are the bulk of the scientific labor force) usually have some training in python, R, or Matlab, but are seldom practiced in applying it to real-world work. So if a lab standardizes on python, R, etc. the senior people have to do a lot of extra work to support the programming, rather than doing work with intrinsic scientific value. It's easier to teach the Excel quirks than to teach effective programming practices. Excel quirks are concrete, and "good practices" in programming are vague and situational. (2) Python & R have their own pitfalls. It's easy to apply the wrong filters to a data set and compare subsets that aren't what you think you're comparing. Although when they go wrong, they go very wrong, and this is easier for a supervisor to notice. (3) Excel has rich text formatting, graph embedding, and data types, so it's very useful as a human-readable data interchange & summary format even if you never do computation in it. IMO neither programming languages nor Excel are a great fit for data analysis. Something purpose-made, like JMP or even GraphPad, is probably the better choice in most situations. Programming gets you automation but at the cost of high complexity. Since you still need to look at and think about the data there's a limit to how much time automation can save (Amdahl's law).
- taeric 5y agoThe same line of questioning can easily land in "why use csv?" It is a terrible format that has very few redeeming qualities. It happens to also be the most widely used one.
- gpapilion 5y agoAutocorrect(and predictive text) has reached an interesting milestone, good enough that you mainly rely on it, but occasionally it distorts the data you intended to send. Disengaging the technology isn't easy, or even understanding field types it shouldn't correct(everyday my work VPN auth window tries to autocorrect my username). There is so much context that is unavailable to the computer, and there is no good way to provide it. I think it has interesting implications for self-driving, since as far as I can tell we're at an earlier but similar stage in its development; works for simple situations, likely to drive you into a wall for no reason. We build so much trust in these systems, we stop paying attention to what they are doing, and then pay the price when it behaves in an unexpected way to the user.
- soco 5y agoI get bitten by Excel's auto-correct all the time and I work nothing related to genomics. It's in my opinion just a dumb decision of Microsoft to not let you disable all those smarty feats (like, thinking you have a date if your string kinda looks like one)
- planet-and-halo 5y agoSerious question, is this a viable area for a startup product? Obviously nothing will easily displace Excel for general spreadsheet work, but I could see room for a domain-specific spreadsheet that has core features and supports just a basic set of functions relevant to genomics, + a "we won't fuck up your data" feature.
- NullPrefix 5y ago>a "we won't fuck up your data" feature That's a hard to implement feature. Hand waving and buzzword lingo will not be enough for people to believe it.
- masklinn 5y ago> That's a hard to implement feature. It really isn't: just remove the data autodetection anything and you're done.
- cm2187 5y agoThat’s an expensive workaround to just formatting your cells as text before entering the values
- dspillett 5y agoYou'd be surprised how much effort can be saved by not relying on human interaction to be reliable. Though convincing people to switch to a new app for that reason, even if it were free and Free, would still be an uphill struggle. > just formatting your cells as text before entering the values It is a little more than that. I work with finance people, and there a lot of data is manipulated in Excel but passed around as CSV files for compatibility elsewhere. This causes no end of problems because fixes by setting cell properties are obviously lost in transcription, and date errors creep in as things are moved back & forth between people in the US and those in locales that do dates properly.
- prionassembly 5y agoThe easier solution is coming up with a 1:1 human-readable-hashing scheme so that MARCH4 translates to "funny-blue-smell-tuesday". Then you keep the safe key in a column next to the unsafe key.
- tcskeptic 5y agoWe need a clippy type feature "It looks like you are working on genomics data, should I turn off all the data destroying auotcorrect features?"
- UnFleshedOne 5y ago"It looks like you are working on genomics data, should I direct you to a search engine to find appropriate tooling instead?"
- KMnO4 5y agoHeh, sure, if you have $10k+ per user. (Maybe genomics isn’t as bad as proteomics)
- delosrogers 5y agoThis exact problem burned me when I started doing transcriptomics, one of the things I found that helps mitigate the problem is to always keep both the gene symbol and a ID like an Ensemble or Entrez ID for every data point because those don’t get mangled by Excel
- guitarbill 5y agoIt's easy to hate on Excel or Microsoft, a "dumb" program and a faceless mega corporation. Don't get me wrong, I'm no fan. But the real question is what are journals doing? It seems like the obvious solution is for editors and reviewers to do a quick search for these errors. To me, this is more of an indication of how poor most journals really are. (You could argue that supplementary data should be in an open format, so you could automate this checking. I'm not against this, either.)
- deleted 5y ago[deleted]
- nnmg 5y agoThis is an important point. What have the journals done? Raised their prices and business as usual. Scientific editors do nothing for data validation. There is no accountability, even after retractions. Scientific journal editors are glorified gatekeepers for "high impact" work (read: flashy), and then use free reviewer labor to cover themselves so they can call it 'peer reviewed'. In the rare cases when journals do require supporting data, they explicitly ask for excel spreadsheets :(
- codetrotter 5y agoThat’s the price we pay. A reasonable alternative would be for example statically typing each column or row in the sheet. So in one sheet column A contains only floats, col B contains only text, col C contains only dates etc. And in another sheet col A may be date type, col B date type also, col C and D text, and col E monetary amounts. But this would come at the cost of not being able to mix types in column, and a lot of people want to be able to mix. And that’s why we’re stuck
- zozbot234 5y ago> A reasonable alternative would be for example statically typing each column or row in the sheet. Excel actually supports this out of the box. But it's one more option to set, and people get lazy.
- dspillett 5y agoAlso those properties are lost if you transfer the data to another format then back, or don't properly survive copy/cut/paste operations (often cutting will remove everything about a cell including options such as these). As well as being easy to be lazy and not use the options, it is easy to accidentally undo them also.
- eitland 5y agoNah, much of this is the same as the dumbness of many autocorrecting keyboards: automatically "correcting" something that was correct based on a dumb hunch It is really simple to remove the most embarrassing autocomplete errors on phones: - just disable autocorrect, - or preferably use another keyboard that just proposes fixes but leaves it to you to select the suggestion (I use SwiftKey and it does this, others probably exist too). The same is not possible in Excel as far as I am aware: You cannot turn of autoformatting. The whole thing could be easily (on a ux level at least ;-) solved by introducing a single checkbox: "stop embarrassing me" with help text that says "turn off autoformatting". Checking that box would leave values like you typed or pasted them in. No need to select on a column basis. Edit: It is also almost at the level of modern search engines that replace my very specific queries with generic queries about something unrelated or vaguely related. Edit 2: it is already more than a year ago I guess since iPhones were caught changing the names of correctly spelled medications to the name of another unrelated medication. Sooner or later this incessant drive to be smarter than the user is going to cost lives I'm afraid. When it comes to search it is already costing me many minutes lost a day, and that is just first order effects, before we get into all the things that doesn't get done because we give up on finding it.
- PedroBatista 5y agoI generally am very critical of my own gang ( programmers ) for being so into their own bubble they completely lose perspective of the real World, and with that ignorance comes the usual arrogance. But this time I feel we should give the business to the geneticists. The Excel "problem" is known and has been known for a long time, finance guys and policy makers were maybe the first big ones to "discover" this for slightly different reasons. Then WHY on Earth these top professionals, elites, "creme de la creme" people continue on this path? And it's not like they love Excel, quite the contrary. Yes, the main reason is they are not programmers so they need to find a way to hack something to solve their problem. The problem with their "problem" solving is they are being slobs in a work that requires the EXACT opposite. "Oh now we need to hire a programmer too?" - Yes, you do. "But we don't have the budget for it" - Yes, you do. You see, money is never enough for anything, it's a question of priorities and when you blow millions of dollars with fancy experiments to ruin your career because of Excel, maybe that Python/R/Julia guy was a bargain after all. I know some of these people, and under the aura of lab coats and distinguished professors it's just some guy/gal trying to crank that paper ASAP to keep the hamster wheel spinning AND they are being sloppy about the data and process AND most of them know it. Sorry, but no excuses.
- breakfastduck 5y agoYeah. They use poor/ unsuitable tooling known for unusual data quirks with absolutely no effort to look at alternatives or pay for an expert. No sympathy from me. Embarrassing. I certainly wouldn't get away with such negligence at my workplace.
- Closi 5y agoThe underlying assumption here is that when transferred into R or Julia, with an equivalent level of development effort and testing, that there will be no/fewer bugs. I'm not entirely sure that's true. I think we are comparing something that is knocked out in an hour in excel to someone spending 4-10x of the time doing it in Python / Julia, but I don't think that's a like-for-like comparison.
- breakfastduck 5y agoSo we blame the software instead of blaming the downright lazy people who can't be fucked learning an appropriate tool even though that's their entire career. Honestly these kind of things really wind me up.
- ironmagma 5y agoIf a problem is re-occurring and between lots of people, it's probably the fault of the system that powers it. The system in this case is the software, so blaming the software is appropriate.
- breakfastduck 5y agoNo it isn't. This isn't a gotcha. This is probably one of the most well known software behaviors on the planet. You KNOW the problem exists, yet you STILL choose to use the software, THEN expect it to change its behaviors because its inconvenient for your very specific use case. Microsoft aren't going to fix this behavior because there are likely millions if not billions of spreadsheets that rely on it. That is not a software problem.
- ironmagma 5y agoPeople don't choose to use Microsoft products, the software is chosen for them by their organization. For a problem like this, the existence of the article may very well be the first step to switching to another solution. The first step in that process is raising awareness that there is a problem.
- breakfastduck 5y agoA convenient cop out there. "I didn't install it, I only chose to use it for something it isnt suited for instead of looking for alternatives, not my fault"
- ironmagma 5y ago
- HPsquared 5y agoThey might move to a relational database, but then the gene named '; DROP TABLE genes; will start causing problems.
- nnmg 5y agoExcel is used as a database/storage/interchange format, especially after the initial analysis by someone who uses python or R. Bioinformatician does the analysis, then the PI wants to see it so they can Ctrl-F for genes they are interested in, so out comes an excel document. And really, even if you know python or R, are you really going to fire up a jupyter notebook, load the data, and run pandas queries every time someone in lab meeting or after a talk asks you about this gene or that gene in your data? I think the important question is why is date conversion a default? Would it really break backwards compatibility for MS Excel users if date conversions were explicit instead of automatic? Turning that off by default would fix a lot of this.
- dec0dedab0de 5y agoAnd really, even if you know python or R, are you really going to fire up a jupyter notebook, load the data, and run pandas queries every time someone in lab meeting or after a talk asks you about this gene or that gene in your data? I don't do any scientific research, but I have been using jupyter as a replacement for excel since it was called the ipython notebook. I don't really use pandas all that often, I just find it easier to read and edit data in python. Though I first learned ipython added the notebook from a talk Wes McKinney gave about Pandas.
- SonicScrub 5y agoI don't work in bioinformatics, but what you are describing is a completely accurate description of what I experienced working in manufacturing quality control. Raw data came in from suppliers in the form of spreadsheets, and management wanted to see results in spreadsheets. Meaning all our quality data was subjected to these issues. The date formatting issue was a particularly annoying "gotcha", particularly when features were defined with a XX-XX numeric code. The number of times I had to deal with someone in a meeting saying "hey, why is this feature called October-13?!" Super frustrating. If I could choose the tools used by the whole process involving multiple different companies and departments, hey I would! It would be python all the way down. But I was but a cog in a massive organization.
- dragonwriter 5y ago
- tssva 5y agoThis isn't a Microsoft or Excel issue. It is a choosing a spreadsheet issue. LibreOffice and Google Sheets for instance by default also try to determine what type of data is being entered. The issue is choosing the incorrect tool or if you have no choice but to use a spreadsheet failing to learn how your tools work.
- cyanydeez 5y agounfortunately, the reality is backwards compatibility with everyone who came before you, sets your budgets and expects to review data with minimal upstart. people like spreadsheets because it provides very immediate feedback
- koksik202 5y agoI remember I was helping my wife with her Uni assignment data split between excel and matlab couldn’t believe the errors we got from misinterpretation looks like lab science needs to catch-up with IT standards for handling data with correct integrity
- Tagbert 5y agoIf an excel spreadsheet is setup properly, this conversion doesn't happen. If the cells are designated as "text", then conversion doesn't happen. This is definitely a problem if you are just opening a raw CSV file and editing it as the default cell type does allow autoconversion.
- djbebs 5y agoExcept from personal experience I can tell you that even when you go out of your way to set the correct data types, if you try to copy paste any data into those cells excel will ignore everything you set and try to automatically change the data types. If you know how to get it to never do that sort of thing, please let me know, because this is causing major issues in production software...