{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\n\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n\nimport os\nimport gc\n\nimport json\nfrom pandas.io.json import json_normalize\n\nfrom tqdm import trange,tqdm\nfrom collections import Counter\n\npd.options.display.max_rows = 8\npd.options.display.max_columns = 999\nprint(os.listdir(\"../input\"))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a8fa646477e32f46bd23bf0505a712910f89797d"},"cell_type":"markdown","source":"# load data"},{"metadata":{"trusted":true,"_uuid":"34bbed872cf5c64c87864d854f2f70c2ab59a87d"},"cell_type":"code","source":"def read_cols(file_tag, cols):\n    data_reader = pd.read_csv(f'../input/{file_tag}_v2.csv', iterator=True, chunksize=20000,\n                          dtype={id_col: 'str'})\n    df = pd.DataFrame()\n    for it in data_reader:\n        df = df.append(it[cols], ignore_index=True)\n        gc.collect()\n    del data_reader, it\n    gc.collect()\n    \n    return df","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true,"scrolled":false},"cell_type":"code","source":"id_col = 'fullVisitorId'\n\ndata_reader = pd.read_csv('../input/train_v2.csv', iterator=True, chunksize=1000, low_memory=False, \n                          dtype={id_col: 'str'})\ntrain_df = next(data_reader)\nprint(train_df.shape)\ntrain_df.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b2c7bb897c3cf6b411706194aa18580274904512"},"cell_type":"code","source":"train_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d440f05a6a31ddd38dfa2bce87661c2d87fbc6e2"},"cell_type":"code","source":"data_reader = pd.read_csv('../input/test_v2.csv', iterator=True, chunksize=1000, low_memory=False, \n                          dtype={id_col: 'str'})\ntest_df = next(data_reader)\nprint(test_df.shape)\ntest_df.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"68f58a5108b9039161acc8a657411160ca1e28b5"},"cell_type":"code","source":"test_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e305ac2fbd5a25a60649a04d843acd19bbb3a4c7","scrolled":true},"cell_type":"code","source":"cols = [id_col, 'date', 'visitId', 'visitNumber', 'visitStartTime', 'channelGrouping', 'socialEngagementType']\ntrain_df = read_cols('train', cols)\ngc.collect()\ntrain_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a478f6de0a732c007da7b7fb5bd352b3b52f8424","scrolled":false},"cell_type":"code","source":"test_df = read_cols('test', cols)\ngc.collect()\ntest_df","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ce8111f962b34e51b78212d529aa031cecb86866"},"cell_type":"markdown","source":"# ids"},{"metadata":{"trusted":true,"_uuid":"30f023d975afebd095d4c8dbb105b845d9b46637"},"cell_type":"code","source":"s = train_df[id_col].astype(np.str) + '_' + train_df.visitId.astype(np.str)\nprint(s.value_counts())\nprint('-------------------------------------------------------')\ns = test_df[id_col].astype(np.str) + '_' + test_df.visitId.astype(np.str)\nprint(s.value_counts())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f682ad67f54db887e5afdcdb7886622da60b8fef"},"cell_type":"code","source":"df = train_df\ns = df.fullVisitorId\nprint(s.value_counts())\nprint('---------------------------------')\ns = df.visitId.groupby(df.fullVisitorId).nunique()\nprint(s.sort_values(ascending=False))\nprint('-------------------------------------------------------')\ndf = test_df\ns = df.fullVisitorId\nprint(s.value_counts())\nprint('---------------------------------')\ns = df.visitId.groupby(df.fullVisitorId).nunique()\nprint(s.sort_values(ascending=False))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c9e8d8dcf8453f627704e38a4ba51b811d9bc41b"},"cell_type":"code","source":"df = train_df\ns = df.visitId\nprint(s.value_counts())\nprint('---------------------------------')\ns = df.fullVisitorId.groupby(df.visitId).nunique()\nprint(s.sort_values(ascending=False))\nprint('-------------------------------------------------------')\ndf = test_df\ns = df.visitId\nprint(s.value_counts())\nprint('---------------------------------')\ns = df.fullVisitorId.groupby(df.visitId).nunique()\nprint(s.sort_values(ascending=False))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0468f46ac446a9bbed3d75c93989b9e426c074fc"},"cell_type":"markdown","source":"# visitNumber"},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"90a71332c81359f562abff4d8da75f5093b53067"},"cell_type":"code","source":"s = train_df.visitNumber\nprint(s.describe())\nprint()\n\ncnt = s.value_counts()\nprint(cnt)\nprint(cnt.loc[cnt>1].shape)\nprint(cnt.describe())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7799701895bff26badf68ee12ee312889577a1a6"},"cell_type":"code","source":"s = test_df.visitNumber\nprint(s.describe())\nprint()\n\ncnt = s.value_counts()\nprint(cnt)\nprint(cnt.loc[cnt>1].shape)\nprint(cnt.describe())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"33879a6edf46f73578308c9ccc7175735c6645ac"},"cell_type":"markdown","source":"# dates"},{"metadata":{"trusted":true,"_uuid":"f3e895de3f474cc3bbaed14e7c9ef0c3480d8334"},"cell_type":"code","source":"df = train_df\ns = df.visitStartTime - df.visitId\nprint(s.describe())\nprint()\nprint(np.sum(df.visitId != df.visitStartTime))\nprint(s.loc[s>0].describe())\nprint('-------------------------------------------')\n\ns = pd.to_datetime(df.date.astype(np.str))\ns1 = pd.to_datetime(df.visitId, unit='s')\ns2 = pd.to_datetime(df.visitStartTime, unit='s')\n\nprint(np.sum(s.dt.year != s1.dt.year), np.sum(s.dt.month != s1.dt.month), np.sum(s.dt.day != s1.dt.day))\nss = (s - s1).dt.days\nprint(ss.min(), ss.max())\nprint(np.sum(s.dt.year != s2.dt.year), np.sum(s.dt.month != s2.dt.month), np.sum(s.dt.day != s2.dt.day))\nss = (s - s2).dt.days\nprint(ss.min(), ss.max())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"086c85a52891e312e5f95bb60f632c1fb5613b4a"},"cell_type":"code","source":"df = test_df\ns = df.visitStartTime - df.visitId\nprint(s.describe())\nprint()\nprint(np.sum(df.visitId != df.visitStartTime))\nprint(s.loc[s>0].describe())\nprint('-------------------------------------------')\n\ns = pd.to_datetime(df.date.astype(np.str))\ns1 = pd.to_datetime(df.visitId, unit='s')\ns2 = pd.to_datetime(df.visitStartTime, unit='s')\n\nprint(np.sum(s.dt.year != s1.dt.year), np.sum(s.dt.month != s1.dt.month), np.sum(s.dt.day != s1.dt.day))\nss = (s - s1).dt.days\nprint(ss.min(), ss.max())\nprint(np.sum(s.dt.year != s2.dt.year), np.sum(s.dt.month != s2.dt.month), np.sum(s.dt.day != s2.dt.day))\nss = (s - s2).dt.days\nprint(ss.min(), ss.max())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ac37394c63882a4bcafae6302b935faaa58b36b9"},"cell_type":"markdown","source":"# channelGrouping"},{"metadata":{"trusted":true,"_uuid":"1a09f2272e794bad0f9f082426b0ca37336c362e"},"cell_type":"code","source":"col = 'channelGrouping'\ndf = train_df\ncnt = df[col].value_counts()\nprint(cnt.shape)\nprint(cnt)\nprint('---------------------------------')\ndf = test_df\ncnt = df[col].value_counts()\nprint(cnt.shape)\nprint(cnt)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3b1131989274a126833c36b29837122977d2e6f6"},"cell_type":"markdown","source":"# socialEngagementType"},{"metadata":{"trusted":true,"_uuid":"d51d9cd27eefb6b395922d649b6f897ce3371314"},"cell_type":"code","source":"col = 'socialEngagementType'\ndf = train_df\ncnt = df[col].value_counts()\nprint(cnt.shape)\nprint(cnt)\nprint('---------------------------------')\ndf = test_df\ncnt = df[col].value_counts()\nprint(cnt.shape)\nprint(cnt)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d2b9542cfc5147860b4949be60ce2e474eb02213"},"cell_type":"markdown","source":"# jsons"},{"metadata":{"trusted":true,"_uuid":"e2c917305c72985430cab7b3aa81e0d4cff42903"},"cell_type":"code","source":"del train_df, test_df\ngc.collect()\njson_cols = ['customDimensions', 'device', 'geoNetwork', 'hits', 'totals', 'trafficSource']\n\n\ndef read_json_col(file_tag, col):\n    data_reader = pd.read_csv(f'../input/{file_tag}_v2.csv', iterator=True, chunksize=20000,\n                          converters={col: json.loads})\n    s = pd.Series()\n    for it in data_reader:\n        s = s.append(it[col], ignore_index=True)\n        gc.collect()\n    del data_reader, it\n    gc.collect()\n    \n    return s","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3e650307592d67f2e798060ab8eaaef1c23720f1"},"cell_type":"markdown","source":"## customDimensions"},{"metadata":{"trusted":true,"_uuid":"60500fd02301b53dc80af271d9248f148c493f30"},"cell_type":"code","source":"col = 'customDimensions'\ndf = read_cols('train', [col])\ngc.collect()\nprint(df[col].unique())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4f9f1e43b4af0f00b0882600d5cfaf3d10b76c80"},"cell_type":"code","source":"col = 'customDimensions'\ndf = read_cols('test', [col])\ngc.collect()\nprint(df[col].unique())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"12e8f79cf24ae24a9d7ac2b32a8fe2e64aa0f167"},"cell_type":"markdown","source":"## device"},{"metadata":{"trusted":true,"_uuid":"d1dda5cea1f75c5da61b540f914925eb68c34740"},"cell_type":"code","source":"col = 'device'\ndf = json_normalize(read_json_col('train', col))\ngc.collect()\nprint(df.shape)\ndf","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c144e24637f08b7ba07ba77e7c300fbbacba975f"},"cell_type":"code","source":"remain_cols = []\nfor col in df.columns:\n    cnt = df[col].value_counts(dropna=False)\n    if cnt.shape[0] <= 1:\n        print(col)\n        print(cnt.index[0])\n    else:\n        remain_cols.append(col)\n        print(cnt)\n    print('------------------------------------------------------------')\nprint(remain_cols)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b19ed9b266a7630585f9ced65dfb0c47aa50cf51"},"cell_type":"code","source":"col = 'device'\ndf = json_normalize(read_json_col('test', col))\ngc.collect()\nprint(df.shape)\ndf","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"100597ccf81d2d4c696d049c73a3bb44d1b1386e"},"cell_type":"code","source":"remain_cols = []\nfor col in df.columns:\n    cnt = df[col].value_counts(dropna=False)\n    if cnt.shape[0] <= 1:\n        print(col)\n        print(cnt.index[0])\n    else:\n        remain_cols.append(col)\n        print(cnt)\n    print('------------------------------------------------------------')\nprint(remain_cols)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cfa41e67a9a5b9e853a40aeb2c9044f748967498"},"cell_type":"markdown","source":"## geoNetwork"},{"metadata":{"trusted":true,"_uuid":"671467d6779eaba68b146d81dec6991528515860"},"cell_type":"code","source":"col = 'geoNetwork'\ndf = json_normalize(read_json_col('train', col))\ngc.collect()\nprint(df.shape)\ndf","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"1f7bf14c2a486a842064aae0d21287d70cc2e0db"},"cell_type":"code","source":"remain_cols = []\nfor col in df.columns:\n    cnt = df[col].value_counts(dropna=False)\n    if cnt.shape[0] <= 1:\n        print(col)\n        print(cnt.index[0])\n    else:\n        remain_cols.append(col)\n        print(cnt)\n    print('------------------------------------------------------------')\nprint(remain_cols)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ed3faf21d41e1b20e4eaff2a210de02e76d75888"},"cell_type":"code","source":"col = 'geoNetwork'\ndf = json_normalize(read_json_col('test', col))\ngc.collect()\nprint(df.shape)\ndf","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"9c8ed0cf5e6de6673b5d6b78c97fa846cf2a30f5"},"cell_type":"code","source":"remain_cols = []\nfor col in df.columns:\n    cnt = df[col].value_counts(dropna=False)\n    if cnt.shape[0] <= 1:\n        print(col)\n        print(cnt.index[0])\n    else:\n        remain_cols.append(col)\n        print(cnt)\n    print('------------------------------------------------------------')\nprint(remain_cols)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0b5d626f2f00a78b5f004033a631955489d2d53e"},"cell_type":"markdown","source":"## hits"},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"11d6984964e5e00e662f4718932ffc170ddb81a5"},"cell_type":"code","source":"data_reader = pd.read_csv('../input/train_v2.csv', iterator=True, chunksize=1000, low_memory=False, \n                          dtype={id_col: 'str'})\ntrain_df = next(data_reader)\ntrain_df.loc[100,'hits']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a736e664a7491b8be5f7376439edf5e6b6eac3ec"},"cell_type":"code","source":"data_reader = pd.read_csv('../input/test_v2.csv', iterator=True, chunksize=1000, low_memory=False, \n                          dtype={id_col: 'str'})\ntest_df = next(data_reader)\ntest_df.loc[100,'hits']","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"54b8deaa36583f22bd55757c64e016ddaae4d792"},"cell_type":"markdown","source":"## totals"},{"metadata":{"trusted":true,"_uuid":"a840f6cf789bc77a011132d30d11c27664f1f522","scrolled":false},"cell_type":"code","source":"col = 'totals'\ndf = json_normalize(read_json_col('train', col))\ngc.collect()\nprint(df.shape)\ndf","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0a7375af3fe755cc41f51db4267ab18a90ded76d","scrolled":true},"cell_type":"code","source":"remain_cols = []\nfor col in df.columns:\n    cnt = df[col].value_counts(dropna=False)\n    if cnt.shape[0] <= 1:\n        print(col)\n        print(cnt.index[0])\n    else:\n        remain_cols.append(col)\n        print(cnt)\n    print('------------------------------------------------------------')\nprint(remain_cols)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"fe212020cd5bc5fdba136251df7ab90397995653"},"cell_type":"code","source":"dff = df.loc[df.transactions=='1']\ndff = dff.fillna(-1)\ngc.collect()\ncol = 'totalTransactionRevenue'\ndff[col] = dff[col].astype(np.int64)\ncol = 'transactionRevenue'\ndff[col] = dff[col].astype(np.int64)\ngc.collect()\nprint(dff.loc[dff.totalTransactionRevenue - dff.transactionRevenue > 9e6].shape)\ndf.loc[df.transactions>'1']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6c501d472dcea752ab5884a717dfbd1341c082d4"},"cell_type":"code","source":"col = 'totals'\ndf = json_normalize(read_json_col('test', col))\nprint(df.shape)\ndf","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3c479eca5fe266ee63c368c0236c0df8d9f04f39","scrolled":false},"cell_type":"code","source":"remain_cols = []\nfor col in df.columns:\n    cnt = df[col].value_counts(dropna=False)\n    if cnt.shape[0] <= 1:\n        print(col)\n        print(cnt.index[0])\n    else:\n        remain_cols.append(col)\n        print(cnt)\n    print('------------------------------------------------------------')\nprint(remain_cols)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"11e705a21dde8697334a808acc244944b52d15c7"},"cell_type":"markdown","source":"## trafficSource"},{"metadata":{"trusted":true,"_uuid":"76d0b233c73abd2e942d7ec85f2e9f47bc3f0291"},"cell_type":"code","source":"col = 'trafficSource'\ndf = json_normalize(read_json_col('train', col))\nprint(df.shape)\ndf","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"07755ecaff22b8be63293c2e495f188a21c79692"},"cell_type":"code","source":"remain_cols = []\nfor col in df.columns:\n    cnt = df[col].value_counts(dropna=False)\n    if cnt.shape[0] <= 1:\n        print(col)\n        print(cnt.index[0])\n    else:\n        remain_cols.append(col)\n        print(cnt)\n    print('------------------------------------------------------------')\nprint(remain_cols)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b68e4bdf2fac86f9943743f99f80b37996e5881f"},"cell_type":"code","source":"col = 'trafficSource'\ndf = json_normalize(read_json_col('test', col))\nprint(df.shape)\ndf","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"da6d53b8c09d7e61def802e5283d0be682a1e267","scrolled":false},"cell_type":"code","source":"remain_cols = []\nfor col in df.columns:\n    cnt = df[col].value_counts(dropna=False)\n    if cnt.shape[0] <= 1:\n        print(col)\n        print(cnt.index[0])\n    else:\n        remain_cols.append(col)\n        print(cnt)\n    print('------------------------------------------------------------')\nprint(remain_cols)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"297fc0df6efef0a76bb1df60136b1470a1961424"},"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}