I 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
Autocorrect errors in Excel still creating genomics headache
101–108 of 108 posts
Re: Autocorrect errors in Excel still creating genomics headache
#102Earlier quoted context omitted.
I would expect dates to show up in large datasets pasted into excel orders of magnitude more often than genes. For the people who use excel, autoformatting is overwhelmingly the desired default behavior.
> For the people who use excel, autoformatting is overwhelmingly the desired default behavior. Seriously? Also for pasted series? Also when the rest of the column doesn't match?
Let's say you have 100,000 rows of data, and in a column all but 3 of those values look like dates. In a real office, which is more likely: that you are working on some set of data that just happens to look like a date 99.997% of the time but isn't a date, or some guy mis-entered or modified 0.003% of the data?
The I-want-to-put-data-in-my-table-which-closely-resembles-an-incredibly-common-format-but-is-problematic-if-mistaken-for-that-format-oh-and-I-can-not-be-bothered-to-google-how-to-turn-off-autoformat niche is not very large. I don't work for microsoft and I don't have copies of user data, but I would be willing to bet my entire life savings that over 95% of the time someone pastes a series which appears to have mismatched data types in the columns, the series actually does have mismatched data types in the columns.
Re: Autocorrect errors in Excel still creating genomics headache
#103If 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.
Re: Autocorrect errors in Excel still creating genomics headache
#104Earlier quoted context omitted.
For the finance and business office worker, it seems to have traction. Just like auto-creating an emoji when you type a : character. Excel is for offices, not specializations of scientists. Bummer.
So maybe we need better software for scientists? Sounds like a hole in the market
The market for scientific software is a bit iffy. Scientific software also needs to be super super flexible since the users are, somewhat by definition, not doing something that's been done before. Hard market.
Re: Autocorrect errors in Excel still creating genomics headache
#105If 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.
Except 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...
Re: Autocorrect errors in Excel still creating genomics headache
#106Earlier quoted context omitted.
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. Honest…
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?
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
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.
Re: Autocorrect errors in Excel still creating genomics headache
#107Earlier quoted context omitted.
So maybe we need better software for scientists? Sounds like a hole in the market
The market for scientific software is a bit iffy. Scientific software also needs to be super super flexible since the users are, somewhat by definition, not doing something that's been done before. Hard market.
A good spreadsheet for scientists. That’s a lot of work for not much money. I don’t know that adapting LibreCalc would do the trick.
Re: Autocorrect errors in Excel still creating genomics headache
#108Earlier quoted context omitted.
> For the people who use excel, autoformatting is overwhelmingly the desired default behavior. Seriously? Also for pasted series? Also when the rest of the column doesn't match?
Let's say you have 100,000 rows of data, and in a column all but 3 of those values look like dates. In a real office, which is more likely: that you are working on some set of data that just happens to look like a date 99.997% of the time but isn't a date, or some guy mis-entered or modified 0.003% of the data? The I-want-to-put-data-in-my-table-which-closely-resembles-an-incredibly-common-format-but-is-problematic-i…
Sounds a bit contrived in this context.
I'd rather think of 100 000 rows where some of them looks like like a date.