{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":false,"_kg_hide-output":false},"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\nimport re\n\nimport json\nfrom pandas.io.json import json_normalize\nfrom sklearn.feature_extraction.text import TfidfVectorizer\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":"43edcbb71366a162e5d3c25214af3b5ee6b0c074"},"cell_type":"markdown","source":"# record"},{"metadata":{"trusted":true,"_uuid":"0d2a594f24b3ed9a917a7c4954449d716b13e404"},"cell_type":"code","source":"data_dir = '../input/gs-flatten-data-analysis-2'\ntrain_df_r = pd.read_pickle(os.path.join(data_dir, 'train_df_r'), compression='gzip')\nprint(f'train_df_r: {train_df_r.shape}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1c4f084b0f61d7e5198bb9a64166fb90017e53b6"},"cell_type":"code","source":"tfs1 = pd.to_datetime('2017-05-01').timestamp()\ntfe1 = pd.to_datetime('2017-10-01').timestamp()\nvtfs1 = pd.to_datetime('2017-12-01').timestamp()\nvtfe1 = pd.to_datetime('2018-02-01').timestamp()\n\ntfs2 = pd.to_datetime('2018-01-01').timestamp()\ntfe2 = pd.to_datetime('2018-06-01').timestamp()\nvtfs2 = pd.to_datetime('2018-08-01').timestamp()\nvtfe2 = pd.to_datetime('2018-10-01').timestamp()\n\nid_col = 'fullVisitorId'\nsort_col = 'visitStartTime'\ntr_df1 = train_df_r.loc[(train_df_r[sort_col]>=tfs1) & (train_df_r[sort_col]<tfe1), [id_col,'target']].copy()\nts_df1 = train_df_r.loc[(train_df_r[sort_col]>=vtfs1) & (train_df_r[sort_col]<vtfe1), [id_col,'target']].copy()\nprint(f'tr_df1: {tr_df1.shape}, ts_df1: {ts_df1.shape}')\ntr_df2 = train_df_r.loc[(train_df_r[sort_col]>=tfs2) & (train_df_r[sort_col]<tfe2), [id_col,'target']].copy()\nts_df2 = train_df_r.loc[(train_df_r[sort_col]>=vtfs2) & (train_df_r[sort_col]<vtfe2), [id_col,'target']].copy()\ndel train_df_r\ngc.collect()\nprint(f'tr_df2: {tr_df2.shape}, ts_df2: {ts_df2.shape}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dfc99722be136b130c09d9e683d278b0357c41cf"},"cell_type":"code","source":"tr_df1 = tr_df1.groupby(id_col).sum()\nts_df1 = ts_df1.groupby(id_col).sum()\ngc.collect()\nprint(f'tr_df1: {tr_df1.shape}, ts_df1: {ts_df1.shape}')\ntr_df2 = tr_df2.groupby(id_col).sum()\nts_df2 = ts_df2.groupby(id_col).sum()\ngc.collect()\nprint(f'tr_df2: {tr_df2.shape}, ts_df2: {ts_df2.shape}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b914cce1597099245a7af14db4d3ebce198ff4bc"},"cell_type":"code","source":"df1 = tr_df1.join(ts_df1, lsuffix='_ls')\ndf2 = tr_df2.join(ts_df2, lsuffix='_ls')\nprint(f'df1: {df1.shape}, df2: {df2.shape}')\nprint(f\"df1's target not null: {np.sum(df1.target.notnull())}, df2's target not null: {np.sum(df2.target.notnull())}\")\ndf1 = np.log1p(df1.fillna(0))\ndf2 = np.log1p(df2.fillna(0))\ngc.collect()\nprint(f'df1.target>0: {np.sum(df1.target>0)}, df2.target>0: {np.sum(df2.target>0)}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"07abe737189da5ad66be9d5fa106f27ecdd97ec1"},"cell_type":"code","source":"print(np.intersect1d(df1.index, df2.index).shape)\ncids = np.intersect1d(df1.loc[df1.target>0].index, df2.loc[df2.target>0].index)\nprint(cids.shape)\ndf1.loc[cids]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"62e4861638bb522ea41cadf3b56c1cc54c8e4dc7"},"cell_type":"code","source":"df2.loc[cids]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b11007826d57353e9178cb968031413408721c40"},"cell_type":"code","source":"s1 = df1.target\nprint(s1.describe())\nprint()\ns1 = s1.loc[s1>0]\nprint(s1.describe())\nprint('----------------------------------')\ns2 = df2.target\nprint(s2.describe())\nprint()\ns2 = s2.loc[s2>0]\nprint(s2.describe())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"55b662be0095da8dd54fe489a218c22cd3ee9473"},"cell_type":"code","source":"s1 = df1.target_ls\nprint(s1.describe())\nprint()\ns1 = s1.loc[s1>0]\nprint(s1.describe())\nprint('----------------------------------')\ns2 = df2.target_ls\nprint(s2.describe())\nprint()\ns2 = s2.loc[s2>0]\nprint(s2.describe())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"736986c4f2c30fb33599cf06e4bd725a1a6406a6"},"cell_type":"code","source":"s1 = df1.loc[(df1.target_ls>0)&(df1.target>0), 'target']\nprint(s1.describe())\nprint('----------------------------------')\ns2 = df2.loc[(df2.target_ls>0)&(df2.target>0), 'target']\nprint(s2.describe())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"251e751fcc216ed87b56a86bcd5988db207fca6d"},"cell_type":"code","source":"s1 = df1.loc[(df1.target_ls>0)&(df1.target>0), 'target_ls']\nprint(s1.describe())\nprint('----------------------------------')\ns2 = df2.loc[(df2.target_ls>0)&(df2.target>0), 'target_ls']\nprint(s2.describe())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9e4f4042f66be380b3b03ae3b7273c62eba26123"},"cell_type":"code","source":"s1 = df1.target_ls - df1.target\nprint(s1.describe())\nprint()\ns1 = s1.loc[s1!=0]\nprint(s1.describe())\nprint('----------------------------------')\ns2 = df2.target_ls -df2.target\nprint(s2.describe())\nprint()\ns2 = s2.loc[s2!=0]\nprint(s2.describe())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b2be471f15e91507132e913f58fb43cbf4f79092"},"cell_type":"code","source":"s1 = df1.loc[(df1.target_ls>0)&(df1.target>0), 'target_ls'] - df1.loc[(df1.target_ls>0)&(df1.target>0), 'target']\nprint(s1.describe())\nprint()\ns1 = s1.loc[s1!=0]\nprint(s1.describe())\nprint('----------------------------------')\ns2 = df2.loc[(df2.target_ls>0)&(df2.target>0), 'target_ls'] - df2.loc[(df2.target_ls>0)&(df2.target>0), 'target']\nprint(s2.describe())\nprint()\ns2 = s2.loc[s2!=0]\nprint(s2.describe())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f147e9105001b1916aea51dddcafe9b7b00f188f"},"cell_type":"code","source":"del tr_df1,ts_df1,tr_df2,ts_df2,df1,df2\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8e76339b2aeed04c938319dc57b63b0613e1d42e"},"cell_type":"markdown","source":"# combine"},{"metadata":{"trusted":true,"_uuid":"bd9451450d4fb479b0b82b0a774dc4e9c9f88985"},"cell_type":"code","source":"train_df_w = pd.read_pickle(os.path.join(data_dir, 'train_df_w'), compression='gzip')\ntest_df_w = pd.read_pickle(os.path.join(data_dir, 'test_df_w'), compression='gzip')\nprint(f'train_df_w: {train_df_w.shape}, test_df_w: {test_df_w.shape}')\ntrain_df_u = pd.read_pickle(os.path.join(data_dir, 'train_df_u'), compression='gzip')\ntest_df_u = pd.read_pickle(os.path.join(data_dir, 'test_df_u'), compression='gzip')\nprint(f'train_df_u: {train_df_u.shape}, test_df_u: {test_df_u.shape}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"be2a71d821acc6d5833b392c5cd58ef4e5b205e3"},"cell_type":"code","source":"train_df = train_df_w.join(train_df_u)\ntest_df = test_df_w.join(test_df_u)\ndel train_df_w,test_df_w,train_df_u,test_df_u\ngc.collect()\nprint(f'train_df: {train_df.shape}, test_df: {test_df.shape}')\nprint(np.sum(train_df.isnull().values))\nprint(np.sum(test_df.isnull().values))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f774874f98ec7746dee22c933b94a80f423afaab"},"cell_type":"code","source":"sort_col = 'visitStartTime_w_min'\ntrain_df = train_df.reset_index()\ngc.collect()\ntrain_df = train_df.sort_values(by=[id_col, sort_col])\ngc.collect()\ntrain_df = train_df.reset_index(drop=True)\ngc.collect()\n\ntest_df = test_df.reset_index()\ngc.collect()\ntest_df = test_df.sort_values(by=[id_col, sort_col])\ngc.collect()\ntest_df = test_df.reset_index(drop=True)\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"289df7a95e71834a28da226f2634ef876c7a9e1f"},"cell_type":"code","source":"def label_target(tfs, tfe, vtfs, vtfe):\n    tr_df = train_df.loc[(((train_df[tm_col1]>=tfs) & (train_df[tm_col1]<tfe))\n                         | ((train_df[tm_col2]>=tfs) & (train_df[tm_col2]<tfe)))]\n    vids = train_df.loc[(((train_df[tm_col1]>=vtfs) & (train_df[tm_col1]<vtfe))\n                         | ((train_df[tm_col2]>=vtfs) & (train_df[tm_col2]<vtfe))), id_col].unique()\n    tr_df = tr_df.loc[~tr_df[id_col].isin(vids)]\n    gc.collect()\n    tr_df = tr_df.loc[tr_df.groupby(id_col)[tm_col1].idxmax()].copy()\n    tr_df['target'] = 0\n    \n    return tr_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"004f263e295ad9b5966af150fda813cc720ed236"},"cell_type":"code","source":"tm_col1 = 'visitStartTime_w_min'\ntm_col2 = 'visitStartTime_w_max'\ntr_df1 = label_target(tfs1, tfe1, vtfs1, vtfe1)\ngc.collect()\ntr_df2 = label_target(tfs2, tfe2, vtfs2, vtfe2)\ngc.collect()\nprint(f'tr_df1: {tr_df1.shape}, tr_df2: {tr_df2.shape}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"69a721a6da6a29f39f433a6c6c1b1a789a58eed0"},"cell_type":"code","source":"ids3 = train_df.loc[(((train_df[tm_col1]>=tfe1) & (train_df[tm_col2]<tfs2)) | (train_df[tm_col2]<tfs1)), id_col].unique()\ntr_df3 = train_df.loc[train_df[id_col].isin(ids3)]\ntr_df3 = tr_df3.loc[tr_df3.groupby(id_col)[tm_col1].idxmax()]\ntr_df3 = tr_df3.loc[(((tr_df3[tm_col1]>=tfe1) & (tr_df3[tm_col2]<tfs2)) | (tr_df3[tm_col2]<tfs1))].copy()\ngc.collect()\ntr_df3['target'] = 0\nprint(f'tr_df3: {tr_df3.shape}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6c9f8c21ab744aa078eccd3d46ad1e8a6bd07a0e"},"cell_type":"code","source":"cnt = train_df[id_col].value_counts()\nids4 = cnt.loc[cnt>1].index.values\ntr_df4 = train_df.loc[train_df[id_col].isin(ids4)].copy()\ngc.collect()\nprint(f'tr_df4: {tr_df4.shape}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0c8c01717c6018ab7437b41e56e0ef7d082bc0cd"},"cell_type":"code","source":"t_col = 'target_w_sum'\ntr_df4['target'] = -1\ngp = tr_df4.groupby(id_col)\ncols = []\nfor i in range(2, 5):\n    col = f'{t_col}_{i}'\n    cols.append(col)\n    tr_df4[col] = gp[t_col].shift(-i)\n    col = f'{tm_col1}_{i}'\n    cols.append(col)\n    tr_df4[col] = gp[tm_col1].shift(-i)\n    col = f'{tm_col2}_{i}'\n    cols.append(col)\n    tr_df4[col] = gp[tm_col2].shift(-i)\nfor i in range(2, 5):\n    ind4 = (tr_df4['target'] < 0) & tr_df4[f'{t_col}_{i}'].notnull() & (\n        tr_df4[f'{tm_col2}_{i}'] - tr_df4[tm_col1] > 60 * 24 * 3600)\n    ind40 = ind4 & (tr_df4[f'{tm_col1}_{i}'] - tr_df4[tm_col2] > 9 * 30 * 24 * 3600)\n    tr_df4.loc[ind40, 'target'] = 0\n    ind41 = ind4 & (tr_df4[f'{tm_col1}_{i}'] - tr_df4[tm_col2] <= 9 * 30 * 24 * 3600)\n    tr_df4.loc[ind41, 'target'] = tr_df4.loc[ind41, f'{t_col}_{i}']\ntr_df4 = tr_df4.loc[tr_df4.target>=0].drop(cols, axis=1)\ngc.collect()\nprint(f'tr_df4: {tr_df4.shape}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2d19a5ba219207554c773f0b34a080fc5f43ce09"},"cell_type":"code","source":"train_df = tr_df1.append([tr_df2,tr_df3,tr_df4], sort=False)\ngc.collect()\ntrain_df = train_df.sort_values(by=[id_col, sort_col])\ngc.collect()\ntrain_df = train_df.reset_index(drop=True)\ngc.collect()\n\ntrain_df = train_df.drop('target_w_sum', axis=1)\ngc.collect()\ntest_df = test_df.drop('target_w_sum', axis=1)\ngc.collect()\nprint(f'train_df: {train_df.shape}, test_df: {test_df.shape}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e978080b056d495bb59dbed0e08c39073b7b6e32"},"cell_type":"code","source":"train_df = train_df.drop('window_id', axis=1)\ntest_df = test_df.loc[0==test_df.window_id].drop('window_id', axis=1)\nprint(f'train_df: {train_df.shape}, test_df: {test_df.shape}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6cbce7c0fdf96957e4932fc6b1bf609425b80ed6"},"cell_type":"code","source":"sub_df = pd.read_csv('../input/ga-customer-revenue-prediction/sample_submission_v2.csv', dtype={id_col: 'str'})\nprint(sub_df.shape)\ns = test_df[id_col].unique()\nsub_ids = sub_df[id_col].unique()\nprint(s.shape, sub_ids.shape)\nsub_ids = np.intersect1d(s, sub_ids)\ndel sub_df, s\ngc.collect()\nprint(sub_ids.shape)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1f13ea74fde81d9356caffd3c17da52ad28175a0"},"cell_type":"markdown","source":"# post process"},{"metadata":{"trusted":true,"_uuid":"66e8986efe7c97311dad5d4b006bb3ac115c1dc5"},"cell_type":"code","source":"def find_still_cols(df, diff_num_threshold=30):\n    still_cols1,still_cols2 = [],[]\n    for col in df.columns:\n        cnt = df[col].value_counts(dropna=False)\n        if cnt.shape[0] <= 1:\n            still_cols1.append(col)\n        elif df.shape[0] - cnt.iloc[0] < diff_num_threshold:\n            still_cols2.append(col)\n    return still_cols1,still_cols2","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"959755039f1bb8642ec48cb090774e2d52e4732b"},"cell_type":"code","source":"still_cols1,still_cols2 = find_still_cols(train_df)\nprint(f'still_cols1({len(still_cols1)}): {still_cols1}')\nprint(f'still_cols2({len(still_cols2)}): {still_cols2}')\nstill_cols = still_cols1 + still_cols2\ntrain_df = train_df.drop(still_cols, axis=1)\ngc.collect()\ntest_df = test_df.drop(still_cols, axis=1)\ngc.collect()\nprint(f'train_df: {train_df.shape}, test_df: {test_df.shape}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3f14b9144e7c5479990efc8f0a2aa3e17e404812"},"cell_type":"code","source":"def find_same_cols(df, diff_num_threshold=30, detail=False):\n    same_cols = set()\n    for col1 in df.columns:\n        if col1 not in same_cols:\n            column1 = df[col1]\n            for col2 in df.columns:\n                if col2 > col1 and col2 not in same_cols:\n                    diff_num = np.sum(column1 != df[col2])\n                    if diff_num < diff_num_threshold:\n                        if detail:\n                            print(f'{col1} - {col2} = {diff_num}')\n                        same_cols.add(col2)\n    return list(same_cols)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a90c37051074c94e91dc3c54b7d92e21607d6c86"},"cell_type":"code","source":"same_cols = sorted(find_same_cols(train_df, detail=True))\nprint(f'same_cols({len(same_cols)}): {same_cols}')\ntrain_df = train_df.drop(same_cols, axis=1)\ngc.collect()\ntest_df = test_df.drop(same_cols, axis=1)\ngc.collect()\nprint(f'train_df: {train_df.shape}, test_df: {test_df.shape}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3299304554f1df932a433577c0f6700a9178f9bf"},"cell_type":"code","source":"cols = [col for col in test_df.columns if col[-1].isdigit()]\nfor col in tqdm(cols):\n    cnt = Counter(train_df[col])\n    cnt_pair = sorted(cnt.items(), key=lambda pair: (pair[1], pair[0]), reverse=True)\n    unseen_value = -127 if -127 in cnt else '-127' if '-127' in cnt else cnt_pair[0][0]\n    data_type = test_df[col].dtype\n    test_df[col] = test_df[col].apply(lambda ele: unseen_value if ele not in cnt else ele).astype(data_type)\n    del cnt,cnt_pair\n    gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7f99cd15f5ebc880b040c70185d913f6a6702f19"},"cell_type":"code","source":"train_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8d85d1dc688c392f474fcdad96bdf28417888f71"},"cell_type":"code","source":"test_df","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"dda9c6583c0b176e470c00731eeb6c11f9685ff8"},"cell_type":"markdown","source":"# check"},{"metadata":{"trusted":true,"_uuid":"c2fe1c49289c64792b5815d75032f449aeffc2dc"},"cell_type":"code","source":"y = train_df.target\nprint(y.describe())\nprint()\nprint(y.loc[y>0].describe())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d9ad84d27a2c35e9b9ff73026b3fadc9908c7abc"},"cell_type":"code","source":"s = test_df.dtypes\nss = list(s.loc[s == np.object].index)\nprint(len(ss))\nprint(ss)\nss = list(s.loc[s != np.object].index)\nprint(len(ss))\nprint(ss)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2770e3f437b03a1d6543fd31427673d290dacb91","scrolled":true},"cell_type":"code","source":"df = train_df\nfor col in ss:\n    print(f'{col}:\\t\\t{np.sum(df[col].isnull())}\\t{df[col].dtype.name}')\ndf.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2fb1ab31411b8d36aab97da851c148ed58c8f9af"},"cell_type":"code","source":"df = test_df\nfor col in ss:\n    print(f'{col}:\\t\\t{np.sum(df[col].isnull())}\\t{df[col].dtype.name}')\ndf.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"270e0d490e4127c9f51dd8cdfe52c048f0aa25e6"},"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}