使用python计算O2,CO2气体挥发，酒精燃烧，及其回收率

使用python计算气体挥发，酒精燃烧，及其回收率

主要涉及问题：

• 时间的处理，数值计算
• pandas加速（向量化，以及pd.eval()的使用等等）
• 重复索引问题解决
• 使用pyinstaller打包问题

# -*- coding: utf-8 -*-
"""
Created on Mon Sep  2 09:26:28 2019

@author: Administrator
"""
import pandas as pd
#import numba 可以进行向量化，加速处理

#o_path = input('请输入气体文件路径：') + '.csv'
#w_path = input('请输入酒精文件路径:') + '.csv'
o_path = r'D:\python_code\ruijin_metaboliaze\Alochol_Vlookup\20190902 Alocholtest\C1\0904\20190902 Alochol test Chamber2 - long.csv'
w_path = r'D:\python_code\ruijin_metaboliaze\Alochol_Vlookup\20190902 Alocholtest\C1\0904\20190902 Chamber2 AT.csv'
dfA = pd.read_csv(o_path)
dfA['F_TIME'] = '20'
dfA.eval('TIME = F_TIME + TIME', inplace=True)

dfB = pd.read_csv(w_path,header=None)
dfB.columns = ['ST','W','U','t','d','i']
dfB['U'] = ' '
dfB.eval('TIME = d + U + t', inplace=True) #避免使用循环，几万行很慢
#dfB.eval('TIME2 = d + @new + t', inplace=True) @new为局部变量

'''for index,row in dfA.iterrows():
    #df.iloc[index,0] = time.strftime('%Y/%m/%d ddd',df.iloc[index,0])
    dfA.loc[index,'TIME'] = '20' + str(dfA.loc[index,'TIME'])'''
    
dfA.index = pd.to_datetime(dfA['TIME'])
dfA = dfB[~dfA.index.duplicated()] #数据不干净，防止有重复索引引发异常
dfB.index = pd.to_datetime(dfB['TIME'])
dfB = dfB[~dfB.index.duplicated()] #数据不干净，防止有重复索引引发异常

s1_time = dfA['TIME'][0]
all_time_length = dfA.index.max() - dfA.index.min()
s2_time = pd.to_datetime(s1_time) + all_time_length
prng2_O2 = pd.date_range(s1_time, s2_time, freq='60S')
prng2 = pd.date_range(s1_time, s2_time, freq='3600S')
    
#使用循环合并dfB时间和日期
#for index,row in dfB.iterrows():
    #df.iloc[index,0] = time.strftime('%Y/%m/%d ddd',df.iloc[index,0])
    #dfB.iloc[index,5] = str(dfB.iloc[index,4]) + ' ' + str(dfB.iloc[index,3])
    
#dfB[[0]] = dfB.apply(lambda row:str(row[[3]]) + str(row[[4]]),axis=1)
#@numba.vectorize 向量化加速
def Time_list(s1_time,b):
    start_time = pd.to_datetime(s1_time)
    length_time = int(b)
    end_time = start_time + pd.Timedelta('%s hours'%length_time)
    prng = pd.date_range(start_time, end_time, freq='3600S')
    prng_O2 = pd.date_range(start_time, end_time, freq='60S')
    return prng,prng_O2

print(prng2) #输出整点时间以供选择
message1 = "请输入开始时间："
time_start = input(message1)
message2 = "请输入需要计算时长(h)："
time_length = input(message2)

prng,prng_O2 = Time_list(time_start,time_length)


df_O2_all = pd.DataFrame(dfA,index=prng2_O2)
#df_O2_all.between_time("7:00","19:00") 规范时间范围
df_O2_select = pd.DataFrame(dfA,index=prng_O2)

df_weight_all = pd.DataFrame(dfB,index=prng2)
df_weight_select = pd.DataFrame(dfB,index=prng)

df_O2_all.drop(['TIME','F_TIME'],axis=1,inplace=True)
df_O2_all['TIME'] = df_O2_all.index
df_sumO2_all = df_O2_all.resample('3600S', on='TIME').sum()
df_sumO2_all.loc[df_sumO2_all.index.max(),:] = df_sumO2_all.sum()

