{"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_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-08-18T10:46:49.861039Z","iopub.execute_input":"2022-08-18T10:46:49.861679Z","iopub.status.idle":"2022-08-18T10:46:49.890023Z","shell.execute_reply.started":"2022-08-18T10:46:49.861577Z","shell.execute_reply":"2022-08-18T10:46:49.889050Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df = pd.read_csv('/kaggle/input/digital-turbine-auction-bid-price-prediction/train_data.csv')\ntest_df = pd.read_csv('/kaggle/input/digital-turbine-auction-bid-price-prediction/test_data.csv')","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:46:49.891657Z","iopub.execute_input":"2022-08-18T10:46:49.892064Z","iopub.status.idle":"2022-08-18T10:47:15.793170Z","shell.execute_reply.started":"2022-08-18T10:46:49.892030Z","shell.execute_reply":"2022-08-18T10:47:15.792176Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# imports","metadata":{}},{"cell_type":"code","source":"from matplotlib import pyplot as plt\nimport seaborn as sns\nfrom catboost import CatBoostRegressor\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.metrics import mean_squared_error\nfrom datetime import datetime\nfrom pytz import country_timezones","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:47:15.794917Z","iopub.execute_input":"2022-08-18T10:47:15.795286Z","iopub.status.idle":"2022-08-18T10:47:16.734909Z","shell.execute_reply.started":"2022-08-18T10:47:15.795246Z","shell.execute_reply":"2022-08-18T10:47:16.733976Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# dataframe info stats\n\ndef stats(data):\n    \n    maxx = []\n    minn = []\n    for i in data.columns:\n        maxx.append(data[i].value_counts().max())\n        minn.append(data[i].value_counts().min())\n\n    return pd.DataFrame(\n        {'nunique': data.nunique(),\n         'len': len(data),\n        # 'nunique/len': data.nunique()/len(data),\n         'types':data.dtypes,\n         'Nulls' : data.isna().sum(),\n        # 'Nullpercent' : data.isna().sum()/len(data),\n         \"Value counts Max\": maxx,\n         'Value counts Min':minn \n        },\n        columns = ['nunique', 'len','types','Nulls'#,'Nullpercent', 'nunique/len'\n                   ,\"Value counts Max\",'Value counts Min']).\\\n        sort_values(by ='nunique',ascending = False)\n\n\n\ndef countPlot(col,num = 6,hue = None):\n    sns.set(rc={'figure.figsize':(6,6)})\n    ax = sns.countplot(x=col, data=train_df, hue = hue,\n                   order=train_df[col].value_counts().iloc[:num].index)\n    \n    return plt.show()\n\n\ndef percentile80(x):\n    return np.percentile(x,80)\n\ndef pivot_tbl(df,column,sort = 'count'):\n    \n    pivoted = df.pivot_table(index = [column], values = ['winBid'], \n               aggfunc = [np.min,np.max,np.mean, np.median, np.std,'count', percentile80])\n    \n    pivoted.columns = pivoted.columns.get_level_values(0)\n    \n    return pivoted.sort_values(sort,ascending=False)\n\n\ndef pl(df):\n    return(print(len(df)))\n\n\n## https://stackoverflow.com/questions/56799202/pandas-groupby-and-cumulative-mean-of-previous-rows-in-group\n\ndef mean_previous(df, Category, Order, Var):\n    \n    \n    # Order the dataframe first \n    df.sort_values([Category, Order], inplace=True)\n\n    # Calculate the ordinary grouped cumulative sum \n    # and then substract with the grouped cumulative sum of the last order\n    csp = df.groupby(Category)[Var].cumsum() - df.groupby([Category, Order])[Var].cumsum()\n\n    # Calculate the ordinary grouped cumulative count \n    # and then substract with the grouped cumulative count of the last order\n    ccp = df.groupby(Category)[Var].cumcount() - df.groupby([Category, Order]).cumcount()\n\n    return csp / ccp","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:47:16.737427Z","iopub.execute_input":"2022-08-18T10:47:16.738010Z","iopub.status.idle":"2022-08-18T10:47:16.752218Z","shell.execute_reply.started":"2022-08-18T10:47:16.737973Z","shell.execute_reply":"2022-08-18T10:47:16.751109Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:47:16.753578Z","iopub.execute_input":"2022-08-18T10:47:16.754040Z","iopub.status.idle":"2022-08-18T10:47:16.789277Z","shell.execute_reply.started":"2022-08-18T10:47:16.754004Z","shell.execute_reply":"2022-08-18T10:47:16.788454Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# sent bid / win bid ratio\nprint((train_df['sentPrice']/train_df['winBid']).mean())\nprint((train_df['winBid']/train_df['sentPrice']).mean())\n\nn=90\nlen(train_df[train_df['winBid']>n])\nprint(len(train_df[(train_df['winBid'] > 10) & (train_df['sentPrice']< 10)]))\n\nprint(len(train_df[(train_df['winBid'] >0 ) & (train_df['sentPrice'] > 10)]))","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:47:16.790533Z","iopub.execute_input":"2022-08-18T10:47:16.790995Z","iopub.status.idle":"2022-08-18T10:47:17.627954Z","shell.execute_reply.started":"2022-08-18T10:47:16.790960Z","shell.execute_reply":"2022-08-18T10:47:17.626902Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"stats(train_df)","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:47:17.629261Z","iopub.execute_input":"2022-08-18T10:47:17.629640Z","iopub.status.idle":"2022-08-18T10:47:46.400546Z","shell.execute_reply.started":"2022-08-18T10:47:17.629602Z","shell.execute_reply":"2022-08-18T10:47:46.399569Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:47:46.402069Z","iopub.execute_input":"2022-08-18T10:47:46.402674Z","iopub.status.idle":"2022-08-18T10:47:46.425982Z","shell.execute_reply.started":"2022-08-18T10:47:46.402633Z","shell.execute_reply":"2022-08-18T10:47:46.425101Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"stats(test_df)","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:47:46.430309Z","iopub.execute_input":"2022-08-18T10:47:46.430591Z","iopub.status.idle":"2022-08-18T10:47:46.600471Z","shell.execute_reply.started":"2022-08-18T10:47:46.430566Z","shell.execute_reply":"2022-08-18T10:47:46.599410Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df.describe().T.applymap(\"{0:.3f}\".format)","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:47:46.601699Z","iopub.execute_input":"2022-08-18T10:47:46.602021Z","iopub.status.idle":"2022-08-18T10:47:48.614536Z","shell.execute_reply.started":"2022-08-18T10:47:46.601988Z","shell.execute_reply":"2022-08-18T10:47:48.613441Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_df.describe().T.applymap(\"{0:.3f}\".format)","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:47:48.616097Z","iopub.execute_input":"2022-08-18T10:47:48.616490Z","iopub.status.idle":"2022-08-18T10:47:48.650286Z","shell.execute_reply.started":"2022-08-18T10:47:48.616453Z","shell.execute_reply":"2022-08-18T10:47:48.649176Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Eda","metadata":{}},{"cell_type":"code","source":"tr = train_df['bidFloorPrice'].value_counts(normalize=True)\nts = test_df['bidFloorPrice'].value_counts(normalize=True)\n\nf= plt.figure(figsize=(14,7))\n\nax=f.add_subplot(121).set(xlabel='bidFloorPrice')\nsns.barplot(x = tr.index,y=tr,).set_title(\"Train\")\n\nax=f.add_subplot(122).set(xlabel='bidFloorPrice')\nsns.barplot(x = ts.index,y=ts).set_title(\"Test\");","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:47:48.651835Z","iopub.execute_input":"2022-08-18T10:47:48.652172Z","iopub.status.idle":"2022-08-18T10:47:49.063738Z","shell.execute_reply.started":"2022-08-18T10:47:48.652138Z","shell.execute_reply":"2022-08-18T10:47:49.062782Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pivot_tbl(train_df,'bidFloorPrice')","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:47:49.065258Z","iopub.execute_input":"2022-08-18T10:47:49.065883Z","iopub.status.idle":"2022-08-18T10:47:51.342050Z","shell.execute_reply.started":"2022-08-18T10:47:49.065842Z","shell.execute_reply":"2022-08-18T10:47:51.340965Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df[train_df['bidFloorPrice']>train_df['winBid']].head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:47:51.343712Z","iopub.execute_input":"2022-08-18T10:47:51.344093Z","iopub.status.idle":"2022-08-18T10:47:51.379329Z","shell.execute_reply.started":"2022-08-18T10:47:51.344055Z","shell.execute_reply":"2022-08-18T10:47:51.378472Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df[train_df['bidFloorPrice']>train_df['winBid']].shape","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:47:51.380869Z","iopub.execute_input":"2022-08-18T10:47:51.381220Z","iopub.status.idle":"2022-08-18T10:47:51.400905Z","shell.execute_reply.started":"2022-08-18T10:47:51.381185Z","shell.execute_reply":"2022-08-18T10:47:51.399953Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(len(train_df))\ntrain_df = train_df[train_df['bidFloorPrice']<=train_df['winBid']]\nprint(len(train_df))\n","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:47:51.402167Z","iopub.execute_input":"2022-08-18T10:47:51.402594Z","iopub.status.idle":"2022-08-18T10:47:52.916385Z","shell.execute_reply.started":"2022-08-18T10:47:51.402559Z","shell.execute_reply":"2022-08-18T10:47:52.915389Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# # down sample to make dist same for ttest and train\n# num_of_samples = (train_df['bidFloorPrice'].value_counts() / test_df['bidFloorPrice'].value_counts(normalize=True)).min()\\\n# *test_df['bidFloorPrice'].value_counts(normalize=True)\n# # floats to ints\n# num_of_samples = {k:int(v) for k, v in num_of_samples.iteritems()}\n# num_of_samples","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:47:52.917688Z","iopub.execute_input":"2022-08-18T10:47:52.919175Z","iopub.status.idle":"2022-08-18T10:47:52.923979Z","shell.execute_reply.started":"2022-08-18T10:47:52.919135Z","shell.execute_reply":"2022-08-18T10:47:52.922849Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# sampled_train_df = train_df.groupby('bidFloorPrice').apply(lambda x: x.sample(num_of_samples[x.name],random_state=17)).reset_index(drop = True)\n# print(sampled_train_df['bidFloorPrice'].value_counts(normalize=True))","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:47:52.925742Z","iopub.execute_input":"2022-08-18T10:47:52.926431Z","iopub.status.idle":"2022-08-18T10:47:52.933394Z","shell.execute_reply.started":"2022-08-18T10:47:52.926396Z","shell.execute_reply":"2022-08-18T10:47:52.932440Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#train_df = sampled_train_df","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:47:52.935219Z","iopub.execute_input":"2022-08-18T10:47:52.936026Z","iopub.status.idle":"2022-08-18T10:47:52.942800Z","shell.execute_reply.started":"2022-08-18T10:47:52.935991Z","shell.execute_reply":"2022-08-18T10:47:52.941772Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pivot_tbl(train_df,'deviceId','mean').head(10)","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:47:52.944804Z","iopub.execute_input":"2022-08-18T10:47:52.945273Z","iopub.status.idle":"2022-08-18T10:48:09.542574Z","shell.execute_reply.started":"2022-08-18T10:47:52.945239Z","shell.execute_reply":"2022-08-18T10:48:09.541404Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pivot_tbl(train_df,'c4')","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:48:09.544197Z","iopub.execute_input":"2022-08-18T10:48:09.544592Z","iopub.status.idle":"2022-08-18T10:48:11.707540Z","shell.execute_reply.started":"2022-08-18T10:48:09.544554Z","shell.execute_reply":"2022-08-18T10:48:11.706464Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pivot_tbl(train_df,'c3')","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:48:11.709123Z","iopub.execute_input":"2022-08-18T10:48:11.709522Z","iopub.status.idle":"2022-08-18T10:48:17.952422Z","shell.execute_reply.started":"2022-08-18T10:48:11.709470Z","shell.execute_reply":"2022-08-18T10:48:17.951406Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pivot_tbl(train_df,'c2')","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:48:17.953998Z","iopub.execute_input":"2022-08-18T10:48:17.954369Z","iopub.status.idle":"2022-08-18T10:48:20.122437Z","shell.execute_reply.started":"2022-08-18T10:48:17.954334Z","shell.execute_reply":"2022-08-18T10:48:20.121462Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pivot_tbl(train_df,'connectionType')","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:48:20.128868Z","iopub.execute_input":"2022-08-18T10:48:20.129150Z","iopub.status.idle":"2022-08-18T10:48:25.586342Z","shell.execute_reply.started":"2022-08-18T10:48:20.129123Z","shell.execute_reply":"2022-08-18T10:48:25.585366Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pivot_tbl(train_df,'c1','std').head(15)","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:48:25.587637Z","iopub.execute_input":"2022-08-18T10:48:25.588737Z","iopub.status.idle":"2022-08-18T10:48:31.586988Z","shell.execute_reply.started":"2022-08-18T10:48:25.588699Z","shell.execute_reply":"2022-08-18T10:48:31.585911Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pivot_tbl(train_df,'countryCode','mean').head(15)","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:48:31.588620Z","iopub.execute_input":"2022-08-18T10:48:31.589006Z","iopub.status.idle":"2022-08-18T10:48:37.091947Z","shell.execute_reply.started":"2022-08-18T10:48:31.588968Z","shell.execute_reply":"2022-08-18T10:48:37.090860Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pivot_tbl(train_df,'countryCode','std').head(15)","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:48:37.093583Z","iopub.execute_input":"2022-08-18T10:48:37.093954Z","iopub.status.idle":"2022-08-18T10:48:42.353201Z","shell.execute_reply.started":"2022-08-18T10:48:37.093917Z","shell.execute_reply":"2022-08-18T10:48:42.352136Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"f, ax = plt.subplots(figsize=(8, 8))\ncorr = train_df.corr()\nsns.heatmap(corr,square=True, ax=ax);","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:48:42.354797Z","iopub.execute_input":"2022-08-18T10:48:42.355203Z","iopub.status.idle":"2022-08-18T10:48:43.836852Z","shell.execute_reply.started":"2022-08-18T10:48:42.355164Z","shell.execute_reply":"2022-08-18T10:48:43.835873Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# f, ax = plt.subplots(figsize=(8, 8))\n# corr = sampled_train_df.corr()\n# sns.heatmap(corr,square=True, ax=ax);","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:48:43.839408Z","iopub.execute_input":"2022-08-18T10:48:43.840380Z","iopub.status.idle":"2022-08-18T10:48:43.844624Z","shell.execute_reply.started":"2022-08-18T10:48:43.840341Z","shell.execute_reply":"2022-08-18T10:48:43.843680Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"f, ax = plt.subplots(figsize=(8, 8))\ncorr = test_df.corr()\nsns.heatmap(corr,square=True, ax=ax);","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:48:43.845863Z","iopub.execute_input":"2022-08-18T10:48:43.846295Z","iopub.status.idle":"2022-08-18T10:48:44.093250Z","shell.execute_reply.started":"2022-08-18T10:48:43.846259Z","shell.execute_reply":"2022-08-18T10:48:44.092371Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, axes = plt.subplots(2, 2)\naxes = axes.ravel()\ntrain_df['unitDisplayType'].hist(figsize = (8, 8),ax=axes[0])\ntrain_df['connectionType'].hist(figsize = (8, 8),ax=axes[1])\ntrain_df['c3'].hist(figsize = (8, 8),ax=axes[2])\ntrain_df['has_won'].hist(figsize = (8, 8),ax=axes[3])\n\n\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:48:44.095305Z","iopub.execute_input":"2022-08-18T10:48:44.095877Z","iopub.status.idle":"2022-08-18T10:48:53.322603Z","shell.execute_reply.started":"2022-08-18T10:48:44.095840Z","shell.execute_reply":"2022-08-18T10:48:53.321712Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"countPlot('countryCode',10)","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:48:53.324071Z","iopub.execute_input":"2022-08-18T10:48:53.324436Z","iopub.status.idle":"2022-08-18T10:48:57.118861Z","shell.execute_reply.started":"2022-08-18T10:48:53.324391Z","shell.execute_reply":"2022-08-18T10:48:57.117915Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"countPlot('winBid',10)","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:48:57.120169Z","iopub.execute_input":"2022-08-18T10:48:57.121222Z","iopub.status.idle":"2022-08-18T10:48:58.327376Z","shell.execute_reply.started":"2022-08-18T10:48:57.121185Z","shell.execute_reply":"2022-08-18T10:48:58.326433Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"countPlot('sentPrice',6,'has_won')","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:48:58.328894Z","iopub.execute_input":"2022-08-18T10:48:58.329507Z","iopub.status.idle":"2022-08-18T10:49:00.171422Z","shell.execute_reply.started":"2022-08-18T10:48:58.329454Z","shell.execute_reply":"2022-08-18T10:49:00.170432Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"countPlot('mediationProviderVersion')","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:49:00.172778Z","iopub.execute_input":"2022-08-18T10:49:00.173226Z","iopub.status.idle":"2022-08-18T10:49:03.849352Z","shell.execute_reply.started":"2022-08-18T10:49:00.173190Z","shell.execute_reply":"2022-08-18T10:49:03.848399Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# preprocessing","metadata":{}},{"cell_type":"code","source":"# Q&D\n# try with missing\n\n# train_df.fillna({'countryCode':'US', 'connectionType':'UNKNOWN'}, inplace=True)\n# test_df.fillna({'countryCode':'US', 'connectionType':'UNKNOWN'}, inplace=True)\n\ntrain_df.fillna({'countryCode':'missing', 'connectionType':'missing'}, inplace=True)\ntest_df.fillna({'countryCode':'missing', 'connectionType':'missing'}, inplace=True)\n                                    ","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:49:03.850720Z","iopub.execute_input":"2022-08-18T10:49:03.851726Z","iopub.status.idle":"2022-08-18T10:49:04.436667Z","shell.execute_reply.started":"2022-08-18T10:49:03.851687Z","shell.execute_reply":"2022-08-18T10:49:04.435585Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cols = ['c2', 'c4']\ntrain_df[cols] = train_df[cols].applymap(np.int8)\ntest_df[cols] = test_df[cols].applymap(np.int8)\n\n\nfloatcols = ['sentPrice', 'bidFloorPrice']\n\ntrain_df['winBid'] = train_df['winBid'].astype(np.float16)\n\ntrain_df[floatcols] = train_df[floatcols].applymap(np.float16)\ntest_df[floatcols] = test_df[floatcols].applymap(np.float16)\n\n","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:49:04.438164Z","iopub.execute_input":"2022-08-18T10:49:04.438610Z","iopub.status.idle":"2022-08-18T10:49:19.993260Z","shell.execute_reply.started":"2022-08-18T10:49:04.438566Z","shell.execute_reply":"2022-08-18T10:49:19.992175Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_df['isTest'] = 1\ntest_df['winBid'] = np.nan\ntest_df['has_won'] = np.nan\n\ntrain_df['isTest'] = 0\n\ndf = pd.concat([train_df, test_df])","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:49:19.994770Z","iopub.execute_input":"2022-08-18T10:49:19.995120Z","iopub.status.idle":"2022-08-18T10:49:23.170003Z","shell.execute_reply.started":"2022-08-18T10:49:19.995082Z","shell.execute_reply":"2022-08-18T10:49:23.168998Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(test_df.shape)\nprint(train_df.shape)\nprint(df.shape)","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:49:23.171329Z","iopub.execute_input":"2022-08-18T10:49:23.171771Z","iopub.status.idle":"2022-08-18T10:49:23.178468Z","shell.execute_reply.started":"2022-08-18T10:49:23.171732Z","shell.execute_reply":"2022-08-18T10:49:23.177547Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del train_df,test_df\n\nimport gc\n\ngc.collect()\n","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:49:23.180044Z","iopub.execute_input":"2022-08-18T10:49:23.180748Z","iopub.status.idle":"2022-08-18T10:49:23.606891Z","shell.execute_reply.started":"2022-08-18T10:49:23.180710Z","shell.execute_reply":"2022-08-18T10:49:23.605854Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# TODO Feature eng ","metadata":{"execution":{"iopub.status.busy":"2022-08-11T12:50:24.678549Z","iopub.execute_input":"2022-08-11T12:50:24.67909Z","iopub.status.idle":"2022-08-11T12:50:24.685948Z","shell.execute_reply.started":"2022-08-11T12:50:24.679059Z","shell.execute_reply":"2022-08-11T12:50:24.683902Z"}}},{"cell_type":"code","source":"%%time\n\n\ndef toLocalTime_pd(row): \n    return row['date'].tz_convert(country_timezones(row['countryCode'])[0]).replace(tzinfo=None)\n\n\ndf['date'] = pd.to_datetime(df['eventTimestamp'], unit='ms').dt.tz_localize('UTC')\n\n\n\n#df['Local_date'] = df. apply(lambda r: toLocalTime_pd(r), axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:49:23.610735Z","iopub.execute_input":"2022-08-18T10:49:23.611034Z","iopub.status.idle":"2022-08-18T10:49:23.754611Z","shell.execute_reply.started":"2022-08-18T10:49:23.611007Z","shell.execute_reply":"2022-08-18T10:49:23.753509Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\n\nfeat = ['date','Local_date']\nfeat = ['date']\n\n\nfor f in feat:\n    print(f)\n    df[f+ '_weekday'] = df[f].dt.weekday\n    df[f+ '_hour'] = df[f].dt.hour\n    df[f+ '_minute'] = df[f].dt.minute\n    df[f+ '_second'] = df[f].dt.second\n    \n\n    cond = [df[f+ '_hour']<8 ,\n            df[f+ '_hour']<13,\n            df[f+ '_hour']<18,\n            df[f+ '_hour']<=24\n           ]\n    \n    choice = ['morning','noon','afternoon','night']\n    \n    df[f+'_POD'] = np.select(cond, choice,np.nan)","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:49:23.755918Z","iopub.execute_input":"2022-08-18T10:49:23.757061Z","iopub.status.idle":"2022-08-18T10:49:29.258723Z","shell.execute_reply.started":"2022-08-18T10:49:23.757013Z","shell.execute_reply":"2022-08-18T10:49:29.257577Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\n# aggregated features\n\ncols = ['sentPrice','countryCode','osAndVersion','brandName','appVersion','mediationProviderVersion','bundleId','deviceId',\n       'unitDisplayType','correctModelName','c1', 'c2', 'c3', 'c4', 'size','connectionType','bidFloorPrice'\n       ]\n\n#cols = ['c1','sentPrice']\n\nfor c in cols:\n    df[c + '_cummulative_mean'] = mean_previous(df, c, 'eventTimestamp', 'winBid')\n    df.fillna({c + '_cummulative_mean':0}, inplace=True)\n\n# TODO last n mean \n# df['appVersion_cummax'] = df.groupby('appVersion')['winBid'].cummax()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:49:29.263187Z","iopub.execute_input":"2022-08-18T10:49:29.263577Z","iopub.status.idle":"2022-08-18T10:57:56.161790Z","shell.execute_reply.started":"2022-08-18T10:49:29.263542Z","shell.execute_reply":"2022-08-18T10:57:56.160747Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\ntest_df = df[df['isTest'] == 1]\ntrain_df = df[df['isTest'] == 0]\n\ndel df\ngc.collect()\n","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:57:56.163697Z","iopub.execute_input":"2022-08-18T10:57:56.164492Z","iopub.status.idle":"2022-08-18T10:57:59.716483Z","shell.execute_reply.started":"2022-08-18T10:57:56.164445Z","shell.execute_reply":"2022-08-18T10:57:59.715520Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:57:59.718025Z","iopub.execute_input":"2022-08-18T10:57:59.718480Z","iopub.status.idle":"2022-08-18T10:57:59.743304Z","shell.execute_reply.started":"2022-08-18T10:57:59.718434Z","shell.execute_reply":"2022-08-18T10:57:59.742239Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# select columns","metadata":{}},{"cell_type":"code","source":"train_columns = [ 'unitDisplayType', 'brandName', 'bundleId',\n       'appVersion', 'correctModelName', 'countryCode', 'deviceId',\n       'osAndVersion', 'connectionType', 'c1', 'c2', 'c3', 'c4', 'size',\n       'mediationProviderVersion', 'bidFloorPrice','sentPrice',#,'has_won'\n                 \n        'date_weekday', 'date_hour', 'date_minute',\n       'date_second', 'date_POD',\n                 \n        'sentPrice_cummulative_mean',\n       'countryCode_cummulative_mean', 'osAndVersion_cummulative_mean',\n       'brandName_cummulative_mean', 'appVersion_cummulative_mean',\n       'mediationProviderVersion_cummulative_mean',\n       'bundleId_cummulative_mean', 'deviceId_cummulative_mean',\n       'unitDisplayType_cummulative_mean', 'correctModelName_cummulative_mean',\n       'c1_cummulative_mean', 'c2_cummulative_mean', 'c3_cummulative_mean',\n       'c4_cummulative_mean', 'size_cummulative_mean',\n       'connectionType_cummulative_mean', 'bidFloorPrice_cummulative_mean'\n       ]\n\ncat_columns = [ 'unitDisplayType', 'brandName', 'bundleId',\n       'appVersion', 'correctModelName', 'countryCode', 'deviceId',\n       'osAndVersion', 'connectionType', 'c1', 'c2', 'c3', 'c4', 'size',\n       'mediationProviderVersion',#'has_won'\n               \n        'date_POD'\n               \n       ]\n\ntarget = ['winBid']","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:57:59.744710Z","iopub.execute_input":"2022-08-18T10:57:59.745306Z","iopub.status.idle":"2022-08-18T10:57:59.755214Z","shell.execute_reply.started":"2022-08-18T10:57:59.745246Z","shell.execute_reply":"2022-08-18T10:57:59.754203Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# train model","metadata":{}},{"cell_type":"code","source":"test_size = (len(test_df)/len(train_df))\ntest_size = 0.27","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:57:59.757026Z","iopub.execute_input":"2022-08-18T10:57:59.757461Z","iopub.status.idle":"2022-08-18T10:57:59.768485Z","shell.execute_reply.started":"2022-08-18T10:57:59.757417Z","shell.execute_reply":"2022-08-18T10:57:59.767569Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_train, X_val, y_train, y_val = train_test_split(train_df[train_columns],train_df[target], test_size = test_size, random_state=17) ","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:57:59.770261Z","iopub.execute_input":"2022-08-18T10:57:59.770624Z","iopub.status.idle":"2022-08-18T10:58:16.352364Z","shell.execute_reply.started":"2022-08-18T10:57:59.770591Z","shell.execute_reply":"2022-08-18T10:58:16.351392Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time \n\ncboost =  CatBoostRegressor(random_state=17,cat_features=cat_columns,task_type='GPU',learning_rate=1,verbose=150)\ny_pred = cboost.fit(X_train, y_train).predict(X_val)","metadata":{"execution":{"iopub.status.busy":"2022-08-18T10:58:16.353974Z","iopub.execute_input":"2022-08-18T10:58:16.354356Z","iopub.status.idle":"2022-08-18T11:12:39.162450Z","shell.execute_reply.started":"2022-08-18T10:58:16.354320Z","shell.execute_reply":"2022-08-18T11:12:39.161428Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"mse = mean_squared_error(y_val, y_pred)\nnp.sqrt(mse)","metadata":{"execution":{"iopub.status.busy":"2022-08-18T11:12:39.163989Z","iopub.execute_input":"2022-08-18T11:12:39.164381Z","iopub.status.idle":"2022-08-18T11:12:39.193213Z","shell.execute_reply.started":"2022-08-18T11:12:39.164340Z","shell.execute_reply":"2022-08-18T11:12:39.192007Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# examine errors","metadata":{"execution":{"iopub.status.busy":"2022-08-18T11:12:39.194833Z","iopub.execute_input":"2022-08-18T11:12:39.195393Z","iopub.status.idle":"2022-08-18T11:12:39.199795Z","shell.execute_reply.started":"2022-08-18T11:12:39.195358Z","shell.execute_reply":"2022-08-18T11:12:39.198850Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"examine_val_df = X_val\nexamine_val_df['y_pred'] = y_pred\nexamine_val_df['y_val'] = y_val","metadata":{"execution":{"iopub.status.busy":"2022-08-18T11:12:39.201212Z","iopub.execute_input":"2022-08-18T11:12:39.201906Z","iopub.status.idle":"2022-08-18T11:12:39.217532Z","shell.execute_reply.started":"2022-08-18T11:12:39.201870Z","shell.execute_reply":"2022-08-18T11:12:39.216649Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"np.sqrt((((examine_val_df['y_val'] - examine_val_df['y_pred']*1)**2).sum())/len(examine_val_df))","metadata":{"execution":{"iopub.status.busy":"2022-08-18T11:12:39.220130Z","iopub.execute_input":"2022-08-18T11:12:39.220458Z","iopub.status.idle":"2022-08-18T11:12:39.289555Z","shell.execute_reply.started":"2022-08-18T11:12:39.220429Z","shell.execute_reply":"2022-08-18T11:12:39.288471Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"examine_val_df['y_diff'] = examine_val_df['y_val'] - examine_val_df['y_pred']\nexamine_val_df['y_diff_2'] = examine_val_df['y_diff']**2","metadata":{"execution":{"iopub.status.busy":"2022-08-18T11:12:39.291124Z","iopub.execute_input":"2022-08-18T11:12:39.291473Z","iopub.status.idle":"2022-08-18T11:12:39.358036Z","shell.execute_reply.started":"2022-08-18T11:12:39.291438Z","shell.execute_reply":"2022-08-18T11:12:39.357115Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"np.sqrt(examine_val_df['y_diff_2'].sum()/len(examine_val_df))","metadata":{"execution":{"iopub.status.busy":"2022-08-18T11:12:39.359567Z","iopub.execute_input":"2022-08-18T11:12:39.359908Z","iopub.status.idle":"2022-08-18T11:12:39.371432Z","shell.execute_reply.started":"2022-08-18T11:12:39.359870Z","shell.execute_reply":"2022-08-18T11:12:39.370018Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"n = 100\nprint(len(examine_val_df))\nprint(len(examine_val_df[examine_val_df['y_diff']>n]))\nprint(len(examine_val_df[examine_val_df['y_diff']<-n]))\n","metadata":{"execution":{"iopub.status.busy":"2022-08-18T11:12:39.372786Z","iopub.execute_input":"2022-08-18T11:12:39.373312Z","iopub.status.idle":"2022-08-18T11:12:40.103538Z","shell.execute_reply.started":"2022-08-18T11:12:39.373275Z","shell.execute_reply":"2022-08-18T11:12:40.102563Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"examine_val_df[examine_val_df['y_diff']>n].head()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T11:12:40.104917Z","iopub.execute_input":"2022-08-18T11:12:40.105536Z","iopub.status.idle":"2022-08-18T11:12:40.143190Z","shell.execute_reply.started":"2022-08-18T11:12:40.105479Z","shell.execute_reply":"2022-08-18T11:12:40.142297Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"examine_val_df[(examine_val_df['sentPrice']>10)& (examine_val_df['sentPrice']>10)]","metadata":{"execution":{"iopub.status.busy":"2022-08-18T11:12:40.145567Z","iopub.execute_input":"2022-08-18T11:12:40.146570Z","iopub.status.idle":"2022-08-18T11:12:40.270257Z","shell.execute_reply.started":"2022-08-18T11:12:40.146531Z","shell.execute_reply":"2022-08-18T11:12:40.269185Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"examine_val_df[examine_val_df['y_diff']<-n].head()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T11:12:40.271831Z","iopub.execute_input":"2022-08-18T11:12:40.272214Z","iopub.status.idle":"2022-08-18T11:12:40.306347Z","shell.execute_reply.started":"2022-08-18T11:12:40.272176Z","shell.execute_reply":"2022-08-18T11:12:40.305440Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"##","metadata":{"execution":{"iopub.status.busy":"2022-08-18T11:12:40.308632Z","iopub.execute_input":"2022-08-18T11:12:40.309274Z","iopub.status.idle":"2022-08-18T11:12:40.313351Z","shell.execute_reply.started":"2022-08-18T11:12:40.309236Z","shell.execute_reply":"2022-08-18T11:12:40.312457Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"y_sub = cboost.predict(test_df[train_columns])","metadata":{"execution":{"iopub.status.busy":"2022-08-18T11:12:40.314849Z","iopub.execute_input":"2022-08-18T11:12:40.315262Z","iopub.status.idle":"2022-08-18T11:12:41.123834Z","shell.execute_reply.started":"2022-08-18T11:12:40.315225Z","shell.execute_reply":"2022-08-18T11:12:41.122847Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"y_sub[0:7]","metadata":{"execution":{"iopub.status.busy":"2022-08-18T11:12:41.125111Z","iopub.execute_input":"2022-08-18T11:12:41.125466Z","iopub.status.idle":"2022-08-18T11:12:41.132990Z","shell.execute_reply.started":"2022-08-18T11:12:41.125432Z","shell.execute_reply":"2022-08-18T11:12:41.131818Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_df[\"y_pred\"] = y_sub\ntest_df[test_df['bidFloorPrice']>test_df['y_pred']].head()\n","metadata":{"execution":{"iopub.status.busy":"2022-08-18T11:12:41.134535Z","iopub.execute_input":"2022-08-18T11:12:41.135688Z","iopub.status.idle":"2022-08-18T11:12:41.173113Z","shell.execute_reply.started":"2022-08-18T11:12:41.135653Z","shell.execute_reply":"2022-08-18T11:12:41.172067Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_df['y_pred'] = np.where(test_df['bidFloorPrice']>test_df['y_pred'], test_df['bidFloorPrice'], test_df['y_pred'])","metadata":{"execution":{"iopub.status.busy":"2022-08-18T11:12:41.174617Z","iopub.execute_input":"2022-08-18T11:12:41.175045Z","iopub.status.idle":"2022-08-18T11:12:41.182060Z","shell.execute_reply.started":"2022-08-18T11:12:41.175008Z","shell.execute_reply":"2022-08-18T11:12:41.181045Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# TODO build classifier to identify them and another regressor for high values","metadata":{"execution":{"iopub.status.busy":"2022-08-18T11:12:41.183604Z","iopub.execute_input":"2022-08-18T11:12:41.184057Z","iopub.status.idle":"2022-08-18T11:12:41.191170Z","shell.execute_reply.started":"2022-08-18T11:12:41.184023Z","shell.execute_reply":"2022-08-18T11:12:41.190213Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# submission","metadata":{}},{"cell_type":"code","source":"subdf = pd.DataFrame({'deviceId':test_df['deviceId'], 'winBid':test_df['y_pred']})\nsubdf.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T11:12:41.192629Z","iopub.execute_input":"2022-08-18T11:12:41.193079Z","iopub.status.idle":"2022-08-18T11:12:41.206683Z","shell.execute_reply.started":"2022-08-18T11:12:41.193045Z","shell.execute_reply":"2022-08-18T11:12:41.205628Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"VER = 'meanFeatures'\nsubdf.to_csv(f'submission_{VER}.csv', index = False)","metadata":{"execution":{"iopub.status.busy":"2022-08-18T11:12:41.208169Z","iopub.execute_input":"2022-08-18T11:12:41.208677Z","iopub.status.idle":"2022-08-18T11:12:41.303308Z","shell.execute_reply.started":"2022-08-18T11:12:41.208634Z","shell.execute_reply":"2022-08-18T11:12:41.302346Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# coping with the high bids\n\nsent_price_val = 100\nhigh_bids_sent_df = train_df[train_df['sentPrice']>sent_price_val]\npl(high_bids_sent_df)\nprint(\"-\"*20)\nhigh_bids_sent_df_test = test_df[test_df['sentPrice']>sent_price_val]\npl(high_bids_sent_df_test)\n\nsent_win_ratio = 1/(high_bids_sent_df['sentPrice']/high_bids_sent_df['winBid']).mean()\nsent_win_ratio","metadata":{"execution":{"iopub.status.busy":"2022-08-18T11:12:41.304896Z","iopub.execute_input":"2022-08-18T11:12:41.305263Z","iopub.status.idle":"2022-08-18T11:12:41.399295Z","shell.execute_reply.started":"2022-08-18T11:12:41.305228Z","shell.execute_reply":"2022-08-18T11:12:41.398356Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_df['y_pred_high_values'] = np.where(test_df['sentPrice']>sent_price_val, test_df['sentPrice']*sent_win_ratio, test_df['y_pred'])","metadata":{"execution":{"iopub.status.busy":"2022-08-18T11:12:41.407142Z","iopub.execute_input":"2022-08-18T11:12:41.407713Z","iopub.status.idle":"2022-08-18T11:12:41.415152Z","shell.execute_reply.started":"2022-08-18T11:12:41.407676Z","shell.execute_reply":"2022-08-18T11:12:41.414145Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"subdf = pd.DataFrame({'deviceId':test_df['deviceId'], 'winBid':test_df['y_pred_high_values']})\nsubdf.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T11:12:41.416708Z","iopub.execute_input":"2022-08-18T11:12:41.417301Z","iopub.status.idle":"2022-08-18T11:12:41.431005Z","shell.execute_reply.started":"2022-08-18T11:12:41.417266Z","shell.execute_reply":"2022-08-18T11:12:41.430007Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"subdf.to_csv(f'submission_{VER}_high_values.csv', index = False)","metadata":{"execution":{"iopub.status.busy":"2022-08-18T11:12:41.432439Z","iopub.execute_input":"2022-08-18T11:12:41.432876Z","iopub.status.idle":"2022-08-18T11:12:41.528960Z","shell.execute_reply.started":"2022-08-18T11:12:41.432842Z","shell.execute_reply":"2022-08-18T11:12:41.528004Z"},"trusted":true},"execution_count":null,"outputs":[]}]}