python练习18_工作实景_数据预处理自动化办公

一、业务场景

我的工作中每天需要审核两种来自于不同数据源的不同甲方公司的数据，审核内容包括数据格式和数据准确性。

两个数据源分别是邮件和系统，数据存储使用excel。

传统方法是记住规则之后用肉眼去看，或者使用excel的一些函数进行匹配。但那样太慢了，于是我使用了python代码。

二、实际需求

1.校验A类型公司回传的a文件的某三列数据是否有重复。

2.将A类型公司回传的a文件的某三列数据与b文件进行匹配，数据需要一一对应。

3.判断A类型公司回传数据中的车牌号是否合法（可能含有临时车牌号，无法录入系统）。

4.校验B类型公司回传的a文件的某三列数据是否有重复。

5.计算两家公司符合某标签的数据量。

6.判断两家公司回传数据中的日期时间期限是否正确。

7.把两个类型的公司的所有回传数据格式修改为历史全量数据格式（A类型公司需要将两个文件进行匹配才能得到全部必要列）。

8.将两个类型的公司的回传数据与历史全量数据进行匹配，查看特殊列或组合列是否有重复。

9.新数据并入全量数据，生成新的全量数据文件。

三、代码实现

1.导包

import pandas as pd
from datetime import date, timedelta, time, datetime
from collections import defaultdict
import os
import shutil
import re

2.读数

读取历史数据

为了方便运行代码结束后覆盖，还能找到原数据，给文件名加一个日期后缀，并用date.today()函数获取每日动态文件名。

读取结束后，显示数据行数、前三行，方便观测数据结构与校验数据准确性。

path_date = str(date.today()).replace('-','')[4:]
path_date_next = str(date.today() + timedelta(days=1)).replace('-','')[4:]
data_all = pd.read_excel('yourpath/历史数据{0}.xlsx'.format(path_date))
print(len(data_all))
data_all.head(3)

读取A类型公司数据

预设变量

目的和上面一样，方便自动化读取，提前把各家公司、各个地区数据的差异写好，后面直接因用变量就能实现代码自动工作。

这里需要运行时手动输入城市名；

每个城市的此类型公司回传数据中，会有不同数量的空行，需要在读取前跳过，这里预设；

然后再按照商议好的数据对接规则，设置文件名称的时间格式。

# 城市
city_name = input('xxx：')
skip_rows = 0
if city_name in ['城市a']:
    skip_rows = 7
elif city_name in ['城市b','城市c']:
    skip_rows = 8
# 日期
time_today = str(date.today() - timedelta(days=1))
path_time = time_today.replace('-','')

使用变量

使用预设的城市变量 city_name 和时间变量 path_time 读取预定路径上的对应数据文件；

使用根据城市预设的数字变量 skip_rows 跳过不同数据文件的空行；

由于有些公司的数据在首列末行会有数据汇总信息，这些信息会使代码读取数据时格式混乱，所以加一个判断，如果存在“合计”行，删除行。

# 读取两个文档的数据
df_a = pd.read_excel('yourpath/{1}_{0}A类型公司清单a.xlsx'.format(city_name,path_time),skiprows=skip_rows)
df_b = pd.read_excel('youpath/{1}_{0}A类型公司清单b.xlsx'.format(city_name,path_time),skiprows=skip_rows)
# 删除合计行
if df_a.iloc[:,0][len(df_a)-1].find('合计') != -1:
    df_a = df_a.drop(len(df_a)-1)

3.初步校验

以下变量命名做了模糊处理，看起来可能有点不方便，逻辑是：

直接读取到的数据：df_a  df_b

使用一个标签字段筛选过滤两个dataframe后：df1  df2

在上述基础上又使用一个标签字段过滤 df_1 后：df1_a  df1_b

由于最终数据需要处理后汇总到全量数据库中堆叠，所以原则上校验过程尽可能不适用直接读数结果的 df_a 和 df_b。所以产生过滤后的 df1  df2

