R和pandas实现透视表(pivot; cast/dcast/acast)和逆透视表(melt)过程

最新推荐文章于 2024-04-28 01:35:27 发布

马志远的生信笔记

最新推荐文章于 2024-04-28 01:35:27 发布

阅读量921

点赞数

分类专栏：数据分析 R

本文链接：https://blog.csdn.net/weixin_42480153/article/details/105795685

版权

数据分析同时被 2 个专栏收录

23 篇文章 4 订阅

订阅专栏

18 篇文章 2 订阅

订阅专栏

直接放代码

R代码

gc()
library('magrittr')
setwd("~/Documents/48sample/mag/")
#合成丰度文件
data.table::fread("DNA.geneabundance.txt(1)", sep="\t", header=T, stringsAsFactors=F) %>%
  data.frame()->profile
sample <- c('GeneID','X127', 'X22', 'X129', 'X48', 'X138', 'X47', 'X140', 'X20', 'X132', 'X18', 'X149', 'X73', 'X120', 'X105', 'X148', 'X44', 'X122', 'X30', 'X124', 'X26', 'X143', 'X34', 'X133', 'X12', 'X145', 'X21', 'X152', 'X10', 'X118', 'X102', 'X147', 'X80', 'X121', 'X1', 'X151', 'X35', 'X135', 'X38', 'X136', 'X2', 'X150', 'X17', 'X146', 'X55', 'X141', 'X11', 'X137', 'X97')
profile<-profile[,which(colnames(profile) %in% sample)]
data.table::fread("~/Documents/48sample/mag/annotation.emapper.annotations", sep="\t", header=F, stringsAsFactors=F,fill=TRUE) %>%
  data.frame() ->ko
colnames(ko)<- c("query_name", "seed_eggNOG_ortholog", "seed_ortholog_evalue", "seed_ortholog_score", "Predicted_taxonomic_group", "Predicted_protein_name", "Gene_Ontology_terms_", "EC_number", "KEGG_ko", "KEGG_Pathway", "KEGG_Module", "KEGG_Reaction", "KEGG_rclass", "BRITE", "KEGG_TC", "CAZy_", "BiGG_Reaction", "tax_scope:_eggNOG_taxonomic_level_used_for_annotation", "eggNOG_OGs_", "bestOG_(deprecated,_use_smallest_from_eggnog_OGs)", "COG_Functional_Category", "eggNOG_free_text_description")
ko<-ko[,which(colnames(ko) %in% c("query_name","Predicted_protein_name", "Gene_Ontology_terms_", "EC_number", "KEGG_ko", "KEGG_Pathway", "KEGG_Module", "KEGG_Reaction", "KEGG_rclass", "BRITE", "KEGG_TC", "CAZy_", "BiGG_Reaction", "COG_Functional_Category"))]
ko[,1]<-gsub("_1$","",ko[,1])
merged<-merge(ko,profile,by.x="query_name", by.y="GeneID",all.y=T)
setwd("~/Documents/48sample/mag/DNAgene")
rm(ko,profile,sample)
gc()




merged<-reshape2::melt(merged,id=c("query_name","Predicted_protein_name", "Gene_Ontology_terms_", "EC_number", "KEGG_ko", "KEGG_Pathway", "KEGG_Module", "KEGG_Reaction", "KEGG_rclass", "BRITE", "KEGG_TC", "CAZy_", "BiGG_Reaction", "COG_Functional_Category"))
write.table(merged,file="melt.csv",sep=",")


list<-c("Predicted_protein_name", "Gene_Ontology_terms_", "EC_number", "KEGG_ko", "KEGG_Pathway", "KEGG_Module", "KEGG_Reaction", "KEGG_rclass", "BRITE", "KEGG_TC", "CAZy_", "BiGG_Reaction", "COG_Functional_Category")
for (i in length(list)){
  reshape2::acast(merged, list[i]~variable,sum)%>%t()%>%data.frame() %>% write.csv(.,file=paste(list[i],"abund.csv"))
echo i
  }

pandas代码

import pandas as pd
import numpy as np
import os
os.chdir("/mnt/10t/mzy/06profile/mag")
profile=pd.read_csv("DNA.geneabundance.txt(1)",sep="\t",header=0,index_col=0)
profile=profile[['127', '22', '129', '48', '138', '47', '140', '20', '132', '18', '149', '73', '120', '105', '148', '44', '122', '30', '124', '26', '143', '34', '133', '12', '145', '21', '152', '10', '118', '102', '147', '80', '121', '1', '151', '35', '135', '38', '136', '2', '150', '17', '146', '55', '141', '11', '137', '97']]
ko=pd.read_csv("annotation.emapper.annotations",sep="\t")
ko.columns=["query_name", "seed_eggNOG_ortholog", "seed_ortholog_evalue", "seed_ortholog_score", "Predicted_taxonomic_group", "Predicted_protein_name", "Gene_Ontology_terms_", "EC_number", "KEGG_ko", "KEGG_Pathway", "KEGG_Module", "KEGG_Reaction", "KEGG_rclass", "BRITE", "KEGG_TC", "CAZy_", "BiGG_Reaction", "tax_scope:_eggNOG_taxonomic_level_used_for_annotation", "eggNOG_OGs_", "bestOG_(deprecated,_use_smallest_from_eggnog_OGs)", "COG_Functional_Category", "eggNOG_free_text_description"]
ko=ko[["query_name","Predicted_protein_name", "Gene_Ontology_terms_", "EC_number", "KEGG_ko", "KEGG_Pathway", "KEGG_Module", "KEGG_Reaction", "KEGG_rclass", "BRITE", "KEGG_TC", "CAZy_", "BiGG_Reaction", "COG_Functional_Category"]]
ko["query_name"].replace("_1$","",regex=True,inplace=True)
merged=pd.merge(ko,profile,left_on="query_name",right_on="GeneID",how="right")

