{"metadata": {"kernelspec": {"name": "python3", "display_name": "Python 3", "language": "python"}, "language_info": {"name": "python", "nbconvert_exporter": "python", "mimetype": "text/x-python", "codemirror_mode": {"name": "ipython", "version": 3}, "version": "3.6.3", "pygments_lexer": "ipython3", "file_extension": ".py"}}, "cells": [{"metadata": {"_uuid": "c5f0f106e7a84978382e6865846d844e5e0980e5", "_cell_guid": "7c0d4ac3-c352-4883-a979-1105c1e8a30f"}, "source": ["# Data Engineering\n", "First, we'll load up our favorite packages (numpy, pandas, matplotlib, seaborn, sklearn). I like looking at plots, so we'll use `%matplotlib inline` to view them in our notebook."], "cell_type": "markdown"}, {"metadata": {"collapsed": true, "_uuid": "43bbfc9158949ec5421b764c7c67f07450d2b69c", "_cell_guid": "d561c140-ebb5-470e-b9d1-584e49bd8111"}, "source": ["%matplotlib inline\n", "import numpy as np\n", "import pandas as pd\n", "import matplotlib.pyplot as plt\n", "import seaborn as sns\n", "from sklearn.externals import joblib\n", "from sklearn.ensemble import RandomForestRegressor\n", "\n", "sns.set_style('whitegrid')\n"], "execution_count": null, "cell_type": "code", "outputs": []}, {"metadata": {"_uuid": "68cadf0c7b1b571bc6aaf7ae3b54ea88249f3d39", "_cell_guid": "a5222d2d-f78f-452c-a691-32032ad9905d"}, "source": ["Here, we'll load up all of our data using pandas. You can see that we've got train and test sets, as well as some other supplementary data regarding all transactions, oilprice (the country economy depends a lot on oil), holiday+events (Ecuadorian), item and store information."], "cell_type": "markdown"}, {"metadata": {"collapsed": true, "_uuid": "6c3e9b7aa4dd6f4b2163a3e6f4cf11e1b27fe752", "_cell_guid": "7c4f89af-a751-4919-8dc5-ea2351c95c6d"}, "source": ["train_db = pd.read_csv('../input/train.csv', parse_dates=['date'])\n", "test_db = pd.read_csv('../input/test.csv', parse_dates=['date'])\n", "transactions_db = pd.read_csv('../input/transactions.csv', parse_dates=['date'])\n", "oilprice_db = pd.read_csv('../input/oil.csv', parse_dates=['date'])\n", "holiday_db = pd.read_csv('../input/holidays_events.csv', parse_dates=['date'])\n", "items_db = pd.read_csv('../input/items.csv')\n", "stores_db = pd.read_csv('../input/stores.csv')"], "execution_count": null, "cell_type": "code", "outputs": []}, {"metadata": {"_uuid": "efc9e98aeaf3a5769da07acaf0e505fd58f8c23c", "_cell_guid": "a9dec0bd-6d82-4a54-b569-80eb3cda7cc2"}, "source": ["# Check if the train and test data has any null values\n", "\n", "Here we're just going to check which columns have null values. We'll check out what they are and figure out how to deal with them later. They might be as easy as throwing them out or perhaps interpolating to fill in new values."], "cell_type": "markdown"}, {"metadata": {"collapsed": true, "_uuid": "9b9264a177f30ef8b46046dd474ba5b6ab96b67a", "_cell_guid": "4cc4cd8b-33be-4364-8903-8882d480a218"}, "source": ["print(\"Training Data\")\n", "for col in train_db.columns:\n", "    print (col, train_db[col].isnull().any())\n", "print (\"=\"*70)\n", "print(\"Test Data\")\n", "for col in test_db.columns:\n", "    print (col, test_db[col].isnull().any())\n"], "execution_count": null, "cell_type": "code", "outputs": []}, {"metadata": {"_uuid": "dd7091b2d2b3f0c3c943a4464ccf554c3c3d2018", "_cell_guid": "91c3bc2c-f089-4178-8176-7c764a10133f"}, "source": ["Seems like Training data has missing  ***onpromotion*** variables. We'll later replace missing values with 2, True = 1 and False = 0."], "cell_type": "markdown"}, {"metadata": {"_uuid": "300f4c7fba23b06405eb82135ce6f4310576b7cc", "_cell_guid": "beb222f7-38fd-4fd2-b5dd-5c0f82ae2594"}, "source": ["# Check if supplementary data has any null values\n", "\n", "Supplementary data = anything not train or test database"], "cell_type": "markdown"}, {"metadata": {"collapsed": true, "_uuid": "d5c0c993e11fc4048fab607edfd241d11f4dc895", "_cell_guid": "c98228ba-628f-490a-a104-84b19a5bfaa0"}, "source": ["supData = [transactions_db, oilprice_db, holiday_db, items_db, stores_db]\n", "for sup in supData:\n", "    print (\"=\"*70)\n", "    for col in sup.columns:\n", "        print (col, sup[col].isnull().any())\n", "        \n", "del supData, sup # deleting them to make sure I have enough memory"], "execution_count": null, "cell_type": "code", "outputs": []}, {"metadata": {"_uuid": "158c7a94b89221b3fabb933ab01a6a6ad999d1a2", "_cell_guid": "7e6aa5f1-e039-43d5-a56b-74d51f72a149"}, "source": ["**Looks like we have some null values in oil prices!**"], "cell_type": "markdown"}, {"metadata": {"_uuid": "9a65f8d8f6d4ce23280684e58e29d811cd054096", "_cell_guid": "faea7422-ccd6-4185-94c3-b08c101abe83"}, "source": ["## Clean data + add 'dow' and 'doy'\n", "Here, we're going to do some data cleaning. First, we're getting rid of all unit_sales that have negative values (I think they're returned purchases) because the problem prompt says we should ignore these negative sales. \n", "\n", "Secondly, I'm adding new features called `'dow'` and `'doy'` becuase I think day of the week and day of the year matters a lot in terms of when purchases are made. For example, I imagine more people go to grocery stores on the weekends rather than weekdays, which is why I'm adding a `'dow'` column. Furthermore, more people purchase items as the date gets closer to the holiday seasons, which is why I'm adding the `'doy'` column."], "cell_type": "markdown"}, {"metadata": {"collapsed": true, "_uuid": "2b07679c154775b2ddd1ffbb772580be8d413e85", "_cell_guid": "66153bc9-5fb6-40cb-8fa7-c2c899e260a5"}, "source": ["# Data Cleaning\n", "train_db.loc[(train_db.unit_sales<0),'unit_sales'] = 0 # Cleaning all negative values to be 0\n", "\n", "# Add 'dow' and 'doy'\n", "train_db['dow'] = train_db['date'].dt.dayofweek # adding day of week as a feature\n", "train_db['doy'] = train_db['date'].dt.dayofyear # adding day of year as a feature\n", "\n", "test_db['dow'] = test_db['date'].dt.dayofweek # adding day of week as a feature\n", "test_db['doy'] = test_db['date'].dt.dayofyear # adding day of year as a feature"], "execution_count": null, "cell_type": "code", "outputs": []}, {"metadata": {"_uuid": "245fb71392b5adf09e4f4a980d8cae39dfde825c", "_cell_guid": "9f20fb77-9e91-47ed-a42e-73b468b7978b"}, "source": ["## Cleaning onpromotion column\n", "\n", "Now, we're going to clean up the onpromotion column up by filling NaN, true, and false values with 2,1,0, respectively. I didn't want to give NaN 0 values because False will be 0. So, I just gave it a 2 entirely on its own class."], "cell_type": "markdown"}, {"metadata": {"collapsed": true, "_uuid": "fec26faf6089164112f10215c97b9f8935e916a8", "_cell_guid": "6e404aba-03cb-4711-a2dd-9d2e511821db"}, "source": ["train_db.loc[:,'onpromotion'].fillna(2, inplace=True) # Replace NaNs with 2\n", "train_db.loc[:,'onpromotion'].replace(True, 1, inplace=True) # Replace Trues with 1\n", "train_db.loc[:,'onpromotion'].replace(False, 0, inplace=True) # Replace Falses with 0\n", "\n", "# Do the same for test set\n", "test_db.loc[:,'onpromotion'].fillna(2, inplace=True) # Replace NaNs with 2\n", "test_db.loc[:,'onpromotion'].replace(True, 1, inplace=True) # Replace Trues with 1\n", "test_db.loc[:,'onpromotion'].replace(False, 0, inplace=True) # Replace Falses with 0"], "execution_count": null, "cell_type": "code", "outputs": []}, {"metadata": {"_uuid": "a57bcbd9bf274da21987ac4df185ebbdd094e1a4", "_cell_guid": "0c7a70ac-6d5c-491a-a24d-4bba9d45b862"}, "source": ["## Finding 'dow' unit_sales and weekly averages"], "cell_type": "markdown"}, {"metadata": {"collapsed": true, "_uuid": "988ebfeafba991ea604b2edb8d8ca2de2f89f521", "_cell_guid": "a14cb1de-bfb3-4708-b8ed-837dc2d68fb8"}, "source": ["# Grouping columns unit sales by\n", "# item_nbr, store_nbr, dow (day of week)\n", "# And storing means as dataframe\n", "ma_dw = train_db[['item_nbr','store_nbr','dow','unit_sales']]\\\n", "             .groupby(['item_nbr','store_nbr','dow'])['unit_sales']\\\n", "             .mean().to_frame('madw')\n", "ma_dw.reset_index(inplace=True)\n", "\n", "# Storing weekly averages\n", "ma_wk = ma_dw[['item_nbr', 'store_nbr','madw']]\\\n", "        .groupby(['item_nbr', 'store_nbr'])['madw']\\\n", "        .mean().to_frame('mawk')\n", "ma_wk.reset_index(inplace=True)"], "execution_count": null, "cell_type": "code", "outputs": []}, {"metadata": {"_uuid": "cdae2bb4e1e954ac14f48c75ee5581449bd5f064", "_cell_guid": "d2e143c0-291f-479d-a086-6e42758440c5"}, "source": ["## Interpolating to fill in NaNs in the oilprice dataset"], "cell_type": "markdown"}, {"metadata": {"collapsed": true, "_uuid": "d5e6bbd8b6912ba379b07728cb741d7eab079026", "_cell_guid": "296ae08f-9d04-4aa0-97cd-fa8506d26e2c"}, "source": ["# Oilprice dataset has many missing date values\n", "# I'm just going to create a new database with every day registered\n", "# Fill in the values by interpolating\n", "\n", "index = pd.date_range(start='2013-01-01', end='2017-08-31')\n", "new_oilprice_db = pd.DataFrame(index=index, columns=['date'] )\n", "new_oilprice_db['date'] = index\n", "new_oilprice_db.reset_index(inplace=True)\n", "del new_oilprice_db['index']\n", "\n", "# Linearly interpolating and manually filling in 3 points for linear interpolation\n", "td = oilprice_db.date.diff() # time differece vector\n", "interp = oilprice_db.dcoilwtico.shift(1) + ((oilprice_db.dcoilwtico.shift(-1) - oilprice_db.dcoilwtico.shift(1)))\\\n", "         * td / (td.shift(-1) + td)\n", "\n", "oilprice_db['dcoilwtico'] = oilprice_db['dcoilwtico'].fillna(interp)\n", "\n", "# Manually added the very first point \n", "oilprice_db['dcoilwtico'][0] = 93.14\n", "oilprice_db['dcoilwtico'][1174] = 46.57\n", "oilprice_db['dcoilwtico'][1175] = 46.75\n", "\n", "# Merge the new oil price dataframe with the old dataframe\n", "new_oilprice_db = pd.merge(new_oilprice_db, oilprice_db, on='date', how='left')\n", "\n", "interp = new_oilprice_db.dcoilwtico.shift(2) +\n", "         ((new_oilprice_db.dcoilwtico.shift(-2) - new_oilprice_db.dcoilwtico.shift(2))) \\\n", "          / 2\n", "new_oilprice_db['dcoilwtico'] = new_oilprice_db['dcoilwtico'].fillna(interp)\n", "\n", "# Repeating interpolating twice because the first time only filled 1 of the back-to-back\n", "# NaN values. If I repeate the interpolation twice, it should fill in all values\n", "\n", "interp = new_oilprice_db.dcoilwtico.shift(1) +\n", "         ((new_oilprice_db.dcoilwtico.shift(-1) - new_oilprice_db.dcoilwtico.shift(1))) \\\n", "          / 2\n", "new_oilprice_db['dcoilwtico'] = new_oilprice_db['dcoilwtico'].fillna(interp)"], "execution_count": null, "cell_type": "code", "outputs": []}, {"metadata": {"_uuid": "262b80b9c8f306bc8f8c2336f686319a09783a77", "_cell_guid": "32f31a0c-ea2b-4f4a-bd28-e9a62014e273"}, "source": ["## Check if we have any null values in oilprice_db"], "cell_type": "markdown"}, {"metadata": {"collapsed": true, "_uuid": "2a7f6affc5967f615e34a20c5806b9c846728f94", "_cell_guid": "16e074f1-8b9c-47e7-951d-274c0b801f32"}, "source": ["new_oilprice_db[new_oilprice_db['dcoilwtico'].isnull()]"], "execution_count": null, "cell_type": "code", "outputs": []}, {"metadata": {"_uuid": "0efc4303729e57ec62c30b30fd8aa0ba432c4c77", "_cell_guid": "67ba17d8-8235-4f42-9573-112d3b2d3f71"}, "source": ["## Now we'll merge all the supplementary data with train"], "cell_type": "markdown"}, {"metadata": {"collapsed": true, "_uuid": "2e442708a46c486087ce886a0a796eb8ced84b3c", "_cell_guid": "54ecc3aa-6c10-4e45-b1ad-af1b1cff8776"}, "source": ["train = pd.merge(train_db, stores_db, on='store_nbr', how='left')\n", "train = pd.merge(train, ma_dw, on=['item_nbr', 'store_nbr', 'dow'], how='left')\n", "train = pd.merge(train, ma_wk, on=['item_nbr', 'store_nbr'], how='left')\n", "train = pd.merge(train, items_db, on='item_nbr', how='left')\n", "train = pd.merge(train, oilprice_db, on='date', how='left')\n", "train = pd.merge(train, holiday_db, on='date', how='left')\n", "\n", "train.head()"], "execution_count": null, "cell_type": "code", "outputs": []}, {"metadata": {"_uuid": "8600c55ae4e83340f1a5fc73813aeb0aa075bc4d", "_cell_guid": "9ea7f859-d7a3-4a5f-b8f2-2ed6e42b333d"}, "source": ["## Check for null values"], "cell_type": "markdown"}, {"metadata": {"collapsed": true, "_uuid": "17995bf6c8d12a5e81e4dbf78bc8630e1a8777cc", "_cell_guid": "5833dd9e-d70e-4110-bc43-2f4147035c67"}, "source": ["for i in train.columns:\n", "    print i, train[i].isnull().any()"], "execution_count": null, "cell_type": "code", "outputs": []}, {"metadata": {"_uuid": "ba7bc67f783250a1b2e4220a80ccb04b590b14f4", "_cell_guid": "a7d34406-f423-4f21-a495-ab1ce5a5b352"}, "source": ["## Merge supplementary with test data"], "cell_type": "markdown"}, {"metadata": {"collapsed": true, "_uuid": "1a9c7277e70993d4d4732e4d166572116647e079", "_cell_guid": "02a35d80-1238-4af2-a543-18cd109777d5"}, "source": ["test = pd.merge(test_db, stores_db, on='store_nbr', how='left')\n", "test = pd.merge(test, ma_dw, on=['item_nbr', 'store_nbr', 'dow'], how='left')\n", "test = pd.merge(test, ma_wk, on=['item_nbr', 'store_nbr'], how='left')\n", "test = pd.merge(test, items_db, on='item_nbr', how='left')\n", "test = pd.merge(test, holiday_db, on='date', how='left') # we have 2017 holiday info\n", "test = pd.merge(test, oilprice_db, on='date', how='left')\n", "\n", "test.head()"], "execution_count": null, "cell_type": "code", "outputs": []}, {"metadata": {"_uuid": "a36e6ebafd95dc76085182b605c6ceb25c3597cf", "_cell_guid": "49880d6a-f5ed-4d52-8036-2e70c3750645"}, "source": ["## Deleting old data to free up memory space"], "cell_type": "markdown"}, {"metadata": {"collapsed": true, "_uuid": "fe52dea25925bea184a81d7602f2e2e42d865830", "_cell_guid": "f7f12104-d34b-468c-ab64-25df89a858bd"}, "source": ["del ma_dw, ma_wk, holiday_db, oilprice_db, stores_db, test_db, items_db"], "execution_count": null, "cell_type": "code", "outputs": []}, {"metadata": {"_uuid": "5492802c0ecf8570da8990871213dfe09dd5b3f6", "_cell_guid": "7e54b7d4-d253-40f6-ade2-0fd068b5d5f3"}, "source": ["## I'm going to divide up date into day, month and year and add them as features\n", "\n", "I'm doing this because I think year, month and day separately give more information about unit sales than them combined, especially year and month. Based on some prior analysis, the number of transactions was growing yearly (pretty linear), and the number of transactions increase dramatically towards holiday seasons (months:11 and 12)."], "cell_type": "markdown"}, {"metadata": {"collapsed": true, "_uuid": "96000b98c1f45f1c78ec74412625a7b3c00fe061", "_cell_guid": "5b6ff3cb-0378-4bda-b675-990a22c5f515"}, "source": ["train['month'] = train['date'].dt.month\n", "train['year'] = train['date'].dt.year\n", "train['day'] = train['date'].dt.day\n"], "execution_count": null, "cell_type": "code", "outputs": []}, {"metadata": {"collapsed": true, "_uuid": "39631945d945184288bd63bb7ff77ed4a200f1c8", "_cell_guid": "a6f50da1-c6f3-41c0-acd0-58c7d73ddea2"}, "source": ["test['month'] = test['date'].dt.month\n", "test['year'] = test['date'].dt.year\n", "test['day'] = test['date'].dt.day\n"], "execution_count": null, "cell_type": "code", "outputs": []}, {"metadata": {"_uuid": "1b7995dc354be67d98db731dec7372920d970a92", "_cell_guid": "ef012c0c-bb7e-4bb6-b260-1d30a34982a8"}, "source": ["# Now we're going to select features\n", "\n", "I'm going to select for features that I think will be important for determining unit_sales:\n", "\n", "* store_nbr\n", "* item_nbr\n", "* cluster \n", "* dow\n", "* doy\n", "* madw\n", "* perishable\n", "* dcoilwtico\n", "* onpromotion\n", "* day\n", "* month\n", "* year\n", "\n", "These will be stored as **`x_train`** and the unit_sales values will be stored as **`y_train`**"], "cell_type": "markdown"}, {"metadata": {"collapsed": true, "_uuid": "a6c94e899e769d611ab496bd5b6f6c7464755219", "_cell_guid": "03b9cd4d-be0b-43ac-8056-1027c3f76d3f"}, "source": ["#train.dropna(inplace=True)\n", "x_train = train[['store_nbr', 'item_nbr', 'cluster', 'dow', 'doy', 'madw',\n", "                 'perishable', 'dcoilwtico','onpromotion', 'day', 'month', 'year']]\n", "y_train = train['unit_sales']\n", "\n", "del train"], "execution_count": null, "cell_type": "code", "outputs": []}, {"metadata": {"_uuid": "e692ab2635c924a1de8af00e2b30dd93cf109638", "_cell_guid": "a9f54149-c2e2-49f2-a05f-f5ae132afc3b"}, "source": ["## Saving the new dataframes as 'x_train.pkl' and 'y_train.pkl' \n", "\n", "We're going to save these new dataframes with **`joblib`**. I've tried cPickling before, but dumping and loading took 20x longer than using joblib. I'm not sure what the magic is behind joblib, but it'll do for this project."], "cell_type": "markdown"}, {"metadata": {"collapsed": true, "_uuid": "a66eb2f80daf6e8836c7e915ccedb727e96de3f9", "_cell_guid": "64118dd0-c869-446c-9e4d-c098356923dc"}, "source": ["joblib.dump(x_train, 'x_train/x_train.pkl')\n", "joblib.dump(y_train, 'y_train/y_train.pkl')"], "execution_count": null, "cell_type": "code", "outputs": []}, {"metadata": {"_uuid": "c9fcaffcb859a522fabc912d2e00e24341b001b3", "_cell_guid": "185d000a-8b6c-4433-b22a-38a620101de7"}, "source": ["### *Footnote: For training and testing*\n", "\n", "Before I finish off, I wanted to share some info about training and testing. Since this dataset is quite large ~12 million rows of unit_sales data, I've had trouble trying to fit beyond 10 trees in random forest model to all my data. So I'm just going to write up a quick footnote on how to split the dataset into train and test, and we'll use the actual test set as validation."], "cell_type": "markdown"}, {"metadata": {"collapsed": true, "_uuid": "68eb5272ae387a0198a978d59916808330ba5722", "_cell_guid": "4c09db73-effe-4315-bbe9-308298a26574"}, "source": ["# Splitting x_train into \n", "from sklearn.cross_validation import train_test_split\n", "\n", "xRealTrain, xRealTest, yRealTrain, yRealTest = train_test_split(x_train, y_train, train_size=0.1)\n", "# train_size = between 0 and 1."], "execution_count": null, "cell_type": "code", "outputs": []}, {"metadata": {"collapsed": true, "_uuid": "1b82a90d7c61c6c65a332fdb8ad71d12bf39d254", "_cell_guid": "acf754f1-2cb5-44da-a99e-dc20e39273a2"}, "source": ["# Summary\n", "\n", "* Merged all data into 1 dataframe\n", "* Added features such as 'dow', 'doy', 'madw', 'mawk', 'day', 'month', 'year'\n", "* Used linear interpolation to fill in oil data\n", "* footnote on how to split train into another set of train, test sets\n"], "cell_type": "markdown"}], "nbformat": 4, "nbformat_minor": 1}