df_O2_select.drop(['TIME','F_TIME'],axis=1,inplace=True)
df_O2_select['TIME'] = df_O2_select.index
df_sumO2_select = df_O2_select.resample('3600S', on='TIME').sum()
df_sumO2_select.loc[df_sumO2_select.index.max(),:] = df_sumO2_select.sum()

df_weight_all['TIME'] = df_weight_all.index
#df_weight_all = df_weight_all.reset_index(drop=True)
df_weight_all['diff_1'] = df_weight_all[['W']].apply(lambda i:i.diff(-1)) #计算前一项减去后一项的值
df_weight_all.loc[df_weight_all.index.max(),'diff_1'] = df_weight_all['diff_1'].sum() #最后一项为所有的和
df_weight_all['diff_1']
#df_weight_all['shift(1)'] = df_weight_all[['W']].apply(lambda i:i.shift(1))
#df_weight_all['diff_1'] = df_weight_all[['W']] - df_weight_all['shift(1)'] 
#action['diff_2'] = action.groupby('userid')['actionTime'].apply(lambda i:i.diff(1))
df_weight_select['diff_1'] = df_weight_select[['W']].apply(lambda i:i.diff(-1))
df_weight_select.loc[df_weight_select.index.max(),'diff_1'] = df_weight_select['diff_1'].sum()

df_all = pd.merge(df_sumO2_all,df_weight_all,on=df_sumO2_all.index,how='left')
#merge_df = pd.merge(merge_df,df_bodyTemperature, on='time',how='left')
df_s = df_all[['TIME','VO2(L/min)','VCO2(L/min)','diff_1']]
#[['time','shijiO2','shijiCO2','losswiight']]
df_s[['lilun_O2']]=(df_s[['diff_1']]*0.995)/46.07*3*22.4 
df_s[['lilun_CO2']]=(df_s[['diff_1']]*0.995)/46.07*2*22.4
df_s.loc[:,'RQ'] = 0.6667
df_s.loc[:,'shiji_RQ'] = df_s['VCO2(L/min)']/df_s['VO2(L/min)']
df_s.loc[:,'recall_O2'] = df_s['VO2(L/min)']/df_s['lilun_O2']*100
df_s.loc[:,'recall_CO2'] = df_s['VCO2(L/min)']/df_s['lilun_CO2']*100
df_s.loc[:,'recall_RQ'] = df_s['RQ']/df_s['shiji_RQ']*100

df_select = pd.merge(df_sumO2_select,df_weight_select,on=df_sumO2_select.index,how='left')
df_ss = df_select[['TIME','VO2(L/min)','VCO2(L/min)','diff_1']]
df_ss[['lilun_O2']]=(df_ss[['diff_1']]*0.995)/46.07*3*22.4
df_ss[['lilun_CO2']]=(df_ss[['diff_1']]*0.995)/46.07*2*22.4
df_ss.loc[:,'RQ'] = 0.6667
df_ss.loc[:,'shiji_RQ'] = df_ss['VCO2(L/min)']/df_ss['VO2(L/min)']
df_ss.loc[:,'recall_O2'] = df_ss['VO2(L/min)']/df_ss['lilun_O2']*100
df_ss.loc[:,'recall_CO2'] = df_ss['VCO2(L/min)']/df_ss['lilun_CO2']*100
df_ss.loc[:,'recall_RQ'] = df_ss['RQ']/df_ss['shiji_RQ']*100

df_s.rename(columns={'VO2(L/min)':'shiji_O2', 'VCO2(L/min)':'shiji_CO2', 'diff_1':'LossWight'}, inplace = True) #对部分列进行重命名
df_ss.rename(columns={'VO2(L/min)':'shiji_O2', 'VCO2(L/min)':'shiji_CO2', 'diff_1':'LossWight'}, inplace = True)



writer = pd.ExcelWriter('test2.xlsx')  #将两个数据框写入同一个Excel
df_s.to_excel(writer,sheet_name = 'all')
df_ss.to_excel(writer,sheet_name = 'select')
writer.save()

然后简单的打包了一下：

pyinstaller -F -i Excel.ico -n Alcohol lookup.py -p C:\ProgramData\Anaconda3\envs\ECG\Lib\site-packages

-p 是导入包的路径，可以避免出现很多BUG

打包成功：

运行：

需要测试文件的话请留言联系。