python用于临床数据管理_LB-AE核查

季风吹向大海/
已于 2022-02-08 15:02:58 修改
阅读量308
点赞数
分类专栏： python用于临床数据管理文章标签： python
于 2021-05-07 09:32:15 首次发布
本文链接：https://blog.csdn.net/weixin_47536499/article/details/116481363
版权
python用于临床数据管理专栏收录该内容
8 篇文章 2 订阅
订阅专栏
import tkinter as tk
from tkinter.filedialog import askopenfilename
import re
#设置GUI-----------------------------------------------------------------------
path1='NA'  #datalisting路径
path2='NA'  #输出路径
def hit_b1():
    global path1
    path1=askopenfilename()
    text1.insert(1.0,path1)
    print(path1)
def hit_b2():
    global path2,en1
    path2=en1.get()
    print(path2)    
    window.destroy()
    

window=tk.Tk()
window.geometry('360x120')
window.title("B301LABAE核查")


b1=tk.Button(window,text='选择数据集文件',command=hit_b1)
b1.grid(row=1,sticky="w")
lab1=tk.Label(window,text='结果输出到：')
lab1.grid(row=0,column=0,sticky="w")
en1=tk.Entry(window,show=None,width=30)
en1.grid(row=0,column=1)
b2=tk.Button(window,text='运行',width=12,height=1,command=hit_b2)
b2.grid(row=3)
text1=tk.Text(window,width=30,height=1)
text1.grid(row=1,column=1)


window.mainloop()

print(path1)
print(path2)
#生成已排序的AELab文件---------------------------------------------------------------------------------
#AE开始日期为空或UK的替换为1900/01/01，结束日期为空则替换为2100-01-01
#修改AEform日期格式

import pandas as pd
import re
import numpy as np
import os


writer=pd.ExcelWriter(r'{}\SCT-I10A-B301-LABAE.xlsx'.format(path2))
path=path1
def B301_AELAB(path,writer,sheetname):
    import pandas as pd
    from datetime import datetime
    sheetAE='AE'
    AE=pd.read_excel(path,sheetAE)
    sheetLB1=sheetname
    LB1=pd.read_excel(path,sheetLB1)
#-----------------------------------------------------------------------------------------------------------------------------
    subject_AE=list(AE['受试者编号'])
    folder_AE=list(AE['访视详细名称'])
    form_AE=list(AE['表单名称'])
    line_AE=list(AE['记录编号'])
    field_AE=list(AE['不良事件名称'])
    sdate_AE=list(AE['开始日期'])
#    Compared_AE=list(AE['Compared result'])
    ongo=list(AE['是否持续？'])
    edate_AE=list(AE['结束日期'])
    out_AE=list(AE['转归  '])
 
    for i in range(len(AE.values)):
        if re.findall('\d$',str(sdate_AE[i]))==[]:
            
            date='1900 01 01'
            sdate_AE[i]=datetime.date(datetime.strptime(date,'%Y %m %d'))
        else:
            sdate_AE[i]=datetime.date(datetime.strptime((sdate_AE[i]),'%Y-%m-%d'))
    for i in range(len(AE.values)):
        if re.findall('\d$',str(edate_AE[i]))==[]:
            date='2100 01 01'
            edate_AE[i]=datetime.date(datetime.strptime(date,'%Y %m %d'))
        else:
            edate_AE[i]=datetime.date(datetime.strptime((edate_AE[i]),'%Y-%m-%d'))

#-----------------------------------------------------------------------------------------------------------------------------
    subject_LB1=list(LB1['受试者编号'])
    folder_LB1=list(LB1['访视详细名称'])
    form_LB1=list(LB1['表单名称'])
    field_LB1=list(LB1['实验室检测项'])
    CS_LB1=list(LB1['临床意义判定'])
    date_LB1=list(LB1['采样日期'])
    comment_LB1=list(LB1['备注'])
#    Compared_LB1=list(LB1['Compared result'])
    result=list(LB1['检测结果'])
 
    for i in range(len(date_LB1)):
        if date_LB1[i] !=date_LB1[i]:  #判断是否为nan，只需看是否与自身相等，在数学上nan！=nan
            date='1900 01 01'
            date_LB1[i]=datetime.date(datetime.strptime(date,'%Y %m %d'))
        else:
            date_LB1[i]=datetime.date(datetime.strptime(str(date_LB1[i]),'%Y-%m-%d'))
    na_LB1=[]
    for i in range (len(LB1.values)):
        na_LB1.append('NA')
