Walmart EDA

options(scipen = 999)
library(tidyverse)
library(DataExplorer)
library(forecast)
library(flextable)
library(explore)
library(plotly)

Walmart Sales Analysis (2010-2012)

Introduction Dataset Info This is the historical data that covers sales from 2010-02-05 to 2012-11-01, in the file Walmart_Store_sales. Within this file you will find the following fields:

Store - the store number Date - the week of sales Weekly_Sales - sales for the given store Holiday_Flag - whether the week is a special holiday week 1 – Holiday week 0 – Non-holiday week Temperature - Temperature on the day of sale Fuel_Price - Cost of fuel in the region CPI – Prevailing consumer price index Unemployment - Prevailing unemployment rate Holiday Events Super Bowl: 12-Feb-10, 11-Feb-11, 10-Feb-12, 8-Feb-13 Labour Day: 10-Sep-10, 9-Sep-11, 7-Sep-12, 6-Sep-13 Thanksgiving: 26-Nov-10, 25-Nov-11, 23-Nov-12, 29-Nov-13 Christmas: 31-Dec-10, 30-Dec-11, 28-Dec-12, 27-Dec-13

Objectives Data exploration Manipulation data (only if is necessary). statistical Exploration Data Analysis (EDA). the focus is to see distribution

what store did best vs worst how much was the difference between the best and least selling store annual sales monthly sales Store quarterly growth for 2012

what holidays were better in average sales than normal day

2012 to 2011 year over year 2012 month over month 2012 q3 QoQ

walmart = readr::read_csv("C:/Users/thepy/Downloads/Walmart.csv")

walmart_eda = walmart

#check NA
# and duplicate
# as outliers
sum(is.na(walmart_eda)) # no NA

[1] 0

sum(duplicated(walmart_eda)) # no duplicate

[1] 0

the data has nulls or duplicate values

# count NAs
apply(X=is.na(walmart_eda), MARGIN=2, FUN = sum)

       Store         Date Weekly_Sales Holiday_Flag  Temperature   Fuel_Price          CPI 
           0            0            0            0            0            0            0 
Unemployment 
           0 

looking at each of the columns we see that there are no NA

#count unique values
sort(sapply(walmart_eda,function(x) length(unique(x))))

Holiday_Flag        Store         Date Unemployment   Fuel_Price          CPI  Temperature 
           2           45          143          349          892         2145         3528 
Weekly_Sales 
        6435 

dim(walmart_eda)
str(walmart_eda)

walmart_eda_2 <- 
walmart_eda %>% 
  mutate(Date=dmy(Date) ,
         Year=year(Date),
         Month=month(Date),
         Quarterly=quarter(Date),
         weekly=week(Date), 
         Store = as.integer(Store)) %>% 
        # Holiday_Flag=as.factor(Holiday_Flag)) %>% 
  rename(Holiday_Y_N=Holiday_Flag) %>% 
  mutate(Weekly_Sales2=ifelse(Weekly_Sales>1046965,'above_average','below_average'),
         Weekly_Sales2=as.factor(Weekly_Sales2)) %>% 
  relocate(Weekly_Sales2,.after = Weekly_Sales) %>% 
  mutate(Fuel_Price2=ifelse(Fuel_Price<3.359,'Low','High' ),
         Fuel_Price2=as.factor(Fuel_Price2)) %>% 
  relocate(Fuel_Price2,.after = Fuel_Price) %>%
  mutate(Temperature_range= case_when( 
    Temperature <= quantile(walmart_eda$Temperature, c(0.25)) ~ "Cold",
    Temperature > quantile(walmart_eda$Temperature, c(0.50)) & 
     Temperature <= quantile(walmart_eda$Temperature, c(0.75)) ~ "Cool", 
    T ~ "Hot")) %>%
  mutate(Temperature_range=as.factor(Temperature_range)) %>% 
  relocate(Temperature_range,.after = Temperature) %>% 
  mutate(CPI2 = ifelse(CPI>mean(CPI),'Yes','No'),
         CPI2= as.factor(CPI2)) %>% 
  relocate(CPI2,.after=CPI) %>% 
 mutate(Unemployment2=ifelse(Unemployment<mean(Unemployment),'Belowavg','Aboveavg' ),
         Unemployment2=as.factor(Unemployment2))%>% 
  relocate(Unemployment2,.after=Unemployment) %>% 
  mutate(Holidays = case_when(Date=='2010-02-10' ~ 'Super_Bowl',
                              Date=='2011-02-11' ~ 'Super_Bowl',
                              Date=='2012-02-12' ~ 'Super_Bowl',
                              
                              Date=='2010-09-10' ~ 'Labour_Day',
                              Date=='2011-09-09' ~ 'Labour_Day',
                              Date=='2012-09-07' ~ 'Labour_Day',
                              Date=='2010-11-26' ~ 'Thanksgiving',
                              Date=='2011-11-25' ~ 'Thanksgiving',
                              Date=='2010-12-31' ~ 'Christmas',
                              Date== '2011-12-30' ~ 'Christmas',
                               TRUE ~ 'Non-Holiday' )) %>% 
  relocate(Holidays,.after = Holiday_Y_N)

