MATH1324 Assignment 3

Supermarket Price Wars

Group/Individual Details

• Alistair Grevis-James (s3644119)

Executive Statement

Data was collected online (www.frugl.com.au) for 489 manufactured food / drink products from Coles and Woolworths at sale and full price. These data were split into four categories, Beauty (n = 205), Sugary Snack (n = 128), Diet/ Gym Food (n = 68) & Supplement (n = 88). After data exploration, visualization and data preprocessing, paired sample t-tests were used to test for a significant mean difference between Woolworths Sale and Coles Sale prices. For all categories (n = 489) the mean difference was found to be 1.69 (SD = 8.68), with t(df=488) = 4.30, p < 0.001, 95% [0.91, 2.46], Woolworths sale items were statistically significantly lower than Coles sale in price. For Beauty (n = 205) the mean difference was found to be 0.16 (SD = 3.78), with t(df=204) = 0.62, p < 0.54, 95% [-0.36, 068], no statistically significantly difference was found between Woolworths Sale and Coles sale in price. For Sugary Snack, per 1000 g, (n = 128) the mean difference was found to be 3.08 (SD = 10.60), with t(df=127) = 3.29, p < 0.001, 95% [1.23, 4.93], Woolworths sale items were statistically significantly lower than Coles sale in price. For Diet/ Gym Food (n = 68) the mean difference was found to be 2.17 (SD = 15.06), with t(df=67) = 1.19, p < 0.23, 95% [-1.47, 5.82], no statistically significantly difference was found between Woolworths Sale and Coles sale in price. For Supplement (n = 88) the mean difference was found to be 2.83 (SD = 6.36), with t(df=87) = 4.18, p < 0.001, 95% [1.48, 4.18], Woolworths sale items were statistically significantly lower than Coles sale in price. QQ plots were not used due to all sample and subsample sizes (n > 30, normality assumed). The granovo.ds() function was used to provide for all Sale data to provide dependent sample assessment plots and summary stats (modulus values were taken as order was not relevant).

Hypothesis

As both Coles and Woolworths heavily market themselves to be “low cost”, over a sample of identical goods (identical being defined as manufactured goods that are stocked at both Coles and Woolworths), they will have the same mean cost.

The statistical hypotheses for the paired-samples t-test are as follows:

• H0:μΔ=0
• HA:μΔ≠0

Introduction

Coles Supermarkets Australia Pty Ltd (trading as Coles) and Woolworths Supermarkets (colloquially known as “Woolworths” or “Woolies”) were founded in Australia in 1914 and 1924, respectively ¹,². Together, they make up more than 80% of the Australian market. Both Coles and Woolworths market themselves as extremely price competitive, whilst maintaining high quality products and a wide range ³. Over the course of this investigation, we will attempt to prove or disprove our null hypothesis (as outlined above).

Data Collection

The price comparison website www.frugl.com.au⁴ was used for data collection. Data was collected over three days (7 Sep, 17 Sep & 18 Sep 2017) within the postcode 3054 (Carlton North, Victoria, Australia). Date was noted as sale items will change with time (and thus collection was performed within an 11 day window). Postcode was kept constant as different regions will presumably have specials and full priced products based on geographies (for example shops in Queensland may sell mangos at much cheaper prices than in Victoria). Such assumption could be evaluated, however are outside the scope of this investigation.

Frugl allows filtering of products into a selection of distinct native categories, for example HEALTH & BEAUTY or BAKING. Categories were chosen based on the likelyhood of containing identical products. For example ‘Nivea Moisturiser 200g’ is manufactured and thus identical between Coles and Woolworths stores, whereas Coles may stock ‘Fresh Celery 250g’ while Woolworths may stock ‘Farm Fresh Celery 225g’. These subtle differences make comparison harder, and so products like the latter were ignored. For the products sampled, the native categories and their respective item counts will be explored below.

For each item four prices were recorded; i. Coles Full Price, ii. Coles Sale Price, iii. Woolworths Full Price & iv. Woolworths Sale Price. If the item was not on sale, the full price was simply recorded into both the full price and the sale column. This was done to for ease of data processing, as delta = 0 between the full price and sale column (within either the Coles or Woolworths categories) will indicate the item is not presently on sale.

