Pandas操作Excel

import osimport pandas as pdimport numpy as npfrom scipy import statsfrom scipy.stats import linregressimport matplotlib.pyplot as pltimport seaborn as snsdf=pd.read_excel('e3.xlsx',sheet_name='Sheet1',nrows=3605)print(df.dtypes)'''批量读取工作页合并成一个大的工作框sheet_name=['Sheet'+str(i) for i in range(1,3)]data_all=pd.Dataframe()print(sheet_name)for i in sheet_name: data=pd.read_excel('e1.xlsx',sheet_name=i) data_all=pd.concat([data_all,data],axis=0,ignore_index=True)'''# # 保存# df.to_excel()# 数据筛选# print(df[['sex','age']][:5])# loc和iloc的用法# miss_rate=pd.Dataframe(df.apply(lambda x:sum(x.isnull()/len(x))))# miss_rate.columns=['缺失率']# miss_rate1=miss_rate[miss_rate['缺失率']>0]['缺失率'].apply(lambda x:format(x,'.3%'))# print(miss_rate1) # 缺失率# data1=df[df['CYC'].isnull()].iloc[:10]# print(data1)# index_num=df[df['CYC']>0.8,df['sex']==1].index# print(index_num)# data=df.loc[df['sex']==1]# print(data)'''# 条件查询df2=df[(df['sex']==1)&(df['age']>60)&(df['CREAT']>0.7&(df['CYC']<=0.8))]print(df2)print(df['age'].between(60,70,inclusive=True)) # 判断年龄在60-70岁之间的并输出df['age'].isin(['60']) # 判断年龄是否有60岁df['CREAT'].str.contains('N') # 判断是否包含'N'字符'''# 增删改查'''# df['eGFR']=df['sex']*df['age'] # 增加操作df['Scr']='肌酐'print(df.columns) # 输出列# df.drop(['Scr','eGFR'],axis=1,inplace=True) # axis=1为按列删除，axis=0为按行删除del df['Scr']mid=144*pow(((df['CREAT'])/0.7),-0.329)*pow(0.993,df['age'])df.insert(5,'eGFR',mid) #参数说明：5是第5列(从第0列开始),列名为eGFR,数据为mid''''''# 筛选def sex_s(a): return a==0def level_a(s): return s>0.7dfs=df.loc[df['sex'].apply(lambda x:x==0)].loc[df['CREAT'].apply(lambda x:x>0.7)] # 使用lambda表达式df['eGFR']=144*pow(((df['CREAT'])/0.7),-0.329)*pow(0.993,df['age'])x=df['mGFR']y=df['eGFR']plt.scatter(x,y,marker='.',edgecolors='red')plt.show()'''# df1=df.loc[df['sex'].apply(lambda x:x==0)].loc[df['CREAT'].apply(lambda x:x>0.7)]# df1['eGFR']=144*pow(((df1['CREAT'])/0.7),-0.329)*pow(0.993,df1['age'])# print(df1)# df2=df.loc[df['sex'].apply(lambda x:x==1)].loc[df['CREAT'].apply(lambda x:x<=0.7)]# df2['eGFR']=144*pow(((df2['CREAT'])/0.7),-1.209)*pow(0.993,df2['age'])# df3=pd.concat([df1,df2],ignore_index=True)# print(df3.isnull())