merged.columns.values
str(merged)
###merged.rename(columns={'127':'X127', '22':'X22', '129':'X129',
       '48':'X48', '138':'x138', '47':'X47', '140':"X140", '20':'X20', '132':'X132', '18':'X18', '149':'X149', '73':'X73', '120':'X120',
       '105':'X105', '148':'X148', '44':'X44', '122':'X122', '30':'X30', '124':'X124', '26':'X26', '143':'X143', '34':'X34', '133':'X133',
       '12':'X12', '145':'X145', '21':'X21', '152':'X152', '10':'X10', '118':'X118', '102':'X102', '147':'X147', '80':'X80', '121':'X121',
       '1':'X1', '151':'X151', '35':'X35', '135':'X135', '38':'X38', '136':'X136', '2':'X2', '150':'X150', '17':'X17', '146':'X146',
       '55':'X55', '141':'X141', '11':'X11', '137':'X137', '97':'X97'}) ##列名是数字会出问题

melted=merged.melt(id_vars=["query_name","Predicted_protein_name", "Gene_Ontology_terms_", "EC_number", "KEGG_ko", "KEGG_Pathway", "KEGG_Module", "KEGG_Reaction", "KEGG_rclass", "BRITE", "KEGG_TC", "CAZy_", "BiGG_Reaction", "COG_Functional_Category"])
###以下测试内容
#pd.pivot_table(melted,index=[u'Predicted_protein_name'],columns=[u'variable'],aggfunc=[sum]).to_csv('test.csv')
###以下循环内容
for i in ["Predicted_protein_name", "Gene_Ontology_terms_", "EC_number", "KEGG_ko", "KEGG_Pathway", "KEGG_Module", "KEGG_Reaction", "KEGG_rclass", "BRITE", "KEGG_TC", "CAZy_", "BiGG_Reaction", "COG_Functional_Category"]:
	pd.pivot_table(melted,index=i,columns=[u'variable'],aggfunc=[sum]).to_csv("/mnt/10t/mzy/06profile/mag/DNAgene/"+i+".csv")

处理RNA的

import pandas as pd
import numpy as np
import os
os.chdir("/mnt/10t/mzy/06profile/mag")
profile=pd.read_csv("RNA.geneabundance.txt",sep="\t",header=0,index_col=0)
profile=profile[["127","22","129","81","138","99","140","20","132","82","149","73","120","105","148","44","122","101","124","100","143","76","133","12","145","79","152","77","118","102","147","80","121","106","151","104","135","38","136","2","150","78","146","55","141","11","137","97"]]
ko=pd.read_csv("annotation.emapper.annotations",sep="\t")
ko.columns=["query_name", "seed_eggNOG_ortholog", "seed_ortholog_evalue", "seed_ortholog_score", "Predicted_taxonomic_group", "Predicted_protein_name", "Gene_Ontology_terms_", "EC_number", "KEGG_ko", "KEGG_Pathway", "KEGG_Module", "KEGG_Reaction", "KEGG_rclass", "BRITE", "KEGG_TC", "CAZy_", "BiGG_Reaction", "tax_scope:_eggNOG_taxonomic_level_used_for_annotation", "eggNOG_OGs_", "bestOG_(deprecated,_use_smallest_from_eggnog_OGs)", "COG_Functional_Category", "eggNOG_free_text_description"]
ko=ko[["query_name","Predicted_protein_name", "Gene_Ontology_terms_", "EC_number", "KEGG_ko", "KEGG_Pathway", "KEGG_Module", "KEGG_Reaction", "KEGG_rclass", "BRITE", "KEGG_TC", "CAZy_", "BiGG_Reaction", "COG_Functional_Category"]]
ko["query_name"].replace("_1$","",regex=True,inplace=True)
merged=pd.merge(ko,profile,left_on="query_name",right_on="GeneID",how="right")

merged.columns.values

melted=merged.melt(id_vars=["query_name","Predicted_protein_name", "Gene_Ontology_terms_", "EC_number", "KEGG_ko", "KEGG_Pathway", "KEGG_Module", "KEGG_Reaction", "KEGG_rclass", "BRITE", "KEGG_TC", "CAZy_", "BiGG_Reaction", "COG_Functional_Category"])
###以下测试内容
#pd.pivot_table(melted,index=[u'Predicted_protein_name'],columns=[u'variable'],aggfunc=[sum]).to_csv('test.csv')
###以下循环内容
for i in ["Predicted_protein_name", "Gene_Ontology_terms_", "EC_number", "KEGG_ko", "KEGG_Pathway", "KEGG_Module", "KEGG_Reaction", "KEGG_rclass", "BRITE", "KEGG_TC", "CAZy_", "BiGG_Reaction", "COG_Functional_Category"]:
	pd.pivot_table(melted,index=i,columns=[u'variable'],aggfunc=[sum]).to_csv("/mnt/10t/mzy/06profile/mag/RNAgene/"+i+".csv")

数据清洗强烈不建议用R，虽然python也不快，但是比R强