Data was collated into a Google Sheet, which was pulled directly into the R Markdown environment. See below for data structure and importation methology.

Load Packages and Data

library(car)
library(ggplot2)
library(dplyr)
library(gsheet)
library(gridExtra)
library("htmltools")
library(reshape2)
library(granova)
ds <- gsheet2tbl('https://drive.google.com/open?id=1_6RZ0HXg7VbgO78oyFfUyZaFOUHYWijy8XH1_jw5tA4')

Summary Statistics, Data Checking and Initial Visualization

The importation of the data set from Google Sheets as ‘ds’ was checked with summary, and the attribute names were also checked using sapply. During data collection, identical items available at both Coles and Woolworths were chosen. These items came from the following native categories on the Frugl website - BAKING, DAIRY & EGGS DIETARY, HEALTH & LIFESTYLE, DRINKS, FROZEN FOOD, HEALTH & BEAUTY, PACKAGED & READY MEALS and PANTRY, value counts can be seen below.

During data collection, these items were re-categorized into the following categories - Beauty, Diet/ Gym Food, Sugary Snack and Supplement, value counts can also be seen below. For this investigation the allocated categories will be used, as they contain the same number of products but spread over fewer groupings. In many cases the allocated categories more accuratly group the products. For example table(ds$"Allocated Category") see four items that can be accurately categorized together as sugary snacks.

## Summary
summary(ds)

 Date Tabulated       Allocated Category Native Category    Description            Coles         Coles Sale      Woolworths   
 Min.   :2017-09-07   Length:489         Length:489         Length:489         Min.   : 1.16   Min.   : 1.00   Min.   : 1.00  
 1st Qu.:2017-09-07   Class :character   Class :character   Class :character   1st Qu.: 4.95   1st Qu.: 4.39   1st Qu.: 4.50  
 Median :2017-09-17   Mode  :character   Mode  :character   Mode  :character   Median :12.65   Median : 9.90   Median :11.50  
 Mean   :2017-09-14                                                            Mean   :12.93   Mean   :10.94   Mean   :11.61  
 3rd Qu.:2017-09-18                                                            3rd Qu.:18.10   3rd Qu.:16.28   3rd Qu.:16.50  
 Max.   :2017-09-18                                                            Max.   :51.70   Max.   :51.70   Max.   :47.00  
 Woolworths Sale     Unit               Amount      
 Min.   : 0.50   Length:489         Min.   :   1.0  
 1st Qu.: 3.50   Class :character   1st Qu.:   1.0  
 Median : 9.40   Mode  :character   Median :  60.0  
 Mean   :10.16                      Mean   : 142.4  
 3rd Qu.:14.00                      3rd Qu.: 200.0  
 Max.   :40.70                      Max.   :2000.0  

## Checking the column names and corresponding data types
sapply(ds, typeof)

    Date Tabulated Allocated Category    Native Category        Description              Coles         Coles Sale         Woolworths 
          "double"        "character"        "character"        "character"           "double"           "double"           "double" 
   Woolworths Sale               Unit             Amount 
          "double"        "character"          "integer" 

## Value counts for the native categories
table(ds$`Native Category`)

                     BAKING                DAIRY & EGGS DIETARY, HEALTH & LIFESTYLE                      DRINKS                 FROZEN FOOD 
                          6                           4                         152                           4                          10 
            HEALTH & BEAUTY      PACKAGED & READY MEALS                      PANTRY 
                        205                           4                         104 

## Value counts for the allocated categories
table(ds$`Allocated Category`)

        Beauty Diet/ Gym Food   Sugary Snack     Supplement 
           205             68            128             88 

## Exploring the Allocated Categories
ds[c(281,286,291,300),c(2,3,4)]

#Exploring Unit and Amount - Data Selection
ds[c(9,47,17,234,283,409),c(2,4,9,10)]

#Value Counts for Unit
table(ds$`Unit`)

   g   mL pack 
 200   55  234 

The attributes “Unit” and “Amount” refer to the unit of measurement the product comes in and the amount, respectively (see exploration code blocks below). Value counts were used to check the categorical attribute Unit.

