Objective

The objective of this applied project is to explain the price of avocados using some basic descriptive analysis. This analysis can be used by producers, retailers, and groceries to make decisions about their pricing strategies, advertising strategies, and supply chain strategies among others.

Dataset

Weekly retail scan data for Hass avocados from the Hass Avocado Board: average price per avocado, total volume, type (conventional or organic), year, city (geography), and Mileage (distance in miles) for 41 U.S. cities.

# avocado.csv must be saved in the same folder as this .Rmd file
data <- read.csv("avocado.csv", fileEncoding = "UTF-8-BOM", check.names = FALSE)
names(data) <- trimws(names(data))                    # removes the extra space in "average_price "
data$date <- as.Date(data$date, format = "%Y/%m/%d")

summary(data)
##       date            average_price    total_volume            type      
##  Min.   :2017-12-03   Min.   :0.500   Min.   :    253   Length   :12628  
##  1st Qu.:2018-08-19   1st Qu.:1.100   1st Qu.:  15733   N.unique :    2  
##  Median :2019-06-12   Median :1.320   Median :  94806   N.blank  :    0  
##  Mean   :2019-06-02   Mean   :1.359   Mean   : 325259   Min.nchar:    7  
##  3rd Qu.:2020-03-08   3rd Qu.:1.570   3rd Qu.: 430222   Max.nchar:   12  
##  Max.   :2020-11-29   Max.   :2.780   Max.   :5660216                    
##       year          geography        Mileage    
##  Min.   :2017   Length   :12628   Min.   : 111  
##  1st Qu.:2018   N.unique :   41   1st Qu.:1097  
##  Median :2019   N.blank  :    0   Median :2193  
##  Mean   :2019   Min.nchar:    5   Mean   :1911  
##  3rd Qu.:2020   Max.nchar:   20   3rd Qu.:2632  
##  Max.   :2020                     Max.   :2998
str(data)
## 'data.frame':    12628 obs. of  7 variables:
##  $ date         : Date, format: "2017-12-03" "2017-12-03" ...
##  $ average_price: num  1.39 1.44 1.07 1.62 1.43 1.58 1.14 1.77 1.4 1.88 ...
##  $ total_volume : int  139970 3577 504933 10609 658939 38754 86646 1829 488588 21338 ...
##  $ type         : chr  "conventional" "organic" "conventional" "organic" ...
##  $ year         : int  2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 ...
##  $ geography    : chr  "Albany" "Albany" "Atlanta" "Atlanta" ...
##  $ Mileage      : int  2832 2832 2199 2199 2679 2679 827 827 2998 2998 ...

Class analysis

library(ggplot2)

# Simple histogram
hist(data$average_price,
     main = "Histogram of average_price",
     xlab = "Price in USD (US Dollar)")

# Price distribution by type
ggplot(data, aes(x = average_price, fill = type)) + 
  geom_histogram(bins = 30, col = "red") + 
  scale_fill_manual(values = c("blue", "green")) +
  ggtitle("Frequency of Average Price - Organic vs. Conventional")

# Total volume by city
ggplot() + 
  geom_col(data, mapping = aes(x = reorder(geography, total_volume), 
                               y = total_volume, fill = year)) +
  xlab("geography") +
  ylab("total_volume") +
  theme(axis.text.x = element_text(angle = 90, size = 7))

Question 1: Price range

summary(data$average_price)
##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
##   0.500   1.100   1.320   1.359   1.570   2.780
data[which.min(data$average_price), ]   # lowest price
##            date average_price total_volume         type year geography Mileage
## 1755 2018-04-22           0.5      2335867 conventional 2018   Houston    1656
data[which.max(data$average_price), ]   # highest price
##            date average_price total_volume    type year geography Mileage
## 6712 2019-07-14          2.78        15994 organic 2019 San Diego     253

Prices range from $0.50 (conventional, Houston, week of April 22, 2018) to $2.78 (organic, San Diego, week of July 14, 2019).

Question 2: Top cities by dollar sales, 2017 and 2018

# Dollar sales = average price per avocado x number of avocados sold
data$dollar_sales <- data$average_price * data$total_volume