summary(walmart_eda_2)

     Store         Date             Weekly_Sales           Weekly_Sales2   Holiday_Y_N     
 Min.   : 1   Min.   :2010-02-05   Min.   : 209986   above_average:2876   Min.   :0.00000  
 1st Qu.:12   1st Qu.:2010-10-08   1st Qu.: 553350   below_average:3559   1st Qu.:0.00000  
 Median :23   Median :2011-06-17   Median : 960746                        Median :0.00000  
 Mean   :23   Mean   :2011-06-17   Mean   :1046965                        Mean   :0.06993  
 3rd Qu.:34   3rd Qu.:2012-02-24   3rd Qu.:1420159                        3rd Qu.:0.00000  
 Max.   :45   Max.   :2012-10-26   Max.   :3818686                        Max.   :1.00000  
   Holidays          Temperature     Temperature_range   Fuel_Price    Fuel_Price2      CPI       
 Length:6435        Min.   : -2.06   Cold:1609         Min.   :2.472   High:3464   Min.   :126.1  
 Class :character   1st Qu.: 47.46   Cool:1608         1st Qu.:2.933   Low :2971   1st Qu.:131.7  
 Mode  :character   Median : 62.67   Hot :3218         Median :3.445               Median :182.6  
                    Mean   : 60.66                     Mean   :3.359               Mean   :171.6  
                    3rd Qu.: 74.94                     3rd Qu.:3.735               3rd Qu.:212.7  
                    Max.   :100.14                     Max.   :4.468               Max.   :227.2  
  CPI2       Unemployment     Unemployment2       Year          Month          Quarterly    
 No :3146   Min.   : 3.879   Aboveavg:3022   Min.   :2010   Min.   : 1.000   Min.   :1.000  
 Yes:3289   1st Qu.: 6.891   Belowavg:3413   1st Qu.:2010   1st Qu.: 4.000   1st Qu.:2.000  
            Median : 7.874                   Median :2011   Median : 6.000   Median :2.000  
            Mean   : 7.999                   Mean   :2011   Mean   : 6.448   Mean   :2.483  
            3rd Qu.: 8.622                   3rd Qu.:2012   3rd Qu.: 9.000   3rd Qu.:3.000  
            Max.   :14.313                   Max.   :2012   Max.   :12.000   Max.   :4.000  
     weekly     
 Min.   : 1.00  
 1st Qu.:14.00  
 Median :26.00  
 Mean   :26.15  
 3rd Qu.:38.00  
 Max.   :53.00  

minimum store is store 1,max store is 45, range of stores is 44

walmart_eda %>% 
  mutate(Date=dmy(Date)) %>% 
  summarise(Last_recored_weeklysale=max(Date),
         First_recored_weeklysale=min(Date))

walmart_eda %>% 
select(Weekly_Sales) %>% 
  summarise(minimum_sales=round(min(Weekly_Sales),2),
         average_sales=round(mean(Weekly_Sales),2),
         maximum_sales=round(max(Weekly_Sales),2))

walmart_eda %>% 
select(Temperature) %>% 
  summarise(minimum_Temperature=round(min(Temperature),2),
         average_Temperature=round(mean(Temperature),2),
         maximum_Temperature=round(max(Temperature),2))

walmart_eda %>% 
select(Fuel_Price) %>% 
  summarise(minimum_Fuel_Price=round(min(Fuel_Price),2),
         average_Fuel_Price=round(mean(Fuel_Price),2),
         maximum_Fuel_Price=round(max(Fuel_Price),2))

