{"cells":[{"metadata":{"_uuid":"5379ff0174b63bfb9fd7b9bafa2e6f0165fc37b7"},"cell_type":"markdown","source":"In many of the discussions and kernels for this competition the reference date was mentioned as end Feb 2018 or 1st March 2018.\nThis was based on the fact the the last transaction date in historical transactions is 28th feb 2018. \nIt's also assumed by many that the customer loyalty score is calculated as on this date. \n**This  is not true . There are multiple reference dates  as explained below ** .\n"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"import numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport time\nimport sys\nimport datetime\nimport gc\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nfrom textwrap import wrap\nimport warnings\nwarnings.filterwarnings(\"ignore\")\nimport os\nprint(os.listdir(\"../input\"))\n\n# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c55ed4044b1a2e9eac7e50f32b57c9fbc8019d4b","_kg_hide-input":true},"cell_type":"code","source":"train = pd.read_csv(\"../input/train.csv\")\ntest = pd.read_csv(\"../input/test.csv\")\nhistory = pd.read_csv(\"../input/historical_transactions.csv\",parse_dates=['purchase_date'])\nnew =pd.read_csv(\"../input/new_merchant_transactions.csv\",parse_dates=['purchase_date'])\nhistory = history.loc[history.authorized_flag ==\"Y\",]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8923c8496f83d841a77b29d36f98d7a5ca7c4584"},"cell_type":"markdown","source":"As explained the historical_transactions.csv and new_merchant_transactions.csv files contain information about each card's transactions. historical_transactions.csv contains up to 3 months' worth of transactions for every card at any of the provided merchant_ids. new_merchant_transactions.csv contains the transactions at new merchants (merchant_ids that this particular card_id has not yet visited) over a period of two months.\n"},{"metadata":{"trusted":true,"_uuid":"281813474be673d6fb2264f2178a64a3cc3d9864"},"cell_type":"code","source":"print(history.purchase_date.min(),history.purchase_date.max())\nprint(new.purchase_date.min(),new.purchase_date.max())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c26e04513ccc3ba1dbbff98a15e398a6d78368aa"},"cell_type":"markdown","source":"From the above result it is clear that seggragation of transactions into new and historical starts from March 2017 ."},{"metadata":{"trusted":true,"_uuid":"9bc42655751ccc8e9ee80db637895ebc372b714b"},"cell_type":"code","source":"print(new.purchase_date[new.month_lag==1].min(),new.purchase_date[new.month_lag==1].max())\nprint(new.purchase_date[new.month_lag==2].min(),new.purchase_date[new.month_lag==2].max())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fe0f9ea5ea30cc34fd95990ea0a8f18354a1b160"},"cell_type":"markdown","source":"Month lag is defined in Data Dictionary .xls as the month lag to reference date. From the above results of the earliest purchase dates for month lag of 1 & 2 for new transactions  it looks like there are multiple reference dates.\nAs per the definition of month lag month lag of 0 would mean the last month of transactions in  historical transaction data for each card_id.\nLet's look at the earliest and the latest purchase dates for month lag of 0 in historical transactions."},{"metadata":{"trusted":true,"_uuid":"9be845c1d09b253977d38523ae5ca2075fc81dc2"},"cell_type":"code","source":"print(history.loc[history.month_lag==0,'purchase_date'].min())\nprint(history.loc[history.month_lag==0,'purchase_date'].max())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c463126502e4ea96d1a41ef7c470130ca10fb119"},"cell_type":"markdown","source":"**This confrms that the refernce month could vary from February 2017 to February 2018**. \n \nLet's create a dataframe which captures the last purchase date for the month lag ==0 . Column ' refernce_ date' is the first date of the next month based on the month of the last purchase date for the month lag ==0"},{"metadata":{"trusted":true,"_uuid":"bea391cb67fd4c2b8a9b6fc4cea33f48421569c0"},"cell_type":"code","source":"cardreferencedate = history.loc[history.month_lag==0,].groupby('card_id').agg({'purchase_date' : 'max'})\ncardreferencedate.reset_index(inplace=True)\ncardreferencedate['reference_date'] = cardreferencedate.purchase_date.apply(lambda x :x+ pd.offsets.MonthBegin())\ncardreferencedate['reference_date']=cardreferencedate['reference_date'].apply(lambda x: x.replace(hour=0, minute=0, second=0))\ncardreferencedate.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"419cad0b7f249dbea2e9b640ecef00db4b636949"},"cell_type":"code","source":"cardreferencedate.loc[:,'reference_date'].value_counts().sort_index().plot(kind='bar')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e4a88715716b968d0bb0c62c7886f384aea56114"},"cell_type":"markdown","source":"About 70% of the card id's have March 1st 2018 as the reference date . Let's probe new transactions for those card id's having a refernce date of  March 1st 2017."},{"metadata":{"trusted":true,"_uuid":"9b30ec6509253bbc3ae4b1b849b2761516101a39"},"cell_type":"code","source":"new.loc[new.card_id.isin(cardreferencedate.card_id[cardreferencedate.reference_date=='2017-03-01 00:00:00']),]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7e8f4471c7a7d575e6a526e5ac41b280d86d02f2"},"cell_type":"code","source":"print(new.loc[new.card_id.isin(cardreferencedate.card_id[cardreferencedate.reference_date=='2017-03-01 00:00:00']),'purchase_date'].min())\nprint(new.loc[new.card_id.isin(cardreferencedate.card_id[cardreferencedate.reference_date=='2017-03-01 00:00:00']),'purchase_date'].max())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d288ee94ea37b61f3200c7eac8a3c347f916aa53"},"cell_type":"markdown","source":"For those card id's with reference date 01- March-2017 the new merchant transactions happen in March & April 2017. It looks like the refernce date is a cut offf date for seggregating historical and new transactions.\nLet's confirm these by further exploratory analysis"},{"metadata":{"trusted":true,"_uuid":"1c17ecb981fa8c9a9c6a25c9894af2f11554ac94"},"cell_type":"code","source":"print(\"Number of cards with reference date\", len(cardreferencedate.index))\nprint(\"Number of cards in train\",len(train.index))\nprint(\"Number of cards in test\",len(test.index))\nprint(\"Number of unique cards in history\",len(history.card_id.unique()))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ea7beadc5a994b154cb92dd071d851231b112beb"},"cell_type":"markdown","source":"About 32,368 cards are missing from the cardreferncedate we created (325,540 unique cards in history - 293,172).\n\nThis implies that these cards don't have any transaction records with month lag==0 .  These card ids's are stored in the array 'Nozeromonthlag'"},{"metadata":{"trusted":true,"_uuid":"7b720153dfc73b0336325fd19bd4ee2336605845"},"cell_type":"code","source":"Nozeromonthlag = history.loc[~history.card_id.isin(cardreferencedate.card_id),'card_id'].unique()\nlen(Nozeromonthlag)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f908be0520aa42c6a3dccfa2aaba61bed23f6947"},"cell_type":"markdown","source":"This finding is confirmed by checking the transactions of card_id 'C_ID_21117571cf'.\nThere are no records for monthlag==0"},{"metadata":{"trusted":true,"_uuid":"4857e4c4805b5462210d15247d5ef3245b510b74"},"cell_type":"code","source":"history.loc[history.card_id=='C_ID_21117571cf','month_lag'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7d0cf9593fde4f11bfa1233d4a4d75cf949b9951"},"cell_type":"markdown","source":"The historical transactions for card_id=='C_ID_21117571cf' is below. Last transaction for this acrd is in Dec 2017 and the month lag is -2 which implies the reference date is Mar 01 ,2018"},{"metadata":{"trusted":true,"_uuid":"106292fcc744c75eaaf56e33bb7b47b248421947"},"cell_type":"code","source":"history.loc[history.card_id=='C_ID_21117571cf',]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"75a5cee77704079a696733f0dd563925f6df8db3"},"cell_type":"markdown","source":"The new merchant transactions for the card is below. The transactions appear 2 month after the  refernce date. Hence we can assume that this card had no transactions during Jan & Feb 2018 , but had some new merchant transactions in April 2018. \n\n**This confirms our assumption that refernce date is a  cut off date for seggregating  historical and new transactions for a specific card.**\n"},{"metadata":{"trusted":true,"_uuid":"3adf29e01b6bd96c9750f0d2cf37c530940b83cd"},"cell_type":"code","source":"new.loc[new.card_id=='C_ID_21117571cf',]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3fdd42a2557ed9d88d0e36c5a00c98870459bc3c"},"cell_type":"markdown","source":"Code below creates a  a data frame ' Nozeromonthlagrefdate' for those card_id's without any  0 values for Month_lag  based on the closest month_lag value to 0."},{"metadata":{"trusted":true,"_uuid":"a665e37bb41745c6c03fb2b94fb3dc9076b0e5f5"},"cell_type":"code","source":"Nozeromonthlagrefdate = history.loc[~history.card_id.isin(cardreferencedate.card_id),].groupby('card_id').agg({'month_lag' : 'max','purchase_date':'max'})\nNozeromonthlagrefdate.reset_index(inplace=True)\nNozeromonthlagrefdate['month_add'] = Nozeromonthlagrefdate.month_lag.apply(lambda x : abs(x))\nNozeromonthlagrefdate['reference_date'] = Nozeromonthlagrefdate.apply(lambda x: x['purchase_date'] + pd.DateOffset(months = x['month_add']), axis=1)\nNozeromonthlagrefdate['reference_date'] = Nozeromonthlagrefdate.reference_date.apply(lambda x :x+ pd.offsets.MonthBegin())\nNozeromonthlagrefdate['reference_date'] = Nozeromonthlagrefdate['reference_date'].apply(lambda x: x.replace(hour=0, minute=0, second=0))\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"236f3652b94c477478204e7cdd35d27ecba83f97"},"cell_type":"markdown","source":"Let's check whether all card id's with non zero month lag appear in new merchant transaction. As shown below only 26,849 out of 32,638 appear in new list."},{"metadata":{"trusted":true,"_uuid":"96557b289585c3bac78973eab4349609bc898907"},"cell_type":"code","source":"sum(Nozeromonthlagrefdate.card_id.isin(new.card_id))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"acdb2064e7525abdfa6004f7a9b5c805e370df04"},"cell_type":"markdown","source":"Let's examine the balance 5,789 cards reference dates "},{"metadata":{"trusted":true,"_uuid":"3f213004874cbc6686d1f34c4df36011cea1e4c1"},"cell_type":"code","source":"Nozeromonthlagrefdate.loc[~Nozeromonthlagrefdate.card_id.isin(new.card_id),'reference_date'].value_counts().plot(kind='bar')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"16966340d21d543f9bc43184a51d337eb4c1584e"},"cell_type":"markdown","source":"**It's surprising that these cards have different reference date even though they don't have any new merchant transactions. This merits further probing as this goes against the assumption that  reference date is the cut off date for seggregating  historical and new transactions. .**"},{"metadata":{"trusted":true,"_uuid":"f46e52455868b0fd8639ed86d538491103b5c9a6"},"cell_type":"code","source":"Nozeromonthlag_nonewtransaction_card_id =Nozeromonthlagrefdate.card_id[~Nozeromonthlagrefdate.card_id.isin(new.card_id)]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8c58adb122f7486305f5ee12fcfee6a995f4442c"},"cell_type":"code","source":"Nozeromonthlag_nonewtransaction_card_agg=  history.loc[history.card_id.isin(Nozeromonthlag_nonewtransaction_card_id),].groupby(['card_id']).agg({'card_id': 'count','month_lag': ['min','max'],'purchase_date': ['min','max'] })","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c027fab242e9bcaceec617c07527e5ec6fdfc439"},"cell_type":"code","source":"Nozeromonthlag_nonewtransaction_card_agg.head(50)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"10ae5ca2858d592cf62d52967c8a44773c19b274"},"cell_type":"markdown","source":"From the above results it's not clear why the reference date for these cards are not 1st day of the month succeding their last transaction month . Since they don't have any new merchant transactions  further probing is not possible. But the mean customerloyalty scores of these cards is 0.53 which is significantly higher than -0.39 for  the target mean."},{"metadata":{"trusted":true,"_uuid":"0ad2335568fca2b1ba4b2d0c7d5b35a48526b98e"},"cell_type":"code","source":"print(train.target.mean())\nprint(train.target[train.card_id.isin(Nozeromonthlag_nonewtransaction_card_id)].mean())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"03a9cf4f58ea5a069c8362800fca33875c34b4c8"},"cell_type":"code","source":"sns.boxplot(y=train.target[train.card_id.isin(Nozeromonthlag_nonewtransaction_card_id)])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1664e70e4df2ca2c777a31d2cb49a4df673cf4ea"},"cell_type":"markdown","source":"The data frames are combined to form a data frame with reference_date value for each card_id in the historical transaction. We will also categorize the cards into four categories.\n* 0- Cards with Reference date based on **Month lag==0**  in historical transactions(cards in the cardsreferencedate dataframe) and having new merchant transactions \n* 1-  Cards with Reference date based on **Month lag==0**  in historical transactions(cards in the cardsreferencedate dataframe) and  **not** having new merchant transactions .\n* 2-  Cards with Reference date based on **Non zero month lag** in historical transactions(cards in the cardsreferencedate dataframe) and having new merchant transactions.\n* 3-  Cards with Reference date based on **Non zero month lag** in historical transactions(cards in the cardsreferencedate dataframe) and **not** having  new merchant transactions."},{"metadata":{"trusted":true,"_uuid":"e280a93ea4ffc26d6e5fa5202063183eb8688afe"},"cell_type":"code","source":"zeromonthlagmissinginnew= cardreferencedate.card_id[~cardreferencedate.card_id.isin(new.card_id)]\nlen(zeromonthlagmissinginnew)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a671963ed1038e168db9421a711382054ca207f6"},"cell_type":"code","source":"cardreferencedate.drop(columns='purchase_date',inplace=True)\ncardreferencedate['category_month_lag'] =np.where(cardreferencedate.card_id.isin(zeromonthlagmissinginnew),1,0)\nNozeromonthlagrefdate['category_month_lag']= np.where(Nozeromonthlagrefdate.card_id.isin(Nozeromonthlag_nonewtransaction_card_id),3,2)\nNozeromonthlagrefdate.drop(columns=['month_lag','purchase_date','month_add'],inplace=True)\ncardreferencedate= pd.concat([cardreferencedate,Nozeromonthlagrefdate])\ncardreferencedate.to_csv(\"Cardreferencedate.csv\",index=False)\nlen(cardreferencedate.index)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4a0488fa8ee290dba95689f1ed472546a7ece040"},"cell_type":"code","source":"cardreferencedate = pd.merge(cardreferencedate,train.loc[:,['card_id','target']],on='card_id',how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5bae322b8304b4c9694d73c9cb006d27a5230c26"},"cell_type":"code","source":"cardreferencedate.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7bb36e993d1d2ca475a307940aa819f271cd2e68"},"cell_type":"code","source":"sns.set(rc={'figure.figsize':(24,12)})\np1= sns.boxplot(x=cardreferencedate.reference_date,y=cardreferencedate.target)\nlabels = [item.get_text() for item in p1.get_xticklabels()]\nlabels =[ '\\n'.join(wrap(l, 10)) for l in labels ]\np1= p1.set_xticklabels(labels, rotation=90)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4e598604c6a6e778cafd05887d28e65c75db0587"},"cell_type":"markdown","source":"Box plot shows there are no outliers  for cards with 1st March 2017 and 1st April 2017 reference dates. For other refernce dates  there is no major difference.\n"},{"metadata":{"trusted":true,"_uuid":"ccd7f150ef7a015b4d3e7fc1bffbbecc51306d34"},"cell_type":"code","source":"sns.set(rc={'figure.figsize':(24,12)})\np1= sns.boxplot(x=cardreferencedate.category_month_lag,y=cardreferencedate.target)\n# labels = [item.get_text() for item in p1.get_xticklabels()]\n# labels =[ '\\n'.join(wrap(l, 10)) for l in labels ]\n# p1= p1.set_xticklabels(labels, rotation=90)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b406b7f2dbd4dadc7f2589f9b885587b2345a03f"},"cell_type":"code","source":"sns.set(rc={'figure.figsize':(24,6)})  \nplt.subplot(1,2,1)\np1=cardreferencedate.loc[cardreferencedate.card_id.isin(train.card_id),'reference_date'].value_counts().sort_index().plot(kind='bar')\np1.set_title(\"Credit cardsreference date - Train\")\nplt.subplot(1,2,2)\np2=cardreferencedate.loc[cardreferencedate.card_id.isin(test.card_id),'reference_date'].value_counts().sort_index().plot(kind='bar')\np2.set_title(\"Credit cardsreference date - Test\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ed506c75ffba28dc308dc136d29defbe783186ba"},"cell_type":"code","source":"testmissingnew = test.card_id[~test.card_id.isin(new.card_id)]\nlen(testmissingnew)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ee2766b5e261e5256f44a2cfa8b036a8db94ccac"},"cell_type":"markdown","source":"The boxplot of target for card_id's based on the newly created category shows some difference in the mean values. But is this significant enough?\n\n***Multiple reference dates throws many questions about the dataset*.**\n\n* Does those card id's with an earlier reference date  have no transactions with their historical merchants subsequently ? or are they not included. Most of them have  transactions with new merchant id  for upto two months as is evident from the new merchant data.\n* Is the reference date just  a cut off  date to segregate historical transactions and new merchant transactions for every card id.\n* Will including the reference_date and new category  as a feature improve the RMSE ?\n\nPlease  share your thoughts on this."}],"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}