{"metadata": {"kernelspec": {"name": "python3", "language": "python", "display_name": "Python 3"}, "language_info": {"codemirror_mode": {"version": 3, "name": "ipython"}, "file_extension": ".py", "pygments_lexer": "ipython3", "version": "3.6.4", "name": "python", "mimetype": "text/x-python", "nbconvert_exporter": "python"}}, "nbformat": 4, "cells": [{"cell_type": "markdown", "metadata": {}, "source": ["# Import Libraries"]}, {"cell_type": "code", "metadata": {"collapsed": true}, "execution_count": null, "source": ["import pandas as pd\n", "import numpy as np\n", "from dateutil import parser\n", "import matplotlib.pyplot as plt\n", "import seaborn as sns\n", "import random\n", "import csv"], "outputs": []}, {"cell_type": "markdown", "metadata": {}, "source": ["# Import Data"]}, {"cell_type": "code", "metadata": {"collapsed": true}, "execution_count": null, "source": ["!ls"], "outputs": []}, {"cell_type": "code", "metadata": {"collapsed": true}, "execution_count": null, "source": ["dataTransactions = pd.read_csv('transactions.csv')\n", "dataHolidays = pd.read_csv('holidays_events.csv')\n", "#dataTrain = pd.read_csv('train.csv')\n", "dataTrain = pd.read_csv('test.csv')\n", "dataTest = pd.read_csv('test.csv')\n", "dataStores = pd.read_csv('stores.csv')\n", "dataItems = pd.read_csv('items.csv')"], "outputs": []}, {"cell_type": "markdown", "metadata": {}, "source": ["# Analyzing annual transactions "]}, {"cell_type": "code", "metadata": {"collapsed": true}, "execution_count": null, "source": ["#dataTrain['unit_sales'] = int(random.randint(1,6))\n", "dataTest['unit_sales'] = 0\n", "dataTransactions['year'] = dataTransactions['date'].apply(lambda row: int(row.split('-')[0]))\n", "dataTransactions['month'] = dataTransactions['date'].apply(lambda row: int(row.split('-')[1]))"], "outputs": []}, {"cell_type": "code", "metadata": {}, "execution_count": null, "source": ["dataTransactions.head()"], "outputs": []}, {"cell_type": "code", "metadata": {"collapsed": true}, "execution_count": null, "source": ["transactionsYear = pd.DataFrame(\n", "    {'transactions' : dataTransactions.groupby( ['year'] )['transactions'].sum()}\n", ").reset_index()"], "outputs": []}, {"cell_type": "code", "metadata": {}, "execution_count": null, "source": ["transactionsYear"], "outputs": []}, {"cell_type": "code", "metadata": {}, "execution_count": null, "source": ["sns.set_style(\"darkgrid\")\n", "ax = sns.factorplot(x=\"year\", y=\"transactions\",data=transactionsYear, size=5, aspect=1.5, kind=\"bar\")\n", "plt.show()"], "outputs": []}, {"cell_type": "markdown", "metadata": {}, "source": ["# Linear sales regression\n", "\n", "#### Linear regression is a statistical technique used to study the relationship between variables and to predict their behavior, in the exercise the number of transactions is related to the period number, taking into account that the analysis period is monthly, you have a total of 56 periods "]}, {"cell_type": "code", "metadata": {"collapsed": true}, "execution_count": null, "source": ["transactionsMont = pd.DataFrame(\n", "    {'transactions' : dataTransactions.groupby( ['year', 'month'] )['transactions'].sum()}\n", ").reset_index()\n", "transactionsMont['period'] = range(1, len(transactionsMont) +1)"], "outputs": []}, {"cell_type": "code", "metadata": {"scrolled": true}, "execution_count": null, "source": ["# numero de periodos o meses disponibles para el calculo de \u00f1a regresion \n", "transactionsMont['period'].max()"], "outputs": []}, {"cell_type": "markdown", "metadata": {}, "source": ["#### It is observed that there is a close relationship between the period and the number of transactions, in addition to a notorious change in this relationship, occurs in the month of December.\n"]}, {"cell_type": "code", "metadata": {}, "execution_count": null, "source": ["#regresion lineal de ventas segun el numero de periodos\n", "g = sns.lmplot(x=\"period\", y=\"transactions\", data=transactionsMont, size=7)\n", "plt.show()"], "outputs": []}, {"cell_type": "markdown", "metadata": {}, "source": ["### al relacionar el numero de transacciones por a\u00f1o se obcerva que tiene un comportamiento acendente "]}, {"cell_type": "code", "metadata": {}, "execution_count": null, "source": ["#regresion lineal de ventas segun por anio\n", "g = sns.lmplot(x=\"period\", y=\"transactions\", data=transactionsMont, size=7, hue=\"year\")\n", "plt.show()"], "outputs": []}, {"cell_type": "markdown", "metadata": {}, "source": ["# Sales by city"]}, {"cell_type": "code", "metadata": {}, "execution_count": null, "source": ["# join de los datos\n", "dataTrainState = dataTransactions.join(dataStores.set_index('store_nbr'), on='store_nbr').reset_index()\n", "dataTrainState.head() "], "outputs": []}, {"cell_type": "code", "metadata": {}, "execution_count": null, "source": ["dataTrainStateCount = pd.DataFrame(\n", "    {'transactions' : dataTrainState.groupby( ['year', 'state'] )['transactions'].sum()}\n", ").reset_index()\n", "dataTrainStateCount.head()"], "outputs": []}, {"cell_type": "markdown", "metadata": {}, "source": ["### transactions are related to the province to determine its impact on sales"]}, {"cell_type": "code", "metadata": {"scrolled": true}, "execution_count": null, "source": ["g = sns.factorplot(x=\"year\", y=\"transactions\", col = 'state', data=dataTrainStateCount, kind=\"bar\", size=2.7, col_wrap=3);\n", "plt.show()"], "outputs": []}, {"cell_type": "code", "metadata": {}, "execution_count": null, "source": ["g = sns.factorplot(x=\"year\", y=\"transactions\", hue='state', data=dataTrainStateCount, size=6);\n", "plt.show()"], "outputs": []}, {"cell_type": "markdown", "metadata": {}, "source": ["### it is observed that one of the provinces with the greatest influence is Pichincha, but in spite of that the index of variability between the provinces is very similar"]}, {"cell_type": "code", "metadata": {}, "execution_count": null, "source": ["g = sns.lmplot(x=\"month\", y=\"transactions\", data=dataTrainState, size=7, hue=\"state\")\n", "plt.show()"], "outputs": []}, {"cell_type": "markdown", "metadata": {}, "source": ["# Sales by stores"]}, {"cell_type": "code", "metadata": {}, "execution_count": null, "source": ["dataTrainStorade = pd.DataFrame(\n", "    {'transactions' : dataTransactions.groupby( ['store_nbr'] )['transactions'].sum()}\n", ").reset_index()\n", "dataTrainStorade = dataTrainStorade.sort_values(['transactions'], ascending=[False])\n", "dataTrainStorade.head()"], "outputs": []}, {"cell_type": "markdown", "metadata": {}, "source": ["### Classification of transactions according to the store"]}, {"cell_type": "code", "metadata": {}, "execution_count": null, "source": ["g = sns.factorplot(x=\"transactions\", y=\"store_nbr\", data=dataTrainStorade, size=10,  kind=\"bar\", orient=\"h\", order=dataTrainStorade.store_nbr)\n", "plt.show()"], "outputs": []}, {"cell_type": "markdown", "metadata": {}, "source": ["# Holiday sales"]}, {"cell_type": "code", "metadata": {}, "execution_count": null, "source": ["# join de los datos\n", "dataTrainHoliday = dataTransactions.join(dataHolidays.set_index('date'), on='date').reset_index()\n", "dataTrainHoliday = pd.DataFrame(\n", "    {'transactions' : dataTrainHoliday.groupby( ['description'])['transactions'].sum()}\n", ").reset_index()\n", "dataTrainHoliday['transactions'].min()"], "outputs": []}, {"cell_type": "code", "metadata": {}, "execution_count": null, "source": ["g = sns.factorplot(x=\"transactions\", y=\"description\", data=dataTrainHoliday, kind=\"bar\", size=20);\n", "plt.show()"], "outputs": []}, {"cell_type": "markdown", "metadata": {}, "source": ["# Product analysis"]}, {"cell_type": "code", "metadata": {}, "execution_count": null, "source": ["dataItemFamily = dataTrain.join(dataItems.set_index('item_nbr'), on='item_nbr').reset_index()\n", "dataItemFamily.head()"], "outputs": []}, {"cell_type": "code", "metadata": {}, "execution_count": null, "source": ["dataItemFamily['id'].max()"], "outputs": []}, {"cell_type": "code", "metadata": {}, "execution_count": null, "source": ["dataItemFamilyStore = pd.DataFrame(\n", "    {'transactions' : dataItemFamily.groupby( ['family', 'store_nbr']).size()}\n", ").reset_index()\n", "dataItemFamilyStore = dataItemFamilyStore.join(dataStores.set_index('store_nbr'), on='store_nbr').reset_index()\n", "dataItemFamilyStore.head()"], "outputs": []}, {"cell_type": "code", "metadata": {}, "execution_count": null, "source": ["len(dataItemFamilyStore)"], "outputs": []}, {"cell_type": "code", "metadata": {"collapsed": true}, "execution_count": null, "source": ["### se relaciona tanto la tienda y su influencia y transacionabilidad de los productos"], "outputs": []}, {"cell_type": "code", "metadata": {"scrolled": false}, "execution_count": null, "source": ["g = sns.factorplot(x=\"transactions\", y=\"family\", col=\"store_nbr\", data=dataItemFamilyStore, kind=\"bar\", size=5, col_wrap=3);\n", "plt.show()"], "outputs": []}, {"cell_type": "code", "metadata": {}, "execution_count": null, "source": ["dataItemFamilyCity = pd.DataFrame(\n", "    {'transactions' : dataItemFamilyStore.groupby( ['state', 'family'])['transactions'].sum()}\n", ").reset_index()\n", "dataItemFamilyCity.head()"], "outputs": []}, {"cell_type": "markdown", "metadata": {}, "source": ["### The product is related to the region to determine consumption by province"]}, {"cell_type": "code", "metadata": {"scrolled": false}, "execution_count": null, "source": ["g = sns.factorplot(x=\"transactions\", y=\"state\", col=\"family\", data=dataItemFamilyCity, kind=\"bar\", size=5, col_wrap=3);\n", "plt.show()"], "outputs": []}, {"cell_type": "markdown", "metadata": {}, "source": ["# Generation of predictions"]}, {"cell_type": "code", "metadata": {"collapsed": true}, "execution_count": null, "source": ["dtypes = {'id':'int64', 'item_nbr':'int32', 'store_nbr':'int8', 'onpromotion':str}\n", "dataTrain = pd.read_csv('../input/train.csv', dtype=dtypes)\n", "dataTest = pd.read_csv('../input/test.csv', dtype=dtypes)\n", "dataTest['unit_sales'] = 0\n", "#dataTrain['unit_sales'] = int(random.randint(1,6))"], "outputs": []}, {"cell_type": "code", "metadata": {}, "execution_count": null, "source": ["# graficos embebidos\n", "%matplotlib inline\n", "dataTrain['day'] = dataTrain['date'].apply(lambda row: int(row.split('-')[2]))\n", "dataTrain['date'] = dataTrain['date'].apply(lambda row: int(row.split('-')[1]))\n", "dataTrain['onpromotion'] = dataTrain['onpromotion'].apply(lambda row: 1 if row == True else 0)\n", "dataTrain.head()"], "outputs": []}, {"cell_type": "code", "metadata": {"collapsed": true}, "execution_count": null, "source": ["feature_cols = ['date', 'store_nbr', 'item_nbr', 'day']"], "outputs": []}, {"cell_type": "markdown", "metadata": {}, "source": ["## Ajustando el modelo"]}, {"cell_type": "code", "metadata": {"collapsed": true}, "execution_count": null, "source": ["from sklearn.linear_model import LinearRegression\n", "\n", "rl = LinearRegression() # Creando el modelo.\n", "rl.fit(np.array(dataTrain[feature_cols]), np.array(dataTrain['unit_sales'])) # ajustando el modelo x, y"], "outputs": []}, {"cell_type": "markdown", "metadata": {}, "source": ["#### Generando predicciones"]}, {"cell_type": "code", "metadata": {"collapsed": true}, "execution_count": null, "source": ["dataTest['onpromotion'] = dataTest['onpromotion'].apply(lambda row: 1 if row == True else 0)\n", "dataTest['day'] = dataTest['date'].apply(lambda row: int(row.split('-')[2]))\n", "dataTest['date'] = dataTest['date'].apply(lambda row: int(row.split('-')[1]))"], "outputs": []}, {"cell_type": "code", "metadata": {"collapsed": true, "scrolled": true}, "execution_count": null, "source": ["predicciones = rl.predict(np.array(dataTest[feature_cols]))\n", "predicciones_df = pd.DataFrame({'id': dataTest['id'], 'unit_sales': predicciones.round()})\n", "predicciones_df.head() # predicciones de las primeras 5 lineas"], "outputs": []}, {"cell_type": "code", "metadata": {"collapsed": true}, "execution_count": null, "source": ["print(rl.intercept_)\n", "print(rl.coef_)"], "outputs": []}, {"cell_type": "code", "metadata": {"collapsed": true}, "execution_count": null, "source": ["# filas requeridas 3370464\n", "len(dataTrain['day'])"], "outputs": []}, {"cell_type": "code", "metadata": {"collapsed": true}, "execution_count": null, "source": ["predicciones_df.to_csv('predicciones.csv', index=False, float_format='%.2f')"], "outputs": []}, {"cell_type": "markdown", "metadata": {}, "source": ["# Conclusions\n", "#### After having analyzed the relevant data, the following conclusions have been reached\n", "#### 1) The average annual transactions is 30295802.6\n", "#### 2) The year with the most bandages was in 2015 with a number of 31312370\n", "#### 3) The total number of sales for 2017 will be an approximate number to 30000204\n", "#### 4) The province with the highest number of sales is Pichincha while the one with the lowest sales is Pastaza\n", "#### 5) The day that most sales are made is the day of carnival with a number of 773458 transactions recorded to date\n", "#### 6) The day with the lowest sales made is the first day of the year with a number of 5941 transactions registered to date\n", "#### 7) The family of products with the most sales concurrency is GROCERY I\n", "#### 8) The store with the most registered sales is 44 located in Pichincha with a number of 6201115 transactions registered to date"]}], "nbformat_minor": 1}