计数

计数过程和匹配唯一过程，由于业务要求，又进一步筛选，产生了 df1_a  df1_b。筛选过后，计数只需要使用 len() 函数读取长度即可。

# 过滤掉非“特定标签”的行
df1 = df_a[df_a['标签字段一'] == '标签1']
df2 = df_b[df_b['标签字段一'] == '标签1']
# 对“特定标签”数据计数
df1_a = df1[df1['标签字段二'] == '----']
print('{1}共有{0}条符合一类标签数据'.format(len(df1_a),city_name))
df1_b = df1[df1['标签字段二'] != '----']
print('{1}共有{0}条符合另一类标签数据'.format(len(df1_b),city_name))

判唯一

判断唯一可以根据业务场景选用 df1 或 df1_a，使用 nunique() 函数，得到的长度如果和总长度不符就不唯一（由于现在是初步校验，一般这个时候就能判断不唯一的部分是什么了，所以没有进一步把不唯一数据呈现出来，如果有这方面业务需要，可以看下面的汇总判唯一写法，那里能实现这样的需求）。

这里呈现的代码只判断了一个字段在一个文件中的唯一性，如果有复数个字段都需要如此判断，只需要复制这部分代码，修改列名即可。

# 判断唯一字段数据是否确实唯一
if df1_a['唯一字段1'].nunique() != len(df1_a):    
    print('!!!!!!!a文件中唯一字段1字段不唯一')
else:    
    print('a文件中唯一字段1字段唯一')
print('*****************************')
# ……以此类推

判车牌

车牌号规范：1个汉字1个大写字母5-6个数字或字母。

先写一个函数能够用来判断字符串是否符合车牌号规范，这里我刻意加了一个判断——如果True返回False，反之亦然。为的是下面如果判断出数据不完全正确，我只提取错误数据。

# 判断车牌号是否合法
def check_plate(s):
    # 匹配第一位汉字、第二位大写字母、5到6位数字或字母
    pattern = r'^[\u4e00-\u9fa5][A-Z][\da-zA-Z]{5,6}$'
    res = bool(re.match(pattern, s))
    if res:
        return False
    else:
        return res
if len(df_a[df_a['车牌号'].apply(check_plate)]) == 0:
    print('车牌号全部合法')
else:
    print('车牌号不合法：')
    print(df_a[df_a['车牌号'].apply(check_plate)][['索引列','车牌号']])
print('*****************************')

两文件匹配

b文件中有某个含重字段，a文件中有不重复的这个字段，我需要判断两个文件中的该字段是否完全匹配，如果不匹配，需要找出是哪个文件中有多出来的数据。

使用经典的 merge() 函数，选择参数 how='outer'，并设置 indicator=Ture ，显示 '_merge' 列匹配结果。

如果有复数个字段需要匹配校验，同样可以复制这段代码。

# 对两个文件的某字段进行匹配对比，查看是否有不相匹配的数据
match1 = df1.merge(df2, on='需匹配字段', how='outer', indicator=True)
not_match1 = match1[match1['_merge'] != 'both']
if not_match1.empty:    
    print('两个文件中需匹配字段完全匹配')
else:    
    print('以下为不匹配数据：')    
    print(not_match1[['需匹配字段', '_merge']])
print('*****************************')
# ……以此类推

判城市

根据车牌号的设计规则，利用车牌号的第一个字母判断车辆属于那座城市（字母对应城市百度就能查到），并以城市分组计数，此处以贵州省为例。

# 根据车牌号第二个字符的字母判断城市并根据城市分组计数（如贵州省）
city_dict_guizhou = {'A': '贵阳', 'B': '六盘水', 'C': '遵义', 'D': '铜仁', 'E': '黔西南布依族苗族自治州','F': '毕节', 'G': '安顺', 'H': '黔东南苗族侗族自治州', 'J': '黔南布依族苗族自治州'}
city_count = defaultdict(int)
for plate_num in df1_a['车牌号']:    
    if plate_num[0] == '贵' :
        city = city_dict_guizhou[plate_num[1]]    
        city_count[city] += 1    