walmart_eda %>% 
select(CPI) %>% 
  summarise(minimum_CPI=round(min(CPI),2),
         average_CPI=round(mean(CPI),2),
         maximum_CPI=round(max(CPI),2))

walmart_eda %>% 
select(Unemployment) %>% 
  summarise(minimum_Unemployment=round(min(Unemployment),2),
         average_Unemployment=round(mean(Unemployment),2),
         maximum_Unemployment=round(max(Unemployment),2))

Data Analysis

which store has max sales?

walmart_eda_2 %>% 
  group_by(Store) %>% 
  summarise(totalsales=sum(Weekly_Sales)) %>% 
  arrange(desc(totalsales)) 

visualize

walmart_eda_2 %>% 
  group_by(Store) %>% 
  summarise(totalsales=sum(Weekly_Sales)) %>% 
  arrange(desc(totalsales)) %>% 
  ggplot(aes(reorder(Store,totalsales),totalsales,fill=totalsales))+geom_col(col='black')+scale_fill_gradient(high='green',low = 'darkred')+theme(axis.text.x = element_text(size=7.2))+xlab('Stores')+ggtitle('Revenue by Store Number')

• Store 20 had the most with 301,397,792
• Store 33 had the least with 37,160,222

What was the difference in revenue between the highest selling store and the least selling store?

walmart_eda_2 %>% 
# filter(Store > 32, Store<34, Store>19, Store<21) %>% 
  group_by(Store) %>% 
  summarise(totalsales=sum(Weekly_Sales))  %>%
  pivot_wider(names_from = Store,values_from = totalsales) %>% 
  transmute(store_maxsale_to_leastsale_difference= `20` - `33`) 

NA

Store 20 which was the store with the overall most sales (301,397,792) had a revenue that was 264,237,570 greater than that of store 33 which happened to bring in the least overall revenue (37,160,222)

what is the percent of sales per year?

annualproportion <- walmart_eda_2 %>% 
  group_by(Year) %>% 
  summarise(totalsales=sum(Weekly_Sales)) %>% 
 mutate(saleproportion=round((totalsales/sum(totalsales)),2)) %>% 
   arrange(desc(totalsales))
webr::PieDonut(annualproportion,aes(saleproportion,Year),title = "Percent of Walmart's annual sales",showPieName = T,pieLabelSize = 5,showRatioPie = F,showRatioDonut = F)

which Holidays are better than the average day sales?

walmart_eda_2 %>% 
  group_by(Holidays) %>% 
  summarise(totalsales=sum(Weekly_Sales)) %>% 
  arrange(desc(totalsales))

walmart_eda_2 %>% 
  group_by(Holidays) %>% 
  summarise(avgsales=mean(Weekly_Sales)) %>% 
  arrange(desc(avgsales)) %>% 
  ggplot(aes(fct_infreq(Holidays,avgsales),avgsales,fill=avgsales))+geom_col(col='black')+scale_fill_gradient(high='green3',low='darkred')+xlab('')+ggtitle('Holidays better than Average')+theme(axis.text.x = element_text(size=7.7))+scale_y_continuous(label=scales::dollar)

1. Thanksgiving is the holiday that brought in well above the normal day’s average
2. Meanwhile both the Super_Bowl and Labour_Day each brought in revenue that was just above the normal day’s average revenue
3. while sales on Christmas sat below the normal day’s average revenue since all christmas revenue is made prior to Christmas and anything during or after is most associated with returns which would be seen as a loss in revenue

quarter 3 progress 2012 for stores average sales

walmart_eda_2 %>%
  filter(Year==2012) %>% 
  group_by(Store,Quarterly) %>% 
  summarise(avgsales=mean(Weekly_Sales)) %>% 
  pivot_wider(names_from = Quarterly,values_from = avgsales) %>% 
  mutate(QoQ= (((`3`- `2`)*100)/`2`) ) %>% 
  select(Store,QoQ) %>% 
  arrange(desc(QoQ))

`summarise()` has grouped output by 'Store'. You can override using the `.groups` argument.

