干货！做数据分析机器学习模型时应该掌握的5个SQL数据清洗方法），根据模型填写对应的维度表，这些维度特征表能用的前提是假设已经清洗过了。但是真正的原始表是杂乱无章的，包含很多无用的冗余特征，所以能够在原始数据的基础上清理出一个相对干净的特征表是非常重要的。前两天看到一篇TowardsDataScience的文章，讲到用Pandas做数据清洗。作者将常用的清洗逻辑一一封装成清洗函数。https://towardsdatascience.com/the-simple-yet-practical-data-cleaning-codes-ad27c4ce0a38公司的业务数据一般都存放在数据仓库中，数据量很大。这个时候不方便用Pandas来处理，更多的时候用HiveSQL和MySql来处理。基于此，我扩展了一些内容，写了一个常用数据清洗的SQL对比版。脚本很简单，重点就是这些清洗场景和逻辑。表格的所有特征（列）都对分析有用。此时只需要提取部分列，那些不用的列可以删除。重命名列可以避免某些列的命名过于冗长（比如CaseWhen语句），有时会根据不同的业务指标要求来命名。删除列Python版本：df.drop(col_names,axis=1,inplace=True)删除列SQL版本：1,selectcol_namesfromTable_Name2,altertableNamedropcolumncolumnNamerenamecolumnPython版本：df.rename(index={'row1':'A'},columns={'col1':'B'})RenamecolumnSQLversion:selectcol_namesascol_name_BfromTable_Name因为一般没有权限删除（可以建临时表），逆向思维，删除的另一个逻辑是选择指定的列（选择）。重复值和缺失值处理场景：比如一个网站今天有1000次访问，但是一个人一天可以访问多次，那么数据库就会记录用户访问过的多条记录，这时候，如果你想找到今天访问的网站，网站1000人的ID，以此为基础进行用户调研，业务方回访需要去除重复值。缺失值：NULL使用运算逻辑时，返回结果仍为NULL。这可能会导致某些脚本可以正确运行，但结果是错误的。在这种情况下，NULL值需要用指定的值填充。重复值处理Python版本：df.drop_duplicates()重复值处理SQL版本：1、selectdistinctcol_namefromTable_Name2、selectcol_namefromTable_Namegroupbycol_name缺失值处理Python版本：df.fillna(value=0)df1.combine_first(df2)缺失值处理SQL版本：1、selectifnull(col_name,0)valuefromTable_Name2、selectcoalesce(col_name,col_name_A,0)asvaluefromTable_Name3、selectcasewhencol_nameisnullthen0elsecol_nameendfromTable_Name替换字符串空格、清理*%@等垃圾字符、字符串拼接、分离等字符串处理场景：了解用户行为的重要项目它就是假设用户的心理，会用到用户反馈或者用户研究的一些文本数据。这些文本数据一般以字符串的形式存储在数据库中，但是用户反馈的文本一般都是杂乱无章的，所以如果需要从这些杂乱的字符串中提取有用的信息，就需要使用文本字符串处理函数。字符串处理Python版本：##1、空格处理df[col_name]=df[col_name].str.lstrip()##2、*%d等垃圾字符处理df[col_name].replace('&#.*','',regex=True,inplace=True)##3、字符串拆分df[col_name].str.split('separator')##4、字符串拼接df[col_name].str.cat()字符串处理SQL版本：##1、空格处理selectltrim(col_name)fromTable_name##2、*%d等垃圾字符处理selectregexp_replace(col_name,正则表达式)fromTable_name##3、字符串拆分selectsplit(col_name,'Separator')fromTable_name##4、字符串拼接selectconcat_ws(col_name,'拼接字符')fromTable_name合并处理场景：有时候你需要的特征存放在不同的表中，为了方便清洗、理解和操作，你需要按照一定的字段合并数据将这些表合并到一个新表中，因此将使用连接等方法。合并处理Python版：左右合并1、pd.merge(left,right,how='inner',on=None,left_on=None,right_on=None,left_index=False,right_index=False,sort=True,suffixes=('_x','_y'),copy=True,indicator=False,validate=None)2、pd.concat([df1,df2])上下合并df1.append(df2,ignore_index=True,sort=False)merge处理SQL版本：左右合并selectA.*,B.*fromTable_aAjoinTable_bBonA.id=B.idselectA.*fromTable_aAleftjoinTable_bBonA.id=B.id上下合并##Union：对两个进行联合操作结果集，排除重复行，同时按默认规则排序；##UnionAll：对两个结果集进行并集运算，包括重复行，不排序；selectA.*fromTable_aAunionelectB.*fromTable_bB#Union会比较每个查询子集的记录，因此，相比UnionAll，速度通常要慢很多。一般来说，如果使用UnionAll可以满足要求，一定要使用UnionAll。窗口函数分组排序场景：如果你是某宝的分析师，想分析今年不同门店不同品类的销量，需要在第二年找到销量较好的那些品类，增加曝光.您需要对不同店铺的不同品类进行分组，并按照销量进行排序，从而找到每个店铺销量较好的品类。Demo数据如上。有a、b、c三个商店。他们销售不同类别的产品。销售量对应于上述。有必要找到每个商店中销售最多的产品。窗口分组Python版本：df['Rank']=df.groupby(by=['Sale_store'])['Sale_Num'].transform(lambdax:x.rank(ascending=False))窗口分组SQL版本：select*from(Select*,row_number()over(partitionbySale_storeorderbySale_Numdesc)rkfromtable_name)bwhereb.rk=1可以清楚的看到a店卖的最多的是蔬菜，c店卖的最多的是鸡肉，b店呢？嗯，b店很好，卖了888只宝犬。综上所述，以上内容的核心是掌握这些数据清洗的应用场景，几乎可以涵盖数据分析前数据清洗的90%的内容。对于分析模型，SQL和Python都是工具。如果熟悉SQL，可以更快速方便地用SQL实现特征清洗。