A scatter plot and a stacked histogram were used explore amount.

The exploration up until this point has highlighted numerous challenges working with the data set. The products are distributed between three units, grams (g), milli litres (mL) & pack. Pack has a different meaning in the context of the products. For example ds[c(9,47,17,234,283,409),c(2,4,9,10)] shows Tresemme Extra Hold Hair Spray (75g) has units g, with an amount of 75. John Frieda Medium Ash Blonde Hair Colour (1 pack) has the unit pack (not volume or mass) and a quantity of one. In the Supplement category Ostelin Vitamin D & Calcium Tablets (180 pack) has unit pack and quantity 180. The data will be explored further and preprocessed, as described below.

Exploring the Native Continuous Data with Scatter Plots

Prior to any transformations, the data was also explored in a qualitative fashion with scatterplots (see below).

The scatter show (qualitively), that the full price between Woolworhts and Coles is quite well correlated, while the sale price between Woolworths and Coles is poorly correlated by comparison (R squared of 0.988 and 0.735, respectively). This indicates that Coles and Woolworths employ different sale pricing stratergy.

Bar Charts of Native Data

These data were plotted into bar charts for Woolworths (denoted WW), Woolworths Sale (denoted WW Sale), Coles and Coles Sale.

As mentioned prior, these data contain a variety of volumes / units / quantites. The plot below shows the “Beauty” allocated category gives no outliers accrross the four plots, and has well distributed bounds. “Beauty” will be left as is for the subsequent analysis. “Sugary Snack” & “Diet/ Gym Food” will be converted all into price per unit 1000 g. For this exercise we will assume 1 g = 1 mL (a reasonable assumption as any liquids from these categories will be mostly water, and 1 mL water has 1 g of mass). “Supplement” has minimal outliers and well distributed bounds, and will be left as is for the subsequent analysis.

Restructuring the Data

These data were broken into their respective categories.

beauty_filter <- ds$`Allocated Category` == "Beauty"
beauty_data <- ds[beauty_filter,]
sugarySnack_filter <- ds$`Allocated Category` == "Sugary Snack"
sugarySnack_data <- ds[sugarySnack_filter,]
dietGymFood_filter <- ds$`Allocated Category` == "Diet/ Gym Food"
dietGymFood_data <- ds[dietGymFood_filter,]
supplement_filter <- ds$`Allocated Category` == "Supplement"
supplement_data <- ds[supplement_filter,]

Resultant units were breifly checked.

#Value Counts for Unit
table(sugarySnack_data$`Unit`)

  g  mL 
116  12 

#Value Counts for Unit
table(dietGymFood_data$`Unit`)

 g mL 
63  5 

Price per 1000g was generated for “Sugary Snack”.

sugarySnack_data$PriceMass <- 1000 / sugarySnack_data$Amount
sugarySnack_data$Per1000gPrice_Coles <- sugarySnack_data$PriceMass*sugarySnack_data$Coles
sugarySnack_data$Per1000gPrice_WW <- sugarySnack_data$PriceMass*sugarySnack_data$Woolworths
sugarySnack_data$Per1000gPrice_ColesSale <- sugarySnack_data$PriceMass*sugarySnack_data$'Coles Sale'
sugarySnack_data$Per1000gPrice_WWSale <- sugarySnack_data$PriceMass*sugarySnack_data$'Woolworths Sale'

Price per 1000g was generated for “Diet/ Gym Food”.

dietGymFood_data$PriceMass <- 1000 / dietGymFood_data$Amount
dietGymFood_data$Per1000gPrice_Coles <- dietGymFood_data$PriceMass * dietGymFood_data$Coles
dietGymFood_data$Per1000gPrice_WW <- dietGymFood_data$PriceMass * dietGymFood_data$Woolworths
dietGymFood_data$Per1000gPrice_ColesSale <- dietGymFood_data$PriceMass * dietGymFood_data$'Coles Sale'
dietGymFood_data$Per1000gPrice_WWSale <- dietGymFood_data$PriceMass * dietGymFood_data$'Woolworths Sale'