#         print(f'{plate_num}: {city}')    
    else:
        break
for city, count in city_count.items():    
    print(f'{city}: {count}')

4.结构转换

结构转换的目标是为了将清理好的数据整合到全量数据中，如果仅需核对数据格式，没有这种需求，可以不做这一步。

先写一个后面会用到的 字典 和 列表 ，供列转换；

先将两个数据文件进行筛选、匹配，需要直接补充的字段进行补充，在一个 dataframe 中拿出所有需要的字段。利用 rename() 函数和字典进行字段重命名，利用 reindex() 函数和列表进行字段顺序转换。

# 字段转换字典
change_columns = {'原字段1':'目标字段1','原字段2':'目标字段2'}
# 字段顺序列表
index_list = ['按目标字段顺序排列字段名']
# 只处理保单
df_aa = df_a[df_a['标签字段'] == '----']
# 字段增、并
tp1 = pd.merge(df_aa, df_b[df_b['标签字段'] == '需要的标签'][['A','B','C','D','E']],on=['A'],how='left').fillna({'B':0,'C':0,'D':time_today,'E':'----'})
tp1 = tp1.rename(columns={'B':'b','C':'c'})
tp1['需补充字段']= 0
tp1['create_date'] = str(date.today()-timedelta(days=1))
tp1['xxx_name'] = 'xxx有限公司{0}分公司'.format(city_name)
tp1['city_name'] = '{}市'.format(city_name)
tp1['xxxx_name'] = 'xxxx'
# 字段更名
tp2 = tp2.rename(columns=change_columns)
# 字段调序
tp2 = tp2.reindex(columns=index_list)
print(len(tp2))
tp2.head(3)

5.清洗

这部分比较随便，根据数据要求和全量数据格式，对整合好的 dataframe 进行数据清洗。

包括使用自定义函数和 apply() 函数对已有列数据的列内转换；

对时间格式进行统一化处理（需要注意数据格式，一般都处理成 str）；

利用一些已知列的关系和数值，算出未知列；

为防止输出时出现问题，把一些 id 列的数据格式转换为 str 。

# 'a'/'b'转换
def xx_type(x):
    if x == 'aaa':
        return 'a'
    elif x == 'bbb':
        return 'b'
    else:
        return '其他'
tp1.loc[:,'xx_type_name'] = tp2['xx_type_name'].apply(xx_type)
# 时间
tp1.loc[:,'xx_begin_time'] = tp2['xx_begin_time']+' 00:00:00'
tp1.loc[:,'xx_end_time'] = tp2['xx_end_time']+' 23:59:59'
# 利用函数对列数据进行处理
def p_type(x):
    if x == 'apple':
        return 'a'
    else:
        return 'b'
tp2['apple_type_name'] = tp2['apple_type_name'].apply(p_type)
# 利用一些已知列的关系和数值，算出未知列
tp1['banana_no'] = tp2['apple_no'][tp2['apple_name'] == tp2['banana_name']]
# 转换数据格式
tp1['id'] = tp1['id'].astype(str)
# 清洗结束
df_res = tp1.copy()
print(len(df_res))
df_res.head(3)

6.二次核对

校验空格

防止存在空值、空格值、excel中的空值N/A。

# 校验空格
has_space = df_res[['需检查列1', '需检查列2', '需检查列3']].apply(lambda x: isinstance(x, str) and (x.str.contains(' ').any() or x.str.strip().eq('').any() or x.str.strip().eq('N/A').any()), axis=0)
print(has_space)

校验时间

提取时间，按照业务需求校验时间是否符合要求。

