{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"### 학습 목표\n\n- 초대용량 데이터는 어떻게 처리할까요?\n\n\n- 데이터를 분할하여 불러오고 처리하는 방법에 대해서 연습해봅니다.\n\n\n- 불량 검출 문제에 대해 살펴봅니다.\n\n\n- Class imbalance 문제를 해결하는 방법에 대해 알아봅니다.","metadata":{}},{"cell_type":"markdown","source":"## Bosch Production Line Performance\n\n- 공정과정 데이터를 통해 제품 내부 불량 검출하기\n\n- 주어진 공정변수를 이용하여 정상/불량 분류하기","metadata":{"id":"U0IhpuicpE1o"}},{"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n\ntrain_numeric_part = pd.read_csv('../input/bosch-production-line-performance/train_numeric.csv.zip',\n                                nrows=1000)# 데이터를 불러올 때 몇개의 Row를 불러올것인가\n#                                 usecols=,# 불러올 column들을 지정\n#                                 chunksize=,) # 데이터를 매번 불러올 때 몇개의 row를 불러올것인가\ntrain_categorical_part = pd.read_csv('../input/bosch-production-line-performance/train_categorical.csv.zip',\n                                nrows=1000)# 데이터를 불러올 때 몇개의 Row를 불러올것인가\ntrain_date_part = pd.read_csv('../input/bosch-production-line-performance/train_date.csv.zip',\n                                nrows=1000)# 데이터를 불러올 때 몇개의 Row를 불러올것인가\nprint(train_numeric_part.shape, train_categorical_part.shape, train_date_part.shape)","metadata":{"id":"improving-limitation","outputId":"874047c9-4bfc-47a0-ee85-24ceb5cf49e1","execution":{"iopub.status.busy":"2022-06-30T07:17:54.530851Z","iopub.execute_input":"2022-06-30T07:17:54.531569Z","iopub.status.idle":"2022-06-30T07:17:55.836970Z","shell.execute_reply.started":"2022-06-30T07:17:54.531472Z","shell.execute_reply":"2022-06-30T07:17:55.835458Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_numeric_part\ntrain_categorical_part\ntrain_date_part","metadata":{"execution":{"iopub.status.busy":"2022-06-30T07:17:55.843092Z","iopub.execute_input":"2022-06-30T07:17:55.844119Z","iopub.status.idle":"2022-06-30T07:17:55.887613Z","shell.execute_reply.started":"2022-06-30T07:17:55.844068Z","shell.execute_reply":"2022-06-30T07:17:55.886220Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 1. 대용량 데이터 다루기\n\n**Technique1. 잘라서 불러오기 (read csv 함수를 사용하는 방법)**\n\n1) row 단위로 일부를 가져오기\n- nrows : 지정한 row 개수만큼만 잘라서 가져옵니다. (위에서부터)\n- chunksize : for문을 사용해서 매번 특정 row개수씩 가져옵니다.(위에서부터 순서대로)\n\n2) column 단위로 일부를 가져오기\n- usecos : 사용할 column들만 지정해서 가져옵니다.\n\n\n**Technique2. Reducing Memory Technique (dtype 변경하기)**","metadata":{}},{"cell_type":"code","source":"# for chunk in pd.read_csv('../input/bosch-production-line-performance/train_date.csv.zip',chunksize=100000,usecols=S0_cols):\n#     print(chunk.sum())","metadata":{"execution":{"iopub.status.busy":"2022-06-30T07:17:55.889189Z","iopub.execute_input":"2022-06-30T07:17:55.890120Z","iopub.status.idle":"2022-06-30T07:17:55.895861Z","shell.execute_reply.started":"2022-06-30T07:17:55.890066Z","shell.execute_reply":"2022-06-30T07:17:55.894440Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Technique 1.\n## 1) train_date_part를 가지고, 각 station별 feature를 뽑아봅니다.\n### 가정. station별로 측정 시간이 모두 같으면, 하나씩만 사용하자. (X)\n### 결론. station별 측정 시간을 평균값으로 사용하자 (time feature)\nstation_names=train_date_part.count().reset_index()['index'].str.split('_',expand=True)[1][1:].drop_duplicates().values\ntrain_date=pd.DataFrame()\nchunks=pd.read_csv('../input/bosch-production-line-performance/train_date.csv.zip',chunksize=100000)\nfor chunk in chunks:\n    temp_date =pd.DataFrame()\n\n    for station in station_names : \n        station_cols = chunk.columns[chunk.columns.str.contains(station)]\n        col_means = chunk[station_cols].mean(axis=1) #각 station에 대해서 row별 평균\n        temp_date[station] = col_means\n    #     print(col_means)\n    train_date = pd.concat([train_date,temp_date]) # 각 station별 측정시간의 평균으로 데이터를 요약한 정보.\ntrain_date","metadata":{"id":"isolated-barbados","execution":{"iopub.status.busy":"2022-06-30T07:17:55.900227Z","iopub.execute_input":"2022-06-30T07:17:55.900903Z","iopub.status.idle":"2022-06-30T07:21:01.480575Z","shell.execute_reply.started":"2022-06-30T07:17:55.900859Z","shell.execute_reply":"2022-06-30T07:21:01.479174Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_date.info()","metadata":{"execution":{"iopub.status.busy":"2022-06-30T07:21:01.482055Z","iopub.execute_input":"2022-06-30T07:21:01.482911Z","iopub.status.idle":"2022-06-30T07:21:01.651788Z","shell.execute_reply.started":"2022-06-30T07:21:01.482869Z","shell.execute_reply":"2022-06-30T07:21:01.650570Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"a=train_numeric_part.isnull().sum() #column별 결측치\nb=train_numeric_part.isnull().sum() #column별 결측치\n# len(train_numeric_part)\na+b","metadata":{"execution":{"iopub.status.busy":"2022-06-30T07:21:01.652913Z","iopub.execute_input":"2022-06-30T07:21:01.653573Z","iopub.status.idle":"2022-06-30T07:21:01.677131Z","shell.execute_reply.started":"2022-06-30T07:21:01.653531Z","shell.execute_reply":"2022-06-30T07:21:01.675769Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## TO-DO 모범답안\n# train_numeric 데이터를 불러와서, 전체 결측치 비율을 column별로 측정한 다음에\n# 결측치가 50%를 넘는 모든 column을 제거합니다.\n\nchunks=pd.read_csv('../input/bosch-production-line-performance/train_numeric.csv.zip',chunksize=100000)\n\nmissing_ratio = pd.Series(index=train_numeric_part.columns,\n                         data=np.zeros(len(train_numeric_part.columns)))\nn_data=0\n\nfor chunk in chunks:\n    temp = chunk.isnull().sum()\n    n_data=n_data + len(chunk)\n    \n    missing_ratio = missing_ratio +temp\n    \nmissing_ratio = missing_ratio / n_data # -> 비율로 변경\nmissing_ratio\n\n## TO-DO\n# train_numeric 데이터를 불러와서, 전체 결측치 비율을 column별로 측정한 다음에\n# 결측치가 50%를 넘는 모든 column을 제거합니다.\n\n# check_numeric=pd.DataFrame()\n# chunks=pd.read_csv('../input/bosch-production-line-performance/train_numeric.csv.zip',chunksize=100000)\n# total=0\n# for chunk in chunks:\n#     temp_numeric =pd.DataFrame()\n    \n#     total+=len(chunk)\n#     if check_numeric.empty:\n#         check_numeric= chunk.isnull().sum()\n#     else:\n#         check_numeric = check_numeric+chunk.isnull().sum()\n# check_numeric/total","metadata":{"execution":{"iopub.status.busy":"2022-06-30T07:21:01.678753Z","iopub.execute_input":"2022-06-30T07:21:01.679220Z","iopub.status.idle":"2022-06-30T07:22:45.301125Z","shell.execute_reply.started":"2022-06-30T07:21:01.679180Z","shell.execute_reply":"2022-06-30T07:22:45.299873Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# del_cols=check_numeric[(check_numeric/total*100)>50]\n# del_cols.index\ndrop_cols = train_numeric_part.columns[missing_ratio>=0.5]\nusecols = np.setdiff1d(train_numeric_part.columns , drop_cols)\n\ntrain_numeric=pd.read_csv('../input/bosch-production-line-performance/train_numeric.csv.zip',usecols=usecols)\ntrain_numeric","metadata":{"execution":{"iopub.status.busy":"2022-06-30T07:22:45.302449Z","iopub.execute_input":"2022-06-30T07:22:45.303242Z","iopub.status.idle":"2022-06-30T07:23:36.282769Z","shell.execute_reply.started":"2022-06-30T07:22:45.303208Z","shell.execute_reply":"2022-06-30T07:23:36.281245Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# JOIN 연산을 할 기준인 Id column을 추가\ntrain_date.columns = 't_'+ train_date.columns # 시간 관련 feature임을 알리기 위해 column name 변경\ntrain_date[\"Id\"]= train_numeric.Id\ntrain_date","metadata":{"execution":{"iopub.status.busy":"2022-06-30T07:23:36.286817Z","iopub.execute_input":"2022-06-30T07:23:36.287762Z","iopub.status.idle":"2022-06-30T07:23:36.573590Z","shell.execute_reply.started":"2022-06-30T07:23:36.287711Z","shell.execute_reply":"2022-06-30T07:23:36.572642Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train = pd.merge(train_numeric, train_date, on=\"Id\") ##inner join\ntrain","metadata":{"execution":{"iopub.status.busy":"2022-06-30T07:23:36.575325Z","iopub.execute_input":"2022-06-30T07:23:36.576074Z","iopub.status.idle":"2022-06-30T07:23:39.101443Z","shell.execute_reply.started":"2022-06-30T07:23:36.576025Z","shell.execute_reply":"2022-06-30T07:23:39.100241Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import gc\ndel train_numeric_part\ndel train_numeric\ndel train_date\ndel train_categorical_part\ndel train_date_part\ngc.collect() # 메모리 청소기","metadata":{"execution":{"iopub.status.busy":"2022-06-30T07:23:39.103516Z","iopub.execute_input":"2022-06-30T07:23:39.104342Z","iopub.status.idle":"2022-06-30T07:23:39.253913Z","shell.execute_reply.started":"2022-06-30T07:23:39.104295Z","shell.execute_reply":"2022-06-30T07:23:39.252575Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 결측치가 50%가 넘는 column들 제거.\n# drop_cols= train[train.isnull().mean()>=0.5]\n# use_cols= train[train.isnull().mean()<0.5]\n# train= train[use_cols]\nuse_cols=train.columns[train.isnull().mean()<0.5]\ntrain=train[use_cols]\ntrain","metadata":{"execution":{"iopub.status.busy":"2022-06-30T07:23:39.255610Z","iopub.execute_input":"2022-06-30T07:23:39.256079Z","iopub.status.idle":"2022-06-30T07:23:42.398584Z","shell.execute_reply.started":"2022-06-30T07:23:39.256032Z","shell.execute_reply":"2022-06-30T07:23:42.397583Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 결측치를 모두 0으로 채웁니다.\ntrain = train.fillna(0)\ntrain","metadata":{"execution":{"iopub.status.busy":"2022-06-30T07:23:42.403762Z","iopub.execute_input":"2022-06-30T07:23:42.404657Z","iopub.status.idle":"2022-06-30T07:23:43.845123Z","shell.execute_reply.started":"2022-06-30T07:23:42.404611Z","shell.execute_reply":"2022-06-30T07:23:43.844212Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Technique 2.\ndef reduce_mem_usage(props):\n    start_mem_usg = props.memory_usage().sum() / 1024**2 \n    print(\"Memory usage of properties dataframe is :\",start_mem_usg,\" MB\")\n    NAlist = [] # Keeps track of columns that have missing values filled in. \n    for col in props.columns:\n        if props[col].dtype != object:  # Exclude strings\n            \n            # Print current column type\n            print(\"******************************\")\n            print(\"Column: \",col)\n            print(\"dtype before: \",props[col].dtype)\n            \n            # make variables for Int, max and min\n            IsInt = False\n            mx = props[col].max()\n            mn = props[col].min()\n            \n            # Integer does not support NA, therefore, NA needs to be filled\n            if not np.isfinite(props[col]).all(): \n                NAlist.append(col)\n                props[col].fillna(mn-1,inplace=True)  \n                   \n            # test if column can be converted to an integer\n            asint = props[col].fillna(0).astype(np.int64)\n            result = (props[col] - asint)\n            result = result.sum()\n            if result > -0.01 and result < 0.01:\n                IsInt = True\n\n            \n            # Make Integer/unsigned Integer datatypes\n            if IsInt:\n                if mn >= 0:\n                    if mx < 255:\n                        props[col] = props[col].astype(np.uint8)\n                    elif mx < 65535:\n                        props[col] = props[col].astype(np.uint16)\n                    elif mx < 4294967295:\n                        props[col] = props[col].astype(np.uint32)\n                    else:\n                        props[col] = props[col].astype(np.uint64)\n                else:\n                    if mn > np.iinfo(np.int8).min and mx < np.iinfo(np.int8).max:\n                        props[col] = props[col].astype(np.int8)\n                    elif mn > np.iinfo(np.int16).min and mx < np.iinfo(np.int16).max:\n                        props[col] = props[col].astype(np.int16)\n                    elif mn > np.iinfo(np.int32).min and mx < np.iinfo(np.int32).max:\n                        props[col] = props[col].astype(np.int32)\n                    elif mn > np.iinfo(np.int64).min and mx < np.iinfo(np.int64).max:\n                        props[col] = props[col].astype(np.int64)    \n            \n            # Make float datatypes 32 bit\n            else:\n                props[col] = props[col].astype(np.float32)\n            \n            # Print new column type\n            print(\"dtype after: \",props[col].dtype)\n            print(\"******************************\")\n    \n    # Print final result\n    print(\"___MEMORY USAGE AFTER COMPLETION:___\")\n    mem_usg = props.memory_usage().sum() / 1024**2 \n    print(\"Memory usage is: \",mem_usg,\" MB\")\n    print(\"This is \",100*mem_usg/start_mem_usg,\"% of the initial size\")\n    return props, NAlist","metadata":{"execution":{"iopub.status.busy":"2022-06-30T07:23:43.846760Z","iopub.execute_input":"2022-06-30T07:23:43.847321Z","iopub.status.idle":"2022-06-30T07:23:43.867142Z","shell.execute_reply.started":"2022-06-30T07:23:43.847284Z","shell.execute_reply":"2022-06-30T07:23:43.865806Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train, _ = reduce_mem_usage(train)","metadata":{"execution":{"iopub.status.busy":"2022-06-30T07:23:43.868822Z","iopub.execute_input":"2022-06-30T07:23:43.869363Z","iopub.status.idle":"2022-06-30T07:24:27.995355Z","shell.execute_reply.started":"2022-06-30T07:23:43.869329Z","shell.execute_reply":"2022-06-30T07:24:27.994078Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 2. 불량 검출 문제 다루기 (Class Imbalance problem)\n\nTechnique 1. Sampling Method (Under / Over)\n\n- 1000000(정상) : 100(불량) = 10000 : 1 ---> 컴퓨터는 모든 데이터를 정상이라고 예층하려고 합니다.\n--> 1;1 비율로 변경이 필요합니다! (예측성능이 좋아짐)\n\n**개수가 많은 클래스=magority class, 개수가 적은 클랙스 = minority class**\n\n1) Undersampling\n- majority class에서 sampling을 하여, minority class의 수로 맞춰주는 기법.\neg. 1000000 -> 100 -------------> 100: 100: = 1:1\n2) Oversamplig\n- minority class에서 sampling하여, majority class의 수를 맞춰주는 기법.\neg. 100 -> 10000000 ------> 1000000 : 1000000 = 1:1\n3) Hybrid approach (under + over)\n- undersampling과 oversapling을 사용하여 1:1의 비율을 맞춰주는 기법.\neg. 1000000 -> 10000, 100 ->10000 ---------> 10000 : 10000 = 1:1\n\n\n\nTechnique 2. Change to Outlier Detection problem\n\n- DBSCAN, IsolationForest (Unsupervised learning approach): 비지도 학습\n\nsemi-supervised learning anomaly detection ","metadata":{}},{"cell_type":"code","source":"# Technique 1.\nsns.countplot(data=train, x=\"Response\")\ntrain.Response.value_counts(normalize=True)\n","metadata":{"execution":{"iopub.status.busy":"2022-06-30T07:24:27.996734Z","iopub.execute_input":"2022-06-30T07:24:27.997076Z","iopub.status.idle":"2022-06-30T07:24:28.296613Z","shell.execute_reply.started":"2022-06-30T07:24:27.997046Z","shell.execute_reply":"2022-06-30T07:24:28.295278Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"normal = train[train.Response ==0]\nabnormal = train[train.Response ==1]\n#Undersampling\nunder_normal = normal.sample(n=len(abnormal),random_state=42)\nu_train = pd.concat([under_normal,abnormal])\nu_train #13758 = 6879 *2\n\n\n\n#Hybrid","metadata":{"execution":{"iopub.status.busy":"2022-06-30T07:28:45.432150Z","iopub.execute_input":"2022-06-30T07:28:45.432636Z","iopub.status.idle":"2022-06-30T07:28:46.155346Z","shell.execute_reply.started":"2022-06-30T07:28:45.432598Z","shell.execute_reply":"2022-06-30T07:28:46.153998Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Oversampling = Generation , 원본 데이터의 분포를 학습해서 데이터 생성\nfrom imblearn.over_sampling import SMOTE\nX_train = train.drop(columns=[\"Id\",\"Response\"])\ny_train = train.Response\n\n# fit_resample 함수가 자동으로 y_train을 보고 minority class를 찾아서 1:1 비율을 맞춰준다\noX_train,oy_train = SMOTE().fit_resample(X_train,y_train)\nprint(oX_train.shape, oy_train.shape) # (1176868,168) /(1176868,168)","metadata":{"execution":{"iopub.status.busy":"2022-06-30T07:28:32.210373Z","iopub.status.idle":"2022-06-30T07:28:32.211344Z","shell.execute_reply.started":"2022-06-30T07:28:32.211060Z","shell.execute_reply":"2022-06-30T07:28:32.211089Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from imblearn.over_sampling import SMOTE\n\n# Hybrid (under + over)\nn_sample = 10000\n\nnormal = train[train.Response ==0].sample(n=n_sample,random_state=42)\nabnormal = train[train.Response ==1]\n\ntemp_df = pd.concat([normal,abnormal]) # 10000 + 6879\n\nX_train = temp_df.drop(columns=[\"Id\",\"Response\"])\ny_train = temp_df.Response\n\n# fit_resample 함수가 자동으로 y_train을 보고 minority class를 찾아서 1:1 비율을 맞춰준다\nhX_train,hy_train = SMOTE().fit_resample(X_train,y_train)\nprint(hX_train.shape, hy_train.shape) # (20000,168) /(20000,168)","metadata":{"execution":{"iopub.status.busy":"2022-06-30T07:39:48.183679Z","iopub.execute_input":"2022-06-30T07:39:48.184125Z","iopub.status.idle":"2022-06-30T07:39:50.144777Z","shell.execute_reply.started":"2022-06-30T07:39:48.184086Z","shell.execute_reply":"2022-06-30T07:39:50.143529Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"hy_train.value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-06-30T07:42:14.393774Z","iopub.execute_input":"2022-06-30T07:42:14.394182Z","iopub.status.idle":"2022-06-30T07:42:14.404512Z","shell.execute_reply.started":"2022-06-30T07:42:14.394148Z","shell.execute_reply":"2022-06-30T07:42:14.402969Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Technique 2.","metadata":{"execution":{"iopub.status.busy":"2022-06-30T07:24:28.298109Z","iopub.execute_input":"2022-06-30T07:24:28.298520Z","iopub.status.idle":"2022-06-30T07:24:28.303631Z","shell.execute_reply.started":"2022-06-30T07:24:28.298462Z","shell.execute_reply":"2022-06-30T07:24:28.302087Z"},"trusted":true},"execution_count":null,"outputs":[]}]}