rossman-store-sales-report

Data Description

You are provided with historical sales data for 1,115 Rossmann stores. The task is to forecast the “Sales” column for the test set. Note that some stores in the dataset were temporarily closed for refurbishment.

2.1. Import library

library(data.table)
library(forecast)
library(lubridate) # 날짜 라이브러리
library(zoo) # 날짜 라이브러리
library(ggplot2) # 시각화 라이브러리
library(DescTools) # 통계 요약 라이브러리
library(sqldf) # sql 라이브러리
library(dplyr) # 전처리 라이브러리
library(knitr) # 출판 라이브러리
library(VIM) # 결측치 라이브러리

2.2. Load data

test <- fread("../../data/sql/rossman/test.csv")
train <- fread("../../data/sql/rossman/train.csv")
store <- fread("../../data/sql/rossman/store.csv")

2.3. Data overview

str(train)

## 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: int  1 1 1 1 1 1 1 1 1 1 ...
##  - attr(*, ".internal.selfref")=<externalptr>

glimpse(train)

## Observations: 1,017,209
## Variables: 9
## $ Store         <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 1...
## $ DayOfWeek     <int> 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, ...
## $ Date          <chr> "2015-07-31", "2015-07-31", "2015-07-31", "2015-...
## $ Sales         <int> 5263, 6064, 8314, 13995, 4822, 5651, 15344, 8492...
## $ Customers     <int> 555, 625, 821, 1498, 559, 589, 1414, 833, 687, 6...
## $ Open          <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, ...
## $ Promo         <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, ...
## $ StateHoliday  <chr> "0", "0", "0", "0", "0", "0", "0", "0", "0", "0"...
## $ SchoolHoliday <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 1, 1, 1, ...

summary(train)

##      Store          DayOfWeek         Date               Sales      
##  Min.   :   1.0   Min.   :1.000   Length:1017209     Min.   :    0  
##  1st Qu.: 280.0   1st Qu.:2.000   Class :character   1st Qu.: 3727  
##  Median : 558.0   Median :4.000   Mode  :character   Median : 5744  
##  Mean   : 558.4   Mean   :3.998                      Mean   : 5774  
##  3rd Qu.: 838.0   3rd Qu.:6.000                      3rd Qu.: 7856  
##  Max.   :1115.0   Max.   :7.000                      Max.   :41551  
##    Customers           Open            Promo        StateHoliday      
##  Min.   :   0.0   Min.   :0.0000   Min.   :0.0000   Length:1017209    
##  1st Qu.: 405.0   1st Qu.:1.0000   1st Qu.:0.0000   Class :character  
##  Median : 609.0   Median :1.0000   Median :0.0000   Mode  :character  
##  Mean   : 633.1   Mean   :0.8301   Mean   :0.3815                     
##  3rd Qu.: 837.0   3rd Qu.:1.0000   3rd Qu.:1.0000                     
##  Max.   :7388.0   Max.   :1.0000   Max.   :1.0000                     
##  SchoolHoliday   
##  Min.   :0.0000  
##  1st Qu.:0.0000  
##  Median :0.0000  
##  Mean   :0.1786  
##  3rd Qu.:0.0000  
##  Max.   :1.0000

head(train)

##    Store DayOfWeek       Date Sales Customers Open Promo StateHoliday
## 1:     1         5 2015-07-31  5263       555    1     1            0
## 2:     2         5 2015-07-31  6064       625    1     1            0
## 3:     3         5 2015-07-31  8314       821    1     1            0
## 4:     4         5 2015-07-31 13995      1498    1     1            0
## 5:     5         5 2015-07-31  4822       559    1     1            0
## 6:     6         5 2015-07-31  5651       589    1     1            0
##    SchoolHoliday
## 1:             1
## 2:             1
## 3:             1
## 4:             1
## 5:             1
## 6:             1

2.4. Reshape data type

train$Date <- as.Date(train$Date)
#train[, Date := as.Date(Date)]
#train$Date <- as.Date(train$Date)
train <- train[order(Date)]

3.1. Sales by total

total_sales <- sqldf("
select
    count(distinct store) as tot_store
    , sum(customers) as tot_cust
    , round(avg(customers), 2) as avg_cust
   
    , sum(sales) as tot_sales
    , round(avg(sales), 2) as avg_sales
   
    , min(date) as first_purchase_date
    , max(date) as last_purchase_date
from train;")

summary(total_sales)

##    tot_store       tot_cust           avg_cust       tot_sales         
##  Min.   :1115   Min.   :6.44e+08   Min.   :633.1   Min.   :5873180623  
##  1st Qu.:1115   1st Qu.:6.44e+08   1st Qu.:633.1   1st Qu.:5873180623  
##  Median :1115   Median :6.44e+08   Median :633.1   Median :5873180623  
##  Mean   :1115   Mean   :6.44e+08   Mean   :633.1   Mean   :5873180623  
##  3rd Qu.:1115   3rd Qu.:6.44e+08   3rd Qu.:633.1   3rd Qu.:5873180623  
##  Max.   :1115   Max.   :6.44e+08   Max.   :633.1   Max.   :5873180623  
##    avg_sales    first_purchase_date last_purchase_date
##  Min.   :5774   Min.   :15706       Min.   :16647     
##  1st Qu.:5774   1st Qu.:15706       1st Qu.:16647     
##  Median :5774   Median :15706       Median :16647     
##  Mean   :5774   Mean   :15706       Mean   :16647     
##  3rd Qu.:5774   3rd Qu.:15706       3rd Qu.:16647     
##  Max.   :5774   Max.   :15706       Max.   :16647

3.2. Sales by annual

annual_sales <- sqldf("
select
    substr(date, 1, 4) as year
    , count(distinct store) as tot_store
   
    , sum(customers) as tot_cust
    , round(avg(customers), 2) as avg_cust

    , count(*) as cnt_trade
   
    , sum(sales) as tot_sales
    , round(avg(sales), 2) as avg_sales
   
    , min(date) as first_purchase_date
    , max(date) as last_purchase_date
from train
group by 1;
")

summary(annual_sales)

##      year             tot_store       tot_cust          avg_cust    
##  Length:95          Min.   : 935   Min.   :2396743   Min.   :432.9  
##  Class :character   1st Qu.:1115   1st Qu.:6420442   1st Qu.:599.6  
##  Mode  :character   Median :1115   Median :6832654   Median :630.6  
##                     Mean   :1083   Mean   :6779387   Mean   :633.6  
##                     3rd Qu.:1115   3rd Qu.:7277780   3rd Qu.:669.2  
##                     Max.   :1115   Max.   :9525313   Max.   :854.3  
##    cnt_trade       tot_sales          avg_sales    first_purchase_date
##  Min.   : 4459   Min.   :20033838   Min.   :3875   Min.   :15706      
##  1st Qu.:11150   1st Qu.:57395018   1st Qu.:5300   1st Qu.:15935      
##  Median :11150   Median :61340641   Median :5730   Median :16170      
##  Mean   :10707   Mean   :61822954   Mean   :5776   Mean   :16170      
##  3rd Qu.:11150   3rd Qu.:66413716   3rd Qu.:6177   3rd Qu.:16405      
##  Max.   :11150   Max.   :95991386   Max.   :8609   Max.   :16640      
##  last_purchase_date
##  Min.   :15709     
##  1st Qu.:15944     
##  Median :16179     
##  Mean   :16179     
##  3rd Qu.:16414     
##  Max.   :16647

annual_sales$tot_sales <- as.numeric(annual_sales$tot_sales)

3.3. Sales by quarter

train$Quarter <- substr(quarters(as.Date(train$Date)), 2, 2)
table(train$Quarter)

## 
##      1      2      3      4 
## 301049 304395 223165 188600

quartely_sales <- sqldf("
select
    substr(date, 1, 4) as year
    , quarter
    , count(distinct store) as tot_store
    , sum(customers) as tot_cust
    , round(avg(customers), 2) as avg_cust
    
    , sum(sales) as tot_sales
    , round(avg(sales), 2) as avg_sales
from train
group by 1, 2;
 ")
summary(quartely_sales)

##      year             Quarter            tot_store       tot_cust      
##  Length:105         Length:105         Min.   : 935   Min.   : 531392  
##  Class :character   Class :character   1st Qu.:1115   1st Qu.:6028617  
##  Mode  :character   Mode  :character   Median :1115   Median :6696881  
##                                        Mean   :1081   Mean   :6133731  
##                                        3rd Qu.:1115   3rd Qu.:7176881  
##                                        Max.   :1115   Max.   :9525313  
##     avg_cust        tot_sales          avg_sales    
##  Min.   : 350.8   Min.   : 4534864   Min.   : 3148  
##  1st Qu.: 598.4   1st Qu.:51969491   1st Qu.: 5295  
##  Median : 631.9   Median :60285966   Median : 5734  
##  Mean   : 636.1   Mean   :55935054   Mean   : 5827  
##  3rd Qu.: 669.9   3rd Qu.:65419656   3rd Qu.: 6213  
##  Max.   :1009.1   Max.   :95991386   Max.   :10819

3.4. Sales by store type

sales_by_store_type <- sqldf("
select
    storetype
    , count(distinct a.store) as tot_store
   
    , sum(customers) as tot_cust
    , round(avg(customers), 2) as avg_cust
   
    , sum(sales) as tot_sales
    , round(avg(sales), 2) as avg_sales
   
    , substr(min(date), 1, 10) as first_purchase_date
    , substr(max(date), 1, 10) as last_purchase_date
from train a
left join store b
on a.store = b.store
group by 1;
")

summary(sales_by_store_type)

##   StoreType           tot_store        tot_cust            avg_cust     
##  Length:4           Min.   : 17.0   Min.   : 31465621   Min.   : 501.4  
##  Class :character   1st Qu.:115.2   1st Qu.: 76963684   1st Qu.: 619.6  
##  Mode  :character   Median :248.0   Median :124517350   Median : 666.1  
##                     Mean   :278.8   Mean   :161010439   Mean   : 955.4  
##                     3rd Qu.:411.5   3rd Qu.:208564105   3rd Qu.:1001.9  
##                     Max.   :602.0   Max.   :363541434   Max.   :1987.7  
##    tot_sales            avg_sales     first_purchase_date
##  Min.   : 159231395   Min.   : 5642   Length:4           
##  1st Qu.: 783221426   1st Qu.: 5703   Class :character   
##  Median : 783221426   Median : 5731   Mode  :character   
##  Mean   :1468295155   Mean   : 6791                      
##  3rd Qu.:1765392943   3rd Qu.: 6818                      
##  Max.   :3165334859   Max.   :10059                      
##  last_purchase_date
##  Length:4          
##  Class :character  
##  Mode  :character  
##                    
##                    
## 

3.5. Create base table

base <- sqldf("select * from train a left join store b on a.store = b.store")
head(base)

##   Store DayOfWeek       Date Sales Customers Open Promo StateHoliday
## 1     1         2 2013-01-01     0         0    0     0            a
## 2     2         2 2013-01-01     0         0    0     0            a
## 3     3         2 2013-01-01     0         0    0     0            a
## 4     4         2 2013-01-01     0         0    0     0            a
## 5     5         2 2013-01-01     0         0    0     0            a
## 6     6         2 2013-01-01     0         0    0     0            a
##   SchoolHoliday Quarter Store..11 StoreType Assortment CompetitionDistance
## 1             1       1         1         c          a                1270
## 2             1       1         2         a          a                 570
## 3             1       1         3         a          a               14130
## 4             1       1         4         c          c                 620
## 5             1       1         5         a          a               29910
## 6             1       1         6         a          a                 310
##   CompetitionOpenSinceMonth CompetitionOpenSinceYear Promo2
## 1                         9                     2008      0
## 2                        11                     2007      1
## 3                        12                     2006      1
## 4                         9                     2009      0
## 5                         4                     2015      0
## 6                        12                     2013      0
##   Promo2SinceWeek Promo2SinceYear   PromoInterval
## 1              NA              NA                
## 2              13            2010 Jan,Apr,Jul,Oct
## 3              14            2011 Jan,Apr,Jul,Oct
## 4              NA              NA                
## 5              NA              NA                
## 6              NA              NA

3.6. Sales by store type & year

sales_by_store_type_year <- sqldf("
select
    storetype
    , substr(date, 1, 4) as year
    , count(distinct a.store) as tot_store
   
    , sum(customers) as tot_cust
    , round(avg(customers), 2) as avg_cust
   
    , sum(sales) as tot_sales
    , round(avg(sales), 2) as avg_sales
   
    , substr(min(date), 1, 10) as first_purchase_date
    , substr(max(date), 1, 10) as last_purchase_date
from train a
left join store b
on a.store = b.store
group by 1, 2;
")

summary(sales_by_store_type_year)

##   StoreType             year             tot_store        tot_cust      
##  Length:380         Length:380         Min.   : 16.0   Min.   :  95689  
##  Class :character   Class :character   1st Qu.:104.8   1st Qu.: 382608  
##  Mode  :character   Mode  :character   Median :207.5   Median :1188746  
##                                        Mean   :270.7   Mean   :1694847  
##                                        3rd Qu.:390.5   3rd Qu.:2057489  
##                                        Max.   :602.0   Max.   :5421709  
##     avg_cust        tot_sales          avg_sales     first_purchase_date
##  Min.   : 346.2   Min.   :  452444   Min.   : 3666   Length:380         
##  1st Qu.: 564.0   1st Qu.: 2531426   1st Qu.: 5362   Class :character   
##  Median : 666.5   Median :11202269   Median : 5935   Mode  :character   
##  Mean   : 954.9   Mean   :15455738   Mean   : 6789                      
##  3rd Qu.:1061.7   3rd Qu.:23607748   3rd Qu.: 8265                      
##  Max.   :2333.6   Max.   :51645134   Max.   :13309                      
##  last_purchase_date
##  Length:380        
##  Class :character  
##  Mode  :character  
##                    
##                    
## 

3.7. Sales by distance (Unit : 5,000)

base['CompetitionDistanceGroup'] <- trunc(base$CompetitionDistance / 5000) * 5000
# base['CompetitionDistanceGroup'] <- round(base$CompetitionDistance / 5000, 1) * 5000
# base['CompetitionDistanceGroup'] <- ceiling(base$CompetitionDistance / 5000) * 5000
#base %>% select(CompetitionDistance, CompetitionDistanceGroup) %>% head(10)

sales_by_distance <- sqldf("
select
    CompetitionDistanceGroup as competition_distance_group
    , count(distinct store) as tot_store
   
    , sum(sales) as tot_sales
    , round(avg(sales), 2) as avg_sales
   
    , sum(customers) as tot_cust
    , round(avg(customers), 2) as avg_cust
   
    , substr(min(date), 1, 10) as first_purchase_date
    , substr(max(date), 1, 10) as last_purchase_date
from base
group by 1;
")

summary(sales_by_distance)

##  competition_distance_group   tot_store        tot_sales         
##  Min.   :    0              Min.   :  1.00   Min.   :   5718883  
##  1st Qu.:13750              1st Qu.:  3.00   1st Qu.:  13614827  
##  Median :27500              Median :  7.00   Median :  36389933  
##  Mean   :29583              Mean   : 85.77   Mean   : 451783124  
##  3rd Qu.:41250              3rd Qu.: 65.00   3rd Qu.: 324222806  
##  Max.   :75000              Max.   :762.00   Max.   :4059521480  
##  NA's   :1                                                       
##    avg_sales       tot_cust            avg_cust     first_purchase_date
##  Min.   :4455   Min.   :   702496   Min.   :457.5   Length:13          
##  1st Qu.:5158   1st Qu.:  1379518   1st Qu.:504.9   Class :character   
##  Median :5575   Median :  3826739   Median :542.4   Mode  :character   
##  Mean   :5686   Mean   : 49541673   Mean   :597.8                      
##  3rd Qu.:6071   3rd Qu.: 29891814   3rd Qu.:678.2                      
##  Max.   :7321   Max.   :471232154   Max.   :861.2                      
##                                                                        
##  last_purchase_date
##  Length:13         
##  Class :character  
##  Mode  :character  
##                    
##                    
##                    
## 

3.8. Does distance affect sales?

• 거리가 매출에 영향을 미치는지
• EDA를 하다가 궁금해서 linear regression

x <- sqldf("
select 
    CompetitionDistance
    , Sales
from base
")

lm <- lm(x$Sales~x$CompetitionDistance)
summary(lm)

## 
## Call:
## lm(formula = x$Sales ~ x$CompetitionDistance)
## 
## Residuals:
##    Min     1Q Median     3Q    Max 
##  -5829  -2051    -27   2084  35738 
## 
## Coefficients:
##                         Estimate Std. Error t value Pr(>|t|)    
## (Intercept)            5.829e+03  4.674e+00 1247.03   <2e-16 ***
## x$CompetitionDistance -9.598e-03  4.955e-04  -19.37   <2e-16 ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 3850 on 1014565 degrees of freedom
##   (2642 observations deleted due to missingness)
## Multiple R-squared:  0.0003698,  Adjusted R-squared:  0.0003688 
## F-statistic: 375.3 on 1 and 1014565 DF,  p-value: < 2.2e-16

• 거리가 멀수록 매출이 덜 나온다.
• 대략 -10 10의 -3승 0.01
• sales = - 0.01 * distance * 5829
• 번화가는 매장들이 붙어 있지만 매출이 많으니까…

ggplot(x, aes(x = x$CompetitionDistance, y = x$Sales)) +
    geom_point(alpha = 0.02) +
    geom_smooth(method = 'lm', col = "red")

• 기울기가 0
  • 예측하는 문제라면 boost, 뉴럴넷
  • sales
• 이상치가 조금 있긴 하지만
  • 거리랑 매출의 관계를 찾은 것
  • 거리가 커질수록 세일즈가 줄어든다.
  • 이런 식으로 인사이트를 찾는 것

3.9. Sales by distance & store type

sales_by_store_type_distance <- sqldf("
select
    storetype
    , CompetitionDistanceGroup as competition_distance_group
    , count(distinct store) as tot_store
   
    , sum(sales) as tot_sales
    , round(avg(sales), 2) as avg_sales
   
    , sum(customers) as tot_cust
    , round(avg(customers), 2) as avg_cust
   
    , substr(min(date), 1, 10) as first_purchase_date
    , substr(max(date), 1, 10) as last_purchase_date
from base
group by 1, 2;
")

summary(sales_by_store_type_distance)

##   StoreType         competition_distance_group   tot_store     
##  Length:32          Min.   :    0              Min.   :  1.00  
##  Class :character   1st Qu.:10000              1st Qu.:  1.00  
##  Mode  :character   Median :20000              Median :  3.50  
##                     Mean   :23000              Mean   : 34.84  
##                     3rd Qu.:33750              3rd Qu.: 26.50  
##                     Max.   :75000              Max.   :450.00  
##                     NA's   :2                                  
##    tot_sales            avg_sales        tot_cust            avg_cust     
##  Min.   :   3385282   Min.   : 3594   Min.   :   337120   Min.   : 357.9  
##  1st Qu.:   6599117   1st Qu.: 5272   1st Qu.:   632957   1st Qu.: 514.1  
##  Median :  19117000   Median : 5652   Median :  2117185   Median : 555.8  
##  Mean   : 183536894   Mean   : 5789   Mean   : 20126305   Mean   : 618.1  
##  3rd Qu.: 159231395   3rd Qu.: 6563   3rd Qu.: 14296809   3rd Qu.: 682.9  
##  Max.   :2383432599   Max.   :10059   Max.   :282162255   Max.   :1987.7  
##                                                                           
##  first_purchase_date last_purchase_date
##  Length:32           Length:32         
##  Class :character    Class :character  
##  Mode  :character    Mode  :character  
##                                        
##                                        
##                                        
## 

3.10. Sales by competition open since year

competition_open_since_year_sales <- sqldf("
select
    CompetitionOpenSinceYear as competition_open_since_year
    , count(distinct store) as tot_store
   
    , sum(sales) as tot_sales
    , round(avg(sales), 2) as avg_sales
   
    , sum(customers) as tot_cust
    , round(avg(customers), 2) as avg_cust
   
    , min(date) as first_purchase_date
    , max(date) as last_purchase_date
from base
group by 1;
")

summary(competition_open_since_year_sales)

##  competition_open_since_year   tot_store        tot_sales        
##  Min.   :1900                Min.   :  1.00   Min.   :5.222e+06  
##  1st Qu.:1998                1st Qu.:  7.25   1st Qu.:3.838e+07  
##  Median :2004                Median : 32.50   Median :1.648e+08  
##  Mean   :1998                Mean   : 46.46   Mean   :2.447e+08  
##  3rd Qu.:2010                3rd Qu.: 54.25   3rd Qu.:2.898e+08  
##  Max.   :2015                Max.   :354.00   Max.   :1.862e+09  
##  NA's   :1                                                       
##    avg_sales       tot_cust            avg_cust     first_purchase_date
##  Min.   :5225   Min.   :   507202   Min.   :550.4   Min.   :15706      
##  1st Qu.:5623   1st Qu.:  5136704   1st Qu.:601.2   1st Qu.:15706      
##  Median :5773   Median : 18711883   Median :629.3   Median :15706      
##  Mean   :5863   Mean   : 26835073   Mean   :640.8   Mean   :15706      
##  3rd Qu.:6013   3rd Qu.: 31904140   3rd Qu.:669.1   3rd Qu.:15706      
##  Max.   :6889   Max.   :204627267   Max.   :818.7   Max.   :15706      
##                                                                        
##  last_purchase_date
##  Min.   :16647     
##  1st Qu.:16647     
##  Median :16647     
##  Mean   :16647     
##  3rd Qu.:16647     
##  Max.   :16647     
## 

3.11. Sales by competition open since year & distance

competition_open_since_year_distance_sales <- sqldf("
select
    CompetitionOpenSinceYear as competition_open_since_year
    , CompetitionDistanceGroup as competition_distance_group

    , count(distinct store) as tot_store

    , sum(sales) as tot_sales
    , round(avg(sales), 2) as avg_sales

    , sum(customers) as tot_cust
    , round(avg(customers), 2) as avg_cust

    , substr(min(date), 1, 10) as first_purchase_date
    , substr(max(date), 1, 10) as last_purchase_date
from base
group by 1, 2;
")

summary(competition_open_since_year_distance_sales)

##  competition_open_since_year competition_distance_group   tot_store     
##  Min.   :1900                Min.   :    0              Min.   :  1.00  
##  1st Qu.:2003                1st Qu.: 5000              1st Qu.:  1.00  
##  Median :2008                Median :10000              Median :  3.00  
##  Mean   :2006                Mean   :14227              Mean   : 10.05  
##  3rd Qu.:2012                3rd Qu.:20000              3rd Qu.:  6.00  
##  Max.   :2015                Max.   :75000              Max.   :233.00  
##  NA's   :9                   NA's   :1                                  
##    tot_sales           avg_sales       tot_cust            avg_cust     
##  Min.   :2.319e+06   Min.   :2461   Min.   :   246036   Min.   : 274.8  
##  1st Qu.:5.979e+06   1st Qu.:5126   1st Qu.:   568147   1st Qu.: 494.4  
##  Median :1.323e+07   Median :5705   Median :  1299613   Median : 566.7  
##  Mean   :5.291e+07   Mean   :5648   Mean   :  5802178   Mean   : 571.4  
##  3rd Qu.:3.345e+07   3rd Qu.:6232   3rd Qu.:  3620793   3rd Qu.: 656.1  
##  Max.   :1.236e+09   Max.   :9303   Max.   :145176448   Max.   :1013.9  
##                                                                         
##  first_purchase_date last_purchase_date
##  Length:111          Length:111        
##  Class :character    Class :character  
##  Mode  :character    Mode  :character  
##                                        
##                                        
##                                        
## 

3.12. Which stores have big differences in sales due to school holidays?

table(base$SchoolHoliday)

## 
##      0      1 
## 835488 181721

Desc(base$SchoolHoliday)

## ------------------------------------------------------------------------- 
## base$SchoolHoliday (integer - dichotomous)
## 
##      length         n       NAs    unique
##   1'017'209 1'017'209         0         2
##                100.0%      0.0%          
## 
##       freq   perc  lci.95  uci.95'
## 0  835'488  82.1%   82.1%   82.2%
## 1  181'721  17.9%   17.8%   17.9%
## 
## ' 95%-CI Wilson

school_holiday <- sqldf("
select
    store
    , schoolholiday
    , sum(sales) as tot_sales
from base
group by 1, 2;
")

school_holiday_diff_desc <- sqldf("
select
    store
    , schoolholiday
    , sum(case when schoolholiday = 0 then tot_sales end) as not_holiday_sales
    , sum(case when schoolholiday = 1 then tot_sales end) as holiday_sales
    , sum(case when schoolholiday = 0 then tot_sales end) - sum(case when schoolholiday = 1 then tot_sales end) as diff
from school_holiday
group by 1
order by diff desc ;") %>% head(10)
summary(school_holiday_diff_desc)

##      Store        SchoolHoliday not_holiday_sales  holiday_sales    
##  Min.   : 251.0   Min.   :1     Min.   :10175787   Min.   :2365944  
##  1st Qu.: 347.0   1st Qu.:1     1st Qu.:11211306   1st Qu.:2772411  
##  Median : 537.5   Median :1     Median :11766496   Median :2958632  
##  Mean   : 575.8   Mean   :1     Mean   :12265578   Mean   :3037902  
##  3rd Qu.: 774.2   3rd Qu.:1     3rd Qu.:13172706   3rd Qu.:3372016  
##  Max.   :1114.0   Max.   :1     Max.   :16131631   Max.   :3653765  
##       diff         
##  Min.   : 7809843  
##  1st Qu.: 8297905  
##  Median : 9050979  
##  Mean   : 9227676  
##  3rd Qu.: 9599275  
##  Max.   :12746420

3.13. Which stores have small differences in sales due to school holidays?

school_holiday_diff_asc <- sqldf("
select
    store
    , schoolholiday
    , sum(case when schoolholiday = 0 then tot_sales end) as not_holiday_sales
    , sum(case when schoolholiday = 1 then tot_sales end) as holiday_sales
    , sum(case when schoolholiday = 0 then tot_sales end) - sum(case when schoolholiday = 1 then tot_sales end) as diff
from school_holiday
group by 1
order by diff;") %>% head(10)

summary(school_holiday_diff_asc)

##      Store       SchoolHoliday not_holiday_sales holiday_sales   
##  Min.   :198.0   Min.   :1     Min.   :1651351   Min.   :462971  
##  1st Qu.:222.2   1st Qu.:1     1st Qu.:1781068   1st Qu.:481274  
##  Median :366.0   Median :1     Median :1825997   Median :494913  
##  Mean   :473.9   Mean   :1     Mean   :1841610   Mean   :511127  
##  3rd Qu.:731.2   3rd Qu.:1     3rd Qu.:1924701   3rd Qu.:526140  
##  Max.   :972.0   Max.   :1     Max.   :2002451   Max.   :588780  
##       diff        
##  Min.   :1188380  
##  1st Qu.:1236802  
##  Median :1341650  
##  Mean   :1330483  
##  3rd Qu.:1419803  
##  Max.   :1451714

3.14. Which stores have big differences in sales due to state holidays?

Desc(base$StateHoliday)

## ------------------------------------------------------------------------- 
## base$StateHoliday (character)
## 
##      length         n       NAs    unique    levels     dupes
##   1'017'209 1'017'209         0         4         4         y
##                100.0%      0.0%                              
## 
##    level     freq   perc    cumfreq  cumperc
## 1      0  986'159  96.9%    986'159    96.9%
## 2      a   20'260   2.0%  1'006'419    98.9%
## 3      b    6'690   0.7%  1'013'109    99.6%
## 4      c    4'100   0.4%  1'017'209   100.0%

state_holiday <- sqldf("
select
    store
    , StateHoliday
    , sum(sales) as tot_sales
from base
group by 1, 2
")

state_holiday_desc <- sqldf("
select
    Store
    , not_holiday_sales
    , public_holiday_sales
    , easter_holiday_sales
    , christmas_holiday_sales

    , case when not_holiday_sales = public_diff then 0 else public_diff end +
      case when not_holiday_sales = easter_diff then 0 else easter_diff end +
      case when not_holiday_sales = christmas_diff then 0 else christmas_diff end as diff

    , case
        when public_diff != 0 then 'a'
        when easter_diff != 0 then 'b'
        when christmas_diff != 0 then 'c'
        else 'error' end as category

from (
        select
            Store
            , sum(case when StateHoliday = 0 then tot_sales end) as not_holiday_sales
            , sum(case when StateHoliday = 'a' then tot_sales end) as public_holiday_sales
            , sum(case when StateHoliday = 'b' then tot_sales end) as easter_holiday_sales
            , sum(case when StateHoliday = 'c' then tot_sales end) as christmas_holiday_sales

            , sum(case when StateHoliday = 0 then tot_sales end) - sum(case when StateHoliday = 'a' then tot_sales end) as public_diff
            , sum(case when StateHoliday = 0 then tot_sales end) - sum(case when StateHoliday = 'b' then tot_sales end) as easter_diff
            , sum(case when StateHoliday = 0 then tot_sales end) - sum(case when StateHoliday = 'c' then tot_sales end) as christmas_diff
        from 
            state_holiday
        group by 1
) 
order by diff desc; ") %>% head(10)

summary(state_holiday_desc)

##      Store        not_holiday_sales  public_holiday_sales
##  Min.   : 259.0   Min.   : 7039181   Min.   :102286      
##  1st Qu.: 357.0   1st Qu.: 9851432   1st Qu.:178832      
##  Median : 528.0   Median :10400706   Median :239213      
##  Mean   : 561.6   Mean   :11748420   Mean   :235231      
##  3rd Qu.: 720.2   3rd Qu.:13271430   3rd Qu.:270714      
##  Max.   :1097.0   Max.   :18751269   Max.   :444916      
##  easter_holiday_sales christmas_holiday_sales      diff         
##  Min.   : 41700       Min.   : 19060          Min.   :20951087  
##  1st Qu.: 56390       1st Qu.: 27449          1st Qu.:29205960  
##  Median : 90579       Median : 44518          Median :30898199  
##  Mean   : 92815       Mean   : 48105          Mean   :34869110  
##  3rd Qu.:102157       3rd Qu.: 57583          3rd Qu.:39399918  
##  Max.   :208687       Max.   :111970          Max.   :55488234  
##    category        
##  Length:10         
##  Class :character  
##  Mode  :character  
##                    
##                    
## 

3.15. Which stores have small differences in sales due to state holidays?

state_holiday_asc <- sqldf("
select
    Store
    , not_holiday_sales
    , public_holiday_sales
    , easter_holiday_sales
    , christmas_holiday_sales

    , case when not_holiday_sales = public_diff then 0 else public_diff end +
      case when not_holiday_sales = easter_diff then 0 else easter_diff end +
      case when not_holiday_sales = christmas_diff then 0 else christmas_diff end as diff

    , case
        when public_diff != 0 then 'a'
        when easter_diff != 0 then 'b'
        when christmas_diff != 0 then 'c'
        else 'error' end as category

from (
        select
            Store
            , sum(case when StateHoliday = 0 then tot_sales end) as not_holiday_sales
            , sum(case when StateHoliday = 'a' then tot_sales end) as public_holiday_sales
            , sum(case when StateHoliday = 'b' then tot_sales end) as easter_holiday_sales
            , sum(case when StateHoliday = 'c' then tot_sales end) as christmas_holiday_sales

            , sum(case when StateHoliday = 0 then tot_sales end) - sum(case when StateHoliday = 'a' then tot_sales end) as public_diff
            , sum(case when StateHoliday = 0 then tot_sales end) - sum(case when StateHoliday = 'b' then tot_sales end) as easter_diff
            , sum(case when StateHoliday = 0 then tot_sales end) - sum(case when StateHoliday = 'c' then tot_sales end) as christmas_diff
        from 
            state_holiday
        group by 1
) 
order by diff; ") %>% head(10)

summary(state_holiday_asc)