{"cells": [{"metadata": {}, "cell_type": "markdown", "source": ["I will start with general overview of all datasets given and some of its features, mainly the Transactions table and Items Table.\n", "\n", "Later, I will be focusing on the Train data, by filtering store number 25 only for the year 2016."]}, {"metadata": {"_cell_guid": "6411bc05-c3b9-4173-a246-cb022b50c26b", "_uuid": "c2f970c7c06690d2cbf59a156c01a4b3db6aa125", "collapsed": true}, "cell_type": "code", "execution_count": null, "outputs": [], "source": ["import pandas as pd\n", "import numpy as np"]}, {"metadata": {"_cell_guid": "c2a7fdf5-e6cb-4124-ab3a-d1f84900d48e", "_uuid": "9256e872ba256aa055363fe2d72fa7bae8ca28cc"}, "cell_type": "code", "execution_count": null, "outputs": [], "source": ["holidays_events_df = pd.read_csv('../input/holidays_events.csv', low_memory=False)\n", "items_df = pd.read_csv('../input/items.csv', low_memory=False)\n", "oil_df = pd.read_csv('../input/oil.csv', low_memory=False)\n", "stores_df = pd.read_csv('../input/stores.csv', low_memory=False)\n", "transactions_df = pd.read_csv('../input/transactions.csv', low_memory=False)"]}, {"metadata": {}, "cell_type": "markdown", "source": ["# Favorita Transactions "]}, {"metadata": {}, "cell_type": "markdown", "source": ["I will start by changing some columns on this data set. \n", "Add columns month and year. Also add *day of the week* so we can see what are the favourite days of the week to shop."]}, {"metadata": {}, "cell_type": "code", "execution_count": null, "outputs": [], "source": ["import calendar\n", "\n", "transactions_df[\"year\"] = transactions_df[\"date\"].astype(str).str[:4].astype(np.int64)\n", "transactions_df[\"month\"] = transactions_df[\"date\"].astype(str).str[5:7].astype(np.int64)\n", "transactions_df['date'] = pd.to_datetime(transactions_df['date'], errors ='coerce')\n", "transactions_df['day_of_week'] = transactions_df['date'].dt.weekday_name\n", "\n", "\n", "transactions_df[\"year\"] = transactions_df[\"year\"].astype(str)\n", "transactions_df.head()"]}, {"metadata": {}, "cell_type": "markdown", "source": ["This heatmap plots the transactions for each month and year. Since we don\u00b4t have data beyond September 2017, those squares appear blank.\n", "\n", "It appears that December has the most transactions, for all years considered. \n", "Also,  as we advance through the years, the little squares are getting lighter, which indicates that the number of transactions are increasing each year. It could be the fact that new Favorita stores are opening each year."]}, {"metadata": {}, "cell_type": "code", "execution_count": null, "outputs": [], "source": ["%matplotlib inline\n", "import matplotlib.pyplot as plt\n", "import seaborn as sns\n", "\n", "x = transactions_df.groupby(['month', 'year'], as_index=False).agg({'transactions':'sum'})\n", "y = x.pivot(\"month\", \"year\", \"transactions\")\n", "fig, ax = plt.subplots(figsize=(10,7))\n", "sns.heatmap(y);"]}, {"metadata": {}, "cell_type": "markdown", "source": ["Now, let\u00b4s see what happens during each day of the week.\n", "\n", "As one might expect, Saturdays and Sundays seem to be the prefered day to shop at this supermaket.\n", "Thursdays and Mondays are little shy on shopping days.\n", "The year 2017 seems to be worse, but that is just the fact that these maps average out for each year, and since we dont have 4 months of data for 2017, it appears that 2017 is performing worse compared to previous years. "]}, {"metadata": {}, "cell_type": "code", "execution_count": null, "outputs": [], "source": ["x = transactions_df.groupby(['day_of_week', 'year'], as_index=False).agg({'transactions':'sum'})\n", "y = x.pivot(\"day_of_week\", \"year\", \"transactions\")\n", "fig, ax = plt.subplots(figsize=(10,7))\n", "sns.heatmap(y);"]}, {"metadata": {}, "cell_type": "markdown", "source": ["# Stores"]}, {"metadata": {}, "cell_type": "markdown", "source": ["Let\u00b4s have a look at another dataset, Stores.\n", "\n", "I will add another column, the region column. This column will have three values/regions: Sierra, Costa and Oriente."]}, {"metadata": {}, "cell_type": "code", "execution_count": null, "outputs": [], "source": ["regions_data = {\n", "        \n", "        'region': ['Sierra','Sierra','Sierra','Sierra', 'Sierra', 'Sierra', 'Sierra', 'Sierra', \n", "                   'Costa', 'Costa', 'Costa', 'Costa', 'Costa', 'Costa' , 'Costa' , 'Oriente'],\n", "        \n", "    \n", "        'state': ['Imbabura','Tungurahua', 'Pichincha', 'Azuay', 'Bolivar', 'Chimborazo', \n", "                 'Loja', 'Cotopaxi', 'Esmeraldas', 'Manabi', 'Santo Domingo de los Tsachilas', \n", "                 'Santa Elena', 'Guayas', 'El Oro', 'Los Rios', 'Pastaza']}\n", "\n", "df_regions = pd.DataFrame(regions_data, columns = ['region', 'state'])\n", "\n", "df_regions_cities = pd.merge(df_regions, stores_df, on='state')\n", "\n", "\n", "transactions_regions = pd.merge(transactions_df, df_regions_cities, on='store_nbr')\n", "transactions_regions.head()"]}, {"metadata": {}, "cell_type": "markdown", "source": ["Pichincha State pops out in terms of transactions for all years considered. Guayas comes right next.\n", "The states with less transactions seem to be Bolivar, Chimborazo, Esmeraldas, Manabi and Santa Elena."]}, {"metadata": {}, "cell_type": "code", "execution_count": null, "outputs": [], "source": ["x = transactions_regions.groupby(['state', 'year'], as_index=False).agg({'transactions':'sum'})\n", "y = x.pivot(\"state\", \"year\", \"transactions\")\n", "fig, ax = plt.subplots(figsize=(12,9))\n", "sns.heatmap(y);"]}, {"metadata": {}, "cell_type": "markdown", "source": ["In terms of stores, from the graph below, we can see that the stores with lighter colors have the most transactions.\n", "Store numbers such as 3, 9, 11, 43, 45 and 47 are the ones with more transactions."]}, {"metadata": {}, "cell_type": "code", "execution_count": null, "outputs": [], "source": ["x = transactions_regions.groupby(['store_nbr', 'year'], as_index=False).agg({'transactions':'sum'})\n", "y = x.pivot(\"store_nbr\", \"year\", \"transactions\")\n", "fig, ax = plt.subplots(figsize=(12,9))\n", "sns.heatmap(y);"]}, {"metadata": {}, "cell_type": "markdown", "source": ["# Items"]}, {"metadata": {}, "cell_type": "code", "execution_count": null, "outputs": [], "source": ["items_df.head()"]}, {"metadata": {}, "cell_type": "code", "execution_count": null, "outputs": [], "source": ["items_df.family.unique()"]}, {"metadata": {}, "cell_type": "markdown", "source": ["There are 33 different food/supplies categories. I want to know what is the percentage for each one in terms of transactions. So I will add a column, the *percentage* column, and then plot the results."]}, {"metadata": {}, "cell_type": "code", "execution_count": null, "outputs": [], "source": ["items_df_family = items_df.groupby(['family']).size().to_frame(name = 'count').reset_index()\n", "items_df_family['percentage']= items_df_family['count']/items_df_family['count'].sum() * 100\n", "items_df_family.head()"]}, {"metadata": {}, "cell_type": "code", "execution_count": null, "outputs": [], "source": ["sns.set_style(\"white\")\n", "fig, ax =plt.subplots(figsize=(14,10))\n", "ax = sns.barplot(x=\"percentage\", y=\"family\", data=items_df_family, palette=\"BuGn_r\")"]}, {"metadata": {}, "cell_type": "markdown", "source": ["It appears that Grocery I has the most transactions, with more than 30%. It then follows Beverages, Cleaning and Produce.\n", "\n", "Beauty Care, Hardware, Seafood and Magazines are in the bottom, being less representative than any other category."]}, {"metadata": {}, "cell_type": "markdown", "source": ["# Items,Transactions and Train Data"]}, {"metadata": {}, "cell_type": "markdown", "source": ["Since there is more than 125 million rows on the Train data, I will filter this data to show only what happened in store number 25 during 2016."]}, {"metadata": {}, "cell_type": "code", "execution_count": null, "outputs": [], "source": ["dtypes = {'store_nbr': np.dtype('int64'),\n", "          'item_nbr': np.dtype('int64'),\n", "          'unit_sales': np.dtype('float64'),\n", "          'onpromotion': np.dtype('O')}\n", "\n", "\n", "train = pd.read_csv('../input/train.csv', index_col='id', parse_dates=['date'], dtype=dtypes)\n", "date_mask = (train['date'] >= '2016-01-01') & (train['date'] <= '2016-12-31') & (train['store_nbr'] == 25)\n", "train = train[date_mask]\n", "train.head()"]}, {"metadata": {}, "cell_type": "markdown", "source": ["It would be interesting to see the train data merged with the Items table so we could work on more information.\n", "I can sort both tables through *item_nbr*  and then I will sort the date."]}, {"metadata": {}, "cell_type": "code", "execution_count": null, "outputs": [], "source": ["df_train_item = pd.merge(train, items_df, on='item_nbr').sort_values(by='date')\n", "df_train_item[\"year\"] = df_train_item[\"date\"].astype(str).str[:4].astype(np.int64)\n", "df_train_item[\"month\"] = df_train_item[\"date\"].astype(str).str[5:7].astype(np.int64)\n", "df_train_item.head()"]}, {"metadata": {}, "cell_type": "markdown", "source": ["For store number 25, during 2016, I will plot the top 7 food/supplies categories and for each I want to know the transactions for each month."]}, {"metadata": {}, "cell_type": "code", "execution_count": null, "outputs": [], "source": ["sns.set_style(\"white\")\n", "ax = plt.subplots(figsize=(13, 9))\n", "sns.countplot(x=\"family\", hue=\"month\", data=df_train_item, palette=\"Greens_d\",\n", "              order=df_train_item.family.value_counts().iloc[:7].index);"]}, {"metadata": {}, "cell_type": "markdown", "source": ["It appears that the top 7 categories are Grocery, Beverages, Cleaning, Produce, Dairy, Bread and Personal Care.\n", "\n", "We dont have data for September, therefore this month does not appear on the graph. Also, for the month of October, we only have a few days of data, therefore the bar for this month is way below, compared to the other months."]}, {"metadata": {}, "cell_type": "markdown", "source": ["Let us see what are the top 30 products for this store.\n", "Below is this list, along with the number of times this product was transactioned for this store."]}, {"metadata": {}, "cell_type": "code", "execution_count": null, "outputs": [], "source": ["df_train_item['item_nbr'].value_counts().nlargest(30)"]}, {"metadata": {"collapsed": true}, "cell_type": "code", "execution_count": null, "outputs": [], "source": []}], "metadata": {"language_info": {"nbconvert_exporter": "python", "pygments_lexer": "ipython3", "mimetype": "text/x-python", "name": "python", "version": "3.6.3", "codemirror_mode": {"version": 3, "name": "ipython"}, "file_extension": ".py"}, "kernelspec": {"display_name": "Python 3", "name": "python3", "language": "python"}}, "nbformat": 4, "nbformat_minor": 1}