# =============================================================================
# Deami, Oxid, Hexが1つだけ出てくるものをアミノ酸ごとに分類してPeptidごとにエクセルシート化 ＋非修飾
# =============================================================================
import os
import datetime
import pandas as pd
import re
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.styles.alignment import Alignment
from openpyxl.utils.dataframe import dataframe_to_rows
# 元になるエクセルファイルを読み込み
xls0 = 'bpv-sp'
xls = str(xls0) +'.xlsx'
# 配列入力。seq_hcに重鎖、seq_lcに軽鎖を入れる
seq_hc = 'EVQL'
seq_lc = 'DIQM'
Modific = input('どのPTMを抽出する？ 1: Deamidated, 2: Oxidation, 3: Hex ?')
if Modific == '1':
ModificD = 'Deamidated'
elif Modific == '2':
ModificD = 'Oxidation'
elif Modific == '3':
ModificD = 'Hex'
else:
ModificD = 'Others'
# エクセルの所定のシートを指定して、データフレームに読み込む
df11 = pd.read_excel(xls, sheet_name='Matched')
# =============================================================================
# タイトルに含まれる半角スペースを除く
# =============================================================================
# タイトル行をrenameする
#df11 = df110.rename(columns={'AnalysisName': 'Analysis Name','DataFile': 'Data File','TheoreticalMono.Mass': 'Theoretical Mono. Mass','TheoreticalMono.m/z': 'Theoretical Mono. m/z','ObservedMono.m/z': 'Observed Mono. m/z','Error(ppm)': 'Error (ppm)','%PeptideSequenceCoverage': '% Peptide Sequence Coverage','XIC_Area': 'XIC Area','Modification%': 'Modification %','DisulfideBonds': 'Disulfide Bonds','DisulfideBondsStatus': 'Disulfide Bonds Status','AA_Index': 'AA Index','ChainNames': 'Chain Names'})
# =============================================================================
# 条件に合致するものだけを選び出す
# =============================================================================
# ■ 基準1/9 ■ Auto-Validatedが、「TRUE」のもの
#df = df[df['Auto-Validated'] == True]
# ■ 基準2/9 ■ Useが、「Certain」のもの
#df = df[df['Use'] == 'Use']
# ■ 基準3/9 ■ Errorが、10ppm以下のもの
#df = df[df['Error (ppm)'] < 10]
# ■ 基準4/9 ■ Modificationsが空欄のものは除外
#df = df[pd.notnull(df3['Modifications']) == True]
# ■ 基準5/9 ■ DeとOxとHxの合計が一つ／Modificationsで、表記が１つだけのものを抽出
# Modification の ModificDを規定
De = 'Deamidated'
Ox = 'Oxidation'
Hx = 'Hex'
Ca = 'Carboxymethyl'
a2 = 'A2G1'
m5 = 'M5'
g0 = 'G0'
g1 = 'G1'
g2 = 'G2'
df12 = df11[df11['Modifications'].str.count(De) +df11['Modifications'].str.count(Ox) +df11['Modifications'].str.count(Hx) == 1]
# ■ 基準6/9 ■ どのPTMかで抽出する
df13 = df12[df12['Modifications'].str.count(ModificD) == 1]
# =============================================================================
# Deamiか、Oxidation、あるいはHEX を含むものだけにして、あとはすべて除去する
# =============================================================================
df14 = df13['Modifications']
li_modi1 = []
li_modificD = []
for i, deam in enumerate(df14):
li_modificD1 = re.split(',', deam)
for j, modi3 in enumerate(li_modificD1):
print('modi3: ' + modi3)
if (ModificD in modi3):
modi3 = modi3.strip() #空白文字を除く
li_modificD.append(modi3) #合致したものをリストに追加
else:
None
df13['Modifications1'] = li_modificD
# =============================================================================
# 日付を得て、ディレクトリにフォルダを作成して、結果を保存
# =============================================================================
# 日付取得／ファイル名のため
d_today = str(datetime.date.today())
# ディレクトリにフォルダ作成
dirname = d_today +'_' +xls0 +'_' +ModificD +'/'
if not os.path.exists(dirname):
os.mkdir(dirname)
# エクセルとして保存
filename11 = dirname + ModificD +'_' +xls
df13.to_excel(filename11)
# =============================================================================
# エクセルの書式設定
# =============================================================================
wb = load_workbook(filename11)
ws = wb.active
# set font #【エクセル】フォント設定
font = Font(name='Calibri', size=9)
for row in ws:
for cell in row:
ws[cell.coordinate].font = font
# alignment #【エクセル】タイトル行だけ左寄せ
for row in ws['A1:AZ1']:
for cell in row:
cell.alignment = Alignment(horizontal='left')
wb.save(filename11) #【エクセル】書式整えたものを保存
# =============================================================================
# Modifictionsにある、（１）記述、（２)ぺプチド番号、（３）アミノ酸番号を分解して、エクセルにこれらの列を追加する。
# =============================================================================
# sheet1を指定して、データフレームに読み込む
df20 = pd.read_excel(filename11, sheet_name='Sheet1')
# Modificationに記された、（１）記述, （２）ペプチド番号、（３）アミノ酸番号に分解する。
df21 = df20['Modifications1']
df22 = df20['ChainNames']
print(df21)
Aaa = [] #Deamidated / Oxidation / ...
PPn = [] #ペプチド番号
An = [] #アミノ酸番号
AAn = [] #アミノ酸表記
AnAAn = [] #アミノ酸表記
BBB1 = [] #Modificationを分解したリスト
for i, AAA in enumerate(df21):
BBB1 = re.split('[\xa0@()]',AAA)
#----------
if len(BBB1) >=1:
Aaa1 = BBB1[0]
else:
Aaa1 = '9999' #空欄のときは、9999 を割り当てる
Aaa.append(Aaa1)
#----------
if len(BBB1) >=2:
PPn1 = BBB1[1]
else:
PPn1 = '9999' #空欄のときは、9999 を割り当てる
PPn.append(PPn1)
#----------
if len(BBB1) >=3:
AAn1 = BBB1[2]
else:
AAn1 = '9999' #空欄のときは、9999 を割り当てる
AAn.append(AAn1)
#----------
Chain = df22[i]
if len(BBB1) >=3:
if (BBB1[2] == '') or (BBB1[2] == '*'):
An1 = 'X' #空欄のときは、X を割り当てる
else:
if Chain == 'HC':
An1 = seq_hc[int(BBB1[2])-1] #HCを読みに
elif Chain == 'LC':
An1 = seq_lc[int(BBB1[2])-1] #HCを読みに
else:
An1 = 'X' #BBB1の表記が足らないで、割り当てられていないときは、アミノ酸 X を割り当てる
An.append(An1)
AnAAn.append(str(An1)+str(AAn1))
df20['Modifications_Aaa'] = Aaa
df20['Modifications_PPn'] = PPn
df20['Modifications_AAn'] = AAn
df20['Modifications_An'] = An
df20['Modifications_AnAAn'] = AnAAn
# エクセルとして保存
filename21 = dirname + ModificD +'_' +xls0 +'_ID.xlsx'
df20.to_excel(filename21)
# =============================================================================
# エクセルの書式設定
# =============================================================================
wb = load_workbook(filename21)
ws = wb.active
# set font
font = Font(name='Calibri', size=9)
# write in sheet
for row in ws:
for cell in row:
ws[cell.coordinate].font = font
# alignment #【エクセル】タイトル行だけ左寄せ
for row in ws['A1:AZ1']:
for cell in row:
cell.alignment = Alignment(horizontal='left')
wb.save(filename21)
# =============================================================================
# あらかじめX999が記述されたExcelを元に、アミノ酸ごとにwbを作成、Peptideごとにwsにして分類する
# =============================================================================
df40 = pd.read_excel(filename21, sheet_name='Sheet1')
# =============================================================================
# HCとLCで分けてアミノ酸をリスト化する
df401 = df40[df40['ChainNames'] == 'HC']
print(df401)
# 【HC鎖】HC鎖のアミノ酸をリスト化。重複をさけてSeries作る
Ser3HC = df401['Modifications_AnAAn'] #HC鎖のX100のリスト化
ser_AnAAn_HC = dict.fromkeys(Ser3HC) #HC鎖のX999のリスト化／重複回避
# 【HC鎖】（アミノ酸で制限をかけて） ペプチドをリスト化。重複をさけてSeries作る
for i, AAnAn1 in enumerate(ser_AnAAn_HC):
df411 = df401[df401['Modifications_AnAAn'] == AAnAn1] #HC,LCそれぞれでリスト化されたアミノ酸分のdf生成
print(df411)
Ser5HC = df411['Peptide']#Peptideのリスト
ser_PepHC = dict.fromkeys(Ser5HC) #ペプチドのリスト化／重複回避
# ペプチドリストをもとにシートを作成
filename41HC = dirname +ModificD +'_HC' +'_' +AAnAn1 + '.xlsx'
df411.to_excel(filename41HC)
wb = load_workbook(filename41HC)
ws = wb.active
ws.title = 'total'+'_' +ModificD
for i, page in enumerate(ser_PepHC):
ws = wb.create_sheet()
df431 = df411[df411['Peptide'] == page]
for row in dataframe_to_rows(df431,index=None, header=True):
ws.append(row)
ws.title = str(page)
wb.save(filename41HC) #workbook保存
# =============================================================================
# HCとLCで分けてアミノ酸をリスト化する
df402 = df40[df40['ChainNames'] == 'LC']
print(df402)
# 【LC鎖】LC鎖のアミノ酸をリスト化。重複をさけてSeries作る
Ser3LC = df402['Modifications_AnAAn'] #LC鎖のX100のリスト化
ser_AnAAn_LC = dict.fromkeys(Ser3LC) #LC鎖のX999のリスト化／重複回避
# 【LC鎖】（アミノ酸で制限をかけて） ペプチドをリスト化。重複をさけてSeries作る
for i, AAnAn1 in enumerate(ser_AnAAn_LC):
df412 = df402[df402['Modifications_AnAAn'] == AAnAn1] #HC,LCそれぞれでリスト化されたアミノ酸分のdf生成
print(df412)
Ser5LC = df412['Peptide']#Peptideのリスト
ser_PepLC = dict.fromkeys(Ser5LC) #ペプチドのリスト化／重複回避
# ペプチドリストをもとにシートを作成
filename41LC = dirname +ModificD +'_LC' +'_' +AAnAn1 + '.xlsx'
df412.to_excel(filename41LC)
wb = load_workbook(filename41LC)
ws = wb.active
ws.title = 'total'+'_' +ModificD
for i, page in enumerate(ser_PepLC):
ws = wb.create_sheet()
df432 = df412[df412['Peptide'] == page]
for row in dataframe_to_rows(df432,index=None, header=True):
ws.append(row)
ws.title = str(page)
wb.save(filename41LC) #workbook保存
# =============================================================================
# =============================================================================
# 非修飾のものを抽出してエクセルに書き出す
# =============================================================================
# =============================================================================
# 元のエクセルから吸いだす
df61 = pd.read_excel(xls, sheet_name='Matched')
# DeとOxとHxの合計がゼロのものを抽出
df62 = df61[df61['Modifications'].str.count(De) +df61['Modifications'].str.count(Ox) +df61['Modifications'].str.count(Hx) == 0]
# =============================================================================
# HCのものだけ抽出する
df63HC = df62[df62['ChainNames'] == 'HC']
# =============================================================================
# 日付を得て、ディレクトリにフォルダを作成して、結果を保存
# =============================================================================
# 日付取得／ファイル名のため
d_today = str(datetime.date.today())
# ディレクトリにフォルダ作成
dirname = d_today +'_' + xls0 +'_' +'intact' +'/'
if not os.path.exists(dirname):
os.mkdir(dirname)
# エクセルとして保存
filename51HC = dirname + 'HC_intact_' +xls
df63HC.to_excel(filename51HC)
# =============================================================================
# エクセルの書式設定
# =============================================================================
wb = load_workbook(filename51HC)
ws = wb.active
# set font #【エクセル】フォント設定
font = Font(name='Calibri', size=9)
for row in ws:
for cell in row:
ws[cell.coordinate].font = font
# alignment #【エクセル】タイトル行だけ左寄せ
for row in ws['A1:AZ1']:
for cell in row:
cell.alignment = Alignment(horizontal='left')
# =============================================================================
# 【HC】HCのPeptideをリスト化(順序を優先して重複を回避)
ser_PepHC_int = df63HC['Peptide']
ser_PepHC_int2 = dict.fromkeys(ser_PepHC_int) #HCのPeptideがリスト化できた（順番を考慮）
# ペプチドリストをもとにシートを作成
filename52HC = dirname +'HC_intact_' +xls0 + '_Pep.xlsx'
df63HC.to_excel(filename52HC)
wb = load_workbook(filename52HC)
ws = wb.active
ws.title = 'total'+'_HC_int'
for i, page in enumerate(ser_PepHC_int2):
ws = wb.create_sheet()
df64HC = df63HC[df63HC['Peptide'] == page]
for row in dataframe_to_rows(df64HC,index=None, header=True):
ws.append(row)
ws.title = str(page)
wb.save(filename52HC) #workbook保存
# =============================================================================
# LCのものだけ抽出する
df63LC = df62[df62['ChainNames'] == 'LC']
# =============================================================================
# 日付を得て、ディレクトリにフォルダを作成して、結果を保存
# =============================================================================
# 日付取得／ファイル名のため
#d_today = str(datetime.date.today())
# ディレクトリにフォルダ作成
#dirname = d_today +'_' + xls +'_' +'intact' +'/'
#if not os.path.exists(dirname):
# os.mkdir(dirname)
# エクセルとして保存
filename51LC = dirname + 'LC_intact_' +xls
df63LC.to_excel(filename51LC)
# =============================================================================
# エクセルの書式設定
# =============================================================================
wb = load_workbook(filename51LC)
ws = wb.active
# set font #【エクセル】フォント設定
font = Font(name='Calibri', size=9)
for row in ws:
for cell in row:
ws[cell.coordinate].font = font
# alignment #【エクセル】タイトル行だけ左寄せ
for row in ws['A1:AZ1']:
for cell in row:
cell.alignment = Alignment(horizontal='left')
# =============================================================================
# 【LC】LCのPeptideをリスト化(順序を優先して重複を回避)
ser_PepLC_int = df63LC['Peptide']
ser_PepLC_int2 = dict.fromkeys(ser_PepLC_int) #LCのPeptideがリスト化できた（順番を考慮）
# ペプチドリストをもとにシートを作成
filename52LC = dirname +'LC_intact_' +xls0 + '_Pep.xlsx'
df63LC.to_excel(filename52LC)
wb = load_workbook(filename52LC)
ws = wb.active
ws.title = 'total'+'_LC_int'
for i, page in enumerate(ser_PepLC_int2):
ws = wb.create_sheet()
df64LC = df63LC[df63LC['Peptide'] == page]
for row in dataframe_to_rows(df64LC,index=None, header=True):
ws.append(row)
ws.title = str(page)
wb.save(filename52LC) #workbook保存
# コードの終わり