{"cells":[
 {
  "cell_type": "code",
  "execution_count": null,
  "metadata": {
   "collapsed": false
  },
  "outputs": [],
  "source": "import pandas as pd"
 },
 {
  "cell_type": "markdown",
  "metadata": {},
  "source": "## Doing this type of analysis is against the competition rules\n\nIt has been pointed out by the competition admin that incorporating external data about distances between cities is against the rules.\n\nSo please don't use this in any way for building your models!\n\n\n\n## The locations puzzle\n\nExpedia presented us with a dataset where countries and cities are hidden behind integer codes. Is it possible to find out which city is which?\n\nLet's grab our pandas and find out :).\n\nRead in a few lines to get a list of columns."
 },
 {
  "cell_type": "code",
  "execution_count": null,
  "metadata": {
   "collapsed": false
  },
  "outputs": [],
  "source": "train = pd.read_csv('../input/train.csv', nrows=10)\ntrain.columns"
 },
 {
  "cell_type": "markdown",
  "metadata": {},
  "source": "Columns related to user location are:\n\n- `user_location_country`\n- `user_location_region`\n- `user_location_city`\n\nColumns related to hotel location are:\n\n- `hotel_country`\n- `hotel_market`\n- `srch_destination_id`\n\nFinally, the `orig_destination_distance` column shows us the distance in miles between the user and their chosen hotel.\n\nWe should note that hotel countries and user location countries are encoded differently, meaning that the same country will have different numbers in these two columns. I will not go into `srch_destination_id`s yet because they might represent different locations within the same city so this division is probably too fine. On the other hand the `hotel_market`s correspond to nonoverlapping regions all over the globe, and large cities are covered by their own `hotel_market`s so this is a nice match to `user_location_city` column.\n\nNow let's read in a million rows using only columns relating to this task. Drop rows where distance is undefined."
 },
 {
  "cell_type": "code",
  "execution_count": null,
  "metadata": {
   "collapsed": false
  },
  "outputs": [],
  "source": "train = pd.read_csv('../input/train.csv', usecols = ['posa_continent', \n       'user_location_country',\n       'user_location_region', 'user_location_city',\n       'orig_destination_distance','hotel_continent', \n       'hotel_country', 'hotel_market'], nrows=1000000).dropna()"
 },
 {
  "cell_type": "markdown",
  "metadata": {},
  "source": "## A mapping of user and hotel countries\n\nIf a user books a hotel in their own city then we should see a very short distance in the corresponding dataset row. So we can look at which user and hotel countries have the lowest minimum distances between them and deduce that these pairs probably refer to the same actual country.\n\nLet's group rows by user country and hotel country and look at the distances."
 },
 {
  "cell_type": "code",
  "execution_count": null,
  "metadata": {
   "collapsed": false
  },
  "outputs": [],
  "source": "distaggs = (train.groupby(['user_location_country','hotel_country'])\n            ['orig_destination_distance']\n            .agg(['min','mean','max','count']))\ndistaggs.sort_values(by='min').head(20)"
 },
 {
  "cell_type": "markdown",
  "metadata": {},
  "source": "So we see a huge number of rows belonging to user country 66 and hotel country 50. It's probably the USA. Then there are some more pairs with low distances.\n\nFirst repeated row is user country 205 and hotel country 50 again. And the minimum distance is 3 miles here - larger than 0.0056 we saw in the first rows. So user country 205 must be some neighbor country, Canada or Mexico.\n\nThen there's the repeat appearance of user country 66 with travels to hotel countries 8 and 198 - those are probably again Canada and Mexico. \n\nBy the end of the table shown minimum distances go up to almost 45 miles, so this criterion does not work so obviously any more - the pairs might be just neighboring countries, and not necessarily the same one.\n\n## user_location_country 66\n\nHow many regions does this country have?"
 },
 {
  "cell_type": "code",
  "execution_count": null,
  "metadata": {
   "collapsed": false
  },
  "outputs": [],
  "source": "c66 = train[train.user_location_country==66]\nc66.user_location_region.unique().shape"
 },
 {
  "cell_type": "markdown",
  "metadata": {},
  "source": "51 looks fitting for the USA.\n\nLet's look at trips within this country.\n\nThe USA have Hawaii which is a popular tourist location and also very far away from other regions. I'll group the data by user_location_region and hotel_market and take a look at maximum distances."
 },
 {
  "cell_type": "code",
  "execution_count": null,
  "metadata": {
   "collapsed": false
  },
  "outputs": [],
  "source": "c66in = c66[c66.hotel_country==50]\n(c66in.groupby(['user_location_region','hotel_market'])['orig_destination_distance']\n      .agg(['min','mean','max','count'])\n      .sort_values(by='max',ascending=False).head(20))"
 },
 {
  "cell_type": "markdown",
  "metadata": {},
  "source": "Looks like we have a lot of hotel_market values 212, 214 and a couple of 213 for good measure. These could be our paradise islands.\n\nLet's look at distances from hotel_market 212 to different user cities in the USA sorting by popularity."
 },
 {
  "cell_type": "code",
  "execution_count": null,
  "metadata": {
   "collapsed": false
  },
  "outputs": [],
  "source": "hawaii = (c66in[c66in.hotel_market == 212]\n          .groupby(['user_location_region','user_location_city'])\n          ['orig_destination_distance']\n          .agg(['min','mean','max','count'])\n          .sort_values(by='count',ascending=False))\nhawaii.head(10)"
 },
 {
  "cell_type": "markdown",
  "metadata": {},
  "source": "Looks like we caught some very local trips in row 4. So region 246 is probably Hawaii.\n\nThe site http://www.distancefromto.net/ tells me that distances from Honolulu to other cities are:\n\n- San Francisco - 2397.40 miles (the second line probably)\n- Los Angeles - 2562.87 miles (could be the first line)\n- New York - 4965.20 miles (could be the third)\n\nSo region 174 must be California and 348 New York with 48862 being New York city.\n\nLet's look at trips from New York city."
 },
 {
  "cell_type": "code",
  "execution_count": null,
  "metadata": {
   "collapsed": false
  },
  "outputs": [],
  "source": "fromny = (c66in[(c66in.user_location_region == 348) & \n                (c66in.user_location_city == 48862)]\n          .groupby(['hotel_market'])\n          ['orig_destination_distance']\n          .agg(['min','mean','max','count'])\n          .sort_values(by='count',ascending=False))\nfromny.head(10)"
 },
 {
  "cell_type": "markdown",
  "metadata": {},
  "source": "We can see that New York city itself is probably hotel_market 675.\n\nDistances from New York:\n\n- to Miami - 1093.57 miles -> hotel_market 701\n- to Las Vegas - 2230.03 miles -> hotel_market 628\n- to Los Angeles - 2448.30 miles -> hotel_market 365\n- to San Francisco - 2568.57 miles -> hotel_market 1230\n- to Chicago - 711.83 miles -> Chicago is hotel_market 637\n- to Washington - 203.78 miles -> Washington might be hotel_market 191 (?)\n- to Philadelphia - 80.63 miles -> hotel_market 623 (?)\n\nWe already know Los Angeles as a user_location_city. Let's do a check by confirming that trips from that city id to hotel market 365 have small distances."
 },
 {
  "cell_type": "code",
  "execution_count": null,
  "metadata": {
   "collapsed": false
  },
  "outputs": [],
  "source": "(c66in[(c66in.hotel_market==365) & \n       (c66in.user_location_region==174) & \n       (c66in.user_location_city==24103)]\n ['orig_destination_distance'].describe())"
 },
 {
  "cell_type": "markdown",
  "metadata": {},
  "source": "This looks about right!\n\n## Going international\n\nLet's check international trips to and from New York."
 },
 {
  "cell_type": "code",
  "execution_count": null,
  "metadata": {
   "collapsed": false
  },
  "outputs": [],
  "source": "tony = (train[(train.hotel_market == 675) & (train.user_location_country != 66)]\n        .groupby(['user_location_country','user_location_region', 'user_location_city'])\n        ['orig_destination_distance']\n        .agg(['min','mean','max','count'])\n        .sort_values(by='count',ascending=False))\ntony.head(10)"
 },
 {
  "cell_type": "markdown",
  "metadata": {},
  "source": "People are flying to New York a lot from country 205. \n\nMost popular city is 342 miles away so that must be Toronto (distance from NY 342.42 miles). So user_location_country 205 must be Canada. It is also hotel_country 198 as seen from the very first table. We can figure out other canadian cities and regions by their distances from NY.\n\nNext is user_location_country 1 with mean distance 4283 miles. That would probably be somewhere in Europe. Some poking around the map brings up Rome at 4286.00 miles from NY. Then there should be another italian city at 4019 miles. Milan comes up with 4020.99 miles. So maybe user_location_country 1 is Italy.\n\nNext user_location_country is 215, looks like this is Mexico, with distance to Mexico city being 2089.76 miles. Then Mexico is also hotel_country 8.\n\nHow about the trips from New York?"
 },
 {
  "cell_type": "code",
  "execution_count": null,
  "metadata": {
   "collapsed": false
  },
  "outputs": [],
  "source": "fromny = (train[(train.hotel_country != 50) & \n                (train.user_location_country == 66) &\n                (train.user_location_region == 348) &\n                (train.user_location_city == 48862)]\n        .groupby(['hotel_country','hotel_market'])\n        ['orig_destination_distance']\n        .agg(['min','mean','max','count'])\n        .sort_values(by='count',ascending=False))\nfromny.head(10)"
 },
 {
  "cell_type": "markdown",
  "metadata": {},
  "source": "First row - with hotel_market 110 - is some place in Mexico - there's a city called Cancún at 1548.30 miles from NY.\n\nSecond line looks like London (3465.05 miles), so hotel_country 70 UK, hotel_market 19 London.\n\nLines 3-5 judging by the distance should be somewhere in the Caribbean.\n\nThe next line might be Paris (3631.16 miles), so hotel_country 204 France, hotel_market 27 Paris.\n\n## Results\n\nIt looks like it's completely possible to deanonymize the countries and cities in this dataset. At least the popular ones. The more countries and cities we identify the easier the subsequent task becomes. We could sort of triangulate the locations yet uncovered using distances to already known locations.\n\nWays to use this information:\n\n- classify trips as home or abroad\n- classify destinations as sea-side, ski resorts, historical cities and so on.\n- what else?\n\nHere is a list of countries and cities matched so far. \n\n*removed to not tempt anyone*"
 }
],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"}}, "nbformat": 4, "nbformat_minor": 0}