3 ms·

Because an absolutely enormous number of people involved in all ends of genomics and biomedical science have absolutely no inkling whatsoever about programming.

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.
  - da_chicken 5y ago
    > There's several ways to avoid the issue in Excel. 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 I open that with Excel and immediately save it as a CSV. I look at the file in a text editor and I see: ID,Range,Ratio 1.23457E+19,9-Aug,15-Jul 12345,10-Sep,21/35 Do you have any idea how fun it is to explain to teachers and school administrators what happened here? 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.
    - occamrazor 5y ago
      If you open the csv file with Excel you get those errors. If instead one uses Get&Transform (aka Power Query) then it is possible to specify the type of each column.
    - dragonwriter 5y ago
      > It doesn't matter how you format the CSV. Excel will do the above. 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.
- CobaltFire 5y ago
  Not specifically this topic but I ran into an issue that would have been trivial in any real programming language this week, but I’m not able to USE anything that’s not on my work computer due to data restrictions. 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.
OptionX 5y ago
Even newcomers? 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?
- jasode 5y ago
  >why stick with Excel versus using python, R or what have you to treat the data? [...] Do the newer researcher still show reluctance to move from Excel 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.
  - int_19h 5y ago
    Besides, these are not mutually exclusive - you can use Excel as the front-end to your Python code via something like PyXLL.
  - OptionX 5y ago
    I'm not saying Excel is without its benefits or a bad tool, just perhaps shouldn't be the only one. 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.

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.
- da_chicken 5y ago
  > There's several ways to avoid the issue in Excel. 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 I open that with Excel and immediately save it as a CSV. I look at the file in a text editor and I see: ID,Range,Ratio 1.23457E+19,9-Aug,15-Jul 12345,10-Sep,21/35 Do you have any idea how fun it is to explain to teachers and school administrators what happened here? 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.
  - occamrazor 5y ago
    If you open the csv file with Excel you get those errors. If instead one uses Get&Transform (aka Power Query) then it is possible to specify the type of each column.
  - dragonwriter 5y ago
    > It doesn't matter how you format the CSV. Excel will do the above. 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.
CobaltFire 5y ago
Not specifically this topic but I ran into an issue that would have been trivial in any real programming language this week, but I’m not able to USE anything that’s not on my work computer due to data restrictions. 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.

OptionX 5y ago

Even newcomers? 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?

jasode 5y ago
>why stick with Excel versus using python, R or what have you to treat the data? [...] Do the newer researcher still show reluctance to move from Excel 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.
- int_19h 5y ago
  Besides, these are not mutually exclusive - you can use Excel as the front-end to your Python code via something like PyXLL.
- OptionX 5y ago
  I'm not saying Excel is without its benefits or a bad tool, just perhaps shouldn't be the only one. 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.