These data were exported to .csv and recombined with the Beauty and Supplment data to give dsAdjusted.

write.csv(dietGymFood_data, file = "DietGymFood.csv")
write.csv(sugarySnack_data, file = "SugarySnack.csv")
dsAdjusted <- gsheet2tbl('https://docs.google.com/spreadsheets/d/1VQh07x0OQaaADhflcZ5YY17bB0Ho9kUsPycBsRFYsIk/edit?usp=sharing')

Box Plot of dsAdjusted

Outlier identification was then performed.

summary(sugarySnack_dataAdjusted$ColesAdjusted)

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
   1.90   20.39   27.45   31.46   44.00   83.33 

The items above were filled with the mean of each category in their respective columns, and data was imported once more as dsAdjusted (name was kept constant for simplicities sake).

Box Plot of dsAdjusted with Outliers removed

Scaled Box Plot of dsAdjusted with Outliers removed

These data will now be prepared for paired sample hypothesis testing, through the introduction of a difference attribute (d) in the dsAdjusted data set. Only Coles SALE and Woolworths SALE data will be considered moving forward. Analysis of full price versus sale price is outside the scope of this investigation.

These data are considered to be sufficiently preprocessed for analysis. Paired sample t-testing will be performed on the data set dsAdjusted, on the attribute ‘d’. Due to the nature of the metholodgy (t-test of difference) the remaining outliers will not interfere with the analysis. Due to the size of the data set and the constituent allocated categories (n > 30), we will assume normality. QQ plots were breifly explored, however were deemed to be unesscesary. These data were broken into their constituent categories for processing also.

beauty_filter_d <- dsAdjusted$`Allocated Category` == "Beauty"
beauty_data_d <- dsAdjusted[beauty_filter_d,]
sugarySnack_filter_d <- dsAdjusted$`Allocated Category` == "Sugary Snack"
sugarySnack_data_d <- dsAdjusted[sugarySnack_filter_d,]
dietGymFood_filter_d <- dsAdjusted$`Allocated Category` == "Diet/ Gym Food"
dietGymFood_data_d <- dsAdjusted[dietGymFood_filter_d,]
supplement_filter_d <- dsAdjusted$`Allocated Category` == "Supplement"
supplement_data_d <- dsAdjusted[supplement_filter_d,]

Descriptive Statistics of Mean Difference - All Categories

Descriptive Statistics of Mean Difference - Beauty Category

Descriptive Statistics of Mean Difference - Sugary Snack Category (per 1000 g)

Descriptive Statistics of Mean Difference - Diet/ Gym Food Category (per 1000 g)

Descriptive Statistics of Mean Difference - Supplement Category

Hypothesis Test

We will now perform paired sample t-tests.

Paired Sample t-test - All Categories

    Paired t-test

data:  dsAdjusted$ColesSaleAdjusted and dsAdjusted$WoolworthsSaleAdjusted
t = 4.2968, df = 488, p-value = 2.093e-05
alternative hypothesis: true difference in means is not equal to 0
95 percent confidence interval:
 0.914826 2.456462
sample estimates:
mean of the differences 
               1.685644 

Paired Sample t-test - Beauty Category

    Paired t-test

data:  beauty_data_d$ColesSaleAdjusted and beauty_data_d$WoolworthsSaleAdjusted
t = 0.61836, df = 204, p-value = 0.537
alternative hypothesis: true difference in means is not equal to 0
95 percent confidence interval:
 -0.3575356  0.6842673
sample estimates:
mean of the differences 
              0.1633659 

Paired Sample t-test - Sugary Snack Category (per 1000 g)

    Paired t-test

data:  sugarySnack_data_d$ColesSaleAdjusted and sugarySnack_data_d$WoolworthsSaleAdjusted
t = 3.288, df = 127, p-value = 0.001306
alternative hypothesis: true difference in means is not equal to 0
95 percent confidence interval:
 1.225281 4.929407
sample estimates:
mean of the differences 
               3.077344 

Paired Sample t-test - Diet/ Gym Food Category (per 1000 g)

    Paired t-test

