{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.12","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":10445,"databundleVersionId":200747,"sourceType":"competition"}],"dockerImageVersionId":30587,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"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","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 一、数据探索","metadata":{}},{"cell_type":"code","source":"import os\nimport numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt","metadata":{"execution":{"iopub.status.busy":"2023-11-27T09:57:26.404683Z","iopub.execute_input":"2023-11-27T09:57:26.405081Z","iopub.status.idle":"2023-11-27T09:57:26.768346Z","shell.execute_reply.started":"2023-11-27T09:57:26.405049Z","shell.execute_reply":"2023-11-27T09:57:26.765804Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test = pd.read_csv('/kaggle/input/elo-merchant-category-recommendation/test.csv')\ntest","metadata":{"execution":{"iopub.status.busy":"2023-11-27T09:59:01.893368Z","iopub.execute_input":"2023-11-27T09:59:01.895500Z","iopub.status.idle":"2023-11-27T09:59:02.138809Z","shell.execute_reply.started":"2023-11-27T09:59:01.895416Z","shell.execute_reply":"2023-11-27T09:59:02.138041Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test['card_id'].duplicated().any()","metadata":{"execution":{"iopub.status.busy":"2023-11-27T09:59:09.047720Z","iopub.execute_input":"2023-11-27T09:59:09.048160Z","iopub.status.idle":"2023-11-27T09:59:09.076595Z","shell.execute_reply.started":"2023-11-27T09:59:09.048123Z","shell.execute_reply":"2023-11-27T09:59:09.074701Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pd.read_csv('/kaggle/input/elo-merchant-category-recommendation/sample_submission.csv')","metadata":{"execution":{"iopub.status.busy":"2023-11-27T09:59:13.418980Z","iopub.execute_input":"2023-11-27T09:59:13.419510Z","iopub.status.idle":"2023-11-27T09:59:13.545638Z","shell.execute_reply.started":"2023-11-27T09:59:13.419467Z","shell.execute_reply":"2023-11-27T09:59:13.543852Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train = pd.read_csv('/kaggle/input/elo-merchant-category-recommendation/train.csv')\ntrain","metadata":{"execution":{"iopub.status.busy":"2023-11-27T09:59:18.968770Z","iopub.execute_input":"2023-11-27T09:59:18.969289Z","iopub.status.idle":"2023-11-27T09:59:19.281939Z","shell.execute_reply.started":"2023-11-27T09:59:18.969250Z","shell.execute_reply":"2023-11-27T09:59:19.280867Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"assert train['card_id'].nunique() == train['card_id'].shape[0]\nassert test['card_id'].nunique() == test['card_id'].shape[0]\nassert test['card_id'].nunique()+ train['card_id'].nunique()  == len(set(test['card_id'].values.tolist()+ \\\n                                                                         train['card_id'].values.tolist()))\n# 结论：训练集与测试集的card_id均为唯一值，且训练集与测试集的card_id无重复","metadata":{"execution":{"iopub.status.busy":"2023-11-27T09:59:25.929946Z","iopub.execute_input":"2023-11-27T09:59:25.930322Z","iopub.status.idle":"2023-11-27T09:59:26.121562Z","shell.execute_reply.started":"2023-11-27T09:59:25.930294Z","shell.execute_reply":"2023-11-27T09:59:26.119943Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train['card_id'].duplicated().any()","metadata":{"execution":{"iopub.status.busy":"2023-11-27T09:59:29.913610Z","iopub.execute_input":"2023-11-27T09:59:29.914008Z","iopub.status.idle":"2023-11-27T09:59:29.939241Z","shell.execute_reply.started":"2023-11-27T09:59:29.913980Z","shell.execute_reply":"2023-11-27T09:59:29.937701Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pd.read_csv('/kaggle/input/elo-merchant-category-recommendation/historical_transactions.csv')","metadata":{"execution":{"iopub.status.busy":"2023-11-26T05:21:08.708810Z","iopub.execute_input":"2023-11-26T05:21:08.709611Z","iopub.status.idle":"2023-11-26T05:21:08.714979Z","shell.execute_reply.started":"2023-11-26T05:21:08.709570Z","shell.execute_reply":"2023-11-26T05:21:08.713515Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pd.read_csv('/kaggle/input/elo-merchant-category-recommendation/merchants.csv')","metadata":{"execution":{"iopub.status.busy":"2023-11-27T09:59:37.878033Z","iopub.execute_input":"2023-11-27T09:59:37.878541Z","iopub.status.idle":"2023-11-27T09:59:39.374080Z","shell.execute_reply.started":"2023-11-27T09:59:37.878500Z","shell.execute_reply":"2023-11-27T09:59:39.372761Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pd.read_csv('/kaggle/input/elo-merchant-category-recommendation/new_merchant_transactions.csv')","metadata":{"execution":{"iopub.status.busy":"2023-11-27T09:59:39.395441Z","iopub.execute_input":"2023-11-27T09:59:39.395807Z","iopub.status.idle":"2023-11-27T09:59:45.236125Z","shell.execute_reply.started":"2023-11-27T09:59:39.395778Z","shell.execute_reply":"2023-11-27T09:59:45.234556Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pd.read_excel('/kaggle/input/elo-merchant-category-recommendation/Data_Dictionary.xlsx')","metadata":{"execution":{"iopub.status.busy":"2023-11-27T09:59:45.238437Z","iopub.execute_input":"2023-11-27T09:59:45.238914Z","iopub.status.idle":"2023-11-27T09:59:45.640247Z","shell.execute_reply.started":"2023-11-27T09:59:45.238859Z","shell.execute_reply":"2023-11-27T09:59:45.639079Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"features = ['feature_1','feature_2','feature_3']\nfor i in features:\n    display(set(train[i]),set(test[i]))","metadata":{"execution":{"iopub.status.busy":"2023-11-27T09:59:48.340270Z","iopub.execute_input":"2023-11-27T09:59:48.340775Z","iopub.status.idle":"2023-11-27T09:59:48.457078Z","shell.execute_reply.started":"2023-11-27T09:59:48.340741Z","shell.execute_reply":"2023-11-27T09:59:48.455171Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 检查缺失情况\ntrain.isnull().sum()","metadata":{"execution":{"iopub.status.busy":"2023-11-27T09:59:50.203252Z","iopub.execute_input":"2023-11-27T09:59:50.203623Z","iopub.status.idle":"2023-11-27T09:59:50.228116Z","shell.execute_reply.started":"2023-11-27T09:59:50.203592Z","shell.execute_reply":"2023-11-27T09:59:50.226434Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test.isnull().sum()","metadata":{"execution":{"iopub.status.busy":"2023-11-27T09:59:51.511912Z","iopub.execute_input":"2023-11-27T09:59:51.512279Z","iopub.status.idle":"2023-11-27T09:59:51.530109Z","shell.execute_reply.started":"2023-11-27T09:59:51.512252Z","shell.execute_reply":"2023-11-27T09:59:51.529044Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- 结论：只有测试集first_active_month有一个缺失值，后续看数据情况适当进行处理，如填充，总体来说一个\n- 缺失值的影响不大","metadata":{}},{"cell_type":"markdown","source":"- 校验过card_id后，简单对比训练集与测试集的其余特征分布差异, 包括单变量分布与联合分布状况\n- 其他：训练集与测试集的card_id是否唯一，是否不交叠？\n- feature_1, feature_2, feature_3的类别分布是否相似？","metadata":{}},{"cell_type":"code","source":"# 以first_active_month为例分析训练集与测试集的差异\nfeatures = ['first_active_month','feature_1','feature_2','feature_3']\ntrain_count = train.shape[0]\ntest_count = test.shape[0]\nfor feature in features:\n    train[feature].value_counts().sort_index().plot()\n                #  返回：索引类 频数\n    test[feature].value_counts().sort_index().plot()\n    plt.xlabel(feature)\n    plt.legend(['train','test'])\n    plt.ylabel('count')\n    plt.show()\n#结论：训练集与测试集在所有单变量上的绝对数量分布形状极其相似，需要进一步查看相对占比分布","metadata":{"execution":{"iopub.status.busy":"2023-11-27T09:59:58.580333Z","iopub.execute_input":"2023-11-27T09:59:58.580799Z","iopub.status.idle":"2023-11-27T09:59:59.459466Z","shell.execute_reply.started":"2023-11-27T09:59:58.580754Z","shell.execute_reply":"2023-11-27T09:59:59.458079Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 以first_active_month为例分析训练集与测试集的差异\nfeatures = ['first_active_month','feature_1','feature_2','feature_3']\ntrain_count = train.shape[0]\ntest_count = test.shape[0]\nfor feature in features:\n    (train[feature].value_counts().sort_index()/train_count).plot()\n    (test[feature].value_counts().sort_index()/test_count).plot()\n    plt.legend(['train','test'])\n    plt.xlabel(feature)\n    plt.ylabel('ratio')\n    plt.show()\n#结论：训练集与测试集在所有单变量上的相对占比分布形状基本一致，猜想训练集与测试集的生成方式\n# 一样，继续验证联合分布以加强猜想的事实依据\n#TODO：这里画图分析有一不严谨之处，即训练集与测试集的单变量取值范围可能不完全一样，由此两根线画\n#在同一张图上有可能会出错，如发生偏移等，请参赛者自行验证二者的横坐标是否完全一样？如果不一样，运行\n#这段代码会发生什么？在下面的联合分布验证中，我们将会填补这一遗漏之处","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:00:01.388388Z","iopub.execute_input":"2023-11-27T10:00:01.388931Z","iopub.status.idle":"2023-11-27T10:00:02.211547Z","shell.execute_reply.started":"2023-11-27T10:00:01.388860Z","shell.execute_reply":"2023-11-27T10:00:02.210656Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 为了查看多变量的联合分布，通常来说可以使用散点图，但这里的四个特征均是离散特征，因此散点图不太适合\n# 继续延续上面画单变量图的思想，参赛者可以通过将两个变量拼到一起转变为单变量的分布\ndef combine_feature(df):\n    cols = df.columns\n    feature1 = df[cols[0]].astype(str).values.tolist()\n    feature2 = df[cols[1]].astype(str).values.tolist()\n    return pd.Series([feature1[i]+'&'+feature2[i] for i in range(df.shape[0])])\nn = len(features)\nfor i in range(n-1):\n    for j in range(i+1, n):\n        cols = [features[i], features[j]]\n        print(cols)\n        train_dis = combine_feature(train[cols]).value_counts().sort_index()/train_count\n        test_dis = combine_feature(test[cols]).value_counts().sort_index()/test_count\n        index_dis = pd.Series(train_dis.index.tolist() + test_dis.index.tolist()).drop_duplicates().sort_values()\n        (index_dis.map(train_dis).fillna(0)).plot()\n        (index_dis.map(train_dis).fillna(0)).plot()\n        plt.legend(['train','test'])\n        plt.xlabel('&'.join(cols))\n        plt.ylabel('ratio')\n        plt.show()\n# 结论：修正上述遗漏后参赛者可以发现训练集与测试集的两变量联合分布也基本保持一致，由此基本可以判定，训练集与测似集\n# 的生成方式基本一摸一样，即测试集与训练集是同一批数据随机划分后的结果，有兴趣的参赛者可继续验证三变量和四变量分布。假定\n# 关于验证集与测试集的这一猜想成立，会极大地增添参赛者后续进行特征工程的信心，对建模方式也会有一个整体把握。","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:00:03.322514Z","iopub.execute_input":"2023-11-27T10:00:03.324597Z","iopub.status.idle":"2023-11-27T10:00:06.107498Z","shell.execute_reply.started":"2023-11-27T10:00:03.324514Z","shell.execute_reply":"2023-11-27T10:00:06.106363Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_dis","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:00:06.109289Z","iopub.execute_input":"2023-11-27T10:00:06.109633Z","iopub.status.idle":"2023-11-27T10:00:06.119638Z","shell.execute_reply.started":"2023-11-27T10:00:06.109609Z","shell.execute_reply":"2023-11-27T10:00:06.118004Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"index_dis","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:00:12.344063Z","iopub.execute_input":"2023-11-27T10:00:12.344591Z","iopub.status.idle":"2023-11-27T10:00:12.355284Z","shell.execute_reply.started":"2023-11-27T10:00:12.344550Z","shell.execute_reply":"2023-11-27T10:00:12.353290Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"index_dis.map(train_dis)","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:00:13.937919Z","iopub.execute_input":"2023-11-27T10:00:13.938276Z","iopub.status.idle":"2023-11-27T10:00:13.951460Z","shell.execute_reply.started":"2023-11-27T10:00:13.938248Z","shell.execute_reply":"2023-11-27T10:00:13.949111Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 最后再来看看本次赛题的关键目标target,由于是忠诚度分数为连续值，通常借助pandas的describe函数进行简单统计\ntrain['target'].describe()","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:00:19.384569Z","iopub.execute_input":"2023-11-27T10:00:19.385096Z","iopub.status.idle":"2023-11-27T10:00:19.417930Z","shell.execute_reply.started":"2023-11-27T10:00:19.385055Z","shell.execute_reply":"2023-11-27T10:00:19.415562Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train['target'].value_counts().head()","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:00:20.837683Z","iopub.execute_input":"2023-11-27T10:00:20.838228Z","iopub.status.idle":"2023-11-27T10:00:20.877544Z","shell.execute_reply.started":"2023-11-27T10:00:20.838182Z","shell.execute_reply":"2023-11-27T10:00:20.874506Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 此外，关于连续值参赛者还可以借助直方图进行频域观察\ntrain['target'].plot.hist()\nplt.show()\n# 结论：忠诚度分数target绝大多数集中在[-1,1]的区间","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:00:22.210590Z","iopub.execute_input":"2023-11-27T10:00:22.211129Z","iopub.status.idle":"2023-11-27T10:00:22.442181Z","shell.execute_reply.started":"2023-11-27T10:00:22.211086Z","shell.execute_reply":"2023-11-27T10:00:22.440875Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- 总结，查看完训练集与测试集的基本信息后，相信参赛者对赛题关键任务有了明确的\n- 理解，但是对数据整体还是有一些缺失概念，因此继续探索赛题提供的其他几份数据","metadata":{}},{"cell_type":"markdown","source":"## 商家基本信息","metadata":{}},{"cell_type":"code","source":"import pandas as pd\n# 查看商家信息字段的含义\ndf = pd.read_excel('/kaggle/input/elo-merchant-category-recommendation/Data_Dictionary.xlsx', sheet_name='merchant', skiprows=2)\nprint(df.shape)\nfor va in df.values:\n    print('Column:', va[0], ' Description:', va[1])\n# 结论：可以看出商家信息表除了基本的类别数值字段，主办方还提供了商家在最近时期的各种交易量\n\n# 目的：查看特征描述文件与特征文件是否对应的上","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:00:31.742834Z","iopub.execute_input":"2023-11-27T10:00:31.743381Z","iopub.status.idle":"2023-11-27T10:00:31.770791Z","shell.execute_reply.started":"2023-11-27T10:00:31.743329Z","shell.execute_reply":"2023-11-27T10:00:31.769554Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 首先检查商家信息数据的正确性\nmerchant = pd.read_csv('/kaggle/input/elo-merchant-category-recommendation/merchants.csv')\nprint(merchant.shape, merchant['merchant_id'].nunique())\nprint(pd.Series(merchant.columns.tolist()).sort_values().values ==  pd.Series([va[0] for va in df.values]).sort_values().values)\n# 结论: 特征信息完全对上，且商家信息记录数据表不唯一，后续处理需要注意","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:00:36.496544Z","iopub.execute_input":"2023-11-27T10:00:36.497021Z","iopub.status.idle":"2023-11-27T10:00:37.168251Z","shell.execute_reply.started":"2023-11-27T10:00:36.496979Z","shell.execute_reply":"2023-11-27T10:00:37.166020Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pd.Series(merchant.columns.tolist()).sort_values()","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:00:39.158012Z","iopub.execute_input":"2023-11-27T10:00:39.159049Z","iopub.status.idle":"2023-11-27T10:00:39.172372Z","shell.execute_reply.started":"2023-11-27T10:00:39.159013Z","shell.execute_reply":"2023-11-27T10:00:39.169574Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.values","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:00:40.365655Z","iopub.execute_input":"2023-11-27T10:00:40.366441Z","iopub.status.idle":"2023-11-27T10:00:40.375498Z","shell.execute_reply.started":"2023-11-27T10:00:40.366399Z","shell.execute_reply":"2023-11-27T10:00:40.373390Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"merchant.head()","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:00:41.588184Z","iopub.execute_input":"2023-11-27T10:00:41.588786Z","iopub.status.idle":"2023-11-27T10:00:41.610175Z","shell.execute_reply.started":"2023-11-27T10:00:41.588758Z","shell.execute_reply":"2023-11-27T10:00:41.608815Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 根据商家信息表字段的离散和连续属性，参赛者可将两类字段分开后统一化分析，现字段分离如下\ncategory_cols = ['merchant_id', 'merchant_group_id', 'merchant_category_id',\n       'subsector_id', 'category_1',\n       'most_recent_sales_range', 'most_recent_purchases_range',\n       'category_4', 'city_id', 'state_id', 'category_2']\nnumeric_cols = ['numerical_1', 'numerical_2',\n     'avg_sales_lag3', 'avg_purchases_lag3', 'active_months_lag3',\n       'avg_sales_lag6', 'avg_purchases_lag6', 'active_months_lag6',\n       'avg_sales_lag12', 'avg_purchases_lag12', 'active_months_lag12']\nassert len(category_cols) + len(numeric_cols) == merchant.shape[1]","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:00:42.839046Z","iopub.execute_input":"2023-11-27T10:00:42.839370Z","iopub.status.idle":"2023-11-27T10:00:42.845479Z","shell.execute_reply.started":"2023-11-27T10:00:42.839346Z","shell.execute_reply":"2023-11-27T10:00:42.844138Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"merchant[category_cols].dtypes","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:00:46.161865Z","iopub.execute_input":"2023-11-27T10:00:46.162327Z","iopub.status.idle":"2023-11-27T10:00:46.196464Z","shell.execute_reply.started":"2023-11-27T10:00:46.162296Z","shell.execute_reply":"2023-11-27T10:00:46.194940Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"merchant[category_cols].nunique()","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:00:47.584769Z","iopub.execute_input":"2023-11-27T10:00:47.585273Z","iopub.status.idle":"2023-11-27T10:00:47.823150Z","shell.execute_reply.started":"2023-11-27T10:00:47.585236Z","shell.execute_reply":"2023-11-27T10:00:47.821279Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- 简单起见，且由于机器学习模型不支持object类型的变量，参赛者可将离散字段进行编码，根据字段含义，object型的变量要么只有两个值\n- （'category_1'，'category_4' ），要么就是含有字典序，因此参赛者可按照字典序对object型变量先编码处理(商家id除外)","metadata":{}},{"cell_type":"code","source":"# 对object型变量进行编码处理\ndef change_object_cols(se):\n    value = se.unique().tolist()\n    value.sort()\n    return se.map(pd.Series(range(len(value)), index=value)).values\nfor col in ['category_1', 'most_recent_sales_range', 'most_recent_purchases_range', 'category_4']:\n    merchant[col] = change_object_cols(merchant[col])","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:00:50.303766Z","iopub.execute_input":"2023-11-27T10:00:50.304212Z","iopub.status.idle":"2023-11-27T10:00:50.432420Z","shell.execute_reply.started":"2023-11-27T10:00:50.304182Z","shell.execute_reply":"2023-11-27T10:00:50.430119Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 同时为了能够更方便统计，首先做缺失值处理，对离散字段可统一用-1进行填充\nmerchant[category_cols].isnull().sum()","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:00:52.951080Z","iopub.execute_input":"2023-11-27T10:00:52.951507Z","iopub.status.idle":"2023-11-27T10:00:53.000460Z","shell.execute_reply.started":"2023-11-27T10:00:52.951476Z","shell.execute_reply":"2023-11-27T10:00:52.998047Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 对离散字段可统一用-1进行填补\nmerchant[category_cols] = merchant[category_cols].fillna(-1)","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:00:54.299744Z","iopub.execute_input":"2023-11-27T10:00:54.300204Z","iopub.status.idle":"2023-11-27T10:00:54.355798Z","shell.execute_reply.started":"2023-11-27T10:00:54.300171Z","shell.execute_reply":"2023-11-27T10:00:54.353392Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 简单看下每个离散字段Top10的数量分布\nfor col in category_cols[1:]:\n    merchant[col].astype(str).value_counts()[:10].sort_index().plot.bar()\n    plt.show()\n# 结论：在建模思路还不明确的情况下，参赛者可适当进行数据探查，也不必盲目面面俱到，做到心中有数\n# 对数据结构有个清晰的了解，以便后续快速进行构思与方案验证","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:00:55.888351Z","iopub.execute_input":"2023-11-27T10:00:55.888842Z","iopub.status.idle":"2023-11-27T10:00:58.701247Z","shell.execute_reply.started":"2023-11-27T10:00:55.888805Z","shell.execute_reply":"2023-11-27T10:00:58.700379Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 对离散型字段探查发现有正无穷值，这也是模型所不能接受的，因此需要对无限值进行处理\nmerchant[numeric_cols].describe()","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:01:04.579960Z","iopub.execute_input":"2023-11-27T10:01:04.580291Z","iopub.status.idle":"2023-11-27T10:01:04.727226Z","shell.execute_reply.started":"2023-11-27T10:01:04.580268Z","shell.execute_reply":"2023-11-27T10:01:04.725430Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"inf_cols = ['avg_purchases_lag3', 'avg_purchases_lag6', 'avg_purchases_lag12']\nmerchant[inf_cols] = merchant[inf_cols].replace(np.inf, merchant[inf_cols].replace(np.inf, -99).max().max())\n                                                            # 替换为-99后，对每一列取最大值，再取这些值中的最大值\n                                                            # 达到将无穷替换为最大值的目的","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:01:06.028863Z","iopub.execute_input":"2023-11-27T10:01:06.029315Z","iopub.status.idle":"2023-11-27T10:01:06.054310Z","shell.execute_reply.started":"2023-11-27T10:01:06.029287Z","shell.execute_reply":"2023-11-27T10:01:06.052843Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 对于离散字段的缺失值处理方式也有多样，这里先使用平均值进行填充，后续有需要再进行优化处理\nfor col in numeric_cols:\n    merchant[col] = merchant[col].fillna(merchant[col].mean())","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:01:07.885418Z","iopub.execute_input":"2023-11-27T10:01:07.887120Z","iopub.status.idle":"2023-11-27T10:01:07.912030Z","shell.execute_reply.started":"2023-11-27T10:01:07.887079Z","shell.execute_reply":"2023-11-27T10:01:07.910636Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"merchant[numeric_cols].describe()","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:01:09.821549Z","iopub.execute_input":"2023-11-27T10:01:09.823172Z","iopub.status.idle":"2023-11-27T10:01:09.991069Z","shell.execute_reply.started":"2023-11-27T10:01:09.823112Z","shell.execute_reply":"2023-11-27T10:01:09.989394Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 信用卡用户交易记录","metadata":{}},{"cell_type":"code","source":"# 最后是本次竞赛最重要的用户交易信息记录数据，可以看到有new_merchant_transactions.csv与historical_transactions.csv\n# 查看交易记录表信息字段的含义\ndf = pd.read_excel('/kaggle/input/elo-merchant-category-recommendation/Data_Dictionary.xlsx', sheet_name='history', skiprows=2)\nprint(df.shape)\nfor va in df.values:\n    print('Column:', va[0], ' Description:', va[1])\n    \ndf = pd.read_excel('/kaggle/input/elo-merchant-category-recommendation/Data_Dictionary.xlsx', sheet_name='new_merchant_period', skiprows=2)\nprint(df.shape)\nfor va in df.values:\n    print('Column:', va[0], ' Description:', va[1])\n# 结论：可以看出两张交易记录信息表字段一致，且除了数量与金额之外均为类别字段\n# 可按照与商家信息表类似的方式对交易记录表进行简单分析，但由于交易记录表的id索引并不唯一\n# 所有的类别字段都值得进行探索分析，当然也可以顺带进行数据的处理\n# 特别注意：有时离散字段与连续字段之间的分界并不明显，有的字段既可以作为离散字段处理，又可以\n# 作为连续字段进行处理，此外，这里出现了很特别的一个字段就是时间，这里我们会专门讲解时间字段的处理","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:01:12.978353Z","iopub.execute_input":"2023-11-27T10:01:12.978821Z","iopub.status.idle":"2023-11-27T10:01:13.030587Z","shell.execute_reply.started":"2023-11-27T10:01:12.978786Z","shell.execute_reply":"2023-11-27T10:01:13.028780Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"new_transaction = pd.read_csv('/kaggle/input/elo-merchant-category-recommendation/new_merchant_transactions.csv')","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:01:14.448454Z","iopub.execute_input":"2023-11-27T10:01:14.448924Z","iopub.status.idle":"2023-11-27T10:01:18.063112Z","shell.execute_reply.started":"2023-11-27T10:01:14.448868Z","shell.execute_reply":"2023-11-27T10:01:18.061706Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"new_transaction['installments'].describe()","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:01:20.510128Z","iopub.execute_input":"2023-11-27T10:01:20.510752Z","iopub.status.idle":"2023-11-27T10:01:20.564462Z","shell.execute_reply.started":"2023-11-27T10:01:20.510713Z","shell.execute_reply":"2023-11-27T10:01:20.562038Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"history_transaction = pd.read_csv('/kaggle/input/elo-merchant-category-recommendation/historical_transactions.csv',nrows=1000000)","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:03:15.384184Z","iopub.execute_input":"2023-11-27T10:03:15.384587Z","iopub.status.idle":"2023-11-27T10:03:16.937510Z","shell.execute_reply.started":"2023-11-27T10:03:15.384558Z","shell.execute_reply":"2023-11-27T10:03:16.936222Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"history_transaction.shape","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:03:21.163193Z","iopub.execute_input":"2023-11-27T10:03:21.164168Z","iopub.status.idle":"2023-11-27T10:03:21.171537Z","shell.execute_reply.started":"2023-11-27T10:03:21.164117Z","shell.execute_reply":"2023-11-27T10:03:21.170398Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"new_transaction.head()","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:01:49.125441Z","iopub.execute_input":"2023-11-27T10:01:49.125942Z","iopub.status.idle":"2023-11-27T10:01:49.147329Z","shell.execute_reply.started":"2023-11-27T10:01:49.125873Z","shell.execute_reply":"2023-11-27T10:01:49.145345Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"numeric_cols = [ 'installments', 'month_lag', 'purchase_amount']\ncategory_cols = ['authorized_flag', 'card_id', 'city_id', 'category_1',\n       'category_3', 'merchant_category_id', 'merchant_id', 'category_2', 'state_id',\n       'subsector_id']\ntime_cols = ['purchase_date']\nassert len(numeric_cols) + len(category_cols) + len(time_cols) == new_transaction.shape[1]","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:01:50.635367Z","iopub.execute_input":"2023-11-27T10:01:50.635916Z","iopub.status.idle":"2023-11-27T10:01:50.645657Z","shell.execute_reply.started":"2023-11-27T10:01:50.635846Z","shell.execute_reply":"2023-11-27T10:01:50.643753Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 对比可以发现与商家信息id有重复字段，因此确认二者含义是否一致\nduplicate_cols = []\nfor col in merchant.columns:\n    if col in new_transaction.columns:\n        duplicate_cols.append(col)\nprint(duplicate_cols)","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:01:52.717665Z","iopub.execute_input":"2023-11-27T10:01:52.718683Z","iopub.status.idle":"2023-11-27T10:01:52.730551Z","shell.execute_reply.started":"2023-11-27T10:01:52.718642Z","shell.execute_reply":"2023-11-27T10:01:52.728063Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(new_transaction[duplicate_cols].drop_duplicates().shape)\nprint(new_transaction.shape)\nprint(new_transaction['merchant_id'].nunique())\nprint(merchant['merchant_id'].nunique(), merchant.shape)\n# 结论：由此可以看出交易记录表中的merhcant_id相关信息并不唯一，有可能会发生动态变化如评分等级等等\n# 因此优先建议使用交易记录中表中的相应记录\nmerchant[duplicate_cols].head()","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:01:54.396669Z","iopub.execute_input":"2023-11-27T10:01:54.397123Z","iopub.status.idle":"2023-11-27T10:01:55.874196Z","shell.execute_reply.started":"2023-11-27T10:01:54.397088Z","shell.execute_reply":"2023-11-27T10:01:55.872465Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"new_transaction[duplicate_cols].head()","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:01:57.272914Z","iopub.execute_input":"2023-11-27T10:01:57.274360Z","iopub.status.idle":"2023-11-27T10:01:57.341582Z","shell.execute_reply.started":"2023-11-27T10:01:57.274295Z","shell.execute_reply":"2023-11-27T10:01:57.339847Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"new_transaction[category_cols].head()","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:01:58.701037Z","iopub.execute_input":"2023-11-27T10:01:58.701466Z","iopub.status.idle":"2023-11-27T10:01:58.818833Z","shell.execute_reply.started":"2023-11-27T10:01:58.701432Z","shell.execute_reply":"2023-11-27T10:01:58.816862Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 可仿照merchant的处理方式对字符型的离散特征进行编码（id除外）\nfor col in ['authorized_flag', 'category_1', 'category_3']:\n    new_transaction[col] = change_object_cols(new_transaction[col].fillna(-1).astype(str))\nnew_transaction[category_cols] = new_transaction[category_cols].fillna(-1)\n","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:02:00.247696Z","iopub.execute_input":"2023-11-27T10:02:00.249247Z","iopub.status.idle":"2023-11-27T10:02:01.416857Z","shell.execute_reply.started":"2023-11-27T10:02:00.249184Z","shell.execute_reply":"2023-11-27T10:02:01.415795Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"category_cols","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:02:04.410929Z","iopub.execute_input":"2023-11-27T10:02:04.411459Z","iopub.status.idle":"2023-11-27T10:02:04.421190Z","shell.execute_reply.started":"2023-11-27T10:02:04.411416Z","shell.execute_reply":"2023-11-27T10:02:04.420318Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 由于两张交易表是根据信用卡用户消费的时间来进行区分，因此有必要着重分析这二者之间的区别与联系\nprint(new_transaction['month_lag'].describe())\nprint('\\n')\nprint(new_transaction['purchase_date'].min(), 'to', new_transaction['purchase_date'].max())","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:02:05.900916Z","iopub.execute_input":"2023-11-27T10:02:05.901762Z","iopub.status.idle":"2023-11-27T10:02:06.142302Z","shell.execute_reply.started":"2023-11-27T10:02:05.901715Z","shell.execute_reply":"2023-11-27T10:02:06.140662Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(history_transaction['month_lag'].describe())\nprint('\\n')\nprint(history_transaction['purchase_date'].min(), 'to', history_transaction['purchase_date'].max())","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:03:35.009253Z","iopub.execute_input":"2023-11-27T10:03:35.009793Z","iopub.status.idle":"2023-11-27T10:03:35.147733Z","shell.execute_reply.started":"2023-11-27T10:03:35.009752Z","shell.execute_reply":"2023-11-27T10:03:35.146440Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 看到这里参赛者难免会有些疑惑，所谓历史与最新的交易整体时间窗差距两个月，但并不是代表\n# 每个用户的交易记录时间均处在同一时间窗，因此有必要重新审视一下用户的注册日期\nprint(train['first_active_month'].min(), train['first_active_month'].max())\nprint(test['first_active_month'].dropna().min(), test['first_active_month'].dropna().max())","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:02:12.637759Z","iopub.execute_input":"2023-11-27T10:02:12.638103Z","iopub.status.idle":"2023-11-27T10:02:12.693499Z","shell.execute_reply.started":"2023-11-27T10:02:12.638077Z","shell.execute_reply":"2023-11-27T10:02:12.692601Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#分析每个用户在存在交易月份数量上的差异\nhistory_transaction.groupby('card_id')['month_lag'].nunique().describe()","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:03:42.711185Z","iopub.execute_input":"2023-11-27T10:03:42.712489Z","iopub.status.idle":"2023-11-27T10:03:42.881698Z","shell.execute_reply.started":"2023-11-27T10:03:42.712431Z","shell.execute_reply":"2023-11-27T10:03:42.879831Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 关于时间的处理上，可以按照一些字符串操作提取相应的信息，比如直接提取年份、月份、日期和小时点等等\n# 还有一种相对万能可适用于各种场景的办法就是使用unix时间戳，可以灵活用于各种转换与计算\nnew_transaction[new_transaction['month_lag']==1]['purchase_date'].apply(lambda x:'-'.join(x.split(' ')[0].split('-')[:2])).value_counts()","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:03:49.794851Z","iopub.execute_input":"2023-11-27T10:03:49.795244Z","iopub.status.idle":"2023-11-27T10:03:50.729121Z","shell.execute_reply.started":"2023-11-27T10:03:49.795214Z","shell.execute_reply":"2023-11-27T10:03:50.727552Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"history_transaction[history_transaction['month_lag']==0]['purchase_date'].apply(lambda x:'-'.join(x.split(' ')[0].split('-')[:2])).value_counts()","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:03:52.498995Z","iopub.execute_input":"2023-11-27T10:03:52.499603Z","iopub.status.idle":"2023-11-27T10:03:52.619000Z","shell.execute_reply.started":"2023-11-27T10:03:52.499570Z","shell.execute_reply":"2023-11-27T10:03:52.617017Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- 结论：由上面可知month_lag是一个相对值，每个信用卡id的month_lag=0的月份都不一样，即用户最新一次购物的时间不一样\n- 显然越是出现在最新日期的用户越有可能粘性越强价值越高，也就是题目所衡量的越忠诚","metadata":{}},{"cell_type":"markdown","source":"## 目标列相关指标分析","metadata":{}},{"cell_type":"code","source":"# 在熟悉基本比赛数据后，直觉上参赛者可考虑使用RFM分析，即采用recent、frequency、money进行\n# 目标列相关性分析,简单起见，这里以new_transaction为例，由于都是最近记录因此就不统计recent\ncard_rfm = train[['card_id', 'target']].copy()\n\ncard_rfm['frequency'] = card_rfm['card_id'].map(new_transaction['card_id'].value_counts()).fillna(0).values\ncard_rfm['money'] = card_rfm['card_id'].map(new_transaction.groupby('card_id')['purchase_amount'].sum()).fillna(0).values\n\ncard_rfm = card_rfm.sort_values('target').reset_index(drop=True)\nfor col in ['target', 'frequency', 'money']:\n    card_rfm[col].plot.hist()\n    plt.show()\n    \n# 结论：可以看出，target高的用户集中在频次与消费都高的区域，这个发现也比较符合逻辑","metadata":{"execution":{"iopub.status.busy":"2023-11-27T10:04:00.529761Z","iopub.execute_input":"2023-11-27T10:04:00.530279Z","iopub.status.idle":"2023-11-27T10:04:02.399287Z","shell.execute_reply.started":"2023-11-27T10:04:00.530242Z","shell.execute_reply":"2023-11-27T10:04:02.397819Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 总结：通过基本的数据探索性分析，相信参赛者对数据以及对赛题任务都有了一个良好的理解，相信\n# 本赛题的重点便是挖掘用户的各种交易行为与目标列的关系，进而达到良好的模型学习效果，准确预测\n# 测试集用户的忠诚度分数。因此这是一个关注信用卡用户局部消费偏好画像的题目，通过找到相似的训练集用户来\n# 类推测试集用户的忠诚度分数，进而对高价值人群进行区分，给商家与信用卡银行提供决策支持，同时也\n# 提升消费者体验，由此特征工程可集中于用户的交易行为画像","metadata":{"execution":{"iopub.status.busy":"2023-11-26T07:23:45.798544Z","iopub.execute_input":"2023-11-26T07:23:45.799016Z","iopub.status.idle":"2023-11-26T07:23:45.805121Z","shell.execute_reply.started":"2023-11-26T07:23:45.798982Z","shell.execute_reply":"2023-11-26T07:23:45.803869Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 二、特征提取","metadata":{}},{"cell_type":"code","source":"import gc\nimport time\nimport numpy as np\nimport pandas as pd\nimport lightgbm as lgb\nfrom datetime import datetime","metadata":{"execution":{"iopub.status.busy":"2023-11-26T09:21:10.223879Z","iopub.execute_input":"2023-11-26T09:21:10.224368Z","iopub.status.idle":"2023-11-26T09:21:14.710002Z","shell.execute_reply.started":"2023-11-26T09:21:10.224292Z","shell.execute_reply":"2023-11-26T09:21:14.708317Z"},"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')\nmerchant = pd.read_csv('/kaggle/input/elo-merchant-category-recommendation/merchants.csv')\nnew_transaction = pd.read_csv('/kaggle/input/elo-merchant-category-recommendation/new_merchant_transactions.csv')\n\nn_rows_to_read = 1000000\nhistory_transaction = pd.read_csv('/kaggle/input/elo-merchant-category-recommendation/historical_transactions.csv',nrows=n_rows_to_read)\n\n# 按照字典序进行编码\ndef change_object_cols(se):\n    value = se.unique().tolist()\n    value.sort()\n    return se.map(pd.Series(range(len(value)), index=value)).values","metadata":{"execution":{"iopub.status.busy":"2023-11-26T09:23:03.574449Z","iopub.execute_input":"2023-11-26T09:23:03.574988Z","iopub.status.idle":"2023-11-26T09:23:17.625285Z","shell.execute_reply.started":"2023-11-26T09:23:03.574941Z","shell.execute_reply":"2023-11-26T09:23:17.623965Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## train&test\n# 对首次活跃月份进行编码\n# se_map = change_object_cols(train['first_active_month'].append(test['first_active_month']).astype(str))\ntrain['first_active_month'] = change_object_cols(train['first_active_month'].astype(str))\ntest['first_active_month'] = change_object_cols(test['first_active_month'].astype(str))","metadata":{"execution":{"iopub.status.busy":"2023-11-26T09:23:31.009360Z","iopub.execute_input":"2023-11-26T09:23:31.009815Z","iopub.status.idle":"2023-11-26T09:23:31.118231Z","shell.execute_reply.started":"2023-11-26T09:23:31.009779Z","shell.execute_reply":"2023-11-26T09:23:31.117021Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## 商户信息\n# 1、根据业务含义划分离散字段category_cols与连续字段numeric_cols。\ncategory_cols = ['merchant_id', 'merchant_group_id', 'merchant_category_id',\n       'subsector_id', 'category_1',\n       'most_recent_sales_range', 'most_recent_purchases_range',\n       'category_4', 'city_id', 'state_id', 'category_2']\nnumeric_cols = ['numerical_1', 'numerical_2',\n     'avg_sales_lag3', 'avg_purchases_lag3', 'active_months_lag3',\n       'avg_sales_lag6', 'avg_purchases_lag6', 'active_months_lag6',\n       'avg_sales_lag12', 'avg_purchases_lag12', 'active_months_lag12']\n\n# 2、对非数值型的离散字段进行字典排序编码。\nfor col in ['category_1', 'most_recent_sales_range', 'most_recent_purchases_range', 'category_4']:\n    merchant[col] = change_object_cols(merchant[col])\n    \n# 3、为了能够更方便统计，进行缺失值的处理，对离散字段统一用-1进行填充。\nmerchant[category_cols] = merchant[category_cols].fillna(-1)\n\n\n# 4、对离散型字段探查发现有正无穷值，这是特征提取以及模型所不能接受的，因此需要对无限值进行处理，此处采用最大值进行替换。\ninf_cols = ['avg_purchases_lag3', 'avg_purchases_lag6', 'avg_purchases_lag12']\nmerchant[inf_cols] = merchant[inf_cols].replace(np.inf, merchant[inf_cols].replace(np.inf, -99).max().max())\n\n# 5、对于离散字段的缺失值处理方式也有多样，这里先使用平均值进行填充，后续有需要再进行优化处理。\nfor col in numeric_cols:\n    merchant[col] = merchant[col].fillna(merchant[col].mean())\n    \n# 6、去除与transaction交易记录表格重复的列，以及merchant_id的重复记录。\nduplicate_cols = ['merchant_id', 'merchant_category_id', 'subsector_id', 'category_1', 'city_id', 'state_id', 'category_2']\nmerchant = merchant.drop(duplicate_cols[1:], axis=1)\nmerchant = merchant.loc[merchant['merchant_id'].drop_duplicates().index.tolist()].reset_index(drop=True)","metadata":{"execution":{"iopub.status.busy":"2023-11-26T09:23:37.964535Z","iopub.execute_input":"2023-11-26T09:23:37.965541Z","iopub.status.idle":"2023-11-26T09:23:38.583963Z","shell.execute_reply.started":"2023-11-26T09:23:37.965485Z","shell.execute_reply":"2023-11-26T09:23:38.582908Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## 交易记录\n# 1、为了统一处理，首先拼接new和history两张表格，后续可以month_lag>=0进行区分。\ntransaction = pd.concat([new_transaction, history_transaction], axis=0, ignore_index=True)\ndel new_transaction\ndel history_transaction\ngc.collect()\n\n# 2、同样划分离散字段、连续字段以及时间字段。\nnumeric_cols = [ 'installments', 'month_lag', 'purchase_amount']\ncategory_cols = ['authorized_flag', 'card_id', 'city_id', 'category_1',\n       'category_3', 'merchant_category_id', 'merchant_id', 'category_2', 'state_id',\n       'subsector_id']\ntime_cols = ['purchase_date']\n\n# 3、可仿照merchant的处理方式对字符型的离散特征进行字典序编码以及缺失值填充。\nfor col in ['authorized_flag', 'category_1', 'category_3']:\n    transaction[col] = change_object_cols(transaction[col].fillna(-1).astype(str))\ntransaction[category_cols] = transaction[category_cols].fillna(-1)\ntransaction['category_2'] = transaction['category_2'].astype(int)\n\n# 4、进行时间段的处理，简单起见进行月份、日期的星期数（工作日与周末）、以及\n# 时间段（上午、下午、晚上、凌晨）的信息提取。\ntransaction['purchase_month'] = transaction['purchase_date'].apply(lambda x:'-'.join(x.split(' ')[0].split('-')[:2]))\ntransaction['purchase_hour_section'] = transaction['purchase_date'].apply(lambda x: x.split(' ')[1].split(':')[0]).astype(int)//6\ntransaction['purchase_day'] = transaction['purchase_date'].apply(lambda x: datetime.strptime(x.split(\" \")[0], \"%Y-%m-%d\").weekday())//5                                                                    \ndel transaction['purchase_date']\n\n\n# 5、对新生成的购买月份离散字段进行字典序编码。\ntransaction['purchase_month'] = change_object_cols(transaction['purchase_month'].fillna(-1).astype(str))","metadata":{"execution":{"iopub.status.busy":"2023-11-26T09:23:45.915988Z","iopub.execute_input":"2023-11-26T09:23:45.916494Z","iopub.status.idle":"2023-11-26T09:24:41.742668Z","shell.execute_reply.started":"2023-11-26T09:23:45.916455Z","shell.execute_reply":"2023-11-26T09:24:41.741096Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"new_transaction = pd.read_csv('/kaggle/input/elo-merchant-category-recommendation/new_merchant_transactions.csv')\nnew_transaction['purchase_date']","metadata":{"execution":{"iopub.status.busy":"2023-11-26T08:34:05.427676Z","iopub.execute_input":"2023-11-26T08:34:05.428594Z","iopub.status.idle":"2023-11-26T08:34:11.965334Z","shell.execute_reply.started":"2023-11-26T08:34:05.428548Z","shell.execute_reply":"2023-11-26T08:34:11.964305Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"new_transaction['purchase_date'].apply(lambda x:'-'.join(x.split(' ')[0].split('-')[:2]))","metadata":{"execution":{"iopub.status.busy":"2023-11-26T08:39:34.436848Z","iopub.execute_input":"2023-11-26T08:39:34.437358Z","iopub.status.idle":"2023-11-26T08:39:36.862145Z","shell.execute_reply.started":"2023-11-26T08:39:34.437324Z","shell.execute_reply":"2023-11-26T08:39:36.860880Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"new_transaction['purchase_date'].apply(lambda x: x.split(' ')[1].split(':')[0]).astype(int)//6","metadata":{"execution":{"iopub.status.busy":"2023-11-26T08:46:11.392158Z","iopub.execute_input":"2023-11-26T08:46:11.392988Z","iopub.status.idle":"2023-11-26T08:46:13.385869Z","shell.execute_reply.started":"2023-11-26T08:46:11.392950Z","shell.execute_reply":"2023-11-26T08:46:13.384965Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"new_transaction['purchase_date'].apply(lambda x: datetime.strptime(x.split(\" \")[0], \"%Y-%m-%d\").weekday())//5 ","metadata":{"execution":{"iopub.status.busy":"2023-11-26T09:01:22.936093Z","iopub.execute_input":"2023-11-26T09:01:22.937294Z","iopub.status.idle":"2023-11-26T09:01:51.883656Z","shell.execute_reply.started":"2023-11-26T09:01:22.937229Z","shell.execute_reply":"2023-11-26T09:01:51.882371Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## 表格合并\n# 6、处理完transaction与merchant两个表格后，为了方便特征的统一计算将其merge合并，重新划分相应字段种类。\ncols = ['merchant_id', 'most_recent_sales_range', 'most_recent_purchases_range', 'category_4']\ntransaction = pd.merge(transaction, merchant[cols], how='left', on='merchant_id')\n\nnumeric_cols = ['purchase_amount', 'installments']\n\ncategory_cols = ['authorized_flag', 'city_id', 'category_1',\n       'category_3', 'merchant_category_id','month_lag','most_recent_sales_range',\n                 'most_recent_purchases_range', 'category_4',\n                 'purchase_month', 'purchase_hour_section', 'purchase_day']\n\nid_cols = ['card_id', 'merchant_id']\n\ntransaction[cols[1:]] = transaction[cols[1:]].fillna(-1).astype(int)\ntransaction[category_cols] =transaction[category_cols].fillna(-1).astype(str)","metadata":{"execution":{"iopub.status.busy":"2023-11-26T09:24:54.028042Z","iopub.execute_input":"2023-11-26T09:24:54.029102Z","iopub.status.idle":"2023-11-26T09:25:22.126543Z","shell.execute_reply.started":"2023-11-26T09:24:54.029064Z","shell.execute_reply":"2023-11-26T09:25:22.125337Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## 初步策略，第一步采用字典结构统计用户的所有行为特征\n## 第二步，用户层面横向进行比较和特征提取\n## 第三步，纵向对比所有用户的交易记录\n\nfeatures = {}\ncard_all = pd.concat([train['card_id'],test['card_id']]).values.tolist()\nfor card in card_all:\n    features[card] = {}\n    \n\ncolumns = transaction.columns.tolist()\nidx = columns.index('card_id')\ncategory_cols_index = [columns.index(col) for col in category_cols]\nnumeric_cols_index = [columns.index(col) for col in numeric_cols]\n\n\n\n# 记录运行时间\ns = time.time()\nnum = 0\nfor i in range(transaction.shape[0]):\n    va = transaction.loc[i].values\n    card = va[idx]\n    for cate_ind in category_cols_index:\n        for num_ind in numeric_cols_index:\n            col_name = '&'.join([columns[cate_ind], va[cate_ind], columns[num_ind]])\n            features[card][col_name] = features[card].get(col_name, 0) + va[num_ind]\n    num += 1\n    if num%1000000==0:\n        print(time.time()-s, \"s\")\ndel transaction\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2023-11-26T09:36:47.699516Z","iopub.execute_input":"2023-11-26T09:36:47.700976Z","iopub.status.idle":"2023-11-26T09:45:11.096522Z","shell.execute_reply.started":"2023-11-26T09:36:47.700921Z","shell.execute_reply":"2023-11-26T09:45:11.095614Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 字典转dataframe\ndf = pd.DataFrame(features).T.reset_index()\ndel features\ncols = df.columns.tolist()\ndf.columns = ['card_id'] + cols[1:]\n# 生成训练集与测试集\ntrain = pd.merge(train, df, how='left', on='card_id')\ntest =  pd.merge(test, df, how='left', on='card_id')\ndel df\ntrain.to_csv(\"/kaggle/working/train_dict.csv\", index=False)\ntest.to_csv(\"/kaggle/working/test_dict.csv\", index=False)","metadata":{"execution":{"iopub.status.busy":"2023-11-26T09:49:08.204110Z","iopub.execute_input":"2023-11-26T09:49:08.204769Z","iopub.status.idle":"2023-11-26T09:56:35.089131Z","shell.execute_reply.started":"2023-11-26T09:49:08.204722Z","shell.execute_reply":"2023-11-26T09:56:35.087682Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}