python 识别不标准的project name

一、业务目标说明

• project 含有两个字段，分别为 project id和project name
• project id和project name理论上是一一对应关系，需要找出project id相同，project name不同的数据
• project name不标准主要体现在 大小写不一致，需要区分大小写；含有非法空格

二、python实现

import  pandas as pd
import numpy as np
import random
np.set_printoptions(precision=3, suppress=True)
np.set_printoptions(formatter={'float': '{: 0.3f}'.format})
pd.set_option('precision', 5) #设置精度
pd.set_option('display.float_format', lambda x: '%.0f' % x) #为了直观的显示数字，不采用科学计数法

'''
drop_repeat_infor:删除完全相同的字段repeat_infor的数据条目，区分大小写
df_data_origin为原始数据（dateframe），repeat_infor是目标字段名称（str）
'''
def drop_repeat_infor(df_data_origin,repeat_infor):
    df_drop_repeat_infor = df_data_origin.drop_duplicates([repeat_infor])
    return df_drop_repeat_infor 

'''
get_case_insensentive_repeat_infor：不区分字段repeat_infor大小写，找出重复字段repeat_infor的数据
保留所有重复值，duplicated(subset=[repeat_infor],keep = False)
保留一条重复值,duplicated(subset=[repeat_infor],keep = 'first')
'''
def get_case_insensentive_repeat_infor(df_data,repeat_infor,ref_infor):
    #去重
    df_drop_repeat_infor = df_data.drop_duplicates(subset=[repeat_infor])
    #字段repeat_infor改为小写
#     ref_infor_lower = repeat_infor+'_lower'
    df_drop_repeat_infor_lower = pd.DataFrame(columns = [repeat_infor])
    df_drop_repeat_infor_lower[repeat_infor] = df_drop_repeat_infor[repeat_infor].astype(str).str.lower()
    #查找去重后，不区分大小写的所有重复值
    df_repeat_infor_lower = df_drop_repeat_infor[df_drop_repeat_infor_lower.duplicated(subset=[repeat_infor],keep = False)]

    return df_repeat_infor_lower
    
'''
def get_similar_infor_data：找出参考字段（ref_infor）相同,关心字段(repeat_infor）不同的数据条目
df_data数据（dateframe）
'''
def get_similar_infor_data(df_data,repeat_infor,ref_infor):
    #去重
    df_drop_repeat_infor = df_data.drop_duplicates([repeat_infor])
    #按照参考字段统计关心字段条目数
    df_counts = df_drop_repeat_infor.groupby([ref_infor]).size().reset_index(name='counts')
    #选择counts>1的条目
    df_similar_infor_data = df_counts[df_counts['counts']>1]
    return df_similar_infor_data

#导入数据
TFS_data_origin = pd.read_csv("data_input/TFS_需求清单项目名称.csv",encoding = 'gbk')

#完全相同的项目名称去重
TFS_drop_repeat_infor_temp = pd.DataFrame(columns=['project_code','project_name'])
TFS_drop_repeat_infor_temp = drop_repeat_infor(TFS_data_origin,'project_name')
#排序
TFS_drop_repeat_infor = TFS_drop_repeat_infor_temp.sort_values('project_name',ascending=True,na_position='first')#na_position='last' NaN放在最后

#针对去重的项目名称，不区分大小写，找出重复的项目名称的数据
TFS_drop_repeat_infor_lower = pd.DataFrame(columns=['project_name_lower'])
TFS_drop_repeat_infor_lower = get_case_insensentive_repeat_infor(TFS_data_origin,'project_name','project_code')
print('1、去重后，不区分大小写的所有重复的项目名称')
print(TFS_drop_repeat_infor_lower)


#查找相同项目id不同项目名称的数据
TFS_get_similar_infor_data = get_similar_infor_data(TFS_data_origin,'project_name','project_code')
#获取项目名称和id
list_temp = TFS_get_similar_infor_data['project_code'].astype(str).tolist()
str_similar_id = ','.join(list_temp)
TFS_similar_name = TFS_drop_repeat_infor[TFS_drop_repeat_infor['project_code'].apply(lambda x:str(x) in (str_similar_id))]
print('2、相同项目id不同项目名称的项目编码/项目名称')
print(TFS_similar_name)