2020-007 Excel处理基因名要小心啊

2023-10-19 18:21:04

Excel处理基因名要小心啊

最近在处理数据的时候，基因名的转换出现了一些奇怪的错误。

de了一番bug后，发现Excel的基因列居然存在日期？

wtf？

这Excel也太坑了。

查了一下资料，原来在2017年，科学家们就发现了这个错误。

Excel随意篡改20%的遗传学论文!

今年（2017年）8月份，三位科学家在《Genome Biology》期刊上发表论文，称他们发现20%的遗传学论文包含了Excel软件导致的基因名转换错误。他们对论文进行的扫描显示，科学文献中的基因名错误十分普遍，在默认设置下Excel软件会将基因的名字转换成日期或浮点数。

举例来说，基因名字SEPT2和MARCH1会被分别转换成2-Sep和1-Mar;标识符2310009E1被转换成浮点数2.31E+13。虽然对三位科学家的发现也有质疑的声音，但大家不妨试一下，在Excel中输入MARCH1，然后敲回车，真得变成1-Mar了! Excel你要闹哪样，想把科学家辛辛苦苦写的论文都变成“日历”吗?难道你3月1日有约会?

引自：Excel-坑你的基因名没商量！

阅读文章：Gene name errors are widespread in the scientific literature

也就是说，上面那个4-Sep应该是输入SEPT4，然后被Excel自动转换了。

在Excel中试一下。

果然，输入sept4后，Excel“贴心”的将数据变成了4-Sep，值就是2020/9/4。

实测中，从sep一直到september，从mar一直到march，接小于100的数字，是都会自动变为日期的。

问题已经复现了，如何解决呢？

最好的办法当然就是直接拒绝Excel，毕竟“消灭问题是解决问题的最好手段”。如果你非要使用Excel，那么就必须记得，将基因列的类型设置为文本，或者刻意的在前面或后面加上一个符号。

像这样：

然后使用函数提取基因名：

值得注意的是，论坛上有人讲到，Excel是在打开文件或者输入的时候自动做的转换，即使文件是文本文件，或者用Linux程序生成的Excel文件，我们可以确保里面基因名字是对的，只要用Excel打开，它就会进行转换。当点保存的时候，这个转换就被保存了下来。

所以，远离Excel用python保平安。

当然上面方案都是处理基因名前的注意事项。当我们遇到这个麻烦的文件的时候，还是要想办法还原出原来的基因名。

使用python还原基因名

首先读取文件。

import pandas as pd
df = pd.read_excel('41586_2019_1414_MOESM3_ESM.xlsx',sheet_name=0,skiprows=1) # sheetname=0,取第一个sheet，skiprows=1,跳过第一行

基因名的列为Gene。

因为pandas读取的日期数据为datetime类型，使用isinstance函数可以找到所有的datetime数据。

from datetime import datetime
df.Gene[df.Gene.apply(lambda x: isinstance(x,datetime))]

out:

1236    2019-09-02 00:00:00
1288    2019-09-11 00:00:00
1333    2019-09-06 00:00:00
1353    2019-09-11 00:00:00
1432    2019-09-04 00:00:00
Name: Gene, dtype: object

从datetime类型的数据中可以获知月份和日期，将其拼接就可以大致得到一个名字。

选一个来测试。

strftime中格式化选项中%b表示提取本地简化的月份名称，%d 提取具体的日期。

date = df.Gene[df.Gene.apply(lambda x: isinstance(x,datetime))].iloc[0]
datetime.strftime(date,'%b%d')

out:

'Sep02'

出来了一个结果，但是不够好，因为这个基因唯一的可转换名字是Sept2。

那么，可以在出来的结果中，将0删除，然后替换Sep为Sept。

但是我在此处用一个更tricky的方法，在strftime时直接生成不带0的日期，这个需求可能在别的时候也需要，在此介绍一下。

windows上，在格式化选项前加一个#号可以省略月份或日期中的0。（类Unix平台应该加-）

datetime.strftime(date,'%b%#d')

out:

'Sep2'

然后进行替换。

datetime.strftime(date,'%b%#d').replace('Sep','Sept')

out:

'Sept2'

目前还不够完美，在此例中只出现了Sept，但是，March也应该考虑在内。

datetime.strftime(date,'%b%#d').replace('Sep','Sept').replace('Mar','March')

重新读取，批量处理。

import pandas as pd
df = pd.read_excel('41586_2019_1414_MOESM3_ESM.xlsx',sheet_name=0,skiprows=1)
datetime_index = df.Gene.apply(lambda x: isinstance(x,datetime))
df.Gene = df.Gene.apply(lambda x: datetime.strftime(x,'%b%#d').replace('Sep','Sept').replace('Mar','March') if isinstance(x,datetime) else x) #日期类型进行处理，非日期类型直接返回数据
df.Gene[datetime_index]

