library(tidyverse)
library(janitor)Analysis
Mixed beverage gross receipts analysis
The goal of this notebook is to analyze seasonal and monthly sales fluctuations in alcohol sales over the last five years (2019 through 2023) in Austin neighborhoods using the data I cleaned from the Texas Open Data Portal.
I will analyze how alcohol sales change through the year, how these changes correlate with seasonal factors and which neighborhoods are affected by these fluctuations. This analysis will also examine how major events throughout the years, like SXSW and ACL impact sales across neighborhoods. To further this analysis, I will break down sales by alcohol type and identify if any drive more revenue in different areas and seasons.
Goals
I will answer the following questions in this notebook:
- Which months had the highest average total alcohol sales across Austin in the last five years?
- How have total alcohol sales in Austin changed from 2019 to 2023?
- Which type of alcohol drives the most revenue citywide, and does this vary by season?
- Which neighborhoods have the highest and lowest average sales?
- Which neighborhoods see the largest spikes in sales during ACL in October?
- Which neighborhoods see the largest spikes in sales during SXSW in March?
Setup
Import
I am importing my cleaned data.
# Importing the clean data
mixedbev <- read_rds("data-processed/01-mixedbev.rds")
mixedbev |> glimpse()Rows: 73,721
Columns: 28
$ taxpayer_number <dbl> 32057827142, 32049953949, 32058372064, 3203…
$ taxpayer_name <chr> "ROCKROSE RESTAURANT L.L.C. DBA MIA ITALIAN…
$ taxpayer_address <chr> "11420 ROCK ROSE AVE STE 120", "PO BOX 6850…
$ taxpayer_city <chr> "AUSTIN", "AUSTIN", "AUSTIN", "MANCHACA", "…
$ taxpayer_state <chr> "TX", "TX", "TX", "TX", "TX", "TX", "TX", "…
$ taxpayer_county <dbl> 227, 227, 227, 227, 227, 227, 227, 227, 227…
$ location_number <dbl> 1, 1, 1, 3, 1, 1, 2, 1, 1, 2, 1, 1, 1, 294,…
$ location_name <chr> "MIA ITALIAN TAPAS & BAR", "THE LODGE", "EL…
$ location_address <chr> "11420 ROCK ROSE AVE UNIT 400", "411 E 6TH …
$ location_city <chr> "AUSTIN", "AUSTIN", "AUSTIN", "AUSTIN", "AU…
$ location_state <chr> "TX", "TX", "TX", "TX", "TX", "TX", "TX", "…
$ location_county <dbl> 227, 227, 227, 227, 227, 227, 227, 227, 227…
$ inside_outside_city_limits <chr> "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y"…
$ tabc_permit_number <chr> "MB927828", "MB848429", "MB630996", "MB1061…
$ liquor_receipts <dbl> 22983, 151396, 3324, 12204, 57536, 31245, 5…
$ wine_receipts <dbl> 14304, 128, 0, 3326, 1979, 572, 23573, 9, 3…
$ beer_receipts <dbl> 3953, 24110, 3719, 1610, 36461, 33655, 1084…
$ cover_charge_receipts <dbl> 0, 7219, 0, 0, 0, 0, 0, 7453, 0, 0, 0, 31, …
$ total_receipts <dbl> 41240, 182853, 7043, 17140, 95976, 65472, 9…
$ responsibility_begin <date> 2015-11-24, 2013-08-08, 2015-10-15, 2020-1…
$ responsibility_end <date> NA, NA, NA, 2023-07-12, NA, NA, NA, NA, NA…
$ obligation_end <date> 2021-06-30, 2023-06-30, 2019-08-31, 2022-0…
$ taxpayer_zip_code <chr> "78758", "78768", "78704", "78652", "78701"…
$ location_zip_code <chr> "78758", "78701", "78704", "78704", "78701"…
$ yr <dbl> 2021, 2023, 2019, 2022, 2019, 2019, 2019, 2…
$ mo <dbl> 6, 6, 8, 5, 8, 6, 5, 6, 1, 6, 10, 1, 6, 12,…
$ mo_label <ord> Jun, Jun, Aug, May, Aug, Jun, May, Jun, Jan…
$ mo_long <ord> June, June, August, May, August, June, May,…mixedbev# A tibble: 73,721 × 28
taxpayer_number taxpayer_name taxpayer_address taxpayer_city taxpayer_state
<dbl> <chr> <chr> <chr> <chr>
1 32057827142 ROCKROSE RESTA… 11420 ROCK ROSE… AUSTIN TX
2 32049953949 KLODGE, INC. PO BOX 685084 AUSTIN TX
3 32058372064 EL BORREGO DE … 3900 S CONGRESS… AUSTIN TX
4 32039907699 DESI & FRIENDS… 12700 ENCINO DR MANCHACA TX
5 32039685030 WL STAR BAR, L… 600 W 6TH ST AUSTIN TX
6 17427121425 CASINO EL CAMI… 517 E 6TH ST AUSTIN TX
7 32045024711 SWOON HOSPITAL… 2406 HOMEDALE C… AUSTIN TX
8 32069216045 407 BARLIB LLC PO BOX 685084 AUSTIN TX
9 32055355815 SOUTH FIRST PI… 2901 S 1ST ST S… AUSTIN TX
10 32051844242 EATX COFFEE LLC 22601 STATE HIG… SPICEWOOD TX
# ℹ 73,711 more rows
# ℹ 23 more variables: taxpayer_county <dbl>, location_number <dbl>,
# location_name <chr>, location_address <chr>, location_city <chr>,
# location_state <chr>, location_county <dbl>,
# inside_outside_city_limits <chr>, tabc_permit_number <chr>,
# liquor_receipts <dbl>, wine_receipts <dbl>, beer_receipts <dbl>,
# cover_charge_receipts <dbl>, total_receipts <dbl>, …Average monthly sales
Which months had the highest average total alcohol sales across Austin in the last five years?
I am grouping the data by month (mo_label) and year (yr), then using summarise() to calculate the sum totals (mo_yr_receipts) for the mo/year first. Then, I get the mean of those by months (avg_mo_receipts) using summarise again. Finally, I am arranging the data by avg_mo_receipts in descending order so that the data is showing the months with the highest sales first.
# Average monthly total alcohol sales
average_monthly_receipts <- mixedbev |>
group_by(mo_label, yr) |>
summarise(mo_yr_receipts = sum(total_receipts)) |>
group_by(mo_label) |>
summarise(avg_mo_receipts = mean(mo_yr_receipts)) |>
arrange(desc(avg_mo_receipts))`summarise()` has grouped output by 'mo_label'. You can override using the
`.groups` argument.average_monthly_receipts# A tibble: 12 × 2
mo_label avg_mo_receipts
<ord> <dbl>
1 Oct 88881854.
2 Mar 81599109.
3 Sep 80173455.
4 May 77233152.
5 Dec 76094336
6 Nov 74253798
7 Apr 73077065.
8 Jun 72817260.
9 Jul 71377155.
10 Aug 69742374.
11 Feb 67537899.
12 Jan 62923171.Data takeaway: On average, Austin sees the highest average alcohol sales in October. The city sees the average lowest alcohol sales in January.
Visualization: Monthly sales
I will use ggplot to create a bar chart from my data that show the average monthly alcohol sales in Austin. I will be using this chart for my summary.
# Creating a vertical column chart for average monthly total alcohol sales
average_monthly_receipts |>
ggplot(aes(x = mo_label, y = avg_mo_receipts)) +
geom_col(fill = "steelblue") +
scale_y_continuous(
breaks = c(20000000, 40000000, 60000000, 80000000, 100000000),
labels = c("20M", "40M", "60M", "80M", "100M")
) +
labs(
title = "Austin sells the most alcoholic beverages in October",
subtitle = str_wrap("On average, Austin sees the highest average alcohol sales in October. The city sees the average lowest alcohol sales in January."),
caption = "By Gabriella Gonzales. Source: Texas Comptroller of Public Accounts",
x = "Month",
y = "Average Total Receipts"
)
ggsave("figures/average_monthly_sales.png")Saving 7 x 5 in imageAnnual sales trends
How have total alcohol sales in Austin changed from 2019 to 2023?
I am grouping the data by year (yr), then using summarize() to calculate the sums of the total_receipts. Then I am arranging the data by yr in ascending order so that the data is showing in order by year.
# Sales by year
sales_by_year <- mixedbev |>
group_by(yr) |>
summarize(total_sales = sum(total_receipts, na.rm = TRUE)) |>
arrange(yr)
sales_by_year# A tibble: 5 × 2
yr total_sales
<dbl> <dbl>
1 2019 882805713
2 2020 425970744
3 2021 858984427
4 2022 1130534665
5 2023 1180257607Data takeaway: Average alcohol sales in Austin have risen by about 34% between 2019 and 2023, despite a dip in sales in 2020.
Visualization: Annual sales
I will use ggplot to create a bar chart from my data that shows the alcohol sales by year in Austin.
# Creating a bar chart for sales by year
sales_by_year |>
ggplot(aes(x = yr, y = total_sales)) +
geom_col(fill = "steelblue") +
geom_text(aes(label = total_sales),
vjust = .5,
hjust = 1.1,
color = "white") +
scale_y_continuous(
breaks = c(200000000, 400000000, 600000000, 800000000, 1000000000, 1200000000),
labels = c("200M", "400M", "600M", "800M", "1B", "1.2B")
) +
coord_flip() +
labs(
title = "Total alcohol sales by year in Austin (2019–2023)",
subtitle = str_wrap("Average alcohol sales in Austin have risen by about 34% between 2019 and 2023, despite a dip in sales in 2020."),
caption = "By Gabriella Gonzales. Source: Texas Comptroller of Public Accounts",
x = "Year",
y = "Total Receipts"
)
Sales by alcohol type
Which type of alcohol drives the most revenue citywide, and does this vary by season?
I am grouping the data by month (mo_label) and year (yr), then using summarise() to calculate the sum totals for the mo/year for each alcohol type. Then, I get the mean of those by month using summarise again. Finally, I am arranging the data by mo_label to display the results in chronological order by month.
# Average monthly sales by alcohol type
avg_by_type <- mixedbev |>
group_by(mo_label, yr) |>
summarise(
mo_yr_liquor = sum(liquor_receipts),
mo_yr_wine = sum(wine_receipts),
mo_yr_beer = sum(beer_receipts),
) |>
group_by(mo_label) |>
summarise(
avg_mo_liquor = mean(mo_yr_liquor),
avg_mo_wine = mean(mo_yr_wine),
avg_mo_beer = mean(mo_yr_beer),
) |>
arrange(mo_label)`summarise()` has grouped output by 'mo_label'. You can override using the
`.groups` argument.avg_by_type# A tibble: 12 × 4
mo_label avg_mo_liquor avg_mo_wine avg_mo_beer
<ord> <dbl> <dbl> <dbl>
1 Jan 38113734. 9733503. 14838484.
2 Feb 41016909. 10782751 15499683.
3 Mar 50656142 11348670. 19289354.
4 Apr 45690418 10338782. 16755994
5 May 48809529. 10731652. 17410794.
6 Jun 45987092. 9621532. 16948460.
7 Jul 45758129. 9023091. 16366756.
8 Aug 43881150. 9363101. 16204009.
9 Sep 50247311. 10684106 18884303.
10 Oct 53847884. 13013958 21655787.
11 Nov 44892646. 12002320. 17036328.
12 Dec 47126036. 12383023. 16236911.Data takeaway: Liquor drives the most revenue in Austin year-round, with spikes during March and October which host SXSW and ACL, respectively.
Visualization: Sales by alcohol type
I will pivot longer so that I can plot the data into a line chart.
# Pivot longer
avg_by_type_long <- avg_by_type |>
pivot_longer(
cols = c(avg_mo_liquor,
avg_mo_wine,
avg_mo_beer),
names_to = "alcohol_type",
values_to = "average_receipts"
)
avg_by_type_long# A tibble: 36 × 3
mo_label alcohol_type average_receipts
<ord> <chr> <dbl>
1 Jan avg_mo_liquor 38113734.
2 Jan avg_mo_wine 9733503.
3 Jan avg_mo_beer 14838484.
4 Feb avg_mo_liquor 41016909.
5 Feb avg_mo_wine 10782751
6 Feb avg_mo_beer 15499683.
7 Mar avg_mo_liquor 50656142
8 Mar avg_mo_wine 11348670.
9 Mar avg_mo_beer 19289354.
10 Apr avg_mo_liquor 45690418
# ℹ 26 more rowsNow, I will create my line chart using ggplot. I will be using this chart for my summary.
# Creating the line chart
avg_by_type_long |>
ggplot(aes(x = mo_label,
y = average_receipts,
group = alcohol_type)) +
geom_line(aes(color = alcohol_type)) +
geom_point(aes(color = alcohol_type)) +
scale_y_continuous(
breaks = c(10000000, 20000000, 30000000, 40000000, 50000000),
labels = c("10M", "20M", "30M", "40M", "50M")
) +
labs(
title = "Liquor drives the most revenue in Austin year-round",
subtitle = str_wrap("Liquor drives the most revenue in Austin year-round, with spikes during March and October which host SXSW and ACL, respectively."),
caption = "By Gabriella Gonzales. Source: Texas Comptroller of Public Accounts",
x = "Month",
y = "Average Receipts",
color = "Alcohol Type"
) +
scale_color_manual(
values = c("avg_mo_liquor" = "skyblue",
"avg_mo_beer" = "springgreen",
"avg_mo_wine" = "indianred"),
labels = c("Average beer receipts", "Average liquor receipts", "Average wine receipts"))
ggsave("figures/alcohol_types.png")Saving 7 x 5 in imageSales by ZIP code
Which neighborhoods have the highest average sales?
I am grouping the data by month (mo_label) and year (yr), then using summarise() to calculate the sum totals (zip_total_receipts) for the mo/year first. Then, I get the mean of those by months (avg_zip_sales) using summarise again. Finally, I am arranging the data by avg_zip_sales in descending order so that the data is showing the ZIP codes with the highest average sales first.
# Calculate average sales by ZIP code
avg_sales_by_zip <- mixedbev |>
group_by(location_zip_code, yr, mo_label) |>
summarise(zip_total_receipts = sum(total_receipts)) |>
group_by(location_zip_code) |>
summarise(avg_zip_sales = mean(zip_total_receipts)) |>
arrange(desc(avg_zip_sales))`summarise()` has grouped output by 'location_zip_code', 'yr'. You can override
using the `.groups` argument.avg_sales_by_zip# A tibble: 46 × 2
location_zip_code avg_zip_sales
<chr> <dbl>
1 78701 25942124.
2 78704 9214430.
3 78758 6329404.
4 78702 5514537.
5 78703 2344716.
6 78759 1899343.
7 78748 1712802.
8 78752 1674980.
9 78757 1480878.
10 78735 1390114.
# ℹ 36 more rowsData Takeaway: On average, the ZIP codes 78701, 78704 and 78758 sell the most alcohol, respectively.
Which neighborhoods have the lowest average sales?
Now I will rearrange the data to look at the ZIP codes with lowest average sales. To do this I am grouping the data by location_zip_code, then using summarize() to calculate the averages of the total_receipts. Then I am arranging the data by location_zip_code in ascending order so that the data is showing the ZIP codes with least average sales from top to bottom.
avg_sales_by_zip |>
arrange(avg_zip_sales)# A tibble: 46 × 2
location_zip_code avg_zip_sales
<chr> <dbl>
1 78774 1369.
2 78713 8593.
3 78742 13890.
4 78739 35148.
5 78733 36377.
6 78724 44431.
7 78721 50716.
8 78754 50795.
9 78747 59197.
10 78736 71135.
# ℹ 36 more rowsData Takeaway: On average, the ZIP codes 78774, 78713 and 78742 sell the least amount of alcohol, respectively.
Visualization: Sales by ZIP code
I will create a map that displays Austin alcohol sales by ZIP code in Datawrapper. I will be using this chart for my summary.
First, I am taking the avg_sales_by_zip I created in the last section and exporting the data for Datawrapper.
# Exporting for Datawrapper
avg_sales_by_zip |> write_csv("data-processed/avg_sales_by_zip.csv")The avg_sales_by_zip graphic:
This visualization can also be found here: https://www.datawrapper.de/_/Md5BY/
Average October sales (ACL)
Which neighborhoods see the largest spikes in sales during ACL in October?
First, I am grouping the data by location_zip_code, yr and mo_label, then using summarise() to calculate the sums of the total_receipts.
# Calculate total sales per ZIP, year and month
monthly_sales_zip <- mixedbev |>
group_by(location_zip_code, yr, mo_label) |>
summarize(monthly_sales_totals = sum(total_receipts), .groups = "drop")
monthly_sales_zip# A tibble: 2,670 × 4
location_zip_code yr mo_label monthly_sales_totals
<chr> <dbl> <ord> <dbl>
1 78660 2019 Jan 107431
2 78660 2019 Feb 99171
3 78660 2019 Mar 129945
4 78660 2019 Apr 122340
5 78660 2019 May 128043
6 78660 2019 Jun 138109
7 78660 2019 Jul 129294
8 78660 2019 Aug 145547
9 78660 2019 Sep 159989
10 78660 2019 Oct 152105
# ℹ 2,660 more rowsThen, I am calculating the the average monthly totals for October. To do this I filter for mo_label == “Oct”, group the data by the data by location_zip_code, and use summarise to get the mean of the monthly_sales_totals . Then I arrange the data by the averages (avg_october_sales_zip).
# Calculate average monthly totals for October
average_october_sales <- monthly_sales_zip |>
filter(mo_label == "Oct") |>
group_by(location_zip_code) |>
summarise(avg_october_sales_zip = mean(monthly_sales_totals), .groups = "drop") |>
arrange(desc(avg_october_sales_zip))
average_october_sales# A tibble: 46 × 2
location_zip_code avg_october_sales_zip
<chr> <dbl>
1 78701 30604935
2 78704 13702950.
3 78758 7098776.
4 78702 6236167.
5 78703 2593283.
6 78759 1920599.
7 78748 1863710
8 78752 1845346
9 78712 1762957.
10 78735 1664269.
# ℹ 36 more rowsTo analyze how October sales compared to yearly averages, I calculate the average yearly total alcohol sales for each ZIP code. First, I roup the data by location_zip_code and yr, and calculate the yearly total receipts for each ZIP code. Then, I group the data by location_zip_code again to calculate the average yearly sales across all years. Finally, I arrange the ZIP codes in descending order based on their average yearly sales.
# Calculate average yearly totals
average_yearly_sales <- monthly_sales_zip |>
group_by(location_zip_code, yr) |>
summarize(yearly_total = sum(monthly_sales_totals), .groups = "drop") |>
group_by(location_zip_code) |>
summarize(avg_yearly_sales = mean(yearly_total), .groups = "drop") |>
arrange(desc(avg_yearly_sales))
average_yearly_sales# A tibble: 46 × 2
location_zip_code avg_yearly_sales
<chr> <dbl>
1 78701 311305482
2 78704 110573161.
3 78758 75952845.
4 78702 66174440.
5 78703 28136590.
6 78759 22792112.
7 78748 20553630.
8 78752 20099765
9 78757 17770541.
10 78735 16681370
# ℹ 36 more rowsI join the average October sales data with the average yearly sales data to calculate the contribution of October sales to the yearly total for each ZIP code.
# Join averages
join_averages <- average_october_sales |>
left_join(average_yearly_sales, by = "location_zip_code")
join_averages# A tibble: 46 × 3
location_zip_code avg_october_sales_zip avg_yearly_sales
<chr> <dbl> <dbl>
1 78701 30604935 311305482
2 78704 13702950. 110573161.
3 78758 7098776. 75952845.
4 78702 6236167. 66174440.
5 78703 2593283. 28136590.
6 78759 1920599. 22792112.
7 78748 1863710 20553630.
8 78752 1845346 20099765
9 78712 1762957. 10917934.
10 78735 1664269. 16681370
# ℹ 36 more rowsNext, I need to calculate the percentage of average yearly sales that occurred in October. I first use mutate() to find the percentage for each ZIP code and round it to three decimal places. I then filter for ZIP codes where October sales contributed at least 10% of the yearly total and arrange in descending order.
# Calculate percentage
oct_sales_percentages <- join_averages |>
mutate(october_percentage = round((avg_october_sales_zip / avg_yearly_sales) * 100, 3)) |>
filter(october_percentage >= 10) |>
arrange(desc(october_percentage))
oct_sales_percentages# A tibble: 8 × 4
location_zip_code avg_october_sales_zip avg_yearly_sales october_percentage
<chr> <dbl> <dbl> <dbl>
1 78712 1762957. 10917934. 16.1
2 78736 113541. 853618. 13.3
3 78713 10493. 80202. 13.1
4 78704 13702950. 110573161. 12.4
5 78733 44872 385595 11.6
6 78722 919702. 9089731. 10.1
7 78737 383533. 3795001. 10.1
8 78719 1567981. 15678001. 10.0Data takeaway: The ZIP codes 78712, 78736 and 78713 make the highest average contributions to alcohol sales during October.
Visualization: October sales
I will use ggplot to create a bar chart from my data that shows the ZIP codes that make the highest average contribution to alcohol sales during October. I will be using this chart for my summary.
oct_sales_percentages |>
ggplot(aes(
x = reorder(location_zip_code, october_percentage),
y = october_percentage,
fill = factor(location_zip_code))) +
geom_col(fill = "steelblue") +
geom_text(aes(
label = sprintf("%.2f", october_percentage)),
vjust = 0.5,
hjust = -0.2,
color = "black") +
coord_flip() +
labs(
title = "Austin ZIP codes make higher contributions during ACL in October",
subtitle = str_wrap("The ZIP codes 78712, 78736 and 78713 make the highest average contributions to alcohol sales during October."),
caption = "By Gabriella Gonzales. Source: Texas Comptroller of Public Accounts",
x = "ZIP Code",
y = "October's average percentage of year sales (%)") +
theme(
plot.margin = margin(t = 10, r = 30, b = 10, l = 10)
) +
scale_y_continuous(expand = expansion(mult = c(0, 0.1)))
ggsave("figures/oct_sales_percentages.png")Saving 7 x 5 in imageOctober sales 2023
After looking at the average alcohol sales activity during October, I want to analyze on specific year. I will look at 2023 in this section.
The first step in analyzing October 2023 alcohol sales is to filter the monthly_sales_zip data for October (mo_label == “Oct”) and the year 2023 (yr == 2023). Then, I group the data by location_zip_code to calculate the total alcohol sales for October in each ZIP code. The summarize() function is used to sum up the sales, and the results are arranged in descending order.
# Filter October sales for 2023
october_2023_sales <- monthly_sales_zip |>
filter(mo_label == "Oct", yr == 2023) |>
group_by(location_zip_code) |>
summarize(october_2023_sales = sum(monthly_sales_totals), .groups = "drop") |>
arrange(desc(october_2023_sales))
october_2023_sales# A tibble: 45 × 2
location_zip_code october_2023_sales
<chr> <dbl>
1 78701 35767283
2 78704 28137417
3 78702 8540954
4 78758 7239369
5 78703 3207811
6 78712 2426589
7 78719 2313270
8 78748 2269019
9 78735 2225663
10 78752 2149455
# ℹ 35 more rowsNext, I calculate the total alcohol sales for the entire year of 2023 for each ZIP code. This step uses filter() to select data from 2023 (yr == 2023) and groups the data by location_zip_code. Then I use summarze() to get the totals.
# Total 2023 sales by ZIP code
yearly_2023_sales <- monthly_sales_zip |>
filter(yr == 2023) |>
group_by(location_zip_code) |>
summarize(total_sales_2023 = sum(monthly_sales_totals), .groups = "drop")
yearly_2023_sales# A tibble: 46 × 2
location_zip_code total_sales_2023
<chr> <dbl>
1 78660 1642353
2 78701 399087970
3 78702 100750007
4 78703 37567394
5 78704 156173372
6 78705 17410334
7 78712 20451824
8 78713 95531
9 78717 11109755
10 78719 24562943
# ℹ 36 more rowsThis step combines the October sales (october_2023_sales) with the yearly totals (yearly_2023_sales) for 2023. I first use left_join() to join (yearly_2023_sales) (october_2023_sales) by location_zip_code. I created a new column, october_percentage_2023, to calculate October’s percentage of total yearly sales, rounded to three decimal places. The results are arranged in descending order of this percentage.
# Combine October and yearly sales data for 2023
sales_comparison_2023 <- october_2023_sales |>
left_join(yearly_2023_sales, by = "location_zip_code") |>
mutate(october_percentage_2023 = round((october_2023_sales / total_sales_2023) * 100, 3)) |>
arrange(desc(october_percentage_2023))
sales_comparison_2023# A tibble: 45 × 4
location_zip_code october_2023_sales total_sales_2023 october_percentage_2023
<chr> <dbl> <dbl> <dbl>
1 78704 28137417 156173372 18.0
2 78736 155056 1132850 13.7
3 78712 2426589 20451824 11.9
4 78724 181891 1604527 11.3
5 78731 663000 6666790 9.94
6 78735 2225663 23114733 9.63
7 78719 2313270 24562943 9.42
8 78754 138443 1512445 9.15
9 78774 1492 16433 9.08
10 78738 135207 1499453 9.02
# ℹ 35 more rowsNext, I filter the data to show only those ZIP codes where October sales contributed more than 9.5% to the total yearly sales. This focuses the analysis on neighborhoods with significant sales spikes in October.
# Filter for significant October contributions
significant_october_sales <- sales_comparison_2023 |>
filter(october_percentage_2023 > 9.5)
significant_october_sales# A tibble: 6 × 4
location_zip_code october_2023_sales total_sales_2023 october_percentage_2023
<chr> <dbl> <dbl> <dbl>
1 78704 28137417 156173372 18.0
2 78736 155056 1132850 13.7
3 78712 2426589 20451824 11.9
4 78724 181891 1604527 11.3
5 78731 663000 6666790 9.94
6 78735 2225663 23114733 9.63Data takeaway: The ZIP codes 78704, 78736 and 78712 made the highest contribution to alcohol sales during October 2023.
Visualization: October sales 2023
I will use ggplot to create a bar chart from my data that shows the ZIP codes that made the highest contribution to alcohol sales during October 2023. I will be using this chart for my summary.
# Bar chart for October percentage of total sales
significant_october_sales |>
ggplot(aes(
x = reorder(location_zip_code, october_percentage_2023),
y = october_percentage_2023,
fill = factor(location_zip_code))) +
geom_col(fill = "steelblue") +
geom_text(aes(
label = sprintf("%.2f", october_percentage_2023)),
vjust = 0.5,
hjust = -0.2,
color = "black") +
coord_flip() +
labs(
title = "Austin ZIP codes make higher contributions in October 2023",
subtitle = str_wrap("The ZIP codes 78704, 78736 and 78712 made the highest contribution to alcohol sales during October 2023."),
x = "ZIP Code",
y = "October's percentage of 2023 sales (%)",
caption = "By Gabriella Gonzales. Source: Texas Comptroller of Public Accounts"
) +
theme(
plot.margin = margin(t = 10, r = 30, b = 10, l = 10)
) +
scale_y_continuous(expand = expansion(mult = c(0, 0.1)))
ggsave("figures/oct_2023_percentages.png")Saving 7 x 5 in imageAverage March sales (SXSW)
Which neighborhoods see the largest spikes in sales during SXSW in March?
I am taking monthly_sales_zip, filtering it for March, grouping by location_zip_code, then using summarize to get the average March sales for each ZIP code. Then I arrange in descending order of average sales.
# Calculate average monthly totals for October
average_march_sales <- monthly_sales_zip |>
filter(mo_label == "Mar") |>
group_by(location_zip_code) |>
summarize(avg_march_sales_zip = mean(monthly_sales_totals), .groups = "drop") |>
arrange(desc(avg_march_sales_zip))
average_march_sales# A tibble: 46 × 2
location_zip_code avg_march_sales_zip
<chr> <dbl>
1 78701 29983952.
2 78704 9725019
3 78758 6521978.
4 78702 6398621.
5 78703 2405045.
6 78759 2050612
7 78748 1899132.
8 78728 1868364.
9 78752 1731384.
10 78757 1515592.
# ℹ 36 more rowsSimilar to the October analysis, I join the March averages with yearly averages to evaluate the significance of March sales.
# Join averages
join_averages_mar <- average_march_sales |>
left_join(average_yearly_sales, by = "location_zip_code")
join_averages_mar# A tibble: 46 × 3
location_zip_code avg_march_sales_zip avg_yearly_sales
<chr> <dbl> <dbl>
1 78701 29983952. 311305482
2 78704 9725019 110573161.
3 78758 6521978. 75952845.
4 78702 6398621. 66174440.
5 78703 2405045. 28136590.
6 78759 2050612 22792112.
7 78748 1899132. 20553630.
8 78728 1868364. 10821155.
9 78752 1731384. 20099765
10 78757 1515592. 17770541.
# ℹ 36 more rowsNext, I need to calculate the percentage of average yearly sales that occurred in March. I first use mutate() to find the percentage for each ZIP code and round it to three decimal places. I then filter for ZIP codes where March sales contributed at least 9.5% of the yearly total and arrange in descending order.
# Calculate percentage
mar_sales_percentages <- join_averages_mar |>
mutate(march_percentage = round((avg_march_sales_zip / avg_yearly_sales) * 100, 3)) |>
filter(march_percentage >= 9.5) |>
arrange(desc(march_percentage))
mar_sales_percentages# A tibble: 9 × 4
location_zip_code avg_march_sales_zip avg_yearly_sales march_percentage
<chr> <dbl> <dbl> <dbl>
1 78742 35233. 158348. 22.2
2 78728 1868364. 10821155. 17.3
3 78713 12766. 80202. 15.9
4 78721 62420. 608587. 10.3
5 78727 122440. 1218428. 10.0
6 78736 82899. 853618. 9.71
7 78702 6398621. 66174440. 9.67
8 78701 29983952. 311305482 9.63
9 78746 847379. 8870022 9.55Data takeaway: The ZIP codes 78742, 78728 and 78713 make the highest average contributions to alcohol sales during March.
Visualization: March sales
I will use ggplot to create a bar chart from my data that shows the ZIP codes that make the highest average contribution to alcohol sales during March. I will be using this chart for my summary.
mar_sales_percentages |>
ggplot(aes(
x = reorder(location_zip_code, march_percentage),
y = march_percentage,
fill = factor(location_zip_code))) +
geom_col(fill = "steelblue") +
geom_text(aes(
label = sprintf("%.2f", march_percentage)),
vjust = 0.5,
hjust = -0.2,
color = "black") +
coord_flip() +
labs(
title = "Austin ZIP codes make higher contributions during SXSW in March",
subtitle = str_wrap("The ZIP codes 78742, 78728 and 78713 make the highest average contributions to alcohol sales during March."),
caption = "By Gabriella Gonzales. Source: Texas Comptroller of Public Accounts",
x = "ZIP Code",
y = "March's average percentage of year sales (%)") +
theme(
plot.margin = margin(t = 10, r = 30, b = 10, l = 10)
) +
scale_y_continuous(expand = expansion(mult = c(0, 0.1)))
ggsave("figures/mar_sales_percentages.png")Saving 7 x 5 in imageMarch sales 2023
After looking at the average alcohol sales activity during March, I want to analyze on specific year. I will look at March 2023 in this section.
To analyze alcohol sales for March 2023, I started by filtering the monthly_sales_zip dataset for entries corresponding to March (mo_label == “Mar”) and the year 2023 (yr == 2023). After filtering, I grouped the data by location_zip_code and used the summarize() function to calculate the total sales for March in each ZIP code. Finally, I arranged the results in descending order to easily identify the areas with the highest sales.
# Filter March sales for 2023
march_2023_sales <- monthly_sales_zip |>
filter(mo_label == "Mar", yr == 2023) |>
group_by(location_zip_code) |>
summarize(march_2023_sales = sum(monthly_sales_totals), .groups = "drop") |>
arrange(desc(march_2023_sales))
march_2023_sales# A tibble: 46 × 2
location_zip_code march_2023_sales
<chr> <dbl>
1 78701 41783582
2 78704 13908380
3 78702 10449360
4 78758 8199011
5 78703 3522316
6 78728 3001029
7 78748 2863574
8 78759 2353719
9 78719 2307194
10 78752 2227954
# ℹ 36 more rowsNext, I combined the March sales data (march_2023_sales) with the yearly totals for 2023 (yearly_2023_sales). I used a left_join() on location_zip_code to merge the datasets and created a new column, march_percentage_2023, to calculate March’s percentage of the total annual sales for each ZIP code. I rounded these percentages to three decimal places and arranged the results in descending order to identify ZIP codes where March sales represented a significant portion of the yearly total.
# Combine March and yearly sales data for 2023
sales_comparison_march_2023 <- march_2023_sales |>
left_join(yearly_2023_sales, by = "location_zip_code") |>
mutate(march_percentage_2023 = round((march_2023_sales / total_sales_2023) * 100, 3)) |>
arrange(desc(march_percentage_2023))
sales_comparison_march_2023# A tibble: 46 × 4
location_zip_code march_2023_sales total_sales_2023 march_percentage_2023
<chr> <dbl> <dbl> <dbl>
1 78728 3001029 13050435 23.0
2 78742 44774 223869 20
3 78739 57680 443668 13.0
4 78721 111233 951971 11.7
5 78734 229854 2164584 10.6
6 78733 69554 658038 10.6
7 78701 41783582 399087970 10.5
8 78702 10449360 100750007 10.4
9 78748 2863574 27871917 10.3
10 78746 1087265 10592070 10.3
# ℹ 36 more rowsNext, I filter the data to show only those ZIP codes where March sales contributed more than 10% to the total yearly sales. This focuses the analysis on neighborhoods with significant sales contributions in March.
# Filter for significant March contributions
significant_march_sales <- sales_comparison_march_2023 |>
filter(march_percentage_2023 > 10)
significant_march_sales# A tibble: 11 × 4
location_zip_code march_2023_sales total_sales_2023 march_percentage_2023
<chr> <dbl> <dbl> <dbl>
1 78728 3001029 13050435 23.0
2 78742 44774 223869 20
3 78739 57680 443668 13.0
4 78721 111233 951971 11.7
5 78734 229854 2164584 10.6
6 78733 69554 658038 10.6
7 78701 41783582 399087970 10.5
8 78702 10449360 100750007 10.4
9 78748 2863574 27871917 10.3
10 78746 1087265 10592070 10.3
11 78713 9556 95531 10.0Data takeaway: The ZIP codes 78728, 78742 and 78739 made the highest contribution to alcohol sales during October 2023.
Visualization: March sales 2023
# Bar chart for March percentage of total sales
significant_march_sales |>
ggplot(aes(
x = reorder(location_zip_code, march_percentage_2023),
y = march_percentage_2023,
fill = factor(location_zip_code))) +
geom_col(fill = "steelblue") +
geom_text(aes(
label = sprintf("%.2f", march_percentage_2023)),
vjust = 0.5,
hjust = -0.2,
color = "black") +
coord_flip() +
labs(
title = "Austin ZIP codes make higher contributions in March 2023",
subtitle = str_wrap("The ZIP codes 78728, 78742 and 78739 made the highest contribution to alcohol sales during October 2023."),
x = "ZIP Code",
y = "March's percentage of 2023 sales (%)",
caption = "By Gabriella Gonzales. Source: Texas Comptroller of Public Accounts"
) +
theme(
plot.margin = margin(t = 10, r = 30, b = 10, l = 10)
) +
scale_y_continuous(expand = expansion(mult = c(0, 0.1)))
ggsave("figures/mar_2023_percentages.png")Saving 7 x 5 in image