由于经历一些调试，所以代码结构不一定最优，适合数据处理的新手，大神欢迎提出改进
数据来源：
http://www.
设p为均值，s为标准差
由表三可知，元素背景值在[p-s, p+s]，所以应用到表一，将不符合的数据剔除
上代码
import numpy as npimport pandas as pd
path = "E:/Model_building/A/cumcmA附件_数据.xls"s1 = pd.read_excel(path, sheet_name="附件1")s2 = pd.read_excel(path, sheet_name="附件2")s3 = pd.read_excel(path, sheet_name="附件3") # 读入一个文件中的三个表s1 = s1.values# 转化为数值形式s2 = s2.valuess3 = s3.valuess1 = pd.DataFrame(s1) # 转化为dataframe形式s2 = pd.DataFrame(s2)s3 = pd.DataFrame(s3)print(s1)
s1 = s1.iloc[2:, 0:5]s2 = s2.iloc[2:, 0:9]s3 = s3.iloc[2:, :]# 去掉表头等不需要的部分，如表一的右边print(s1,'\n')print(s2,'\n')print(s3,'\n')
l = []for c in range(1,9):# 将各元素标准差放到列表a = s2.iloc[: ,c].std()l.append( a )print(a,'\n')p = []for c in range(1,9): # 各元素均值放到列表a = s2.iloc[:, c].mean()p.append( a )for c in range(0,8): themin = p[c] - 2*l[c]themax = p[c] + 2*l[c]print(themin, ' ', themax)s2 = s2[(s2.iloc[:, c+1] >= themin )& (s2.iloc[:, c+1] <= themax ) ]# 选出符合的行print(s2)print('\n',s2.shape[0],'\n')
ID = pd.DataFrame(s2.iloc[:, 0]) # 表二0列单独成表print(ID)s1 = pd.merge(s1, ID, how="inner", right_on=0, left_on=0) # 找出表一0列在表二0列的行，merge，join，constack进行拼接print(s1)
path = "E:/Model_building/A/" # 保存s1.to_excel(path+"sheet1.xlsx")s2.to_excel(path+"sheet2.xlsx")s3.to_excel(path+"sheet3.xlsx")
