Excel处理基因名要小心啊 |
您所在的位置：网站首页 › 副卡可以指定旧号码吗 › Excel处理基因名要小心啊 |
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。 使用Rpython已经可以解决问题，但是生信中常使用的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") dateout: [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，函数中.代表原值 dateout: [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 % 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，呵，自作聪明的软件。 解决问题的过程就是学习的过程。 |
【本文地址】