{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Wifi feature as another source of leakage\nPreviously, I have shown that [the paths in train and test set are divided from a single measurement](https://www.kaggle.com/tomooinubushi/postprocessing-based-on-leakage).\nWhen I examined the data in detail, I found a much clearer evidence of the leakage in wifi data.\n\nI will show two things in this notebook.\n\n* The same wifi records are shared among train and test dataset.\n* From this, I could partially recover user ID, which could be a predictor of the waypoints.\n\n\nI use some codes and ideas from following notebooks. Thank you very much.\n\n* https://www.kaggle.com/kenmatsu4/feature-store-for-indoor-location-navigation\n* https://www.kaggle.com/jiweiliu/fix-the-timestamps-of-test-data-using-dask"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"import json\nimport re\nimport gc\nimport pickle\nimport itertools\nimport pandas as pd\nimport numpy as np\nfrom glob import glob\nfrom datetime import datetime as dt\nfrom pathlib import Path\nfrom tqdm import tqdm\nimport datetime\nts_conv = np.vectorize(datetime.datetime.fromtimestamp) # ut(10 digit) -> date\n\n# pandas settings -----------------------------------------\npd.set_option(\"display.max_colwidth\", 100)\npd.set_option(\"display.max_rows\", None)\npd.set_option(\"display.max_columns\", None)\npd.options.display.float_format = '{:,.5f}'.format\n\n# Graph drawing -------------------------------------------\nimport matplotlib\nfrom matplotlib import font_manager\nimport matplotlib.pyplot as plt\nimport matplotlib.cm as cm\nfrom matplotlib import rc\nfrom matplotlib_venn import venn2, venn2_circles\nfrom matplotlib import animation as ani\nfrom IPython.display import Image\nfrom pylab import imread\n\nplt.rcParams[\"patch.force_edgecolor\"] = True\nfrom IPython.display import display # Allows the use of display() for DataFrames\nimport seaborn as sns\nsns.set(style=\"whitegrid\", palette=\"muted\", color_codes=True)\nsns.set_style(\"whitegrid\", {'grid.linestyle': '--'})\nred = sns.xkcd_rgb[\"light red\"]\ngreen = sns.xkcd_rgb[\"medium green\"]\nblue = sns.xkcd_rgb[\"denim blue\"]\n\n%matplotlib inline\n%config InlineBackend.figure_format='retina'\n\n# ML -------------------------------------------\nfrom sklearn.preprocessing import LabelEncoder\n\n\nimport dill\nfrom collections import defaultdict, OrderedDict\nfrom scipy.spatial import distance","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"def unpickle(filename):\n    with open(filename, 'rb') as fo:\n        p = pickle.load(fo)\n    return p\n\ndef to_pickle(filename, obj):\n    with open(filename, 'wb') as f:\n        pickle.dump(obj, f, -1)\n\n\n\nclass FeatureStore():\n    \n    # necessayr to re-check\n    floor_convert = {'1F' :  0, '2F' : 1, '3F' : 2, '4F' : 3, '5F' : 4, \n                     '6F' : 5, '7F' : 6, '8F' : 7, '9F' : 8,\n                     'B'  : -1, 'B1' : -1, 'B2' : -2, 'B3' : -3, \n                     'BF' : -1, 'BM' : -1, \n                     'F1' : 0, 'F2' : 1, 'F3' : 2, 'F4' : 3, 'F5' : 4, \n                     'F6' : 5, 'F7' : 6, 'F8' : 7, 'F9' : 8, 'F10': 9,\n                     'L1' : 0, 'L2' : 1, 'L3' : 2, 'L4' : 3, 'L5' : 4, \n                     'L6' : 5, 'L7' : 6, 'L8' : 7, 'L9' : 8, 'L10': 9, \n                     'L11': 10,\n                     'G'  : 0, 'LG1': 0, 'LG2': 1, 'LM' : 0, 'M'  : 0, \n                     'P1' : 0, 'P2' : 1,}\n    \n    df_types = ['accelerometer',\n                'accelerometer_uncalibrated',\n                'beacon',\n                'gyroscope',\n                'gyroscope_uncalibrated',\n                'magnetic_field',\n                'magnetic_field_uncalibrated',\n                'rotation_vector',\n                'waypoint',\n                'wifi']\n    \n    # https://github.com/location-competition/indoor-location-competition-20\n    df_type_cols = {'accelerometer': [\"timestamp\", \"x\", \"y\", \"z\", \"accuracy\"],\n                'accelerometer_uncalibrated': [\"timestamp\", \"x\", \"y\", \"z\", \n                                               \"x2\", \"y2\", \"z2\", \"accuracy\" ],\n                'beacon': [\"timestamp\", \"uuid\", \"major_id\", \"minor_id\", \"tx_power\", \n                           \"rssi\", \"distance\", \"mac_addr\", \"timestamp2\"],\n                'gyroscope': [\"timestamp\", \"x\", \"y\", \"z\", \"accuracy\"],\n                'gyroscope_uncalibrated': [\"timestamp\", \"x\", \"y\", \"z\", \n                                           \"x2\", \"y2\", \"z2\", \"accuracy\" ],\n                'magnetic_field': [\"timestamp\", \"x\", \"y\", \"z\", \"accuracy\"],\n                'magnetic_field_uncalibrated': [\"timestamp\", \"x\", \"y\", \"z\", \n                                                \"x2\", \"y2\", \"z2\", \"accuracy\" ],\n                'rotation_vector': [\"timestamp\", \"x\", \"y\", \"z\", \"accuracy\"],\n                'waypoint': [\"timestamp\", \"x\", \"y\"],\n                'wifi': [\"timestamp\", \"ssid\", \"bssid\",\"rssi\",\"frequency\",\n                         \"last_seen_timestamp\",]}\n\n    dtype_dict = {}\n    dtype_dict[\"accelerometer\"] = {\"timestamp\":int, \"x\":float, \"y\":float, \"z\":float, \n                                   \"accuracy\":int}\n    dtype_dict[\"accelerometer_uncalibrated\"] = {\"timestamp\":int, \"x\":float, \"y\":float, \n                                                \"z\":float, \"x2\":float, \"y2\":float, \n                                                \"z2\":float, \"accuracy\":int}\n    dtype_dict[\"beacon\"] = {\"timestamp\":int, \"uuid\":str, \"major_id\":str, \n                            \"minor_id\":str, \"tx_power\":int,  \"rssi\":int, \n                            \"distance\":float, \"mac_addr\":str, \"timestamp2\":int}\n    dtype_dict[\"gyroscope\"] = {\"timestamp\":int, \"x\":float, \"y\":float, \"z\":float, \n                               \"accuracy\":int}\n    dtype_dict[\"gyroscope_uncalibrated\"] = {\"timestamp\":int, \"x\":float, \"y\":float, \n                                            \"z\":float, \"x2\":float, \"y2\":float, \n                                            \"z2\":float, \"accuracy\":int}\n    dtype_dict[\"magnetic_field\"] = {\"timestamp\":int, \"x\":float, \"y\":float, \n                                    \"z\":float, \"accuracy\":int}\n    dtype_dict[\"magnetic_field_uncalibrated\"] = {\"timestamp\":int, \"x\":float, \n                                                 \"y\":float, \"z\":float, \"x2\":float, \n                                                 \"y2\":float, \"z2\":float, \"accuracy\":int}\n    dtype_dict[\"rotation_vector\"] = {\"timestamp\":int, \"x\":float, \"y\":float, \n                                     \"z\":float, \"accuracy\":int}\n    dtype_dict[\"waypoint\"] = {\"timestamp\":int, \"x\":float, \"y\":float, \"z\":float}\n    dtype_dict[\"wifi\"] = {\"timestamp\":int, \"ssid\":str, \"bssid\":str,\n                          \"rssi\":int,\"frequency\":int, \"last_seen_timestamp\":int}\n\n    def __init__(self, site_id, floor, path_id, \n                 input_path=\"../input/indoor-location-navigation/\",\n                 save_path=\"../mid\"):\n        self.site_id = site_id.strip()\n        self.floor = floor.strip()\n        self.n_floor = self.floor_convert[self.floor]\n        self.path_id = path_id.strip()\n        \n        self.input_path = input_path\n        assert Path(input_path).exists(), f\"input_path do not exist: {input_path}\"\n        \n        self.save_path = save_path\n        Path(save_path).mkdir(parents=True, exist_ok=True)\n        \n        self.site_info = SiteInfo(site_id=self.site_id, floor=self.floor, input_path=self.input_path)\n        \n    def _flatten(self, l):\n        return list(itertools.chain.from_iterable(l))\n    \n    def multi_line_spliter(self, s):\n        matches = re.finditer(\"TYPE_\", s)\n        matches_positions = [match.start() for match in matches]\n        split_idx = [0] + [matches_positions[i]-14 for i in range(1, len(matches_positions))] + [len(s)]\n        return [s[split_idx[i]:split_idx[i+1]] for i in range(len(split_idx)-1)]\n    \n    def load_df(self, ):\n        path = str(Path(self.input_path)/f\"train/{self.site_id}/{self.floor}/{self.path_id}.txt\")\n        with open(path) as f:\n            data = f.readlines()\n        \n        modified_data = []\n        for s in data:\n            if s.count(\"TYPE_\")>1:\n                lines = self.multi_line_spliter(s)\n                modified_data.extend(lines)\n            else:\n                modified_data.append(s)\n        del data\n        self.meta_info_len = len([d for d in modified_data if d[0]==\"#\"])\n        self.meta_info_df = pd.DataFrame([m.replace(\"\\n\", \"\").split(\":\") \n                                          for m in self._flatten([d.split(\"\\t\") \n                                                                  for d in modified_data if d[0]==\"#\"]) if m!=\"#\"])\n\n        data_df = pd.DataFrame([d.replace(\"\\n\", \"\").split(\"\\t\") for d in modified_data if d[0]!=\"#\"])\n        for dt in self.df_types:\n            # select data type\n            df_s = data_df[data_df[1]==f\"TYPE_{dt.upper()}\"]\n            if len(df_s)==0:\n                setattr(self, dt, pd.DataFrame(columns=self.df_type_cols[dt]))\n            else:\n                # remove empty cols\n                na_info = df_s.isna().sum(axis=0) == len(df_s)\n                df_s = df_s[[i for i in na_info[na_info==False].index if i!=1]].reset_index(drop=True)\n                \n                if len(df_s.columns)!=len(self.df_type_cols[dt]):\n                    df_s.columns = self.df_type_cols[dt][:len(df_s.columns)]\n                else:\n                    df_s.columns = self.df_type_cols[dt]\n            \n                # set dtype          \n                for c in df_s.columns:\n                    df_s[c] = df_s[c].astype(self.dtype_dict[dt][c])\n                                     \n                # set DataFrame to attr\n                setattr(self, dt, df_s)\n    \n    def get_site_info(self, keep_raw=False):\n        self.site_info.get_site_info(keep_raw=keep_raw)\n            \n    def load_all_data(self, keep_raw=False):     \n        self.load_df()\n        self.get_site_info(keep_raw=keep_raw)\n        \n    def __getitem__(self, item):\n        if item in self.df_types:\n            return getattr(self, item)\n        else:\n            return None\n    \n    def save(self, ):\n        # to be implemented\n        pass\n    \n    \nclass SiteInfo():\n    def __init__(self, site_id, floor, input_path=\"../input/indoor-location-navigation/\"):\n        self.site_id = site_id\n        self.floor = floor\n        self.input_path = input_path\n        assert Path(input_path).exists(), f\"input_path do not exist: {input_path}\"\n        \n    def get_site_info(self, keep_raw=False):\n        floor_info_path = f\"{self.input_path}/metadata/{self.site_id}/{self.floor}/floor_info.json\"\n        with open(floor_info_path, \"r\") as f:\n            self.floor_info = json.loads(f.read())\n            self.site_height = self.floor_info[\"map_info\"][\"height\"]\n            self.site_width = self.floor_info[\"map_info\"][\"width\"]\n            if not keep_raw:\n                del self.floor_info\n            \n        geojson_map_path = f\"{self.input_path}/metadata/{self.site_id}/{self.floor}/geojson_map.json\"\n        with open(geojson_map_path, \"r\") as f:\n            self.geojson_map = json.loads(f.read())\n            self.map_type = self.geojson_map[\"type\"]\n            self.features = self.geojson_map[\"features\"]\n            \n            self.floor_coordinates = self.features[0][\"geometry\"][\"coordinates\"]\n            self.store_coordinates = [self.features[i][\"geometry\"][\"coordinates\"] \n                                          for i in range(1, len(self.features))]\n                \n            if not keep_raw:\n                del self.geojson_map\n    \n    def show_site_image(self):\n        path = f\"{self.input_path}/metadata/{self.site_id}/{self.floor}/floor_image.png\"\n        plt.imshow(imread(path), extent=[0, self.site_width, 0, self.site_height])\n\n    def draw_polygon(self, size=8, only_floor=False):\n\n        fig = plt.figure()\n        ax = plt.subplot(111)\n            \n        xmax, xmin, ymax, ymin = self._draw(self.floor_coordinates, ax, calc_minmax=True)\n        if not only_floor:\n            self._draw(self.store_coordinates, ax, fill=True)\n        plt.legend([])\n        \n        xrange = xmax - xmin\n        yrange = ymax - ymin\n        ratio = yrange / xrange\n        \n        self.x_size = size\n        self.y_size = size*ratio\n\n        fig.set_figwidth(size)\n        fig.set_figheight(size*ratio)\n        # plt.show()\n        return ax\n        \n    def _draw(self, coordinates, ax, fill=False, calc_minmax=False):\n        xmax, ymax = -np.inf, -np.inf\n        xmin, ymin = np.inf, np.inf\n        for i in range(len(coordinates)):\n            ndim = np.ndim(coordinates[i])\n            if ndim==2:\n                corrd_df = pd.DataFrame(coordinates[i])\n                if fill:\n                    ax.fill(corrd_df[0], corrd_df[1], alpha=0.7)\n                else:\n                    corrd_df.plot.line(x=0, y=1, style=\"-\", ax=ax)\n                        \n                if calc_minmax:\n                    xmax = max(xmax, corrd_df[0].max())\n                    xmin = min(xmin, corrd_df[0].min())\n\n                    ymax = max(ymax, corrd_df[1].max())\n                    ymin = min(ymin, corrd_df[1].min())\n            elif ndim==3:\n                for j in range(len(coordinates[i])):\n                    corrd_df = pd.DataFrame(coordinates[i][j])\n                    if fill:\n                        ax.fill(corrd_df[0], corrd_df[1], alpha=0.6)\n                    else:\n                        corrd_df.plot.line(x=0, y=1, style=\"-\", ax=ax)\n                        \n                    if calc_minmax:\n                        xmax = max(xmax, corrd_df[0].max())\n                        xmin = min(xmin, corrd_df[0].min())\n\n                        ymax = max(ymax, corrd_df[1].max())\n                        ymin = min(ymin, corrd_df[1].min())\n            else:\n                assert False, f\"ndim of coordinates should be 2 or 3: {ndim}\"\n        if calc_minmax:\n            return xmax, xmin, ymax, ymin\n        else:\n            return None","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"def pickle_dump_dill(obj, path):\n    with open(path, mode='wb') as f:\n        dill.dump(obj, f)\n\n\ndef pickle_load_dill(path):\n    with open(path, mode='rb') as f:\n        data = dill.load(f)\n        return data","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"def read_txt(file):\n    with open(file) as f:\n        txt = f.readlines()\n\n    modified_data = []\n    for s in txt:\n        if s.count(\"TYPE_\") > 1:\n            lines = multi_line_spliter(s)\n            modified_data.extend(lines)\n        else:\n            modified_data.append(s)\n    return modified_data\n\n\ndef _flatten(l):\n    return list(itertools.chain.from_iterable(l))\n\nsample_sub = pd.read_csv('../input/indoor-location-navigation/sample_submission.csv')\nsample_sub = sample_sub[\"site_path_timestamp\"].apply(\n    lambda x: pd.Series(x.split(\"_\")))\nsample_sub.columns = [\"site_id\", \"path_id\", \"timestamp\"]\n\ndef get_feature_test(site_id, path_id, sample_sub, input_path=\"../input/indoor-location-navigation/\"):\n    file = f\"{input_path}/test/{path_id}.txt\"\n    content = read_txt(file)\n    data_df = pd.DataFrame([d.replace(\"\\n\", \"\").split(\"\\t\")\n                            for d in content if d[0] != \"#\"])\n    data_dict = OrderedDict()\n    for dt in FeatureStore.df_types:\n        # select data type\n        df_s = data_df[data_df[1] == f\"TYPE_{dt.upper()}\"]\n        if len(df_s) == 0:\n            setattr(data_dict, dt, pd.DataFrame(\n                columns=FeatureStore.df_type_cols[dt]))\n        else:\n            # remove empty cols\n            na_info = df_s.isna().sum(axis=0) == len(df_s)\n            df_s = df_s[[i for i in na_info[na_info ==\n                                            False].index if i != 1]].reset_index(drop=True)\n\n            if len(df_s.columns) != len(FeatureStore.df_type_cols[dt]):\n                df_s.columns = FeatureStore.df_type_cols[dt][:len(\n                    df_s.columns)]\n            else:\n                df_s.columns = FeatureStore.df_type_cols[dt]\n\n            # set dtype\n            for c in df_s.columns:\n                df_s[c] = df_s[c].astype(FeatureStore.dtype_dict[dt][c])\n            setattr(data_dict, dt, df_s)\n    data_dict.meta_info_df = pd.DataFrame([m.replace(\"\\n\", \"\").split(\":\")\n                                           for m in _flatten([d.split(\"\\t\")\n                                                              for d in content if d[0] == \"#\"]) if m != \"#\"])\n    startTime_ind = int(np.where(data_dict.meta_info_df[0] == 'startTime')[0])\n    endTime_ind = int(np.where(data_dict.meta_info_df[0] == 'endTime')[0])\n    data_dict.meta_info_df.loc[startTime_ind,\n                               1] = data_dict.meta_info_df.loc[startTime_ind+1, 0]\n    data_dict.meta_info_df.loc[endTime_ind,\n                               1] = data_dict.meta_info_df.loc[endTime_ind+1, 0]\n\n    data_dict.waypoint['timestamp'] = sample_sub[sample_sub.path_id ==\n                                                 path_id].timestamp.values.astype(int)\n    data_dict.waypoint['x'] = 0\n    data_dict.waypoint['y'] = 0\n    data_dict.n_floor = 0\n    data_dict.site_id = site_id\n    if len(data_dict.beacon) > 0:\n        gap = data_dict.beacon.loc[0, 'timestamp2'] + \\\n            data_dict.beacon.loc[0, 'timestamp']\n    else:\n        gap = (data_dict.wifi.last_seen_timestamp.values -\n               data_dict.wifi.timestamp.values).max()+210.14426803816337  # from mean gap\n    #data_dict.wifi.last_seen_timestamp = data_dict.wifi.last_seen_timestamp-gap\n    return data_dict","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# The common wifi records in the training dataset."},{"metadata":{},"cell_type":"markdown","source":"First, please look at these two paths in train set."},{"metadata":{"trusted":true},"cell_type":"code","source":"site_id='5a0546857ecc773753327266'\nfloor='B1'\npath_id_in_train1='5e15bdabf4c3420006d52333'\npath_id_in_train2='5e15bda91506f2000638feb7'\n\nfeature = FeatureStore(\n    site_id=site_id, floor=floor, path_id=path_id_in_train1)\nfeature.load_all_data() \nwifi1 = feature.wifi\nwaypoint1 = feature.waypoint\n\nfeature = FeatureStore(\n    site_id=site_id, floor=floor, path_id=path_id_in_train2)\nfeature.load_all_data() \nwifi2 = feature.wifi\nwaypoint2 = feature.waypoint","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"waypoint1","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"waypoint2","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The last waypoint of path 1 is the same as the first waypoint of path 2. I guess the path 1 and path 2 are divided from a single measurement.\n\nIn the wifi data of these paths you can find the records with the same BSSID."},{"metadata":{"trusted":true},"cell_type":"code","source":"common_wifi_bssid = '9ad1d8c3a29b04ff542c90d2f6e05eaeddc42a97'","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"wifi1[wifi1.bssid==common_wifi_bssid]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"wifi2[wifi2.bssid==common_wifi_bssid]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"You can see there is a common wifi record with the same SSID, BSSID, RSSI, frequency, and last seen timestamps.\n\nI am not familiar with wifi data, but @franoisboyer in [this discussion section](https://www.kaggle.com/c/indoor-location-navigation/discussion/224491) explained how the wifi data are collected.\n\n* The first timestamp is the time when the scanning device calls the scan function.\n* When the device calls the scan function, there may be 3 possibilities for scan result values\n\n\n1. Either the wifi access point has never been scanned => nothing is returned and nothing is in the dataset for this timestamp\n2. Either the wifi access point is in range => then lastseen timestamp is updated with current time stamp and fresh values are returned\n3. Either the wifi access point is not in range anymore => then lastseen timestamp is not updated, and previously seen values are returned\n\nI suspect that these common wifi records are predictor of the leakage.\n"},{"metadata":{},"cell_type":"markdown","source":"# The common wifi records between the training and test dataset.\nSurprisingly, path 1 shares the same wifi records with a path in test set"},{"metadata":{"trusted":true},"cell_type":"code","source":"path_id_in_test='d592885af4e6e380c376dc55'\nfeature = get_feature_test(\n        site_id=site_id, path_id=path_id_in_test, sample_sub=sample_sub)\nwifi_test = feature.wifi","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"common_wifi_bssid2 = '914cb2b0c63064164d4b8fd821bbde4a164a2a6a'","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"wifi1[wifi1.bssid==common_wifi_bssid2]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"wifi_test[wifi_test.bssid==common_wifi_bssid2]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"You can see there are common wifi records with the same SSID, BSSID, RSSI, frequency, and last seen timestamps.\nI could not believe my eyes when I first found it."},{"metadata":{},"cell_type":"markdown","source":"# Get wifi data and start/end waypoints/time in the dataset\nNow I partially recovered the user ID with these leaked common wifi records."},{"metadata":{"trusted":true},"cell_type":"code","source":"# train_meta_data\ntrain_meta = glob(\"../input/indoor-location-navigation/train/*/*/*\")\ntrain_meta_org = pd.DataFrame(train_meta)\ntrain_meta = train_meta_org[0].str.split(\"/\", expand=True)[[4, 5, 6]]\ntrain_meta.columns = [\"site_id\", \"floor\", \"path_id\"]\ntrain_meta[\"path_id\"] = train_meta[\"path_id\"].str.replace(\".txt\", \"\")\ntrain_meta[\"path\"] = train_meta_org[0]\n#train_meta.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sample_sub = pd.read_csv('../input/indoor-location-navigation/sample_submission.csv')\nsample_sub = sample_sub[\"site_path_timestamp\"].apply(\n    lambda x: pd.Series(x.split(\"_\")))\nsample_sub.columns = [\"site_id\", \"path_id\", \"timestamp\"]\ntest_meta=sample_sub.drop('timestamp', axis=1)\ntest_meta = test_meta.drop_duplicates(subset=[\"site_id\", \"path_id\"]).reset_index(drop=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_meta['start_time'] = 0\ntrain_meta['end_time'] = 0\ntrain_meta['start_wp_time'] = 0\ntrain_meta['start_wp_x'] = 0\ntrain_meta['start_wp_y'] = 0\ntrain_meta['end_wp_time'] = 0\ntrain_meta['end_wp_x'] = 0\ntrain_meta['end_wp_y'] = 0\ntrain_meta['n_floor'] = 0\nwifi_dict=defaultdict(lambda:pd.DataFrame())\nfor i in tqdm(range(len(train_meta))):\n    t = train_meta.iloc[i]\n    n_floor = FeatureStore.floor_convert[t.floor]\n    feature = FeatureStore(\n        site_id=t.site_id, floor=t.floor, path_id=t.path_id)\n    feature.load_all_data() \n    if feature.meta_info_df[feature.meta_info_df[0] == 'startTime'][1].values == None:\n        start_time = int(np.nanmin([feature.accelerometer.timestamp.min(\n        ), feature.wifi.timestamp.min(), feature.beacon.timestamp.min()]))\n    else:\n        start_time = int(\n            feature.meta_info_df[feature.meta_info_df[0] == 'startTime'][1])\n    if (len(feature.meta_info_df[feature.meta_info_df[0] == 'endTime']) == 0) or (feature.meta_info_df[feature.meta_info_df[0] == 'endTime'][1].values == None):\n        end_time = int(np.nanmax([feature.accelerometer.timestamp.max(\n        ), feature.wifi.timestamp.max(), feature.beacon.timestamp.max()]))\n    else:\n        end_time = int(\n            feature.meta_info_df[feature.meta_info_df[0] == 'endTime'][1])\n    train_meta.loc[i, 'start_time'] = start_time\n    train_meta.loc[i, 'start_wp_time'] = feature.waypoint.iloc[0]['timestamp']\n    train_meta.loc[i, 'start_wp_x'] = feature.waypoint.iloc[0]['x']\n    train_meta.loc[i, 'start_wp_y'] = feature.waypoint.iloc[0]['y']\n    train_meta.loc[i, 'end_time'] = end_time\n    train_meta.loc[i, 'end_wp_time'] = feature.waypoint.iloc[-1]['timestamp']\n    train_meta.loc[i, 'end_wp_x'] = feature.waypoint.iloc[-1]['x']\n    train_meta.loc[i, 'end_wp_y'] = feature.waypoint.iloc[-1]['y']\n    train_meta.loc[i, 'n_floor'] = feature.n_floor\n    wifi_dict[t.path_id]=feature.wifi[['bssid', 'last_seen_timestamp']].drop_duplicates()\ntrain_meta = train_meta.sort_values(\n    ['site_id', 'start_time']).reset_index(drop=True)\n\ntest_meta['start_time'] = 0\ntest_meta['end_time'] = 0\ntest_meta['start_wp_time'] = 0\ntest_meta['start_wp_x'] = 0\ntest_meta['start_wp_y'] = 0\ntest_meta['end_wp_time'] = 0\ntest_meta['end_wp_x'] = 0\ntest_meta['end_wp_y'] = 0\ntest_meta['n_floor'] = 0\nfor i in tqdm(range(len(test_meta))):\n    t = test_meta.iloc[i]\n    #print(f\"site_id: {t.site_id}, floor: {t.floor}, path_id: {t.path_id}\")\n    feature = get_feature_test(\n        site_id=t.site_id, path_id=t.path_id, sample_sub=sample_sub)\n    if feature.meta_info_df[feature.meta_info_df[0] == 'startTime'][1].values == None:\n        start_time = int(np.nanmin([feature.accelerometer.timestamp.min(\n        ), feature.wifi.timestamp.min(), feature.beacon.timestamp.min()]))\n    else:\n        start_time = int(\n            feature.meta_info_df[feature.meta_info_df[0] == 'startTime'][1])\n    if (len(feature.meta_info_df[feature.meta_info_df[0] == 'endTime']) == 0) or (feature.meta_info_df[feature.meta_info_df[0] == 'endTime'][1].values == None):\n        end_time = int(np.nanmax([feature.accelerometer.timestamp.max(\n        ), feature.wifi.timestamp.max(), feature.beacon.timestamp.max()]))\n    else:\n        end_time = int(\n            feature.meta_info_df[feature.meta_info_df[0] == 'endTime'][1])\n    if len(feature.beacon) > 0:\n        gap = feature.beacon.loc[0, 'timestamp2'] - \\\n            feature.beacon.loc[0, 'timestamp']\n    else:\n        gap = (feature.wifi.last_seen_timestamp.values -\n               feature.wifi.timestamp.values).max()+210.14426803816337  # from mean gap\n    test_meta.loc[i, 'start_time'] = start_time+gap\n    test_meta.loc[i, 'start_wp_time'] = feature.waypoint.iloc[0]['timestamp']\n    test_meta.loc[i, 'start_wp_x'] = feature.waypoint.iloc[0]['x']\n    test_meta.loc[i, 'start_wp_y'] = feature.waypoint.iloc[0]['y']\n    test_meta.loc[i, 'end_time'] = end_time+gap\n    test_meta.loc[i, 'end_wp_time'] = feature.waypoint.iloc[-1]['timestamp']\n    test_meta.loc[i, 'end_wp_x'] = feature.waypoint.iloc[-1]['x']\n    test_meta.loc[i, 'end_wp_y'] = feature.waypoint.iloc[-1]['y']\n    test_meta.loc[i, 'n_floor'] = feature.n_floor\n    wifi_dict[t.path_id]=feature.wifi[['bssid', 'last_seen_timestamp']].drop_duplicates()\n\ndf = pd.merge(train_meta, test_meta, how='outer')\ndf = df.sort_values(['site_id', 'start_time']).reset_index(drop=True)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Get user ID based on leaked common records\nI consider a small noize in last seen timestamps as shown above."},{"metadata":{"trusted":true},"cell_type":"code","source":"df['user_id'] = 0\ndf['counter'] = 0\nn = 0\nfor i in tqdm(range(len(df))):\n    t = df.iloc[i]\n    current_wifi=wifi_dict[t.path_id]\n    min_last_seen_timestamp = current_wifi.last_seen_timestamp.min()\n    df_site = df[df.site_id == t.site_id]\n    df_site = df_site[df_site.end_time < t.start_time]\n    df_site = df_site[min_last_seen_timestamp < df_site.end_time]\n    counter = 0\n    if len(df_site) > 0:\n        for j in range(len(df_site)):\n            t = df_site.iloc[j]\n            old_wifi = wifi_dict[t.path_id]\n            common_wifi = pd.merge(\n                current_wifi, old_wifi, how='inner', on=['bssid'])\n            common_wifi['diff_time'] = abs(\n                common_wifi.last_seen_timestamp_x-common_wifi.last_seen_timestamp_y)\n            if (common_wifi.diff_time < 5).sum() > 0:\n                #If there is a leak\n                df.loc[i, 'user_id'] = t.user_id\n                counter += 1\n    if counter == 0:\n        df.loc[i, 'user_id'] = n\n        n += 1\n    df.loc[i, 'counter'] = counter","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Three paths shown above has the same user ID."},{"metadata":{"trusted":true},"cell_type":"code","source":"df[df.user_id==161]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df[:30]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df.to_csv('df.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Strictly speaking, my model is contaminated by the leakage, as far as I use wifi features even if I do not use last seen timestamps. \n\nWhat should I do?\n\nAny comments are welcome."}],"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":4,"nbformat_minor":4}