{"cells":[{"metadata":{"_uuid":"041a50eac6074d4b228fb97f4932cf33e6a0a6ee"},"cell_type":"markdown","source":"# Do points in time matter?\n\nIn this kernel we will take a closer look at all variables containing dates or timestamps and their relationship with the target variable. We will search for seasonal patterns, weekly patterns and more.\n\nThe historical data will not be used in this example. It's only of exploratory nature and does not contain any models whatsoever. It's only supposed to showcase some different possibilities for using these variables in your own models."},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import warnings\nimport datetime\nimport calendar\nimport numpy as np\nimport pandas as pd\nimport seaborn as sns\nimport matplotlib.pyplot as plt\nfrom datetime import time\nfrom dateutil.relativedelta import relativedelta\n\n# to ignore future warnings\nwarnings.simplefilter(action = 'ignore', category = FutureWarning)\n\n# set size of seaborn plots\nsns.set(rc={'figure.figsize':(10, 7)})","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"## read data\ntrain = pd.read_csv('../input/train.csv', sep = ',')\ntest = pd.read_csv('../input/test.csv', sep = ',')\nmerchants = pd.read_csv('../input/merchants.csv', sep = ',')\nnew_merchant = pd.read_csv('../input/new_merchant_transactions.csv', sep = ',')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9ec4fd5ecce479fa097500d503be1d47852f7876"},"cell_type":"markdown","source":"Before we can start we obviously have to merge the data."},{"metadata":{"trusted":true,"_uuid":"16d46977823edc5f201e46a7fc22a1761bc202a0"},"cell_type":"code","source":"## prepare data\n# this is not a valid approach if you want to build models from the data\n# drop some redundant columns\ndropping = ['merchant_category_id', 'subsector_id', 'category_1', 'city_id', 'state_id',\n            'category_2']\nfor var in dropping:\n    merchants = merchants.drop(var, axis = 1)\n\n# merge merchants with new_merchants\ndata = pd.merge(merchants, new_merchant, on = 'merchant_id')\n\n# merge data with train data\ndata = pd.merge(data, train, on = 'card_id')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"36128cbd46f9d3be75d66095b081b4f0ecd1c23c"},"cell_type":"markdown","source":"The variables in question here are *first_active_month* and *purchase_time*. Let's take a brief look at their number of unique values and the first five values:"},{"metadata":{"trusted":true,"_uuid":"34afa3fd5e1b53cc4e5e1d33aefae98f2b679c22"},"cell_type":"code","source":"print(len(data['first_active_month'].unique()))\ndata['first_active_month'][:5]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"12dbda17b3393c8669a1bef55cf2ae4acdd9669d"},"cell_type":"code","source":"print(len(data['purchase_date'].unique()))\ndata['purchase_date'][:5]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"560f7fa34308a3469027a009c03658e9d7c9c7e8"},"cell_type":"markdown","source":"This shows us that the *purchase_date* variable actually carrys more information than it's name makes it sound like. It's not only the date, but also the specific time. Let's recode this into two variables:"},{"metadata":{"trusted":true,"_uuid":"f5e2d0338e47a73637ee5a1a8b7574780f4d1196"},"cell_type":"code","source":"# recode purchase_date\ndata['purchase_time'] = data['purchase_date'].str.split(' ')\ndata['purchase_date'] = data['purchase_time'].str[0]\ndata['purchase_time'] = data['purchase_time'].str[1]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"094b86b147a35c97f970f8d245a9b6d9d335215a"},"cell_type":"markdown","source":"Now there are two different strategies to use these time variables in further models.\n\n1. Recode them to a linear variable where the lowest number is the day furthest in the past and the highest number the most recent day\n2. Recode them to ordered categorical variables\n\nLet's start with number 1:\n"},{"metadata":{"trusted":true,"_uuid":"6d4d245eb6043c98df9b195a7842da9085d9d0da"},"cell_type":"code","source":"def dates_to_numeric(series, kind = 'month'):\n    # get all unique values\n    months = list(series.unique())\n\n    # sort them\n    if kind == 'month':\n        date_string = \"%Y-%m\"\n    elif kind == 'day':\n        date_string = \"%Y-%m-%d\"\n\n    # make them a datetime object\n    dates = [datetime.datetime.strptime(ts, date_string) for ts in months]\n    dates.sort()\n    sorteddates = [datetime.datetime.strftime(ts, date_string) for ts in dates]\n\n    # generate all month stamps between first and last\n    start_date = sorteddates[0]\n    end_date = sorteddates[len(sorteddates) - 1]\n    \n    cur_date = start = datetime.datetime.strptime(start_date, date_string).date()\n    end = datetime.datetime.strptime(end_date, date_string).date()\n\n    months = []\n    while cur_date < end:\n        if kind == 'month':\n            months.append(str(cur_date)[:-3])\n            cur_date += relativedelta(months = 1)\n        elif kind == 'day':\n            months.append(str(cur_date))\n            cur_date += relativedelta(days = 1)\n    \n    # create dict that maps new values to each month\n    map_dict = {}\n    keys = range(0, len(months))\n    for i in keys:\n        map_dict[i] = months[i]\n\n    # reverse dict keys / values for mapping\n    new_dict = {v: k for k, v in map_dict.items()}\n    return new_dict\n\nnew_dict = dates_to_numeric(data['first_active_month'])\ndata['first_active_month_numeric'] = data['first_active_month'].apply(lambda x: new_dict.get(x))\n\nnew_dict = dates_to_numeric(data['purchase_date'], kind = 'day')\ndata['purchase_date_numeric'] = data['purchase_date'].apply(lambda x: new_dict.get(x))\n\n# recode timestamp to number of seconds passed since 00:00:00\ndef timestamp_to_seconds(time):\n    seconds = sum(x * int(t) for x, t in zip([3600, 60, 1], time.split(':'))) \n    return seconds\n\ndata['purchase_seconds'] = data['purchase_time'].apply(lambda x: timestamp_to_seconds(x))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f7ff1634ebc61b5234bdc0992ca8c283ba663975"},"cell_type":"code","source":"ax = sns.regplot(x = data['first_active_month_numeric'], y = data['target'], marker = \"+\",\n                 lowess = True, line_kws = {'color': 'black'})\nax.set_title('Relationship of the target variable and linear first active month')\nax.set_xlabel('first active month linear')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d75959c12217b210560d82ee0b8bff99cf13fe17"},"cell_type":"code","source":"ax = sns.regplot(x = data['purchase_date_numeric'], y = data['target'], marker = \"+\",\n                 lowess = True, line_kws = {'color': 'black'})\nax.set_title('Relationship of the target variable and linear purchase date')\nax.set_xlabel('purchase date linear')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9451a5a2d6baf23bef140f0dd47f2f5abdcda860"},"cell_type":"code","source":"#ax = sns.regplot(x = data['purchase_seconds'], y = data['target'], marker = \"+\",\n#                 lowess = True, line_kws = {'color': 'black'})\n#ax.set_xlabel('purchase seconds linear')\n# This takes incredibly long and is therefore commented out. It however looks very similar to the plot above","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a34a38de882f7c56f85197d5e218cad8e6ed7773"},"cell_type":"markdown","source":"These plot show no relationship between the metric versions of the three date variables and the target variable. By using the lowess smoother instead of a linear regression we also made sure there is no nonlinear relationship between the two variables.\n\nNow that this is out of the way, let's dive into the more reasonable data transformations. For the *purchase_date* variable we will create a new variable that contains the name of the corresponding weekday (e.g. monday, tuesday ..). For the *purchase_time* variable we will create a new categorical variable with 4 categories: Morning, Afternoon, Evening and Night. These correspond to:\n\n**Morning**: 5am to 12pm (05:00 to 11:59)\n\n**Afternoon**: 12pm to 5pm (12:00 to 16:59)\n\n**Evening**: 5pm to 9pm (17:00 to 20:59)\n\n**Night**: 9pm to 5am (21:00 to 04:59)\n\nWe will also create two variables containing the corresponding month (e.g. January, February ..) for *purchase_date* and *first_active_month*. For the latter we will create a *first_active_year* variable as well. **Let's start!\n\n**EDIT:** I forgot that the time of the month itself (beginning, end etc.) may have an effect on the target variable as well. This will be explored too in this second version. We will look both at a categorical version and a numeric version just using the day."},{"metadata":{"trusted":true,"_uuid":"23fa84aa108cacd59f14826894e0b2b0003eb500"},"cell_type":"code","source":"def get_weekday(date_string):\n    date = datetime.datetime.strptime(date_string, '%Y-%m-%d')\n    return calendar.day_name[date.weekday()]\n\n# get weekday for date variable\ndata['purchase_weekday'] = data['purchase_date'].apply(lambda x: get_weekday(x))\n\n# for plotting recode to ordered categorical\nday_labels = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']\ndata['purchase_weekday'] = pd.Categorical(data['purchase_weekday'], categories = day_labels, \n                                          ordered = True)\n\ndef get_month(date_string, kind = 'month'):\n    if kind == 'month':\n        date = datetime.datetime.strptime(date_string, '%Y-%m')\n    elif kind == 'day':\n        date = datetime.datetime.strptime(date_string, '%Y-%m-%d')\n    return date.strftime(\"%B\")\n\ndata['purchase_month'] = data['purchase_date'].apply(lambda x: get_month(x, kind = 'day'))\ndata['first_active_month2'] = data['first_active_month'].apply(lambda x: get_month(x))\ndata['first_active_year'] = data['first_active_month'].str[:4]\n\nmonth_labels = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August',\n                'September', 'October', 'November', 'December']\ndata['purchase_month'] = pd.Categorical(data['purchase_month'], categories = month_labels, \n                                          ordered = True)\ndata['first_active_month2'] = pd.Categorical(data['first_active_month2'], categories = month_labels, \n                                          ordered = True)\n\nyear_labels = ['2011', '2012', '2013', '2014', '2015', '2016', '2017', '2018']\ndata['first_active_year'] = pd.Categorical(data['first_active_year'], categories = year_labels, \n                                          ordered = True)\n\n# get time of the day\ndata['temp'] = data['purchase_time'].str.split(':')\n\ndef get_session(time_list):\n    time_list[0] = int(time_list[0])\n    if time_list[0] > 4 and time_list[0] < 12:\n        return 'Morning'\n    elif time_list[0] >= 12 and time_list[0] < 17:\n        return 'Afternoon'\n    elif time_list[0] >= 17 and time_list[0] < 21:\n        return 'Evening'\n    else:\n        return 'Night'\n    \ndata['purchase_session'] = data['temp'].apply(lambda x: get_session(x))\n\nsession_labels = ['Morning', 'Afternoon', 'Evening', 'Night']\ndata['purchase_session'] = pd.Categorical(data['purchase_session'], categories = session_labels, \n                                          ordered = True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"257d04b785395e3abd85dc8998ee21cbc46a5789"},"cell_type":"code","source":"## time of month\n# as categorical variable, thressholds are arbitrary and could be different\ndef get_time_of_month_cat(date):\n    date_temp = date.split('-')\n    if int(date_temp[2]) < 10:\n        time_of_month = 'Beginning'\n    elif int(date_temp[2]) >= 10 and int(date_temp[2]) < 20:\n        time_of_month = 'Middle'\n    else:\n        time_of_month = 'End'\n    return time_of_month\n\ndata['time_of_month_cat'] = data['purchase_date'].apply(lambda x: get_time_of_month_cat(x))\n\ntof_labels = ['Beginning', 'Middle', 'End']\ndata['time_of_month_cat'] = pd.Categorical(data['time_of_month_cat'], categories = tof_labels, \n                                           ordered = True)\n\ndata['time_of_month_num'] = data['purchase_date'].str[8:].astype(int)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b923574d0fc6c23f8136a949f9b1f335abfffc9f"},"cell_type":"code","source":"ax = sns.lineplot(x = \"purchase_weekday\", y = \"target\", \n                  markers = True, dashes = False, data = data)\nplt.xticks(rotation = 45)\nax.set_title('Target Variable Changes over Purchase Week')\nax.set_xlabel('Purchase Weekday')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"bb4849f6727c696b72f36de93d38876c11204618"},"cell_type":"markdown","source":"There is a pattern here. The target variable follows a non-linear curve over the weekdays. The differences may be small, but they are statistically significant."},{"metadata":{"trusted":true,"_uuid":"b3eef66eaed81c888cf8035c3f5dd2453a93d9e1"},"cell_type":"code","source":"ax = sns.lineplot(x = \"purchase_month\", y = \"target\", \n                  markers = True, dashes = False, data = data)\nplt.xticks(rotation = 45)\nax.set_title('Target Variable Changes over Purchase Month')\nax.set_xlabel('Purchase Month')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c33da9cebdb3fca1a5358d8e1d8e0c975ccaea42"},"cell_type":"markdown","source":"Now this is more like it! There are pretty big differences in the mean of the target variable between each month of purchase. In January the mean is close to - 2 while in April it's at - 0,25. Adding the purchase month as dummy variables to your model looks promising. "},{"metadata":{"trusted":true,"_uuid":"abffddeff0a81cfad08d2a0a04a21b0b75c7ffe9"},"cell_type":"code","source":"ax = sns.lineplot(x = \"first_active_month2\", y = \"target\", \n                  markers = True, dashes = False, data = data)\nplt.xticks(rotation = 45)\nax.set_title('Target Variable Changes over the First Active Month')\nax.set_xlabel('First Active Month')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4749d5f563c5064163a1764c0b9233df485a0126"},"cell_type":"markdown","source":"There are some clear variations in this plot as well, which follow almost a step-wise linear pattern. The differences might not be nearly as strong as in the purchase month plot, but this still might help improve your model."},{"metadata":{"trusted":true,"_uuid":"e4406c53e78d48159c32b938715c0c1b28aee28c"},"cell_type":"code","source":"ax = sns.lineplot(x = \"first_active_year\", y = \"target\", \n                  markers = True, dashes = False, data = data)\nplt.xticks(rotation = 45)\nax.set_title('Target Variable Changes over the First Active Year')\nax.set_xlabel('First Active Year')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e2441dac46ee78d4f7f58b53fa8693ffb6e5116d"},"cell_type":"markdown","source":"The first active year show some big differences! The target variable increases with each year. Definitly an interesting pattern."},{"metadata":{"trusted":true,"_uuid":"a27a2cf5cfacd856574df43244c255bfcd9d90a5"},"cell_type":"code","source":"ax = sns.lineplot(x = \"purchase_session\", y = \"target\", \n                  markers = True, dashes = False, data = data)\nplt.xticks(rotation = 45)\nax.set_title('Target Variable Changes over Purchase Time of Day')\nax.set_xlabel('Purchase Time of Day')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"915fb69146be28c4b5bd081c9e71697e43c34790"},"cell_type":"markdown","source":"Differences over the day are rather small, but follow a clear pattern as well. Let's see if that pattern holds up if we look at it by different week days:"},{"metadata":{"trusted":true,"_uuid":"76e3304880c111999d77c29a0fb10291ed765245"},"cell_type":"code","source":"ax = sns.catplot(x = 'purchase_weekday', y = 'target', hue = 'purchase_session', data = data,\n                kind = 'bar', height = 5, aspect = 2)\nax.despine(left = True)\nplt.xticks(rotation = 45)\nax.set_ylabels(\"target\")\nax.set_xlabels('Weekday')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"386155d645628153edd2ba3ad266cbad61dfe949"},"cell_type":"markdown","source":"It seems like it does. The pattern seems to be nearly the same on all weekdays. There are some differences between saturday and tuesday though.\n\nAll in all there are some interesting relationships in the date variables of this data set. I hope this little eploratory insights helped some of you in some way. I am a beginner so I am very thankful for any advice / comments you have to offer."},{"metadata":{"_uuid":"124b86e6a0061588889548a7392f6881c5081c13"},"cell_type":"markdown","source":"**NEW** Following are the new parts of the analysis:"},{"metadata":{"trusted":true,"_uuid":"1b3692793c0ceb312103c576187aa3f545781764"},"cell_type":"code","source":"ax = sns.regplot(x = data['time_of_month_num'], y = data['target'], marker = \"+\",\n                 lowess = True, line_kws = {'color': 'black'})\nax.set_title('Relationship of the target variable and purchase time of month')\nax.set_xlabel('time of purchase inside month')\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ef33cff8403a4e417a334e2fcf3e982b446d9534"},"cell_type":"markdown","source":"Using just the day itself doesn't seem very useful. No direct relationship can be observed. Now let's look at the categorical version:"},{"metadata":{"trusted":true,"_uuid":"c884cc092b78a5c6bd08e7c68e7d8eb6a543d994"},"cell_type":"code","source":"ax = sns.lineplot(x = \"time_of_month_cat\", y = \"target\", \n                  markers = True, dashes = False, data = data)\nplt.xticks(rotation = 45)\nax.set_title('Target Variable Changes over Purchase Time of Month')\nax.set_xlabel('Purchase Time of Month')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3825113faf28650fed85d18ddbd15e85c57181c6"},"cell_type":"markdown","source":"The pattern is really small with the biggest deviation being between -0.52 and -0.66, but it does exist. I am not sure if this will be particularly useful but it's definitly worth a try. Let's also look if this pattern is the same in each month:"},{"metadata":{"trusted":true,"_uuid":"dc11e167c5249beeaa83a1f52a9acf67de60a1a5"},"cell_type":"code","source":"ax = sns.catplot(x = 'purchase_month', y = 'target', hue = 'time_of_month_cat', data = data,\n                kind = 'bar', height = 5, aspect = 2)\nax.despine(left = True)\nplt.xticks(rotation = 45)\nax.set_ylabels(\"Target\")\nax.set_xlabels('Purchase Time of Month')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"769d309a4b4b485bef2dba6cbf9da0f760dd2878"},"cell_type":"markdown","source":"The pattern obviously isn't really stable at all. The only consistent finding is, that the end of the month seemingly always has a higher score on the target variable then other parts. If only the very end of the month has an effect on the target variable, we might still be able to utilize this. Let's try by creating a dummy variable that only looks at the very last days of the month:"},{"metadata":{"trusted":true,"_uuid":"a627043de857cbf0dfd10ec618c58b4ffeb5f157"},"cell_type":"code","source":"def get_end_of_month(date):\n    date_temp = date.split('-')\n    if int(date_temp[2]) >= 25:\n        end_of_month = 'Yes'\n    else:\n        end_of_month = 'No'\n    return end_of_month\n\ndata['end_of_month'] = data['purchase_date'].apply(lambda x: get_end_of_month(x))\n\nax = sns.barplot(x = 'end_of_month', y = 'target', data = data)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ff151a20c022ab539ec6b1242fe58a031428e8e3"},"cell_type":"markdown","source":"This shows bigger differences than before. Only trying it out will show if this is usefull in prediction or not though. Good luck!"}],"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}