walmart_eda_2 %>%
  filter(Year==2012) %>% 
  group_by(Store,Quarterly) %>% 
  summarise(avgsales=mean(Weekly_Sales)) %>% 
  pivot_wider(names_from = Quarterly,values_from = avgsales) %>% 
  mutate(QoQ= round((((`3`- `2`)*100)/`2`),2) ) %>% 
  select(Store,QoQ) %>% 
  arrange(desc(QoQ)) %>% 
  ggplot(aes(reorder(Store,QoQ),QoQ,fill=QoQ))+geom_col(col='black')+scale_fill_gradient(high='green',low='darkred')+xlab('Store')+ggtitle('Quarterly Growth by Store 2012' )+theme(axis.text.x = element_text(size=7.7))

`summarise()` has grouped output by 'Store'. You can override using the `.groups` argument.

in quarter 3 of 2012, we can see that the vast majority of store say negative growth while only a handful saw positive growth with store 14 seeing the most negative growth (-15.77% growth) and store 7 the most positive growth (13.33% growth)

Monthly Sales

#monthly sales

walmart_eda_2 %>% 
  #filter(Year==2012) %>% 
  group_by(Month,Year) %>% 
  mutate(Year=as.factor(Year)) %>% 
  summarise(totalsales=sum(Weekly_Sales)) %>% 
  arrange(desc(totalsales))

• Dec 2010 ( 288,760,533)
• Dec 2011 (28,8078,102)
• June 2012 (240,610,329)
• December of 2010 saw the most sales given all the shopping that comes with preparing for Christmas gifts
• September of 2010 saw the least sales
• December of 2011 also saw the most sales given all the shopping that comes with preparing for Christmas gifts
• January of 2011 saw the fewest sales
• the current data did not provide for the last 2 months of 2012 June of 2012 saw the most sales followed closely by august and march January brought in the least revenue

Semester sales (biannual)

# creating new dataframes for semester revenue (total revenue by 6 month or biannual) for each year
semesterrev <- walmart_eda_2 %>% 
  filter(Year==2010,Month >1, Month < 7) %>% 
  group_by(Year,Month) %>% 
  summarise(totalsale=sum(Weekly_Sales)) %>% 
  pivot_wider(names_from = Month,values_from = totalsale) %>% 
  mutate(Semester1= round(`2`+`3`+`4`+`5`+`6`),2) %>% 
  select(Year,Semester1)


semesterrev2 <-walmart_eda_2 %>% 
  filter(Year==2010,Month >6) %>% 
  group_by(Year,Month) %>% 
  summarise(totalsale=sum(Weekly_Sales)) %>% 
  pivot_wider(names_from = Month,values_from = totalsale) %>% 
  mutate(Semester2= round(`7`+`8`+`9`+`10`+`11`+`12`),2) %>% 
  select(Year,Semester2)

semesterrev3 <- walmart_eda_2 %>% 
  filter(Year==2011,Month >=1, Month < 7)%>% 
  group_by(Year,Month) %>% 
  summarise(totalsale=sum(Weekly_Sales)) %>% 
  pivot_wider(names_from = Month,values_from = totalsale) %>% 
  mutate(Semester3= round(`1`+`2`+`3`+`4`+`5`+`6`),2) %>% 
  select(Year,Semester3)

semesterrev4 <- walmart_eda_2 %>% 
  filter(Year==2011,Month >6)%>% 
  group_by(Year,Month) %>% 
  summarise(totalsale=sum(Weekly_Sales)) %>% 
  pivot_wider(names_from = Month,values_from = totalsale) %>% 
  mutate(Semester4= round(`7`+`8`+`9`+`10`+`11`+`12`)) %>% 
  select(Year,Semester4)



semesterrev5 <-walmart_eda_2 %>% 
  filter(Year==2012,Month >=1, Month < 7)%>% 
  group_by(Year,Month) %>% 
  summarise(totalsale=sum(Weekly_Sales)) %>% 
  pivot_wider(names_from = Month,values_from = totalsale) %>% 
  mutate(Semester5= round(`1`+`2`+`3`+`4`+`5`+`6`),2) %>% 
  select(Year,Semester5)

semesterrev6 <-walmart_eda_2 %>% 
  filter(Year==2012,Month >6)%>% 
  group_by(Year,Month) %>% 
  summarise(totalsale=sum(Weekly_Sales)) %>% 
  pivot_wider(names_from = Month,values_from = totalsale) %>% 
  mutate(Semester6= round(`7`+`8`+`9`+`10`),2) %>% 
  select(Year,Semester6)

# joining semester tables with inner join
semester_revenues <- semesterrev %>% 
  inner_join(semesterrev2,by='Year')