我这里是提取一个起止时间，把提取的数据作为新的一列（临时df，不存入全量数据）格式转换为数字，判断如果不等于365就算有误，并将有误数据提取一些有必要的显示列查看。

df_time = df_res.copy() # 备份
# 计算时间差
df_time['p_time'] = pd.to_datetime(df_a['xx_end_time']) - pd.to_datetime(df_a['xx_begin_time']) 
# 格式转换为int天数
df_time['p_time'] = [int(str(i)[:str(i).index(' ')]) for i in df_time['p_time']]
# 判断
df_problem_time = df_time[df_time['p_time']!=365][['显示列1','显示列2','显示列3']]
if len(df_problem_time) > 0:
    print('起止期有误：')
    print(df_problem_time)

历史校验

因为已经进行格式转换了，先把今日新数据合并到全量数据中。

# 合并
data_all_temp = pd.concat([data_all, df_res], ignore_index=True)
print('原数据：'+str(len(data_all)))
print('合并后：'+str(len(data_all_temp)))

然后进行历史校验，主要校验某些字段是否唯一，此时检查的是业务唯一性，所以历史出险重复也是有可能的，需要结合经验去判断重复是否合理。

并且为了预防各种情况发生，需要单键、组合键一起检查。

在我的yyy&zzz组合键校验过程中，有一个 old df 和一个 new df ，它们都是匹配出来的全量组合键重复数据，区别是一个加了今天的新数据，一个是历史原数据。因为存在历史重复，所以我们最终需要关注的只是两个 df 的差值。

# xxx历史校验
duplicated_row_xxx_no = data_all_temp[data_all_temp.duplicated('policy_no', keep=False)][['xxx_no', 'yyy_no', 'zzz']]
if len(duplicated_row_xxx_no) > 0:
    print('xxx重复')
    print(duplicated_row_xxx_no)
# yyy&zzz历史校验
duplicated_row_yyy_no_old = data_all[data_all.duplicated(['yyy_no','zzz'], keep=False)][['xxx_no', 'yyy_no', 'zzz']]
duplicated_row_yyy_no_new = data_all_temp[data_all_temp.duplicated(['yyy_no','zzz'], keep=False)][['xxx_no', 'yyy_no', 'zzz']]
if len(duplicated_row_yyy_no_old) < len(duplicated_row_yyy_no_new):
    merge_result = pd.merge(duplicated_row_yyy_no_old, duplicated_row_yyy_no_new, on=['xxx_no'], how='outer', indicator=True)
    unique_data = merge_result.loc[merge_result['_merge'] == 'right_only',['xxx_no', 'yyy_no_y', 'zzz_y']]
    print('yyy&zzz重复')
    print(unique_data)    

7.输出

过程输出

使用上面已经清洗完成并合并的数据，输出到新的全量数据，如果整个数据处理过程需要进行多次，那么中途可以先使用这段代码更新历史全量数据。

注意：这种输出方式是覆盖文件的，所以务必保证数据结果准确，或者如果进行代码测试的话，原数据需要有备份。代码输出写成覆盖的是为了全流程可复用。

# 输出新excel
data_all_temp.to_excel('yourpath/历史数据{0}.xlsx'.format(path_date),index=False)

最终输出

如果全部数据预处理工作结束，那么执行以下代码，path_date_next 变量也在最上面声明过，是明天的日期，这样输出的全量数据就可以自然地作为明天任务的历史数据继续跑代码了。

# 结束时输出明天excel
data_all_temp.to_excel('yourpath/历史数据{0}.xlsx'.format(path_date_next),index=False)

当然，最终代码所需要修改、更新、迭代的都是业务需要的，要根据业务实际情况进行调整、添加。无需拘泥于全流程一键运行就能解决所有问题，遇到实际问题也可以尽心一些手动调整。不过尽管如此，这些代码也能极大程度提升我的工作速度了。

还年轻，时间不应该浪费在大量的数据预处理工作上，我因此有时间进行更有价值的工作和学习。