日常工作中，分析師會(huì)接到一些專項(xiàng)分析的需求，首先會(huì)搜索腦中的分析體系，根據(jù)業(yè)務(wù)需求構(gòu)建相應(yīng)的分析模型（不只是機(jī)器學(xué)習(xí)模型），根據(jù)模型填充相應(yīng)維度表，這些維度特征表能夠被使用的前提是假設(shè)已經(jīng)清洗干凈了。

但真正的原始表是混亂且包含了很多無(wú)用的冗余特征，所以能夠根據(jù)原始數(shù)據(jù)清洗出相對(duì)干凈的特征表就很重要。

前兩天在Towards Data Science上看到一篇文章，講的是用Pandas做數(shù)據(jù)清洗，作者將常用的清洗邏輯封裝成了一個(gè)個(gè)的清洗函數(shù)。

https://towardsdatascience.com/the-simple-yet-practical-data-cleaning-codes-ad27c4ce0a38

而公司的業(yè)務(wù)數(shù)據(jù)一般存儲(chǔ)在數(shù)據(jù)倉(cāng)庫(kù)里面，數(shù)據(jù)量很大，這時(shí)候用Pandas處理是不大方便的，更多時(shí)候用的是HiveSQL和MySQL做處理。

基于此，我拓展了部分內(nèi)容，寫(xiě)了一個(gè)常用數(shù)據(jù)清洗的SQL對(duì)比版，腳本很簡(jiǎn)單，重點(diǎn)是這些清洗場(chǎng)景和邏輯，大綱如圖：

刪除指定列、重命名列

場(chǎng)景：

多數(shù)情況并不是底表的所有特征（列）都對(duì)分析有用，這個(gè)時(shí)候就只需要抽取部分列，對(duì)于不用的那些列，可以刪除。

重命名列可以避免有些列的命名過(guò)于冗長(zhǎng)（比如Case When 語(yǔ)句），且有時(shí)候會(huì)根據(jù)不同的業(yè)務(wù)指標(biāo)需求來(lái)命名。

刪除列Python版：
df.drop(col_names, axis=1, inplace=True)

刪除列SQL版：
1、select col_names from Table_Name

2、alter table tableName drop column columnName

重命名列Python版：
df.rename(index={'row1':'A'},columns ={'col1':'B'})

重命名列SQL版：
select col_names as col_name_B from Table_Name

因?yàn)橐话闱闆r下是沒(méi)有刪除的權(quán)限（可以構(gòu)建臨時(shí)表），反向思考，刪除的另一個(gè)邏輯是選定指定列（Select）。

重復(fù)值、缺失值處理

場(chǎng)景：比如某網(wǎng)站今天來(lái)了1000個(gè)人訪問(wèn)，但一個(gè)人一天中可以訪問(wèn)多次，那數(shù)據(jù)庫(kù)中會(huì)記錄用戶訪問(wèn)的多條記錄，而這時(shí)候如果想要找到今天訪問(wèn)這個(gè)網(wǎng)站的1000個(gè)人的ID并根據(jù)此做用戶調(diào)研，需要去掉重復(fù)值給業(yè)務(wù)方去回訪。

缺失值：NULL做運(yùn)算邏輯時(shí)，返回的結(jié)果還是NULL，這可能就會(huì)出現(xiàn)一些腳本運(yùn)行正確，但結(jié)果不對(duì)的BUG，此時(shí)需要將NULL值填充為指定值。

重復(fù)值處理Python版：
df.drop_duplicates()

重復(fù)值處理SQL版：
1、select distinct col_name from Table_Name

2、select col_name from Table_Name group bycol_name

缺失值處理Python版：
df.fillna(value = 0)

df1.combine_first(df2)

缺失值處理SQL版：
1、select ifnull(col_name,0) value from Table_Name

2、select coalesce(col_name,col_name_A,0) as value from Table_Name

3、select case when col_name is null then 0 else col_name end from Table_Name

替換字符串空格、清洗*%@等垃圾字符、字符串拼接、分隔等字符串處理