#----------------------------------------------------------------------------------------------------------------------------
    Subject=subject_AE + subject_LB1

    Folder=folder_AE + folder_LB1

    Form=form_AE + form_LB1

    Field=field_AE + field_LB1

    Date1=sdate_AE + date_LB1

    Date2_CS=edate_AE + CS_LB1

    Line=line_AE + comment_LB1

    OUT=out_AE + na_LB1
    result_ongo=ongo+result
    print(result_ongo)
#---------------------------------------------------------------------------------------------------------------------------------------------
    df=pd.DataFrame(data={'受试者编号':Subject,'访视':Folder,'表单':Form,'字段':Field,'AE开始日期/LAB采样日期':Date1,'AE是否持续/LB检查结果':result_ongo,'AE结束日期/LAB临床意义判定':Date2_CS,'AELogline#/CommentLB1':Line,'转归':OUT},columns=['受试者编号','访视','表单','字段','AE开始日期/LAB采样日期','AE是否持续/LB检查结果','AE结束日期/LAB临床意义判定','AELogline#/CommentLB1','转归'])

    df=df.sort_values(['受试者编号','AE开始日期/LAB采样日期'])
 
    df.to_excel(writer,sheet_name=LB1['表单名称'][1],index=False)

#调用函数生成5个sheet--------------------------------------------------------------------------------------
sheetname='LB1'
B301_AELAB(path,writer,sheetname)
sheetname='LB2'
B301_AELAB(path,writer,sheetname)
sheetname='LB3'
B301_AELAB(path,writer,sheetname)
sheetname='LB4'
B301_AELAB(path,writer,sheetname)
sheetname='LB5'
B301_AELAB(path,writer,sheetname)
writer.save()

#生成lab-ae互相匹配的表单-------------------------------------------------------------------------------------------------------
import pandas as pd
import re
from datetime import datetime
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
import os

writer=pd.ExcelWriter(r'{}\SCT-I10A-B301-LABAE-2.xlsx'.format(path2))
a=datetime.now()
#血常规------------------------------------------------------------------------------------------------------------------------------------------------------
def AELAB0(path,writer,sheetname):
    LB=[]
    LBdate=[]
    LBCS=[]
    LB2=[]
    LBdate2=[]
    LBCS2=[]
    data=pd.read_excel(path,sheetname)
    for i in range(len(data)):
        LB.append('NA')
        LBdate.append('NA')
        LBCS.append('NA')
        LB2.append('NA')
        LBdate2.append('NA')
        LBCS2.append('NA')
    for i in range(len(data)):
        if (data['表单'][i]=='不良事件') and (data['字段'][i]==data['字段'][i]):
            for n in range(len(data)):
                if  data['受试者编号'][i]==data['受试者编号'][n] and data['表单'][n]=='血常规'  :
#红细胞计数---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                        #含红细胞的AE及红细胞计数检查项
                        if len(re.findall('红细胞',str(data['字段'][i])))>0  and data['字段'][n]=='红细胞计数（RBC）' and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                            #匹配AE开始日期对应检查
                            if  data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                                LB[i]=data['字段'][n]
                                LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                                LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                                print(i)
                            print(data['AE开始日期/LAB采样日期'][n],data['AE开始日期/LAB采样日期'][i],data['AE结束日期/LAB临床意义判定'][i])
                            #匹配检查项对应的AE
                            if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                                LB[n]=data['字段'][i]
                                LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                                LBCS[n]=data['AELogline#/CommentLB1'][i]
                                print(i)
                            print('/')
                            #匹配结束日期对应的检查
                            if  data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n]:
                                LB2[i]=data['字段'][n]
                                LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                                LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                                print(i)
#白细胞计数--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                        if len(re.findall('白细胞',data['字段'][i]))>0  and data['字段'][n]=='白细胞计数（WBC）' and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':                   
                            if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                                LB[i]='白细胞计数（WBC）'
                                LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                                LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                                print(i)
                            if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                                LB[n]=data['字段'][i]
                                LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                                LBCS[n]=data['AELogline#/CommentLB1'][i]
                                print(i)
                            if data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n]:
                                LB2[i]='白细胞计数（WBC）'
                                LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                                LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                                print(i)
#贫血------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                        #如果不良事件名称中包含【贫血】字样；检查项名称为【血红蛋白浓度（HGB）】；检查结果不为NA或ND；
                        if len(re.findall('贫血',data['字段'][i]))>0  and data['字段'][n]=='血红蛋白浓度（HGB）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                            #如果不良事件开始日期等于检查日期：将此检查项名称、日期、临床意义判定匹配至此不良事件开始日期对应的检查。
                            if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                                LB[i]='血红蛋白浓度（HGB）'
                                LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                                LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                                print(i)
                            #如果检查日期晚于等于AE开始日期且早于AE结束日期，则将此AE匹配至此检查项对应的AE。
                            if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                                LB[n]=data['字段'][i]
                                LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                                LBCS[n]=data['AELogline#/CommentLB1'][i]
                                print(i)
                            #如果AE结束日期等于检查项检查日期，则将此检查项匹配至AE结束日期对应的检查。
                            if data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n]:
                                LB2[i]='血红蛋白浓度（HGB）'
                                LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                                LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                                print(i)
                        #如果贫血不良事件开始日期对应的检查项的临床意义判定不是异常有临床意义，且存在异常有临床意义的红细胞计数，则将红细胞计数匹配至贫血开始日期对应的检查。
                        if len(re.findall('贫血',data['字段'][i]))>0  and data['字段'][n]=='红细胞计数（RBC）'  and LBCS[i] != '异常有临床意义' and data['AE结束日期/LAB临床意义判定'][n]=='异常有临床意义'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                            if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                                LB[i]=data['字段'][n]
                                LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                                LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                    #如果红细胞计数目前对应的不良事件为空（不存在不良事件名称中含红细胞的对应的不良事件），且检查日期在贫血AE持续期间，则将此贫血AE匹配至此检查项对应的AE。
                        if len(re.findall('贫血',data['字段'][i]))>0  and data['字段'][n]=='红细胞计数（RBC）'  and LBCS[n]=='NA' and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                            if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                                LB[n]=data['字段'][i]
                                LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                                LBCS[n]=data['AELogline#/CommentLB1'][i]
                                print(i)
#血红蛋白浓度---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                        if len(re.findall('血红蛋白',data['字段'][i]))>0  and data['字段'][n]=='血红蛋白浓度（HGB）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                            if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                                LB[i]='血红蛋白浓度（HGB）'
                                LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                                LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                                print(i)
                            if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                                LB[n]=data['字段'][i]
                                LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                                LBCS[n]=data['AELogline#/CommentLB1'][i]
                                print(i)
                            if data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n]:
                                LB2[i]='血红蛋白浓度（HGB）'
                                LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                                LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                                print(i)
#中性粒细胞绝对值------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                        if len(re.findall('中性粒',data['字段'][i]))>0  and data['字段'][n]=='中性粒细胞绝对值（NEUT#）' and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                            if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                                LB[i]=data['字段'][n]
                                LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                                LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                                print(i)
                            if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                                LB[n]=data['字段'][i]
                                LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                                LBCS[n]=data['AELogline#/CommentLB1'][i]
                                print(i)
                            if data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n]:
                                LB2[i]=data['字段'][n]
                                LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                                LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
#2021 03 24 修改中性粒细胞百分比检查可匹配含中性粒字样的AE---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                        if len(re.findall('中性粒',data['字段'][i]))>0  and data['字段'][n]=='中性粒细胞百分比（NEUT%）' and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                            if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                                LB[n]=data['字段'][i]
                                LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                                LBCS[n]=data['AELogline#/CommentLB1'][i]
                                print(i)
                        #如果含中性粒的AE开始日期未匹配上异常有临床意义的中性粒细胞绝对值，则尝试匹配异常有临床意义的中性粒细胞百分比
                        if len(re.findall('中性粒',data['字段'][i]))>0  and data['字段'][n]=='中性粒细胞百分比（NEUT%）' and LBCS[i] !='异常有临床意义' and data['AE结束日期/LAB临床意义判定'][n]=='异常有临床意义' and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                            if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                                LB[i]=data['字段'][n]
                                LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                                LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                                print(i)
#单核细胞绝对值-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                        if len(re.findall('单核细胞',data['字段'][i]))>0  and data['字段'][n]=='单核细胞绝对值（MONO#）' and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                            if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                                LB[i]='单核细胞绝对值（MONO#）'
                                LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                                LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                                print(i)
                            if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                                LB[n]=data['字段'][i]
                                LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                                LBCS[n]=data['AELogline#/CommentLB1'][i]
                                print(i)
                            if data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n]:
                                LB2[i]='单核细胞绝对值（MONO#）'
                                LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                                LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                                print(i)
#淋巴细胞绝对值---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                        if len(re.findall('淋巴细胞',data['字段'][i]))>0  and data['字段'][n]=='淋巴细胞绝对值（LYMPH#）' and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                            if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                                LB[i]='淋巴细胞绝对值（LYMPH#）'
                                LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                                LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                                print(i)
                            if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                                LB[n]=data['字段'][i]
                                LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                                LBCS[n]=data['AELogline#/CommentLB1'][i]
                                print(i)
                            if data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n]:
                                LB2[i]='淋巴细胞绝对值（LYMPH#）'
                                LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                                LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                                print(i)
#血小板计数----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                        if len(re.findall('血小板',data['字段'][i]))>0  and data['字段'][n]=='血小板计数（PLT）' and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                            if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                                LB[i]='血小板计数（PLT）'
                                LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                                LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                                print(i)
                            if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                                LB[n]=data['字段'][i]
                                LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                                LBCS[n]=data['AELogline#/CommentLB1'][i]
                                print(i)
                            if data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n]:
                                LB2[i]='血小板计数（PLT）'
                                LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                                LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                                print(i)
#写入excel------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    data['开始日期对应的LB/LB对应的AE']=LB
    data['LB检测日期/AE开始日期']=LBdate
    data['LB临床意义判定/AENO']=LBCS
    data['结束日期对应的LB']=LB2
    data['LB检测日期']=LBdate2
    data['LB临床意义判定']=LBCS2
    print(data)
    data.to_excel(writer,sheetname,index=False)
    
#血生化--------------------------------------------------------------------------------------------------------------------------------
def AELAB1(path,writer,sheetname):
    LB=[]
    LBdate=[]
    LBCS=[]
    LB2=[]
    LBdate2=[]
    LBCS2=[]
    data=pd.read_excel(path,sheetname)
    for i in range(len(data)):
        LB.append('NA')
        LBdate.append('NA')
        LBCS.append('NA')
        LB2.append('NA')
        LBdate2.append('NA')
        LBCS2.append('NA')
    for i in range(len(data)):
        if data['表单'][i]=='不良事件' and data['字段'][i]==data['字段'][i]:
            for n in range(len(data)):
                if data['受试者编号'][i]==data['受试者编号'][n] and data['表单'][n]=='血生化' :
#天冬氨酸氨基转移酶----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    if len(re.findall('天冬氨酸',data['字段'][i]))>0  and data['字段'][n]=='天冬氨酸氨基转移酶（AST）'   and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:    
                            LB[i]=data['字段'][n]
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
#新增匹配谷草转氨酶-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    if len(re.findall('谷草转氨酶',data['字段'][i]))>0  and data['字段'][n]=='天冬氨酸氨基转移酶（AST）'   and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:    
                            LB[i]=data['字段'][n]
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
#丙氨酸氨基转移酶-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    if len(re.findall('丙氨酸',data['字段'][i]))>0  and data['字段'][n]=='丙氨酸氨基转移酶（ALT）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]=data['字段'][n]
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
#新增匹配谷丙转氨酶-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    if len(re.findall('谷丙转氨酶',data['字段'][i]))>0  and data['字段'][n]=='丙氨酸氨基转移酶（ALT）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]=data['字段'][n]
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
#碱性磷酸酶--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    if len(re.findall('碱性磷酸酶',data['字段'][i]))>0  and data['字段'][n]=='碱性磷酸酶（ALP）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]=data['字段'][n]
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
#谷氨酰转肽酶----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    if len(re.findall('谷氨酰转肽酶',data['字段'][i]))>0  and data['字段'][n]=='谷氨酰转肽酶（GGT）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]=data['字段'][n]
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
#尿素------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    if len(re.findall('尿素',data['字段'][i]))>0  and data['字段'][n]=='尿素（UREA）' and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n] :
                            LB[i]=data['字段'][n]
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
#尿素氮-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    if len(re.findall('尿素',data['字段'][i]))>0  and data['字段'][n]=='尿素氮（BUN）' and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        #如果含尿素AE未匹配上尿素，则尝试匹配尿素氮
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]  and LBCS[i] !='异常有临床意义' and data['AE结束日期/LAB临床意义判定'][n]=='异常有临床意义':
                            LB[i]=data['字段'][n]
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
#血肌酐---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    if len(re.findall('肌酐',data['字段'][i]))>0  and data['字段'][n]=='血肌酐（CREA）' and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n] :
                            LB[i]='血肌酐（CREA）'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
#白蛋白--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                            #含白蛋白字样的AE开始日期只匹配白蛋白
                    if len(re.findall('白蛋白',data['字段'][i]))>0  and data['字段'][n]=='白蛋白（ALB）'and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n] :
                                LB[i]='白蛋白（ALB）'
                                LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                                LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                            #含蛋白的AE但排除蛋白尿(属于尿蛋白）
                    if len(re.findall('蛋白',data['字段'][i]))>0 and data['字段'][i] !='蛋白尿' and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        #如果AE含蛋白字样，则白蛋白检查项可匹配至含蛋白的AE，AE对应的检查匹配总蛋白不匹配白蛋白
                        if data['字段'][n]=='白蛋白（ALB）' :
                            if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                                LB[n]=data['字段'][i]
                                LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                                LBCS[n]=data['AELogline#/CommentLB1'][i]
                                print(i)
                        #检查项为总蛋白时LabAE可互相匹配
                        if data['字段'][n]=='总蛋白（TP）' :
                            if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                                LB[i]='总蛋白（TP）'
                                LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                                LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                            if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                                LB[n]=data['字段'][i]
                                LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                                LBCS[n]=data['AELogline#/CommentLB1'][i]
                                print(i)
#总胆红素-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    if len(re.findall('总胆红素',data['字段'][i]))>0  and data['字段'][n]=='总胆红素（TBIL）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]='总胆红素（TBIL）'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
#直接胆红素---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    if len(re.findall('直接胆红素',data['字段'][i]))>0  and data['字段'][n]=='直接胆红素（DBIL）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]='直接胆红素（DBIL）'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
#肌酸激酶--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    if len(re.findall('肌酸激酶',data['字段'][i]))>0  and data['字段'][n]=='肌酸激酶（CK）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]='肌酸激酶（CK）'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
#血糖------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    if len(re.findall('血糖',data['字段'][i]))>0  and data['字段'][n]=='空腹血糖（Glu）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]='空腹血糖（Glu）'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
#钾-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    if len(re.findall('钾',data['字段'][i]))>0  and data['字段'][n]=='钾（K）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]='钾（K）'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
#钠-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    if len(re.findall('钠',data['字段'][i]))>0  and data['字段'][n]=='钠（Na）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]='钠（Na）'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
#氯-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    if len(re.findall('氯',data['字段'][i]))>0  and data['字段'][n]=='氯（Cl）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]='氯（Cl）'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
#镁-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    if len(re.findall('镁',data['字段'][i]))>0  and data['字段'][n]=='镁（Mg）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]='镁（Mg）'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
#钙-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    if len(re.findall('钙',data['字段'][i]))>0  and data['字段'][n]=='钙（Ca）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]='钙（Ca）'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
#磷-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    if len(re.findall('磷',data['字段'][i]))>0  and data['字段'][n]=='磷（P）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]='磷（P）'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
#新增匹配结束日期-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    if len(re.findall('天冬氨酸',data['字段'][i]))>0  and data['字段'][n]=='天冬氨酸氨基转移酶（AST）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        print(i)
                    #新增匹配谷草转氨酶
                    if len(re.findall('谷草转氨酶',data['字段'][i]))>0  and data['字段'][n]=='天冬氨酸氨基转移酶（AST）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        print(i)
                    if len(re.findall('丙氨酸',data['字段'][i]))>0  and data['字段'][n]=='丙氨酸氨基转移酶（ALT）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        print(i)
                        #新增匹配谷丙转氨酶
                    if len(re.findall('谷丙转氨酶',data['字段'][i]))>0  and data['字段'][n]=='丙氨酸氨基转移酶（ALT）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        print(i)
                    if len(re.findall('碱性磷酸酶',data['字段'][i]))>0  and data['字段'][n]=='碱性磷酸酶（ALP）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        print(i)
                    if len(re.findall('谷氨酰转肽酶',data['字段'][i]))>0  and data['字段'][n]=='谷氨酰转肽酶（GGT）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        print(i)
                    if len(re.findall('尿素',data['字段'][i]))>0  and data['字段'][n]=='尿素（UREA）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                    if len(re.findall('尿素',data['字段'][i]))>0  and data['字段'][n]=='尿素氮（BUN）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        print(i)
                    if len(re.findall('肌酐',data['字段'][i]))>0  and data['字段'][n]=='血肌酐（CREA）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        print(i)
                    if len(re.findall('蛋白',data['字段'][i]))>0  and data['字段'][i] !='蛋白尿' and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if len(re.findall('白蛋白',data['字段'][i]))>0  and data['字段'][n]=='白蛋白（ALB）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] :
                            LB2[i]=data['字段'][n]
                            LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                            print(i)
                        elif data['字段'][n]=='总蛋白（TP）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB2[i]=data['字段'][n]
                            LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                            print(i)
                    if len(re.findall('总胆红素',data['字段'][i]))>0  and data['字段'][n]=='总胆红素（TBIL）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n] 
                        print(i)
                    if len(re.findall('直接胆红素',data['字段'][i]))>0  and data['字段'][n]=='直接胆红素（DBIL）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        print(i)
                    if len(re.findall('肌酸激酶',data['字段'][i]))>0  and data['字段'][n]=='肌酸激酶（CK）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        print(i)
                    if len(re.findall('血糖',data['字段'][i]))>0  and data['字段'][n]=='空腹血糖（Glu）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        print(i)
                            
                    if len(re.findall('钾',data['字段'][i]))>0  and data['字段'][n]=='钾（K）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        print(i)
                    if len(re.findall('钠',data['字段'][i]))>0  and data['字段'][n]=='钠（Na）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        print(i)
                    if len(re.findall('氯',data['字段'][i]))>0  and data['字段'][n]=='氯（Cl）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        print(i)
                    if len(re.findall('镁',data['字段'][i]))>0  and data['字段'][n]=='镁（Mg）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        print(i)
                    if len(re.findall('钙',data['字段'][i]))>0  and data['字段'][n]=='钙（Ca）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        print(i)
                    if len(re.findall('磷',data['字段'][i]))>0  and data['字段'][n]=='磷（P）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        print(i)
    data['开始日期对应的LB/LB对应的AE']=LB
    data['LB检测日期/AE开始日期']=LBdate
    data['LB临床意义判定/AENO']=LBCS
    data['结束日期对应的LB']=LB2
    data['LB检测日期']=LBdate2
    data['LB临床意义判定']=LBCS2
    data.to_excel(writer,sheetname,index=False)
    
#尿常规---------------------------------------------------------------------------------------------------------------
def AELAB2(path,writer,sheetname):
    LB=[]
    LBdate=[]
    LBCS=[]
    LB2=[]
    LBdate2=[]
    LBCS2=[]
    data=pd.read_excel(path,sheetname)
    for i in range(len(data)):
        LB.append('NA')
        LBdate.append('NA')
        LBCS.append('NA')
        LB2.append('NA')
        LBdate2.append('NA')
        LBCS2.append('NA')
    for i in range(len(data)):
        if data['表单'][i]=='不良事件' and data['字段'][i]==data['字段'][i]:
            for n in range(len(data)):
                if  data['表单'][n]=='尿常规' and data['受试者编号'][i]==data['受试者编号'][n]:
                    if len(re.findall('蛋白尿',data['字段'][i]))>0  and data['字段'][n]=='尿蛋白（U-PRO）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]='尿蛋白（U-PRO）'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
                    if len(re.findall('尿糖',data['字段'][i]))>0  and data['字段'][n]=='尿糖（U-GLU）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]='尿糖（U-GLU）'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
                    if len(re.findall('尿白细胞',data['字段'][i]))>0  and data['字段'][n]=='尿白细胞（U-WBC）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]='血红蛋白浓度（HGB）'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
                    if len(re.findall('尿红细胞',data['字段'][i]))>0  and data['字段'][n]=='尿红细胞（U-RBC）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]='尿红细胞（U-RBC）'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
#新增AE结束日期匹配对应的检查------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    if len(re.findall('蛋白尿',data['字段'][i]))>0  and data['字段'][n]=='尿蛋白（U-PRO）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        print(i)
                    if len(re.findall('尿糖',data['字段'][i]))>0  and data['字段'][n]=='尿糖（U-GLU）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        print(i)
                    if len(re.findall('尿白细胞',data['字段'][i]))>0  and data['字段'][n]=='尿白细胞（U-WBC）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n] 
                        print(i)
                    if len(re.findall('尿红细胞',data['字段'][i]))>0  and data['字段'][n]=='尿红细胞（U-RBC）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]=data['字段'][n]
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        print(i)
    data['开始日期对应的LB/LB对应的AE']=LB
    data['LB检测日期/AE开始日期']=LBdate
    data['LB临床意义判定/AENO']=LBCS
    data['结束日期对应的LB']=LB2
    data['LB检测日期']=LBdate2
    data['LB临床意义判定']=LBCS2
    print(data)
    data.to_excel(writer,sheetname,index=False)   
#凝血功能---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
def AELAB3(path,writer,sheetname):
    LB=[]
    LBdate=[]
    LBCS=[]
    LB2=[]
    LBdate2=[]
    LBCS2=[]
    data=pd.read_excel(path,sheetname)
    for i in range(len(data)):
        LB.append('NA')
        LBdate.append('NA')
        LBCS.append('NA')
        LB2.append('NA')
        LBdate2.append('NA')
        LBCS2.append('NA')
    for i in range(len(data)):
        if data['表单'][i]=='不良事件' and data['字段'][i]==data['字段'][i]:
            for n in range(len(data)):
                if data['表单'][n]=='凝血功能' and data['受试者编号'][i]==data['受试者编号'][n]:
                    if len(re.findall('凝血酶原时间',data['字段'][i]))>0  and data['字段'][n]=='凝血酶原时间（PT）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]='凝血酶原时间（PT）'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
                    if len(re.findall('凝血活酶时间（APTT）',data['字段'][i]))>0  and data['字段'][n]=='活化部分凝血活酶时间（APTT）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]='活化部分凝血活酶时间（APTT）'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
                    if len(re.findall('D-二聚体',data['字段'][i]))>0  and data['字段'][n]=='D-二聚体'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]='D-二聚体'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
                    if len(re.findall('纤维蛋白原',data['字段'][i]))>0  and data['字段'][n]=='FIB'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]='FIB'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
                    if len(re.findall('国际化标准比值',data['字段'][i]))>0  and data['字段'][n]=='国际化标准比值INR'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]='国际化标准比值INR'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
                        
                    if len(re.findall('凝血酶原时间',data['字段'][i]))>0  and data['字段'][n]=='凝血酶原时间（PT）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]='凝血酶原时间（PT）'
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                       
                        print(i)
                    if len(re.findall('凝血活酶时间（APTT）',data['字段'][i]))>0  and data['字段'][n]=='活化部分凝血活酶时间（APTT）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]='活化部分凝血活酶时间（APTT）'
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                            
                        print(i)
                    if len(re.findall('D-二聚体',data['字段'][i]))>0  and data['字段'][n]=='D-二聚体' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]='D-二聚体'
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                            
                        print(i)
                    if len(re.findall('纤维蛋白原',data['字段'][i]))>0  and data['字段'][n]=='FIB' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]='FIB'
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                            
                        print(i)
                    if len(re.findall('国际化标准比值',data['字段'][i]))>0  and data['字段'][n]=='国际化标准比值INR' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]='国际化标准比值INR'
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                           
                        print(i)
                    if len(re.findall('淋巴细胞',data['字段'][i]))>0  and data['字段'][n]=='淋巴细胞绝对值（LYMPH#）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]='淋巴细胞绝对值（LYMPH#）'
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                            
                        print(i)
                        
    data['开始日期对应的LB/LB对应的AE']=LB
    data['LB检测日期/AE开始日期']=LBdate
    data['LB临床意义判定/AENO']=LBCS
    data['结束日期对应的LB']=LB2
    data['LB检测日期']=LBdate2
    data['LB临床意义判定']=LBCS2
    print(data)
    data.to_excel(writer,sheetname,index=False)    
#----------------------------------------------------------------------------------
def AELAB4(path,writer,sheetname):
    LB=[]
    LBdate=[]
    LBCS=[]
    LB2=[]
    LBdate2=[]
    LBCS2=[]
    data=pd.read_excel(path,sheetname)
    for i in range(len(data)):
        LB.append('NA')
        LBdate.append('NA')
        LBCS.append('NA')
        LB2.append('NA')
        LBdate2.append('NA')
        LBCS2.append('NA')
    for i in range(len(data)):
        if data['表单'][i]=='不良事件' and data['字段'][i]==data['字段'][i]:
            for n in range(len(data)):
                if data['表单'][n]=='甲状腺功能' and data['受试者编号'][i]==data['受试者编号'][n]:
                    if len(re.findall('促甲状腺激素',data['字段'][i]))>0  and data['字段'][n]=='血清促甲状腺激素（TSH）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]='血清促甲状腺激素（TSH）'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
                    if len(re.findall('T3',data['字段'][i]))>0  and data['字段'][n]=='血清游离T3（FT3）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]='血清游离T3（FT3）'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
                    if len(re.findall('T4',data['字段'][i]))>0  and data['字段'][n]=='血清游离T4（FT4）'  and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        if data['AE开始日期/LAB采样日期'][i]==data['AE开始日期/LAB采样日期'][n]:
                            LB[i]='血清游离T4（FT4）'
                            LBdate[i]=data['AE开始日期/LAB采样日期'][n]
                            LBCS[i]=data['AE结束日期/LAB临床意义判定'][n]
                        if data['AE开始日期/LAB采样日期'][n]>=data['AE开始日期/LAB采样日期'][i] and data['AE开始日期/LAB采样日期'][n] < data['AE结束日期/LAB临床意义判定'][i]:
                            LB[n]=data['字段'][i]
                            LBdate[n]=data['AE开始日期/LAB采样日期'][i]
                            LBCS[n]=data['AELogline#/CommentLB1'][i]
                            print(i)
   
                    if len(re.findall('促甲状腺激素',data['字段'][i]))>0  and data['字段'][n]=='血清促甲状腺激素（TSH）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]='血清促甲状腺激素（TSH）'
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        
                        print(i)
                    if len(re.findall('T3',data['字段'][i]))>0  and data['字段'][n]=='血清游离T3（FT3）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]='血清游离T3（FT3）'
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        
                        print(i)
                    if len(re.findall('T4',data['字段'][i]))>0  and data['字段'][n]=='血清游离T4（FT4）' and data['AE结束日期/LAB临床意义判定'][i]==data['AE开始日期/LAB采样日期'][n] and data['AE是否持续/LB检查结果'][n] !='ND' and data['AE是否持续/LB检查结果'][n] !='NA':
                        LB2[i]='血清游离T4（FT4）'
                        LBdate2[i]=data['AE开始日期/LAB采样日期'][n]
                        LBCS2[i]=data['AE结束日期/LAB临床意义判定'][n]
                        
                        print(i)
                
    data['开始日期对应的LB/LB对应的AE']=LB
    data['LB检测日期/AE开始日期']=LBdate
    data['LB临床意义判定/AENO']=LBCS    
    data['结束日期对应LB']=LB2
    data['LB检测日期']=LBdate2
    data['LB临床意义判定']=LBCS2
    print(data)
    data.to_excel(writer,sheetname,index=False)   




path=r'{}\SCT-I10A-B301-LABAE.xlsx'.format(path2)

sheetname='血常规'
AELAB0(path,writer,sheetname)
sheetname='血生化'
AELAB1(path,writer,sheetname)
sheetname='尿常规'
AELAB2(path,writer,sheetname)
sheetname='凝血功能'
AELAB3(path,writer,sheetname)
sheetname='甲状腺功能'
AELAB4(path,writer,sheetname)

writer.save()

#-----------------------------------------------------------------------------------------------------

#-----------------------------------------------------------------------------------------------------
def reset_col(filename):
    wb=load_workbook(filename)
    for sheet in wb.sheetnames:
        ws=wb[sheet]
        df=pd.read_excel(filename,sheet)
        
        for col in df.columns:
            index=list(df.columns).index(col)
            letter=get_column_letter(index+1)
            collen=len(str(col).encode())
            print(collen)
            ws.column_dimensions[letter].width=collen+4
    wb.save(filename)
            
filename=r'{}\SCT-I10A-B301-LABAE-2.xlsx'.format(path2)
            
reset_col(filename)
#------------------------------------------------------------------------------------------------------------


#------------------------------------------------------------------------------------------------------------
b=datetime.now()
print('运行时间：{}分'.format(((b-a).seconds)/60))
季风吹向大海/
关注
0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
python用于临床数据管理_LB-AE核查

import tkinter as tkfrom tkinter.filedialog import askopenfilenameimport re#设置GUI-----------------------------------------------------------------------path1='NA' #datalisting路径path2='NA' #输出路径def hit_b1(): global path1 path1=askopenfilenam
复制链接

扫一扫
专栏目录