{"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":"2023-10-30T21:42:57.291101Z","iopub.execute_input":"2023-10-30T21:42:57.291483Z","iopub.status.idle":"2023-10-30T21:42:57.298993Z","shell.execute_reply.started":"2023-10-30T21:42:57.291443Z","shell.execute_reply":"2023-10-30T21:42:57.298034Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pip install xgboost ","metadata":{"execution":{"iopub.status.busy":"2023-10-30T21:43:00.034838Z","iopub.execute_input":"2023-10-30T21:43:00.035731Z","iopub.status.idle":"2023-10-30T21:43:12.593439Z","shell.execute_reply.started":"2023-10-30T21:43:00.035692Z","shell.execute_reply":"2023-10-30T21:43:12.592101Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import pandas as pd\nimport datetime\nimport xgboost as xgb\nfrom sklearn.ensemble import RandomForestRegressor \n#import lightgbm as lgb\nfrom sklearn.metrics import accuracy_score,recall_score,roc_auc_score,roc_curve,confusion_matrix as cm,mean_squared_error as mse\nimport numpy as np\nimport matplotlib.pyplot as plt\nplt.rcParams[\"font.sans-serif\"] = [\"SimHei\"]\nfrom sklearn.preprocessing import OrdinalEncoder\nfrom sklearn.model_selection import cross_val_score\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.preprocessing import StandardScaler\nfrom sklearn.impute import SimpleImputer,KNNImputer\nimport numpy as np\nimport gc","metadata":{"execution":{"iopub.status.busy":"2023-10-30T21:43:12.595547Z","iopub.execute_input":"2023-10-30T21:43:12.595867Z","iopub.status.idle":"2023-10-30T21:43:13.870126Z","shell.execute_reply.started":"2023-10-30T21:43:12.595837Z","shell.execute_reply":"2023-10-30T21:43:13.869305Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"导入数据","metadata":{}},{"cell_type":"code","source":"new_merchant_transactions=pd.read_csv(\"/kaggle/input/elo-merchant-category-recommendation/new_merchant_transactions.csv\")\nhistorical_transactions=pd.read_csv('/kaggle/input/elo-merchant-category-recommendation/historical_transactions.csv')","metadata":{"execution":{"iopub.status.busy":"2023-10-30T21:43:13.871234Z","iopub.execute_input":"2023-10-30T21:43:13.871521Z","iopub.status.idle":"2023-10-30T21:44:53.461869Z","shell.execute_reply.started":"2023-10-30T21:43:13.871496Z","shell.execute_reply":"2023-10-30T21:44:53.460731Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"new_merchant_transactions","metadata":{"execution":{"iopub.status.busy":"2023-10-30T21:44:53.464201Z","iopub.execute_input":"2023-10-30T21:44:53.464508Z","iopub.status.idle":"2023-10-30T21:44:53.501763Z","shell.execute_reply.started":"2023-10-30T21:44:53.464481Z","shell.execute_reply":"2023-10-30T21:44:53.500835Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"historical_transactions","metadata":{"execution":{"iopub.status.busy":"2023-10-30T21:44:53.503060Z","iopub.execute_input":"2023-10-30T21:44:53.503445Z","iopub.status.idle":"2023-10-30T21:44:53.529070Z","shell.execute_reply.started":"2023-10-30T21:44:53.503409Z","shell.execute_reply":"2023-10-30T21:44:53.528029Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train=pd.read_csv(\"/kaggle/input/elo-merchant-category-recommendation/train.csv\")\ntest=pd.read_csv('/kaggle/input/elo-merchant-category-recommendation/test.csv')","metadata":{"execution":{"iopub.status.busy":"2023-10-30T21:44:53.530715Z","iopub.execute_input":"2023-10-30T21:44:53.531053Z","iopub.status.idle":"2023-10-30T21:44:53.931684Z","shell.execute_reply.started":"2023-10-30T21:44:53.531022Z","shell.execute_reply":"2023-10-30T21:44:53.930683Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"合并训练集和测试集","metadata":{}},{"cell_type":"code","source":"test.iloc[11578]\ntest=test.drop(index=test[test['card_id']=='C_ID_c27b4f80f7'].index)","metadata":{"execution":{"iopub.status.busy":"2023-10-30T21:44:53.932970Z","iopub.execute_input":"2023-10-30T21:44:53.933285Z","iopub.status.idle":"2023-10-30T21:44:53.970723Z","shell.execute_reply.started":"2023-10-30T21:44:53.933259Z","shell.execute_reply":"2023-10-30T21:44:53.969770Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_test=pd.concat([train,test])\ntrain_test['first_active_month']=train_test['first_active_month'].astype(str)\ntrain_test['year']=train_test['first_active_month'].apply(lambda x:x[0:4])\ntrain_test['month']=train_test['first_active_month'].apply(lambda x:x[5:])\ntrain_test[['year','month']]=train_test[['year','month']].astype(int)","metadata":{"execution":{"iopub.status.busy":"2023-10-30T21:44:53.972128Z","iopub.execute_input":"2023-10-30T21:44:53.972767Z","iopub.status.idle":"2023-10-30T21:44:54.414381Z","shell.execute_reply.started":"2023-10-30T21:44:53.972731Z","shell.execute_reply":"2023-10-30T21:44:54.413524Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"yearfeature=train_test.groupby('year')[['feature_1','feature_2','feature_3']].mean()\nyearfeature.columns=['year_feature_1','year_feature_2','year_feature_3']\n\nmonthfeature=train_test.groupby('month')[['feature_1','feature_2','feature_3']].mean()\nmonthfeature.columns=['month_feature_1','month_feature_2','month_feature_3']\n\ndatefeature=train_test.groupby('first_active_month')[['feature_1','feature_2','feature_3']].mean()\ndatefeature.columns=['date_feature_1','date_feature_2','date_feature_3']\n\ntrain_test=pd.merge(train_test,yearfeature,on='year',how='left')\ntrain_test=pd.merge(train_test,monthfeature,on='month',how='left')\ntrain_test=pd.merge(train_test,datefeature,on='first_active_month',how='left')\ntrain_test.head()","metadata":{"execution":{"iopub.status.busy":"2023-10-30T21:44:54.415445Z","iopub.execute_input":"2023-10-30T21:44:54.415712Z","iopub.status.idle":"2023-10-30T21:44:54.747870Z","shell.execute_reply.started":"2023-10-30T21:44:54.415690Z","shell.execute_reply":"2023-10-30T21:44:54.746772Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"yeartarget=train_test.groupby('year')['target'].agg(['mean','max','min','median'])\nyeartarget=pd.DataFrame(yeartarget)\nyeartarget.columns=['year_target_mean','year_target_max','year_target_min','year_target_median']\n\nmonthtarget=train_test.groupby('month')['target'].agg(['mean','max','min','median'])\nmonthtarget=pd.DataFrame(monthtarget)\nmonthtarget.columns=['month_target_mean','month_target_max','month_target_min','month_target_median']\n\ntrain_test=pd.merge(train_test,yeartarget,on='year',how='left')\ntrain_test=pd.merge(train_test,monthtarget,on='month',how='left')\ntrain_test.head()","metadata":{"execution":{"iopub.status.busy":"2023-10-30T21:44:54.751633Z","iopub.execute_input":"2023-10-30T21:44:54.751938Z","iopub.status.idle":"2023-10-30T21:44:54.963007Z","shell.execute_reply.started":"2023-10-30T21:44:54.751911Z","shell.execute_reply":"2023-10-30T21:44:54.962062Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"f1=train_test.groupby('feature_1')['target'].agg(['mean','max','min','median'])\nf1=pd.DataFrame(f1)\nf1.columns=['f1_target_mean','f1_target_max','f1_target_min','f1_target_median']\n\nf2=train_test.groupby('feature_2')['target'].agg(['mean','max','min','median'])\nf2=pd.DataFrame(f2)\nf2.columns=['f2_target_mean','f2_target_max','f2_target_min','f2_target_median']\n\nf3=train_test.groupby('feature_3')['target'].agg(['mean','max','min','median'])\nf3=pd.DataFrame(f3)\nf3.columns=['f3_target_mean','f3_target_max','f3_target_min','f3_target_median']\n\nf12=train_test.groupby(['feature_1','feature_2'])['target'].agg(['mean','max','min','median'])\nf12=pd.DataFrame(f12)\nf12.columns=['f12_target_mean','f12_target_max','f12_target_min','f12_target_median']\n\nf13=train_test.groupby(['feature_1','feature_3'])['target'].agg(['mean','max','min','median'])\nf13=pd.DataFrame(f13)\nf13.columns=['f13_target_mean','f13_target_max','f13_target_min','f13_target_median']\n\nf23=train_test.groupby(['feature_2','feature_3'])['target'].agg(['mean','max','min','median'])\nf23=pd.DataFrame(f23)\nf23.columns=['f23_target_mean','f23_target_max','f23_target_min','f23_target_median']\n\nf123=train_test.groupby(['feature_1','feature_2','feature_3'])['target'].agg(['mean','max','min','median'])\nf123=pd.DataFrame(f123)\nf123.columns=['f123_target_mean','f123_target_max','f123_target_min','f123_target_median']","metadata":{"execution":{"iopub.status.busy":"2023-10-30T21:44:54.964781Z","iopub.execute_input":"2023-10-30T21:44:54.965238Z","iopub.status.idle":"2023-10-30T21:44:55.134451Z","shell.execute_reply.started":"2023-10-30T21:44:54.965203Z","shell.execute_reply":"2023-10-30T21:44:55.133609Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_test=pd.merge(train_test,f1,on='feature_1',how='left')\ntrain_test=pd.merge(train_test,f2,on='feature_2',how='left')\ntrain_test=pd.merge(train_test,f3,on='feature_3',how='left')\ntrain_test=pd.merge(train_test,f12,on=['feature_1','feature_2'],how='left')\ntrain_test=pd.merge(train_test,f13,on=['feature_1','feature_3'],how='left')\ntrain_test=pd.merge(train_test,f23,on=['feature_2','feature_3'],how='left')\ntrain_test=pd.merge(train_test,f123,on=['feature_1','feature_2','feature_3'],how='left')\ntrain_test.head()","metadata":{"execution":{"iopub.status.busy":"2023-10-30T21:44:55.135583Z","iopub.execute_input":"2023-10-30T21:44:55.135889Z","iopub.status.idle":"2023-10-30T21:44:55.815669Z","shell.execute_reply.started":"2023-10-30T21:44:55.135861Z","shell.execute_reply":"2023-10-30T21:44:55.814884Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"合并新旧transcation","metadata":{}},{"cell_type":"code","source":"transaction=pd.concat([historical_transactions,new_merchant_transactions],axis=0)\noe=OrdinalEncoder()\noe.fit(transaction[['authorized_flag','category_1','category_3']])\ntransaction[['authorized_flag','category_1','category_3']]=oe.transform(transaction[['authorized_flag','category_1','category_3']])\ntransaction['purchase_date']=pd.to_datetime(transaction['purchase_date'])\ntransaction['purchase_year']=transaction['purchase_date'].dt.year\ntransaction['purchase_month']=transaction['purchase_date'].dt.month\ntransaction['purchase_day']=transaction['purchase_date'].dt.day\ntransaction=transaction.drop(columns='purchase_date')\ntransaction.head()","metadata":{"execution":{"iopub.status.busy":"2023-10-30T21:44:55.817320Z","iopub.execute_input":"2023-10-30T21:44:55.818024Z","iopub.status.idle":"2023-10-30T21:46:09.830739Z","shell.execute_reply.started":"2023-10-30T21:44:55.817966Z","shell.execute_reply":"2023-10-30T21:46:09.829593Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"处理merchants","metadata":{}},{"cell_type":"code","source":"merchants=pd.read_csv(\"/kaggle/input/elo-merchant-category-recommendation/merchants.csv\")\nmer_null=pd.DataFrame(merchants.isnull().sum())\nmer_null_list=mer_null[mer_null[0]>0].index.tolist()\nmerchants[mer_null_list[0:3]]=merchants[mer_null_list[0:3]].fillna(merchants[mer_null_list[0:3]].mean())\nmerchants['category_2']=merchants['category_2'].fillna(merchants['category_2'].value_counts().sort_values(ascending=False).index[0])","metadata":{"execution":{"iopub.status.busy":"2023-10-30T21:46:09.832358Z","iopub.execute_input":"2023-10-30T21:46:09.832819Z","iopub.status.idle":"2023-10-30T21:46:12.636533Z","shell.execute_reply.started":"2023-10-30T21:46:09.832777Z","shell.execute_reply":"2023-10-30T21:46:12.635498Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"oe2=OrdinalEncoder()\noe2.fit(merchants[['category_1','category_4']])\nmerchants[['category_1','category_4']]=oe2.transform(merchants[['category_1','category_4']])\ndict={'A':5,'B':4,'C':3,'D':2,'E':1}\ntrans=pd.Series(dict)\nmerchants['most_recent_sales_range']=merchants['most_recent_sales_range'].map(trans)\nmerchants['most_recent_purchases_range']=merchants['most_recent_purchases_range'].map(trans)\nfor column in ['avg_purchases_lag3','avg_purchases_lag6','avg_purchases_lag12']:\n    merchants[column]=merchants[column].replace(np.inf,-99)\n    merchants[column]=merchants[column].replace(-99,merchants[column].max())\n    merchants=merchants[~(merchants['merchant_id'].duplicated())]\nmerchants.head()","metadata":{"execution":{"iopub.status.busy":"2023-10-30T21:46:12.637879Z","iopub.execute_input":"2023-10-30T21:46:12.638191Z","iopub.status.idle":"2023-10-30T21:46:13.238577Z","shell.execute_reply.started":"2023-10-30T21:46:12.638165Z","shell.execute_reply":"2023-10-30T21:46:13.237574Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"合并merchants和transcation","metadata":{}},{"cell_type":"code","source":"mcol_list=merchants.columns.tolist()\ntcol_list=transaction.columns.tolist()\nduplicated_col=[]\nfor col in mcol_list:\n    if col in tcol_list:\n        duplicated_col.append(col)\ndel duplicated_col[0]\nduplicated_col\ntransaction=transaction.drop(columns=duplicated_col)\nm_t=pd.merge(transaction,merchants,on='merchant_id',how='left')\nm_t['category_3']=m_t['category_3'].fillna(0)","metadata":{"execution":{"iopub.status.busy":"2023-10-30T21:46:13.240042Z","iopub.execute_input":"2023-10-30T21:46:13.240430Z","iopub.status.idle":"2023-10-30T21:46:36.934426Z","shell.execute_reply.started":"2023-10-30T21:46:13.240393Z","shell.execute_reply":"2023-10-30T21:46:36.920737Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"m_t.shape","metadata":{"execution":{"iopub.status.busy":"2023-10-30T22:08:12.225636Z","iopub.execute_input":"2023-10-30T22:08:12.226774Z","iopub.status.idle":"2023-10-30T22:08:12.238381Z","shell.execute_reply.started":"2023-10-30T22:08:12.226728Z","shell.execute_reply":"2023-10-30T22:08:12.237199Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cat_col=['authorized_flag','purchase_year','purchase_month','merchant_category_id','category_1','category_2','category_3','category_4','state_id','city_id']\nnum_col=['purchase_amount','installments']\n\nfor catcol in cat_col:\n    for numcol in num_col:\n        df=m_t.groupby(['card_id',catcol])[numcol].sum()\n        df_=df.unstack(catcol)\n        lens=len(m_t[catcol].value_counts())\n        list=[]\n        for i in range(0,lens):\n            list.extend([catcol+'&'+numcol+str(i)+'sum'])\n        df_.columns=list\n        df_[list]=df_[list].fillna(0)\n        train_test=pd.merge(train_test,df_,on='card_id',how='left')","metadata":{"execution":{"iopub.status.busy":"2023-10-04T15:00:55.404175Z","iopub.execute_input":"2023-10-04T15:00:55.404471Z","iopub.status.idle":"2023-10-04T15:03:07.69153Z","shell.execute_reply.started":"2023-10-04T15:00:55.404446Z","shell.execute_reply":"2023-10-04T15:03:07.69048Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cat_col=['authorized_flag','purchase_year','purchase_month','merchant_category_id','category_1','category_2','category_3','category_4','state_id','city_id']\nnum_col=['purchase_amount']\n\nfor catcol in cat_col:\n    for numcol in num_col:\n        df=m_t.groupby(['card_id',catcol])[numcol].count()\n        df_=df.unstack(catcol)\n        lens=len(m_t[catcol].value_counts())\n        list=[]\n        for i in range(0,lens):\n            list.extend([catcol+'&'+numcol+str(i)+'count'])\n        df_.columns=list\n        df_[list]=df_[list].fillna(0)\n        train_test=pd.merge(train_test,df_,on='card_id',how='left')","metadata":{"execution":{"iopub.status.busy":"2023-10-04T15:03:07.692868Z","iopub.execute_input":"2023-10-04T15:03:07.693199Z","iopub.status.idle":"2023-10-04T15:04:17.771438Z","shell.execute_reply.started":"2023-10-04T15:03:07.693171Z","shell.execute_reply":"2023-10-04T15:04:17.77039Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cat_col=['authorized_flag','merchant_category_id','purchase_year','purchase_month','category_1','category_2','category_3','category_4','state_id','city_id']\nnum_col=['purchase_amount','installments','month_lag','numerical_1','numerical_2','most_recent_sales_range','most_recent_purchases_range','avg_sales_lag3'\n        ,'active_months_lag3','avg_purchases_lag3','avg_sales_lag6','avg_purchases_lag6','active_months_lag6','avg_sales_lag12','avg_purchases_lag12',\n         'active_months_lag12']\nfor catcol in cat_col:\n    for numcol in num_col:\n        df=m_t.groupby(['card_id',catcol])[numcol].agg(['mean','max','min'])\n        df_=df.unstack(catcol)\n        lens=len(m_t[catcol].value_counts())\n        mean_list=[]\n        max_list=[]\n        min_list=[]\n        median_list=[]\n        for i in range(0,lens):\n            mean=[catcol+\"&\"+numcol+str(i)+'mean']\n            mean_list.extend(mean)\n        for i in range(0,lens):\n            max=[catcol+\"&\"+numcol+str(i)+'max']\n            max_list.extend(max)\n        for i in range(0,lens):\n            min=[catcol+\"&\"+numcol+str(i)+'min']\n            min_list.extend(min)\n        name_list=mean_list+max_list+min_list\n        df_.columns=name_list\n        df_[name_list]=df_[name_list].fillna(0)\n        train_test = pd.merge(train_test,df_,on='card_id',how='left')\n        print(catcol,numcol)\nprint('ok')","metadata":{"execution":{"iopub.status.busy":"2023-10-04T15:04:17.772702Z","iopub.execute_input":"2023-10-04T15:04:17.772998Z","iopub.status.idle":"2023-10-04T15:48:48.230428Z","shell.execute_reply.started":"2023-10-04T15:04:17.772973Z","shell.execute_reply":"2023-10-04T15:48:48.229236Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train=train_test[train_test['target'].notnull()]\ntest=train_test[train_test['target'].isnull()]\n\nytrain=train['target']\nxtrain=train.drop(columns=['first_active_month','card_id','target'])\nxtest=test.drop(columns=['first_active_month','card_id','target'])","metadata":{"execution":{"iopub.status.busy":"2023-10-04T15:48:48.434421Z","iopub.status.idle":"2023-10-04T15:48:48.434782Z","shell.execute_reply.started":"2023-10-04T15:48:48.434596Z","shell.execute_reply":"2023-10-04T15:48:48.434613Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dtrain=xgb.DMatrix(xtrain,ytrain)\ndtest=xgb.DMatrix(xtest)","metadata":{"execution":{"iopub.status.busy":"2023-10-04T15:48:48.436169Z","iopub.status.idle":"2023-10-04T15:48:48.436473Z","shell.execute_reply.started":"2023-10-04T15:48:48.436321Z","shell.execute_reply":"2023-10-04T15:48:48.436336Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"params = { \n          'eta': 0.1, \n          'max_depth': 8, \n          'colsample_bytree':0.88,\n            'seed':9\n          }\n\ngbm=xgb.train(params,dtrain,num_boost_round=90)\npred=gbm.predict(dtest)\npred","metadata":{"execution":{"iopub.status.busy":"2023-10-04T15:48:48.437514Z","iopub.status.idle":"2023-10-04T15:48:48.437807Z","shell.execute_reply.started":"2023-10-04T15:48:48.437659Z","shell.execute_reply":"2023-10-04T15:48:48.437673Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pred=pd.DataFrame(pred)\npred.to_csv(\"/kaggle/working/submission40.csv\")","metadata":{"execution":{"iopub.status.busy":"2023-10-04T15:48:48.438811Z","iopub.status.idle":"2023-10-04T15:48:48.439125Z","shell.execute_reply.started":"2023-10-04T15:48:48.438966Z","shell.execute_reply":"2023-10-04T15:48:48.43898Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}