場(chǎng)景：理解用戶行為的重要一項(xiàng)是去假設(shè)用戶的心理，這會(huì)用到用戶的反饋意見(jiàn)或一些用研的文本數(shù)據(jù)，這些文本數(shù)據(jù)一般會(huì)以字符串的形式存儲(chǔ)在數(shù)據(jù)庫(kù)中，但用戶反饋的這些文本一般都會(huì)很亂，所以需要從這些臟亂的字符串中提取有用信息，就會(huì)需要用到文字符串處理函數(shù)。

字符串處理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('分割符')

## 4、字符串拼接
df[col_name].str.cat()

字符串處理SQL版：
## 1、空格處理
select ltrim(col_name) from Table_name 

## 2、*%d等垃圾符處理
select regexp_replace(col_name,正則表達(dá)式) from Table_name 

## 3、字符串分割
select split(col_name,'分割符') from Table_name 

## 4、字符串拼接
select concat_ws(col_name,'拼接符') from Table_name 

合并處理

場(chǎng)景：有時(shí)候你需要的特征存儲(chǔ)在不同的表里，為便于清洗理解和操作，需要按照某些字段對(duì)這些表的數(shù)據(jù)進(jìn)行合并組合成一張新的表，這樣就會(huì)用到連接等方法。

合并處理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)
合并處理SQL版：
左右合并
select A.*,B.* from Table_a A join Table_b B on A.id = B.id
select A.* from Table_a A left join Table_b B on A.id = B.id
上下合并
## Union：對(duì)兩個(gè)結(jié)果集進(jìn)行并集操作，不包括重復(fù)行，同時(shí)進(jìn)行默認(rèn)規(guī)則的排序；
## Union All：對(duì)兩個(gè)結(jié)果集進(jìn)行并集操作，包括重復(fù)行，不進(jìn)行排序；
select A.* from Table_a A 
union
elect B.* from Table_b B 

# Union 因?yàn)闀?huì)將各查詢子集的記錄做比較，故比起Union All ，通常速度都會(huì)慢上許多。一般來(lái)說(shuō)，如果使用Union All能滿足要求的話，務(wù)必使用Union All。 

窗口函數(shù)的分組排序

場(chǎng)景：假如現(xiàn)在你是某寶的分析師，要分析今年不同店的不同品類銷(xiāo)售量情況，需要找到那些銷(xiāo)量較好的品類，并在第二年中加大曝光，這個(gè)時(shí)候你就需要將不同店里不同品類進(jìn)行分組，并且按銷(xiāo)量進(jìn)行排序，以便查找到每家店銷(xiāo)售較好的品類。

Demo數(shù)據(jù)如上，一共a,b,c三家店鋪，賣(mài)了不同品類商品，銷(xiāo)量對(duì)應(yīng)如上，要找到每家店賣(mài)的最多的商品。

窗口分組Python版：
df['Rank'] = df.groupby(by=['Sale_store'])['Sale_Num'].transform(lambda x: x.rank(ascending=False))
窗口分組SQL版：
select 
  * 
from
  (
  Select 
    *，
    row_number() over(partition by Sale_store order by Sale_Num desc) rk
  from 
    table_name
  ) b where b.rk = 1

可以很清晰的看到，a店鋪賣(mài)的最火的是蔬菜，c店鋪賣(mài)的最火的是雞肉，b店鋪？

嗯，b店鋪很不錯(cuò)，賣(mài)了888份寶器狗。

總結(jié)，上面的內(nèi)容核心是掌握這些數(shù)據(jù)清洗的應(yīng)用場(chǎng)景，這些場(chǎng)景幾乎可以涵蓋90%的數(shù)據(jù)分析前數(shù)據(jù)清洗的內(nèi)容。而對(duì)于分析模型來(lái)說(shuō)，SQL和Python都是工具，如果熟悉SQL，是能夠更快速、方便的將特征清洗用SQL實(shí)現(xiàn)。

文源：數(shù)據(jù)管道 作者：寶器