Udacity P4 EDA, Rossman Store Sales
Andres Felipe Gonzalez
November 26, 2015
This is the exploratory data analysis for the Kaggle competition Rossman Store Sales. The data can be found in the link: https://www.kaggle.com/c/rossmann-store-sales/data
The data contain information of 1115 stores for each day starting in 2013 until July of 2015. They provide three data sets, train, test and additional data for all the stores.
The variables provide in the train set are
## Classes 'data.table' and 'data.frame': 1017209 obs. of 9 variables:
## $ Store : int 1 2 3 4 5 6 7 8 9 10 ...
## $ DayOfWeek : int 5 5 5 5 5 5 5 5 5 5 ...
## $ Date : chr "2015-07-31" "2015-07-31" "2015-07-31" "2015-07-31" ...
## $ Sales : int 5263 6064 8314 13995 4822 5651 15344 8492 8565 7185 ...
## $ Customers : int 555 625 821 1498 559 589 1414 833 687 681 ...
## $ Open : int 1 1 1 1 1 1 1 1 1 1 ...
## $ Promo : int 1 1 1 1 1 1 1 1 1 1 ...
## $ StateHoliday : chr "0" "0" "0" "0" ...
## $ SchoolHoliday: chr "1" "1" "1" "1" ...
## - attr(*, ".internal.selfref")=<externalptr>In the training set we have information of Sales and Customers with other categorical variables such as the day of the week, a dummy variable indicating if the store was open (Open = 1) or close (Open = 0).
In the test set we have information of the following variables
## Classes 'data.table' and 'data.frame': 41088 obs. of 8 variables:
## $ Id : int 1 2 3 4 5 6 7 8 9 10 ...
## $ Store : int 1 3 7 8 9 10 11 12 13 14 ...
## $ DayOfWeek : int 4 4 4 4 4 4 4 4 4 4 ...
## $ Date : chr "2015-09-17" "2015-09-17" "2015-09-17" "2015-09-17" ...
## $ Open : int 1 1 1 1 1 1 1 1 1 1 ...
## $ Promo : int 1 1 1 1 1 1 1 1 1 1 ...
## $ StateHoliday : chr "0" "0" "0" "0" ...
## $ SchoolHoliday: chr "0" "0" "0" "0" ...
## - attr(*, ".internal.selfref")=<externalptr>Notice that Customers and Sales are missing from the test set. In other words we can’t use information of customers to forecast Sales, without making an other model to forecast customers.
To make a first overview of the data, I want to explore the variable Store in the train and test set, the periodicity of the variable Date and finally Start looking to the variable Sales with some histograms.
Variable Store:
## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 1.0 280.0 558.0 558.4 838.0 1115.0## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 1.0 279.8 553.5 555.9 832.2 1115.0It seems that not all stores in the training data appear in the test set. The min value for Store is 1 and max 1115, which means there are 1115 different Stores in the train set. But, the first quantile, median, mean and third quantile differ from the training and test set.
## [1] "Numeric: lengths (1115, 856) differ"There are 1115 stores in the training set and only 856 different stores in the test set.
Now it is important to know the periodicity of the Date variable, in other words if we have information daily, monthly or in other time frame.
## Min. 1st Qu. Median
## "2013-01-01 00:00:00" "2013-08-17 00:00:00" "2014-04-02 00:00:00"
## Mean 3rd Qu. Max.
## "2014-04-11 01:30:42" "2014-12-12 00:00:00" "2015-07-31 00:00:00"## [1] "2015-07-31 COT" "2015-07-30 COT" "2015-07-29 COT" "2015-07-28 COT"
## [5] "2015-07-27 COT" "2015-07-26 COT" "2015-07-25 COT" "2015-07-24 COT"
## [9] "2015-07-23 COT" "2015-07-22 COT"The data is reported daily, starting in January 1 of 2013 and ending in July 31 of 2015. This mean we should have 942 data points for each Store in the training set.
For the test set
## Min. 1st Qu. Median
## "2015-08-01 00:00:00" "2015-08-12 18:00:00" "2015-08-24 12:00:00"
## Mean 3rd Qu. Max.
## "2015-08-24 12:00:00" "2015-09-05 06:00:00" "2015-09-17 00:00:00"## [1] "2015-09-17 COT" "2015-09-16 COT" "2015-09-15 COT" "2015-09-14 COT"
## [5] "2015-09-13 COT" "2015-09-12 COT" "2015-09-11 COT" "2015-09-10 COT"
## [9] "2015-09-09 COT" "2015-09-08 COT"In the test set the data starts in August 1 of 2015 and ends in September 17 of 2015, so we have to forecast 48 periods ahead for each store.
As notice by this thread: “https://www.kaggle.com/nsecord/rossmann-store-sales/filling-gaps-in-the-training-set” One would suppose that there are 942 rows for each of the 1115 stores, but this is not the case. The data contain 1017209 observations, which is not equal to 1.0503310^{6}
This is a valuable lesson, DON’T ASSUME ANYTHING. To see this in a graph, we can group the data by Date, and compute the number of observations in each day. If all dates have the same amount of data points, then the graph would be a horizontal line in 1115.
## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 935 1115 1115 1085 1115 1115
It is important to know the frequency of the Data, specially if we want to make a time series model to forecast Sales. The first assumption of a time series model is that the observation are sampled in an equally space interval of time. For some stores in this dataset, that assumption is violated.
In the test set, we have to forecast a contiguous interval of time:
## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 856 856 856 856 856 856Univariate Plots Section
## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 0 3727 5744 5774 7856 41550
The distribution has a big density on 0 and long tails. There are over 150.000 observations with sales equal to 0. The maximum value for Sales is 41.550 EU, which is a very high value in relationship with the third quantile.
A common thing to do when we have distribution with big tails is to apply logarithm to the data. The logarithm transformation is useful to make the data look normal distributed, but we have the problem that a lot of observations are zero, and the logarithm of zero is undefined. It is better to exclude those observations or, make a different transformation like log(Sales + 1).
Lets see the histogram for the variable Sales, excluding observations equal to 0 and adjusting the x axis to see only observations bellow 3 times the inter quantile range.
## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 46 4859 6369 6956 8360 41550
The mean and the median differ slightly, the distribution is skewed to the right. Now the same histogram but transforming the data in a logarithm scale, removing observations equal to 0.
## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 3.850 8.489 8.759 8.758 9.031 10.630
The previous histogram was scale alone the x-axis because the distribution has bigger tails. So Once again I remove the observations below the first quantile, minus 3 times the IQR and the observations above the third quantile plus 3 times the IQR. With this transformation, the mean and the median of the distribution are almost the same and the data look log-normal distributed.
Customers
## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 0.0 405.0 609.0 633.1 837.0 7388.0
We can make the same observations as before, there is high density in 0.
## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 8.0 519.0 676.0 762.8 893.0 7388.0
And now transforming the x axis to a logarithm scale:
## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 1.386 6.254 6.518 6.535 6.796 8.908
The mean and median are very close to each others with this transformation.
Bivariate Plots Section
Sales by Date
The plot from above isn’t very helpful to see the distribution of sales across time. It is better to plot only one store:
It is hard to see a tendency or seasonality effects because of overplotting. Maybe shrinking the time window (scale the x axis) to show only observations of the year 2015 can help:
There are a lot of observations with Sales equal to 0 and it looks like those observations appear on Sundays. For what I can see, but it is prematurely to tell, Saturdays are days with high Sales, also for this store the first and last days of the month have higher sales.
But this is only for one store, the idea is do this kind of plot for all the stores and hopefully find some common patterns in the relationship of Sales and Date.
To do this, I’m going to remove Sales with values of 0, scale the x-axis to show only observations from 2015 and add some summary statistics to the plot.
Even with the summary statistics this plot isn’t very helpful to spot interesting trends or patterns. The last thing I’m going to try is to add a smooth line to the plot:
This is an example of overplotting, and the smooth line added didn’t help to identify interesting patterns in the data.
I’m going to analyse Sales by date with a different approach. I’m going to create three categorical variables derived from the Date variable. The three variables are Day, Month, and Year.
## Classes 'data.table' and 'data.frame': 1017209 obs. of 4 variables:
## $ DayOfWeek: Factor w/ 7 levels "Monday","Tuesday",..: 5 5 5 5 5 5 5 5 5 5 ...
## $ Day : Factor w/ 31 levels "01","02","03",..: 31 31 31 31 31 31 31 31 31 31 ...
## $ Month : Factor w/ 12 levels "01","02","03",..: 7 7 7 7 7 7 7 7 7 7 ...
## $ Year : Factor w/ 3 levels "2013","2014",..: 3 3 3 3 3 3 3 3 3 3 ...
## - attr(*, ".internal.selfref")=<externalptr>It would be interesting to know if the distribution of Sales is different over time. To do this, I’m going to make two plots for each new time variable I had recently created. The first plot is a density plot and the second a boxplot. For the boxplots, the y axis is scale to show only observations below 3 times the IQR.
The density plots and the boxplot are very similar to each others. It appears that over years the distribution of sales is very similar. Sales are increasing subtly over years.
The density of sales divided by Month show that in one month in which sales belong to a different distribution. This distribution looks greater than the others. The boxplot by Month show clearly that observations from Month 12 have a bigger median and the size of the box is considerably bigger than the others.
Density of sales by Day of the month have a wide range of differences. The density plots show that there are different distributions across days, but it is not helpful to see which day has a greater or lower distribution. Contrary the boxplot show clearly that some days have bigger distributions than others. In fact you can see a tendency in the medians of Sales by Day of the month. The first days have bigger medians and they start to decrease until day 11 and then start to grow smoothly until day 18, then they start to decrease again until day 24 or 25 and then they increase again. This effect is known as seasonality.
Finally, we can combine the boxplot of Sales by Year and Sales by Month into a single one: 
This boxplot shows the evolution of Sales for each Month in the three years available of data. In this boxplot we can see a subtle difference in Sales for each year. The difference of Sales by year depends on the Month of the year. For example, in January, the difference of Sales in 2013 and 2015 are bigger than in July.
Multivariate Plots Section
Sales by Promo and Date
When a Store has a promotion in one day, the variable Promo takes the value of 1 and 0 if there was no promotion in that day.
It is more likely to have bigger sales when there is a promotion in the store, according to the distribution. This result suggest that if we make the boxplot of Sales by Day and divide the boxplot by the categorical variable Promo, the median of the days with promo would be higher:
And what about DayOfWeek?
The above plot reveal various things. We can easily see that most sales are 0 in Sunday (DayOfWeek = 7) and there are no promo in Saturdays. It is worth noting how all days with promo have Sales greater than those days with no promo. The effect of Promo in sales differ depending on the day of the week, for example it is clear that the median of sales on Mondays with a promotion is bigger than the median of sales on Fridays with a promotion.
There are three variables that we haven’t analysed yet, Open, StateHoliday and SchoolHoliday.
## 0 a b c
## 986159 20260 6690 4100## 0 1
## 835488 181721## 0 1
## 172817 844392Probably if a Store is close there should be no sales in that day:
Does it happen the same with StateHoliday?
It looks that most of the stores are close in holidays, however there are a lot of points with sales different from 0 which means the stores aren’t close those days. Let’s look to Sales different from 0 when there is a State Holiday:
When a day is a state holiday, the density of Sales show different patterns. It seems that higher values for Sales are more likely to appear during holiday. This is also true for Small values of Sales. The boxplot show that the size of the box change considerably with the type of the StateHoliday. StateHoliday = b, has the biggest box.
Contrary to StateHoliday, Stores appear to be open when a day is a school holiday.
The density and the boxplots didn’t show any suggestion that the distribution of Sales is different when there is a school holiday. Maybe SchoolHoliday has an impact if the day is a State Holiday too:
This was a surprise, the higher sales are reported when there is a state holiday “b” and no school holiday.
## SchoolHoly
## StateHoly 0 1
## 0 819949 166210
## a 15055 5205
## b 484 6206
## c 0 4100More Data
The Rossman competitions include an additional file, that has specific information of the stores, such as Assortment, type of Store, competition distance and other variables.
## Classes 'data.table' and 'data.frame': 1115 obs. of 10 variables:
## $ Store : int 1 2 3 4 5 6 7 8 9 10 ...
## $ StoreType : chr "c" "a" "a" "c" ...
## $ Assortment : chr "a" "a" "a" "c" ...
## $ CompetitionDistance : int 1270 570 14130 620 29910 310 24000 7520 2030 3160 ...
## $ CompetitionOpenSinceMonth: int 9 11 12 9 4 12 4 10 8 9 ...
## $ CompetitionOpenSinceYear : int 2008 2007 2006 2009 2015 2013 2013 2014 2000 2009 ...
## $ Promo2 : int 0 1 1 0 0 0 0 0 0 0 ...
## $ Promo2SinceWeek : int NA 13 14 NA NA NA NA NA NA NA ...
## $ Promo2SinceYear : int NA 2010 2011 NA NA NA NA NA NA NA ...
## $ PromoInterval : chr "" "Jan,Apr,Jul,Oct" "Jan,Apr,Jul,Oct" "" ...
## - attr(*, ".internal.selfref")=<externalptr>I’m going to start with the analysis of StoreType:
## Store Store_Type
## Min. : 1.0 a:602
## 1st Qu.: 279.5 b: 17
## Median : 558.0 c:148
## Mean : 558.0 d:348
## 3rd Qu.: 836.5
## Max. :1115.0
There is a clear difference in the store type b, the density suggest this type of stores have greater sales in general than the other types of stores.
I extract the next plot from “Christian Thiele” in his script, https://www.kaggle.com/thie1e/rossmann-store-sales/exploratory-analysis-rossmann :
This plot shows a prominent difference between Sales of StoreType b an the other stores type. It is interesting how StoreType “a” was very similar to StoreType “c”" until the year 2015, where this type of stores move bellow the levels of Sales of StoreType “c”.
Probably the effects of DayOfWeek and Promo differ from the type of store:
Store type a, c and d are very similar to each others, but store “a” has a median sales lower on Saturday than store type c and d, when there are no promotion. Also I notice that the length of the bar for store type “a” is bigger than the others in almost every day of the week when there is no promo.
Maybe StoreType is also related to the variable Assortment:
## a b c
## 537445 8294 471470## StoreType
## Assortment a b c d
## a 381 7 77 128
## b 0 9 0 0
## c 221 1 71 220As I expected, there is a relationship with store type and assortment. Let’s combine those variables in a density plot:
What happen if we take the mean of sales, by Date, store type and assortment?
Finally I want to see the relationship between sales and competition distance:
## Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
## 20.0 717.5 2325.0 5405.0 6882.0 75860.0 3
Correlation with sales. In this time I’m going to work with a sub sample because there are a lot of observations and it will be hard to find some relationship this way. I’m going to take a sub sample of 15000 observations from the original data.
## Warning: Removed 35 rows containing missing values (geom_point).
The two red lines in the graph correspond to the mean of competition distance (vertical line) and the mean of sales (horizontal line).
Maybe the correlation between these two variables can be more clear if we aggregate the sales by Store:
It is hard to tell from this plot a relationship between competition distance and sales aggregated over the three years.
##
## Pearson's product-moment correlation
##
## data: aggSales and CompetitionDistance
## t = -3.7811, df = 14955, p-value = 0.0001567
## alternative hypothesis: true correlation is not equal to 0
## 95 percent confidence interval:
## -0.04690762 -0.01488571
## sample estimates:
## cor
## -0.0309046The correlation test reveal a small negative correlation between aggregated sales and Competition distance. I want to see if by grouping the stores by assortment and store type the relationship between competition distance and aggregated sales changes:
The correlations in numbers!
## StoreType Assortment corrByStyAsrt
## 1: a a -0.033193095
## 2: d c -0.027648970
## 3: c c -0.008892225
## 4: a c -0.064613378
## 5: c a 0.008337798
## 6: d a 0.045320946
## 7: b a 0.001001076
## 8: b b -0.653609609
## 9: b c NAThe highest correlation is for store type “b” and assortment “b”.
Removing Seasonality
I’m going to remove the seasonality found by subtracting to the logarithm of Sales, the median by day of the Month.
Final Plots and Summary
Plot One
Description One
Without the log transformation the data has a distribution with long tails and skewed to the right. The mean and the median differ considerable. When we applied the log transformation to sales, the mean and the median become the same, the distribution still have long tails, and visually it looks skewed to the left.
Plot Two
Description Two
The effect of a Promotion in Sales vary depending on the day of the month and on the day of the week that the promotion is running. This plot reveals the difference between days with promotion and with no promotion. It also shows how the seasonality by day of the month remains when we control by the variable Promo. One of the most important things about this plot is that Sales are different depending in the day of the month and on the day of the week that a promotion was running.
Plot Three
Description Three
You can see how sales vary during the days of the month, the average sales is different depending on the day of the month an effect known as seasonality. To remove this seasonality, it was subtracted from the logarithm of sales, the median sales for each day of the month. We see in the chart above, the line that represents the median in the boxplot, changes over time, while in the graph below the median is centered at zero for all days with exception of day 1. With this transformation we were able to remove most of the seasonality in the series.
Reflection
The Rossman Store Sales is a big dataset with more than one million rows and 16 variables, I analyze most of them and create more to have a total of 33 variables.
I had some struggles with the data when trying to visualize all the observations. For example, the point plot of Sales by Date took to much time to run and the plot didn’t reveal any trend or relationship because of overlplotting. In that case I try several option like taking a sub sample, adding summaries statistics and using a smooth line without good results.
Using densities and boxplots were crucial in the analysis of the data. It was prove successful to divide the sales by time, day of the week, month and year and split the boxplot or density with categorical variables such as promo or Store Type. Another important thing was to scale the axis to leave “outliers” because it makes easy to reveal difference across groups in the boxplots.