Can 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?
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“.
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.
If not then your personal experience is irrelevant in this context.
No, not really. There are several ways to help reduce the issue, but none of them eliminate what it does.
Example that comes to mind is the data file for College Board's SAT test. The data formats for student reports for schools come in two formats: PDF (one page per student), CSV, and fixed-width. That is the comprehensive list of your options. College Board doesn't care about you as a customer. They're too big. Any request you submit will be black holed.
Some of the columns in the file indicate a range, usually in the format "X-Y". Excel will try to coerce that into a date, if it's valid.
Other columns indicate a ratio, expressed as "X/Y". Excel will coerce that into a date, if it's valid.
Other columns indicate an ID number, expressed as a large, fixed-digit number, zero-padded. Excel will coerce that into an integer, or, if it's too long, into scientific notation discarding digits.
It doesn't matter how you format the CSV. Excel will do the above.
Here's an example CSV:
ID,Range,Ratio
12345678901234567890,8-9,"7/15"
"00000000000000012345","9-10",21/35
ID,Range,Ratio
1.23457E+19,9-Aug,15-Jul
12345,10-Sep,21/35
The correct way to work with this data file is: Do not, under any circumstances, open it with Excel if you expect to use it for anything else.
The problem is, there are very few applications that work well with CSV files. I know of CsvEd and Delimit. There are several text editors with a CSV column mode that makes the file look like a table (with varying degrees of success). All of these vary between "godawful" and "a complete nightmare" in terms of performance and usability compared to Excel.
Only if you open the file, so that Exel assumes defaults for formatting. If you import the CSV (Data -> From Text in the current UI) you can specify the format with the Import Text Wizard as described on the College Board instructions for using the file. Except...
Unfortunately, the College Board has outdated instructions on their website; Excel used to offer the Import Text Wizard on opening a csv or text file, rather than making default assumptions, and the College Board instructions page, while it does tell you to use that Import Text Wizard and provide all the details of what to plug into that wizard, tells you to open the file to get it, which bypasses the wizard.
Things like this is why clerical and other low-level positions involving excel test candidates on proficiency with specific versions of excel. Higher-level workers are expected to be able to figure out these kind of changes themselves, though (or consume lower-level staff time, if they are in management.
We have now changed a 1 second automated process that anybody could do, into a 2 minute manual process that requires knowledge of data types. We've gone from a non-technical task to a highly technical task. To open a file without completely munging the data.
It also will sometimes do weird things like exclusive lock the file on disk until you close every open window of Excel because it creates data connections to the file.
You don't, because while the wizard doesn't default to headers, the first screen of the wizard has a checkbox for it, so the option is there without Power Query.
There is no checkbox in the wizard.
So I get to cobble together some really ugly spreadsheets in excel when I’d MUCH rather use something more appropriate. Sometimes it’s not the person, it’s the org. In this case, government.
I've had my education in a engineering focused campus so that may skew it, but even not IT related area had programming courses to, at least, learn the basics.
Do the newer researcher still show reluctance to move from Excel or is it an old guard kind of deal?
The feature of Excel that's underestimated/overlooked by skilled programmers of Python/R is that spreadsheets immediately expose an editable visual spatial canvas datagrid GUI.
I have decades of programming C/C++/C#/Python/etc and yet I create new spreadsheets and use them every day.
Spreadsheets are much faster than wiring up a datagrid in C# or C++ Qt gui widget editor or any other "true" programming language. Spreadsheets are also faster than using Python package Pandas to import a csv into a dataframe and view it in a Jupyter notebook. And last time I checked, displaying an output grid of cells in Jupyter is read-only and not 2-way editable like Excel.
And yes, the complaint is that "MS Excel calculations are not auditable, repeatable, version controlled, etc". All true, but it still doesn't change the fact that Python doesn't have an instant datagrid GUI. UI affordances also matter in viewing science data sets as well as financial budgets.
And I was wondering if people like in the non-engineering but scientific fields, as the ones discussed in post, are exposed to the alternatives during their formation years.
If one chooses to use Excel because its the best tool great, but if its because its the perceived only option that's probably counterproductive.
(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).
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.
"A bad workman always blames his tools" has never been truer than this situation.
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…
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?
There are groups dedicated to organizing/ annotating genes. They’re quite organized and have highly structured databases.
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/
Also most people in this field are using free software.
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.
It is a terrible format that has very few redeeming qualities. It happens to also be the most widely used one.
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.
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”.
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.