Special situations

This section describes several special situations that curators should be aware of.

Reporting more  than one comparison in a single row

There are many cases where a single figure may report several different results, e.g. severe COVID-19 cases vs healthy, ICU COVID-19 cases vs healthy.  This can be handled in two ways, either by creating two rows in the annotation sheet, one for each comparison, or including them together, separated by commas, in the same row.  The latter has the advantage of keeping the context together, i.e. that the results are from the same figure and conditions.  A third alternative is to separate the two comparisons with a semicolon, in this case, the comparisons will be separated into two rows in the post-processing code.  This may be useful if the comparisons are complex, with e.g. time and subgroup qualifiers.

Copying a table from a publication to a spreadsheet

It may often occur that the curator must copy a list of gene symbols and perhaps filter or reformat them.  Copying gene symbols into Excel is very dangerous, whether by pasting or by reading in a list.  It is much better to use e.g. Google Sheets or some other program like Notepad++ to work with gene symbols in a non-destructive fashion.

For example, you may need to copy a table from a PDF or Word doc to a spreadsheet in order to separate e.g. gene symbols with positive and negative fold-changes or correlation values into two different lists.  

Don't use Excel for manipulating lists of gene symbols

Excel will automatically change gene symbols that look like dates, e.g SEPT8, to actual Excel dates.  This is in many cases not reversible (many-to-one mappings).  Never open a .csv file containing a list of single gene symbols in Excel.  Instead, change the extension to .txt and manually import.  During the import process, change the type of any column that contains single gene symbols from “general” to type “text”.

If you really want to use Excel, see the topic in the Appendix below “If you really want to use Excel

Separating a list of symbols into two lists

Once the table of symbols and statistics is copied into a spreadsheet, you can separate values e.g. by correlation value by sorting on the column containing the value.  Then copy the symbols for e.g. the positive correlation values into one text file, and the values for negative correlation into a second file.

WARNING - we have often seen that the one of these lists ends up containing all of the symbols, not just one half or the other.  Please be careful when copying values.

Formatting a column of symbols into a comma-separated list to paste into a curation sheet.

  1. Select the column of gene symbols, not including any header row that may be present.
  2. Paste the symbols into a text editor such as Notepad++.
  3. Join all lines into one line.  This will leave each symbol separated by a space.
  4. Do a global find and replace to replace all space characters “ “, with a comma followed by a space “, “.  Keeping the spaces improves how the lines are displayed when pasted back into a curation sheet, as lines can be broken at any space.


If you really want to use Excel

If you really want to use Excel, you must carefully prepare an empty Excel sheet before copying the original table into it in order to avoid the Excel date problem. By default, all cells in Excel are of type “General” and will suffer from the date problem.

To prepare a sheet to receive gene symbols being pasted in, first set the format of the column that will receive the dates to type “text”. For example, if the first column of the original table contains the gene symbols, you must change the format type of the first column to “text”.  Only then is it safe to paste in the symbols.  

Note - the same problem occurs if you open a CSV file with Excel.  As a CSV file is imported automatically, you will have no opportunity to first specify the column type and the  date conversion error will occur.  If you change the file extension to “.txt”, Excel will use its import dialog and allow you to set the type of each column as desired.

The HGNC has recently updated all date-like gene symbols to avoid the Excel date problem.  However, all older gene lists will still be subject to this problem.