{"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":"ae48a820a77d73b98cae7b3645452bafc78b6c51"},"cell_type":"markdown","source":"# load record data"},{"metadata":{"trusted":true,"_uuid":"22a4e5bdc2cacbcee605d38823f3c0efbc2a8d99"},"cell_type":"code","source":"data_dir = '../input/gs-flatten-data-analysis-1'\ntrain_df_r = pd.read_pickle(os.path.join(data_dir, 'train_df_r'), compression='gzip')\ntest_df_r = pd.read_pickle(os.path.join(data_dir, 'test_df_r'), compression='gzip')\nprint(f'train_df_r: {train_df_r.shape}, test_df_r: {test_df_r.shape}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1603336342bd46732a93174f7387baa77bc362f2"},"cell_type":"markdown","source":"# window level"},{"metadata":{"trusted":true,"_uuid":"642d9339f73d301d5d35a58df6263c8460c0059d"},"cell_type":"code","source":"def agg_most_common(_df, _cols, n=3, window_type='u', gp_col=None):\n    gp_cols = [id_col]\n    if gp_col is not None:\n        gp_cols.append(gp_col)\n    \n    _df = _df.loc[:, _cols + gp_cols].set_index(gp_cols)\n    gc.collect()\n    cnt = _df.groupby(gp_cols)[_cols[0]].count()\n\n    ids1 = cnt.loc[1 == cnt].index\n    df1 = _df.loc[ids1].copy()\n    for _col in _cols:\n        for k in range(1, n):\n            df1[f'{_col}_{window_type}_{k}'] = -127\n            df1[f'{_col}_{window_type}_{k}'] = df1[f'{_col}_{window_type}_{k}'].astype(_df[_col].dtype)\n\n    ids2 = cnt.loc[2 == cnt].index\n    gp = _df.loc[ids2].groupby(gp_cols)\n    df2 = gp.min().join(gp.max(), rsuffix=f'_{window_type}_1')\n    for _col in _cols:\n        for k in range(2, n):\n            df2[f'{_col}_{window_type}_{k}'] = -127\n            df2[f'{_col}_{window_type}_{k}'] = df2[f'{_col}_{window_type}_{k}'].astype(_df[_col].dtype)\n        df2[f'{_col}_{window_type}_1'] = df2[f'{_col}_{window_type}_1'].astype(_df[_col].dtype)\n\n    def most_common(rows):\n        _cnt = sorted(Counter(rows).items(), key=lambda pair: (pair[1], pair[0]), reverse=True)\n        return [_cnt[i][0] if len(_cnt) > i else -127 for i in range(n)]\n\n    ids3 = cnt.loc[cnt >= 3].index\n    df3 = _df.loc[ids3].groupby(gp_cols).agg(most_common)\n    for _col in _cols:\n        for k in range(1, n):\n            df3[f'{_col}_{window_type}_{k}'] = df3[_col].str.get(k)\n            df3[f'{_col}_{window_type}_{k}'] = df3[f'{_col}_{window_type}_{k}'].astype(_df[_col].dtype)\n        df3[_col] = df3[_col].str.get(0)\n        df3[_col] = df3[_col].astype(_df[_col].dtype)\n\n    return df1.append([df2, df3], sort=False).rename(columns={_col: f'{_col}_{window_type}_0' for _col in _cols})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"25903eca6d75bc908dc78fd343920ab02c9dabb2"},"cell_type":"code","source":"id_col = 'fullVisitorId'\n\ncols = ['device.isMobile','totals.bounces','totals.newVisits','trafficSource.adwordsClickInfo.isVideoAd',\n        'trafficSource.isTrueDirect','visit_weekend']\ntrain_df_w = agg_most_common(train_df_r, cols, n=2, window_type='w', gp_col='window_id')\ngc.collect()\ntest_df_w = agg_most_common(test_df_r, cols, n=2, window_type='w', gp_col='window_id')\ngc.collect()\nprint(f'train_df_w: {train_df_w.shape}, test_df_w: {test_df_w.shape}')\n\ncols = ['channelGrouping','customDimensions','device.browser','device.deviceCategory','device.operatingSystem','geoNetwork.city',\n        'geoNetwork.continent','geoNetwork.country','geoNetwork.metro','geoNetwork.networkDomain','geoNetwork.region',\n        'geoNetwork.subContinent','trafficSource.adContent','trafficSource.adwordsClickInfo.adNetworkType',\n        'trafficSource.adwordsClickInfo.slot','trafficSource.campaign','trafficSource.keyword','trafficSource.medium',\n        'trafficSource.referralPath','trafficSource.source','ref_level_1','ref_level_2','ref_level_3','ref_level_4','ref_level_n',\n        'src_level_1','src_level_2','src_level_3','src_level_n','visit_year']\ntrain_df_w1 = agg_most_common(train_df_r, cols, n=3, window_type='w', gp_col='window_id')\ntrain_df_w = train_df_w.join(train_df_w1)\ndel train_df_w1\ngc.collect()\ntest_df_w1 = agg_most_common(test_df_r, cols, n=3, window_type='w', gp_col='window_id')\ntest_df_w = test_df_w.join(test_df_w1)\ndel test_df_w1\ngc.collect()\nprint(f'train_df_w: {train_df_w.shape}, test_df_w: {test_df_w.shape}')\n\ncols = ['visit_quarter']\ntrain_df_w1 = agg_most_common(train_df_r, cols, n=4, window_type='w', gp_col='window_id')\ntrain_df_w = train_df_w.join(train_df_w1)\ndel train_df_w1\ngc.collect()\ntest_df_w1 = agg_most_common(test_df_r, cols, n=4, window_type='w', gp_col='window_id')\ntest_df_w = test_df_w.join(test_df_w1)\ndel test_df_w1\ngc.collect()\nprint(f'train_df_w: {train_df_w.shape}, test_df_w: {test_df_w.shape}')\n\ncols = ['visit_month','visit_day','visit_hour','visit_week','visit_dayofweek','visit_dayofyear']\ntrain_df_w1 = agg_most_common(train_df_r, cols, n=7, window_type='w', gp_col='window_id')\ntrain_df_w = train_df_w.join(train_df_w1)\ndel train_df_w1\ngc.collect()\ntest_df_w1 = agg_most_common(test_df_r, cols, n=7, window_type='w', gp_col='window_id')\ntest_df_w = test_df_w.join(test_df_w1)\ndel test_df_w1\ngc.collect()\nprint(f'train_df_w: {train_df_w.shape}, test_df_w: {test_df_w.shape}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"53af2b4ab659c15222c486bf27f0613368484b37"},"cell_type":"code","source":"def agg_numeric_cols(_df, gdf, cols, agg_methods, window_type='u', gp_col=None, positive=False):\n    gp_cols = [id_col]\n    if gp_col is not None:\n        gp_cols.append(gp_col)\n    \n    data_types = _df.dtypes\n    _df = _df.loc[:, cols + gp_cols].copy()\n    if positive:\n        for _col in cols:\n            _df.loc[_df[_col] <= 0, _col] = np.nan\n    \n    gp = _df.groupby(gp_cols)\n    if 'mean' in agg_methods:\n        gdf = gdf.join(gp.mean().fillna(0))\n        for _col in cols:\n            gdf[_col] = gdf[_col].astype(data_types[_col])\n        gdf = gdf.rename(columns={_col: f'{_col}_{window_type}_mean' for _col in cols})\n        gc.collect()\n    if 'std' in agg_methods:\n        gdf = gdf.join(gp.std().fillna(0).astype(np.float32))\n        gdf = gdf.rename(columns={_col: f'{_col}_{window_type}_std' for _col in cols})\n        gc.collect()\n    if 'sum' in agg_methods:\n        gdf = gdf.join(gp.sum().fillna(0))\n        gdf = gdf.rename(columns={_col: f'{_col}_{window_type}_sum' for _col in cols})\n        gc.collect()\n    if 'min' in agg_methods:\n        gdf = gdf.join(gp.min().fillna(0))\n        gdf = gdf.rename(columns={_col: f'{_col}_{window_type}_min' for _col in cols})\n        for _col in cols:\n            gdf[f'{_col}_{window_type}_min'] = gdf[f'{_col}_{window_type}_min'].astype(data_types[_col])        \n        gc.collect()\n    if 'max' in agg_methods:\n        gdf = gdf.join(gp.max().fillna(0))\n        gdf = gdf.rename(columns={_col: f'{_col}_{window_type}_max' for _col in cols})                     \n        for _col in cols:\n            gdf[f'{_col}_{window_type}_max'] = gdf[f'{_col}_{window_type}_max'].astype(data_types[_col])\n        gc.collect()\n    return gdf","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3af8fdccb68173efecd4c150f94707fd71e93291"},"cell_type":"code","source":"cols = ['max_diff_time','max_price','min_diff_time','min_price','totals.extraRevenue','totals.hits','totals.pageviews',\n        'totals.sessionQualityDim','totals.timeOnSite','totals.totalTransactionRevenue','user_g_span','visit_day_pv',\n       'visit_hour_pv']\nagg_methods = ['mean', 'std', 'min', 'max']\ntrain_df_w = agg_numeric_cols(train_df_r, train_df_w, cols, agg_methods, window_type='w', gp_col='window_id', positive=True)\ngc.collect()\ntest_df_w = agg_numeric_cols(test_df_r, test_df_w, cols, agg_methods, window_type='w', gp_col='window_id', positive=True)\ngc.collect()\nprint(f'train_df_w: {train_df_w.shape}, test_df_w: {test_df_w.shape}')\n\ncols = ['visit_delay']\nagg_methods = ['mean', 'std', 'min', 'max']\ntrain_df_w = agg_numeric_cols(train_df_r, train_df_w, cols, agg_methods, window_type='w', gp_col='window_id', positive=False)\ngc.collect()\ntest_df_w = agg_numeric_cols(test_df_r, test_df_w, cols, agg_methods, window_type='w', gp_col='window_id', positive=False)\ngc.collect()\nprint(f'train_df_w: {train_df_w.shape}, test_df_w: {test_df_w.shape}')\n\ncols = ['mean_diff_time','mean_price','mean_time','std_diff_time','std_price','max_price_ratio','mean_price_ratio',\n        'min_price_ratio','totals.transactions','trafficSource.adwordsClickInfo.page','user_v_pv','user_s_pv','user_s_idle',\n        'user_s_span','user_v_idle','user_v_span']\nagg_methods = ['mean', 'min', 'max']\ntrain_df_w = agg_numeric_cols(train_df_r, train_df_w, cols, agg_methods, window_type='w', gp_col='window_id', positive=True)\ngc.collect()\ntest_df_w = agg_numeric_cols(test_df_r, test_df_w, cols, agg_methods, window_type='w', gp_col='window_id', positive=True)\ngc.collect()\nprint(f'train_df_w: {train_df_w.shape}, test_df_w: {test_df_w.shape}')\n\ncols = ['ref_level_cnt','src_level_cnt','visit_idle']\nagg_methods = ['mean', 'min', 'max']\ntrain_df_w = agg_numeric_cols(train_df_r, train_df_w, cols, agg_methods, window_type='w', gp_col='window_id', positive=False)\ngc.collect()\ntest_df_w = agg_numeric_cols(test_df_r, test_df_w, cols, agg_methods, window_type='w', gp_col='window_id', positive=False)\ngc.collect()\nprint(f'train_df_w: {train_df_w.shape}, test_df_w: {test_df_w.shape}')\n\ncols = ['user_w_pv','user_w_idle','user_w_span']\nagg_methods = ['max']\ntrain_df_w = agg_numeric_cols(train_df_r, train_df_w, cols, agg_methods, window_type='w', gp_col='window_id', positive=False)\ngc.collect()\ntest_df_w = agg_numeric_cols(test_df_r, test_df_w, cols, agg_methods, window_type='w', gp_col='window_id', positive=False)\ngc.collect()\nprint(f'train_df_w: {train_df_w.shape}, test_df_w: {test_df_w.shape}')\n\ncols = ['visitStartTime']\nagg_methods = ['min', 'max']\ntrain_df_w = agg_numeric_cols(train_df_r, train_df_w, cols, agg_methods, window_type='w', gp_col='window_id', positive=False)\ngc.collect()\ntest_df_w = agg_numeric_cols(test_df_r, test_df_w, cols, agg_methods, window_type='w', gp_col='window_id', positive=False)\ngc.collect()\nprint(f'train_df_w: {train_df_w.shape}, test_df_w: {test_df_w.shape}')\n\ncols = ['target']\nagg_methods = ['sum']\ntrain_df_w = agg_numeric_cols(train_df_r, train_df_w, cols, agg_methods, window_type='w', gp_col='window_id', positive=False)\ngc.collect()\ntest_df_w = agg_numeric_cols(test_df_r, test_df_w, cols, agg_methods, window_type='w', gp_col='window_id', positive=False)\ngc.collect()\nprint(f'train_df_w: {train_df_w.shape}, test_df_w: {test_df_w.shape}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1a6446f23f2a23e7f90eaaa47e91cfb734932e91"},"cell_type":"code","source":"def stat_window_cnt(_df, gdf, cols=None, window_type='u', gp_col=None):\n    cols = cols if cols else ['visitId','session_id']\n    gp_cols = [id_col]\n    if gp_col is not None:\n        gp_cols.append(gp_col)\n    \n    _df = _df[cols + gp_cols]\n    gc.collect()\n    gdf = gdf.join(_df.groupby(gp_cols)[cols].nunique().rename(columns={_col: f'{_col}_{window_type}_cnt' for _col in cols}))\n    gc.collect()\n    gdf[f'visitId_{window_type}_cnt'] = gdf[f'visitId_{window_type}_cnt'].astype(np.int16)\n    gdf[f'session_id_{window_type}_cnt'] = gdf[f'session_id_{window_type}_cnt'].astype(np.int8)\n    gc.collect()\n    return gdf","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ca3d6e967ae287b5ad1f6ab65ff51c64af5bd427"},"cell_type":"code","source":"train_df_w = stat_window_cnt(train_df_r, train_df_w, window_type='w', gp_col='window_id')\ngc.collect()\ntest_df_w = stat_window_cnt(test_df_r, test_df_w, window_type='w', gp_col='window_id')\ngc.collect()\nprint(f'train_df_w: {train_df_w.shape}, test_df_w: {test_df_w.shape}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"faafe5527f62888efd8416663ca9356f99d6abfe"},"cell_type":"code","source":"def concat_by_window(_df, gdf, cols, window_type='u', gp_col=None):\n    gp_cols = [id_col]\n    if gp_col is not None:\n        gp_cols.append(gp_col)\n    \n    _df = _df.loc[:, cols + gp_cols].set_index(gp_cols)\n    gc.collect()\n    cnt = _df.groupby(gp_cols)[cols[0]].count()\n\n    ids1 = cnt.loc[1 == cnt].index\n    df1 = _df.loc[ids1]\n\n    ids2 = cnt.loc[cnt >= 2].index\n    df2 = _df.loc[ids2].groupby(gp_cols).agg(lambda rows: ' '.join(rows))\n    gdf = gdf.join(df1.append(df2, sort=False).rename(columns={_col: f'{window_type}_{_col}' for _col in cols}))\n    \n    return gdf","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b72768abfd3c0c32dbbba393a01f12c4d02bbf96"},"cell_type":"code","source":"cols = ['exitScreenNames_src','promoNames_src','productNames_src','productCategorys_src','hitProductNames_src','device.browser_src',\n        'trafficSource.adContent_src','trafficSource.keyword_src','trafficSource.referralPath_src','trafficSource.source_src']\ntrain_df_w = concat_by_window(train_df_r, train_df_w, cols, window_type='w', gp_col='window_id')\ngc.collect()\ntest_df_w = concat_by_window(test_df_r, test_df_w, cols, window_type='w', gp_col='window_id')\ngc.collect()\nprint(f'train_df_w: {train_df_w.shape}, test_df_w: {test_df_w.shape}')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f208efe6d3f6a912664ae7282bc70f4580ef6376"},"cell_type":"markdown","source":"## check"},{"metadata":{"trusted":true,"_uuid":"421b48c697d99e4bafcbdb6a88a9714936d1a533"},"cell_type":"code","source":"s = train_df_w.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":"520de3ac2f6b34934682ef68a26c17bf902d2446"},"cell_type":"code","source":"df = train_df_w\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":"447d863dff6582b8eb37d4a6fc9f9384883133fe"},"cell_type":"code","source":"df = test_df_w\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":"fad28f9b6c50f32ea4d483c5cae9678f186f61aa"},"cell_type":"markdown","source":"# user level"},{"metadata":{"trusted":true,"_uuid":"7ef3be9595948185144e2ae2121b8feddf2b6486"},"cell_type":"code","source":"def expand_most_common(_df, _cols, n=3, window_type='u', gp_col='window_id', sort_col='visitStartTime'):\n    gp_cols = [id_col]\n    if gp_col is not None:\n        gp_cols.append(gp_col)\n    \n    _df = _df.loc[:, _cols + gp_cols + [sort_col]].sort_values(by=sort_col)\n    gc.collect()\n    cnt = _df[id_col].value_counts()\n    \n    ids1 = cnt.loc[1 == cnt].index\n    df1 = _df.loc[_df[id_col].isin(ids1)].copy()\n    for _col in _cols:\n        for k in range(1, n):\n            df1[f'{_col}_{window_type}_{k}'] = -127\n            df1[f'{_col}_{window_type}_{k}'] = df1[f'{_col}_{window_type}_{k}'].astype(_df[_col].dtype)\n            \n    def most_common(eles):\n        _cnt = Counter()\n        commons = []\n        for ele in eles:\n            _cnt.update([ele])\n            cnt_pair = sorted(_cnt.items(), key=lambda pair: (pair[1], pair[0]), reverse=True)\n            commons.append([cnt_pair[i][0] if len(cnt_pair) > i else -127 for i in range(n)])\n        return commons\n    \n    ids2 = cnt.loc[cnt >= 2].index\n    df2 = _df.loc[_df[id_col].isin(ids2)]\n    gdf = df2.groupby(id_col)[_cols].transform(most_common)\n    gdf = gdf.loc[df2.groupby(gp_cols)[sort_col].idxmax()]\n    for _col in _cols:\n        for k in range(1, n):\n            gdf[f'{_col}_{window_type}_{k}'] = gdf[_col].str.get(k)\n            gdf[f'{_col}_{window_type}_{k}'] = gdf[f'{_col}_{window_type}_{k}'].astype(_df[_col].dtype)\n        gdf[_col] = gdf[_col].str.get(0)\n        gdf[_col] = gdf[_col].astype(_df[_col].dtype)\n    for _col in gp_cols:\n        gdf[_col] = df2[_col]\n        \n    gdf = df1.drop(sort_col, axis=1).append(gdf, sort=False).rename(\n        columns={_col: f'{_col}_{window_type}_0' for _col in _cols}).set_index(gp_cols)\n    gc.collect()\n    \n    return gdf","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dba551a1ebf6d8c6f6c2b7d94180a37af81c581e"},"cell_type":"code","source":"cols = ['device.isMobile','totals.bounces','totals.newVisits','trafficSource.adwordsClickInfo.isVideoAd',\n        'trafficSource.isTrueDirect','visit_weekend']\ntrain_df_u = expand_most_common(train_df_r, cols, n=2, window_type='u')\ngc.collect()\ntest_df_u = expand_most_common(test_df_r, cols, n=2, window_type='u')\ngc.collect()\nprint(f'train_df_u: {train_df_u.shape}, test_df_u: {test_df_u.shape}')\n\ncols = ['channelGrouping','customDimensions','device.browser','device.deviceCategory','device.operatingSystem','geoNetwork.city',\n        'geoNetwork.continent','geoNetwork.country','geoNetwork.metro','geoNetwork.networkDomain','geoNetwork.region',\n        'geoNetwork.subContinent','trafficSource.adContent','trafficSource.adwordsClickInfo.adNetworkType',\n        'trafficSource.adwordsClickInfo.slot','trafficSource.campaign','trafficSource.keyword','trafficSource.medium',\n        'trafficSource.referralPath','trafficSource.source','ref_level_1','ref_level_2','ref_level_3','ref_level_4','ref_level_n',\n        'src_level_1','src_level_2','src_level_3','src_level_n','visit_year']\ntrain_df_u1 = expand_most_common(train_df_r, cols, n=3, window_type='u')\ntrain_df_u = train_df_u.join(train_df_u1)\ndel train_df_u1\ngc.collect()\ntest_df_u1 = expand_most_common(test_df_r, cols, n=3, window_type='u')\ntest_df_u = test_df_u.join(test_df_u1)\ndel test_df_u1\ngc.collect()\nprint(f'train_df_u: {train_df_u.shape}, test_df_u: {test_df_u.shape}')\n\ncols = ['visit_quarter']\ntrain_df_u1 = expand_most_common(train_df_r, cols, n=4, window_type='u')\ntrain_df_u = train_df_u.join(train_df_u1)\ndel train_df_u1\ngc.collect()\ntest_df_u1 = expand_most_common(test_df_r, cols, n=4, window_type='u')\ntest_df_u = test_df_u.join(test_df_u1)\ndel test_df_u1\ngc.collect()\nprint(f'train_df_u: {train_df_u.shape}, test_df_u: {test_df_u.shape}')\n\ncols = ['visit_month','visit_day','visit_hour','visit_week','visit_dayofweek','visit_dayofyear']\ntrain_df_u1 = expand_most_common(train_df_r, cols, n=7, window_type='u')\ntrain_df_u = train_df_u.join(train_df_u1)\ndel train_df_u1\ngc.collect()\ntest_df_u1 = expand_most_common(test_df_r, cols, n=7, window_type='u')\ntest_df_u = test_df_u.join(test_df_u1)\ndel test_df_u1\ngc.collect()\nprint(f'train_df_u: {train_df_u.shape}, test_df_u: {test_df_u.shape}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"26bc9f95a4147047bcb57e73f183bfec4f0a5d4a"},"cell_type":"code","source":"def expand_numeric_cols(_df, _gdf, cols, expand_methods, window_type='u', gp_col='window_id', positive=False, \n                        sort_col='visitStartTime'):\n    gp_cols = [id_col]\n    if gp_col is not None:\n        gp_cols.append(gp_col)\n    \n    data_types = _df.dtypes\n    cnt = _df[id_col].value_counts()\n    \n    ids1 = cnt.loc[1 == cnt].index\n    df1 = _df.loc[_df[id_col].isin(ids1)]\n    gdf1 = pd.DataFrame()\n    for _col in gp_cols:\n        gdf1[_col] = df1[_col]\n    for _col in cols:\n        column = df1[_col]\n        for agg_method in expand_methods:\n            if 'std' != agg_method:\n                gdf1[f'{_col}_{window_type}_{agg_method}'] = column\n            else:\n                gdf1[f'{_col}_{window_type}_std'] = 0\n                gdf1[f'{_col}_{window_type}_std'] = gdf1[f'{_col}_{window_type}_std'].astype(np.float32)\n    gdf1 = gdf1.set_index(gp_cols)\n    del df1\n    gc.collect()\n    \n    ids2 = cnt.loc[cnt > 1].index\n    _df = _df.loc[_df[id_col].isin(ids2)]\n    _df = _df.loc[:, cols + gp_cols + ([] if sort_col in cols else [sort_col])].sort_values(by=sort_col).copy()\n    if positive:\n        for _col in cols:\n            _df.loc[_df[_col] <= 0, _col] = np.nan\n    gc.collect()\n    \n    gp = _df.groupby(id_col)[cols].expanding()\n    gdf = None\n    if 'mean' in expand_methods:\n        gdf = gp.mean().fillna(0)\n        for _col in cols:\n            gdf[_col] = gdf[_col].astype(data_types[_col])\n        gdf = gdf.rename(columns={_col: f'{_col}_{window_type}_mean' for _col in cols})\n        gc.collect()\n    if 'std' in expand_methods:\n        cur_gdf = gp.std().fillna(0).astype(np.float32)\n        gdf = gdf.join(cur_gdf) if gdf is not None else cur_gdf\n        gdf = gdf.rename(columns={_col: f'{_col}_{window_type}_std' for _col in cols})\n        gc.collect()\n    if 'sum' in expand_methods:\n        cur_gdf = gp.sum().fillna(0)\n        gdf = gdf.join(cur_gdf) if gdf is not None else cur_gdf\n        gdf = gdf.rename(columns={_col: f'{_col}_{window_type}_sum' for _col in cols})\n        gc.collect()\n    if 'min' in expand_methods:\n        cur_gdf = gp.min().fillna(0)\n        gdf = gdf.join(cur_gdf) if gdf is not None else cur_gdf\n        gdf = gdf.rename(columns={_col: f'{_col}_{window_type}_min' for _col in cols})\n        for _col in cols:\n            gdf[f'{_col}_{window_type}_min'] = gdf[f'{_col}_{window_type}_min'].astype(data_types[_col])        \n        gc.collect()\n    if 'max' in expand_methods:\n        cur_gdf = gp.max().fillna(0)\n        gdf = gdf.join(cur_gdf) if gdf is not None else cur_gdf\n        gdf = gdf.rename(columns={_col: f'{_col}_{window_type}_max' for _col in cols})                     \n        for _col in cols:\n            gdf[f'{_col}_{window_type}_max'] = gdf[f'{_col}_{window_type}_max'].astype(data_types[_col])\n        gc.collect()\n        \n    gdf.index=gdf.index.droplevel()\n    gdf = gdf.loc[_df.groupby(gp_cols)[sort_col].idxmax()]\n    for _col in gp_cols:\n        gdf[_col] = _df[_col]\n        \n    gdf = gdf1.append(gdf.set_index(gp_cols), sort=False)        \n    _gdf = _gdf.join(gdf)\n    del _df,gdf1,gdf,gp\n    gc.collect()\n    \n    return _gdf","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d78b4eb3eda3ebecbb441c0b2a1435c58ff49489"},"cell_type":"code","source":"cols = ['max_diff_time','max_price','min_diff_time','min_price','totals.extraRevenue','totals.hits','totals.pageviews',\n        'totals.sessionQualityDim','totals.timeOnSite','totals.totalTransactionRevenue','user_g_span','visit_day_pv',\n       'visit_hour_pv']\nagg_methods = ['mean', 'std', 'min', 'max']\ntrain_df_u = expand_numeric_cols(train_df_r, train_df_u, cols, agg_methods, positive=True)\ngc.collect()\ntest_df_u = expand_numeric_cols(test_df_r, test_df_u, cols, agg_methods, positive=True)\ngc.collect()\nprint(f'train_df_u: {train_df_u.shape}, test_df_u: {test_df_u.shape}')\n\ncols = ['visit_delay']\nagg_methods = ['mean', 'std', 'min', 'max']\ntrain_df_u = expand_numeric_cols(train_df_r, train_df_u, cols, agg_methods, positive=False)\ngc.collect()\ntest_df_u = expand_numeric_cols(test_df_r, test_df_u, cols, agg_methods, positive=False)\ngc.collect()\nprint(f'train_df_u: {train_df_u.shape}, test_df_u: {test_df_u.shape}')\n\ncols = ['mean_diff_time','mean_price','mean_time','std_diff_time','std_price','max_price_ratio','mean_price_ratio',\n        'min_price_ratio','totals.transactions','trafficSource.adwordsClickInfo.page','user_v_pv','user_s_pv','user_s_idle',\n        'user_s_span','user_v_idle','user_v_span']\nagg_methods = ['mean', 'min', 'max']\ntrain_df_u = expand_numeric_cols(train_df_r, train_df_u, cols, agg_methods, positive=True)\ngc.collect()\ntest_df_u = expand_numeric_cols(test_df_r, test_df_u, cols, agg_methods, positive=True)\ngc.collect()\nprint(f'train_df_u: {train_df_u.shape}, test_df_u: {test_df_u.shape}')\n\ncols = ['ref_level_cnt','src_level_cnt','visit_idle']\nagg_methods = ['mean', 'min', 'max']\ntrain_df_u = expand_numeric_cols(train_df_r, train_df_u, cols, agg_methods, positive=False)\ngc.collect()\ntest_df_u = expand_numeric_cols(test_df_r, test_df_u, cols, agg_methods, positive=False)\ngc.collect()\nprint(f'train_df_u: {train_df_u.shape}, test_df_u: {test_df_u.shape}')\n\ncols = ['visitStartTime']\nagg_methods = ['min', 'max']\ntrain_df_u = expand_numeric_cols(train_df_r, train_df_u, cols, agg_methods, positive=False)\ngc.collect()\ntest_df_u = expand_numeric_cols(test_df_r, test_df_u, cols, agg_methods, positive=False)\ngc.collect()\nprint(f'train_df_u: {train_df_u.shape}, test_df_u: {test_df_u.shape}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0693a95c359690bae3e8662bd78bc1b609cb2f1f"},"cell_type":"code","source":"def expand_target_cols(_df, _gdf, expand_methods, t_col='target_w_sum', window_type='u', sort_col='visitStartTime_w_min',\n                       positive=False):\n    cnt = _df.groupby(id_col)[sort_col].count()\n    \n    ids1 = cnt.loc[1 == cnt].index\n    df1 = _df.loc[ids1, [t_col]].copy()\n    column = df1[t_col]\n    for agg_method in expand_methods:\n        if 'std' != agg_method:\n            df1[f'target_{window_type}_{agg_method}'] = column\n        else:\n            df1[f'target_{window_type}_std'] = 0\n            df1[f'target_{window_type}_std'] = df1[f'target_{window_type}_std'].astype(np.int64)\n    df1 = df1.drop(t_col, axis=1)\n    gc.collect()\n    \n    ids2 = cnt.loc[cnt > 1].index\n    _df = _df.loc[ids2, [t_col, sort_col]].sort_values(by=sort_col).copy()\n    if positive:\n        _df.loc[_df[t_col] <= 0, t_col] = np.nan\n    gc.collect()\n\n    gp = _df.groupby(id_col)[[t_col]].expanding()\n    gdf = None\n    for agg_method in expand_methods:\n        cur_gdf = gp.agg(agg_method).fillna(0).astype(np.int64).rename(\n            columns={t_col: f'target_{window_type}_{agg_method}'})\n        gdf = gdf.join(cur_gdf) if gdf is not None else cur_gdf\n        gc.collect()\n\n    gdf.index=gdf.index.droplevel()\n    gdf = df1.append(gdf, sort=False)\n    _gdf = _gdf.join(gdf)\n    del df1,_df,cur_gdf,gdf,gp\n    gc.collect()\n    \n    return _gdf","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b6165ec4f2426c52ee5668e1a5266d38b2b45af8"},"cell_type":"code","source":"agg_method = ['mean', 'std', 'sum', 'min', 'max']\ntrain_df_u = expand_target_cols(train_df_w, train_df_u, agg_method, positive=True)\ngc.collect()\ntest_df_u = expand_target_cols(test_df_w, test_df_u, agg_method, positive=True)\ngc.collect()\nprint(f'train_df_u: {train_df_u.shape}, test_df_u: {test_df_u.shape}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"93ee1389909b8aa53e47d70185be8f71896e28af"},"cell_type":"code","source":"def expand_window_cnt(_df, gdf, cols=None, window_type='u', sort_col='visitStartTime_w_min'):\n    cols = cols if cols else ['visitId_w_cnt','session_id_w_cnt']\n    \n    _df = _df.sort_values(by=sort_col)\n    gp = _df.groupby(id_col)\n    \n    cur_df = gp[sort_col].cumcount().astype(np.uint8)\n    cur_df.name = f'window_id_{window_type}_cnt'\n    gdf = gdf.join(cur_df)\n    gc.collect()\n    \n    gdf = gdf.join(gp[cols].cumsum().rename(\n        columns={_col: _col.replace('_w_', f'_{window_type}_') for _col in cols}))\n    del _df,gp,cur_df\n    gc.collect()\n    \n    return gdf","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a71ec519cf7ad52825f7c4378c68dd060334011e"},"cell_type":"code","source":"train_df_u = expand_window_cnt(train_df_w, train_df_u)\ngc.collect()\ntest_df_u = expand_window_cnt(test_df_w, test_df_u)\ngc.collect()\nprint(f'train_df_u: {train_df_u.shape}, test_df_u: {test_df_u.shape}')\n\ntrain_df_u['user_u_span'] = train_df_u['visitStartTime_u_max'] - train_df_u['visitStartTime_u_min']\ntest_df_u['user_u_span'] = test_df_u['visitStartTime_u_max'] - test_df_u['visitStartTime_u_min']\nprint(f'train_df_u: {train_df_u.shape}, test_df_u: {test_df_u.shape}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"36278332ca668433351af0a9dfb54fea9686ba34"},"cell_type":"code","source":"def expanded_concat_by_window(_df, _gdf, cols, window_type='u', sort_col='visitStartTime_w_min'):\n    cnt = _df.groupby(id_col)[sort_col].count()\n    \n    ids1 = cnt.loc[1 == cnt].index\n    df1 = _df.loc[ids1, cols].rename(columns={_col: _col.replace('w_', f'{window_type}_') for _col in cols})\n    gc.collect()\n    \n    def expanded_concat(eles):\n        infos = []\n        info = ''\n        for ele in eles:\n            info += ele + ' '\n            infos.append(info)\n        return infos\n    \n    ids2 = cnt.loc[cnt > 1].index\n    _df = _df.loc[ids2].sort_values(by=sort_col)\n    gc.collect()\n    gdf = _df.groupby(id_col)[cols].transform(expanded_concat).rename(\n        columns={_col: _col.replace('w_', f'{window_type}_') for _col in cols})\n    gc.collect()\n    gdf = df1.append(gdf, sort=False)\n    _gdf = _gdf.join(gdf) if _gdf is not None else gdf\n    del _df,df1,gdf\n    gc.collect()\n    \n    return _gdf","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"44ecf5ccdba31cf3e25c2a4ef2679dcf11bd772e"},"cell_type":"code","source":"cols = ['w_productNames_src','w_productCategorys_src','w_hitProductNames_src']\ntrain_df_u = expanded_concat_by_window(train_df_w, train_df_u, cols)\ngc.collect()\ntest_df_u = expanded_concat_by_window(test_df_w, test_df_u, cols)\ngc.collect()\nprint(f'train_df_u: {train_df_u.shape}, test_df_u: {test_df_u.shape}')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"afe2a03ca3905b4fa69f104eb7fe841cf3212d7d"},"cell_type":"markdown","source":"## check"},{"metadata":{"trusted":true,"_uuid":"2dfebbc3d43234c0b9618f0fa9db2670e1f20df0"},"cell_type":"code","source":"s = train_df_u.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":"6ae0d45f6ea2f1abcb9e2bb5abb3d0a6a03f5b39"},"cell_type":"code","source":"df = train_df_u\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":"d5584e09a6c9f3d5bdb7475fc6d4236dde5f0c29"},"cell_type":"code","source":"df = test_df_u\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":"8e76339b2aeed04c938319dc57b63b0613e1d42e"},"cell_type":"markdown","source":"# save"},{"metadata":{"trusted":true,"_uuid":"be2a71d821acc6d5833b392c5cd58ef4e5b205e3"},"cell_type":"code","source":"train_df_r.to_pickle('train_df_r', compression='gzip')\ndel train_df_r\ngc.collect()\ntest_df_r.to_pickle('test_df_r', compression='gzip')\ndel test_df_r\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0a98f77a77349f205245a2cb0bbaf93b51bb5765"},"cell_type":"code","source":"train_df_w.to_pickle('train_df_w', compression='gzip')\ndel train_df_w\ngc.collect()\ntest_df_w.to_pickle('test_df_w', compression='gzip')\ndel test_df_w\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"595d4433ccbb3816b126b904df82ed2a6800f78c"},"cell_type":"code","source":"train_df_u.to_pickle('train_df_u', compression='gzip')\ndel train_df_u\ngc.collect()\ntest_df_u.to_pickle('test_df_u', compression='gzip')\ndel test_df_u\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"140e8961c9348ba3a94deb7624783e21f6a84e89"},"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}