N175_标记异常值_自动汇聚某单位的住房公积金 有补发有增减 如何批量找出来

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
plt.rcParams['font.sans-serif'] = ['SimHei']    #定义使其正常显示中文字体黑体
plt.rcParams['axes.unicode_minus'] = False      #用来正常显示表示负号 
data = pd.read_excel(r'.\N175_标记异常值_数据源.xlsx')
import seaborn as sns
# 使用seaborn获取颜色
cm = sns.light_palette("green", as_cmap=True)
# 色阶实现
data.head(100).style.background_gradient(cmap=cm,subset=["发生额"])
# data

limit_output extension: Maximum message size of 10000 exceeded with 132577 characters

df = data[["laiyuan","日期","客户姓名","发生额"]]
df.head(100).style.background_gradient(cmap=cm,subset=["发生额"])  #主要是靠这些列

limit_output extension: Maximum message size of 10000 exceeded with 43779 characters

# 透视表 交叉统计
tongji01=pd.pivot_table(data, index=['laiyuan','日期'], columns='客户姓名', values='发生额',margins=False, aggfunc=[np.sum])
# 最后，要扁平化MultiIndex列，我们可以只连接元组中的值：
tongji01.columns = ['_'.join(col).replace("sum_","") for col in tongji01.columns.values]
tongji01 = tongji01.reset_index()
tongji01.head(10)

10 rows × 84 columns

# 按列找异常值，并用语言描述出来
import pandas as pd
cun = tongji01.copy()  #将数据块复制，后面发现的异常值进行覆盖
hangzuobiao = tongji01.laiyuan.to_list() # 将来源列转列表
suo = tongji01   #.iloc[:,1:4] #   为了缩小范围，提高实验速度
print(suo.shape)
lieming = suo.columns.to_list() #将列标题转列表
for index,col in enumerate(lieming): # 遍历列名，lie_max = suo[col].value_counts().index[:1].values # 关键点，找到该列频率最高的值gailie_max = lie_max[0] # 取当前值，上面计算出来的是列表for hang in range(suo.shape[0]): # 按行总数遍历 ，if suo[col].dtype in ["int64", "float64"]: #判断是否都是数字danqian = suo.loc[hang,col] # 取出当前值if pd.isnull(danqian) == True or danqian == gailie_max: # 为空跳过，相等跳过continue
#             print("当前值："+str(danqian))
#             print("当列出现次数最多的："+str(gailie_max))yichang = (gailie_max-danqian)/gailie_max
#             print(yichang)if abs(yichang)>0.6: # 差额相除取绝对值，看是否超过6成zhi = "异常"+str(hangzuobiao[hang])[:10]+"_"+str(col)+"_："+str(suo.loc[hang,col])cun.iloc[hang,index] = zhi
cun.to_excel("N175_标记异常值_结果.xlsx")

(107, 84)

from styleframe import StyleFrame
import openpyxl
#可以自我覆盖，但是这样有风险，为了列宽自适应，筛选，冻结窗口
file_path = "N175_标记异常值_结果.xlsx"
df=pd.read_excel(file_path)
col = df.columns.values.tolist()
excel_writer = StyleFrame.ExcelWriter(file_path)
sf = StyleFrame(df)
sf.to_excel(excel_writer=excel_writer, best_fit=col,columns_and_rows_to_freeze='a2', row_to_add_filters=0,
)
excel_writer.save()
# 将句柄关闭，否则是只读格式，打不开
excel_writer.handles = None  #这句代码有用，很久才找到
print("well done")

well done

原始数据

处理后的数据