# Pivot table: total dollar sales by year, type, and city
sales <- aggregate(dollar_sales ~ year + type + geography,
                   data = subset(data, year %in% c(2017, 2018)), FUN = sum)

# Rank cities within each year and type, keep the top 3
sales$rank <- ave(-sales$dollar_sales, sales$year, sales$type, FUN = rank)
top3 <- sales[sales$rank <= 3, ]
top3 <- top3[order(top3$year, top3$type, top3$rank), ]
top3$dollar_sales <- format(round(top3$dollar_sales), big.mark = ",")
top3
##     year         type     geography dollar_sales rank
## 81  2017 conventional   Los Angeles   13,456,647    1
## 101 2017 conventional      New York    9,322,034    2
## 29  2017 conventional       Chicago    5,161,767    3
## 103 2017      organic      New York      720,451    1
## 83  2017      organic   Los Angeles      622,945    2
## 147 2017      organic       Seattle      550,111    3
## 82  2018 conventional   Los Angeles  153,526,216    1
## 102 2018 conventional      New York  118,997,580    2
## 142 2018 conventional San Francisco   59,780,808    3
## 104 2018      organic      New York    9,391,204    1
## 84  2018      organic   Los Angeles    8,561,139    2
## 148 2018      organic       Seattle    5,920,932    3

Los Angeles led conventional sales and New York led organic sales in both 2017 and 2018. Note that the 2017 data only covers December 2017.

Question 3: Custom figures

Figure 1: Organic vs. conventional prices over time

# Average price across all 41 cities for each week
weekly <- aggregate(average_price ~ date + type, data = data, FUN = mean)

ggplot(weekly, aes(x = date, y = average_price, color = type)) +
  geom_line() +
  scale_color_manual(values = c(conventional = "steelblue", organic = "darkgreen")) +
  labs(title = "Figure 1. Weekly average avocado price across 41 cities",
       x = NULL, y = "Average price per avocado (USD)", color = "Type")

# Numbers behind the insight
aggregate(average_price ~ type, data = data, FUN = mean)    # organic premium
##           type average_price
## 1 conventional      1.142566
## 2      organic      1.575117
conv_weekly <- subset(weekly, type == "conventional")
conv_weekly[which.min(conv_weekly$average_price), ]          # cheapest week
##           date         type average_price
## 111 2020-02-02 conventional      0.882439
conv_weekly[which.max(conv_weekly$average_price), ]          # most expensive week
##          date         type average_price
## 83 2019-07-21 conventional      1.537317

Organic avocados cost more than conventional every single week (about $1.58 vs. $1.14 on average). Prices are lowest in winter and highest in summer, and the cheapest week was February 2, 2020, Super Bowl Sunday.

Figure 2: Does distance make avocados more expensive?

# One point per city: its average price and its Mileage
city <- aggregate(cbind(average_price, Mileage) ~ geography + type, data = data, FUN = mean)

ggplot(city, aes(x = Mileage, y = average_price)) +
  geom_point(color = "darkgreen", size = 2) +
  geom_smooth(method = "lm", se = FALSE, color = "black") +
  facet_wrap(~ type) +
  labs(title = "Figure 2. Do farther cities pay more for avocados?",
       x = "Mileage (distance in miles)", y = "Average price per avocado (USD)")

# Correlation between distance and price for each type
sapply(split(city, city$type), function(d) round(cor(d$Mileage, d$average_price), 2))
## conventional      organic 
##         0.08        -0.43
# Cheapest and most expensive cities for conventional avocados
conv_city <- subset(city, type == "conventional")
head(conv_city[order(conv_city$average_price), ], 3)
##           geography         type average_price Mileage
## 17          Houston conventional     0.7868831    1656
## 11 Dallas/Ft. Worth conventional     0.8100000    1449
## 29   Phoenix/Tucson conventional     0.8197403     482
tail(conv_city[order(conv_city$average_price), ], 3)
##               geography         type average_price Mileage
## 16 Hartford/Springfield conventional      1.378766    2906
## 36        San Francisco conventional      1.380000     283
## 37              Seattle conventional      1.425325    1029