5 ms·

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?

minikites 5y ago
Given 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 ago
  I 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 ago
  Switch 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 ago
    These 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 ago
      XXX YYY ZZZ, PhD in genomics, probably re-trainable
- blackbear_ 5y ago
  Anecdotically, 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 ago
Because 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 ago
  Yeah. 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.
    - pletnes 5y ago
      As someone who has programmed in python for a decade, and no idea how to do anything right in Excel, I would beg to differ.
      - qayxc 5y ago
        Do you use Excel on a daily basis for productive work as well? If not then your personal experience is irrelevant in this context.
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 ago
  Wait, if she knows how to avoid the issue, how is it still an issue?
  - acomjean 5y ago
    I 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 ago
      What 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 ago
        File 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 ago
It might be easier to teach people how to use excel correctly with for example power query.
happytoexplain 5y ago
Is 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 ago
I 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 ago
From 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 ago
When 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 ago
  Tough 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 ago
As 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 ago
The 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.

minikites 5y ago

Given 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 ago
I 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 ago
Switch 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 ago
  These 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 ago
    XXX YYY ZZZ, PhD in genomics, probably re-trainable
blackbear_ 5y ago
Anecdotically, 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 ago

Because 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 ago
Yeah. 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.
  - pletnes 5y ago
    As someone who has programmed in python for a decade, and no idea how to do anything right in Excel, I would beg to differ.
    - qayxc 5y ago
      Do you use Excel on a daily basis for productive work as well? If not then your personal experience is irrelevant in this context.

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 ago
Wait, if she knows how to avoid the issue, how is it still an issue?
- acomjean 5y ago
  I 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 ago
    What 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 ago
      File 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 ago

It might be easier to teach people how to use excel correctly with for example power query.

happytoexplain 5y ago

Is 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 ago

I 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 ago

From 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 ago

When 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 ago
Tough 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 ago

As 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 ago

The 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.