<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="https://www.r-bloggers.com/data-science-for-business-time-series-forecasting-part-1-eda-data-preparation/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://shiring.github.io/forecasting/2017/05/28/retail_forcasting_part1"> Shirin's playgRound</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>Data Science is a fairly broad term and encompasses a wide range of techniques from data visualization to statistics and machine learning models. But the techniques are only tools in a – sometimes very messy – toolbox. And while it is important to know and understand these tools, here, I want to go at it from a different angle: What is the task at hand that data science tools can help tackle, and what question do we want to have answered?</p>
<p>A straight-forward business problem is to estimate future sales and future income. Based on past experience, i.e. data from past sales, data science can help improve forecasts and generate models that describe the main factors of influence. This, in turn, can then be used to develop actions based on what we have learned, like where to increase advertisement, how much of which products to keep in stock, etc.</p>
<p></p>
<h2>Data preparation</h2>
<p>While it isn’t the most exciting aspect of data science, and therefore often neglected in favor of fancy modeling techniques, getting the data into the right format and extracting meaningful features, is arguably THE most essential part of any analysis!</p>
<p>Therefore, I have chosen to dedicate an entire article to this part and will discuss modeling and time series forecasting in separate blog posts.</p>
<p></p>
<p>Many of the formal concepts I am using when dealing with data in a tidy way come from <a target="_blank" rel="nofollow" href="http://r4ds.had.co.nz/">Hadley Wickham &amp; Garrett Grolemund’s “R for Data Science”</a>.</p>
<p>The central package is <strong>tidyverse</strong>, which contains <strong>tidyr</strong>, <strong>dplyr</strong>, <strong>ggplot</strong>, etc. Other packages I am using are <strong>tidyquant</strong> for its nice <strong>ggplot</strong> theme, <strong>modelr</strong>, <strong>gridExtra</strong> and <strong>grid</strong> for additional plotting functionalities.</p>
<div>
<pre><code><span>library</span><span>(</span><span>tidyverse</span><span>)</span><span>
</span><span>library</span><span>(</span><span>tidyquant</span><span>)</span><span>
</span><span>library</span><span>(</span><span>modelr</span><span>)</span><span>
</span><span>library</span><span>(</span><span>gridExtra</span><span>)</span><span>
</span><span>library</span><span>(</span><span>grid</span><span>)</span><span>
</span></code></pre>
</div>
<p></p>
<h2>The data</h2>
<p>I am again using a dataset from <a target="_blank" rel="nofollow" href="http://archive.ics.uci.edu/ml/datasets/Online+Retail">UC Irvine’s machine learning repository</a>.</p>
<p>From the dataset description:</p>
<blockquote>
<p>This is a transnational data set which contains all the transactions occurring between 01/12/2010 and 09/12/2011 for a UK-based and registered non-store online retail.The company mainly sells unique all-occasion gifts. Many customers of the company are wholesalers.</p>
</blockquote>
<blockquote>
<p>Daqing Chen, Sai Liang Sain, and Kun Guo, Data mining for the online retail industry: A case study of RFM model-based customer segmentation using data mining, Journal of Database Marketing and Customer Strategy Management, Vol. 19, No. 3, pp. 197â€“208, 2012 (Published online before print: 27 August 2012. doi: 10.1057/dbm.2012.17).</p>
</blockquote>
<p></p>
<h3>Reading in the data</h3>
<p>A fast way to read in data in csv format is to use <strong>readr</strong>’s <em>read_csv()</em> function. With a small dataset like this, it makes sense to specifically define what format each column should have (e.g. integers, character, etc.). In our case, this is particularly convenient for defining the date/time column to be read in correctly with <em>col_datetime()</em>.</p>
<p>The original data contains the following features (description from <a target="_blank" rel="nofollow" href="http://archive.ics.uci.edu/ml/datasets/Online+Retail">UC Irvine’s machine learning repository</a>):</p>
<ul>
<li>InvoiceNo: Invoice number uniquely assigned to each transaction. If this code starts with letter ‘c’, it indicates a cancellation.</li>
<li>StockCode: Product (item) code uniquely assigned to each distinct product.</li>
<li>Description: Product (item) name.</li>
<li>Quantity: The quantities of each product (item) per transaction.</li>
<li>InvoiceDate: Invoice Date and time, the day and time when each transaction was generated.</li>
<li>UnitPrice: Unit price. Product price per unit in sterling.</li>
<li>CustomerID: Customer number uniquely assigned to each customer.</li>
<li>Country: Country name. The name of the country where each customer resides.</li>
</ul>
<p>Because read_csv generates a tibble (<a target="_blank" rel="nofollow" href="http://r4ds.had.co.nz/">a specific dataframe class</a>) and is part of the tidyverse, we can directly create a few additional columns:</p>
<ul>
<li>day: Invoice date, the day when each transaction was generated.</li>
<li>time: Invoice time, the time when each transaction was generated.</li>
<li>month: The month when each transaction was generated.</li>
<li>income: The amount of income generated from each transaction (Quantity * UnitPrice), negative in case of returns</li>
<li>income_return: Description of whether a transaction generated income or loss (i.e. purchase or return)</li>
</ul>
<div>
<pre><code><span>retail</span><span> </span><span>&lt;-</span><span> </span><span>read_csv</span><span>(</span><span>&quot;OnlineRetail.csv&quot;</span><span>,</span><span>
</span><span>col_types</span><span> </span><span>=</span><span> </span><span>cols</span><span>(</span><span>
</span><span>InvoiceNo</span><span> </span><span>=</span><span> </span><span>col_character</span><span>(),</span><span>
</span><span>StockCode</span><span> </span><span>=</span><span> </span><span>col_character</span><span>(),</span><span>
</span><span>Description</span><span> </span><span>=</span><span> </span><span>col_character</span><span>(),</span><span>
</span><span>Quantity</span><span> </span><span>=</span><span> </span><span>col_integer</span><span>(),</span><span>
</span><span>InvoiceDate</span><span> </span><span>=</span><span> </span><span>col_datetime</span><span>(</span><span>&quot;%m/%d/%Y %H:%M&quot;</span><span>),</span><span>
</span><span>UnitPrice</span><span> </span><span>=</span><span> </span><span>col_double</span><span>(),</span><span>
</span><span>CustomerID</span><span> </span><span>=</span><span> </span><span>col_integer</span><span>(),</span><span>
</span><span>Country</span><span> </span><span>=</span><span> </span><span>col_character</span><span>()</span><span>
</span><span>))</span><span> </span><span>%>%</span><span>
</span><span>mutate</span><span>(</span><span>day</span><span> </span><span>=</span><span> </span><span>parse_date</span><span>(</span><span>format</span><span>(</span><span>InvoiceDate</span><span>,</span><span> </span><span>&quot;%Y-%m-%d&quot;</span><span>)),</span><span>
</span><span>day_of_week</span><span> </span><span>=</span><span> </span><span>wday</span><span>(</span><span>day</span><span>,</span><span> </span><span>label</span><span> </span><span>=</span><span> </span><span>TRUE</span><span>),</span><span>
</span><span>time</span><span> </span><span>=</span><span> </span><span>parse_time</span><span>(</span><span>format</span><span>(</span><span>InvoiceDate</span><span>,</span><span> </span><span>&quot;%H:%M&quot;</span><span>)),</span><span>
</span><span>month</span><span> </span><span>=</span><span> </span><span>format</span><span>(</span><span>InvoiceDate</span><span>,</span><span> </span><span>&quot;%m&quot;</span><span>),</span><span>
</span><span>income</span><span> </span><span>=</span><span> </span><span>Quantity</span><span> </span><span>*</span><span> </span><span>UnitPrice</span><span>,</span><span>
</span><span>income_return</span><span> </span><span>=</span><span> </span><span>ifelse</span><span>(</span><span>Quantity</span><span> </span><span>></span><span> </span><span>0</span><span>,</span><span> </span><span>&quot;income&quot;</span><span>,</span><span> </span><span>&quot;return&quot;</span><span>))</span><span>
</span></code></pre>
</div>
<p></p>
<h2>Exploratory Data Analysis (EDA)</h2>
<p>In order to decide, which features to use in the final dataset for modeling, we want to get a feel for our data. And a good way to do this, is by creating different visualizations. It also helps with assessing your models later on, because to closer you are acquainted with the data’s properties, the better you’ll be able to pick up on things that might have gone wrong in your analysis (think of it as a kind of sanity-check for your data).</p>
<p></p>
<h3>Transactions by country</h3>
<p>The online retailer is UK-based, but its customers come from all over the world. However, the plots below tell us very quickly that the main customer base is from the UK, followed by Germany and France.</p>
<div>
<pre><code><span>p</span><span>1</span><span> </span><span>&lt;-</span><span> </span><span>retail</span><span> </span><span>%>%</span><span>
</span><span>filter</span><span>(</span><span>Country</span><span> </span><span>==</span><span> </span><span>&quot;United Kingdom&quot;</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>ggplot</span><span>(</span><span>aes</span><span>(</span><span>x</span><span> </span><span>=</span><span> </span><span>Country</span><span>,</span><span> </span><span>fill</span><span> </span><span>=</span><span> </span><span>income_return</span><span>))</span><span> </span><span>+</span><span>
</span><span>geom_bar</span><span>(</span><span>alpha</span><span> </span><span>=</span><span> </span><span>0.8</span><span>)</span><span> </span><span>+</span><span>
</span><span>scale_fill_manual</span><span>(</span><span>values</span><span> </span><span>=</span><span> </span><span>palette_light</span><span>())</span><span> </span><span>+</span><span>
</span><span>theme_tq</span><span>()</span><span> </span><span>+</span><span>
</span><span>theme</span><span>(</span><span>axis.text.x</span><span> </span><span>=</span><span> </span><span>element_text</span><span>(</span><span>angle</span><span> </span><span>=</span><span> </span><span>45</span><span>,</span><span> </span><span>vjust</span><span> </span><span>=</span><span> </span><span>1</span><span>,</span><span> </span><span>hjust</span><span> </span><span>=</span><span> </span><span>1</span><span>))</span><span> </span><span>+</span><span>
</span><span>guides</span><span>(</span><span>fill</span><span> </span><span>=</span><span> </span><span>FALSE</span><span>)</span><span> </span><span>+</span><span>
</span><span>labs</span><span>(</span><span>x</span><span> </span><span>=</span><span> </span><span>&quot;&quot;</span><span>)</span><span>
</span><span>p</span><span>2</span><span> </span><span>&lt;-</span><span> </span><span>retail</span><span> </span><span>%>%</span><span>
</span><span>filter</span><span>(</span><span>Country</span><span> </span><span>!=</span><span> </span><span>&quot;United Kingdom&quot;</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>ggplot</span><span>(</span><span>aes</span><span>(</span><span>x</span><span> </span><span>=</span><span> </span><span>Country</span><span>,</span><span> </span><span>fill</span><span> </span><span>=</span><span> </span><span>income_return</span><span>))</span><span> </span><span>+</span><span>
</span><span>geom_bar</span><span>(</span><span>alpha</span><span> </span><span>=</span><span> </span><span>0.8</span><span>)</span><span> </span><span>+</span><span>
</span><span>scale_fill_manual</span><span>(</span><span>values</span><span> </span><span>=</span><span> </span><span>palette_light</span><span>())</span><span> </span><span>+</span><span>
</span><span>theme_tq</span><span>()</span><span> </span><span>+</span><span>
</span><span>theme</span><span>(</span><span>legend.position</span><span> </span><span>=</span><span> </span><span>&quot;right&quot;</span><span>)</span><span> </span><span>+</span><span>
</span><span>theme</span><span>(</span><span>axis.text.x</span><span> </span><span>=</span><span> </span><span>element_text</span><span>(</span><span>angle</span><span> </span><span>=</span><span> </span><span>45</span><span>,</span><span> </span><span>vjust</span><span> </span><span>=</span><span> </span><span>1</span><span>,</span><span> </span><span>hjust</span><span> </span><span>=</span><span> </span><span>1</span><span>))</span><span> </span><span>+</span><span>
</span><span>labs</span><span>(</span><span>x</span><span> </span><span>=</span><span> </span><span>&quot;&quot;</span><span>,</span><span>
</span><span>fill</span><span> </span><span>=</span><span> </span><span>&quot;&quot;</span><span>)</span><span>
</span><span>grid.arrange</span><span>(</span><span>p</span><span>1</span><span>,</span><span> </span><span>p</span><span>2</span><span>,</span><span> </span><span>widths</span><span> </span><span>=</span><span> </span><span>c</span><span>(</span><span>0.2</span><span>,</span><span> </span><span>0.8</span><span>))</span><span>
</span></code></pre>
</div>
<p><img alt="" src="https://i2.wp.com/shiring.github.io/forecasting/2017/05/28/retail_forecasting_part1_files/figure-markdown_github/unnamed-chunk-5-1.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p></p>
<h3>Transactions over time</h3>
<p>To get an idea of the number of transactions over time, we can use a frequency polygon. Here, we can see that the number purchases slightly increased during the last two months of recording, while the number of returns remained relatively stable.</p>
<div>
<pre><code><span>retail</span><span> </span><span>%>%</span><span>
</span><span>ggplot</span><span>(</span><span>aes</span><span>(</span><span>x</span><span> </span><span>=</span><span> </span><span>day</span><span>,</span><span> </span><span>color</span><span> </span><span>=</span><span> </span><span>income_return</span><span>))</span><span> </span><span>+</span><span>
</span><span>facet_grid</span><span>(</span><span>income_return</span><span> </span><span>~</span><span> </span><span>.</span><span>,</span><span> </span><span>scales</span><span> </span><span>=</span><span> </span><span>&quot;free&quot;</span><span>)</span><span> </span><span>+</span><span>
</span><span>geom_freqpoly</span><span>(</span><span>bins</span><span> </span><span>=</span><span> </span><span>100</span><span>,</span><span> </span><span>size</span><span> </span><span>=</span><span> </span><span>1</span><span>,</span><span> </span><span>alpha</span><span> </span><span>=</span><span> </span><span>0.8</span><span>)</span><span> </span><span>+</span><span>
</span><span>scale_color_manual</span><span>(</span><span>values</span><span> </span><span>=</span><span> </span><span>palette_light</span><span>())</span><span> </span><span>+</span><span>
</span><span>theme_tq</span><span>()</span><span> </span><span>+</span><span>
</span><span>guides</span><span>(</span><span>color</span><span> </span><span>=</span><span> </span><span>FALSE</span><span>)</span><span> </span><span>+</span><span>
</span><span>labs</span><span>(</span><span>title</span><span> </span><span>=</span><span> </span><span>&quot;Number of purchases/returns over time&quot;</span><span>,</span><span>
</span><span>x</span><span> </span><span>=</span><span> </span><span>&quot;&quot;</span><span>)</span><span>
</span></code></pre>
</div>
<p><img alt="" src="https://i1.wp.com/shiring.github.io/forecasting/2017/05/28/retail_forecasting_part1_files/figure-markdown_github/unnamed-chunk-6-1.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p></p>
<p>Because the number of returns is much smaller than the number of purchases, it is difficult to visualize and compare them in the same plot. While above, I split them into two facets with free scales, we can also compare the density of values. From this plot, we can more easily see the relationship between purchases and returns over time: except for the last month, the proportion of both remained relatively stable.</p>
<div>
<pre><code><span>retail</span><span> </span><span>%>%</span><span>
</span><span>ggplot</span><span>(</span><span>aes</span><span>(</span><span>x</span><span> </span><span>=</span><span> </span><span>day</span><span>,</span><span> </span><span>y</span><span> </span><span>=</span><span> </span><span>..density..</span><span>,</span><span> </span><span>color</span><span> </span><span>=</span><span> </span><span>income_return</span><span>))</span><span> </span><span>+</span><span>
</span><span>geom_freqpoly</span><span>(</span><span>size</span><span> </span><span>=</span><span> </span><span>1</span><span>,</span><span> </span><span>alpha</span><span> </span><span>=</span><span> </span><span>0.8</span><span>,</span><span> </span><span>bins</span><span> </span><span>=</span><span> </span><span>100</span><span>)</span><span> </span><span>+</span><span>
</span><span>scale_color_manual</span><span>(</span><span>values</span><span> </span><span>=</span><span> </span><span>palette_light</span><span>())</span><span> </span><span>+</span><span>
</span><span>theme_tq</span><span>()</span><span> </span><span>+</span><span>
</span><span>labs</span><span>(</span><span>title</span><span> </span><span>=</span><span> </span><span>&quot;Density of purchases/returns over time&quot;</span><span>,</span><span>
</span><span>x</span><span> </span><span>=</span><span> </span><span>&quot;&quot;</span><span>,</span><span>
</span><span>color</span><span> </span><span>=</span><span> </span><span>&quot;&quot;</span><span>)</span><span>
</span></code></pre>
</div>
<p><img alt="" src="https://i2.wp.com/shiring.github.io/forecasting/2017/05/28/retail_forecasting_part1_files/figure-markdown_github/unnamed-chunk-7-1.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p></p>
<h3>Income/loss from transactions</h3>
<p>Let’s look at the income/loss from transactions over time. Here, we plot the sum of income and losses for each day. The income seems to increase slightly during the last month, while losses remained more stable. The only severe outlier is the last day.</p>
<div>
<pre><code><span>retail</span><span> </span><span>%>%</span><span>
</span><span>group_by</span><span>(</span><span>day</span><span>,</span><span> </span><span>income_return</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>summarise</span><span>(</span><span>sum_income</span><span> </span><span>=</span><span> </span><span>sum</span><span>(</span><span>income</span><span>))</span><span> </span><span>%>%</span><span>
</span><span>ggplot</span><span>(</span><span>aes</span><span>(</span><span>x</span><span> </span><span>=</span><span> </span><span>day</span><span>,</span><span> </span><span>y</span><span> </span><span>=</span><span> </span><span>sum_income</span><span>,</span><span> </span><span>color</span><span> </span><span>=</span><span> </span><span>income_return</span><span>))</span><span> </span><span>+</span><span>
</span><span>facet_grid</span><span>(</span><span>income_return</span><span> </span><span>~</span><span> </span><span>.</span><span>,</span><span> </span><span>scales</span><span> </span><span>=</span><span> </span><span>&quot;free&quot;</span><span>)</span><span> </span><span>+</span><span>
</span><span>geom_ref_line</span><span>(</span><span>h</span><span> </span><span>=</span><span> </span><span>0</span><span>,</span><span> </span><span>colour</span><span> </span><span>=</span><span> </span><span>&quot;grey&quot;</span><span>)</span><span> </span><span>+</span><span>
</span><span>geom_line</span><span>(</span><span>size</span><span> </span><span>=</span><span> </span><span>1</span><span>,</span><span> </span><span>alpha</span><span> </span><span>=</span><span> </span><span>0.8</span><span>)</span><span> </span><span>+</span><span>
</span><span>scale_color_manual</span><span>(</span><span>values</span><span> </span><span>=</span><span> </span><span>palette_light</span><span>())</span><span> </span><span>+</span><span>
</span><span>theme_tq</span><span>()</span><span> </span><span>+</span><span>
</span><span>guides</span><span>(</span><span>color</span><span> </span><span>=</span><span> </span><span>FALSE</span><span>)</span><span> </span><span>+</span><span>
</span><span>labs</span><span>(</span><span>title</span><span> </span><span>=</span><span> </span><span>&quot;Income/loss from transactions per day&quot;</span><span>,</span><span>
</span><span>x</span><span> </span><span>=</span><span> </span><span>&quot;&quot;</span><span>,</span><span>
</span><span>y</span><span> </span><span>=</span><span> </span><span>&quot;sum of income/losses&quot;</span><span>,</span><span>
</span><span>color</span><span> </span><span>=</span><span> </span><span>&quot;&quot;</span><span>)</span><span>
</span></code></pre>
</div>
<p><img alt="" src="https://i2.wp.com/shiring.github.io/forecasting/2017/05/28/retail_forecasting_part1_files/figure-markdown_github/unnamed-chunk-8-1.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p></p>
<p>We can also look at the sum of income and losses according to time of day of the transaction. Not surprisingly, transactions happen mostly during business hours.</p>
<div>
<pre><code><span>retail</span><span> </span><span>%>%</span><span>
</span><span>group_by</span><span>(</span><span>time</span><span>,</span><span> </span><span>income_return</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>summarise</span><span>(</span><span>sum_income</span><span> </span><span>=</span><span> </span><span>sum</span><span>(</span><span>income</span><span>))</span><span> </span><span>%>%</span><span>
</span><span>ggplot</span><span>(</span><span>aes</span><span>(</span><span>x</span><span> </span><span>=</span><span> </span><span>time</span><span>,</span><span> </span><span>y</span><span> </span><span>=</span><span> </span><span>sum_income</span><span>,</span><span> </span><span>color</span><span> </span><span>=</span><span> </span><span>income_return</span><span>))</span><span> </span><span>+</span><span>
</span><span>facet_grid</span><span>(</span><span>income_return</span><span> </span><span>~</span><span> </span><span>.</span><span>,</span><span> </span><span>scales</span><span> </span><span>=</span><span> </span><span>&quot;free&quot;</span><span>)</span><span> </span><span>+</span><span>
</span><span>geom_ref_line</span><span>(</span><span>h</span><span> </span><span>=</span><span> </span><span>0</span><span>,</span><span> </span><span>colour</span><span> </span><span>=</span><span> </span><span>&quot;grey&quot;</span><span>)</span><span> </span><span>+</span><span>
</span><span>geom_line</span><span>(</span><span>size</span><span> </span><span>=</span><span> </span><span>1</span><span>,</span><span> </span><span>alpha</span><span> </span><span>=</span><span> </span><span>0.8</span><span>)</span><span> </span><span>+</span><span>
</span><span>scale_color_manual</span><span>(</span><span>values</span><span> </span><span>=</span><span> </span><span>palette_light</span><span>())</span><span> </span><span>+</span><span>
</span><span>theme_tq</span><span>()</span><span> </span><span>+</span><span>
</span><span>guides</span><span>(</span><span>color</span><span> </span><span>=</span><span> </span><span>FALSE</span><span>)</span><span> </span><span>+</span><span>
</span><span>labs</span><span>(</span><span>title</span><span> </span><span>=</span><span> </span><span>&quot;Income from purchases/returns per time of day&quot;</span><span>,</span><span>
</span><span>x</span><span> </span><span>=</span><span> </span><span>&quot;time of day&quot;</span><span>,</span><span>
</span><span>y</span><span> </span><span>=</span><span> </span><span>&quot;sum of income/losses&quot;</span><span>,</span><span>
</span><span>color</span><span> </span><span>=</span><span> </span><span>&quot;&quot;</span><span>)</span><span>
</span></code></pre>
</div>
<p><img alt="" src="https://i2.wp.com/shiring.github.io/forecasting/2017/05/28/retail_forecasting_part1_files/figure-markdown_github/unnamed-chunk-9-1.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p>Here, we again see the two extreme outliers. Let’s look at them in the dataset. This purchase of 80995 paper craft birdies might have been a mistake, because we can see that the same customer who bought them at 09:15 cancelled the order only 15 minutes later and didn’t order a smaller number either.</p>
<div>
<pre><code><span>retail</span><span> </span><span>%>%</span><span>
</span><span>filter</span><span>(</span><span>day</span><span> </span><span>==</span><span> </span><span>&quot;2011-12-09&quot;</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>arrange</span><span>(</span><span>-</span><span>Quantity</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>.</span><span>[</span><span>1</span><span>:</span><span>3</span><span>,</span><span> </span><span>]</span><span>
</span></code></pre>
</div>
<div>
<pre><code>## # A tibble: 3 x 14
##   InvoiceNo StockCode                         Description Quantity
##       </code></pre></div>