{"cells":[{"metadata":{"trusted":true,"_uuid":"5adb2abeaf1c20a37eee0c7fd6814ae2e967bfd9"},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport datetime\nimport gc\nimport math\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport os\nprint(os.listdir(\"../input\"))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"75ef28cb60ab9ade43a17ad02097742d35f59db5"},"cell_type":"markdown","source":"The idea behind this notebook is that certain category combinations in  transactions might be important and not only aggregations of categories. For instance, if unauthorized transactions are bad and high installment transactions are bad, then it is likely that transactions that are both unauthorized and that were made in a number of installments are even worse! Therefore, instead of merely aggregating a customers average in each category, this notebook aims to count transactions by category combinations. \nFirst, let's read the data:"},{"metadata":{"trusted":true,"_uuid":"8393710bf52d782af6773b798d01dcac3361c86a"},"cell_type":"code","source":"df_train = pd.read_csv(\"../input/train.csv\", parse_dates = [\"first_active_month\"])\ndf_test = pd.read_csv(\"../input/test.csv\", parse_dates = [\"first_active_month\"])\nh_trans = pd.read_csv(\"../input/historical_transactions.csv\", parse_dates = [\"purchase_date\"])\ndf_train = df_train[[\"card_id\"]]\ndf_test = df_test[[\"card_id\"]]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6eb5b8694a453e7c848555db13682d63aa3d207e"},"cell_type":"markdown","source":"Next, extract the useful columns from the *h_trans* table, ie. The columns which classify the type of transaction. Also, we'll convert the non-numeric columns to numeric, and fill all NaN cells. By replacing NaN cells with the median/mode/mean, one would lose any valuable information they might contain -> The fact that a cell is NaN might be important."},{"metadata":{"trusted":true,"_uuid":"4ac0ebd0587aa428ea83b4ff24a97368d2e6634b"},"cell_type":"code","source":"h_trans = h_trans[[\"card_id\", \"authorized_flag\", \"category_1\", \"category_2\", \"category_3\", \"purchase_date\", \"purchase_amount\", \"merchant_id\"]]\nh_trans[\"authorized_flag\"] = h_trans[\"authorized_flag\"].map({\"Y\":1, \"N\":0})\nh_trans[\"category_1\"] = h_trans[\"category_1\"].map({\"Y\":1, \"N\":0})\nh_trans = h_trans.fillna(6)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"152598485d5cded0efdf006355c2b20f7828b4c6"},"cell_type":"markdown","source":"To aggregate the unique transaction types, we first need a function that classifies which type a transaction belongs to. The following function uniquely encodes combinations in categories. (The function return \"0000\" for the extremely rare combinations)."},{"metadata":{"trusted":true,"_uuid":"0c342367191608852e5748483703c61ae6b85a45"},"cell_type":"code","source":"def cat(af, c1, c2, c3):\n    s=\"\"\n    s += str(int(c2))\n    s += str(c1)\n    s += str(af)\n    s += str(c3)\n    \n    if s in [\"101B\", \"101A\", \"611B\", \"610B\", \"301B\", \"501B\", \"401B\", \"401A\", \"301A\", \"101C\", \"501A\", \"100B\", \"611C\",\"100A\", \"201A\", \"610C\", \"201B\", \"301C\", \"300B\", \"100C\", \"401C\", \"601B\", \"601A\"]:\n        return s\n    else:\n        return \"0000\"\n\nh_trans[\"cat\"] = list(map(cat, h_trans[\"authorized_flag\"], h_trans[\"category_1\"], h_trans[\"category_2\"], h_trans[\"category_3\"]))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2c230fa8c17d15ef8f0a0dd8aac105b15a9d0430"},"cell_type":"code","source":"#Create more space\nh_trans = h_trans.drop([\"authorized_flag\", \"category_1\", \"category_2\", \"category_3\"], axis=1)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a379e64b4b7639a2c0749c14b4f42800e7648232"},"cell_type":"markdown","source":"Now that the transactions are encoded, we can extract meaningful information from the dataframe:\n1. How many transactions of type x does the customer have.\n3. What's the customers average purchase amount for a type x transaction."},{"metadata":{"trusted":true,"_uuid":"fced91d11e1c04962f9ad8dc381aa9f9c2b96d5e"},"cell_type":"code","source":"#Code to clean column names\ndef clean(prefix, df):\n    df = df.unstack()\n    df.reset_index(inplace=True)\n    df.columns = df.columns.droplevel()\n    names = []\n    i = 0\n    for col in df.columns:\n        if i == 0:\n            names.append(\"drop\")\n        elif i == 1:\n            names.append(\"card_id\")\n        else:\n            names.append(prefix+\"_\"+col)\n        i+=1\n    df.columns = names\n    return df.drop([\"drop\"],axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ec2c72527a663367a537b259dfccb54ce721de95"},"cell_type":"code","source":"dataframe = h_trans.pivot_table(index='cat', \n                                columns='card_id', \n                                values='merchant_id',\n                                fill_value=0, \n                                aggfunc={\"count\"}).unstack().to_frame().rename(columns={0:\"transaction_count\"})\n\ndataframe = clean(\"c\", dataframe)\ndf_train = pd.merge(df_train, dataframe, on = \"card_id\")\ndf_test = pd.merge(df_test, dataframe, on = \"card_id\")\n\ndf_train.to_csv(\"train_counts.csv\", index=False)\ndf_test.to_csv(\"test_counts.csv\", index=False)\ndf_train = df_train[[\"card_id\"]]\ndf_test = df_test[[\"card_id\"]]\n\ndataframe = h_trans.pivot_table(index='cat', \n                                columns='card_id', \n                                values='purchase_amount',\n                                fill_value=0, \n                                aggfunc={\"mean\"}).unstack().to_frame().rename(columns={0:\"purchase_mean\"})\n\ndataframe = clean(\"pm\", dataframe)\ndf_train = pd.merge(df_train, dataframe, on = \"card_id\")\ndf_test = pd.merge(df_test, dataframe, on = \"card_id\")\ndf_train.to_csv(\"train_pm.csv\", index=False)\ndf_test.to_csv(\"test_pm.csv\", index=False)","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.6","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}