out:

1236     Sept2
1288    Sept11
1333     Sept6
1353    Sept11
1432     Sept4
Name: Gene, dtype: object

这些基因名都可以进行正确的转换。

问题看似已经解决。

但是。。

你能想象得到，同时存在Marc1基因和March1基因被共同转换成1-Mar了吗？

但是这两个显然是不同的基因。而上面的操作有潜在将Marc1基因更换为March1基因的可能性。

遇到这个问题，在我看来，已经无解了，因为这个二到一的转换，相当于信息的减少，是不可能复原的。只能从别的地方拿到信息。

还要注意的是，在这张图中：

能够发现，sep10和sep50的转换方式是不同的，当日期大于31时，其转化方式我目前不清楚（当然也懒得去了解），但幸运的是，sept、marc和march基因，后面的数字目前不超过31。

使用R

python已经可以解决问题，但是生信中常使用的R也应该给出相应的代码。生信技能树的这篇推文提到了该问题也给了R的解决方案，但是该方案是使用来自于其他列的信息。

让我使用R来做一下。

df = readxl::read_excel('41586_2019_1414_MOESM3_ESM.xlsx',skip = 1,sheet = 1) #取第一个sheet，跳过第一行

读取数据后，本来想利用和python一样的思路，通过类型找到被改变的基因位置，但是不行。

df$Gene[!is.character(df$Gene)]

也就是说Gene列全是字符串类型，向量里的类型一致，稍微看了下具体内容，转换过的值都是数字。

那怎么找呢，可以把这一列每个值都as.integer一下，然后看哪些行出错，那些行就是应该被处理的。

在测试中，发现as.integer遇到不能转换的并不出错，只是有warning，然后返回NA，这样反而简单了。

df$Gene[!is.na(as.integer(df$Gene))]  #使用is.na找到转换后值不为NA的行

out:

[1]  "43710" "43719" "43714" "43719" "43712"

值找到了。

下一步转化成日期。感谢知乎大佬！

R语言中如何将数字转化为日期？ - 冬冬的回答 - 知乎

date = df$Gene[!is.na(as.integer(df$Gene))][1]
date = as.Date(as.numeric(date),origin="1899-12-30") 
date

out:

[1] "2019-09-02"

是那个日期了。再格式化成需要的格式。

strftime(date,format='%b%d')

out:

[1] "9月02"

啊这，你为何如此“友好”的给我换成中文。我真的是***。

解决一下。

old.lctime = Sys.getlocale('LC_TIME')
Sys.setlocale('LC_TIME', 'C')
print(strftime(date,format='%b%d'))
Sys.setlocale('LC_TIME', old.lctime)

out:

[1] "Sep02"

不过，-和#去0的方式这里都不能用。那就sub吧

date = strftime(date,format='%b%d')
library(purrr)
date = date %>% sub('0','',.) %>% sub('Sep','Sept',.)  %>% sub('Mar','March',.) #避免函数层层嵌套，使用purrr，函数中.代表原值
date

out:

[1] "Sept2"

将之前的代码整合一下。

df = readxl::read_excel('41586_2019_1414_MOESM3_ESM.xlsx',skip = 1,sheet = 1)
print(df$Gene[!is.na(as.integer(df$Gene))])
library(purrr)
date.transform = function(Gene){
    old.lctime <- Sys.getlocale('LC_TIME')
    Sys.setlocale('LC_TIME', 'C')
    dates = Gene[!is.na(as.integer(Gene))]
    single_transform = function(date){
        date = as.Date(as.numeric(date),origin="1899-12-30")
        date = strftime(date,format='%b%d')
        date = date %>% sub('0','',.) %>% sub('Sep','Sept',.)  %>% sub('Mar','March',.)
        date
    }
    dates = sapply(dates,single_transform)
    print(dates)  #原来的值变成了name
    Sys.setlocale('LC_TIME', old.lctime)
    Gene[!is.na(as.integer(Gene))] = dates
    Gene
}
df$Gene = date.transform(df$Gene)
print(df$Gene[!is.na(as.integer(df$Gene))])

out:

[1] "43712" "43710" "43719" "43714" "43719"
   43712    43710    43719    43714    43719   #这一行是name
 "Sept4"  "Sept2" "Sept11"  "Sept6" "Sept11"   #这一行是值
character(0)

可以看出，转换已经成功了。

总结

有些截图使用到了mybulider对象，那是公司提供的代码，在此不进行介绍。

这个处理过程太麻烦，我就不尝试在Excel中编写宏解决了，毕竟Excel，呵，自作聪明的软件。

解决问题的过程就是学习的过程。

我

我是 SSSimon Yang，关注我，用code解读世界

码农公寓

Excel处理基因名要小心啊

使用python还原基因名

使用R

总结

我

相关文章