{"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":"<h1><center> Foursquare Location Matching </center></h1>\n<h3><center> Train Data Preprocessing </center></h3>\n<h3><center> Tao Shan </center></h3>\n\nThis notebook preprocess data pairs including methods:\n1. finding same countries pairs\n1. generate new cols accroding to missing values\n1. numeric data generate new columns\n1. impute missing values\n1. word preprocessing\n1. fuzzy similarity\n1. save data to pkl format. This format runs faster than csv\n\n### Other Relevant notebooks and links\n\nCompetition: [Foursquare - Location Matching](https://www.kaggle.com/competitions/foursquare-location-matching)\n\nTrain data generation notebook: [Foursquare - train data generation](https://www.kaggle.com/taos2000/foursquare-train-data-generation)\n\nTrain data preprocessing notebook: [Foursquare - train data preprocess](https://www.kaggle.com/taos2000/foursquare-train-data-preprocess)\n\nModel Selection: [Foursquare - model selection](https://www.kaggle.com/taos2000/foursquare-model-selection)\n\nModel Training: [Foursquare - model training](https://www.kaggle.com/taos2000/foursquare-model-training)","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-07-06T16:50:48.519310Z","iopub.execute_input":"2022-07-06T16:50:48.519669Z","iopub.status.idle":"2022-07-06T16:50:48.528678Z","shell.execute_reply.started":"2022-07-06T16:50:48.519637Z","shell.execute_reply":"2022-07-06T16:50:48.527696Z"}}},{"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nfrom sklearn.neighbors import NearestNeighbors\nfrom sklearn.feature_extraction.text import TfidfVectorizer\n\nfrom geopy.geocoders import Nominatim\n\nimport re\nimport string\nfrom nltk.tokenize import word_tokenize\nfrom nltk.corpus import stopwords, wordnet as wn\nfrom nltk.stem import PorterStemmer, WordNetLemmatizer\n\nimport Levenshtein as lev\nimport math\nfrom collections import Counter\n\nfrom pickle import dump, load\nimport time\nfrom sklearn.neighbors import BallTree\n\n\nimport itertools\nfrom tqdm.auto import tqdm\ntqdm.pandas()\nimport gc\n\nfrom fuzzywuzzy import fuzz\nfrom xgboost import XGBClassifier\nfrom sklearn.preprocessing import MinMaxScaler\nstart_time = time.time()","metadata":{"execution":{"iopub.status.busy":"2022-07-25T19:47:20.019759Z","iopub.execute_input":"2022-07-25T19:47:20.020285Z","iopub.status.idle":"2022-07-25T19:47:20.031222Z","shell.execute_reply.started":"2022-07-25T19:47:20.020238Z","shell.execute_reply":"2022-07-25T19:47:20.029705Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#pairs = pd.read_csv('../input/foursquare-location-matching/pairs.csv')\n#pairs = pd.read_csv('../input/foursquare-location-matching/pairs.csv').sample(n=100000, random_state=6888)\npairs = pd.read_pickle('../input/last-day-four-points/train_pairs (1).pkl')","metadata":{"execution":{"iopub.status.busy":"2022-07-25T19:47:20.077727Z","iopub.execute_input":"2022-07-25T19:47:20.078498Z","iopub.status.idle":"2022-07-25T19:47:53.705946Z","shell.execute_reply.started":"2022-07-25T19:47:20.078446Z","shell.execute_reply":"2022-07-25T19:47:53.704684Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ids = ['id_1','id_2']\ntest_id = pairs[ids]\npairs['country_same'] = np.where(pairs['country_1'].astype(object) == pairs['country_2'].astype(object),1,0)\n# missing values generate new cols\nmissing_list = ['url_1','url_2','phone_1','phone_2','address_1','address_2','city_1','city_2','zip_1','zip_2']\nfor col in tqdm(missing_list):\n    pairs[f\"{col}_missing\"] = pairs[col].notnull().astype('int8')\ndef count_occurance(df, cols_1,cols_2):\n    for i in tqdm(range(len(cols_1))):\n        df[f\"{cols_1[i]}_count\"] = (df[cols_1[i]].map(df[cols_1[i]].dropna().value_counts().to_dict())).fillna(0)\n        df[f\"{cols_2[i]}_count\"] = df[cols_2[i]].map(df[cols_2[i]].dropna().value_counts().to_dict()).fillna(0)\n        df[f\"{cols_1[i]}_count_diff\"] = (df[f\"{cols_2[i]}_count\"] - df[f\"{cols_1[i]}_count\"])\n        gc.collect()\n    return df\ncount_cols_1 = ['country_1','city_1','state_1','categories_1']\ncount_cols_2 = ['country_2','city_2','state_2','categories_2']\npairs = count_occurance(pairs, count_cols_1, count_cols_2)\ndef numeric_group_counts(df,cols_1,cols_2):\n    # cols should be [lat,lon]\n    for i in tqdm(range(len(cols_1))):\n        group_1 = pd.cut(df[cols_1[i]], 180)\n        df[f\"{cols_1[i]}_count\"] = group_1.map(group_1.value_counts().to_dict())\n        group_2 = pd.cut(df[cols_1[i]], 180)\n        df[f\"{cols_2[i]}_count\"] = group_2.map(group_2.value_counts().to_dict())\n        df[f\"{cols_2[i]}_count_diff\"] = (df[f\"{cols_1[i]}_count\"] - df[f\"{cols_2[i]}_count\"])\n        gc.collect()\n    return df\nnum_group_count_1 = ['latitude_1','longitude_1']\nnum_group_count_2 = ['latitude_2','longitude_2']\npairs = numeric_group_counts(pairs, num_group_count_1, num_group_count_2)\ngc.collect()\n# impute missing values\ncat_col = pairs.select_dtypes(include = ['object']).columns\npairs[cat_col] = pairs[cat_col].fillna('')\ngc.collect()\n# 1. location\ndef distance(lat1, lon1, lat2, lon2):\n    R = 6373.0\n    d_lon = lon2 - lon1\n    d_lat = lat2 - lat1\n    a = (np.sin(d_lat/2)) ** 2 + np.cos(lat1) * np.cos(lat2) * (np.sin(d_lon/2)) ** 2\n    c = 2 * np.arctan2(np.sqrt(a), np.sqrt(1-a))\n    distance = R * c\n    return distance\n\n# lowercase\ndef lower(df, cols):\n    for col in tqdm(cols):\n        df[col] = df[col].progress_apply(lambda x: x.lower())\n    return df\n\n# number removing\ndef num_remove(df, cols):\n    for col in tqdm(cols):\n        df[col] = df[col].progress_apply(lambda x: re.sub(r'\\d+', '', x))\n    return df\n\n# punctuation removal\ndef punc_remove(df,cols):\n    for col in tqdm(cols):\n        df[col] = df[col].progress_apply(lambda x: re.sub('['+string.punctuation+']', ' ', x))\n    return df\n\n# white spaces removal\ndef space_remove(df,cols):\n    for col in tqdm(cols):\n        df[col] = df[col].progress_apply(lambda x: x.strip()) # remove front and end space\n        df[col] = df[col].str.replace('\\s+', ' ', regex=True) # remove double space\n    return df\n\ndef preprocess(df,cols):\n    df[cols] = num_remove(df,cols)[cols]\n    df[cols] = punc_remove(df,cols)[cols]\n    df[cols] = space_remove(df,cols)[cols]\n    return df\n\n# remove url\ndef remove_URL(df,cols):\n    # cols = [\"url_1\",\"url_2\"]\n    df[cols] = df[cols].fillna('')\n    for i in tqdm(cols):\n        df[i] = df[i].str.replace('http', '')\n        df[i] = df[i].str.replace('https', '')\n        df[i] = df[i].str.replace('www', '')\n        #df[i] = df[i].progress_apply(lambda x: re.sub('\\W', \"\", x))\n    return df\n\n# stop words removal\n\ndef list_to_string(lis):\n    string = ''\n    for i in tqdm(lis):\n        string += i\n        string += ' '\n    return string[:-1]\n\ndef stop(string):\n    stops = set(stopwords.words('english'))\n    tokens = word_tokenize(string)\n    result = [i for i in tqdm(tokens) if not i in stops]\n    return result\n    \ndef stop_remove(df,cols):\n    stops = set(stopwords.words('english'))\n    for col in tqdm(cols):\n        df[col] = df[col].progress_apply(lambda x: ' '.join([word for word in x.split() if word not in stops]))\n    return df\nlowercase_cols = ['name_1','address_1','city_1','state_1','url_1','categories_1','name_2','address_2','city_2','state_2','url_2','categories_2']\npreprocess_cols = ['name_1','address_1','name_2','address_2','url_1','url_2','categories_1','categories_2']\nurl_columns = ['url_1','url_2']\npairs['distance'] = distance(pairs.latitude_1,pairs.longitude_1,pairs.latitude_2,pairs.longitude_2)\npairs[lowercase_cols] = lower(pairs[lowercase_cols], lowercase_cols)[lowercase_cols]\npairs[preprocess_cols] = preprocess(pairs[preprocess_cols], preprocess_cols)[preprocess_cols]\npairs[url_columns] = remove_URL(pairs[url_columns], url_columns)[url_columns]\npairs[preprocess_cols] = stop_remove(pairs[preprocess_cols], preprocess_cols)[preprocess_cols]\ndef fuzzy_similarity(df, cols_1, cols_2):\n    # length for cols_1 and cols_2 must be the same.\n    temp = pd.DataFrame()\n    for i in tqdm(range(len(cols_1))):\n        temp[f\"{cols_1[i]}_fuzzy\"] = df.progress_apply(lambda x: lev.ratio(x[cols_1[i]],x[cols_2[i]]), axis = 1)\n        gc.collect()\n    return temp    \ndef fuzzy_similarity_partial(df, cols_1, cols_2):\n    # length for cols_1 and cols_2 must be the same.\n    temp = pd.DataFrame()\n    for i in tqdm(range(len(cols_1))):\n        temp[f\"{cols_1[i]}_fuzzy_partial\"] = df.progress_apply(lambda x: fuzz.partial_ratio(x[cols_1[i]],x[cols_2[i]]), axis = 1)\n        gc.collect()\n    return temp    \n\ncol_1 = ['name_1','address_1','categories_1','url_1']\ncol_2 = ['name_2','address_2','categories_2','url_2']\ncol_1_partial = ['name_1','categories_1']\ncol_2_partial = ['name_2','categories_2']\ntemp = fuzzy_similarity(pairs[col_1+col_2], col_1, col_2)\npairs = pd.concat([pairs,temp], axis = 1)\ndel temp\ngc.collect()\ntemp = fuzzy_similarity_partial(pairs[col_1_partial+col_2_partial], col_1_partial, col_2_partial)\npairs = pd.concat([pairs,temp], axis = 1)\ndel temp\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-07-25T19:47:53.708228Z","iopub.execute_input":"2022-07-25T19:47:53.708597Z","iopub.status.idle":"2022-07-25T20:16:00.354568Z","shell.execute_reply.started":"2022-07-25T19:47:53.708564Z","shell.execute_reply":"2022-07-25T20:16:00.353211Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pairs.info()","metadata":{"execution":{"iopub.status.busy":"2022-07-25T20:16:00.356272Z","iopub.execute_input":"2022-07-25T20:16:00.356747Z","iopub.status.idle":"2022-07-25T20:16:00.379789Z","shell.execute_reply.started":"2022-07-25T20:16:00.356700Z","shell.execute_reply":"2022-07-25T20:16:00.378429Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pairs.to_pickle('./train_pairs.pkl')\n#pairs.to_pickle('./train_pairs_sample.pkl')\n#pairs.to_pickle('./train_pairs_test.pkl')","metadata":{"execution":{"iopub.status.busy":"2022-07-25T20:16:00.382134Z","iopub.execute_input":"2022-07-25T20:16:00.382486Z","iopub.status.idle":"2022-07-25T20:16:38.476208Z","shell.execute_reply.started":"2022-07-25T20:16:00.382456Z","shell.execute_reply":"2022-07-25T20:16:38.474979Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cols = ['address_1_missing',\n 'address_2_missing',\n 'categories_1_count',\n 'categories_1_count_diff',\n 'categories_2_count',\n 'city_1_count',\n 'city_1_count_diff',\n 'city_1_missing',\n 'city_2_count',\n 'city_2_missing',\n 'country_1_count',\n 'country_1_count_diff',\n 'distance',\n 'latitude_1',\n 'latitude_1_count',\n 'latitude_2_count',\n 'latitude_2_count_diff',\n 'longitude_1',\n 'longitude_1_count',\n 'longitude_2_count',\n 'longitude_2_count_diff',\n 'phone_1_missing',\n 'phone_2_missing',\n 'state_1_count',\n 'state_1_count_diff',\n 'state_2_count',\n 'url_1_missing',\n 'url_2_missing',\n 'zip_1_missing',\n 'zip_2_missing',\n 'name_1_fuzzy',\n 'name_1_fuzzy_partial',\n 'address_1_fuzzy',\n 'categories_1_fuzzy',\n 'categories_1_fuzzy_partial',\n 'url_1_fuzzy']\nids = ['id_1','id_2']","metadata":{"execution":{"iopub.status.busy":"2022-07-25T20:16:38.477507Z","iopub.execute_input":"2022-07-25T20:16:38.477913Z","iopub.status.idle":"2022-07-25T20:16:38.487450Z","shell.execute_reply.started":"2022-07-25T20:16:38.477882Z","shell.execute_reply":"2022-07-25T20:16:38.486266Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a href=\"./train_pairs.pkl\"> train_pairs pickle </a>","metadata":{"execution":{"iopub.status.busy":"2022-07-06T17:25:27.499569Z","iopub.execute_input":"2022-07-06T17:25:27.500362Z","iopub.status.idle":"2022-07-06T17:25:27.673150Z","shell.execute_reply.started":"2022-07-06T17:25:27.500335Z","shell.execute_reply":"2022-07-06T17:25:27.672183Z"}}}]}