{"cells": [{"execution_count": null, "metadata": {"_uuid": "11b6dfdb573870069a77d896cae9eeb93d42e35d", "_cell_guid": "fbd3398a-0408-4d07-94d1-64411498f6f2"}, "outputs": [], "source": ["from subprocess import check_output\n", "import numpy as np # linear algebra\n", "import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n", "print(check_output([\"ls\", \"../input\"]).decode(\"utf8\"))\n", "import re\n", "from datetime import datetime as dt\n", "from datetime import timedelta# Any results you write to the current directory are saved as output."], "cell_type": "code"}, {"execution_count": null, "metadata": {"collapsed": true, "_uuid": "d9bff1888f89e10eba97dafb0bbb4674aa8d29e3", "_cell_guid": "1ebc9c58-d480-45a5-9515-9186a3a4849a"}, "outputs": [], "source": ["holiday = pd.read_csv('../input/holidays_events.csv')\n", "stores = pd.read_csv('../input/stores.csv')"], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "66b1e47976d20f37cd0f32b9d51154f89936c30f", "_cell_guid": "f36c2f14-8ada-42a3-b23e-334c82c9c91f"}, "outputs": [], "source": ["# converting date into datetime format\n", "holiday['date'] = pd.to_datetime(holiday['date'])\n", "print(holiday['description'].unique())"], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "9f71bab0e8948f5bd609689f7ed2f9f999a65d37", "_cell_guid": "5b82be9b-b657-453f-b96a-7cd0023a6087"}, "outputs": [], "source": ["# all bridge-type has puente (puente actually mean bridge), will remove it \n", "# all work day type has recupero (recupero means recovery), will remove it\n", "# almost all Transfer have traslado (it actually means transfer), will remove it\n", "# as all of these information are already contained in type\n", "hol_groups = holiday.groupby('type')\n", "for c,group in hol_groups:\n", "    print(c,group['description'].unique())"], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "3202fc59f7a1670972af49d38f9c6fe2127fe816", "_cell_guid": "f66bcecc-e913-4384-afc1-1f93ead3ed45"}, "outputs": [], "source": ["# for Regional and Local holidays, \n", "# name locale_name is part of description as well (will remove that)\n", "# as this info is already contained in locale_name\n", "hol_groups2 = holiday.groupby(['locale','locale_name'])\n", "for c,group in hol_groups2:\n", "    print(c,group['description'].unique())"], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "a128d11e3a87276a40239c74c74693278045db74", "_cell_guid": "2d44bf76-85ae-4ad8-ba15-63a312c8fdf4"}, "outputs": [], "source": ["# number just mean consecutive events which is already given by there dates, \n", "# hence will remove them from description\n", "navidadl = ['Navidad-4','Navidad-3','Navidad-2','Navidad-1','Navidad','Navidad+1']\n", "print(holiday[holiday['description'].isin(navidadl)][['date','description']])\n", "terr_hol = ['Terremoto Manabi','Terremoto Manabi+1','Terremoto Manabi+2',\n", "            'Terremoto Manabi+3','Terremoto Manabi+4','Terremoto Manabi+5',\n", "            'Terremoto Manabi+6','Terremoto Manabi+7','Terremoto Manabi+8',\n", "            'Terremoto Manabi+9','Terremoto Manabi+10','Terremoto Manabi+11',\n", "            'Terremoto Manabi+12','Terremoto Manabi+13','Terremoto Manabi+14',\n", "            'Terremoto Manabi+15','Terremoto Manabi+16','Terremoto Manabi+17',\n", "            'Terremoto Manabi+18','Terremoto Manabi+19','Terremoto Manabi+20',\n", "            'Terremoto Manabi+21','Terremoto Manabi+22','Terremoto Manabi+23',\n", "            'Terremoto Manabi+24','Terremoto Manabi+25','Terremoto Manabi+26',\n", "            'Terremoto Manabi+27','Terremoto Manabi+28','Terremoto Manabi+29',\n", "            'Terremoto Manabi+30']\n", "print(holiday[holiday['description'].isin(terr_hol)][['date','description']])"], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "9af818c39ed6e77c56dc64f08c58afb18dfd8a92", "_cell_guid": "807627f3-a479-4e13-b4c5-167a23cabee5"}, "outputs": [], "source": ["# de and del are just stop words, will remove them too\n", "# seriously reduces dimensionality of description variable\n", "holiday['description'] = \\\n", "holiday['description'].str.lower().replace(to_replace='[^a-z ]', value='', regex=True)\n", "holiday['description'] = holiday.apply(lambda x: x['description'].replace(x['locale_name'].lower(),''),axis=1)\n", "holiday['description'] = holiday['description']. \\\n", "replace(to_replace='traslado ',value='',regex=True).replace(to_replace='puente ',value='',regex=True). \\\n", "replace(to_replace='del ',value='',regex=True).replace(to_replace='de ',value='',regex=True). \\\n", "replace(to_replace='mundial futbol brasil',value='mfb',regex=True). \\\n", "replace(to_replace='recupero ',value='',regex=True).replace(to_replace='santo domingo',value='',regex=True)\n", "holiday['description'] = holiday['description'].apply(lambda x: x.strip())\n", "print(holiday['description'].unique())"], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "abfdca021517cfa57c987ec7ba6f2a4894b7ffd5", "_cell_guid": "20722bfb-d6f0-42d3-a6a3-76d1b0ea4e00"}, "outputs": [], "source": ["# now some holidays are city level and will impact only that city store (locale == 'Local')\n", "# and some are state level and will only impact store in that state (locale == 'Regional')\n", "# and national onces will impact all of stores in Ecuador\n", "# and hence city level and state level will need us to merge store and date some how\n", "## Creating date only dataframe\n", "start_dt = dt(year=2013,month=1,day=1)\n", "dt_only = pd.DataFrame([start_dt + timedelta(days=i) for i in range(1704)],columns=['date'])\n", "print(dt_only.max())\n", "print(dt_only.min())"], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "f1e8156b12155ca2c3c49555f2df682e44a5533a", "_cell_guid": "e22e2bf8-af38-438a-bd29-81d6cae7cdd2"}, "outputs": [], "source": ["# taking a cross product of store and date\n", "dt_only['dummy'] = 1\n", "stores['dummy'] = 1\n", "dt_store = pd.merge(dt_only,stores,on=['dummy']).drop('dummy',axis=1)\n", "print(dt_store.head(10))"], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "a984aece3214a294bd3fd9d9a97d9e09774fd828", "_cell_guid": "6cc5da11-028e-48aa-9398-10fc96670615"}, "outputs": [], "source": ["# seperating out National, city and state level holidays\n", "national_h = holiday[holiday['locale'] =='National'].drop(['locale','locale_name'],axis=1)\n", "national_h.columns = ['date','ntype','ndescription','ntransfered']\n", "city_h = holiday[holiday['locale'] == 'Local'].drop('locale',axis=1)\n", "city_h.columns = ['date','ctype','city','cdescription','ctransfered']\n", "state_h=holiday[holiday['locale'] == 'Regional'].drop('locale',axis=1)\n", "state_h.columns = ['date','stype','state','sdescription','stransfered']\n", "print(national_h.head())\n", "print(city_h.head())\n", "print(state_h.head())"], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "6b4445ffe5de777816bbfc24a4b501eab612d098", "_cell_guid": "259f76f2-2b6b-4501-a01f-5282a87fc339"}, "outputs": [], "source": ["# merging with date store dataframe  \n", "dt_store_nat = pd.merge(dt_store,national_h,on='date',how='left')\n", "print(dt_store_nat.shape)\n", "dt_store_nat_ct = pd.merge(dt_store_nat,city_h,on=['date','city'],how='left')\n", "print(dt_store_nat_ct.shape)\n", "dt_store_nat_ct_st = pd.merge(dt_store_nat_ct,state_h,on=['date','state'],how='left')\n", "print(dt_store_nat_ct_st.shape)\n", "print(dt_store_nat_ct_st.head())"], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "d53a6f667aa336731e360f12d89fde260ec1ba40", "_cell_guid": "d46b8101-8249-45a4-8c74-7d6bc830dc24"}, "outputs": [], "source": ["# You can use holiday features now after on hot encoding or whatever you would like to do\n", "# you can choose to keep only one of (national,city,state) features by using some hirachial ordering\n", "# now you can use this dataframe to merge with main data set on ['date','store_nbr']\n", "# Note: the increase in number of records on merging city_h\n", "# happening because of transfer and Additional on falling on same date\n", "# My suggestion is to remove record with ctype = 'Transfer' and city = 'Guayaquil'\n", "citygroups = city_h.groupby(['date','city'])\n", "for c,group in citygroups:\n", "    if group.shape[0] > 1:\n", "        print(group)"], "cell_type": "code"}, {"execution_count": null, "metadata": {"collapsed": true, "_uuid": "ad0b5079b9a3feb0b1a9ab6d0f7439a780577da3", "_cell_guid": "6424483a-7379-4505-b492-546f9983d538"}, "outputs": [], "source": [], "cell_type": "code"}], "metadata": {"kernelspec": {"name": "python3", "display_name": "Python 3", "language": "python"}, "language_info": {"name": "python", "mimetype": "text/x-python", "file_extension": ".py", "codemirror_mode": {"name": "ipython", "version": 3}, "pygments_lexer": "ipython3", "version": "3.6.3", "nbconvert_exporter": "python"}}, "nbformat": 4, "nbformat_minor": 1}