{"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":"In this notebook, we cleanses data that does not match the Japanese zip codes(postal codes) format.\n\n日本の郵便番号の形式に一致しないデータをクレンジングします。","metadata":{}},{"cell_type":"code","source":"import pandas as pd\nimport numpy as np","metadata":{"execution":{"iopub.status.busy":"2022-07-16T11:41:09.638250Z","iopub.execute_input":"2022-07-16T11:41:09.638685Z","iopub.status.idle":"2022-07-16T11:41:09.644150Z","shell.execute_reply.started":"2022-07-16T11:41:09.638635Z","shell.execute_reply":"2022-07-16T11:41:09.642984Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"DF_PATH = \"../input/foursquare-location-matching/train.csv\"\ndf = pd.read_csv(DF_PATH).reset_index()","metadata":{"execution":{"iopub.status.busy":"2022-07-16T11:41:10.300957Z","iopub.execute_input":"2022-07-16T11:41:10.301677Z","iopub.status.idle":"2022-07-16T11:41:17.212596Z","shell.execute_reply.started":"2022-07-16T11:41:10.301624Z","shell.execute_reply":"2022-07-16T11:41:17.211488Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"日本の郵便番号は下記の形式です。（7けたの数字。3けた目と4けた目の間にハイフン）\n\nJapanese zip codes(postal codes) are in the following format.(7 digits, with a hyphen between the 3rd and 4th digit)\n\n123-4567\n\nhttps://www.post.japanpost.jp/zipcode/zipmanual/p04.html","metadata":{}},{"cell_type":"code","source":"# 形式が異なるデータが109件存在\n# 109 data exist with different formats\nprint(\"number of invalid zip codes: \", len(df.loc[(df[\"country\"]==\"JP\")&(df[\"zip\"]==df[\"zip\"])&(~df['zip'].astype(str).fillna(\"\").str.contains('[0-9]{3}-[0-9]{4}')), \"zip\"]))\ndf.loc[(df[\"country\"]==\"JP\")&(df[\"zip\"]==df[\"zip\"])&(~df['zip'].astype(str).fillna(\"\").str.contains('[0-9]{3}-[0-9]{4}')), \"zip\"]","metadata":{"execution":{"iopub.status.busy":"2022-07-16T11:41:39.245833Z","iopub.execute_input":"2022-07-16T11:41:39.246262Z","iopub.status.idle":"2022-07-16T11:41:42.144192Z","shell.execute_reply.started":"2022-07-16T11:41:39.246226Z","shell.execute_reply":"2022-07-16T11:41:42.142969Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"郵便番号と住所等には半角と全角が混在しているので、統一します。\n\nZip codes and address, etc., are mixed with full-width and half-width characters, so they should be unified.","metadata":{}},{"cell_type":"code","source":"#  full-width example in zip code\ndf.loc[(df[\"country\"]==\"JP\")&(df[\"zip\"].str.contains(\"０|１|２|３|４|５|６|７|８|９\", regex=True)), \"zip\"] ","metadata":{"execution":{"iopub.status.busy":"2022-07-16T11:42:50.791918Z","iopub.execute_input":"2022-07-16T11:42:50.792329Z","iopub.status.idle":"2022-07-16T11:42:51.759297Z","shell.execute_reply.started":"2022-07-16T11:42:50.792297Z","shell.execute_reply":"2022-07-16T11:42:51.757749Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#  half-width example in zip code\ndf.loc[(df[\"country\"]==\"JP\")&(df[\"zip\"].str.contains(\"0|1|2|3|4|5|6|7|8|9\", regex=True)), \"zip\"] ","metadata":{"execution":{"iopub.status.busy":"2022-07-16T11:42:51.761749Z","iopub.execute_input":"2022-07-16T11:42:51.762207Z","iopub.status.idle":"2022-07-16T11:42:52.995767Z","shell.execute_reply.started":"2022-07-16T11:42:51.762161Z","shell.execute_reply":"2022-07-16T11:42:52.994319Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#  full-width example in address\ndf.loc[(df[\"country\"]==\"JP\")&(df[\"address\"].str.contains(\"０|１|２|３|４|５|６|７|８|９\", regex=True)), \"address\"] ","metadata":{"execution":{"iopub.status.busy":"2022-07-16T11:42:52.998291Z","iopub.execute_input":"2022-07-16T11:42:52.998809Z","iopub.status.idle":"2022-07-16T11:42:54.139252Z","shell.execute_reply.started":"2022-07-16T11:42:52.998758Z","shell.execute_reply":"2022-07-16T11:42:54.138016Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#  half-width example in address\ndf.loc[(df[\"country\"]==\"JP\")&(df[\"address\"].str.contains(\"0|1|2|3|4|5|6|7|8|9\", regex=True)), \"address\"] ","metadata":{"execution":{"iopub.status.busy":"2022-07-16T11:42:54.142517Z","iopub.execute_input":"2022-07-16T11:42:54.142879Z","iopub.status.idle":"2022-07-16T11:42:55.284869Z","shell.execute_reply.started":"2022-07-16T11:42:54.142849Z","shell.execute_reply":"2022-07-16T11:42:55.283433Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Convert Full-width to Half-width\nfor col_to_cleanse in [\"name\", \"address\", \"city\", \"state\", \"zip\"]:\n    df.loc[(df[\"country\"]==\"JP\")&(df[col_to_cleanse]==df[col_to_cleanse]), col_to_cleanse] = df.loc[(df[\"country\"]==\"JP\")&(df[col_to_cleanse]==df[col_to_cleanse]), col_to_cleanse].astype(str).str.normalize('NFKC')","metadata":{"execution":{"iopub.status.busy":"2022-07-16T11:42:55.287053Z","iopub.execute_input":"2022-07-16T11:42:55.287378Z","iopub.status.idle":"2022-07-16T11:42:59.262044Z","shell.execute_reply.started":"2022-07-16T11:42:55.287348Z","shell.execute_reply":"2022-07-16T11:42:59.260737Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#  full-width example in zip code (converted)\ndf.loc[(df[\"country\"]==\"JP\")&(df[\"zip\"].str.contains(\"０|１|２|３|４|５|６|７|８|９\", regex=True)), \"zip\"] ","metadata":{"execution":{"iopub.status.busy":"2022-07-16T11:42:59.264088Z","iopub.execute_input":"2022-07-16T11:42:59.264534Z","iopub.status.idle":"2022-07-16T11:43:00.238232Z","shell.execute_reply.started":"2022-07-16T11:42:59.264502Z","shell.execute_reply":"2022-07-16T11:43:00.236982Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#  full-width example in address (converted)\ndf.loc[(df[\"country\"]==\"JP\")&(df[\"address\"].str.contains(\"０|１|２|３|４|５|６|７|８|９\", regex=True)), \"address\"] ","metadata":{"execution":{"iopub.status.busy":"2022-07-16T11:43:00.239729Z","iopub.execute_input":"2022-07-16T11:43:00.240090Z","iopub.status.idle":"2022-07-16T11:43:01.503065Z","shell.execute_reply.started":"2022-07-16T11:43:00.240058Z","shell.execute_reply":"2022-07-16T11:43:01.501649Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Unify hyphenation distortion\n# ハイフンの表記ゆれを統一する\ndf[\"zip\"] = df[\"zip\"].astype(str).str.replace(\"‒\",\"-\")\ndf[\"zip\"] = df[\"zip\"].astype(str).str.replace(\"‐\",\"-\")\ndf[\"zip\"] = df[\"zip\"].astype(str).str.replace(\"–\",\"-\")\ndf[\"zip\"] = df[\"zip\"].astype(str).str.replace(\"−\",\"-\")\ndf[\"zip\"] = df[\"zip\"].astype(str).str.replace(\"ｰ\",\"-\")\ndf[\"zip\"] = df[\"zip\"].astype(str).str.replace(\"－\",\"-\")\ndf[\"zip\"] = df[\"zip\"].astype(str).str.replace(\"ー\",\"-\")\ndf[\"zip\"] = df[\"zip\"].astype(str).str.replace(\"--\",\"-\")\ndf[\"zip\"] = df[\"zip\"].replace(\"nan\",np.nan)","metadata":{"execution":{"iopub.status.busy":"2022-07-16T11:43:22.403852Z","iopub.execute_input":"2022-07-16T11:43:22.404256Z","iopub.status.idle":"2022-07-16T11:43:29.307575Z","shell.execute_reply.started":"2022-07-16T11:43:22.404223Z","shell.execute_reply":"2022-07-16T11:43:29.306423Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Add hyphens to non-hyphenated data\n# ハイフンのないデータにハイフンを追加\n# 1234567 →　123-4567\ndf.loc[(df[\"country\"]==\"JP\")&(df[\"zip\"]==df[\"zip\"])&(df['zip'].astype(str).str.contains('[0-9]{7}')), \"zip\"] = df.loc[(df[\"country\"]==\"JP\")&(df[\"zip\"]==df[\"zip\"])&(df['zip'].astype(str).str.contains('[0-9]{7}')), \"zip\"].str[:3] + \"-\" + df.loc[(df[\"country\"]==\"JP\")&(df[\"zip\"]==df[\"zip\"])&(df['zip'].astype(str).str.contains('[0-9]{7}')), \"zip\"].str[3:]","metadata":{"execution":{"iopub.status.busy":"2022-07-16T11:43:50.166685Z","iopub.execute_input":"2022-07-16T11:43:50.167228Z","iopub.status.idle":"2022-07-16T11:43:54.153088Z","shell.execute_reply.started":"2022-07-16T11:43:50.167180Z","shell.execute_reply":"2022-07-16T11:43:54.151886Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# The number of data in different formats is reduced from 109 to 41.\n# 形式が異なるデータの数を109件から41件に削減できた、\nprint(\"number of invalid zip codes: \", len(df.loc[(df[\"country\"]==\"JP\")&(df[\"zip\"]==df[\"zip\"])&(~df['zip'].astype(str).fillna(\"\").str.contains('[0-9]{3}-[0-9]{4}')), \"zip\"]))\ndf.loc[(df[\"country\"]==\"JP\")&(df[\"zip\"]==df[\"zip\"])&(~df['zip'].astype(str).fillna(\"\").str.contains('[0-9]{3}-[0-9]{4}')), \"zip\"]","metadata":{"execution":{"iopub.status.busy":"2022-07-16T11:46:33.645691Z","iopub.execute_input":"2022-07-16T11:46:33.646129Z","iopub.status.idle":"2022-07-16T11:46:36.595489Z","shell.execute_reply.started":"2022-07-16T11:46:33.646095Z","shell.execute_reply":"2022-07-16T11:46:36.594139Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Data in incorrect format is noise, so it may be better to replace it with np.nan\n# 不正な形式のデータはノイズとなるので、np.nanに置き換えた方が良いかもしれない\ndf.loc[(df[\"country\"]==\"JP\")&(df[\"zip\"]==df[\"zip\"])&(~df['zip'].astype(str).fillna(\"\").str.contains('[0-9]{3}-[0-9]{4}')), \"zip\"]=np.nan","metadata":{},"execution_count":null,"outputs":[]}]}