{"metadata": {"kernelspec": {"display_name": "R", "language": "R", "name": "ir"}, "language_info": {"file_extension": ".r", "name": "R", "mimetype": "text/x-r-source", "pygments_lexer": "r", "codemirror_mode": "r", "version": "3.4.1"}}, "nbformat": 4, "nbformat_minor": 1, "cells": [{"cell_type": "markdown", "metadata": {"_cell_guid": "47e539be-e8d0-448c-816b-f42fea6e65bd", "_uuid": "726b2f365e409c25aca549b5823915b76b5f597a"}, "source": ["# Introduction\n", "\n", "This kernel is a continuation of my EDA kernal in python. Switched to R to use the awesome dynamic plotting library -**\"dygraphs\"**\n", "\n", "Link to part 1 (EDA in python): https://www.kaggle.com/jagangupta/memory-optimization-and-eda-on-entire-dataset\n", "\n", "Checkout their wonderful collection of templates I used to make this kernel in the link below\n", "- Link :https://rstudio.github.io/dygraphs/index.html\n", "\n", "## Instructions\n", "\n", "* Pan : Use the range selector and drag it left/right\n", "* Zoom in : Click and drag\n", "* Zoom out: Double-click on graph window\n", "* Change smoothness of the curve : Enter the required time-period in the box given in the botton left corner of the graph\n", "\n", "Please upvote if you found it useful.\n", "\n", "Note:A random sample of 10% is taken on the train dataset\n"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_cell_guid": "c6115e6d-dba1-4b4c-96ff-e517b9360764", "_uuid": "80e39db0e3ac29911f20fad5f2c58d4170bf93b1", "_kg_hide-input": true, "_kg_hide-output": true}, "outputs": [], "source": ["#loading the required libraries\n", "\n", "library('dplyr') \n", "library('readr') \n", "library('data.table')\n", "library(\"tibble\")\n", "library('stringr')\n", "library('lubridate')\n", "library('dygraphs')\n", "library('xts')\n", "library('timeDate')\n", "\n", "#read data\n", "test <- as.tibble(fread('../input/test.csv'))\n", "stores <- as.tibble(fread('../input/stores.csv'))\n", "items <- as.tibble(fread('../input/items.csv'))\n", "trans <- as.tibble(fread('../input/transactions.csv'))\n", "oil <- as.tibble(fread('../input/oil.csv'))\n", "holidays <- as.tibble(fread('../input/holidays_events.csv'))\n", "test <- test %>%\n", "  mutate(date = ymd(date),\n", "         store_nbr = as.factor(store_nbr),\n", "         item_nbr = as.factor(item_nbr))\n", "\n", "stores <- stores %>%\n", "  mutate(city = as.factor(city),\n", "         state = as.factor(state),\n", "         type = as.factor(type),\n", "         store_nbr = as.factor(store_nbr))\n", "\n", "items <- items %>%\n", "  mutate(family = as.factor(family),\n", "         class = as.factor(class),\n", "         perish = as.logical(perishable),\n", "         item_nbr = as.factor(item_nbr))\n", "\n", "trans <- trans %>%\n", "  mutate(date = ymd(date),\n", "         store_nbr = as.factor(store_nbr))\n", "\n", "oil <- oil %>%\n", "  mutate(date = ymd(date)) %>%\n", "  rename(oilprice = dcoilwtico)\n", "\n", "holidays <- holidays %>%\n", "  mutate(type = as.factor(type),\n", "         locale = as.factor(locale),\n", "         locale_name = as.factor(locale_name))\n", "items_perish <- items %>% select (item_nbr,perish)\n", "train_raw <- sample_frac(as.tibble(fread('../input/train.csv',select = 2:5,showProgress=T)),0.1)\n", "train_raw <- train_raw %>% mutate(date = ymd(date),\n", "                          store_nbr = as.factor(store_nbr),\n", "                          item_nbr = as.factor(item_nbr))\n", "train <- merge(train_raw,items_perish,by=\"item_nbr\",all.x=T)"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_cell_guid": "095ecd3f-2868-4676-aabf-34cc2050010b", "_uuid": "9610991fa64154e1fa13c65a7710067fb529baf7", "_kg_hide-input": true}, "outputs": [], "source": ["#aggregations\n", "#calculating perishable sales overall\n", "day_store_perish_sales <- train %>% group_by(date,store_nbr,perish) %>% summarise(sales=sum(unit_sales))\n", "#day_store_perish_sales <- subset_train %>% group_by(date,store_nbr,perish) %>% summarise(sales=sum(unit_sales))\n", "#calculate the store level sales\n", "day_store_sales <- day_store_perish_sales %>% group_by(date,store_nbr) %>% summarise(sales=sum(sales))\n", "#daily_store_sales <- train_raw %>% group_by(date) %>% summarise (sales=sum(unit_sales),avg_sales=mean(sales))\n", "\n", "#For Item dashboard\n", "#store_sales <- day_store_sales %>% group_by(store_nbr) %>% summarise(sales=sum(sales))\n", "# selected stores 44 9 26\n", "#get store item level info\n", "#store_item_sales <- train %>% group_by(store_nbr,item_nbr) %>% summarise(sales=sum(unit_sales))\n", "#store_item_sales = store_item_sales %>% filter(store_nbr==selected_store)\n", "# top_10_items = arrange(store_item_sales,desc(sales))\n", "# top_10_items = head(top_10_items,10)\n", "# top_10_items\n", "#to be used later , if decided to scale\n", "#subset_day_store_perish_sales = day_store_perish_sales %>% filter(store_nbr==selected_store)\n", "#day_store_sales = day_store_sales %>% filter(store_nbr==selected_store)\n", "daily_store_sales <- day_store_sales %>% group_by(date) %>% summarise (avg_sales=mean(sales),sales=sum(sales))"]}, {"cell_type": "markdown", "metadata": {"_cell_guid": "70a1e617-56f9-4412-9585-72019a4e843b", "_uuid": "4fc649eda74805304a452d1fd26051b8eafddbbd"}, "source": ["# Favorita at a Glance\n", "\n", "## Oil's Influence\n", "First graph: The first graph shows the weekly fluctuations in oil price.\n", "* The daily data is converted into weekly OHLC data(Open,High,Low,Close)\n", "* This weekly data is plotted in a candlestick plot just like the ones used in stock market\n", "\n", "Second graph: Transactions and sales\n", "* Overall transactions\n", "* Total number of units sold (sales) \n", "\n", "Note: The two graphs are linked together,so you can zoom in/out, use the slider in one chart and it will change the other one too."]}, {"cell_type": "code", "execution_count": null, "metadata": {"_cell_guid": "ffbe3997-2d47-4bdf-8f28-6aea826b2299", "_uuid": "add4f9b4a32ad6cc1ca90c151ee88c2129490bbd", "_kg_hide-input": true}, "outputs": [], "source": ["start_range=as.Date(c('2016-01-01','2017-01-01'))\n", "\n", "#convert to time series object using xts package\n", "oil_price = xts(oil$oilprice,oil$date)\n", "#convert to weekly OHLC\n", "weekly_oil=to.weekly(oil_price)\n", "\n", "#Plot as a candlestick graph\n", "dygraph(weekly_oil,main=\"Weekly fluctuations in oil price\",xlab ='Time',ylab='Price',group='overall',width=800,height=300)%>% dyCandlestick() %>% \n", "dyRangeSelector(height = 20,dateWindow=start_range) %>%\n", "dyOptions(drawGrid = FALSE)"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_cell_guid": "9fa7faa4-462a-43bc-b4c0-3ea80964a606", "_uuid": "8eb120b00d529d0561dc80e409a83bd215ce9477", "_kg_hide-input": true}, "outputs": [], "source": ["# transactions\n", "overall_trans = trans %>% group_by(date) %>% summarize(transactions=sum(transactions))\n", "transactions = xts(overall_trans$transactions,overall_trans$date)\n", "#sales\n", "daily_store_sales$date=ymd(daily_store_sales$date)\n", "units_sold = xts(daily_store_sales$sales,daily_store_sales$date)\n", "#join\n", "plot_data = cbind(transactions,units_sold)\n", "#plot\n", "dygraph(plot_data,main=\"Sales and transactions\",group=\"overall\",width=800,xlab ='Time',height=350)  %>% \n", "    dyAxis(\"y\", label = \"Sales\") %>%\n", "    dyAxis(\"y2\", label = \"Transactions\", independentTicks = TRUE) %>%\n", "    dySeries(\"..2\",label = \"Sales\",axis='y',color = \"green\") %>% \n", "    dySeries(\"..1\",label = \"Transactions\",axis='y2',color = \"blue\") %>%\n", "    dyRoller(rollPeriod=7) %>%\n", "    dyRangeSelector(dateWindow=start_range) %>%\n", "    dyEvent(\"2016-4-16\", \"Earthquake\", labelLoc = \"bottom\",strokePattern = \"dotdash\") %>%\n", "    dyOptions(axisLineWidth = 1.5, fillGraph = TRUE, drawGrid = FALSE) %>%\n", "    dyHighlight(highlightSeriesOpts = list(strokeWidth = 2),highlightCircleSize = 5, \n", "              highlightSeriesBackgroundAlpha = 0.5,\n", "              hideOnMouseOut = T) "]}, {"cell_type": "markdown", "metadata": {"_cell_guid": "d17764e3-5510-4924-9f0e-f08de7cef63e", "_uuid": "133f6e9ab8c1d5f316418d7aa3ed6c40652cfc94"}, "source": ["The grouping of the charts logic didn't work. All the other features are working. Let me know if you found the fix to it :)\n", "\n", "# Comprehensive store dashboard\n", "- The initial idea was to have a dynamic input and then refresh the below information based on that, but kaggle kernals dont allow shinywidgets or readline\n", "- So just showing three stores (store_nbr=7 , store_nbr=44 and store_nbr=25) \n", "\n", "## Metrics shown\n", "\n", "1. Sale - Units sold in that store for the selected time period\n", "2. Perishable sales - Since there is a weight of 1.5 for these items, showing a seperate plot for them\n", "3. Average sales - Average performance of a store (ie) Total sales on that day/ number of stores open on that day\n"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_cell_guid": "adc635d8-0637-425e-a5b3-4436e76a1a66", "_uuid": "778a50f520a71fd0d44ec7e581a2de85187cd90c", "_kg_hide-input": true}, "outputs": [], "source": ["avg_sale_per_store=xts(daily_store_sales$avg_sales,daily_store_sales$date)\n", "# avg_sale_per_store\n", "\n", "#subset for a store\n", "selected_store=7\n", "subset_day_store_perish_sales <- day_store_perish_sales %>% filter(store_nbr==selected_store,perish==T)\n", "subset_day_store_sales <- day_store_sales %>% filter(store_nbr==selected_store)\n", "\n", "#create xts objects\n", "subset_day_store_perish_sales$date=ymd(subset_day_store_perish_sales$date)\n", "perish_sales <- xts(subset_day_store_perish_sales$sales,subset_day_store_perish_sales$date)\n", "subset_day_store_sales$date=ymd(subset_day_store_sales$date)\n", "total_sales <- xts(subset_day_store_sales$sales,subset_day_store_sales$date)\n", "\n", "#join\n", "plot_data = cbind(avg_sale_per_store,perish_sales,total_sales)\n", "plot_data = na.fill(plot_data,0)\n", "\n", "dygraph(plot_data,main=\"Store Number 7\",group=\"store\",width=800,xlab ='Time',ylab='sales',height=500)  %>% \n", "     dySeries(\"..1\",label = \"Average sale per store\",color=\"red\") %>% \n", "     dySeries(\"..2\",label = \"Perishables sale\",color=\"blue\") %>% \n", "     dySeries(\"..3\",label = \"Store 7 sale\",color=\"green\") %>%\n", "     dyRoller(rollPeriod=7) %>%\n", "     dyEvent(\"2016-4-16\", \"Earthquake\", labelLoc = \"bottom\",strokePattern = \"dotdash\") %>%\n", "     dyRangeSelector(dateWindow=start_range) %>%\n", "     dyLegend(width = 600) %>%\n", "     dyHighlight(highlightCircleSize = 5,highlightSeriesOpts = 3,\n", "              highlightSeriesBackgroundAlpha = 0.5,\n", "              hideOnMouseOut = FALSE)"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_cell_guid": "7651544e-3493-4e90-8fa7-20be51437ed0", "_uuid": "d317ab8d8da806b86d31ee0cdc16d7c17c72fea0", "_kg_hide-input": true}, "outputs": [], "source": ["#subset for a store\n", "selected_store=44\n", "subset_day_store_perish_sales <- day_store_perish_sales %>% filter(store_nbr==selected_store,perish==T)\n", "subset_day_store_sales <- day_store_sales %>% filter(store_nbr==selected_store)\n", "\n", "#create xts objects\n", "subset_day_store_perish_sales$date=ymd(subset_day_store_perish_sales$date)\n", "perish_sales <- xts(subset_day_store_perish_sales$sales,subset_day_store_perish_sales$date)\n", "subset_day_store_sales$date=ymd(subset_day_store_sales$date)\n", "total_sales <- xts(subset_day_store_sales$sales,subset_day_store_sales$date)\n", "\n", "#join\n", "plot_data = cbind(avg_sale_per_store,perish_sales,total_sales)\n", "plot_data = na.fill(plot_data,0)\n", "\n", "dygraph(plot_data,main=\"Store Number 44\",group=\"store\",width=800,xlab ='Time',ylab='sales',height=500)  %>% \n", "     dySeries(\"..1\",label = \"Average sale per store\",color=\"red\") %>% \n", "     dySeries(\"..2\",label = \"Perishables sale\",color=\"blue\") %>% \n", "     dySeries(\"..3\",label = \"Store 44 sale\",color=\"green\") %>%\n", "     dyEvent(\"2016-4-16\", \"Earthquake\", labelLoc = \"bottom\",strokePattern = \"dotdash\") %>%\n", "     dyRoller(rollPeriod=7) %>%\n", "     dyRangeSelector(dateWindow=start_range) %>%\n", "     dyLegend(width = 600) %>%\n", "     dyHighlight(highlightCircleSize = 5,highlightSeriesOpts = 3,\n", "              highlightSeriesBackgroundAlpha = 0.5,\n", "              hideOnMouseOut = FALSE)\n"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_cell_guid": "84843430-367d-4103-a431-30704ca05227", "_uuid": "b7a8dd74bbc0d53bffc4c80f4e7d9f3fb388fcf9", "_kg_hide-input": true}, "outputs": [], "source": ["#subset for a store\n", "selected_store=25\n", "subset_day_store_perish_sales <- day_store_perish_sales %>% filter(store_nbr==selected_store,perish==T)\n", "subset_day_store_sales <- day_store_sales %>% filter(store_nbr==selected_store)\n", "\n", "#create xts objects\n", "subset_day_store_perish_sales$date=ymd(subset_day_store_perish_sales$date)\n", "perish_sales <- xts(subset_day_store_perish_sales$sales,subset_day_store_perish_sales$date)\n", "subset_day_store_sales$date=ymd(subset_day_store_sales$date)\n", "total_sales <- xts(subset_day_store_sales$sales,subset_day_store_sales$date)\n", "\n", "#join\n", "plot_data = cbind(avg_sale_per_store,perish_sales,total_sales)\n", "plot_data = na.fill(plot_data,0)\n", "\n", "dygraph(plot_data,main=\"Store Number 25\",group=\"store\",width=800,xlab ='Time',ylab='sales',height=500)  %>% \n", "     dySeries(\"..1\",label = \"Average sale per store\",color=\"red\") %>% \n", "     dySeries(\"..2\",label = \"Perishables sale\",color=\"blue\") %>% \n", "     dySeries(\"..3\",label = \"Store 25 sale\",color=\"green\") %>%\n", "     dyEvent(\"2016-4-16\", \"Earthquake\", labelLoc = \"bottom\",strokePattern = \"dotdash\") %>%\n", "     dyRoller(rollPeriod=7) %>%\n", "     dyRangeSelector(dateWindow=start_range) %>%\n", "     dyLegend(width = 600) %>%\n", "     dyHighlight(highlightCircleSize = 5,highlightSeriesOpts = 3,\n", "              highlightSeriesBackgroundAlpha = 0.5,\n", "              hideOnMouseOut = FALSE)\n"]}, {"cell_type": "markdown", "metadata": {"_cell_guid": "4cb564e0-53af-4cc1-91c2-13348560e046", "_uuid": "6183be9dc7b59c7a0c89a10dbde2805991094fad"}, "source": ["### There seems to be a period of zero sales between Aug,28 2016 - Oct 26 2016 for store 25 which is wierd.\n", "* This can be due to two reasons\n", "    * The store was shut during that period\n", "    * There were 0 entries after the 10 % random sample for that store in that period (which is unlikely)\n", "\n", "We can cross check that piece of info with the transactions table."]}, {"cell_type": "code", "execution_count": null, "metadata": {"_cell_guid": "62abf1e2-55de-405a-a6f4-224ee842377b", "_uuid": "05afd4fee6b700a300c750321e0d1e708fb92799"}, "outputs": [], "source": ["#trans\n", "trans_25=trans[trans$store_nbr==25,]\n", "trans_25=trans_25[trans_25$date>as.Date('2016-08-20'),]\n", "head(trans_25)"]}, {"cell_type": "markdown", "metadata": {"_cell_guid": "b4055c93-b572-41d8-a91f-da38c0d6f14a", "_uuid": "8440d9460bce009ae4950c4bdba7029c3b9645e9"}, "source": ["# Yup, The store must have been closed for renovation I guess.  :)"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_cell_guid": "aed7c816-9d0c-47d0-b936-2f3a957f2c26", "_uuid": "18b533eaa29a554dee7c41edcb4773521a04116c", "_kg_hide-input": true}, "outputs": [], "source": ["#To highligh the holidays\n", "fifteenth=seq(from=min(daily_store_sales$date)+14,max(daily_store_sales$date),by = \"month\")\n", "last <- ymd(fifteenth)+days_in_month(fifteenth)-15\n", "fifteenth<-ymd(fifteenth)\n", "payday = data.frame(fifteenth,last)\n", "# payday"]}, {"cell_type": "markdown", "metadata": {"_cell_guid": "2230d9fc-b592-4293-9b87-d20ec9b4ed42", "_uuid": "94edf45708396000238068144d9ee7a13b923fbc"}, "source": ["# To be continued \n", "\n", "Next steps:\n", "* Fix grouping problem\n", "* Add holiday markers\n", "* Create item dashboard\n", "* Create base-line model "]}, {"cell_type": "markdown", "metadata": {"_cell_guid": "cd4a9dbe-d106-4915-bd27-30dddcc5f273", "_uuid": "7ded4e0d27a8c4493a357199ca3e216b99f698d0"}, "source": []}, {"cell_type": "markdown", "metadata": {"_cell_guid": "81f2e555-739a-40b3-8040-91473c925501", "_uuid": "0d3b2fc71398ba53d7fc4b52e335fc01df6e2078"}, "source": []}]}