{"cells":[{"metadata":{"_uuid":"40d42d8ae46d9ef62afbb5c73349e619ee376abd"},"cell_type":"markdown","source":"**You can download the resulting file at the bottom of the notebook or under the [code](https://www.kaggle.com/frankherfert/region-and-city-details-with-lat-lon-and-clusters/code) section of the Kernel.**"},{"metadata":{"_uuid":"77c33777fc64faf885051893cbb3c75892ea8915"},"cell_type":"markdown","source":"The Avito dataset provides information about the region and city an ad was placed in.\n\nWhile we can label encode those, the labels will not have any relationship to each other. Different machine learning algorithms can learn some similarities between the labels but this will likely only work if there are enough similar ads in each city.\n\nWith this preprocessing notebook we will add latitude, longitude and clusters for each city with the aim of helping our models to better group nearby cities."},{"metadata":{"_uuid":"e68318da040952884bed52693eb2659d1153736d"},"cell_type":"markdown","source":"# imports"},{"metadata":{"collapsed":true,"trusted":true,"_uuid":"5c32a36cdc6e18e0d1026d895c2038afd8076dad"},"cell_type":"code","source":"import pandas as pd\n\nimport time\n\nimport matplotlib.pyplot as plt\nimport seaborn as sns\ncolor = sns.color_palette()\n%matplotlib inline \n\nfrom sklearn import preprocessing\n\nimport string","execution_count":49,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"aef7b48c088d1a38d02ec4807e91917fa0fbfccc","collapsed":true},"cell_type":"code","source":"pd.set_option('display.max_columns', 300)\npd.set_option('max_colwidth',400)\npd.set_option('display.max_rows', 300)\n\nfrom IPython.core.display import display, HTML\ndisplay(HTML(\"<style>.container { width:95% !important; }</style>\")) # using this in your offline notebooks, will get rid of most of the side space\ndisplay(HTML(\"<style>table {float:left}</style>\")) # makes the changelog table nicer","execution_count":50,"outputs":[]},{"metadata":{"_uuid":"2674588eefc398be182fbd38abfbf1c2fe398bed"},"cell_type":"markdown","source":"# loading data"},{"metadata":{"trusted":true,"_uuid":"4e85f5f7b581a1f5544001d5c65e8410e061394e","collapsed":true},"cell_type":"code","source":"train = pd.read_csv('../input/avito-demand-prediction/train.csv')","execution_count":41,"outputs":[]},{"metadata":{"trusted":true,"collapsed":true,"_uuid":"42c04b28b40d9f2155538d386ce02f6e273e1930"},"cell_type":"code","source":"test = pd.read_csv('../input/avito-demand-prediction/test.csv')","execution_count":42,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bdeae56c48b79708be617679c7ecd481f4b3710b","collapsed":true},"cell_type":"code","source":"full = pd.concat([train, test], axis=0)","execution_count":43,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4cb86147e7beaf9a135a7c58ec8530ee62f273f8","collapsed":true},"cell_type":"code","source":"full.info(memory_usage=\"deep\")","execution_count":44,"outputs":[]},{"metadata":{"_uuid":"c49ce216ac1e817daad119180fe63bf75bfe5462"},"cell_type":"markdown","source":"# combine city and region"},{"metadata":{"trusted":true,"_uuid":"87093e763684a4014b26d17b02497c7fb4f8cb96","collapsed":true},"cell_type":"code","source":"%%time\nfull[\"city_region\"] = full.loc[:, [\"city\", \"region\"]].apply(lambda l: \" \".join(l), axis=1)","execution_count":12,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"44b4aa00c8ff4d8b7dd225c07780545172d06fba","collapsed":true},"cell_type":"code","source":"full.sample(5)","execution_count":13,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fefb67d494677085d4685166eaa639f597013641","collapsed":true},"cell_type":"code","source":"print(full.shape)\ncity_region_unique = full.drop_duplicates(subset=\"city_region\").reset_index(drop=True)\nprint(city_region_unique.shape)","execution_count":14,"outputs":[]},{"metadata":{"_uuid":"4ce782e3989011d019c99ead001615f835b68164"},"cell_type":"markdown","source":"# geocoder"},{"metadata":{"_uuid":"e76fc8526f949d764fc95e84c59fd8f136dd1687"},"cell_type":"markdown","source":"We can use the geocoders class from the geopy package to search through different services for  additional data"},{"metadata":{"trusted":true,"collapsed":true,"_uuid":"60e774439645db3b49baddadf9924307a508cc46"},"cell_type":"code","source":"from geopy import geocoders\n\n# api_key = \"\" # place your API key here if you want to access the API as often as you like.\n# g = geocoders.GoogleV3(api_key=api_key)\n\ng = geocoders.GoogleV3()","execution_count":18,"outputs":[]},{"metadata":{"_uuid":"ba57ed840d702e86426e950b9b3ceb87656a2e6c"},"cell_type":"markdown","source":"Calling the .geocode() function will return a search results from the Google Maps API in the specified language. Depending on the country, this includes different levels of geographical information.\n\nAS the Kaggle server has likely hit the API limit already, this part is commented out."},{"metadata":{"trusted":true,"_uuid":"ac013049c2e0085d227e109c2c5575e961c5d7e9","collapsed":true},"cell_type":"code","source":"# geocode = g.geocode(\"Самара Самарская область\", timeout=10, language=\"en\")","execution_count":20,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"30972d05b1bf668c56f9029fba9dffac12addcfd","collapsed":true},"cell_type":"code","source":"# geocode.raw","execution_count":21,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"be577dbfb6d4522577b2df64a1d507db849945f4","collapsed":true},"cell_type":"code","source":"# print(geocode.address)\n# print(geocode.latitude)\n# print(geocode.longitude)","execution_count":22,"outputs":[]},{"metadata":{"_uuid":"ab85f74a9c092702a62d1f3bc6e25b2d0e616788"},"cell_type":"markdown","source":"Loop through all cities and extract the latitude and longitude from the geocode results.\n\nDue to API limits, we will load the resulting file from a local run."},{"metadata":{"trusted":true,"_uuid":"8f20df1b0e89bec42480323dbcf02850c4c41636","collapsed":true},"cell_type":"code","source":"city_region_unique = pd.read_csv(\"../input/avito-russian-region-cities/avito_region_city_features.csv\")\nprint(city_region_unique.shape)","execution_count":23,"outputs":[]},{"metadata":{"collapsed":true,"trusted":true,"_uuid":"7f2f9c055430ae2d245a402a0264a87efe6ad3a9"},"cell_type":"code","source":"# city_region_unique[\"latitude\"] = np.nan","execution_count":24,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9fa111f18b92b66f441aace5802fc4d4e2cff5cf","collapsed":true},"cell_type":"code","source":"# %%time\n# print(\"searching\", len(city_region_unique), \"entries\")\n\n# for index, row in city_region_unique.loc[city_region_unique[\"latitude\"].isnull(), \n#                                          :].iterrows():\n    \n#     search = city_region_unique.loc[index, \"city_region\"]\n#     try:\n#         geocode = g.geocode(search, timeout=10, language=\"en\")\n\n#         city_region_unique.loc[index, 'latitude'] = geocode.latitude\n#         city_region_unique.loc[index, 'longitude'] = geocode.longitude\n#     except:\n#         city_region_unique.loc[index, 'latitude'] = -999\n#         city_region_unique.loc[index, 'longitude'] = -999\n        \n#     time.sleep(.1)\n    \n#     if index%10==0:\n#         print(str(index).ljust(6), end=\" \")\n#     if (index+1)%120==0:\n#         print(\"\")\n\n# print(\"\\n done\")","execution_count":25,"outputs":[]},{"metadata":{"_uuid":"966b2feef9503991ab34c548f93ee15b0d0dbc56"},"cell_type":"markdown","source":"# Clusters"},{"metadata":{"_uuid":"683e529c6aeb5ce5813707ff69d6a34f8a5e6baa"},"cell_type":"markdown","source":"A kmeans cluster will not do much good here because it will create equally sized area clusters, which doesn't reflect the real world distribution of cities.\n\nA density based cluster will provide better groups and also detec outliers (cities that are too far away from everything else)."},{"metadata":{"_uuid":"eda151aa41dd69e92a092c5eb2376f1e7dd11044"},"cell_type":"markdown","source":"[HDBSCAN](https://github.com/scikit-learn-contrib/hdbscan) is a great clustering algorithm and has sklearn integration as well. \n\nIt allows cluster creation with a minimum entry count per cluster compared to a specified number of clusters as in kmeans. \n\nIt can be installed with `conda install -c conda-forge hdbscan`"},{"metadata":{"trusted":true,"_uuid":"3c36ab2250ddaedc77b42c5efb395a20bd79ff68","collapsed":true},"cell_type":"code","source":"# The library in the Kaggle kernel seems to be miscompiled\n# import hdbscan","execution_count":27,"outputs":[]},{"metadata":{"_uuid":"f080e8066bceb1779834a3ca38edfd8c0fe2e9b0"},"cell_type":"markdown","source":"## cluster size 5"},{"metadata":{"trusted":true,"collapsed":true,"_uuid":"334b4e4b0be5d1302dd62c1367e7af74362395c8"},"cell_type":"code","source":"# city_region_unique[\"lat_lon_hdbscan_cluster_05_03\"] = hdbscan.HDBSCAN(min_cluster_size=5, min_samples=3\n#                                                                      ).fit_predict(city_region_unique.loc[:, [\"latitude\", \"longitude\"]])","execution_count":28,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2a24ab58cf72c2d8684fdecc4fd82fc4b92eb67c","collapsed":true},"cell_type":"code","source":"print(\"number of clusters:\", len(city_region_unique[\"lat_lon_hdbscan_cluster_05_03\"].unique()))","execution_count":29,"outputs":[]},{"metadata":{"_uuid":"a17a60426d5240a7be7cdf620f0f6d2eee351031"},"cell_type":"markdown","source":"A lot of cities are located in the west of Russia with a big orange cluster around the Moscow region, a big blue cluster towards the the Georgian border, a single pink cluster at 55,20 around Kaliningrad and some scattered cities in the north-east"},{"metadata":{"trusted":true,"_uuid":"382ce140afb923671e9c03b4060c81f74ca7507a","collapsed":true},"cell_type":"code","source":"sns.lmplot(data=city_region_unique, x=\"longitude\", y=\"latitude\", hue=\"lat_lon_hdbscan_cluster_05_03\", \n           size=10, legend=False, fit_reg=False)","execution_count":48,"outputs":[]},{"metadata":{"_uuid":"aea0c1f8f6236e605151a48a814e1bb58373eb4d"},"cell_type":"markdown","source":"## cluster size 10"},{"metadata":{"_uuid":"36d61632d513f68ad0d352100db622e42082a1c7"},"cell_type":"markdown","source":"Let's add a few more clusters with different parameters"},{"metadata":{"trusted":true,"_uuid":"177f1442da091e23a31e901c52b22c1d11c5ab2a","collapsed":true},"cell_type":"code","source":"# city_region_unique[\"lat_lon_hdbscan_cluster_10_03\"] = hdbscan.HDBSCAN(min_cluster_size=10, min_samples=3\n#                                                                      ).fit_predict(city_region_unique.loc[:, [\"latitude\", \"longitude\"]])\nprint(\"number of clusters:\", len(city_region_unique[\"lat_lon_hdbscan_cluster_10_03\"].unique()))","execution_count":31,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e877f9372f8cf56bdae800d28de79ae14da1df6c","collapsed":true},"cell_type":"code","source":"sns.lmplot(data=city_region_unique, x=\"longitude\", y=\"latitude\", hue=\"lat_lon_hdbscan_cluster_10_03\", \n           size=10, legend=False, fit_reg=False)","execution_count":32,"outputs":[]},{"metadata":{"_uuid":"7bbcb8cba475d33d01da027d93b08e5365ff0225"},"cell_type":"markdown","source":"## cluster size 20"},{"metadata":{"trusted":true,"_uuid":"38e10464a9dedbc02c29dda440c4be324ee2ae41","collapsed":true},"cell_type":"code","source":"# city_region_unique[\"lat_lon_hdbscan_cluster_20_03\"] = hdbscan.HDBSCAN(min_cluster_size=20, min_samples=3\n#                                                                      ).fit_predict(city_region_unique.loc[:, [\"latitude\", \"longitude\"]])\nprint(\"number of clusters:\", len(city_region_unique[\"lat_lon_hdbscan_cluster_20_03\"].unique()))","execution_count":33,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d3bdb04c92073f3d5d9bbcb7f95e4311a8e9576a","collapsed":true},"cell_type":"code","source":"sns.lmplot(data=city_region_unique, x=\"longitude\", y=\"latitude\", hue=\"lat_lon_hdbscan_cluster_20_03\", \n           size=10, legend=False, fit_reg=False)","execution_count":34,"outputs":[]},{"metadata":{"_uuid":"aa5f05f9722673068a46f84710ed92173bbcf7c2"},"cell_type":"markdown","source":"# label encoding"},{"metadata":{"_uuid":"25695c1875e3d73fa56df4a389d3f9bbf2370ecf"},"cell_type":"markdown","source":"The cluster IDs will likely be more accurate than simple label-encoded city-IDs but just in case we will add in regular label encodings as well."},{"metadata":{"collapsed":true,"trusted":true,"_uuid":"fc450016a5ca0c5e4c61ed5937f73d109a40bb38"},"cell_type":"code","source":"city_region_unique[\"region_id\"]      = preprocessing.LabelEncoder().fit_transform(city_region_unique[\"region\"].values)\ncity_region_unique[\"city_region_id\"] = preprocessing.LabelEncoder().fit_transform(city_region_unique[\"city_region\"].values)","execution_count":35,"outputs":[]},{"metadata":{"_uuid":"c745b673e9b00a7692c9cedee5095455d97648e1"},"cell_type":"markdown","source":"# Saving features"},{"metadata":{"trusted":true,"_uuid":"bcd791bfab382d92bbac692fa8f9a7c48e63000e","collapsed":true},"cell_type":"code","source":"city_region_unique.head()","execution_count":36,"outputs":[]},{"metadata":{"trusted":true,"collapsed":true,"_uuid":"5937901a9718fd2e2769c8214353e35cf536f05e"},"cell_type":"code","source":"city_region_unique.to_csv(\"avito_region_city_features.csv\", index=False)","execution_count":37,"outputs":[]},{"metadata":{"_uuid":"8881a312f5709ccfb8d5831c37e1f5271a615be6"},"cell_type":"markdown","source":"As most people don't have access to a Google Maps API key, the generated features will be saved in a csv file for everyone to download.\n\nYou can download it from the Kernel or from the Kaggle datasets site: https://www.kaggle.com/frankherfert/avito-russian-region-cities"},{"metadata":{"_uuid":"5e740b0314fdbaad8be704aff655655213b6bb90"},"cell_type":"markdown","source":"You can add these features to your regular data this way:"},{"metadata":{"trusted":true,"_uuid":"07c8eb755405ae670569b3c5f861270f9bf8bcd9","collapsed":true},"cell_type":"code","source":"print(\"before:\", full.shape)\nfull = pd.merge(left=full, right=city_region_unique, how=\"left\", on=[\"region\", \"city\"])\nprint(\"after :\", full.shape)","execution_count":45,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"30ad81f148a957441cfe0e9fffd1bb8c0e2f5011","collapsed":true},"cell_type":"code","source":"full.loc[:, [\"item_id\", \"user_id\", \"region\", \"city\", \"latitude\", \"longitude\", \"lat_lon_hdbscan_cluster_05_03\"]].sample(5)","execution_count":46,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.5","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"toc":{"base_numbering":1,"nav_menu":{},"number_sections":true,"sideBar":true,"skip_h1_title":false,"title_cell":"Table of Contents","title_sidebar":"Contents","toc_cell":false,"toc_position":{"height":"calc(100% - 180px)","left":"10px","top":"150px","width":"286px"},"toc_section_display":true,"toc_window_display":true}},"nbformat":4,"nbformat_minor":1}