data:  dietGymFood_data_d$ColesSaleAdjusted and dietGymFood_data_d$WoolworthsSaleAdjusted
t = 1.1899, df = 67, p-value = 0.2383
alternative hypothesis: true difference in means is not equal to 0
95 percent confidence interval:
 -1.472253  5.818430
sample estimates:
mean of the differences 
               2.173088 

Paired Sample t-test - Supplement Category

    Paired t-test

data:  supplement_data_d$ColesSaleAdjusted and supplement_data_d$WoolworthsSaleAdjusted
t = 4.177, df = 87, p-value = 6.998e-05
alternative hypothesis: true difference in means is not equal to 0
95 percent confidence interval:
 1.483825 4.177993
sample estimates:
mean of the differences 
               2.830909 

The critical value, t∗ for the paired-sample t-test (assuming a two-tailed test with α=0.05) was then calculated.

Critical Value - All Categories

[1] -1.964837

Critical Value - Beauty Category

[1] -1.971661

Critical Value - Sugary Snack Category (per 1000 g)

[1] -1.97882

Critical Value - Diet/ Gym Food Category (per 1000 g)

[1] -1.996008

Critical Value - Supplement Category

[1] -1.987608

Hypothesis Testing Results & Interpretation

All Categories

The t* values are ± 1.96. As t = 4.3 is more extreme than + 1.96, H0 should be rejected (additionally the 95% CI of the mean difference is found to be [0.91 2.46], which does not contain capture H0). There was a statistically significant mean difference between the Woolworths Sale and Coles Sale prices accross all categories.

Beauty Category

The t* values are ± 1.97. As t = 0.62, t is less extreme than than +1.97, H0 cannot be rejected on critical value. The 95% CI of the mean difference is found to be [-0.36 0.68], H0 cannot be rejected on 95% CI. As p > 0.05, we fail to reject H0. There was not a statistically significant mean difference between the Woolworths Sale and Coles Sale prices in the beauty category.

Sugary Snack Category (per 1000 g)

The t* values are ± 1.98. As t = 3.28 is more extreme than + 1.98, H0 should be rejected (additionally the 95% CI of the mean difference is found to be [1.23 4.93], which does not contain capture H0). There was a statistically significant mean difference between the Woolworths Sale and Coles Sale prices in the Sugary Snack category (price per 1000 g).

Diet/ Gym Food Category (per 1000 g)

The t* values are ± 2.0. As t = 1.19, t is less extreme than than +2.0, H0 cannot be rejected on critical value. The 95% CI of the mean difference is found to be [-1.47 5.82], H0 cannot be rejected on 95% CI. As p > 0.05, we fail to reject H0. There was not a statistically significant mean difference between the Woolworths Sale and Coles Sale prices in the diet/ gym food category.

Supplement Category

The t* values are ± 1.99. As t = 4.18 is more extreme than + 1.99, H0 should be rejected (additionally the 95% CI of the mean difference is found to be [1.48 4.18], which does not contain capture H0). There was a statistically significant mean difference between the Woolworths Sale and Coles Sale prices in the supplement category.

Mean Difference Scatter Plots

All Categories

            Summary Stats
n                 489.000
mean(x)            20.756
mean(y)            22.441
mean(D=x-y)        -1.686
SD(D)               8.675
ES(D)              -0.194
r(x,y)              0.884
r(x+y,d)           -0.200
LL 95%CI           -2.456
UL 95%CI           -0.915
t(D-bar)           -4.297
df.t              488.000
pval.t              0.000

Beauty Category

            Summary Stats
n                 205.000
mean(x)            12.292
mean(y)            12.456
mean(D=x-y)        -0.163
SD(D)               3.783
ES(D)              -0.043
r(x,y)              0.671
r(x+y,d)           -0.018
LL 95%CI           -0.684
UL 95%CI            0.358
t(D-bar)           -0.618
df.t              204.000
pval.t              0.537

Sugary Snack Category (per 1000 g)

            Summary Stats
n                 128.000
mean(x)            20.425
mean(y)            23.502
mean(D=x-y)        -3.077
SD(D)              10.589
ES(D)              -0.291
r(x,y)              0.596
r(x+y,d)           -0.442
LL 95%CI           -4.929
UL 95%CI           -1.225
t(D-bar)           -3.288
df.t              127.000
pval.t              0.001

