{"cells":[{"metadata":{"_uuid":"86972631ebcd9d9d997823447ae535f4bb420d9e"},"cell_type":"markdown","source":"While working on my first EDA, I found that `visitId` seems to be **broken**."},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import matplotlib.pyplot as plt\nimport numpy as np\nimport pandas as pd\n\nimport json\nfrom pandas.io.json import json_normalize","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"# https://www.kaggle.com/julian3833/1-quick-start-read-csv-and-flatten-json-fields\n\nJSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource']\n\ndef load_df(csv_path):\n    df = pd.read_csv(csv_path, \n                     converters={column: json.loads for column in JSON_COLUMNS}, \n                     dtype={'fullVisitorId': 'str'})\n\n    json_part = df[JSON_COLUMNS]\n    df = df.drop(JSON_COLUMNS, axis=1)\n    normed_json_part = []\n    for col in JSON_COLUMNS:\n        col_as_df = json_normalize(json_part[col])\n        col_as_df.rename(columns=lambda x: f'{col}.{x}', inplace=True)\n        normed_json_part.append(col_as_df)\n    df = pd.concat([df] + normed_json_part, axis=1)\n\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b1eadccfc7a5bfe049a00325f9f43021d8bb3ad1"},"cell_type":"code","source":"train = load_df('../input/train.csv')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c888d2b361b9d60b947dcb7dfc511ad48a6a9b5f"},"cell_type":"markdown","source":"'visitId' seems to be created from 'visitStartTime'"},{"metadata":{"trusted":true,"_uuid":"ef00dd3774084343147d059616b798a02cf19efc"},"cell_type":"code","source":"train[['visitId', 'visitStartTime']].head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"37fc02cc792c7412627efc5db8073c7124a27c5b"},"cell_type":"markdown","source":"However, **4709 (0.52%) rows have different values.**"},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"b20861a541d441dfb5a6e6348ef65ee22880ae96"},"cell_type":"code","source":"mismatch = train['visitId'].astype(str) != train['visitStartTime'].astype(str)\nmismatch.sum(), mismatch.mean()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"018bdd4c8a1a85fe868c447daec1c177d1953b33"},"cell_type":"markdown","source":"It cause 898 duplicate sessionId's discussed in [#66100](https://www.kaggle.com/c/google-analytics-customer-revenue-prediction/discussion/66100)."},{"metadata":{"trusted":true,"_uuid":"a74953ad0e9e4d1b1ae3efef614f2c44bcf041ef"},"cell_type":"code","source":"# confirm `sessionId` == `fullVisitorId` + '_' + `visitId`\n((train['fullVisitorId'] + '_' + train['visitId'].astype(str)) == train['sessionId']).all()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"df80726d9e6ea0d0b651ae73b318b520938c4a20"},"cell_type":"code","source":"train['sessionId'].value_counts().max(), (train['sessionId'].value_counts() > 1).sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"5ae44a1bcad6858ef680af2dc2d1b7ef6807a75c"},"cell_type":"code","source":"duplicates = train[train['sessionId'].map(train['sessionId'].value_counts()) > 1].sort_values(by='sessionId')\nduplicates.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"bb1c762600a7bcce0d8d3ba33d68a9577ede2207"},"cell_type":"markdown","source":"For all rows where `visitId` != `visitStartTime`, `visitId` is smaller than `visitStartTime`, and the differences tend to be small values."},{"metadata":{"trusted":true,"_uuid":"779e8b6f542d1320db38407bafa7fd0a031600fd"},"cell_type":"code","source":"(train.loc[mismatch, 'visitId'] < train.loc[mismatch, 'visitStartTime']).all()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c2a26ea7812026ce2b7a5cf77421b25e539c2d3b"},"cell_type":"code","source":"diff = train.loc[mismatch, 'visitStartTime'] - train.loc[mismatch, 'visitId']\ndiff.hist()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"35ac422b6ca1a9b2347ef910ce84cd58cb9d2d2a"},"cell_type":"code","source":"diff.value_counts().iloc[:20]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3697a600dd7cf4932efe3729455bdc149d82a0b4"},"cell_type":"code","source":"diff.max()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"bffcb76ebc049228a01ca1273061195d45b9c98a"},"cell_type":"markdown","source":"I found that some of mismatches are caused by setting previous `visitStartTime` as `visitId`.\n895 of 898 duplicates are caused by the mismatches."},{"metadata":{"trusted":true,"_uuid":"6dd52be1393bc3edac7cbf95746ba79f3252e956"},"cell_type":"code","source":"train = train.sort_values(by='visitStartTime')\n# convert to `str` so that the series will not be converted to `float` automatically.\ntrain['previous_visitStartTime'] = train['visitStartTime'].astype(str).groupby(train['fullVisitorId']).shift(1).fillna('-1').astype(np.int64)\ntrain = train.sort_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7f6d10c156051020b2559f78a885da13b9ec484f"},"cell_type":"code","source":"((train['previous_visitStartTime'] == train['visitId']) & mismatch).sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d9eb12561db50a84378909198a7c59ce1dbd753e"},"cell_type":"code","source":"duplicates = train[train['sessionId'].map(train['sessionId'].value_counts()) > 1].sort_values(by=['sessionId', 'date'])\n(duplicates['visitId'] == duplicates['previous_visitStartTime']).sum()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"aa6fd7eaa73714e6683c436a6d07128a49259e43"},"cell_type":"markdown","source":"`fullVisitorId` + '_' + `visitId` will work well as true session ids."},{"metadata":{"trusted":true,"_uuid":"332f8d7be96610e707640009f27d103f3a3b97ac"},"cell_type":"code","source":"(train['fullVisitorId'] + '_' + train['visitStartTime'].astype(str)).nunique() == train.shape[0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7385d87fc264ea0ec6a234bba25285ce74541bbc"},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.6","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}