07

'''
1. 移除重複值
'''
import pandas as pd
df = pd.DataFrame({'col1':['a','a','a','b','b','c','d','e'],'col2':[1,1,2,2,3,3,4,5]})
df
df.duplicated()
df.drop_duplicates()

# 針對所選column的duplicate值移除（預設會留下重複列的第一個）
df.drop_duplicates(['col1','col2'])
df.drop_duplicates(['col1'])
df.drop_duplicates(['col1']).reset_index(drop=True)

# 針對所選column的duplicate值移除（挑選重複列的最後一個）
df.drop_duplicates(['col1'], keep='last')

'''
2. 遺失值（Missing Data）
'''
import pandas as pd
df = pd.DataFrame({'col1':['a',float('NaN'),'a',None,'b','c','d','e'],'col2':[1,1,3,2,float('NaN'),3,4,5]})
df
df['col1'].isnull()
df = df[df['col1'].notnull()]
df
df = df[df['col2'].notnull()]
df
df = df.dropna()
df

'''
2.1 遺失值補值
'''
from scipy import stats
df = pd.DataFrame({'學歷':[4,float('NaN'),3,None,4,1,2,5],'薪水':[18,25,30,22,float('NaN'),33,41,14]})

temp_mode = stats.mode(df['學歷'])[0][0]
df['學歷'] = df['學歷'].fillna(temp_mode)  #若遺失值為類別型，補上眾數
df
df['薪水'] = df['薪水'].fillna(df['學歷'].mean())  #若遺失值為連續型，補上平均數
df

'''
3. 資料對應轉換（Mapping）
'''
df = pd.DataFrame({'sex':['male','male','female','male','female','female']})
df
sex_to_boolean = {'female':0,'male':1} #dict
df['code'] = df['sex'].map(sex_to_boolean)
df

'''
3.1 資料取代
'''
df = pd.DataFrame({'col1':['c01','c02','c03','c04','c05'],'col2':[65,'NULL','NaN','NaN',78],'col3':[321,34,'NULL','NaN',34]})
df
df['col2'].replace('NaN',0)
df.replace('NaN',0)
df.replace(['NaN','NULL'],0)
df.replace({'NaN':0,'NULL':-1})  #dictionary

'''
3.2 One-Hot-Encoding
'''
import pandas as pd
df = pd.DataFrame({'部門':['會計','業務','業務','研發','管理','會計','研發','人事'], '薪水':[10,25,34,22,15,18,30,22]})
df
dummy_code = pd.get_dummies(df['部門'])
dummy_code
df = df.drop('部門', axis=1)
df = df.join(dummy_code)
df

'''
4. 分箱、轉換
'''
df = pd.DataFrame({'id':['s01','s02','s03','s04','s05'],'score':[74,59,98,84,60]})
df
bins = [0,60,70,80,90,100]
pd.cut(df['score'],bins)
pd.cut(df['score'],bins, right=False)
labels = ['F','D','C','B','A']
pd.cut(df['score'],bins, right=False, labels=labels)
df['label'] = pd.cut(df['score'],bins, right=False, labels=labels)
df