semester_revenues2011 <- semesterrev3 %>% 
  inner_join(semesterrev4,by='Year')

semester_revenues2012 <- semesterrev5 %>% 
  inner_join(semesterrev6,by='Year')

# bring all joined tables together into new dataframe with rbind
all_semesters <- rbind(semester_revenues,semester_revenues2011,semester_revenues2012)

all_semesters%>%
  pivot_longer(col=-Year,names_to = 'Semesters',values_to = 'Revenue') %>% 
  filter(Revenue!='NA') %>% # filter out Nulls
  arrange(desc(Revenue))

#creating visual
all_semesters%>%
  pivot_longer(col=-Year,names_to = 'Semesters',values_to = 'Revenue') %>% 
  filter(Revenue!='NA') %>% 
  ggplot(aes(reorder(Semesters,Revenue),Revenue,col=Revenue))+geom_col(aes(fill=Year),linewidth=1.2)+coord_flip()+xlab('Semester2010to2012')+scale_color_gradient(high='green3',low='darkred')

• Semester 4 of 2011 ( months July through December) had the greatest Revenue for the six semesters
• Semester 6 being months (July through October) had the least Revenue of the six Semesters

all_semesters%>%
  pivot_longer(col=-Year,names_to = 'Semesters',values_to = 'Revenue') %>% 
  filter(Revenue!='NA') %>% # filter out Nulls
  #group_by(Year,Semesters) %>% 
 # summarise(totalRev=sum(Revenue)) %>% 
  mutate(Revenueproportion=round(Revenue/sum(Revenue),2)) %>% 
    arrange(desc(Revenue))  

NA

all_semester_proportions <-all_semesters%>%
  pivot_longer(col=-Year,names_to = 'Semesters',values_to = 'Revenue') %>% 
  filter(Revenue!='NA') %>% # filter out Nulls
  group_by(Semesters) %>% 
  summarise(totalRev=sum(Revenue)) %>% 
  mutate(Revenueproportion=round(totalRev/sum(totalRev),3)*100) %>% 
    arrange(desc(totalRev))  

PieDonut(all_semester_proportions,aes(Semesters,Revenueproportion),title = 'Percent of Total Weekly Sales by Semester',ratioByGroup = F,donutLabelSize=4,pieLabelSize=4,showPieName=F,showRatioDonut=F,showRatioPie=F,explodeDonut = T)

• Semester 4 being the second half of 2011 saw the greatest percent 19.6% of revenue intake
• Semester 6 being the second half of 2012 saw the smallest percent 11.7% of revenue intake and we can note that an obvious reason for this is that we did not have sale records for the holiday season in addition to the fact that we know from hindsight that the economy in 2012 began to take a downwards direction.

Lastly, I will include the general year over year for 2011 into 2012, 2012 quarter over quarter, 2012 month over month as well as 2012 monthly revenue

walmart_eda_2 %>% 
  filter(Year>2010) %>% 
  group_by(Year) %>% 
  summarise(totalsales=sum(Weekly_Sales)) %>% 
  pivot_wider(names_from = Year,values_from = totalsales) %>% 
  transmute(YoY=round((((`2012`-`2011`)*100)/`2011`),2))

for 2011 into 2012 Walmart saw a -18.3% year over year drop in revenue

walmart_eda_2 %>% 
  filter(Year>2011) %>% 
  group_by(Quarterly) %>% 
  summarise(totalsales=sum(Weekly_Sales)) %>% 
  pivot_wider(names_from = Quarterly,values_from = totalsales) %>% 
  transmute(QoQ=round((((`3`-`2`)*100)/`2`),2))

from quarter 2 to quarter 3 of 2012

Walmart saw an overall -2.15% drop in quarter over quarter growth

walmart_eda_2 %>% 
  filter(Year>2011) %>% 
  group_by(Month) %>% 
  summarise(totalsales=sum(Weekly_Sales)) %>% 
  pivot_wider(names_from = Month,values_from = totalsales) %>% 
  transmute(MoM=round((((`10`-`9`)*100)/`9`),2))

from September to October of 2012

Walmart saw an overall 2.06% rise in Month over Month growth

walmart_eda_2 %>% 
  filter(Year>2011,Month>9,) %>% 
  group_by(Month) %>% 
  summarise(totalsales=sum(Weekly_Sales)) 

In October of 2012 Walmart had a monthly revenue of 184,361,680