{"cells":[{"metadata":{"_uuid":"3db4583aac6dd6b4949848f3040c350b3f3d6256"},"cell_type":"markdown","source":"The goal is to see if it's more likely to have a higher loyalty score if the card holder keeps using the payment system in the new transaction files in new merchants, because if they do and intuitively it means they like the service."},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"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 in \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 \"../input/\" directory.\n# For example, running this (by clicking run or pressing Shift+Enter) will list the files in the input directory\n\nimport os\nprint(os.listdir(\"../input\"))\n\nfrom matplotlib import pyplot as plt\n\n# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"train = pd.read_csv(\"../input/train.csv\")\ntest = pd.read_csv(\"../input/test.csv\")\nhist_tran = pd.read_csv(\"../input/historical_transactions.csv\")\nnew_tran = pd.read_csv(\"../input/new_merchant_transactions.csv\")\n\nprint(\"loading done\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2a324c32b090ec36b83888f14d4c7227189ff74c"},"cell_type":"code","source":"overlap_card_train_hist = set(train[\"card_id\"]) & set(hist_tran[\"card_id\"])\noverlap_card_train_new = set(train[\"card_id\"]) & set(new_tran[\"card_id\"])\n\noverlap_card_test_hist = set(test[\"card_id\"]) & set(hist_tran[\"card_id\"])\noverlap_card_test_new = set(test[\"card_id\"]) & set(new_tran[\"card_id\"])\n\nprint(len(overlap_card_train_hist), len(overlap_card_train_new), len(train[\"card_id\"]))\nprint(len(overlap_card_test_hist), len(overlap_card_test_new), len(test[\"card_id\"]))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e4f57065c7c13c90f5bcc0e8f37c46e5b2c28284"},"cell_type":"markdown","source":"It seems the spliting of train and test are solely based on card id hashing. Both appear 100% in historical transactions and both have some missing entries in the new transaction file meaning they didn't use it in new merchants."},{"metadata":{"_uuid":"8d1b61582605a65362f2d463d4ce615c32d103d1"},"cell_type":"markdown","source":"Now let's see if the assumption is true - that card holders continuously use the service in new merchants should tend to have higher loyalty scores."},{"metadata":{"trusted":true,"_uuid":"5796c396dfec4d5f44dccb68ada1fca00f734bc9"},"cell_type":"code","source":"train[\"card_in_new\"] = pd.Series([i in overlap_card_train_new for i in train[\"card_id\"]], index=train.index)\n\nscores_not_in_new = train[train[\"card_in_new\"] == False][\"target\"]\nscores_not_in_new.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"799a513ab3c12ab138586a0981722ada507f8d18"},"cell_type":"code","source":"scores_in_new = train[train[\"card_in_new\"] == True][\"target\"]\nscores_in_new.describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4384c723334e3a7a2f0cdad7b18ea79916e31f0b"},"cell_type":"markdown","source":"Now this doesn't make sense - the original expectation is for scores_in_new to be in general > scores_not_in_new, but the mean of scores_in_new is actually smaller. Let's plot the data."},{"metadata":{"trusted":true,"_uuid":"3f2fd0ea1ef438b53047e77aae71216d4d76de05"},"cell_type":"code","source":"\nfrom matplotlib import pyplot as plt\nplt.figure(figsize=(12, 5))\nplt.hist(scores_not_in_new.values, bins=200)\nplt.title('Histogram target counts for scores_not_in_new')\nplt.xlabel('Count')\nplt.ylabel('Target')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"85655ff53330a9fcee59bcbcd3b0e92884cb5dc7"},"cell_type":"code","source":"plt.figure(figsize=(12, 5))\nplt.hist(scores_in_new.values, bins=200)\nplt.title('Histogram target counts for scores_in_new')\nplt.xlabel('Count')\nplt.ylabel('Target')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"86e5670c7db4fe4104c79e105d7a80989015012b"},"cell_type":"markdown","source":"It seems we have outliers, let's try removing them"},{"metadata":{"trusted":true,"_uuid":"eff730442cb5a8f00cf986baab2ee210079150be"},"cell_type":"code","source":"train_cleaned = train[train[\"target\"] > -30]\n\nscores_not_in_new_cleaned = train_cleaned[train_cleaned[\"card_in_new\"] == False][\"target\"]\nprint(scores_not_in_new_cleaned.describe())\n\nscores_in_new_cleaned = train_cleaned[train_cleaned[\"card_in_new\"] == True][\"target\"]\nprint(scores_in_new_cleaned.describe())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"377e764fc96b47e580a80d6301506c3ca9678423"},"cell_type":"code","source":"\nfrom matplotlib import pyplot as plt\nplt.figure(figsize=(12, 5))\nplt.hist(scores_not_in_new_cleaned.values, bins=200)\nplt.title('Histogram target counts for scores_not_in_new_cleaned')\nplt.xlabel('Count')\nplt.ylabel('Target')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4624e0a19d047f2e0b4871f475df2af2ab4aac38"},"cell_type":"code","source":"\nfrom matplotlib import pyplot as plt\nplt.figure(figsize=(12, 5))\nplt.hist(scores_in_new_cleaned.values, bins=200)\nplt.title('Histogram target counts for scores_in_new_cleaned')\nplt.xlabel('Count')\nplt.ylabel('Target')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"95ec20e0e445a9a08805aa1c5a5a80a99613d6b7"},"cell_type":"markdown","source":"Now we see an interesting pattern. For the card holders which appear in both hist and new transaction history, there's a gap in \"target=0\", whereas for the other group (card holder not appeared in new transaction history), there's a huge spike in target=0. \n\nLet's understand this more quantitatively."},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"32caaadd80e82ac12c09c96988b0c8d5ed1027fd"},"cell_type":"code","source":"\nprint(\n    len(scores_in_new_cleaned[scores_in_new_cleaned == 0.0]),\n    len(scores_in_new_cleaned),\n    len(scores_in_new_cleaned[scores_in_new_cleaned == 0.0]) / len(scores_in_new_cleaned),\n)\nprint(\n    len(scores_not_in_new_cleaned[scores_not_in_new_cleaned == 0.0]),\n    len(scores_not_in_new_cleaned),\n    len(scores_not_in_new_cleaned[scores_not_in_new_cleaned == 0.0]) / len(scores_not_in_new_cleaned)\n)\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e7143315bd330e9292446979a912df8d973e45ca"},"cell_type":"markdown","source":"The difference is really big. 7% in the disppearing group have 0 score, whereas 0.06% in overlap group has 0 score. This leads to my guess that:\n1. loyalty score = 0 is likely a default value, which we assign when there's not enough information for us to determine the loyalty score\n2. And as long as we have **some** information, there loyalty score will very rarely become 0 (it might be very small value but really rarely 0)"},{"metadata":{"_uuid":"1267f0a50e60e14fa669b1aeab8629bb564e8fde"},"cell_type":"markdown","source":"I'm also intrigued by where those 117 come from - do we apply some kind of thresholds like, on the number of new merchants being developed in new transaction?"},{"metadata":{"trusted":true,"_uuid":"6916cc4378b093da1e6eaacaaca2f21ac8861fe5"},"cell_type":"code","source":"card_id_to_new_merchants = {}\nfor _, row in new_tran.iterrows():\n    card_id_to_new_merchants.setdefault(row.card_id, set())\n    card_id_to_new_merchants[row.card_id].add(row.merchant_id)\n    ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"995349b20a6d67267be60c476327362bbe5bb685"},"cell_type":"code","source":"train_cleaned[\"new_merchant_count\"] = pd.Series(\n    [len(card_id_to_new_merchants.get(c, set())) for c in train_cleaned[\"card_id\"]],\n    index=train_cleaned.index)\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"01be565f00574982fb88dbe432a4df558ebeb0bb"},"cell_type":"code","source":"train_cleaned_in_new = train_cleaned[train_cleaned[\"card_in_new\"] == True]\ntrain_cleaned_in_new_and_zero_score = train_cleaned_in_new[train_cleaned_in_new[\"target\"] == 0]\ntrain_cleaned_in_new_and_zero_score[\"new_merchant_count\"].describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"432bc53ff6645d9b0fc0e3d3eec42386f6a11442"},"cell_type":"code","source":"plt.figure(figsize=(12, 5))\nplt.hist(train_cleaned_in_new_and_zero_score[\"new_merchant_count\"].values, bins=200)\nplt.title('Histogram new merchant counts for train_cleaned_in_new_and_zero_score')\nplt.xlabel('Count')\nplt.ylabel('New merchant count')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"edd9df3634164454c2fd4aaf62a9a982853577da"},"cell_type":"code","source":"train_cleaned_in_new_and_nonzero_score = train_cleaned_in_new[train_cleaned_in_new[\"target\"] != 0]\nprint(train_cleaned_in_new_and_nonzero_score[\"new_merchant_count\"].describe())\n\nplt.figure(figsize=(12, 5))\nplt.hist(train_cleaned_in_new_and_nonzero_score[\"new_merchant_count\"].values, bins=200)\nplt.title('Histogram new merchant counts for train_cleaned_in_new_and_nonzero_score')\nplt.xlabel('Count')\nplt.ylabel('New merchant count')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e7f004dba7142585439f1f3da07d07441322b00d"},"cell_type":"markdown","source":"Well, my guess for the underlying rule being \"IF newMerchantCount < k THEN target = 0\" is clearly wrong, because both groups have cards with new merchant counts being 1. Still, we can clearly see higher new merchant count being a huge factor contributing to nonzero loyalty score. So my guess wasn't completely irrelevant.\n\nFinally let's also try to see the relationship between new merchant count and loyalty score "},{"metadata":{"trusted":true,"_uuid":"c8e18ff6db1b4a5d171ffdb318abcd8031ce4bb3"},"cell_type":"code","source":"plt.scatter(\n    train_cleaned_in_new[\"new_merchant_count\"].values,\n    train_cleaned_in_new[\"target\"].values\n)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"69ffc811a02faee111bad7f0ee6f8dc10a5e1341"},"cell_type":"code","source":"max_merchant_bucket = train_cleaned_in_new[\"new_merchant_count\"].max()\nexpectations = [\n    train_cleaned_in_new[train_cleaned_in_new[\"new_merchant_count\"] == i][\"target\"].mean()\n     for i in range(max_merchant_bucket + 1)\n]\nexpectations = [\n    -5 if np.isnan(v) else v\n    for v in expectations\n]\n# plt.plot(\n#     range(max_merchant_bucket + 1),\n#     [train_cleaned_in_new[train_cleaned_in_new[\"new_merchant_count\"] == i].mean()\n#      for i in range(max_merchant_bucket + 1)]\n# )","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"caaae9f1a0e2c6d7a7aa63d66d2a7124cbb25d90"},"cell_type":"code","source":"plt.scatter(\n    range(max_merchant_bucket + 1),\n    expectations\n)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ad1172d1d861ee86d0cec7ce81c40b7c762ad913"},"cell_type":"markdown","source":"Very interesting, it looks like a normal distribution but it's tricky to interpret. It seems to indicate that the loyalty score's variance will reduce as the number of merchants increases (which makes a lot of sense), but the expectation of the score doesn't necessarily increase (even slightly negatively correlated from the way it seems). \n\nOne explanation could be that some people maybe using the payment service a lot, and they discover a lot of underlying problems (but they can't stop using it for some reason, maybe it's too hard to swtich, maybe there's no other alternative, maybe they are lazy, whatever) which cause the loyalty drop. Nonetheless, aggregation of the new/hist transactions separately and trying to find some difference between the two seem to be a good direction when doing feature engineering."}],"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}