Diet/ Gym Food Category (per 1000 g)

            Summary Stats
n                  68.000
mean(x)            51.898
mean(y)            54.071
mean(D=x-y)        -2.173
SD(D)              15.060
ES(D)              -0.144
r(x,y)              0.817
r(x+y,d)           -0.149
LL 95%CI           -5.818
UL 95%CI            1.472
t(D-bar)           -1.190
df.t               67.000
pval.t              0.238

Supplement Category

            Summary Stats
n                  88.000
mean(x)            16.888
mean(y)            19.719
mean(D=x-y)        -2.831
SD(D)               6.358
ES(D)              -0.445
r(x,y)              0.757
r(x+y,d)           -0.318
LL 95%CI           -4.178
UL 95%CI           -1.484
t(D-bar)           -4.177
df.t               87.000
pval.t              0.000

Discussion

The following was determined over the duration of the investigation,

• There was a statistically significant mean difference between the Woolworths Sale and Coles Sale prices accross all categories. The granovo.ds() function summary stats show WW Sale to have a mean value of AUD 20.76, while Coles Sale has a mean value of AUD 22.44. Woolworths Sale is cheaper overall (over the four categories of goods).

• There was not a statistically significant mean difference between the Woolworths Sale and Coles Sale prices in the beauty category. The granovo.ds() function summary stats show WW Sale to have a mean value of AUD 12.29, while Coles Sale has a mean value of AUD 12.46. While the mean of Woolworths Sale is lower, this is statistically insignificant over the beauty goods.

• There was a statistically significant mean difference between the Woolworths Sale and Coles Sale prices in the Sugary Snack category (price per 1000 g). The granovo.ds() function summary stats show WW Sale to have a mean value of AUD 20.43, while Coles Sale has a mean value of AUD 23.50. Woolworths Sale is cheaper over the sugary snack category per 1000 g.

• There was not a statistically significant mean difference between the Woolworths Sale and Coles Sale prices in the diet/ gym food category (price per 1000 g). The granovo.ds() function summary stats show WW Sale to have a mean value of AUD 51.90, while Coles Sale has a mean value of AUD 54.07. While the mean of Woolworths Sale is lower, this is statistically insignificant over the diet/ gym food goods.

• There was a statistically significant mean difference between the Woolworths Sale and Coles Sale prices in the supplement category. The granovo.ds() function summary stats show WW Sale to have a mean value of AUD 16.89, while Coles Sale has a mean value of AUD 19.72. Woolworths Sale is cheaper over the supplment category.

The strengths of the investigation included high sample size (overall and in sub-categories), controls of sampling in one postcode over a period of only 11 days and choosing manufactured items that were identical. Limitations include working with the sale data (as full price items not on sale were included here). Full prices overall may be more stable with time, and thus a better representation of the price of the supermarket overall. For future studies, sampling at time intervals (t = 1 month, over 24 months) could be performed at full price in one postcode.

Conclusion

Data of almost 500 points was collected from Coles, Coles Sale, Woolworths and Woolworths Sale (postcode 3054, Carlton North, Victoria, Australia, 7 Sep 2017 till 18 Sep 2017) accross the categories BAKING, DAIRY & EGGS DIETARY, HEALTH & LIFESTYLE, DRINKS, FROZEN FOOD, HEALTH & BEAUTY, PACKAGED & READY MEALS and PANTRY (generalized as Beauty, Diet/ Gym Food, Sugary Snack and Supplement). It was determined that Woolworths Sale had statistically significant lower prices overall, in the sugary snack category and in the supplement category.

---

1. http://www.wesfarmers.com.au/util/news-media/article/2017/06/22/coles-creates-115-jobs-at-its-milestone-800th-supermarket↩

2. http://www.smh.com.au/business/supermarket-duopoly-blamed-for-soaring-food-prices-20091108-i3tn.html↩

3. http://www.smh.com.au/business/retail/coles-deepens-price-war-after-slicing-bread-prices-20170712-gxa33p.html↩

4. https://www.frugl.com.au/↩