Mistaken Identifiers: Gene name errors can be introduced inadvertently when using Excel in bioinformatics
- Barry R Zeeberg†1,
- Joseph Riss†2,
- David W Kane3,
- Kimberly J Bussey1,
- Edward Uchio4,
- W Marston Linehan4,
- J Carl Barrett2 and
- John N Weinstein1Email author
© Zeeberg et al; licensee BioMed Central Ltd. 2004
Received: 05 March 2004
Accepted: 23 June 2004
Published: 23 June 2004
When processing microarray data sets, we recently noticed that some gene names were being changed inadvertently to non-gene names.
A little detective work traced the problem to default date format conversions and floating-point format conversions in the very useful Excel program package. The date conversions affect at least 30 gene names; the floating-point conversions affect at least 2,000 if Riken identifiers are included. These conversions are irreversible; the original gene names cannot be recovered.
Users of Excel for analyses involving gene names should be aware of this problem, which can cause genes, including medically important ones, to be lost from view and which has contaminated even carefully curated public databases. We provide work-arounds and scripts for circumventing the problem.
There is another default conversion problem for RIKEN  clone identifiers of the form nnnnnnnEnn, where n denotes a digit. These identifiers are comprised of the serial number of the plate that contains the library, information on plate status, and the address of the clone . A search (using the "DNA sequence length search" functionality at http://fantom2.gsc.riken.go.jp/db/search/) identified more than 2,000 such identifiers out of a total set of 60,770. For example, the RIKEN identifier "2310009E13" was converted irreversibly to the floating-point number "2.31E+13." A non-expert user might well fail to notice that approximately 3% of the identifiers on a microarray with tens of thousands of genes had been converted to an incorrect form, yet the potential for 2,000 identifiers to be transmogrified without notice is a considerable concern. Most important, these conversions to an internal date representation or floating-point number format are irreversible; the original gene name cannot be recovered. If one were dealing manually with small numbers of genes, these problems could be detected and then corrected by the tedious, convoluted process described in the legend of Figure 1. But with microarray or other high-throughput data, human proofreading and manual curation are impractical.
The floating-point conversion is not restricted to RIKEN clone identifiers but will affect any clone designation derived from plate coordinates. Although the standard convention is to designate clones officially by library-plate-row-column identification, it is common practice to omit the library reference, particularly if all of the clones come from a single library. Without the library reference in the identifier, all clones from row E of any plate are converted to floating point numbers by Excel. If the library designation is numeric, as it is for RIKEN clones , then including it does not solve the problem. Since 96-well plates contain 8 rows and 12 columns, row E represents 12/96 or 12.5% of the clones on the plate; similarly, 6.25% of clones from 384-well plates would be affected. Most libraries contain hundreds of plates, each of which would be subject to this problem.
There are a number of work-arounds to these behaviors in Excel, but all of them require continued attention on the part of the user to avoid introducing errors. The appropriate solutions depend on the context in which Excel is opened:
If Excel is configured to open a test file automatically from another application, then the data must be pre-processed in the upstream application. For example, a space character or an apostrophe can be placed in front of the gene name. That is the solution implemented in the Excel output format option of MatchMiner  and the primary approach recommended by Microsoft in their Knowledge Base Article on the issue .
If a text file is to be opened by Excel, open Excel first and then select the text file to read. Then select "text" mode for the column(s) containing potentially affected symbols in the Text Import Wizard Step 3 of 3.
If text is to be copied from another application (such as a text processor) and pasted into a pre-opened Excel spreadsheet, the formatting must be set in the spreadsheet. Within the pre-opened spreadsheet, prior to pasting, use Format -> cells to specify which columns of the recipient spreadsheet are to be treated as text . That procedure works for copying from several text processors tested on Mac OS 10.2. However, changing the format of the spreadsheet column to text fails to solve the conversion problem when pasting data from a Microsoft Word file. In that case, in addition to the formatting, use the Paste Special -> Paste: As: Text command to insert the text.
We hope the date and floating-point conversions will be made non-default options – in deference to the large bioinformatics and biotechnology communities if not for other users. Even if that is done, however, there will be a lag time before all researchers have the new program version and an even longer time of confusion before the existing errors and inconsistencies have been expunged from all public and private databases. In the meantime, it is important to be alert to the problem.
The twin aims of this paper are (i) to minimize additional contamination of the public databases and literature, and (ii) to provide a cautionary note to the many researchers who put their microarray data through the exceptionally useful medium of an Excel spreadsheet, then map the clone identifiers or GenBank accession numbers into human-readable gene names. There is no way to know how many times and in how many laboratories the default date and floating point conversions to non-gene names have adversely affected an experiment or caused genes to "disappear" from view.
We thank Dr. Keith Collins (Laboratory of Biosystems and Cancer, CCR, NCI, NIH) and Jocelyn Hsu (SRA International) for helpful comments and technical review of the manuscript.
- Bussey KJ, Kane D, Sunshine M, Narasimhan S, Nishizuka S, Reinhold WC, Zeeberg B, Ajay W, Weinstein JN: MatchMiner: a tool for batch navigation among gene and gene product identifiers. Genome Biol 2003, 4: R27. 10.1186/gb-2003-4-4-r27PubMed CentralView ArticlePubMed
- Zeeberg BR, Feng W, Wang G, Wang MD, Fojo AT, Sunshine M, Narasimhan S, Kane DW, Reinhold WC, Lababidi S, Bussey KJ, Riss J, Barrett JC, Weinstein JN: GoMiner: a resource for biological interpretation of genomic and proteomic data. Genome Biol 2003, 4: R28. 10.1186/gb-2003-4-4-r28PubMed CentralView ArticlePubMed
- Yun Z, Maecker HL, Johnson RS, Giaccia AJ: Inhibition of PPAR gamma 2 gene expression by the HIF-1-regulated gene DEC1/Stra13: a mechanism for regulation of adipogenesis by hypoxia. Dev Cell 2002, 2: 331–341. 10.1016/S1534-5807(02)00131-4View ArticlePubMed
- Okazaki Y, Furuno M, Kasukawa T, Adachi J, Bono H, Kondo S, Nikaido I, Osato N, Saito R, Suzuki H, Yamanaka I, Kiyosawa H, Yagi K, Tomaru Y, Hasegawa Y, Nogami A, Schonbach C, Gojobori T, Baldarelli R, Hill DP, Bult C, Hume DA, Quackenbush J, Schriml LM, Kanapin A, Matsuda H, Batalov S, Beisel KW, Blake JA, Bradt D, Brusic V, Chothia C, Corbani LE, Cousins S, Dalla E, Dragani TA, Fletcher CF, Forrest A, Frazer KS, Gaasterland T, Gariboldi M, Gissi C, Godzik A, Gough J, Grimmond S, Gustincich S, Hirokawa N, Jackson IJ, Jarvis ED, Kanai A, Kawaji H, Kawasawa Y, Kedzierski RM, King BL, Konagaya A, Kurochkin IV, Lee Y, Lenhard B, Lyons PA, Maglott DR, Maltais L, Marchionni L, McKenzie L, Miki H, Nagashima T, Numata K, Okido T, Pavan WJ, Pertea G, Pesole G, Petrovsky N, Pillai R, Pontius JU, Qi D, Ramachandran S, Ravasi T, Reed JC, Reed DJ, Reid J, Ring BZ, Ringwald M, Sandelin A, Schneider C, Semple CA, Setou M, Shimada K, Sultana R, Takenaka Y, Taylor MS, Teasdale RD, Tomita M, Verardo R, Wagner L, Wahlestedt C, Wang Y, Watanabe Y, Wells C, Wilming LG, Wynshaw-Boris A, Yanagisawa M, Yang I, Yang L, Yuan Z, Zavolan M, Zhu Y, Zimmer A, Carninci P, Hayatsu N, Hirozane-Kishikawa T, Konno H, Nakamura M, Sakazume N, Sato K, Shiraki T, Waki K, Kawai J, Aizawa K, Arakawa T, Fukuda S, Hara A, Hashizume W, Imotani K, Ishii Y, Itoh M, Kagawa I, Miyazaki A, Sakai K, Sasaki D, Shibata K, Shinagawa A, Yasunishi A, Yoshino M, Waterston R, Lander ES, Rogers J, Birney E, Hayashizaki Y, FANTOM Consortium; RIKEN Genome Exploration Research Group Phase I & II Team: Analysis of the mouse transcriptome based on functional annotation of 60,770 full-length cDNAs. Nature 2002, 420: 563–573. 10.1038/nature01266View ArticlePubMed
- Konno H, Fukunishi Y, Shibata K, Itoh M, Carninci P, Sugahara Y, Hayashizaki Y: Computer-based methods for the mouse full-length cDNA encyclopedia: real-time sequence clustering for construction of a nonredundant cDNA library. Genome Res 2001, 11: 281–289. 10.1101/gr.GR-1457RPubMed CentralView ArticlePubMed
- Microsoft Knowledge Base Article 21 XL: Text or Number Converted to Unintended Number Format[http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q214233]
- Mistaken Identifiers Companion[http://discover.nci.nih.gov/symbolmutation]
This article is published under license to BioMed Central Ltd. This is an Open Access article: verbatim copying and redistribution of this article are permitted in all media for any purpose, provided this notice is preserved along with the article's original URL.