{"cells":[{"metadata":{"_uuid":"ea79d2ea-414e-4cf1-b93b-9138366e7b79","_cell_guid":"b5b0f2b4-6d56-4212-9ad0-1ac907d04709","trusted":true},"cell_type":"markdown","source":"# Data Wrangling\nIn this section, I will wrangle the pathway data in such a way that the output will be split dataframes, one for each sensor type.  \nI will define reusable functions along the way, which will be in the hidden cells so you may need to unhide them.  \n\nI will take care of cleaning the data quality issue on missing new line character as reported by the dataset provider.  \nAn example of the issue is shown below, where the whole string below occurs within one line:  \n\n> 1560916208644\tTYPE_BEACON\tbd1b5cf6d9f4f7bcb796b62cc831b6c81b1aa6ae\t356a192b7913b04c54574d18c28d46e6395428ab\t356a192b7913b04c54574d18c28d46e6395428ab\t-60\t-97\t36.627261007490404\t778c2c52390b3513c1510c2fe7579c1011d250bb1560916208852\tTYPE_WIFI\tb1e32753c8cfd3624253d16d9bc944d917c451e4\t8760dd3789b36258dea5d2b3687be70eb2163310\t-77\t2452\t1560916206584\n\nAs you can see, within a single line, there are more than one sensor type being listed, where in the example they are TYPE_BEACON and TYPE_WIFI within a single line. I assumed that if this line occurs, this means that both sensor reading occurs at the same timestamp.\nSo, for cleaning those problematic lines, the following is done in the code:\n- I'll split the line into multiple lines, following the number of sensor types within that single line\n- I'll then assume that the splited lines all have the same timestamp as the timestamp on the problematic line\n\nI will also create the following columns to be able to identify every sensor reading across the many files:\n- `map_id`: the ID of the folder\n- `floor`: the floor level from the folder name\n- `trip_id`: the ID of the text file\n- `waypoint_seq`: the sequence number for each waypoint path (unique only within one trip)\n\nI believe by having those 3 IDs in each row, added with the timestamp, we'll then be able to uniquely identify every sensor reading across the many files.\n\nThe waypoint sequence number is useful to find all the sensor readings that happened within one waypoint path before reaching the next waypoint. In this way, we can query the sensor readings related to a specific path between one waypoint and the next waypoint. The sequence number is generated based on the sequence of the waypoint that is found from the data. The sequence number starts from 0."},{"metadata":{"_uuid":"442da59d-a765-4e13-8bd9-318ca387fc69","_cell_guid":"821c1ff2-a7f4-40f4-8dc9-6b201b3c965d","trusted":true,"scrolled":false},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nfrom glob import glob\nimport os\nfrom tqdm import tqdm\nfrom collections.abc import Iterable\nimport seaborn as sns\nimport matplotlib.pyplot as plt\n%matplotlib inline","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6028b9f8-fce0-4b83-be3a-7581108985a0","_cell_guid":"0891de74-bede-4fba-8b10-50b7ce4f358b","trusted":true},"cell_type":"markdown","source":"Below is the function to read in the pathway data and produce dataframe. The function will:\n- Clean the reported data quality issue on the missing new line characters to separate multiple sensor readings.\n- Create new columns `waypoint_seq` to help separate the sensor readings into multiple paths based on pairs of waypoints.\n- Create the IDs mentioned above."},{"metadata":{"_uuid":"7c620962-05f1-4e4d-a4c8-d04707f9afc3","_cell_guid":"094fba77-b293-4d49-a79d-51c74c07a4ab","trusted":true,"_kg_hide-input":true,"scrolled":false},"cell_type":"code","source":"def read_pathway_data(file_path):\n    '''\n    Read the pathway text file into separate dataframes, where each dataframe corresponds to each sensor type.\n    This function will do the data cleaning for the \"missing new line\" problem.\n    This function will also add the corresponding columns to add identifications of sensor reading.\n    '''\n    \n    # Create a new csv file with all the comment lines removed and the data quality issue fixed, then read the csv file using pandas\n    # because the number of columns varies for each row, there will be NaN values at around the last columns for some rows\n    # this is expected and to be cleaned at the end of this function.\n    with open(file_path, 'r', encoding='utf-8') as file:\n        lines = np.array([line for line in file.readlines() if not line.startswith('#')])\n    \n    # clean the missing new line data quality issue by adding new line and copying the same timestamp\n    new_lines_to_add = []\n    line_idx_to_remove = []\n    for line_idx, line in enumerate(lines):\n        tokens = line.split('\\t')\n        type_idx = [i for i, token in enumerate(tokens) if token.startswith('TYPE_')]\n        \n        if len(type_idx) > 1:\n            line_idx_to_remove.append(line_idx)\n            timestamp = tokens[0]\n            for j, start_idx in enumerate(type_idx):\n                end_idx = -1 if j == (len(type_idx)-1) else type_idx[j+1]-1\n                tokens_sub = tokens[start_idx:end_idx+1] if end_idx > -1 else tokens[start_idx:]\n                new_line = '\\t'.join([timestamp] + tokens_sub)\n                if not new_line.endswith('\\n'):\n                    new_line += '\\n'\n                new_lines_to_add.append(new_line)\n    \n    lines = np.delete(lines, line_idx_to_remove)\n    lines = np.append(lines, new_lines_to_add)\n    \n    # rewrite cleaned lines into new csv file\n    with open('temp.csv', 'w') as tmp_csv:\n        for line in lines:\n            tmp_csv.write(line)\n    \n    # import the cleaned CSV file\n    df = pd.read_csv('temp.csv', sep='\\t', names=np.arange(9), header=None, low_memory=False) # need high memory due to mixed data type in each column\n    os.remove('temp.csv')\n    \n    # rename columns\n    df = df.rename(columns={0: 'timestamp', 1: 'sensor_type'})\n    \n    # create the ID column based on timestamp and textfile name\n    path, filename = os.path.split(file_path)\n    trip_id  = os.path.splitext(filename)[0]\n    path, floor = os.path.split(path)\n    path, map_id = os.path.split(path)\n    \n    df['trip_id'] = trip_id\n    df['floor'] = floor\n    df['map_id'] = map_id\n    \n    # create waypoint sequence number\n    waypoint_timestamps = np.append(df.query('sensor_type == \"TYPE_WAYPOINT\"').timestamp.sort_values().values[1:], -1)\n    start_timestamp = 0\n    df['waypoint_seq'] = -1\n    for i, end_timestamp in enumerate(waypoint_timestamps):\n        if end_timestamp > -1:\n            df.loc[(df.timestamp >= start_timestamp) & (df.timestamp < end_timestamp), 'waypoint_seq'] = i\n        else:\n            df.loc[(df.timestamp >= start_timestamp), 'waypoint_seq'] = i\n        start_timestamp = end_timestamp\n    \n    # split the dataframe into multiple dataframes, one for each sensor type\n    df_dict = {}\n    for sensor_type in df.sensor_type.unique():\n        df_sub = df.query(f'sensor_type == \"{sensor_type}\"')\n        if sensor_type == 'TYPE_WAYPOINT':\n            df_sub = (df_sub.rename(columns={2: 'x', 3: 'y'})\n                      .loc[:, ['map_id', 'floor', 'trip_id', 'timestamp', 'waypoint_seq', 'sensor_type', 'x', 'y']]\n                      .astype({'x': float, 'y': float})\n                     )\n        elif sensor_type == 'TYPE_WIFI':\n            df_sub = (df_sub.rename(columns={2: 'ssid', 3: 'bssid', 4: 'rssi', 5: 'frequency', 6: 'last_seen_timestamp'})\n                      .astype({'last_seen_timestamp': int})\n                      .loc[:, ['map_id', 'floor', 'trip_id', 'timestamp', 'waypoint_seq', 'sensor_type', 'ssid', 'bssid', 'rssi', 'frequency', 'last_seen_timestamp']]\n                     )\n        elif sensor_type == 'TYPE_BEACON':\n            df_sub = (df_sub.rename(columns={2: 'uuid', 3: 'major_id', 4: 'minor_id', 5: 'tx_power', 6: 'rssi', 7: 'distance', 8: 'mac_address'})\n                      .astype({'uuid': str,\n                               'major_id': str,\n                               'minor_id': str,\n                               'tx_power': float,\n                               'rssi': float,\n                               'distance': float,\n                               'mac_address': str\n                              })\n                      .loc[:, ['map_id', 'floor', 'trip_id', 'timestamp', 'waypoint_seq', 'sensor_type', 'uuid', 'major_id', 'minor_id', 'tx_power', 'rssi',\n                              'distance', 'mac_address']]\n                     )\n        else:\n            df_sub = (df_sub.rename(columns={2: 'x', 3: 'y', 4: 'z'})\n                      .loc[:, ['map_id', 'floor', 'trip_id', 'timestamp', 'waypoint_seq', 'sensor_type', 'x', 'y', 'z']]\n                      .astype({'x': float, 'y': float})\n                     )\n            \n        df_dict[sensor_type] = df_sub.reset_index(drop=True)\n    \n    return df, df_dict","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1aab721e-6c38-4b25-9f33-94ab22401507","_cell_guid":"b9c1d948-972e-4a70-a853-65515fdc8139","trusted":true,"scrolled":false},"cell_type":"code","source":"train_file_paths = glob('../input/indoor-location-navigation/train/*/*/*')\ndf_raw, df_dict = read_pathway_data(train_file_paths[4]) # this is the path with TYPE_BEACON data","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Preview of the dataframes:"},{"metadata":{"_uuid":"a25ff116-74ba-42e2-88c8-29c964e94983","_cell_guid":"7da944b2-7d39-4be3-a716-ac05e5c5ed59","trusted":true,"scrolled":false},"cell_type":"code","source":"for sensor_type in df_raw.sensor_type.unique():\n    print('-------------------------------')\n    print(sensor_type)\n    print('-------------------------------')\n    display(df_dict[sensor_type].head())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Preview of the dataframe column infos:"},{"metadata":{"_uuid":"b5a1e8dd-85e9-4e95-ba1f-b7840cc094a1","_cell_guid":"dbd97d2a-bd01-4b32-9b53-68c15f88afc1","trusted":true,"scrolled":false},"cell_type":"code","source":"for sensor_type in df_raw.sensor_type.unique():\n    print('-------------------------------')\n    print(sensor_type)\n    print('-------------------------------')\n    df_dict[sensor_type].info()\n    print('\\n')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Convert Text Files to CSV\n\nBelow I will:\n- Define the function to collect the training data (only for a specific map) into separate dataframes, one dataframe for each sensor type.\n- Do the collection using the defined function."},{"metadata":{"trusted":true,"_kg_hide-input":true,"scrolled":false},"cell_type":"code","source":"def is_iterable(x):\n    try:\n        iter(sensor_type)\n        return True\n    except TypeError:\n        return False\n\ndef to_csv_whole_map(map_dir, sensor_type=None, out_dir='', low_memory=True):\n    \"\"\"Export the whole map specified in `map_dir` into CSV files, one CSV file for each sensor type.\"\"\"\n    # This function assumes the folder structure being '<map_dir>/<floor>/<trip text file>'\n    selected_train_files = glob(os.path.join(map_dir, '*', '*'))\n    \n    # create out dir if does not exists\n    if len(out_dir) > 0 and not os.path.exists(out_dir):\n        os.mkdir(out_dir)\n    \n    # ensure sensor_type is iterable\n    if not is_iterable(sensor_type): sensor_type = [sensor_type]\n        \n    # initialize df_out_dict if high memory approach is chosen\n    if not low_memory: df_out_dict = {}\n    \n    for file_index, file_path in enumerate(tqdm(selected_train_files, desc='Converting text files')):\n        df_raw, df_dict = read_pathway_data(file_path)\n        \n        s_types = df_raw.sensor_type.unique() if sensor_type is None else sensor_type\n        \n        for s_type in s_types:\n            if low_memory:\n                # export and append one by one if low_memory approach is chosen\n                out_filepath = os.path.join(out_dir, s_type + '.csv')\n                df_dict[s_type].to_csv(out_filepath,\n                                       mode=('a' if file_index > 0 else 'w'),\n                                       header=(file_index == 0),\n                                       index=False)\n            else:\n                # collect all dataframes and export in a single shot if high memory is possible\n                df_out_list = df_out_dict.get(s_type)\n                if df_out_list is None:\n                    df_out_dict[s_type] = []\n                    df_out_list = df_out_dict[s_type]\n                df_out_list.append(df_dict[s_type])\n    \n    # the final step of high memory approach\n    if not low_memory:\n        for s_type in tqdm(df_out_dict.keys(), desc='Exporting to CSV files'):\n            out_filepath = os.path.join(out_dir, s_type + '.csv')\n            pd.concat(df_out_dict[s_type]).to_csv(out_filepath, index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":false},"cell_type":"code","source":"# # UNCOMMENT TO RUN THIS\n# to_csv_whole_map('../input/indoor-location-navigation/train/5a0546857ecc773753327266', low_memory=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Data Exploration\n\n## Import Data\n\nBelow I will import the CSV files into dataframes, collected into dictionary form."},{"metadata":{"trusted":true},"cell_type":"code","source":"sensor_types = ['TYPE_WAYPOINT', 'TYPE_MAGNETIC_FIELD', 'TYPE_GYROSCOPE',\n                'TYPE_ACCELEROMETER', 'TYPE_WIFI', 'TYPE_ROTATION_VECTOR', 'TYPE_BEACON']\nindexes = ['map_id', 'floor', 'trip_id', 'waypoint_seq', 'timestamp']\n\ndf = {}\nfor s_type in sensor_types:\n    key = s_type.replace('TYPE_', '').lower() # for convenience when getting the df\n    df[key] = (pd.read_csv(f'{s_type}.csv')\n               .set_index(indexes)\n               .drop(columns='sensor_type')\n               .sort_index()\n              )","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Waypoints:"},{"metadata":{"trusted":true},"cell_type":"code","source":"b1_waypoints = df['waypoint'].loc['5a0546857ecc773753327266', 'B1']\nsns.scatterplot(data=b1_waypoints, x='x', y='y', hue='trip_id', legend=False);\nnum_trips = b1_waypoints.index.get_level_values('trip_id').unique().shape[0]\nplt.title(f'Number of trips: {num_trips}');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Wifi"},{"metadata":{"trusted":true},"cell_type":"code","source":"df['wifi']","execution_count":null,"outputs":[]}],"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}