In [1]:
#the point of this notebook is to be able to call out specific sets without cutting and pasting everything
In [2]:
#import modules
import numpy as np
import xlrd as xl
import xlwt
In [3]:
#Load book, define sheet as X1, print the number of rows, set n to number of rows
#of sheet 1 column 1, numbers is column,row format. List is X1a, creates array z.
#Create new Workbook
X1 = xl.open_workbook('avpfloat.xlsx').sheet_by_name("NoFormulas")
book=xlwt.Workbook()
In [4]:
#ID test, should be 34730 x 28
m =X1.ncols
n =X1.nrows
print n,"x",m
In [6]:
#test and append D29E pCPP45::avrPto I96A 2xA upregulated (PTI)
#p-> pvalue
#r-> ratio
#d-> RPKM D_mean
#e-> RPKM E_mean
#DEupID-> List of upregulated gene IDs for genes upregulated in D29E pCPP45::avrPto I96A 2xA relative to mock inoculation
#DEupDesc-> Readable descriptions
#DEupRatio-> List of ratios for associated genes
#DEuppVal-> List of pValues for associated genes
DEupID= []
DEupDesc= []
DEupRatio= []
DEuppVal=[]
DEupID.append('GeneID')
DEupDesc.append('Description')
DEupRatio.append('Ratio')
DEuppVal.append('Adjust p')
for i in range (1,n):
p= X1.cell_value(rowx=i, colx=21)
r= X1.cell_value(rowx=i, colx=20)
d= X1.cell_value(rowx=i, colx=19)
e= X1.cell_value(rowx=i, colx=18)
if (p<=0.05 and r>=2 and (d>=3 or e>=3)):
DEupID.append(X1.cell_value(i,0).encode('utf8'))
DEupDesc.append(X1.cell_value(i,1).encode('utf8'))
DEupRatio.append(X1.cell_value(i,20))
DEuppVal.append(X1.cell_value(i,21))
z=len(DEupID)
sheet=book.add_sheet('I96A+2xA_Up-regulated_vs_Mock')
for i in range (0,z):
sheet.write(i,0,DEupID[i].decode('utf8'))
sheet.write(i,1,DEupDesc[i].decode('utf8'))
sheet.write(i,2,DEupRatio[i])
sheet.write(i,3,DEuppVal[i])
In [7]:
#Test and append "AvrPto FIRE" genes
#pad-> pvalue AD
#pde-> pvalue DE
#rad-> ratio AD
#rde-> ratio DE
#a-> RPKM A_mean
#d-> RPKM D_mean
#e-> RPKM E_mean
#AvpFire-> List of genes meeting criteria for "AvrPto FIRE" genes. See Rosli 2013 for FIRE gene list.
#AvrPto FIRE genes are upregulated with D29E pCPP45::avrPto I96A 2xA relative to a mock inoculation and downregulated
#by D29E pCPP45::avrPto relative to D29E pCPP45::avrPto I96A 2xA
#Ratio and pValue lists also made
AvpFireID= []
AvpFireDesc= []
AvpFireADRatio= []
AvpFireDERatio= []
AvpFireADpVal= []
AvpFireDEpVal= []
AvpFireID.append('GeneID')
AvpFireDesc.append('Description')
AvpFireADRatio.append('WT vs I96A+2xA ratio')
AvpFireADpVal.append('WT vs I96A+2xA adjust p')
AvpFireDERatio.append('I96A+2xA vs Mock ratio')
AvpFireDEpVal.append('I96A+2xA vs Mock adjust p')
for i in range (1,n):
pad= X1.cell_value(rowx=i, colx=5)
pde= X1.cell_value(rowx=i, colx=21)
rad= X1.cell_value(rowx=i, colx=4)
rde= X1.cell_value(rowx=i, colx=20)
a= X1.cell_value(rowx=i, colx=3)
d= X1.cell_value(rowx=i, colx=2)
e= X1.cell_value(rowx=i, colx=18)
if ((pde<=0.05 and rde>=2 and (d>=3 or e>=3))and(pad<=0.05 and rad<=0.5 and (a>=3 or d>=3))):
AvpFireID.append(X1.cell_value(i,0).encode('utf8'))
AvpFireDesc.append(X1.cell_value(i,1).encode('utf8'))
AvpFireADRatio.append(X1.cell_value(i,4))
AvpFireADpVal.append(X1.cell_value(i,5))
AvpFireDERatio.append(X1.cell_value(i,20))
AvpFireDEpVal.append(X1.cell_value(i,21))
z=len(AvpFireID)
sheet=book.add_sheet('AvrPto_FIRE')
for i in range (0,z):
sheet.write(i,0,AvpFireID[i].decode('utf8'))
sheet.write(i,1,AvpFireDesc[i].decode('utf8'))
sheet.write(i,2,AvpFireADRatio[i])
sheet.write(i,3,AvpFireADpVal[i])
sheet.write(i,4,AvpFireDERatio[i])
sheet.write(i,5,AvpFireDEpVal[i])
In [8]:
#Test and append up and down regulated genes of each domain
#a-> RPKM A_mean
#b-> RPKM B_mean
#c-> RPKM C_mean
#d-> RPKM D_mean
#pad-> pvalue AD
#pbd-> pvalue BD
#pcd-> pvalue CD
#rad-> Ratio AD
#rbd-> Ratio BD
#rcd-> Ratio CD
#AvrPtoUp-> List of genes significantly up-regulated in D29E pCPP45::avrPto relative to D29E pCPP45::avrPto I96A,2xA
#AvrPtoDown-> List of genes significantly down-regulated in D29E pCPP45::avrPto relative to D29E pCPP45::avrPto I96A,2xA
#CTDUp-> List of genes significantly up-regulated in D29E pCPP45::avrPto I96A relative to D29E pCPP45::avrPto I96A,2xA
#CTDDown-> List of genes significantly down-regulated in D29E pCPP45::avrPto I96A relative to D29E pCPP45::avrPto I96A,2xA
#CoreUp-> List of genes significantly up-regulated in D29E pCPP45::avrPto 2xA relative to D29E pCPP45::avrPto I96A,2xA
#CoreDown-> List of genes significantly down-regulated in D29E pCPP45::avrPto 2xA relative to D29E pCPP45::avrPto I96A,2xA
#Description, Ratio and pValue lists also made - shortened titles compared to above.ID = GeneID, D = description
#R = Ratio, P = adjust p
AvrPtoUpID= []
AvrPtoUpD= []
AvrPtoUpR= []
AvrPtoUpP= []
AvrPtoDownID= []
AvrPtoDownD= []
AvrPtoDownR= []
AvrPtoDownP= []
CTDUpID= []
CTDUpD= []
CTDUpR= []
CTDUpP= []
CTDDownID= []
CTDDownD= []
CTDDownR= []
CTDDownP= []
CoreUpID= []
CoreUpD= []
CoreUpR= []
CoreUpP= []
CoreDownID=[]
CoreDownD= []
CoreDownR= []
CoreDownP= []
AvrPtoUpID.append('GeneID')
AvrPtoUpD.append('Description')
AvrPtoUpR.append('Ratio')
AvrPtoUpP.append('Adjust p')
AvrPtoDownID.append('GeneID')
AvrPtoDownD.append('Description')
AvrPtoDownR.append('Ratio')
AvrPtoDownP.append('Adjust p')
CTDUpID.append('GeneID')
CTDUpD.append('Description')
CTDUpR.append('Ratio')
CTDUpP.append('Adjust p')
CTDDownID.append('GeneID')
CTDDownD.append('Description')
CTDDownR.append('Ratio')
CTDDownP.append('Adjust p')
CoreUpID.append('GeneID')
CoreUpD.append('Description')
CoreUpR.append('Ratio')
CoreUpP.append('Adjust p')
CoreDownID.append('GeneID')
CoreDownD.append('Description')
CoreDownR.append('Ratio')
CoreDownP.append('Adjust p')
for i in range (1,n):
a= X1.cell_value(rowx=i, colx=3)
b= X1.cell_value(rowx=i, colx=7)
c= X1.cell_value(rowx=i, colx=11)
d= X1.cell_value(rowx=i, colx=10)
pad= X1.cell_value(rowx=i, colx=5)
pbd= X1.cell_value(rowx=i, colx=9)
pcd= X1.cell_value(rowx=i, colx=13)
rad= X1.cell_value(rowx=i, colx=4)
rbd= X1.cell_value(rowx=i, colx=8)
rcd= X1.cell_value(rowx=i, colx=12)
if (pad<=0.5 and rad>=2 and (a>=3 or d>=3)):
AvrPtoUpID.append(X1.cell_value(i,0).encode('utf8'))
AvrPtoUpD.append(X1.cell_value(i,1).encode('utf8'))
AvrPtoUpR.append(X1.cell_value(i,4))
AvrPtoUpP.append(X1.cell_value(i,5))
if (pad<=0.5 and rad<=0.5 and (a>=3 or d>=3)):
AvrPtoDownID.append(X1.cell_value(i,0).encode('utf8'))
AvrPtoDownD.append(X1.cell_value(i,1).encode('utf8'))
AvrPtoDownR.append(X1.cell_value(i,4))
AvrPtoDownP.append(X1.cell_value(i,5))
if (pbd<=0.5 and rbd>=2 and (b>=3 or d>=3)):
CTDUpID.append(X1.cell_value(i,0).encode('utf8'))
CTDUpD.append(X1.cell_value(i,1).encode('utf8'))
CTDUpR.append(X1.cell_value(i,8))
CTDUpP.append(X1.cell_value(i,9))
if (pbd<=0.5 and rbd<=0.5 and (b>=3 or d>=3)):
CTDDownID.append(X1.cell_value(i,0).encode('utf8'))
CTDDownD.append(X1.cell_value(i,1).encode('utf8'))
CTDDownR.append(X1.cell_value(i,8))
CTDDownP.append(X1.cell_value(i,9))
if (pcd<=0.5 and rcd>=2 and (c>=3 or d>=3)):
CoreUpID.append(X1.cell_value(i,0).encode('utf8'))
CoreUpD.append(X1.cell_value(i,1).encode('utf8'))
CoreUpR.append(X1.cell_value(i,12))
CoreUpP.append(X1.cell_value(i,13))
if (pcd<=0.5 and rcd<=0.5 and (c>=3 or d>=3)):
CoreDownID.append(X1.cell_value(i,0).encode('utf8'))
CoreDownD.append(X1.cell_value(i,1).encode('utf8'))
CoreDownR.append(X1.cell_value(i,12))
CoreDownP.append(X1.cell_value(i,13))
z=len(AvrPtoUpID)
sheet=book.add_sheet('WT up-regulated vs I96A+2xA')
for i in range (0,z):
sheet.write(i,0,AvrPtoUpID[i].decode('utf8'))
sheet.write(i,1,AvrPtoUpD[i].decode('utf8'))
sheet.write(i,2,AvrPtoUpR[i])
sheet.write(i,3,AvrPtoUpP[i])
z=len(AvrPtoDownID)
sheet=book.add_sheet('WT down-regulated vs I96A+2xA')
for i in range (0,z):
sheet.write(i,0,AvrPtoDownID[i].decode('utf8'))
sheet.write(i,1,AvrPtoDownD[i].decode('utf8'))
sheet.write(i,2,AvrPtoDownR[i])
sheet.write(i,3,AvrPtoDownP[i])
z=len(CTDUpID)
sheet=book.add_sheet('CTD up-regulated vs I96A+2xA')
for i in range (0,z):
sheet.write(i,0,CTDUpID[i].decode('utf8'))
sheet.write(i,1,CTDUpD[i].decode('utf8'))
sheet.write(i,2,CTDUpR[i])
sheet.write(i,3,CTDUpP[i])
z=len(CTDDownID)
sheet=book.add_sheet('CTD down-regulated vs I96A+2xA')
for i in range (0,z):
sheet.write(i,0,CTDDownID[i].decode('utf8'))
sheet.write(i,1,CTDDownD[i].decode('utf8'))
sheet.write(i,2,CTDDownR[i])
sheet.write(i,3,CTDDownP[i])
z=len(CoreUpID)
sheet=book.add_sheet('Core up-regulated vs I96A+2xA')
for i in range (0,z):
sheet.write(i,0,CoreUpID[i].decode('utf8'))
sheet.write(i,1,CoreUpD[i].decode('utf8'))
sheet.write(i,2,CoreUpR[i])
sheet.write(i,3,CoreUpP[i])
z=len(CoreDownID)
sheet=book.add_sheet('Core down-regulated vs I96A+2xA')
for i in range (0,z):
sheet.write(i,0,CoreDownID[i].decode('utf8'))
sheet.write(i,1,CoreDownD[i].decode('utf8'))
sheet.write(i,2,CoreDownR[i])
sheet.write(i,3,CoreDownP[i])
In [9]:
book.save('Lists of genes showing significant regulation.xls')
In [ ]: