pd.read_excel()练习

今天药忘吃喽~ 2022-01-27 18:59 214阅读 0赞

# -*- coding: utf-8 -*-
    import pandas as pd
    import numpy as np
    
    df11=pd.read_excel("C:/Users/Administrator/Desktop/20190527/表1.xlsx")
    df12=pd.read_excel("C:/Users/Administrator/Desktop/20190527/表2.xlsx")
    df13=pd.read_excel("C:/Users/Administrator/Desktop/20190527/表3.xlsx")
    df11['LipidIon.1']=df11['LipidIon.1'].apply(lambda x : x.strip())
    df12['LipidIon.1']=df12['LipidIon.1'].apply(lambda x : x.strip())
    df13['LipidIon.1']=df13['LipidIon.1'].apply(lambda x : x.strip())
    df11['LipidIon']=df11['LipidIon'].apply(lambda x : x.strip())
    df12['LipidIon']=df12['LipidIon'].apply(lambda x : x.strip())
    df13['LipidIon']=df13['LipidIon'].apply(lambda x : x.strip())
    
    df11['category']=df11['LipidIon'].apply(lambda x : x.split("+")[-1])
    df12['category']=df12['LipidIon'].apply(lambda x : x.split("+")[-1])
    df13['category']=df13['LipidIon'].apply(lambda x : x.split("+")[-1])
    str(df11['LipidIon.1'])
    
    df1=df11[['LipidIon','LipidIon.1','category','Area']]
    df2=df12[['LipidIon','LipidIon.1','category','Area']]
    df3=df13[['LipidIon','LipidIon.1','category','Area']]
    df=pd.concat([df1,df2,df3],axis=0)
    df=df.sort_values(by=['LipidIon'],inplace=False, kind='quicksort', na_position='last') 
    df=df.reset_index(drop=True)    #重置索引
    
    #1.功能1:过滤信息并汇总表4
    count=0
    dfdf=df.groupby(['LipidIon'], as_index = False)
    data=pd.DataFrame(columns=['LipidIon','category','LipidIon.1','Area_i','Area_sum'])
    
    #1.先对数据做分群处理
    class_name = list(dfdf[['LipidIon','category']])
    
    #for line in class_name[62:63]:
    for line in class_name:
        #对每一个分组内部,统计三类子分组中的最大值: max(max(每一个分组))
        group1_name=line[0]
        group1_data=line[1].groupby(['category'], as_index = False)
        class_name1 = list(group1_data[['LipidIon.1','category']])
        #统计每个分组中的最大值,并计算总的峰值面积
        #筛选出来最大值所对应的行
        max1_row=class_name1[0][1][class_name1[0][1].Area==class_name1[0][1]['Area'].max()]
        #计算最大值所在那个分组的所有峰值面积和
        #max_key=max1_row['category']
        area0=0
        class_name1[0][1].index=range(len(class_name1[0][1]))
        area0=class_name1[0][1]['Area'].sum()
        #重新组织数据存表
        #type(max1_row['LipidIon'])    //Series
        data.loc[count,'LipidIon']=max1_row['LipidIon'].values[0]
        data.loc[count,'LipidIon.1']= max1_row['LipidIon.1'].values[0]
        data.loc[count,'category']= max1_row['category'].values[0] 
        data.loc[count,'Area_i']= max1_row['Area'].values[0] 
        data.loc[count,'Area_sum']=area0
        count+=1
    data['Area_i']= data['Area_i'].apply(lambda x:np.float(x))  
    data['Area_sum']= data['Area_sum'].apply(lambda x:np.float(x))  
    data=data.sort_values(by=['LipidIon'],inplace=False, kind='quicksort', na_position='last') 
    data=data.reset_index(drop=True)    #重置索引
    data.to_excel("C:/Users/Administrator/Desktop/20190527/表4_去重.xlsx")
    
    
    #读入表4
    biao4=pd.read_excel("C:/Users/Administrator/Desktop/20190527/表4_去重.xlsx")
    biao4_name=sorted(set(biao4['LipidIon.1']))
    
    #2.功能2:从表4中删除掉只出现一次的行
    freq_dict=dict()
    for i in range(len(biao4_name)):
        freq_dict[biao4_name[i]]=0
    
    
    for i in range(data.shape[0]):
        freq_dict[data.loc[i,'LipidIon.1']]+=1
    #筛选出来大于2的行    
    lengt2=[]
    for key,value in freq_dict.items():
        if value>1:
            lengt2.append(key)
    dfdf33=pd.DataFrame(lengt2,columns=['LipidIon.1'])
    ss_df=pd.merge(dfdf33,data)   #做一次数据关联,关联出来出现行数大于2的行。
    ss_df.to_excel("C:/Users/Administrator/Desktop/20190527/表4.xlsx")
    
    
    
    #读入总表
    sum_df=pd.read_excel("C:/Users/Administrator/Desktop/20190527/总表.xlsx")
    sum_df.columns.tolist()
    sum_name=sorted(set(sum_df['LipidMolec']))
    sum_df=sum_df[['LipidMolec','MainMScore[c]','NMArea[c]']]
    sum_df.columns=['LipidIon.1','MainMScore[c]','NMArea[c]']
    
    #读入表4
    biao4=pd.read_excel("C:/Users/Administrator/Desktop/20190527/表4.xlsx")
    biao4_name=sorted(set(biao4['LipidIon.1']))
    
    #求交集=>表5
    interSet=sorted(set(sum_name) & set(biao4_name))
    biao41=biao4[biao4['LipidIon.1'].apply(lambda x : x in interSet )]
    biao41.to_excel("C:/Users/Administrator/Desktop/20190527/表5.xlsx")
    
    #求表4没有但总表有的=>表6
    onlySumhas=sorted(set(sum_name) - set(biao4_name))
    biao42=sum_df[sum_df['LipidIon.1'].apply(lambda x : x in onlySumhas )]
    biao42.to_excel("C:/Users/Administrator/Desktop/20190527/表6.xlsx")
    
    #表4有但总表没有的=>表7
    onlybiao4has=sorted(set(biao4_name) - set(sum_name))
    biao43=biao4[biao4['LipidIon.1'].apply(lambda x : x in onlybiao4has )]
    biao43.to_excel("C:/Users/Administrator/Desktop/20190527/表7.xlsx")